牛客网-数据库SQL实战41-61

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

CREATE TRIGGER audit_log AFTER INSERT ON employees_test
BEGIN
INSERT INTO audit VALUES(NEW.id,NEW.name);
END;

注意构造触发器的语句

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

DELETE FROM titles_test
WHERE emp_no IN (SELECT emp_no FROM titles_test GROUP BY emp_no HAVING count(emp_no)>1)
AND id NOT IN (SELECT MIN(id) FROM titles_test GROUP BY emp_no)

删除记录用DELETE FROM 表名，后面跟筛选条件

43. 将所有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'

更新记录用UPDATE 表名 SET……WHERE

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

UPDATE titles_test 
SET emp_no = REPLACE(emp_no,10001,10005)
WHERE id =5

注意REPLACE 的用法

45. 将titles_test表名修改为titles_2017。

ALTER TABLE titles_test RENAME TO titles_2017

在MYSQL 中 还可以用 RENAME TALBE 旧表名 TO 新表名，SQLite中只能用ALTER......

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

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));

MYSQL 中可以用 ALTER TALBE 表名 ADD FOREIGN KEY......REFERENCE......

47. 存在如下的视图：create view emp_v as select * from employees where emp_no >10005; 如何获取emp_v和employees有相同的数据？

SELECT *
FROM employees
WHERE emp_no>10005

这题太bug了，视图存在的很没价值，视图就是虚拟表，当表用就好了

48.将所有获取奖金的员工当前的薪水增加10%。

UPDATE salaries
SET salary = salary*1.1
WHERE emp_no IN (SELECT emp_no FROM emp_bonus)

注意是更新数据，不是把查找结果加10%

49. 针对库中的所有表生成select count(*)对应的SQL语句

SELECT 'select count(*) from '||name||';'
FROM sqlite_master
WHERE type='table'

SQLite 的系统表是sqlite_master，表的类型是table

MYSQL 中用

select concat('select count(*) from ',t.TABLE_NAME) AS cnts  
from (select TABLE_NAME 
      from information_schema.TABLES
      where TABLE_SCHEMA = database()) t;

50. 将employees表中的所有员工的last_name和first_name通过(')连接起来。

SELECT  (last_name||"'"||first_name) AS name
FROM employees

51. 查找字符串'10,A,B' 中逗号','出现的次数cnt。

SELECT LENGTH('10,A,B')-LENGTH(REPLACE('10,A,B',',',''))

52. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

SELECT first_name
FROM employees
ORDER BY SUBSTR(first_name,-2,2)

SQLite 中截取字符串长度的函数是 SUBSTR（被截取的字符串，开始位置，截取长度）

MYSQL中可以用MID( )，LEFT( )，RIGHT( )等

53. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

SELECT dept_no,GROUP_CONCAT(emp_no)
FROM dept_emp 
GROUP BY dept_no

注意GROUP_CONCAT的用法

54. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

SELECT AVG(salary)
FROM salaries
WHERE salary not in (SELECT MAX(salary) FROM salaries )
AND salary not in (SELECT MIN(salary) FROM salaries )
AND to_date ='9999-01-01'

55. 分页查询employees表，每5行一页，返回第2页的数据

SELECT *
FROM employees
LIMIT 5,5

其实是手动分页。。。

56. 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和recevied，没有分配具体的员工不显示

SELECT d.emp_no,d.dept_no,b.btype,b.recevied
FROM dept_emp d LEFT JOIN emp_bonus b ON d.emp_no = b.emp_no 

57. 使用含有关键字exists查找未分配具体部门的员工的所有信息。

SELECT e.*
FROM employees e
WHERE NOT EXISTS(SELECT d.* FROM dept_emp d WHERE e.emp_no = d.emp_no )

注意EXISTS的用法

58. 重复47题

59. 获取有奖金的员工相关信息。

SELECT e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
(CASE WHEN b.btype =1 THEN s.salary*0.1
 WHEN b.btype =2 THEN s.salary*0.2
 ELSE  s.salary*0.3
 END) AS bonus
FROM employees e,emp_bonus b,salaries s
WHERE e.emp_no = b.emp_no AND e.emp_no = s.emp_no AND s.to_date = '9999-01-01'

60. 按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。

SELECT s1.emp_no,s1.salary,(SELECT SUM(s2.salary) 
                            FROM salaries s2 
                            WHERE s2.emp_no<=s1.emp_no AND s2.to_date = '9999-01-01') AS running_total
FROM salaries s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.emp_no

61. 对于employees表中，按first_name升序排序后，给出奇数行的first_name

SELECT e2.first_name
FROM employees e2
WHERE (SELECT COUNT(*) FROM employees e1 WHERE e2.first_name<=e1.first_name)%2=1

注意用COUNT来表示序号