MySQL常见语句备忘

MySQL

SQL:Structured Query Language
DBMS:DataBase Management System,包括基于文件共享系统、客户机-服务器模式
MySQL is one of the DBMS
本地主机名:localhost,默认端口:3306

SQL语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
# 登录
$ mysql -h localhost -u root -p

# 创建
$ CREATE DATABASE django;
$ SHOW VARIABLES LIKE "CHAR%";
$ SHOW DATABASES;
$ USE django;
$ SHOW TABLES;
$ SHOW COLUMNS FROM app_author; = DESCRIBE app_author;

# 表操作
$ CREATE TABLE customers(cust_id int NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL, cust_address char(50) NULL, PRIMARY KEY (cust_id))ENGINE=InnoDB;
$ ALTER TABLE customers ADD cust_phone char(20);
$ ALTER TABLE customers DROP COLUMN cust_phone;
$ DROP TABLE customers;
$ RENAME TABLE customers TO cus;

# 插入,更新,删除
$ INSERT INTO customers(cust_name, cust_email) VALUES('wcg', 'itswcg@gmail.com'), VALUES('', '');
$ UPDATE customers SET cust_name = 'wcg', cust_email = '' WHERE cust_id =100;
$ DELETE FROM customers WHERE cust_id = 100;

# 查询
$ SELECT email, name FROM app_author;
$ SELECT * FROM app_author;
$ SELECT DISTINCT name FROM app_author; #去重
$ SELECT name FROM app_author LIMIT 3,1; #从第三行开始,下一行
$ SELECT name, email FROM app_author ORDER BY name DESC; #倒序
$ SELECT name, email FROM app_author WHERE name='wcg'; #范围 BETWEEN 5 AND 10 空值检查 IS NULL

# 组合操作负 AND OR IN NOT

$ SELECT name, email FORM app_author WHERE name LIKE ''; # wcg% 以wcg开始,%wcg% 任意位置,_cg 只匹配单个字符
$ SELECT name, email FORM app_author WHERE name REGEXP '';
$ SELECT name, email FORM app_author WHERE Data(timestamp) = '2018-4-17';

# 汇总数据,COUNT(*), AVG(), MAX(), MIN(), SUM()
$ SELECT COUNT(*) AS NUM FROM app_author;

# 子查询
$ SELECT ... WHERE .. IN (SELECT ... WHERE ..);

# 联结
$ SELECT vend_name, prod_name FROM vendors, products WHERE vendors.vend_id = products.vend_id;
$ SELECT vend_name ,prod_name FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
# 自联结
$ SELECT p1.prod_id , p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';
# 外部联结
$ SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

# 组合查询 UNION

# 存储过程
$ CREATE PROCEDURE ordertotal(IN onumber INT, OUT ototal DECIMAL(8,2))BEGIN ... END;
$ CALL ordertotal(10, @total);

# 触发器
$ CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

# 事务处理
$ START TRANSACTION;
$ ...
$ COMMIT;

$ SHOW CREATE TABLE CORE_USER; # 查看建表语言
$ ALTER TABLE users AUTO_INCREMENT=1001; # id从1001开始
$ ALTER TABLE CORE_USER DROP FOREIGN KEY <外键名> # 删除外键,外键名在上一命令中
$ ALTER TABLE CORE_USER CHANGE COLUMN user_id user VARCHAR(255) NOT NULL; # 重命名
$ SELECT * FORM CORE_USER\G 格式化

## 设置

# 设置 utf8mb4
$ ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; # 数据库
$ ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # 表
$ ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # 字段

# 远程访问
$ use mysql;
$ update user set host='%' where user = 'root';
$ select host, user from user;
$ netstat -an | grep 3306 # 查看端口状态
$ vim /etc/mysql/mysql.conf.d/mysqld.conf
$ bind-address = 127.0.0.1 # 注释掉
$ systemctl restart mysql.service # 然后在云服务商放行3306端口

# 用户管理
$ USE mysql;
$ SELECT user FROM user;
$ CREATE USER wcg IDENTIFIED BY 'password';
$ DROP USER wcg;
$ SHOW GRANTS FOR wcg;
$ GRANT/REVOKE SELECT ON customers.* TO wcg;
$ SET PASSWORD FOR wcg = Password('');

# 备份
$ mydqldump -h localhost -P 3306 -u root -p wcg -database django >django.sql
----------本文完,感谢您的阅读----------