【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

借着这次实验,复习一下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; 

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

尝试修改表。

/*向Student表增加“入学时间”列,其数据类型为日期型。*/
ALTER TABLE Student ADD S_entrance DATE;
/*将年龄的数据类型改为整数。*/
ALTER TABLE Student MODIFY COLUMN Sage INT;
/*增加课程名称必须取唯一值的约束条件。*/
ALTER TABLE Course ADD UNIQUE(Cname); 

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

若要删除,因为要考虑完整性约束条件,则应该按照SC、Course、Student的顺序来删除,否则会有 a foreign key constraint fails 的报错(当然也可以加级联删除CASCADE )。

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

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;

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

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);

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)
【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

然后尝试修改数据

/*学生年龄全部加一*/
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';

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

然后删除数据

DELETE FROM Student WHERE Sno='2017004';
DELETE FROM SC WHERE Sno='2017012' AND CNO='1011';

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

3.1.2 完成各类查询操作(单表查询,连接查询,嵌套查询,集合查询)

单表查询

SELECT Sno,Sname,Sdept FROM Student;
SELECT Sname,Ssex,Sage FROM Student WHERE Sage>=20;

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

连接查询

/*查询计科所有学生的选课*/
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;

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

嵌套查询

/*查询数据库成绩不低于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
            )
    );

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

集合查询

/*查询计科的学生以及选修了数据库的学生*/
SELECT Sname FROM Student WHERE Sdept='CS'
UNION 
SELECT Sname FROM Student WHERE sno IN(
    SELECT sno FROM SC
    	WHERE cno='1002'
);

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

另外,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;

【学习笔记】温习sql及MySQL的使用(数据库第2次实验)

视图的更新

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操作

上一篇:学习数据库概论第六天


下一篇:精度丢失、截取字符串、返回坐标、替换