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;