个人中心
gpt4 book ai didi

程序员最实用的 SQL 语句收藏,看完这篇就够了

转载 作者：qq735679552 更新时间：2022-09-28 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章程序员最实用的 SQL 语句收藏,看完这篇就够了由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

程序员最实用的 SQL 语句收藏,看完这篇就够了

前言

  。

文章沿着设计一个假想的应用 awesome_app 为主线，从零创建修改数据库，表格，字段属性，索引，字符集，默认值，自增，增删改查，多表查询，内置函数等实用 sql 语句。收藏此文，告别零散又低效地搜索经常使用的 sql 语句。所有 sql 都在 mysql 下通过验证，可留着日后回顾参考，也可跟着动手一起做，如果未安装 mysql 可参考 《macos 安装 mysql》 (windows 安装大同小异).

1. 创建

  。

1.1 创建数据库 。

语法：create database db_name 。

示例：创建应用数据库 awesome_app 。

?
1
create database `awesome_app`

1.2 创建表格 。

语法：create table table_name ( … columns ) 。

示例：创建用户表 users 。

?
1
2
3
4
5
6
7
create table `users`
(
  `id` int ,
  ` name ` char (10),
  `avatar` varchar (300),
  `regtime` date
)

1.3 创建索引 。

语法：create index index_name on table_name (column_name) 。

示例：为用户 id 创建索引 idx_id 。

?
1
2
3
create index `idx_id` on `users` (`id`)
/* 创建唯一索引 */
create unique index `idx_id` on `users` (`id`)

1.4 为已存在的列创建主键 。

更常用的方式是在创建表语句所有列定义的后面添加一行 primary key (column_name).

语法：alter table table_name add primary key (column_name) 。

示例：将用户 id 设为主键 。

?
1
alter table users add primary key (`id`)

1.5 为已存在的列创建自增约束 。

更常用的方式是在创建表语句中添加自增列 id int not null auto_increment.

?
1
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, …)

示例：新增注册用户 。

?
1
2
3
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 修改数据记录 。

语法:

  • update table_name set column=new_value where condition
  • update table_name set column1=new_value1,column2=new_value2,… wherecondition

示例:

?
1
2
3
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 修改数据库字符集为 utf8 。

?
1
alter database `awesome_app` default character set utf8

3.3 修改表字符集为 utf8 。

?
1
alter table `users` convert to character set utf8

3.4 修改表字段字符集为 utf8 。

?
1
alter table `users` modify ` name ` char (10) character set utf8

3.5 修改字段类型 。

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

3.5 修改字段默认值 。

?
1
2
3
alter table `users` alter `regtime` set default '2019-10-12 00:00:00'
/* 设置默认为当前时间 current_timestamp ，需要重新定义整个列 */
alter table `users` modify `regtime` datetime not null default current_timestamp

3.6 修改字段注释 。

?
1
2
3
4
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 '注册时间' ;

修改后，查看改动后的列:

?
1
2
3
4
5
6
7
8
9
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 删除数据记录 。

语法：delete from table_name where condition 。

示例：删除用户名未填写的用户 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 先增加一条用户名为空的用户
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 删除数据库 。

?
1
drop database if exists `awesome_app`

4.3 删除表 。

?
1
drop table if exists `users`

4.4 清空表中所有数据 。

这个操作相当于先 drop table 再 create table ，因此需要有 drop 权限.

?
1
truncate table `users`

4.5 删除索引 。

?
1
drop index `idx_id` on `users`

5. 查询

  。

5.1 语法 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 准备数据:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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' );
/* 增加重复行 */
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 查询所有列 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 查询指定列 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 查询不重复记录 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 限制查询行数 。

查询前几行 。

?
1
2
3
4
5
6
7
mysql> select id, name from users limit 2;
+ ----+--------+
| id | name |
+ ----+--------+
| 1 | steven |
| 2 | bill |
+ ----+--------+

查询从指定偏移(第一行为偏移为0)开始的几行 。

?
1
2
3
4
5
6
7
8
mysql> select id, name from users limit 2,3;
+ ----+--------+
| id | name |
+ ----+--------+
| 3 | 张三 |
| 4 | 李四 |
| 5 | 王五 |
+ ----+--------+

5.2.6 排序 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 正序
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 分组 。

增加城市字段 。

?
1
2
3
4
5
6
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;

按城市分组统计用户数 。

?
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
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 多表关联查询 。

5.3.1 准备数据 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table if not exists `orders`
(
  `id` int not null primary key auto_increment comment '订单id' ,
  `title` varchar (50) not null comment '订单标题' ,
  `user_id` int not null comment '用户id' ,
  `cretime` timestamp not null default current_timestamp comment '创建时间'
);
create table if not exists `groups`
(
  `id` int not null primary key auto_increment comment '用户组id' ,
  `title` varchar (50) not null comment '用户组标题' ,
  `cretime` timestamp not null default current_timestamp comment '创建时间'
);
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 。

用于在多个表中查询相互匹配的数据.

?
1
2
3
4
5
6
7
8
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 。

内部连接。效果与 join 一样 , 但用法不同，join 使用 where ，inner join 使用 on .

?
1
2
3
4
5
6
7
8
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 填充.

?
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
```
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 用于合并两个或多个查询结果，合并的查询结果必须具有相同数量的列，并且列拥有形似的数据类型，同时列的顺序相同。
 
 
```sql
mysql> ( select `id`, `title` from `groups`) union ( select `id`, `title` from `orders`);
+ ----+--------------------------------------+
| id | title |
+ ----+--------------------------------------+
| 1 | 大佬 |
| 2 | 萌新 |
| 3 | 菜鸡 |
| 1 | 《大佬是怎样炼成的？》 |
| 2 | 《mysql 从萌新到删库跑路》 |
| 3 | 《菜鸡踩坑记》 |
+ ----+--------------------------------------+
```
 
6. 函数
 
6.1 语法
 
** select function **(* column *) ** from ** *table_name*
 
6.2 合计函数（aggregate functions）
 
合计函数的操作面向一系列的值，并返回一个单一的值。通常与 group by 语句一起用。
 
函数 描述 avg ( column ) 返回某列的平均值 count ( column ) 返回某列的行数（不包括 null 值） count (*) 返回被选行数 first ( column ) 返回在指定的域中第一个记录的值 last ( column ) 返回在指定的域中最后一个记录的值 max ( column ) 返回某列的最高值 min ( column ) 返回某列的最低值 sum ( column ) 返回某列的总和 6.3 标量函数（scalar functions）
 
函数 描述 ucase(c) 转换为大写 lcase(c) 转换为小写 mid(c, start[, end ]) 从文本提取字符 len(c) 返回文本长度 instr(c, char ) 返回在文本中指定字符的数值位置 left (c, number_of_char) 返回文本的左侧部分 right (c, number_of_char) 返回文本的右侧部分 round(c, decimals) 对数值指定小数位数四舍五入 mod(x, y) 取余（求模） now() 返回当前的系统日期 format(c, format) 格式化显示 datediff(d, date1, date2) 日期计算

以上就是程序员最实用的 sql 语句收藏看完这篇就够了的详细内容，更多关于程序员sql 语句的资料请关注我其它相关文章！ 。

原文链接：https://blog.csdn.net/Java_Caiyo/article/details/117172001 。

最后此篇关于程序员最实用的 SQL 语句收藏,看完这篇就够了的文章就讲到这里了,如果你想了解更多关于程序员最实用的 SQL 语句收藏,看完这篇就够了的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

25 4 0
文章推荐： oracle表分区的概念及操作
文章推荐： 详解将数据从Laravel传送到vue的四种方式
文章推荐： 漂亮的thinkphp 跳转页封装示例
文章推荐： Thinkphp页面跳转设置跳转等待时间的操作
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com