select distinct stuId from result;--相同的学号只取一次
select score,distinct stuId from result;--distinct只能在开头,不能在中间
select distinct stuId from result order by stuId desc;--会把学生编号降序排列
select r.*,@a:=@a+1 from result r,(select @a:=0) s;--嵌套语句增加行键
--s是(select @a:=0)这个临时表的表别名
select * from result where score between 80 and 90 and score not in (80,90);
--查询80-90分之间的成绩记录
select * from result where not (score<80 or score>90);
--(score<80 or score>90)表示小于80,大于90.not表示取反,结果就是取介于80-90之间的值
select * from result where stuId not in (select stuId from student);
--筛选不在学生表范围之内的学生Id
select * from subject where ClassHour is not null;
--筛选课程时长非空的记录
select * from result where stuId not in (select stuId from student);
--筛选不在学生范围表内的学生id
select stuId as '学生ID', score '成绩' from result where stuId in
(select stuId from student where address like '%学院');
--1.设置stuid的别名为学生id,score的别名为成绩
--2.从表中筛选地址包含学院的学生id
--3.在主表中查找学生id和成绩,筛选条件是学生id符合从表的学生id
select stuName '学生姓名', score '成绩' from result r
join student s on r.stuId=s.stuId
where address like '%学院';
--筛选学生姓名和成绩联合学生表中stuid和主表stuid相同,且地址包含学院的学生信息
select GradeName , stuName from student
join grade on student.gradeId =grade.gradeId
where GradeName='大一'
--查找年级名和学生姓名,联合连个表的年级Id,筛选条件是大一学生
select a.stuName 警官幼儿园, b.stuName 警官托儿所
from student a join student b
where a.address='警官幼儿园' and b.address='警官托儿所';
--从学生a表和学生b表中,查找两张表的地址为警官幼儿园和警官托儿所的学生学生姓名
select r.stuId, stuName '学生姓名',score '成绩' from result r
join student s on r.stuId=s.stuId;
--结合s表,从r表中筛选出两个表相同学生id学生成绩项
select r.stuId , stuName '学生姓名',score '成绩' from result r
right join student s on r.stuId=s.stuId;
--右连接
select r.stuId , stuName '学生姓名',score '成绩' from result r
right join student s on r.stuId=s.stuId;
--左连接
insert into category values (1,"软件开发",0),(2,"美术设计",0),(3,"数据库基础",0),
(4,"PhotoShop",0),(5,"PHP基础",0),(6,"一起学java",0),(7,"色彩搭配学",0),
select a.categoryName ,b.categoryName from category a
join category b on a.categoryId=b.pid;