主頁 > 知識庫 > 程序員最實用的 SQL 語句收藏,看完這篇就夠了

程序員最實用的 SQL 語句收藏,看完這篇就夠了
熱門標(biāo)簽：萬利達(dá)綜合醫(yī)院地圖標(biāo)注點(diǎn) 在電子版地圖標(biāo)注要收費(fèi)嗎 南京電銷外呼系統(tǒng)哪家好 實體店地圖標(biāo)注怎么標(biāo) 武漢AI電銷機(jī)器人 電銷機(jī)器人 深圳 股票配資電銷機(jī)器人 地圖標(biāo)注如何弄全套標(biāo) 外呼系統(tǒng)會封嗎

前言

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

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,… wherecondition

示例：

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)前時間 current_timestamp，需要重新定義整個列 */
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 '注冊時間';

修改后，查看改動后的列：

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 | 注冊時間 |
+---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+

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ù)

這個操作相當(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)計用戶數(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)建時間'
);
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)建時間'
);
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

用于在多個表中查詢相互匹配的數(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 正好相反，會返回**右表**所有行，即使**左表**中沒有匹配的行，不匹配的用 NULL 填充。
```sql
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 用于合并兩個或多個查詢結(jié)果，合并的查詢結(jié)果必須具有相同數(shù)量的列，并且列擁有形似的數(shù)據(jù)類型，同時列的順序相同。


```sql
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 合計函數(shù)（Aggregate functions）

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

函數(shù) 描述 avg(column) 返回某列的平均值 count(column) 返回某列的行數(shù)（不包括 NULL 值） count(*) 返回被選行數(shù) first(column) 返回在指定的域中第一個記錄的值 last(column) 返回在指定的域中最后一個記錄的值 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) 對數(shù)值指定小數(shù)位數(shù)四舍五入 mod(x, y) 取余（求模） now() 返回當(dāng)前的系統(tǒng)日期 format(c, format) 格式化顯示 datediff(d, date1, date2) 日期計算

以上就是程序員最實用的 SQL 語句收藏看完這篇就夠了的詳細(xì)內(nèi)容，更多關(guān)于程序員SQL 語句的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:
  • 數(shù)據(jù)庫SQL語句優(yōu)化總結(jié)(收藏)
  • 收藏的SQL知識以及SQL語句簡單實踐通俗易懂
  • 一些有用的sql語句整理 推薦收藏
  • sqlserver 統(tǒng)計sql語句大全收藏

標(biāo)簽：武威 廣東 泰安 濟(jì)寧 汕頭 臺州 濟(jì)源 安徽

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《程序員最實用的 SQL 語句收藏,看完這篇就夠了》，本文關(guān)鍵詞  程序員,最實,用的,SQL,語句,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《程序員最實用的 SQL 語句收藏,看完這篇就夠了》相關(guān)的同類信息！
  • 本頁收集關(guān)于程序員最實用的 SQL 語句收藏,看完這篇就夠了的相關(guān)信息資訊供網(wǎng)民參考！
    • 推薦文章