分享MySQL的一个小练习，基础简单题，回顾SQL语句

CREATE DATABASE homework1
DROP TABLE IF EXISTS emp;
CREATE TABLE IF NOT EXISTS emp(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(10) NOT NULL,
job VARCHAR(20),
salary DOUBLE(8,2),
mgr INT(10),
bonus DOUBLE(6,2),
hiredate DATE,
deptno INT(10)
);
INSERT INTO emp VALUES(NULL,'mary','sales',7000,6,1000,'2014-1-1',10);
INSERT INTO emp VALUES(NULL,'lily','sales',6000,1,800,'2014-5-1',10);
INSERT INTO emp VALUES(NULL,'tom','sales',5000,1,4000,'2014-3-1',10);
INSERT INTO emp VALUES(NULL,'james','account',8000,6,NULL,'2014-2-1',20);
INSERT INTO emp VALUES(NULL,'scott','teaching',8000,6,3000,'2014-1-20',30);
INSERT INTO emp VALUES(NULL,'tom','BOSS',38000,NULL,NULL,'2013-1-20',30);
INSERT INTO emp VALUES(NULL,'kitty','teaching',7000,5,700,'2014-5-20',30);
INSERT INTO emp VALUES(NULL,'kitty','teaching',6000,5,500,'2014-6-20',30);
INSERT INTO emp VALUES(NULL,'green','analyst',15000,6,1000,'2014-2-20',40);
INSERT INTO emp VALUES(NULL,'brown','analyst',12000,9,7000,'2014-4-20',40);
INSERT INTO emp VALUES(NULL,'danis','department',3000,6,800,'2014-3-4',50);
INSERT INTO emp VALUES(NULL,'brown','department',1800,11,600,'2014-4-20',50);
INSERT INTO emp VALUES(NULL,'smith','department',1200,11,500,'2014-5-20',50);

1：查询EMP表的全部职工的EMPNO、ENAME和JOB。

SELECT empno,ename,job 
FROM emp;

2：查询EMP表的全部职工的EMPNO、ENAME和JOB，按salary升序次序排列。

SELECT empno,ename,job 
FROM emp
ORDER BY salary ;

3：查询EMP表的全部列，列的次序为：

-- JOB，SALARY，ENAME，EMPNO，MGR，HIREDATE，BONUS，DEPTNO，查询结果按年薪降序。
SELECT job,salary,ename,ename,mgr,hiredate,bonus,deptno
FROM emp
ORDER BY salary*12+bonus DESC; 

4：列出EMP表中的不同的JOB名称

SELECT DISTINCT job
FROM emp;

5：查询在部门10中工作，其工资高于6000的职工信息。

SELECT ename,empno
FROM emp 
WHERE deptno=10 AND salary>6000;

6：列出其JOB为MANAGER或ANALYST的职工名及职工号

SELECT ename,empno
FROM emp 
WHERE job='manager' OR job='analyst';

7：列出工资在5500至20000之间的职工名字、职工号。使用两种方式实现

SELECT ename,empno
FROM emp 
WHERE salary BETWEEN 5500 AND 20000
SELECT ename,empno
FROM emp 
WHERE salary>=5500 AND salary<=20000;

8：查出工资为6k的人员所在的部门名称和地址。

SELECT deptno
FROM emp
WHERE salary=6000;

9：查出以W开头或以S结尾的职工名的职工信息。

SELECT ename,empno
FROM emp
WHERE ename LIKE 'w%' OR ename LIKE '%s' ;

10：查出以k开头或以y结尾的职工信息和部门信息。

SELECT ename,empno
FROM emp
WHERE ename LIKE 'k%' OR ename LIKE '%y' ;