mysql 性别以女生升序_03.MySQL数据库练习-答案

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

```