目录：
1.连接查询
2.外键
3.外键约束
4.外键约束模式
5.联合查询(合并结果集)
6.子查询
7.标量子查询
8.列子查询
9.行子查询
10.表子查询
11.exists子查询
12.视图
13.查看视图
14.视图的使用
15.视图的修改和删除
16.视图的意义
17.视图数据操作
18.视图--算法
19.数据表备份
20.单表数据备份
21.SQL备份
22.增量备份
1.连接查询
* SQL连接查询分类：内连接，外连接，自然连接，交叉连接
交叉连接
* 交叉连接：cross join,从一张表中循环取出一条记录，每条记录都去另外一张表进行匹配，
最终形成的结果叫：【笛卡尔积】
select * from 左表 cross join 右表;<==>select * from 左表, 右表;
* 实际查询时不会用交叉连接
内连接
* [inner] join:从左表中取出一条记录，取右表中与所有的记录进行匹配，匹配必须
是某个在左表中与右表中相同最终才会保留结果，否则不保留。
* 例子：
select s.字段名,c.字段名
from tb_stu as s
inner join tb_class as c
on s.id = c.id;
外连接
* 外连接：以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接；
不能匹配on条件，其他表的字段置空null
* 例子：
select s.字段名,c.字段名
from tb_stu as s
left/right outer join tb_class as c
on s.id = c.id;
5.自然连接
* natural join 自然内连接
* natural left/right join 自然外连接
* 自然连接字段使用同名字段作为连接条件；连接之后会合并同名字段。
2.外键
* 外键：foreign key,如果一张表中有一个字段引用另外一张表的主键，那么将该表字段称为外键；
一张表可以有多个外键。
* 创建表的时候增加外键：在所有的表字段之后，使用foreign key(外键字段) references 外部表(主键字段)
* 例子：
create table tb_class( -- 主表
id int primary key AUTO_INCREMENT comment '主键',
cname varchar(20) comment '班级名称'
)charset utf8;
insert into tb_class values(null,'java一班'),(null,'java二班'),(null,'java三班');
select * from tb_class;
create table tb_stu( -- 从表
id int primary key auto_increment,
uname varchar(20),
gender varchar(10),
cid int,
foreign key(cid) REFERENCES tb_class(id) -- 定义外键
)charset utf8;
show create table tb_stu;======>结果：MyISAM不支持外键，所有查询创建语句中没有外键，只有索引
CREATE TABLE `tb_stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uname` varchar(20) DEFAULT NULL,
`gender` varchar(10) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cid` (`cid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
* 修改表时指定外键： constraint [kənˈstreɪnt]
alter table tb_stu add [constraint fk_stu_cid] foreign key(cid) references tb_class(id)；
* 删除外键
- 外键不可更改，只能删除后新增
alter table tb_stu drop foreign key 外键名;
如果只是删除外键，索引key还存在
3.外键约束
* 外键默认的作用有两点：一个对父表(主表)，一个对子表(从表，即外键字段所在的表)
* 对子表约束：子表数据进行写操作时，如果对于的外键字段在父表中找不到对应的匹配，那么操作或失败(约束子表数据操作)
* 对父表约束：对父表数据进行写操作(删、改都必须涉及主键本身)，如果对应的主键在子表中已经被数据引用，
那么不允许操作。
* 外键条件：
外键要存在，首先必须保证表的存储引擎是innodb；如果不是innodb存储引擎，那么外键可以创建成功，但是没有约束效果。
外键字段的字段类型(类类型)必须与父表的主键类型完全一致
* 我使用mysql 5.1，默认ENGINE=MyISAM，所以创建外键无效
4.外键约束模式
* 所谓外键约束：就是指外键的作用
* 之前所讲的外键作用，是默认的作用；其实可以通过对外键的需求，进行定制操作
* 外键约束有三种约束模式：都是针对父表的约束
district：严格模式，父表不能删除或更新一个已经被子表数据引用的记录
cascade：级联模式，父表的操作，对应子表关联的数据也跟着操作
set null：置空模式，父表操作之后，子表对应的数据(外键字段)被置空
* 通常一个合理的做法(约束模式)：删除的时候子表置空，更新的时候子表级联操作
语法：constraint 约束名 foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;
* 外键置空的前提条件：外键字段允许为空，如果不满足条件，外键无法创建
* 外键虽然很强大，能够进行各种约束；但是对于PHP来讲，外键的约束降低了PHP对数据的可控性；
通常在实际开发中，很少使用外键来处理
* 创建外键约束
CREATE TABLE `tb_class` ( -- 主表
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`cname` varchar(20) DEFAULT NULL COMMENT '班级名称',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
create table tb_stu( -- 从表
id int primary key AUTO_INCREMENT,
sname varchar(20) comment '姓名',
cid int,
constraint fk_stu_class foreign key(cid) REFERENCES tb_class(id)
)engine=INNODB charset=utf8;
-- 引擎使用INNODB创建表失败 原因是主表ENGINE=MyISAM
Error Code : 1005
Can't create table 'test1.tb_stu' (errno: 150)
5.联合查询(合并结果集)
* 多条select语句构成，每一条select语句获取的字段数必须严格一致，但与列类型无关
* 语法：
select 语句1
union [union选项]
select 语句2...
* union选项
all:保留所有(不管重复)
distinct:去重，默认
* 联合查询的意义：
1)查询同一张表，但是需求不同：如查询学生信息，男生年龄升序，女生年龄降序；
2)多表查询：多张表的结构是完全一样的，保存的数据(结构)也是一样的
* 例子：查询学生信息，男生年龄升序，女生年龄降序
drop table if exists tb_stu;
create table tb_stu(
id int primary key AUTO_INCREMENT,
sname varchar(20) comment '姓名',
gender varchar(20),
age int
) charset=utf8;
insert into tb_stu values(null,'张三1','男',floor(rand()*20+20));-- 多次执行下面两句，插入多条记录
insert into tb_stu values(null,'张三1','女',floor(rand()*20+20));
-- 联合查询里面使用order by需要满足：select语句加(),并且要有limit
(select * from tb_stu where gender = '男' order by age asc limit 99999)
union
(select * from tb_stu where gender = '女' order by age desc limit 99999)
6.子查询
* 子查询：sub query，查询是在某个查询结果之上进行的(一条select语句内部包含了另外一条select语句)
* 子查询分类：
按位置分类：
from 子查询：子查询出现在from之后
where 子查询
exists 子查询
* 按结果分类：根据子查询得到的数据进行分类(任何一个查询得到的结果集都可以理解为二维表)
标量子查询：子查询得到的结果是一行一列
列子查询：子查询得到的结果是一行多列
行子查询：子查询得到的结果是多行多列
上面几个出现的位置都是在where之后
表子查询：子查询得到的结果是是多行多列，出现的位置是在from之后
7.标量子查询
* 需求：班级名字为'java一班'(并不知该班对应的id)，获取该班所有学生信息
CREATE TABLE `tb_class` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`cname` varchar(20) DEFAULT NULL COMMENT '班级名称',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
drop table tb_stu;
create table tb_stu(
id int primary key AUTO_INCREMENT,
sname varchar(20) comment '姓名',
cid int
) charset=utf8;
insert into tb_stu values(null,'张三1',floor(rand()*3+1)); --多次执行该语句
select * from tb_stu where cid = (select id from tb_class where cname = 'java一班');
8.列子查询
* 查询java一班和java二班所有学生信息
select * from tb_stu where cid in (select id from tb_class where cname in('java一班','java二班'));
* any,some,all
=any 等价于 in
9.行子查询
* 查询年龄最大，身高最高的的学生
drop table tb_stu;
create table tb_stu(
id int primary key AUTO_INCREMENT,
sname varchar(20) comment '姓名',
age int comment '年龄',
height int comment '身高'
) charset=utf8;
insert into tb_stu values(null,'张三1',20,180),(null,'张三2',30,170),(null,'张三3',50,190),(null,'张三4',10,160);
-- 方法一：
select * from tb_stu
where age = (select max(age) from tb_stu)
and height = (select max(height) from tb_stu);
-- 方法二：构造行元素(age,height)
select * from tb_stu
where (age,height) = (select max(age),max(height) from tb_stu);
10.表子查询
* 需求：查询每个班身高最高的学生信息
* 方法一：
drop table tb_stu;
create table tb_stu(
id int primary key AUTO_INCREMENT,
sname varchar(20) comment '姓名',
age int comment '年龄',
height int comment '身高',
cid int comment '班级id'
) charset=utf8;
insert into tb_stu values(null,'张三1',20,180,2),(null,'张三2',30,170,1),(null,'张三3',50,190,3),(null,'张三4',10,160,2);
insert into tb_stu values(null,'张三1',20,120,2),(null,'张三2',30,150,1),(null,'张三3',50,130,3),(null,'张三4',10,150,2);
select * from tb_stu;
select * from tb_stu
where (cid,height) = any (select cid,max(height) from tb_stu group by cid);
* 方法二：
select * from (select * from tb_stu order by height desc) as s group by cid;
11.exists子查询
* exists:是否存在的意思。exists子查询就是用来判断某些条件是否满足(跨表),exists是接在where之后，
exists返回的结果只有0或1
* 例子：查询所有的学习信息，前提是班级存在
select * from tb_stu where exists(select * from tb_class); -- exists(select * from tb_class)结果是1
select * from tb_stu where exists(select * from tb_class where id=1000); -- exists(select * from tb_class where id=1000)结果是0
12.视图
* 视图：view，是一种有结构(有行有列)但是没有结果(结构中不真实放数据)的虚拟表，虚拟表的结构来源不是自己定义，
而是从对应的【基表】中产生(视图的数据来源)
* 创建视图的语法：
create view 视图名 as select语句
* 例子：
create view v1 as select * from emp;
create view v2 as select * from emp left join dept where emp.id = dept.id; -- 有重名字段，创建失败；可以对重名字段重命名
13.查看视图
* show tables; -- 创建的视图也会显示
* desc 视图名;
* show create table/view 视图名;
14.视图的使用
* select * from 视图名;
* 视图的执行：本质就是执行封装的select语句
15.视图的修改和删除
* 视图本身不可修改，但是视图的来源是可以修改的；修改视图就是修改视图封装的select语句
alter view 视图名 as 新select语句;
* 删除视图(视图不存储数据，可以删除；但是不建议随意删除)
drop view 视图名;
16.视图的意义
1)视图可以复用SQL语句：将一条复杂的查询语句使用视图进行保存，以后可以直接对视图进行操作。
2)数据安全：视图操作主要是针对查询的，如果对视图结构进行处理(删除),不会影响基本数据
3)视图往往是在大项目中使用，而是多系统使用；封装基表数据，对外提供有用的数据，隐藏关键的数据，数据安全
4)视图可以对外提供友好型：不同的视图提供不同的数据，对外好像专门设计
5)视图可以更好(容易)进行权限控制
17.视图数据操作
* 视图新增数据
不能向多表视图(视图来源于多张基表)新增数据
单表视图是可以新增数据的，并且会影响基表(即新增的数据插入到基表)
* 注意：如果视图中剔除基表的某个字段，但是该字段not null且没有显示指定默认值，则出错
* 视图删除视图
多表视图不能执行删除记录操作
单表视图可以执行删除记录操作 delete from 视图名 [where语句];
* 更新数据
多表/单表视图都可以更新数据
例子：
create view my_v4 as select * from tb_stu where age >30 with check option;
-- with check option:视图更新数据后仍然要满足age>30
注意：需要更新的数据都是视图中有的数据
18.视图--算法
* 视图算法
undefined:未定义，默认的，这不是一种实际的使用算法，只是告诉系统没有定义算法，系统自己看着办
temptable:临时表算法，系统应该先执行视图的select语句，后执行外部查询语句
merge:合并算法，系统先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行。
* 视图算法选择：如果视图的select语句中会包含一个查询子句(五子句)，而且很有可能顺序比外部的查询语句要靠后，
一定要使用算法temptable，其他情况可以不用指定，使用默认即可
* 创建视图时指定算法
create algorithm=temptable view my_v6 as select语句;
19.数据表备份
* 数据备份还原的方式：数据表备份、单表数据备份、SQL备份、增量备份
* mysql存储引擎：免费的两种，innodb和myisam；其他的收费
* 查看mysql的版本：select @@version; show variables like 'version';
* innodb和myisam的数据存储方式：
innodb：只有表结构，数据全部存储到ibdata1文件中
myisam:数据、表和索引分开存储 (.frm结构文件 .MYD数据 .MYI索引)
frame data index
* 数据表备份：不需要通过SQL来备份，直接进入到数据库文件夹复制对应的表结构以及数据文件，以后还原的时候，
直接将备份的内容放进去即可。
- 数据备份还原有前提条件：根据不同的存储引擎有不同的区别
- 这种文件备份通常【适用于myisam存储引擎】，直接复制这三个文件即可
20.单表数据备份
* 每次只能备份一张表，只能备份数据(表结构不能备份)；
通常的使用：将表的数据进行导出到文件
* select */字段列表 into outfile 文件路径 [fields 字段处理 lines 行处理] from 数据源; -- 前提是外部文件不存在。
select * into outfile 'D:/temp/sql/a.txt' from emp; -- 注意：temp/sql目录必须存在，而a.txt不存在
* fields 字段处理:
enclosed by:字段使用什么内容包裹，默认是空字符串
terminated by:字段以什么结束，默认是 "\t",即tab键
escaped by:特殊符号用什么方式处理，默认是"\\"，即反斜杠转义
* lines 行处理：
starting by:每行以什么开始，默认是空字符串
terminated by:每行以什么结束，默认是"\r\n" ，即换行符
* 数据还原：将一个在外部保存的数据重新恢复到表中
load data infile 文件路径 into table 表名(字段列表) fields 字段处理 lines 行处理;
* 例子：
备份：
select * into outfile 'D:/temp/sql/a.txt' fields enclosed by '"' terminated by '|' lines starting by 'start:' from emp;
结果：
start:"1"|"张三"|"10"
start:"2"|"张三"|"20"
start:"3"|"张三"|"40"
还原：(数据库utf8,txt也是utf8,但是还原后有乱码)
load data infile 'D:/temp/sql/a.txt' into table emp fields enclosed by '"' terminated by '|' lines starting by 'start:';
21.SQL备份
* SQL备份：系统会对表结构及数据进行处理，变成对应的SQL语句，还原时只要执行SQL指令即可；
1)数据库导出sql脚本(备份)back-up
>mysqldump -u密码 -p密码 数据库名 [表名列表]>生成的脚本文件的路径(文件名后缀为.sql)
>例如：【mysqldump -uroot -p123 mydb3>d:\a.sql】 (没登录前使用该命令)
>注意：不要打分号，不要登录mysql，直接在cmd下运行
>注意：生成的脚本文件中不包含create database语句
2)执行sql脚本(恢复) restore[rɪˈstɔ:(r)]
第一种方式：(未登录状态)
>mysql -u用户名 -p密码 数据库
>例如：
* 先删除mydb1，再重新创建mydb1库
*【mysql -uroot -p123 mydb1
>注意：不要打分号，不要登录mysql，直接在cmd下运行
第二种方式：
>登录mysql
>source sql脚本路径
>例如：
*先删除mydb1库，在重新创建mydb1
*切换到mydb1库
*【source c:\mydb1.sql】
22.增量备份
* 不是针对数据或者SQL指令进行备份，是针对mysql服务器的日志文件进行备份
* 指定时间段开始进行备份，备份数据不会重复，而且所有的操作都会备份，大项目都用增量备份。
---
扫一扫
927
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
