数据库概念

快速定位核心表

核心表的数据往往是最大的

select count( * ) from 表名

笛卡尔积和内外连接

内外连接的底层是从两表产生笛卡尔积临时表，内连接筛选的临时表必须同时满足两表的关联条件，而外连接（以左连接为例）是临时表主要满足左表关联条件，其次满足右表关联条件，右表关联不上的显示为null。注意关联条件必须是双方都唯一的，一般为主键和外键

表关系

一般而言，对于有直接关联关系的2张表都是1对n或1对1的关系。例如用户和订单是1对n的关系，产品和订单的关系也是1对n的关系，此时可以把用户和产品的关系称为n对m的关系。订单充当了多对多关系的中间表作用。大多数情况下跨实体之间的关系都是n对m的关系。而中间表起到了连接的作用

范式

非范式的特点是：当更新数据时，由于有数据冗余，需要更新多个地方的数据，但是查数据时不用关联太多的表，而符合范式的情况下，查数据会关联多张表。特别是对于经常被修改的信息不能严格依赖于范式，例如预约记录依赖于我们设置的时间段，时间段若是个可配置项，用户的预约记录中的时段必须为冗余字段，否则在预约记录生成后直接修改时段的配置项会直接影响预约记录数据

e	d
1NF	属性不可再分
2NF	1NF条件下非主属性必须完全依赖主键
3NF	2NF条件下不能有传递依赖，即非主键属性不能互相推算出来
BCNF	3NF下，只有一列作为主键

事务

事务是数据库区别去其它文件系统的特性之一，事务指某段脚本开始到结束过程的任务，单机模式的事务可以保证ACID，但分布式事务需要人为控制。事务的开启和提交是影响性能的，所以记录批量提交比逐条提交快得多。回滚事务时数据虽然能rollback，但是为之前数据产生的分配空间依然存在。当多个事务以不同的顺序锁定资源或同时锁定同个资源会出现死锁，不同的数据库有不同的处理方案。大事务锁定太多数据会造成大量阻塞和锁超时，回滚时间长，从而造成主从延迟

e	c	d
A	原子性	要么全完成，要么全不完成
C	一致性	事务完成后，数据正确完整
I	隔离性	事务可隔离，防止并发交叉读写数据导致数据出错
D	持久性	事务完成后数据保存

隔离级别

数据库服务器处于高并发环境下，常见的隔离级别是阻止幻读级别

c	d
赃读（未提交读）	指A事务中读取到其它事务未提交的数据
不可重复读（已提交读）	指A事务中多次查询的数据不同（被其它事务update）
幻读（可重复读）	指A事务多次查询的结果集多了/少了，产生幻行（被其它事务insert/delete）
可串行化	强一致性

日志

redo log保存着将事务提交但未持久化的SQL等数据；undo log用于保存着与redo log相反的SQL等数据，用于数据回滚，从而保证原子性

影响性能的物理因素

物理因素对QPS的影响巨大。根据木桶效应，当其中一个指标出问题，整体都会受影响
①硬件性能。例如CPU，内存，磁盘的质量。多核比频率重要得多，多核能提高并发量，而频率能加快SQL运算
②使用率

• 网卡IO。主从复制时若slave服务器过多，数据同步时会占用网卡IO；select *也会导致多余的流量损耗
• 磁盘IO。使用固态硬盘查询速度更快
• CPU使用率。使用率过高会导致SQL处理速度下降

影响性能的因素

• 冗余字段过多
• 索引字段过多会影响update和delete效率
• 外键和级联是强阻塞的，不适合高并发环境
• 超过 500 万行或者单表容量超过 2GB的大表，数据量是后期数据库的主要性能瓶颈。需要把冷数据/旧数据进行归档拆分
• 对大表的DDL，如建立索引也需要大量时间，一般会锁表
• 数据筛选区分度过低。例如某个字段只表示2种类型，意味着查询数据的分母数太大，会导致大量磁盘IO降低磁盘效率从出现慢查询
• 分库带来的跨库和分布式事务问题
• 服务器参数或数据库参数优化，例如TCP回收和最大TCP连接数修改

规范

1. 不使用大写、复数、保留字命名，保留字如desc, user
2. 使用decimal表示金额
3. id、gmt_create、gmt_modified、state为必备四个字段
4. 增加不经常修改数据的冗余字段提高查询性能
5. 及时更新字段注释
6. 使用unsigned类型扩大范围

视图

视图依据基本表存在，是预编译的SQL语句，不占物理空间，并不能优化性能

存储过程

存储过程是完成特定功能的SQL语句集，能在存储过程中调用其他存储过程，但不能drop其他存储过程。其入参不能与数据表字段名相同，例如在删除记录时，指定的入参实际会变成每条记录的字段值，导致把表的数据全部删除。

与函数的区别

存储过程作为独立的程序执行，而函数需要嵌入SQL语句调用

触发器

触发器是特殊的存储过程，只是在insert,update,delete发生时才执行，是隐式调用，被动触发的。相同的表，相同的事件只能创建一个触发器

---

索引

加索引是为记录数据位置，以实现快速查找。但索引太多会占用空间，影响修改的效率，当需要大批量修改，操作前先删除索引，操作后再加入索引

---

数据类型

char会填充空格到指定长度，查询速度快但浪费空间；varchar是动态的，查询速度慢。

datatime的范围是1000-9999；timestamp会自动转时区

---

MySQL引擎

-	innodb	myisam
事务	支持	不支持
锁级别	行	表
存储文件	frm、myd、myi	数据文件和日志文件
主键	必须	非必须
全文索引	5.6支持	支持
恢复	日志恢复	无日志
外键	支持	不支持

optimize table 'tablename'

对于delete的数据，存储文件并不会马上释放空间，而是会把原来位置的标志位置为已删除，后续的新数据会逐步填充这些空间。若需要马上整理，则使用optimize table 'tablename'进行整理压缩

分库分表

分表
分表的难点：以某个区分区域字段来拆分不同来源的表数据，或者对表的历史数据进行归档，因为旧数据大多数情况下是冷数据，要确定好归档时间点，归档操作要保证不阻塞
分库
分库的难点：业务的拆分，好的拆分不可能出现跨库的join问题，但是随着业务的发展，有可能是没法避免的。
分库的复杂性：单体的事务很好实现，解决分布式事务问题是很复杂的，具体看分布式理论
垂直拆分
竖着切，类似于把一些字段从原先的表中拆出来，这种比较少，在数据库设计之初没设计好才会出现这种问题
分离活跃数据
活跃数据存在默认表优先查询，不活跃数据定期转移到备用表

---

备份还原

oracle

# 将数据库TEST完全导出,用户名system 密码123456 导出到D:\oracle.dmp中
exp system/123456@orcl file=d:\oracle.dmp

# 将D:\oracle.dmp 中的数据导入 TEST数据库中。
imp system/123456@orcl file=d:\oracle.dmp ignore=y

mysql

mysql> mysqldump -u root -p mydb > mydb_backup.sql

mysql> source mydb_backup.sql

sqlserver

--- sqlserver
DECLARE @filename VARCHAR(255); --文件名
DECLARE @date DATETIME; --日期
DECLARE @path VARCHAR(255); --文件存放路径
SELECT @date = GETDATE(); --获取当前时间
--根据当前时间自动生成文件名，后缀为.bak
--年月日 时分
SET @filename = CAST(DATEPART(yyyy, @date) AS VARCHAR) + '-' + CAST(DATEPART(mm, @date) AS VARCHAR) + '-'
      + CAST(DATEPART(dd, @date) AS VARCHAR) +'-' + CAST(DATEPART(hh,@date) as varchar)+CAST(DATEPART(mi,@date) as varchar)+'.bak';
SET @path = N'E:\backup\test_' + @filename; --设置文件路径

--备份数据库，test数据库名称
BACKUP DATABASE test TO  DISK = @path;

use master;
RESTORE DATABASE 数据库名 FROM DISK='还原文件路径\文件名.bak' with replace

---

学生 成绩 课程 老师的SQL练习

1. 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号

select a.s_id, c.s_name a.s_score "01", b.s_score "02" from
(select s_id, c_id, s_score from score where c_id = '01') as a
inner join
(select s_id, c_id, s_score from score where c_id = '02') as b
on a.s_id = b.s_id
-- 拓展获取姓名
inner join student as c on c.s_id = a.s_id
where a.s_score > b.s_score

2.查询平均成绩大于60分的学生的学号和平均成绩

select s_id, avg(c_score)
from score 
group by s_id
-- 注意是having
having avg(s_score) > 60

3. 查询所有学生的学号、姓名、选课数、 总成绩

-- 后面两个字段并不能马上算出来，间接join后在group by
select a.s_id,a.s_name, count(b.c_id),
-- 判断若没选择课程，显示为0而不是null
sum(case when b.s_score is null then 0 else b.s_score end)
from student  as a
-- 注意不能为inner join，因为可能有学生没选课
left join score as b on a.s_id = b.s_id
group by s_id, a.s_name

4. 查询姓“张”的老师的个数

-- distinct去重，但是会降低损失，考虑好再加
select count(distinct t_id)
from teacher
where t_name like '张%'

5. 查询没学过“张三”老师课的学生的学号、姓名

select s_id, s_name from 
student where s_id not in(
select s_id from score as a
inner join course as c on a.c_id = c.c_id
-- 注意关联字段
inner join teacher as t on c.t_id = t.t_id where t.t_name = '张三') 

6. 查询学过‘张三’老师课程的同学的学号、姓名

7. 查询学过编号为“01”的课程并且学过编号为“02”的课程的学生的学号，姓名

select s_id, s_name
from student where s_id in(
select s_id from 
(select s_id from score  where c_id = '01') as a 
-- 求交集
innner join
(select s_id from score where c_id =  '02') as b on a.s_id = b.s_id)

8. 查询每门课程的的总成绩和平均成绩

select c_id, sum(s_score), avg(s_score), count(s_score), count(s_id) from score
group by c_id

9. 查询所有课程成绩均小于60分的学生的学号、姓名

select
    s.s_id, s.s_name
from
    student s
left join score b
on s.s_id = b.s_id
group by s.s_id 
-- 大于60则是min(b.s_score) > 60
having max(b.s_score) < 60

10. 查询没有学全所有课的学生的学号、姓名

select a.s_id, a.s_name
from student as a
-- 注意为join left，因为有可能有学生所学课程全为null，
left join score as b on a.s_id = b.s_id
group by a.s_id having count(distinct s.s_id) <
-- 所有课程数
(select count(c_id) from course)

11. 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

select a.s_id, a.s_name from student a 
-- 以上添加学生字段
inner join
select distinct s_id from score 
where c_id in(
select c_id from course where s_id = '01') and
-- 排除01自身 
s_id != '01'）b on a.s_id = b.s_id

12. 查询和“01”号同学所学课程完全相同的其他同学的学号

select * from student
where s_id in (
  select s_id from score
-- 排除自身
where s_id != '01'
group by s_id having
-- 01的课程数量
count(distinct c_id ) = (select count(distinct c_id) from score where s_id = '01')
)
-- 学了在01所学课程之外的学生要排除
and s_id not in (
  -- 学了01所学课程之外的学生
  select distinct s_id from score
where c_id not in (
-- 01同学所学的课程
    select c_id from score where s_id = '01'
)
)

15.查询两门及以上不及格课程的同学的学号，姓名和平均成绩

select a.s_id, a.s_name, avg(s_score) from student as a
-- 以上增加学生字段
inner join score as b
on a.s_id = b.s_id 
where a.s_id in (
select s_id from score 
where s_score < 60
-- 筛选出不及格课程>=2的同学
group by s_id, s_name having count(distinct c_id) >= 2
)

16. 查询“01”课程分数小于60，按分数降序排列学生信息

select a.*, b.s_score from student as a
inner join score as b on
a.s_id = b.s_id
where s.c_id = '01' and b.s_score < 60
order by b.s_score desc

17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select 
s_id "学号"，
-- max的妙用
max(case when c_id = '01' then s_score else null end) '语文'，
max(case when c_id = '02' then s_score else null end) '数学'，
max(case when c_id = '03' then s_score else null end) '英语'，
avg(s_score) "平均成绩"
from score
group by s_id
order by avg(s_score) desc

18. 查询各科成绩最高分、最低分和平均分。以如下形式显示：课程ID, 课程name, 最高分，最低分，平均分，及格率，中等率，优良率，优秀率

select s.c_id '课程ID',
c.c_name,
max(s.s_score),
min(s.s_score),
avg(s.s_score),
-- 神奇点
sum(case when s.s_score >= 60 then 1 else 0 end)/count(s_id) “及格”,
sum(case when s.s_score >= 70 then 1 else 0 end)/count(s_id) “中等”,
sum(case when s.s_score >= 80 and s.s_score < 90 then 1 else 0 end)/count(s_id) “良好”,
sum(case when s.s_score >= 90  then 1 else 0 end)/count(s_id) “优秀”
from score as s
inner join course as c on s.c_id = c.c_id
group by c_id

19. 窗口函数

20. 查询学生的总成绩并进行排名

select s_id "学号", sum(s_score) "总成绩"
from score
group by s_id
order by 总成绩 desc

21. 查询不同老师所教不同课程平均分从高到底显示

22. 查询一门课程第三大的成绩

select min(a.s_score) from (
select s_score from score order by s_score desc) a
limit 0, 3

23. 统计各分数段人数，课程ID，课程名称

select c.c_id, c.c_name
,sum(case when sc.s_score <= 100 and sc.score > 85 then 1 else 0 END) as "[100, 85)"
-- 注意用count then的值已经不重要，但是else的值必须为null，不然还是会统计
,count(case when sc.s_score <= 85 and sc.score > 70 then 1 else NULL END) as "[85, 70)"
,sum(case when sc.s_score <= 70 and sc.score > 60 then 1 else 0 END) as "[70, 60)"
,sum(case when sc.s_score <60 then 1 else 0 END) as "[<60)"

24. 查询学生平均成绩及其名次

-- oracle支持，新版mysql也支持
select s_id, avg(s_score), row_number() over(order by avg(s_score) desc) from score group by s_id

26. 查询每门课程被选修的学生数

select c.c_id,c.c_name, count(distinct sc.s_id) from score as sc
inner join course as c
on sc.c_id = c.c_id
group by c.c_id,c.c_name

27. 查询出只有两门课程的全部学生的学号和姓名

select b.s_id, b.s_name
from score as a
inner join student b
on a.s_id = b.s_id
group by a.s_id
having count(distinct a.c_id) = 2

28. 查询男生、女生人数

select s_sex, count(s_id) from student
group by s_sex

31. 查询2000年出生的学生名单

-- 日期为varchar
select * from student where year(s_birth) = 2000

32. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select a.s_id, a.s_name, b.avg_score from student a
inner join  
(select s_id, avg(s_score) as avg_score from score group by s_id
having avg(s_score) >= 85) b on a.s_id = b.s_id

33. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排序

select c_id, avg(s_score) as 
avg_score from score as s
inner join course as c on
s.c_id = c. c_id
group by s.c_id
-- 重点
order by avg_score asc, s.c_id desc

34. 查询课程名称为“数学”，且分数低于60分的学生姓名和分数

select a.s_name, b.s_score from
student a 
inner join score b
on a.s_id = b.s_id
inner join course c
on b.c_id = c.c_id
where b.s_score < 60 and c.c_name = '数学'

44. 检索至少选修两门课程的学生学号

select a.s_id,count(b.s_score)from student a
inner join score b
on a.s_id = b.s_id
group by a.s_id having count(b.s_score) >= 2

46. 查询各个学生的年龄

SELECT TIMESTAMPDIFF(YEAR,s_birth,NOW()) as age FROM student

---

语法

函数	用法	结果
round	select round(5.7345, 2)	5.73
truncate	select truncate(5.7345, 2)	5.73
ceiling	select ceiling(5.2)	6
floor	select floor(5.7)	5
abs	select abs(-5.2)	5.2
rand	select rand()	随机小数

函数	用法	结果
length	select length('sky')	3
upper	select upper('sky') SKY
lower	select lower('Sky')	sky
ltrim	select ltrim(' Sky')	Sky
left	select left('kindergarten', 4)	kind
right	selec right('kindergarten', 6)	garten
substring	select substring('kindergarten',3,5)	nderg
locate	select locate('N', 'kindergarten')	3
repalce	select replace('kindergarten', 'garten', 'garden')
concat	select concat(first_name, ' ', last_name) as full_name from customers	-

函数	用法	结果
year	select year(now())	2020
dayname	select dayname(now())	monday
extract	select extract(year from now())	2020
date_format	select date_format(now(), '%Y %m %d %H:%i:%s')	2020 02 02 10:00:00
date_add	date_add(now(), interval -1 day)	2020-02-01 20:47:30
datediff	select datediff('2020-2-8', '2020-2-1')	7

select 
    order_id,
    ifnull(shipper_id, 'not assigned') as shipper
from orders;

-- 返回三个参数中第一个不为null的值
select 
    order_id,
    coalesce(shipper_id, comments, 'not assigned') as shipper
from orders;

select
    order_id,
    order_date,
    if(year(order_date) = year(now()), 'Active', 'Archived') as category
from orders;


select 
    order_id,
    case
          when year(order_date) = year(now()) then 'Active'
          when year(order_date) = year(now()) - 1 then 'Last Year'
          when year(order_date) < year(now()) - 1 then 'Archived'
          else 'Future'
    end as category
from orders;

---

select 
    last_name,
    first_name,
    points,
    (points + 10) * 100 as 'discount factor'
    --  (points + 10) * 100 as discount_factor
from customers;

select distinct state 
    from customers;

select * 
from products
where product_id not in (
    select distinct product_id
    from order_items
)

select a.name,b.age from test as a
join test2 as b
on a.id=b.id
select a.name,b.age from test as a
join test2 as b
using(id)

-- 关联子查询，性能更差
select * 
from employees e
where salary > (
    select avg(salary)
    from employees
    where office_id = e.office_id
)

-- 效率高
select * 
from employees e, (select avg(salary) avgsalary from employees) ev
where e.office_id = ev.office_id
and e.salary > ev.salary; 

select * 
from clients
where client_id in (
    select distinct client_id
    from invoices
)

select * 
from clients c
where exists (
    select client_id
    from invoices
    where client_id = c.client_id
)

create index idx_state on customers(state, 1);

create fulltext index idx_title_body on posts(title, body);

select * match(title, body) against ('react redux')
from posts
where match(title, body) against('react -redux + form' in boolean mode)

create index idx_state_points on customers(state, points);
explain select customer_id from customers
where state = 'CA' and points > 1000; 

show indexes in customers

create user john@'%.codewithmosh.com' identified by '1234';

select * from mysql.user;

create user bob@codewithmosh.com identified by '1234';
drop user bob@codewithmosh.com;

set password for john='1234';

create user moon_app identified by '1234';

grant select, insert, update, delete, execute
on sql_store.*
to moon_app;

grant all
on *.*
to john;

show grants for john;

revoke create view
on sql_store.*
from moon_app;