(day2)数据库条件查找部分语句

first_name请用两种方式：
1.查询员工表中所有员工名字及员工工资并按从小到大的顺序排列

select first_name,salary from s_emp order by salary;
select first_name ,salary from s_emp order by 2;
select first_name ,salary from s_emp order by 1;

2.查询员工表中所有员工名字及员工工资并按从大到小的顺序排列

select first_name,salary from s_emp order by 2 desc;

3.查询员工表中所有员工名字及员工工资并按名字从大到小的顺序排列

select first_name,salary from s_emp order by 1;
select first_name,salary from s_emp order by first_name desc;

4.查询员工表中所有员工名字及员工工资并按工资从小到大，名字从大到小的顺序排列

select first_name ,salary from s_emp order by salary,first_name desc;
select first_name ,salary from s_emp order by 2,1 desc;

5.查询员工表中编号为14的员工(标题为Stock Clerk)

select * from s_emp;
select *from s_emp where id=14;
select *from s_emp where id>14;
select *from s_emp where id<14;
select *from s_emp where id>=14;
select *from s_emp where id<=14;
select *from s_emp where id<>14;
select *from s_emp where id!=14;
select *from s_emp where id^=14;

select * from s_emp where title = 'Stock Clerk';

6.查询员工表中编号在12到24之间的记录

select * from s_emp where id between 12 and 24;

7.查询员表中编号为1,3,8的记录

select * from s_emp where id in(1,3,8);

8.查询员工表中姓名包含a字母的员工信息

select * from s_emp where first_name like '%a%';
select * from s_emp where first_name like '_a_';

9.查询员工表中提成为空的所有员工信息

select * from s_emp where commission_pct is null;

10.查询员工表中编号不在12到24之间的记录

select *from s_emp where id not between 12 and 24;

11.查询员表中编号不为1,3,8的记录

select * from s_emp where id not in (1,3,8);

12.查询员工表中姓名不包含a字母的员工信息

select *from s_emp where first_name not like '%a%';

13.查询员工表中提成不为空的所有员工信息

select * from s_emp where commission_pct is not null;

14.查询员工表中id为11并且工资为1400的记录

select * from s_emp where id=11 and salary=1400;

15.查询员工表中id为11或者工资为1400的记录

select * from s_emp where id=11 or salary=1400;

16.逻辑运算符多个混合使用

SELECT* FROM s_emp WHEREsalary >= 1000 ANDdept_id = 44 ORdept_id = 42 ;
SELECT* FROM s_emp WHEREsalary >= 1000 AND(dept_id = 44 ORdept_id = 42 );

17.LOWER转换为小写

select lower('ORACEL') from dual;

18.UPPER转换为大写

select upper('oracel') from dual;

19.INITCAP转换为首字母大写

select initcap(' oh my ladygaga ') from dual;

20.CONCAT字符串连接 ||

select concat(first_name ,last_name) from s_emp;
select first_name ||'.'||last_name "姓名" from s_emp;
select concat(concat(first_name,'.'),last_name) from s_emp;

21.SUBSTR(原字符串,起始值,返回子串的长度)返回子串

"my ladygaga" ======>"lady"

select substr('my ladygaga',4,4) from dual ;

22.LENGTH(字符串)返回长度

select first_name ,length(first_name) from s_emp;

23.ROUND四舍五入

select round (45.923, 2) from dual;
select round (45.923, 0) from dual;
select round (45.923, -1) from dual;

24.TRUNC截取

select trunc(45.923, 2) from dual;
select trunc(45.923, 0) from dual;
select trunc(45.923, -1) from dual;

25.查看系统日期

select sysdate from dual;

26.查询当前时间五天后的日期

select sysdate+5 from dual;

27.MONTHS_BETWEEN 两个日期中做计算(为毫秒数)

select months_between(sysdate,sysdate+30) from dual;

28.ADD_MONTHS当前时间增加（按月增加）

select add_months(sysdate,2) from dual;

29.NEXT_DAY当前时间的下一个时间

select next_day(sysdate,'星期一') from dual;

30.LAST_DAY本月的最后一天

select last_day(sysdate) from dual;

31.嵌套(sysdate,值)值可为 正 负

select last_day(add_months(sysdate,-1)) from dual;

32.TO_CHAR 转换为字符串

select to_char(1234) from dual;
select to_number(45678) from dual;

33.转换为固定格式

select to_char(123456789,'fm$999,999,999') from dual;

34.TO_DATE 转换为日期

select to_date('04-3月 -90') from dual;