SQL 概述

SQL (Structured Query Language) 结构化查询语言，用于存取、查询、更新数据以及管理关系型数据库系统

SQL 发展

  • SQL 是在 1981 年由 IBM 公司推出，一经推出基于其简洁的语法在数据库中得到了广泛的应用，成为主流数据库的通用规范
  • SQL 由 ANSI 组织确定规范
  • 在不同的数据库产品中遵守 SQL 的通用规范，但是也对 SQL 有一些不同的改进，形成了一些数据库的专有指令
    • MySQ: limit
    • SQLServer: top
    • Oracle: rownum

SQL 分类

根据 SQL 指令完成的数据库操作的不同，可以将 SQL 指令分为四类：

  • DDL Data Definition Language 数据定义语言
    • 用于完成对数据库对象（数据库、数据表、视图、索引等）的创建、删除、修改
  • DML Data Manipulation Language 数据操作/操纵语言
    • 用于完成对数据表中的数据的添加、删除、修改操作
    • 添加：将数据存储到数据表
    • 删除：将数据从数据表移除
    • 修改：对数据表中的数据进行修改
  • DQL Data Query Language 数据查询语言
    • 用于将数据表中的数据查询出来
  • DCL Data Control Language 数据控制语言
    • 用于完成事务管理等控制性操作

字段约束

约束介绍

在创建数据表的时候，指定的对数据表的列的数据限制性的要求（对表的列中的数据进行限制）

为什么要给表中的列添加约束？

  • 保证数据的有效性
  • 保证数据的完整性
  • 保证数据的正确性

字段常见的约束有哪些？

  • 非空约束 (not null): 限制此列的值必须提供，不能为 null
  • 唯一约束 (unique): 在表中的多条数据，此列的值不能重复
  • 主键约束 (primary key): 非空 + 唯一，能够唯一标识数据表中的一条数据
  • 外键约束 (foreign key): 建立不同表之间的关联关系

非空约束

限制数据表中此列的值必须提供

创建表：设置图书表的 book_name not null

  1. create table books(
  2.  book_isbn char(4),
  3.  book_name varchar(10) not null,
  4.  book_author varchar(6)
  5. );

主键约束

主键——就是数据表中记录的唯一标识，在一张表中只能有一个主键（主键可以是一个列，也可以是多个列的组合）

当一个字段声明为主键之后，添加数据时：

  • 此字段数据不能为 null
  • 此字段数据不能重复

创建表时添加主键约束

  1. create table books(
  2.  book_isbn char(4) primary key,
  3.  book_name varchar(10) not null,
  4.  book_author varchar(6)
  5. );

或者

  1. create table books(
  2.  book_isbn char(4),
  3.  book_name varchar(10) not null,
  4.  book_author varchar(6),
  5.  primary key(book_isbn)
  6. );

删除表主键约束

  1. alter table books drop primary key;

创建表之后添加主键约束

  1. ## 创建表时没有添加主键约束
  2. create table books(
  3.  book_isbn char(4),
  4.  book_name varchar(10) not null,
  5.  book_author varchar(6)
  6. );
  8. ## 创建表之后添加主键约束
  9. alter table books modify book_isbn char(4) primary key;

主键自动增长

在创建一张数据表时，如果数据表中有列可以作为主键（例如：学生表的学号、图书表的 isbn）可以直接这是这个列为主键；

当有些数据表中没有合适的列作为主键时，可以额外定义一个与记录本身无关的列（ID）作为主键，此列数据无具体的含义主要⽤于标识一条记录，在 mysql 中可以将此列定义为 int，同时设置为自动增长，当向数据表中新增一条记录时，无需提供 ID 列的值，它会自动生成

定义主键自动增长

  • 定义 int 类型字段自动增长： auto_increment
  1. create table types(
  2.   type_id int primary key auto_increment,
  3.   type_name varchar(20) not null,
  4.   type_remark varchar(100)
  5. );

注意：自动增长从 1 开始，每添加一条记录，自动的增长的列会自定 + 1，当把某条记录删除之后再添加数据，自动增长的数据也不会重复生成（自动增长只保证唯一性、不保证连续性）

联合主键

联合组件——将数据表中的多列组合在⼀起设置为表的主键

定义联合主键

  1. create table grades(
  2.   stu_num char(8),
  3.   course_id int,
  4.   source int,
  5.   primary key(stu_num, course_id)
  6. );

注意：在实际企业项目的数据库设计中，联合主键使用频率并不高；当⼀个张数据表中没有明确的字段可以作为主键时，可以额外直接添加⼀个 ID 字段作为主键

外键约束

*在多表关联部分

DML 数据操纵语言

用于完成对数据表中数据的插入、删除、修改操作

  1. create table students(
  2.  stu_num char(8) primary key,
  3.  stu_name varchar(20) not null,
  4.  stu_gender char(2) not null,
  5.  stu_age int not null,
  6.  stu_tel char(11) not null unique,
  7.  stu_qq varchar(11) unique
  8. );

插入数据

语法

  1. insert into <tableName> (columnName, columnName...)
  2. values(value1, value2...);

示例

  1. ## 向数据表中指定的列添加数据（不允许为空的列必须提供数据）
  2. insert into stus(stu_num, stu_name, stu_gender, stu_age, stu_tel) 
  3. values('20210101', '张三', '男', 21, '13030303300');
  5. ## 数据表名后的字段名列表顺序可以不与表中⼀致，但是 values 中值的顺序必须与表名后字段名顺序对应
  6. insert into stus(stu_num, stu_name, stu_age, stu_tel, stu_gender)
  7. values('20210103', '王五', 20, '13030303302', '⼥');
  9. ## 当向表中的所有列添加数据时，数据表名后⾯的字段列表可以省略，但是 values 中的值的顺序要与数据表定义的字段保持⼀致;
  10. insert into stus values('20210105', '孙琦', '男', 21, '13030303304', '666666');
  12. ## 在项⽬开发中，即使要向所有列添加数据，也建议将列名的列表显式写出来(增强 SQL 的稳定性)
  13. insert into stus(stu_num,stu_name, stu_gender, stu_age, stu_tel, stu_qq)
  14. values('20210105', '孙琦', '男', 21, '13030303304', '666666');

删除数据

从数据表中删除满足特定条件（所有）的记录

语法

  1. delete from <tableName> [where conditions];

示例

  1. ## 删除学号为 20210102 的学⽣信息
  2. delete from stus where stu_num = '20210102';
  4. ## 删除年龄大于 20 岁的学⽣信息(如果满足 where ⼦句的记录有多条，则删除多条记录)
  5. delete from stus where stu_age > 20;
  7. ## 如果删除语句没有 where ⼦句，则表示删除当前数据表中的所有记录(敏感操作)
  8. delete from stus;

修改数据

对数据表中已经添加的记录进行修改

语法

  1. update <tableName> set columnName = value [where conditions]

示例

  1. ## 将学号为 20210105 的学⽣姓名修改为“孙七”（只修改⼀列）
  2. update stus set stu_name = '孙七'
  3. where stu_num = '20210105';
  5. ## 将学号为 20210103 的学⽣性别修改为“男”,同时将 QQ 修改为 777777（修改多列）
  6. update stus set stu_gender = '男', stu_qq = '777777'
  7. where stu_num = '20210103';
  9. ## 根据主键修改其他所有列
  10. update stus set stu_name = '韩梅梅', stu_gender = '⼥', stu_age=18, stu_tel = '13131313311', stu_qq = '999999'
  11. where stu_num = '20210102';
  13. ## 如果 update 语句没有 where ⼦句，则表示修改当前表中所有行（记录）
  14. update stus set stu_name = 'Tom';

DQL 数据查询语言

从数据表中提取满足特定条件的记录

  • 单表查询
  • 多表联合查询

查询基础语法

  1. ## select 关键字后指定要显示查询到的记录的哪些列
  2. select columnName1 [, columnName2, columnName3...] from <tableName> [where conditions];
  4. ## 如果要显示查询到的记录的所有列，则可以使⽤ * 替代字段名列表 （在项⽬开发中不建议使⽤ *）
  5. select * from stus;

WHERE 子句

在删除、修改及查询的语句后都可以添加 where 子句（条件），用于筛选满足特定的添加的数据进行删除、修改和查询操作

  1. delete from tableName where conditions;
  2. update tableName set ... where conditions;
  3. select columnName... from tableName where conditions;

条件关系运算符

  1. ## = 等于
  2. select * from stus where stu_num = '20210101';
  4. ## != <> 不等于
  5. select * from stus where stu_num != '20210101';
  6. select * from stus where stu_num <> '20210101';
  8. ## > 大于
  9. select * from stus where stu_age > 18;
  11. ## < 小于
  12. select * from stus where stu_age < 20;
  14. ## >= 大于等于
  15. select * from stus where stu_age >= 20;
  17. ## <= 小于等于
  18. select * from stus where stu_age <= 20;
  20. ## between and 区间查询 between v1 and v2 [v1,v2]
  21. select * from stus where stu_age between 18 and 20;

条件逻辑运算符

在 where 子句中，可以将多个条件通过逻辑预算 (and or not) 进行连接，通过多个条件来筛选要操作的数据

  1. ## and 并且 筛选多个条件同时满足的记录
  2. select * from stus where stu_gender = '⼥' and stu_age < 21;
  4. ## or 或者 筛选多个条件中⾄少满足⼀个条件的记录
  5. select * from stus where stu_gender = '⼥' or stu_age < 21;
  7. ## not 取反
  8. select * from stus where stu_age not between 18 and 20;

LIKE 子句

在 where 子句的条件中，可以使用 like 关键字来实现模糊查询

语法

  1. select * from tableName where columnName like 'reg';
  • 在 like 关键字后的 reg 表达式中
    • % 表示任意多个字符 【 %o% 包含字母 o】
    • _ 表示任意一个字符 【 _o% 第二个字母为 o】

示例

  1. # 查询学⽣姓名包含字⺟ o 的学⽣信息
  2. select * from stus where stu_name like '%o%';
  4. # 查询学⽣姓名第⼀个字为`张`的学⽣信息
  5. select * from stus where stu_name like '张%';
  7. # 查询学⽣姓名最后⼀个字⺟为 o 的学⽣信息
  8. select * from stus where stu_name like '%o';
  10. # 查询学⽣姓名中第⼆个字⺟为 o 的学⽣信息
  11. select * from stus where stu_name like '_o%';

对查询结果的处理

设置查询的列

声明显示查询结果的指定列

  1. select columnName1, columnName2,... from stus where stu_age > 20;

计算列

对从数据表中查询的记录的列进⾏⼀定的运算之后显示出来

  1. ## 出⽣年份 = 当前年份 - 年龄
  2. select stu_name, 2021 - stu_age from stus;
  4. +-----------+----------------+
  5. | stu_name  | 2021 - stu_age |
  6. +-----------+----------------+
  7. | 张三      |           2000 |
  8. | 韩梅梅    |           2003 |
  9. | 王五      |           2001 |
  10. | Tom       |           1988 |
  11. | 孙七      |           2000 |
  12. | Piolicy   |           2009 |
  13. +-----------+----------------+

as 字段取别名

可以为查询结果的列名取⼀个语义性更强的别名 (as 关键字也可省略)

  1. select stu_name, 2021 - stu_age as stu_birth_year from stus;
  3. +-----------+----------------+
  4. | stu_name  | stu_birth_year |
  5. +-----------+----------------+
  6. | 张三      |           2000 |
  7. | 韩梅梅    |           2003 |
  8. | 王五      |           2001 |
  9. | Tom       |           1988 |
  10. | 孙七      |           2000 |
  11. | Piolicy   |           2009 |
  12. +-----------+----------------+

distinct 消除重复行

从查询的结果中将重复的记录消除 distinct

  1. select stu_age from stus;
  2. +---------+
  3. | stu_age |
  4. +---------+
  5. |      21 |
  6. |      18 |
  7. |      20 |
  8. |      33 |
  9. |      21 |
  10. |      12 |
  11. +---------+
  13. select distinct stu_age from stus;
  14. +---------+
  15. | stu_age |
  16. +---------+
  17. |      21 |
  18. |      18 |
  19. |      20 |
  20. |      33 |
  21. |      12 |
  22. +---------+

排序 - order by

将查询到的满足条件的记录按照指定的列的值升序/降序排列

语法

  1. select * from tableName where conditions order by columnName asc|desc;
  • order by columnName 表示将查询结果按照指定的列排序
    • asc 按照指定的列升序（默认）
    • desc 按照指定的列降序

实例

  1. ## 单字段排序
  2. select * from stus where stu_age > 15 order by stu_gender desc;
  3. +----------+-----------+------------+---------+-------------+--------+
  4. | stu_num  | stu_name  | stu_gender | stu_age | stu_tel     | stu_qq |
  5. +----------+-----------+------------+---------+-------------+--------+
  6. | 20210101 | 张三      | 男         |      21 | 13030303300 | NULL   |
  7. | 20210103 | 王五      | 男         |      20 | 13030303302 | 777777 |
  8. | 20210104 | Tom       | 男         |      33 | 13030030220 | 333433 |
  9. | 20210105 | 孙七      | 男         |      21 | 13030303304 | 666666 |
  10. | 20210102 | 韩梅梅    | ⼥         |      18 | 13131313311 | 999999 |
  11. +----------+-----------+------------+---------+-------------+--------+
  13. ## 多字段排序：先满足第⼀个排序规则，当第⼀个排序的列的值相同时再按照第⼆个列的规则排序
  14. select * from stus where stu_age > 15 order by stu_gender asc, stu_age desc;
  15. +----------+-----------+------------+---------+-------------+--------+
  16. | stu_num  | stu_name  | stu_gender | stu_age | stu_tel     | stu_qq |
  17. +----------+-----------+------------+---------+-------------+--------+
  18. | 20210102 | 韩梅梅    | ⼥         |      18 | 13131313311 | 999999 |
  19. | 20210104 | Tom       | 男         |      33 | 13030030220 | 333433 |
  20. | 20210101 | 张三      | 男         |      21 | 13030303300 | NULL   |
  21. | 20210105 | 孙七      | 男         |      21 | 13030303304 | 666666 |
  22. | 20210103 | 王五      | 男         |      20 | 13030303302 | 777777 |
  23. +----------+-----------+------------+---------+-------------+--------+

聚合函数

SQL 中提供了⼀些可以对查询的记录的列进行计算的函数——聚合函数

  • count
  • max
  • min
  • sum
  • avg
  • count() 统计函数，统计满足条件的指定字段值的个数（记录数）
  1. ## 统计学⽣表中学⽣总数
  2. select count(stu_num) from stus;
  3. +----------------+
  4. | count(stu_num) |
  5. +----------------+
  6. |              6 |
  7. +----------------+
  9. ## 统计学⽣表中性别为男的学⽣总数
  10. select count(stu_num) from stus where stu_gender = '男';
  11. +----------------+
  12. | count(stu_num) |
  13. +----------------+
  14. |              4 |
  15. +----------------+
  • max() 计算最大值，查询满足条件的记录中指定列的最大值
  1. select max(stu_age) from stus;
  2. +--------------+
  3. | max(stu_age) |
  4. +--------------+
  5. |           33 |
  6. +--------------+
  8. select max(stu_age) from stus where stu_gender = '⼥';
  9. +--------------+
  10. | max(stu_age) |
  11. +--------------+
  12. |           18 |
  13. +--------------+
  • min() 计算最小值，查询满足条件的记录中指定列的最小值
  1. select min(stu_age) from stus;
  2. +--------------+
  3. | min(stu_age) |
  4. +--------------+
  5. |           12 |
  6. +--------------+
  8. select min(stu_age) from stus where stu_gender = '⼥';
  9. +--------------+
  10. | min(stu_age) |
  11. +--------------+
  12. |           12 |
  13. +--------------+
  • sum() 计算和，查询满足条件的记录中指定的列的值的总和
  1. ## 计算所有学⽣年龄的总和
  2. select sum(stu_age) from stus;
  3. +--------------+
  4. | sum(stu_age) |
  5. +--------------+
  6. |          125 |
  7. +--------------+
  9. ## 计算所有性别为男的学⽣的年龄的总和
  10. select sum(stu_age) from stus where stu_gender = '男';
  11. +--------------+
  12. | sum(stu_age) |
  13. +--------------+
  14. |           95 |
  15. +--------------+
  • avg() 求平均值，查询满足条件的记录中 计算指定列的平均值
  1. select avg(stu_age) from stus;
  2. +--------------+
  3. | avg(stu_age) |
  4. +--------------+
  5. |      20.8333 |
  6. +--------------+
  8. select avg(stu_age) from stus where stu_gender = '男';
  9. +--------------+
  10. | avg(stu_age) |
  11. +--------------+
  12. |      23.7500 |
  13. +--------------+

日期函数和字符串函数

  • 日期函数

当向日期类型的列添加数据时，可以通过字符串类型赋值（字符串的格式必须为yyyy-MM-dd hh:mm:ss）

如果我们想要获取当前系统时间添加到日期类型的列，可以使⽤ now() 或者 sysdate()

示例

  1. ## 通过字符串类型给⽇期类型的列赋值
  2. alter table stus add stu_entrance datetime;
  3. insert into stus(stu_num, stu_name, stu_gender, stu_age, stu_tel, stu_qq, stu_entrance)
  4. values('20210107', '七七', '女', 20, '13333333333', '12333333', '2021-10-25 22:34:28');
  6. ## 通过 now() 获取当前时间
  7. insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_entrance)
  8. values('20210109','张⼩四','⼥',20,'13434343355','1233333',now());
  10. ## 通过 sysdate() 获取当前时间
  11. insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_entrance)
  12. values('20210110', '李雷', '男', 16, '13434343366', '123333344', sysdate());
  14. ## 通过 now() 和 sysdate() 获取当前系统时间
  15. select now();
  16. +---------------------+
  17. | now()               |
  18. +---------------------+
  19. | 2021-10-25 22:43:51 |
  20. +---------------------+
  22. select sysdate();
  23. +---------------------+
  24. | sysdate()           |
  25. +---------------------+
  26. | 2021-10-25 22:44:12 |
  27. +---------------------+
  • 字符串函数

就是通过 SQL 指令对字符串进行处理

示例

  1. ## concat(column1, column2, ...) 拼接多列
  2. select concat(stu_name,'-',stu_gender) from stus;
  3. +---------------------------------+
  4. | concat(stu_name,'-',stu_gender) |
  5. +---------------------------------+
  6. | 张三-男                         |
  7. | 韩梅梅-⼥                       |
  8. | 王五-男                         |
  9. | Tom-男                          |
  10. | 孙七-男                         |
  11. | Policy-女                      |
  12. | 七七-女                         |
  13. +---------------------------------+
  15. ## upper(column) 将字段的值转换成⼤写
  16. select upper(stu_name) from stus;
  17. +-----------------+
  18. | upper(stu_name) |
  19. +-----------------+
  20. | 张三            |
  21. | 韩梅梅          |
  22. | 王五            |
  23. | TOM             |
  24. | 孙七            |
  25. | POLICY         |
  26. | 七七            |
  27. +-----------------+
  29. ## lower(column) 将指定列的值转换成⼩写
  30. select lower(stu_name) from stus;
  31. +-----------------+
  32. | lower(stu_name) |
  33. +-----------------+
  34. | 张三            |
  35. | 韩梅梅          |
  36. | 王五            |
  37. | tom             |
  38. | 孙七            |
  39. | policy         |
  40. | 七七            |
  41. +-----------------+
  43. ## substring(column, start, len) 从指定列中截取部分显示 （start 从 1 开始，后数 lun 个字符）
  44. select stu_name, substring(stu_tel, 8, 4) from stus;
  45. +-----------+--------------------------+
  46. | stu_name  | substring(stu_tel, 8, 4) |
  47. +-----------+--------------------------+
  48. | 张三      | 3300                     |
  49. | 韩梅梅    | 3311                     |
  50. | 王五      | 3302                     |
  51. | Tom       | 0220                     |
  52. | 孙七      | 3304                     |
  53. | Policy   | 3789                     |
  54. | 七七      | 3333                     |
  55. +-----------+--------------------------+

分组查询 - group by

分组——就是将数据表中的记录按照指定的列进行分组

语法

  1. select 分组字段/聚合函数
  2. from 表名
  3. [where 条件]
  4. group by 分组列名 [having 条件]
  5. [order by 排序字段]
  • select 后使用 * 显示对查询的结果进行分组之后，显示每组的第一条记录（这种显示通常是无意义的）
  • select 后通常显示分组字段和聚合函数 (对分组后的数据进行统计、求和、平均值等)
  • 语句执行属性:
    1. 先根据 where 条件从数据库查询记录
    2. group by 对查询记录进行分组
    3. 执行 having 对分组后的数据进行筛选

示例

  1. # 先对查询的学生信息按性别进行分组（分成了男、⼥两组），然后再分别统计每组学生的个数
  2. select stu_gender, count(stu_num) from stus group by stu_gender;
  3. +------------+----------------+
  4. | stu_gender | count(stu_num) |
  5. +------------+----------------+
  6. | 男         |              4 |
  7. | ⼥         |              3 |
  8. +------------+----------------+
  10. # 先对查询的学生信息按性别进行分组（分成了男、⼥两组），然后再计算每组的平均年龄
  11. select stu_gender, avg(stu_age) from stus group by stu_gender;
  12. +------------+--------------+
  13. | stu_gender | avg(stu_age) |
  14. +------------+--------------+
  15. | 男         |      23.7500 |
  16. | ⼥         |      16.6667 |
  17. +------------+--------------+
  19. # 先对学生按年龄进行分组（分了16、17、18、20、21、22六组），然后统计各组的学生数量，还可以对最终的结果排序
  20. select stu_age, count(stu_num) from stus group by stu_age order by stu_age;
  21. +---------+----------------+
  22. | stu_age | count(stu_num) |
  23. +---------+----------------+
  24. |      12 |              1 |
  25. |      18 |              1 |
  26. |      20 |              2 |
  27. |      21 |              2 |
  28. |      33 |              1 |
  29. +---------+----------------+
  31. # 查询所有学生，按年龄进行分组，然后分别统计每组的⼈数，再筛选当前组⼈数 > 1 的组，再按年龄升序显示出来
  32. select stu_age, count(stu_num) from stus
  33. group by stu_age having count(stu_num) > 1
  34. order by stu_age;
  35. +---------+----------------+
  36. | stu_age | count(stu_num) |
  37. +---------+----------------+
  38. |      20 |              2 |
  39. |      21 |              2 |
  40. +---------+----------------+
  42. # 查询性别为'男'的学生，按年龄进行分组，然后分别统计每组的⼈数，再筛选当前组⼈数 > 1 的组，再按年龄升序显示出来
  43. select stu_age, count(stu_num) from stus
  44. where stu_gender = '男'
  45. group by stu_age having count(stu_num) > 1
  46. order by stu_age;
  47. +---------+----------------+
  48. | stu_age | count(stu_num) |
  49. +---------+----------------+
  50. |      21 |              2 |
  51. +---------+----------------+

分页查询 - limit

当数据表中的记录比较多的时候，如果⼀次性全部查询出来显示给用户，用户的可读性/体验性就不太好，因此可以将这些数据分页进行展示

语法

  1. select ...
  2. from ... 
  3. where ...
  4. limit param1, param2
  • param1 int, 表示获取查询语句的结果中的第一条数据的索引（索引从 0 开始）
  • param2 int, 表示获取的查询记录的条数（如果剩下的数据条数少于 param2，则返回剩下的所有记录）

示例

对数据表中的学生信息进行分页显示，总共有 10 条数据，每页显示 3 条

总记录数 count 10
每页显示 pageSize 3
总页数 pageCount = count % pageSize==0 ? count / pageSize : count / pageSize +1;

  1. # 查询第⼀⻚：
  2. select * from stus [where ...] limit 0, 3;   (1 - 1) * 3
  4. # 查询第⼆⻚：
  5. select * from stus [where ...] limit 3, 3;   (2 - 1) * 3
  7. # 查询第三⻚：
  8. select * from stus [where ...] limit 6, 3;   (3 - 1) * 3
  10. # 如果在⼀张数据表中：
  11. # pageNum   查询的⻚码
  12. # pageSize  每⻚显示的条数
  13. # 通⽤分⻚语句如下：
  14. select * from <tableName> [where ...] limit (pageNum - 1) * pageSize, pageSize;