基础查询(6个子句)
执行过程:from->where->group by->having->select->order by
高级查询_子查询
非关联子查询:
a. 执行过程: 执行子查询(独立 一次)->
结果返回(单值、多值)-> 执行主查询
b. 比较符选择: = =any
c. 多值多列(比较规则相同)
关联子查询:
a. 执行过程: 执行主查询(传递数据)->
执行子查询(依赖数据)->
结果返回 -> 再执行主查询
b. exists关键字(题目满足某种关系条件 )
组合查询:
规则:组合查询由两条或两条以上的 select 语句组成,中间使用 union 分隔
条件:两个结果集必须有相同的结构(列的个数、列顺序、列类型)
– 查询10号部门的员工姓名和薪水
select ename,salary
from emp_xu
where deptno=10;
-- 查询薪水大于6000的员工姓名和薪水
select ename,salary
from emp_xu
where salary>6000;
-- 组合查询:只能使用一个 order by 进行排序,跟在最后一个select后面
select ename,salary
from emp_xu
where deptno=10 union
select ename,salary
from emp_xu
where salary>6000; //union 会自动去重
Mysql分页查询(重点):
limit 限定查询记录数
语法:SQL
select 字段 from 表 limit 数量;
或者
select 字段 from 表 limit 开始,数量; //常用
select empno,ename from emp_xu limit 2,2; //开始表示从下标为0
– 找规律
假设每页显示5条记录
select empno,ename from emp_xu limit 开始,5;
第一页 0-4 limit 0,5
第二页 5-9 limit 5,5
第三页 10-14 limit 10,5
第四页 15-19 limit 15,5
page:第几页
pageSize:每页的记录数
– 计算公式
int beginValue= (page-1)*pageSize
分页查询核心SQL语句:
select * from emp_xu limit beginValue,pageSize;
表间关联查询(多表联合查询)
内连接
语法: 表1 inner join 表2 on 条件
-- 查询员工姓名和其部门的名字
select ename,dname
from emp_xu e inner join dept_xu d
on e.deptno=d.deptno;
ps:
内连接的结果集中数据一定是在两个表中都能找到的匹配记录(内连接的结果集保留匹配上的记录)
select ename,dname
from emp_xu e,dept_xu d
where e.deptno=d.deptno; //如果不加where条件关联,得到笛卡尔积结果
-- 查询员工姓名和其领导的名字
select e1.ename,e2.ename
from emp_xu e1 inner join emp_xu e2
on e1.leader=e2.empno; //表1和表2可以互换
说明:表1 join 表2 on 条件
a. 表1为驱动表,表2为匹配表
b. 执行过程:
遍历驱动表在匹配表中找匹配记录
匹配上的记录保留,匹配不上的记录去掉
c. 等值连接方式(条件中使用等号),驱动表和匹配表可以互换不影响结果
-- 查询员工的姓名和其部门的名字,要求没有部门的员工也要被查询出来
select ename,dname
from emp_xu e join dept_xu d
where e.deptno=d.deptno union
select ename,'No dept'
from emp_xu
where deptno is null;
外连接:(严格区分哪个表为驱动表)
语法:
-- 左外连接 左边的表为驱动表
表1 left [outer] join 表2 on 条件
-- 右外连接 右边的表为驱动表
表1 right [outer] join 表2 on 条件
-- 查询员工的姓名和其部门的名字,要求没有部门的员工也要被查询出来(员工表作为驱动表)
分析:查询全部员工 -> 外连接 -> 员工表作为驱动表
select ename,ifnull(danme,'No dept') danme
from emp_xu e left join dept_xu d
on e.deptno=d.deptno;
select ename,ifnull(danme,'No dept') danme
from dept_xu d right join emp_xu e
on e.deptno=d.deptno; //
ps:
外连接特点:
驱动表中中数据全部出现在外连接的结果集
如果驱动表中的数据在匹配表中找不到匹配记录,则匹配一行空行
外连接结果集=内连接结果集(匹配上的记录)+驱动表在匹配表中匹配不上的记录(匹配不上的记录)
-- 查询部门名称和员工姓名,要求没有员工的部门也要被查询出来(部门表作为驱动表)
select dname,ifnull(ename,'No ename') ename
from emp_xu e right join dept_xu d
on e.deptno=d.deptno;
-- 查询哪些部门没有员工(查询全部部门,部门表作为)
select d.deptno,dname,ename
from dept_xu d left join emp_xu e
on e.deptno=d.deptno
where ename is null;
外连接的本质:驱动表中数据全部出现在外连接的结果集中
注意点:
a. 不要关联不必须的表,处理关联表非常消耗资源
b. 关联的表越多,可能导致性能下降、
c. 获取同样的结果,可能存在很多SQL实现,找最优方式
约束条件:
1)主键约束:
主键:(primary key 简称PK)
主键=不能重复+不能为空
一张表只能有一个主键,主键可以是一列或多列组合
mysql支持主键自增: primary key auto_increment表示自增
两种定义方式:列级和表级
-- 列级:
create table stu_xu(
id int primary key,
name varchar(10)
);
insert into stu_xu values(1001,'李四');
insert into stu_xu values(1001,'张三'); //不能重复
-- 如果id 有主键自增,id为null使用自增数据插入表
insert into stu_xu values(null,'李四'); //不能为空
-- 表级
create table stu_xu(
id int,
name varchar(10),
constraint stu_xu_id_pk primary key(id)
);
ps: stu_xu_id_pk 主键约束名字
表名_字段名_约束名
2)非空约束
非空
create table stu_xu(
id int primary key auto_increment,
name varchar(10) not null
);
insert into stu_xu values(1001,'李四');
insert into stu_xu values(1002,null); //不能为空
3)唯一约束
唯一(unique 简称UK)
-- 列级
drop table stu_xu;
create table stu_xu(
id int primary key auto_increment,
name varchar(10) not null,
email varchar(20) unique
);
insert into stu_xu values(1001,'李四','123@qq.com');
insert into stu_xu values(1002,'张三','123@qq.com'); //不能重复
insert into stu_xu values(1002,'张三',null);
insert into stu_xu values(1002,'张三',null);
-- 表级
drop table stu_xu;
create table stu_xu(
id int primary key auto_increment,
name varchar(10) not null,
constraint stu_xu_email_uk unique(email)
);
insert into stu_xu values(1001,'李四','123@qq.com');
insert into stu_xu values(1002,'张三','123@qq.com'); //不能重复
insert into stu_xu values(1002,'张三',null);
insert into stu_xu values(1002,'张三',null);
4)外键约束
外键(foreign key 简称FK)
外键约束定义在两张表的两个字段上,用于保证这两个字段的关系
如果表A中主键字段是表B中字段,则该字段称为表B的外键。表A为主表,表B为从表
A B
部门表 员工表
deptno(主键) empno(主键)
deptno
-- 创建部门表
create table temp_dept(
deptno int primary key auto_increment,
danme varchar(10) not null
);
create table temp_emp(
empno int primary key auto_increment,
ename varchar(8) not null,
deptno int,
constraint temp_emp_deptno_fk
foreign key(deptno)
references temp_dept(deptno)
);
-- 插入数据
insert into temp_emp values(1001,'张三',)