MySQL学习笔记

最新推荐文章于 2024-09-04 20:13:13 发布

大枫树

最新推荐文章于 2024-09-04 20:13:13 发布

阅读量252

点赞数

文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/qq_38889970/article/details/131671377

版权

文章目录

MySQL

MySQL

一、数据库概述及数据准备

1.1 安装MySQL

参考：MySQL的安装与配置——详细教程 - itcui - 博客园 (cnblogs.com)

**安装服务：**mysqld --install
初始化：　mysqld --initialize --console
**开启服务：**net start mysql
**关闭服务：**net stop mysql
**登录mysql：**mysql -u root -p
**修改密码：**alter user ‘root’@‘localhost’ identified by ‘root’;(by 接着的是密码)
标记删除mysql服务：sc delete mysql

MySQL基本配置文件：在mysql目录下创建一个my.ini配置文件：

[mysqld]
character-set-server=utf8mb4
bind-address=0.0.0.0
port=3306
default-storage-engine=INNODB
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4

1.2 什么是MySQL

sql、DB、DBMS分别是什么，他们之间的关系是什么？
- DB：DataBase（数据库），数据库实际上在硬盘上以文件的形式存在
DBMS：DataBase Management System（数据库管理系统），常见的有：MySQL、Oracle、DB2、Sybase、SqlServer…
SQL: Structure Query Language
结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。
SQL属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。
SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。（sql语句的编译由DBMS完成。）
DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。
DBMS -(执行)-> SQL -(操作)-> DB

1.3 什么是表

表：table，表格。
表是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。
一个表包括行和列：
- 行：被称为数据/记录(data)
- 列：被称为字段(column)
每一个字段应该包括哪些属性？
- 字段名
- 数据类型
- 相关的约束
学号（int）姓名（varchar）年龄（int）
110 张三 20
120 李四 21

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

1.4 SQL语句的分类

学习MySQL主要还是学习通用的SQL语句，那么SQL语句包括增删改查，SQL语句怎么分类呢？（前两个是重点）

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

1.5 导入数据

注意以下命令不是SQL语句，而是MySQL命令

第一步：登录mysql数据库管理系统
- mysql -u root -p

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

show databases;

以下四个数据库是MySQL自带的：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

第三步：创建属于我们自己的数据库
- create database bjpowernode;
第四步：使用bjpowernode数据
- use bjpowernode;
第五步：查看当前使用的数据库中有哪些表？
- show tables;

第六步：初始化数据

source C:\Users\Administrator\Desktop\W\Java\MySQL\bjpowernode.sql;

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

+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |		（部门表）
| emp                   |		（员工表）
| salgrade              |		（工资等级表）
+-----------------------+

删除数据库：
- drop database bjpowernode;

查看表格结构：（注意不是查看数据）

查看部门表结构：desc dept;

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |		部门编号
| DNAME  | varchar(14) | YES  |     | NULL    |       |		部门名称
| LOC    | varchar(13) | YES  |     | NULL    |       |		部门位置
+--------+-------------+------+-----+---------+-------+

查看员工表结构：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    |       |	部门编号
+----------+-------------+------+-----+---------+-------+

查看工资等级表结构：desc salgrade;

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

查看表格数据：注意：这里的不再是MySQL的命令，而是SQL语句

查看部门表数据：select * from dept;

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

查看员工表数据：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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

查看工资等级表数据：select * from salgrade;

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

注意：bjpowernode.sql，这个文件以.sql结尾，这样的文件被称为“sql脚本”。什么是sql脚本呢？
- 当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
- 注意：直接使用source命令可以执行sql脚本，即批量的执行sql语句。
- sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

1.6 MySQL常用命令

查看msyql版本：
- 在 mysql未登录前：
  - mysql --version
  - mysql -V
- 在MySQL登录后：
  - select version();
查看有哪些数据库
- show databases;
创建数据库:
- create database 数据库名;
使用/切换数据库：
- use 数据库名;
查询正在使用哪个数据库：
- select database();
查看当前使用的数据库中有哪些表：
- show tables;
初始化数据
- source sql脚本路径;
查看创建表的语句：
- show create table tableName;
查看表结构：
- desc 表名;
退出MySQL：
- exit
- \q
- quit

二、数据查询语音 DQL

DQL（Data Query Language：数据查询语言）: 查询语句，凡是select语句都是DQL。

2.1 简单的查询

语法格式：

select 字段名1,字段名2,字段名3,... from 表名;

例：

select ename,sal from emp;

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

字段可以参与数学运算，如查询员工年薪：

select ename,sal*12 from emp;

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

列重命名，使用：原列名 as 新列名，对列名重命名，as可省略：

select ename,sal * 12 as yearsal from emp;
或：
select ename,sal * 12 yearsal from emp;

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

列名中有中文，使用单引号’'括起来：

select ename,sal * 12 as 年薪 from emp; -- 错误
select ename,sal * 12 as '年薪' from emp; -- 字符串使用单引号括起来

+--------+----------+
| ename  | 年薪     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

查询所有字段：

select * from emp;	// 实际开发中不建议使用*，效率较低。

注意：
- 任何一条sql语句以“;”结尾。
- sql语句不区分大小写。
- 标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。
- as关键字可以省略

distinct

distinct 查询结果去重：如找出所有岗位的名称：

select distinct job from emp;

注意：distinct只能出现在所有字段的最前面，如：

select distinct deptno,job from emp;

+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+

2.2 条件查询

语法格式：

select 字段名1,字段名1,... from 表名 where 条件;

为看的更清楚也可这样写：

select 
	字段名1,字段名1,... 
from 
	表名 
where 
	条件;

执行顺序：先from，然后where，最后select

SQL支持的运算符：

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

找出工资大于等于3000的员工：

select ename,sal from emp where sal >= 3000;

+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

查询SMITH的工资：

select sal from emp where ename = 'SMITH'; -- 字符串使用单引号括起来。

+--------+
| sal    |
+--------+
| 800.00 |
+--------+

找出工资在1100和3000之间的员工，包括1100和3000：
```
select ename,sal from emp where sal >= 1100 and sal <= 3000;
select ename,sal from emp where sal between 1100 and 3000; -- between...and...是闭区间 [1100， 3000]
```
注意：
- between and在使用的时候必须左小右大。
- between and除了可以使用在数字方面之外，还可以使用在字符串方面。
- between and 用在字符串方面为左闭右开，[value1, value2)
找出名字首字母在a~c之间的员工，不包括c：
```
select ename from emp where ename between 'a' and 'c';
```

找出没有津贴的员工：

select ename,comm from emp where comm is null or comm = 0;

+--------+------+
| ename  | comm |
+--------+------+
| SMITH  | NULL |
| JONES  | NULL |
| BLAKE  | NULL |
| CLARK  | NULL |
| SCOTT  | NULL |
| KING   | NULL |
| TURNER | 0.00 |
| ADAMS  | NULL |
| JAMES  | NULL |
| FORD   | NULL |
| MILLER | NULL |
+--------+------+

注意：

在数据库中NULL不是一个值，代表什么都没有。
所以需要查询为Null时，得用 is null 或 not is null ，而不是使用字段名 = null

找出薪资大于1000的并且部门编号是20或30部门的员工：

select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno = 30; -- 错误的
select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30); -- 正确的。

注意：

and优先级大于or
当运算符的优先级不确定的时候加小括号

in等同于or：找出工作岗位是MANAGER和SALESMAN的员工？

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

注意：

select ename,job from emp where sal in(800, 5000); -- in后面的值不是区间，是具体的值。

模糊查询like：

搞清楚符号$与_的含义：
- $：表示任意多个字符
- _：表示任意一个字符

例：

select ename from emp where ename like '%O%';		-- 找出名字当中含有O的
select ename from emp where ename like '_A%';		-- 找出名字中第二个字母是A的
select name from t_user where name like '%\_%';		-- 找出名字中有下划线的
select ename from emp where ename like '%T';  		-- 找出名字中最后一个字母是T的

2.3 数据排序

排序：关键字order by，默认为升序排列，可在后面加上asc表升序，desc表降序

格式：

select
	字段名1,字段名2,...
from
	表名
order by
	排序字段;

例：

select ename,sal from emp order by sal;			-- 按照工资升序（默认为升序），找出员工名和薪资
select ename,sal from emp order by sal asc;		-- 按照工资升序，找出员工名和薪资
select ename,sal from emp order by sal desc;	-- 按照工资降序，找出员工名和薪资

多个排序字段：

select ename,sal from emp order by sal desc , ename asc;	-- 按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

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

注意：有多个排序字段时，越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

与条件查询语句一起使用：
```
select 
	ename,job,sal
from
	emp
where 
	job = 'SALESMAN'
order by
	sal desc;
```
注意执行顺序为：先from、再where、再select、最后order by

2.4 分组函数

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

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

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

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

使用格式：

select 分组函数(字段名) from 表名;

例：

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

注意：分组函数会自动忽略NULL：

select count(comm) from emp;

+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

计算每个员工的年薪？

select ename,(sal+comm)*12 as yearsal from emp;		-- 错误

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+

可以发现结果明显不对，这是因为在数据库中，只要是NULL参与的表达式结果一定为NULL，可以使用ifnull()函数处理这种问题。

单行处理函数：ifnull()

单行函数：输入一行，输出一行。

使用格式：

ifnull(可能为Null的数据，被当做什么处理);

例：计算每个员工的年薪

select ename,(sal+ifnull(comm, 0))*12 as yearsal from emp;	-- 正确

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

注意：条件查询语句where中不能使用分组函数：
- 例：找出工资高于平均工资的员工
```
select ename,sal from emp where sal > avg(sal);  -- 错误，不能这样写，因为条件查询语句where中不能使用分组函数
```
  - 为什么条件查询语句where中不能使用分组函数？原因在分组查询的 group by 中讲解
  - 因为group by是在where语句之后才执行，而分组函数是在group by之后执行的。
- 应该这样写：select语句嵌套select语句，叫做子查询
```
select ename,sal from emp where sal > (select avg(sal) from emp);		-- 正确
```
count(*)和count(字段名)的区别：
- count(*)：不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
- count(字段名): 表示统计该字段中不为NULL的数据总数量。

分组函数也能组合起来用：

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

2.5 分组查询

包括group by 和 having两条语句

2.5.1 group by

group by ：按照某个字段或者某些字段进行分组。
使用格式：
```
group by 字段名1,字段名2....
```

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

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

+----------+-----------+
| max(sal) | job       |
+----------+-----------+
|  3000.00 | ANALYST   |
|  1300.00 | CLERK     |
|  2975.00 | MANAGER   |
|  5000.00 | PRESIDENT |
|  1600.00 | SALESMAN  |
+----------+-----------+

注意：
- 分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
- 任何一个分组函数都是在group by语句执行结束之后才会执行的。
- 当一条sql语句没有group by的话，整张表的数据会自成一组。
- 当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
- 例如以下语句就是错误的：
```
select ename,max(sal),job from emp group by job; 	-- 错误，因为ename既不是分组函数也不是参与分组的字段
```
多字段分组：找出每个部门不同工作岗位的最高薪资。
```
select deptno,job,max(sal) from emp group by deptno,job;
```

2.5.2 having

having：如需对分完组之后的数据再进行过滤，则可使用having
只有使用了group by ，才能使用having
使用格式：
```
having 表达式
```

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

select max(sal),deptno from emp group by deptno having max(sal) > 2900;	 -- 这种方式效率较低。
select max(sal),deptno from emp where sal > 2900 group by deptno; 		 -- 效率较高，建议能够使用where过滤的尽量使用where。

找出每个部门的平均薪资，要求显示薪资大于2000的数据：
```
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
```
注意：这种情况只能使用having过滤。

2.6 一个完整的DQL语句

应按照以下顺序书写：

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

但其执行顺序为：from -> where -> group by -> having -> select -> order by

2.7 连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。

2.7.1 连接查询的分类

根据语法出现的年代来划分的话，包括：
- SQL92（现在很少用了）
- SQL99（比较新的语法）
根据表的连接方式来划分，包括：
- 内连接：
  - 等值连接
  - 非等值连接
  - 自连接
- 外连接：
  - 左外连接（左连接）
  - 右外连接（右连接）
- 全连接（这个不讲，很少用！）

2.7.2 笛卡尔积现象

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

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

select e.ename,d.dname from emp e,dept d;

+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | OPERATIONS |
| SMITH  | SALES      |
| SMITH  | RESEARCH   |
| SMITH  | ACCOUNTING |
| ALLEN  | OPERATIONS |
| ALLEN  | SALES      |
| ALLEN  | RESEARCH   |
| ALLEN  | ACCOUNTING |
.......

以上语句找出的结果为56条（因为员工表中有14条，部门表中有4条，14*4=56）

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。
关于表的别名：
```
select e.ename,d.dname from emp e,dept d;		-- e 和 d 分别是emp表和dept表的别名
```
使用表的别名的好处：
- 执行效率高。（这样遇到不同表中的同名字段就可以更快找到需要的表）
- 可读性好。
怎么避免笛卡尔积现象？
- 当然是加条件进行过滤。
- 但请注意：加条件进行过滤不会减少记录匹配次数，如上面的还是56次，只不过显示出的条数减少了。

避免笛卡尔积现象：找出每一个员工的部门名称，要求显示员工名和部门名。

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

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

2.7.3 内连接

等值连接

使用“=”条件连接

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

SQL92语法：

select 
	e.ename,d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

SQL99语法：

select 
	e.ename,d.dname
from
	emp e
inner join				-- inner表内连接，可省略
	dept d
on						-- 连接条件
	e.deptno = d.deptno;

SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了。

join … on …语法：

select 
	表1.字段1, 表2.字段2
from
	表1
inner join				-- inner表内连接，可省略
	表2
on
	连接条件;

因为SQL99语法中使用join on 语法，所以可以再后面在加上where语句进行过滤

比如：查询每个员工的部门名称，要求显示工资大于1500的员工名和部门名。

select 
	e.ename,d.dname,e.sal
from 
	emp e
join 
	dept d
on 
	e.deptno = d.deptno
where						-- where语句，但其实还是匹配56次
	e.sal > 1500;

+-------+------------+---------+
| ename | dname      | sal     |
+-------+------------+---------+
| ALLEN | SALES      | 1600.00 |
| JONES | RESEARCH   | 2975.00 |
| BLAKE | SALES      | 2850.00 |
| CLARK | ACCOUNTING | 2450.00 |
| SCOTT | RESEARCH   | 3000.00 |
| KING  | ACCOUNTING | 5000.00 |
| FORD  | RESEARCH   | 3000.00 |
+-------+------------+---------+

非等值连接

连接条件中的关系是非等量关系。

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

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

自连接

一张表看做两张表，自己连接自己。

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

select 
	e.ename, m.ename as mgrname 
from 
	emp e 	-- 相当于员工表
join 
	emp m 	-- 相当于领导表
on 
	e.mgr = m.empno;

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

只有13条记录，因为KING没有领导

2.7.4 外连接

外连接使用较多

什么是外连接，和内连接有什么区别：
- 内连接：
  - 假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接，无法匹配的不显示
  - AB两张表没有主副之分，两张表是平等的。
- 外连接：
  - 假设A和B表进行外连接，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，
  - 当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
左连接表示左边的表为主表，右连接表示右边的表为主表。

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

左连接：

select 
	a.ename as '员工', b.ename as '领导'
from
	emp a			-- 主表
left outer join		-- 左连接表示左表为主表，outer可以省略
	emp b			-- 副表
on
	a.mgr = b.empno;

右连接：

select 
	a.ename as '员工', b.ename as '领导'
from
	emp b			-- 副表
right outer join	-- 右连接表示右表为主表，outer可以省略
	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  |
+--------+--------+

注意：因为是使用外连接查询，所以有14条记录，因为KING没有领导，无法匹配到结果，就会自动给他匹配为NULL

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

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

先用外连接查看，连接条件设置为：e.deptno = d.deptno

select 
	d.*,e.*
from 
	emp e
right join
	dept d
on 
	e.deptno = d.deptno;

+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+
| DEPTNO | DNAME      | LOC      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+
|     10 | ACCOUNTING | NEW YORK |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|     10 | ACCOUNTING | NEW YORK |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|     10 | ACCOUNTING | NEW YORK |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|     20 | RESEARCH   | DALLAS   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|     20 | RESEARCH   | DALLAS   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|     30 | SALES      | CHICAGO  |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|     30 | SALES      | CHICAGO  |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|     30 | SALES      | CHICAGO  |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|     30 | SALES      | CHICAGO  |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|     30 | SALES      | CHICAGO  |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|     30 | SALES      | CHICAGO  |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|     40 | OPERATIONS | BOSTON   |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+--------+

上面语句一共查出15条记录，其中第15条便是我们想要的，加上where语句：

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

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

2.8 多表连接查询

3张以上表的连接查询

多表连接格式：
```
from
	A
join
	B
on
	...
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;

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

select 
	e.ename '员工',d.dname '部门',s.grade '工资等级',m.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 m
on 
	e.mgr = m.empno;

2.9 子查询

子查询就是select语句中嵌套select语句

子查询可以出现在select、from、where语句后面
```
select
	..(select)
from
	..(select)
where
	..(select)
```

2.9.1 where字句中的子查询

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

select * from emp where sal > (select avg(sal) from emp);

2.9.2 from字句中的子查询

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

第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）

select deptno,avg(sal) as avgsal from emp group by deptno;

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

第二步：将以上得出的部门平均薪水表与工资等级表进行连接查询：

select 
	das.deptno,das.avgsal,s.grade 
from 
	(select deptno,avg(sal) avgsal from emp group by deptno) das 	-- from字句中的子查询
join 
	salgrade s 
on 
	das.avgsal between s.losal and s.hisal;

+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+

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

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

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

+--------+--------+-------+
| ename  | deptno | grade |
+--------+--------+-------+
| SMITH  |     20 |     1 |
| ALLEN  |     30 |     3 |
| WARD   |     30 |     2 |
| JONES  |     20 |     4 |
| MARTIN |     30 |     2 |
| BLAKE  |     30 |     4 |
| CLARK  |     10 |     4 |
| SCOTT  |     20 |     4 |
| KING   |     10 |     5 |
| TURNER |     30 |     3 |
| ADAMS  |     20 |     1 |
| JAMES  |     30 |     1 |
| FORD   |     20 |     4 |
| MILLER |     10 |     2 |
+--------+--------+-------+

第二步：将每个员工的薪水等级表按部门编号分组求grade的平均值：

把上表当做临时表用：（可以这样写，但不推荐，因为效率低）

select 
	dga.deptno,avg(dga.grade) dsga
from 
	(select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) dga
group by 
	deptno;

直接在上表进行分组查询：（这样写效率高）

select 
	e.deptno,avg(s.grade) dsga
from 
	emp e join salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	e.deptno;

+--------+--------+
| deptno | dsga   |
+--------+--------+
|     20 | 2.8000 |
|     30 | 2.5000 |
|     10 | 3.6667 |
+--------+--------+

2.9.3 select语句中的子查询

案例：找出每个员工所在的部门名称，要求显示员工名和部门名。
- 使用连接查询：（推荐这样写）
```
select 
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;
```
- 使用select语句中的子查询：（只是做个示范，不推荐这样写）
```
select 
	e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;
```

2.10 union

将两条select查询语句的结果进行拼接

注意union是将两个select语句查询结果进行上下拼接，所以要求两个查询结果的列数（字段数）一样

案例：找出工作岗位是SALESMAN和MANAGER的员工？

第一种：使用or条件查询

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

第二种：使用in条件查询

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

第三种：使用union合并查询结果

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

两张不相干的表中的数据拼接在一起显示：（示范一下，查询结果没啥意义）

select ename from emp
union
select dname from dept;

+------------+
| ename      |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
......

2.11 limit

重点，主要用于分页查询中

limit是MySQL特有的语句，其他数据库没有，不通用，不过在Oracle中有一个功能相似的语句，叫rownum
截取一定范围内的数据进行显示
语法结构：
```
limit startIndex, length
```
- startIndex：表示截取的起始位置，从0开始，0表示第1条数据。
- length：表示截取数据的条数。

注意：limit语句是最后执行的一个环节：

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

案例：取出工资前5名的员工：

select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;		-- 起始位置不写时，默认从0开始

通用的标准分页sql
- 每页显示pageSize条记录，则第pageNo页的：
- 截取开始位置为：(pageNo - 1) * pageSize
- 截取长度为：pageSize

三、数据定义语言 DDL

DDL：Data Define Language，数据定义语言

数据定义语言包括增删改
- create
- drop
- alter

3.1 创建表

创建表的语法格式：

create table 表名(
	字段名1 数据类型,
    字段名2 数据类型 约束条件 default 默认值,		-- 约束条件和字段默认值可不写
    字段名3 数据类型,
    ...
) ENGINE=存储引擎 DEFAULT CHARSET = utf8;	  -- ENGINE与DEFAULT CHARSET可省略，默认值为 ENGINE=InnoDB 、 DEFAULT CHARSET = utf8

MySQL中常见的数据类型：

类型	描述
Char(最大长度)	定长字符串，存储空间大小固定，适合作为主键或外键
Varchar(最大长度)	变长字符串，存储空间等于实际数据空间，可设置的最大长度为255
double(有效数字位数，小数位)	数值型
Float(有效数字位数，小数位)	数值型
Int( 长度)	整型
bigint(长度)	长整型
Date	日期型年月日
DateTime	日期型年月日时分秒毫秒
time	日期型时分秒
BLOB	Binary Large OBject（二进制大对象），可用于存储图片、视频等流媒体信息
CLOB	Character Large OBject（字符大对象），可用于存储大文本，最大可存储4g大小的字符串

char与varchar：
- 使用方式：
  - char(最大长度)
  - varchar(最大长度)
- char(最大长度)
  - 存入的数据不能超过最大长度，超过报错
  - 但即使数据小于这个最大长度，系统给它分配的空间任然是这个最大长度的内存，类似于JAVA中的数组
- varchar(最大长度)
  - 存入的数据不能超过最大长度，超过报错
  - 但当数据小于最大长度时，varchar占据的内存空间会自动变小，也就是动态的分配空间，类似于JAVA中的list
- 在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
- 当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。
BLOB和CLOB类型的使用
- 如：
```
电影表: t_movie
id(int)	name(varchar)		playtime(date/char)		haibao(BLOB)		history(CLOB)
----------------------------------------------------------------------------------------
1			蜘蛛侠	
2
3
```
- 注意：
  - 像int、char、varchar等数据类型都可以直接使用SQL语句insert进行插入，但向BLOB（二进制大对象）则需要使用JAVA中的io流进行插入。
  - 但其实一般情况下，图片、视频等二进制文件并不会直接放在数据库中，而是存储文件的地址等信息。
表名在数据库当中一般建议以：t_或者tbl_开始。

案例：创建学生表

学生信息包括：学号、姓名、性别、班级编号、生日

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

查看建好表的结构：

desc t_student;

  字段名	   数据类型	   数据能否为空		默认值
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| no      | bigint       | YES  |     | NULL    |       |
| name    | varchar(255) | YES  |     | NULL    |       |
| sex     | char(1)      | YES  |     | NULL    |       |
| classno | varchar(255) | YES  |     | NULL    |       |
| birth   | char(10)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

注意：以上表结构中的Default代表当执行插入数据语句insert时如果该字段未赋值，则给这条数据的该字段赋予这个默认值

设置字段默认值：

create table t_student(
    no bigint,
    name varchar(255),
    sex char(1) default 1, 		-- 设置性别的默认值为1
    classno varchar(255),
    birth char(10)
);

使用其他表的查询结果创建一张表：

格式：

create table 表名 as select查询语句;

例：

create table emp2 as select empno,ename from emp;

查看表结构：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int         | NO   |     | NULL    |       |
| ename | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

查看表中数据：

+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | SMITH  |
|  7499 | ALLEN  |
|  7521 | WARD   |
......

3.2 删除表

删除表的语法格式：

drop table 表名;				-- 这个通用
drop table if exists 表名;    -- 当这个表存在的话删除。Oracle不支持该语句

例：
```
drop table if exists t_student;
```

3.3 修改表

实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。

修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）

增删改查有一个术语：CRUD操作：Create（增） Retrieve（检索） Update（修改） Delete（删除）

使用alter对表结构进行修改

添加字段：

语法格式：

alter table 表名 add 字段名 数据类型;

删除字段：
- 语法格式：
```
alter table 表名 drop 字段名
```

修改字段：

语法格式：

alter table 表名 modify 字段名 新的数据类型;				-- 只修改字段的数据类型
alter table 表名 change 老字段名 新字段名 新的数据类型;		 -- 修改字段名和数据类型

四、数据操作语音 DML

DML：Data ManipulationLanguage，数据操作语言

数据操作语音包括增删改
- insert
- remove
- update

4.1 插入数据

插入数据的语法格式：
```
insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....);
```
注意：字段的数量和值的数量要相同，并且数据类型要对应相同。

例：

插入一个学生信息：

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');

-- 使用select语句查询插入结果
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+

插入信息时，无需按照建表时字段的顺序来，只需要字段名与数据对应就可以

insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban', '1950-10-12',2);	-- 将no字段放后面

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+

当插入信息有缺省时：

insert into t_student(name,no) values('wangwu',3); 	-- 未赋值的字段信息被自动插入NULL值，

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
|    3 | wangwu   | NULL | NULL       | NULL       |
+------+----------+------+------------+------------+

插入数据时可以对字段名进行省略，但values()中的值，必须按照定义表结构时的顺序来（而且数据信息不能缺省）：

insert into t_student values(4,'jack','0','gaosan2ban','1986-10-23');		-- 正确
insert into t_student values(4,'jack');										-- 会报错

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
|    3 | wangwu   | NULL | NULL       | NULL       |
|    4 | jack     | 0    | gaosan2ban | 1986-10-23 |
+------+----------+------+------------+------------+

一次插入多行数据

-- 方式一：
insert into t_student
	(no,name,sex,classno,birth) 
values
	(5,'rose','1','gaosi2ban','1952-12-14'),
	(6,'laotie','1','gaosi2ban','1955-12-14');
-- 方式二：
insert into t_student2 values
	(5,'rose','1','gaosi2ban','1952-12-14'),
	(6,'laotie','1','gaosi2ban','1955-12-14');

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-10-12 |
|    3 | wangwu   | NULL | NULL       | NULL       |
|    4 | jack     | 0    | gaosan2ban | 1986-10-23 |
|    5 | rose     | 1    | gaosi2ban  | 1952-12-14 |
|    6 | laotie   | 1    | gaosi2ban  | 1955-12-14 |
+------+----------+------+------------+------------+

将其他表的查询结果插入表中：

格式：
```
insert into 表名 select查询语句;
```

例：

create table dept1 as select * from dept;		-- 将使用dept表创建新表的dept1
insert into dept1 select * from dept;			-- 将将查询结果插入到表dept1中
select * from dept1;							-- 查看dept1表中数据

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

4.2 删除数据

删除数据的语法格式：
```
delete from 表名 where 条件;
```
注意：没有写where条件时，整张表数据全部删除。

案例：删除20部门的数据

delete from dept1 where deptno=20;

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | RENSHIBU   | SHANGHAI |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | RENSHIBU   | SHANGHAI |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

注意：使用delete删除数据是可以撤回的，也正是因为如此，导致delete在删除时比较慢，当需要删除的表很大时，会很慢。

永久删除数据：（注意使用）

truncate table 表名;		-- 表被截断，不能恢复，永久丢失

4.3 修改数据

修改数据的语法格式：
```
update 表名 set 字段名1='值1',字段名2='值2',... where 条件;
```
注意：没有写where条件时，整张表数据全部更新。

案例：将表dept1中部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU

update dept1 set loc='SHANGHAI',dname='RENSHIBU' where deptno = 10;

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | RENSHIBU   | SHANGHAI |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | RENSHIBU   | SHANGHAI |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

五、约束(Constraint)

什么是约束？
- 在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束有哪些呢？
- 非空约束(not null)：约束的字段不能为NULL
- 唯一约束(unique)：约束字段的数据不能重复
- 主键约束(primary key)：约束字段的数据既不能为NULL，也不能重复（简称PK）
- 外键约束(foreign key)：…（简称FK）
- 检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

在表的创建时可对字段添加约束：

create table 表名(
	字段名1 数据类型 约束,
    字段名2 数据类型 约束,
    字段名3 数据类型 约束,
    ...
);

5.1 非空约束 not null

非空约束(not null)：约束字段的数据不能为NULL

例：

create table t_user(
    id int,
    username varchar(255) not null,		-- 添加非空约束
    password varchar(255)
);

若此时执行以下插入语句：

insert into t_user(id,password) values(1,'123');	-- 会报错，因为没有username字段不能为空
	-- ERROR 1364 (HY000): Field 'username' doesn't have a default value

5.2 唯一约束 unique

唯一约束(unique)：约束字段的数据不能重复，但能为null

案例1：给某一列添加unique

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'

案例2：给多个字段添加联合唯一约束

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

注意：以上代码与以下代码不一样：

create table t_user2(
	    id int,
	    usercode varchar(255) unique,
	    username varchar(255) unique,
 	  );

对以上两表执行以下语句：

insert into t_user1 values(1,'111','zs');
insert into t_user1 values(2,'111','ls');
insert into t_user1 values(3,'222','zs');

表t_user1不会报错，因为那样写是只有usercode,username都相同的时候才算不唯一。而表t_user2会报错

5.3 主键约束 primary key

主键约束(primary key)：约束字段的数据既不能为NULL，也不能重复（简称PK）

案例：

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(1,'ls','ls@123.com');	-- 报错，因为主键值不能重复
insert into t_user(username,email) values('ww','ww@123.com');		-- 报错，因为主键值不能为null

主键相关的术语：
- 主键约束：primary key
- 主键字段 : 字段添加primary key之后，该字段叫做主键字段
- 主键值：主键字段中的每一个值都是主键值。
一张表的主键约束只能有1个。(必须记住)
主键的作用：
- 表的设计三范式中第一范式要求：任何一张表都应该有主键
- 主键值是这行数据在该表中的唯一标识。（就像一个人的身份证号一样）
主键分类：
- 根据主键字段的字段数量来划分：
  - 单一主键：推荐的，常用的。
  - 复合主键：多个字段联合起来添加一个主键约束，复合主键不建议使用，因为复合主键违背三范式。
    
    注意：这里的复合主键不是多个主键，而是将多个字段联合声明为主键。
- 根据主键性质来划分：
  - 自然主键：主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的)
  - 业务主键 : 主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键、拿着身份证号做为主键。(不推荐使用)，最好不要拿着和业务挂钩的字段做为主键。因为以后的业务一旦发生改变的时候，主键也可能需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键重复。

主键值自增：

因为主键最好为自然主键，就是一个和业务没有任何关系的自然数，所以可以通过让主键值自增来保证主键值得非nul、唯一性。

语法格式：在主键声明后面加上auto_increment关键字

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(id,username) values(10,'c');
insert into t_user(username) values('d');			-- 注意自增是相对于上一条记录保持自增，所以这条记录的主键值为11
select * from t_user;

+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
| 10 | c        |
| 11 | d        |
+----+----------+

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

5.4 外键约束 foreign key

外键约束（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表叫做父表。

将以上表的建表语句写出来：

注意：
- 创建表的时候，先创建父表，再创建子表。
- 删除表的时候，先删除子表，再删除父表。
- 为子表添加数据的时候，若父表中没有子表引用的外键值，则需要先添加父表中的数据，再添加子表中的数据。
- 在删除父表数据的时候，要先删除子表中引用了该外键值的数据，再删除父表中的数据。

建表：

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)	-- 将classno字段声明为外键字段，并引用表t_class中的cno字段
);

插入数据：

insert into t_class values(101,'河南省平顶山市舞钢市垭口一高高三1班');
insert into t_class values(102,'河南省平顶山市舞钢市垭口一高高三2班');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',102);
insert into t_student values(3,'zs3',103);			-- 报错，因为父表t_class中的cno字段没有103这条记录
insert into t_student(sno,sname) values(3,'zs3');	-- 不报错，因为外键值可以为null

外键值可以为null
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
- 被引用的字段不一定是主键
- 但被引用的字段要具有unique约束，具有唯一性，不可重复！
- 但大多数的时候被引用的字段为主键

六、存储引擎

存储引擎这个名字只有在mysql中存在。(Oracle中有对应的机制，但不叫做存储引擎，叫存储方式。

mysql支持很多存储引擎，每个存储引擎都对应了一种不同的存储方式。
每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。
查看当前mysql支持的存储引擎？
```
show engines \G
```
常见的存储引擎？
- MyISAM
- InnoDB
- MEMORY

6.1 MyISAM 存储引擎

MySQL中的MyISAM引擎：

Engine: MyISAM
	     Support: YES
	     Comment: MyISAM storage engine
	Transactions: NO						// 表示不支持事务
		  	  XA: NO
	  Savepoints: NO

MyISAM存储引擎是MySQL最常用的引擎，但MySQL的默认存储引擎是InnoDB。
MyISAM管理的表具有以下特征：
- 使用三个文件表示每个表：
  - 格式文件 — 存储表结构的定义（mytable.frm）
  - 数据文件 — 存储表行的内容（mytable.MYD）
  - 索引文件 — 存储表上索引（mytable.MYI）
- 灵活的AUTO_INCREMENT字段处理
- 可被转换为压缩、只读表来节省空间
MyISAM存储引擎的优缺点：
- 优点：
  - 可被压缩，节省存储空间。
  - 并且可以转换为只读表，提高检索效率。
- 缺点：
  - 不支持事务。

6.2 InnoDB 存储引擎

MySQL中的InnoDB 引擎：

Engine: InnoDB
	     Support: DEFAULT
	     Comment: Supports transactions, row-level locking, and foreign keys
	Transactions: YES		// 表示支持事务
		      XA: YES
	  Savepoints: YES

InnoDB存储引擎是MySQL的缺省引擎。
InooDB管理的表具有下列主要特征：
- 每个InnoDB表在数据库目录中以.frm格式文件表示
- InnoDB表空间tablespace被用于存储表的内容
- 提供一组用来记录事务性活动的日志文件
- 用COMMIT(提交)、**SAVEPOINT（保存点）及ROLLBACK(回滚)**支持事务处理
- 提供全ACID兼容
- 在MySQL服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
InooDB存储引擎的优缺点：
- 优点：
  - 支持事务、行级锁、外键等。
  - 这种存储引擎数据的安全得到保障。
- 缺点：
  - 数据存储在tablespace这样的表空间中(逻辑概念)，无法被压缩，无法转换成只读。

6.3 MEMORY 存储引擎

MEMORY 中的InnoDB 引擎：

Engine: MEMORY
	     Support: YES
	     Comment: Hash based, stored in memory, useful for temporary tables
	Transactions: NO		// 不支持事务
		      XA: NO
	  Savepoints: NO

MEMORY存储引擎管理的表具有下列特征：
- 在数据库目录内，每个表均以.frm格式的文件表示。
- 表数据及索引被存储在内存中。
- 表级锁机制。
- 不能包含TEXT或BLOB字段。
InooDB存储引擎的优缺点：
- 优点：
  - 不支持事务。
  - 数据容易丢失，因为所有数据和索引都是存储在内存当中的。
- 缺点：
  - 查询速度最快。以前叫做HEPA引擎。

七、事务 transaction

COMMIT（提交）、SAVEPOINT（保存点）及ROLLBACK（回滚）

7.1 事务概述

什么是事务？
- 一个事务是一个完整的业务逻辑单元，不可再分。
- 比如：银行账户，从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
- 原子性（Atomicity）：整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
- 一致性（Consistency）：事务必须保证多条DML语句同时成功或者同时失败。
- 隔离性（Isolation）：事务A与事务B之间具有隔离，一个事务不会影响其他事务的运行。
- 持久性（Durability）：数据必须持久化到硬盘中，事务才算成功结束。
事务之间的隔离性：事务隔离性存在隔离级别，理论上隔离级别包括4个：
- 第一级别：读未提交(read uncommitted)
  - 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
  - 读未提交存在脏读(Dirty Read) 现象：表示读到了脏数据，该数据是不稳定的。
- 第二级别：读已提交(read committed)
  - 对方事务提交之后的数据我方可以读取到。
  - 读已提交存在的问题是：不可重复读取。
  - 不可重复读取：我方事务不可以重复读取数据，只能读取一次，因为多次读取可能会导致两次读取到的数据不同。
- 第三级别：可重复读(repeatable read)
  - 这种隔离级别解决了：不可重复读问题。
  - 如果对方事务提交了该数据，我方事务即使再次读取，读取到的也是未更新前的数据（即我方事务开启时数据的状态）。
  - 这种隔离级别存在的问题是：读取到的数据是幻象。
- 第四级别：序列化读/串行化读
  - 解决了所有问题。
  - 效率低，需要事务排队。
  - 不同的事务不可以同时使用数据，即如果两个事务需要操作同一个数据，先开启事务的一方如未提交事务，后开启事务的一方无法对数据进行DML操作。

设置事务的全局隔离级别：

set global transaction isolation level 事务隔离级别;	-- 包括 read uncommitted、read committed、repeatable read

查看事务的全局隔离级别：
```
select @@global.tx isolation;
```

7.2 事务原理

假如有一个业务，需要先执行一条insert，再执行一条update，最后执行一条delete，那使用事务是怎么完成这些操作的呢？
- 开启事务机制
- 执行insert语句。（执行成功后，把这个执行记录放到数据库的操作历史当中，并不会真的向硬盘中保存这条数据）
- 执行update语句。（执行成功后，把这个执行记录放到数据库的操作历史当中，并不会真的向硬盘中更新这条数据）
- 执行delete语句。（执行成功后，把这个执行记录放到数据库的操作历史当中，并不会真的向硬盘中删除这条数据）
- 提交事务或回滚事务
提交事务：向硬盘中更新数据库中的操作历史中的操作，并且删除操作历史。
回滚事务：不更新数据，只删除操作历史。

7.3 事务演示

mysql事务默认情况下是自动提交的。
- 什么是自动提交？只要执行任意一条DML语句则提交一次。

怎么关闭默认提交？

start transaction;		-- 开启一个事务，只有执行 commit 或 rollback 时才会结束该事物

提交事务：
```
commit;
```
回滚事务：
```
rollback;
```

例1：mysql事务默认情况下是自动提交的

create table t_user(
	id int primary key auto_increment,
	username varchar(255)
);
insert into t_user(username) values('zs');
rollback;				-- 发现回滚没有作用，因为事务是自动提交的，只要执行任意一条DML语句则提交一次，已提交的事务无法回滚。
select * from t_user;

+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+

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

start transaction;
insert into t_user(username) values("lisi");
insert into t_user(username) values("wangwu");
select * from t_user;
rollback; 
select * from t_user;
start transaction;
insert into t_user(username) values("wangwu");
insert into t_user(username) values("object");
insert into t_user(username) values("joke");
insert into t_user(username) values("xiaozhaozhao");
select * from t_user;
commit;
select * from t_user;

回滚前：

+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+

回滚后：

+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
+----+----------+

提交前：

+----+--------------+
| id | username     |
+----+--------------+
|  1 | zs           |
|  4 | wangwu       |		-- 可以发现主键自增机制在回滚后依然保持原有的增长
|  5 | object       |
|  6 | joke         |
|  7 | xiaozhaozhao |
+----+--------------+

提交后：

+----+--------------+
| id | username     |
+----+--------------+
|  1 | zs           |
|  4 | wangwu       |
|  5 | object       |
|  6 | joke         |
|  7 | xiaozhaozhao |
+----+--------------+

例：演示读未提交：

set global transaction isolation level read uncommitted;	-- 将MySQL的全局隔离级别设置为读未提交

例：演示读已提交：

set global transaction isolation level read committed;		-- 将MySQL的全局隔离级别设置为读已提交

例：演示可重复读：

set global transaction isolation level repeatable read;		-- 将MySQL的全局隔离级别设置为可重复读

例：演示序列化读：

set global transaction isolation level serializable;		-- 将MySQL的全局隔离级别设置为序列化读，先开启事务的一方如未提交事务，后开启事务的一方无法对数据进行DML操作。

八、锁

8.1 行级锁

在查询语句的末尾加上 ”for update“ 关键字给查询结果加上行级锁
被加上行级锁的整行记录在该事务没有结束前，其他事务都不允许对其修改（update、delete）

例：

start transaction;  -- 开启事务
select ename from emp where deptno=20 for update;  -- 在该事务没结束前，查询出的这些记录都不允许被修改
-- 其他操作
commit;  -- 提交事务

行级锁是一种悲观锁
乐观锁与悲观锁：
- 乐观锁：支持事务并发，事务也不需要排队，只不过需要一个版本号
- 悲观锁：事务必须排队执行，数据锁住了，不允许并发
如：
- 对于表：
```
ename		job		 	sal				version
-------------------------------------------------
Blake		Manager		2850.00			1.1
```
- 事务1和事务2对该条记录进行读取，版本号都为1.1
- 如果其中事务1先修改了，修改后发现版本号为1.1，于是直接提交，并将版本号改为1.2
- 然后事务2后修改，修改后准备提交发现版本号为1.2，与之前读取时的版本号不一致，不提交，而进行事务回滚。

九、索引 index

在数据库方面，查询一张表的时候有两种检索方式：
- 第一种方式：全表扫描
- 第二种方式：根据索引检索(效率很高)
什么是索引？有什么用？
- 索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
索引为什么可以提高检索效率呢？
- 其实最根本的原理是缩小了扫描的范围。
添加索引是给某一个字段，或者说某些字段添加索引。
例：
```
select ename,sal from emp where ename = 'SMITH';
```
- 当ename字段没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
- 当ename字段添加索引的时候，以上sql语句会根据索引扫描，快速定位。
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护，是有维护成本的。
- 比如：表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
什么时候考虑给字段添加索引？(满足什么条件)
- 数据量庞大。(根据客户的需求，根据线上的环境)
- 该字段很少的DML操作。(因为字段进行修改操作，索引也需要维护)
- 字段经常出现在where子句中。(经常根据哪个字段维护)
主键具有unique约束的字段会自动添加索引，因此根据主键查询效率较高，尽量根据主键检索。
怎么创建索引对象？怎么删除索引对象？
- 创建索引对象：
```
create index 索引名称 on 表名(字段名);
```
- 删除索引对象：
```
drop index 索引名称 on 表名;
```

使用explain关键字查看查询语句执行计划：

explain select ename,sal from emp where sal = 5000;

						type 为 ALL 表示从所有数据中进行查询，也就是没用索引，rows表示扫描记录的条数
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

type 为 ALL 表示从所有数据中进行查询，也就是没用索引，rows表示扫描记录的条数

索引的底层是使用B树实现的。
- 通过查询B树，快速获取到该条记录存储的物理地址。
- 然后在通过物理地址直接访问该数据。l
索引的分类：
- 单一索引：给单个字段添加索引
- 复合索引：给多个字段联合起来添加一个索引
- 主键索引：主键上会自动添加索引
- 唯一索引：有unique约束的字段会自动添加索引
  …
模糊查询的时候，如果第一个通配符为’%'时，索引会失效。

十、视图 view

什么是视图？
- 站在不同的角度去看到数据。(同一张表的数据，通过不同的角度去看待)

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

create view 视图名 as select语句;	-- 创建视图
drop view 视图名;									-- 删除视图

注意只有select语句才能创建视图
对试图进行增删改查，会影响到原表数据。(通过视图影响原表数据，不是直接操作的原表)

面向视图操作：

create view myview as select empno,ename,sal from emp;
update myview set ename = 'hehe',sal = 1 where empno 7369;  -- 通过视图修改原表数据。
delete from myview where empno = 7369; 	 					-- 通过试图删除原表数据。

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

十一、DBA命令

将数据库当中的数据导出

导出整个数据库：（在DOS命令窗口）

mysqldump 数据库名>导出文件路径 -u 用户名 -p 密码

导出数据库中指定的表：（在DOS命令窗口）

mysqldump 数据库名 表名>导出文件路径 -u 用户名 -p 密码

将数据导入数据库

create database 数据库名;
use 数据库名;
source 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_detail中的id字段即是主键也是外键

外键唯一方案：

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

注意：t_user_detail中的userid字段是外键+唯一约束。

习题：

取得每个部门最高薪水的人员名称：

select 
	ms.deptno,e.ename,ms.maxsal 
from 
	emp e
join 
	(select deptno,max(sal) as maxsal from emp group by deptno) ms 		-- 先使用查询到每个部门的最高工资，并将其作为子表
on 
	e.deptno=ms.deptno and e.sal=ms.maxsal 
order by 
	deptno;

哪些人的薪水在部门的平均薪水之上

select 
	a.*,e.ename,e.sal
from 
	emp e 
join 
	(select deptno,avg(sal) as avgsal from emp e group by deptno) a 	-- 找出每个部门的平均薪水，并重命名为a
on 
	e.deptno = a.deptno and e.sal > a.avgsal;

取得部门中（所有人的）平均的薪水等级

select 
	e.deptno,avg(s.grade) 
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal 
group 
	by e.deptno;

取得部门中（所有人的）平均薪水的等级

select 
	das.deptno,sg.grade 
from 
	salgrade sg 
join 
	(select deptno,avg(sal) avgsal from emp group by deptno) das 	-- 找出每个部门的平均薪水，并重命名为das
on 
	das.avgsal between sg.losal and sg.hisal;

不准用组函数（Max），取得最高薪水

select * from emp order by sal desc limit 1;		-- 使用 降序+limit 取得

select 
	ename,sal 
from 
	emp 
where 
	sal not in(select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);		-- 使用自连接的方式，效率低

取得平均薪水最高的部门的部门编号

方法一、使用降序+limit

select 
	deptno,avg(sal) avgsal 
from 
	emp 
group by 
	deptno 
order by 
	avgsal desc 
limit 
	1;

方法二、先找出每个部门的平均薪水，再使用max()函数，再使用having

select 
	max(e.avgsal) 
from 
	(select deptno,avg(sal) avgsal from emp group by deptno) e;

select 
	deptno,avg(sal) as avgsal 
from 
	emp 
group by 
	deptno 
having 
	avgsal =  (select max(e.avgsal) from (select deptno,avg(sal) avgsal from emp group by deptno) e);

取得平均薪水最高的部门的部门名称

select 
	d.dname,avg(e.sal) avgsal 
from 
	emp e 
join 
	dept d 
on 
	e.deptno = d.deptno 
group by 
	d.dname 
order by 
	avgsal desc 
limit 
	1;

大枫树

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫