8-21

yiju0815CREATE TABLE employee_usa(
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;

CREATE TABLE department(
id INT,
NAME VARCHAR(50)
);

CREATE TABLE employee(
id INT,
NAME VARCHAR(50),
depno ningda``departmentINT
);
DELETE FROM employee WHERE id = 5;

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 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

WHERE e.id=1 ;

CREATE TABLE depart(
depno INT PRIMARY KEY,
NAME VARCHAR(50),
location VARCHAR(50),
setuptime 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)
);

SELECT * FROM emp WHERE sal> (SELECT sal FROM emp WHERE ename=‘林同学’ )
emp

工资高于技术部所有员工的工资
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=‘刘同学’;

字段控制查询
去除重复记录
去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：
聚合函数
聚合函数是用来做纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0