编辑代码

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);
INSERT INTO emp VALUES(9936,'张%一','保洁',9982,1200,NULL,NULL);

-- 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;
-- SELECT COUNT(*) FROM emp;
-- SELECT COUNT(COMM) FROM emp;
-- SELECT * FROM emp;
--  SELECT SUM(COMM) FROM emp;
--  SELECT AVG(COMM) FROM emp;
--  SELECT AVG(IFNULL(COMM,0)) FROM emp;
--  SELECT deptno FROM emp GROUP BY deptno;
-- SELECT deptno,AVG(sal),SUM(sal) FROM emp GROUP BY deptno;
-- SELECT * from emp WHERE deptno=30 ORDER BY sal;
-- SELECT * from emp WHERE deptno=30 ORDER BY sal DESC;
-- SELECT * from emp WHERE deptno=30 ORDER BY comm;
-- SELECT * from emp WHERE deptno=30 ORDER BY job,empno DESC;
-- SELECT * FROM emp ORDER BY sal DESC LIMIT 5;
-- SELECT * FROM emp ORDER BY sal DESC LIMIT 1,4;
-- SELECT name,upload_time FROM book WHERE state=0;
-- SELECT name,price,state FROM book ORDER BY name ASC LIMIT 5;
-- SELECT name, price FROM book WHERE price>50;
-- SELECT name, price FROM book WHERE price BETWEEN 30 AND 50;
-- SELECT name, borrower_id,borrower_time FROM book WHERE state=1;
-- SELECT name FROM book WHERE name LIKE "%java%";
-- SELECT name FROM book WHERE name LIKE '%入门';
-- SELECT name,price FROM book WHERE name IN('西游记','红楼梦') ;

CREATE TABLE dept(deptno INT PRIMARY  KEY,
                  dname  VARCHAR(20)UNIQUE);
INSERT INTO dept
VALUES
(10, '总裁办'),
(20, '研究院'),
(30, '销售部'),
(40, '运营部');
INSERT INTO dept VALUES(50, '人力资源部');
INSERT INTO emp VALUES(9966,'八戒','运营专员',9839,3000,2000,40);
INSERT INTO emp VALUES(9999,'悟空','人事专员',9982,3000,NULL,50);
-- SELECT * FROM emp,dept;
-- SELECT ename,dname FROM emp e JOIN dept d ON e.deptno=d.deptno;
-- SELECT d.dname,e.ename FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;
-- SELECT d.dname,e.ename FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;
-- SELECT e.*,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno ORDER BY e.sal DESC;
-- SELECT deptno FROM emp WHERE sal>2900;
-- select * from dept where deptno not in (select deptno from emp where sal>2900);
-- show CREATE table emp;
-- ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);
-- show CREATE table emp;
--  SELECT e.*,d.dname FROM emp e,dept d
--      WHERE e.deptno=d.deptno AND d.dname='人力资源部';
-- SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='人力资源部');

-- DELETE FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='人力资源部');
-- DELETE FROM  dept WHERE dname='人力资源部';
ALTER TABLE emp DROP FOREIGN KEY fk_deptno;
DELETE FROM  dept WHERE dname='运营部';
-- SELECT * FROM emp WHERE ename='陈二' OR ename='李四';
START TRANSACTION;
UPDATE emp SET comm=10000 WHERE ename='陈二';
UPDATE emp SET comm=10000 WHERE ename='李四';
ROLLBACK;
UPDATE emp SET comm=1000 WHERE ename='陈二';
UPDATE emp SET comm=1000 WHERE ename='李四';
COMMIT;

SELECT * FROM emp WHERE ename='陈二' OR ename='李四';