1.查询stu01表中,全部列的数据 ``` select * from stu01; ``` 2.查询cou01表中,课程编号、课程名称 ``` select * from cou01; ``` 3.查询sco01表中,学员编号、学员成绩并给每列起别名 ``` select sno as 学员编号,garde as 成绩 from sco01; ``` 4.查询sco01表中,学员编号、学员成绩,根据学员成绩降序排列 ``` select sno,garde from sco01 order by garde desc; ``` **5.**查询**sco01表中,学员编号、学员成绩,根据学员成绩降序排列,如果成绩一致,根据学员编号升序排列** ``` select sno,garde from sco01 order by garde desc,sno asc; ``` 6.查询sco01表中,课程编号并去重 ``` select distinct cno from sco01; ``` 7.查询stu01表中,学员编号是2019005的,学员的编号、学员姓名、学员年龄 ``` select sno,sname,age from stu01 where sno=2019005; ``` 8.查询stu01表中,学员姓名是Jones的,学员的编号、学员姓名、学员地址 ``` select sno,sname,address from stu01 where sname='jones'; ``` 9.查询stu01表中,学员姓名含有o的,学员的编号、学员姓名、学员性别 ``` select sno,sname,age from stu01 where sname like '%o%'; ``` 10.查询stu01表中,学员姓名首字母以J开头的,学员编号、学员姓名 ``` select sno,sname from stu01 where sname like 'j%'; ``` 11.查询sco01表中,学员成绩在70~90之间的,学员的编号、学员成绩 ``` select sno,garde from sco01 where garde between 70 and 90; ``` 12.查询sco01表中,学员成绩不在70~90之间的,学员的编号、学员成绩 ``` select sno,garde from sco01 where garde not between 70 and 90; ``` 13.查询stu01表中,学员编号是2019001、2019003、2019009的学员信息 ``` select * from stu01 where sno in(2019001,2019003,2019009); ``` 14.查询stu01表中,除了学员编号是2019001、2019003、2019009的其他学员信息 ``` select * from stu01 where sno not in (2019001,2019003,2019009); ``` 15.查询stu01表中,学员姓名不含有o的或者地址是北京的学员信息 ``` select * from stu01 where sname not like '%o% ' or address='北京'; ``` 16.查询stu01表中,学员的总数量 ``` select count(*) from stu01; ``` 17.查询sco01表中,每个学员的平均成绩 ``` select avg(garde) from sco01; ``` **18.**查询stu01表中,男生和女生的人数**** ``` select sex,count(*) from stu01 where sex is not null group by sex; ``` **19.查询stu01表中,2018年入学的学生名单,根据学员编号降序排列** ``` select * from stu01 where year(etime)='2018' order by sno desc; ``` **20. 查询sco01表中,选修两门课程的学员学号** ``` select sno from sco01 group by sno having count(*)=2; ``` 21.删除stu01表中,学员编号是2019012,2019008,2019006的学员信息 ``` delete from stu01 where sno in(2019012,2019008,2019006); ``` **22.查询emp表中,和员工blake是同一个部门的,查询其他员工的编号,姓名,职位,部门编号(deptno)** ``` select deptno,empno,ename,mgr from emp where deptno=(select deptno from emp where ename=’blake’) and ename !='blake '; ``` **23.查询课程编号(cno)是1002的,学员的编号(sno),姓名(sname),年龄(age),地址(address)** 1) 求出学员编号 ``` select sno from sco01 where cno='1002'; ``` 2) 查询学员信息 ``` select sno,sname,age,address from stu01 where sno in(select sno from sco01 where cno='1002'); ```