MySQL查询语句使用

一、在school数据库中建立四个表：

student/teacher/course/score

mysql> create table student(
    -> sno varchar(20) primary key,
    -> sname varchar(20)not null,
    -> ssex varchar(10)not null,
    -> sbirthday datetime,
    -> class varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table teacher(
    -> tno varchar(20) primary key,
    -> tname varchar(20) not null,
    -> tsex varchar(20) not null,
    -> tbirthday datetime,
    -> prof varchar(20) not null,
    -> depart varchar(20) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table course(
    -> cno varchar(20) primary key,
    -> cname varchar(20) not null,
    -> tno varchar(20) not null,
    -> foreign key(tno) references teacher(tno)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create table score(
    -> sno varchar(20) not null,
    -> cno varchar(20) not null,
    -> degree decimal,
    -> foreign key(sno) references student(sno),
    -> foreign key(cno) references course(cno),
    -> primary key(sno,cno)
    -> );
Query OK, 0 rows affected (0.02 sec)

 

二、插入数据

insert into 表名 values();

 

三、查询数据

1.查询student表中所有记录sname,ssex,class列：

select sname,ssex,class from student;

 

2.查询教师所有单位中不重复的depart列：(distinct排除重复)

select distinct depart from teacher;

 

3.查询score表中成绩在60～80之间的所有记录：(查询区间between..and../直接使用运算符比较)

select *from score where degree between 60 and 80;

或select *from score where degree>60 and degree<80;

 

4.查询score表中成绩为85，86或88的记录：(表示或者关系的查询 in)

select *from score where degree in(85,86,88);

 

5.查询student表中“95031”班或性别为女的同学记录：

select *from student where class='95031' or ssex='女';

 

6.以class降序查询student表的所有记录：(升序asc/降序desc)

select *from student order by class desc;

默认是升序所以一般不会写

 

7.以cno升序、degree降序查询score表的所有记录：

select *from score order by cno asc,degree desc;

 

8.查询“95031”班的学生人数：(统计count)

select count(*) from student where class='95031';

 

9.查询score表中的最高分的学生学号和课程号：(子查询/排序)

select sno,cno from score where degree=(select max(degree) from score);

或select sno,cno from score order by degree desc limit 0,1;     //降序方式取第一个，即从0开始查一条，如果是limit 0,2则表示从0开始查两条

 

10.查询每门课的平均成绩：(avg平均+group by分组)

如果是一门课的平均成绩：select avg(degree) from score where cno='3-105';

但如果是所有课：select cno,avg(degree) from score group by cno;

 

11.查询score表中至少有2名同学选修并且以3开头的课程的平均成绩：

先筛选出至少有2名同学选修并且以3开头的课程：

select cno from score

group by cno     //以cno进行分组

having count(cno)>=2     

and cno like'3%';

加上平均成绩：select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like'3%';

加上每门课有几人选：select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like'3%';