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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
# 登录
$ mysql -h localhost -u root -p

# 创建
$ CREATE DATABASE django;
$ SHOW VARIABLES LIKE "CHAR%";
$ SHOW DATABASES;
$ SHOW CREATE DATABASE name;
$ USE django;
$ SHOW TABLES;
$ SHOW CREATE TABLE name;
$ DESCRIBE app_author; = SHOW COLUMNS FROM app_author;
$ SHOW STATUS
$ SHOW GRANTS
$ SHOW ERRORS SHOW WARNINGS
$ HELP SHOW # 要善于用help

# 表操作
$ 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 4 OFFSET 3; # 从行3开始取4行(从id=4开始)
$ SELECT name, email FROM app_author ORDER BY name, name1 DESC; #倒序
$ SELECT name, email FROM app_author WHERE name='wcg'; #范围 BETWEEN 5 AND 10
$ SELECT * FROM app_author WHERE name IS NULL;

# 组合操作负 AND OR IN NOT
$ SELECT * FROM app_author WHERE id IN (1, 2);
$ 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';

# 字段(field) 和column的意思相同,但是计算来的
$ SELECT CONCAT(name, '-by-wcg') FROM app_author; # 拼接字符串
$ SELECT CONCAT(RTrim(name), '-by-wcg') FROM app_author; # 去掉右边所有的空格
$ AS A 别名 SELECT NOW(); # 当前时间
$ LEFT() LENGTH() SOUNDEX() UPPER() LOWER() SUBSTRING() # 函数
$ CURDATE() CURTIME() DATA() DATA_FORMAT() NOW() YEAR() # 日期
$ SELECT * FROM app_author WHERE DATA(create_time) BETWEEN '2018-01-01' AND '2019-01-01';

# 聚合(汇总)函数,COUNT(*), AVG(), MAX(), MIN(), SUM()
$ SELECT COUNT(*) AS NUM FROM app_author;
$ SELECT COUTN(name) FROM app_author; # 只统计name不为null

# 分组 having是分组后过滤
$ SELECT name, COUNT(*) AS NUM FROM app_author GROUP BY name HAVING name <> 'wcg';
$ SELECT name, count(*) as count from app_author group by name having count(*) > 1; # 找出name重复的

# 子查询
$ SELECT ... WHERE .. IN (SELECT ... WHERE ..); # 能用子查询的尽量都用联结

# 联结
$ SELECT vend_name, prod_name FROM vendors, products WHERE vendors.vend_id = products.vend_id; # sql没有对表进行联结的东西,必须自己做,where子句就是将两个表进行配对
# 笛卡尔积 (两个表没有联结关系)
$ SELECT name, products from app_author, products;
# 内部联结(join 或 inner join, 两张不同的表)
$ SELECT vend_name ,prod_name FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
$ SELECT vend_name, prod_name, quantity FROM orders, products, vendors where products.vend_id = vendors.vend_id and orders.prod_id = products.prod_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';
# 外部联结(left join rigin join, 包括内联结没有包括的行)
$ 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
----------本文完,感谢您的阅读----------