mysql数据库5.20作业笔记

最新推荐文章于 2023-10-06 16:20:11 发布

serendipity★

最新推荐文章于 2023-10-06 16:20:11 发布

阅读量365

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/zhangbao20001223/article/details/117198630

版权

本文详细讲解了如何在MySQL 5.20环境中创建数据库、设置外键约束、操作表数据、连接查询以及删除和修改外键关联。涉及的内容包括表结构定义、数据插入、删除和修改，以及不同类型的SQL查询，如交叉连接、内连接、左连接和右连接。

摘要由CSDN通过智能技术生成

mysql5.20作业

```首先创建数据库 mysql

CREATE DATABASE xx;




## /*1、先定义两个表在字段，为表student添加外键约束。*/

```mysql
CREATE TABLE student(sid INT(4) PRIMARY KEY NOT NULL,sname VARCHAR(20),sex VARCHAR(4),age INT(4),did INT(4) NOT NULL);


CREATE TABLE department(did INT(4) PRIMARY KEY NOT NULL,NAME VARCHAR(20));

/2、按照表3，表4，添加数据。/

INSERT INTO student VALUES(1,'zhangyong','男',21,1),
	(2,'zhanghong','女',22,1),
	(3,'zhaogao','男',21,1),
	(4,'xuyang','男',19,2),
	(5,'xusong','男',21,2),
	(6,'yangqing','女',20,3),
	(7,'wangming','女',23,3),
	(8,'songwenqian','女',24,5),
	(9,'sunwei','男',25,6);

INSERT INTO student VALUES (8,'songwenqian','女',24,5),
	(9,'sunwei','男',25,6);


INSERT INTO department VALUES(1,'英语系'),(2,'计算机系'),(3,'护理系'),(5,'中文系'),(6,'会计系');

/3、将计算机系删除。/

ALTER TABLE student ADD FOREIGN KEY(did) REFERENCES department(did);

DESC student;

SHOW CREATE TABLE student;

SELECT * FROM student;

SELECT * FROM department;

/4、将表student中的外键约束删除。/

/删除外键表中计算机系的学生数据/

DELETE FROM student WHERE did=2;

/* 删除计算机系*/

DELETE FROM department WHERE NAME='计算机系';

/删除外键约束/

ALTER TABLE student DROP FOREIGN KEY student_ibfk_1;

SHOW CREATE TABLE student;

/* 5、按照下面表格将student表中did的数据修改后再完成后面操作 */

UPDATE student SET did=4 WHERE sid=6;

UPDATE student SET did=4 WHERE sid=7;

UPDATE student SET did=7 WHERE sid=9;

SELECT * FROM student;

/6、使用交叉连接查询院系表和学生表中的所有数据。/

SELECT * FROM student JOIN department;

/* 7、在department表和student表之间使用内连接查询。 */

SELECT department.`NAME`,student.`sname`FROM department JOIN student ON department.`did`=student.`did`;

/8、查询zhangyong所在的院系有哪些学生。/

SELECT P1.*FROM student p1 JOIN student p2 ON p1.`did`=p2.`did`WHERE p2.`sname`='zhangyong';

/9、在department表和student表之间使用左连接查询。 /

SELECT department.`did`,department.`NAME`,student.`sname` FROM department LEFT JOIN student ON department.`did`=student.`did`;

/10、在department表和student表之间使用右连接查询。/

SELECT department.`did`,department.`NAME`,student.`sname`FROM department RIGHT JOIN student ON department.`did`=student.`did`;

SELECT * FROM department;

SELECT * FROM student;

/11、在department表和student表之间使用内连接查询，并将查询结果按照年龄从大到小进行排序。/

SELECT student.`sname`,student.`age`,department.`NAME` FROM department JOIN student ON department.`did`=student.`did` ORDER BY age;

/12、查询存在年龄为21岁的学生的院系。/

```mysqlSELECT * FROM department WHERE did IN(SELECT did FROM student WHERE age=21);

SELECT * FROM student;

/13、查询student表中是否存在大于20岁的员工，如果存在，则查询department表中的所有记录。/

SELECT * FROM department WHERE EXISTS(SELECT did FROM student WHERE age>21);

/14、使用ANY关键字的子查询，查询满足条件的院系。/

SELECT * FROM department WHERE did>ANY(SELECT did FROM student);

/15、使用ALL关键字的子查询，查询满足条件的院系。/

SELECT * FROM department WHERE did>ALL(SELECT did FROM student);


SELECT * FROM department;

SELECT * FROM student;

DELETE FROM student WHERE did>3;

/16、使用带比较运算符的子查询，查询songwenqian是哪个院系的学生。/

SELECT * FROM department WHERE did=(SELECT did FROM student WHERE sname='songwenqian');