多表查询:
当需要查询的数据在多张表中,需要多张表同时查询。
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 视图名;