前言

文章沿著設(shè)計(jì)一個(gè)假想的應(yīng)用 awesome_app 為主線，從零創(chuàng)建修改數(shù)據(jù)庫，表格，字段屬性，索引，字符集，默認(rèn)值，自增，增刪改查，多表查詢，內(nèi)置函數(shù)等實(shí)用 SQL 語句。收藏此文，告別零散又低效地搜索經(jīng)常使用的 SQL 語句。所有 SQL 都在 MySQL 下通過驗(yàn)證，可留著日后回顧參考，也可跟著動(dòng)手一起做。

1. 創(chuàng)建

1.1 創(chuàng)建數(shù)據(jù)庫

語法：create database db_name

示例：創(chuàng)建應(yīng)用數(shù)據(jù)庫 awesome_app

create database `awesome_app`

1.2 創(chuàng)建表格

語法：create table table_name （ 。.. columns ）

示例：創(chuàng)建用戶表 users

create table `users`（ `id` int， `name` char（10）， `avatar` varchar（300）， `regtime` date）

1.3 創(chuàng)建索引

語法：create index index_name on table_name （column_name）

示例：為用戶 id 創(chuàng)建索引 idx_id

create index `idx_id` on `users` （`id`）/* 創(chuàng)建唯一索引 */create unique index `idx_id` on `users` （`id`）

1.4 為已存在的列創(chuàng)建主鍵

更常用的方式是在創(chuàng)建表語句所有列定義的后面添加一行 primary key （column_name）。

語法：alter table table_name add primary key （column_name）

示例：將用戶 id 設(shè)為主鍵

alter table users add primary key （`id`）

1.5 為已存在的列創(chuàng)建自增約束

更常用的方式是在創(chuàng)建表語句中添加自增列 id int not null auto_increment。

alter table `users` modify `id` int not null auto_increment

2. 插入

語法：

insert into table_name values （value1， value2， 。..）

insert into table_name （column1， column2， 。..） values （value1， value2， 。..）

示例：新增注冊用戶

insert into `users` values （1， ‘ken’， ‘http://cdn.awesome_app.com/path/to/xxx/avatar1.jpg’， curdate（））/* 指定列插入 */insert into `users` （`name`， `avatar`） values （‘bill’， ‘http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg’）

3. 修改

3.1 修改數(shù)據(jù)記錄

語法：

update table_name set column=new_value where condition

update table_name set column1=new_value1，column2=new_value2，。..where condition

示例：

update `users` set `regtime`=curdate（） where `regtime` is null/* 一次修改多列 */update `users` set `name`=‘steven’，`avatar`=‘http://cdn.awesome_app.com/path/to/xxx/steven.jpg’ where `id`=1

3.2 修改數(shù)據(jù)庫字符集為 utf8

alter database `awesome_app` default character set utf8

3.3 修改表字符集為 utf8

alter table `users` convert to character set utf8

3.4 修改表字段字符集為 utf8

alter table `users` modify `name` char（10） character set utf8

3.5 修改字段類型

alter table `users` modify `regtime` datetime not null

3.5 修改字段默認(rèn)值

alter table `users` alter `regtime` set default ‘2019-10-12 00:00:00’/* 設(shè)置默認(rèn)為當(dāng)前時(shí)間 current_timestamp，需要重新定義整個(gè)列 */alter table `users` modify `regtime` datetime not null default current_timestamp

3.6 修改字段注釋

alter table `users` modify `id` int not null auto_increment comment ‘用戶ID’;alter table `users` modify `name` char（10） comment ‘用戶名’;alter table `users` modify `avatar` varchar（300） comment ‘用戶頭像’;alter table `users` modify `regtime` datetime not null default current_timestamp comment ‘注冊時(shí)間’;

修改后，查看改動(dòng)后的列：

mysql》 show full columns from users;+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+| id | int（11） | NULL | NO | PRI | NULL | auto_increment | select，insert，update，references | 用戶ID || name | char（10） | utf8_general_ci | YES | | NULL | | select，insert，update，references | 用戶名 || avatar | varchar（300） | utf8_general_ci | YES | | NULL | | select，insert，update，references | 用戶頭像 || regtime | datetime | NULL | NO | | CURRENT_TIMESTAMP | | select，insert，update，references | 注冊時(shí)間 |+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+

4. 刪除

4.1 刪除數(shù)據(jù)記錄

語法：delete from table_name where condition

示例：刪除用戶名未填寫的用戶

# 先增加一條用戶名為空的用戶mysql》 insert into `users` （`regtime`） values （curdate（））;mysql》 select * from users;+----+--------+----------------------------------------------------+------------+| id | name | avatar | regtime |+----+--------+----------------------------------------------------+------------+| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 || 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 || 3 | NULL | NULL | 2019-10-12 |+----+--------+----------------------------------------------------+------------+# 刪除用戶名為空的行mysql》 delete from `users` where `name` is null;mysql》 select * from users;+----+--------+----------------------------------------------------+------------+| id | name | avatar | regtime |+----+--------+----------------------------------------------------+------------+| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 || 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |+----+--------+----------------------------------------------------+------------+

4.2 刪除數(shù)據(jù)庫

drop database if exists `awesome_app`

4.3 刪除表

drop table if exists `users`

4.4 清空表中所有數(shù)據(jù)

這個(gè)操作相當(dāng)于先 drop table 再 create table ，因此需要有 drop 權(quán)限。

truncate table `users`

4.5 刪除索引

drop index `idx_id` on `users`

5. 查詢

5.1 語法

SELECT ［ALL | DISTINCT | DISTINCTROW ］ ［HIGH_PRIORITY］ ［STRAIGHT_JOIN］ ［SQL_SMALL_RESULT］ ［SQL_BIG_RESULT］ ［SQL_BUFFER_RESULT］ ［SQL_CACHE | SQL_NO_CACHE］ ［SQL_CALC_FOUND_ROWS］ select_expr ［， select_expr 。..］ ［FROM table_references ［PARTITION partition_list］ ［WHERE where_condition］ ［GROUP BY {col_name | expr | position} ［ASC | DESC］， 。.. ［WITH ROLLUP］］ ［HAVING where_condition］ ［ORDER BY {col_name | expr | position} ［ASC | DESC］， 。..］ ［LIMIT {［offset，］ row_count | row_count OFFSET offset}］ ［PROCEDURE procedure_name（argument_list）］ ［INTO OUTFILE ‘file_name’ ［CHARACTER SET charset_name］ export_options | INTO DUMPFILE ‘file_name’ | INTO var_name ［， var_name］］ ［FOR UPDATE | LOCK IN SHARE MODE］］

5.2 單表查詢

5.2.1 準(zhǔn)備數(shù)據(jù)：

insert into users （`name`， `avatar`） values（‘張三’， ‘http://cdn.awesome_app.com/path/to/xxx/3.jpg’），（‘李四’， ‘http://cdn.awesome_app.com/path/to/xxx/4.jpg’），（‘王五’， ‘http://cdn.awesome_app.com/path/to/xxx/5.jpg’），（‘馬六’， ‘http://cdn.awesome_app.com/path/to/xxx/6.jpg’），（‘肖七’， ‘http://cdn.awesome_app.com/path/to/xxx/7.jpg’），（‘劉八’， ‘http://cdn.awesome_app.com/path/to/xxx/8.jpg’），（‘楊九’， ‘http://cdn.awesome_app.com/path/to/xxx/9.jpg’），（‘鄭十’， ‘http://cdn.awesome_app.com/path/to/xxx/10.jpg’）;/* 增加重復(fù)行 */insert into users （`name`， `avatar`） values（‘張三’， ‘http://cdn.awesome_app.com/path/to/xxx/3.jpg’），（‘李四’， ‘http://cdn.awesome_app.com/path/to/xxx/4.jpg’），（‘王五’， ‘http://cdn.awesome_app.com/path/to/xxx/5.jpg’）;

5.2.2 查詢所有列

mysql》 select * from users;+----+--------+----------------------------------------------------+---------------------+| id | name | avatar | regtime |+----+--------+----------------------------------------------------+---------------------+| 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 00:00:00 || 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 00:00:00 || 3 | 張三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg | 2019-10-13 10:58:37 || 4 | 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg | 2019-10-13 10:58:37 || 5 | 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg | 2019-10-13 10:58:37 || 6 | 馬六 | http://cdn.awesome_app.com/path/to/xxx/6.jpg | 2019-10-13 10:58:37 || 7 | 肖七 | http://cdn.awesome_app.com/path/to/xxx/7.jpg | 2019-10-13 10:58:37 || 8 | 劉八 | http://cdn.awesome_app.com/path/to/xxx/8.jpg | 2019-10-13 10:58:37 || 9 | 楊九 | http://cdn.awesome_app.com/path/to/xxx/9.jpg | 2019-10-13 10:58:37 || 10 | 鄭十 | http://cdn.awesome_app.com/path/to/xxx/10.jpg | 2019-10-13 10:58:37 || 11 | 張三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg | 2019-10-13 11:20:17 || 12 | 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg | 2019-10-13 11:20:17 || 13 | 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg | 2019-10-13 11:20:17 |+----+--------+----------------------------------------------------+---------------------+

5.2.3 查詢指定列

mysql》 select id，name from users;+----+--------+| id | name |+----+--------+| 1 | steven || 2 | bill || 3 | 張三 || 4 | 李四 || 5 | 王五 || 6 | 馬六 || 7 | 肖七 || 8 | 劉八 || 9 | 楊九 || 10 | 鄭十 || 11 | 張三 || 12 | 李四 || 13 | 王五 |+----+--------+

5.2.4 查詢不重復(fù)記錄

mysql》 select distinct name，avatar from users;+--------+----------------------------------------------------+| name | avatar |+--------+----------------------------------------------------+| steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg || bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg || 張三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg || 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg || 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg || 馬六 | http://cdn.awesome_app.com/path/to/xxx/6.jpg || 肖七 | http://cdn.awesome_app.com/path/to/xxx/7.jpg || 劉八 | http://cdn.awesome_app.com/path/to/xxx/8.jpg || 楊九 | http://cdn.awesome_app.com/path/to/xxx/9.jpg || 鄭十 | http://cdn.awesome_app.com/path/to/xxx/10.jpg |+--------+----------------------------------------------------+

5.2.5 限制查詢行數(shù)

查詢前幾行

mysql》 select id，name from users limit 2;+----+--------+| id | name |+----+--------+| 1 | steven || 2 | bill |+----+--------+

查詢從指定偏移（第一行為偏移為0）開始的幾行

mysql》 select id，name from users limit 2，3;+----+--------+| id | name |+----+--------+| 3 | 張三 || 4 | 李四 || 5 | 王五 |+----+--------+

5.2.6 排序

# 正序mysql》 select distinct name from users order by name asc limit 3;+--------+| name |+--------+| bill || steven || 劉八 |+--------+# 倒序mysql》 select id，name from users order by id desc limit 3;+----+--------+| id | name |+----+--------+| 13 | 王五 || 12 | 李四 || 11 | 張三 |+----+--------+

5.2.7 分組

增加城市字段

alter table `users` add `city` varchar（10） comment ‘用戶所在城市’ after `name`;update `users` set `city`=‘舊金山’ where `id`=1;update `users` set `city`=‘西雅圖’ where `id`=2;update `users` set `city`=‘北京’ where `id` in （3，5，7）;update `users` set `city`=‘上海’ where `id` in （4，6，8）;update `users` set `city`=‘廣州’ where `id` between 9 and 10;update `users` set `city`=‘深圳’ where `id` between 11 and 13;

按城市分組統(tǒng)計(jì)用戶數(shù)

mysql》 select city， count（name） as num_of_user from users group by city;+-----------+-------------+| city | num_of_user |+-----------+-------------+| 上海 | 3 || 北京 | 3 || 廣州 | 2 || 舊金山 | 1 || 深圳 | 3 || 西雅圖 | 1 |+-----------+-------------+mysql》 select city， count（name） as num_of_user from users group by city having num_of_user=1;+-----------+-------------+| city | num_of_user |+-----------+-------------+| 舊金山 | 1 || 西雅圖 | 1 |+-----------+-------------+mysql》 select city， count（name） as num_of_user from users group by city having num_of_user》2;+--------+-------------+| city | num_of_user |+--------+-------------+| 上海 | 3 || 北京 | 3 || 深圳 | 3 |+--------+-------------+

5.3 多表關(guān)聯(lián)查詢

5.3.1 準(zhǔn)備數(shù)據(jù)

create table if not exists `orders`（ `id` int not null primary key auto_increment comment ‘訂單ID’， `title` varchar（50） not null comment ‘訂單標(biāo)題’， `user_id` int not null comment ‘用戶ID’， `cretime` timestamp not null default current_timestamp comment ‘創(chuàng)建時(shí)間’）;create table if not exists `groups`（ `id` int not null primary key auto_increment comment ‘用戶組ID’， `title` varchar（50） not null comment ‘用戶組標(biāo)題’， `cretime` timestamp not null default current_timestamp comment ‘創(chuàng)建時(shí)間’）;alter table `users` add `group_id` int comment ‘用戶分組’ after `city`;insert into `groups` （`title`） values （‘大佬’）， （‘萌新’）， （‘菜雞’）;insert into `orders` （`title`， `user_id`） values （‘《大佬是怎樣煉成的？》’， 3）， （‘《MySQL 從萌新到刪庫跑路》’， 6）， （‘《菜雞踩坑記》’， 9）;update `users` set `group_id`=1 where `id` between 1 and 2;update `users` set `group_id`=2 where `id` in （4， 6， 8， 10， 12）;update `users` set `group_id`=3 where `id` in （3， 5， 13）;

5.3.2 join

join

用于在多個(gè)表中查詢相互匹配的數(shù)據(jù)。

mysql》 select `users`。`name` as `user_name`， `orders`。`title` as `order_title` from `users`， `orders` where `orders`。`user_id`=`users`。`id`;+-----------+--------------------------------------+| user_name | order_title |+-----------+--------------------------------------+| 張三 | 《大佬是怎樣煉成的？》 || 馬六 | 《MySQL 從萌新到刪庫跑路》 || 楊九 | 《菜雞踩坑記》 |+-----------+--------------------------------------+

inner join

內(nèi)部連接。效果與 join 一樣 ， 但用法不同，join 使用 where ，inner join 使用 on 。

mysql》 select `users`。`name` as `user_name`， `orders`。`title` as `order_title` from `users` inner join `orders` on `orders`。`user_id`=`users`。`id`;+-----------+--------------------------------------+| user_name | order_title |+-----------+--------------------------------------+| 張三 | 《大佬是怎樣煉成的？》 || 馬六 | 《MySQL 從萌新到刪庫跑路》 || 楊九 | 《菜雞踩坑記》 |+-----------+--------------------------------------+

left join

左連接。返回左表所有行，即使右表中沒有匹配的行，不匹配的用 NULL 填充。

mysql》 select `users`。`name` as `user_name`， `orders`。`title` as `order_title` from `users` left join `orders` on `orders`。`user_id`=`users`。`id`;+-----------+--------------------------------------+| user_name | order_title |+-----------+--------------------------------------+| 張三 | 《大佬是怎樣煉成的？》 || 馬六 | 《MySQL 從萌新到刪庫跑路》 || 楊九 | 《菜雞踩坑記》 || steven | NULL || bill | NULL || 李四 | NULL || 王五 | NULL || 肖七 | NULL || 劉八 | NULL || 鄭十 | NULL || 張三 | NULL || 李四 | NULL || 王五 | NULL |+-----------+--------------------------------------+

right join

右連接。和 left join 正好相反，會(huì)返回右表所有行，即使左表中沒有匹配的行，不匹配的用 NULL 填充。

mysql》 select `groups`。`title` as `group_title`， `users`。`name` as `user_name` from `groups` right join `users` on `users`。`group_id`=`groups`。`id`;+-------------+-----------+| group_title | user_name |+-------------+-----------+| 大佬 | steven || 大佬 | bill || 萌新 | 李四 || 萌新 | 馬六 || 萌新 | 劉八 || 萌新 | 鄭十 || 萌新 | 李四 || 菜雞 | 張三 || 菜雞 | 王五 || 菜雞 | 王五 || NULL | 肖七 || NULL | 楊九 || NULL | 張三 |+-------------+-----------+

5.3.3 union

union 用于合并兩個(gè)或多個(gè)查詢結(jié)果，合并的查詢結(jié)果必須具有相同數(shù)量的列，并且列擁有形似的數(shù)據(jù)類型，同時(shí)列的順序相同。

mysql》 （select `id`， `title` from `groups`） union （select `id`， `title` from `orders`）;+----+--------------------------------------+| id | title |+----+--------------------------------------+| 1 | 大佬 || 2 | 萌新 || 3 | 菜雞 || 1 | 《大佬是怎樣煉成的？》 || 2 | 《MySQL 從萌新到刪庫跑路》 || 3 | 《菜雞踩坑記》 |+----+--------------------------------------+

6. 函數(shù)

6.1 語法

select function（column） from table_name

6.2 合計(jì)函數(shù)（Aggregate functions）

合計(jì)函數(shù)的操作面向一系列的值，并返回一個(gè)單一的值。通常與 group by 語句一起用。

函數(shù) 描述 avg（column） 返回某列的平均值 count（column） 返回某列的行數(shù)（不包括 NULL 值） count（*） 返回被選行數(shù) first（column） 返回在指定的域中第一個(gè)記錄的值 last（column） 返回在指定的域中最后一個(gè)記錄的值 max（column） 返回某列的最高值 min（column） 返回某列的最低值 sum（column） 返回某列的總和 6.3 標(biāo)量函數(shù)（Scalar functions）

函數(shù) 描述 ucase（c） 轉(zhuǎn)換為大寫 lcase（c） 轉(zhuǎn)換為小寫 mid（c， start［， end］） 從文本提取字符 len（c） 返回文本長度 instr（c， char） 返回在文本中指定字符的數(shù)值位置 left（c， number_of_char） 返回文本的左側(cè)部分 right（c， number_of_char） 返回文本的右側(cè)部分 round（c， decimals） 對(duì)數(shù)值指定小數(shù)位數(shù)四舍五入 mod（x， y） 取余（求模） now（） 返回當(dāng)前的系統(tǒng)日期 format（c， format） 格式化顯示 datediff（d， date1， date2） 日期計(jì)算