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'); ```