MySQL数据库学习笔记03（多表查询，取别名，分组查询，子查询，连接查询，约束，外键，标识列，索引，视图）

1、多表查询
当需要查询的数据在多张表中，这种查询叫多表查询。
select 字段 from 表1,表2 where 条件。
注意：一定要where条件，否则会产生笛卡乐积。
练习1：班级编号是2086的属于哪所学校，查出学校的名字。
练习2：查询出每个班级的班主任的姓名，教授的课程。

2、取别名
给字段取别名，提高查询结果的可读性。
select 字段 as 别名 from 表名;

给表取别名，可以精简SQL语句。
select 别名.字段 from 表名 as 别名;

3、分组查询
select 字段 from 表名 group by 字段;
一般配合组函数使用
练习3：查询出每个学校班级的数量
练习4：查询出学校id为1006的班级的数量
当需要对分组后的数据进行过虑，having 相当于 select 的 where 子句。

语句的顺序：
    select 组函数(字段)
        from 表名
            where 条件
                group by 分组标准
                    having 条件
                        order by 排序标准

4、子查询
把一个查询的结果当作另一个查询的基础，这种查询叫子查询，原理就是把一个查询的结果当作一张虚拟的内存表。

练习5：查询出学号为4096的学生的五名任课老师的姓名。
select 字段 from [select] where 条件;

select tr.tch_id,tr.name from student as s,teaching as tg ,teacher as tr where s.stu_id=4096 and s.class_id = tg.class_id and tg.tch_id = tr.tch_id;

select tch_id,name from teacher where tch_id in (select tch_id from student,teaching where stu_id=4096 and student.class_id = teaching_class_id);

5、连接查询
多表查询时，where条件一一对应这种叫等值连接(内连接)。
内连接 inner join
左连接 left join
右连接 right join
全连接 union all
练习6：查询出每个班级的班级主任姓名、班级编号，并显示出没有分配班主任的班级。

select class_id,name from class left like join teacher on class.tch_id=teacher.tch_id;

6、约束
一种对数据限制，用于保证表中数据的准备性和可靠性，可以在创建表时或修改表时使用。
not null 非空，要求字段的值不能为空。
default 默认值，用于相似较高或者数据值出现次较多的情况。
primary key 主键，要求该字段的值非空且唯一，一般用于学号、工号。
unique 唯一，要求该字段值不重复，比如：手机号，身份证号。
check 设置字段的检查条件，但是MySQL不支持。
foreign key 外键，要求该字段的值依赖其它表中的数据。

表级约束：给表中的多个字段同时设置某些约束。
列级线束：只给靓啊的某个字段设置某些约束。

方法一：
create table name(
    字段名 类型 列级约束,
    ...
    表级线束(字段1,字段2,...)
);

方法二：
alter table 表名 modify 字段 类型 列级约束。
alter table 表名 add 表级约束(字段1,字段2,...)；
注意：此时添加约束，表中可能已经有不符合约束的数据了，这种情况添加约束会失败。

7、外键
一张表(子表)的字段值依赖于自另一张表(父表)的字段值，被依赖的字段必须具备唯一性且非空。
一般先建父表，再建子表
create table 父表(
字段 类型 primary key,
…
);
create table 子表(
…
foreign key(字段) references 父表(字段) on delete cascade on update cascade
);
插入数据时，一般插入父表，再插入子表(子表中外键字段的值必须在父表中存在)。
删除或更新数据时，由于子表依赖父表中的数据，因此父表不随意删除，需要先删除子表中的数据，再删除父表中的的数据，或都设置子表的外支持级联更新，级联删除。

8、标识列
又叫自动增加列，可以不用手动插入，系统可以提供自动的序列值。
特点：
1、标识列必须与key搭配使用(key指的是主键、外键、唯一)。
2、一张表中只能设置一个标识列。
3、必须是整数型
4、可以设置步长 set auto_increment_increment = n;
方法一、创建表时设置
create table 表名(
字段 类型 primary key auto_increment
) auto_increment = 1;

方法二、修改表时设置
alter table 表名 modify 字段 auto_increment;
alter table 表名 auto_increment = 1;

修改表时删除标识列
alter table 表名 modify 字段;
注意：使用标识列并不能保证完全连接，数据在插入失败时，标识列的值依然后自动增加，此时就会形成标识列的黑洞，如果从中间删除一条数据，也会造成不连续。

9、索引
索引是一种提高数据库检索速度的一项技术。
相当于汉语词典的目录，先把关键字在个独立的地方(内存)列出，还有该记录在硬盘中的存储位置。

索引分为单列索引和组合索引，单列索引顾名思义只包含一个列，一个表中可以有多个单列索引，组合索引就是多个列组合在一起建立的索引。

创建索引时，应该确保该字段经常出现在查询语句的where子句中，否则没有必要创建索引。

但需要注意的是过渡使用索引会降低表的插入速度、更新速度、删除速度，还会浪费磁盘空间。

创建索引：
create table 表名(
    .....
    index [索引名] (字段) 
)

添加索引：
alter table 表名 add index [索引名](字段);

删除索引：
drop index 索引名 on 表名;

查看索引：
show index from 表名;

10、视图
视图是一种虚拟存在的表(逻辑表)，本身不包含数据，作为一个select语句保存在数据字典中，是由其它若干个基础表通过连接组合而成的。
使用视图的优点：
1、简单，使用视图的用户不需要关心基础表的表结构、关联条件、筛选条件，对于使用者来说视图就是过虑好的符合条件的结果集。
2、安全，使用者只能访问充他们访问的字段，表的权限管理可以精确控制以列为单位。
3、数据独立，一旦视图结构确定，在原表中增加列不影响视图的使用。
创建视图：
create view <视图名> as <select语句>;
注意：创建创建完成后就可以当表格使用了，但只能查询。
删除视图：
drop view 视图名;