mysql 视图 联合查询_mysql之旅_外键_联合查询_子查询_视图

回顾4

连接查询:

连接多张表到一起，不管记录数如何，字段数一定会增加。

分类：内连接，外连接。自然连接，交叉连接，

交叉连接：cross join (笛卡尔积)

内连接：inner join，左右两张表有连接条件匹配(不匹配自动忽略)

外连接：left/right join 主表的一条记录一定会存在匹配保留副表数据，否则置空

自然连接：natural join 自动匹配(相同字段名)

PHP操作：mysql

Php充当客户端，开启mysql扩展

连接认证：mysql_connect;执行sql

获取结果:mysql_query;

解析结果：mysql_fetch系列

释放资源：mysql_free_result和mysql_close

错误处理：mysql_ermo和mysql_error

外键

Foreign key 外面的键

如果一张表中有一个字段(非字段)指向另一张表的主键，那么将该字段称之为外键

增加外键：

外键可以在创建表的时候或者在创建表之后增加(但是要考虑数据问题)一张表有多个外键

1)创建表的时候增加外键：在所有的表字段之后使用：

Foreign key (外键字段) references 外部表(主键字段)

例：

Crate table My_c (

c_id int

Foreign key (c_id) references

My_class

) character utf8;

在新增之后增加外键；修改表结构

Alter table表名 add foreignkey(外键字段)

References 父表(主键字段);

例

Alter table my_foreign add

Constraint student_class_1

Foreign key (c_id)

Reference my_class(id);

修改&删除外键

外键不可修改；只能先删除后新增

删除外键

Alter table 表名 drop foreignerkey 外键名;–一张表有个外键但是外键名称不能一样。

例：alter table my_foreigner drop foreigner key

My_foreigner_id;

注意：外键删除是不能通过查看表结构体现出来，查看创建语句查看；

外键作用

外键默认作用：一个对父表，一个对子表(外键所在表)

对字表的约束：字表数据进行操作的时候如果对应的外键字段在父表找不到对应的匹配操作时便(约束字表数据)

对父表约束：父表数据进行写操作(删与改：主键)如果对应主键在字表中已经被数据引用，那么不允许操作。

外键条件

1，外表要存在：首先要保证表的存储引擎是innodb(默认存储引擎)如果不是innodb，外键创建成功，但是没有约束效果。

2，外键字段的字段类型(列属性)必须与父表的主键类型完全一致。

3，一张表中外键名称不能重复

4，增加外键的字段(数据已经存在)，必须保证数据与父表主键要求一致。

外表约束

所谓外表约束就是指外键的作用

之前将外键作用：是默认作用，其实可以通过对外键的需求爱，进行定制操作。

外键约束有三种模式：针对父表。

District ：严格模式(默认的)父表不能删除或者更新一个已经被子表数据引用的数据。

Cascade：级联模式，父表的操作，对应子表关联的数据也跟着操作。

Set null: 置空模式，父表的操作之后，子表的数据被置空。

约束模式：

删除的时候子表置空，更新的时候子表级联操作。

指定模式的语法：foreign 可以(外表字段)references 父表(主键字段)on delete 模式 update 模式;

更新操作：级联操作

删除操作：置空

删除置空前提条件：外键字段允许为空(如果不满足条件)，外键无法创建。

外键很强大，能够进行各种约束；但对php来讲，外键约束降低了php对数据的可控性，但是实际开发中，很少用外键来处理。

联合查询

联合查询：将多次查询(多条select语句)在记录上进行拼接(字段不会增加)。

语法：多条select语句构成，每条select语句获取的字段必须严格一致(但是字段类型无关)。

Select 语句1

Union [union选项]

Select语句2…

Union 选项有两个

All：保留所有

Distinct：去重。默认的

联合查询只要求字段一样，与数据类型无关。

意义：两种

1，查询一张表但是需求不同。

2，多表查询，多张表的结构完全一样，保存的数据也一样

Order by

使联合查询中order by 不能直接使用需要对查询语句使用括号。

例：

(select * from my-class where sex=’男’ order by age asc limit 999)

Union

(select * from my-class where sex=’女’ order by age desc limit 999);

若要order by生效，必须搭配limit ，limit最大值。

子查询

子查询：subquery 查询是在某个查询结果之上进行的(一条select语句含另一条select语句)

子查询分为两种：按位置分，按结果分

按位置分类：子查询select语句在外部查询

Select语句出现的位置

Form子查询：子查询跟在form后面。

Where 子查询：子查询出现在where条件中

Exists子查询:子查询出现在exists里面

按结果分类：根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结构都可以理解为二维表)

标量查询：子查询得到的结果是一行一列

列子查询：子查询得到的结果是一行多列

行子查询：子查询得到的结果是一行多列(多行多列)

表子查询：子查询得到的结果是多行多列(出现位置在form之后)

标量子查询

例：需求：知道名字php获取学生

Select *from my_class where c_id =(select id from my_student where c_name=php);

列子查询

例：需求查询所有在读班级的学生(班级表存在的班级)

1，确定数据源：学生

Select * from my_sutdent where c_id in (?)

2,确定有效班级id，所有班级id

Select id from my_class;

合：Select * from my_sutdent where c_id in (Select id from my_class);

列子查询返回的结果比较：一行多列，需要使用in作为条件匹配，其实mysql还有几个类似：all ，some，any===in；

行子查询：

行子查询返回的结果可以是多行多列(一行多列)、

例：需求:要求查询整个学生中年龄最大，身高最高的学生

1，确定数据源

Select *from my_student where age =? and heigth=?;

2,确定最大的年龄和最高的身高

Select max(age)，max(height) from my_student;

行子查询：需要构造行元素，行元素多个字段构成

Select * from my_student where (age,height)=(select max(age),max (height)from my_student);

表子查询：

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

例：需求：找出每班最后一个的学生

1，确定数据源；先将学生安装身高进行降序或者是升序

Select *from my_student order by height desc ;

2，从每个班选出第一个学生

Select * from my_student group by c_id;

表子查询：from子查询得到的结果作为from的数据源。

Exists 子查询

Exists 是否存在，exists子查询就是用来判断某些条件是否满足(跨表)exists是接在where之后，exists返回的结果只有0和1

例：查询所有的学生，前提条件是班级存在

1，确定数据源

Select *form my_student where?

2,确定条件是否满足

Exists (select * from my_class)–是否成立

视图

视图：view是一种有结构的但是没有结构来源的虚拟表，虚拟表的结构来源不是自己定义的而是从对应的基表中产生(来源)

创建视图

基本语法：

Create view 视图名字 as select 语句

可以是普通查询，也可以是连接查询，联合查询，子查询。

创建单表视图：

基表只有一个

例：create view my_view as select *from my_class;

创建多表查询

基表有多个

查看视图：

查看视图结构

视图是一张虚拟表，表的查看方式适用于

视图：show tables ,desc 表名

Show create table 表名\G

视图与表还是有一个关键字的区别：view

查看表(视图)的创建语句的时候可以使用view关键字

视图一旦创建：系统就会在视图对应的数据库文件夹下创建一个对应的结构文件：frm文件

使用视图

使用视图：主要是为了子查询：将视图做为表一样

查询：视图的执行：其实本质就是执行封装的select语句

修改视图：

视图本身不可修改，但是视图的来源可以修改

修改视图：修改视图本身的来源语句(select 语句)

Alter view视图名字 as 新的select语句

例：alter view my_view as select id,name,age,sex from my_stu;

删除视图

Drop view 视图名字;

视图的意义

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

2，数据安全：视图操作是主要针对查询，如果对视图结果进行处理(删除)不会影响基表数据(相对安全)

3，视图往往是在大型项目中使用，而且是多系统使用，对外提供有效有用的数据隐藏关键(无用的)数据，数据安全。

4，视图对外提供友好型，不同的视图提供不同的数据，对外好像是专门设计

5，视图可以更好地进行权限控制

视图数据操作

视图可以进行很多数据写操作：但是有限制，将数据直接在视图上进行操作

新增数据

数据新增直接对数据进行新增

1，多表数据不能进行新增操作

2，可以向单表视图插入数据，但是视图中包含的字段必须有基表所不能为空(或者没有默认值)字段

3，视图可以向基表插入数据(不应该给权限)

删除数据

1，多表视图不能删除数据

2，单表视图可以删除数据(对基表进行操作)‘delete from my_view2 where id=2’

更新数据

理论上单表与多表视图都可以更新数据库

Update my_view1 set id=3 where id=2;

更新限制：with check option 如果对视图在新增的时候，限定了某个字段有限制；那么在对视图进行数据更新操作之后，数据依然可以被实体查询出来，否则不更新。

例：select * from my_stu where age>30 with check option—表视图数据来源年龄不能>30岁，with check option 决定更新的时候不能将age >30改成<30。

视图算法

系统对视图以及外部查询视图select语句的一种解析方法。

视图算法有三种

1，undefind：未定义(默认的)，这不是实际使用算法，推卸责任的算法告诉系统，视图没有定义算法，自己看着办吧！

2，Temptable:临时算法，系统应该先执行视图的select语句，后执行外部查询语句

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

算法指定：创建视图的时候

Create algorithm =指定算法 view 视图名字 as select 语句;

视图算法的选择

如果视图的select语句中包含一个查询子句(五子句？？？)而且很有肯比外部查询语句要靠后，一定要使用temptable 其他情况不指定。