速成SQL（Base on Mysql）

一、Docker compose 快速拉起mysql 单机实例

 1# 创建目录
 2# mkdir -p ./.etc ./.data
 3
 4# 容器内mysqld.cnf 文件参数优化：
 5# cat .etc/my.cnf
 6[mysqld]
 7character-set-server=utf8
 8log-bin=mysql-bin
 9server-id=1
10pid-file        = /var/run/mysqld/mysqld.pid
11socket          = /var/run/mysqld/mysqld.sock
12datadir         = /var/lib/mysql
13sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
14symbolic-links=0
15secure_file_priv =
16wait_timeout=120
17interactive_timeout=120
18default-time_zone = '+8:00'
19skip-external-locking
20skip-name-resolve
21open_files_limit = 10240
22max_connections = 1000
23max_connect_errors = 6000
24table_open_cache = 800
25max_allowed_packet = 40m
26sort_buffer_size = 2M
27join_buffer_size = 1M
28thread_cache_size = 32
29query_cache_size = 64M
30transaction_isolation = READ-COMMITTED
31tmp_table_size = 128M
32max_heap_table_size = 128M
33log-bin = mysql-bin
34sync-binlog = 1
35binlog_format = ROW
36binlog_cache_size = 1M
37key_buffer_size = 128M
38read_buffer_size = 2M
39read_rnd_buffer_size = 4M
40bulk_insert_buffer_size = 64M
41lower_case_table_names = 1
42explicit_defaults_for_timestamp=true
43skip_name_resolve = ON
44event_scheduler = ON
45log_bin_trust_function_creators = 1
46innodb_buffer_pool_size = 512M
47innodb_flush_log_at_trx_commit = 1
48innodb_file_per_table = 1
49innodb_log_buffer_size = 4M
50innodb_log_file_size = 256M
51innodb_max_dirty_pages_pct = 90
52innodb_read_io_threads = 4
53innodb_write_io_threads = 4
54
55# docker-compose文件：
56# cat docker-compose.yaml
57version: '3.1'
58services:
59  db:
60    image: mysql:5.7 #mysql版本
61    volumes:
62      - ./.data/db:/var/lib/mysql
63      - ./.etc/my.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
64    restart: always
65    ports:
66      - 3306:3306
67    environment:
68      MYSQL_ROOT_PASSWORD: 123456 #访问密码
69      secure_file_priv:
70# 新版本docker 使用已经不适用docker-compose 单独命令，docker-compose的编排能力已经集成至docker 命令下。 
71# docker compose up -d

二 、Mysql 创建用户于赋权限

1# 创建用户
2create user 'kid'@'%' identified by 'kidlee123'
3# mysql用户权限赋值
4grant all on *.* to 'kid'@'%';
5revoke all on *.* from 'kid'@'%';

三、Mysql 导入数据

1# CRUD数据模拟导入
2# 下载test.sql
3curl -O
4https://raw.githubusercontent.com/ssslkj123/shuanglu.life/master/post/20240516_SQL%E9%80%9F%E6%88%90/test.sql
5# 导入至库
6mysql -uroot -p123456 -h 192.168.31.14 -P 3306 <  test.sql

四、 随便查点啥

1select 1;
2select 1 as id;

五、 范围查找、逻辑表达式，显示过滤

 1/* 获取recordsets  |  querysets */
 2select  test.e.emp_no, concat( e.first_name , " " , e.last_name) as `fullname`  from test.employees as e  where emp_no !=10003;
 3
 4select  test.e.emp_no, concat( e.first_name , " " , e.last_name) as name  from test.employees as e  where emp_no !=10003;
 5/*  <> SQL 标准的 不等于 ，等价于 !=  */
 6select  test.e.emp_no, concat( e.first_name , " " , e.last_name) as name  from test.employees as e  where emp_no  <> 10003;
 7
 8/*  between */
 9select  test.e.emp_no, concat( e.first_name , " " , e.last_name) as name  from test.employees as e  where emp_no  between  10004 and 10010 ;
10
11/* 拿3条， 跳过前3个。 */
12select   emp_no,concat(first_name," " , last_name) as Fullname  from  employees as e   limit 3 offset 3 ;
13select   emp_no,concat(first_name," " , last_name) as Fullname  from  employees as e   limit 3, 3 ;
14
15/* limit 按照sql 语句需要在where 条件之后  */
16select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees as e limit 3 offset 2 ;

六、模糊查询like

1/* 这里不可以用Fullname 作为 like的条件。 */
2select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees where  first_name like 'C%';
3select   emp_no,concat(first_name," " , last_name) as Fullname  from  employees where  last_name like '_en__' ;
4/*
5% 代表0或者N个字符
6# _ 代表1个字符
7*/

七、 查看sql语句执行计划

 1explain
 2select * from employees;
 3
 4/*
 5Type字段：
 6ALL 全表扫描，效率最低
 7range 范围索引，效率较高
 8index 使用了索引
 9
10rows字段：
11一共有多少行
12
13filtered：
14查找出来了之后过滤了的百分比。
15*/

八、创建索引

 1/* 查询当前的索引 */
 2show indexes from employees;
 3/* 创建索引 */
 4create index last_name_index using btree on employees (last_name);
 5/* %开始则会是全表扫描 */
 6explain  select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees where   last_name like '%B%' ;
 7/* 使用的是索引 */ 
 8explain  select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees where   last_name like 'B%'  ;
 9
10/* 如果不建索引 */
11mysql> explain   select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees where   last_name like 'B%'  ;
12+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
13| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
14+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
15|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    11.11 | Using where |
16+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
171 row in set, 1 warning (0.00 sec)
18/* 创建索引后 */
19
20mysql> explain   select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees where   last_name like 'B%'  ;
21+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
22| id | select_type | table     | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
23+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
24|  1 | SIMPLE      | employees | NULL       | range | last_name_index | last_name_index | 50      | NULL |    3 |   100.00 | Using index condition |
25+----+-------------+-----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
261 row in set, 1 warning (0.00 sec)

九、where条件查询

1select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees  where emp_no in (10001,10002,10004) ;

十、order排序

 1mysql>  select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees  where emp_no  between 10012  and 10020   order by first_name desc ;
 2+--------+----------------------+
 3| emp_no | Fullname             |
 4+--------+----------------------+
 5|  10012 | Patricio Bridgland   |
 6|  10020 | Mayuko Warwick       |
 7|  10019 | Lillian Haddadi      |
 8|  10016 | Kazuhito Cappelletti |
 9|  10018 | Kazuhide Peha        |
10|  10015 | Guoxiang Nooteboom   |
11|  10013 | Eberhardt Terkki     |
12|  10017 | Cristinel Bouloucos  |
13|  10014 | Berni Genin          |
14+--------+----------------------+
159 rows in set (0.00 sec)
16
17mysql> explain select  emp_no,concat(first_name," " , last_name) as Fullname  from  employees  where emp_no  between 10012  and 10020   order by first_name desc ;
18+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
19| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
20+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
21|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where; Using filesort |
22+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
231 row in set, 1 warning (0.00 sec)

十一、复合主键、索引，查询

 1CREATE TABLE `dept_emp` (
 2  `emp_no` int(11) NOT NULL,
 3  `dept_no` char(4) NOT NULL,
 4  `from_date` date NOT NULL,
 5  `to_date` date NOT NULL,
 6  PRIMARY KEY (`emp_no`,`dept_no`),
 7  KEY `dept_no` (`dept_no`),
 8  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
 9  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
10) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11
12/* 这里的PRIMARY KEY (`emp_no`,`dept_no`)  则表示使用了复合主键作为索引。*/
13/* 主键是唯一的、复合主键也是唯一的，但是唯一键不一定是主键。*/
14
15select *  from dept_emp as emp order by dept_no,emp_no ;

十二、distinct 的使用，

1SELECT DISTINCT emp_no FROM salaries;

十三、count、max、min、avg、sum 聚合函数的使用。

 1select  emp_no,max(salary) as max ,avg(salary) as avg  from  (select  emp_no,salary from salaries) as s ;
 2
 3# 不加索引全表扫描：
 4mysql> explain select  emp_no,max(salary) as max ,avg(salary) as avg  from  (select  emp_no,salary from salaries) as s ;
 5+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
 6| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
 7+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
 8|  1 | SIMPLE      | salaries | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   40 |   100.00 | NULL  |
 9+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
101 row in set, 1 warning (0.00 sec)
11
12# 添加salaries列索引
13create index salary_index using btree on salaries(salary);
14
15# 命中索引：
16mysql> explain select  emp_no,max(salary) as max ,avg(salary) as avg  from  (select  emp_no,salary from salaries) as s ;
17+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
18| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
19+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
20|  1 | SIMPLE      | salaries | NULL       | index | NULL          | salary_index | 4       | NULL |   40 |   100.00 | Using index |
21+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
221 row in set, 1 warning (0.00 sec)

十四、group by 分组

 1select emp_no,max(salary),min(salary)  from  salaries group by emp_no;
 2+--------+-------------+-------------+
 3| emp_no | max(salary) | min(salary) |
 4+--------+-------------+-------------+
 5|  10001 |       88958 |       60117 |
 6|  10002 |       72527 |       65828 |
 7|  10003 |       43699 |       40006 |
 8|  10004 |       60770 |       40054 |
 9+--------+-------------+-------------+
104 rows in set (0.00 sec)
11
12SELECT  count(emp_no) as salary_month , SUM(salary) as total_salary, emp_no as  employees_id FROM salaries GROUP BY emp_no;

十五、having 用法

PS：mysql可以将别名放在having 之后，在group by 统计之后，针对查询的不存在的值进行过滤的 条件判断方法，过滤完的结果不可以使用where 条件来进行过滤。

1SELECT emp_no,SUM(salary),AVG(salary) as avg_salary,COUNT(emp_no) FROM salaries  GROUP BY emp_no HAVING  avg_salary > 60000;
2
3SELECT emp_no,SUM(salary),AVG(salary) as avg_salary,COUNT(emp_no) FROM salaries  GROUP BY emp_no HAVING AVG(salary) > 60000;
4
5SELECT emp_no,SUM(salary),AVG(salary) as avg_salary,COUNT(emp_no) FROM salaries  GROUP BY emp_no  HAVING  avg_salary > 49000  ORDER BY  avg_salary  LIMIT 1 OFFSET 1 ;

十六、子查询

 1# 子查询军需要通过() 内查询到结果。
 2# 1、通过sql语句产生一个临时表，从其中进行进一步的查询，由于临时表没有表名所以需要用as产生一个
 3SELECT * FROM  (  SELECT  salary,to_date from salaries where emp_no < 10002  )  as sa  where sa.salary < 70000;
 4
 5# 2、通过in 的方式批量指定条件，这种方式需要确定查询出来的结果只有一类结果，类似golang的切片。
 6/* 查找工资小于60000的并按照员工号进行分组，得到员工ID，将员工ID 作为过滤条件，交给主查询语句进行查询后统计这些数据分别有多少条，并使用员工ID进行分组。输出。*/
 7SELECT  count(emp_no),emp_no,max(salary),min(salary),avg(salary)  FROM  salaries   WHERE emp_no IN ( SELECT emp_no FROM salaries where salary < 60000   GROUP BY emp_no  ) GROUP BY emp_no ; 
 8
 9+---------------+--------+-------------+-------------+-------------+
10| count(emp_no) | emp_no | max(salary) | min(salary) | avg(salary) |
11+---------------+--------+-------------+-------------+-------------+
12|             7 |  10003 |       43699 |       40006 |  43030.2857 |
13|            10 |  10004 |       60770 |       40054 |  49571.7000 |
14+---------------+--------+-------------+-------------+-------------+
15
16/* 找每个人的最高工资的子查询。*/
17SELECT *  FROM salaries WHERE  salary IN  ( SELECT MAX(salary) FROM salaries GROUP BY emp_no  )
18+--------+--------+------------+------------+
19| emp_no | salary | from_date  | to_date    |
20+--------+--------+------------+------------+
21|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
22|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
23|  10003 |  43699 | 2000-12-01 | 2001-12-01 |
24|  10004 |  60770 | 1995-11-29 | 1996-11-28 |
25+--------+--------+------------+------------+
26
27
28# 3、通过 = 的方式来实现单值的表达式。
29
30/* 查找某个人所有薪水当中 最高工资的那条记录
31 SELECT *  FROM salaries WHERE  salary =  ( SELECT MAX(salary) FROM salaries   ) */
32
33# 在= 号之后只可能是单一的值，而不是类似于 IN 的 切片数据类型的数据。
34
35 +--------+--------+------------+------------+
36| emp_no | salary | from_date  | to_date    |
37+--------+--------+------------+------------+
38|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
39+--------+--------+------------+------------+

十七、多表join

 1# 1、 全表连接，笛卡尔乘积（强烈禁止），employees 数据为 20条，salaries 的数据为40条。 此时会产生 full join的效果 产生20*40=800条记录。
 2SELECT COUNT(*)  FROM employees JOIN salaries  ;
 3SELECT COUNT(*)  FROM employees FULL JOIN salaries  ;
 4SELECT *  FROM employees,salaries ;
 5# 2、 内连接 INNER JOIN 
 6
 7# 3、 左连接 这里的左等于是 employees 的表,右边相当于 salaries 表。当出现employees 当中连接不不存在匹配记录的时候则 产生的结果当中会以空来进行替代。 由于右边的表 salaries.emp_no 只有4种不同的值，因此左边的表employees会有20-4=16条记录无法匹配的上，最终结果为16 + 40 = 56条记录出来。
 8SELECT  *  FROM employees  LEFT JOIN salaries ON employees.emp_no = salaries.emp_no ;
 9SELECT  count(*)  FROM employees  LEFT JOIN salaries ON employees.emp_no = salaries.emp_no ;
10SELECT  * FROM employees  LEFT JOIN salaries ON employees.emp_no = salaries.emp_no  WHERE  employees.emp_no >  10003 ;
11SELECT  * FROM employees  LEFT JOIN salaries ON employees.emp_no = salaries.emp_no  WHERE  employees.first_name =  "Bezalel" ;
12# 理解过程如下： 使用left join 之后将两个表产生一个全新的表，再到该全新的表上面 去使用group by等方式进行更进一步的处理。
13# 此外由于是left join 所以会出现salaries_count2 列数值为0 、  MAX(salaries.salary) 列为 Null 的情况。
14SELECT  COUNT(employees.emp_no) as employees_count1 ,COUNT(salaries.emp_no) as salaries_count2 , employees.first_name, MAX(salaries.salary)  FROM employees  LEFT JOIN salaries ON employees.emp_no = salaries.emp_no  WHERE  employees.emp_no >  10002  GROUP BY employees.emp_no ;
15
16
17# 4、 右链接 这里的左等于是 employees 的表,右边相当于 salaries 表。此时以salaries 表为主，当出现左边表内无法 employees.emp_no = salaries.emp_no  的值时，则会将值进行 丢弃处理。此时 会产生 40条记录。
18SELECT  *  FROM employees  RIGHT JOIN salaries ON employees.emp_no = salaries.emp_no ;
19SELECT  count(*)  FROM employees  RIGHT JOIN salaries ON employees.emp_no = salaries.emp_no ;
20
21# 5、 需要注意的是 mysql当中可以不用指定 left join ... on 或者 right join  ... on 而直接使用join ... where 的方式进行 进行表的内联，如：
22SELECT  *  FROM employees  JOIN salaries  WHERE employees.emp_no = salaries.emp_no  and employees.emp_no >  10002   GROUP BY employees.emp_no ;
23# 此时 只使用了join 并没有指定 join的类型， 后通过WHERE 条件 进行了对比，得到结果并进行了group by 操作，由于没有指定Join 类型，此时只会出来2条记录。但是salary的结果逻辑是没有意义的，salary还需要做更多的聚合函数处理。
24SELECT  COUNT(employees.emp_no) as employees_count1 ,COUNT(salaries.emp_no) as salaries_count2 , employees.first_name, MAX(salaries.salary) ,AVG(salaries.salary) , MIN(salaries.salary)FROM employees   JOIN salaries  where employees.emp_no = salaries.emp_no  and employees.emp_no >  10002  GROUP BY employees.emp_no ;
25
26# 6、 自然连接（Natural Join，不建议使用，该方式为非显示逻辑）与使用join where employees.emp_no = salaries.emp_no 类似，会让两个表自动使用主键进行关联，并除空值的部分。 
27SELECT * FROM employees NATURAL JOIN salaries;

特殊表：自连接（同一张表内实现了层级的逻辑关系，如：部门当中领导与同事之间在同一张表当中。）

 1# 创建表：
 2CREATE TABLE `manager` (
 3  `empno` int(11) NOT NULL AUTO_INCREMENT,
 4  `name` varchar(255) DEFAULT NULL,
 5  `mgr` int(11) DEFAULT NULL,
 6  PRIMARY KEY (`empno`)
 7) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
 8
 9# 插入数据模拟：
10INSERT  INTO manager( empno,`name`,mgr ) VALUES (1,"tom",NULL),(2,"jerry",1),(3,"ben",2),(4,"kili",2),(5,"lucy",2)

逻辑关系：

image-20240516215959073

image-20240516220159850 

 1/* 查全表 */
 2SELECT * FROM manager;
 3
 4/* 查询没有mgr id的这个人即为最大的boss */
 5SELECT * FROM manager WHERE mgr IS NOT NULL;
 6
 7/* 这里通过内联查询查询到该表当中的上下级关系，其中jerry 是 tom的 mgr ，jerry 没有上级， jerry是ben的上级 */
 8/* 使用内联模式查询谁是boss */
 9select emp.*,worker.* FROM  manager  emp ,manager  worker WHERE emp.empno = worker.mgr AND emp.empno=1; 
10
11/* 使用内联模式查询谁是底层员工*/
12SELECT emp.*,worker.* FROM manager emp INNER JOIN  manager worker ON emp.empno = worker.mgr WHERE emp.empno = 2  ;
13SELECT worker.* FROM manager emp INNER JOIN  manager worker ON emp.empno = worker.mgr WHERE emp.empno = 2  ;