我们不生产代码,但我们是bug的专业制造者

Mysql练习题1

1.创建数据表

1
2
3
4
5
6
7
CREATE TABLE Student (
Sno CHAR(3) NOT NULL UNIQUE PRIMARY KEY,
Sname CHAR(8) NOT NULL ,
Ssex CHAR(2) NOT NULL,
Sbirthday DATETIME,
Class CHAR(5) NOT NULL
);
1
2
3
4
5
CREATE TABLE Course (
Cno CHAR(5) NOT NULL,
Cname VARCHAR(10) NOT NULL,
Tno CHAR(3) NOT NULL
);
1
2
3
4
5
6
CREATE TABLE Score (
Sno CHAR(3) NOT NULL,
Cno CHAR(5) NOT NULL,
Degree DECIMAL(4,1)
);
1
2
3
4
5
6
7
8
CREATE TABLE Teacher (
Tno CHAR(3) NOT NULL UNIQUE,
Tname CHAR(4) NOT NULL,
Tsex CHAR(2) NOT NULL,
Tbirthday DATETIME,
Prof CHAR(6),
Depart VARCHAR(10) NOT NULL
);
1
CREATE TABLE grade(low INT(3),upp INT(3),rank CHAR(1));

2.向创建的四个表中插入数据

1
2
3
4
5
6
7
INSERT INTO Course VALUES('3-105','计算机导论','825');
INSERT INTO Course VALUES ('3-245','操作系统','804');
INSERT INTO Course VALUES ('6-166','数字电路','856');
INSERT INTO Course VALUES ('9-888','高等数学','831');
1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO Score VALUES ('103','3245','86');
INSERT INTO Score VALUES ('105','3245','75');
INSERT INTO Score VALUES ('109','3245','68');
INSERT INTO Score VALUES ('103','3245','92');
INSERT INTO Score VALUES ('105','3105','88');
INSERT INTO Score VALUES ('109','3105','76');
INSERT INTO Score VALUES ('101','3105','64');
INSERT INTO Score VALUES ('107','3105','91');
INSERT INTO Score VALUES ('108','3105','78');
INSERT INTO Score VALUES ('101','6166','85');
INSERT INTO Score VALUES ('107','6166','79');
INSERT INTO Score VALUES ('108','6166','81');
1
2
3
4
INSERT INTO Teacher VALUES ('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO Teacher VALUES ('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO Teacher VALUES ('825','王平','女','1972-05-05','助教','计算机系');
INSERT INTO Teacher VALUES ('831','刘冰','女','1977-08-14','助教','电子工程系');
1
2
3
4
5
6
7
8
9
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'C');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');

3.查询语句练习

  • 以Cno升序、Degree降序查询Score表的所有记录

    1
    select * from score order by cno,degree desc;
  • 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

    1
    select avg(degree) from score a where (select sum(sno) from score b where a.cno=b.cno)>=5 && cno like '3%';
  • 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

    1
    select * from student,score where student.Sno=score.Sno&& cno='3105' && degree>(select degree from score where sno='109'&&cno='3105');
  • 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

    1
    select sno,cno,degree from score where cno='3105'&& degree >(select min(degree) from score where cno='3245' ) order by degree desc;
  • 查询至少有2名男生的班号。

    1
    select class from student group by class having (select count(sno ) from student where ssex="男" )>=2;
  • 查询每门课的平均成绩。

    1
    select avg(degree) from score group by cno;
  • 查询选修某课程的同学人数多于5人的教师姓名。

    1
    select tname from teacher where tno in (select tno from course where cno in ( select cno from score group by cno having count(cno )>5 ) );
Jquery_w3c入门

  1. 1. 1.创建数据表
  2. 2. 2.向创建的四个表中插入数据
  3. 3. 3.查询语句练习