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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
# 登录
$ 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 COLUMN cust_phone char(20);
$ ALTER TABLE customers DROP COLUMN cust_phone;
$ ALTER TABLE customers modify COLUMN cust_phone varchar(1024) NOT NULL;
$ DROP TABLE customers;
$ RENAME TABLE customers TO cus;
$ ALTER TABLE customers DROP PRIMARY KEY, ADD PRIMARY KEY(id, create_time)

# 插入,更新,删除
$ 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开始) offset = (page-1) * limit
$ 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';

# exists
$ SELECT * FORM app_author A WHERE exists(SELECT * FROM customers C WHERE A.id=C.user_id)

# 字段(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);
$ SHOW PROCEDURE STATUS; # 查看数据库存储的过程
$ SHOW CREATE PROCEDURE 存储过程名
$ DROP PROCEDURE 存储过程名

$ DELIMITER // # 给变分隔符,默认;否则存储过程中有;监视器会无法分辨
$ CREATE PROCEDURE sp_search_customer() (IN p_name VARCHAR(20)) # out p_result INT
$ BEGIN
$ DECLARE tem CHAR(4); # 申明变量
$ SET tem='wcg'; # 给变量赋值
$ IF p_name=NULL OR p_name='' THEN
$ select * from customers;
$ ElSE
$ select * from customers where nam like p_nam;
$ END IF;
$ END//

$ CALL sp_search_customer(""); # 调用
$ CALL sp_search_customer('wcg', @res) # out
$ SELECT @res

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

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

# 定时任务
$ SHOW VARIABLES LIKE 'event_scheduler'; # 确定event是否开启
$ SET GLOBAL event_scheduler = 1;
$ SHOW EVENTS;
$ SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
$ SHOW CREATE EVENT 任务名
$ create event second_event
$ on sechdule every 1 second
$ on completion preserve disable # 创建后并不开始生效
$ do call sp_search_customer();
$ alter event second_event on completion preserve enable; # 开启定时任务
$ alter event second_event on sechdule every 1 day starts current_timestamp;
$ alter event second_event rename to third_event; # 重命名

# 分区 按时间
$ ALTER TABLE customers DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `create_time`);
$ ALTER TABLE customers PARTITION BY RANGE (YEARWEEK(`create_time`)) (
PARTITION p201811 VALUES LESS THAN (201811) ENGINE = InnoDB,
PARTITION p201812 VALUES LESS THAN (201812) ENGINE = InnoDB,
PARTITION p201813 VALUES LESS THAN (201813) ENGINE = InnoDB,
); # 分区必须包含所有数据范围 yearweek 一年的第几周
$ ALTER TABLE customers ADD PARTITION (PARTITION p201820 VALUES LESS THAN (201820) ENGINE = InnoDB); # 手动添加分区
$ ALTER TABLE customers DROP PARTITION p201820; # 删除
$ SELECT * FROM infomation_schema.PARTITIONS where table_schema='django' and table_name='customers'; # 查看分区大小
$ SELECT PARTITION_ORDINAL_POSITION, TABLE_ROWS, PARTITION_METHOD FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'django' AND TABLE_NAME = 'customers';

$ 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 格式化

# 索引
$ ALTER TABLE CORE_USER ADD INDEX index_name (column_name) # index_name 可省略
$ ALTER TABLE CORE_USER ADD UNIQUE/PRIMARY KEY (column_name) # 创建唯一,主键索引
$ ALTER TABLE CORE_USER DROP INDEX index_name # 删除

## 设置
# 设置 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('');

# 复制表
$ CREATE TABLE customers_bk select * from customers;

# 备份
$ mysqldump -h localhost -P 3306 -u root -p wcg --set-gtid-purged=OFF -database django >django.sql # database 不包含 GTID 信息
$ mysqldump -h localhost -P 3306 -u root -p wcg django user > user.sql # table
$ mysqldump -h localhost -P 3306 -u root -p wcg django user --where="name='wcg'" > user.sql # 筛选条件 “true limit 100” 前100个
# 导入
$ mysql -h localhost -u root -p wcg django < user.sql
$ source user.sql
# 导出excel
$ mysql -h 127.0.0.1 -u root -pwcg --default-character-set=utf8 -e "select * from user" django > user.xls
----------本文完,感谢您的阅读----------