SQL刷题及练习总结

一、重要or模糊知识点总结

1. limit [位置偏移量,]行数 限制查询结果集显示的行数；limit和offset组合时，limit后面只能有一个参数，表示要取的数量，offset表示要跳过的数量；limit x,n意味从x+1行开始返回n行那么第4到第7，共4行，就是在排序后使用limit 3,4。eg：查找入职员工时间排名倒数第三的员工所有信息（limit 1 offset 2）
2. 表1 （left/right）join 表2 on xx=xx，将两表连接起来
3. 排序函数：rank()over(partition by dept_no order by salary desc
4. in，用于查询指定条件范围内的数据，一般为in (xxx,xxx,......)，用括号将条件括起来
5. is null，用于查询空值（NULL），空值不同于0，也不同于null字符串，与='null'不同
6. 模糊查询like：跟在like后面进行数据过滤常用的通配符有%和_，%用来匹配多个字符可以是零个、一个也可以是多个字符，_仅能用来匹配单个字符。
7. and优先级大于or
8. between是包含两边的数值的，可以用!=来去除边界值
9. asc指定该字段升序排序，desc为降序排序，不写则默认为升序排序；order by 后可加多个字段，按照顺序作为排序依据，先满足哪一个
10. order by subject in ('chemistry','physics') , subject, winner （如果在in的括号里，会被标注为1，排在最后面）
11. 1.count（）或sum或avg等聚合函数计算某个字段，如果为空值，会被忽略；2.如果非聚合函数和聚合函数混合引用，必须加group by，否则会报错
12. 用group by 对字段进行分组去重操作（具体过程：1.先对字段进行分区整合，相同的为一个区；2.将字段去重分组。注意分区和分组的区别）
13. 别名中不能含有函数运算的语句，如max（）
14. having 表达式：限定分组聚合后的查询行必须满足的条件（对聚合函数进行筛选）（where是对聚合分组前进行筛选）
15. round(x,y)——四舍五入函数：对x值进行四舍五入，精确到小数点后y位；y为负值时，保留小数点左边相应的位数为0，不进行四舍五入。例如：round(3.15,1)返回3.2，round(14.15,-1)返回10
16. concat(s1,s2,...)——连接字符串函数：返回连接参数s1、s2等产生的字符串；任一参数为null时，则返回null；例如：concat('My',' ','SQL')返回My SQL，concat('My',null,'SQL')返回null
17. group_concat(s):将分组后的每一组的字段使用逗号连接起来
18. replace(s,s1,s2)——替换函数：使用字符串s2代替s中所有的s1；例如：replace('MySQLMySQL','SQL','sql')返回MysqlMysql
19. left(s,n)、right(s,n)、substring(s,n,len)——截取字符串一部分的函数：left函数返回字符串s最左边n个字符，right函数返回字符串s最右边n个字符；substring函数返回字符串s从第n个字符起取长度为len的子字符串，n也可以为负值，则从倒数第n个字符起取长度为len的子字符串，没有len值则取从第n个字符起到最后一位。例如：left('abcdefg',3)返回abc，right('abcdefg',3)返回efg，substring('abcdefg',2,3)返回bcd，substring('abcdefg',-2,3)返回fg，substring('abcdefg',2)返回bcdefg
20. cast(x as type)——转换数据类型的函数：将一个类型的x值转换为另一个类型的值，type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数据类型
21. year(date)、month(date)、day(date)——获取年月日的函数：year(date)返回日期格式中的年份，month(date)返回日期格式中的月份，day(date)返回年日期格式中的日份。例如：year('2021-08-03')返回2021，month('2021-08-03')返回8，day('2021-08-03')返回3
22. date_add(date,interval expr type)和date_sub(date,interval expr type)——对指定起始时间进行加减操作：expr用来指定从起始时间添加或减去的时间间隔，type指示expr被解释的方式（可以是second、minute、day、hour、month、year等）。date_add函数对起始时间进行加操作，date_sub函数对起始时间进行减操作。例如：date_add('2021-08-03 23:59:59',interval 1 second)返回2021-08-04 24:00:00，date_sub('2021-08-03 23:59:59',interval 2 month)返回2021-06-03 23:59:59
23. datediff(date1,date2)——计算两个日期之间间隔的天数：由date1-date2计算出间隔的时间，只有date的日期部分参与计算，时间不参与
24. date_format(date,format)——将日期和时间格式化：根据format指定的格式显示date值。例如：date_format('2018-06-01 16:23:12','%b %d %Y %h:%i %p')返回Jun 01 2018 04:23 PM，date_format('2018-06-01 16:23:12','%Y/%d/%m')返回2018/01/06
25. 【条件判断函数】：if(expr,v1,v2)，如果表达式expr是true返回值v1，否则返回v2。例如：if(1<2,'Y','N')返回Y，if(1>2,'Y','N')返回N
26. case expr when v1 then r1 [when v2 then r2] ...[else rn] end：例如：case 2 when 1 then 'one' when 2 then 'two' else 'more' end 返回two，case后面的值为2，与第二条分支语句when后面的值相等相等，因此返回two
27. case when v1 then r1 [when v2 then r2]...[else rn] end：例如：case when 1<0 then 'T' else 'F' end返回F，1<0的结果为false，因此函数返回值为else后面的F
28. 【排序窗口函数】：rank()over()、dense_rank()over()、row_number()over()。1.窗口函数只能写在select语句中；2.partition by只分区不去重，与group by不同；3.无partition by，默认对整个表排序；4.窗口函数里order by是必须要写的；5.rank（）：跳跃式排序，1、1、3、4，可并列第一，dense_rank():并列连续，如1、1、2、3，row_number():连续型排序，只能1、2、3、4，不能有多个第一
29. EXISTS语句：执行employees.length次。指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
30. sum(x)over(partition by x order by x):按照一定顺序逐步求和

二、数分不常考问题集合

1.创建一个actor表：

create table actor(actor_id smallint(5) not null,first_name varchar(45) not null,last_name varchar(45) not null,last_update date not null,primary key(actor_id))

2.对于表actor批量插入如下数据：

insert into actor (actor_id,first_name,last_name,last_update)

values('1','PENELOPE','GUINESS','2006-02-15 12:34:33'),

('2','NICK','WAHLBERG','2006-02-15 12:34:33')

3.插入批量数据：insert ignore into actor values('3','ED','CHASE','2006-02-15 12:34:33')

4.将actor表中的所有first_name以及last_name导入该表：insert into actor_name select first_name,last_name from actor;

5.对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname：alter table actor add unique uniq_idx_firstname(first_name);

alter table actor add index idx_lastname(last_name)

6.针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

create view actor_name_view(first_name_v,last_name_v) as

select first_name,last_name from actor

7.针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005，使用强制索引：select * from salaries force index (idx_emp_no) where emp_no=10005

8.在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'2020-10-01 00:00:00'：

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

9.构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中：

create trigger audit_log after insert on employees_test

for each row

begin insert into audit values(new.id,new.name);

End

10.删除emp_no重复的记录，只保留最小的id对应的记录：

delete from titles_test

where id not in (

    select * from ( select min(id) from titles_test group by emp_no) a)

11.将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01：

update titles_test set to_date=null,from_date='2001-01-01'

where to_date='9999-01-01'

12.将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现：

upate titles_test set emp_no=replace(emp_no,10001,10005)

where id=5

13.将titles_test表名修改为titles_2017：

alter table titles_test rename to titles_2017

14.在audit表上创建外键约束，其emp_no对应employees_test表的主键id：

alter table audit add constraint foreign key(emp_no) references employees_test(id)

15.将所有获取奖金的员工当前的(salaries.to_date='9999-01-01')薪水增加10%：

update salaries set salary=salary*1.1

where salaries.to_date='9999-01-01'

and salaries.emp_no in (select emp_no from emp_bonus )

16.统计每个字符串中逗号出现的次数cnt：
select id,length(string)-length(replace(string,',','')) cnt from strings

17.分页查询employees表，每5行一页，返回第2页的数据：
select * from employees limit 5,5

18.按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和：
select emp_no,salary,sum(salary) over(order by emp_no) running_total from salaries

where  to_date = '9999-01-01'