Mysql基础语法详解+code练习汇总
目录
- Mysql基础语法详解+code练习汇总
- 1 数据定义 DDL
- 1.1 创建数据库/表格
- 1.2 删除语句
- 1.3 索引
- 1.4 视图
- 1.5 触发器
- 1.6 自定义函数
- 2 数据操作 DML
- 2.1 插入语句
- 2.2 查询语句
- 2.2.1 基础语法
- 2.2.2 条件语句
- 2.2.3 读取几行数据limti&offset
- 2.2.4 字符串操作
- 2.2.5 内置函数
- 2.2.5.1 日期函数
- 2.2.5.2 数学函数
- 2.2.5.3 Replace函数
- 聚合函数
- 2.2.5.4 With函数
- 2.2.5.5 窗口函数
- （1）排序窗口函数
- （2）聚合窗口函数
- （3）滑动窗口函数
- （4）偏移函数
- 注意事项
- 2.2.6 Join操作
- 2.2.7 复杂操作
- 2.2.7.1 求交集、差集、补集、并集的方法
- 2.2.7.2 行列互转
- 2.2.7.3 查找前n名
- 2.2.7.4 求最值、中位数
- 2.2.7.5 双向关系
- 2.2.7.6 查询连续n天登录
- 2.2.8 正则表达式
- 2.3 更新/修改语句
- 2.4 细节与其他规则
- 2.5 尚未解决的问题
- 2.6 Mysql代码风格
- 2.7 Mysql的执行顺序
- 3 数据控制 DCL
- 4 事务控制
- 5 一些必知概念
- 5.1 存储引擎
- 5.2 主从同步
- 5.3 事务
- 5.3.1 隔离性
- 6 面试题
SQL语言包括四种语句：
- 数据定义语言(DDL Data Definition Language)
- 数据操作语言(DML)
- 数据控制语言(DCL)
- 事务控制语言(TCL)。
1 数据定义 DDL
数据定义语言：负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER、DROP和TRUNCATE四个语法组成。定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器等等。
1.1 创建数据库/表格
1.2 删除语句
基本语法：
（1）删除表：delete table 表名; （2）删除数据：delete from 表名 where 字段= 应用场景1：使用delete语句删除重复数据
使用自连接的方式，借助p2删除特定条件下p1中的数据
相关题目：196. 删除重复的电子邮箱
1.3 索引
（1）添加索引：必须分开一条一条写，记得加’;‘
单列索引和组合索引
缺点：降低更新表的速度，占用磁盘空间的索引文件
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
// 这条语句创建索引的值必须是唯一的。# 错误
select *
from salaremp_noies
force index idx_emp_no(emp_no)
where emp_no = '10005';
select *
from salaries
force index (idx_emp_no)
where emp_no=10005
ALTER TABLE tbl_name ADD INDEX index_name (col_list);
// 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
// 该语句指定了索引为 FULLTEXT ，用于全文索引。（2）force index()：查询时使用，可以强制使用某个索引，优化查询缩短时间，用explain检查查询任务
# 错误
select *
from salaremp_noies
force index idx_emp_no(emp_no)
where emp_no = '10005';
select *
from salaries
force index (idx_emp_no)
where emp_no=100051.4 视图
create view actor_name_view as
select first_name as first_name_v,last_name as last_name_v
from actor;1.5 触发器
create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end1.6 自定义函数
格式
create function 函数名([参数列表]) returns 数据类型
begin
sql语句;
return 值;
end;2 数据操作 DML
2.1 插入语句
-
insert into：插入values(),()…一次性插入多条数据 -
ignore：插入多条数据，忽略已经存在的
INSERT IGNORE INTO TABLE VALUES();
REPLACE INTO TABLE VALUES();2.2 查询语句
2.2.1 基础语法
- 排序：
order by，自动升序，desc降序，可根据多个字段排序，字段1 asc, 字段2 desc - 去重：
distinct(字段1, 字段2 ...)，不加括号表示对某字段及其之后所有的字段去重 - 分组：
group by可以连接多个字段，通过某一个字段分类，通常和having连用过滤。
ps：可用于取每组的第一行数据 - 连接：
join，将多张表连接起来，不同join用法不同
（1）inner join/join：获取两个表中字段匹配关系的记录，取交集
（2）普通join没有on条件：取笛卡尔积，笛卡尔连接不需要连接条件（另外cross join、join、inner join不加连接条件都可以实现笛卡尔连接）
（3）left join：左连接，右表连接字段中不存在的值记为null
（4）right join：右连接，左表连接字段中不存在的值记为null
（5）full join：全连接，左右连接字段中不存在的值都记为null - 空/非空：
is null/not null，判断字段是否为空。ifnull(a,b)：如果a是null的话就用b填充 - 或者：
or两边的条件成立其一则成功 - where联合键查询：
where(字段1, 字段2) in (select 字段1, 字段2 from ...)
select player_id, device_id
from Activity
where (player_id, event_date) in (
select player_id, min(event_date)
from Activity
group by player_id
);ps：常见语法错误=null
- 包含：
in/not in(字段1, 字段2,...) in(select 字段1, 字段2,... from)(字段1, 字段2,...) not in(select 字段1, 字段2,... from)ps：如果子查询中存在null值则过滤无效，例如(1,2,3) in (null, 1, 2) 得到结果集为1,2,3 - 存在：
exists存在某种情形
select *
from employees
where not exists
(select dept_emp.emp_no
from dept_emp
where employees.emp_no = dept_emp.emp_no);- in和exists的联系与区别
in适合于外表大而内表（子查询表）小的情况；exists适合于外表小而内表大的情况
in的运行逻辑：
（1）查询内表
（2）将内表和外表做笛卡尔积
（3）过滤
exist的运行逻辑：
（1）查询主表
（2）对每一行数据做判断，执行exists后面的select看是否能返回
使用in导致超时
# Write your MySQL query statement below
select a.user1_id user_id, page_id, count(*) friends_likes
from(
select user1_id, user2_id
from Friendship
union
select user2_id, user1_id
from Friendship
) a
left join Likes
on a.user2_id = user_id
# 错误代码：这里的user_id来自这个没有别名的Likes相当于是user2_id
# where (user_id, page_id) not in(
where (a.user1_id, page_id) not in(
select l.user_id, l.page_id
from Likes l
where l.user_id = a.user1_id and l.page_id = page_id
)
group by a.user1_id, page_id
order by a.user1_id, friends_likes desc
- any/all：
any，至少有一个。all，全都。<any = 找到最小值， >all 找到最大值
select
activity
from Friends
group by activity
having count(*)>any(
select count(*) from Friends group by activity
) and count(*)<any(
select count(*) from Friends group by activity
)2.2.2 条件语句
-
if(...,是输出,否输出)可以用于交换两个字段的数据
select c.person1, c.person2,count(c.person1) as call_count , sum(c.duration) as total_duration
from(select if(from_id>to_id,to_id,from_id) as person1,
if(from_id>to_id,from_id,to_id) as person2,
duration
from Calls) as c
group by c.person1, c.person2
order by c.person1, c.person2;-
case多条件语句
case
when 条件1 then 结果1
...
else 默认结果
endps：when和when语句之间没有 “,”，不要忘记在最后加上end
案例：
select id,
(case
when id=(select id from tree where p_id is null) then 'Root'
when id in (select distinct p_id from tree) then 'Inner'
else 'Leaf'
end ) as Type
from tree;- if和case之间的区别
（1）如果case只有两个分支，且分支之间是互补的那么case等价于if
（2）如果case中有多个分支，即多个条件，那么使用case会比使用if的效率高
2.2.3 读取几行数据limti&offset
-
limit n,m：跳过n条数据从第n+1行开始读数据，读m条数据。ps：不要加括号 -
limit n offset m：跳过m条数据读n条数据 - 读取最后一行/第一行数据可使用limit求解：
- 第一行：升序排序+
limit 1 - 最后一行：倒序排序+
limit 1
方法1：limit
select id
from Seat
order by id desc
limit 1方法2：count
select count(distinct id)
from Seat2.2.4 字符串操作
- 计数某值出现次数：
select id,length(string) -
length(replace(string,',','')) as
from strings;- 取子字符串的方法
（1）right(字段,n)/left(字段,n)：对于一个字符串，从右往左数取n个值，left同理
（2）substring(字段,n)：从第n位开始取，字符串从1开始计数
select first_name
from employees
order by right(first_name,2);- 大小写转换
将字段的前n个字符转变为大写/小写
（1）upper(字段,n)（2）lower(字段,n)
2.2.5 内置函数
2.2.5.1 日期函数
- 当前
now():返回当前日期时间 2021-03-17 14:44:06curdate():返回当前日期 2021-03-17curtime():返回当前时间 14:46:00 - 获得某一粒度的时间
YEAR('2023-06-12')YEAR()：提取年份MONTH()：提取月份DAY()：提取日期（月中的天）HOUR()：提取小时MINUTE()：提取分钟SECOND()：提取秒 - 转换日期：
date(now()) :提取时间的日期部分extract(year/month from date):返回时间的指定部分的值date_format(date,"日期格式"):字符的大小写不同匹配结果有区别最常用是"%Y-%m-%d"
日期格式参考 - 日期加法/减法：
date_add('2019-07-27', interval 30 day)计算30天后的日期（日期1+30）date_sub('2019-07-27', interval 30 day)计算30天前的日期（日期1-30）
时间单位：year:年，quarter:季，month:月，week:周，day:天，hour:小时，minuter:分钟，second:秒，microsecond:毫秒 - 日期间隔
（1）datediff(日期1, 日期2)计算日期1和日期2之间的天数差值
（2）timestampdiff(时间粒度, 日期1, 日期2)计算日期1和日期2之间的秒/分/时等差值
时间粒度：
2/SECOND–秒：计算开始时间与结束时间相差的秒数。
4/MINUTE–分：计算开始时间与结束时间相差多少分钟。
8/HOUR–小时：计算开始时间与结束时间相差多少小时。
16/DAY–天：计算开始时间与结束时间相差的天数。
32/WEEK–周：计算开始时间与结束时间相差的周数。
64/MONTH–月：计算开始时间与结束时间相差多少月。
128/QUARTER–季：计算开始时间与结束时间相差的季度数。
256/YEAR–年：计算开始时间与结束时间相差的年数。
（2）开始时间：需要计算时间差的开始时间，参数格式需要为时间格式。
（3）结束时间：需要计算时间差的结束时间，参数格式需要为时间格式。
ps：如果直接将两个date类型的数据相减lead(a.visit_date,1) over() - a.visit_date ，他们会先被转为int再相减
应用场景：
- 将多个int拼接到一起形成一个日期
拼接日期：concat('2020-',a.month)将字符串转完整日期：str_to_date(concat('2020-',a.month),'%Y-%m')将完整日期转年月格式：date_format(str_to_date(concat('2020-',a.month),'%Y-%m')ps：第二步得到日期是完整的0000-00-00格式还需要截取年月 - 将date型的数值与int作比较
date_format(d.join_date,'%Y%m') <= 202000+a.month
2.2.5.2 数学函数
-
count（）count()返回值是bigint类型，如果没有查询到值就返回0
（1）count(字段)as 新字段名：对不为null的行计数，效率比count(字段)差
（2）count(1) = count(*)效率差不多但还是建议使用count(*)
注意：对id做分组对其中一个字段取min，其他的字段不会和该字段取同行对应值，会先分组然后取第一次出现的值
count的优化：
（1）MyISAM：表级锁不会有并发的行操作，结果是准确的，自动统计一个行数可以直接查询（不加where）
（2）Innodb：聚簇索引和非聚簇索引，优化事选择小的非聚簇索引查询（坑）
-
max/min：最大最小值，max(字段) 最近一天 -
avg(字段)：平均数 在做/运算的时候可以考虑使用avg -
round(字段,保留位数)：保留小数位数 -
least(字段1,字段2...)/greatest(字段1,字段2...)：多个字段中找到最大值最小值应用场景1：在双向关系中找到其中一方的所有联系人（eg：1和2是朋友，3和1是朋友，找到1的所有朋友）
- 平均工资：部门与公司比较
select from_id person1,
to_id person2,
count(1) as call_count,
sum(duration) as total_duration
from Calls
group by least(from_id,to_id),greatest(from_id,to_id)
order by from_id;
```sql
select dept_no,group_concat(emp_no) as employees
from dept_emp
group by dept_no;- 页面推荐
select least(user1_id,user2_id) id_1,
greatest(user1_id,user2_id) id_2
from Friendship
order by id_12.2.5.3 Replace函数
#函数原型
update `table_name`
set `field_name` = replace (`field_name`,'from_str','to_str')
WHERE `field_name` LIKE '%from_str%'
#案例实现
update titles_test
set emp_no = replace(emp_no, 10001, 10005)
where id = 5聚合函数
concat(s1,s2)-
concat_ws(分隔符，左，右)：拼接两个字段成一个字段 -
group_concat组聚合函数：
2.2.5.4 With函数
需要从多张子表中做查询时，可以使用with函数让结构更清晰，减少嵌套冗余
-
with a as(), b()：普通with
select from_id person1,
to_id person2,
count(1) as call_count,
sum(duration) as total_duration
from Calls
group by least(from_id,to_id),greatest(from_id,to_id)
order by from_id;-
with recursive as()：循环递归
应用场景：
（1）生成连续自然数
with recursive m as(
select 1 month
union all
select month + 1 from m where month<=11
),（2）生成连续日期
with recursive t(n) as(
select
'2019-01-01'
union all
select date_add(n, interval 1 day) from t where n< '2019-12-31'
)
select * from t;（1）生成日期
1635. Hopper 公司查询 I
# Write your MySQL query statement below
# 问：如何列举出1-12
# 答：使用递归函数
with recursive m as(
select 1 month
union all
select month + 1 from m where month<=11
),
s as(
# 问：为什么两次使用日期函数会报错
# 答：母鸡
# 问：如何统计2020年每个月的累积司机个数（包含2019的数据）
# 答：首先列出1-12月，对于每个月找出比2020-月小的所有数据（使用left join中的on函数解决）然后对所有数据做分组统计即可
select
m.month,
# 注意：这里不能count(*)，如果20201月及之前都没有数据这里会有null，使用count(*)会直接对null计数导致错误
# count(*) driver_count
count(d.driver_id) driver_count
from m
left join Drivers d
on 202000+m.month >= date_format(d.join_date,'%Y%m')
group by m.month
),
t as(
select
date_format(r.requested_at,'%c') request_date,
count(*) accpept_count
from Rides r
left join AcceptedRides a on r.ride_id = a.ride_id
where date_format(requested_at,'%Y')='2020' and a.ride_id is not null
group by request_date
)
select
m.month,
s.driver_count active_drivers,
ifnull(t.accpept_count ,0) accepted_rides
from m
left join t on m.month = t.request_date
left join s on m.month = s.month2.2.5.5 窗口函数
基本语法:
函数() over(partition by 分群体 order by 排序) 选择开窗大小
可放置多个变量
select *,
count(id) over (partition by user_id,... order by ...) number（1）排序窗口函数
rank函数
-
rank()：遇到相同的数据算做同级，下一个累加排序 -
dense_rank()：遇到相同的数据算做同级，下一个+1 -
row_number()：按顺序排序，遇到相同的数据也+1
相关题目：累计排序 dense_rank()
select score
dense_rank() over (order by score desc) as 'rank'
from Scores;ps：as ‘rank’ 必须加上双引号否则会报错（应该是识别为关键字了）
# 首先想到的是滑动窗口的思路，无奈不会使用滑动窗口的窗口函数
# num出现的真实id-截至目前同一个num出现的次数 = 一个定值，如果这个定值>3那么就证明它连续出现了三次
select distinct s.Num as ConsecutiveNums
from(
select Num,
row_number() over(order by Id) -
row_number() over(partition by Num order by Id) as serialNum
from Logs
) as s
group by s.Num,s.serialNum
having count(1)>=3;（2）聚合窗口函数
sum(字段) over（partition by 用于分组的列名, order by 用于累加的列名）avgcount应用场景1： 统计value值出现的次数- 统计rnk出现的次数，id=2和id=3的rnk都是1，所以rnk=1出现的次数为2
select s.id,
count(rnk) over(partition by s.rnk) as cs
from(
select *,
(id - row_number() over(order by id)) as rnk
from Stadium
where people >= 100) as smaxmin
select player_id, event_date,
sum(games_played) over(partition by player_id order by event_date) as games_played_so_far
from Activity;（3）滑动窗口函数
-
rows：按行取，从当前行开始，取前n或者后n行，共计算了n+1条数据 -
range n：按值取（order by后面只有一个参数，依赖于这个参数的值），从当前行的值开始，取前n个值或者后n个值
[你要的操作] OVER ( PARTITION BY <用于分组的列名>
ORDER BY <按序叠加的列名>
ROWS <窗口滑动的数据范围> )边界值Current Row 当前行n preceding 表示前2行unbounded preceding 开头n following 后n行unbounded following 结尾
range取特定日期区间range interval 7-1 day preceding 最近7天的值range between interval 1 day preceding and interval 1 day following 前后一天和当天的值
eg：
取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 folowing --共11行
作者：哼哼哼select a.Id, a.Month,
# 注意range和rows之间的区别：
#对于7月rows会取3、4、7月的数据，range会取5、6、7月的数据
sum(a.Salary) over(partition by a.Id order by a.Month range 2 preceding) as Salary
from(
select Id, Month, Salary,
row_number() over(partition by Id order by Id, Month desc) as rnk
from Employee
) a
where a.rnk>1
order by a.Id, a.Month desc;（4）偏移函数
-
lag(x, offset) over()：向前偏移，offset默认值为1 -
lead：向后偏移
只能查找到一行数据，要对截取多行数据做统计分析需要使用rows控制窗口大小
SELECT
customerName,
orderDate,
LEAD(orderDate,1) OVER ( #返回的值是向前一行的orderDate，没有定义后续行，返回NULL。
PARTITION BY customerNumber #根据customerNumber分组
ORDER BY orderDate ) nextOrderDate #根据orderDate排序
FROM
orders
INNER JOIN customers USING (customerNumber);注意事项
（1）窗口函数所产生的结果需要等该条语句运行完毕后才产生，不能在本条语句中使用窗口函数的结果。
select LAT, LON,
count(*) over(partition by LAT,LON) as count_sum
from insurance
where count_sum=1;
（2）窗口函数之间互不干扰（eg：若两者都涉及到parition，第二个函数不会在第一个函数的partition下做partition）
select round(sum(a.TIV_2016), 2) TIV_2016
from(
select *,
count(*) over(partition by TIV_2015) 2015_sum,
count(*) over(partition by LAT, LON) LAT_LON
from insurance
) a
where a.2015_sum>1 and a.LAT_LON<2;2.2.6 Join操作
- 左连接 & 右连接
right join on（left join同理）：右连接，右表不动，根据公共字段，将左表中的字段添加到右表中，没有的字段值记为null - 自连接，笛卡尔积 ：当自己与自己连接时，会默认为连接两张不同的表，所以会n×n配对
- 多表join
#基本思路：先根据client_id筛选出非禁用户，再根据driver_id选出非禁用户 = 乘客和司机都必须未被禁止
# 错误写法：只要d_id或u_id属于非禁用户就能够被选中
# SELECT *
# FROM Trips AS T JOIN Users AS U
# ON (T.client_id = U.users_id OR T.driver_id = U.users_id ) AND U.banned ='No'
select t.request_at as `Day`,
round(
sum(
if(t.status = 'completed',0,1)
)
/
count(t.status),
2
) as `Cancellation Rate`
from Trips as t
join Users as u1 on (t.client_id = u1.users_id and u1.banned ='No')
join Users as u2 on (t.driver_id = u2.users_id and u2.banned ='No')
where t.request_at between '2013-10-01' and '2013-10-03'
group by t.request_at;2.2.7 复杂操作
2.2.7.1 求交集、差集、补集、并集的方法
（1）交集inner join/ join 省略inner
（2）差集left join or right join
#左减右
select *
from l
left join r
on l.id = r.id
where r.id if null（3）补集左连接集合 union 右连接集合 （4）并集 select 语句1 union select 语句2 union将所有数据不重复地列出并排序（并集）select 语句1 union all select 语句1 union all将所有的数据列出不去重
select
t.employee_id
from
(
select
e.employee_id
from
Employees e left join Salaries s
on e.employee_id = s.employee_id
where
s.salary is null
union all
select
s.employee_id
from
Salaries s left join Employees e
on s.employee_id = e.employee_id
where
e.name is null
) t
order by
t.employee_id asc;2.2.7.2 行列互转
- 表格转换问题
（1）列转行：读取每一行数据的时候获取其中某列的元素，将其转变为行，有多少列就有多少个这样的语句
解法：union all
select product_id, 'store1' as store, store1 as price
from Products where store1 is not null
union
select product_id, 'store2' as store, store2 as price
from Products where store2 is not null
union
select product_id, 'store3' as store, store3 as price
from Products where store3 is not null;（2）行转列：
- 情况1： 原表格有基准id（如：product_id,department_id等）
解法：使用基准id聚合，group by+max/min/sum
问：为什么需要使用sum函数？
答：group by的原理是根据基准id形成关于每一个id的一张虚表，如果不适用sum等聚合函数，就只会从每一张虚表中抽出第一行展示，导致错误，而sum函数把所有的数据统计了一遍解决了这一问题。
- 情况2： 原表格无基准id（如：product_id,department_id等）
解法：构造一个基准id，再按照情况1处理
构造id：每出现一次州就需要增加一行
select
max(if(a.continent='America',name,null)) America,
max(if(a.continent='Asia',name,null)) Asia,
max(if(a.continent='Europe',name,null)) Europe
from (select *,
row_number() over(partition by continent order by name) rnk
from Student) a
group by a.rnk
order by America,Asia,Europeps：这里不能使用sum，只能使用max/min
使用sum产生的结果：
select
sum(if(t.continent='America', t.name, null)) America,
sum(if(t.continent='Asia', t.name, null)) Asia,
sum(if(t.continent='Europe', t.name, null)) Europe
from (
select
*,
row_number() over(partition by continent) rnk
from Student
) t
group by t.rnk
使用max输出正确结果：
这是因为我们只需要获得一个名字值而不是将所有的结果加到一起，所以使用max/min就好
- 设计序列构建新表格问题
解法：使用with语句构造一个数值连续的新列+left join tips:result表中出现无中生有的列，且列中的数据为一个逐渐递增的数列，
2.2.7.3 查找前n名
（1）找第一名
方法1：使用max limit，使用两个聚合，在第二个聚合中用limit过滤出最大值，第一个聚合中只要使得count(*)等于第二个输出的最大值就可以了
select project_id
from Project
group by project_id
having count(*) = (
select count(*)
from Project
group by project_id
order by count(*) desc
limit 1
)方法2：使用>=all，做两次聚合操作，第二次聚合操作找出所有计数值，在第二次聚合的基础上筛选出id
select
project_id
from Project
group by project_id
having count(*) >= all(
select
count(*) amount
from Project
group by project_id
)方法3：窗口函数，嵌套太多了。。。
select s.project_id
from(select t.project_id,
rank() over(order by t.employee_count desc)rnk
from(select project_id, count(*) as employee_count
from Project
group by project_id
) t
)s
where s.rnk = 1;（2）找前n名
基本思路：自连接，select的本质是读取每一行数据然后做处理，因此只需要在另一张表中对大于本行数据的salary做count统计，第一名为0，第二名为1，第三名为2
SELECT e1.Salary
FROM Employee AS e1
WHERE 3 >
(SELECT count(DISTINCT e2.Salary)
FROM Employee AS e2
WHERE e1.Salary < e2.Salary AND e1.DepartmentId = e2.DepartmentId) ;2.2.7.4 求最值、中位数
最值（以最大值为例）
- rank函数
- 本条数据大于等于本条数据所有值
- 本条数据等于本表最大值
中位数
方法1：正反序解法
- 排除某些值再进行计算：
max和min要分开写，不然会报错
select avg(a.salary) avg_salary
from salaries a
where a.to_date='9999-01-01'
and a.salary not in (select max(b.salary) from salaries b where b.to_date='9999-01-01')
and a.salary not in (select min(b.salary) from salaries b where b.to_date='9999-01-01');- 刷题通过的题目排名:对排名在前的人计数，形成新列
select a.id,a.number,
(select count(distinct b.number) from passing_number as b where b.number>=a.number) as t_rank
from passing_number as a
order by a.number desc,a.id asc;ps:删去‘distinct’，出现同分情况时排名不同
2.2.7.5 双向关系
方法1：least&greatest函数，参照2.2.5.2
方法2：case when
select
(case
when user1_id=1 then user2_id
when user2_id=1 then user1_id
end) id
from Friendship
where user1_id=1 or user2_id=1方法3：union
select user2_id from Friendship where user1_id=1
union
select user1_id from Friendship where user2_id=1或者颠倒两个字段，直接列举出所有的双向关系
select user1_id, user2_id
from Friendship
union
select user2_id, user1_id
from Friendship
order by user1_id, user2_id2.2.7.6 查询连续n天登录
基本思路：
方法1：row_number按照数据出现顺序创建顺序id（若有id可略），row_number根据不同用户partition和数据出现顺序排序
方法2：自连接做笛卡尔积，找到时间相差1的行，用distinct过滤用户id
方法3：
>相关题目：
> [180. 连续出现的数字](https://leetcode.cn/problems/consecutive-numbers/)
> [601. 体育馆的人流量](https://leetcode.cn/problems/human-traffic-of-stadium/description/)
- 180. 连续出现的数字
方法1：row_number()
```sql
select distinct s.Num as ConsecutiveNums
from(
select Num,
row_number() over() -
row_number() over(partition by Num order by Id) as serialNum
from Logs
) as s
group by s.Num,s.serialNum
having count(1)>=3;方法2：lead()
select distinct t.Num ConsecutiveNums
from(
select
Id,
Num,
Id+2 Id2,
lead(Id,2) over(partition by Num order by Id) Id3
from Logs
) t
where t.Id2 = t.Id3报错：
分析：
Id-row_number() over(partition by Num order by Id) as rnk 两个整数（int）相减的时候，如果其中有一个是unsigned int类型的，那么结果就被当做是unsigned的。产生负数出错，row_number返回的是unsigned的bigint。
解决：
把其中一个unsigned字段强转为signed类型
Id-CAST(row_number() over(partition by Num order by Id) AS SIGNED) as rnk
2.2.8 正则表达式
select *
from Patients
where conditions regexp '^DIAB1|\\sDIAB1';
^：匹配字符串的开头
|：或者
\:转义
\s:空格2.3 更新/修改语句
（1） 更新：update 表名 set 字段=
（2）修改： alter table rename as/to:修改表名 mysl里只适用于altertable
alter table titles_test
rename as titles_2017;
#（ps：’titles_2017‘不能有分号会报错）增加列：
alter table actor
add column create_date datetime not null
default '2020-10-01 00:00:00'
after last_update;建立外键：注意要为字段加上括号
alter table audit
add constraint foreign key (emp_no)
references employees_test(id);2.4 细节与其他规则
- 不能边查询边删除表中的数据，需要建立一个新表
- 通过查询建立新表需要嵌套查询语句，需要为新表命别名，不然会存在冲突
- 判断某数是否为奇数
（1）mod(a, 2) = 1（2）a%2=1（3）a&1=1
2.5 尚未解决的问题
问题：想用distinct筛选出唯一Email与对应的id失败
# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
DELETE from Person
where id not in(select id from (select id, distinct email from Person));2.6 Mysql代码风格
2.7 Mysql的执行顺序
了解mysql的执行顺序可以避免一些逻辑错误，可以使用explain来查询语句的执行方案
explain select ... from... 执行顺序：from->join->on->where->group by->having->聚合函数：min max avg count sum->select 后的各种函数(eg:count) 字段->distinct->order by->limit
问：为什么having可以使用别名呢？
答案：虽然having在select之后执行但mysql就是这样规定的，可以在having中使用别名。。好牵强啊（待填坑）
group by是在select字段之前执行的，所以只能以表中现有的字段聚合，不能按照新产生的字段聚合
3 数据控制 DCL
数据控制语言：可对数据访问权进行控制。它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。
4 事务控制
5 一些必知概念
5.1 存储引擎
共9种，最常见的有3种
- List itemInnoDB
（1）事务安全：提供提交、回滚、崩溃处理的功能。能够锁行，然后非锁地读取行中的数据
（2）混查：可以查询其他类型的表
（3）支持外键约束
（4）支持自动增长列
（5）支持热备：需要配合一些热备工具
（6）缓存池：将索引和数据全部存到缓冲池中，加快查询速度
（7）高性能：所有存储引擎中它的cpu效率最高
- MyISAM
- MEMORY
5.2 主从同步
- 主库db的更新事件(update、insert、delete)被写到binlog 主库创建一个binlog dump thread，把binlog的内容发送到从库
- 从库启动并发起连接，连接到主库
- 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
- 从库启动之后，创建一个SQL线程，从relay
- log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db
5.3 事务
一组数据操作要么全部成功要么全部失败，事务具有
- 原子性
- 一致性
- 隔离性
- 持久性
5.3.1 隔离性
基础概念
脏读：读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。
可重复读：在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。
不可重复读：在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。
幻读：假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。
四种隔离级别
6 面试题
- datetime和timestamp的区别？
datetime | timestamp | |
日期范围 | 1001——9999 年 | 1970——2038 年 |
时区 | 无关 | 有关 |
存储空间 | 8 字节 | 4 字节 |
默认值 | null | current_timestamp |
- varchar和char有什么区别？
varchar：变长，申请最大长度，实际为实际字符长度+1
char：定长，申请多长占多长 - count相关的面试题
1、COUNT有几种用法？
count(1) count() count(字段)
2、COUNT(字段名)和COUNT()的查询结果有什么不同？
count(字段)不计算空值 count()都计算
3、COUNT(1)和COUNT()之间有什么不同？
count()包括了所有的列，count(1)包括了忽略所有列，计算结果相同
4、COUNT(1)和COUNT()之间的效率哪个更高？
效率一致，count(字段)因为要过滤空值反而效率比较低
5、为什么《阿里巴巴Java开发手册》建议使用COUNT()
不过滤空值
6、MySQL的MyISAM引擎对COUNT()做了哪些优化？
MyISAM：单独记录行总数可供直接查询
7、MySQL的InnoDB引擎对COUNT()做了哪些优化？
选择一个成本较低的索引（非聚簇索引）：聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值
8、上面提到的MySQL对COUNT()做的优化，有一个关键的前提是什么？
不要有where，InnoDB还要求不要group by
9、SELECT COUNT() 的时候，加不加where条件有差别吗？
有差别，加了where会过滤一些值
10、COUNT()、COUNT(1)和COUNT(字段名)的执行过程是怎样的？
count(*)是对所有计数 count(字段)筛出非空值然后计算
哈哈太虐了:)
- exist和in的区别？
in：先执行子查询，再做主查询，小表驱动大表，返回真实值。如果主查询中的表较大且又有索引时应该用in
exist：先查主表再查询子表，返回值True或False。如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists
not in：内外表都进行全表扫描，没有用到索引
not exists：子查询依然能用到表上的索引
无论那个表大，用not exists都比not in要快 - truncate、delete与drop区别？
truncate | delete | drop | |
回滚 | 可回滚 | 不可回滚 | 不可回滚 |
删除内容 | 表结构还在，删除表的全部或者一部分数据行 | 表结构还在，删除表中的所有数据 | 表结构也会删除，所有的数据行，索引和权限也会被删除 |
删除速度 | 删除速度慢，逐行删除 | 删除速度快 | 删除速度最快 |
- union与union all的区别？
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
union all 要比union快很多 - group by 和 distinct 的区别？
distinct：先获取结果集，再去重复记录
group by：基于KEY先分组，再返回计算结果
执行效率无明显差异
推崇group by：语义更为清晰，进行更为复杂的一些处理 - Blob和text有什么区别？（不熟悉）
- 常见的存储引擎有哪些？
- 说一说InnoDB与MyISAM的区别？
- 常见的存储引擎有哪些？
- bin log/redo log/undo log是什么？
- bin log和redo log有什么区别？
- 说一下数据库的三大范式？
- 什么是存储过程？有哪些优缺点？
- 超大分页怎么处理?
- 一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
- 日常开发中你是怎么优化SQL的？
- 添加合适索引
- 优化表结构
（1）数字型字段优于字符串类型
（2）数据类型更小：磁盘的空间，内存和CPU缓存（eg：TIMESTAMP>DATETIME，只需要 DATETIME 类型的一半）
（3）尽量使用 NOT NULL - 优化查询语句
（1）是否有冗余字段/行。
（2）优化索引结构，看是否可以适当添加索引
（3）如果表数据量太大，考虑分表
分析SQl执行计划，是否命中索引等。
（4）数据库主从分离，读写分离 - 分析语句的执行计划
