mysql基础，单表查询基础练习题。

CREATE TABLE emp (
empno INT,
ename VARCHAR(20),
job VARCHAR(10),
mgr INT,
hiredate DATE,
sal DOUBLE,
COMM DOUBLE,
deptno INT
)
ALTER TABLE emp MODIFY COLUMN sal DOUBLE(20,2);
ALTER TABLE emp MODIFY COLUMN COMM DOUBLE(20,2);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1001,'甘宁','文员',1013,'2000-12-17',8000.00,20);
INSERT INTO emp VALUES(1002,'黛琦丝','销售员',1006,'2001-02-20',16000.00,3000.00,30);
INSERT INTO emp VALUES(1003,'殷天正','销售员',1006,'2001-02-22',12500.00,5000.00,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1004,'刘备','经理',1009,'2001-04-02',29750.00,20);
INSERT INTO emp VALUES(1005,'谢逊','销售员',1006,'2001-09-28',12500.00,14000.00,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1006,'关羽','经理',1009,'2001-05-01',28550.00,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1007,'张飞','经理',1009,'2001-09-01',24500.00,10);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1008,'诸葛亮','分析师',1004,'2007-04-19',30000.00,20);
INSERT INTO emp(empno,ename,job,hiredate,sal,deptno)VALUES(1009,'曹阿牛','经理','2001-11-17',50000.00,10);
INSERT INTO emp VALUES(1002,'韦一笑','销售员',1006,'2001-09-08',15000.00,0.00,30);
UPDATE emp SET empno=1010 WHERE ename='韦一笑';
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1011,'周泰','文员',1008,'2007-05-23',11000.00,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1012,'程普','文员',1006,'2001-12-03',9000.00,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1013,'庞统','分析师',1004,'2001-12-03',30000.00,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(1014,'黄盖','文员',1007,'2002-01-23',13000.00,10);
INSERT INTO emp VALUES(1015,'张三','保洁员',1001,'2013-05-01',80000.00,50000.00,50);
UPDATE emp SET job='董事长' WHERE empno=1009;
UPDATE emp SET sal=9500 WHERE empno=1012;
SELECT * FROM emp;

-- 条件查询
-- 查询所有销售员信息
SELECT * FROM emp WHERE job = '销售员';
-- 查询所有工资大于10000的员工姓名
SELECT ename FROM emp WHERE (sal>10000);
-- 查询工资大于10000的销售员姓名
SELECT ename FROM emp WHERE sal>10000 AND job='销售员';
-- 查询所有销售员和经理的姓名、工资
SELECT ename,sal FROM emp WHERE job='销售员' OR job='经理';
-- 查询工资在5000至一万内的员工信息
SELECT * FROM emp WHERE sal>=5000 AND sal <=10000;
-- betwwen and
SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000;
-- 查询不是经理的员工姓名
SELECT ename FROM emp WHERE job<>'经理';
-- 查询姓名不为空的员工信息
SELECT*FROM emp WHERE ename IS NOT NULL AND ename <> '';
-- 查询奖金不为空的员工信息
SELECT*FROM emp WHERE COMM IS NOT NULL;
-- 查询姓张的员工信息
SELECT * FROM emp WHERE ename LIKE '张%';
-- 查询三个字的员工信息
SELECT * FROM emp WHERE ename LIKE '___';
-- 查询名字中带天字的员工信息
SELECT * FROM emp WHERE ename LIKE '%天%';
-- 查询每个同学三门成绩和
SELECT sname , math+js+html AS num FROM score;
-- 查询math的总成绩
SELECT SUM(math) FROM score;
-- 查询所有同学的三门课总成绩
SELECT SUM(math+js+html) FROM score;
-- 查询js这门课的平均成绩
SELECT AVG(js) FROM score;
-- 查询html的最高分和最低分
SELECT MAX(html),MIN(html) FROM score;
-- 查询员工表有多少员工
SELECT COUNT(*) FROM emp;
-- 查询销售员的人数
SELECT COUNT(*) FROM emp WHERE job='销售员';

-- 分页 每页显示三条数据
-- 第一页数据
SELECT * FROM emp LIMIT 0,3;
-- 第二页
SELECT * FROM emp LIMIT 3,3;
-- 第三页
SELECT * FROM emp LIMIT 6,3;
-- select * from emp limit (当前页-1)*每页显示条数，每页显示条数