SQL测试test

-- 排序
select * from stu group by age asc
select * from stu group by math desc
select * from stu group by math desc,english asc   -- 如果数学成绩一样，再按英语升序

-- 分组查询
select 字段名 from 表名 [where 分组前条件限定] group by [having 分组后条件过滤]
-- 分组后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
-- where和having的区别
-- where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对对结果进行过滤
-- where > 聚合函数 > having
select sex,avg(math) from stu group by sex
select sex,avg(math),count(*) from stu group by sex
select sex,count(*),avg(math) from stu where math>70 group by sex

select sex,avg(math),count(*) from stu where math>70 
group by sex having count(*)>2
 




-- 约束的分类
-- not null,unique,primary key,check,default,foreign key
 
-- 外键约束
drop table if exists emp
drop table if exists dept
create table dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
) ;

create table emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,
	-- 添加外键，外键在主表关联
	constraint fk_emp_dept foreign key(dep_id) references dept(id)
)
-- 删除外键
-- alter table emp drop foreign key fk_emp_dept




-- 多表查询
-- select * from emp,dept
-- 隐式内连接
select * from emp,dept where emp.dep_id = dept.id   
-- 显示外连接
select * from emp join dept on emp.dep_id = dept.id
-- 外连接，左外连接
select * from emp left join dept on emp.dep_id = dept.id
select * from emp right join dept on emp.dep_id = dept.id




-- 子查询
select avg(age) from emp
select * from emp where age <= (select avg(age) from emp)

select * from emp where age = 18 or age = 22
select * from emp where id in(select id from emp where name = '张三' or name = '王五')

select * from emp where age>=20;
select * from (select * from emp where age>=20) t1 where t1.dep_id = dept.id