-- 部门表
CREATE TABLE dept(id INT,deptName VARCHAR(20));
-- 员工表
CREATE TABLE employee (id INT,empName VARCHAR(20),deptId INT);
-- 薪水表
CREATE TABLE salary (id INT PRIMARY KEY AUTO_INCREMENT,empId INT,money INT);
-- 初始数据
DELETE FROM dept;
INSERT INTO dept VALUES(1,'工程部');
INSERT INTO dept VALUES(2,'销售部'),(3,'客服部');
INSERT INTO dept VALUES(5,'市场部');
DELETE FROM employee;
INSERT INTO employee VALUES(1,'张三',1);
INSERT INTO employee VALUES(2,'李四',1);
INSERT INTO employee VALUES(3,'王五',1);
INSERT INTO employee VALUES(4,'乔布斯',3);
INSERT INTO employee VALUES(5,'比尔盖茨',2);
INSERT INTO employee VALUES(6,'克林顿',2);
INSERT INTO employee VALUES(7,'小布什',3);
INSERT INTO employee VALUES(8,'希拉里',6);
INSERT INTO salary VALUES (NULL,1,1000);
INSERT INTO salary VALUES (NULL,2,10000);
INSERT INTO salary VALUES (NULL,3,10500);
INSERT INTO salary VALUES (NULL,4,1300);
INSERT INTO salary VALUES (NULL,5,1300);
INSERT INTO salary VALUES (NULL,6,1300);
INSERT INTO salary VALUES (NULL,7,1300);
SELECT * FROM salary;
DELETE FROM salary WHERE id = 7;
SELECT * FROM dept;
SELECT * FROM employee;
-- 员工id 员工名字 员工部门名称
-- 1. 交叉连接查询(没有条件) 结果数量=7*3
-- [例]
SELECT employee.id,empName,deptName FROM employee,dept;
-- 2. 内连接查询，只有符合条件的信息才会显示 (用的最普遍)
-- [例]
SELECT employee.id,employee.empName,dept.deptName FROM employee,dept WHERE employee.deptId = dept.id;
-- select 哪些字段
-- from 从哪些表
-- where 条件
-- 查询2张表 需要1个条件
-- 查询3张表 需要2个条件
-- [例] 三张表
-- 员工信息、部门名字、薪水
SELECT employee.id,employee.empName,dept.deptName,salary.money FROM employee,dept,salary WHERE employee.deptId = dept.id AND employee.id = salary.empId;
-- 3. 外连接
worldcity
-- 左外连接 左表全部显示
-- [例] 左表 右表
SELECT dept.deptName,employee.empName FROM dept LEFT OUTER JOIN employee ON dept.id=employee.deptId;
-- 右外连接 右表全部显示
-- [例]
SELECT dept.deptName,employee.empName FROM dept RIGHT OUTER JOIN employee ON dept.id=employee.deptId;
-- 4. 自连接
ALTER TABLE employee ADD bossId INT;
UPDATE employee SET bossId = 1 WHERE id = 2;
UPDATE employee SET bossId = 1 WHERE id = 3;
UPDATE employee SET bossId = 1 WHERE id = 4;
UPDATE employee SET bossId = 2 WHERE id = 5;
UPDATE employee SET bossId = 2 WHERE id = 6;
-- [例]
-- [需求] 显示员工及其上司
-- 注意:需要使用别名区分
SELECT a.empName,b.empName FROM employee AS a LEFT OUTER JOIN employee b ON a.bossId = b.id;
-- 子查询 从一个表中查询的结果 作为另外一个表查询的条件
-- in,not in ,any,all,exists,not exists
SELECT * FROM employee WHERE deptId IN (SELECT id FROM dept);
SELECT * FROM employee WHERE deptId NOT IN (SELECT id FROM dept);
-- 合并查询结果
SELECT id FROM dept UNION SELECT deptId FROM employee; -- 去除重复的
SELECT id FROM dept UNION ALL SELECT deptId FROM employee;
-- 例子
CREATE TABLE computer_stu(
id INT PRIMARY KEY,
NAME VARCHAR(20),
score FLOAT
);
CREATE TABLE scholarship
(
LEVEL INT PRIMARY KEY,
score INT
);
INSERT INTO computer_stu VALUES(1001,'张三',85);
INSERT INTO computer_stu VALUES(1002,'lisi',91);
INSERT INTO computer_stu VALUES(1003,'乔布斯',75);
INSERT INTO computer_stu VALUES(1004,'王五',77);
INSERT INTO computer_stu VALUES(1005,'库克',65);
INSERT INTO computer_stu VALUES(1006,'比尔',99);
INSERT INTO computer_stu VALUES(1007,'克林顿',86);
INSERT INTO computer_stu VALUES(1008,'小布什',70);
INSERT INTO scholarship VALUES(1,90);
INSERT INTO scholarship VALUES(2,80);
INSERT INTO scholarship VALUES(3,70);
-- 查询可以拿1等奖学金的学生
SELECT id,NAME,score FROM computer_stu WHERE score >= (SELECT score FROM scholarship WHERE LEVEL = 1);
-- 查询可以所有拿奖学金的学生
SELECT id,NAME,score FROM computer_stu WHERE score >= (SELECT score FROM scholarship WHERE LEVEL = 3);
SELECT id,NAME,score FROM computer_stu WHERE score >= ANY (SELECT score FROM scholarship);
-- 查询可以拿1等奖学金的学生
SELECT id,NAME,score FROM computer_stu WHERE score >= ALL (SELECT score FROM scholarship);
