SQL常用语句之DQL语言

select

单表查询

select * from students;  --查询students整张表的数据
select stuid,name,age from students;  --在students表中查询stuid,name,age字段的全部信息
select name as 姓名,age 年龄,stuid from students; --给字段加上中文别名，as可加也可以不加
select * from students where stuid = 20;  --查询students表中stuid为20的行信息
select * from students where stuid > 20;  --查询students表中stuid大于20的行信息
select * from students where stuid <> 20;  --查询students表中stuid不等于20的行信息
select * from students where stuid != 20;  --查询students表中stuid不等于20的行信息
select * from students where age>10 and gender='f';  --查询students表中年龄大于10岁且为女生的信息
select * from students where age >= 20 and age <= 30;  --查询students表中年龄大于等于20且小于等于30的人
select * from students where age between 20 and 30;  --等同于上面的写法
select * from students where name like 's%';  --查询students表中name字段s开头的任意行信息
select * from students where name like '%yu%';  --查询students表中name字段包含yu的任意行信息
select distinct age  from students ;  --查询students表中age字段，并把重复的内容合并
select * from students where classid is null; --查询students表中classid为空值的行的信息
select * from students where classid is not null; --查询students表中classid不为空值的行的信息
select count(stuid) from students;  --查询students表中stuid不为空的值有多少个
select max(age) from students;   --查询students表中年龄最大的数值
select min(age) from students;   --查询students表中年龄最小的数值
select avg(age) from students;   --查询students表中年龄的平均数值
select gender,avg(age) as 记录数 from students group by gender;  --查询students表中平均年龄，按性别进行分组
select classid,avg(age) as 记录数 from students group by classid having classid >3;  --查询students表中classid大于3的，以classid作为分组的班级平均年龄
select classid,gender,avg(age)  from students group by classid,gender;  --查询students表中以classid和gender作为分组的平均年龄。是一种多组查询
select * from students order by age desc;  --查询students表，以age字段倒序来排列，默认是正序
select * from students order by -classid desc;  --查询students表，以classid字段正序排列，且把null值放到末尾，仅针对数字
select classid,sum(age) from students group by classid order by -classid desc;  --查询students表，先针对classid字段进行分组并取年龄的总数，然后进行排序
select classid,sum(age) from students group by classid order by -classid desc limit 3;  --查询students表，先针对classid字段进行分组并取年龄的总数，然后进行排序，并取出前三个
select classid,sum(age) from students group by classid order by -classid desc limit 2，3;  --查询students表，先针对classid字段进行分组并取年龄的总数，然后进行排序，并跳过前两个取出后三个
select * from students where classid in (1,3,5);  --查询students表classid为1,3,5的值

多表查询

纵向合并

select stuid,name,age,gender from students union select * from teachers;  --把学生表的stuid,name,age,gender四个字段和教师表的所有字段进行纵向合并
select * from teachers union all  select * from teachers;  --把教师表进行合并，不去重，不加all可以做到去重功能
select * from students cross join teachers;  --把学生表的记录和教师表的记录进行交叉连接，让表一的每一行单独和表二进行组合，工作中不常用
select * from students inner join teachers on students.teacherid=teachers.tid;  --匹配学生表中教师ID和教师表中教师id相同的数据，内连接方式
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s inner join teachers as t on s.teacherid=t.tid; --表中只显示学生表的id，姓名和年龄，教师表的id，姓名和年龄，并通过给两个表做别名，减少显示复杂度，通过内连接方式，匹配学生表中教师ID和教师表的教师ID相等的数据
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid;  --两张表中取出学生表和教师表有交集的部分以及学生表的所有信息，显示学生表的id，姓名和年龄，教师表的id，姓名和年龄，若没有对应的信息填null，左外连接方式
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right outer join teachers as t on s.teacherid=t.tid;  --两张表中取出学生表和教师表有交集的部分以及教师表的所有信息，显示学生表的id，姓名和年龄，教师表的id，姓名和年龄，若没有对应的信息填null，右外连接方式
select s.stuid,s.name student_name,s.age student_age,t.tid,t.name teacher_name,t.age teacher_age from students as s inner join teachers as t on s.teacherid=t.tid and s.age > 30; --表中只显示学生表的id，姓名和年龄，教师表的id，姓名和年龄，并通过给两个表做别名，减少显示复杂度，通过内连接方式，匹配学生表中教师ID和教师表的教师ID相等的数据，并筛选学生年龄大于30的数据
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is null;  --先执行左外连接，然后判断把tid是空值得去掉
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right outer join teachers as t on s.teacherid=t.tid where s.teacherid is  null;  --先执行右外连接，然后判断把teacher是空值得去掉
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s full outer join teachers as t on s.teacherid=t.tid;  --学生表和教师表所有数值都要，但是mysql不支持
select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid union select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right outer join teachers as t on s.teacherid=t.tid ;  --mysql可以做到的完全外连接，先左外连接，然后联合查询做右外连接合并
select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left outer join teachers t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from students as s right outer join teachers as t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;  --取教师表和学生表的所有值但是去掉两张表之间交集的值
select st.name,co.course,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.CourseID;  --学生表的学生ID和成绩表的学生ID进行内连接匹配，然后把课程表与成绩表的课程id也同样进行内连接匹配，然后对三张表取学生表的姓名，课程表的课程名称以及成绩表的成绩，进行取值查询

合并的表格数据类型一样，字段的数量一样

子查询

select * from students where age >(select avg(age) from students);  --把年龄的平均值当做参数被调用到另一个select查询中进行查询

自连接

seletct e.name,l.name from emp as e left join emp as l on e.leaderid=l.id;  --把员工表自己做两个别名，让表中的领导ID和员工ID等值作为判断进行查询员工对应的领导