借着这次实验,复习一下sql语言。
实验二:交互式SQL
文章目录
1.实验目的
-
熟悉数据库的交互式SQL工具。
-
熟悉通过SQL对数据库进行操作。
-
完成作业的上机练习。
2.实验工具
Windows10
5.7.22 MySQL Community Server (GPL)
3.实验过程
实验用例就取教材上最经典的学生-课程的数据表,包括学生表Student,课程表Course,学生选课表SC。
3.1 数据定义
3.1.1 熟悉基本表的创建、修改及删除
先来练习创建表。
创建学生表Student:
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
)DEFAULT CHARSET=utf8;
创建课程表Course:
CREATE TABLE Course
( Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4) ,
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
)DEFAULT CHARSET=utf8;
创建学生选课表SC:
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
)DEFAULT CHARSET=utf8;
尝试修改表。
/*向Student表增加“入学时间”列,其数据类型为日期型。*/
ALTER TABLE Student ADD S_entrance DATE;
/*将年龄的数据类型改为整数。*/
ALTER TABLE Student MODIFY COLUMN Sage INT;
/*增加课程名称必须取唯一值的约束条件。*/
ALTER TABLE Course ADD UNIQUE(Cname);
若要删除,因为要考虑完整性约束条件,则应该按照SC、Course、Student的顺序来删除,否则会有 a foreign key constraint fails
的报错(当然也可以加级联删除CASCADE )。
3.1.2 熟悉索引的创建和删除
为学生-课程数据库中的Student,Course,SC三个表建立索引:Student表按学号升序建唯一索引,Course表按课程号升序建唯一索引,SC表按学号升序和课程号降序建唯一索引。
SC表按学号升序和课程号降序建唯一索引
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
尝试删除索引:
DROP INDEX Stusno ON Student;
DROP INDEX Coucno ON Course;
DROP INDEX SCno ON SC;
3.1.3 熟悉视图的创建和删除
CREATE VIEW CS_Student
AS
SELECT *
FROM Student
WHERE Sdept='CS';
DROP VIEW CS_Student;
3.2 数据操作
3.1.1 完成各类更新操作(插入数据,修改数据,删除数据)
先插入数据:
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('2017001','王五','男','CS',18);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('2017002','李明','男','CS',19);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('2017003','陈小明','男','CS',19);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('2017004','吴思思','女','CS',19);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('2017011','陈华','女','IS',18);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('2017012','杨晓红','女','IS',20);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('2017013','孙磊','男','IS',19);
INSERT INTO Course (Cno,Cname) VALUES ('1001','操作系统');
INSERT INTO Course (Cno,Cname) VALUES ('1002','数据库');
INSERT INTO Course (Cno,Cname) VALUES ('1011','信息安全');
INSERT INTO Course (Cno,Cname) VALUES ('1021','软件工程');
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017001','1001',80);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017001','1002',89);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017001','1021',78);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017002','1001',69);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017002','1002',75);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017003','1001',89);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017003','1002',85);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017011','1002',85);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017011','1011',78);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017012','1002',75);
INSERT INTO SC (Sno,Cno,Grade) VALUES ('2017012','1011',72);
然后尝试修改数据:
/*学生年龄全部加一*/
UPDATE Student SET Sage=Sage+1;
/*王五的操作系统成绩改为82*/
UPDATE SC SET Grade=82 WHERE Sno='2017001' AND Cno='1001';
/*所有的数据库成绩增加5*/
UPDATE SC SET Grade=Grade+5 WHERE Cno='1002';
然后删除数据:
DELETE FROM Student WHERE Sno='2017004';
DELETE FROM SC WHERE Sno='2017012' AND CNO='1011';
3.1.2 完成各类查询操作(单表查询,连接查询,嵌套查询,集合查询)
单表查询:
SELECT Sno,Sname,Sdept FROM Student;
SELECT Sname,Ssex,Sage FROM Student WHERE Sage>=20;
连接查询:
/*查询计科所有学生的选课*/
SELECT Sname,Cname
FROM Student,Course,SC
WHERE Sdept='CS' AND Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
/*查询数据库成绩不低于80的学生*/
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno AND Cno='1002' AND Grade>=80;
嵌套查询:
/*查询数据库成绩不低于80的学生*/
SELECT Sno,Sname
FROM Student
WHERE Sno IN (
SELECT Sno FROM SC
WHERE Grade>=80 AND Cno IN(
SELECT Cno From Course
WHERE Cname='数据库'
)
);
/*查询每科成绩都不低于平均分的学生*/
SELECT Sname FROM Student
WHERE NOT EXISTS(
SELECT * FROM SC sc1
WHERE Student.Sno=sc1.Sno AND Grade<(
SELECT AVG(Grade)
FROM SC sc2
WHERE sc1.Sno=sc2.Sno
)
);
集合查询:
/*查询计科的学生以及选修了数据库的学生*/
SELECT Sname FROM Student WHERE Sdept='CS'
UNION
SELECT Sname FROM Student WHERE sno IN(
SELECT sno FROM SC
WHERE cno='1002'
);
另外,MySQL似乎不支持INTERSECT(交集)和EXCEPT(差),可以分别用 INNER JOIN 和 LEFT JOIN 代替。
3.3 视图的操作
以计科学生的信息的视图为例。
创建视图:
CREATE VIEW CS_Student
AS
SELECT *
FROM Student
WHERE Sdept='CS';
删除视图:
DROP VIEW CS_Student;
视图的查询:
SELECT * FROM CS_Student;
视图的更新:
1、CREATE OR REPLACE VIEW语句修改视图
基本格式:create or replace view view_name as select语句
在视图存在的情况下可对视图进行修改,视图不在的情况下可创建视图
2、ALTER语句修改视图
ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]VIEW view_name [(column_list)]AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
是指修改数据库中已存在的表的定义,当基表的某些字段发生改变时,可以通过修改视图来保持视图和基本表之间一致。
3、DML操作更新视图。
( DML 即数据操作语言,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除)
因为视图本身没有数据,因此对视图进行的dml操作最终都体现在基表中。
有下列内容之一,视图不能做DML操作:
①select子句中包含distinct
②select子句中包含组函数
③select语句中包含group by子句
④select语句中包含order by子句
⑤select语句中包含union 、union all等集合运算符
⑥where子句中包含相关子查询
⑦from子句中包含多个表
⑧如果视图中有计算列,则不能更新
⑨如果基表中有某个具有非空约束的列未出现在视图定义中,则不能做insert操作