MySql笔记（三）

1.链接查询：

①交叉链接(cross join) ：从一张表中循环取出每一条数据，每条记录都会导另外一个表进行匹配，保留所有字段信息

select * from student cross join class;

②内链接([inner] join)：从左表取出每一条记录，去右表中与所有的记录进行匹配

select * from student AS s inner join class AS c on s.c_id=c._id;

③外链接(out join)：以某张表为主，取出里面的索引记录，然后每条与另外一张表进行链接，不管是否能匹配都保留最终结果，能匹配的数据进行正常保留，不能匹配的字段值为null

④左链接(left join)：以左表为主表

select s.*,c.name AS c_name from student AS s left join class AS c on s.c_id=c.id;

⑤右链接(right join)：以右表为主表

select s.*,c.name AS c_name from student AS s right join class AS c on s.c_id=c.id;

⑥自由链接(natural join)：自动匹配链接条件，系统以字段作为匹配模式(同名字段就作为条件，多个同名字段都作为条件)

⑦自由内链接:

select * from student natural join class;

⑧自由外链接(natural left/right join):

select * from student natural left/right join class;

2.联合查询：将多处查询（多条select语句），在记录上进行拼接（字段不会增加）

①多条select语句构成：每一条select语句获取的字段都必须严格一致（与字段类型无关）

--select 语句1 Union[union 选项] select 语句2....
--Union选项：同select都有两个
--------------ALL：保留所有数据（包含重复）--select默认
--------------Distinct：去重（对整个执行结果），默认  --Union默认，select需要配置参数

select * from class unio select * from class;--默认去重
select * from class union all select * from class; --不去重

②order by 的使用

--1.order by 不能直接使用，需要对查询语句使用括号才行；

--2.若要使 order by 生效，必须搭配limit

(select * from student where sex='男' order by Age ASC limit 9999) nuion (select * from student where sex='女' order by Age ASC limit 9999);

3.子查询：一条select语句内部包含了另外一条select语句

①.两种分类方式：

方式一：按位置分类，子查询(select语句)  在外部查询(select语句) 中的位置

-- from子查询：子查询跟在from之后；

-- where子查询：子查询出现在where中；

-- Exists子查询：子查询出现在exists里面；

方式二：按结果分类，根据子查询得到的数据进行分类；

-- 标量子查询：子查询得到的结果是一行一列（where之后）；

-- 列子查询：子查询得到的结果是一行多列（where之后）；

-- 行子查询：子查询得到的结果是多列一行（多行多列）--（where之后）；

-- 表子查询：子查询得到的结果是多行多列（from之后）；

②标量子查询：获取班级为007下的所有学生

select * from student where id=(select id from class where c_name='007');

③列子查询：查询所有在读班级的学生（列子查询返回的结果较多，需要用in作为条件匹配，还有all、some、any）

select * from student where c_id in(select id from class);
select * from student where c_id=any(select id from class);
select * from student where c_id=some(select id from class);
select * from student where c_id=all(select id from class);

④行子查询：返回的结果阔以是多行多列（一行多列）

select * from student where (Age,height)=(select MAX(Age),Max(height) from student) at limit 1;--查询年龄最大且身高最高的学生

⑤表子查询：子查询返回的结果是多行多列的二维表，返回的结果是当做二维表来使用

--找出每个班身高最高的学生
--select * from student order by height DESC;
--select * from student group by c_id;
select * from(select * from student order by height DESC) as my_student group by c_id;

⑥Exists子查询：是否存在；用来判断默写条件是否满足（跨表）

select exists(select * from student); --返回1
select * from student where exists(select * from class);

4.外键：foreign key

①创建外键：

foreign key(外键字段) rederences (主键字段); --一张表可以有多个外键
create table IF NOT EXISTS my_forname(
ID int not null primary key auto_increment,
Name varchar(20) not null,
c_id int  comment'班级ID',
foreign key(c_id) references class(id) --增加外键class(id)指向班级表的外键引用
)charset utf8;

②在新增表之后添加外键：修改表结构

Alter table 表 add[constraint 外键名] forigm key(外键字段) references 引用表(主键字段);
Alter table my_forname add constraint student_class_1 foreign key(c_id) references class(id);

③删除外键：外键不可修改，只能先删除后新增

Alter table 表 drop foreign key 外键名; --从创建语句中产看c_id的外键名称进行删除，同样的删除不能从表结构中看到，从创建语句中进行查看

④外键的约束：

方式一：对子表约束，子表数据进行写操作的时候，如果对应的外键字段在父表找不到对应的匹配，那么操作失败（结束字表数据操作）

insert into student values(null,'tom',4); --在class中没有班级id=4，外键错误

方式二：对父表约束，父表数据进行写操作(删喝改，都必须涉及到主键本身)，如果对应的主键在子表中已经被数据所引用，那么就不允许操作

insert into student values(null,'tom',4);
insert into student values(null,'jore',1);
update class set id=4 where id=1; --id=1已经被引用

⑤外键条件

--1.外键要存在：首先必须保证表的存储引擎是innodb(默认)，不是的话，外键可以创建成功，但是没有约束效果

--2.外键字段的字段类型(列类型)必须与父表的主键类型完全一致

--3.同一张表中外键名字不能重复

--4.增加外加的字段(数据已经存在)，必须保证数据与父表主键的对应

⑥约束模式：主要针对父表的约束

--1.District：严格模式(默认)，父表不能删除或者更新一个呗子表数据引用的记录

--2.Cascade：级联模式，父表的操作，对应字表关联的数据也跟着操作更新

--3.Set null：置空模式，父表的操作之后，子表对应的数据（外键字段）被置空

⑦合理的约束模式：删除的时候字表置空(NULL)，更新的时候字表级联操作

foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;
cerate table if not exists my_forname(
ID int not null peimary key auto_increment,
Name varchar(20) not null,
c_id int comment'班级id',foreign key(c_id) references class(id),--增加指向班级表的外键引用
on delete set null,  --指定删除模式
on update cascade --指定更新模式
)charset utf8;

--1.更新操作：级联更新

--2.删除置空的条件：外键字段允许为空（如果不满足条件，外键无法创建）

5.视图

①.创建视图

create view 视图名字 AS select 语句;
--单表视图
create view v1 AS  select * from student;

--多表视图
create view v2 as select * from student AS s left join class AS c on s.c_id=c.id;

--解决sql写法
create view v2 AS select s.*,c_name,c.room from student AS s left join class AS c on s.c_id=c.id;

②.查看视图：查看视图的结构（虚拟表，表的所有查看方式都使用于视图的查询），结构文件为.frm的视图文件

--show create view 视图名;
show create view v1\g;

③.使用视图

--1.查询视图：

select * from v1;

--2.修改视图：修改视图本事的来源语句（select语句）

--Alter vier 视图名字 AS 新的视图名字 select语句
Alter vier V1 as select id,name,age,heigth,c_id form student; --去掉了student表中的sex字段

--3.删除视图：

drop view v1;

--4.视图的意义：

-- 视图可以节省sql语句，将一复杂的查询语句使用视图进行保存，以后阔以直接对视图进行操作；

-- 数据安全：视图存储的只是表结构，不会有数据存储，对原数据的安全性较高；

-- 视图往往是在大项目中进行使用，而且是多系统使用，可以对外提供有用的数据，但是隐藏关键数据，提高数据隐私安全；

-- 视图可以更好（容易）的进行权限控制；

④.视图的操作：新增

--1.数据新增就是直接对视图进行新增数据，在使用insert语句想数据表中插入数据时，用户必须有插入数据的权限；

***多表视图不能新增数据（一个视图有两个或者两个以上的表结构构成）***

--2.单表视图插入数据：视图中包含的字段必须有基表中所有不能为空（或者没有默认）的字段；

***该字段允许为空***

***该字段没有默认值***

***改字段是标识字段，可根据标识种子和标识增量自动填充数据***

***改字段的数据类型为timestamp或uniqueudentifier***

--3.视图中不能包含多个字段值的组合，或者包含使用统计函数的结果；

--4.视图不能包含distinct或group by 子句；

--5.如果视图中使用了with check option，那么该子句将检查插入的数据是否符合视图定义中select语句所设置的条件，如果插入的数据不符合该条件，拒绝插入数据；

--6.不能在一个语句中对多个基础表使用的数据进行修改，因此，如果要引用多个数据表的视图添加数据时，必须使用多个insert语句进行添加；

desc v1; --查看视图字段信息
insert into v1 values('tom',1); 

⑤.删除视图

--1.多表视图不能删除数据

--2.可删除单表视图

delete from v1;

--3.对视图的数据进行删除会影响基表数据，谨慎操作；

⑥.更新视图

--1.更新语句，视图只能修改查询中所能看到的数据

update v1 set Name='tom' where id=1;

--2.更新限制

-- with check option，如果对视图在新增的时候，限定了某个字段有限制，呢么在对视图进行数据更新操作时，系统会进行验证，要保证更新之后数据依然阔以被实体查询出来，否则不让更新

--查询年龄>30的数据，并且不能修改<30的数据
create view v1 AS select * from student where Age>30 whith check option;

⑦.视图算法：系统对视图以及外部查询视图的select语句的一种解析方式

--1.视图算法分为三种：

算法一：Undefined：未定义（默认的）；

算法二：Temptable：临时算法（系统应该先执行视图的select语句，后执行外部查询语句）；

算法三：Merge：合并算法（系统应该先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行（效率高：常态））；

--2.算法指定：在创建视图的时候

create algorithm = 指定算法方式 view v1 AS select 语句；
--获取所以班级中最高的一个学生
create algorithm = Temotable view v1 AS select * from student order by height DESC;

--3.视图算法的选择：如果视图的select语句中包含一个查询子句（五子句），而且很有可能顺序比外部的查询语句要靠后，使用指定算法Temptable，其他情况可以不用指定（默认即可）

-- 五子句：where、having、group by、order by、limit

6.单表数据备份

①.从表中选出一部分数据保存到外部的文件中（outfile）

--select * /字段名 into outfile '文件路劲' from 数据表;
ues student;
select * into outfile 'D:/class/class.txt' from class; 

②.高级备份：自己制定字段和行的处理方式

select */字段名 into outfile '文件路劲' fields 字段处理 lines 行处理 from 数据表;

--1.fields：字段处理

-- enclosed by：字段要用什么内容包裹，默认是“ ”空字符串

-- escaped by：特殊符号用什么方式处理，默认是“\\”，使用反斜杠转义

-- Terminnated by：字段以什么结束，默认是“\t”，tab键

--2.Lines：行处理

-- starting by：每行以什么开始，默认是“ ”，空字符串

-- Terminnated by：每行以什么结束，默认是“\r\n”，换行符

select * into outfile 'D:/class/class.txt'
fields
--字段处理
enclosed by '"' --数据使用双引号包裹
Terminnated by '|' --使用竖线分割字段数据
--行处理
lines
starting by 'START'
from class;

③.数据还原：将一个再外部保存的数据重新恢复到表中（如果表结构不存在则无法还原）

Load data infile '文件路劲' into table 表名[(字段名)] fiels 字段处理 lines 行处理;
--怎么备份怎么还原
Load data infile 'D:/class/class.txt' into table calss 
  fields 
--字段处理
  enclosed by '"' --数据使用双引号包裹
  Terminnated by '|'  --使用竖线分割字段数据   
--行处理
  lines
  starting by 'START';

④.sql备份：备份的sql语句，系统会对比数据结果以及对数据进行处理，变成对应的sql语句，然后进行备份，还原的时候只要执行sql指令即可（主要针对表结构）

-- 1.mysql没有提供备份指令，需要使用mysql工具-mysqldump.exe（\bin下）

--2.使用方式：

-- mysqldump/mysqldump.exe -hPup 数据库名字[数据库1[数据库2]...]> 外部文件目录\.sql（建议使用.sql）

-- 3.单表备份：

mysqldump -uroot -proot mydatabase student > D:/sql备份/student.sql

-- 4.整库备份：

mysqldump -uroot -proot mydatabase > D:/sql备份/mysqldatabase.sql

⑤.sql 还原数据

-- 1.使用musql.exe客户端还原

mysql.exe/mysql -uroot -proot mydatabase < D:/sql备份/student.sql

--2.使用sql指令还原

Source D:/sql备份/student.sql;