Mysql的使用

数据库概述

sql、DB、DBMS分别是什么，他们之间的关系

DB：DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）
DBMS：DataBase Management System（数据库管理系统，常见的有：Oracle、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql）
SQL：结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。SQL属于高级语言。SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成）
DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。

什么是表

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

学号（int）	姓名（varchar）	年龄
110	张三	20
120	李四	21

每一个字段应该包括哪些属性？
字段名、数据类型、字段约束/字段长度
字段约束/字段长度

SQL分类

数据查询语言(DQL-Data Query Language)
代表关键字:select
数据操纵语言(DML-Data Manipulation Language)
代表关键字:insert,delete,update
数据定义语言(DDL-Data Definition Language)
代表关键字:create ,drop,alter,
事务控制语言(TCL-Transactional Control Language)
代表关键字:commit ,rollback;
数据控制语言(DCL-Data Control Language)
代表关键字:grant,revoke.

导入数据

第一步：登录mysql数据库管理系统
命令窗口：

mysql -uroot -p

第二步：查看有哪些数据库

show databases;
(这个不是SQL语句，属于Mysql的命令)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| algorithm_box      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

第三步：创建数据库

create database baishu_demo;

mysql> create database baishu_demo;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| algorithm_box      |
| baishu_demo        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

第四步：使用baishu_demo数据

use baishu_demo;
第五步：查看当前使用的数据中有哪些表？

show tables;

第六步：初始化数据

source ~/sql/baishu_demo.sql;

mysql> show tables;
+-----------------------+
| Tables_in_baishu_demo |
+-----------------------+
| DEPT                  |
| EMP                   |
| SALGRADE              |
+-----------------------+
3 rows in set (0.00 sec)

什么是sql脚本？

当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

删除数据库

drop database baishu_demo;

查看表结构

+-----------------------+
| Tables_in_baishu_demo |
+-----------------------+
| 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    |       | 部门位置
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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    |       | 部门编号
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> desc SALGRADE;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       | 等级
| LOSAL | int(11) | YES  |     | NULL    |       | 最低薪资
| HISAL | int(11) | YES  |     | NULL    |       | 最高薪资
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

常用命令

查看mysql版本

mysql -V
mysql –version

创建数据库

1.create database 数据库名称;
create database baishu_demo;
2.use 数据库名称
use baishu_demo;
在数据库中建立表，因此创建表的时候必须要先选择数据库。

查询当前使用的数据库

select database();
查询数据库版本也可以使用
select version();

终止一条语句

如果想要终止一条正在编写的语句，可键入\c。

退出mysql

可使用\q、QUIT或EXIT

查看建表语句

show create table table name;

简单的查询

语法格式:select 字段名1,字段名2,字段名3,…from 表名;

提示：
1.任何一条sql语句以“；”结尾。
2.sql语句不区分大小写。

查询一个字段

select ename from EMP;

Select语句后面跟的是字段名称，select是关键字，select和字段名称之间采用空格隔开，from表示将要查询的表，它和字段之间采用空格隔开

查询多个字段

select empnp, ename from EMP;

查询多个字段，select中的字段采用逗号间隔即可，最后一个字段，也就是在from前面的字段不能使用逗号了。

查询全部字段

select * from EMP;

采用select * from emp，虽然简单，但是*号不是很明确，建议查询全部字段将相关字段写到select语句的后面,在以后java连接数据库的时候,是需要在java程序中编写SQL语句的,这个时候编写的SQL语句不建议使用select * 这种形式,建议写明字段,这样可读性强.

计算员工的年薪

列出员工的编号，姓名和年薪

select empno, ename, sal*12 from EMP;

将查询出来的字段显示为中文

select empno as ‘员工编号’, ename as ‘员工姓名’, sal*12 as ‘年薪’ from EMP;

注意:字符串必须添加单引号 | 双引号

条件查询

语法格式：

select
字段，字段…
from
表名
where
条件
执行顺序：先from，然后where，最后select

找出工资高于3000的员工
select ename,sal from EMP where sal >= 3000;
查询SMITH的工资
select sal from EMP where ename = ‘SMITH’
找出工资在1100和3000之间的员工
select ename from EMP where sal between 1100 and 3000 (between and 是闭区间)

支持如下运算符：

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

等号操作符

查询job为MANAGER的员工

select empno,ename,job from EMP where job = ‘manager’

mysql> select empno,ename,job from EMP where job = 'manager';
+-------+-------+---------+
| empno | ename | job     |
+-------+-------+---------+
|  7566 | JONES | MANAGER |
|  7698 | BLAKE | MANAGER |
|  7782 | CLARK | MANAGER |
+-------+-------+---------+
3 rows in set (0.00 sec)

<>操作符

查询薪水不等于5000的员工

select empno,ename,sal from EMP where sal<>5000;

1. mysql> select empno,ename,sal from EMP where sal<>5000;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
13 rows in set (0.00 sec)


2. mysql> select empno, ename, sal from EMP where sal != 5000;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
13 rows in set (0.00 sec)

between … and …操作符

查询薪水为1600到3000的员工(第一种方式，采用>=和<=)

select empno, ename, sal from EMP where sal between 1600 and 3000;

mysql> select empno, ename, sal from EMP where sal between 1600 and 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7499 | ALLEN | 1600.00 |
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
6 rows in set (0.00 sec)

is null

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

查询津贴为空的员工

select * from EMP where comm=null;
mysql> select * from EMP where COMM is null;

mysql> select * from EMP where comm=null;
Empty set (0.00 sec)
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 |
+-------+--------+-----------+------+------------+---------+------+--------+
10 rows in set (0.00 sec)

and

and表示并且的含义，表示所有的条件必须满足

工作岗位为MANAGER,薪水大于2500的员工

select ename from EMP where job = ‘manager’ and sal > 2500

mysql> select ename from EMP where job = 'manager' and sal > 2500;
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
+-------+
2 rows in set (0.00 sec)

or

or，只要满足条件即可,相当于包含

查询出job为manager或者job为salesman的员工

select * from EMP where job = ‘manager’ or job = ‘salesman’;

mysql> select * from EMP where job = 'manager' or job = 'salesman';
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  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 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.01 sec)

表达式的优先级

关于运算符的问题：不用记，没有把握尽量采用括号

查询薪水大于1800，并且部门代码为20或30的员工

select * from EMP where sal > 1800 and (DEPTNO = 20 or DEPTNO = 30);

mysql> select * from EMP where sal > 1800 and (DEPTNO = 20 or DEPTNO = 30);
+-------+-------+---------+------+------------+---------+------+--------+
| 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 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)

in

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

查询出job为manager或者job为salesman或者job为clerk的员工

select * from EMP where job in (‘manager’,’salesman’,’clerk’);

mysql> select * from EMP where job in ('manager','salesman','clerk');
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  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 |
+-------+--------+----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)

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

select * from EMP where sal in (1600,3000);

mysql> select * from EMP where sal in (1600,3000);
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7788 | SCOTT | ANALYST  | 7566 | 1987-04-19 | 3000.00 |   NULL |     20 |
|  7902 | FORD  | ANALYST  | 7566 | 1981-12-03 | 3000.00 |   NULL |     20 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)

in 子句中是变量

d.matCodes: 为数组
var _matCodes = d.matCodes.toString();
var _orderId = d.orderId
var sqlCapacity = `select * from MATDB where Matcode in (?) AND ORDERID = '${_orderId}'`
connection.query(sqlCapacity,[d.matCodes], function(err, results) {
  if(err) {
    return res.json({
        code: -2,
        message: '查询失败',
    })
  }
  res.json({
      code: 200,
      message: '查询成功',
      data: results,
  })
});

not

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

select * from EMP where sal <> 1600 and sal <> 3000;
select * from EMP where not (sal = 1600 or sal = 3000);
select * from EMP where sal not in (1600, 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11 rows in set (0.00 sec)

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

select * from EMP where comm is not 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 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)

like

Like可以实现模糊查询，like支持%和下划线匹配

查询姓名以M开头所有的员工
select * from EMP where ename like ‘M%’;

mysql> select * from EMP where ename like 'M%';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+
2 rows in set (0.00 sec)

查询姓名以N结尾的所有的员工

select * from EMP where ename like ‘%N’;
查询姓名中包含O的所有的员工

select * from EMP where ename like ‘%O%’;
查询姓名中第二个字符为A的所有员工

select * from EMP where ename like ‘_A%’;

Like中%和下划线的差别？
%匹配任意字符出现的个数，下划线只匹配一个字符，Like 中的表达式必须放到单引号中|双引号中

排序数据

单一字段排序

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

按照薪水由小到大排序(系统默认由小到大)

select * from EMP order by sal;

mysql> select * from EMP order by sal;
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)

select * from EMP where job = ‘manager’ order by sal;

mysql> select * from EMP where job = 'manager' order by sal;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  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 |
+-------+-------+---------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

如果包含where语句order by必须放到where后面，如果没有where语句order by放到表的后面

按照多个字段排序，如：首先按照job排序，再按照sal排序

select * from EMP order by job,sal;

手动指定排序顺序

按照薪水由小到大排序,从大到小

select * from EMP order by sal asc;
select * from EMP order by sal desc;
mysql> select * from EMP order by sal asc;

+——-+——–+———–+——+————+———+———+——–+
| 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 |
+——-+——–+———–+——+————+———+———+——–+
14 rows in set (0.00 sec)

多个字段排序顺序

按照job和薪水倒序

select * from EMP order by job desc, sal desc;
如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序

使用字段的位置来排序

按照薪水升序

select * from EMP order by 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

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

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

函数	功能
count	取得记录数
sum	求和
avg	取平均
max	取最大的数
min	取最小的数
注意：

分组函数自动忽略空值，不需要手动的加where条件排除空值；
分组函数不能直接使用在where关键字后面。
count

取得所有的员工数

select count(*) from EMP;

mysql> select count(*) from EMP;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

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

取得津贴不为null员工数

select count(comm) from EMP;

mysql> select count(comm) from EMP;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

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

取得工作岗位的个数

select count(distinct job) from EMP;

mysql> select count(distinct job) from EMP;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)

sum

Sum可以取得某一个列的和，null会被忽略

取得薪水的合计

select sum(sal) from EMP;

mysql> select sum(sal) from EMP;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

取得津贴的合计

select sum(comm) from EMP;

mysql> select sum(comm) from EMP;
+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+
1 row in set (0.00 sec)

null会被忽略

取得薪水的合计

select sum(sal+comm) from EMP;

mysql> select sum(sal+comm) from EMP;
+---------------+
| sum(sal+comm) |
+---------------+
|       7800.00 |
+---------------+
1 row in set (0.00 sec)

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

select sum(sal+IFNULL(comm, 0)) from EMP;

mysql> select sum(sal+IFNULL(comm, 0)) from EMP;
+--------------------------+
| sum(sal+IFNULL(comm, 0)) |
+--------------------------+
|                 31225.00 |
+--------------------------+
1 row in set (0.00 sec)

avg

取得某一列的平均值

取得平均薪水

select avg(sal) feom EMP;

mysql> select avg(sal) from EMP;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

max

取得某个一列的最大值

取得最高薪水

mysql> select max(sal) from EMP;

mysql> select max(sal) from EMP;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

取得最晚入职得员工

select max(str_to_date (hiredate, ‘%Y-%m-%d’)) from EMP;

mysql> select max(str_to_date (hiredate, '%Y-%m-%d')) from EMP;
+-----------------------------------------+
| max(str_to_date (hiredate, '%Y-%m-%d')) |
+-----------------------------------------+
| 1987-05-23                              |
+-----------------------------------------+
1 row in set (0.00 sec)

min

取得某个一列的最小值

取得最低薪水

select min(sal) from EMP;

mysql> select min(sal) from EMP;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

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

select min(str_to_date(hiredate, ‘%Y-%m-%d’)) from EMP;

mysql> select min(str_to_date(hiredate, '%Y-%m-%d')) from EMP;
+----------------------------------------+
| min(str_to_date(hiredate, '%Y-%m-%d')) |
+----------------------------------------+
| 1980-12-17                             |
+----------------------------------------+
1 row in set (0.00 sec)

组合聚合函数

可以将这些聚合函数都放到select中一起使用

select count(*),sum(sal),avg(sal),max(sal),min(sal) from EMP;

mysql> select count(*),sum(sal),avg(sal),max(sal),min(sal) from EMP;
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal)    | max(sal) | min(sal) |
+----------+----------+-------------+----------+----------+
|       14 | 29025.00 | 2073.214286 |  5000.00 |   800.00 |
+----------+----------+-------------+----------+----------+
1 row in set (0.00 sec)

分组查询

分组查询主要涉及到两个子句，分别是：group by和having

group by

取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

select job,sum(sal) from EMP group by job;

mysql> select job,sum(sal) from EMP group by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| ANALYST   |  6000.00 |
| CLERK     |  4150.00 |
| MANAGER   |  8275.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
+-----------+----------+
5 rows in set (0.00 sec)

如果使用了order by，order by必须放到group by后面

1
2

mysql> select job,sum(sal) from EMP order by job group by job;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by job' at line 1

mysql> select job,sum(sal) from EMP group by job order by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| ANALYST   |  6000.00 |
| CLERK     |  4150.00 |
| MANAGER   |  8275.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
+-----------+----------+
5 rows in set (0.00 sec)

按照工作岗位和码分组，取得的工资合计

select job,deptno,sum(sal) from EMP group by job,deptno;

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
以上SQL语句在Oracle数据库中无法执行，执行报错。
以上SQL语句在Mysql数据库中可以执行，但是执行结果矛盾。
在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。

mysql> select job,deptno,sum(sal) from EMP group by job,deptno;
+-----------+--------+----------+
| job       | deptno | sum(sal) |
+-----------+--------+----------+
| ANALYST   |     20 |  6000.00 |
| CLERK     |     10 |  1300.00 |
| CLERK     |     20 |  1900.00 |
| CLERK     |     30 |   950.00 |
| MANAGER   |     10 |  2450.00 |
| MANAGER   |     20 |  2975.00 |
| MANAGER   |     30 |  2850.00 |
| PRESIDENT |     10 |  5000.00 |
| SALESMAN  |     30 |  5600.00 |
+-----------+--------+----------+
9 rows in set (0.00 sec)

having

如果想对分组数据再进行过滤需要使用having子句

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

select job,avg(sal) from EMP group by job having avg(sal) > 2000;

mysql> select job,avg(sal) from EMP group by job where avg(sal) > 2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where avg(sal) > 2000' at line 1
mysql> select job,avg(sal) from EMP group by job having avg(sal) > 2000;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
3 rows in set (0.00 sec)

分组函数的执行顺序：
根据条件查询数据
分组
采用having过滤，取得正确的数据

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的过滤是专门对分组之后的数据进行过滤的。

连接查询

什么是连接查询

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

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

连接查询的分类

根据语法出现的年代来划分的话，包括：

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

在表的连接查询方面有一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）

案例：找出每一个员工的部门名称，要求显示员工名和部门名。

EMP表
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+

DEPT表
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

select ename,dname from emp,dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
............
56 rows in set (0.00 sec)

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

关于表的别名：
select e.ename,d.dname from emp e,dept d;
表的别名有什么好处？
```
  第一：执行效率高。
  第二：可读性好。
```

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

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？

不会，次数还是56次。只不过显示的是有效记录。

案例：找出每一个员工的部门名称，要求显示员工名和部门名。

select	
	e.ename,d.dname
from
	emp e , dept d
where
	e.deptno = d.deptno; //SQL92，以后不用。

+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |

内连接之等值连接

最大特点是：条件是等量关系。
案例：查询每个员工的部门名称，要求显示员工名和部门名。


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;

	// inner可以省略的，带着inner目的是可读性好一些。
	select 
		e.ename,d.dname
	from
		emp e
	inner join
		dept d
	on
		e.deptno = d.deptno;
	
	语法：
		...
			A
		join
			B
		on
			连接条件
		where
			...
	
	SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了。

	+--------+------------+
	| ename  | dname      |
	+--------+------------+
	| CLARK  | ACCOUNTING |
	| KING   | ACCOUNTING |
	| MILLER | ACCOUNTING |
	| SMITH  | RESEARCH   |
	| JONES  | RESEARCH   |
	| SCOTT  | RESEARCH   |
	| ADAMS  | RESEARCH   |
	| FORD   | RESEARCH   |
	| ALLEN  | SALES      |
	| WARD   | SALES      |
	| MARTIN | SALES      |
	| BLAKE  | SALES      |
	| TURNER | SALES      |
	| JAMES  | SALES      |
	+--------+------------+

内连接之非等值连接

最大的特点是：连接条件中的关系是非等量关系。
案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

mysql> select ename,sal from emp; e
+--------+---------+
| 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; s
+-------+-------+-------+ 
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

select 
	e.ename,e.sal,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

// inner可以省略
select 
	e.ename,e.sal,s.grade
from
	emp e
inner 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 |
+--------+---------+-------+

自连接

最大的特点是：一张表看做两张表。自己连接自己。
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

mysql> select empno,ename,mgr from emp;
emp a 员工表
+-------+--------+------+
| 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 |
+-------+--------+------+
emp b 领导表
+-------+--------+
| empno | ename  |
+-------+--------+
|  7566 | JONES  |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7902 | FORD   |
+-------+--------+

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

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  |
+--------+--------+

外连接

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

  内连接:
  假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
  AB两张表没有主副之分，两张表是平等的。  
外连接: 
    假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
    的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
外连接的分类？
    左外连接（左连接）：表示左边的这张表是主表。
    右外连接（右连接）：表示右边的这张表是主表。
左连接有右连接的写法，右连接也会有对应的左连接的写法。

案例：找出每个员工的上级领导？（所有员工必须全部查询出来。）

emp a 员工表
+-------+--------+------+
| 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 |
+-------+--------+------+
emp b 领导表
+-------+--------+
| empno | ename  |
+-------+--------+
|  7566 | JONES  |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7788 | SCOTT  |
|  7839 | KING   |
|  7902 | FORD   |
+-------+--------+

内连接：
select 
	a.ename '员工', b.ename '领导'
from
	emp a
join
	emp b
on
	a.mgr = b.empno;

外连接：（左外连接/左连接）
select 
	a.ename '员工', b.ename '领导'
from
	emp a
left join
	emp b
on
	a.mgr = b.empno;

// outer是可以省略的。
select 
	a.ename '员工', b.ename '领导'
from
	emp a
left outer 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;

// outer可以省略。
select 
	a.ename '员工', b.ename '领导'
from
	emp b
right outer 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  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

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

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

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
DEPT
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

select 
	d.*
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	e.empno is null;

+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+

三张表怎么连接查询

案例：找出每一个员工的部门名称以及工资等级。

EMP e
+-------+--------+---------+--------+
| 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 d
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
SALGRADE s
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

注意，解释一下：
	....
		A
	join
		B
	join
		C
	on
		...
	
	表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

	select 
		e.ename,d.dname,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      | grade |
	+--------+------------+-------+
	| SMITH  | RESEARCH   |     1 |
	| ALLEN  | SALES      |     3 |
	| WARD   | SALES      |     2 |
	| JONES  | RESEARCH   |     4 |
	| MARTIN | SALES      |     2 |
	| BLAKE  | SALES      |     4 |
	| CLARK  | ACCOUNTING |     4 |
	| SCOTT  | RESEARCH   |     4 |
	| KING   | ACCOUNTING |     5 |
	| TURNER | SALES      |     3 |
	| ADAMS  | RESEARCH   |     1 |
	| JAMES  | SALES      |     1 |
	| FORD   | RESEARCH   |     4 |
	| MILLER | ACCOUNTING |     2 |
	+--------+------------+-------+

案例：找出每一个员工的部门名称、工资等级、以及上级领导。

select 
	e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp e1
on
	e.mgr = e1.empno;

+--------+------------+-------+-------+
| 员工      | dname      | grade | 领导    |
+--------+------------+-------+-------+
| SMITH  | RESEARCH   |     1 | FORD  |
| ALLEN  | SALES      |     3 | BLAKE |
| WARD   | SALES      |     2 | BLAKE |
| JONES  | RESEARCH   |     4 | KING  |
| MARTIN | SALES      |     2 | BLAKE |
| BLAKE  | SALES      |     4 | KING  |
| CLARK  | ACCOUNTING |     4 | KING  |
| SCOTT  | RESEARCH   |     4 | JONES |
| KING   | ACCOUNTING |     5 | NULL  |
| TURNER | SALES      |     3 | BLAKE |
| ADAMS  | RESEARCH   |     1 | SCOTT |
| JAMES  | SALES      |     1 | BLAKE |
| FORD   | RESEARCH   |     4 | JONES |
| MILLER | ACCOUNTING |     2 | CLARK |
+--------+------------+-------+-------+

子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表

在where语句中使用子查询，也就是在where语句中加入select语句

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

首先取得管理者的编号

select distinct MGR from EMP where MGR is not null;

mysql> select distinct MGR from EMP where MGR is not null;
+------+
| MGR  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
+------+
6 rows in set (0.00 sec)

查询员工编号

select EMPNO,ENAME from EMP where EMPNO in (select distinct MGR from EMP where MGR is not null);

mysql> select EMPNO,ENAME from EMP where EMPNO in (select distinct MGR from EMP where MGR is not null);
+-------+-------+
| EMPNO | ENAME |
+-------+-------+
|  7902 | FORD  |
|  7698 | BLAKE |
|  7839 | KING  |
|  7566 | JONES |
|  7788 | SCOTT |
|  7782 | CLARK |
+-------+-------+
6 rows in set (0.00 sec)

查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水
实现思路：

取得平均薪水

select avg(sal) from EMP;

mysql> select avg(sal) from EMP;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

取得大于平均薪水的员工

select empno, ename, sal from EMP where sal > (select avg(sal) from EMP);

mysql>  select empno, ename, sal from EMP where sal > (select avg(sal) from EMP);
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
6 rows in set (0.00 sec)

在from语句中使用子查询，可以将该子查询看做一张表

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

首先取得管理者的编号，去除重复的

select distinct mgr from EMP where mgr is not null;

将以上查询作为一张表，放到from语句的后面

select e.empno,e.ename from EMP e join (select distinct mgr from EMP where mgr is not null) m on e.empno = m.mgr;

mysql> select e.empno,e.ename from EMP e join (select distinct mgr from EMP where mgr is not null) m on e.empno = m.mgr;
+-------+-------+
| empno | ename |
+-------+-------+
|  7902 | FORD  |
|  7698 | BLAKE |
|  7839 | KING  |
|  7566 | JONES |
|  7788 | SCOTT |
|  7782 | CLARK |
+-------+-------+
6 rows in set (0.00 sec)

查询各部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

首先取得各个部门平均薪水

select deptno,avg(sal) from EMP group by deptno

mysql> select deptno,avg(sal) from EMP group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级

select m.deptno,avg_sal,n.grade from (select deptno,avg(sal) avg_sal from EMP group by deptno) m join SALGRADE n on (m.avg_sal between n.LOSAL and n.HISAL);

mysql> select m.deptno,avg_sal,n.grade from (select deptno,avg(sal) avg_sal from EMP group by deptno) m join SALGRADE n on (m.avg_sal between n.LOSAL and n.HISAL);
+--------+-------------+-------+
| deptno | avg_sal     | grade |
+--------+-------------+-------+
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

在select语句中使用子查询

查询员工信息，并显示出员工所属的部门名称
第一种做法，将员工表和部门表连接

select e.ename, d.dname from EMP e, dept d where e.deptno=d.deptno;
第二种做法，在select语句中再次嵌套select语句完成部分名称的查询
select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from EMP e;

union合并集合（相加）

查询job包含MANAGER和包含SALESMAN的员工

select * from EMP where job in(‘MANAGER’, ‘SALESMAN’);

mysql> select * from EMP where job in('MANAGER', 'SALESMAN');
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  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 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.00 sec)

采用union来合并

select * from EMP where job=’MANAGER’
union
select * from EMP where job=’SALESMAN’

mysql> select * from EMP where job='MANAGER'
    -> union
    -> select * from EMP where job='SALESMAN';
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  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 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.00 sec)

limit的使用

mysql提供了limit，主要用于提取前几条或者中间某几行数据

select * from table limit m,n
其中m是记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条
select * from tablename limit 2,4
即取出第3条至第6条，4条记录

取得前5条数据

select * from EMP limit 0,5

mysql> select * from EMP limit 5;
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from EMP limit 0,5;
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)

从第二条开始取两条数据

select * from EMP limit 1,2;

mysql> select * from EMP limit 1,2;
+-------+-------+----------+------+------------+---------+--------+--------+
| 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 |
+-------+-------+----------+------+------------+---------+--------+--------+
2 rows in set (0.00 sec)

取得薪水最高的前5名

select * from EMP order by sal desc limit 5;

mysql> select * from EMP order by sal desc limit 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.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 |
+-------+-------+-----------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)

表

创建表

语法格式

create table tableName(
  columnName dataType(length),
  ......
  columnName dataType(length)
);
set character_set_results='gbk';

show variables like '%char%'

创建表的时候，表中有字段，每一个字段有：
     * 字段名
     * 字段数据类型
     * 字段长度限制
     * 字段约束

MySql常用数据类型

类型	描述
Char(长度)	定长字符串，存储空间大小固定，适合作为主键或外键
Varchar(长度)	变长字符串，存储空间等于实际数据空间
double(有效数字位数，小数位)	数值型
Float(有效数字位，小数位)	数值型
Int(长度)	整型
bigint(长度)	长整型
Date	日期型年月日
DateTime	日期型年月日时分秒毫秒
time	日期型时分秒
BLOB	Binary Large OBject（二进制大对象）
CLOB	Character Large OBject（字符大对象）

建立学生信息表

建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识别

mysql> create table t_student
    -> (
    -> student_id int(10),
    -> student_name varchar(20),
    -> sex char(2),
    -> birthday date,
    -> email varchar(30),
    -> class_id int(3)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t_student;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| student_id   | int(10)     | YES  |     | NULL    |       |
| student_name | varchar(20) | YES  |     | NULL    |       |
| sex          | char(2)     | YES  |     | NULL    |       |
| birthday     | date        | YES  |     | NULL    |       |
| email        | varchar(30) | YES  |     | NULL    |       |
| class_id     | int(3)      | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

向t_student表中加入数据

 mysql> insert into t_student(student_id,student_name,sex,birthday,email,class_id) values (1001,'zhangsan','m','1988-01-01','qqq@163.com',10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
+------------+--------------+------+------------+-------------+----------+
| student_id | student_name | sex  | birthday   | email       | class_id |
+------------+--------------+------+------------+-------------+----------+
|       1001 | zhangsan     | m    | 1988-01-01 | qqq@163.com |       10 |
+------------+--------------+------+------------+-------------+----------+
1 row in set (0.00 sec)

向t_student表中加入数据（使用默认值）

mysql> drop table if exists t_student
    -> create table t_student( student_id int(10), student_name varchar(20), sex char(2) default 'm', birthday date, email varchar(30), classes_id int(3) )
    -> insert into t_student(student_id, student_name, birthday, email, classes_id) values (1002,'zhangsan','1988-01-01','qqq@163',10);
mysql> select * from t_student;
+------------+--------------+------+------------+---------+------------+
| student_id | student_name | sex  | birthday   | email   | classes_id |
+------------+--------------+------+------------+---------+------------+
|       1002 | zhangsan     | m    | 1988-01-01 | qqq@163 |         10 |
+------------+--------------+------+------------+---------+------------+
1 row in set (0.00 sec)

增加/删除/修改表结构

采用alter table 来增加/删除/修改表结构，不影响表中的数据

添加字段

如：需求发生改变，需要向t_student中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40)

alter table t_student add contact_tel varchar(40);

mysql> alter table t_student add contact_tel varchar(40);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| student_id   | int(10)     | YES  |     | NULL    |       |
| student_name | varchar(20) | YES  |     | NULL    |       |
| sex          | char(2)     | YES  |     | m       |       |
| birthday     | date        | YES  |     | NULL    |       |
| email        | varchar(30) | YES  |     | NULL    |       |
| classes_id   | int(3)      | YES  |     | NULL    |       |
| contact_tel  | varchar(40) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

用mysql创建完表后，如何设置主键

alter table t_student add PRIMARY KEY (student_id);

mysql> alter table t_student add PRIMARY KEY (student_id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10)      | NO   | PRI | NULL    |       |
| student_name | varchar(100) | YES  |     | NULL    |       |
| sex          | char(2)      | YES  |     | m       |       |
| birthday     | date         | YES  |     | NULL    |       |
| email        | varchar(100) | YES  |     | NULL    |       |
| classes_id   | int(3)       | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改字段

如：student_name无法满足需求，长度需要更改为100

alter table t_student modify student_name varchar(100);

mysql> desc t_student;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| student_id   | int(10)     | YES  |     | NULL    |       |
| student_name | varchar(20) | YES  |     | NULL    |       |
| sex          | char(2)     | YES  |     | m       |       |
| birthday     | date        | YES  |     | NULL    |       |
| email        | varchar(30) | YES  |     | NULL    |       |
| classes_id   | int(3)      | YES  |     | NULL    |       |
| contact_tel  | varchar(40) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> alter table t_student modify student_name varchar(100);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10)      | YES  |     | NULL    |       |
| student_name | varchar(100) | YES  |     | NULL    |       |
| sex          | char(2)      | YES  |     | m       |       |
| birthday     | date         | YES  |     | NULL    |       |
| email        | varchar(30)  | YES  |     | NULL    |       |
| classes_id   | int(3)       | YES  |     | NULL    |       |
| contact_tel  | varchar(40)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

删除字段

如：删除联系电话字段

alter table t_student drop contact_tel;

mysql> desc t_student;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10)      | YES  |     | NULL    |       |
| student_name | varchar(100) | YES  |     | NULL    |       |
| sex          | char(2)      | YES  |     | m       |       |
| birthday     | date         | YES  |     | NULL    |       |
| email        | varchar(30)  | YES  |     | NULL    |       |
| classes_id   | int(3)       | YES  |     | NULL    |       |
| contact_tel  | varchar(40)  | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> alter table t_student drop contact_tel;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| student_id   | int(10)      | YES  |     | NULL    |       |
| student_name | varchar(100) | YES  |     | NULL    |       |
| sex          | char(2)      | YES  |     | m       |       |
| birthday     | date         | YES  |     | NULL    |       |
| email        | varchar(30)  | YES  |     | NULL    |       |
| classes_id   | int(3)       | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

添加、修改和删除表数据

insert

insert语法格式

insert into 表名(字段，…) values (值…)
省略字段的插入
不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到insert语句

insert into EMP values(9999,’zhangsan’,’MANAGER’,null,null,3000,500,10);

mysql> insert into EMP values(9999,'zhangsan','MANAGER',null,null,3000,500,10);
Query OK, 1 row affected (0.00 sec)

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 |
|  9999 | zhangsan | MANAGER   | NULL | NULL       | 3000.00 |  500.00 |     10 |
+-------+----------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)

指定字段的插入
建议使用此种方式

insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values (1003,’dh’,’n’,now(),‘lsabella@163’,11);

mysql> insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values (1003,'dh','n',now(),'lsabella@163',11);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t_student;
+------------+--------------+------+------------+--------------+------------+
| student_id | student_name | sex  | birthday   | email        | classes_id |
+------------+--------------+------+------------+--------------+------------+
|       1002 | baishu       | m    | 1988-01-01 | qqq@163      |         10 |
|       1003 | dh           | n    | 2021-11-03 | lsabella@163 |         11 |
+------------+--------------+------+------------+--------------+------------+
2 rows in set (0.00 sec)

出现了主键重复的错误，主键表示了记录的唯一性，不能重复

1 2	mysql> insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values (1002,'haha','n',now(),'haha@163',11); ERROR 1062 (23000): Duplicate entry '1002' for key 'PRIMARY'

如何插入日期
（1）插入的日期格式和显示的日期格式一致

insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values (1004,’haha1’,’n’,’1991-01-01’,‘haha@163’,11);

mysql> insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values (1004,'haha1','n','1991-01-01','haha@163',11);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------------+--------------+------+------------+--------------+------------+
| student_id | student_name | sex  | birthday   | email        | classes_id |
+------------+--------------+------+------------+--------------+------------+
|       1002 | baishu       | m    | 1988-01-01 | qqq@163      |         10 |
|       1003 | dh           | n    | 2021-11-03 | lsabella@163 |         11 |
|       1004 | haha1        | n    | 1991-01-01 | haha@163     |         11 |
+------------+--------------+------+------------+--------------+------------+
3 rows in set (0.00 sec)

（2）采用str_to_date

insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values (1005,’haha2’,’n’,str_to_date(‘1985-01-01’,’%Y-%m-%d’),‘haha@163’,11);

mysql> insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values (1005,'haha2','n',str_to_date('1985-01-01','%Y-%m-%d'),'haha@163',11);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
+------------+--------------+------+------------+--------------+------------+
| student_id | student_name | sex  | birthday   | email        | classes_id |
+------------+--------------+------+------------+--------------+------------+
|       1002 | baishu       | m    | 1988-01-01 | qqq@163      |         10 |
|       1003 | dh           | n    | 2021-11-03 | lsabella@163 |         11 |
|       1004 | haha1        | n    | 1991-01-01 | haha@163     |         11 |
|       1005 | haha2        | n    | 1985-01-01 | haha@163     |         11 |
+------------+--------------+------+------------+--------------+------------+
4 rows in set (0.00 sec)

（3）添加系统日期（now()）

1 2	mysql> insert into t_student(student_id,student_name,sex,birthday,email,classes_id) values (1006,'haha3','n',now(),'haha@163',11); Query OK, 1 row affected, 1 warning (0.00 sec)

表复制

create table t_student_bak as select student_id,student_name,sex,email,classes_id from t_student;

mysql> create table t_student_bak as select student_id,student_name,sex,email,classes_id from t_student;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show tables;
+-----------------------+
| Tables_in_baishu_demo |
+-----------------------+
| DEPT                  |
| EMP                   |
| SALGRADE              |
| t_student             |
| t_student_bak         |
+-----------------------+
5 rows in set (0.01 sec)

mysql> select * from t_student_bak;
+------------+--------------+------+--------------+------------+
| student_id | student_name | sex  | email        | classes_id |
+------------+--------------+------+--------------+------------+
|       1002 | baishu       | m    | qqq@163      |         10 |
|       1003 | dh           | n    | lsabella@163 |         11 |
|       1004 | haha1        | n    | haha@163     |         11 |
|       1005 | haha2        | n    | haha@163     |         11 |
|       1006 | haha3        | n    | haha@163     |         11 |
+------------+--------------+------+--------------+------------+
5 rows in set (0.00 sec)

以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中

如何将查询的数据直接放到已经存在的表中，可以使用条件

insert into t_student_bak select * from t_student where student_id = 1003;

insert into t_student_bak select student_id,student_name,sex,birthday,classes_id from t_student where student_id = 1003;

mysql> select * from t_student_bak;
+------------+--------------+------+------------+----------+------------+
| student_id | student_name | sex  | birthday   | email    | classes_id |
+------------+--------------+------+------------+----------+------------+
|       1002 | baishu       | m    | 1988-01-01 | qqq@163  |         10 |
|       1004 | haha1        | n    | 1991-01-01 | haha@163 |         11 |
|       1005 | haha2        | n    | 1985-01-01 | haha@163 |         11 |
|       1006 | haha3        | n    | 2021-11-03 | haha@163 |         11 |
+------------+--------------+------+------------+----------+------------+
4 rows in set (0.00 sec)

mysql> insert into t_student_bak select * from t_student where student_id = 1003;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t_student_bak;
+------------+--------------+------+------------+--------------+------------+
| student_id | student_name | sex  | birthday   | email        | classes_id |
+------------+--------------+------+------------+--------------+------------+
|       1002 | baishu       | m    | 1988-01-01 | qqq@163      |         10 |
|       1004 | haha1        | n    | 1991-01-01 | haha@163     |         11 |
|       1005 | haha2        | n    | 1985-01-01 | haha@163     |         11 |
|       1006 | haha3        | n    | 2021-11-03 | haha@163     |         11 |
|       1003 | dh           | n    | 2021-11-03 | lsabella@163 |         11 |
+------------+--------------+------+------------+--------------+------------+
5 rows in set (0.00 sec)

表结构不一致
mysql> insert into t_student_bak select student_id,student_name,sex,birthday,classes_id from t_student where student_id = 1003;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

update

语法格式
可以修改数据，可以根据条件修改数据
语法格式:update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where …

update t_student_bak set student_name = ‘test1’ where student_id = 1003;

mysql> update t_student_bak set student_name = 'test1' where student_id = 1003;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from t_student_bak;
+------------+--------------+------+------------+------------+
| student_id | student_name | sex  | birthday   | classes_id |
+------------+--------------+------+------------+------------+
|       1002 | baishu       | m    | 1988-01-01 |         10 |
|       1004 | haha1        | n    | 1991-01-01 |         11 |
|       1005 | haha2        | n    | 1985-01-01 |         11 |
|       1006 | haha3        | n    | 2021-11-03 |         11 |
|       1003 | test1        | n    | 2021-11-03 |         11 |
+------------+--------------+------+------------+------------+
5 rows in set (0.00 sec)

delete

可以删除数据，可以根据条件删除数据
语法格式:delete from表名 where …

删除津贴为null的员工

delete from EMP where comm is null;

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 |
|  9999 | zhangsan | MANAGER   | NULL | NULL       | 3000.00 |  500.00 |     10 |
+-------+----------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)

mysql> delete from EMP where comm is null;
Query OK, 10 rows affected (0.00 sec)

mysql> select * from EMP;
+-------+----------+----------+------+------------+---------+---------+--------+
| 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 |
|  9999 | zhangsan | MANAGER  | NULL | NULL       | 3000.00 |  500.00 |     10 |
+-------+----------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)

创建表加入约束

常见的约束

非空约束，not null

唯一约束，unique

主键约束，primary key

外键约束，foreign key

自定义检查约束，check（不建议使用）（在mysql中现在还不支持）

非空约束，not null

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30),
	classes_id	int(3)	
)

insert into t_student(student_id, birthday, email, classes_id) 
values
(1002, '1988-01-01', 'qqq@163.com', 10)

唯一约束，unique

唯一性约束修饰的字段具有唯一性，不能重复。但可以为null。

案例：给某一列添加unique

 drop table if exists t_user;
 create table t_user(
 id int,
username varchar(255) unique  //列级约束
);
insert into t_user values(1,'zhangsan');		
insert into t_user values(2,'zhangsan');  //出现编译错误，唯一性约束，该字段与上一行字段重复，但可以为null！
ERROR 1062 (23000) : Duplicate entry 'zhangsan' for key 'username'

insert into t_user(id) values(2);
       insert into t_user(id) values(3);
insert into t_user(id) values(4);

案例：给两个列或者多个列添加unique

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

insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
select * from t_user;
insert into t_user values(4,'111','zs');  //出现编译错误！
ERROR 1062 (23000) : Duplicate entry '111-zs' for key 'usercode'

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

注意：not null约束只有列级约束，没有表级约束。

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  unique,
	classes_id	int(3)	
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)

以上插入了重复的email，所以出现了“违反唯一约束错误”，所以unique起作用了，同样可以为唯一约束起个约束名。

查看约束

关于约束名称可以到table_constraints中查询

mysql> use information_schema;

mysql> select * from table_constraints where table_name = ‘t_student’;

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from table_constraints where table_name = 't_student';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | baishu_demo       | PRIMARY         | baishu_demo  | t_student  | PRIMARY KEY     |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
1 row in set (0.03 sec)

约束的名称我们也可以自定义

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
  email		varchar(30)  ,
	classes_id	int(3)	,
  constraint email_unique unique(email) /*表级约束*/
)

主键约束，primary key

怎么给一张表添加主键约束呢？

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,'zs','zs@123.com');
      insert into t_user(id,username,email) values(2,'ls','ls@123.com');
      insert into t_user(id,username,email) values(3,'ww','ww@123.com');
      select * from t_user;
      +-----------------------------+
      | id | username | email       |
      +-----------------------------+
      |  1 | zs       | zs@123.com  |
      |  2 | ls       | ls@123.com  |
      |  3 | ww       | ww@123.com  |
      +----+----------+-------------+

      insert into t_user(id,username,email) values(1,'jack','jack@123.com');  //出现编译错误，主键约束，不能为null也不能重复！
      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,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ws');
insert into t_user(id,username) values(4,'cs');
select * from t_user;

insert into t_user(id,username) values(3,'cx');  //出现编译错误！
ERROR 1062 (23000) : Duplicate entry '4' for key 'PRIMARY'

以下内容是演示一下复合主键，不需要掌握：
  drop table if exists t_user;
  create table t_user(
  id int,
  username varchar(255),
  password varchar(255),
  primary key(id,username)
  );
  insert ......

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('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
insert into t_user(username) values('f');
select * from t_user;

提示：Oracle当中也提供了一个自增机制，叫做：序列(sequence)对象。

使用列级约束定义主键：

mysql> create table t_student(
    -> student_id int(10) primary key,/* 列级约束 */
    -> student_name varchar(20) not null,
    -> sex char(2) default 'm',
    -> birthday date,
    -> email varchar(30),
    -> classes_id int(3)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
    -> values
    -> (1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
    -> values
    -> (1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);
ERROR 1062 (23000): Duplicate entry '1001' for key 'PRIMARY'
向以上表中加入学号为1001的两条记录，出现如下错误，因为加入了主键约束

可通过表级约束为约束起个名称

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  ,
	classes_id	int(3),
    CONSTRAINT p_id PRIMARY key (student_id)
)

外键约束，foreign key

关于外键约束的相关术语：

主键约束 : foreign key
主键字段 : 添加有外键约束的字段
主键值 : 外键字段中的每一个值。

业务背景：
请设计数据库表，用来维护学生和班级的信息？

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

no(pk)          name          classno         classname
-----------------------------------------------------------
 1               zs1            101          河南省平顶山市舞钢市垭口一高高三1班         
 2               zs2            101          河南省平顶山市舞钢市垭口一高高三1班 
 3               zs3            102          河南省平顶山市舞钢市垭口一高高三2班 
 4               zs4            102          河南省平顶山市舞钢市垭口一高高三2班
 5               zs5            102          河南省平顶山市舞钢市垭口一高高三2班 
 缺点：冗余。【不推荐】

两张表(班级表和学生表)

t_class 班级表
cno(pk)         cname
-------------------------------------------------------------
        101           河南省平顶山市舞钢市垭口一高高三1班 
 102           河南省平顶山市舞钢市垭口一高高三2班 

 t_student 学生表
 sno(pk)        sname          classno(该字段添加外键约束fk)
 -----------------------------------------------------------
  1              zs1              101
  2              zs2              101
  3              zs3              102
  4              zs4              102
  5              zs5              102

将以上表的建表语句写出来
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,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
 insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');

 insert into t_student values(1,'zs1',101);
 insert into t_student values(2,'zs2',101);
 insert into t_student values(3,'zs3',102);
 insert into t_student values(4,'zs4',102);
 insert into t_student values(5,'zs5',102);
 insert into t_student values(6,'zs6',102);
 select * from t_class;
 select * from t_student;

 insert into t_student values(7,'lisi',103);  //编译错误，引用的103，父表中没有该字段！
 ERROR 1452 (23000) : Cannot add or update a child row :aforeign key constraint fails (bjpowernode INT YT......)

外键值可以为NULL？
外键可以为null。

外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少是具有unique约束，具有唯一性，不可重复！

详细步骤如下：

首先建立班级表t_classes

mysql> create table t_classes(
    -> classes_id int(3),
    -> classes_name varchar(40),
    -> constraint pk_classes_id primary key(classes_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

在t_student中加入外键约束

mysql> create table t_student(
    -> student_id int(10),
    -> student_name varchar(20),
    -> sex char(2),
    -> birthday date,
    -> email varchar(30),
    -> classes_id int(3),
    -> constraint student_id_pk primary key(student_id),
    -> constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

向t_student中加入数据

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, ‘zhangsan’, ‘m’, ‘1988-01-01’, ‘qqq@163.com‘, 10)

mysql> insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`baishu_demo`.`t_student`, CONSTRAINT `fk_classes_id` FOREIGN KEY (`classes_id`) REFERENCES `t_classes` (`classes_id`))

出现错误，因为在班级表中不存在班级编号为10班级，外键约束起到了作用
存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表

当classes_id设置为null，成功

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, ‘zhangsan’, ‘m’, ‘1988-01-01’, ‘qqq@163.com‘, null);

1 2	mysql> insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null); Query OK, 1 row affected (0.01 sec)

以上成功的插入了学生信息，当时classes_id没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空

drop table if exists t_student;
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20),
	sex		char(2),
	birthday	date,
	email		varchar(30),
	classes_id	int (3) not null,
	constraint  student_id_pk primary key(student_id),
	constraint	fk_classes_id foreign key(classes_id) references t_classes(classes_id)	     
)
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null);

再次插入班级编号为null的数据

1
2

mysql> insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', null);
ERROR 1048 (23000): Column 'classes_id' cannot be null

添加数据到班级表，添加数据到学生表，删除班级数据，将会出现如下错误：

mysql> insert into t_classes (classes_id,classes_name) values (10,'366');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student(
    -> student_id, student_name, sex, birthday, email, classes_id
    -> ) values (
    -> 1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10
    -> );
Query OK, 1 row affected (0.00 sec)

mysql> update t_classes set  classes_id = 20 where classes_name = '366';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`baishu_demo`.`t_student`, CONSTRAINT `fk_classes_id` FOREIGN KEY (`classes_id`) REFERENCES `t_classes` (`classes_id`))

因为子表（t_student）存在一个外键classes_id，它参照了父表（t_classes）中的主键，所以先删除子表中的引用记录，再修改父表中的数据。

delete from t_classes where classes_id = 10;

mysql> delete from t_classes where classes_id = 10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`baishu_demo`.`t_student`, CONSTRAINT `fk_classes_id` FOREIGN KEY (`classes_id`) REFERENCES `t_classes` (`classes_id`))
因为子表（t_student）存在一个外键classes_id，它参照了父表（t_classes）中的主键，所以先删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据，再删除父表中的数据，采用drop table也不行，必须先drop子表，再drop父表
我们也可以采取以下章节的措施 级联删除。

级联更新与级联删除

on update cascade;

mysql对有些约束的修改比较麻烦，所以我们可以先删除，再添加我们只修改了父表中的数据，子表中的数据也会跟着变动。

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on update cascade;

update t_classes set classes_id = 20 where classes_name = ‘366’;

mysql> alter table t_student drop foreign key fk_classes_id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id)  on update cascade;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update t_classes set classes_id = 20 where classes_name = '366';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_classes;
+------------+--------------+
| classes_id | classes_name |
+------------+--------------+
|         20 | 366          |
+------------+--------------+
1 row in set (0.00 sec)

mysql> select * from t_student;
+------------+--------------+------+------------+-------------+------------+
| student_id | student_name | sex  | birthday   | email       | classes_id |
+------------+--------------+------+------------+-------------+------------+
|       1001 | zhangsan     | m    | 1988-01-01 | qqq@163.com |         20 |
+------------+--------------+------+------------+-------------+------------+
1 row in set (0.00 sec)

mysql> desc t_student;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| student_id   | int(10)     | NO   | PRI | NULL    |       |
| student_name | varchar(20) | YES  |     | NULL    |       |
| sex          | char(2)     | YES  |     | NULL    |       |
| birthday     | date        | YES  |     | NULL    |       |
| email        | varchar(30) | YES  |     | NULL    |       |
| classes_id   | int(3)      | NO   | MUL | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

on delete cascade;

mysql对有些约束的修改时不支持的，所以我们可以先删除，再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on delete cascade;

delete from t_classes where classes_id = 20;

t_student和t_classes完整示例

drop table if exists t_classes;
create table t_classes(
	classes_id	int (3),
	classes_name	varchar(30)	not null,
	constraint	pk_classes_id	primary key(classes_id) 
)

drop table if exists t_student;
create table t_student(
	student_id	int(10),
	student_name 	varchar(50) not null,
	sex		char(2) not null,
	birthday	date not null,
	email		varchar(30) unique,
	classes_id	int (3) not null,
	constraint	pk_student_id	primary key(student_id),
	constraint	fk_classes_id	foreign key(classes_id) references t_classes(classes_id) 
)

存储引擎

完整的建表语句

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

注意：在MySQL当中，凡是标识符使用飘号括起来的。最好别用，不通用。
建表的时候可以指定存储引擎，也可以指定字符集。
mysql默认使用的存储引擎是InnoDB方式。默认采用的字符集是UTF-8。

什么是存储引擎呢？

存储引擎这个名字只有在mysql中存在。(Oracle中有对应的机制，但不叫做存储引擎。Oracle中没有特殊的名字，就是”表的存储方式”)
mysql支持很多存储引擎，每个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

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

ysql> 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.00 sec)

常见的存储引擎？

Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO

MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种存储引擎不是默认的。
MyISAM采用三个文件组织一个表：
xxx.frm(存储格式的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点:不支持事务。

Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中(逻辑概念)，无法被压缩，无法转换成只读。
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
InoDB支持级联删除和级联更新。

Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。
以前叫做HEPA引擎。

事务

什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行账户，从A账户向B账户转账10000元，需要执行两条update语句。

update t_act set balance = balance - 10000 where actno = ‘act-001’;
update t_act set balance = balance + 10000 where actno = ‘act-002’;

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
想要保证以上的两条DML语句同时成功或者同时失败，那么就要使用数据库的”事务机制”。

和事务相关的语句只有：DML语句。(insert delete update)

为什么？

因为他们这三个语句都是和数据库表当中的"数据"相关的。事务的存在是为了保证数据的完整性，安全性。

假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要事务机制。
但实际情况不是这样的，通常一个”事儿(事务【业务】)”需要多条DML语句共同联合完成。

事务的特性？

事务包括四大特性：ACID
A:原子性：事务是最小的工作单元，不可再分。
B:一致性：事务必须保证多条DML语句同时成功或者同时失败。
C:隔离性：事务A与事务B之间具有隔离。
D:持久性：持久性说的是最终数据必须持久化到硬盘中，事务才算成功结束。

关于事务之间的隔离性？

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

第一级别：读未提交(read uncommitted)
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读(Dirty Read) 现象：表示读到了脏数据。
第二级别：读已提交(read committed)
对方事务提交之后的数据我方可以读取到。
读已提交存在的问题是：不可重复读。
第三级别：可重复读(repeatable read)
这种隔离级别解决了：不可重复读问题。
这种隔离级别存在的问题是：读取到的数据是幻象。
第四级别：序列化读/串行化读
解决了所有问题。
效率低，需要事务排队。

演示事务

mysql事务默认情况下是自动提交的。
(什么是自动提交？只要执行任意一条DML语句则提交一次。)怎么关闭默认提交？start transaction;

建表：

mysql> create table t_user(
    -> id int primary key auto_increment,
    -> username varchar(255)
    -> );

演示：mysql中的事务是支持自动提交的，只要执行一条DML语句，则提交一次。

mysql> insert into t_user(username) values('zs');
	   Querk OK, 1 row affected (0.03 sec)
mysql> select * from t_user;
		+----+----------+
		| id | username |
		+----+----------+
		|  1 | zs       |
		+----+----------+
mysql> rollback;
	   Query OK, 0 rows affected (0.00 sec)

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

使用start transaction; 关闭自动提交机制。

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

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> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

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

mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
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 | zs       |
+----+----------+
1 row in set (0.00 sec)

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("object");
Query OK, 1 row affected (0.00 sec)

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

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

mysql> select * from t_user;
+----+--------------+
| id | username     |
+----+--------------+
|  1 | zs           |
|  4 | wangwu       |
|  5 | object       |
|  6 | joke         |
|  7 | xiaozhaozhao |
+----+--------------+
5 rows in set (0.00 sec)

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

mysql> select * from t_user;
+----+--------------+
| id | username     |
+----+--------------+
|  1 | zs           |
|  4 | wangwu       |
|  5 | object       |
|  6 | joke         |
|  7 | xiaozhaozhao |
+----+--------------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user;
+----+--------------+
| id | username     |
+----+--------------+
|  1 | zs           |
|  4 | wangwu       |
|  5 | object       |
|  6 | joke         |
|  7 | xiaozhaozhao |
+----+--------------+
5 rows in set (0.00 sec)

rollback : 回滚。
commit ：提交。
start transaction : 关闭自动提交机制。

演示两个事务，假如隔离级别：
```
 演示第1级别：读未提交
     set global transaction isolation level read uncommitted;
 演示第二级别；读已提交
     set global transaction isolation level read committed;
 演示第三级别:可重复读
     set global transaction isolation level repeatable read;
```
索引
什么是索引？有什么用？
索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候又两种检索方式：
（1）全表扫描
（2）根据索引检索（效率很高）
索引为什么可以提高检索效率呢？
其实最根本的原理是缩小了扫描的范围。

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

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

select ename,sal from EMP where ename = ‘SMITH’;
当ename字段没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
当ename字段添加索引的时候，以上sql语句会根据索引扫描，快速定位。

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

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

什么时候考虑给字段添加索引？(满足什么条件)

数据量庞大。(根据客户的需求，根据线上的环境)
该字段很少的DML操作。(因为字段进行修改操作，索引也需要维护)
该字段经常出现在where子句中。(经常根据哪个字段维护)

注意：主键具有unique约束的字段会自动添加索引

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

查看sql语句的执行计划

create index emp_sal_index on EMP(sal);

mysql> explain select ename,sal from EMP where sal = 5000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | EMP   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

给薪资sal字段添加索引：
create index emp_sal_index on EMP(sal);

mysql> explain select ename,sal from EMP where sal = 5000;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | EMP   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
rows检索次数减少了

索引底层采用的数据结构

B + Tree

索引的实现原理

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

索引的分类

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

索引什么时候失效？

select ename from EMP where ename like ‘ %A% ‘;
模糊查询的时候，第一个通配符使用的是%，这个时候索引是是失效的。

试图(View)

什么是视图？

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

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

create view myview as select empno,ename from EMP;
drop view myview;
注意：只有DQL语句才能以试图对象的方式创建出来。

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

可以对试图进行CRUD操作。

面向视图操作？

mysql> select * from myview;
	+-------+--------+
	| 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 |
	+-------+--------+

create table emp_bak as select * from EMP;
create view myview1 as select empno,ename,sal from emp_bak;
update myview1 set ename = 'hehe',sal = 1 where empno 7369;  //通过视图修改原表数据。
delete from myview1 where empno = 7369;  //通过试图删除原表数据。

试图的作用？

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

DBA命令

在数据库当中的数据导出

在windows的DOS命令窗口中执行： (导出整个库)
    mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p999
在windows的dos命令窗口中执行：(导出数据库中指定的表)
    mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p999

导入数据

1
2
3

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

数据库设计三范式(重点内容，面试经常会问)

什么是设计范式？

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

三范式都是哪些？

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部份依赖。
多对多？三张表，关系表两个外键。

t_student学生表
sno(pk)        sname
---------------------
  1             张三
  2             李四 
  3             王五

t_teacher 讲师表
tno(pk)        tname
----------------------
  1						  王老师
  2							张老师
  3							李老师

t_student_teacher_relation 学生讲师关系表
id(pk)         sno(fk)          tno(fk)
-------------------------------------------
  1	             1                  3
  2	             1	              	1
  3	             2	              	2
  4	             2                	3
  5	             3	              	1
  6	             3	              	3

建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。
一对多？两张表，多的表加外键。

班级t_class
cno(pk)          cname
--------------------------
  1              班级1
  2              班级2

学生t_student
sno(pk)         sname         classno(fk)
--------------------------------------------
 101	     张1	            1
 102             张2	            2
 103	     张3	            2
 104	     张4	            1
 105	     张5	            2

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

一对一怎么设计？

一对一设计有两种方案：主键共享，外键唯一。

主键共享

t_user_login 用户登陆表
id(pk)       username        password
----------------------------------------
  1             zs                123
  2             ls                456
   
t_user_detail 用户详细信息表
id(pk+fk)         realname          tel          ...
----------------------------------------------------
1                  张三            11111111112234
2	                 李四            12112523432412

外键唯一

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

t_user_detail 用户详细信息表
id(pk)          realname            tel            userid(fk+unique)      
----------------------------------------------------
  1                张三            111111114          2
  2	               李四            121432412          1

本文作者：Baishu
本文链接：http://example.com/2019/04/06/mysql/use/
版权声明：本博客所有文章除特别声明外，均默认采用许可协议。