MySQL 实例练习---day05

Until you make peace with who you are,you’ll never be content with what you have.
只有与真实的自己和平相处，才能对自己所拥有的感到满足。——Doris Mortman

SQL39

• 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005，使用强制索引

drop table if exists salaries;
CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`));
create index idx_emp_no on salaries(emp_no);
INSERT INTO salaries VALUES(10005,78228,'1989-09-12','1990-09-12');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');

SELECT * FROM salaries
    FORCE INDEX (idx_emp_no) WHERE emp_no = 10005;

SQL40

• 在last_update后面新增加一列名字为create_date

ALTER TABLE actor ADD create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00'

MySQL 数据表添加字段的 3 种方式

1、在末尾添加字段（默认）

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件];

2、在开头添加字段
MySQL 默认在表的最后位置添加新字段，如果要在表开头位置（第一列的前面）添加新字段，可以使用 FIRST 关键字，一般放在语句的末尾

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

3、在中间位置添加字段
使用 AFTER 关键字，只能在某个已有字段的后面添加新字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已有字段>;

SQL41

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

当对某张表进行增删改操作时，可以使用触发器自定义关联数据操作

CREATE TRIGGER audit_log AFTER INSERT ON employees_test FOR each ROW
BEGIN
    INSERT INTO audit (EMP_no, NAME) VALUES(NEW.ID, NEW.NAME);
END;

SQL42

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

DELETE  FROM titles_test
WHERE id NOT IN 
(SELECT min_id FROM
(SELECT MIN(id) min_id FROM titles_test
GROUP BY emp_no ) t1
);

SQL43

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

SQL44

• 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错。

replace(str, search_str, replace_str)：在 str 字段中用 replace_str 替换 search_str

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

SQL45

• 将titles_test表名修改为titles_2017

修改表名：rename table 表名 to 新表名

RENAME TABLE titles_test TO titles_2017

SQL46

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

基本语法：alter table 从表 add constraint 约束名 foreign key (关联字段) references 主表(关联字段)

ALTER TABLE audit 
ADD CONSTRAINT FOREIGN KEY (emp_no) REFERENCEs employees_test(id)

SQL48

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

UPDATE salaries SET salary = salary+salary*0.1
WHERE salaries.emp_no IN (SELECT emp_no FROM emp_bonus) 
AND salaries.to_date = '9999-01-01' 

修改之前：
修改之后：

SQL50

• 将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)

concat(字段1, 分隔符, 字段2)：将多个字符连接成一个字符串

SELECT CONCAT(last_name, '\'', first_name) AS name FROM employees 

SQL51

• 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt

length(str)函数：统计 str 长度（按照字节）
replace(str, search_str, replace_str)：在 str 字段中用 replace_str 替换 search_str
思路：将逗号分隔的字符串变成连续的字符串，用原来的字符串长度减去替换之后的字符串长度

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

SQL52

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

right(str, length)：在 str 字符串中从右边起取出 length 长度的字符
order by 默认升序排序

SELECT first_name FROM employees ORDER BY RIGHT(first_name,2)

SQL53

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

函数 group_concat(要连接的字段 [se[arator ‘分隔符’])：将 group by 产生的同一个分组中的值连接起来，返回一个字符串

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

SQL54

• 查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary

avg(字段)：统计函数，返回 where 条件的一列的平均值

SELECT AVG(salary) FROM salaries 
WHERE salary NOT IN(
	(SELECT MIN(salary) FROM salaries where to_date = '9999-01-01') , 
    (SELECT MAX(salary) FROM salaries where to_date = '9999-01-01') )
AND to_date = '9999-01-01'

SQL55

• 分页查询employees表，每5行一页，返回第2页的数据
  limit m, n 公式：limit m[ 是每页显示记录数*（第几页 - 1）], n[ 是每页要显示的记录数]

SELECT * FROM employees LIMIT 5, 5

SQL57

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

exists 筛选结果集为 TRUE 的数据项；而 not exists 相反，返回结果集为 FALSE 的数据项

SELECT * FROM employees e
WHERE not EXISTS (select * from dept_emp d where e.emp_no = d.emp_no)