SQL语言
DDL语句 数据库定义语言
create 创建数据库/表
alter 修改表
drop 删除库/表
DML语言，数据操作语言，对表中数据的操作
insert 插入/新增
delete 删除
update 更新/改
1 插入/增加
关键词：isnert
插入的值和列必须保持一一对应的关系。
格式一，如下：
insert into 表名
(字段1,字段2,……)
values
(值1,值2,……)
格式二，如下：
insert into 表名
values
(值1,值2,……)
格式三，一次插入多条记录，如下：
insert into 表名
values
(值1,值2,……),
(值1,值2,……),
(值1,值2,……),
(值1,值2,……)
2 删除
关键词：delete
delete VS drop
delete删除表中的数据，表依然还在，但是，drop会删除表的结构和数据。
格式一，把整个表中的数据全部删除如下：
delete from 表名
格式二，与where配合使用，删除表中某种记录，如下：
delete from 表名 where ……
and 条件是同时满足的，如下：
delete from 表名 where 条件1 and 条件2 and 条件3 ……
or 满足其中之一条件即可，如下：
delete from 表名 where 条件1 or 条件2 or 条件3 ……
删除操作应注意外键约束，若想删除主表的数据，必须先删除从表的记录，才可以删除主表的记录。
3 改/更新
关键词：update
修改表中的数据
格式一，如下：
update 表名 set 字段=值
格式二，更改多个字段，如下：
update 表名 set 字段1=值1,字段2=值2,……
格式三，有条件的更新，如下：
update 表名 set 字段1=值1 where 条件
4 查询
所谓查询，就是对已经存在于数据库中的数据按特定的组合、条件或次序进行检索。查询功能是数据库最基本也是最重要的功能。
格式如下：
select 字段 from 表名
select子句=select 字段 作用：查询什么
from子句=from 表名 作用：从哪里查询
针对查询语句的题目，做题思路：1 先确定表 2 看查询哪个字段
查询多个字段格式如下：
select 字段1,字段2,字段3,…… from 表名
4.1 distinct查询
查询结果中删除重复行，就可以在SELECT子句中使用DISTINCT关键字。
格式如下：
select distinct 列名 from 表名
查询stuinfo表学生来自哪里，语句如下：
-- 建stuinfo表
CREATE TABLE stuinfo(
sno VARCHAR(10),
cno VARCHAR(10),
sname VARCHAR(10),
ssex VARCHAR(2),
place VARCHAR(10)
)ENGINE=INNODB DEFAULT CHARSET=utf8
SELECT * FROM stuinfo
-- 查询stuinfo表学生来自哪里
SELECT DISTINCT(place) FROM stuinfo
4.2 别名查询
使用select语句查询数据时，可使用别名的方法根据需要对数据显示的标题进行修改。
格式如下：
select 列名1 as 别名1/'别名1', 列名2 as 别名2/'别名2',……from 表名
-- as可省
计算列，在进行数据查询时，经常需要对查询的数据进行再次计算。
在grade表，将每个人的成绩加10分，语句如下：
-- 创建grade表
CREATE TABLE grade(
sno VARCHAR(10),
cno VARCHAR(10),
grade DECIMAL(5,2)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 为grade表插入数据
INSERT INTO grade
VALUES
('1','c01',88.2),
('2','c01',44.5),
('3','c02',66.4),
('4','c03',77.3),
('5','c02',55.6),
('6','c01',80.9),
('7','c01',33.1)
-- 将每个人的成绩加10分
SELECT grade+10 AS '加分之后' FROM grade
4.3 条件查询
where子句用来限定表中数据的查询
格式如下：
select 字段 from 表名 where条件
比较运算符
逻辑运算符
and 链接多个条件，必须同时满足的
or 链接多个条件，这些条件满足其中一个即可
格式如下：
select 字段 from 表名 where 条件1 and 条件2 and 条件3 ……
select 字段 from 表名 where 条件1 or 条件2 or 条件3 ……
范围搜索条件
针对查询结果满足某个范围内的数据
在某个范围之内：between …and…
格式如下：
select 字段 from 表名 where 字段 not between 开始值 and 结束值
开始值和结束值都参与到查询中，[开始值,结束值]
列表搜索条件
关键词：in
通过in加上条件可以和列表中任意值匹配，只要满足其中一个即可
格式如下：
select 字段 from 表名 where 字段 in(值1,值2,……)
不在这个列表范围之内的 not in
格式如下：
select 字段 from 表名 where 字段 not in(值1,值2,……)
4.4 模糊查询
字符匹配符
关键词：like
% 代表的是零个或多个字符
_ 代表的是一个字符
not like匹配不要某个字符模式
4.5 空值查询
关键词：null
判断为空的格式如下：
select * from 表名 where 字段 is null
判断不为空的格式如下：
select * from 表名 where 字段 is not null
查询sc表中没有成绩的同学，语句如下：
-- 创建sc表
CREATE TABLE sc(
sno VARCHAR(10),
cno VARCHAR(10),
grade INT,
category VARCHAR(5)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 为sc插入数据
INSERT INTO sc
VALUES
('1','c01',80,'选修'),
('2','c01',44,'必修'),
('3','c02',99,'必修'),
('4','c03',70,'必修'),
('5','c02',60,'选修'),
('6','c01',60,'选修')
INSERT INTO sc
(sno,cno,category)
VALUES
('8','c02','必修')
-- 查询sc表中没有成绩的同学
SELECT * FROM sc WHERE grade IS NULL
4.6 聚合函数
sum(字段) 对某个字段内的数据进行求和
avg(字段) 对某个字段内的数据进行求平均值
max(字段) 对某个字段求最大值
min(字段) 对某个字段求最小值
count(*) 统计表有多少条记录/行
count(字段) 统计某个字段有多少个值，字段中的null不参与计算
格式如下：
select 聚合函数 from表名
题目：
查询课程号c01学生的总数，语句如下：
SELECT COUNT(*) c01总人数 FROM sc WHERE cno='c01'
统计grade字段值的数量，语句如下：
SELECT COUNT(grade) 总人数 FROM sc
4.7 查看结果行数的限定
查询第n行到第m行的内容
关键词：limit
limit的范围(n,m]
格式如下：
select * from 表名 limit [指定开始查询的行,] 查询的总行数
题目：
查询student表，从第2行开始查询5行记录，语句如下：
select * from student limit 1,5
4.8 分组查询
关键词：group by
group by常于聚合函数配合使用，对查询结果进行分组。
格式如下：
select 聚合函数 from 表名 group by 字段
分组查询的思想：先查询结果，即先进行聚合函数的运算，统计出相关的总数；然后，将该结果进行分组。
题目：统计每个年级的学生总人数和班级总数，语句如下：
SELECT classrank,SUM(peoplenum) 人数,COUNT(*) 班级数
FROM class
GROUP BY classrank
做题思路：先统计出全校的总人数，后进行分班（分组）。
4.9 having
having必须与group by子句一起使用。相当于一个用于组的where子句，制定组的搜索条件。
having子句可以包含聚合函数，但where不可以使用聚合函数。
使用思想：对分组之后的结果再筛选
格式如下：
select 字段 from 表名 group by 字段 having 条件
having后面的条件一般情况下都是聚合函数。
题目：
grade(sno,cno,grade)
查看grade表每个学生的总成绩，输入成绩大于200，语句如下：
select sno,sum(grade)>200 总成绩
from grade
group by sno
having sum(grade)>200
sc(sno,cno,grade,category)
查看选课（sc）总数大于等于2门课的学生的学号，语句如下：
select sno,count(cno) 选课数量
from sc
group by sno
having count(cno)>=2
4.10 排序
关键词：order by
格式如下：
select * from 表名 order by 字段1 ,字段2 desc,……
-- 默认情况下升序asc，降序desc
5 MySQL函数
MySQL函数仅在MySQL中使用。
5.1 字符串函数
length返回字符串的字节长度
MySQL中一个汉字占3个字节，一个字母占1个字节。
char_length返回字符串的字符长度
题目：计算student表中sedpt字段内容的字节长度和字符长度，语句如下：
-- 创建student表
CREATE TABLE student(
sno VARCHAR(10),
cno VARCHAR(10),
sname VARCHAR(10),
ssex varchar(2),
sage int,
sdept varchar(15)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 计算student表中sedpt字段内容的字节长度和字符长度
SELECT sdept,LENGTH(sdept) 字节长度,CHAR_LENGTH(sdept) 字符长度
FROM student GROUP BY sdept
mid(字符串,开始位置,截取长度)
题目：截取student表中sdept字段，从第一个字符开始，截取3个字符，语句如下：
SELECT sdept,MID(sdept,1,3) 截取后的字符串 FROM student
5.2 数学函数
round(X,N)
作用：四舍五入
x为数值，N为保留几位小数，若不写N，则取整。X，N都是具体数值，不是字段。
avg聚合函数 默认保留6位小数（MySQL版本不一样保留个数有所不同）
题目：用round()对3.1415926四舍五入保留两位小数，并且对它四舍五入取整，语句如下：
SELECT ROUND(3.1415926,2) 保留两位小数,ROUND(3.9415926) 四舍五入取整
题目：使用avg()对grade表的学生成绩求平均值
首先查看garde表，语句如下：
-- 查看garde表
SELECT * FROM grade
再进行计算，语句如下：
select avg(grade) from grade
least(x.y,z,……)
求最小的数字
参数为具体的数字
greatest(x.y,z,……)
求最小的数字
参数为具体的数字
SELECT LEAST(1,2,344,5555,66,123)
SELECT GREATEST(1,2,344,5555,66,123)
5.3 日期时间函数
now() 获取日期时间，语法如下：
SELECT NOW()
current_date() 获取日期，语法格式如下：
SELECT CURRENT_DATE()
current_time() 获取时间，语法格式如下：
SELECT CURRENT_TIME()
to_days(日期值) 将日期转换成总天数，例子如下：
SELECT TO_DAYS('2022-02-22')
dayofyear(日期值) 求该年已过的天数，例子如下：
SELECT DAYOFYEAR('2022-02-22')
week() 求该年已过的周数，例子如下：
SELECT WEEK('2022-02-22')
5.4 控制函数
if(布尔表达式,参数1,参数2) 布尔表达式成立，输出参数1,；否则输出参数2。例子如下：
select if(2>1,'True','False')
if(null,参数1,参数2) 没有布尔表达式，直接输出参数2。例子如下：
SELECT IF(NULL,'Bass','Top')
ifnull(参数1,参数2) 参数1不为空，直接输出参数1。例子如下：
SELECT IFNULL('Bass','Top')
ifnull(null,参数2) 参数1为空，直接输出参数2。例子如下：
SELECT IFNULL(NULL,'Top')
6 表连接
需要的数据不在一张表中，需要多张表结合查询，用表连接来实现。
6.1 内连接（常用）
特点：只关联表与表中能匹配的数据信息，才能有对应的查询结果（交集）。
格式一，通过where来进行的关联，如下：
select 表名1.字段1,表名2.字段1,……
from 表名1,表名2,……
where 表名1.字段1=表名2.字段1 and ……
格式二，通过join来进行关联，如下：
select 表名1.字段1,表名2.字段1,……
from 表名1 [inner]join 表名2
on 表名1.字段1=表名2.字段1
where 额外条件
注意：
1 以上字段1为共同有的字段，该字段名可以不一样，但是该字段数据类型和内容必须一样！
2 查询的这个列，两个表中都有，那么在写的过程中，列的前面一定要加表名；如果查询的这个列，只属于一个表中的，那就可以直接写列名。
题目：查询学生的学号，姓名，性别，成绩。
sc(sno,cno,grade)
student(sno,sname,ssex,sage,sdept)
查询语句如下：
-- 格式一
SELECT a.sno,sname,ssex,grade
FROM student a,sc b
WHERE a.sno=b.sno `在这里插入代码片`
-- 格式二
SELECT a.sno,sname,ssex,grade
FROM student a JOIN sc b
ON a.sno=b.sno
题目：查询学号以1开头学生的姓名，性别，班级名称和年级。
sc(sno,cno,grade)
class(classno,classname,classrank,people)
stuinfo(sno,cno,sname,ssex)
classno字段和cno数据类型和内容一样
查询语句如下：
SELECT a.sno,b.sname,b.ssex,c.classname,c.classrank
FROM stuinfo a,student b,class c
WHERE a.sno=b.sno AND b.cno=c.classno
AND a.sno LIKE '1%'
分析：sno在sc表和studnet表上都有，sname，ssex在stuinfo表上，classname和classrank在class表上，sc表和student表公共字段sno，sc表和class表公共字段“cno”。
多表连接
多表连接属于内连接。
格式一，where如下：
select 表名.字段
from 表名1,表名2,表名3
where 表名1.字段=表名2.字段 and 表名2.字段=表名3.字段
格式二，on如下：
select
from 表名1 inner join 表名2
on 表名1.字段=表名2.字段 join 表名3
on 表名2.字段=表名3.字段
思想：把表1和表2连接起来形成一张大表，再与表3连接。
题目：查询学生的学号，姓名，成绩，课程名
course(cno,cname,ceredit,semester)
sc(sno,cno,grade,category)
student(sno,sname,ssex,sage,sdept)
select c.sno,c.sname,b.grade,a.cname
from course a,sc b,student c
where a.cno=b.cno and b.sno=c.sno
select c.sno,c.sname,b.grade,a.cname
from course a join sc b
on a.cno=b.cno join student c
on c.sno=b.sno
6.2 外链接
特点：至少会返回出一个表的所有内容
分类：左外链接、右外连接
左外链接
返回左表所有行
关键词：left outer join
格式如下：
select 表名1.字段1,表名2.字段1,……
from 表名1 left [outer] join 表名2
on 表名1.字段1=表名2.字段1
where 额外条件
注：在 from之后，先写的表是左表，后写是右表。
左外链接后，会返回左表的所有内容，如果在右表中没有匹配到，在对应位置上就显示为null。
题目：查询所有学生的学号，姓名，课程编号，成绩
sc(sno,cno,grade,category)
student(sno,cno,sname,ssex,sage,sdept)
语句如下：
select a.sno,sname,b.cno,grade
from student a left join sc b
on a.sno=b.sno
右外连接：返回右表所有行
select a.sno,sname,b.cno,grade
from student a right join sc b
on a.sno=b.sno
题目：使用右外连接查询所有学生的学号，姓名，课程编号，成绩
sc(sno,cno,grade,category)
student(sno,cno,sname,ssex,sage,sdept)
语句如下：
select a.sno,sname,b.cno,grade
from sc b right join student b
on a.sno=b.sno
左外连接转换为右外连接：1 修改关键词；2 左右两张表颠倒
7 子查询
查询语句里面嵌套查询语句，该嵌套语句放在where子句里。
格式如下：
-- 两条查询语句
select 字段 from 表1 where 表1和表2共同有的字段 in
(select 表1和表2共同有的字段 from 表2 where 条件)
-- 三条查询语句
select 字段 from 表1 where 表1和表2共同有的字段 in
(select 表1和表2共同有的字段 from 表2 where 表2和表3共同有的字段 in
(select 表2和表3共同有的字段 from 表3 where 条件))
注：
where后的不能直接写聚合函数，借助查询语句。
例如：查询成绩大于平均成绩，如下：
where grade>(select avg(grade) from sc)
-- 作用等价于where grade>avg(grade)，
-- 但是where后不能有聚合函数，所以借助查询语句
分类：嵌套子查询、相关子查询
嵌套子查询
子查询中还包含其他的子查询
select(select(select()))
题目：查询计算机工程系学生选修了哪些课程。
sc(sno,cno,grade,category)
student(sno,cno,sname,ssex,sage,sdept)
语句如下：
select cno from sc where sno in
(select sno from student where sdept='计算机工程系')
思路：题目要求查课程，课程是sc表的cno内容，所以外层查询是查询sc表，此外还要求查询计算机工程系的学生，student表有sdept，student表和sc表的共同有的字段为sno。
题目：查询修了’c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。
sc(sno,cno,grade,category)
student(sno,cno,sname,ssex,sage,sdept)
语句如下：
select grade,sno from sc where grade >
(SELECT AVG(grade) FROM sc WHERE cno='c02')
思路：
select——查sno和grade
from——确定表sc
where——条件：修了co2这门课；高于c02这门课程平均分
相关子查询
子查询的结果有且只有一个值。
题目：查询和张三同一个班的学生的信息（学号，姓名，籍贯，班级名称，年级）
class(classno,classname,classrank,people)
stuinfo(sno,cno,sname,ssex,place)
cno和classno数据类型和内容相同
SELECT sno,cno,sname,place,classrank
FROM class a,stuinfo b
WHERE a.classno=b.cno AND
cno=(SELECT cno FROM stuinfo
WHERE sname='张三')
思路：
需要我查的是：select——sno,sname,place在stuinfo表；classname,classrank在class表，因此需要内连接。
from——class表和stuinfo表
where——和张三同班的人，在class表上做查询
calss表和stuinfo表共有的字段为“cno”（在class表为classno，在stuinfo表为cno，虽然字段名不同但是数据类型和内容相同）
8 insert、update、delete与select共同使用
8.1 insert与select共同使用
作用：查询出的结果插入到表中
题目：将stuinfo表籍贯为北京的学生插入到stu_1表中，语句如下：
-- 新建stu_1表，结构与stuinfo表一致
CREATE TABLE stu_1(
sno VARCHAR(10),
cno VARCHAR(10),
sname VARCHAR(10),
ssex VARCHAR(2),
place VARCHAR(10)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 将stuinfo表籍贯为北京的学生插入到stu_1
INSERT INTO stu_1 SELECT * FROM stuinfo WHERE place='北京'
-- 查看stu_1表中的数据
SELECT * FROM stu_1
8.2 update与select共同使用
格式如下：
update 表名
set 字段
where 字段 in(SELECT 语句)
题目：将计算机工程系全体学生的成绩加5分
grade(sno,cno,grade)
student(sno,cno,sname,ssex,sage,sdept)
update grade
set grade=grade+5
where sno in
(SELECT sno FROM student WHERE sdept='计算机工程系')
思路：
grade在grade表中，而sdept在student表中，所以需要sno连接两张表。
8.3 delete与select共同使用
格式如下：
delete from 表名 where 字段 in(select 查询语句)
题目：删除计算机工程系的成绩
语句如下：
delete from grade where sno in
(select sno from student where sdept='计算机工程系')
9 视图
9.1 视图的定义
视图是根据某个表查询出来生成的虚表。
作用：方便用户对数据的操作。将多站表合成一张虚表，一次性修改数据。
视图是虚表，对表的增删改查操作，视图同样成立。
9.2 创建视图
格式如下：
create view 视图名 as select 语句
注意：进行增删改查操作，注意外键约束
9.3 修改视图
格式如下：
alter view 视图名 as select 查询语句
修改视图中数据格式如下：
update 视图命
set 字段=值
where 条件
9.4 删除视图
格式如下：
-- 删除整个视图
drop view 视图名
-- 删除视图中某条记录
drop view 视图名 where 条件
-- 删除多个视图
drop view 视图1,视图2,……
题目：
创建class表的视图，视图名为v_class；
查看视图；
并将二年级的课程号改为c02；
向视图插入班级编号为c03，班级名称为三（3）班，年级为三年级，人数为30；
删除v_class视图人数字段；
删除刚刚插入的数据；
class(classno,classname,classrank,peoplenum)
语句如下：
-- 创建视图
create view v_class as select * from class
-- 查看视图
SELECT * FROM v_class
-- 将二年级的课程号改为c02
UPDATE v_class
SET classno='c02'
WHERE classrank='二年级'
-- 插入数据
insert into v_class
values
('c03','三（3）班','三年级',30)
-- 修改视图结构
alter view v_class as
select classno,classname,classrank from class
-- 删除视图数据
drop from v_class where classno='c03'
小结：
视图是从表得到的，所以对视图数据进行增删改查操作对原表是有影响的。
修改视图的结构不会影响原表的结构。
删除视图，不会删除原表。
视图创建后，对原表插入数据，视图也有影响。
10 索引
10.1 是什么
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：数据结构
简单理解为：排好序的快速查找数据结构
作用：排序 查找
结论：数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方向指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
我们平时所说的索引，如果没有特别指明，都是指B树索引。其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引和唯一索引默认都是使用B树索引，统称索引。当然，除了B树这种类型的索引之外，还有哈稀索引（hash index）。
优势
类似图书馆建书目索引，提高数据检索 的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。
10.2 索引分类
单值索引。即一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引。索引列的值必须唯一，单允许有空值。
复合索引。即一个索引包含多个列。
基本语法
创建
create [unique] index 索引名 on 表名(字段)
alter 表名 add [unique] index [索引名] on(字段)
删除
drop index [索引名] on 表名
查看
show index from 表名
使用alter
添加一个主键，这以为这索引只必须是唯一的，且不能为null，如下：
alter table 表名 add primary
创建唯一索引，格式如下：
alter table 表名 add unique 索引名(字段)
添加普通索引，索引值出现多次，格式如下：
alter 表名 add index 索引名(字段)
创建全文索引，如下：
alter 表名 add fulltext 索引名(字段)
10.3 MySQL索引结构
BTree索引
Tree索引检索原理：
【初始化介绍】
一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示)，如磁盘块1包含数据项17和35，包含指针P1、P2、P3。
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO)可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
真实的情况是，3层的B树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，都么总共需要百万次的IO，显然成本非常非常高。
Hash索引（了解）
full-text全文索引（了解）
R-tree索引（了解）
10.4 哪些情况需要创建索引
1 主键自动建立唯一索引。
2 频繁作为查询查询条件的字段应该创建索引。
3 查询中与其他表关联的字段，外键关系建立索引。
4 查询中排序的字段，排序字段若通过索引访问将大大提高排序速度
5 查询中统计或分组的字段
10.5 哪些情况不需要创建索引
1 表记录太少。
2 经常增删改的表。提高了查询的速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
3 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意如果某个数据列包含许多重复的内容，为该字段创建索引就没有太大实际效果。