java基础教学第三集 - 编程好6文档

接上期后续

本期分享第二章节-MySQL

大家已经学完基础的JavaSE篇章，内容很多~

已经入门了哦，现在继续努力吧！

大家需保持耐心，慢慢的学~

争取你们学习的速度！

跟上我更新的速度哦~

今日新篇章

【MySQL】

【主要内容】

1. 数据库相关概念

2. 数据库和操作工具的安装

3. 创建数据库、表、视图、索引

4. 查询、新增、删除、修改数据

5. 条件查询

6. 数据库的高级查询

7. MySQL的常用函数

8. MySQL版本新特性

9. MySQL索引优化

10. MySQL事务

【学习目标】

1. 数据库的相关概念

1.1 数据

1，定义：描述事物的符号

2，表达形式：多种表现形式：文本，图形，音频，视频

1.2 数据库（Database, DB）

1，粮库车库

2，存放数据的仓库存放在计算机中，按照一定格式存放可为用户共享

1.3 数据库管理系统（Database Management System, DBMS）

1，如何科学的组织和存储数据，如何高效的获取和维护数据，靠数据库管理系统完成

2，Oracle MySQL SQL Server(微软) DB2 FoxPro，Access

1.4 数据库应用程序（DBAS）

1，在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序

2，学生管理系统、人事管理系统、图书管理系统

1.5 数据库管理员（Database Administrator, DBA）

1，数据库管理系统的操作者

1.6 最终用户

1，数据库应用程序的使用者

1.7 数据库系统

1，数据库+数据库管理系统+数据库应用程序+最终用户

1.8 数据库的发展阶段

1，网状数据库

2，层次数据库

3，关系数据库

采用关系[二维表]结构存储与管理数据

采用结构化查询语言(sql)作为客户端数据库服务器间沟通的桥梁

目前主流的数据库技术

4，对象数据库

1.9 NOSQL数据库

1，Not only sql数据库泛指非关系数据库。如MongoDB,Redis

2，关系数据库在超大规模和高并发的web2.0纯属动态网站已经显示力不从心，暴露了很多难以克服的问题。Nosql数据库的产生就是为了解决大无框数据集合多重数据种类带来的挑战，尤其是大数据应用难题

1.10 市场上流行的关系型数据库

数据库厂商

Oracle 甲骨文公司

MySQL MySQL

SQLServer Microsoft

DB2 IBM

Sqlite RichardHipp

2. 数据库相关安装

2.1 准备工作

msi文件：mysql数据库的安装文件

navicat:操作数据库的工具

Navicat:激活成功教程navicat的工具

vcredis_x64.exe:安装mysql环境检查的通过的一个工具

2.2 安装mysql

2.2.1 右键安装

2.2.2 读条中

2.2.3 安装主页面

2.2.4 这里选择只安装服务

2.2.5 下一步检查环境【停住，别安装】

上图中环境有问题，说差一个Microsoft Visual C++2013

2.2.6 找到准备文件夹里面的工具包

vcredis_x64.exe ----》双击安装它

2.2.7 到2.2.5步回退一步，再下一步【发现没有问题了-如下】

2.2.8 下一步

2.2.9 执行安装并下一步

2.2.10 下一步

2.2.11 设置root用户的密码

2.2.12 设置安装的服务名

2.2.13 下一步完成配置

2.2.14 执行完成如果全部对钩说明安装成功

2.3 安装navcat

2.3.1 右键以管理员方式运行

2.3.2 安装主页面

2.3.3 下一步

2.3.4 下一步

2.3.5 下一步到结束【安装中】

2.3.6 安装完成

2.4 激活成功教程navcat

2.4.1 解压上图的文件

2.4.2 再解压进入

2.4.3 双击运行上面的文件如下

2.4.4 点击patch后

2.4.5 生成注册码并复制这个注册码

2.4.6 打开navcat并点击注册

2.4.7 输入注册码并点击激活

2.4.8 输入注册码并点击激活选择手动激活

2.4.9 按下图操作

2.4.10 检查激活状态

2.4.11 连接mysql

2.5 配置MySQL环境变量

2.5.1 找到mysql的安装目录

2.5.2 复制这个目录地址右键此电脑->属性->高级系统设置

2.5.3 打开环境变量

2.5.4 新建

2.5.5 配置PATH并确定

2.5.6 测试环境变量是否生效

按win+r键输入cmd

输入 mysql -uroot -p

3. 数据库相关基础操作

3.1 启动|停止

3.1.1 方式1

3.1.2 方式2

win+r cmd

net start mysql 启动

net stop mysql 停止

3.2 使用root账户登陆

mysql -uroot -p

root是默认的用户名---超级管理员

是之前我们安装的时候设置的默认密码

3.3 显示所有数据库

show databases;

3.4 默认的几个数据库说明

3.4.1 information_schema

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件

3.4.2 mysql

这个是mysql的核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除，如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。

3.4.3 performance_schema

mysql 5.5 版本新增了一个性能优化的引擎： PERFORMANCE_SCHEMA这个功能默认是关闭的：

需要设置参数： performance_schema 才可以启动该功能，这个参数是静态参数，只能写在

my.cnf 中不能动态修改。

windows里面叫 my.ini文件 C:ProgramDataMySQLMySQL Server 5.7 【默认在这个目录】

linux里面叫my.cnf文件

3.4.4 sys

通过这个库可以快速的了解系统的元数据信息

这个库确实可以方便DBA发现数据库的很多信息，解决性能瓶颈都提供了巨大帮助

3.4.5 test

这个库就是一个测试库而已

3.4.6 重要说明

以上的所有默认库。只有、test库能删除，其它的都不要删除

3.5 使用某一个数据库

-- 语法

-- use <数据库名称>;

-- 例如：使用mysql数据库

use mysql;

3.6 修改密码

-- 1使用mysql数据库

use mysql;

-- 2修改密码命令(将root用户的密码修改为)

update mysql.user set authentication_string=PASSWORD('') where user='root;

-- 3刷新

flush privileges;

3.7 创建用户

3.7.1 命令

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

3.7.2 说明

username：你将创建的用户名

host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

3.7.3 例子

CREATE USER 'dog'@'localhost' IDENTIFIED BY '';

CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '';

CREATE USER 'pig'@'%' IDENTIFIED BY '';

CREATE USER 'pig'@'%';

3.8 给用户授权

3.8.1 命令

GRANT privileges ON databasename.tablename TO 'username'@'host'

3.8.2 说明

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL

databasename：数据库名

tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

3.8.3 例子

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';

GRANT ALL ON *.* TO 'pig'@'%';

GRANT ALL ON maindataplus.* TO 'pig'@'%';

3.8.4 注意

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

3.9 创建一个数据库

-- 创建数据库有两种方式：

-- 第一种语法如下
-- 注意：<>里面的内容都是可变的
-- create database <数据库名称>;
-- 例如
create database whpowernode;
-- 第二种语法如下
-- if not exists 创建之前会做check操作，如果数据库名称在MySQL数据库管理系统中不存在才创建
-- charset default 为数据库指定默认编码
-- create database [if not exists] <数据库名称> default charset <数据库字符集编码>;
-- 例如：
create database if not exists whpowernode default charset UTF8;
create database if not exists whpowernode default charset UTF8MB4;

--第三种: create database <数据库名称> [character set utf8];
-- UTF8和UTF8MB4区别？
-- 绝大多数情况下使用UTF8编码的字符集一个汉字占据3个字符，但是有极少数情况下一个汉字占据4个字符
-- 占据4个字符的汉字不能使用UTF8存储，必须使用UTF8MB4存储
-- UTF8MB4字符集编码表示一个汉字最多占据4个字符 M Most B Byte
-- 注意：创建数据库指定的字符集编码必须跟MySQL数据库里面的my.ini字符集编码保持一致

3.10 修改默认编码[找到my.ini文件]

3.11 删除一个数据库

-- 第一种方式语法：
-- drop database <数据库名称>;
-- 例如：
drop database whpowernode;
-- 第二种方式语法：
-- 删除之前先判断存在才删除
-- drop database if exists <数据库名称>;
-- 例如：
drop database if exists whpowernode;

3.12 修改数据库

alter database <数据库名称> character set 新编码名;

3.13 3.13 查看库

-- 查询mysql管理系统软件下所有的库:

show databases;

-- 查询指定库的详细信息

show create database <数据库名称>;

-- 查看当前用户使用的是具体哪个库

select database();

4. 什么是SQL

SQL是一个面向过程的结构化查询语言，全称Struct Query Language 。我们对MySQL做的任何操作(命令)都建立在结构化查询语言中。

SQL由以下四部分组成：DDL、DML、DQL、TCL

4.1 DDL 数据定义语言

全称Data Definition Language，主要用于创建和删除结构。

Ø 1)创建结构相关操作：库、表、视图、索引 create

Ø 2)删除结构相关操作：库、表、视图、索引 drop

Ø 3)改变结构相关操作：库、表、视图、索引 alter

4)查询结构相关操作: 库、表、视图、索引 show

4.2 DML 数据操纵语言

DML对表和视图进行插入、删除、更新相关操作，全称Data Manipulation Language。插入数据关键字：insert

Ø 删除数据关键字：delete

Ø 修改数据关键字：update

4.3 DQL 数据查询语言

对表和视图进行操作，全称： Data Query Language

Ø DQL关键字 select

4.4 TCL 事务控制语言

TCL全称Transaction Control Language，使用事务管理DML操作。

Ø 提交事务关键字 commit;

Ø 回滚事务关键字 rollback;

Ø 开启事务关键字 start transaction;

5. MySQL数据类型

5.1 整数类型

5.2 小数类型

5.3 定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 decimal(m,d) 参数m是总个数，d是小数位。

5.4 字符串

5.4.1 char、varchar、text区别

char：存储定长数据很方便，CHAR字段上的索引效率级高，必须在括号里定义长度，可以有默认值，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间（自动用空格填充），且在检索的时候后面的空格会隐藏掉，所以检索出来的数据需要记得用什么trim之类的函数去过滤空格。

varchar：存储变长数据，但存储效率没有CHAR高，必须在括号里定义长度，可以有默认值。保存数据的时候，不进行空格自动填充，而且如果数据存在空格时，当值保存和检索时尾部的空格仍会保留。另外，varchar类型的实际长度是它的值的实际长度+1，这一个字节用于保存实际使用了多大的长度。

text：存储可变长度的非Unicode数据，最大长度为2^31-1个字符。text列不能有默认值，存储或检索过程中，不存在大小写转换，后面如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是你插入数据的时候，超过你指定的长度还是可以正常插入。

5.4.2 实战结论

1、经常变化的字段用varchar；

2、知道固定长度的用char；

3、超过255字节的只能用varchar或者text；

4、能用varchar的地方不用text；

5、能够用数字类型的字段尽量选择数字类型而不用字符串类型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了；

6、同一张表出现多个大字段，能合并时尽量合并，不能合并时考虑分表

5.5 日期时间类型

MySQL数据类型含义

date 日期 '2021-1-1'

time 时间 '12:25:36'

datetime 日期时间 '2021-1-1 22:06:44'

timestamp 自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

5.6 Logic类型

Logic类型使用bit表示，它只能存储两个值0或者1

5.7 数据类型的属性

6. MySQL表的相关操作DDL

6.1 创建表

6.1.1 语法

create table <表名称>

(

列名称1 数据类型1,

列名称2 数据类型2,

..........

列名称n 数据类型n

);

6.1.2 实例创建一个学生表tb_student

create table tb_student(

id int(11),

stu_name varchar(50),

stu_sex bit,

stu_hobby varchar(50),

stu_age smallint

);

-- 注意：最后一列没有逗号

-- 列与列之间以半角逗号相隔

6.1.3 实例创建一个学生表tb_student并指定编码和存储引擎

create table student(

id int(11),

stu_name varchar(50),

java基础教学第三集 stu_sex bit,

stu_hobby varchar(50),

stu_age smallint

)default charset=UTF8, ENGINE=INNODB;

DEFAULT CHARSET 用于指定表中数据的字符集编码方式

ENGINE 用于指定表的存储引擎

6.2 数据存储的问题

现在我们在数据库中创建了一个表，那么如我们把电脑重启，这个表还在不在呢？

测试结果为在那是为什么呢？

原因是当我们创建表个表之后，在我们数据库的文件系统里面就会有一个对应的文件存在

.frm文件：保存了每个表的元数据，包括表结构的定义等；

.ibd文件：InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table = 1)产生的存放该表的数据和索引的文件。

6.3 查看表结构

创建表之后如何查看是否创建成功？可以通过desc命令来查看表结构

-- 第一种方式查看表结构

-- desc <表名称>;

desc student;

-- 第二种方式查看表结构

-- show create table <表名称>;

show create table tb_student;

6.4 修改表结构

基本语法如下：alter table <表名称>;

6.4.1 案例1

添加列，例如：向student表新增加一列stu_height学生身高

-- 语法如下：

-- alter table <表名称> add column <列名称> <数据类型>;

alter table student add column stu_height int(11);

-- 查看表结构

desc student;

6.4.2 案例2

删除某一个列，例如：删除student表中的学生身高

-- 语法如下：

-- alter table <表名称> drop column <列名称>;

alter table student drop column stu_height;

6.4.3 案例3

修改列名称和数据类型：例如修改stu_sex 为 student_sex 数据类型修改为 varchar(30)

-- 语法如下：

-- alter table <表名称> change column <原列名称> <新列名称> <数据类型>;

-- 修改stu_sex 为 student_sex 数据类型修改为 varchar(30)

alter table student change column stu_sex student_sex varchar(30);

6.4.4 案例4

修改某一列的数据类型，例如：将学生年龄修改为int类型

-- 语法如下：

-- alter table <表名称> modify column <原来的列名称> <新的数据类型>;

-- 例如：

alter table student modify column stu_age int(11);

6.5 删除表

6.5.1 直接删除

-- drop table <表名称>;

drop table EMP_bak;

6.5.2 删除之前先判断表是否存在

-- drop table if exists <表名称>;

-- 刪除之前先做校验，表在数据库中存在才删除

drop table if exists EMP_bak;

6.6 显示当前库的所有表

show tables;

7. MySQL表里面数据的相关操作DML

通过我们上面的操作，存放数据的容器创建好了，那么现在接着就是要向这个容器里面添加修改删除数据了

7.1 添加数据

7.1.1 单个添加

-- 往表中添加数据(insert，插入)

-- 插入方式1：insert into 表名(字段1,字段2,...字段N) values(值1,值2,...值N);

insert into students(stuNo,stuName) values('10086','王二麻子');

insert into students(stuNo,stuName,gender,age) values('10010','张三','男',20);

-- 插入方式2: insert into 表名 values(值1,值2,...值N);

insert into students values('10011','李四','男',28);

-- 插入方式3: insert into 表名 set 字段1=值1,字段2=值2,...字段n=值n;

insert into students set stuNo='10000',stuName='凤姐';

7.1.2 批量添加

-- 插入方式4: insert into 表名(字段1,字段2,...字段N) values(值1,值2,...值N),(值1,值2,...值N),(值1,值2,...值N)

insert into students(stuNo,stuName,gender,age) values

('10010','刘涛','男',18),('10020','刘西陵','男',28),('10030','张文杰','男',38);

7.2 修改数据

7.2.1 批量修改

-- 修改所有数据

-- 语法

-- update 表名 set 字段1=新值1,字段2=值2,...字段N=值N [where 条件];

-- 例如：student表的年龄全部修改为50

update student set stu_age = 50;

7.2.2 单个修改

-- 如果只想修改某一行的数据，需要加上条件
-- 例如：我只想修改id为1031的年龄为60
-- where 表示行过滤
-- where id=1031 表示只选择id为1031的行进行修改
-- 下面代码执行步骤：
-- 首先执行 update student 确定修改那张表
-- 然后执行 where 条件确定修改表中的哪一行，不满住条件的行都会被过滤掉
update student set stu_age = 60 where id = 1031;

7.2.3 单个修改多个值

update student set stu_age = 60，name='习大大' where id = 1031;

7.3 删除数据

7.3.1 删除一个

-- 删除某一条数据语法：

-- delete from 表名 [where 条件];

-- 例如：删除id为108的student数据

delete from student where id = 108;

7.3.2 删除全部

-- 语法

-- delete from <表名称> ;

-- 例如：删除student表所有数据

delete from student;

7.3.3 truncate关键字

truncate是DDL，删除所有数据，不支持where，不能回滚

delete是DML，可以删除部分数据，因为支持where，可以回滚

总体上truncate效率比delete高，他们都只能删除数据不能删除表结构，只有drop才能删除表结构

-- truncate [table] 表名;

8. 数据完整性

为什么要学习数据完整性？我们上面创建的tb_student表缺乏安全性，还会有重复的id，这些都是脏数据。

什么是数据完整性？需要为MySQL数据库中的表定义一个规则，确保表中数据的有效性、一致性、安全性、尽最大的可能减少脏数据，重复的数据(冗余)。

8.1 主键约束

什么是主键：主要的关键字，一个表如果创建了主键那么该行的所有数据必须在表中唯一。

身份证号就是身份证表的主键，能够确保在表中的唯一性，并且身份证编号是不能为空的

身份证编号

姓名

地址

所属公安局

性别

张三

武汉

武昌分局

男

李四

杭州

武昌分局

男

王五

长沙

武昌分局

女

主键约束小结：非空并且唯一

8.1.1 主键设计原则

在开发中，一般的主键为数值类型，呈递增趋势

如果不想每次都插入主键，我们可以让数据自动增长

语法：

create table tb_class(

id int(11) auto_increment, -- id是主键列，不用显示插入值让其自动增长

class_name varchar(30) not null, -- not null 非空约束

class_desc varchar(100), -- 班级描述

primary key (id) -- 为tb_class表的id列设置主键约束

)ENGINE=INNODB,DEFAULT CHARSET UTF8;

注意： MySQL 数据库 auto_increment自动增长和主键primary key 是配套的不能单独使用

8.2 非空约束

create table tb_class(

id int(11) auto_increment, -- id是主键列，不用显示插入值让其自动增长

class_name varchar(30) not null, -- not null 非空约束

class_desc varchar(100), -- 班级描述

primary key (id) -- 为tb_class表的id列设置主键约束

)ENGINE=INNODB,DEFAULT CHARSET UTF8;

8.3 唯一约束

制定一个规则，让表的某一列数据必须唯一

特征：某列数据可以为空，但是必须唯一

例如：学生的手机号码

create table tb_1(

phone int unique

)

8.4 默认约束

为某一列制定一个默认规则

例如：性别默认为 0

create table tb_1(

phone int unique,

sex int DEFAULT ‘0’

)

8.5 外键约束

外部的关键字叫做外键，通常为多张表中建立联系，确保表与表之间的数据安全性，一致性，能够减少数据冗余(重复的数据)。

前面几个约束：在一张表建立约束(规则)

外键约束：多张表之间建立约束(关联，联系)

场景：创建一个tb_student表，为其添加默认约束、唯一约束、非空约束、外键约束

create table tb_student(

id int(11) auto_increment,

stu_name varchar(50) not null,

stu_mobile varchar(20) unique, -- unique 唯一混熟

stu_sex bit default 1, -- 默认约束性别默认为1 0女 1男

class_id int(11) not null,

primary key (id),

-- 学生表的class_id建立外键去关联 tb_class表的主键

foreign key (class_id) references tb_class(id)

)ENGINE=INNODB,DEFAULT CHARSET UTF8;

8.6 约束关键字总结

约束名称关键字

主键约束 primary key

非空约束 not null

唯一约束 unique

默认约束 default

外键约束 foreign key.......references

约束分为两类：

1 行级别约束：主键约束、外键约束

2 列级别约束：非空约束、唯一约束、默认约束

外键约束小结：

foreign key(外键列) references 主表 (主键列)
references 关键字的右边是主表的主键列，左边是从表的外键列，该关键字在主表和从边之间建立了联系。

创建表小结：没有外键的表是主表，有外键的表是从表。先创建主表后创建从表(有外键的表是从表)

插入数据小结：先插入主表数据后插入从表数据

删除数据小结：先删除从表数据后删除主表数据

9. 表关系

9.1 一对一（one-to-one）【了解】

一对一关系就如球队与球队所在地址之间的关系，一支球队仅有一个地址，而一个地址区也仅有一支球队。

数据表间一对一关系的表现有两种，一种是外键关联，一种是主键关联。图示如下：

一对一外键关联：

9.2 一对多/多对一（many-to-one）【掌握】

存在最普遍的映射关系，简单来讲就如球员与球队的关系；

一对多：从球队角度来说一个球队拥有多个球员即为一对多

多对一：从球员角度来说多个球员属于一个球队即为多对一

数据表间一对多关系如下图：

9.2.1 案例：

-- 班级表

create table tb_class(

id int(11) auto_increment, -- id是主键列，不用显示插入值让其自动增长

class_name varchar(30) not null, -- not null 非空约束

class_desc varchar(100), -- 班级描述

primary key (id) -- 为tb_class表的id列设置主键约束

)ENGINE=INNODB,DEFAULT CHARSET UTF8;

-- 学生表
create table tb_student(
  id int(11) auto_increment,
  stu_name varchar(50) not null,
  stu_mobile varchar(20) unique, -- unique 唯一混熟
  stu_sex bit default 1, -- 默认约束性别默认为1 0女 1男
  class_id int(11) not null,
  primary key (id),
    -- 学生表的class_id建立外键去关联 tb_class表的主键
  foreign key (class_id) references tb_class(id)
)ENGINE=INNODB,DEFAULT CHARSET UTF8;

9.3 多对多（many-to-many）【掌握】

多对多关系也很常见，例如学生与选修课之间的关系，一个学生可以选择多门选修课，而每个选修课又可以被多名学生选择。

数据库中的多对多关联关系一般需采用中间表的方式处理，将多对多转化为两个一对多。

数据表间多对多关系如下图：

9.3.1 案例：

-- 多对多

-- 学生表

create table tb_student(

id bigint(20) primary key auto_increment comment '主键',

stu_name varchar(50) comment '学生名',

stu_mobile varchar(20) comment '手机号',

stu_sex varchar(2) default '1' comment '性别默认为1 0女 1男'

);

-- 课程表

create table tb_course(

id bigint(20) primary key auto_increment comment '主键',

course_name varchar(30) not null comment '课程名称'

);

-- 学生课程中间表

create table tb_student_course(

stu_id bigint(20) comment '学生id',

course_id bigint(11) comment '课程id',

course_score float(4,1) comment '课程的成绩',

FOREIGN KEY(course_id) REFERENCES tb_course(id),

FOREIGN KEY(stu_id) REFERENCES tb_student(id)

);

9.4 员工系统设计【自行完成】

现在要为一个公司设计一个系统

9.5 要求

1，可以记录员工信息

2，可以记录公司所有的部门信息

3，可以记录工资等级信息

4，可以记录公司的职位

9.6 表结构

9.6.1 部门表（dept）

9.6.2 员工表（emp）

9.6.3 工资等级表（salgrade）

9.6.4 表关系分析

9.6.5 创建表的脚本

-- ----------------------------

-- 创建部门表

-- ----------------------------

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (

DEPTNO int(11) NOT NULL COMMENT '部门编号',

DNAME varchar(20) NOT NULL COMMENT '部门名称',

LOC varchar(20) COMMENT '部门所在的位置',

PRIMARY KEY (DEPTNO)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '部门表';

-- ----------------------------

-- 创建员工表

-- ----------------------------

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (

EMPNO int(11) NOT NULL COMMENT '雇员的编号',

ENAME varchar(50) NOT NULL COMMENT '雇员的姓名',

JOB varchar(50) NOT NULL COMMENT '职位',

MGR int(11) NULL DEFAULT NULL COMMENT '雇员对应的领导编号',

HIREDATE date NULL DEFAULT NULL COMMENT '雇员的雇佣日期',

SAL decimal(7, 2) NULL DEFAULT NULL COMMENT '基本工资',

COMM decimal(7, 2) NULL DEFAULT NULL COMMENT '奖金，佣金',

DEPTNO int(11) NULL DEFAULT NULL COMMENT '雇员所在的部门编号',

PRIMARY KEY (EMPNO),

CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES dept (DEPTNO) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '员工表';

-- ----------------------------

-- 工资等级表

-- ----------------------------

DROP TABLE IF EXISTS salgrade;

CREATE TABLE salgrade (

GRADE int(11) NOT NULL COMMENT '工资的等级主键',

LOSAL decimal(7, 2) NOT NULL COMMENT '此等级的最低工资',

HISAL decimal(7, 2) NOT NULL COMMENT '此等级的最高工资',

PRIMARY KEY (GRADE)

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '工资等级表' ;

9.6.6 初始化数据脚本

-- ----------------------------

-- 部门表插入数据

-- ----------------------------

INSERT INTO DEPT VALUES (10, '财务部', '武汉');

INSERT INTO DEPT VALUES (20, '研发部', '武汉');

INSERT INTO DEPT VALUES (30, '销售部', '深圳');

INSERT INTO DEPT VALUES (40, '业务部', '上海');

-- ----------------------------

-- 员工表插入数据

-- ----------------------------

INSERT INTO EMP values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, null, 20);

INSERT INTO EMP values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);

INSERT INTO EMP values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);

INSERT INTO EMP values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, null, 20);

INSERT INTO EMP values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);

INSERT INTO EMP values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, null, 30);

INSERT INTO EMP values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, null, 10);

INSERT INTO EMP values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, null, 20);

INSERT INTO EMP values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000.00, null, 10);

INSERT INTO EMP values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);

INSERT INTO EMP values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, null, 20);

INSERT INTO EMP values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, null, 30);

INSERT INTO EMP values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, null, 20);

INSERT INTO EMP values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, null, 10);

-- ----------------------------

-- 工资等级表插入数据

-- ----------------------------

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);

10. 单表数据查询

10.1 最简单的查询方式

select * from emp;

select * from dept;

select empno, ename,sal,job from emp;

10.2 使用算术表达式

select empno, ename,sal,sal*1.08 from emp;

select empno, ename,sal, sal*12 from emp;

select empno, ename,sal, sal*12 +1000 from emp;

注：在Select语句中，对数值型数据可以使用算术运算符创建表达式

10.3 使用字段别名

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

select empno, ename "Ename", sal*12 "Anual Salary" from emp;

select sal*12+5000 as "年度工资(加年终奖)" from emp;

10.3.1 字段别名说明

重命名查询结果中的字段，以增强可读性

别名如果含有空格或其他特殊字符或大小写敏感，需用双引号引起来。

AS可以省略

10.4 去重查询

缺省情况下，查询结果中包含所有符合条件的记录行，包括重复行

select deptno from emp;

使用DISTINCT关键字可从查询结果中清除重复行

select distinct deptno from emp;

select distinct job from emp;

DISTINCT的作用范围是后面所有字段的组合

select, distinct deptno job from emp;

10.5 排序查询

-- 排序: SELECT * FROM 表名 order by 排序字段1,排序字段2 desc(降序)/asc(升序)

使用order by 子句对查询结果进行排序

排序方式包括升序（asc，缺省）和降序（desc）两种：

select empno, ename, sal from emp order by sal;

select empno, ename, sal from emp order by sal desc ;

按多字段排序

select deptno, empno, ename, sal from emp order by deptno, sal;

使用字段别名排序

select empno, ename, sal*12 annsal from emp order by annsal;

10.6 分页查询

关键字：limit

10.6.1 案列1：查询前3条客户信息

-- select * from 表名 limit 起始下标,pageSize;

select * from emp limit 3

小结：limit 后面跟1个参数，例如 limit 3 此时表示最多返回3行

10.6.2 案列2：查询第三条3条到第6条的客户信息

limit 参数1 , 参数2

参数1：表示伪列(index)的偏移量(下标从0开始)
参数2：表示返回最大限制的行数

select * from emp limit 3,6

10.7 合并查询

10.7.1 作用

将多个select语句联合(合并)为一个select语句，涉及的关键字union 和union all。

union all 不管是否重复，全部合并

union 如果有重复的，过滤掉重复的

10.7.2 案例

select * from emp where DEPTNO=10

union all

select * from emp where DEPTNO=20

select * from emp where DEPTNO=10

union

select * from emp where DEPTNO=20

11. where子句

作用：指定查询条件使用where子句

11.1 用法举例

select * from emp where deptno=10;

select * from emp where ename = 'JACK';

select * from emp where hiredate = '2020-12-12';

注意：

字符串和日期值要用单引号扩起来

字符串大小写敏感

日期值格式敏感，缺省的日期格式是'YYYY-MM-DD HH:mm:ss'

11.2 查询条件中可以使用比较运算符

MySQL支持如下比较运算符： > >= < <= != <>

注意：MySQL使用=运算符来判断表达式是否相等，它没有==

Java使用!=表示不等于，MySQL也支持。但是我们不要使用，效率太低了

MySQL使用的不等于使用<>

select * from emp where sal > 2900;

select * from emp where deptno <> 30;

select * from emp where sal between 1600 and 2900;

select * from emp where ename in('SMITH','CLARK','KING');

11.3 使用LIKE运算符执行模糊查询（通配查询）

% 表示零或多个字符 _ 表示一个字符

对于特殊符号可使用ESCAPE 标识符来查找

用法举例

select * from emp where ename like 'S%';

select * from emp where ename like '_A%';

select * from emp where ename like '%_%' escape '';

11.4 使用IS NULL运算符进行空值判断

用法举例

select * from emp where comm is null;

select * from emp where comm is not null;

11.5 查询条件中可以使用逻辑运算符

select * from emp where deptno = 10 and sal > 1000;

select * from emp where deptno = 10 or job = ‘CLERK’;

select * from emp where sal not in (800, 1500, 2000);

11.6 SQL优化问题

AND: 把检索结果较少的条件放到后面

OR: 把检索结果较多的条件放到后面

11.7 四种运算符优先级：算术>连接>比较>逻辑

可使用小括号强行改变运算顺序

select * from emp where job='SALESMAN' or job='CLERK' and sal>=1280;

select * from emp where (job='SALESMAN' or job='CLERK') and sal>=1280;

12. 聚合函数

使用函数可以大大提高SELECT语句操作数据库的能力。它给数据的转换和处理提供了方便。

函数只是将取出的数据进行处理，不会改变数据库中的值。

12.1 函数的分类

12.1.1 单行函数分类

Ø 数学函数

Ø 字符串函数

Ø 日期和时间函数

Ø 条件判断函数

Ø 系统信息函数

Ø 加密函数

Ø 格式化函数

12.1.2 多行函数（聚合函数）

Ø 仅适用数值型的多行函数

n sum() 求和

n avg() 求平均值

Ø 适用任何类型数据

n count() 计数

n max() 求最大值

n min() 求最小值

12.2 单行函数

12.2.1 数学函数

（1）ABS(x) 返回x的绝对值

（2）PI() 返回圆周率π，默认显示6位小数

（3）SQRT(x) 返回非负数的x的二次方根

（4）MOD(x,y) 返回x被y除后的余数

（5）CEIL(x)、CEILING(x) 返回不小于x的最小整数

（6）FLOOR(x) 返回不大于x的最大整数

（7）ROUND(x)、ROUND(x,y) 前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位

（8）SIGN(x) 返回参数x的符号，-1表示负数，0表示0，1表示正数

（9）POW(x,y)和、POWER(x,y) 返回x的y次乘方的值

（10）EXP(x) 返回e的x乘方后的值

（11）LOG(x) 返回x的自然对数，x相对于基数e的对数

（12）LOG10(x) 返回x的基数为10的对数

（13）RADIANS(x) 返回x由角度转化为弧度的值

（14）DEGREES(x) 返回x由弧度转化为角度的值

（15）SIN(x)、ASIN(x) 前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦

（16）COS(x)、ACOS(x) 前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦

（17）TAN(x)、ATAN(x) 前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切

（18）COT(x) 返回给定弧度值x的余切

（19）RAND(x) 返回0-1之间的随机小数

12.2.2 字符串函数

（1）CHAR_LENGTH(str) 计算字符串字符个数

（2）CONCAT(s1,s2，...) 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL

（3）CONCAT_WS(x,s1,s2,...) 返回多个字符串拼接之后的字符串，每个字符串之间有一个x

（4）INSERT(s1,x,len,s2) 返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符

（5）LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写

（6）LEFT(s,n)、RIGHT(s,n) 前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符

（7）LPAD(s1,len,s2)、RPAD(s1,len,s2) 前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符

（8）LTRIM(s)、RTRIM(s) 前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除

（9）TRIM(s) 返回字符串s删除了两边空格之后的字符串

（10）TRIM(s1 FROM s) 删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格

（11）REPEAT(s,n) 返回一个由重复字符串s组成的字符串，字符串s的数目等于n

（12）SPACE(n) 返回一个由n个空格组成的字符串

（13）REPLACE(s,s1,s2) 返回一个字符串，用字符串s2替代字符串s中所有的字符串s1

（14）STRCMP(s1,s2) 若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1

（15）SUBSTRING(s,n,len)、MID(s,n,len)两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串

（16）LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）

（17）REVERSE(s) 将字符串s反转

（18）ELT(N,str1,str2,str3,str4,...) 返回第N个字符串

12.2.3 日期和时间函数

（1）CURDATE()、CURRENT_DATE()将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

（3）MONTH(date)和MONTHNAME(date)前者返回指定日期中的月份，后者返回指定日期中的月份的名称

（4）WEEK(d)、WEEKOFYEAD(d)前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周

（5）DAYOFYEAR(d)、DAYOFMONTH(d)前者返回d是一年中的第几天，后者返回d是一月中的第几天

（6）EXTRACT(type FROM date)从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

（7）TimeStampDiff(间隔类型，前一个日期，后一个日期)

例如距离现在差了多少年：TimeStampDiff(year, 前一个日期, now())

间隔类型有：second秒，minute分，hour时，day天，week周，month月，quarter季度，year年

（8）DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)返回将起始时间加上expr type之后的时间，比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒

12.2.4 条件判断函数

（1）IF(expr,v1,v2)如果expr是TRUE则返回v1，否则返回v2

（2）IFNULL(v1,v2)函数如果v1的值不为NULL，则返回v1，否则返回v2。

（2）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

12.2.5 系统信息函数

（1）VERSION()查看MySQL版本号

（2）CONNECTION_ID()查看当前用户的连接数

（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的

（4）CHARSET(str)查看字符串str使用的字符集

（5）COLLATION()查看字符串排列方式

12.2.6 加密函数

（1）PASSWORD(str)从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用

（2）MD5(str)为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回

（3）ENCODE(str, pswd_str)使用pswd_str作为密码，加密str

（4） DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串

12.2.7 格式化函数

（1）FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回

（2）CONV(N,from_base,to_base)不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制

（3）INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特

（4）INET_NTOA(expr)给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示

（5）BENCHMARK(count,expr)重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间

12.3 多行函数

对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数。

12.3.1 看图说话

12.3.2 常用的多行函数

sum() avg() 仅适用数值型

count() max() min() 适用任何类型数据

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

select max(hiredate), min(hiredate) from emp;

12.3.3 多行函数与空值

多行函数除了count(*)外，都跳过空值而处理非空值

select count(comm),sum(comm),avg(comm) from emp;

可使用IF()函数强制多行函数处理空值

select count(IF(comm is null,0,comm)),sum(IF(comm is null,0,comm)),avg(IF(comm is null,0 ,comm)) from emp;

12.3.4 count函数的说明

count(*)返回组中总记录数目；

count(exp)返回表达式exp值非空的记录数目；

count(distinct(exp))返回表达式exp值不重复的、非空的记录数目。

select count(*) from emp;

select count(comm) from emp;

select count(distinct(deptno)) from emp;

select count(IF(comm IS null,0,comm)) from emp;

12.4 GROUP BY子句

GROUP BY 子句将表中数据分成若干小组

12.4.1 语法格式

select column, group_function(column)

from table

[where condition]

[group by group_by_expression]

[order by column];

12.4.2 使用举例

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

select deptno,, count(*),avg(sal) from emp group by deptno；

12.4.3 注意事项

出现在SELECT列表中的字段，如果不是包含在多行函数中，那么该字段必须同时在GROUP BY子句中出现。

错误：select ename,deptno,sum(sal) from emp group by deptno;

|--以上的SQL在MYSQL中是不错的，但是在oracle是错的，在开发中避免这样写

包含在GROUP BY子句中的字段则不必须出现在SELECT列表中。

如果没有GROUP BY子句，SELECT列表中不允许出现字段（单行函数）与多行函数混用的情况

select empno, sal from emp; //合法

select avg(sal) from emp; //合法

select empno, avg(sal) from emp; //非法

不允许在WHERE 子句中使用多行函数

select deptno, avg(sal) from emp

where avg(sal) > 2000; //执行where时尚未执行groupby 及其他

group by deptno;

12.5 HAVING子句

对分组查询的结果进行过滤，要使用having从句。

having从句过滤分组后的结果，它只能出现在group by从句之后，而where从句要出现在group by从句之前。

where过滤行，having过滤分组。having支持所有where操作符。

12.5.1 语法格式

select column, group_function(column)

from table

[where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

执行过程：from--where -- group by– having– select-- order by

12.5.2 用法举例

列出平均工资大于8000元的部门ID

select deptno, avg(sal) from emp

group by deptno having avg(sal) > 8000 order by deptno;

12.5.3 思考练习

统计人数小于4的部门的平均工资。

统计各部门的最高工资，排除最高工资小于8000的部门。

显示部门编号大于101 的部门的人数，要求人数大于3

13. 连接查询SQL92

13.1 连接查询概述

Ø 笛卡尔集

Ø 等值连接

Ø 非等值连接

Ø 外连接

Ø 自连接

说明。SQL92是1992年提出的查询语法，向上兼容

13.2 连接查询语法

13.2.1 语法规则：

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

13.2.2 特点

在 WHERE 子句中写入连接条件

当多个表中有重名列时，必须在列的名字前加上表名作为前缀

13.2.3 连接的类型：

等值连接 -- Equijoin

非等值连接 -- Non-equijoin

左连接 --LEFT JOIN

右连接 --RIGHT JOIN

自连接 -- Self join

13.3 笛卡尔集

select * from dept;//4条记录
select * from emp; ;//14条记录
select * from dept,emp; ;//4*14=56条记录
总结
检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
检索出的列的数目将是第一个表中的列数加上第二个表中的列数
应该保证所有联结都有where子句，不然数据库返回比想要的数据多得多的数据

13.4 等值查询

select * from dept,emp where dept.deptno=emp.deptno;

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

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

select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno and d.deptno=10

select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno and loc='武汉';

注意点

当被连接的多个表中存在同名字段时，须在该字段前加上"表名."前缀

可使用AND 操作符增加查询条件；

使用表别名可以简化查询

使用表名（表别名）前缀可提高查询效率；

13.5 非等值查询

要求：查询员工的工资等级

select empno,ename,job,sal,grade

from emp e,salgrade s

where e.sal<s.hisal and e.sal>s.losal;

select empno,ename,job,sal,grade

from emp e,salgrade s

where e.sal<s.hisal and e.sal>s.losal and e.job='MANAGER';

13.6 自连接

特点：将一个表当两个表使用

使用举例：查询每个员工的工号、姓名、直接领导姓名

特点：将一个表当两个表使用

使用举例：查询每个员工的工号、姓名、经理姓名

select e1.empno,e1.ename,e1.job,e2.ename

from emp e1 ,emp e2

where e1.mgr=e2.empno

order by e1.empno;

14. 连接查询SQL99【重点掌握】

14.1 SQL99语法概述

SQL92的语法规则的缺点：

语句过滤条件和表连接的条件都放到了where子句中。

当条件过多时，联结条件多，过滤条件多时，就容易造成混淆

SQL99修正了整个缺点，把联结条件，过滤条件分开来，包括以下新的TABLE JOIN的句法结构：

Ø 交叉连接（Cross join）

Ø 自然连接（Natural join）

Ø 使用Using子句建立连接

Ø 使用On子句建立连接

Ø 连接（ Outer join ）

n 内连接INNER JOIN

n 左连接LEFT JOIN

n 右连接RIGHT JOIN

14.2 语法

select 字段列表

from table1

[cross join table2] | //1：交叉连接

[natural join table2] | //2：自然连接

[join table2 using (字段名)] | //3：using子句

[join table2 on (table1.column_name = table2.column_name)] | //4：on子句

[(left | right | full outer) join table2

on (table1.column_name = table2.column_name)]; //5：左/右/满外连接

14.3 交叉连接

Cross join产生了一个笛卡尔集，其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件;

可以使用where条件从笛卡尔集中选出满足条件的记录。

用法举例

select dept.deptno,dname,ename

from dept cross join emp;

等价于

select dept.deptno,dname,ename

from dept,emp;

14.4 自然连接

Natural join基于两个表中的全部同名列建立连接

Ø 从两个表中选出同名列的值均对应相等的所有行

Ø 如果两个表中同名列的数据类型不同，则出错

Ø 不允许在参照列上使用表名或者别名作为前缀

Ø 自然连接的结果不保留重复的属性

举例：

select empno, ename, sal, deptno, dname

from emp natural join dept

14.5 Using子句

如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列（参照列）名。

using子句引用的列在sql任何地方不能使用表名或者别名做前缀

举例：

select e.ename,e.ename,e.sal,deptno,d. dname

from emp e join dept d

using(deptno)

where deptno=101

14.6 On子句

自然连接的条件是基于表中所有同名列的等值连接

为了设置任意的连接条件或者指定连接的列，需要使用ON子句

连接条件与其它的查询条件分开书写

使用ON 子句使查询语句更容易理解

举例：

1----select ename,dname

from emp inner join dept on emp.deptno=dept.deptno

where emp.deptno=30;

2---select empno, ename, sal, emp.deptno, dname

from emp inner join dept

on (emp.deptno = dept.deptno and sal>5000);

3---select *

from dept, emp

where dept.deptno = emp.deptno and sal>5000;

第三种是SQL92里面的查询方法，和上面两种等价

14.7 外连接

14.7.1 左外联接

两个表在连接过程中除返回满足连接条件的行以外，还返回左表中不满足条件的行，这种连接称为左外联接。

14.7.2 右外联接

两个表在连接过程中除返回满足连接条件的行以外，还返回右表中不满足条件的行，这种连接称为右外联接。

14.7.3 满外联接

两个表在连接过程中除返回满足连接条件的行以外，还返回两个表中不满足条件的所有行，这种连接称为满外联接。

内连接:在SQL99规范中，内连接只返回满足连接条件的数据。

14.7.4 外连接举例

左外连接

select deptno, dname,empno,ename from dept left join emp using(deptno);

右外连接

select deptno, dname,empno,ename from dept right join emp using(deptno);

满外连接

select deptno, dname,empno,ename from dept full join emp using(deptno);

15. 子查询

15.1 问题引入

如何查得所有比“CLARK”工资高的员工的信息

select * from emp

where sal>(select sal from emp where ename='CLARK');

思考：查询工资高于平均工资的雇员名字和工资。

思考：查询和Lucy同一部门且比他工资低的雇员名字和工资。

15.2 语法格式：

select 字段列表 from table

where 表达式 operator (select 字段列表 from table);

15.3 特点

子查询在主查询前执行一次

主查询使用子查询的结果

使用子查询注意事项

在查询是基于未知值时应考虑使用子查询

子查询必须包含在括号内

建议将子查询放在比较运算符的右侧，以增强可读性。

除非进行Top-N 分析，否则不要在子查询中使用ORDER BY 子句。

如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符

15.4 单行子查询

单行子查询只返回一行记录

对单行子查询可使用单行记录比较运算符

< 、 > 、 = 、 >=、 <= 、 <>

15.4.1 举例：

select * from emp

where sal > (select sal from emp where empno = 101);

思考：查询工资最高的雇员名字和工资。

查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息

SELECT empno, ename, job FROM emp

WHERE job =(SELECT job FROM emp WHERE empno=7788)

AND hiredate < (SELECT hiredate FROM emp WHERE empno=7788);

查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字

select empno,ename,sal,hiredate

from emp

where sal>(select sal from emp where ename='SCOTT') or hiredate<(select hiredate from emp where ename='SCOTT')

15.5 多行子查询

多行子查询返回多行记录

对多行子查询只能使用多行记录比较运算符

ALL 和子查询返回的所有值比较

ANY 和子查询返回的任意一个值比较

IN 等于列表中的任何一个

15.5.1 案例

查询工资低于任何一个'CLERK'的工资的雇员信息。

查询工资比所有的 'SALESMAN'都高的雇员的编号、名字和工资。

查询部门20中职务同部门10的雇员一样的雇员信息。

15.5.2 案例答案

SELECT empno, ename, job,sal

FROM emp

WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK') AND job <> 'CLERK';

SELECT empno, ename,sal

FROM emp

WHERE sal > ALL(SELECT sal FROM emp WHERE job= 'SALESMAN');

SELECT empno, ename, job FROM emp

WHERE job IN (SELECT job FROM emp WHERE deptno=10)

AND deptno =20;

15.5.3 查询在雇员中有哪些人是领导

分析：有哪些人的empno号在mgr这个字段中出现过，应首先查询mgr中有哪些号码，然后再看有哪些人的雇员号码在此出现

select empno, ename from emp

where empno in (

select distinct mgr from emp

);

15.5.4 找出部门编号为20的所有员工中收入最高的职员

select * from emp

where sal >= all(

select sal from emp where deptno = 20)

and deptno = 20

15.5.5 查询每个部门平均薪水的等级

分析：首先将每个部门平均薪水求出来，然后把结果当成一张表，再用这张结果表和salgrade表做连接，以此求得薪水等级

先求出每个部门平均薪水的表t。

将t和salgrade进行关联查询就可以了。

select * from

salgrade s, (select deptno,avg(sal) avg_sal

from emp group by deptno) t

where t.avg_sal between s.losal and s.hisal;

16. 视图

16.1 视图的定义及作用

16.1.1 定义

视图是从若干基本表和（或）其他视图构造出来的表。

在创建一个视图时，只是存放的视图的定义，也即是动态检索数据的查询语句，而并不存放视图对应的数据

在用户使用视图时才去求相对应的数据。所以视图被称作“虚表”

16.1.2 作用

可以限制对数据的访问，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。

可以使复杂的查询变的简单。在编写查询后，可以方便地重用它而不必知道他的基本查询细节。

提供了对相同数据的不同显示

16.2 视图的创建和删除

16.2.1 创建单表视图

CREATE VIEW MYVIEW1

SELECT * FROM EMP;

16.2.2 创建或修改一个单表视图

CREATE 【OR REPLACE】 VIEW MYVIEW2

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO

FROM EMP

WHERE DEPTNO >=102

16.2.3 使用视图添加和修改数据

INSERT INTO MYVIEW1 (EMPNO,ENAME,SAL)VALUES(9999,'AAAA',3000);

16.2.4 为视图添加相关权限

CREATE OR REPLACE VIEW MYVIEW2

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO

FROM EMP

WHERE DEPTNO >=20

16.2.5 删除视图

DROP VIEW MYVIEW4;

16.2.6 统计视图

CREATE OR REPLACE VIEW MYVIEW4

SELECT DEPTNO 部门编号,DNAME 部门名称,AVG(SAL) 平均工资,MAX(SAL) 最高工资,COUNT(*)人数

FROM EMP E

JOIN DEPT D USING(DEPTNO)

WHERE DEPTNO IS NOT NULL

GROUP BY DEPTNO，DNAME

ORDER BY AVG(SAL)

16.2.7 基于其它视图的视图

CREATE OR REPLACE VIEW MYVIEW5

SELECT * FROM MYVIEW4

WHERE 部门编号=10

16.3 视图的总结

1.视图对应一个查询语句；视图是（从若干基本表和（或）其他视图构造出来的）表

2.视图进行查询，添加，修改，删除，其实就是对背后的表进行相应操作

3 虚表在创建一个视图时，只是存放的视图的定义，也即是动态检索数据的查询语句，而并不存放视图对应的数据

4.视图的好处

1.安全可以只显示部分行部分列的数据;可以对添加的数据进行检查；可以设置为只读视图

2.操作简单

列出薪金高于公司平均薪金的所有员工。

3.只显示多个数据库表的部分列，部分行的视图

17. MySQL存储引擎

17.1 InnoDB 引擎(MySQL5.5以后默认使用)

MySQL 5.5 及以后版本中的默认存储引擎，他的优点如下：

灾难恢复性好

支持事务

使用行级锁

支持外键关联

支持热备份

对于InnoDB引擎中的表，其数据的物理组织形式是簇表（Cluster Table），主键索引和数据是在一起的，数据按主键的顺序物理分布

实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取

支持热备份

17.2 MyISAM引擎

特性如下：

不支持事务

使用表级锁，并发性差

主机宕机后，MyISAM表易损坏，灾难恢复性不佳

可以配合锁，实现操作系统下的复制备份、迁移

只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳

数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能

17.3 两种存储引擎的大致区别表现在：

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

3）InnoDB支持外键，MyISAM不支持

4）从MySQL5.5.5以后，InnoDB是默认引擎

5）InnoDB不支持FULLTEXT类型的索引

6）InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。

7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。

9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

有人说MYISAM只能用于小型应用，其实这只是一种偏见。

如果数据量比较大，这是需要通过升级架构来解决，比如分表分库，读写分离，而不是单纯地依赖存储引擎。

现在一般都是选用InnoDB了，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

17.4 总结

1.MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。

2.MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

17.5 其它说明

存储引擎：数据在数据库中的组织方式（存储方式）。

例如：

create table test_table(

id int(11)

)ENGINE=INNODB ,DEFAULT CHARSET = UTF8;

创建表的时候指定存储引擎为INNODB，这个存储引擎在MySQL中用的非常广泛，因为它支持外键、支持事务、支持行级锁。

查看MySQL支持的存储引擎命令如下：

SHOW ENGINES;

除了上述操作之外，还可以在数据库安装盘的my.ini文件可以配置数据库表默认的存储引擎。

default-storage-engine=INNODB ;

18. MySQL事务

18.1 概念

完成一个事情需要的一系列步骤(操作)，这些操作要么同时成功，要么同时失败

18.2 事务的基本操作

场景：有两个账户分别为张三和李四，他们默认都有1000块钱的余额。在这两个账户之间进行转账。

-- 1 创建tb_account表

create table tb_account(

id int(11) auto_increment comment '主键',

uName varchar(20) comment '用户名',

account varchar(30) comment '银行卡账号',

pwd varchar(30) comment '银行卡密码',

account_blance float(12,2) comment '账户余额',

primary key (id)

);

insert into tb_account values

(null,'宝强','110','',1000),

(null,'马蓉','120','',1),

(null,'宋喆','911','',0.5);

-- 转账: 宝强转账1000万给马蓉

-- 操作1：娃娃

update tb_account set account_blance=account_blance-1000 where account='110' and pwd='';

-- 操作2: 打气筒

update tb_account set account_blance=account_blance+1000 where account='120' and pwd='';

如何解决上面的问题呢？

使用事务来介入(管理)转账的操作

18.3 事务的四大特征(ACID)

原子性：事务每一步都是不可再分

一致性：张三和李四账户一共2000块钱，不管转账多少次总金额不变

持久性：当一个事务执行成功(完毕)，数据会持久化到磁盘的数据文件中。例如转账成功：张三余额变为500，李四余额变为1500.

隔离性：A事务和B事务同时操作一份数据，相互之间不影响。

18.4 事务的提交方式

1 自动提交，MySQL默认为自动提交。

不需要写commit;就会自动将DML语句持久化提交

2 手动提交，Oracle默认手动提交。

如何在MySQL中查看提交方式：

-- 查询结果为1表示自动提交，0表示手动提交
select @@autocommit;
-- 修改提交方式（自动提交修改为手动提交）
set @@autocommit = 0 ;

18.5 事务的基本操作

1 开启事务

start transaction;

2 提交事务

commit;

3 回滚事务

rollback;

注意：一旦使用start transaction;开启事务那么自动提交将失效

如果所有操作都正常执行使用commit;提交事务

当发生异常情况回滚事务，数据(此时为tb_account表)通常回滚到开启事务之前的状态

18.6 转账操作

-- 1 开启事务
start transaction;
-- 2 执行SQL语句
update tb_account set account_blance=account_blance-500 where id=1;
手机转账异常情况：转账过程中手机没电了
update tb_account set account_blance=account_blance+500 where id=2;
-- 3 如果SQL语句全部执行成功就提交事务，如果其中任何一步执行失败，立刻回滚事务
-- 此时第一个update执行成功，第二个update语句执行失败了,并没有提交事务，查询结果如下：
select * from tb_account;
/*
+----+-----------+----------------+
| id | user_name | account_blance |
+----+-----------+----------------+
| 1 | ZS        |            500 |
| 2 | LS        |           1000 |
+----+-----------+----------------+
*/
-- 问题：张三账户的余额减少了，李账户余额没有增加。这就是脏数据
-- 此时需要将脏数据回滚到开启事务之前
rollback;
-- 回滚完毕再次查询
select * from tb_account;
-- 此时事务回滚到开启之前的状态
/*
+----+-----------+----------------+
| id | user_name | account_blance |
+----+-----------+----------------+
| 1 | ZS        |           1000 |
| 2 | LS        |           1000 |
+----+-----------+----------------+
*/
-- 一个事务一旦开启了，在没有执行commit;或者rollback;之前事务不会结束
-- 相关面试题：工作中有没有用到事务？请解释事务的概念？不使用事务会发生什么问题？使用事务能够解决什么问题？解释事务的四大特征[隔离级别]？

18.7 事务隔离级别

18.7.1 ISO定义的四大隔离级别

查询隔离级别select @@tx_isolation

隔离级别1最低，4最高。隔离级别越高就越安全，同时内存资源消耗也越大。隔离级别越高效率越低下。

工作中：1和4都不用，只会在2和3之间切换

MySQL默认的事务隔离级别为3，Oracle默认隔离界别默认为2

18.7.2 并发下的脏读，不可重复读，幻读的问题

问题：不使用事务隔离级别会引发啥问题？使用事务隔离级别能够解决什么问题

18.7.2.1 脏读问题

一个事务(A)读取到另一个事务(B)没有提交的数据（破坏了隔离性）.

例如：事务A开启事务做转账，DML语句执行成功但是没有commit；事务B在另一个窗口开启了，执行Select语句读取tb_account数据，读取到的结果是事务A没有提交的数据。

18.7.2.2 不可重复读问题

同一个事务中多次读取到的数据不一致(破坏了一致性，update和delete)

例如：事务A开启事务做转账，DML语句执行成功但是没有commit；；事务B在另一个窗口开启了，执行Select语句读取tb_account数据，读取的结果正确（1000,1000）.

事务A里面提交了事务。然后事务B再次做Select操作查询结果也正确(500,1500)

问题：事务B在一次事务中对tb_account表做了两次select操作，两次操作查询的结果不一致。

18.7.2.3 幻读问题

事务A插入一条数据，能够使用select获取结果，此时事务B几乎同时插入了一条或者大量数据，此时事务A看不到事务B的更新（破坏了一致性，insert）。

查看事务隔离级别：

-- REPEATABLE-READ 可重复读
-- tx 表示事务的简称 transaction
-- isolation表示隔离性
select @@tx_isolation;

-- 设置事务的隔离级别读未提交
set session transaction isolation level READ UNCOMMITTED;

18.7.3 read uncommitted脏读测试

使用隔离级别为read uncommitted;完成转账，目的观察会出现脏读问题，如何解决？

-- 开启事务
start transaction;
-- 执行事务的转账操作
update tb_account set account_blance=account_blance-500 where id=1;
update tb_account set account_blance=account_blance+500 where id=2;

18.7.4 read uncommitted脏读问题解决

如何解决脏读问题？修改事务隔离级别：读已提交

set session transaction isolation level read committed;

进行转账操作

18.7.5 如何解决不可重复读的问题

将事务隔离级别设置为"可重复读 " repeatable read , 解决不可重复读的问题。

set session transaction isolation level repeatable read;
-- 还原为默认值
update tb_account set account_blance=1000;
-- 开启事务
start transaction;
-- 执行事务的转账操作
update tb_account set account_blance=account_blance-500 where id=1;
update tb_account set account_blance=account_blance+500 where id=2;

18.7.6 幻读问题演示

事务A插入一条数据，能够使用select获取结果，此时事务B几乎同时插入了一条或者大量数据，此时事务A看不到事务B的更新（破坏了一致性，insert）。

18.7.7 串行化serializable测试

能够解决所有的问题，但是效率低下，它类似Java的synchronized

Java使用synchronized用来锁对象。MySQL使用serializable锁表，事务A开启事务，做了DML操作，但是没有提交。此时事务B开启事务，执行select操作，没有查询到数据，因为此时tb_account表被事务A占用了(锁住了)。

set session transaction isolation level serializable;

19. SQL优化之索引

19.1 索引是啥

Ø MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

Ø 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

Ø 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

Ø 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

Ø 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

Ø 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

Ø 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

Ø 建立索引会占用磁盘空间的索引文件。

19.2 准备工作-创建person表

-- ----------------------------

-- 创建Person表

-- ----------------------------

DROP TABLE IF EXISTS person;

CREATE TABLE person (

PID int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',

PNAME varchar(50) NOT NULL COMMENT '姓名',

PSEX varchar(10) NULL DEFAULT NULL COMMENT '性别',

PAGE int(11) NOT NULL COMMENT '年龄',

SAL decimal(7, 2) NULL DEFAULT NULL COMMENT '工资',

PRIMARY KEY (PID)

) ENGINE = InnoDB AUTO_INCREMENT = 7001 CHARACTER SET = utf8mb4 COMMENT = '人员表';

19.3 索引分类

19.3.1 普通索引

如何创建普通索引？

-- 语法
-- CREATE INDEX 索引名称 on 表名称(列名称);

场景1：为person表的pname列建立普通索引

CREATE INDEX INDEX_PERSON_PNAME ON PERSON(PNAME);

如何查看SQL语句是否使用索引？可以使用EXPLAIN关键字来查看

EXPLAIN select * from person where pname= 'Jack';

在MySQL中为表创建主键的同时默认也创建了一个索引

-- 下面的DQL语句使用了索引。
EXPLAIN
select * from person
where id =1;

场景2：where pname like 模糊查询用到索引没有

-- 前后模糊中间精确不会使用索引
EXPLAIN
select * from person where pname like '%e%';

-- 前面模糊后面精确也不会使用索引
EXPLAIN
select * from person where pname like '%e';
-- 只有前面精确后面模糊才会使用索引，工作中数据量大的表模糊查询尽量不要使用 '%%',也不要使用'%a',他们都不支持索引
EXPLAIN
select * from person where pname like 'e%';

19.3.2 索引压力测试

19.3.2.1 场景1

向person表中插入500W条数据,为pname创建一个索引，观察创建索引之前执行耗时是多长时间，创建索引之后耗时多长时间。

删除之前的pname的索引

ALTER TABLE PERSON DROP INDEX INDEX_PERSON_PNAME;

插入500W条数据

create procedure insert_person(in max_num int(10))

begin

declare i int default 0;

set autocommit = 0; -- 把autocommit设置成0，这样可以只提交一次，否则。。。。。

repeat

set i = i +1;

insert into person (PID,PNAME,PSEX,PAGE,SAL) values (i,rand_string(5),IF(RAND()>0.5,'男','女'),FLOOR((RAND()*100)+10),FLOOR((RAND()*19000)+1000));

until i = max_num

end repeat;

commit;

end;

-- 调用

call insert_person();

-- 我查询耗时

不创建索引测试

select * from person where PNAME="oQlJN"; -- 耗时

查看索引文件

创建索引测试

CREATE INDEX INDEX_PERSON_PNAME ON PERSON(PNAME);

创建耗时

查询测试

select * from person where PNAME="oQlJN"; -- 耗时

再次查看索引文件

问题1：为person表的pname创建索引消耗了9.472秒，为什么耗时这么长？

在person.ibd数据文件中创建了一个“目录”，目录的结构是一个B+Tree，类似Java的TreeMap，表的数据越多，索引结构越大，创建时间越长。

问题2：没有创建索引执行WHERE PNAME耗时3.892秒，创建索引执行耗时0.03秒，为什么这么快？

没有创建索引，进行全文检索(从person.ibd数据文件中检索所有的输入，然后在判断是否满足where条件)，创建索引不进行全文检索，直接查询索引

问题3：创建索引之前数据文件person.ibd大小，创建索引之后大小，为什么会有这么大的变化？

创建索引就相当于创建目录，表中的数据越大，数据文件就越多。

问题4：INDEX_PERSON_PNAME索引的内部结构是啥？

类似TreeMap的B+Tree

19.3.2.2 场景2

如何创建一个复合索引，例如：我要对年龄和性别两个列创建复合索引

-- 为多个列创建复合索引

CREATE INDEX INDEX_PERSON_AGE_SEX ON PERSON(PAGE,PSEX);

-- 没有创建索执行下面DQL语句引耗时1.427秒，创建索引执行耗时1毫秒

SELECT * FROM PERSON WHERE PAGE =22 AND PSEX='男';

-- 创建的复合索引，但是只对第二个索引列单独进行where条件，也会使用索引

-- 如果创建复合索引，经常使用的列放在前面

EXPLAIN

SELECT * FROM PERSON WHERE PSEX='男';

19.3.2.3 场景3

PERSON表的PSEX列只有2个有效值，为该列建立索引会提高查询效率吗？

CREATE INDEX INDEX_CUSTOMER_SEX ON PERSON(PSEX);

-- 没有建立索引耗时2.7秒
-- 建立索引耗时之后耗时12.5秒

SELECT * FROM PERSON WHERE PSEX='男';

19.3.2.4 小结

1如果为某个列创建索引，那么就会在数据文件中创建一个类似TreeMap的文件。如果一个表的数据很多，那么索引会大量的占据数据文件的磁盘空间。

2不是所有的列都适合建立索引，如果某个列的有效数据很少不要建立索引。

3可以为表的多个列创建复合索引，经常使用的列放在前面。

4创建主键的同时默认也创建了一个索引。

19.3.3 唯一索引

唯一索引关键字： unique

之前学习了唯一约束，当我们创建了一个唯一约束的时候就创建了一个唯一索引，唯一约束就是唯一索引。

场景：对CUSTOMER_BAK3表的CUST_MOBILE列创建唯一索引，先观察没有创建索引执行WHERE CUST_MOBILE=XXX条件耗时多长时间，然后创建唯一索引，最后观察创建索引执行WHERE CUST_MOBILE=XXX条件耗时多长时间耗时多长时间。

前置条件：先删除唯一约束

-- 删除唯一索引
ALTER TABLE PERSON DROP INDEX UNIQUE_INDEX_PERSON_MOBILE;

-- 创建唯一索引语法
-- CREATE UNIQUE INDEX 索引名称 ON 表名称(列名称);

-- PERSON表有500W数据，创建唯一索引耗时：16.491秒
CREATE UNIQUE INDEX UNIQUE_INDEX_PERSON_MOBILE ON PERSON(MOBILE);

-- 没有创建唯一索引耗时1.8秒，创建唯一索引查询耗时2毫秒
EXPLAIN
select * from PERSON where mobile = '';

小结：工作中如果表中的某个列数据全部唯一，可以考虑创建唯一索引

19.3.4 组合索引

-- 为多个列创建复合索引

ALTER TABLE PERSON DROP INDEX INDEX_PERSON_AGE_SEX;

CREATE INDEX INDEX_PERSON_AGE_SEX ON PERSON(PNAME,PSEX);

-- 没有创建索执行下面DQL语句引耗时11.427秒，创建索引执行耗时1毫秒

SELECT * FROM PERSON WHERE PNAME ="oQlJN" AND PSEX='男';

EXPLAIN SELECT * FROM PERSON WHERE PSEX='男' -- AND PNAME ="oQlJN";

-- 创建的复合索引，但是只对第二个索引列单独进行where条件，不会使用索引

-- 如果创建复合索引，经常使用的列放在前面，并且查询时一定要带上第一列的条件

19.3.5 全文索引【了解】

【概述】

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

你可能没有注意过全文索引，不过至少应该对一种全文索引技术比较熟悉：各种的搜索引擎。虽然搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理是一样的。

【版本支持】

开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

Ø MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；

Ø MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

Ø 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

【使用】

工作中用的不多不是重点。

关键字: fulltext

-- 语法：

-- CREATE FULLTEXT INDEX 索引名称 ON 表名称(列名称);

-- 示例：

-- 为EMP表的ENAME列创建全文索引
CREATE FULLTEXT INDEX FULLTEXT_EMP_ENAME ON EMP(ENAME);

-- 测试

EXPLAIN select * from EMP WHERE MATCH(ENAME) AGAINST('JACK')

坑点：只有建立了全文索引后，才能使用 MATCH(ENAME) AGAINST('JACK') 的语法

19.4 删除索引

-- 语法
-- ALTER TABLE 表名称 DROP INDEX 索引名称；
-- 示例
ALTER TABLE PERSON DROP INDEX INDEX_PERSON_PNAME;

20. 数据库设计原则

20.1 引入三大范式

20.1.1 必须保证数据库设计的合理性

数据库设计关系整个系统的架构，关系到后续的开发效率和运行效率

数据库的设计主要包含了设计表结构和表之间的联系

20.1.2 如何是合理数据库

结构合理

冗余较小

尽量避免插入删除修改异常

20.1.3 如何才能保证数据库设计水平

遵循一定的规则

在关系型数据库中这种规则就称为范式

20.1.4 什么是范式（NF= NormalForm）

范式是符合某一种设计要求的总结。

要想设计一个结构合理的关系型数据库，必须满足一定的范式。

20.1.5 范式的分类

第一范式

第二范式

第三范式

Boyce Codd范式=NCNF

由Boyce和Codd提出的,

比3NF又进了一步

通常认为是修正的第三范式.

第四范式

第五范式

各个范式是依次嵌套包含的

范式越高，设计质量越高，在现实设计中也越难实现

一般数据库设计，只要达到第三范式，即可避免异常的出现

20.2 第一范式

20.2.1 要求

最基本的范式

数据库表每一列都是不可分割的基本数据项，同一列中不能有多个值

简单说就是要确保每列保持原子性

第一范式的合理遵循需要根据系统的实际需求来定

20.2.2 示例

用户表（用户名，家庭地址）

用户表（用户名，省，城市，详细地址）

系（系名称，系主任，系高级职称人数）

系（系名称，系主任，系教授人数，系副教授人数）

20.3 第二范式

20.3.1 要求

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

即在一个数据库表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

20.3.2 示例

学号和课程编号作为联合主键

课程名称只依赖于课程编号，而和学号没有关系

分析以上的设计发现数据冗余

如何解决呢？

提取出学生表

提取成课程表

提取选课表，存放选课记录

1，学生表

2，课程表

3，选课表

20.4 第三范式

20.4.1 要求

确保数据表中的每一列数据都和主键直接相关，而不能间接相关

属性不依赖于其他非主属性。

20.4.2 示例1：学生班级表

分析以上的表，发现有问题存在班级名称和班级信息出现了数据冗余

如何解决

学生表

班级表

1，学生表

2，班级表

20.5 范式的优缺点

20.5.1 优点

结构合理

冗余较小

尽量避免插入删除修改异常

20.5.2 缺点

性能降低

多表查询比单表查询速度慢

20.5.3 数据库的设计应该根据当前情况和需求做出灵活的处理。

在实际设计中，要整体遵循范式理论。

如果在某些特定的情况下还死死遵循范式也是不可取的，因为可能降低数据库的效率，此时可以适当增加冗余而提高性能。

20.5.4 示例：

比如经常购物车条目的中除了条目编号，商品编号，商品数量外，可以增加经常使用的商品名称，商品价格等

商品表

订单明细表

其它这个设计在开发中还要优化----思考哦

21. 设计一个商品入库审批的数据库

22. 设计一个商品购物车的数据库

更多干货我们下期再说！

下期会分享

第三章节

JDBC