MySQL数据库三

1.2 多表查询

1.2.1 内连接

规则：返回两个表的公共记录
语法：

--语法一
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
--语法二
select * from 表1,表2 where 表1.公共字段=表2.公共字段

例题：

-- inner join
select * from  stuinfo inner join stumarks on  stuinfo.stuno=stumarks.stuno;

--相同的字段只显示一次
mysql>select s.stuno,stuname,stusex,writtenexam,labexam from stuinfo s  inner join stumarks m on s.stuno=m.stuno;

--使用where
select * from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;

多学一招：

1、内连接中inner可以省略
select * from 表1 join 表2 on 表1.公共字段=表2.公共字段

--如何实现三表查询
select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段

--连接越多，效率越低

思考：

select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段
和
select * from 表2 inner join 表1 on 表1.公共字段=表2.公共字段  一样吗？
答：一样的

1.2.2 左外连接

规则：以左边的表为准，右边如果没有对应的记录用null显示
语法：

select * from 表1 left join 表2 on  表1.公共字段=表2.公共字段

例题：

select stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;

思考：

select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段 和
select * from 表2 left join 表1 on 表1.公共字段=表2.公共字段 一样吗？
答：不一样，第一个SQL以表1为准，第二个SQL以表2为准

1.2.3 右外连接

规则：以右边的表为准，左边没有对应的记录用null显示
语法：

select * from 表1 right join 表2 on  表1.公共字段=表2.公共字段

例题：

select stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;

思考：
思考：

select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段 和
select * from 表2 right join 表1 on 表1.公共字段=表2.公共字段 一样吗？
答：一样,都是以表一为准

1.2.4 交叉连接

语法：返回笛卡尔积

select * from 表1 cross join 表2

例题：

--交叉连接
select * from stuinfo cross join stumarks;
--交叉连接有连接表达式与内连接是一样的
select * from stuinfo cross join stumarks on stuinfo.stuno=stumarks.stuno;

小结：
1、交叉连接如果没有连接条件返回笛卡尔积
2、如果有连接条件和内连接是一样的

1.2.5 自然连接

自动判断条件连接，判断的条件是依据同名字段
1、自然内连接(natural join)

select * from stuinfo natural join stumarks;

2、自然左外连接(natural left join)

select * from stuinfo natural left join stumarks;

3、自然右外连接(natural right join )

select * from stuinfo natural right join stumarks;

小结：
1、表连接是通过同名字段来连接的
2、如果没有同名字字段就返回笛卡尔积
3、同名的连接字段只显示一个，并且将该字段放在最前面

1.2.6 using

using用来指定连接字段

select * from stuinfo inner join stumarks using(stuno);

using的结果也会对公共字段进行优化，优化的规则和自然连接是一样的

1.2.7 练习

1、显示地区及每个地区参加笔试的人数，并按人数降序排列

--第一步：显示地区及每个地区参加笔试的人数
select stuaddress,count(writtenexam) from stuinfo left join stumarks using(stuno) group by stuaddress;
-- 第二步:将结果降序排列
select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc;

2、显示有学生参加考试的地区

-- 表连接实现
--第一步：右连接获取有成绩的地区
select stuaddress from stuinfo right join stumarks using(stuno);
-- 第二步：去重复
select distinct stuaddress from stuinfo right join stumarks using(stuno);
--去除null
select distinct stuaddress from stuinfo right join stumarks using(stuno) having stuaddress is not null;

3、显示男生和女生的人数

select stusex,count(*) from stuinfo group by stusex;
--方法二：union
select stusex,count(*) from stuinfo where stusex='男' union select stusex,count(*) from stuinfo where stusex='女';
--方法三:直接写条件
select sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo;

4、显示每个地区男生、女生、总人数

select stuaddress,count(*) 总人数,sum(stusex='男') 男,sum(stusex='女') 女 from stuinfo group by stuaddress;

1.3 子查询

语法：select * from 表1 where (子查询)
外面的查询称为父查询
子查询为父查询提供查询条件

1.3.1 标量子查询

特点：子查询返回的值是一列

--查询笔试成绩是80的学生
select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);

-- 查找最高分的学生
--方法一
select * from stuinfo where stuno=(select stuno from stumarks order by writtenexam desc limit 1);
--方法二
select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=(select max(writtenexam) from stumarks))

1.3.2 列子查询

特点：子查询返回的结果是一列

如果子查询的结果返回多条记录，不能使用等于，用in 或not in

--查询及格的同学
select * from stuinfo where stuno in (select stuno from stumarks where writtenexam>=60);
--查询不及格的同学
select * from stuinfo where stuno in (select stuno from stumarks where writtenexam>=60);
--查询需要补考的学生
select * from stuinfo where stuno not in (select stuno from stumarks where writtenexam>=60);

1.3.3 行子查询

特点：子查询返回的结果是多个字段组成

--查找语文成绩最高的男生和女生
select * from stu where(stusex,ch) in (select stusex,max(ch) from stu group by stusex);

注意：form 后面跟的是数据源，如果将子查询当成表来看，必须给结果集取别名

1.3.5 exists子查询

--如果笔试成绩有人超过80人，就是显示所有学生信息
select * from stuinfo where exists(select * from sumarks where writtenexam>=80);
--没有超过80的学生，就显示所有学生信息
select * from stuinfo where not exists(select * from stumarks where writtenexam>=80);

作用：提高查询效率

1.4 视图

1.4.1 概述

1、视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
2、视图中并不存放数据，而是存放在视图所引用的原始表（基表）中
3、同一张原始表，根据不同用户的不同需求。可以创建不同的视图

1.4.2 作用

1、筛选表中的行
2、防止未经许可的用户访问敏感数据
3、增加数据的兼容性（数据库的字段名发生变化，程序可以不用改字段名）
4、隐藏数据表的结构
5、降低数据库的复杂度

1.4.3 创建视图

语法：

--创建视图
create[ or replace] view 视图名
as
	select 语句;
--查询视图
select 列名 from 视图

例题：

--创建视图
create view view1
as
select * from stu where ch>=60 and math>=60;

--查询视图
select * from view1;

--视图可以降低SQL语句的复杂度
create view view2
as 
select stuno,stusex,writtenexam,labexam from stuinfo natural join stumarks;

1.4.4 修改视图

语法

alter view 视图名
as
	select 语句

例题：

alter view view2
as
select stuname from stuinfo;

1.4.5 删除视图

drop view view2;

1.4.6 查看视图信息

show tables; --显示所有的表和视图

--方法二：精确查找视图(视图信息存储在information_schema下的views表中)
select table_name from information_schema.views;

--方法三：通过表的comment属性查询视图
show table status where comment='view'\G; --查询表和视图的详细状态信息
show table status where comment='view'\G; --只查找视图信息

查询视图的结构

desc view1;

查询创建视图的语法

show create view view1\G

1.4.7 视图算法

场景：找出语文成绩最高的男生和女生
方法一：

select * from (select * from stu order by ch desc) t group by stusex;

方法二：

create view view3
as
select * from stu order by ch desc;

select * from view3 group by stusex;

结论：方法一和方法二的结果不一样，这是因为视图的算法造成的

视图的算法有：
1、merge:合并算法（将视图语句和外层语句合并后再执行）
2、temptable:临时表算法（将视图作为一个临时表来执行）
3、undefined:未定义算法（用哪种算法有MySQL决定，这是默认算法，视图一般会选merge算法）

重新通过视图实现

创建视图，指定算法为临时表算法
create or replace algorithm=temptable view view3
as
select * from stu order by ch desc;

select * from view3 group by stusex;

结论：和子查询结果一致。

1.5 事务

1.5.1 概述

1、事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作。是一个整体，要么一起执行，要么一起不执行。
2、这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行
3、事务是一个不可分隔的工作逻辑单元

1.5.2 事务特性

事务必须具备以下四个属性，简称ACID属性：
原子性(Atomicity):事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
一致性（Consistency）:当事务完成时，数据必须处于一致状态。
隔离性（lsolation）:对数据进行修改的所有并发事务是彼此隔离的。
永久性（Durability）：事务完成后，它对数据库的修改被永久保持。

1.5.3 事务处理

开启事务

提交事务

回滚事务

rollback

例题：

--插入测试数据
create table bank(
		card char(4) primary key comment '卡号',
		money decimal(10,2) not null
		engine=innodb charset=utf8;

insert into bank values('1001',1000),('1002',1);

--开启事务
begin;

delimiter // --更改定界符

update bank set money=money-100 wher card='1001';
update bank set money=money+100 where card ='1002' //

--回滚事务
rollback//

select * from bank//
card  money
1001   1000.00
1002    1.00
-------------------------------------------------------------
--开启事务
start transaction //

update bank set money=money-100 wher card='1001';
update bank set money=money+100 where card ='1002' //

--提交事务
commit//

select * from bank//
card   money
1001   900.00
1002   101.00

设置事务的回滚点

begin//

insert into bank values ('1003',500) //

--记录事务回滚点
savepoint a1 //

insert into bank values ('1004',200) //

--回滚到回滚点a1
rollback to a1//

select * from bank//

card money
1001  900.00
1002  101.00
1003   500.00

自动提交事务

每一个SQL语句都是一个独立的事务

小结：
1、事务是事务开启的时候开启
2、提交事务、回滚事务后事务都结束
3、只有innodb支持事务
4、一个SQL语句就是一个独立的事务，开启事务是将多个SQL语句放到一个事务中执行

1.6 索引

1.6.1 概述

优点：

加快查询速度

缺点：

带索引的表在数据库中需要更多的存储空间
（增、删、改）操作数据库的命令需要更长的处理时间，因为它们需要对索引进行更新

1.6.2 创建索引的指导原则

适合创建索引的列

1、该列用于频繁搜索
2、该列用于对数据进行排序
3、在WHERE子句中出现的列，在join子句中出现的列。

请不要使用下面的列创建索引：

1、列中仅包含几个不同的值
2、表中仅包含几行，为小型表创建索引可能不太划算，因为MySQL中在索引中搜索数据所花的时间比在表中逐行搜索花的时间更长

1.6.3 创建索引

1、主键索引：主要创建了主键就会自动的创建主键索引

2、唯一索引：创建唯一键就创建了唯一索引

--创建表的时候添加唯一索引
create table t4(
id int primary key,
name varchar(20),
unique ix_name(name) --添加唯一索引
);

-- 给表添加唯一索引
create table t5(
name varchar(20),
addr varchar(50)
);

create unique index ix_name on t5(name);

-- 通过更改表的方式创建唯一索引
create unique index ix_name on t5(name);

普通索引

--创建表的时候添加普通索引
create table t6(
id int primary key,
name varchar(20),
index ix_name(name)
);

--给表添加普通索引
create table t7(
name varchar(20),
add varchar(50)
);

create index ix_name on t7(name);

-- 通过更改表的方式创建索引
alter table t7 add index ix_addr(addr);

多列索引

小结：
1、创建主键就会创建主键索引
2、创建唯一键就会创建唯一索引
3、创建唯一键的语法

--语法一
create unique [index] 索引名 on 表名（字段名）
--语法二
alter table 表名 add unique [index] 索引名（字段名）

4、创建普通索引

--语法一
create index 索引名 on 表名（字段名）
--语法二
alter table 表名 add index 索引名（字段名）

5、索引创建后，数据库根据查询语句自动选择索引

1.6.4 删除索引

语法：drop index 索引名 on 表名

drop index ix_name on t7;

1.7 函数

1.7.1 数字类

-- 获取随机数
select rand();

--随机排序
select * from stuinfo order by rand();

--随机获取一条记录
select * from stuinfo order by rand() limit 1;

--四舍五入，向上取整，向下取整
select round(3.1415926,3) '四舍五入',truncate(3.14159,3) '截取数据',ceil(3.1) '向上取整',floor(3.9) '向下取整';

注意：截取数据直接截取，不四舍五入

1.7.2 字符串类

-- 大小写转换
select ucase('i name is tom') '转成大写',lcase('My Name IS TOM') '转成小写';
转成大写          转成小写
I NAME IS TOM    my name is tom

--截取字符串
select left ('abcdef',3) '从左边截取',right('abcdef',3) '从右边截取',substring('abcdef',2,3) '字符串';
从左边截取		从右边截取		字符串
abc  				def					bcd

--字符串相连
select concat('中国','北京','顺义') '地址'
地址
中国北京顺义

select concat(stuname,'-',stusex) 信息 from stuinfo;
```mysql
信息
张秋丽-男
李文才-女
...

-- coalesce(str1,str2) :str1有值显示str1,如果str1为空就显示str2
-- 将成绩为空的显示为缺考
select stuname,coalesce(writttenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks;	

stuname      coalesce(writtenexam,'缺考')    coalesce(labexam,'缺考') 
张秋丽				77												82
王无					缺考													66
张飞					66												缺考

-- length():字节长度,char_length():字符长度
select length('锄禾日当午') 字节,char_length('锄禾日当午') 字符;
字节			字符
10			5

1.7.3 时间类

--时间戳
select unix_timestamp();

--格式化时间戳
select from_unixtime(unix_timestamp());

--获取当前格式化时间
select now();

--获取年、月、日、小时、分钟、秒
select year(now()) 年,month(now()) 月,day(now()) 日,hour(now()) 小时,minute(now()) 分钟,second(now())秒;

--星期，本年第几天
select dayname(now()) 星期,dayofyear(now())  本年第几天;

--日期相减
select datediff(now(),'2010-08-08') 相距天数;

1.7.4 加密函数

1、md5()
2、sha()

select md5('aa');

select sha('aa');

1.8 预处理

预编译一次，可以执行多次。用来解决一条SQL语句频繁执行的问题。

预处理语句:prepare 预处理名字 from 'sql语句'
执行处理语句：execute 预处理名字 [using 变量]

例题：不带参数的预处理

--创建预处理
prepare stmt from 'select * from stuinfo';

--执行预处理
execute stmt;

例题:带一个参数的预处理

--创建预处理
prepare stmt from 'select * from stuinfo where stuno=?';


--调用预处理，并传参数
delimiter //  --更改定界符

set @id='s25301';
execute stmt using @id//

例题：传递多个参数

prepare stmt from 'select * from stuinfo where stuage>? and stusex=?' //

set @age=20;
set @sex='男';
execute stmt using @age,@sex//

小结：
1、Mysql中变量以@开头
2、通过set给变量赋值
3、?是位置占位符

1.9 数据库备份和还原

数据库中的数据需要定期备份，数据量小的可以一周备份一次，数据量大的可以一天备份一次。

1.9.1 数据备份

利用mysqldump工具，语法：

mysqldump 数据库连接 数据库>数据库备份

例题：

--将数据库中所有的表导出到data.sql中
mysqldump -uroot -proot data>c:\data.sql

--将data数据库中的stuinfo、stumarks表
mysqldump -uroot -proot data stuinfo stumarks>c:\data.sql

--导出data数据库，导出的语句中带有创建数据库的语法
mysqldump -uroot -proot -B data>c:datal.sql

1.9.2 数据库还原

方法一：MySQL的source指令

source c:/data.sql;
注意：地址分隔符用斜线，不能用反斜线

方法二：通过mysql指令数据还原(不需要登录MySQL)
语法：

msyql 连接数据库 导入数据库的名 <  导入的SQL文件

例题：

F:\wamp\PHPTutorial\Mysql\bin>mysql -uroot -proot data1 < c:\data.sql