DBS-Project2-SQL数据定义和操作

实验2-SQL数据定义和操作

###实验目的

• 掌握MySQL数据库的复杂SQL查询和DML语句的使用方法
• 掌握MySQL数据库的视图设计和使用方法。
• 掌握MySQL数据库的索引设计和使用方法。

实验平台

数据库管理系统（MySQL）

实验内容和要求

1、建立数据库school

CREATE DATABASE school;

2、数据的定义

利用create创建三张表。

CREATE TABLE IF NOT EXISTS S(
	SID VARCHAR(20) not NULL,
	SNAME VARCHAR(20) not NULL,
	AGE INT UNSIGNED,
	SEX VARCHAR(1)
);
CREATE TABLE IF NOT EXISTS SC(
	SID VARCHAR(20) not NULL,
	CID VARCHAR(20) not NULL,
	GRADE INT UNSIGNED
);
CREATE TABLE IF NOT EXISTS C(
	CID VARCHAR(20) not NULL,
	CNAME VARCHAR(20) not NULL,
	TEACHER VARCHAR(20) not NULL
);

用alter 增加主键和外键

ALTER TABLE S ADD PRIMARY KEY (SID);
ALTER TABLE SC ADD PRIMARY KEY (SID,CID);
ALTER TABLE C ADD PRIMARY KEY (CID);
ALTER TABLE SC ADD FOREIGN KEY (SID) REFERENCES S (SID);
ALTER TABLE SC ADD FOREIGN KEY (CID) REFERENCES C (CID);

3、数据插入

代码略

插入结果如下

4、数据查询

• ```mysql
  SELECT DISTINCT SNAME, AGE FROM S;
  SELECT DISTINCT SID FROM SC;
  SELECT DISTINCT SID FROM SC WHERE GRADE < 60;
  SELECT DISTINCT SNAME, SEX , AGE FROM S WHERE AGE BETWEEN 20 AND 23;
  SELECT DISTINCT SID, SNAME, AGE FROM S WHERE SNAME LIKE ‘liu%’;
  SELECT MAX(GRADE) FROM SC WHERE CID = ‘C1’;
  SELECT CID, COUNT(SID) FROM SC GROUP BY CID;
  SELECT DISTINCT SNAME FROM S NATURAL JOIN SC WHERE SC.CID = ‘C3’;
  SELECT DISTINCT SNAME FROM S NATURAL JOIN SC WHERE SC.CID = ‘C1’ and SC.GRADE > 90;
  SELECT CID, AVG(GRADE) FROM SC GROUP BY CID;
  SELECT DISTINCT CNAME FROM C WHERE TEACHER LIKE ‘%i%’;
  SELECT SID, CID FROM SC WHERE GRADE BETWEEN 80 and 90;
  SELECT

    CNAME, MAX( GRADE ) 
    FROM
        SC
    NATURAL JOIN C 
    GROUP BY
        CNAME 
    HAVING
        MAX( GRADE ) > 80;
  

  SELECT

    SID,SNAME,AGE,SEX 
  

  FROM

    (
        S
        NATURAL JOIN ( SELECT * FROM SC WHERE CID = 'C1' ) AS T1
        NATURAL JOIN ( SELECT CID, AVG( GRADE ) AS avg FROM SC GROUP BY CID ) AS T2 
    ) 
  

  WHERE

    GRADE > avg;
  

  	
  
  - 结果展示
  
  	a. ![image-20200320225619693](./DBS-Project2-SQL数据定义和操作/image-20200320225619693.png)
  
  	b. ![image-20200320225632891](./DBS-Project2-SQL数据定义和操作/image-20200320225632891.png)
  
  	c. ![image-20200320225649058](./DBS-Project2-SQL数据定义和操作/image-20200320225649058.png)
  
  	d. ![image-20200320225655825](./DBS-Project2-SQL数据定义和操作/image-20200320225655825.png)
  
  	e. ![image-20200320225702410](./DBS-Project2-SQL数据定义和操作/image-20200320225702410.png)
  
  	f. ![image-20200320225708522](./DBS-Project2-SQL数据定义和操作/image-20200320225708522.png)
  
  	g. ![image-20200320225714513](./DBS-Project2-SQL数据定义和操作/image-20200320225714513.png)
  
  	h. ![image-20200320225720273](./DBS-Project2-SQL数据定义和操作/image-20200320225720273.png)
  
  	i. ![image-20200320225733498](./DBS-Project2-SQL数据定义和操作/image-20200320225733498.png)
  
  	j. ![image-20200320225756334](./DBS-Project2-SQL数据定义和操作/image-20200320225756334.png)
  
  	k. ![image-20200320225828417](./DBS-Project2-SQL数据定义和操作/image-20200320225828417.png)
  
  	l. ![image-20200320225838289](./DBS-Project2-SQL数据定义和操作/image-20200320225838289.png)
  
  	m. ![image-20200320225844874](./DBS-Project2-SQL数据定义和操作/image-20200320225844874.png)
  
  	n. ![image-20200320225852895](./DBS-Project2-SQL数据定义和操作/image-20200320225852895.png)
  
  ##### 5、视图使用
  
  - ```mysql
  	CREATE VIEW S_C_SC as SELECT SID, SNAME, CNAME, GRADE FROM S NATURAL JOIN SC NATURAL JOIN C;
  	SELECT * FROM S_C_SC;
  	SELECT CNAME, GRADE FROM S_C_SC WHERE SNAME LIKE 'Wu%';
  	UPDATE S_C_SC SET SNAME = 'Hello' WHERE SID = 'S1';
  	SELECT * FROM S_C_SC;
  	SELECT * FROM S;
  	UPDATE S_C_SC SET SNAME = 'Wang feng' WHERE SID = 'S1';
  	DROP VIEW S_C_SC;

• 结果展示

  • 
  • 
  • 
  • 

6、索引的使用

• ```mysql
  SHOW INDEX FROM C;
  ALTER TABLE C ADD INDEX INDEX_CID(CID);
  SHOW INDEX FROM C;
  DROP INDEX INDEX_CID ON C;

  -   结果展示
  
  	- 
  
  	![image-20200321225603246](./DBS-Project2-SQL数据定义和操作/image-20200321225603246.png)
  
  	- ![image-20200321225717407](./DBS-Project2-SQL数据定义和操作/image-20200321225717407.png)
  
  	- 
  
  - 索引的使用可以提升查找数据的速度，不会改变表中数据
  
  
  
  ##### 7、数据更新
  
  - ```mysql
  	UPDATE C SET CID = 'C6' WHERE CID = 'C3';

  报错信息

  1451 - Cannot delete or update a parent row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`CID`) REFERENCES `c` (`CID`))

• ```mysql
  SET FOREIGN_KEY_CHECKS = 0;
  UPDATE C SET CID = ‘C6’ WHERE CID = ‘C3’;
  SET FOREIGN_KEY_CHECKS = 1;

  ​	   禁用外键约束即可删除
  
  - 题目中 “用update命令修改sc表中课由“Li”任课的课程号为由“Wen”任课的课程号” 要求会导致主键重复，故删除了改条数据
  
  - ```mysql
  	SET FOREIGN_KEY_CHECKS = 0; 
  	UPDATE C SET CID = 'C6' WHERE CID = 'C3';
  	SET FOREIGN_KEY_CHECKS = 1;
  	DELETE FROM SC WHERE SID = 'S3' AND CID = 'C1';
  	UPDATE SC NATURAL JOIN C SET SC.CID = (SELECT CID FROM C WHERE TEACHER = 'Wen') WHERE C.TEACHER = 'Li' ;
  	DELETE FROM SC WHERE GRADE < 60;
  	DELETE SC FROM SC NATURAL JOIN C WHERE C.CNAME = 'OS';
  	SELECT * FROM SC;

8、删除表和数据库

DROP TABLE S,C,SC;
DROP DATABASE school;