SQL学习前言 From 一些名词数据库：可以理解为一个文件夹，存放各类文件表：数据库文件夹里的文件，更像是ex

前言

From

SQL是一门和数据对话的语言，你可以用SQL查询语句找到你想要的数据
比如你想找到：

社区今天发了多少条动态？

2021年每月的销售额有多少？

最近一个月每天有多少活跃用户？

一些名词

数据库： 可以理解为一个文件夹，存放各类文件

表：数据库文件夹里的文件，更像是excel文件，下图就是一个表名为“student”的表

字段： 表里的字段，可以理解为excel里的表头，上图里的SId、Sname、Sage、Ssex就是表里的字段名

数据库管理系统： 为了控制数据库里的数据，进行增删改查操作，我们需要用到数据库管理系统，数据库管理系统有MySQL、Oracle等

学习思路

1. 准备

找课程

SQL教程 | 菜鸟教程
 SQL教程 - 廖雪峰
工具书：《SQL必知必会》

找工具

在线工具一
 在线工具二

2. 开始学习

在写SQL语句前，需要先了解：

对应的数据库表结构
了解每个字段的含义

然后开始学习SQL语句，包括：单表查询、关联查询

3. 实战上手

LeetCode
牛客网
 经典SQL题目

数据库概念

数据库是按照数据结构来组织、存储和管理数据的仓库。

数据库类型

数据库可以分成两种类型，分别是关系型数据库和非关系型数据库。

关系型数据库： 是基于关系模型的数据库，使用一系列的数据表来表达数据和数据之间的关系，一般叫做SQL。
主流的关系型数据库有： mysql、oracle、sql server

非关系型数据库： 是一种相对松散且可以不按照严格结构规范进行存储的数据库，它一般存储在数据集中，比如文档、键值对、图结构。一般叫做NOSQL。
主流的非关系型数据库有： mongodb、couchDB

数据库常用术语

数据库： 关联表的集合，类比excel表格里面的n个sheet。

表： n行数据记录组成一张表，类比excel表格里面的一个sheet。

行： n列数据组成一行，类比excel表格里面的一行数据。

列：具有相同数据类型的数据集合，类比excel表格里面的一列。

字段： 字段指的是列名，即表示这类数据的名字。比如t_user表的name。

主键： 指的是用来唯一标识这一行数据的字段，比如t_user表的id可以唯一标识每个学生。可以通过唯一主键来快速查询数据。主键不是必须的。

自增主键： 现在很多表里面用的主键都是自增主键，它是数据库自带的功能，只要指定了自增，那么每一行记录的主键字段将从1开始递增。

外健： 用来关联2张表的字段。

索引： 数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

数据库字段

在表中使用属性来表示某一类数据，属性也叫字段。

比如t_user表里面的id、name、sex就是字段。一个表里面的字段名不能重复，而且只能用英文、下划线表示。

字段还有自己的数据类型，类型有整型、字符型、布尔型、日期型等，如果在存储时没有按照自己的数据类型规范存储，数据库就会报错。比如t_user表里面的name字段是字符型，但此时如果存入了一个日期型的数据是错误的。

数据库查询语句

单表查询

单表查询是指只从一张表中查找数据，不需要关联多张表的查询。

SQL查询的基本结构由三个子句构成：select、from和where。

select 字段1,字段2,字段3 from 表名 where 条件

select 命令后面跟着要查询的字段名，如果想查询表的所有字段名，可以用 * 表示。需要注意的是select 如果有多个字段，字段之间需要用英文逗号分隔。
from 命令跟着的是表名
where 后面跟着的是查询条件，查询条件可以很简单，也可以嵌套。

举例

假如有这样一张学生信息的表：t_user

1. 找出所有学生的姓名

select name from t_user

学生的姓名字段是name，因此我们应该把name字段放在select 中，表名是t_user

查询结果：

2. 找出性别是女性的所有学生信息

select * from t_user where sex='女'

所有学生信息需要展示所有的字段，可以使用 * 号代替，查询条件是性别女

查询结果：

3. 找出所有的专业名

select distinct major from t_user

因为一个专业有多名学生，所以查询出的专业名称不止一个。为了去除重复，我们可以在select后面加上关键词 distinct。这样就可以去除重复的记录了

查询结果：

4. 找出学费增长1.1倍后的学费结果

select name,tuition*1.1  from t_user

select 子句还可以带有+、-、* 、/ 运算符的算术表达式。运算对象可以是常数或字段名

5. 找出专业是软件工程且性别是女性的学生信息

select * from t_user where major='软件工程' and sex='女'

where 子句允许使用逻辑连词and 、or、not

查询结果：

6. 比如找出年龄小于19岁的学生

select * from t_user where age<19

where 子句还支持比较运算符 < 、> 、<=、>=、=

查询结果：

7. 更名运算

语法： old-name as new-name

select name as "姓名" from t_user

select 的字段名可以更改，这样在查询结果中显示的就是更改后的属性名。
表名也可以更改，更改表名在多表查询时非常有用

查询结果：

8. 字符串运算

name like '张%'  //表示以张开头的姓名
name like '%张'  //表示以张结尾的姓名
name like '%张%' //表示字符串中存在张字的姓名

SQL使用一对英文单引号或双引号来表示字符串。比如name="张三"。
字符串是大小写敏感的，比如"Comp" 和 "comp"是不相等的。
字符串还可以使用like关键字来模糊匹配

9. 排序

select * from t_user order by age //表示按照年龄升序
select * from t_user order by age asc //表示按照年龄升序
select * from t_user order by age desc //表示按照年龄降序
select * from t_user order by age desc,name asc //表示按照年龄降序且按姓名升序。排到前面的字段，优先级越高。

SQL提供了order by 关键字来排序。order by 语句是默认使用升序

聚集查询

基本聚集

聚集函数是以值的一个集合为输入、返回单个值的函数。

五个固有聚集函数：

平均值：avg
最小值：min
最大值：max
总和：sum
计数：count

PS： sum和avg的输入必须为数字类型，其他的函数输入可以是其他数据类型，比如字符串。

举例

假设有这样一张学生信息表t_user：

还有一张课程信息表t_user_course：

1. 找出专业是软件工程和计算机专业的平均学费

select avg(tuition) 
from t_user 
where major = "软件工程" or major = "计算机"

学生的姓名字段是name，因此我们应该把name字段放在select 中，表名是t_user

查询结果：

2. 找出2019秋季上过高等数学的学生人数

select count(distinct user_id) as total
from t_user_course 
where season='秋季' and year='2019'

在计算平均值时保留重复元组是很重要的。有些情况下在计算聚集函数时需要先删除掉重复元组。可用关键词distinct删除掉重复的元组。一个学生无论在2019秋季上了多少次高等数学，都应该只计算一次。

查询结果：

分组聚集

有时候我们希望聚集函数作用到一组元组集上。那么就可以用到分组聚集。

SQL提供了group by 子句，group by 子句中的所有属性取值相同的元组被分在一个组里。

语法： group by 属性1,属性2,属性3

举例： 找出每个专业的学生人数

select count(id) as total,major
from t_user
group by major

该句子的查询过程是这样的：
（1）先按照group by来分组，具有相同专业的学生被分成一个组，如下图：注意看下相同专业的id=2和id=4，被分到了同一组。

（2）然后在每个小组里面，统计人数。查询结果：

PS：
需要保证出现在select语句中但没有被聚集的属性只能出现在group by 子句中的那些属性，否则查询是错误的。

举例： id不应该出现在select中，因为id没有被分组

select count(id) as total,major,id
from t_user
group by major

having 子句

如果想对分组再加点限定条件，比如找出每个专业人数超过2人的学生人数。那么可以使用having子句

having子句是对分组后的结果再限定条件

select count(id) as total,major
from t_user
group by major
having total > 2

嵌套子查询

子查询是嵌套在另一个查询中的select-from-where表达式。任何select-from-where表达式的返回结果都是一个关系，因此可以被插入到另一个select-from-where中任何关系可以出现的位置。

where 嵌套

in 和 not in

这里介绍一个新的关键词 in 和 not in。表示元组是否是集合中的成员。

假设有一张课程信息表：t_course：

举例： 找出2019年秋季和2020年秋季同时开课的所有课程

select distinct id
from t_course
where season="秋季" and year="2020" and id
in (select id  
    from t_course
    where season="秋季" and year="2019"
);

我们先找出2020年秋季开课的所有课程，然后需要从子查询中找出那些同时在2019年秋季开课课程。

from 嵌套

举例： 找出每个专业人数超过2人的学生人数（效果同having子句查询一致）

select total,major
from (  
    select count(id) as total,major  
    from t_user  
    group by major  
)
where total > 2

from子句查询出来的结果就像一个新的中间表，select的字段名必须来自from子查询结果表的字段名

连接运算

如果我们需要从多个表的组合匹配关系中得出结果，那么就需要使用连接运算。

内连接

语法：join {表名} on {表1字段} = {表2字段}

join后面跟着的是需要连接的表名，on后面跟着的是两个表关联起来的条件

举例

假设有2张表，分别是t_student学生信息表和t_course课程表

t_student：

t_course：

找出所有学生选修了的课程信息

以前的写法：

select * from t_student,t_course
where t_student.course_id=t_course.id

使用内连接的写法：

select * from t_student join t_course on 
t_student.course_id=t_course.id;

查询结果：

join..on 操作符把2个表在条件属性下取值相同的元组搜出来。

需要注意的是，学生表中陈红这名学生没有选修任何课程，所以她的记录没有出现在结果表中，内连接会把2个关系没有匹配的元组 “丢失” 掉。

我们来看下查询结果中字段的排序，首先出现的是第一个表中的所有字段，紧接着的才是第二个表中的所有字段。

外连接

外连接还分为三种子类型：

左外连接(left outer join)：只保留出现在连接符左边表的元组。
右外连接(right outer join): 只保留出现在连接符右边表的元组。
全外连接(full outer join)：保留出现在2个表中的元组。

左外连接

举例：找出所有学生选修的课程情况

select * from t_student 
left outer join t_course on t_student.course_id=t_course.id

查询结果：

可以看到结果中陈红这名同学是没有选修任何课程的，但是也出现在了结果表中。

左外连接会以左边表格元组为主，如果是没有找到匹配关系的元组，则左边表格的字段继续保留，右边表格的字段以 null 来填充。

右外连接

举例：找出所有课程的选修情况

select * from t_student 
right join t_course on t_student.course_id=t_course.id;

查询结果：

可以看出算法和政治这2门课是没有学生选修的。

右外连接会以右边表格元组为主，如果是没有找到匹配关系的元组，则右边表格的字段继续保留，左边表格的字段以 null 来填充。

全外连接

全外连接是左外连接和右外连接的组合。

如果想找出所有学生以及他们选修的所有课程列表，则就可以使用全外连接。

但是需要注意的是Mysql中不支持全外连接的语法。我们可以使用 union 操作符代替。

(
select * from t_student  
left join t_course on t_student.course_id=t_course.id
) 
union
(select * from t_student 
right join t_course on t_student.course_id=t_course.id
)

查询结果：

可以看到没有选修的课程和没有选修课程的学生信息都出现了。

注意： 外连接可以省略outer这个关键字，直接写left...join...、right...join...就可以啦。

表设计

数据库约束与常见操作

主键

概述

关系表都有一个约束：每一行记录必须要有某个字段来唯一标识，能唯一标记记录的字段，称为主键。

假设有一张学生表：t_student

id name class_id mobile
1 张三 2 130xxx
2 李四 4 140xxx
3 陈红 3 150xxx

id	name	class_id	mobile
1	张三	2	130xxx
2	李四	4	140xxx
3	陈红	3	150xxx

上面这张表，id是主键，我们可以通过id来区分出每一个同学：id=1是张三，id=2是李四，id=3是陈红

一个表必须要有主键。我们可以在建表的时候，用primary key标识。

create table t_student ( id int, name varchar(10), primary key (id));

主键字段的选择

当表已经开始使用并录入数据后，最好不要再做更改了，因为表的主键可能已经在其他表里用做外键关联或者已经在业务逻辑中使用。

所以定义主键最好不要使用业务字段，业务字段发生变更的概率比较大，比如学生的手机号码、学生的身份证都是可能发生变化的。

我们可以使用默认的自增字段来做主键，比如student表的id。
也可以使用基于时间和空间生成的uuid来做主键，uuid一般是业务逻辑里面生成来动态插入数据表。

联合主键

主键可以使用多个字段来标记。但是不太建议使用。这样在以后处理表关系或者业务逻辑时，会增加复杂度。

外键

假设有一张学生表：t_student

id name class_id mobile
1 张三 2 130xxx
2 李四 4 140xxx
3 陈红 3 150xxx

还有一张班级表：t_class

id class_name
1 1班
2 2班
3 3班
4 4班

我们怎么识别张三属于哪个班级，李四属于哪个班级呢？

这时我们可以使用外键。

id	name	class_id	mobile
1	张三	2	130xxx
2	李四	4	140xxx
3	陈红	3	150xxx

id	class_name
1	1班
2	2班
3	3班
4	4班

（1）在t_student表增加一列叫class_id来表示班级id。

（2）我们通过关系：t_student.class_id=t_class.id可以看出，张三在1班，李四在4班，陈红在3班。

（3）通过外键我们可以看出，这2张表是一个一对多的关系。这里的“多”指的是学生表，“一”指的是班级表，含义是一个学生只可以对应一个班级，但是一个班级可以对应多个学生。

那如果是多对多的关系该如何实现呢？

假设有一张学生选修的课程表：t_course

id course_name
1 高等数学
2 物理
3 政治
4 算法

一个学生可选修多门课程，一个课程也可以被多名学生选择学习。这是一个多对多的关系。

一般会通过一个中间表来实现。

id	course_name
1	高等数学
2	物理
3	政治
4	算法

我们建立一个中间表叫做：t_student_course

id	course_id	student_id
1	1	1
1	1	2
2	2	3
2	2	4
3	3	1
4	4	2

表里一般要存储2个表的主键，如上的course_id和student_id。从表中我们就可以看出，学生id=1的张三选修了两门课程，分别是数学和政治。学生id=2的李四也选修了两门课程，分别是数据和算法。

还剩下一种表关系是1对1的关系

假设还有一张学生信息明细表，存储的是学生更详细的信息的表：t_student_detail

id student_id address age
1 1 深圳南山男
2 2 深圳福田男
3 3 深圳龙岗女

id	student_id	address	age
1	1	深圳南山	男
2	2	深圳福田	男
3	3	深圳龙岗	女

我们可以看出，一个学生对应一个详细信息。但是如果t_student_detail表里面只有一个字段或者2个字段的话，根据业务情况，也可以把这些字段纳入到t_student表中。

索引

索引按用途可以分为2种，一种是用于提升查询速度的查询索引，另一种是约束唯一性作用的唯一索引。

查询索引

如果t_student表中，name是经常要查询的字段，那我们可以给name创建一个索引。

ALTER TABLE t_student ADD INDEX idx_name (name)

唯一索引

唯一索引表示的是字段的值是唯一的。

比如学生的手机号码，身份证这些业务字段，都是唯一的，如果给这些字段加上唯一索引约束，那么当你往学生表里插入重复的手机号码或者身份证时，数据库就会报错阻止你的操作，起到保护数据唯一性的作用。

1. 对单个字段添加唯一索引

例如：mobile字段

ALTER TABLE t_student
ADD UNIQUE INDEX uni_mobile (mobile);

2. 对多个字段添加联合唯一索引

例如：mobile和card_id字段

ALTER TABLE t_student
ADD UNIQUE INDEX uni_mobile_card (mobile,card_id);

默认值

我们可以给字段设置默认值，但是默认值跟数据类型必须是匹配的。比如你是一个int整型，不能设置一个字符串类型的值。

举例： 设置时间字段的默认值为当前时间戳

create table t_student ( create_time timestamp default current_timestamp );

NOT NULL 约束

null不是数据类型，它是列的一个属性。

null表示的是空，如果你不允许你的字段值为空，则添加not null约束

举例： 约束学生表的名字不能为空

create table t_student (name VARCHAR(22) NOT NULL)

建表规范

一般建表都要遵守三范式原则。

第一范式

要求有主键，并且要求每一个字段都遵守原子性不可再分。

第二范式

满足第一范式的前提下，要求所有非主键字段完全依赖主键，不能产生部分依赖。

举例： 假设有一张学生表：t_student

学生编号老师编号学生姓名老师姓名
s1001 t1001 张三叶老师
s1002 t1001 李四叶老师
s1003 t1003 陈红李老师

学生编号	老师编号	学生姓名	老师姓名
s1001	t1001	张三	叶老师
s1002	t1001	李四	叶老师
s1003	t1003	陈红	李老师

这个表以学生编号和老师编号为联合主键。

该表会出现大量的冗余，冗余字段为“学生姓名”和“教师姓名”，出现冗余主要是学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部分依赖了主键的一个字段教师编号，这就是第二范式部分依赖。

所以这张表是不合理的，学生和老师之间的关系应该是多对多的关系，我们应该通过建立3张表来实现，分别是

t_student（学生表）
t_teacher（老师表）
t_student_teacher（学生老师中间表）

第三范式

满足第二范式的前提下，所有非主键字段和主键字段之间不能产生传递依赖。

某个字段依赖于主键，而有其他字段依赖于该字段。这就是传递依赖。

修改数据

新增记录

我们可以通过 insert 语句给数据表插入一条记录。

语法： INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

也可以同时插入多条记录：

语法：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES

(值1, 值2, ...),

(值1, 值2,...),

(值1, 值2,...);

需要注意的是，插入的字段顺序可以跟表的字段顺序不一致，但是字段和值的顺序必须是要一一对应的。

如果表的主键是自增主键（如id），那插入数据时可以不用写主键字段，数据库会自动计算。
如果字段有默认值，比如常见的创建时间字段“create_time”，经常用的默认值是当前时间，也可以不用写该字段，数据库会自动填充。

举例： 假设有一张学生表：t_student

id name mobile score
1 张三 130xxx 80
2 李四 140xxx 78
3 陈红 150xxx 90

id	name	mobile	score
1	张三	130xxx	80
2	李四	140xxx	78
3	陈红	150xxx	90

我插入一条数据：

INSERT INTO t_student (name, mobile) VALUES ('王麻子', '160xxx');

再执行 select * from t_student就会发现t_student多了一条记录。

修改记录

我们可以通过update语句来更新表数据。

语法： UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

注意： 更新语句后面可以有个where的筛选条件，如果不加筛选条件，则会更新整张表的数据。

SET语句后面也可以是表达式

举例： 设置所有学生的分数增加10分

UPDATE student SET score=score+10

举例： 把张三的手机号码修改为"190xxx"

UPDATE student SET mobile='190xxx' WHERE id=1

删除记录

我们可以通过DELETE语句来删除表数据

语法： delete from <表名> where ...;

注意： 删除语句后面可以有个where的筛选条件，如果不加筛选条件，则会删除整张表的数据。

举例： 删除掉id=3的记录

delete from t_studen where id=3

但实际业务当中，删除一条记录不会真的把这条记录移除，一般会使用一个状态status来表示数据是否可用。这样的话，这些数据以后就可以追溯。这种删除叫“软删除”。

比如最常见的删除订单，系统不会真的把订单记录删掉，而是把状态置为0（不可用）

删除表

我们可以通过drop语句来删除整张表，这时表和数据都会被删除。

语法： drop table <表名>;

举例： 删除学生表

drop table t_student