CREATE TABLE emp (
id INT AUTO_INCREMENT PRIMARY KEY,
empname VARCHAR(100),
empage INT,
job_position VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE,
managerid INT
);
INSERT INTO emp (empname,empage,job_position,salary,hire_date,managerid) VALUES
('张三',51,'总经理', 8000.00,'2020-01-15', 0),
('李四', 42,'开发部门经理', 7000.00,'2021-06-22', 1),
('王五', 33,'开发组长', 5500.00, '2022-08-05', 2),
('赵六', 28,'开发', 4500.00, '2019-03-10', 3),
('钱七', 35,'开发', 4500.00, '2018-12-01', 3),
('孙八', 46,'销售部门经理', 6000.00, '2020-09-17', 1),
('周九', 23,'销售', 5500.00, '2021-04-25', 6),
('吴十', 21,'开发', 4500.00, '2022-11-30', 3),
('郑十一',30, '市场', 6200.00, '2019-07-15', 6),
('王十二', 32,'销售', 5500.00, '2020-10-20', 6);
INSERT INTO emp (empname,empage,job_position,salary,hire_date) VALUES
('王十三', 32,'销售', 5500.00, '2020-10-20');
单表查询
SELECT * FROM emp;
* 表示所有字段
指定字段查询数据
SELECT id,empname FROM emp;
条件查询
WHERE
> < >= <= <>
查询薪资大于5000的员工（id,empname,salary）
SELECT id,empname,salary FROM emp WHERE salary > 5000;
查询薪资不等于5000的员工（id,empname,salary）
SELECT id,empname,salary FROM emp WHERE salary <> 5000;
and or in is is not
and 并且
查询员工id 大于等于4的 并且 员工id 小于等于 8的
SELECT id,empname,salary FROM emp WHERE id >=4 and id <=8;
or 和 in
查询员工id 等于4的 or 员工id 小于 8的
SELECT id,empname,salary FROM emp WHERE id = 7 or id < 5;
in 范围区间 相当于多个or
SELECT id,empname,salary FROM emp WHERE id in(1,2,3,5,7);
is 数据是不是为空
SELECT id,empname,salary FROM emp WHERE managerid is not null;
SELECT id,empname,salary FROM emp WHERE managerid is null;
as 别名 对于字段 或者表名 简写一下
SELECT id,empname as name,salary FROM emp WHERE id >=4 and id <=8;
SELECT * FROM department as d;
多表查询
内连接查询
SELECT * FROM department;
SELECT * FROM students;
隐式内连接
查询每一个学生和关联的学院
SELECT * FROM students,department WHERE students.deptid = department.did;
SELECT * FROM students as s,department as d WHERE s.deptid = d.did;
join 显示内连接
SELECT * FROM students INNER JOIN department;
SELECT * FROM students JOIN department;
查询每一个学生和关联的学院
SELECT * FROM students JOIN department ON students.deptid = department.did
SELECT * FROM students s JOIN department d ON s.deptid = d.did
外连接查询
左外连接
INSERT INTO department (dname) VALUES ("理工学院")
INSERT INTO students (sname) VALUES ("小测")
SELECT * FROM students LEFT JOIN department ON students.deptid = department.did;
右外连接
SELECT * FROM students RIGHT JOIN department ON students.deptid = department.did;
查询所有学生的名字以及学生档案信息
SELECT * FROM students s LEFT JOIN studetail stu ON s.sid=stu.ssid;
查询2学生的名字以及学生档案信息
SELECT * FROM students AS s LEFT JOIN studetail AS stu ON s.sid=stu.ssid WHERE s.sid = 2;
主表数据尽量用小表
""" 查询课程id是1001的所有学生档案信息"""
自链接查询
当前表自身和自身进行关联查询 自链接查询 表名必须取别名
SELECT 字段名称 FROM 表A 别名 A JOIN 表A 别名 B ON 条件
查询员工名和上级名
SELECT A.empname "上级名",B.empname "下属名" FROM emp as A JOIN emp as B ON A.id = B.managerid
UNION [ALL]全连接查询 吧多张表的查询结果合并
UNION ALL 会有重复数据
UNION 没有重复
多张表的字段必须保持一致
查询员工表 薪资大于5000的
SELECT * FROM emp WHERE salary > 5000;
查询员工表 年龄小于30的
SELECT * FROM emp WHERE empage < 30;
SELECT * FROM emp WHERE salary > 5000
UNION
SELECT * FROM emp WHERE empage < 30;
三表链接查询
查询所有的课程以及选了该课程的学生的学生名字
SELECT * FROM 表1
LEFT JOIN 表2 ON 表1的什么 = 表2的什么
LEFT JOIN 表3 ON 表2的什么 = 表3的什么
查询学生的课程id
SELECT sc.c_id FROM students as s
JOIN stucourse as sc ON s.sid = sc.s_id;
查询课程表关联的学生id
SELECT sc.s_id FROM course as c
JOIN stucourse as sc ON c.cid = sc.c_id;
查询所有的课程以及选了该课程的学生的学生名字
SELECT sc.c_id,sc.s_id,s.sname,c.cname FROM students as s
JOIN stucourse as sc ON s.sid = sc.s_id
JOIN course as c ON c.cid = sc.c_id;
查询课程id是1004的课程名称，以及选了该课程的学生的学生名字
SELECT sc.c_id,sc.s_id,s.sname,c.cname FROM students as s
JOIN stucourse as sc ON s.sid = sc.s_id
JOIN course as c ON c.cid = sc.c_id
WHERE c.cid = 1004;
查询18岁的所有学生以及课程名
学生表和学生详情表关联查询
SELECT * FROM students s JOIN studetail stu ON s.sid = stu.ssid;
学生表和中级表关联查询
SELECT * FROM students s JOIN stucourse sc ON s.sid = sc.s_id;
课程表和 中级表关联查询
SELECT * FROM course c JOIN stucourse sc ON c.cid = sc.c_id;
WHERE stu.stuage>18
SELECT s.sname,stu.stuage,c.cname FROM students s
JOIN studetail stu ON s.sid = stu.ssid
JOIN stucourse sc ON s.sid = sc.s_id
JOIN course c ON c.cid = sc.c_id
WHERE stu.stuage>18;
子表查询
标量子查询 子查询结果必须为单值
查询105学院的学生信息
SELECT deptid FROM students WHERE deptid = 105;
查询学院表id是105的学院名称
SELECT dname FROM department WHERE did = 105;
查询 理工学院的学生
SELECT dname FROM department WHERE did = (
SELECT deptid FROM students WHERE deptid = 105)
SELECT dname FROM department d JOIN
students s ON s.deptid= d.did
WHERE d.dname = "理工学院"
列子查询
可以是多行数据
查询清华大学和 上海交通大学的学生
查询学院是 清华大学和 上海交通大学 看看学院id是多少
SELECT did FROM department WHERE dname = "清华大学" or dname = "上海交通大学";
查询学生表
SELECT * FROM students WHERE deptid in (
SELECT did FROM department WHERE dname = "清华大学" or dname = "上海交通大学");
行子查询
子查询结果一行（但是可以是多列）
查询 员工"赵六"的薪资和上级领导相同的员工信息
+----+-----------+--------+--------------------+---------+------------+-----------+
| id | empname | empage | job_position | salary | hire_date | managerid |
+----+-----------+--------+--------------------+---------+------------+-----------+
| 1 | 张三 | 51 | 总经理 | 8000.00 | 2020-01-15 | 0 |
| 2 | 李四 | 42 | 开发部门经理 | 7000.00 | 2021-06-22 | 1 |
| 3 | 王五 | 33 | 开发组长 | 5500.00 | 2022-08-05 | 2 |
| 4 | 赵六 | 28 | 开发 | 4500.00 | 2019-03-10 | 3 |
| 5 | 钱七 | 35 | 开发 | 4500.00 | 2018-12-01 | 3 |
| 6 | 孙八 | 46 | 销售部门经理 | 6000.00 | 2020-09-17 | 1 |
| 7 | 周九 | 23 | 销售 | 5500.00 | 2021-04-25 | 6 |
| 8 | 吴十 | 21 | 开发 | 4500.00 | 2022-11-30 | 3 |
| 9 | 郑十一 | 30 | 市场 | 6200.00 | 2019-07-15 | 6 |
| 10 | 王十二 | 32 | 销售 | 5500.00 | 2020-10-20 | 6 |
| 11 | 王十三 | 32 | 销售 | 5500.00 | 2020-10-20 | NULL |
+----+-----------+--------+--------------------+---------+------------+-----------+
查询薪资是4500 部门id 3的员工信息
SELECT * FROM emp WHERE salary = 4500 and managerid = 3;
查询赵六的薪资和部门id
SELECT salary,managerid FROM emp WHERE empname = "赵六";
查询薪资是4500 部门id 3的员工信息
SELECT * FROM emp WHERE (salary,managerid ) = (4500,3);
SELECT * FROM emp WHERE (salary,managerid ) =
(SELECT salary,managerid FROM emp WHERE empname = "赵六")
表子查询
子查询结果是一个表 （多行多列）
查询所有的课程以及选了该课程的学生的学生名字
SELECT sc.c_id,sc.s_id,s.sname,c.cname FROM students as s
JOIN stucourse as sc ON s.sid = sc.s_id
JOIN course as c ON c.cid = sc.c_id;
SELECT * FROM students s
JOIN (SELECT * FROM stucourse as sc JOIN course c ON sc.c_id = c.cid ) as csc
ON csc.s_id =s.sid;