sql语句操作

培训打卡
CREATE TABLE employee_china(
id PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50)
);
CREATE TABLE employee_usa(
id PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50)
);
INSERT INTO employee_china(NAME) VALUES(‘123’);

SELECT* FROM employee_usa CHAIN UNION SELECT * FROM employee_china ; /合并去重/
SELECT * FROM employee_usa CHAIN UNION ALL SELECT *FROM employee_china; /合并不去重/

DROP TABLE exam/删除表/

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

SELECT*FROM employee e/别名/ , department d/别名,可以不加as/ WHERE e .depno=d. id /关联两张表/

SELECT e.id ,e.NAME,d.NAME 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 ;

外连接（左连接、右链接）
左连接
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=l ;

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=‘林同学’)

SELECT *FROM emp WHERE depno=(SELECT depno 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 ename=‘刘同学’ AND emp.depno=depart.depno;