两个例子学习MySQL

student

Sno	SNAME	AGE	SEX
201215121	李勇	20	男
201215122	刘晨	19	女
201215123	王敏	18	女
201215125	张立	19	男
201215126	王平	17	女

 

Course

Cno	CNAME
C1	数据库
C2	MATHS
C3	信息系统
C4	操作系统
C5	数据结构
C6	数据处理
C7	PASCAL语言

 

SC

Sno	Cno	GRADE
201215121	C1	92
201215121	C2	85
201215121	C3	88
201215122	C2	90
201215122	C3	80

 

创建Sudent学生表:

create table Student

(

    Sno smallint PRIMARY KEY,  //列级设置主键

    Sname char(20),

    Sage smallint,

    Ssex char(2),

    CHECK (Ssex IN('男','女'))     //checkj约束，表示检查，表明Ssex属性只能从'男''女'中选择

);

输入数据：

insert into student(Sno,Sname,Sage,Ssex)

values(201215121,'李勇',20,'男'),

(201215122,'刘晨',19,'女'),

(201215123,'王敏',18,'女'),

(201215124,'张力',19,'男'),

(201215125,'王平',17,'女');

创建Course表:

create table Course

(

       Cno VARCHAR(20) PRIMARY KEY,

    Cname VARCHAR(20)

);

输入数据：

insert into course(Cno,Cname)

values('C1','数据库'),

('C2','MATHS'),

('C3','信息系统'),

('C4','操作系统'),

('C5','数据结构'),

('C6','数据处理'),

('C7','PASCAL语言');

创建SC表:

create table SC
(
 Sno INT,
 Cno VARCHAR(20),
  grade SMALLINT,
 PRIMARY KEY (Sno,Cno),  //表级设置主键，当有两个及以上主键时必须用表级设置主键
 FOREIGN KEY (Sno) REFERENCES student(Sno)    //外键
  ON DELETE CASCADE   /*当删除student元组时，级联删除SC相应的元组*/
  ON UPDATE CASCADE, /*级联更新*/

 FOREIGN KEY (Cno) REFERENCES course(Cno)  //外键

  ON DELETE NO ACTION /*当删除course元组照成与sc不一致时，拒绝删除*/
  ON UPDATE CASCADE  /*级联更新*/
);

输入数据：

insert intosc(Sno,Cno,grade)

values(201215121,'C1',92),

(201215121,'C2',85),

(201215121,'C3',88),

(201215122,'C2',90),

(201215122,'C3',80);

注意：输入数据时，表SC有两个外键，所以先输入表student和course的数据，再输入SC的数据，否则有可能出错。

问题：

（1）检索选修课程名称为“MATHS”的学生的学号与姓名

SELECT student.Sno,Sname

FROM student,course,sc

WHERE student.Sno=sc.Sno AND sc.Cno=course.Cno and course.Cname='MATHS';

注：可以用嵌套查询

（2）检索至少学习了课程号为“C1”和“C2”的学生的学号

分析：可以用嵌套查询，先查询学习了c2的学生有哪些，再从中查询学了c1课程的学生学号

SELECT Sno                              /*外查询是从子查询返回的结果中查询*/

FROM sc

WHERE sc.Cno='c1' AND Sno IN 

(SELECT Sno                           /*子查询返回SC表中学习了c2课程的学生学号*/

FROM sc

WHERE sc.Cno='c2'

);

注意：这里为什么用IN，而不用 '=' ,子查询里面返回的是好几条数据，而不是一个数据，不能以一对多。

（3）检索年龄在18到20之间（含18和20）的女生的学号、姓名和年龄

SELECT student.Sno,student.Sname,student.Sage

FROM student

WHERE student.Ssex='女' AND student.Sage>=18 AND student.Sage<=20;

（4）检索平均成绩超过80分的学生学号和平均成绩

SELECT sno,AVG(grade)

FROMsc x

WHERE(

SELECT AVG(grade)

FROMsc y

WHERE y.sno=x.sno

)>=80;

 

（5）检索选修了全部课程的学生姓名

SELECT Sname

FROM student

WHERE NOT EXISTS

(

 SELECT *                                                       //  这里得到的是以course表的列的元组

FROM course

WHERE NOT EXISTS(                                               //NOT EXISTS表示中层查询的内容是把子查询的结果排除在外的内容

SELECT *

FROM sc

WHERE Sno=student.Sno AND cno=course.Cno            //最内层是以SC为中心把3个表连接在一起,其中把某一列没有数据的元组去了

));

结果为空

 注：NOT EXISTS表示不返回子查询的结果为真，也就是子查询得结果为空时为真，注意它返回的是真假值而不是数据，

EXISTS与它相反。外查询在子查询为真时查询，为假是结果为空。

 

（6）检索选修了三门课及以上的学生的姓名

SELECT Sname

FROM sc,student

WHERE sc.Sno=student.Sno

GROUP BY sc.Sno

HAVING COUNT(*)>=3;

 

 

二.

student

Sno	SNAME	AGE	SEX
201215121	李勇	20	男
201215122	刘晨	19	女
201215123	王敏	18	女
201215125	张立	19	男
201215126	王平	17	女

 

Course

Cno	CNAME
C1	数据库
C2	MATHS
C3	信息系统
C4	操作系统
C5	数据结构
C6	数据处理
C7	PASCAL语言

 

SC

Sno	Cno	GRADE
201215121	C1	92
201215121	C2	85
201215121	C3	88
201215122	C2	90
201215122	C3	80

 建表和出入数据同第一题

（1）查询选修课程包括“1042”号学生所学的课程的学生学号

分析：要查询的是至少选修课选修了“1042“号学生学过的全部课程的学生学号

SELECT DISTINCT SNO       //DISTINCT是去重复

FROM SC SCX                     //用到三次SC表，重命名
 WHERE NOT EXISTS
   (SELECT * FROM SC SCY
           WHERE SCY.SNO = '1042' 
 AND NOT EXISTS
    (SELECT * FROM SC SCZ
               WHERE SCZ.SNO=SCX.SNO AND
               SCZ.CNO=SCY.CNO));

2）创建一个计科系学生信息视图S_CS_VIEW，包括Sno学号、Sname姓名、Sex性别

CREATE VIEW S_CS_VIEW(Sno,Sname,Sex)
AS
SELECT student.Sno,Sname,Sex
From student
WHERE Sdept='CS';

（3）通过上面第2题创建的视图修改数据，把王平的名字改为王慧平

UPDATE S_CS_VIEW
SET Sname='王慧平'
WHERE Sno=1047;

 

（4）创建一选修“数据库”课程信息的视图，视图名称为datascore_view，包含学号、姓名、成绩。

CREATE VIEW database_view(Sno,Sname,Grade)
AS
SELECT student.Sno,Sname,sc.Grade
FROM student,sc,course
WHERE course.Cname='数据库' AND sc.Cno=course.Cno;