前言
这篇文章的内容很基础，主要是MySQL基础、常用命令的收录，算是《MySQL必知必会》的读书笔记，方便自己查阅。
基础SQL
三部分：权限管理、库操作、表结构操作、数据操作。
登录数据库1
mysql -h 127.0.0.1 -u root -p;
用户权限：1
2
3
4
5
6
7
8mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
1.库操作：
查看建库语句：
show database dbName;
建库：1
2
3create database orders_db01 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
create database if not exists orders_db02 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
create database orders_db03;
查库：
show databases;
use orders_db01;
删库：
drop database dbName;
备份：
mysqldump命令
恢复：
source /xxx/dbName_bak_20210418.sql;
表中删除大量数据可以用optimize table来优化空间。
- 表结构操作
查看有哪些表：
show tables;
查看建表语句：
show create tableName;
查看表列定义：
desc tableName;
show columns from orders;
查看数据库服务器状态信息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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362mysql> show status;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 1469 |
| Bytes_sent | 18536 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_instance | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 2 |
| Com_change_master | 0 |
| Com_change_repl_filter | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 2 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 1 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 1 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_explain_other | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 4 |
| Com_set_option | 0 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 2 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 4 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 5 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 3 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_show_create_user | 0 |
| Com_shutdown | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_group_replication_start | 0 |
| Com_group_replication_stop | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_close | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Com_stmt_reprepare | 0 |
| Compression | OFF |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 9 |
| Created_tmp_disk_tables | 5 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 12 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 6 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 5 |
| Handler_read_key | 7 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 145 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 117 |
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 210407 0:31:28 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 256 |
| Innodb_buffer_pool_bytes_data | 4194304 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 113 |
| Innodb_buffer_pool_pages_free | 256 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 3243 |
| Innodb_buffer_pool_reads | 400 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1081 |
| Innodb_data_fsyncs | 36 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 6623744 |
| Innodb_data_reads | 438 |
| Innodb_data_writes | 152 |
| Innodb_data_written | 2433024 |
| Innodb_dblwr_pages_written | 34 |
| Innodb_dblwr_writes | 3 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 12 |
| Innodb_log_writes | 15 |
| Innodb_os_log_fsyncs | 21 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 21504 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 53 |
| Innodb_pages_read | 399 |
| Innodb_pages_written | 113 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 81 |
| Innodb_rows_read | 100 |
| Innodb_rows_updated | 0 |
| Innodb_num_open_files | 30 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6693 |
| Key_blocks_used | 5 |
| Key_read_requests | 18 |
| Key_reads | 5 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 1.399000 |
| Last_query_partial_plans | 1 |
| Locked_connects | 0 |
| Max_execution_time_exceeded | 0 |
| Max_execution_time_set | 0 |
| Max_execution_time_set_failed | 0 |
| Max_used_connections | 6 |
| Max_used_connections_time | 2021-04-07 00:54:31 |
| Not_flushed_delayed_rows | 0 |
| Ongoing_anonymous_transaction_count | 0 |
| Open_files | 19 |
| Open_streams | 0 |
| Open_table_definitions | 319 |
| Open_tables | 111 |
| Opened_files | 536 |
| Opened_table_definitions | 1 |
| Opened_tables | 3 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_index_stat_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_memory_classes_lost | 0 |
| Performance_schema_metadata_lock_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_nested_statement_lost | 0 |
| Performance_schema_prepared_statements_lost | 0 |
| Performance_schema_program_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_table_lock_stat_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 94 |
| Questions | 36 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 12 |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 120 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 7 |
| Table_open_cache_misses | 3 |
| Table_open_cache_overflows | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 6 |
| Threads_created | 6 |
| Threads_running | 1 |
| Uptime | 3030 |
| Uptime_since_flush_status | 3030 |
+-----------------------------------------------+--------------------------------------------------+
356 rows in set (0.00 sec)
查看完整列定义
mysql> show full columns from orders;
+————+———-+———–+——+—–+———+—————-+———————————+———+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+————+———-+———–+——+—–+———+—————-+———————————+———+
| order_num | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| order_date | datetime | NULL | NO | | NULL | | select,insert,update,references | |
| cust_id | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | |
+————+———-+———–+——+—–+———+—————-+———————————+———+
3 rows in set (0.00 sec)
建表：
CREATE TABLE orders2 (
order_num int(11) NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL,
cust_id int(11) NOT NULL,
PRIMARY KEY (order_num),
KEY fk_orders_customers2 (cust_id),
CONSTRAINT fk_orders_customers2 FOREIGN KEY (cust_id) REFERENCES customers (cust_id)
) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8;
CREATE TABLE orderitems3 (
order_num int(11) NOT NULL comment ‘订单号’,
order_item int(11) NOT NULL comment ‘订单项’,
prod_id char(10) NOT NULL,
quantity int(11) NOT NULL default 1,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (order_num,order_item),
index idx_prod_id(prod_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table orders_new like order;
引擎类型：InnoDB vs MyISAM
- InnoDB：支持事务；不支持全文本搜索；存储在磁盘。
- MyISAM：不支持事务；支持全文本搜索；存储在内存，性能高。
同一个数据库可以混用引擎类型。
修改表结构：
增、删、改列：
alter table vendors add column vend_phone varchar(16) null after vend_address;
alter table vendors modify column vend_phone varchar(32) comment ‘租户手机号码’;
alter table vendors change column vend_phone vend_phone varchar(32) comment ‘租户手机号码’;
alter table vendors drop column vend_phone;
增删改其他类型（主键、外键、索引）：
alter table orderitems2 drop primary key;
alter table orderitems2 add primary key (order_num,order_item);
alter table orderitems2 drop primary key, add column id int(11) NOT NULL AUTO_INCREMENT FIRST, add primary key(id);
alter table orderitems2
add constraint fk_orderitems_orders2 foreign key (order_num) references orders(order_num),
add constraint fk_orderitems_products2 foreign key (prod_id) references products(prod_id);
alter table orderitems2 drop foreign key fk_orderitems_orders2;
普通索引：索引列值可不唯一。
CREATE INDEX indexName ON table_name (column_name);
ALTER table tableName ADD INDEX indexName(columnName);
DROP INDEX [indexName] ON mytable;
唯一索引：索引列值必须唯一，允许有空值。
CREATE UNIQUE INDEX indexName ON mytable(username(length));
ALTER table mytable ADD UNIQUE [indexName] (username(length));
两者均可以指定列索引长度：
修改字段默认值：直接修改表文件，速度很快。
alter table orderitems alter quantity set default 1;
alter table orderitems alter quantity drop default;
删表
drop table if exists orderitems2;
重命名表
rename table orders to orders_old, orders_new to orders;
ALTER TABLE orders_old RENAME TO orders;
视图：
- 特点：重用SQL，虚拟的表，本身不包含数据，保护数据，注意性能问题。
- 规则：表中的order by会覆盖视图中的；不能索引、关联触发器或默认值；可以与表一起连用。
create view productcustomers as
(
select cust_name, cust_contact, prod_id from customers c, orders o, orderitems oi
where c.cust_id = o.cust_id and oi.order_num = o.order_num
);
show create view productcustomers;
drop view productcustomers;
create or replace view productcustomers as
(
select cust_name, cust_contact, prod_id from customers c, orders o, orderitems oi
where c.cust_id = o.cust_id and oi.order_num = o.order_num
);
使用视图
select cust_name, cust_contact from productcustomers where prod_id = ‘TNT2’;
存储过程：
delimiter //
告诉命令行使用程序使用//作为新的语句结束分隔符。
示例：
delimiter //
create procedure proc_ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
) comment ‘Obtain order total, optionally adding tax’
begin
declare total decimal(8,2);
declare taxrate int default 6;
select sum(item_price*quantity) from orderitems where order_num = onumber into total;
– if need to add tax
if taxable then
select total+(total/100*taxrate) into total;
end if;
select total into ototal;
end //
delimiter ;
call proc_ordertotal(20005, 0, @total);
select @total;
drop procedure proc_ordertotal;
show procedure status;
show create procedure proc_ordertotal;
游标：存储游标后，可以根据需要滚动或浏览其中的数据。只能用于存储过程。游标使用结束时必须关闭。
示例：
delimiter //
create procedure proc_processorders()
begin
declare isDone boolean default 0;
declare oNumber int;
declare outTotal decimal(8,2);
declare ordernumbers cursor for (select order_num from orders);
declare continue handler for sqlstate ‘02000’ set isDone = 1;
create table if not exists ordertotals(
order_num int, total decimal(8,2)
);
open ordernumbers;
repeat
fetch ordernumbers into oNumber;
call proc_ordertotal(oNumber, 1, outTotal);
insert into ordertotals(order_num, total) values(oNumber, outTotal);
until isDone end repeat;
close ordernumbers;
end //
delimiter ;
call proc_processorders();
select *from ordertotals;
触发器：
- 四要素：唯一触发器名；关联的表；应该响应的活动（delete, insert, update）；何时执行（before, after）
- 规则：仅支持表；每个表最多支持6个。insert有new虚拟表（值可被更新）；delete有old表，值不可被更新，为只读；update有new和old表，new表的值可被更新，old的为只读。
delimiter //
create trigger tg_neworder after insert on orders for each row
begin
select new.order_num into @tt;
end //
delimiter ;
insert into orders(order_date,cust_id) values(now(), 10001);
select @tt;
drop trigger tg_neworder;
delimiter //
create trigger tg_deleteorder before delete on orders for each row
begin
insert into archive_orders(order_num, order_date, cust_id) values(old.order_num, old.order_date, old.cust_id);
end //
delimiter ;
create trigger tg_updatevendor before update on vendors for each row set new.vend_state = Upper(new.vend_state);
drop trigger tg_updatevendor;
事务
要么全部执行失败，要么全部执行成功，对于中途发生错误的事务回滚。
只能回退insert, update, delete语句。
start transaction;
xxx操作
rollback;
start transaction;
xxx操作
commit;
保留点
savepoint deletePoint;
xxx操作
rollback to deletePoint;
执行rollback或commit后事务结束，保留点释放，或使用release savepoint deletePoint释放
取消MySQL自动提交：
set autocommit = 0;
全球化和本地化
查看字符集：
show character set;
查看校对：
show collation;
可以在安装时定义一个默认字符集和校对，也可以在创建数据库时创建，还可以对表、表字段创建。
show variables like ‘character%’;
show variables like ‘collation%’;
create table mytable(
column1 int,
column2 varchar(32)
) default character set hebrew
collate hebrew_general_ci;
安全管理
use mysql;
select * from user;
创建用户
create user tsukasa identified by ‘p12345’;
设置访问权限
三要素：
- 要授予的权限
- 被授予访问权限的数据库或表
- 用户名
show grants for tsukasa;
grant select, insert, update, delete on test. to tsukasa;
grant all on test. to tsukasa;
回收权限
revoke select on test.* from tsukasa;
更改口令
set password for tsukasa = Password(‘p54321’);
重命名用户
rename user tsukasa to tsukasa2;
删除用户
drop user tsukasa2;
表结构处理
数据处理
增
单行
INSERT INTO products (prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES (‘FU12’, ‘1002’, ‘Apple’, ‘4.00’, ‘sweet’);
批量（提高性能）
INSERT INTO products (prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES (‘FU12’, ‘1002’, ‘Apple’, ‘4.00’, ‘sweet’),
(‘FU13’, ‘1003’, ‘Banana’, ‘4.00’, ‘sweet’);
INSERT INTO products_new (prod_id, vend_id, prod_name, prod_price, prod_desc)
select (prod_id, vend_id, prod_name, prod_price, prod_desc) from products where vend_id = 1002;
删
delete from products where prod_id = ‘FU12’;
truncate tableName;
改
update customers set cust_name = ‘The Fudds’, cust_email = ‘elmer@fudd.com’
where cust_id = 10005;
查
select子句顺序：select>from>where>group by>having>order by>limit
select from 表名 where … having … group by … order by… limit …
selct from 表名1 inner/left/right join 表名2 on 条件 group by…
限制行数（limit）
select from products limit 5;
select from products limit 3,2;
按字典排序（order by）
select prod_id as ‘产品ID’, prod_name as ‘产品名称’ from products order by prod_id desc, vend_id asc;
- 典型场景：找出最昂贵的物品
select prod_id, prod_price from products order by prod_price desc limit 1;
条件查询（where）
select * from products where prod_name = ‘Oil can’ and prod_price < 10;
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;
IN与NOT、BETWEEN
select prod_id ‘产品ID’ from products where vend_id in (‘1001’, ‘1002’, ‘1003’);
select prod_id ‘产品ID’ from products where vend_id not in (‘1001’, ‘1002’, ‘1003’);
select prod_name, prod_price from products where prod_price between 5 and 10;
select distinct vend_id from products;
通配符（多字符）：
select t1.prod_id, t1.prod_name from products t1 where t1.prod_id like ‘ANV%’;
select t1.prod_id, t1.prod_name from products t1 where t1.prod_id like ‘ANV%’;
单字符：
select prod_id, prod_name from products where prodname like ‘ ton%’;
正则表达式：
select prod_id, prod_name from products where prod_name regexp ‘.000’;
常见函数：
官方文档链接：https://dev.mysql.com/doc/refman/5.6/en/functions.html
- 数学函数：ABS(x), SQRT(x), MOD(x,y), CEIL(x), COS(x), LOG10(x), DEGREES(x)弧度换角度, ROUND(x, D), sign(x)正负零判断, SUM(x), AVG(x), MAX(x), MIN(x), COUNT(x)
- 字符串函数：CONCAT(str1, str2, …, strn), RTRIM(str), TRIM(str), UPPER(str), LENGTH(str), CHAR_LENGTH(str), LPAD(s1, len, s2)用s2在s1左边填充，直到整个字符串为len, SUBSTRING(str,pos), SUBSTRING(str,pos,len), STRCMP(s1, s2)字符串是否相等, REVERSE(str), LEFT(s,n), REPEAT(s,n), REPLACE(s,s1,s2)
- 日期和时间函数：CURDATE(), NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date), DAYOFYEAR(d)、DAYOFMONTH(d), WEEKOFYEAD(d), QUARTER(date), HOUR(), ADDTIME(date,expr), SUBTIME(date,expr), datediff(d1,d2), date_add(time, intervalTime), date_format(time, format)
- 条件判断函数：IF(expr,v1,v2), IFNULL(v1,v2), case expr when v1 then r1 [when v2 then r2][else r3] end类似case函数
- 系统信息函数：CONNECTION_ID(), USER()
- 加密函数：MD5(str), PASSWORD(str)单项加密
- 格式化函数：CONV(N,from_base,to_base)进制转换，FORMAT(x,n)将数字x格式化并以四十五入方式保留小数点后n位，以字符串形式返回。
示例：
SELECT MOD(29,9);
select concat(vend_name, ‘(‘, vend_country, ‘)’) as ‘vendInfo’ from vendors order by vend_name;
select prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems where order_num = 20005;
select degrees(pi());
select substring(“abcdefg”, 1, 3);
select MONTH(now());
select datediff(now(), ‘2021-04-06’);
SELECT DATE_ADD(‘2018-05-01’,INTERVAL 1 DAY);
SELECT DATE_FORMAT(curtime(), ‘%W %M %Y’);
select DAYOFWEEK(“2021-04-08”);
select cust_id, order_num from orders where date(order_date) between ‘2005-09-01’ and ‘2005-09-30’;
select (case ‘b’
when ‘a’ then ‘1’
when ‘b’ then ‘2’
else ‘3’
end) as ‘value’;
select CONNECTION_ID(), USER();
select conv(3,10,2);
分组数据(group by)：可以包含任意数目的列，一般使用group by子句时要用order by保证数据顺序
select vend_id, count(*) as num_prods from products group by vend_id;
过滤分组(having)：支持所有where操作符，having关键字需要出现在group后面，order by放在having后面。
select cust_id, count() as orders_num from orders group by cust_id having count() >= 2;
select order_num, sum(quantityitem_price) ordertotal from orderitems
group by order_num
having sum(quantityitem_price) >= 50
order by ordertotal;
子查询：
select cust_name, cust_contact from customers
where cust_id in (
select cust_id from orders
where order_num in (select order_num from orderitems
where prod_id = ‘TNT2’));
select t0.cust_name, t0.cust_state,
(select count(*) from orders t1
where t1.cust_id = t0.cust_id) as order_numbers
from customers t0
order by t0.cust_name;
联结表：实际上是第一个表的第一行与第二个表符合条件的每一行配对，即笛卡尔积(m*n)，联结表越多性能下降越厉害，有时候处理联结比子查询要快。
分为内联（inner join, 表相同，不会出现没有关联的行）、外联(left join, right join, 包含了没有关联的行)
select vend_name, prod_name, prod_price
from vendors v1 inner join products p1
on v1.vend_id = p1.vend_id
order by vend_name, prod_name;
select p1.prod_id, p1.prod_name from products p1, products p2
where p1.vend_id = p2.vend_id and p2.prod_id = ‘DTNTR’;
select c.cust_id, o.order_num
from customers c left join orders o
on c.cust_id = o.cust_id;
+———+———–+
| cust_id | order_num |
+———+———–+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+———+———–+
6 rows in set (0.00 sec)
select c.cust_id, o.order_num
from customers c right join orders o
on c.cust_id = o.cust_id;
+———+———–+
| cust_id | order_num |
+———+———–+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+———+———–+
5 rows in set (0.00 sec)
select c.cust_name, c.cust_id, count(o.order_num) as num_ord
from customers c left join orders o
on c.cust_id = o.cust_id
group by c.cust_id;
组合查询：也叫符合查询，多条select语句组成
union：求并集。
union all：包含重复的行
(
select vend_id, prod_id, prod_price from products
where prod_price < 5
)
union
(
select vend_id, prod_id, prod_price from products
where vend_id in (1001, 1002)
)
order by vend_id, prod_price;
====其他知识
show variables like ‘slow_query_log’; ##查询并设置慢SQL日志开关
set global slow_query_log=’ON’;
show variables like ‘long_query_time’; ##查询并设置慢sql时间标准
set global long_query_time=0.3;
了解show命令
help show;
help create table;
1 | mysql> help show; |
声明：本站所有文章均为原创或翻译，遵循署名 - 非商业性使用 - 禁止演绎 4.0 国际许可协议，如需转载请确保您对该协议有足够了解，并附上作者名 (Tsukasa) 及原文地址
