数据库常见易错点（使用MySQL语法）

1. 左连接，右连接，用on表示连接条件

2. 取表中间某几条记录：limit（行偏移量《初始为0》，返回记录总数）

    TopN问题 Select * from table limit 0,1 (取第一条记录)

3. ifnull(a,b) 判断a是否为空，如果不空输出a的值，否则输出b的值

4. order by默认升序，一般放在最后，但可以在limit之前

5. T-SQL自定义函数

6. group by 分组，分组之后select子句的列名列表中只能出现分组属性和聚集函数，having用于筛选出符合条件的组

7. where执行早于group by早于having, 也就是说where可以在分组前先筛选记录

8. 常用聚集函数：count() sum() avg() max() min()，where 语句中不能使用聚集函数

9. 嵌套查询，外层为父查询，内层为子查询，子查询不能用 order by 语句

10. 考虑一下多表的链接

select distinct l1.Num 'ConsecutiveNums'
from Logs l1
inner join Logs l2 on l1.id+1=l2.id
inner join Logs l3 on l1.id+2=l3.id
where l1.Num=l2.Num and l1.Num=l3.Num

11. MySQL8.0 以后开始支持窗口函数，一般用于 select 语句，rand() over, dense_rank() over, row_number() over 都有分类排序的功能，主要区别在于对并列情况如何处理，如有成绩 100 的学生 3 名，99 的 1 名：Rand() 1,1,1,4 dense_rank () 1,1,1,2 row_number() 1,2,3,4

select s.Score as Score, (dense_rank() over(order by Score desc)) as 'Rank'
from Scores s

12. 聚合窗口函数 sum(成绩) over(order by 学号) 对排序中位于自己之上的所有记录求和

13. Row_number() over(partition by a order by b) 分组排序功能，根据 a 列分组，b 列排序，返回值是一个整数

14. Not exists, 用于 where 语句后，子查询返回的一定是 true/false, 所以子查询通常用 select *

15. 创建视图

create view 视图名（列名1，列名2，...）
as
子查询

16. from 后也可以写 select 语句，将其当作一个新的表，执行效率可能比嵌套表查询更高

select s.Department, s.Employee, s.Salary
from
(
 select d.Name 'Department',e.Name 'Employee',e.Salary 'Salary',
 (dense_rank() over(partition by DepartmentId order by Salary desc)) 'RK'
 from Employee e 
 left join Department d on e.DepartmentId=d.Id
) as s
where s.RK=1

17. 通过 SQL，您如何按字母顺序选取 Persons 表中 LastName 介于 Adams 和 Carter 的所有记 录

错误回答：SELECT * FROM Persons WHERE LastName>'Adams' AND LastName<'Carter'

正确答案：SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

18. 注意中间逗号

select s.Score as Score,

(select count(distinct b.Score) from Scores b where b.Score >= s.Score) as `Rank`

from Scores s

order by Score desc

19. 日期处理函数

      日期相减，返回天数：datediff(date1, date2)

      日期排序 order by date, 是按照自然顺序进行排序的

20. 注意使用 from a，b 直接连接两个表时，必须用 where 指明连接条件，left/right/inner/outter join 用 on 指明连接条件

21. Group by 可以同时对两个字段进行分组, Group by X,Y 意思是将具有相同 X 字段值和 Y 字段值的记录放到同一个分组里

22. MySQL 字符串拼接函数 concat(str1,str2,…)

23. insert ignore into actor values (3,'ED','CHASE','2006-02-15 12:34:33'); 插入数据，如果数据已经存在则忽略

24. You have an error in your SQL syntax; 出现这种错误一般都是多个标点符号之类的问题

25. 将某个表的数据导入另一个表

insert into actor_name 
select first_name,last_name from actor 

26. 创建唯一索引，create unique index 索引名 on 表名(列名 asc); 对某列创建唯一索引相当于给其 加了一个唯一性约束

创建普通索引 create index idx_lastname on actor(last_name);

使用强制索引查询 select * from salaries force index (idx_emp_no) where emp_no=10005

27. 新增加列，并设置默认值

alter table actor 
add create_date datetime NOT NULL default '2020-10-01 00:00:00'

28. MySQL 创建触发器

create trigger audit_log after insert
on employees_test for each row
begin
 insert into audit values(NEW.ID,NEW.NAME);
end

 NEW 与 OLD 相当于 inserted 与 deleted 临时表

注意：begin end 之间的代码块要写分号，否则测试不通过

29. 报错：You can't specify target table 'titles_test' for update in FROM clause. 意思是不能先 select 出同一表中的某些值，然后在同一语句中更改这个表，修改方式：把用到 titles_test 这个表的 查询作为中间表

错误代码：

delete from titles_test
where id not in （
   select min(id) 
   from titles_test
   group by emp_no
）

正确代码： 

delete from titles_test t1
where t1.id not in (
   select t.min_id
   from 
   (
      select min(id) 'min_id'
      from titles_test t2
      group by t2.emp_no
   ) as t
)

30. 更新语句中，set 中间用逗号，不要用 and

update titles_test
set to_date=NULL, from_date='2001-01-01'
where to_date='9999-01-01'

31. 

replace into titles_test 
values (5,'10005','Senior Engineer', '1986-06-26', '9999-01-01');

 replace(字段，旧值，新值)

select length("10,A,B")-length(replace("10,A,B", ',' , ''))
length() 统计字符长度

32. 修改表名 alter table titles_test rename titles_2017

33. 新增外键约束 alter table audit add foreign key(EMP_no) references employees_test(ID)

34. substr(str,start,length) 截取字符串子串

Start：开始位置，初始位置为 1，当 start 为负数时，表示从结尾开始往前数

Length：可选，截取子串长度，不设置即为返回字符串结尾前的全部字符

select substr('abcdefg',3,4) from dual; 结果是 cdef

select substr('abcdefg',-3,4) from dual; 结果 efg

35．聚集函数 group_concat(连接字段，连接字符) 连接字符默认为逗号，通常和 group by 联合使用

36. 

case
When 条件 1 then 结果 1
When 条件 2 then 结果 2
Else 结果 N
End

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,
(
 case
 when eb.btype=1 then s.salary*0.1
 when eb.btype=2 then s.salary*0.2
 else s.salary*0.3
 end
) 'bonus'
from employees e
inner join emp_bonus eb on e.emp_no=eb.emp_no 
inner join salaries s on s.emp_no=e.emp_no
where s.to_date='9999-01-01'

37. round() 四舍五入，floor() 向下取整， ceiling() 向上取整

38. having 后面的判断字段必须是聚合函数返回的结果