05.MySQL之多表操作

xiaoxiao2021-02-28  8

外键:

准备:创建两张表,一个班级表和一个学生表 #建库

CREATE DATABASE chapter05;

#建表

USE chapter05;

CREATE TABLE grade(    id int(4) NOT NULL PRIMARY KEY,    name varchar(36) ); CREATE TABLE student(    sid int(4) NOT NULL PRIMARY KEY,    sname varchar(36),    gid int(4) NOT NULL);

(1)为表添加外键约束

#student添加外键约束

alter table student add constraint FK_ID foreign key(gid) REFERENCES grade (id);

#查看学生表和班级表desc grade;

desc student;

#查看表的详细结构show create table student; (2)删除外键约束 #student中的外键约束删除

alter table student drop foreign key FK_ID;

2.操作关联表

(1)添加数据 #为表student和表grade中添加外键约束来建立两个表的关联关系

alter table student add constraint FK_ID foreign key(gid) REFERENCES grade (id);

#添加数据INSERT INTO grade(id,name)VALUES(1,'软件一班');INSERT INTO grade(id,name)VALUES(2,'软件二班');INSERT INTO student(sid,sname,gid)VALUES(1,'王红',1);INSERT INTO student(sid,sname,gid)VALUES(2,'李强',1);INSERT INTO student(sid,sname,gid)VALUES(3,'赵四',2);INSERT INTO student(sid,sname,gid)VALUES(4,'郝娟',2); (2)在grade表中查询出班级名称为“软件一班”的id SELECT id FROM grade WHERE name='软件一班'; (3)在student表中,查询gid=1的学生,即为软件一班的学生, SELECT sname FROM student WHERE gid=1; (4)将软件一班的所有学生全部删除 delete from student where sname='王红';delete from student where sname='李强'; (5)在grade表中,将软件一班删除 delete from grade where id=1;

3.连接查询

准备:

#建表 USE chapter05; CREATE TABLE department(     did int(4) NOT NULL PRIMARY KEY,     dname varchar(36) 

);

CREATE TABLE employee (     id int(4) NOT NULL PRIMARY KEY,     name varchar(36),     age int(2),     did int(4) NOT NULL

);

#添加数据 INSERT INTO department(did,dname)VALUES(1,'网络部'); INSERT INTO department(did,dname)VALUES(2,'媒体部'); INSERT INTO department(did,dname)VALUES(3,'研发部');

INSERT INTO department(did,dname)VALUES(5,'人事部');

INSERT INTO employee(id,name,age,did)VALUES(1,'王红',20,1); INSERT INTO employee(id,name,age,did)VALUES(2,'李强',22,1); INSERT INTO employee(id,name,age,did)VALUES(3,'赵四',20,2); INSERT INTO employee(id,name,age,did)VALUES(4,'郝娟',20,4); (1)使用交叉连接查询部门表和员工表中所有的数据  SELECT * FROM department CROSS JOIN employee; (2)在department和employee 表之间使用内连接查询 SELECT employee.name, department.dname FROM department JOIN employee  ON department.did=employee.did; (3)在department和employee 表之间使用WHRER SELECT employee.name, department.dname FROM department,employee  WHERE department.did=employee.did; (4)在department和employee 表之间使用自连接查询 SELECT p1.* FROM employee p1 JOIN employee p2 ON p1.did=p2.did WHERE p2.name='王红'; (5)在department和employee 表之间使用左连接查询 SELECT department.did,department.dname,employee.name FROM department  LEFT JOIN employee on department.did=employee.did; (6)在department和employee 表之间使用右连接查询 SELECT department.did,department.dname,employee.name FROM department  RIGHT JOIN employee ON department.did=employee.did; (7)在department和employee 表之间使用内连接查询,并将查询结果按照年龄从大到小进行排序 SELECT employee.name, employee.age, department.dname FROM department JOIN employee  ON department.did=employee.did order by age; (8)查询存在年龄为20岁的员工部门 SELECT * FROM department WHERE did IN(SELECT did FROM employee WHERE age=20); (9)查询不存在年龄为20岁的员工部门 SELECT * FROM department WHERE did NOT IN(SELECT did FROM employee WHERE age=20);

(10)查询employee表中是否存在年龄大于21岁的员工,如果存在,则查询department表中的所有记录

SELECT *  FROM department WHERE EXISTS(select did from employee where age > 21);

(11)使用带ANY关键字的子查询,查询满足条件的部门 SELECT * FROM department WHERE did>any(select did from employee); (12)使用带ALL关键字的子查询,查询满足条件的部门 SELECT *  FROM department WHERE did>all(select did from employee); (13)使用带比较运算符的子查询,查询赵四是哪个部门的员工 SELECT * FROM department WHERE did=(select did from employee where name='赵四');
转载请注明原文地址: https://www.6miu.com/read-2150001.html

最新回复(0)