一、数据库和SQL概述以及MySQL安装与基本使用

数据库的好处

保存数据的容器一般有：数组、集合、文件等。
好处
1、实现数据持久化；
2、使用完整的管理系统统一管理，易于查询。

数据库的概念

DB

数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。

数据库的特点

1、将数据放到表中，表再放到库中;
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性；
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计；
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”；
5、表中的数据是按行存储的，每一行类似于java中的“对象”。

DBMS

数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器。

常见的数据库管理系统：MySQL、Oracle、DB2、SqlServer等

SQL

结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。

SQL语言概述

SQL的优点：
1、不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL;
2、简单易学;
3、虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

SQL语言分类

1、DDL（Data Definition Language): 数据定义语句，用于库和表的创建、修改、删除；

2、DML（Data Manipulation Language): 数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性；

3、DCL（Data Control Language): 数据控制语句，用于定义用户的访问权限和安全级别。

4、DQL（Data Query Language）：数据查询语言，用来查询记录（数据）

DDL
DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：

1
2
3
4
5
CREATE TABLE：创建数据库表
ALTER TABLE：更改表结构、添加、删除、修改列长度
DROP TABLE：删除表
CREATE INDEX：在表上建立索引
DROP INDEX：删除索引

DML
DML用于查询与修改数据记录，包括如下SQL语句：

1
2
3
**INSERT**：添加数据到数据库中**UPDATE**：修改数据库中的数据
**DELETE**：删除数据库中的数据
**SELECT**：选择（查询）数据

==SELECT是SQL语言的基础，最为重要。==

DCL
DCL用来控制数据库的访问，包括如下SQL语句：

1
2
3
4
5
6
GRANT：授予访问权限
REVOKE：撤销访问权限
COMMIT：提交事务处理
ROLLBACK：事务处理回退
SAVEPOINT：设置保存点
LOCK：对数据库的特定部分进行锁定

MySQL安装与使用

MySQL数据库产品的介绍

MySQL产品的特点：
• MySQL数据库隶属于MySQL AB公司，总部位于瑞典，后被oracle收购。
• 优点：
– 成本低：开放源代码，一般可以免费试用
– 性能高：执行很快
– 简单：很容易安装和使用

MySQL数据库的安装

• DBMS分为两类：
– 基于共享文件系统的DBMS （Access ）
– 基于客户机——服务器的DBMS（MySQL、Oracle、SqlServer）
MySQL的版本
• 社区版（免费）
• 企业版（收费）
数据库官网下载
具体的安装教程可以参考：MySQL安装教程
启动和停止MySQL服务
方式一：通过计算机管理方式
右击计算机—管理—服务—启动或停止MySQL服务
方式二：通过命令行方式
启动：net start mysql服务名
停止：net stop mysql服务名
MySQL服务端的登录和退出
登录
mysql –h 主机名 –u用户名 –p密码
退出
exit

MySql数据库的使用

MySQL语法规范

– 不区分大小写
– 每句话用;或\g结尾
– 各子句一般分行写
– 关键字不能缩写也不能分行
– 用缩进提高语句的可读性

  • 进入 mysql, 在命令行中输入: mysql –uroot –p#### (其中：####表示密码）在这里插入图片描述
  • 查看 mysql 中有哪些个数据库: show databases; 在这里插入图片描述
  • 使用一个数据库: use 数据库名称; 在这里插入图片描述
  • 新建一个数据库: create database 数据库名
    在这里插入图片描述
    5.查看指定的数据库中有哪些数据表: show tables; 在这里插入图片描述 6.建表:
    在这里插入图片描述7. 查看表的结构：desc 表名
    在这里插入图片描述8. 删除表: drop table 表名
    在这里插入图片描述
  • 查看表中的所有记录: select * from 表名; 在这里插入图片描述
  • 向表中插入记录：insert into 表名(列名列表) values(列对应的值的列表);
    在这里插入图片描述注意：插入 varchar 或 date 型的数据要用 单引号 引起来
  • 修改记录: update 表名 set 列1 = 列1的值, 列2 = 列2的值 where …
    在这里插入图片描述
  • 删除记录: delete from 表名 where ….
    在这里插入图片描述
  • 查询所有列: select * from 表名;
    在这里插入图片描述
  • 查询特定的列: select 列名1,列名2, … from 表名
    在这里插入图片描述
  • 对查询的数据进行过滤：使用 where 子句
    在这里插入图片描述
    运算符：
  • % 匹配任意多字符；
    在这里插入图片描述
  • _只匹配一个字符
    在这里插入图片描述
  • 查询 name 中有 o 的人的名字
    在这里插入图片描述
  • 查询 name 中 第 3 个字母是 r 的人的名字
    在这里插入图片描述
  • 查询 email 不为 空 的所有人的信息
    在这里插入图片描述
  • 查询所有客户信息, 且按 salary 升序排列
    在这里插入图片描述
    查询所有客户信息, 且按 salary 降序排列
    在这里插入图片描述

二、MySQL之DDL语言

DDL语言概述

DDL（Data Definition Language):数据定义语句，用于库和表的创建、修改、删除。

库的管理

创建库

语法
–create database 【if not exists】 库名【 character set 字符集名】;
在这里插入图片描述
相关其他命令
– show databases;查看当前所有数据库。
– use employees;“使用”一个数据库，使其作为当前数据库。
命名规则
• 数据库名不得超过30个字符，变量名限制为29个；
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符；
• 不能在对象名的字符间留空格；
• 必须不能和用户定义的其他对象重名；
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突；
• 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了。

修改库

因为修改库有很大风险造成数据错乱，所以不建议修改库！
修改库名可以采用：退出mysql客服端，找到安装mysql的目录–data–找到想要修改的库修改名称–重启mysql客服端。

删除库

语法
drop database 【if exists】 库名;
在这里插入图片描述

表的管理

创建表

语法
create table 【if not exists】 表名(
字段名 字段类型 【约束】,
字段名 字段类型 【约束】,
。。。
字段名 字段类型 【约束】
)

  • 必须具备:
    – CREATE TABLE权限
    – 存储空间
  • 必须指定:
    – 表名
    – 列名, 数据类型, 尺寸
    在这里插入图片描述

使用子查询创建表

语法
CREATE TABLE table
[(column, column…)]
AS subquery;
• 使用 AS subquery 选项，将创建表和插入数据结合起来。
• 指定的列和子查询中的列要一一对应
• 通过列名和默认值定义列
在这里插入图片描述

常见的数据类型

数据类型 含义
INT 使用4个字节保存整数数据
CHAR(size) 定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size) 可变长字符数据，根据字符串实际长度保存，==必须指定长度 ==
FLOAT(M,D) 单精度，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D) 双精度。D<=M<=255,0<=D<=30，默认M+D<=15
DATE 日期型数据，格式’YYYY-MM-DD’
BLOB 二进制形式的长文本数据，最大可达4G
TEXT 长文本数据，最大可达4G
ENUM enum(‘a’,‘b’,‘c’)数据只能是枚举类型后面之一

一、数值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1个字节 2个字节 3 个字节 4个字节 8个字节
特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号；
②如果超出了范围，会报out or range异常，插入临界值；
③长度可以不指定，默认会有一个长度，长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型。
2、浮点型
定点数：decimal(M,D)
浮点数:
float(M,D) 4个字节
double(M,D) 8个字节
特点：
①M代表整数部位+小数部位的个数，D代表小数部位
②如果超出范围，则报out or range异常，并且插入临界值
③M和D都可以省略，但对于定点数，M默认为10，D默认为0
④如果精度要求较高，则优先考虑使用定点数
二、字符型
char、varchar、binary、varbinary、enum、set、text、blob
char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略
三、日期型
year年
date日期
time时间
datetime 日期+时间 8个字符
timestamp 日期+时间 4个字符 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间

常见的约束

约束名 含义
NOT NULL 非空，该字段的值必填
UNIQUE 唯一，该字段的值不可重复
DEFAULT 默认，该字段的值不用手动插入有默认值
CHECK 检查，mysql不支持
PRIMARY KEY 主键，该字段的值不可重复并且非空 ，相当于unique+not null
FOREIGN KEY 外键，该字段的值引用了另外的表的字段

修改表

1.添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
在这里插入图片描述
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
在这里插入图片描述
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
在这里插入图片描述
4 .删除列
alter table 表名 drop column 列名;
在这里插入图片描述
5.修改表名
alter table 表名 rename 【to】 新表名;
在这里插入图片描述

删除表

drop table【if exists】 表名;
在这里插入图片描述

复制表

1、复制表的结构
create table 表名 like 旧表;
在这里插入图片描述
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧表【where 筛选】;
在这里插入图片描述

三、MySQL之DML语言

DML(Data Manipulation Language –数据操纵语言) 可以在下列条件下执行:
– 向表中插入数据
– 修改现存数据
– 删除现存数据
事务是由完成若干项工作的DML语句组成的。

插入（insert）

方式一

语法：
insert into 表名(字段名,…) values(值,…);
特点：
1、要求值的类型和字段的类型要一致或兼容
2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
3、假如表中有可以为null的字段，注意可以通过以下两种方式插入null值
①字段和值都省略
在这里插入图片描述
②字段写上，值使用null
在这里插入图片描述
4、字段和值的个数必须一致
5、字段名可以省略，默认所有列

方式二

语法：
insert into 表名 set 字段=值,字段=值,…;
在这里插入图片描述
两种方式 的区别：
1.方式一支持一次插入多行，语法如下：
insert into 表名【(字段名,…)】 values(值，…),(值，…),…;
在这里插入图片描述
2.方式一支持子查询，语法如下：
insert into 表名 查询语句;
在这里插入图片描述

修改（update）

一、修改单表的记录

语法：update 表名 set 字段=值,字段=值 【where 筛选条件】;
在这里插入图片描述

二、修改多表的记录

语法：
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;
在这里插入图片描述

删除（delete）

方式一：使用delete

一、删除单表的记录
语法：delete from 表名 【where 筛选条件】【limit 条目数】
在这里插入图片描述
二、级联删除
语法：
delete 别名1,别名2 from 表1 别名
inner|left|right join 表2 别名
on 连接条件
【where 筛选条件】
在这里插入图片描述

方式二：使用truncate

语法：truncate table 表名
两种方式的区别【面试题】
1.truncate删除后，如果再插入，标识列从1开始
delete删除后，如果再插入，标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚

四、MySql之DCL语言

DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

1.创建用户

语法:

CREATE USER ‘用户名’@地址 IDENTIFIED BY ‘密码’;

1
2
CREATE USER ‘user1’@localhost IDENTIFIED BY ‘123’;
CREATE USER ‘user2’@’%’ IDENTIFIED BY ‘123’;

2.给用户授权

语法：

GRANT 权限 1, … , 权限 n ON 数据库.* TO ‘用户名’@地址;

1
2
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO 'user1'@localhost;
GRANT ALL ON mydb1.* TO user2@localhost;

3.撤销授权

语法:

REVOKE 权限 1, … , 权限 n ON 数据库.* FROM ‘用户名’@地址;

1
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM 'user1'@localhost;

4..查看用户权限

SHOW GRANTS FOR ‘用户名’@地址;

1
SHOW GRANTS FOR 'user1'@localhost;

5.删除用户

语法：
DROP USER ‘用户名’@地址;

1
DROP USER ‘user1’@localhost;

6.修改用户密码（以root身份）

语法 ：
use mysql;
alter user ‘用户名‘@localhost identified by ‘新密码’;

五、MySQL之DQL语言

DQL（Data Query Language）：数据查询语言，用来查询记录（数据）

一、MySQL中DQL语言之基础查询

语法

1
2
select 查询列表
from 表名;

特点

1、查询列表可以是字段、常量、表达式、函数，也可以是多个
2、查询结果是一个虚拟表

示例

演示表student结构：
在这里插入图片描述
1、查询单个字段

1
select 字段名 from 表名;

在这里插入图片描述
2、查询多个字段

1
select 字段名，字段名 from 表名;

在这里插入图片描述
3、查询所有字段

1
select * from 表名

在这里插入图片描述
4、查询常量

1
select 常量值;

在这里插入图片描述
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
5、查询函数

1
select 函数名(实参列表);

在这里插入图片描述
如果想了解更多关于常见函数的内容，可以查看：MySQL之常见函数
6、查询表达式

1
select (10+20) sum;

在这里插入图片描述
7、起别名

1
2
①as
②空格

在这里插入图片描述
8、去重

1
select distinct 字段名 from 表名;

在这里插入图片描述
9、+
作用：做加法运算

1
2
3
select 数值+数值; #直接运算
select 字符+数值;#先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;#结果都为null

在这里插入图片描述
10、concat函数

1
2
#功能：拼接字符
select concat(字符1，字符2，字符3,...);

在这里插入图片描述
11、ifnull函数

1
2
#功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值
select ifnull(指定值或表达式,0) from 表;

在这里插入图片描述
12、isnull函数
功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0
在这里插入图片描述

二、MySQL中DQL语言之条件查询

语法

1
2
3
select 查询列表
from 表名
where 筛选条件

在这里插入图片描述

筛选条件的分类

一、按条件表达式筛选

操作符 含义
= 等于 (不是 ==)
> 大于
>= 大于、等于
< 小于
<= 小于、等于
<> 不等于 (也可以是 !=)

img

二、按逻辑表达式筛选

1
2
3
4
5
6
逻辑运算符：
作用：用于连接条件表达式

&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false

在这里插入图片描述

三、模糊查询

1
2
3
4
5
like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符
between and:在两者之间包含边界
in:多个中的一个或多个
is null:不为空

在这里插入图片描述

三、MySQL中DQL语言之排序查询

语法

1
2
3
4
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc}desc】

特点

1、asc ：升序，如果不写默认升序; desc：降序
在这里插入图片描述
2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
在这里插入图片描述
3、order by的位置一般放在查询语句的最后（除limit语句之外）
在这里插入图片描述

四、MySQL之常见函数

概述

功能：类似于java中的方法
好处：提高重用性和隐藏实现细节
调用：select 函数名(实参列表);

字符函数

大小写控制函数
这类函数改变字符的大小写。

函数 结果
LOWER(‘SQL Course’) sql course
UPPER(‘SQL Course’) SQL COURSE

字符控制函数
这类函数控制字符:

函数 结果
CONCAT(‘Hello’, ‘World’) HelloWorld
SUBSTR(‘HelloWorld’,1,5) Hello
LENGTH(‘HelloWorld’) 10
INSTR(‘HelloWorld’, ‘W’) 6
LPAD(salary,10,’*’) *****24000
RPAD(salary, 10, ‘*’) 24000*****
TRIM(‘H’ FROM ‘HelloWorld’) elloWorld
REPLACE(‘abcd’,‘b’,‘m’) amcd

数字函数

函数 结果
ROUND(45.926, 2) 45.93
TRUNCATE(45.926, 2) 45.92
MOD(1600, 300) 100
CEIL(20.1) 21
FLOOR(20.6) 20
RAND() 获取随机数，返回0-1之间的小数

日期函数

函数 结果
now（） 获取当前时间
STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) 1999-09-13
DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) 2018年06月06日
YEAR(date) 返回年
MONTH(NOW()) 返回月
DAY(NOW()) 返回日
CURDATE() 返回当前时期
CURTIME() 返回当前时间
HOUR(NOW()) 返回当前小时24小时制
MINUTE(NOW()) 返回当前分钟
SECOND(NOW()) 返回当前秒
DATEDIFF(‘1970-01-01’,NOW()) 返回两个日期相差的天数
MONTHNAME(NOW()) 以英文形式返回月
格式符 功能
%Y 四位的年份
%y 2位的年份
%m 月份（01,02…11,12）
%c 月份（1,2,…11,12）
%d 日（01,02,…）
%H 小时（24小时制）
%h 小时（12小时制）
%i 分钟（00,01…59）
%s 秒（00,01,…59）

其他函数

函数 功能
version() 当前数据库服务器的版本
database() 当前打开的数据库
user() 当前用户
password(‘字符’) 返回该字符的密码形式
md5(‘字符’) 返回该字符的md5加密形式

流程控制函数

①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
在这里插入图片描述在这里插入图片描述
②case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
…
else 值n
end
在这里插入图片描述
③case情况2
case
when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end
在这里插入图片描述

分组函数

1、分类

max:最大值
在这里插入图片描述
min :最小值
在这里插入图片描述
sum: 和
在这里插入图片描述
avg: 平均值
在这里插入图片描述
count: 计算个数
在这里插入图片描述

2、特点

①语法
select max(字段) from 表名;

②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型

③以上分组函数都忽略null
④都可以搭配distinct使用，实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count(字段)：统计该字段非空值的个数
count():统计结果集的行数
count(1):统计结果集的行数
效率上：
MyISAM存储引擎，count()最高
InnoDB存储引擎，count(*)和count(1)效率>count(字段)
⑥ 和分组函数一同查询的字段，要求是group by后出现的字段

五、MySQL中DQL语言之分组查询

语法

功能：用作统计使用，又称为聚合函数或统计函数或组函数

1
2
3
4
5
6
select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

特点

1
1、sum、avg一般用于处理数值型;max、min、count可以处理任何类型

在这里插入图片描述

1
2、以上分组函数都忽略null值

在这里插入图片描述

1
3、可以和distinct搭配实现去重的运算

在这里插入图片描述

1
4、count函数一般使用count(*)用作统计行数

在这里插入图片描述

1
5、和分组函数一同查询的字段要求是group by后的字段

在这里插入图片描述
对比：

使用关键字 筛选的表 位置
分组前筛选 where 原始表 group by的前面
分组后筛选 having 分组后的结果 group by 的后面

六、MySQL中DQL语言之连接查询

含义

含义：连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。
语法

1
2
select 字段1，字段2
from 表1，表2,...;

笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接。
笛卡尔积结果集=表1行数*表2行数；
例如：表1有10条数据，表2有6条数据

1
select count(*) from 表1，表2

count=10×6=60条数据
在这里插入图片描述
如何解决：添加有效的连接条件
在这里插入图片描述

分类

按年代分类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sql92：
	等值
	非等值
	自连接
	也支持一部分外连接（用于oracle、sqlserver，mysql不支持）

sql99【推荐使用】:
		内连接
			等值
			非等值
			自连接
		外连接
			左外
			右外
			全外（mysql不支持）
		交叉连接

按功能分类：

1
2
3
4
5
6
7
8
9
内连接：
	等值连接
	非等值连接
	自连接
外连接：
	左外连接
	右外连接
	全外连接		
交叉连接

SQL92语法

演示表结构：
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
1、等值连接

语法：

1
2
3
4
5
6
7
select 查询列表
	from 表1 别名,表2 别名
	where 表1.key=表2.key
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

在这里插入图片描述
特点：
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分

2、非等值连接

语法：

1
2
3
4
5
6
7
select 查询列表
	from 表1 别名,表2 别名
	where 非等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

在这里插入图片描述
3、自连接

语法：

1
2
3
4
5
6
7
select 查询列表
	from 表 别名1,表 别名2
	where 等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

演示：查询员工名和上级的名称
在这里插入图片描述

SQL99语法

1、内连接

语法：

1
2
3
4
5
6
7
8
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

特点：
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件

分类：

等值连接
演示：查询员工名、部门名
在这里插入图片描述
非等值连接
演示：查询员工的工资级别
在这里插入图片描述
自连接
演示：查询员工的名字、上级的名字
在这里插入图片描述
2、外连接

1
2
3
4
5
6
7
8
9
语法：
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

演示：查询哪个部门没有员工

左外连接
在这里插入图片描述
右外连接
在这里插入图片描述
特点：
①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
②left join 左边的就是主表，right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行

3、交叉连接

语法：

1
2
3
select 查询列表
from 表1 别名
cross join 表2 别名;

特点：
类似于笛卡尔乘积
在这里插入图片描述

七、MySQL中DQL语言之子查询

含义

嵌套在其他语句内部的select语句称为子查询或内查询，外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多外面如果为select语句，则此语句称为外查询或主查询。

分类

1、按结果集的行列
标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为多行多列
表子查询：结果集为多行多列
2、按出现位置
select后面：仅仅支持标量子查询
from后面：表子查询
where或having后面：标量子查询、列子查询、行子查询
exists后面：标量子查询、列子查询、行子查询、表子查询

示例

演示表结构：
在这里插入图片描述

1、select后面

特点：仅仅支持标量子查询
演示：查询每个部门的员工个数
在这里插入图片描述

2、from后面

特点：将子查询结果充当一张表，要求必须起别名
演示：查询每个部门的平均工资的工资等级
在这里插入图片描述

3、where或having后面

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用 > < >= <= = <>
列子查询，一般搭配着多行操作符使用 in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1、标量子查询

特点：查询结果只有一行一列
演示：查询最低工资的员工姓名和工资
在这里插入图片描述

2、列子查询

特点：查询结果为多行一列
演示：查询所有是领导的员工姓名
在这里插入图片描述

3、行子查询

特点：结果集一行多列或多行多列
演示：查询员工编号最小并且工资最高的员工信息
在这里插入图片描述

4、exists后面

特点：
exists(完整的查询语句)
结果：1或0
演示：查询有员工的部门名
对比in和exists：

使用in查询
在这里插入图片描述
使用exists查询
在这里插入图片描述

八、MySQL中DQL语言之分页查询

应用场景

当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

1
2
3
4
5
6
7
8
9
10
11
12
select 查询列表
	from 表
	left|right| outer|inner join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;

offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数

特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size

1
2
3
select 查询列表
from 表
limit (page-1)*size,size;

演示表结构：
在这里插入图片描述
演示：查询前五条员工信息
在这里插入图片描述
演示：查询第11条——第25条
在这里插入图片描述

九、MySQL中DQL语言之联合查询

含义

union：合并、联合，将多次查询结果合并成一个结果

语法

1
2
3
4
5
查询语句1
union 【all】
查询语句2
union 【all】
...

意义

1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候，查询的列基本是一致

特点

1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重，union all包含重复项
演示表结构
在这里插入图片描述
演示: 查询部门编号>90或邮箱包含a的员工信息

常规查询：
在这里插入图片描述
联合查询：
在这里插入图片描述

总结

DQL查询语言可以总结如下，编号代表执行顺序：

1
2
3
4
5
6
7
8
9
10
语法：
select 查询列表     ⑦
from 表1 别名       ①
连接类型 join 表2   ②
on 连接条件         ③
where 筛选          ④
group by 分组列表   ⑤
having 筛选         ⑥
order by排序列表    ⑧
limit 起始条目索引，条目数;  ⑨

六、MySQL之事务

一、事务的概念

事务(Transaction Controlo Language)：事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单
元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。
MySQL 中的存储引擎
1、概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。
2、通过show engines；来查看mysql支持的存储引擎。
3、在mysql中用的最多的存储引擎有：innodb，myisam ,memory 等。其中innodb支持事务，而myisam、memory等不支持事务
在这里插入图片描述

二、事务的特点

事务的ACID(acid)属性

  1. 原子性（Atomicity）
    原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  2. 一致性（Consistency）
    事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
  3. 隔离性（Isolation）
    事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  4. 持久性（Durability）
    持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响事务的使用。

三、事务的使用

1.以第一个 DML 语句的执行作为开始， 以下面的其中之一作为结束:
①COMMIT 或 ROLLBACK 语句
②DDL 或 DCL 语句（自动提交）
③用户会话正常结束
④系统异常终了

2.隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete。

3.显式事务：具有明显的开启和结束。
使用显式事务：
①开启事务

1
2
3
set autocommit=0;
start transaction;#可以省略
12

②编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete
设置回滚点：
savepoint 回滚点名;

③结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方：rollback to 回滚点名;

演示事务的使用步骤：
在这里插入图片描述

四、并发事务

1、事务的并发问题是如何发生的？
多个事务同时操作同一个数据库的相同数据时
2、并发问题都有哪些？
脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据。
不可重复读：一个事务多次读取，结果不一样。
虚读/幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据。
3、如何解决并发问题？
通过设置隔离级别来解决并发问题。
4、隔离级别

脏读 不可重复读 幻读
read uncommitted:读未提交 × × ×
read committed：读已提交 √ × ×
repeatable read：可重复读 √ √ ×
serializable：串行化 √ √ √

①.Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
②.Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
③.在 MySql 中设置隔离级别
每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别.
查看当前的隔离级别:

1
2
SELECT @@tx_isolation;
1

在这里插入图片描述
设置当前 mySQL 连接的隔离级别:

1
2
set session transaction isolation level read committed;
1

设置数据库系统的全局的隔离级别:

1
2
set global transaction isolation level read committed;
1

五、演示脏读/不可重复读/幻读

脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据。
在这里插入图片描述
说明：左边窗口第一次读取结果小王为1000，右边窗口开启事务对小王money修改此时事务并没有提交，左边窗口再次读，发现小王money变了，所以read uncommitted 不能避免脏读、不可重复读。
不可重复读： 一个事务多次读取，结果不一样
在这里插入图片描述
说明：左边窗口为窗口1，右边为窗口2。窗口1读取表account一次，窗口2开启事务，更新小王的money不提交，此时窗口1再次读取表account，发现前后两次读取结果一样，说明read commited能避免脏读，提交窗口2的事务，窗口1第三次读取表account，发现此时读取的小王数据与之前两次不同，说明read commited不能避免不可重复读。
幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据。
在这里插入图片描述
说明：窗口1开启事务查看表account有两条记录，编写update语句不执行，
窗口2开启事务，执行insert语句提交，此时再执行update语句发现受影响行变成了3行，说明幻读已经产生了。

serializable :能避免脏读，不可重复读，虚读/幻读
在这里插入图片描述
说明：窗口1编写update语句不执行，窗口2执行insert语句，此时再去执行窗口1的update语句，发现一直等待，只有等窗口2提交后窗口1才能继续操作。类似于java多线程中的锁。

七、MySQL之视图

什么是视图

视图：MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。
应用场景：
– 多个地方用到同样的查询结果
– 该查询结果使用的sql语句较复杂
视图的好处
• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

创建视图

语法

1
2
3
4
create view 视图名
as
查询语句;
123

在这里插入图片描述

修改视图

方式一：

1
2
3
4
create or replace view 视图名
as
查询语句;
123

在这里插入图片描述
方式二：

1
2
3
4
alter view 视图名
as
查询语句;
123

在这里插入图片描述

删除视图

语法

1
2
drop view 视图1，视图2,...;
1

在这里插入图片描述
用户可以一次删除一个或者多个视图，前提是必须有该视
图的drop权限。

查看视图

语法

1
2
show tables;
1

在这里插入图片描述
如果需要查询某个视图的定义，可以使用:

1
2
show create view view_name \G
1

在这里插入图片描述

视图的使用

视图的增加修改删除和表一样，详见：
MySQL之DML语言

注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新。
• 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

视图和表的对比

关键字 是否占用物理空间 使用
视图 view 占用较小，只保存sql逻辑 一般用于查询
表 table 保存实际的数据 增删改查

八、MySQL之变量

一、系统变量

说明：变量由系统提供的，不用自定义
语法：
①查看系统变量

1
2
3
show 【global|session 】variables like ''; 
#如果没有显式声明global还是session，则默认是session
12

在这里插入图片描述
②查看指定的系统变量的值

1
2
3
select @@【global|session】.变量名; 
#如果没有显式声明global还是session，则默认是session
12

在这里插入图片描述
③为系统变量赋值
方式一：

1
2
3
set 【global|session 】 变量名=值; 
#如果没有显式声明global还是session，则默认是session
12

在这里插入图片描述
方式二：

1
2
3
set @@global.变量名=值;
set @@变量名=值；
12

在这里插入图片描述

1、全局变量

全局变量(global):服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效。

2、会话变量

会话变量(session):服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）。

二、自定义变量

1、用户变量

作用域：针对于当前连接（会话）生效位置：begin end里面，也可以放在外面。
使用：
①声明并赋值：

1
2
3
4
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;
123

在这里插入图片描述
②更新值
方式一：

1
2
3
4
set @变量名=值;或
set @变量名:=值;或
select @变量名:=值;
123

方式二：

1
2
select xx into @变量名 from 表;
1

在这里插入图片描述
③使用

1
2
select @变量名;
1

在这里插入图片描述

2、局部变量

作用域：仅仅在定义它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句
使用：局部变量的使用再后面关于”存储变量和函数”文章中会介绍，此处仅做举例。
①声明

1
2
declare 变量名 类型 【default 值】;
1

在这里插入图片描述
②赋值或更新
方式一：
set 变量名=值;或
set 变量名:=值;或
select @变量名:=值;
方式二：
select xx into 变量名 from 表;
③使用
select 变量名;

用户变量和局部变量的对比

作用域 定义位置 语法
用户变量 当前会话 会话的任何地方 加@符号，不用指定类型
局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型

九、MySQL之存储过程

什么是存储过程

存储过程：
事先经过编译并存储在数据库中的一段sql语句的集合，类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字。
使用好处：
1、简化应用开发人员的很多工作
2、减少数据在数据库和应用服务器之间的传输
3、提高了数据处理的效率

存储过程

一、创建

语法

1
2
3
4
5
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
		存储过程体
end
1234

注意：
1.参数模式：in、out、inout，其中in可以省略
2.存储过程体的每一条sql语句都需要用分号结尾

二、调用

语法

1
2
call 存储过程名(实参列表)
1

举例：
调用in模式的参数：

1
2
call sp1（‘值’）;
1

在这里插入图片描述
调用out模式的参数：

1
2
3
4
set @name;
call sp1(@name);
select @name;
123

在这里插入图片描述
在这里插入图片描述
调用inout模式的参数：

1
2
3
4
set @name=值;
call sp1(@name);
select @name;
123

在这里插入图片描述

三、查看

语法

1
2
show create procedure 存储过程名;
1

在这里插入图片描述
四、删除
语法

1
2
drop procedure 存储过程名;
1

在这里插入图片描述

十、MySQL之函数

什么是函数

函数：
事先经过编译并存储在数据库中的一段sql语句的集合，类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字。
使用好处：
1、提高重用性
2、sql语句简单
3、减少了和数据库服务器连接的次数，提高了效率

函数

一、创建

语法

1
2
3
4
5
create function 函数名(参数名 参数类型) returns  返回类型
begin
	函数体
end
1234

在这里插入图片描述
注意：函数体中肯定需要有return语句

二、调用

语法

1
2
select 函数名(实参列表);
1

在这里插入图片描述

三、查看

语法

1
2
show create function 函数名;
1

在这里插入图片描述

四、删除

语法

1
2
drop function 函数名；
1

在这里插入图片描述

十一、MySQL之流程控制结构

顺序结构：程序从上往下依次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句

顺序结构

我们平时所作的增删改查等大部分都属于顺序结构，再此不作说明！

分支结构

建表及插入数据：
在这里插入图片描述
特点：

1、if函数

功能：实现简单双分支
语法：

1
2
if(条件，值1，值2)
1

位置：可以作为表达式放在任何位置
在这里插入图片描述
下面的实例将涉及到函数，如果你对MySQL中的函数还不是很了解可以查看：MySQL之函数

2、case结构

功能：实现多分支
语法1：

1
2
3
4
5
6
7
case 表达式或字段
when 值1 then 语句1;
when 值2 then 语句2；
..
else 语句n;
end [case];
123456

语法2：

1
2
3
4
5
6
7
case 
when 条件1 then 语句1;
when 条件2 then 语句2；
..
else 语句n;
end [case];
123456

位置：可以放在任何位置。

如果放在begin end 外面，作为表达式结合着其他语句使用

情况1：
在这里插入图片描述
情况2：
在这里插入图片描述
如果放在begin end 里面，一般作为独立的语句使用

情况1：
在这里插入图片描述
情况2：
在这里插入图片描述

3、if结构

功能：实现多分支
语法：

1
2
3
4
5
6
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;
12345

位置：只能放在begin end中

在这里插入图片描述

循环结构

位置：只能放在begin end中

特点：都能实现循环结构

1、while

语法：

1
2
3
4
【名称:】while 循环条件 do
		循环体
end while 【名称】;
123

在这里插入图片描述

2、loop

语法：

1
2
3
4
【名称：】loop
		循环体
end loop 【名称】;
123

3、repeat

语法：

1
2
3
4
5
【名称:】repeat
		循环体
until 结束条件 
end repeat 【名称】;
1234

在这里插入图片描述
对比：

①这三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave或iterate）则必须添加名称
②loop ：一般用于实现简单的死循环
while ：先判断后执行
repeat ：先执行后判断，无条件至少执行一次

4、循环控制语句

leave：类似于break，用于跳出所在的循环
在这里插入图片描述
iterate：类似于continue，用于结束本次循环，继续下一次
在这里插入图片描述

十二、MySQL索引详解

一、什么是索引？为什么要建立索引？

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

二、MySQL中索引的优缺点及使用原则

优点：

1、所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引

2、大大加快数据的查询速度

缺点：

1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2、索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

使用原则：

通过上面说的优点和缺点，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。

1、对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，

2、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

3、在一同值少的列上(字段上)不要建立索引，比如在学生表的”性别”字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引。

三、Explain执行器名词解释

1、作用

  • 复杂sql语句的读取顺序
  • sql中有哪些索引可以使用
  • sql中哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

2、使用方法

  • EXPLAIN + 查询sql
  • 如：EXPLAIN SELECT * FROM test_index;

3、基本字段解读
在这里插入图片描述
4、Explain字段详解
id

1
2
3
4
5
select查询的序列号，并不是单纯的从上到下或者从下向上执行，共有三种情况
 - id相同：按照从上到下的顺序执行
 - id不同：按照id值由大到小的顺序执行
 - id既有相同又有不同：先执行id值大的，然后相同值的从上到下执行
1234

select_type

1
2
3
4
5
6
7
8
9
分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
值如下：
 - SIMPLE：简单查询，不包含子查询和UNION查询
 - PRIMARY：主查询，如包含子查询的sql中的父查询
 - SUBQUERY：查询sql中的子查询
 - DERIVED：衍生查询（在from列表中的子查询，Mysql递归这些子查询，结果放到临时表中）
 - UNION：联合查询，t1 UNION t2 中的t2
 - UNION RESULT：从UNION表获取结果的SELECT分别用来表示查询的类型
12345678

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
table

1
2
一般指查询的表，对于带尖括号的，表示select_type + id的指向。
1

partitions

1
2
如果查询是基于分区表的话，会显示查询将访问的分区。
1

type

1
2
3
4
5
6
7
8
9
10
11
type所显示的是查询使用了哪种类型
查询速度：null > syetem > const > eq_ref > ref > range > index > all
 - null：甚至不需要访问索引表，例如主键作为条件超过当前表主键最大值;
 - system：const的特殊情况，只有一条数据的系统表
 - const：使用唯一索引等价查询，仅能匹配到一条数据
 - eq_ref：使用唯一索引作为关联条件，匹配多条不重复数据
 - ref：普通索引等价
 - range：检索给定范围的索引 ， > 、< 、>= 、<=、between and
 - index：仅查询索引表
 - all：遍历全表以找到匹配的行
12345678910

possible_keys

1
2
3
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，
但不一定被查询实际使用
12

key

1
2
查询中实际使用到的索引，小于等于possible_keys
1

key_len

1
2
3
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
12

在这里插入图片描述

ref

1
2
表示查询中的连接匹配条件，即哪些列或常量被用于查找索引列上的值
1

rows

1
2
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好 
1

filtered

1
2
按表条件过滤的行百分比
1

Extra

1
2
3
4
5
6
7
8
9
10
查询结果的备注信息，很重要
Using index：性能提升，索引覆盖，此查询仅查询索引不需要回表查询
Using where：该查询中使用了where条件过滤
Using index, Using where：双条件，表明索引被用来执行索引键值的查找
Using filesort：性能消耗大，需要额外一次排序（查询）
Using temporary：性能消耗大，用到临时表，常见于order by和group by
Using join buffer	连接缓存
impossible where：where子句的值总是false，不能用来获取任何元组
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
123456789

四、索引的分类

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

存储引擎种类：
在这里插入图片描述
查看当前版本存储引擎信息：
在这里插入图片描述
两种在实际开发中使用最多的两种引擎【MyISAM】和【InnoDB】。
MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换。
MEMORY/HEAP存储引擎：支持HASH和BTREE索引。
MySQL目前主要有以下几种索引类型：

1、 普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

（1）直接创建索引

1
2
CREATE INDEX index_name ON table(column(length)) 
1

（2）修改表结构的方式添加索引

1
2
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
1

（3）创建表的时候同时创建索引

1
2
3
4
5
6
7
8
9
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)
12345678

2、 唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

（1）创建唯一索引

1
2
CREATE UNIQUE INDEX indexName ON table(column(length))
1

（2）修改表结构

1
2
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
1

（3）创建表的时候直接指定

1
2
3
4
5
6
7
8
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);
1234567

3、 主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

1
2
3
4
5
6
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);
12345

4、 组合索引

组合索引就是在多个字段上创建一个索引，如：

1
2
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 
1

在这里插入图片描述
在这里插入图片描述
最左前缀原则：只有当查询条件为name,name and city,name and city and age三种情况下才会走索引name_city_age。在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

5、全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
在这里插入图片描述