CREATE DATABASE ems CHARACTER SET utf8mb4;
USE ems;
CREATE TABLE emp(
empno   INT PRIMARY KEY,
ename   VARCHAR(20) UNIQUE NOT NULL,
job     VARCHAR(20) NOT NULL,
mgr     INT ,
sal     DECIMAL(7,2),
comm    DECIMAL(7,2),
deptno  INT  
) ;
INSERT INTO emp 
VALUES
(9839,'刘一','董事长',NULL,6000,NULL,10),
(9982,'陈二','经理',9839,3450,NULL,10),
(9369,'张三','保洁',9902,900,NULL,20),
(9566,'李四','经理',9839,3995,NULL,20),
(9988,'王五','分析员',9566,4000,NULL,20),
(9902,'赵六','分析员',9566,4000,NULL,20),
(9499,'孙七','销售',9698,2600,300,30),
(9521,'周八','销售',9698,2250,500,30),
(9654,'吴九','销售',9698,2250,1400,30),
(9844,'郑十','销售',9698,2500,0,30),
(9900,'萧十一','保洁',9698,1050,NULL,30);
-- SELECT empno,ename,job,mgr,sal,comm,deptno FROM emp;
-- SELECT empno,job,mgr,sal,comm,deptno,ename FROM emp;
-- SELECT * from emp;
-- SELECT empno,ename FROM emp;
-- SELECT ename,empno FROM emp;
-- SELECT deptno FROM emp;
-- SELECT DISTINCT deptno FROM emp;
-- SELECT DISTINCT ename,job,deptno FROM emp;
SELECT * FROM emp WHERE ename LIKE '%一';
SELECT * FROM emp WHERE ename LIKE '萧%一';
SELECT * FROM emp WHERE ename LIKE '%十%';
SELECT * FROM emp WHERE ename LIKE '__一';
INSERT INTO emp VALUES(9936,'张%一','保洁',9982,1200,NULL,NULL);
SELECT * FROM emp WHERE ename LIKE '%\%%';
SELECT * FROM emp WHERE deptno NOT IN(10,30);
SELECT * FROM emp WHERE job='经理' AND deptno=20;
SELECT * FROM emp WHERE empno BETWEEN 9900 AND 9935;
SELECT * FROM emp WHERE empno NOT BETWEEN 9500 AND 9900;
SELECT * FROM emp WHERE job='经理' OR deptno=10;