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/rpm安装（不推荐）、源代码安装、二进制包安装 推荐使用二进制包的安装方式 1.创建mysql用户（删除系统自带的，因为系统自带的无法登陆而且不能执行shell命令）：
groupadd mysql useradd -r -g mysql mysql2.软件与目录准备：
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 /data3.准备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=54.初始化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=mysql5.复制启动脚本到/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';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日志管理
管理内容管理命令开启binlogmy.ini的mysqld配置项下面加上log_bin=mysql_bin查看当前存在哪些binlogshow 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命中率：如果设置太小的话，每次打开表，都要重新打开数据文件，在高并发会形成冲突
关闭NUMA 尽量选择大的内存 最好能选用SSD盘 使用RAID1+0的模式 阵列卡选用WB的写入策略 电源模式设置为最大性能模式 Memory Speed设置为最大性能模式
操作系统优化
磁盘调度策略选用deadline文件系统使用xfs或者ext4文件系统挂载加上noatime、nobarriervi /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 # 将所有没有使用带索引的查询语句全部写到慢查询日志中慢查询分析工具
mysqldumpslowpt-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执行情况的描述和说明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
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或者拉备份的方式了。
等等，慢慢补充
连基础语句都不会，还运维？ 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 ASCJOIN的用法：默认是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条件的区别如下：
on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。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 >= 215、查找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_no1、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_no23、对所有员工的当前(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.salary26、 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 actor39、针对上面的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 = 545、将titles_test表名修改为titles_2017。
ALTER TABLE titles_test RENAME TO titles_201746、在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,557、使用含有关键字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)>=364、找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照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.id66、牛客网每个人最近的登录日期（一）：写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序
先按照user_id分组，并选出每个组最大的date的情况。 后面再排序
select max(date) from login group by user_id order by user_id72、查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面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;