本博客练习数据获取网站：https://www.db-book.com/
MySQL 启动
以管理员启动 cmd，输入 net start mysql80 启动服务，输入 net stop mysql80 关闭服务
在启动服务之后，找到 MySQL 8.0 Command Line Client ，输入密码即可访问数据库
MySQL IDE —— Workbench
基础教学
单条运行快捷键：选中单条语句，CTRL + enter
格式化：选中代码块 CTRL + B
SQL 不区分大小写，注意句尾一定加分号
SQL shell 中使用向上箭头复制刚才输入的命令
MySQL 语法
DDL （Data Definition Language）
数据定义语言，用于定义数据库对象
查询所有数据库（注意一定分号结尾
show databases;
查询当前正在使用的数据库
select database();
创建数据库 （中括号中为可选内容
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
删除数据库
drop database [if exists] 数据库名;
使用数据库
use 数据库名;
数据库中的表操作
查询当前数据库中所有的表
show tables;
查询表结构
desc 表名;
创建表
create table 表名(
字段1 字段1类型 comment 字段1 注释,
字段2 字段2类型 comment 字段2 注释,
...
字段n 字段n类型 comment 字段n 注释
) comment 注释;
例子：
create table sheet1(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄'
)comment '用户表';
查询指定表的建表语句
show create table 表名;
添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
例子：
alter table sheet1 add nickname varchar(10) comment '昵称';
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 数据类型(长度) [comment 注释] [约束];
删除字段
alter table 表名 drop 字段名;
修改表名
alter table 表名 rename to 新表名;
删除表
drop table [if exists] 表名;
删除表，并重新创建表（等价于清除表内所有数据）
truncate table 表名;
DML（Data Manipulation Language）
数据操作与语言，用于增删改
向表中插入数据，注意列名和 values 的对应关系
insert into 表名 (字段名1, 字段名2, ...) values(值1, 值2, ...);
例：
insert into test (id, name, age, username) values(1, "jerome", 20, 'zjl');
向表中全部字段插入数据
insert into 表名 values(值1, 值2, ...);
例：
insert into test values(1, "jerome", 20, 'zjl');
Add a new tuple to student with tot_creds set to null
insert into student
values (’3003’, ’Green’, ’Finance’, null);
批量插入用逗号分隔每一行
例：
insert into test values(1, "jerome", 20, 'zjl'), (2, "raymond", 21, 'R');
加 select
Add all instructors to the student relation with tot_creds set to
0
insert into student
select ID, name, dept_name, 0
from instructor
修改数据（注，若安全模式报错：解决方案）
update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];
例：
update test set name = 'Jerome' where id = 1;
修改整列：
update test set age = 30;
Increase salaries of instructors whose salary is over
$100,000 by 3%, and all others by a 5%
update instructor
set salary = salary * 1.03
where salary > 100000;
update instructor
set salary = salary * 1.05
where salary <= 100000;
case 版本：
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end
Recompute and update tot_creds value for all students
update student S
set tot_cred = (
select sum(credits)
from takes, course
where takes.course_id = course.course_id and
S.ID= takes.ID.and
takes.grade <> 'F' and
takes.grade is not null);
删除数据
delete from 表名 [where 条件];
例：
delete from test where gender = male;
Delete all tuples in the instructor relation for those instructors
associated with a department located in the Watson building.
delete from instructor
where dept name in (select dept name
from department
where building = ’Watson’);
Delete all instructors whose salary is less than the average
salary of instructors
delete from instructor
where salary < (select avg (salary)
from instructor);
删除所有数据：
delete from test;
DQL（Data Query Language）
数据查询语言，用于查询数据库中表的记录
DQL 语句的编写顺序
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
DQL 语句的执行顺序
执行顺序影响了变量别名的使用顺序
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
select
字段列表
order by
排序字段列表
limit
分页参数
查询多个字段
select 字段1,字段2, ... from 表名;
查询所有字段
select * from 表名;
例：
select id,name from student;
设置别名
select 字段1 as 别名1, 字段2 as 别名2, ... from 表名;
例：
select name as "姓名" from student;
as 可以省略：
select name "姓名" from student;
去重查询
select distinct 字段列表 from 表名;
例：
select distinct dept_name "课程" from student;
条件查询 where
select 字段列表 from 表名 where 条件列表;
多种条件的案例：
普通条件
select * from student where name = 'Zhang';
select ID,tot_cred from student where name = 'Zhang';
不等关系
select * from student where tot_cred <= 60;
select * from student where tot_cred != 60;
查询空值
select * from student where tot_cred is null;
查询非空值
select * from student where tot_cred is not null;
区间查找
select * from student where tot_cred > 50 and tot_cred <= 60;
select * from student where tot_cred between 50 and 60;
注：between and 既包含最大也包含最小
多条件查找
select * from student where dept_name = "Physics" and tot_cred <= 60;
使用元组进行比较
select *
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');
等价于
where instructor.ID = teaches.ID, dept_name = 'Biology';
或关系查找
select * from student where dept_name = "Physics" or dept_name = "Music";
select * from student where dept_name in("Physics","Music");
模糊查询 like
匹配七个字长的名字，like 后加七个下划线
select * from student where dept_name like '_______';
匹配最后一位 / 最后几位：查询以 Sci. 结尾的课程名，%代表匹配前面所有字符
select * from student where dept_name like '%Sci.';
匹配名字中含有 da 的 instructor
select name
from instructor
where name like '%da%'
使用“\”转义
select *
from sheet
where score like '100 \%' escape '\'
常见聚合函数。用于对列进行操作
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
语法：
select 聚合函数(字段列表) from 表名;
注：所有的 null 值不参与聚合函数计算
例：
select count(*) from student;
select avg(tot_cred) from student;
select max(tot_cred) from student;
select min(tot_cred) from student;
select sum(tot_cred) from student where dept_name = "Comp. Sci.";
找出在 Spring 2010 semester 教课的 instructor 总数
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010;
注意，以 ID 为统计对象，并注意去重
分组查询
语法：
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
先满足 where 条件，再满足 having 条件；where 中不能使用聚合函数，having 中可以
例：
按照课程名称统计数量
select dept_name, count(*)
from student
group by dept_name;
按照课程名称分组，统计平均得分
select dept_name, avg(tot_cred)
from student
group by dept_name;
查询总分小于 60 的学生，按照课程名分组，获取选课人数等于 3 的课程
select dept_name, count(*)
from student
where tot_cred < 60
group by dept_name
having count(*) = 3;
注：分组查询后返回一般为聚合函数或分组字段。如果查询单个值是没有意义的，只显示第一个值
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
先按字段1排，相同再按字段2排序
asc 表示升序，desc 表示降序
select * from student order by tot_cred asc;
select * from student order by tot_cred desc;
先按照总分进行升序排序，若总分相同，再按照学号进行降序排序
select * from student order by tot_cred asc, ID desc;
分页查询
select 字段列表 from 表名 limit 起始索引, 查询记录数;
起始索引的计算公式：（当前页码数-1）x 每页记录数
起始索引从 0 开始
例：
查询第一页，每页 6 条记录
select * from student limit 0, 6;
可省略作：
select * from student limit 6;
查询第二页六条记录：
select * from student limit 6, 6;
查询第二页六条记录：
select * from student limit 12, 6;
注 执行多个条件的时候，可以把每个条件加括号，看起来更清晰
嵌套查询
Find courses offered in Fall 2009 but not in Spring 2010
select distinct course_id
from section
where semester = 'Fall' and year= 2009 and
course_id not in (
select course_id
from section
where semester = 'Spring' and year= 2010
);
多关系表查询
由于要查询的数据在不同关系表中，所以要从多张表中查询数据
一对多 / 多对一的关系
案例：部门 与 员工的关系，一个部门对应多个员工
实现：在多的一方建立外键，指向一的一方的主键
多对多的关系
案例：学生 与 选修课的关系，一个学生对应多个课程，一个课程也可以对应多个学生
实现：建立第三张中间表，至少包含两个外键，分别关联两方的主键
一对一的关系
案例：用户 与 用户详情的关系，将基础字段和详情字段拆分，提高查询效率
实现：在任意一张表加入外键，关联另一方主键
例：
查找教艺术的所有教师姓名和课程 ID
select name, course_id
from instructor , teaches
where instructor.ID = teaches.ID and instructor. dept_name = 'Art';
注
select name, course_id from instructor , teaches；这条语句将 instructor 和 teaches 表做笛卡尔积，是没有进行主键自动匹配等操作的，即 但看这个表是没有任何意义的。而后面的 where 语句条件限制，从 笛卡尔积得到的结果的一张大表 中 筛选出有意义的行数。
from 子句中包括多个关系表时，一定要在 where 子句中加入连接条件！过滤掉无效的笛卡尔积（两个集合连接时每个元素会分别组合，但是只有键相同的组合是有用的）
实际应用中，对频繁执行的SQL查询，其 from 子句中的表的个数不要过多，如不要超过4个！避免耗时费力的多表连接操作。
如果频繁执行的 SQL 查询涉及的查询数据存放在 N≥4 张表中，可以考虑将这N张表中的数据进行合并
MySQL 中 join 的用法
MySQL 中 join 的用法，含有 natural join、using 等简化版语句
以下三种写法都是取二者的笛卡尔积，但在加限定条件时，只有 带有 join 的语句才能用 on 和 using 语法
select * from teaches, takes;
select * from teaches join takes;
select * from teaches cross join takes;
natural join 自动匹配两表中键相同的列（注意，natural 一定不要拼错，要不会变成重命名语句）
select *
from instructor natural join teaches
等价于
select *
from instructor join teaches
using(ID)
利用 natural join 简化语句：
SELECT customer.customer_name
FROM customer
JOIN depositor ON customer.customer_id = depositor.customer_id
JOIN account ON depositor.account_number = account.account_number
JOIN branch ON account.branch_name = branch.branch_name
WHERE customer.city = 'Beijing'
AND branch.city = 'Tianjin'
AND account.balance > 100;
简化为
SELECT customer.customer_name
FROM customer
NATURAL JOIN depositor
NATURAL JOIN account
NATURAL JOIN branch
WHERE customer.city = 'Beijing'
AND branch.city = 'Tianjin'
AND account.balance > 100;
all/any/some/in
用 A B 两个实数集合举例
all：A > all( B ) ，则 A 中选出的数要比 B 中所有的数都大
some/any：A > some/any( B )，则 A 中选出的数只要有比 B 中的数大的就行，选出的结果往往是原列表除去最小值后的列表
Find the names of all instructors whose salary is greater
than the salary of all instructors in the Biology department.
select name
from instructor
where salary > all (
select salary
from instructor
where dept name = 'Biology'
);
EXISTS
Except
unique
如果作为参数的子查询结果中无重复元组，则 unique 返回 true
with as 的用法
Find all departments where the total salary is greater than
the average of the total salary at all departments
with dept _total (dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),
dept_total_avg(value) as
(select avg(value)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;
解析：其中，dept _total 和 dept_total_avg 是两个临时创立的表，括号里是表定义的特征，分别对应 as 后面 select 后选出的特征。定义临时表后，在后续 select 语句中就可以直接使用。
标量子查询（Scalar subquery）：只需要查询单个值
select dept_name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name)
as num_instructors
from department;
解析：括号中 select count 语句只查询了单个值，将其命名为 num_instructors，作为外层 select 的子查询，表示 instructor 的数量
综合案例
找出工资高于 Comp. Sci. 学院的所有 instructors 的 instructors 名字
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp. Sci.'
分析：利用 T 、S 重命名以区分不同的 instructor，实现了对同一属性的不同值的比较。
从 2009 年秋季开设的每个课程段中，找出最多的选课人数。
select sec_id , max(countid)
from (
select sec_id, count(ID) countid
from takes
where semester = "Fall" and year = '2009'
group by sec_id
) as countidlist
group by sec_id
视图 —— view
视图关系可以定义为包含查询结果的关系。可以隐藏不需要的信息，可以把信息从多个关系收集到一个单一的试视图中。
创建视图
create view com_instructor as(
select *
from instructor
where dept_name = 'Comp. Sci.'
);
select *
from com_instructor;
删除视图
drop view com_instructor;
事务——transaction
将一条/几条语句关联在一起，要么全部执行成功，要么全部执行失败。如果成功，则提交（Commit）如果其中有一条执行失败，则全部回滚（Rollback）（重新执行）防止数据库信息受损。
事务有很多实用的场景。例如对于电商网站，通常将用户订单存储在一张表中，将商品库存情况存储在另一张表中，当有用户下单时，需要执行两条 SQL 语句，一条负责更新订单表，一条负责更新库存表，这两条 SQL 语句必须同时执行成功。如果只有一条语句执行成功，另一条语句执行失败，将导致数据库出错，这种后果是无法接受的。
为了避免出现意外，可以将以上两条语句放到一个事务中，其中一条语句执行失败时，数据库将回滚到原来未修改的状态。对于买家来说，数据库回滚会导致下单失败，但这很容易处理，让买家再次下单即可。数据库的正确性永远是最重要的。
其实我们平时使用数据库时，就已经在使用事务了，只不过这种事务只包含一条 SQL 语句，并且由数据库引擎自动封装和提交。这意味着，对于任何一条 SQL 语句，要么执行成功，要么执行失败，不能成功一部分，失败一部分。
完整性约束
保证授权用户对数据库做出的改变不会导致数据一致性的破坏，在创建表的时候添加关键字添加约束
常用约束总结：
主键约束 primary key
该项为主键，唯一且非空
create table student_1(
id varchar(10) primary key,
name varchar(15),
age int(2),
sex varchar(1)
)
desc student_1
唯一约束 unique
指该项在表中不能重复
create table student(
id int(8),
name varchar(20) unique,
school varchar(10),
age int(2),
sex varchar(1)
)
非空约束 not null
直接写在非空变量的后面即可
create table instructor(
ID varchar (5),
name varchar (20), not null
dept_name varchar (20)
)
默认约束 default
指在没有对某字段插入具体值的时候，使用默认的值
create table student(
id int(8),
name varchar(20),
age int(2),
sex varchar(1) default '男'
)
外键约束 foreign key
某一表中某字段的值依赖于另一张表中某字段的值
check 子句
可以定义某一字段满足某种限定
用check子句模拟了一个枚举类型，限定了semester必须是四个中的一个
限定了该字段值的范围
参照完整性约束：当一个表作为另几个表的参照时，对该表的操作会影响参照该表的表
DCL（Data Control Language）
数据控制语言，用于创建数据库用户、控制数据库的访问权限
索引
SQL索引详解
索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。简单说来，索引就是指向表中数据的指针。数据库的索引同书籍后面的索引非常相像。
索引分为聚集索引和非聚集索引
聚集索引 clustering index
有主键的表，主键即为聚集索引。MySQL 不能手动创建聚集索引，在创建主键的时候自动创建聚集索引。如果没有创建主键，那么默认非空的列为聚集索引，如果没有非空的列那么会自动生成一个隐藏列为聚集索引。
聚集索引可以理解为顺序排列，比如一个主键自增的表即为聚集索引，即 id 为1的存在于第一条，id为 2 的存在于第二条... 假使数据库中是使用数组来存放的这张表中的数据，那么如果我需要查找第100条，那么直接第一条数据的地址加上100即为第一百条的地址，一次就能查询出来。
因为数据库中的数据只能按照一个顺序进行排列，所以聚集索引一个数据库只能有一个。所以一般在MySQL 中，我们创建的主键即为聚集索引，数据是按照我们的主键顺序进行排列。所以在根据主键进行查询时会非常快。
非聚集索引 nonclustering index
非聚集索引可以简单理解为有序目录，是一种以空间换取时间的方法。举个例子，在一个user表中，有一个id_num，即身份号，此不为主键id，那么这些数据在存储的时候都是无序的，比如
id为1的id_num为100，id为2的id_num为97，id为3的id_num为98，id为4的id_num为99，id为5的id_num为96。。。id为67的id_num为56。。。
那么如果我要查找id_num为56的人，那么只能一条一条的遍历，n条就需要查询n次，时间复杂度为O(n)，这是非常耗费性能的。
所以，现在就需要为id_num增加非聚集索引，添加了非聚集索引后，会给id_num进行排序（内部使用结构为B+树），并且排序后，我只需要查询此目录(即查询B+树)，很快就知道为id为56的在数据库中的第67条，而不需要在去遍历表中的所有数据。
所以，在非聚集索引中，不重复的数据越多，那么索引的效率越高。
稠密索引 dense index、稀疏索引 sparse index
稠密：为每个搜索码都建立索引 稀疏：只为一部分搜索码建立索引
索引的选择原则
非聚集索引在数据库创建、增加、删除、修改的时候都需要作出相应的修改，所以，使用索引也是有一定的原则，即：
1、较频繁的作为查询条件的字段应该创建索引
2、重复太多的字段不适合单独创建索引，即使频繁作为查询条件
3、不会出现在WHERE子句中的字段不应该创建索引
以下情况不建议使用索引
1、小量的数据不用建索引（参考值一万条数据往下为小量数据），如果对小量数据建立索引可能会更慢，因为索引查找是二次查找，直接全表查询即可。
2、需要频繁进行大批量的更新或者插入操作的表；
3、如果列中包含大数或者 NULL 值，不宜创建索引；
4、频繁操作的列不宜创建索引。
5、不重复的字段越多，那么索引的价值越高，查看不重复的字段占总体的比例可以使用下面的sql语句：
SELECT count(DISTINCT(name))/count(*) AS Selectivity FROM index_test;
比如上面这个sql就是判断index_test表中name字段中不重复的值占整体的比例，这个比例应该在(0,1]之间，这个数值越大，越应该使用索引。
创建索引
创建普通索引
CREATE INDEX index_name ON table_name(属性名);
或者
修改表: ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2,...);
或者
创建表时指定索引：CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );
eg:
CREATE INDEX name_index ON index_test(name);
此为在index_test表上的name列上创建一个索引name_index。
创建唯一索引（添加UNIQUE字段）
下面三种模式都可以创建唯一索引：
1、创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
2、在表上增加索引：ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
3、创建表时指定索引：CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );
CREATE UNIQUE INDEX id_num_index ON index_test(idNum);
也可以写成下面的形式：
ALTER TABLE index_test ADD UNIQUE id_num_index(idNum);
此为在index_test表的idNum列上创建一个唯一索引id_num_index
删除索引
以下两种模式都可以删除索引：
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
eg:
DROP INDEX name_index ON index_test;
此为删除在index_test表上的name_index索引
