SQL分类

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用select子句，from子句和where子句的组合来查询一条或多条数据

数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据 增加、修改和删除的操作，其主要包括：
INSERT：增加数据 updeta：修改数据 delect：删除数据

数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）进行创建、修改和删除操作。其主要包括：
CREATE：创建 alter：修改 drop：删除

数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数 据库的权限，其主要包括：
GRANT：授予用户某种权限 REVOKE：回收授予的某种权限

事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：
START TRANSACTION：开启事务 SET TRANSACTION：设置事务的属性
COMMIT：提交事务 ROLLBACK：回滚事务

DDL操作

查看所有的数据库

show databases;

创建数据库

create database aa;

删除数据库

drop database aa;

浮点数类型

注意事项：
浮点数类型的宽度不会自动扩充，使用时需要指定长度
如：score double(4,1) 总体长度是4 精度为1位

字符串类型

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变
因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

注意事项：
CHAR和VARCHAR表示的是字符的个数，而不是字节的个数

日期和时间类型

IMESTEMP类型的数据指定方式与DATETIME基本相同，两者不同之处在于以下几点：

1. 数据的取值范围不同，TIMESTEMP类型的取值范围更小
2. 如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值， MySQL会自动将该字段赋值为系统当前的日期与时间

DDL创建数据库表

create table 表名(
字段一  数据类型,
字段二  数据类型(长度)   
);
# 注意: 最后一列不要加 , 号 
#      需要长度的类型必需添加  不需要忽略

DDL查看数据库表

-- 显示当前数据库的所有数据库表
show tables;
-- 显示当前数据表的结构  desc 表名
desc category;
-- 查看完整的建表语句  show create table 表名
show create table category;

DDL删除数据库表

-- 删除当前数据库中的表  drop table 表名;
drop table category;

DDL修改数据库表

修改表名

rename table 旧表名 to 新表名

向表中添加字段 关键字 add

alert table 表名 add 字段名称 字段类型

修改表中字段 关键字 modify | change

alert table 表名 modify 字段名称 字段类型;  

Change: 修改字段

alter table 表名 change 旧列名 新列名 类型(长度);  

删除表中字段 关键字 drop

alter table 表名 drop 列名;

DML操作表中数据

SQL中的DML 用于对表中的数据进行增删改操作

插入数据

-- 插入部分字段或全部字段
insert into 表名 （字段名1，字段名2...） values(字段值1，字段值2...);

-- 插入全部的字段
insert into 表名 values(字段值1，字段值2...);

注意事项： 
1) 值与字段必须要对应，个数相同&数据类型相同 
2) 值的数据大小，必须在字段指定的长度范围内
3) varchar char date类型的值必须使用单引号，或者双引号包裹。 
4) 如果要插入空值，可以忽略不写，或者插入null 
5) 如果插入指定字段的值，必须要上写列名
6) 字符串日期类型支持  "1256-12-23"   "1256/12/23"  "1256.12.23"

更改数据

update 表名 set 字段名=值 where 条件(如: 字段名=值)

update 表名 set 字段名=值 -- 慎重: 不加条件更改所有

需求: 将sid为1的学生 ssex改为女
SQL实现
update stu set ssex='女' where sid = 1;

删除数据

delete from 表名 where 条件(如: 字段名=值)

delete from 表名     -- 慎重: 删除表中所有的数据

truncate table 表名;  -- 慎重: 删除表中所有的数据

需求1: 删除sid为5的学生信息
SQL实现
delete from stu where sid = 5;

需求2: 将 stu表数据全部删除
SQL实现
delete from stu;
truncate table stu;  -- 推荐使用

从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：
(1)DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL。 
(2) DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作	则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，	TRUNCATE操作的效率更高。 
(3)DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚（后	面会讲解事务的提交和回滚）。 
(4)DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“Affected rows：4”）；截断操作不会返回已删除的行量，结果通常是“Affected 	rows：0”。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设	置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；	TRUNCATE操作会重新从1开始自增。

表的约束

约束的作用
对表中数据的一种限制约束, 从而保证数据的正确性, 有效性, 和完整性. 违反约束的不正确数据是无法插入到表中的

主键约束

添加主键约束
语法格式
字段名 字段类型 primary key

需求1: 创建一个带有主键的emp表 字段 eid int  ename varchar(10)  esex char(1)
SQl实现: 方式一
create table emp(
	eid int primary key,  -- 设置主键 非空唯一
	ename varchar(10),
	esex char(1)
);
-- 查看表结构
desc emp;

测试主键的唯一 非空约束
-- 插入正常数据
insert into emp values(1,"宋江","男");

-- 插入一条数据, 主键为空
insert into emp values(null,"李逵","男");
-- Column 'eid' cannot be NULL 主键不能为空 

-- 插入一条数据, 主键重复
insert into emp values(1,"孙二娘","女");
-- Duplicate entry '1' for key 'emp.PRIMARY' 主键冲突

删除主键约束

-- 使用DDL 删除表中的主键约束
alter table emp drop primary key;

-- 查看表结构
desc emp;

-- 删除唯一约束(了解)
-- 添加了唯一约束为 eid int not null, 通过设置字段属性删除唯一约束 
alter table emp modify eid int;

主键自增

自己添加数据可能出现重复, 我们通常希望在每次插入新记录时, 数据库自动生成主键字段的值
语法格式
关键字
auto_increment  
表示自增长(字段类型必须为整数类型)

需求: 为emp表eid字段添加主键约束, 并设置为自增
1) 创建主键自增的emp表
drop table emp;

create table emp(
	eid int primary key auto_increment,
	ename varchar(10),
	esex char(1)
);

非空约束

语法格式
字段名 字段类型 not null

需求: emp表 eid 主键约束,自增  ename 非空约束
SQL实现
-- 删除存在的emp表 
drop table emp;

create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	esex char(1)
);
-- 查看emp表结构 
desc emp;

唯一约束

语法格式
字段名 字段类型 unique

需求: emp表 eid 主键约束,自增  ename 非空约束 esex唯一约束
SQL实现
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	esex char(1) unique
);
-- 测试唯一约束 添加两个性别为男
insert into emp values(default,'zs','男');

默认值约束

语法格式
字段名 字段类型 default '值'

需求: emp表 eid 主键约束,自增  ename 非空约束 esex默认值'男'
SQL实现
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	esex char(1) default '男'
);
-- 查看emp表结构 
desc emp;

检查约束

语法格式
字段名 字段类型 check(字段='值' or 字段='值')
字段名 字段类型 check(字段>n or 字段<m)
字段名 字段类型 check(字段>n and 字段<m)

需求: emp表 eid 主键约束,自增  ename 非空约束 esex默认值'男'并检查 男|女
SQL实现
-- 删除存在的emp表 
drop table emp;
create table emp(
	eid int primary key auto_increment,
	ename varchar(10) not null,
	esex char(1) default '男' check(esex='男' or esex='女')
);
-- 查看emp表结构 
desc emp;

DQL查询表中数据

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，
其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据
DQL是实际开发过程中使用最多的

简单查询

查询不会对数据库中的数据进行修改, 只是一种显示数据的方式
语法格式
select 字段名 from 表名 

需求1: 查询emp中所有的数据
-- 将要查询的字段全部列出
select eid ,ename, sex, salary, hire_date, dept_name from emp;

--查询所有的字段可以使用 * , * 代表所有的字段
select * from emp;

需求2: 查询emp表中所有的记录, 只显示eid和ename字段

select eid, ename from emp;

需求3: 查询所有的员工信息, 将字段名显示为中文
select 
	eid as '编号',      -- as可以省略
	ename as '姓名',
	sex as '性别',
	salary as '薪资',
	hire_date as '入职日期',
	dept_name as '部门名称'
from emp;

需求4: 查询有多少个部门
-- 查询所有的部门(出现重复部门)
select dept_name from emp;
-- 使用去重查询 关键字 distinct
select distinct dept_name from emp;

将所有的员工薪资加1000显示

-- 支持算术运算符 + - * / % 的运算
select eid, ename, salary, salary+1000 from emp;

条件查询

·如果查询语句中没有设置条件, 就会查询所有的行信息
·在实际应用中, 一定要指定查询的条件, 对记录进行过滤

语法格式
select 列名 from 表名 where 条件

需求1: 精确查询
#1 查询员工姓名为黄蓉的员工信息 
#2 查询薪水价格为5000的员工信息 
#3 查询薪水价格不是5000的所有员工信息 
#4 查询薪水价格大于6000元的所有员工信息 
#5 查询薪水价格在5000到10000之间所有员工信息 
#6 查询薪水价格是3600或7200或者20000的所有员工信息
SQL实现
#1 查询员工姓名为黄蓉的员工信息 
select * from emp where ename = '黄蓉';

#2 查询薪水价格为5000的员工信息
select * from emp where salary = 5000;

#3 查询薪水价格不是5000的所有员工信息 
select * from emp where salary != 5000; 
select * from emp where salary <> 5000; 

#4 查询薪水价格大于6000元的所有员工信息 
select * from emp where salary > 6000; 
#5 查询薪水价格在5000到10000之间所有员工信息 
select * from emp where salary >= 5000 and salary <= 10000; 
select * from emp where salary between 5000 and 10000; 

#6 查询薪水价格是3600或7200或者20000的所有员工信息
select * from emp where salary = 3600 or salary = 7200 or salary = 20000;
select * from emp where salary in (3600, 7200, 20000);

需求2: 模糊查询
#1 查询含有'八'字的所有员工信息 
#2 查询以'孙'字开头的所有员工信息 
#3 查询第二个字为'兔'的所有员工信息 
#4 查询没有部门的员工信息 
#5 查询有部门的员工信息

SQL实现

#1 查询含有'八'字的所有员工信息
select * from emp where ename like '%八%';
 
#2 查询以'孙'字开头的所有员工信息 
select * from emp where ename like '孙%';

#3 查询第二个字为'兔'的所有员工信息 
select * from emp where ename like '_兔%';

#4 查询没有部门的员工信息 
select * from emp where dept_name is null;
#5 查询有部门的员工信息
select * from emp where dept_name is not null;

排序

通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影	响真实的数据

select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc  升序(默认)
-- desc 降序
1)单列排序
· 只按照某一个字段进行排序

需求1: 查询所有的员工信息, 使用saraly进行排序

SQL实现

-- 升序排序(默认 asc)
select * from emp order by salary;

-- 降序排序(desc)
select * from emp order by salary desc;

组合排序
·同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推
需求2: 查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序
SQL实现
-- 组合排序
select * from emp order by salary, hire_date desc;

函数

MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。
函数只是对查询结果中的数据进行处理，不会改变数据库中数据表的值。MySQL中的函数主要分为单行函数和多行函数两大类
单行函数 
单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数
使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据
多行函数
我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值
·多行函数会忽略null空值
·多行函数也称为分组函数, 聚合函数

单行函数

-- 字符串函数的操作
#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
#2 查询emp表所有数据, 将ename第二个字符都换为 某
#3 查询emp表所有数据, 显示ename的长度
#4 查询emp表所有数据, 将 ename有英文的改为都是大写
#5 查询emp表所有数据, 将 ename有英文的改为都是小写
#6 查询emp表所有数据, ename只显示姓
SQL实现
#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat('编号:', eid), concat('姓名:', ename), concat('性别:', sex) from emp;

#2 查询emp表所有数据, 将ename第二个字符都换为 某
select eid, insert(ename, 2, 1, 某'), sex from emp;

#3 查询emp表所有数据, 显示ename的长度
select eid, ename, length(ename), sex from emp;

#4 查询emp表所有数据, 将 ename有英文的改为都是大写
select eid, ename, upper(ename), sex from emp;

#5 查询emp表所有数据, 将 ename有英文的改为都是小写
select eid, ename, lower(ename), sex from emp;

#6 查询emp表所有数据, ename只显示姓
select eid, ename, substring(ename, 1, 1), sex from emp;

MySQL提供的一张虚拟表中进行演示，该表名为“dual”，是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。 在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表

-- 数值函数的操作
select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25) from dual;
select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25);

-- 日期与时间函数的操作
select curdate(), curtime(), now(), sysdate();  
select curdate(), curtime(), now(), sleep(2), sysdate();  

-- 流程函数的操作
#1 查询emp表所有数据, 薪资 >= 10000 高工资  <10000 低工资
#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
#3 查询emp表所有数据, 薪资 >= 15000 优秀   >=9000 坚持住   >=5000 加油哦 
>= 3000 加把劲   其他 努力奋斗吧骚年 

#1 查询emp表所有数据, 薪资 >= 10000 高工资  其他 低工资
select eid, ename, salary, if(salary >= 10000, '高工资', '低工资') from emp;

#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, salary, salary * 12+30000 '年薪' from emp;  -- 需要考虑null
select eid, ename, salary, ifnull(salary, 0) * 12+30000 '年薪' from emp;

#3 查询emp表所有数据, 薪资 >=3000 加把劲  >=5000 加油哦  >=9000 坚持住 >= 15000 优秀  其他 不及格
select eid, ename, salary, 
	case
		when salary >= 15000 then '优秀'
		when salary >= 9000 then '坚持住'
		when salary >= 5000 then '加油哦'
		when salary >= 3000 then '加把劲'
		else '努力奋斗吧骚年'
	end
from emp;

多行函数

多行函数会忽略null空值
·多行函数也称为分组函数, 聚合函数

需求: 
#1 查询员工的总数 
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ... 
#3 查询薪水大于4000员工的个数 
#4 查询部门为'教学部'的所有员工的个数 
#5 查询部门为'市场部'所有员工的平均薪水
#6 查询部门的个数

SQL实现
#1 查询员工的总数 
-- 使用某个字段查询，聚合函数会忽略null, 需要注意为null的字段
select count(eid) from emp;  
-- 所有字段匹配查询
select count(*) form emp; 
-- 增加一列
select 1 from emp;	
-- 效率更高推荐使用		  
select count(1) from emp; 
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ... 
select 
	sum(salary) '总薪水',
	max(salary) '最高薪水',
	min(salary) '最小薪水',
	avg(salary) '平均薪水'
from emp;

#3 查询薪水大于4000员工的个数 
select count(1) from emp where salary > 4000;

#4 查询部门为'教学部'的所有员工的个数 
select count(1) from emp where dept_name = '教学部';

#5 查询部门为'市场部'所有员工的平均薪水
select avg(salary) from emp where dept_name = '市场部';

#6 查询部门的个数
select dept_name from emp;  -- 9个
select count(dept_name) from emp; -- 8个
-- 部门去重之后， 统计个数
select count(distinct dept_name) from emp; -- 3个

分组

·分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组

语法格式
select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件]

需求1: 通过性别分组
SQL实现
# 按照性别分组查询
select * from emp group by sex;    -- 能查到结果, 但是没有意义
select sex from emp group by sex;  -- 正确操作

需求1: 

#1 查询每个部门的名称
#2 查询每个部门的平均薪资 
#3 查询每个部门的平均薪资, 部门名称不能为空
SQL实现

#1 查询每个部门的名称
select dept_name from emp group by dept_name;

#2 查询每个部门的平均薪资 
select dept_name, avg(salary) from emp group by dept_name;
 
#3 查询每个部门的平均薪资, 部门名称不能为空
select dept_name from emp where dept_name is not null group by dept_name;


需求2:

#4 查询每个部门的平均薪资, 只显示平均工资在4000以上的 

SQL实现

select dept_name, avg(salary) from emp where avg(salary) > 4000 group by dept_name;
-- Invalid use of group function  报错

 需要在分组后, 对数据进行过滤, where的作用是在分组前过滤

2) select语句的执行顺序
from -- where -- group by -- having –- select -- order by

3)分组操作中的having子语句, 适用于对分组后的数据进行过滤的, 作用类似于	where

#4 查询每个部门的平均薪资, 只显示平均工资在4000以上的 
select dept_name, avg(salary) from emp group by dept_name having avg(salary) > 4000;

where 和 having的区别

group by 和 having练习
#1 统计每个部门中的最小工资, 列出最小工资小于2000的部门名称
#2 统计平均工资大于3000的部门名称
#3 统计人数小于4的部门的平均工资
#4 统计每个部门最高工资, 排除最高工资小于3000的部门

#1 统计每个部门中的最小工资, 列出最小工资小于4000的部门名称
-- 每个部门的最小工资
select dept_name, min(salary)  from emp group by dept_name; 
select dept_name, min(salary)  from emp group by dept_name having min(salary) < 4000;

#2 统计平均工资大于6000的部门名称 
-- 每个部门的平均工资
select dept_name, max(salary)  from emp group by dept_name;
select dept_name, max(salary)  from emp group by dept_name having max(salary) > 6000;

#3 统计人数小于4个人部门的平均工资
-- 每个部门的平均工资和人数
select dept_name, avg(salary), count(1) from emp group by dept_name;  
select dept_name, avg(salary), count(1) from emp group by dept_name having count(1) < 4;

#4 统计每个部门最高工资, 排除最高工资小于10000的部门
-- 每个部门的最高工资
select dept_name, max(salary) from emp group by dept_name
select dept_name, max(salary) from emp group by dept_name having max(salary) >= 10000;

limit关键字

作用:
·limit是限制的意思, 限制返回的查询结果的函数(通过limit之sing查询多少行数据)
·limit 语法是 MySql的方言, 用来完成分页
语法结构
select 字段1, 字段2 ... from 表名 limit offset, length;

参数说明
1) offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据
2) length 返回的行数

需求1: 

#1 查询emp表中的前5条数据
#2 查询emp表中 从第4条开始, 查询6条

SQL实现

#1 查询emp表中的前5条数据
select * from emp limit 5;   -- 不指定从哪行还是, 默认从0开始
select * from emp limit 0, 5;

#2 查询emp表中 从第4条开始, 查询6条
select * from emp limit 3, 6; -- 从0开始, 所以第四条数据为3

多表

多表的概述
实际开发中, 一个项目通常需要很多张表才能完成
例如一个商城项目的数据库, 需要很多张表: 如 用户表, 分类表, 商品表. 订单表.

创建外键约束

语法格式:
1. 新建表时添加外键约束
constraint 外键约束名称 foreign key(外键字段名) references 主表名(主键字段); 
2. 为已创建好的表添加外键约束
alter table 从表名 add constraint 外键约束名称 foreign key(外键字段名)
references 主表名(主键字段名);

需求: 为employee表的 dep_id字段添加外键约束
1. 为已经创建好的employee表添加外键约束
alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(dep_id);

2. 新创建employee表时, 为dep_id添加外键约束
create table employee(
	eid int primary key auto_increment,
	ename varchar(10),
	age int,
	dep_id int, 
	-- 添加外键约束
	constraint emp_dep_fk foreign key(dep_id) references department(dep_id)
);

删除外键约束

语法格式:
alter table 从表名 drop foreign key 外键约束的名称

需求: 删除employee表中的外键约束
alter table employee drop foreign key emp_dep_fk;

外键约束的注意事项

从表的外键类型必须和主表的主键类型保持一致

2) 添加从表数据时
从表中添加的外键值, 必须在主表的主键中存在
3)删除和变更数据主表数据时
先删除从表中的数据或将外键设置为null, 再删除主表中的数据

-- 删除部门表中主键为1的部门信息
delete from department where dep_id = 1;
-- 报错信息如下
-- Cannot delete or update a parent row: a foreign key constraint fails

多表关系设计

实际的开发过程中, 一个项目通常需要很多张表才能完成. 例如: 一个商城项目就需要分类表(category), 商品表(products), 订单表(orders)等多张表. 而且这些表的数据之间存在一定的关系, 接下来我们一起学习以下多表设计方面的知识

一对多关系(常见)
·一对多关系(1 : n)
例如: 班级和学生, 部门和员工, 客户和订单, 类别和商品
·一对多建表原则
在从表(多方)创建一个字段, 该字段作为外键指向主表的主键

多对多关系

多对多关系(m : n)
例如: 老师和学生, 学生和课程, 用户和角色
·多对多关系建表原则
多对多的关系不能直接处理, 需要创建第三张表, 也称为中间表, 中间表至少两个字段,这两个字段分别作为外键指向各自一方的主键, 实际就是将多对多拆分为两个一对多

多表查询

使用语法

select 字段名 from 表1 cross join 表2; 

隐式内连接
from子句后面直接写多个表名 使用where指定连接条件的 这种连接方式是隐式内连接. 使用where条件过滤无用的数据

语法格式

select 字段名 from 表1, 表2 where 连接条件;

显示内连接
使用 inner join …on 这种方式, 就是显式内连接
语法格式

select 字段名 from 表1 [inner] join 右表 on 条件

外连接查询

使用 left outer join , outer 可以省略
语法格式

select 字段名 from 表1 left join 表2 on 条件;

左外连接的特点:
1) 以左表为主, 左表中的数据全部显示
2) 右表匹配到数据就显示匹配到的数据
3) 右表没有匹配的数据显示为null

使用左外连接查询每个分类下的商品名称, 商品个数

# 思路分析
-- 1.查询哪几张表 category products
-- 2.表之间的连接条件 主表主键 = 从表外键
-- 3.查询条件 每个类别  需要分组
-- 4.查询的字段 类别名称 商品数量
select c.cname, count(p.cid)  from category c left join products p on c.cid = p.cid
group by c.cname;

右外连接

使用 right outer join, outer可以省略
语法格式

select 字段名 from 表1 right join 表2 on 条件;

右外连接的特点:
1) 以右表为主, 右表中的数据全部显示
2) 左表匹配到数据就显示匹配到的数据
3) 左表没有匹配的数据显示为null

子查询(Sub Query)

什么是子查询

1)子查询概念 
一条select查询语句的结果, 作为另一条select语句的一部分
2) 子查询的特点
子查询一般作为父查询的查询条件使用
子查询必须放在小括号中使用
SQL语句含有多个select，先执行子查询，再执行外查询

单行子查询

单行子查询: 查询出的结果为一列一行(一个数据) 如: 最高, 最低, 平均等,
 可以使用判断符号 如: > | < | = | != 等 

语法格式

select 字段 from 表 where 字段 判断符号 (子查询)

需求:

#1 查询价格最高的商品信息
#2 查询化妆品分类下的 商品名称 商品价格
#3 查询小于平均价格的商品信息

SQL实现

#1 查询价格最高的商品信息
-- 1.查询出商品最高的价格
select max(price) from products;  -- 5000
select * from products where price = 5000;
-- 2.将查询出的最高价格作为条件查询, 获取商品信息 
select * from products where price = (select max(price) from products);

#2 查询化妆品分类下的 商品名称 商品价格
-- 1. 查询化妆品分类的cid
select cid from category where cname = '化妆品';  --c003
select pname, price from products where cid = 'c003';
-- 2. 将查询出的化妆品分类cid作为条件, 查询 商品名称 商品价格
select pname, price from products 
where cid = (select cid from category where cname = '化妆品');

#3 查询小于平均价格的商品信息
-- 1.查询出商品平均价格
select avg(price) from products;  -- 1866.6667
-- 2.将查询出的商品平均价格作为条件, 查询商品信息
select * from products 
where price < (select avg(price) from products);

多行子查询

多行子查询: 查询出的结果为一列多行(多个数据) 如: 化妆品类别下的商品cid, , 可以使用判断符号 如: in all any  
In :  等于任意一个
使用方式:
in(值1, 值2 ...)
all:  所有  
使用方式: 
如: 字段 > | <all(值1, 值2 ...) 大于所有的值  
any: 任意一个 
使用方式
如: 字段 > | < any(值1, 值2 ...) 大于任意一个值  
如: 字段 = any(值1, 值2 ...) 等于任意一个值 效果等同于 in

语法格式

select 字段 from 表 where 字段 判断符号(in | any | all) (子查询)

需求:

#1 查询化妆品类别中的商品价格和鞋服类别中的商品价格一样的商品信息
#2 查询价格比所有鞋服类别中商品的价格都高的商品信息
#3 查询价格比任意一个鞋服类别中商品的价格高的商品信息

SQL实现
#1 查询化妆品类别中的商品价格和鞋服类别中的商品价格一样的商品信息
-- 1.查询化妆品类别下所有商品的价格
select price from products where cid = 'c003'; -- 800 200
-- 2.查询鞋服类别下所有商品的价格
select price from products where cid = 'c002'; -- 800 200 300 2000
-- 3.化妆品中商品的价格和鞋服中商品的价格进行条件判断
select * from products 
where cid = 'c003' and price in (select price from products where cid = 'c002');

#2 查询价格比所有鞋服类别中商品的价格都高的商品信息
-- 1.查询出所有鞋服类别中商品的价格
select price from products where cid = 'c002'; -- 800 200 300 2000
-- 2.鞋服类别中商品的价格作为判断条件
select * from products 
where price > all(select price from products where cid = 'c002');

#3 查询价格比任意一个鞋服类别中商品的价格高的商品信息
-- 1.查询出所有鞋服类别中商品的价格
select price from products where cid = 'c002';  -- 800 200 300 2000
-- 2.鞋服类别中商品的价格作为判断条件
select * from products 
where price > any(select price from products where cid = 'c002');

注意事项:
单行子查询: 查询出的结果为一列一行(一个数据), 可以使用 > < = !=
多行子查询: 查询出的结果为一列多行(多个数据), 需要使用in all any

数据库事务控制(TCL)

什么是事务

常用的存储引擎有InnoDB（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎），其中InnoDB支持事务处理机制，而MyISAM不支持
事务是一个整体, 由一条或者多条SQL语句组成, 这些SQL语句要么都执行成功, 要么就失败, 只要有一条SQL出现异常, 整个操作就会回滚
回滚: 就是事务运行的过程中发生了某种故障, 或者SQL出现了异常, 事务不能继续执行, 系统将事务中对数据库的所有已完成的操作全部取消, 回滚到事务开始时的状态
比如: 银行的转账业务,张三给李四转账500元 , 至少要操作两次数据库, 张三 -500, 李四 + 500,这中 间任何一步出现问题,整个操作就必须全部回滚, 这样才能保证用户和银行都没有损失.

MySQL事务操作

MySQL 中可以有两种方式进行事务的操作：
1) 自动提交事务(MySQL默认)
2) 手动提交事务

手动提交事务

1) START TRANSACTION 这个语句显式地标记一个事务的起始点。 

2) COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所	有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。 
	
3) ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不	能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

手动提交事务的流程

1)执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务执行
2) 失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

手动控制事务的转账案例

手动开启事务 start transaction
·手动开启事务后, 事务不再是自动提交
提交事务 commit  
·没有出现任何问题, 提交事务 数据就会持久化到本地磁盘
回滚事务 rollback  
· 出现问题回滚事务, 回滚事务要在提交事务之前操作,一旦事务完成了提交, 就不能	再回滚. 如出现宕机等情况事务会自动回滚

-- 开启事务
start transaction;

-- tom账户 -500
update account set money = money-500 where name = 'tom';
-- jack账户 +500
update account set money = money+500 where name = 'jack';

-- 提交事务(没发生任何问题提交)
commit;

-- 回滚事务(出现问题回滚, 如果出现宕机等原因事务会自动回滚)
rollback;

事务的四大特性 ACID

原子性（Atomicity）
一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性

一致性（Consistency）
指事物必须是数据库从一个一致性状态到另一个一致性状态。也就是说一个事物执行之前和执行之后都必须处于一致性状态

隔离性（Isolation）
事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰

持久性（Durability）
一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态

MySQL事务的隔离级别

数据并发访问

一个数据库可能拥有多个访问客户端, 这些客户端都可以并发方式访问数据库. 数据库的相同数据可能被多个事务同时访问, 如果不采取隔离措施, 就会导致各种问题, 破坏数据的完整性

并发访问产生的问题

事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据, 可能引发并发访问的问题

四种隔离级别

通过设置隔离级别,可以防止上面的三种并发问题. MySQL数据库有四种隔离级别上面的级别最低，下面的级别最高

注意事项:
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大

隔离级别相关命令

查看隔离级别
select @@transaction_isolation;

设置隔离级别

– 设置隔离级别语法格式
set session transaction isolation level 隔离级别名称;
– 如: 设置为读未提交
set session transaction isolation level read uncommitted;

read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化

索引

什么是索引

对于任何的数据库管理系统，索引都是进行优化的最主要因素。对于少量的数据，即使没有合适的索引对数据库性能的影响并不是很大，但是随着数据量的增加，数据库性能会急剧下降, 所以索引目的在于提高检索数据的效率
索引就是用于快速找到记录的一种数据结构, 可以简单理解为：排好序的快速查找数据结构

索引的作用

MySQL的data(数据)是存放在磁盘, 读取数据时需要将磁盘中的数据读取到内存中,所以频繁的查询会导致频繁的磁盘IO, 所谓磁盘IO，简单来讲就是就是将磁盘中的数据读取到内存或者是从内存写入磁盘。在系统开发与设计过程中，磁盘IO的瓶颈往往不可忽略，因为这是一个相对比较耗时的操作
在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询运行更加高效。 如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字

索引的底层实现

索引底层实现使用的就是数据结构, 将操作的索引放到数据结构中, 借助于数据结构实现快速的查询

Hash结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询

缺点:
1)哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表
2) 适合于精确的查找, 也不适合范围查询

Tree结构

BTree结构

BTree分为B-Tree, B+Tree, MySQL数据库索引采用的B+Tree, B+Tree是在B-Tree上做了优化改造

1. B-Tree
   ·索引值和data(数据)分布在整棵树结构中
   ·每个节点可以存放多个索引值以及对应的data(数据)
   ·树节点中的多个索引值从左到右升序排列

缺点:
所有的节点都存放数据, 数据会占用空间, 导致存放的索引变少

2. B+Tree
   ·非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
   ·叶子节点包含了所有的索引值和data数据
   ·叶子节点用指针连接，提高区间的访问性能

相比B-树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B-树需要遍历范围内所有的节点和数据，显然B+Tree效率高

常见的索引的分类