MYSQL学习总结（部分）

Mysql

本文使用的源代码下载地址

链接：https://pan.baidu.com/s/10Xn3rj3MUL3AJwzOd5XPBw
提取码：6666
复制这段内容后打开百度网盘手机App，操作更方便哦

1、数据库概述

1.1、SQL的分类

• DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。
• DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。
• DDL（数据定义语言）：create drop alter，对表结构的增删改。
• TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
• DCL（数据控制语言）: grant授权、revoke撤销权限等。

1.2、导入数据

​ 第一步：登录mysql数据库管理系统
​ dos命令窗口：
​ mysql -uroot -p333
​ 第二步：查看有哪些数据库
​ show databases; (这个不是SQL语句，属于MySQL的命令。)
​ 第三步：创建属于我们自己的数据库
​ create database bjpowernode; (这个不是SQL语句，属于MySQL的命令。)
​ 第四步：使用bjpowernode数据
​ use bjpowernode; (这个不是SQL语句，属于MySQL的命令。)
​ 第五步：查看当前使用的数据库中有哪些表？
​ show tables; (这个不是SQL语句，属于MySQL的命令。)
​ 第六步：初始化数据
​ mysql> source D:\course\05-MySQL\resources\bjpowernode.sql
​

注意：数据初始化完成之后，有三张表：
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
+-----------------------+

2、常用命令

2.1、查看mysql版本号

select version();

2.2、查看全部数据库

show databases;

2.3、查看当前使用的是哪个数据库

select database();

2.4、查看数据库中的表

show tables;

2.5、查看表结构

desc <table name>

2.6、查看表的创建语句

show create table <table name>;

2.7、创建数据库

在数据库中建立表，因此创建表的时候必须要先选择数据库。

create database 数据库名字

use 数据库名字

3、查询 （select）

3.1、完整的查询过程

select			5
	..			
from			1
	..
where			2
	..
group by		3
	..
having			4
	..
order by		6
	..

3.2、起别名

可以采用as关键字重命名表字段，其实as也可以省略，如：

select empno "员工编号", ename "员工姓名", sal*12 "年薪" from emp;

3.3、条件查询

条件查询需要用到where语句，where必须放到from语句表的后面

支持如下运算符:

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and ….	两个值之间,****等同于 >= and <=****
is null	为null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in不在这个范围中）
not	not可以取非，主要用在is 或in中
like	like称为模糊查询，支持%或下划线（‘__’）匹配 %匹配任意个字符 下划线，一个下划线只匹配一个字符

关于表中的运算符 笔者只记录了一些笔者自己没记下来的的…

一些常用的但是笔者太过熟悉就没有记载…

3.3.1、between … and …

between … and …等于 >= and <=

关于between … and …是包含最大值和最小值的

3.3.2、is null

Null为空，但不是空串，为null可以设置这个字段不填值，如果查询为null的字段，采用is null

• 查询津贴为空的员工

mysql> select * from emp where comm is null;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+

3.3.3、in

in 表示包含的意思，完全可以采用 or 来表示，采用 in 会更简洁一些

select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
select ename,job from emp where job in('SALESMAN', 'MANAGER');

select ename,job from emp where sal in(800, 5000); // in后面的值不是区间，是具体的值。
    +-------+-----------+---------+
    | ename | job       | sal     |
    +-------+-----------+---------+
    | SMITH | CLERK     |  800.00 |
    | KING  | PRESIDENT | 5000.00 |
    +-------+-----------+---------+

3.3.4、not

not 表示否定的意思

• 查询出薪水不包含1600和薪水不包含3000的员工

mysql> select * from emp where sal <> 1600 and sal <> 3000; (第一种写法)
mysql> select * from emp where not (sal = 1600 or sal = 3000); (第二种写法)
mysql> select * from emp where sal not in (1600, 3000); (第三种写法)
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

• 查询出津贴不为 null 的所有员工

mysql> select * from emp where comm is not null;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

3.3.5、like

（在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_）
% 代表任意多个字符，_ 代表任意1个字符。

找出名字当中含有O的？
    select ename from emp where ename like '%O%';
    +-------+
    | ename |
    +-------+
    | JONES |
    | SCOTT |
    | FORD  |
    +-------+

找出名字中第二个字母是A的？
    select ename from emp where ename like '_A%';
    +--------+
    | ename  |
    +--------+
    | WARD   |
    | MARTIN |
    | JAMES  |
    +--------+

找出名字中有下划线的？
	mysql> select * from t_user;
    +------+----------+
    | id   | name     |
    +------+----------+
    |    1 | zhangsan |
    |    2 | lisi     |
    |    3 | WANG_WU  |
    +------+----------+
    select name from t_user where name like '%_%';
    +----------+
    | name     |
    +----------+
    | zhangsan |
    | lisi     |
    | WANG_WU  |
    +----------+
    select name from t_user where name like '%\_%';
    +---------+
    | name    |
    +---------+
    | WANG_WU |
    +---------+

找出名字中最后一个字母是T的？
    select ename from emp where ename like '%T';
    +-------+
    | ename |
    +-------+
    | SCOTT | 
    +-------+

3.3.6、关于表达式的优先级

表达式的优先级不用记，没有把握就加括号…

4、排序数据

排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面

4.1、单字段排序

• 注意：默认是升序。怎么指定升序或者降序呢？asc表示升序，desc表示降序。

select ename , sal from emp order by sal; // 升序
select ename , sal from emp order by sal asc; // 升序
select ename , sal from emp order by sal desc; // 降序。

4.2、多字段排序

• 按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

select ename,sal from emp order by sal desc , ename asc;
-- 注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

4.3、使用字段位置排序

• 按照薪水升序 6 代指关键字的位置

mysql> select * from emp order by 6;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

这里的6代表字段的位置，表中数据6代表的是sal sal按照升序排序

不建议使用此种方式，采用数字含义不明确，程序不健壮

4.4、语句执行先后

	select 
		字段						3
	from
		表名						1
	where
		条件						2
	order by
		....					 4
	
	order by是最后执行的。

5、分组函数/聚合函数/多行处理函数

count	取得记录数
sum	求和
avg	取平均
max	取最大的数
min	取最小的数

记住：所有的分组函数都是对“某一组”数据进行操作的。

分组函数还有另一个名字：多行处理函数。
多行处理函数的特点：输入多行，最终输出的结果是1行。

分组函数自动忽略NULL。

5.1、分组函数使用

找出工资总和？
	select sum(sal) from emp;
找出最高工资？
	select max(sal) from emp;
找出最低工资？
	select min(sal) from emp;
找出平均工资？
	select avg(sal) from emp;
找出总人数？
	select count(*) from emp;
	select count(ename) from emp;

5.2、count

• Count(*)表示取得所有记录，忽略null，为null的值也会取得

• 采用count(字段名称)，不会取得为null的记录

5.3、sum

• 取得薪水的合计（sal+comm）

从以上结果来看，不正确，原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0

5.4、max

• 取得最晚入职的员工（可以不使用str_to_date 转换）

mysql> select max(hiredate) from emp;

5.5、min

• 取得最早入职的员工（可以不使用str_to_date 转换）

mysql> select min(hiredate) from emp;

6、分组查询

6.1、group by

group by： 按照某个字段或者某些字段进行分组。

• 找出每个工作岗位的最高薪资。

mysql> select max(sal), job from emp group by job;
+----------+-----------+
| max(sal) | job       |
+----------+-----------+
|  1300.00 | CLERK     |
|  1600.00 | SALESMAN  |
|  2975.00 | MANAGER   |
|  3000.00 | ANALYST   |
|  5000.00 | PRESIDENT |
+----------+-----------+

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。

select ename,max(sal),job from emp group by job;

以上SQL语句在Oracle数据库中无法执行，执行报错。

以上SQL语句在Mysql数据库中可以执行，但是执行结果矛盾。

记住一个规则：当一条语句中有group by的话，select 后面只能跟分组函数+参与分组的字段。

6.2、having

having：having是对分组之后的数据进行再次过滤。

• 取得每个岗位的平均工资大于2000的数据

mysql> select job, avg(sal) from emp group by job having avg(sal) > 2000;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+

• 找出每个部门的最高薪资，要求显示薪资大于2900的数据。

第一步：找出每个部门的最高薪资
		select max(sal),deptno from emp group by deptno;
		+----------+--------+
		| max(sal) | deptno |
		+----------+--------+
		|  5000.00 |     10 |
		|  3000.00 |     20 |
		|  2850.00 |     30 |
		+----------+--------+

第二步：找出薪资大于2900
		select max(sal),deptno from emp group by deptno having max(sal) > 2900; // 这种方式效率低。
		+----------+--------+
		| max(sal) | deptno |
		+----------+--------+
		|  5000.00 |     10 |
		|  3000.00 |     20 |
		+----------+--------+

		select max(sal),deptno from emp where sal > 2900 group by deptno;  // 效率较高，建议能够使用where过滤的尽量使用where。
		+----------+--------+
		| max(sal) | deptno |
		+----------+--------+
		|  5000.00 |     10 |
		|  3000.00 |     20 |
		+----------+--------+

• 找出每个部门的平均薪资，要求显示薪资大于2000的数据。

第一步：找出每个部门的平均薪资
	select deptno,avg(sal) from emp group by deptno;
	+--------+-------------+
	| deptno | avg(sal)    |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+

第二步：要求显示薪资大于2000的数据
	select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;	
	+--------+-------------+
	| deptno | avg(sal)    |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	+--------+-------------+
	
-- where后面不能使用分组函数：
-- select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno;	// 错误了。
-- 这种情况只能使用having过滤。

6.3、select 语句总结

一个完整的select语句格式如下

select 字段

from 表名

where …

group by …

having …(就是为了过滤分组后的数据而存在的—不可以单独的出现)

order by …

以上语句的执行顺序

1. 首先执行where语句过滤原始数据
2. 执行group by进行分组
3. 执行having对分组数据进行操作
4. 执行select选出数据
5. 执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

7、连接查询

7.1、什么是连接查询

​ 在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。
​ 在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。
​ stuno stuname classno classname

​ 1 zs 1 北京大兴区亦庄经济技术开发区第二中学高三1班
​ 2 ls 1 北京大兴区亦庄经济技术开发区第二中学高三1班
​ …
​ 学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。

7.2、连接查询的分类

根据语法出现的年代来划分的话，包括：
SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）
SQL99（比较新的语法）
根据表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（很少用！）

7.3、笛卡尔积

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

避免笛卡尔积的方法是加入条件过滤

​ 但是这并不会减少记录的匹配次数，但显示的是有效记录

7.4、内连接

7.4.1、等值连接

• 查询每个员工的部门名称，要求显示员工名和部门名。

SQL92:（太老，不用了）
	select 
		e.ename,d.dname
	from
		emp e, dept d
	where
		e.deptno = d.deptno;

SQL99：（常用的）
	select 
		e.ename,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;

像上面一样，数据会存在大量的重复，导致数据的冗余。

7.2、连接查询的分类

根据语法出现的年代来划分的话，包括：
SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）
SQL99（比较新的语法）
根据表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（很少用！）

7.3、笛卡尔积

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

避免笛卡尔积的方法是加入条件过滤

​ 但是这并不会减少记录的匹配次数，但显示的是有效记录

7.4、内连接

7.4.1、等值连接

• 查询每个员工的部门名称，要求显示员工名和部门名。

SQL92:（太老，不用了）
	select 
		e.ename,d.dname
	from
		emp e, dept d
	where
		e.deptno = d.deptno;

SQL99：（常用的）
	select 
		e.ename,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;