mysql数据库的基本操作(连接查询、外键、合并结果集、子查询、视图、备份还原)
目录:
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<d:\a.sql】
>注意:不要打分号,不要登录mysql,直接在cmd下运行
第二种方式:
>登录mysql
>source sql脚本路径
>例如:
*先删除mydb1库,在重新创建mydb1
*切换到mydb1库
*【source c:\mydb1.sql】
22.增量备份 <--返回目录
* 不是针对数据或者SQL指令进行备份,是针对mysql服务器的日志文件进行备份
* 指定时间段开始进行备份,备份数据不会重复,而且所有的操作都会备份,大项目都用增量备份。
---
posted on 2020-07-11 00:18 wenbin_ouyang 阅读(494) 评论(0) 编辑 收藏 举报