• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
慧眼识英雄1 显示全部楼层 发表于 2021-8-14 14:51:55 |阅读模式 打印 上一主题 下一主题
目次


  • 前言
  • 1. 创建

    • 1.1 创建数据库
    • 1.2 创建表格
    • 1.3 创建索引
    • 1.4 为已存在的列创建主键
    • 1.5 为已存在的列创建自增束缚

  • 2. 插入
  • 3. 修改

    • 3.1 修改数据记载
    • 3.2 修改数据库字符集为 utf8
    • 3.3 修改表字符集为 utf8
    • 3.4 修改表字段字符集为 utf8
    • 3.5 修改字段类型
    • 3.5 修改字段默认值
    • 3.6 修改字段表明

  • 4. 删除

    • 4.1 删除数据记载
    • 4.2 删除数据库
    • 4.3 删除表
    • 4.4 清空表中所有数据
    • 4.5 删除索引

  • 5. 查询

    • 5.1 语法
    • 5.2 单表查询
    • 5.3 多表关联查询



前言

文章沿着设计一个假想的应用 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. create table `users`
  2. (
  3. `id` int,
  4. `name` char(10),
  5. `avatar` varchar(300),
  6. `regtime` date
  7. )
复制代码
1.3 创建索引

语法:create index index_name on table_name (column_name)
示例:为用户 id 创建索引 idx_id
  1. create index `idx_id` on `users` (`id`)
  2. /* 创建唯一索引 */
  3. create unique index `idx_id` on `users` (`id`)
复制代码
1.4 为已存在的列创建主键
  1. 更常用的方式是在创建表语句所有列定义的后面添加一行 primary key (column_name)。
复制代码
语法:alter table table_name add primary key (column_name)
示例:将用户 id 设为主键
  1. alter table users add primary key (`id`)
复制代码
1.5 为已存在的列创建自增束缚
  1. 更常用的方式是在创建表语句中添加自增列 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. insert into `users` values (1, 'ken', 'http://cdn.awesome_app.com/path/to/xxx/avatar1.jpg', curdate())
  2. /* 指定列插入 */
  3. 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. update `users` set `regtime`=curdate() where `regtime` is null
  2. /* 一次修改多列 */
  3. 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. alter table `users` alter `regtime` set default '2019-10-12 00:00:00'
  2. /* 设置默认为当前时间 current_timestamp,需要重新定义整个列 */
  3. alter table `users` modify `regtime` datetime not null default current_timestamp
复制代码
3.6 修改字段表明
  1. alter table `users` modify `id` int not null auto_increment comment '用户ID';
  2. alter table `users` modify `name` char(10) comment '用户名';
  3. alter table `users` modify `avatar` varchar(300) comment '用户头像';
  4. alter table `users` modify `regtime` datetime not null default current_timestamp comment '注册时间';
复制代码
修改后,检察改动后的列:
  1. mysql> show full columns from users;
  2. +---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
  3. | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
  4. +---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
  5. | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | 用户ID |
  6. | name | char(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 用户名 |
  7. | avatar | varchar(300) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 用户头像 |
  8. | regtime | datetime | NULL | NO | | CURRENT_TIMESTAMP | | select,insert,update,references | 注册时间 |
  9. +---------+--------------+-----------------+------+-----+-------------------+----------------+---------------------------------+--------------+
复制代码
4. 删除


4.1 删除数据记载

语法:delete from table_name where condition
示例:删除用户名未填写的用户
  1. # 先增加一条用户名为空的用户
  2. mysql> insert into `users` (`regtime`) values (curdate());
  3. mysql> select * from users;
  4. +----+--------+----------------------------------------------------+------------+
  5. | id | name | avatar | regtime |
  6. +----+--------+----------------------------------------------------+------------+
  7. | 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 |
  8. | 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
  9. | 3 | NULL | NULL | 2019-10-12 |
  10. +----+--------+----------------------------------------------------+------------+
  11. # 删除用户名为空的行
  12. mysql> delete from `users` where `name` is null;
  13. mysql> select * from users;
  14. +----+--------+----------------------------------------------------+------------+
  15. | id | name | avatar | regtime |
  16. +----+--------+----------------------------------------------------+------------+
  17. | 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 |
  18. | 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 |
  19. +----+--------+----------------------------------------------------+------------+
复制代码
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. SELECT
  2. [ALL | DISTINCT | DISTINCTROW ]
  3. [HIGH_PRIORITY]
  4. [STRAIGHT_JOIN]
  5. [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  6. [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
  7. select_expr [, select_expr ...]
  8. [FROM table_references
  9. [PARTITION partition_list]
  10. [WHERE where_condition]
  11. [GROUP BY {col_name | expr | position}
  12. [ASC | DESC], ... [WITH ROLLUP]]
  13. [HAVING where_condition]
  14. [ORDER BY {col_name | expr | position}
  15. [ASC | DESC], ...]
  16. [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  17. [PROCEDURE procedure_name(argument_list)]
  18. [INTO OUTFILE 'file_name'
  19. [CHARACTER SET charset_name]
  20. export_options
  21. | INTO DUMPFILE 'file_name'
  22. | INTO var_name [, var_name]]
  23. [FOR UPDATE | LOCK IN SHARE MODE]]
复制代码
5.2 单表查询

5.2.1 预备数据:
  1. insert into users (`name`, `avatar`) values
  2. ('张三', 'http://cdn.awesome_app.com/path/to/xxx/3.jpg'),
  3. ('李四', 'http://cdn.awesome_app.com/path/to/xxx/4.jpg'),
  4. ('王五', 'http://cdn.awesome_app.com/path/to/xxx/5.jpg'),
  5. ('马六', 'http://cdn.awesome_app.com/path/to/xxx/6.jpg'),
  6. ('肖七', 'http://cdn.awesome_app.com/path/to/xxx/7.jpg'),
  7. ('刘八', 'http://cdn.awesome_app.com/path/to/xxx/8.jpg'),
  8. ('杨九', 'http://cdn.awesome_app.com/path/to/xxx/9.jpg'),
  9. ('郑十', 'http://cdn.awesome_app.com/path/to/xxx/10.jpg');
  10. /* 增加重复行 */
  11. insert into users (`name`, `avatar`) values
  12. ('张三', 'http://cdn.awesome_app.com/path/to/xxx/3.jpg'),
  13. ('李四', 'http://cdn.awesome_app.com/path/to/xxx/4.jpg'),
  14. ('王五', 'http://cdn.awesome_app.com/path/to/xxx/5.jpg');
复制代码
5.2.2 查询所有列
  1. mysql> select * from users;
  2. +----+--------+----------------------------------------------------+---------------------+
  3. | id | name | avatar | regtime |
  4. +----+--------+----------------------------------------------------+---------------------+
  5. | 1 | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg | 2019-10-12 00:00:00 |
  6. | 2 | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg | 2019-10-12 00:00:00 |
  7. | 3 | 张三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg | 2019-10-13 10:58:37 |
  8. | 4 | 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg | 2019-10-13 10:58:37 |
  9. | 5 | 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg | 2019-10-13 10:58:37 |
  10. | 6 | 马六 | http://cdn.awesome_app.com/path/to/xxx/6.jpg | 2019-10-13 10:58:37 |
  11. | 7 | 肖七 | http://cdn.awesome_app.com/path/to/xxx/7.jpg | 2019-10-13 10:58:37 |
  12. | 8 | 刘八 | http://cdn.awesome_app.com/path/to/xxx/8.jpg | 2019-10-13 10:58:37 |
  13. | 9 | 杨九 | http://cdn.awesome_app.com/path/to/xxx/9.jpg | 2019-10-13 10:58:37 |
  14. | 10 | 郑十 | http://cdn.awesome_app.com/path/to/xxx/10.jpg | 2019-10-13 10:58:37 |
  15. | 11 | 张三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg | 2019-10-13 11:20:17 |
  16. | 12 | 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg | 2019-10-13 11:20:17 |
  17. | 13 | 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg | 2019-10-13 11:20:17 |
  18. +----+--------+----------------------------------------------------+---------------------+
复制代码
5.2.3 查询指定列
  1. mysql> select id,name from users;
  2. +----+--------+
  3. | id | name |
  4. +----+--------+
  5. | 1 | steven |
  6. | 2 | bill |
  7. | 3 | 张三 |
  8. | 4 | 李四 |
  9. | 5 | 王五 |
  10. | 6 | 马六 |
  11. | 7 | 肖七 |
  12. | 8 | 刘八 |
  13. | 9 | 杨九 |
  14. | 10 | 郑十 |
  15. | 11 | 张三 |
  16. | 12 | 李四 |
  17. | 13 | 王五 |
  18. +----+--------+
复制代码
5.2.4 查询不重复记载
  1. mysql> select distinct name,avatar from users;
  2. +--------+----------------------------------------------------+
  3. | name | avatar |
  4. +--------+----------------------------------------------------+
  5. | steven | http://cdn.awesome_app.com/path/to/xxx/steven.jpg |
  6. | bill | http://cdn.awesome_app.com/path/to/xxx/avatar2.jpg |
  7. | 张三 | http://cdn.awesome_app.com/path/to/xxx/3.jpg |
  8. | 李四 | http://cdn.awesome_app.com/path/to/xxx/4.jpg |
  9. | 王五 | http://cdn.awesome_app.com/path/to/xxx/5.jpg |
  10. | 马六 | http://cdn.awesome_app.com/path/to/xxx/6.jpg |
  11. | 肖七 | http://cdn.awesome_app.com/path/to/xxx/7.jpg |
  12. | 刘八 | http://cdn.awesome_app.com/path/to/xxx/8.jpg |
  13. | 杨九 | http://cdn.awesome_app.com/path/to/xxx/9.jpg |
  14. | 郑十 | http://cdn.awesome_app.com/path/to/xxx/10.jpg |
  15. +--------+----------------------------------------------------+
复制代码
5.2.5 限定查询行数
查询前几行
  1. mysql> select id,name from users limit 2;
  2. +----+--------+
  3. | id | name |
  4. +----+--------+
  5. | 1 | steven |
  6. | 2 | bill |
  7. +----+--------+
复制代码
查询从指定偏移(第一举动偏移为0)开始的几行
  1. mysql> select id,name from users limit 2,3;
  2. +----+--------+
  3. | id | name |
  4. +----+--------+
  5. | 3 | 张三 |
  6. | 4 | 李四 |
  7. | 5 | 王五 |
  8. +----+--------+
复制代码
5.2.6 排序
  1. # 正序
  2. mysql> select distinct name from users order by name asc limit 3;
  3. +--------+
  4. | name |
  5. +--------+
  6. | bill |
  7. | steven |
  8. | 刘八 |
  9. +--------+
  10. # 倒序
  11. mysql> select id,name from users order by id desc limit 3;
  12. +----+--------+
  13. | id | name |
  14. +----+--------+
  15. | 13 | 王五 |
  16. | 12 | 李四 |
  17. | 11 | 张三 |
  18. +----+--------+
复制代码
5.2.7 分组
增加都会字段
  1. alter table `users` add `city` varchar(10) comment '用户所在城市' after `name`;
  2. update `users` set `city`='旧金山' where `id`=1;
  3. update `users` set `city`='西雅图' where `id`=2;
  4. update `users` set `city`='北京' where `id` in (3,5,7);
  5. update `users` set `city`='上海' where `id` in (4,6,8);
  6. update `users` set `city`='广州' where `id` between 9 and 10;
  7. update `users` set `city`='深圳' where `id` between 11 and 13;
复制代码
按都会分组统计用户数
  1. mysql> select city, count(name) as num_of_user from users group by city;
  2. +-----------+-------------+
  3. | city | num_of_user |
  4. +-----------+-------------+
  5. | 上海 | 3 |
  6. | 北京 | 3 |
  7. | 广州 | 2 |
  8. | 旧金山 | 1 |
  9. | 深圳 | 3 |
  10. | 西雅图 | 1 |
  11. +-----------+-------------+
  12. mysql> select city, count(name) as num_of_user from users group by city having num_of_user=1;
  13. +-----------+-------------+
  14. | city | num_of_user |
  15. +-----------+-------------+
  16. | 旧金山 | 1 |
  17. | 西雅图 | 1 |
  18. +-----------+-------------+
  19. mysql> select city, count(name) as num_of_user from users group by city having num_of_user>2;
  20. +--------+-------------+
  21. | city | num_of_user |
  22. +--------+-------------+
  23. | 上海 | 3 |
  24. | 北京 | 3 |
  25. | 深圳 | 3 |
  26. +--------+-------------+
复制代码
5.3 多表关联查询

5.3.1 预备数据
  1. create table if not exists `orders`
  2. (
  3. `id` int not null primary key auto_increment comment '订单ID',
  4. `title` varchar(50) not null comment '订单标题',
  5. `user_id` int not null comment '用户ID',
  6. `cretime` timestamp not null default current_timestamp comment '创建时间'
  7. );
  8. create table if not exists `groups`
  9. (
  10. `id` int not null primary key auto_increment comment '用户组ID',
  11. `title` varchar(50) not null comment '用户组标题',
  12. `cretime` timestamp not null default current_timestamp comment '创建时间'
  13. );
  14. alter table `users` add `group_id` int comment '用户分组' after `city`;
  15. insert into `groups` (`title`) values ('大佬'), ('萌新'), ('菜鸡');
  16. insert into `orders` (`title`, `user_id`) values ('《大佬是怎样炼成的?》', 3), ('《MySQL 从萌新到删库跑路》', 6), ('《菜鸡踩坑记》', 9);
  17. update `users` set `group_id`=1 where `id` between 1 and 2;
  18. update `users` set `group_id`=2 where `id` in (4, 6, 8, 10, 12);
  19. update `users` set `group_id`=3 where `id` in (3, 5, 13);
复制代码
5.3.2 join
join
用于在多个表中查询相互匹配的数据。
  1. mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users`, `orders` where `orders`.`user_id`=`users`.`id`;
  2. +-----------+--------------------------------------+
  3. | user_name | order_title |
  4. +-----------+--------------------------------------+
  5. | 张三 | 《大佬是怎样炼成的?》 |
  6. | 马六 | 《MySQL 从萌新到删库跑路》 |
  7. | 杨九 | 《菜鸡踩坑记》 |
  8. +-----------+--------------------------------------+
复制代码
inner join
内部毗连。结果与 join 一样 , 但用法不同,join 利用 where ,inner join 利用 on 。
  1. mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users` inner join `orders` on `orders`.`user_id`=`users`.`id`;
  2. +-----------+--------------------------------------+
  3. | user_name | order_title |
  4. +-----------+--------------------------------------+
  5. | 张三 | 《大佬是怎样炼成的?》 |
  6. | 马六 | 《MySQL 从萌新到删库跑路》 |
  7. | 杨九 | 《菜鸡踩坑记》 |
  8. +-----------+--------------------------------------+
复制代码
left join
左毗连。返回左表所有行,即使右表中没有匹配的行,不匹配的用 NULL 填充。
  1. ```
  2. mysql> select `users`.`name` as `user_name`, `orders`.`title` as `order_title` from `users` left join `orders` on `orders`.`user_id`=`users`.`id`;
  3. +-----------+--------------------------------------+
  4. | user_name | order_title |
  5. +-----------+--------------------------------------+
  6. | 张三 | 《大佬是怎样炼成的?》 |
  7. | 马六 | 《MySQL 从萌新到删库跑路》 |
  8. | 杨九 | 《菜鸡踩坑记》 |
  9. | steven | NULL |
  10. | bill | NULL |
  11. | 李四 | NULL |
  12. | 王五 | NULL |
  13. | 肖七 | NULL |
  14. | 刘八 | NULL |
  15. | 郑十 | NULL |
  16. | 张三 | NULL |
  17. | 李四 | NULL |
  18. | 王五 | NULL |
  19. +-----------+--------------------------------------+
  20. ```
  21. **right join**
  22. 右连接。和 left join 正好相反,会返回**右表**所有行,即使**左表**中没有匹配的行,不匹配的用 NULL 填充。
  23. ```sql
  24. mysql> select `groups`.`title` as `group_title`, `users`.`name` as `user_name` from `groups` right join `users` on `users`.`group_id`=`groups`.`id`;
  25. +-------------+-----------+
  26. | group_title | user_name |
  27. +-------------+-----------+
  28. | 大佬 | steven |
  29. | 大佬 | bill |
  30. | 萌新 | 李四 |
  31. | 萌新 | 马六 |
  32. | 萌新 | 刘八 |
  33. | 萌新 | 郑十 |
  34. | 萌新 | 李四 |
  35. | 菜鸡 | 张三 |
  36. | 菜鸡 | 王五 |
  37. | 菜鸡 | 王五 |
  38. | NULL | 肖七 |
  39. | NULL | 杨九 |
  40. | NULL | 张三 |
  41. +-------------+-----------+
  42. ```
  43. **5.3.3 union**
  44. union 用于合并两个或多个查询结果,合并的查询结果必须具有相同数量的列,并且列拥有形似的数据类型,同时列的顺序相同。
  45. ```sql
  46. mysql> (select `id`, `title` from `groups`) union (select `id`, `title` from `orders`);
  47. +----+--------------------------------------+
  48. | id | title |
  49. +----+--------------------------------------+
  50. | 1 | 大佬 |
  51. | 2 | 萌新 |
  52. | 3 | 菜鸡 |
  53. | 1 | 《大佬是怎样炼成的?》 |
  54. | 2 | 《MySQL 从萌新到删库跑路》 |
  55. | 3 | 《菜鸡踩坑记》 |
  56. +----+--------------------------------------+
  57. ```
  58. 6. 函数
  59. 6.1 语法
  60. **select function**(*column*) **from** *table_name*
  61. 6.2 合计函数(Aggregate functions)
  62. 合计函数的操作面向一系列的值,并返回一个单一的值。通常与 group by 语句一起用。
  63. 函数 描述 avg(column) 返回某列的平均值 count(column) 返回某列的行数(不包括 NULL 值) count(*) 返回被选行数 first(column) 返回在指定的域中第一个记录的值 last(column) 返回在指定的域中最后一个记录的值 max(column) 返回某列的最高值 min(column) 返回某列的最低值 sum(column) 返回某列的总和 6.3 标量函数(Scalar functions)
  64. 函数 描述 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 语句的资料请关注草根技能分享别的干系文章!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作