Contents
DML-SQL语句-select 基本功能
1.选择所有列
select * from EMPLOYEES;
2.选择指定的列
select DEPARTMENT_ID,POSITION_ID,SALARY from EMPLOYEES;
select employee_id,salary,first_name from EMPLOYEES;
3.算术运算符
--数字+,-,*,/
--日期+,-
select first_name,last_name,salary,salary+300 from EMPLOYEES;
select first_name,last_name,salary,salary*12+300 from EMPLOYEES;
select first_name,last_name,salary,salary*(12+300) from EMPLOYEES;
4.比较运算符
--> < >= <= !=<>
select first_name,last_name,salary from EMPLOYEES where salary > 3000;
select first_name,last_name,salary from EMPLOYEES where salary <= 6000;
5.列连接
select first_name||last_name from EMPLOYEES;
select first_name||' '||last_name from EMPLOYEES;
6.字符串
select first_name||' '||last_name||Q'['s salary is ]'||salary from EMPLOYEES;
7.distinct重复行(去重)
select distinct POSITION_ID from employees;
8.别名
select employee_id "员工ID",salary "薪水" from employees;
9.||构建表达式
select table_name from all_tables where owner='ITPUX';
10.批量删除表
select 'drop table ' || table_name||';' from all_tables where owner='ITPUX';
11.使用like运算符
--模糊查询，%,_
select * from DEPARTMENTS;
select * from departments where department_name like 'H%'; #匹配字母"H"的后面
select * from departments where department_name like '%R%'; #匹配字母"R"的前面和后面
select * from departments where location like '_e_%'; #_只能匹配单个字符
12.and or in 符合条件
--and,or,in
select * from employees;
select * from employees where salary > 1000 and hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');
select * from employees where salary > 1000 and hire_date > TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
select * from employees where salary > 10000 or hire_date > TO_DATE('2020-01-01', 'YYYY-MM-DD');
select * from positions where position_name in ('HR Manager','CTO');
13.变量查找
select * from employees where salary=&salary and hire_date=&hire_date;
select * from employees where salary=&salary and first_name=&first_name;
14.order by使用查询排序
--order by
--asc 升序排列-默认
--desc 降序排列
select * from employees order by salary; --asc
select * from employees order by salary desc; --desc
select * from employees order by salary desc,comm; --以第一列为主
select * from employees order by 6;