/*查询emp表中工资大于1000的员工姓名以及工资*/
SELECT ename,sal FROM emp WHERE sal>1000;
/*查询emp表中值为'MANAGER'的员工姓名和工资以及职位*/
SELECT ename,sal,job FROM emp WHERE job='MANAGER';
/*查询emp表中名字长度为5的员工姓名和工资以及职位*/
SELECT ename 姓名,sal 工资,job 职位 FROM emp WHERE LENGTH(ename)=5;
SELECT ename AS 姓名,sal AS 工资,job AS 职位 FROM emp WHERE LENGTH(ename)=5;/*AS可省略,同上一语句*/
SELECT ename '姓 名',sal '工 资',job '职 位' FROM emp WHERE LENGTH(ename)=5;/*如果列别名要加空格,需将列别名加单引号*/
/*查询emp表中有奖金的员工信息,不可以用comm!=NULL,任何字段与NULL比较皆不相等,包括NULL*/
SELECT * FROM emp WHERE comm IS NOT NULL;
/*查询emp表中有奖金并且工资大于1000的员工姓名以及工资和奖金*/
SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL AND sal>1000;
/*查询工资在1000-2000之间的员工信息,BETWEEN为闭区间*/
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;
/*查询工资在大于1000并且小于2000之间的员工信息*/
SELECT * FROM emp WHERE sal>1000 AND sal<2000;
/*查询10部门和20部门的员工信息*/
SELECT * FROM emp WHERE deptno=10 OR deptno=20;
SELECT * FROM emp WHERE deptno IN (10,20);/*意思同上*/
/*查询emp表中职位为'CLERK','MANAGER','ANALYST'的员工信息(两种方式)*/
SELECT * FROM emp WHERE job='CLERK' OR job='MANAGER' OR job='ANALYST';
SELECT * FROM emp WHERE job IN ('CLERK','MANAGER','ANALYST');
/*查询emp表中既不是10部门也不是20部门的员工*/
SELECT * FROM emp WHERE deptno!=10 AND deptno!=20;
SELECT * FROM emp WHERE deptno NOT IN (10,20);/*同上*/
/*查询emp表中值不为'CLERK','MANAGER','ANALYST'的员工信息*/
SELECT * FROM emp WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
/*查询emp表中工资大于SMITH,ALLEN,WARD中最大工资的员工,ALL(1500,2000,2500)方法在MySql中无法执行,但ORACLE中可以,集合函数中该四方法不适用于直接加数字,仅能够将该值转换为子查询,但语法无错误>all 大于最大的 >any大于最小的 <all小于最小的 <any小于最大的*/
SELECT * FROM emp WHERE sal>ALL (SELECT sal FROM emp WHERE ename IN ('SMITH','ALLEN','WARD'));
/*查询emp表中名字带有'S'的*/
SELECT * FROM emp WHERE ename LIKE '%S%';
/*查询名字中第五个字符为'S'的员工*/
SELECT * FROM emp WHERE ename LIKE '____S%';
/*查询emp表中职位的第二个字符为'a'的员工*/
SELECT * FROM emp WHERE job LIKE '_A%';
SELECT * FROM emp WHERE job LIKE '_a%';
SELECT * FROM emp WHERE LOWER(job) LIKE '_a%';
/*查询emp表中部门号为20号的工资,工资降序排列*/
SELECT ename,sal FROM emp WHERE deptno=20 ORDER BY sal DESC;
/*查询emp表中部门号为20号的工资,工资升序排列*/
SELECT ename,sal FROM emp WHERE deptno=20 ORDER BY sal ASC;
/*查询emp表中部门号为20号的工资,工资降序排列,工资相同时,按照姓名升序排列,若省略排序关键字默认按升序排列,若是多个排序规则,会在第一个排序结果相同时按下一排序规则进行排序,DESC也可查询表结构*/
SELECT ename,sal FROM emp WHERE deptno=20 ORDER BY sal DESC,ename ASC;
/*若字段名为关键字,可使用``符号进行转义*/