MySQL 基础运维

MySQL架构
连接层
SQL服务层
存储层

mysql分支：percona，mariadb

MySQL查询语法顺序
①SELECT
②FROM
③LEFT JOIN
④ON
⑤WHERE
⑥GROUP BY
⑦HAVING
⑧ORDER BY
⑨LIMIT

docker run --name C7-MySQL --privileged=true -p 3307:3306 -d -v /root:/root -it centos /usr/sbin/init
docker exec -it C7-MySQL /bin/bash

mysql的下载地址：https://dev.mysql.com/downloads/mysql/

MySQL部署

rpm安装（不推荐）、源代码安装、二进制包安装
推荐使用二进制包的安装方式
1.创建mysql用户（删除系统自带的，因为系统自带的无法登陆而且不能执行shell命令）：

groupadd mysql
useradd -r -g mysql mysql

2.软件与目录准备：

tar zxvf mysql-5.7.31-el7-x86_64.tar.gz
ln -s /root/mysql-5.7.31-el7-x86_64 /usr/local/mysql 
mkdir -p /data/my3306/{data,log,run,tmp}
chown -R mysql.mysql /usr/local/mysql 
chown -R mysql.mysql /data

3.准备mysql配置文件/etc/my.cnf

[mysqld]
server-id=1
datadir=/data/my3306/data
tmpdir=/data/my3306/tmp
socket=/data/my3306/run/mysql.sock
pid-file=/data/my3306/run/mysql.pid
log-bin=/data/my3306/log/mysql-bin
log-error=/data/my3306/log/error.log
innodb data home dir=/data/my3306/data
innodb_undo_directory=/data/my3306/log
innodb_log_group_home_dir=/data/my3306/log
slow-query-log=1
slow-query-log-file=/data/my3306/log/slow.log
long_query_time=5

4.初始化mysql数据库

mysql5.6版本：
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/my3306/data --user=mysql

mysql5.7版本：
生成临时密码
/usr/local/mysql/bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/data/my3306/data --user=mysql

不生成临时密码
/usr/local/mysal/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/data/my3306/data --user=mysql

5.复制启动脚本到/etc/init.d/目录

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld # 拷贝过去后要修改参数
vi /etc/init.d/mysqld
basedir=/usr/local/mysql 
datadir=/data/my3306/data

启动mysql服务
service mysqld start

6.修改root用户密码：

无临时密码登录：
mysql-uroot--skip-password 
mysql> alter user root@'localhost'identified by'mysql123';

有临时密码登录：
查看error日志获取临时密码：
mysql-uroot-p临时密码
mysql>alter user root@'localhost'identified by'mysql123';

MySQL日常运维

1、表碎片清理
产生原因：

• 每当删除了一行数据，该行数据所占的空间并不会被释放，而是会变为空白空间
• 当执行插入操作时，MysQL会尝试使用空白空间，但如果插入数据的大小于空白空间大小时，就会形成碎片

解决方法：对空间碎片进行整理合并，消除由于删除或者更新造成的空间浪费

• optimize table 表名;
• alter table 表名 engine=innodb;

建议：
清除碎片操作会暂时锁表，数据量越大，耗费的时间越长，不要在业务高峰期做。
解决办法有两种，第二种只针对 innodb存储引擎

2、mysql的导出和导入

全库导出：
mysqldump -uroot -p密码 -A -F -q --single-transaction --triggers --events --routines > full_backup.sql

导出指定数据库：
mysqldump -uroot -p密码 -B db1,db2 -F -q --single-transaction --events --routines > db_backup.sql

导出指定表：
mysqldump -uroot -p密码 -F -q --single-transaction db1 test > test.sql

在mysql客户端导入数据：mysql > source db_backup.sql
在操作系统下导入数据：mysql -uroot -pmysql123 < db_backup.sql

-A 亦表示--all-databases，导出全部数据库
-B 亦表示--databases db1 db2，导出几个数据库。参数后面所有名字参量都被看作数据库名。

--events, -E 导出事件。
--routines, -R 导出存储过程以及自定义函数。
-q 不缓冲查询，直接导出到标准输出。默认为打开状态。表示导出的时候，不会先把数据放到内存中再导出来，而是直接把数据从表中存放到备份文件，这样就不会挤压掉内存，减少内存消耗。

--single-transaction 该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
参数的作用，设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表。

事务隔离级别 √ 为会发生，×为不会发生
https://blog.csdn.net/zhouym_/article/details/90381606
脏读、不可重复读、幻读。 MySQL默认是可重复读
https://blog.csdn.net/Vincent2014Linux/article/details/89669762
https://blog.csdn.net/lonely_bin/article/details/96175384

脏读
就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
1、如果都未更新你就读取了，或者都更新完才读取，这都不是脏读，因为得到的是更新前的有效值，或完全更新后的值。

2、如果那个用户更新一半你就读取了，也就是说更新了A，正打算要更新B但尚未更新时，就读取了，此时得到的就是脏数据。

避免脏读的办法就是采取事务，使得用户正在更新时锁定数据库，阻止你读取，直至全部完成才让读取。

不可重复读
通俗的讲，一个事务范围内，多次查询某个数据，却得到不同的结果。
与脏读的区别：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但实际上是违反了事务的一致性原则。

幻读
事务在插入已经检查过不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测获取到的数据如同鬼影一般。

3、binlog日志管理

管理内容	管理命令
开启binlog	my.ini的mysqld配置项下面加上log_bin=mysql_bin
查看当前存在哪些binlog	show master logs;
清除某个日期前的binlog日志	purge binary logs before ‘yyyy-mm-dd hh24:mi:ss’ ;
清除某个二进制日志前的binlog日志	purge binary logs to ‘二进制日志名称’;
自动清理binlog日志	设置expire_logs_days参数，set global expire_logs_days=7;flush logs;
查看某个二进制日志内部的事件	show binlog events in ‘二进制日志名称’
导出某个二进制日志执行的SQL语句	mysqlbinlog -v --base64-output=decode-rows 二进制日志名称

4、索引
查看冗余的索引

select table_schema,table_name,redundant_index_name,redundant_index_columns from sys.schema_redundant_indexes;

查看未使用过的索引

select * from sys.schema_unused_indexes;

5、常见信息查询

Table Cache命中率：如果设置太小的话，每次打开表，都要重新打开数据文件，在高并发会形成冲突

MySQL性能优化

关闭NUMA
尽量选择大的内存
最好能选用SSD盘
使用RAID1+0的模式
阵列卡选用WB的写入策略
电源模式设置为最大性能模式
Memory Speed设置为最大性能模式

操作系统优化

• 磁盘调度策略选用deadline
• 文件系统使用xfs或者ext4
• 文件系统挂载加上noatime、nobarrier
• vi /etc/security/limits.conf
  soft nproc 16384
hard nproc 16384
soft nofile 65535
hard nofile 65535

操作系统参数优化

net.ipv4.tcp_tw_resuse = 1  # 1表示开启复用，允许TIME_WAIT socket重新用于新的TCP连接，0表关闭
net.ipv4.tcp_tw_recycle = 1  # 1表示开启TIME_WAIT socket快速回收，0表示关闭
net.ipv4.tcp_fin_timeout = 30  # 表示TIME_WAIT超时时间，默认是60s
net.ipv4.tcp_max_tw_buckets = 4096  # 系统保持TIME_WAIT socket最大数量
net.ipv4.tcp_max_syn_backlog = 4096  # 进入SYN队列最大长度，加大队列长度可容纳更多的等待连接
vm.swapiness=10  # 减少使用swap的几率
vm.dirty_ratio=15 # 如果脏页数据超过这个数量，新的IO请求将会被阻挡，直到脏数据被写进磁盘。
vm.dirty_background_ratio=3  # 及时清理脏页数据，避免由于出现大量脏页时，新的IO请求被阻塞

MySQL重要参数优化
查询缓存0关了，查询较多的时候可以提高性能。但如果写操作比较多的话，查询缓存不仅不会提高性能，还会导致系统不稳定

query_cache_type:0
query_cache_size:0
skip_name_resolve:1
tx_isolation:read_committed
interactive_timeout:默认8小时，根据业务需求进行修改
wait_timeout:默认8小时，根据业务需求进行修改
max_connections:根据业务并发度进行修改
max_allowed_packet:128M
table_open_cache:根据业务并发度进行修改

thread_cache:根据业务并发度修改
innodb_flush_log_at_trx_commit:1
刷新日志的方式设为1，每次提交事务的时候都能确保把日志写到硬盘中。
innodb_flush_method:O_DIRECT
innodb_io_capacity:根据磁盘系统的iops设置
innodb_buffer_pool_size:内存的70%左右
innodb_data_file_path:ibdata1:1G:autoxtend
innodb_log_file_size:500M或者1G

表设计优化

1、选择Innodb作为存储引擎
2、使用自增ID或者UNSIGNED整型作为主键
3、建议不适用外键，使用应用程序实现完整性
4、建议把字段定义为NOT NULL并设默认值
5、建议不要在数据库中存放text、blob等大字段
6、金钱、日期时间、IPV4尽量使用int类型来保存
7、使用varchar(20)存储手机号，不要使用整数
8、建议不要使用存储过程、触发器、函数、视图、事件等高级功能

SQL语句优化
慢查询日志设置

show variables like "%slow_query_log%";
mysql> show variables like "%slow_query_log%";
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/aliyun-slow.log |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

mysql> set global slow_query_log=on;  
slow_query_log:慢查询是否开启
long_query_time:超过多长时间记录
slow_query_log_file:慢查询文件路径与名称
log_queries_not_using_indexes:是否没有使用索引的sql

vim /etc/my.cnf 修改mysql配置文件保证永久生效
-----------------------------------
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/slow-query.log
long_query_time=1　　　　　　　　　　 # 慢查询日志的时间定义（秒），默认为10秒，多久就算慢查询的日志
log_queries_not_using_indexes=1    # 将所有没有使用带索引的查询语句全部写到慢查询日志中

慢查询分析工具

• mysqldumpslow
• pt-query-digest

使用mysqldumpslow分析慢查询日志

·获取执行次数最多的前5条sql
mysqldumpslow -s c -t 5 /mylog/my3306/slow.log
·获取返回记录最多的前5条sql
mysqldumpslow -s r -t 5 /mylog/my3306/slow.log
·获取执行时间最久的前5条sql
mysqldumpslow -s t -t 5 /mylog/my3306/slow.log
·获取执行次数最久的前5条sql，并且不把里面的数字和字符串抽象成“N”
mysqldumpslow -s c -a -t 5 /mylog/my3306/slow.log

使用pt-query-digest分析慢查询日志

·直接分析慢查询文件
pt-query-digest /mylog/my3306/slow.log
·分析最近5小时内的查询
pt-query-digest --since=5h /mylog/my3306/slow.log
·分析制定时间范围内的查询
pt-query-digest --since ‘2020-07-26 22:43:00’ --until ‘2020-07-28 11:30:00’ /mylog/my3306/slow.log
·分析指含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint}=~m/^select/i' /mylog/my3306/slow.log

 

查看哪些语句使用了全表扫描
select * from sys.statements_with_full_table_scans;

查看哪些语句使用了文件排序
select * from sys.statements_with_sorting;

SQL语句优化步骤：
①使用profile获取执行详情
②使用explain获取执行计划
③执行计划动作
④使用profile获取执行详情
 

①使用profile获取执行详情

打开profile
mysql> set profiling=1;

执行sql语句后可以获取到对应的profile
获取profile
mysql> show profiles;

查看特定profile的详细情况
mysql> show profile for query 1   1是指Query_ID

②使用explain获取执行计划
explain、explain extended、show warnings

mysql> explain select first_seen from sys.statements_with_full_table_scans \G;

explain结果解释

字段名	作用
id	执行顺序
select_type	表示查询的类型
table	输出结果集的表
partitions	匹配的分区
type	表示表的访问路径和连接类型
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	实际使用到的索引里的字节数
ref	进行关联操作时使用的字段
rows	估算扫描出的行数
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明

SQL优化案例

1、子查询优化

SQL语句：
select count(*) from employees as a where exists (select emp_no from dept_emp b where a.emp_no = b.emp_no and b.dept_nod = 'd007');

优化写法：
select count(*) from employees as a,(select distinct emp_no from dept_emp where dept_no = 'd007') b where a.emp_no = b.emp_no;

上面开启了profile后，可以进行show profiles查看执行时间差别

2、表关联优化（最好确保有索引）

3、group by语句优化
确保group by字段上有索引

4、分页查询优化

显示5到10行的记录，即查询6行记录
select * from tablename limit 4,6;

显示第6行的记录
select * from tablename limit 5,1;

查询前n行记录
select * from tablename limit n;

普通写法
select * from employees limit 250000,5000;

优化写法
select * from (select emp_no from employees limit 250000,5000) b,employees a where a.emp_no = b.emp_no;

更优写法
使用 id 限定优化，这种方式假设数据表的id是连续递增的
select * from employees where emp_no > 25000 order by emp_no limit 5000;

5、union优化
默认执行union会进行排序操作，如果确保数据结果无需排序，可以采取union all

 

MySQL常见故障处理

1、mysql无法启动
cat /etc/my.cnf | grep log-error 找到存储错误日志的地方
查看log-error日志获取故障原因

2、乱码问题
（1）检查当前字段值使用的编码类型，通过观察哪个类型显示正常值

mysql> select binary(convert(字段名 using gbk)) from user;
mysql> select binary(convert(字段名 using utf8)) from user;

（2）检查当前字段集设置 character_set_results
show variables like ‘char%’;
因为有可能是character_set_results 和实际存储的字符集格式不匹配

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

（3）修改character_set_results和实际存储的字符集一致

mysql> set character_set_results=gbk;

3、基于gtid的复制中断处理
在从库执行下面的命令

查看从库事务接收和执行情况
mysql> show slave status \G;   从库还可以 show slave logs;
Retrieved_Gtid_Set: fba5d191-13ed-11e9-9bee-000c29f3cfeb:1-18
Executed_Gtid_Set: 084bf09c-18dc-11e9-b043-000c29e4e4c0:1-5,
fba5d191-13ed-11e9-9bee-000c29f3cfeb:1-17

执行下面的命令跳过导致冲突的事务

从上面的错误输出可以开出来 从库已经执行过的事务是’084bf09c-18dc-11e9-b043-000c29e4e4c0:1-5’,执行出错的事务是’fba5d191-13ed-11e9-9bee-000c29f3cfeb:18’，当前主备的数据其实是一致的，可以通过设置gtid_next跳过这个出错的事务。

mysql> set gtid_next='fba5d191-13ed-11e9-9bee-000c29f3cfeb:18';

mysql> begin;
mysql> commit;
mysql> set gtid_next='AUTOMATIC';
mysql> start slave;

当然也可以跳过多个事物 假如主从都执行了 几个事物 都成功了 ，数据已经一致了 只不过主从中断了
在可以在从库上面
reset master；
set global gtid_purged=‘fba5d191-13ed-11e9-9bee-000c29f3cfeb:1-18’
此时从库的Executed_Gtid_Set已经包含了主库上’1-18’的事务，再开启复制会从后面的事务开始执行，就不会出错了。
 
mysql> start slave;
使用gtid_next和gtid_purged修复复制错误的前提是，跳过那些事务后仍可以确保主备数据一致。如果做不到，就要考虑pt-table-sync或者拉备份的方式了。

等等，慢慢补充

MySQL基础语句练习

连基础语句都不会，还运维？
https://www.nowcoder.com/ta/sql

1、查找最晚入职员工的所有信息

select * from employees where hire_date in (select max(hire_date) from employees);

2、查找入职员工时间排名倒数第三的员工所有信息

select * from employees where hire_date = (select hire_date from employees order by hire_date desc limit 2,1);

3、查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no

SELECT a.*, b.dept_no
FROM salaries AS a JOIN dept_manager AS b ON a.emp_no = b.emp_no
WHERE b.to_date = '9999-01-01' AND a.to_date = '9999-01-01'
ORDER BY a.emp_no ASC

JOIN的用法：默认是Inner join
解释：产生的结果是A和B的交集（相同列里面的相同值）

4、查找所有已经分配部门的员工的last_name和first_name以及dept_no

select a.last_name,a.first_name,b.dept_no from employees a,dept_emp b
where a.emp_no = b.emp_no;

5、查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工

LEFT JOIN（左连接）：获取左表employees所有记录，即使右表dept_emp没有对应匹配的dept_no记录。

SELECT
    e.last_name,e.first_name,d.dept_no 
FROM 
    employees e
LEFT JOIN 
    dept_emp d
ON 
    e.emp_no = d.emp_no;

注意on与where有什么区别，两个表连接时用on，在使用left jion时，on和where条件的区别如下：

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2. where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

1.where查询条件，on内外连接时候用,as作为别名,in查询某值是否在某条件里
2.INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
3.LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
4.RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

6、查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

select a.emp_no,a.salary 
from salaries as a,employees as b 
where a.emp_no=b.emp_no and a.from_date=b.hire_date 
order by a.emp_no desc;

7、查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

SELECT emp_no,COUNT(salary) AS t
FROM salaries
GROUP BY emp_no having t > 15;

8、找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

select distinct(salary) from salaries where to_date='9999-01-01' order by salary desc; 

9、获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

select d.dept_no,s.emp_no,s.salary 
from dept_manager as d,salaries as s 
where d.emp_no = s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01' 

10、获取所有非manager的员工emp_no

解析：即employees 里的emp_no不在dept_manager 出现非manager了，关键使用not in
select a.emp_no
from employees a
where a.emp_no not in (select b.emp_no from  dept_manager b)

11、获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。

select a.emp_no,b.emp_no from dept_emp as a,dept_manager as b 
where a.dept_no = b.dept_no and a.emp_no != b.emp_no
AND a.to_date = '9999-01-01'
AND b.to_date = '9999-01-01';

12、获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列。

select d.dept_no,s.emp_no,max(s.salary) 
from dept_emp as d,salaries as s 
where d.emp_no = s.emp_no 
and d.to_date = '9999-01-01' 
and s.to_date='9999-01-01' 
group by d.dept_no ;

13、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

select title,count(title) as t 
from titles 
group by title 
having t>=2;

14、从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略

SELECT title, COUNT(DISTINCT emp_no) AS t FROM titles
GROUP BY title HAVING t >= 2

15、查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

SELECT *
FROM employees
WHERE emp_no%2 =1
AND last_name !='Mary'
ORDER BY hire_date desc;

16、统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

select t.title as title,avg(s.salary) as avg from salaries s,titles t 
where t.to_date='9999-01-01' and s.to_date='9999-01-01'
and s.emp_no = t.emp_no
group by title;

17、获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,max(salary) as salary
from salaries as s
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN(
SELECT MAX(salary)
FROM salaries
WHERE to_date = '9999-01-01'
);

18、查找当前薪水排名第二多的员工编号emp_no

/*首先找到最多工资的一组，然后剔除他，得到的最大值就是第二大的数据*/
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01'
AND s.salary NOT IN(
SELECT MAX(salary)
FROM salaries
WHERE to_date = '9999-01-01'
);

19、查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

SELECT
	e.last_name,
	e.first_name,
	dd.dept_name 
FROM
	employees e
	LEFT JOIN dept_emp d ON e.emp_no = d.emp_no
	LEFT JOIN departments dd ON dd.dept_no = d.dept_no

1、Left Join（左联接）
以左表为中心，返回左表中符合条件的所有记录以及右表中联结字段相等的记录——当右表中无相应联接记录时，返回空值。
2、Right Join（右联接）
以右表为中心，返回右表中符合条件的所有记录以及左表中联结字段相等的记录——当左表中无相应联接记录时，返回空值。
3、Inner Join（等值连接）
返回两个表中联结字段相等的行。

20、查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

select max(salary)-min(salary) as growth
from salaries
where emp_no=10001;

21、查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）

SELECT
	a.emp_no,
	( b.salary - c.salary ) AS growth 
FROM
	employees AS a
	INNER JOIN salaries AS b ON a.emp_no = b.emp_no 
	AND b.to_date = '9999-01-01'
	INNER JOIN salaries AS c ON a.emp_no = c.emp_no 
	AND a.hire_date = c.from_date 
ORDER BY
	growth ASC;

22、统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum

将 每个部门分组，并分别统计工资记录总数，思路如下：
1、用INNER JOIN连接dept_emp表和salaries表，并以dept_emp.no分组，统计每个部门所有员工工资的记录总数
2、再将上表用INNER JOIN连接departments表，限制条件为两表的dept_no相等，找到dept_no与dept_name的对应关系，最后依次输出dept_no、dept_name、sum

SELECT de.dept_no, dp.dept_name, COUNT(s.salary) AS sum 
FROM (dept_emp AS de 
INNER JOIN salaries AS s ON de.emp_no = s.emp_no)
INNER JOIN departments AS dp ON de.dept_no = dp.dept_no 
GROUP BY de.dept_no

23、对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

本题的主要思想是复用salaries表进行比较排名，具体思路如下：
1、从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。

2、本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。

3、千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()

4、最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果

SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01' AND s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC, s1.emp_no ASC

--  ORDER BY rank, s1.emp_no ASC 这样也可以的

24、获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date='9999-01-01’

select d.dept_no,d.emp_no,s.salary 
from salaries as s,dept_emp as d
where s.to_date='9999-01-01' and d.to_date='9999-01-01'
and s.emp_no = d.emp_no
and d.emp_no not in (select emp_no from dept_manager);

25、获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,

创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：
1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录sem
2、再用INNER JOIN连接salaries和demp_manager，建立当前所有员工的工资记录sdm
3、最后用限制条件sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary

SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de 
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem, 
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm 
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

26、
27、
28、查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

33、创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime(‘now’,‘localtime’))

actor_id的主键设置与last_update的默认获取系统时间：
1、在actor_id字段末尾加上PRIMARY KEY是将该字段设置为主键，或者在表的最后一行加上PRIMARY KEY(actor_id)
2、在last_update末尾加上DEFAULT是为该字段设置默认值，且默认值为(datetime(‘now’,‘localtime’))，即获得系统时间，注意最外层的括号不可省略

CREATE TABLE actor
(
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')) -- ,
-- PRIMARY KEY(actor_id)
)

34、对于表actor批量插入如下数据(不能有2条insert语句哦!)

批量插入数据要求在一条语句内完成，以下有两种方法供参考：
方法一：利用VALUES(value1, value2, …), (value1, value2, …), …(value1, value2, …),

INSERT INTO actor 
VALUES (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')

方法二：利用 UNION SELECT 批量插入

INSERT INTO actor
SELECT 1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'
UNION SELECT 2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33'

35、对于表actor批量插入如下数据,如果数据已经存在，请忽略

sqlite3
insert or ignore into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

mysql
insert IGNORE into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

36、创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表

题目使用的是sqlite3，可以这么做：
create table actor_name as
select first_name,last_name from actor;

如果是mysql，那么as可以去掉，也可以不去掉，例如：
create table actor_name
select first_name,last_name from actor;

两条语句完成，先用 CREATE TABLE 语句创建actor_name表，包含first_name与last_name字段，
然后用 INSERT INTO ... SELECT ... 语句向actor_name表插入另一张表中的数据

CREATE TABLE actor_name
(
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
INSERT INTO actor_name SELECT first_name, last_name FROM actor;

37、对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

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

方法一：注意 CREATE VIEW ... AS ... 的 AS 是创建视图语法中的一部分，而后面的两个 AS 只是为字段创建别名
CREATE VIEW actor_name_view AS
SELECT first_name AS first_name_v, last_name AS last_name_v
FROM actor 

方法二：直接在视图名的后面用小括号创建视图中的字段名
CREATE VIEW actor_name_view (first_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor 

39、针对上面的salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005,

SQLite中，使用 INDEXED BY 语句进行强制索引查询，可参考：
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005

MySQL中，使用 FORCE INDEX 语句进行强制索引查询，可参考：
SELECT * FROM salaries FORCE INDEX idx_emp_no WHERE emp_no = 10005

create index idx_emp_no on salaries(emp_no)

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

alter table actor 
add `create_date` datetime not null default '0000-00-00 00:00:00'

默认在末尾列增加
正常情况下，AFTER last_update 写在末尾可以支持的

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

1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
2、触发器执行的内容写出 BEGIN与END 之间
3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录

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

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

先用 GROUP BY 和 MIN() 选出每个 emp_no 分组中最小的 id，
然后用 DELETE FROM ... WHERE ... NOT IN ... 语句删除 “非每个分组最小id对应的所有记录”

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

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';

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

方法一：全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5 冲突，故会替换掉表中 id=5 的记录，否则会插入一条新记录（例如新插入的记录 id = 10）。并且要将所有字段的值写出，否则将置为空。

REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')

方法二：运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过。

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

45、将titles_test表名修改为titles_2017。

ALTER TABLE titles_test RENAME TO titles_2017

46、在audit表上创建外键约束，其emp_no对应employees_test表的主键id(audit已经创建，需要先drop)

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

47、将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

要先选出符合条件的 emp_no，即用 INNER JOIN 连接 salaries 和 emp_bonus，且用 s.to_date = ‘9999-01-01’ 表示当前薪水，然后再用 UPDATE … SET … WHERE … IN … 语句来更新表中数据。

正常思路是：
UPDATE salaries SET salary = salary * 1.1 WHERE emp_no IN
(SELECT s.emp_no FROM salaries AS s INNER JOIN emp_bonus AS eb 
ON s.emp_no = eb.emp_no AND s.to_date = '9999-01-01')

但这里写了emp_bonus里面的emp_no都是当前获奖的所有员工
update salaries set salary=salary*1.1 where emp_no in (
    select emp_no from emp_bonus
) and to_date='9999-01-01'

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

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

SELECT last_name || "'" || first_name FROM employees

mysql中用concat()连接字符串
select concat(last_name, "'", first_name) as namefrom employees;

51、查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
本题用length()函数与replace()函数的结合灵活地解决了统计子串出现次数的问题，属于技巧题，即先用replace函数将原串中出现的子串用空串替换，再用原串长度减去替换后字符串的长度

SELECT (length("10,A,B")-length(replace("10,A,B",",","")))

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

本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

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

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

SELECT dept_no,group_concat(emp_no) employees
FROM dept_emp GROUP BY dept_no

• 聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
• 此函数必须与GROUP BY配合使用。此题以dept_no作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。

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

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

55、分页查询employees表，每5行一页，返回第2页的数据
根据题意，每行5页，返回第2页的数据，即返回第6~10条记录，以下有两种方法可以解决：
方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。

SELECT * FROM employees LIMIT 5 OFFSET 5

方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。

SELECT * FROM employees LIMIT 5,5

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

#筛选条件，没有分配具体部门

select * from employees 
where not exists(
    select emp_no 
    from dept_emp 
    where emp_no = employees.emp_no
);

58、获取employees中的行数据，且这些行也存在于emp_v中
视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

SELECT
	* 
FROM
	employees 
WHERE
	emp_no IN (
	SELECT
		emp_no 
FROM
	emp_v)

62、出现三次以上相同积分的情况

select number from grade group by number
having count(id)>=3

64、找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序

select p.id,p.name,t.content from person as p
left join task as t on p.id=t.person_id
order by p.id

66、牛客网每个人最近的登录日期（一）：写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序

先按照user_id分组，并选出每个组最大的date的情况。 后面再排序

select max(date) from login 
group by user_id order by user_id

72、查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位

select job,round(avg(score),3) avg from grade group by job order by avg desc;
/*
round() 函数用于把数值字段舍入为指定的小数位数。第一个参数是舍入的字段，第二个是位数。
round(avg(score),3) avg。表示avg保留三位小数
group by job表示安装job列分类汇总
order by avg desc。order by从句后跟要排序的列，ASC表示升序排序（默认），DESC表示降序排序
*/

xx题，rank排名

select a.id,a.number,
(select count(distinct b.number) from passing_number b where b.number>=a.number )
 from passing_number a order by a.number desc, a.id asc;