MySQL数据库（四）

多表查询：

​ 当需要查询的数据在多张表中，需要多张表同时查询。
​ select 字段 from 表1，表2 where 条件;
​ 注意：条件是多表查询的基础，它是多张表联合在一起的依据，多表查询时如果不写条件会产生笛卡尔积

​ 查询出每位员工的姓名、工资、部门id、部门名。
​ select first_name,salary,dept_id,name from s_dept,s_emp where dept_id=s_dept.id;
​ 注意：如果在多张表中有相同的字段，可以使用表名.字段名加以区别。

​ select s_dept.name,s_region.name from s_dept,s_region while region_id=s_region.id;

取别名：

​ 给字段取别名，可以提高查询结果的可读性；
​ select 字段名 as 别名 from 表名;

​ 给表取别名，可以精简查询语句;
​ select 别名.字段 from 表名 as 别名;
​ select d.name,r.name from s_dept as d,s_region as r while region_id=r.id;

分组查询：

​ 按照一定的标准给数据进行分组，然后合理的使用组函数，查询出需要的数据。
​ select 字段 from 表名 group by 字段;
​ 查询出每个部门的员工数量？

​ select count(last_name),dept_id from s_emp,s_dept where dept_id=s_dept.id group by dept_id;
​ 查询出部门员工数量大于的部门名和部门ID？

​ having对分组数据过虑，它相当于select的where。
​ select 字段 from 表名 group by 字段 having 条件;
​ select count(last_name),dept_id,s_dept.name form s_emp,s_dept where dept_id=s_dept.id group by dept_id count(last_name)>3;

SQL语句的执行顺序：

​ select 组函数(字段),字段
​ from 表名
​ where 条件
​ group 分组标准
​ having 过虑分组后的数据
​ order by 排序标准。

select count(last_name),max(salary),min(salary),avg(salary),s_dept.name ,dept_id
    from s_emp,s_dept
        where dept_id=s_dept.id
            group by dept_id
                order by avg(salary);

连接查询：

​ 多表查询，通过调用where条件进行查询方式叫连接查询，当有值时连接查询分为。
​ 内连接 inner join，出现的结果一一对应，等值连接。
​ 左连接 left join ，出现的结果是内连接+左表未对应的数据
​ 右连接 right join，出现的结果是内连接+右表未对应的数据
​ 全连接 full join MySQL数据库不支持，再现的结果是内连接数据+左、右表未对应的数据。
​ select 字段 from 左表 [] join on 连接条件;
​ 查询出每位员工的姓名、工资、部门id、部门名，外加未分配部门的员工。
​ select first_name,salary,dept_id,name from s_dept right join s_emp on dept_id=s_dept.id;
​ 查询出公司所有的领导层？
​ 如果一个员工的ID是另一个员工的管理ID则说明该员工是管理层。
​ select id,userid from s_emp where id=manager_id;

子查询

​ 把一个查询的结果做为另一个查询的基础，这种查询叫子查询。
​ select id,userid from s_emp where id in (select manager_id from s_emp);

约束

​ 是一种限制，用于保障表中数据的合法性，在创建表和修改表时使用。

六大约束：

​ not null 非空，用于保证字段的值不能为空。
​ default 默认值，用于给字段设置默认值。
​ primary key 主键，用于保证字段的值唯一且非空。
​ unique 唯一，用于保证字段的值唯一不重复
​ check 检查，用于给字段值设置检查条件，当不符合条件时无法插入(MySQL数据库不支持).
​ foreign key 外键，用于限制两个表的关系，该字段的值必须来自另一张表的数据。

​ 表级约束：针对表的约束，not null 和 default 无法设置表级结束。
​ 列级约束：针对字段的约束，所有约束都能使用，外键约束除外。

方法一：创建表时设置约束

create table 表名(
    字段名 字段类型 列级约束,
    ... 
    表级约束(字段名)
);

create table Student(
    id int primary key,
    name char(20) not null,
    sex char default 'm',
);

方法二：修改表时设置约束

​ alter table 表名 modify 字段 列类型 列级约束。
​ alter table 表名 add 表级约束(字段);

外键约束的使用方法：

​ 外键约束就是一张表的字段引用自另一张表的字段值，被引用的表的字段必须具备唯一性(主键)。
​ 然后可以限制子表的取值是父表中的值。
​ 1、先创建父表

create table 表名(
        字段 类型 primary key,
    );

create table class(
    id int primary key,
    name char(20) unique
);

​ 2、再创建子表

create table 表名(
    ...
    foreign key(字段) references 父表名(字段)
);

create table Student(
    id int primary key,
    name char(20) not null,
    sex char default 'm',
    class_id int,
    foreign key(class_id) references class(id) on delete cascade on update cascade
);

​ 3、插入数据
​ 先插入父表中的数据，再插入子表中的数据。

​ 4、更新或删除数据
​ 先子表中的数据删除，父表中的数据才能删除。
​ on delete cascade 级联删除
​ on update cascade 级联更新

​ alter table 表名 add 表级约束(字段);
​ alter table Student add foreign key(class_id) references class(id) on delete cascade;

标识列

​ 又称为自动增长列，不需要手动插入，由系统提供默认的序列值。
​ 特点：
​ 1、标识列必须与主键搭配使用。
​ 2、一张表最多有一个标识列。
​ 3、标识列的类型必须是数值型。
​ 4、通过set auto_increment_increment=n;设置步长(全局)
​ 5、在第一次插入时通过手动插入标识列的值设置起始值。

1、创建表时设置标识列

create table 表名(
	字段 类型 primary key auto_increment,
);

2、修改表时设置标识列

​ alter table 表名 modify 字段 类型 primary key auto_increment;
​ alter table class modify id int primary key auto_increment;

索引

​ 索引是一种提高查询效率的一种技术，如果把汉语字典看作数据库，索引就是它的目录。
​ 但是缺点也很明显，就是会额外消耗存储空间，并且插入、删除、更新数据的速度会降低。
​ 注意：MySQL数据中会自动为主键、外键设置索引。
​ 创建表时设置索引：

create table 表名(
	index [索引名](字段,...)
);

​ 注意：一般常用于查询条件的字段创建索引。

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

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

查询索引：
show index from 表名;

视图

​ 视图(view)是一种虚拟表、逻辑表，它本身不包含数据，是一个select语句的保存在数据库。
​ 通过视图，可以只展现基础表的部分数据，视图的数据由自创建时视图所查询的表动态生成的。

​ 优点：
​ 简单，使用视图的用户不需要关心后面对应的表结构、关联条件和筛选条件，对于使用者来说它已经是过虑好的符合条件的结果集。
​ 安全，使用视图的用户只能访问被允许查询的结果集，因我们在管理表时中并不限制到某个列，但使用视图可以轻松实现。
​ 数据独立，一旦视图创建完成，可以屏蔽表结构变化对视图用户的影响(源表增加列、删除列、修改列，都不会对视图造成影响)。

​ 缺点：相比真实表，视图的速度比较慢，而且只能查询不能修改。
​ 创建视图：
​ create view 视图名 [字段名,字段名,…] as select 语句

​ create view view_emp as select s_emp.id,last_name,salary,dept_id,s_dept.name from s_emp,s_dept where dept_id=s_dept.id;

​ 删除视图：
​ drop view 视图名;