MySQL笔记

一、常见的有哪些数据库管理系统：

Oracle 甲骨文(SUN太阳) --》Oracle吞并SUN公司
IBM公司 —》开发了eclipse
Oracle做数据库起家，Oracle收购了Mysql AB公司

常见的sql有：Oracle、Mysql、 DB2、 sybase、 “MS sqlServer 支持标准数据库管理系统”

IP地址是这台计算机在这个网络当中的唯一标识（身份号）
端口号是这台计算机上运行的每个软件都有一个端口，端口号就是每个正在运行的软件的唯一标识。 (端口号在同一台计算机上不能重复)

二、MySQL

1、sql、DB、DBMS分别是什么？他们的关系是?

DB:DataBase（数据库，数据库在硬盘上实际上是以文件的形式存在）
DBMS：DataBase Management System（数据库管理系统，常见的有：MySQL、Oracle、DB2、Sybase、Sqlserver）

SQL：结构化查询语言，是一门标准通用语言，标准的sql使用于所有的数据库产品。
SQL是高级语言，只要可以看懂英语单词，就可以读懂sql的意思
SQL语句在执行的时候实际上内部也会进行编译，然后执行sql语句(sql语句的编译由DBMS完成)

DBMS执行sql语句，通过执行sql语句，来操作DB中的数据
DBMS 执行sql， 最终操作的是DB

三、表(table)

行：就是数据
列：就是字段

1、什么是表table

表是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。
一张表包括行和列
行被称为数据/记录(data)
列被称为字段(column)

学号(int)  姓名(varchar) 年龄(int)
----------------------------------
110	      		 张三	  22
120              李四	  23
140              王五	  24

数据库中字符串叫：varchar

每一个字段应该有什么属性：
字段名、数据类型、相关的约束

2、学习MySQL主要是学习通用的sql语句，sql语句包括增删改查，那么sql语句如何分类？

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

3、导入数据

第一步：登录MySQL
Dos命令窗口：mysql -uroot -p123456
mysql -uroot -p
123456
第二步：查看有哪些数据库

show databases;(这个是mysql的命令，不属于sql语句)
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 666                |
| mysql              |
| perfectcrm         |
| performance_schema |
| sqlexample         |
| sqlstudy           |
| sys                |
| test               |
+--------------------+

第三步：创建自己的数据库
create database study;(这个是mysql的命令，不属于sql语句)
第四步：使用自己创建的数据库
use study;(这个是mysql的命令，不属于sql语句)
第五步：查看当前使用的数据库中有哪些表
show tables;(这个是mysql的命令，不属于sql语句
第六步：初始化数据
source 加上文件绝对路径;(这个是mysql的命令，不属于sql语句

注意：数据初始化完成之后有三张表

4、sqlstudy.sql,这个文件以.sql结尾，称为“sql脚本”，什么是sql脚本？

以“.sql”结尾的文件，并且里面含有大量的sql语句，我们称这样的文件叫做sql脚本。

注意：直接使用source命令可以执行sql脚本
sql文件过大，可以使用source命令完成初始化。

5、删除数据库：drop database sqlstudy;

6、查看表结构

mysql> show tables;
+--------------------+
| Tables_in_sqlstudy |
+--------------------+
| dept               |	(部门表)
| emp                |	(员工表)
| salgrade           |	(员工等级表)
+--------------------+

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |(部门编号)
| DNAME  | varchar(14) | YES  |     | NULL    |       |(部门名字)
| LOC    | varchar(13) | YES  |     | NULL    |       |(部门地理位置)
+--------+-------------+------+-----+---------+-------+

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |(员工编号)
| ENAME    | varchar(10) | YES  |     | NULL    |       |(员工姓名)
| JOB      | varchar(9)  | YES  |     | NULL    |       |(员工工作岗位)
| MGR      | int(4)      | YES  |     | NULL    |       |(员工上级领导)
| HIREDATE | date        | YES  |     | NULL    |       |(员工入职日期)
| SAL      | double(7,2) | YES  |     | NULL    |       |(员工月薪)
| COMM     | double(7,2) | YES  |     | NULL    |       |(员工补助\津贴)
| DEPTNO   | int(2)      | YES  |     | NULL    |       |(员工部门编号)
+----------+-------------+------+-----+---------+-------+

mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |(等级)
| LOSAL | int(11) | YES  |     | NULL    |       |(最低薪资)
| HISAL | int(11) | YES  |     | NULL    |       |(最高薪资)
+-------+---------+------+-----+---------+-------+

7、表格中的数据

select *from emp;（sql语句）
mysql> select *from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  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 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  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 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select *from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select *from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

8、常用命令：

mysql> select database();	// 查看当前使用的是哪个数据库(是命令，不是sql语句)
+------------+
| database() |
+------------+
| sqlstudy   |
+------------+

​

mysql> select version();	// 查看当前使用的数据库的版本号(是命令，不是sql语句)
+-----------+
| version() |
+-----------+
| 5.7.18    |
+-----------+

\c 命令结束一条语句

exit 命令退出MySQL

9、查看创建表的语句：

show create table 表名;

mysql> show create table emp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `JOB` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

10、简单的查询语句(sql语句)

语法格式：select 字段名1，字段名2，… from 表名;

提示：1、任何一条sql语句以“;”分号结尾。
2、sql语句不区分大小写。
3、sql语句的字符串以单引号括起来

// 查询员工姓名和职位

mysql> select ename,job from emp;
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| JONES  | MANAGER   |
| MARTIN | SALESMAN  |
| BLAKE  | MANAGER   |
| CLARK  | MANAGER   |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+

// 查询员工编号和员工姓名

mysql> select empno,ename from emp;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+

// 查询员工的年薪?(字段可以参与数学运算)
select ename，sal * 12 from emp;

mysql> select ename,sal * 12 from emp;
+--------+----------+
| ename  | sal * 12 |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

// 给查寻结果重命名
select ename，sal * 12 as yearsal from emp;

mysql>  select ename,sal * 12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

// 起的别名中有中文
select ename,sal * 12 as ‘年薪’ from emp;

mysql> select ename,sal * 12 as '年薪' from emp;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

注意：标准sql语句中字符串使用单引号括起来
as关键字可以省略

// 查询所有字段
select * from emp;

// 查询出工资等于5000的

select empno,ename,sal=5000 from emp;
+-------+--------+----------+
| empno | ename  | sal=5000 |
+-------+--------+----------+
|  7369 | SMITH  |        0 |
|  7499 | ALLEN  |        0 |
|  7521 | WARD   |        0 |
|  7566 | JONES  |        0 |
|  7654 | MARTIN |        0 |
|  7698 | BLAKE  |        0 |
|  7782 | CLARK  |        0 |
|  7788 | SCOTT  |        0 |
|  7839 | KING   |        1 |工资为5000
|  7844 | TURNER |        0 |
|  7876 | ADAMS  |        0 |
|  7900 | JAMES  |        0 |
|  7902 | FORD   |        0 |
|  7934 | MILLER |        0 |
+-------+--------+----------+

// 查询出工资小于5000的
select empno,ename,sal<5000 from emp;

mysql> select empno,ename,sal<5000 from emp;
+-------+--------+----------+
| empno | ename  | sal<5000 |
+-------+--------+----------+
|  7369 | SMITH  |        1 |
|  7499 | ALLEN  |        1 |
|  7521 | WARD   |        1 |
|  7566 | JONES  |        1 |
|  7654 | MARTIN |        1 |
|  7698 | BLAKE  |        1 |
|  7782 | CLARK  |        1 |
|  7788 | SCOTT  |        1 |
|  7839 | KING   |        0 |
|  7844 | TURNER |        1 |
|  7876 | ADAMS  |        1 |
|  7900 | JAMES  |        1 |
|  7902 | FORD   |        1 |
|  7934 | MILLER |        1 |
+-------+--------+----------+

// select empno,ename,sal<5000 ‘工资小于5000’ from emp;

mysql> select empno, ename, sal < 5000 '工资小于5000' from emp;

“as 关键字可以省略”
+-------+--------+------------------+
| empno | ename  | 工资小于5000     |
+-------+--------+------------------+
|  7369 | SMITH  |                1 |
|  7499 | ALLEN  |                1 |
|  7521 | WARD   |                1 |
|  7566 | JONES  |                1 |
|  7654 | MARTIN |                1 |
|  7698 | BLAKE  |                1 |
|  7782 | CLARK  |                1 |
|  7788 | SCOTT  |                1 |
|  7839 | KING   |                0 |
|  7844 | TURNER |                1 |
|  7876 | ADAMS  |                1 |
|  7900 | JAMES  |                1 |
|  7902 | FORD   |                1 |
|  7934 | MILLER |                1 |

11、条件查询

语法格式：
select
字段名,字段名…
from
表名
where
条件;

// 查询工资小于5000的工人编号，姓名

mysql> select
    -> empno,ename
    -> from
    -> emp
    -> where
    -> sal<5000;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7902 | FORD   |
|  7934 | MILLER |
+-------+--------+

// 查询工资等于5000的

mysql> select
    -> empno,ename
    -> from
    -> emp
    -> where
    -> sal=5000;
+-------+-------+
| empno | ename |
+-------+-------+
|  7839 | KING  |
+-------+-------+ 

// 查询’smith’的工资

mysql> select sal from emp where ename='smith';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+

// 找出工资不等于3000的

mysql> select empno, ename from emp where sal<>3000;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7566 | JONES  |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7839 | KING   |
|  7844 | TURNER |
|  7876 | ADAMS  |
|  7900 | JAMES  |
|  7934 | MILLER |
+-------+--------+

// 找出工资sal>=1100 and sal<=3000的

mysql> select ename,sal from emp where sal >= 1100 and sal <=3000;
mysql> select ename, sal from emp where sal between 1100 and 3000;（闭区间）

+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

注意：between and在使用时必须前大后小(咦，这里好像不对，应该是前小后大吧)
between and不仅可以使用在数字方面也可以使用在字符方面

mysql> select ename from emp where ename between 'A' and 'C';
+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| ADAMS |
+-------+

// 找出哪些人没有津贴？
// 在数据库中null不是一个值，代表什么也没有，为空。
// 空不是一个值，不能用等号来衡量。
// 判断是否为空，必须使用is null 或者 is not null

mysql> select ename,sal,comm from emp where comm is null;
+--------+---------+------+
| ename  | sal     | comm |
+--------+---------+------+
| SMITH  |  800.00 | NULL |
| JONES  | 2975.00 | NULL |
| BLAKE  | 2850.00 | NULL |
| CLARK  | 2450.00 | NULL |
| SCOTT  | 3000.00 | NULL |
| KING   | 5000.00 | NULL |
| ADAMS  | 1100.00 | NULL |
| JAMES  |  950.00 | NULL |
| FORD   | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+

// 找出哪些人的津贴不为null?

mysql> select ename,sal,comm from emp where comm is not null;
+--------+---------+---------+
| ename  | sal     | comm    |
+--------+---------+---------+
| ALLEN  | 1600.00 |  300.00 |
| WARD   | 1250.00 |  500.00 |
| MARTIN | 1250.00 | 1400.00 |
| TURNER | 1500.00 |    0.00 |
+--------+---------+---------+

// 找出哪些人没有津贴？

mysql> select ename,sal,comm from emp where comm is null or comm = 0;
+--------+---------+------+
| ename  | sal     | comm |
+--------+---------+------+
| SMITH  |  800.00 | NULL |
| JONES  | 2975.00 | NULL |
| BLAKE  | 2850.00 | NULL |
| CLARK  | 2450.00 | NULL |
| SCOTT  | 3000.00 | NULL |
| KING   | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS  | 1100.00 | NULL |
| JAMES  |  950.00 | NULL |
| FORD   | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+

// 找出工作岗位是’salesman’和’president’的人

mysql> select ename, job from emp where job = 'salesman' or job = 'president';
+--------+-----------+
| ename  | job       |
+--------+-----------+
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| MARTIN | SALESMAN  |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
+--------+-----------+

// 找出薪资大于1000并且部门编号为20或者30的员工

mysql> select ename,sal,deptno from emp where sal>1000 and deptno = 20 or empno = 30;（这个是有问题的）
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| ADAMS | 1100.00 |     20 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+ 

// and和or联合使用

mysql> select ename,sal,deptno from emp where sal>1000 and (deptno = 20 or deptno = 30);
mysql> select empno, ename, sal, deptno from emp where sal > 1000 and deptno in (20,30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+

// in等同于or 找出工作岗位是’salesman’ 和 'manager’的所有员工

mysql> select ename,job from emp where job in ('salesman', 'manager');
mysql> select ename,job from emp where job = 'salesman' or job = 'manager';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

mysql> select ename, job, sal from emp where sal in (1100, 5000);
// 这句的意思是找出工资是1100和5000的，不是一个区间 
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| KING  | PRESIDENT | 5000.00 |
| ADAMS | CLERK     | 1100.00 |
+-------+-----------+---------+

// not in 不在这几个值当中的

mysql> select ename,sal from emp where sal not in (1100, 5000);
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| TURNER | 1500.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

// 模糊查询like
找出名当中有o的人
(在模糊查询中必须掌握两个特殊字符，一个是‘%’，一个是‘_’)
%代表任意多个字符，_代表任意一个字符
select ename from emp where ename like ‘%o%’;

mysql> select ename from emp where ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+

// 找出第二个字母是a的人
select ename from emp where ename like ‘_a%’;

mysql> select ename from emp where ename like '_a%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+

// 找到名字中含有_的人
// 添加转义字符“\”让_变成普通字符

mysql> select id, name from t_user where name like ('%\_%');
+------+-----------+
| id   | name      |
+------+-----------+
|    2 | zhang_san |
+------+-----------+

// 找出名字最后一个字母是T的

mysql> select ename from emp where ename like ('%T');
+-------+
| ename |
+-------+
| SCOTT |
+-------+

// 排序
// 默认使用升序

mysql> select ename, sal from emp order by sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

注意：降序使用desc [descending]
升序使用asc [ascending]

mysql> select ename, sal from emp order by sal asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

// 按照工资的降序排列，当工资一样的时候，按照名字的升序排列。
// 只有当前面的字段无法排序的时候才会启用后面的字段
// 字段越靠前，越是起主导作用

mysql> select ename,sal from emp order by sal desc, ename asc;
按照题目要求排列		
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

只按照工资降序排列

mysql> select ename,sal from emp order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

// order by 可以按照字段的顺序进行排序

mysql> select ename, sal from emp  order by 2;		// 按照第二列进行排序
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

// 找出员工职位是‘salesman’ 的员工并按照工资的降序排列。

mysql> select ename, job, sal from emp where job = 'salesman' order by sal desc;
mysql> select
    ->  ename, job, sal
    -> from
    ->  emp
    -> where
    ->  job = 'salesman'
    -> order by
    ->	sal desc;
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| ALLEN  | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+

select 3
字段
from 1
表名
where 2
条件
order by 4
…

注意：order by是最后执行的

12、分组函数

count 计数
sum 求和
avg 求平均
max 最大值
min 最小值

注意:所有的分组函数都是对“某一组”数据进行操作的

// 求出工资总和

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

// 求出工资最小值

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+ 
|   800.00 |
+----------+

// 计算总人数

mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+

分组函数共有5个
分组函数又叫做：多行处理函数
多行处理函数特点：输入多行，最终输出一行

*****分组函数自动忽略null

mysql> select count(comm) from emp;
+-------------+
| count(comm) | // comm中有null,但是计算结果不为null
+-------------+
|           4 |
+-------------+

mysql> select sum(comm) from emp;
	select sum(comm) from emp where comm is not null; // 不需要添加这个额外的过滤条件，sum函数自动忽略null
	+-----------+
	| sum(comm) |	// comm中有null,但是计算结果不为null，说明分组函数自动忽略null
	+-----------+
	|   2200.00 |
	+-----------+

// 找出工资高于平均工资的员工
平均工资

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

mysql> select ename, sal from emp where avg(sal);
		ERROR 1111 (HY000): Invalid use of group function
		// 思考以上错误信息？无效的使用了分组函数
		注意：SQL语句中有一个语法规则，分组函数不能直接使用在where子句中。

count(*)和count 某个字段 的区别？
count(*) 表示：不是统计某个字段中数据的条数，而是统计总记录条数
count comm 表示：comm字段中不为NULL的数据总数。

13、单行处理函数？

什么是单行处理函数？
输入一行，输出一行。

计算每个员工的年薪？
select ename, (sal + comm) * 12 as yearsal from emp;

mysql> select ename, sal, comm, (sal + comm) * 12 as yearsal from emp;
+--------+---------+---------+----------+
| ename  | sal     | comm    | yearsal  |
+--------+---------+---------+----------+
| SMITH  |  800.00 |    NULL |     NULL |
| ALLEN  | 1600.00 |  300.00 | 22800.00 |
| WARD   | 1250.00 |  500.00 | 21000.00 |
| JONES  | 2975.00 |    NULL |     NULL |
| MARTIN | 1250.00 | 1400.00 | 31800.00 |
| BLAKE  | 2850.00 |    NULL |     NULL |
| CLARK  | 2450.00 |    NULL |     NULL |
| SCOTT  | 3000.00 |    NULL |     NULL |
| KING   | 5000.00 |    NULL |     NULL |
| TURNER | 1500.00 |    0.00 | 18000.00 |
| ADAMS  | 1100.00 |    NULL |     NULL |
| JAMES  |  950.00 |    NULL |     NULL |
| FORD   | 3000.00 |    NULL |     NULL |
| MILLER | 1300.00 |    NULL |     NULL |
+--------+---------+---------+----------+

注意：所有数据库都是这样规定的，只要null参与运算，结果都为null

ifnull()空处理函数？
ifnull(可能为null的数据，被当作什么处理) // 单行处理函数

	mysql> select ename, (sal + ifnull(comm, 0)) * 12 from emp;
	+--------+------------------------------+
	| ename  | (sal + ifnull(comm, 0)) * 12 |
	+--------+------------------------------+
	| SMITH  |                      9600.00 |
	| ALLEN  |                     22800.00 |
	| WARD   |                     21000.00 |
	| JONES  |                     35700.00 |
	| MARTIN |                     31800.00 |
	| BLAKE  |                     34200.00 |
	| CLARK  |                     29400.00 |
	| SCOTT  |                     36000.00 |
	| KING   |                     60000.00 |
	| TURNER |                     18000.00 |
	| ADAMS  |                     13200.00 |
	| JAMES  |                     11400.00 |
	| FORD   |                     36000.00 |
	| MILLER |                     15600.00 |
	+--------+------------------------------+

​

	mysql> select ename, ifnull(comm, 0) from emp;
	+--------+-----------------+
	| ename  | ifnull(comm, 0) |
	+--------+-----------------+
	| SMITH  |            0.00 |
	| ALLEN  |          300.00 |
	| WARD   |          500.00 |
	| JONES  |            0.00 |
	| MARTIN |         1400.00 |
	| BLAKE  |            0.00 |
	| CLARK  |            0.00 |
	| SCOTT  |            0.00 |
	| KING   |            0.00 |
	| TURNER |            0.00 |
	| ADAMS  |            0.00 |
	| JAMES  |            0.00 |
	| FORD   |            0.00 |
	| MILLER |            0.00 |
	+--------+-----------------+

14、group by 和 having

group by是对某个字段或者某些字段进行分组
having是对分组之后的数据进行再次过滤，having是group by 的搭档，只有group by出现的时候having才可以用

注意：分组函数一般和group by联合使用，这也是因为他为什么叫做分组函数的原因。
任何一个分组函数(max, min, avg, count, sum)都是在group by执行结束之后执行的。
当一条sql语句没有group by的时候，整张表的数据会自成一组。

分组函数不能直接使用在where子句当中？
因为group by是在where执行之后才会执行。

	select		5
		...
	from		1
		...	
	where		2  // 进行过滤 ，其后不能跟分组函数(多行处理函数)
		...
	group by	3  // 进行分组
		...
	having		4  // having是对自己分完组之后的数据不满意，再进行过滤
		...	
	order by	6  // 进行排序
		...

// 找出每一个工作岗位的最高薪资

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

// 找出工资高于平均工资的员工

	mysql> select ename, sal from emp where sal > (select avg(sal) from emp);
// 记住一条规则：当
	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	| CLARK | 2450.00 |
	| SCOTT | 3000.00 |
	| KING  | 5000.00 |
	| FORD  | 3000.00 |
	+-------+---------+

// 每个工作岗位的平均薪资

mysql> select job, avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+

// 多个字段能不能联合起来一块分组？
// 找出每个部门的不同工作岗位的最高薪资

mysql> select deptno, job, sal from emp order by deptno; // 从emp表中找出deptno, job, sal 按deptno排序
+--------+-----------+---------+
| deptno | job       | sal     |
+--------+-----------+---------+
|     10 | MANAGER   | 2450.00 |
|     10 | PRESIDENT | 5000.00 |
|     10 | CLERK     | 1300.00 |
|     20 | CLERK     |  800.00 |
|     20 | MANAGER   | 2975.00 |
|     20 | ANALYST   | 3000.00 |
|     20 | CLERK     | 1100.00 |
|     20 | ANALYST   | 3000.00 |
|     30 | SALESMAN  | 1600.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | MANAGER   | 2850.00 |
|     30 | SALESMAN  | 1500.00 |
|     30 | CLERK     |  950.00 |
+--------+-----------+---------+

// 按照部门和职位分组，从emp中，找出最高工资。

select 
	deptno, job, max(sal)
from
	emp
group by 
	 deptno, job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+

// 找出每个部门的最高薪资，要求显示薪资大于2900的数据。
第一步：找出每个部门的最高薪资

mysql> 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) maxsal, deptno from emp group by deptno having maxsal > 2900; // 这种方式效率较低
+----------+--------+
| maxsal | deptno |
+----------+--------+
|  5000.00 |     10 |
|  3000.00 |     20 |
+----------+--------+

select max(sal), deptno from emp where sal > 2900 group by deptno; // 这种方式效率较高
+----------+--------+
| 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 |
+--------+-------------+

select deptno, avg(sal) from emp where avg(sal) > 2000 group by deptno; // 错误
// where之后不能使用分组函数
// 这种情况只能使用having过滤。

15、一个完整的数据查寻语句怎么写？(DQL)

select		5、对过滤分组，再过滤之后的数据查找出来
	...
from		1、通过from查询表
	...	
where		2、where条件过滤
	...
group by	3、group by分组(一个语句中有group by 的话，select中只能有参加分组的字段，以及这些字段的分组函数)
	...
having		4、having对分组之后的数据进行再次过滤
	...
order by	6、对查找出来的数据进行排序
	...

16、关于查询结果集的去重

mysql> select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+

mysql> select distinct job from emp;
// distinct 只能出现在所有字段的最前方。
// distinct 出现在所有字段的最前方，表示所有字段联合起来去除重复。
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

// 实例：统计岗位的数量

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) | 
+---------------------+
|                   5 |
+---------------------+

17、连接查询

17.1 什么是连接查询

实际开发中，大部分情况下都不是从单张表中查询数据，而是多张表联合查询，取出最终的结果。
实际开发中，一个业务一般对应多张表，比如：学生和班级。（假如学生和班级存一张表会出现数据的冗余）

17.2 连接查询的分类？

根据SQL出现的年代进行划分，包括：
SQL92
SQL99
根据表的连接方式进行划分：
内连接：
等值连接
非等值连接
自连接
外连接
左外连接(左连接)
右外连接(右连接)
全连接(很少用)

17.3 在表的连接查询方面有一个现象，叫做笛卡尔积现象(笛卡尔乘积现象)

// 找出每一个员工的部门名，要求显示部门名称和员工姓名。
select e.ename, d.dname from emp p, dept d;

mysql> select e.ename, d.dname from emp e, dept d;	// ename, dname 一块联合显示
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
| WARD   | SALES      |
| WARD   | OPERATIONS |
| JONES  | ACCOUNTING |
| JONES  | RESEARCH   |
| JONES  | SALES      |
| JONES  | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH   |
| MARTIN | SALES      |
| MARTIN | OPERATIONS |
| BLAKE  | ACCOUNTING |
| BLAKE  | RESEARCH   |
| BLAKE  | SALES      |
| BLAKE  | OPERATIONS |
| CLARK  | ACCOUNTING |
| CLARK  | RESEARCH   |
| CLARK  | SALES      |
| CLARK  | OPERATIONS |
| SCOTT  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| SCOTT  | SALES      |
| SCOTT  | OPERATIONS |
| KING   | ACCOUNTING |
| KING   | RESEARCH   |
| KING   | SALES      |
| KING   | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH   |
| TURNER | SALES      |
| TURNER | OPERATIONS |
| ADAMS  | ACCOUNTING |
| ADAMS  | RESEARCH   |
| ADAMS  | SALES      |
| ADAMS  | OPERATIONS |
| JAMES  | ACCOUNTING |
| JAMES  | RESEARCH   |
| JAMES  | SALES      |
| JAMES  | OPERATIONS |
| FORD   | ACCOUNTING |
| FORD   | RESEARCH   |
| FORD   | SALES      |
| FORD   | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH   |
| MILLER | SALES      |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set (0.00 sec)

// 关于表的别名
别名的好处：
1、执行效率高
2、可读性好

17.4 怎么避免笛卡尔积现象？当然是加条件过滤。

避免了笛卡尔积现象，可以减少记录的匹配次数吗？
不能。笛卡尔积避免以后执行次数还是56次。只不过显示的是有效记录。
// 找出每一个员工的部门名，要求显示部门名称和员工姓名。

select 
		e.ename, d.dname 
	from
		emp e, dept d 
	where
		e.deptno = d.deptno;	// SQL92语法
	+--------+------------+
	| ename  | dname      |
	+--------+------------+
	| SMITH  | RESEARCH   |
	| ALLEN  | SALES      |
	| WARD   | SALES      |
	| JONES  | RESEARCH   |
	| MARTIN | SALES      |
	| BLAKE  | SALES      |
	| CLARK  | ACCOUNTING |
	| SCOTT  | RESEARCH   |
	| KING   | ACCOUNTING |
	| TURNER | SALES      |
	| ADAMS  | RESEARCH   |
	| JAMES  | SALES      |
	| FORD   | RESEARCH   |
	| MILLER | ACCOUNTING |
	+--------+------------+
	14 rows in set (0.02 sec)

17.5 内连接之等值连接，最大的特点是：条件是等量关系。

// 查询每个员工的部门编号，要求显示部门名称，员工姓名。

• 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
	inner join
		dept d
	on 
		e.deptno = d.deptno;

// 语法：
	A
inner join
	B
on
	连接条件
where
	过滤条件
SQL99语法的好处：
	将表连接和where过滤分离

​

17.6 内连接之非等值连接，特点：连接条件中的关系是非等量关系。

案例：找出每个员工的工资等级，要求显示员工名，工资，工资等级。

mysql> select ename, sal from emp;
	+--------+---------+
	| ename  | sal     |
	+--------+---------+
	| SMITH  |  800.00 |
	| ALLEN  | 1600.00 |
	| WARD   | 1250.00 |
	| JONES  | 2975.00 |
	| MARTIN | 1250.00 |
	| BLAKE  | 2850.00 |
	| CLARK  | 2450.00 |
	| SCOTT  | 3000.00 |
	| KING   | 5000.00 |
	| TURNER | 1500.00 |
	| ADAMS  | 1100.00 |
	| JAMES  |  950.00 |
	| FORD   | 3000.00 |
	| MILLER | 1300.00 |
	+--------+---------+

mysql> select * from salgrade;
	+-------+-------+-------+
	| GRADE | LOSAL | HISAL |
	+-------+-------+-------+
	|     1 |   700 |  1200 |
	|     2 |  1201 |  1400 |
	|     3 |  1401 |  2000 |
	|     4 |  2001 |  3000 |
	|     5 |  3001 |  9999 |
	+-------+-------+-------+

// inner可以省略

select 
		e.ename, e.sal, s.grade
from 
		emp e
join 
		salgrade s
on 
		e.sal between s.losal and s.hisal;
	+--------+---------+-------+
	| ename  | sal     | grade |
	+--------+---------+-------+
	| SMITH  |  800.00 |     1 |
	| ALLEN  | 1600.00 |     3 |
	| WARD   | 1250.00 |     2 |
	| JONES  | 2975.00 |     4 |
	| MARTIN | 1250.00 |     2 |
	| BLAKE  | 2850.00 |     4 |
	| CLARK  | 2450.00 |     4 |
	| SCOTT  | 3000.00 |     4 |
	| KING   | 5000.00 |     5 |
	| TURNER | 1500.00 |     3 |
	| ADAMS  | 1100.00 |     1 |
	| JAMES  |  950.00 |     1 |
	| FORD   | 3000.00 |     4 |
	| MILLER | 1300.00 |     2 |
	+--------+---------+-------+

17.7 自连接。最大的特点是：一张表看作两张表，自己连自己。

// 找出每个员工的领导名，要求显示员工名和领导名。

mysql> select empno, ename, mgr from emp;
	+-------+--------+------+
	| empno | ename  | mgr  |
	+-------+--------+------+
	|  7369 | SMITH  | 7902 |
	|  7499 | ALLEN  | 7698 |
	|  7521 | WARD   | 7698 |
	|  7566 | JONES  | 7839 |
	|  7654 | MARTIN | 7698 |
	|  7698 | BLAKE  | 7839 |
	|  7782 | CLARK  | 7839 |
	|  7788 | SCOTT  | 7566 |
	|  7839 | KING   | NULL |
	|  7844 | TURNER | 7698 |
	|  7876 | ADAMS  | 7788 |
	|  7900 | JAMES  | 7698 |
	|  7902 | FORD   | 7566 |
	|  7934 | MILLER | 7782 |
	+-------+--------+------+

员工的领导编号 = 领导的员工编号

select a.ename as '员工名', b.ename as '领导名' from emp a inner join emp b on a.mgr = b.empno;
	+-----------+-----------+
	| 员工名    | 领导名    |
	+-----------+-----------+
	| SMITH     | FORD      |
	| ALLEN     | BLAKE     |
	| WARD      | BLAKE     |
	| JONES     | KING      |
	| MARTIN    | BLAKE     |
	| BLAKE     | KING      |
	| CLARK     | KING      |
	| SCOTT     | JONES     |
	| TURNER    | BLAKE     |
	| ADAMS     | SCOTT     |
	| JAMES     | BLAKE     |
	| FORD      | JONES     |
	| MILLER    | CLARK     |
	+-----------+-----------+

18、外连接

18.1 什么是外连接？和内连接有什么区别？

• 内连接：
  假设A表和B表进行连接，使用内连接，凡是A表B表可以匹配上的记录都查询出来，这就是内连接。
  AB表没有主副之分，是平等的。

• 外连接：
  假设A表和B表进行连接，使用外连接，AB表有主副之分，其中一张是A表，一张是副表，主要
  查询主表中的内容，捎带查询副表中的内容，当副表中的内容没有和主表中的内容匹配上，
  副表自动模拟出NULL与之匹配。

18.2 外连接的分类：

左外连接(左边的表是主表)：
右外连接(右边的表是主表)：

左连接有右连接的写法，右连接也有对应的左连接的写法。

// 查询出所有员工及其所对应的领导，要求显示员工名和领导名
内连接：

select
		a.ename '员工名', b.ename '领导名'
from 
		emp a
join
		emp b
on 
		a.mgr = b.empno;
		+-----------+-----------+
		| 员工名    | 领导名    |
		+-----------+-----------+
		| SMITH     | FORD      |
		| ALLEN     | BLAKE     |
		| WARD      | BLAKE     |
		| JONES     | KING      |
		| MARTIN    | BLAKE     |
		| BLAKE     | KING      |
		| CLARK     | KING      |
		| SCOTT     | JONES     |
		| TURNER    | BLAKE     |
		| ADAMS     | SCOTT     |
		| JAMES     | BLAKE     |
		| FORD      | JONES     |
		| MILLER    | CLARK     |
		+-----------+-----------+

外连接：

select
		a.ename '员工名', b.ename '领导名'
from 
		emp a			// 主表
left join
		emp b			// 副表
on 
		a.mgr = b.empno;

// 左连接的右连接写法

select
	a.ename '员工名', b.ename '领导名'
from 
	emp b			// 副表
right join
	emp a			// 主表
on 
			a.mgr = b.empno;
		+-----------+-----------+
		| 员工名     | 领导名     |
		+-----------+-----------+
		| SMITH     | FORD      |
		| ALLEN     | BLAKE     |
		| WARD      | BLAKE     |
		| JONES     | KING      |
		| MARTIN    | BLAKE     |
		| BLAKE     | KING      |
		| CLARK     | KING      |
		| SCOTT     | JONES     |
		| KING      | NULL      |	// 副表匹配不上，自动模拟NULL与之匹配
		| TURNER    | BLAKE     |
		| ADAMS     | SCOTT     |
		| JAMES     | BLAKE     |
		| FORD      | JONES     |
		| MILLER    | CLARK     |
		+-----------+-----------+

外连接最重要的特点是：主表的数据无条件的全部查询出来

实例：找出哪个部门没有员工？

select
			d.dname, e.ename
from 
			dept d
left join
			emp e
on 
			d.deptno = e.deptno;
		+------------+--------+
		| dname      | ename  |
		+------------+--------+
		| RESEARCH   | SMITH  |
		| SALES      | ALLEN  |
		| SALES      | WARD   |
		| RESEARCH   | JONES  |
		| SALES      | MARTIN |
		| SALES      | BLAKE  |
		| ACCOUNTING | CLARK  |
		| RESEARCH   | SCOTT  |
		| ACCOUNTING | KING   |
		| SALES      | TURNER |
		| RESEARCH   | ADAMS  |
		| SALES      | JAMES  |
		| RESEARCH   | FORD   |
		| ACCOUNTING | MILLER |
		| OPERATIONS | NULL   |		// 这个部门没有员工
		+------------+--------+
		15 rows in set (0.00 sec)	// 14个员工15条记录

// 正确写法

select
			d.*
from 
			dept d
left join
			emp e
on 
			d.deptno = e.deptno
where
			e.empno is null;
		+--------+------------+--------+
		| DEPTNO | DNAME      | LOC    |
		+--------+------------+--------+
		|     40 | OPERATIONS | BOSTON |
		+--------+------------+--------+

19、三张表怎么连接？

找出每个员工的部门名称，和工资等级
EMP表

mysql> select empno, ename, sal, deptno from emp;
		+-------+--------+---------+--------+
		| empno | ename  | sal     | deptno |
		+-------+--------+---------+--------+
		|  7369 | SMITH  |  800.00 |     20 |
		|  7499 | ALLEN  | 1600.00 |     30 |
		|  7521 | WARD   | 1250.00 |     30 |
		|  7566 | JONES  | 2975.00 |     20 |
		|  7654 | MARTIN | 1250.00 |     30 |
		|  7698 | BLAKE  | 2850.00 |     30 |
		|  7782 | CLARK  | 2450.00 |     10 |
		|  7788 | SCOTT  | 3000.00 |     20 |
		|  7839 | KING   | 5000.00 |     10 |
		|  7844 | TURNER | 1500.00 |     30 |
		|  7876 | ADAMS  | 1100.00 |     20 |
		|  7900 | JAMES  |  950.00 |     30 |
		|  7902 | FORD   | 3000.00 |     20 |
		|  7934 | MILLER | 1300.00 |     10 |
		+-------+--------+---------+--------+

DEPT表

mysql> select * from dept;
		+--------+------------+----------+
		| DEPTNO | DNAME      | LOC      |
		+--------+------------+----------+
		|     10 | ACCOUNTING | NEW YORK |
		|     20 | RESEARCH   | DALLAS   |
		|     30 | SALES      | CHICAGO  |
		|     40 | OPERATIONS | BOSTON   |
		+--------+------------+----------+

SALGRADE表

mysql> select * from salgrade;
		+-------+-------+-------+
		| GRADE | LOSAL | HISAL |
		+-------+-------+-------+
		|     1 |   700 |  1200 |
		|     2 |  1201 |  1400 |
		|     3 |  1401 |  2000 |
		|     4 |  2001 |  3000 |
		|     5 |  3001 |  9999 |
		+-------+-------+-------+

select 
		e.ename, d.dname, e.sal, s.grade
from 
		emp e
join
		dept d
on	
		e.deptno = d.deptno
join
		salgrade s	
on
		e.sal between s.losal and s.hisal;
		+--------+------------+---------+-------+
		| ename  | dname      | sal     | grade |
		+--------+------------+---------+-------+
		| SMITH  | RESEARCH   |  800.00 |     1 |
		| ALLEN  | SALES      | 1600.00 |     3 |
		| WARD   | SALES      | 1250.00 |     2 |
		| JONES  | RESEARCH   | 2975.00 |     4 |
		| MARTIN | SALES      | 1250.00 |     2 |
		| BLAKE  | SALES      | 2850.00 |     4 |
		| CLARK  | ACCOUNTING | 2450.00 |     4 |
		| SCOTT  | RESEARCH   | 3000.00 |     4 |
		| KING   | ACCOUNTING | 5000.00 |     5 |
		| TURNER | SALES      | 1500.00 |     3 |
		| ADAMS  | RESEARCH   | 1100.00 |     1 |
		| JAMES  | SALES      |  950.00 |     1 |
		| FORD   | RESEARCH   | 3000.00 |     4 |
		| MILLER | ACCOUNTING | 1300.00 |     2 |
		+--------+------------+---------+-------+
		14 rows in set (0.00 sec)

找出每个员工的部门名称，和工资等级，以及上级领导

select
			a.ename '员工名', d.dname '部门名称', a.sal '薪资', s.grade '工资等级', b.ename '领导名'
from 
			emp a
left join
			emp b
on
			a.mgr = b.empno
join
			dept d
on	
			a.deptno = d.deptno
join
			salgrade s	
on
			a.sal between s.losal and s.hisal;
		+-----------+--------------+---------+--------------+-----------+
		| 员工名    | 部门名称     | 薪资    | 工资等级     | 领导名    |
		+-----------+--------------+---------+--------------+-----------+
		| SMITH     | RESEARCH     |  800.00 |            1 | FORD      |
		| ADAMS     | RESEARCH     | 1100.00 |            1 | SCOTT     |
		| JAMES     | SALES        |  950.00 |            1 | BLAKE     |
		| WARD      | SALES        | 1250.00 |            2 | BLAKE     |
		| MARTIN    | SALES        | 1250.00 |            2 | BLAKE     |
		| MILLER    | ACCOUNTING   | 1300.00 |            2 | CLARK     |
		| ALLEN     | SALES        | 1600.00 |            3 | BLAKE     |
		| TURNER    | SALES        | 1500.00 |            3 | BLAKE     |
		| JONES     | RESEARCH     | 2975.00 |            4 | KING      |
		| BLAKE     | SALES        | 2850.00 |            4 | KING      |
		| CLARK     | ACCOUNTING   | 2450.00 |            4 | KING      |
		| SCOTT     | RESEARCH     | 3000.00 |            4 | JONES     |
		| FORD      | RESEARCH     | 3000.00 |            4 | JONES     |
		| KING      | ACCOUNTING   | 5000.00 |            5 | NULL      |
		+-----------+--------------+---------+--------------+-----------+
		14 rows in set (0.00 sec)

20、什么是子查询？子查询都出现在哪里？

select语句中嵌套select语句，被嵌套的select语句是子查询
子查询可以出现在哪里？
select
…
from
…
where
…

20.1 where子句中使用子查询

案例：找出高于平均薪资的员工信息

• 第一步：找出平均薪资

select avg(sal) from emp;

• 第二步：找出高于平均薪资的员工信息

select
		*
	from
		emp
	where
		sal > (select avg(sal) from emp);
	+-------+-------+-----------+------+------------+---------+------+--------+
	| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
	+-------+-------+-----------+------+------------+---------+------+--------+
	|  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 |
	|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
	+-------+-------+-----------+------+------------+---------+------+--------+
	6 rows in set (0.00 sec)

20.2 from后面嵌套子查询

案例：找出每个部门平均薪水的薪资等级

• 第一步：按照部门编号分组，找出每个部门的平均薪水

select
		deptno, avg(sal)
	from
		emp
	group by
		deptno;
	+--------+-------------+
	| deptno | avg(sal)    |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+
	

• 第二步：将上面这张表看作t表

select
		d.dname, s.grade
	from
		(select deptno, avg(sal) as avgsal from emp group by deptno) t
	join
		salgrade s
	on
		t.avgsal between s.losal and s.hisal
	join
		dept d
	on
		t.deptno = d.deptno;
	+------------+-------+
	| dname      | grade |
	+------------+-------+
	| SALES      |     3 |
	| ACCOUNTING |     4 |
	| RESEARCH   |     4 |
	+------------+-------+

案例：找出每个部门平均的薪水等级。

• 第一步：找出每个员工的薪水等级。

select	e.deptno, e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between losal and hisal;  
+--------+--------+---------+-------+
| deptno | ename  | sal     | grade |
+--------+--------+---------+-------+
|     20 | SMITH  |  800.00 |     1 |
|     30 | ALLEN  | 1600.00 |     3 |
|     30 | WARD   | 1250.00 |     2 |
|     20 | JONES  | 2975.00 |     4 |
|     30 | MARTIN | 1250.00 |     2 |
|     30 | BLAKE  | 2850.00 |     4 |
|     10 | CLARK  | 2450.00 |     4 |
|     20 | SCOTT  | 3000.00 |     4 |
|     10 | KING   | 5000.00 |     5 |
|     30 | TURNER | 1500.00 |     3 |
|     20 | ADAMS  | 1100.00 |     1 |
|     30 | JAMES  |  950.00 |     1 |
|     20 | FORD   | 3000.00 |     4 |
|     10 | MILLER | 1300.00 |     2 |
+--------+--------+---------+-------+	

• 第二步：

select 
t.deptno, avg(t.grade) as '平均薪水等级' 
from 
(select e.deptno, e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between losal and hisal) t 
group by t.deptno;
+--------+--------------------+
| deptno | 平均薪水等级       |
+--------+--------------------+
|     10 |             3.6667 |
|     20 |             2.8000 |
|     30 |             2.5000 |
+--------+--------------------+	

第二步中也可以不使用临时表进行查询

select	
	e.deptno, avg(s.grade) as '薪资平均等级' 
from 
	emp e 
join 
	salgrade s 
on 
	e.sal 
between 
	losal 
and 
	hisal
group by
	e.deptno;  
+--------+--------------------+
| deptno | 薪资平均等级       |
+--------+--------------------+
|     10 |             3.6667 |
|     20 |             2.8000 |
|     30 |             2.5000 |
+--------+--------------------+

20.3 select后嵌套子查询

// 找出每个员工所在部门名称，要求显示部门名称，员工姓名
以前使用的方法：

select
  e.ename, d.dname 
from
  emp e
join
  dept d
on 
  e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.02 sec)

select       
  e.ename, (select d.dname from dept d where e.deptno = d.deptno) as dname 
from
  emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.02 sec)

21、union (可以将查询结果集相加)

// 找出工作岗位是salesman，manager的员工

• 第一种：select ename, job from emp where job = 'salesman' or job = 'manager';
• 第二种：`select ename, job from emp where job in(‘salesman’, ‘manager’);

+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.02 sec)

第三种：使用union

select  ename, job from emp where job = 'salesman'
union
select  ename, job from emp where job = 'manager';

union可以用在将两张不相干的表的数据拼接在一起
需要注意的是：两张表需要拼接在一块的数据列必须是一致的。

select ename from emp
union
select dname from dept;
+------------+
| ename      |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
18 rows in set (0.00 sec)

22、limit （重点，分页查询全靠它）

22.1 limit是MySQL特有的，其他数据库没有，不通用。（Oracle中也有相同的机制，关键字是：rownum)

22.2 limit取结果集中的部分数据。这是它的作用。

22.3 limit语法机制

limit startIndex， length
startIndex ：表示起始位置，从0开始，0表示第一条数据
length：表示取几个

案例：取出工资前5名的员工(降序取出前五个)
// 按照工资降序排列

mysql> select ename, sal from emp order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+
14 rows in set (0.00 sec)

// 取出排序中工资的前五个数据
第一种写法：select ename, sal from emp order by sal desc limit 0, 5;
第二种写法：select ename, sal from emp order by sal desc limit 5;

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

22.4 limit是sql语句种最后一个执行的。

select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit 7
…

// 案例：找出工资排名在第四到第九名的员工

select
	ename, job, sal
from 
	emp
order by 
	sal desc
limit
	3, 6;
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| JONES  | MANAGER  | 2975.00 |
| BLAKE  | MANAGER  | 2850.00 |
| CLARK  | MANAGER  | 2450.00 |
| ALLEN  | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| MILLER | CLERK    | 1300.00 |
+--------+----------+---------+
6 rows in set (0.00 sec)

// 分页显示：pageNo, pageSize
每页显示10条数据

第1页 limit 0, 3
第2页 limit 3, 3
第3页 limit 6, 3
第4页 limit 9, 3
…
第pageNO页 (pageSize - 1) * pageNo, pageNO
java代码{
int pageNO = 2; // 页码是2
int pageSize = 10; // 每页显示10条数据
limit (pageNO - 1) * pageSize, pageSize;
}

23、创建表

建表语句：
create table 表名 (
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型，
…
);

如何删除一张表：
drop table if exists t_student; // 如果这张表存在的话，删除。

关于MySQL中的数据类型(以下只说常见的)
int 整数型(对应Java中的int类型)
bigint 长整型(对应Java中的long类型)
float 浮点型(对应Java中的float，double)
char 定长字符串（对应Java中的String类型）
varchar 可变长字符串(对应Java中的StringBuilder和StringBuffer)
date 日期类型(对应Java中的java.sql.date)
BLOB 二进制大对象（存储图片，视频，流媒体信息）Binary Large OBject（对应Java中的Object）
CLOB 字符大对象(存储大文本文件，例如：存储4G的字符串。)Character Large OBject（第一营Java中的Object）

char和varchar如何选择？
在实际开发中，当某个字段的数据长度不发生改变，是定长时，例如：生日，性别等，都使用char数据类型。
当一个字段的数据长度不确定，例如：姓名，简介等，使用varchar类型。

BLOB和CLOB的使用？
电影表：t_move
id(int) name(varchar) playtime(date/char) haibao(BLOB) history(CLOB)
1 蜘蛛侠
2
3

表名在数据库中一般建议使用：t_表名 或者 tbl_表名

创建学生表
学生信息包括：
学号 姓名 性别 班级编号 生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char

create table t_student(
		no bigint,
		name varchar(255),
		sex char(1),
		classno varchar(255),
		birth char(10)
	);

24、insert插入数据

语法：insert into 表名(字段名1， 字段名2，字段名3，…) values(值1，值2，值3，…);
要求：字段的数量和值的数量相同，并且对应的数据类型相同。

insert into t_student (no, name, sex, classno, birth) values(1, 'zhangsan', '1', 'gaosan1ban');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
insert into t_student (no, name, sex, classno, birth) values(1, 'zhangsan', '1', 'gaosan1ban', '1998-12-26');

mysql> select *  from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1998-12-26 |
+------+----------+------+------------+------------+ 

insert into t_student (name, sex, classno, birth, no) values('lisi', '1', 'gaosan1ban', '2002-3-30', 2);
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1998-12-26 |
|    2 | lisi     | 1    | gaosan1ban | 2002-3-30  |
+------+----------+------+------------+------------+

insert into t_student (name) values('wangwu');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1998-12-26 |
|    2 | lisi     | 1    | gaosan1ban | 2002-3-30  |
| NULL | wangwu   | NULL | NULL       | NULL       |
+------+----------+------+------------+------------+

删除表，重新建，重新插入

create table t_student(
	no bigint,
	name varchar(255),
	sex char(1) default 1,
	classno varchar(255),
	birth char(10)
);
insert into t_student (name) values('lisi');
+------+------+------+---------+-------+
| no   | name | sex  | classno | birth |
+------+------+------+---------+-------+
| NULL | lisi | 1    | NULL    | NULL  |
+------+------+------+---------+-------+

需要注意⚠️：
当一条insert语句执行成功之后，表格中必然会多出一行记录
即使这条记录中某些字段是NULL，后期也不能使用insert再进行插入
数据，只能使用update进行数据的更新。

// insert语句插入数据，可以对字段进行省略，但是对后面的values的数量和顺序都有要求。

insert into t_student values(1, 'zhangsan', 1, 'gaosan17', '1998-12-16');
+------+----------+------+----------+------------+
| no   | name     | sex  | classno  | birth      |
+------+----------+------+----------+------------+
| NULL | lisi     | 1    | NULL     | NULL       |
|    1 | zhangsan | 1    | gaosan17 | 1998-12-16 |
+------+----------+------+----------+------------+

// 一次插入多行数据

insert into t_student values(2, 'lisi', 1, 'gaosan16', '1998-06-16'),
		 (3, 'wangwu', 1, 'gaosan18', '1994-06-16'),
		 (4, 'xiaohong', 0, 'gaosan16', '1999-06-06');
mysql> select * from t_student;
+------+----------+------+----------+------------+
| no   | name     | sex  | classno  | birth      |
+------+----------+------+----------+------------+
| NULL | lisi     | 1    | NULL     | NULL       |
|    1 | zhangsan | 1    | gaosan17 | 1998-12-16 |
|    2 | lisi     | 1    | gaosan16 | 1998-06-16 |
|    3 | wangwu   | 1    | gaosan18 | 1994-06-16 |
|    4 | xiaohong | 0    | gaosan16 | 1999-06-06 |
+------+----------+------+----------+------------+

25、表的复制

语法：
create table 表名 as DQL语句;
将查询结果当作表创建出来

create table emp1 as select * from emp;

create table emp2 as select ename, job from emp;
mysql> select * from emp2;
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| ALLEN  | SALESMAN  |
| WARD   | SALESMAN  |
| JONES  | MANAGER   |
| MARTIN | SALESMAN  |
| BLAKE  | MANAGER   |
| CLARK  | MANAGER   |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| TURNER | SALESMAN  |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+

26、将查询结果插入到一张表中

语法：
insert into 表名 DQL语句；

create table dept1 as select * from dept;
insert into dept1 select * from dept;
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

26、修改数据：update

语法格式：update 表名 set 字段名1 = 值1， 字段名2 = 值2 … where 条件;

注意：没有条件，整张表的数据全部更新

案例：将dept1表中的deptno = 10 的数据，dname改为’RENSHIBU’,LOC改为’SHANGHAI’

mysql> update dept1 set dname = 'RENSHIBU', LOC = 'SHANGHAI' where deptno = 10;
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | RENSHIBU   | SHANGHAI |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | RENSHIBU   | SHANGHAI |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

27、删除数据：delete

语法格式：delete from 表名 where 条件;
注意： 没有条件全部删除；

案例：删除10部门数据

delete from dept1 where deptno = 10;
mysql> select * from dept1;
+--------+------------+---------+
| DEPTNO | DNAME      | LOC     |
+--------+------------+---------+
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+

删除所有记录：

delete from dept1;
mysql> select * from dept1;
Empty set (0.00 sec)

如何删除大表中的数据？（重点）

truncate table 表名;	// 表被截断，不可回滚，永久丢失 （truncate截断的意思）
mysql> select * from emp1;
Empty set (0.00 sec)

如何删除表？
drop table 表名；（Oracle支持这种写法）
drop table if exists 表名；（Oracle不支持这种写法）

28、对表结构的操作(DDL)不讲，因为实际开发中对表结构的修改很少，如果修改是对以前设计的否定。

数据查询语言DQL(select)、数据操作语言DML(insert,delete\truncate,update)、数据定义语言DDL(create, drop, alter)

要是修改可以借助工具进行操作，修改表结构的代码不会出现在Java代码当中，出现在Java代码中的sql
包括：insert、delete、update、select（对数据的增删改查）

增删改查有一个术语：
CRUD 即：create(增)、retrieve(检索)、update(修改)、delete(删除)

29、约束（constraint）

29.1 什么是约束？常见的约束有哪些？

在添加表的时候可以给表的任意字段添加相应的约束，添加约束的目的是为了保证表中的数据的合法性，
完整性，有效性。

常见的约束有：

• 非空约束(not null) 约束的字段不能为NULL
• 唯一约束(unique) 约束的字段不能重复
• 主键约束(primary key) 约束的字段既不能重复也不能为NULL(简称：PK)
• 外键约束(foreign key) (简称fk)
• 检查约束(check) 注意：Oracle数据库有check约束，但是MySQL目前不支持check约束。

表格

id username(唯一性约束) password(非空约束)

---

29.2 非空约束 not null

drop table if exists t_user;
// 建表
create table t_user (
	id int,
	username varchar(255) not null,
	password varchar(255)
);

insert into t_user (id, password) values(1, '123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

insert into t_user (id, username, password) values(1, 'lisi', '123');

29.3 唯一性约束 unique

• 唯一约束修饰的字段不能重复，但是可以为NULL，
• 案例：

drop table if exists t_user;
create table t_user (
	id int,
	username varchar(255) unique
);
insert into t_user values(1, 'lisi');
insert into t_user values(1, 'lisi');
ERROR 1062 (23000): Duplicate entry 'lisi' for key 'username'

insert into t_user (id) values(2);
insert into t_user (id) values(3);
insert into t_user (id) values(4);
+------+----------+
| id   | username |
+------+----------+
|    1 | lisi     |
|    2 | NULL     |
|    3 | NULL     |
|    4 | NULL     |
+------+----------+

• 案例： 给两个列或者多个列添加约束

drop table if exists t_user;
create table t_user (
	id int,
	usercode varchar(255),
	username varchar(255),
	unique(usercode, username)	// 表级约束   多个字段联合起来添加一个约束
);

insert into t_user (id, usercode, username) values(1, '111', 'ls');
insert into t_user (id, usercode, username) values(2, '111', 'zs');
insert into t_user (id, usercode, username) values(3, '222', 'ls');
mysql> select * from t_user;
+------+----------+----------+
| id   | usercode | username |
+------+----------+----------+
|    1 | 111      | ls       |
|    2 | 111      | zs       |
|    3 | 222      | ls       |
+------+----------+----------+
111ls和111zs不一样，联合起来的约束

insert into t_user(id, usercode, username) values(4, '111', 'ls');
ERROR 1062 (23000): Duplicate entry '111-ls' for key 'usercode'

drop table if exists t_user;
create table t_user (
	id int,
	usercode varchar(255) unique,
	username varchar(255) unique	// 列级约束
);

insert into t_user (id, usercode, username) values(1, '111', 'ls');
insert into t_user (id, usercode, username) values(2, '111', 'zs');
ERROR 1062 (23000): Duplicate entry '111' for key 'usercode'
mysql> select * from t_user;
+------+----------+----------+
| id   | usercode | username |
+------+----------+----------+
|    1 | 111      | ls       |
+------+----------+----------+

• not null 只有列级约束，没有表级约束

29.4、主键约束

如何给一张表添加主键约束？

drop table if exists t_user;
create table t_user(
	id int primary key,
	username varchar(255),
	email varchar(255)
);

insert into t_user (id, username, email) values(1, 'lisi', 'lisi@163.com');
insert into t_user (id, username, email) values(2, 'wangwu', 'wangwu@163.com');
insert into t_user (id, username, email) values(3, 'zhaoliu', 'zhaoliu@163.com');
select * from t_user;
+----+----------+-----------------+
| id | username | email           |
+----+----------+-----------------+
|  1 | ls       | ls@163.com      |
|  2 | wangwu   | wangwu@163.com  |
|  3 | zhaoliu  | zhaoliu@163.com |
+----+----------+-----------------+

insert into t_user (id, username, email) values(1, 'zhouzhou', 'zhouzhou@163.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into t_user (username, email) values('qianqian', 'qianqian@163.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

⚠️根据以上测试，id字段添加了主键约束，不能为NULL，也不能重复。
主键的特点：不能为NULL，也不能重复

• 主键相关术语
  主键约束：primary key
  主键字段：id字段添加primary key主键约束之后，id字段叫做，主键字段
  主键值：id字段中的每一个值，都是主键值

• 主键有什么用？
  表的设计三范式中，第一范式要求任何一张表都应该有主键。
  主键的作用：主键值是这行记录在这张表中的唯一标识。（就像一个人的身份证号一样）。

• 主键的分类？

  根据主键的字段数量来划分
  单一主键
  复合主键（多个字段联合起来添加一个主键约束）【复合主键不建议使用，因为其违背了三范式】
  根据主键性质划分
  自然主键：主键值最好和业务值没有任何关系的自然数（这种方式是推荐的）
  业务主键：主键值和系统的业务挂钩，例如：身份证号作为主键，银行卡号作为主键（不推荐使用）
  最好不要拿和业务相关的字段作为主键。因为一旦以后的业务发生改变的时候，主键值也需要发生改变，
  但是有时候不能发生变化。

• 注意：一张表的主键约束只能有1个

• 使用表级约束的方式定义主键

drop table if exists t_user;
create table t_user (
	id int,
	username varchar(255),
	primary key(id)
);

insert into t_user(id, username) values(1, 'lisi');
insert into t_user(id, username) values(2, 'zhangsan');
insert into t_user(id, username) values(3, 'wangwu');
insert into t_user(id, username) values(4, 'zhaoliu');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | lisi     |
|  2 | zhangsan |
|  3 | wangwu   |
|  4 | zhaoliu  |
+----+----------+

• 复合主键

drop table if exists t_user;
create table t_user (
	id int,
	username varchar(255),
	password varchar(255),
	primary key(id, username)

);

• mysql提供的自增主键

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment,    // id字段自动维护一个自增的数字，从1开始，以1递增。
	username varchar(255)
);

insert into t_user (username) values('a');
insert into t_user (username) values('a');
insert into t_user (username) values('a');
insert into t_user (username) values('a');
insert into t_user (username) values('a');
select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | a        |
|  3 | a        |
|  4 | a        |
|  5 | a        |
+----+----------+

29.5 外键约束

• 关于外键约束的相关术语：
  外键约束：foreign key
  外键字段：添加有外键约束的字段，叫做外键约束
  外键值：外键字段中的每个值

• 业务背景：
  请设计数据库表：存储所有学生和班级的信息？

  第一种方案：一张表存储所有数据

  no(pk)		name			classno			classname
  --------------------------------------------------------------------------------
  1			zs1			101			高三一班
  2			zs2			101			高三一班
  3			zs3			102			高三一班
  4			zs4			104			高三一班
  5			zs5			102			高三一班
  6			zs6			107			高三一班

缺点：数据冗余（不推荐）

第二种方案：两张表
t_student学生表（子表）
sno(pk) sname classno(给该字段添加外键约束fk)
1 zs1 101
2 zs2 101
3 zs3 103

​ t_class班级表（父表）
​ cno(pk) cname
​ 101 高三一班
​ 102 高三一班
​ 103 高三一班

• 将以上表的建表语句写出来
  t_student表中的classno字段引用了t_class表中的cno字段，此时t_student表叫做子表，
  t_class表叫做父表。

  顺序：
  删除数据的时候先删除子表，再删除父表
  添加数据的时候先添加父表再添加子表。
  删除表的时候，先删除子表，再删除父表。
  添加表的时候，先添加父表，再添加子表。

drop table if exists t_student;
drop table if exists t_class;
create table t_class (
	cno int,
	cname varchar(255),
	primary key(cno)
);

create table t_student (
	sno int,
	sname varchar(255),
	classno int,
	primary key(sno),
	foreign key(classno) references t_class(cno)
);

insert into t_class values(101, 'xxxxxxxxxxxxxxxxxxxxxxxxxxx');
insert into t_class values(102, 'yyyyyyyyyyyyyyyyyyyyyyyyyyy');

insert into t_student values(1, 'ls1', 101);
insert into t_student values(2, 'ls2', 102);
insert into t_student values(3, 'ls3', 102);
insert into t_student values(4, 'ls4', 101);
select * from t_class;
select * from t_student;

insert into t_student values(5, 'ls5', 103);
ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`sqlstudy`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

• 外键值可以为NULL吗？
  外键值可以为NULL

• 外键字段引用其他表中的字段的时候，这个字段必须是主键吗?
  外键引用不一定是主键，但是所引用字段需要具有唯一性

30、存储引擎？

30.1 完整的建表语句、

 CREATE TABLE `t_x` (
  `id` int(11) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

注意：MySQL中凡是标识符都可以使用飘号括起来的，但是其他数据库不行。````最好别用，不通用。
建表的时候可以指定引擎，和字符编码。
MySQL默认的存储引擎是INnoDB，字符集是utf8mb4。

30.2 什么是存储引擎？

存储引擎这个名字只在MySQL中这样叫，在Oracle中由对应的机制，但是不叫存储引擎，没有特殊的名字，就是表的存储方式。

MySQL支持各种不同的存储引擎，但是不同的存储引擎对应不同的存储方式，也有各自的优缺点

• 查看当前MySQL支持的存储引擎？

show engines \G
     *************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
	  XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
	  XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
	  XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
	  XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
	  XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
	  XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
	  XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
	  XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
	  XA: NULL
  Savepoints: NULL
9 rows in set (0.01 sec)
 mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.18    |
+-----------+

mysql的这个版本支持的存储引擎的个数是9个

30.3 常见的存储引擎？

    Engine: MyISAM
	     Support: YES
	     Comment: MyISAM storage engine
	Transactions: NO		// 这种存储引擎不支持事务
		  XA: NO
	  Savepoints: NO
	  
	  
MyIsAM这种存储引擎不支持事务。
	  MyIsAM这种存储引擎是MySQL常用的存储引擎。
	  MyIsAM采用三个文件组织一张表：
		  xxx.frm（存储表格式的文件）
		  xxx.MYD（存储表中数据的文件）
		  xxx.MYI（存储表中索引的文件）
	  优点：可被压缩，节省存储空间。并且可以被转换成只读表，提高检索效率。
	  缺点：不支持事务。
	  ----------------------------------------------------------------------
	  ngine: InnoDB
	     Support: DEFAULT
	     Comment: Supports transactions, row-level locking, and foreign keys
	Transactions: YES
		  XA: YES
	  Savepoints: YES
	  优点：支持事务，行级锁，外键。这种存储引擎，数据安全得到保证。
	  表的结构存储在xxx.frm文件中
	  表的数据存储在tablespace这样的表空间中（逻辑概念）。因此不支持压缩，只读等。
	  这种INnoDB存储引擎，当MySQL数据库崩溃之后，提供数据自动恢复机制。
	  INnoDB支持级联删除和级联更新。
	  -----------------------------------------------------------------------------
	  Engine: MEMORY
	     Support: YES
	     Comment: Hash based, stored in memory, useful for temporary tables
	Transactions: NO
		  XA: NO
	  Savepoints: NO
	  缺点：不支持事务。因为所有数据索引存储在内存当中，数据容易丢失。表结构存储在xxx.frm文件中		
	  优点：查询速度最快
	  以前叫做HEAP引擎。

31、事务（transaction）

31.1 什么是事务？

事务是一个完整的业务逻辑单元，不可再分。
例如：银行账户转账：A账户向B账户转账10000，需要执行两条sql语句，这是一个完整的业务逻辑单元，要么同时失败，要么同时成功，这就是事务
update t_act set balance = balance - 10000 where actno = ‘act_001’;
update t_act set balance = balance + 10000 where actno = ‘act_002’;
以上两条DML语句，必须同时成功，或者同时失败，不允许一条成功一条失败。

要想保证以上两条DML语句同时成功或者同时失败就必须使用数据库的“事务机制”了。

31.2 和事务相关的语句只有：DML语句（delete、insert、update）

为什么?因为这三个语句都是和数据库中的数据相关的
事务的存在是为了保证数据的安全性，完整性。

31.3 假设所有的业务都可以使用一条DML语句完成，还需要事务吗？

不需要事务。
但实际情况不是这样的，通常一件事（事务【业务】）需要多条DML语句才可以完成。

31.4 事务的四大特性：ACID

• A：原子性 【Atomicity】：事务是最小的工作单元，不可再分。
• C：一致性 【consistency】：必须保证多条DML语句同时成功或者同时失败。
• I：隔离性 【Isolation】：事务A和事务B之间具有隔离。
• D：持久性 【durability】：持久性说的是数据最终必须持久化到硬盘文件中，事务才算成功的结束

31.5 关于事务之间的隔离性

事务之间的隔离性存在隔离级别，理论上隔离级别包括4个：

• 第一级别：读未提交（read uncommitted）
  对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
  读未提交存在脏读现象（dirty read）现象：表示读到脏的数据。

• 第二级别：读已提交（read committed）
  对方事务提交之后的数据，我方可以读取到。
  这种隔离级别解决了什么问题：解决了脏读现象。
  读已提交存在的问题是：不可重复读。

• 第三级别：可重复读（repeatable read）
  这种隔离级别解决了：不可重复读问题。
  这种隔离级别存在的问题：读取到的数据是幻想。

• 第四级别：序列化/串行化读(serializable)。
  解决了所有问题
  效率低。需要事务排队。【对一张表操作的一个事务还没有结束，不能再次去操作那张表】

  Oracle默认的事务隔离级别是：读已提交
  MySQL默认的事务隔离级别是：可重复读

  # MySQL查看事务的隔离级别：
  mysql> select @@global.transaction_isolation;    # mysql 8.0版本使用命令
  +--------------------------------+
  | @@global.transaction_isolation |
  +--------------------------------+
  | REPEATABLE-READ                |
  +--------------------------------+
  1 row in set (0.00 sec)
  

多个事务并发⚠️（同一时间段，多个任务同时执行，不是单位时间）运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）并发虽然是必须的，但是存在一下问题：

• 脏读（Dirty read）：一个事务正在访问数据库，并对数据进行了修改，这个修改还没有提交到数据库；另一个事务也访问了这个数据，并使用了这个数据，因为这个数据还没有提交，那么另一个事务读到的这个数据就是脏数据，依据脏数据所进行的操作可能是不正确的。即：使用未提交数据
• 丢失修改（Lost to modify）：指在一个事务读取一个数据的时候，另一个事务也访问了这个数据，那么在第一个事务修改了这个数据，另一个事务也修改了这个数据。第一个事务修改的结果丢失，称为丢失修改。eg:事务1读去A=20，事务2也读取A=20。事务1修改数据为A=A-1，结果是A=19。事务2修改数据为A=A-1，结果为19。即事务1的修改被丢失。即：修改再修改
• 不可重复读（Unrepeatableread）：一个事务中多次读取同一数据结果不一致。即，一个事务两次读取同一数据的间隙，另一事务对这一数据进行了修改，导致读取的数据结果不一致，叫做不可重复读。
• 幻读（Phantom read）：与不可重复读类似。一个事务（T1）读取一个数据几行的时候，另一个事务（T2）修改了数据。在随后的查询中，发现数据发生变化。就像幻觉一样。称为：幻读。

31.6 演示事务

mysql事务默认情况下是自动提交的。（什么是自动提交？只要执行一条DML语句，则提交一次）。
如何关闭自动提交：start transaction

准备表

drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);

演示：MySQL的事务是自动提交的，只要执行一次DML语句，就提交一次。
mysql> insert into t_user (username) values(‘ls’);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | ls       |
+----+----------+
1 row in set (0.00 sec)

mysql> rollback;		// 事务没有回滚成功
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | ls       |
    +----+----------+

演示：使用start transaction关闭自动提交机制
	mysql> start transaction;		// 关闭自动提交机制，开始一条事务
	Query OK, 0 rows affected (0.00 sec)

	mysql> insert into t_user (username) values('wangwu');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t_user(username) values('zhaosi');
	Query OK, 1 row affected (0.00 sec)

	mysql> select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | ls       |
	|  2 | wangwu   |
	|  3 | zhaosi   |
	+----+----------+
	3 rows in set (0.00 sec)

	mysql> rollback;		// 回滚之后，剩余一条数据/记录
	Query OK, 0 rows affected (0.00 sec)

	mysql> select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | ls       |
	+----+----------+

	* 演示commit
	mysql> start transaction;
	Query OK, 0 rows affected (0.00 sec)

	mysql> insert into t_user(username) values('lisi');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t_user(username) values('wangwu');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t_user(username) values('rose');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t_user(username) values('lily');
	Query OK, 1 row affected (0.00 sec)

	mysql> select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | ls       |
	|  4 | lisi     |
	|  5 | wangwu   |
	|  6 | rose     |
	|  7 | lily     |
	+----+----------+
	5 rows in set (0.00 sec)

	mysql> commit;
	Query OK, 0 rows affected (0.01 sec)

	mysql> rollback;		// rollback只能回滚到上一次的提交点 
	Query OK, 0 rows affected (0.00 sec)

	mysql> select * from t_user;
	+----+----------+
	| id | username |
	+----+----------+
	|  1 | ls       |
	|  4 | lisi     |
	|  5 | wangwu   |
	|  6 | rose     |
	|  7 | lily     |
	+----+----------+	

31.5 使用两个事务，演示以上的隔离级别。

• 第一：演示read uncommitted;
  设置事务的隔离级别：set transaction isolation level read uncommitted;
  查看事务的全局隔离级别：select @@global.tx_isolation;
  ±----------------------+
  | @@global.tx_isolation |
  ±----------------------+
  | READ-UNCOMMITTED |
  ±----------------------+

• 第二：演示read committed;

• 第三：演示repeatable read;

• 第四：演示serializable

32、索引

32.1 什么是索引？有什么用？

索引就相当于一本书的目录，通过索引可以快速找到对应的资源。
在数据库方面，查询一张表的时候有两种方式：
第一种：全表扫描
第二种：根据索引检索（效率很高）

索引为什么能提高检索效率？
其实最根本的原理是缩小了扫描范围。

索引虽然可以提高检索效率，但是不能随意的增加索引。索引也是数据库中的对象，
也是需要数据库不断的维护的。是有维护成本的。比如：表中的数据经常被修改，就不适合添加索引
因为数据一旦被修改，索引需要重新排序，进行维护。

添加索引是给某个字段，或者某些字段添加索引。

select ename, sal from emp where ename = 'smith';

当ename字段没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
当ename字段添加了索引，以上sql语句会根据索引进行扫描，快速定位。

32.2 怎么创建索引对象？怎么删除索引对象？

创建索引对象
create index 索引名称 on 表名(字段名);

删除索引对象
drop index 索引名称 on 表名;

32.3 什么时候考虑给字段添加索引？（满足什么条件）

• 数据俩庞大的时候（根据客户的需求，根据线上环境）

• 该字段很少的DML操作的时候。（因为字段进行修改操作，索引也需要维护）

• 该字段经常出现在where子句中。（经常根据哪个字段查询）

32.4 主键和具有unique约束的字段会自动添加索引

根据主键查询效率较高，尽量通过主键检索。

32.5 查看sql语句的执行计划

mysql> explain  select ename from emp where ename = 'smith';
	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
	| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
	|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
	+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

• 创建索引，通过索引去查
  给工资sal字段添加索引

  create index emp_sal_index on emp(sal);
  

  查看sql的执行计划。

  mysql> explain select ename from emp where sal = 5000;
  +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
  |  1 | SIMPLE      | emp   | NULL       | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 |   100.00 | NULL  |
  +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
  

32.6 索引底层采用的数据结构：B + Tree

32.7 索引的实现原理

通过B Tree索效扫描范围，底层索引进行排序，分区，索引会携带数据在表中的“物理地址”，
最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的
select ename from emp where ename = ‘smith’;
通过索引转换为：
select ename from emp where 物理地址 = 0 x 3;

32.8 索引分类？

• 单一索引：给单个字段添加索引
• 复合索引：给多个字段添加1个索引
• 主键索引：主键上会自动添加索引
• 唯一索引：有unique约束的字段上会自动添加索引

32.9 索引什么时候失效？

select ename from emp where ename = ‘%A%’; // 模糊查询会使索引失效，即使给字段ename添加了索引
模糊查询的时候第一个通配符使用的是%，会使索引失效。

33 视图（view）

33.1 什么是视图？

站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

33.2 怎么创建视图？怎么删除视图？

create view myview as select empno, ename from emp;  // 创建视图
drop view myview;  // 删除视图

注意：只有DQL语句才能以视图对象的方式创建出来。

33.3 对视图进行增删改查，会影响到原表中的数据。（通过视图影响原表中的数据的，不是直接操作原表）

可以对视图进行CRUD操作。

33.4 面向视图操作？

mysql> select * from myview;
+--------+-------+
| ename  | empno |
+--------+-------+
| SMITH  |  7369 |
| ALLEN  |  7499 |
| WARD   |  7521 |
| JONES  |  7566 |
| MARTIN |  7654 |
| BLAKE  |  7698 |
| CLARK  |  7782 |
| SCOTT  |  7788 |
| KING   |  7839 |
| TURNER |  7844 |
| ADAMS  |  7876 |
| JAMES  |  7900 |
| FORD   |  7902 |
| MILLER |  7934 |
+--------+-------+

create table emp1 as select * from emp;
create view myview1 as select ename, empno, sal from emp1;
update myview1 set ename = 'hehehe', sal = 20 where empno = 7329; // 通过视图修改原表中的数据
delete from myview1 where empno = 7369;  // 通过视图删除表中的数据 

33.5 视图的作用？

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，Java程序员只对视图对象进行
CRUD。

34 DBA命令

34.1 将数据库中的数据导出

在windows的dos命令窗口上执行下面语句（导出整个库）
mysqldump sqlstudy>D:\sqlstudy.sql -uroot -p123456

在windows的dos命令窗口上执行下面语句（导出指定数据库中的指定表）
mysqldump sqlstudy>D:\sqlstudy.sql emp -uroot -p123456

34.2 导入数据

 create database sqlstudy;
 use sqlstudy;
 source D:\sqlstudy.sql

34.3 导出数据

35 数据库设计三范式（重点内容，面试经常提问）

35.1 什么是设计范式

设计表的依据，根据这个三范式设计出的表不会出现数据冗余。

35.2 三范式都是哪些？

• 第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
• 第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖。
  多对多? 关系表，两个外键
• 第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。
  一对多？两张表，多的表加外键。

在实际开发中，以客户的需求为主，有时候会拿数据冗余换执行速度。

35.3 一对一怎么设计？

一对一设计两种方案之：主键共享

t_user_login （用户登录表）
    id(pk)	username	password
    --------------------------------------------
    1		ls			123
    2		zs       	456

    
t_user_detail（用户详细信息表）
    id(pk + fk)	realname	tel
    ------------------------------------
    1		李四		111111111111
    2		张三		222222222222

一对一设计两种方案之：外键唯一

t_user_login （用户登录表）
    id(pk)	username	password
    --------------------------------------------
    1		ls			123
    2		zs       	456

t_user_detail（用户详细信息表）
    id(pk)	realname	tel		user_id(fk + unique) 【既是外键又是唯一性约束】
    ---------------------------------------------------------------------------------------------
    1		李四		111111111111	1
    2		张三		222222222222	2