8.21
1.创建表
CREATE TABLE employee_usa(
id INT,
NAME VARCHAR(50)
);
CREATE TABLE employee_chain(
id INT,
NAME VARCHAR(50)
);
实现查询
SELECT * FROM employee_chain;
SELECT * FROM employee_usa;
SELECT * FROM employee_chain UNION SELECT * FROM employee_usa;
SELECT * FROM employee_chain UNION ALL SELECT * FROM employee_usa;
SELECT * FROM employee_usa;
2.多表连接
(1)创建表
CREATE TABLE department(
id INT,
NAME VARCHAR(50)
);
CREATE TABLE employee(
id INT,
NAME VARCHAR(50),
depno INT
);
(2)实现
SELECT * FROM employee WHERE id=1
SELECT e.id
,e.name
AS ‘员工名’,d.name
AS ‘部门’ FROM employee e,department d WHERE e.depno
= d.id
;
内连接
SELECT e.id
,e.name
AS ‘员工名’,d.name
AS ‘部门’ FROM employee e INNER JOIN department d ON e.depno
= d.id
;
外连接 (左连接,右连接) ‘department’
左连接
SELECT e.id
,e.name
AS ‘员工名’,d.name
AS ‘部门’ FROM employee e LEFT OUTER JOIN department d ON e.depno
= d.id
;
右连接
SELECT e.id
,e.name
AS ‘员工名’,d.name
AS ‘部门’ FROM employee e RIGHT OUTER JOIN department d ON e.depno
= d.id
;
SELECT * FROM employee e LEFT OUTER JOIN department d ON e.depno = d.id;
3.多表查询
(1)创建表
CREATE TABLE depart(
depno INT PRIMARY KEY,
NAME VARCHAR(50),
location VARCHAR(50),
setuption TIMESTAMP
);
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
depno INT,
CONSTRAINT fk_emp_depart FOREIGN KEY(depno) REFERENCES depart(depno)
);
(2)实现
工资高于林同学的所有员工
SELECT * FROM emp WHERE sal> (SELECT sal FROM emp WHERE ename = “林同学”)
工资高于技术部所有员工的工资
SELECT * FROM emp WHERE sal> (SELECT MAX(sal) FROM emp WHERE depno = “20”)
查询刘同学的员工编号 工资 部门名称 部门地址
SELECT empno,sal,NAME,location FROM emp,depart WHERE emp.depno = depart.depno AND emp.ename = “刘同学”