一、在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);