数据库常用搜索命令(个人整理)

select * from user_tables;
select * from Employees;
select commission_pct from employees;

select first_name,email,job_id from employees;
select department_name,department_id from departments;
/* 查询多条字段时需要在中间加上“，”号。*/


select first_name,last_name from employees where salary*12>150000;
select salary*12 from employees;
/* 所要查询的字段后可以跟上运算式进行运算查询。*/


select 'fankl'||last_name from employees;
/* 添加自定义的字符串。格式：'xxx'|| */
select * from employees where first_name||last_name='DavidAustin';
/* 字段的拼接。全称是“David Austin”的信息。*/



select 'fankl'||last_name username from employees;
/* 更改显示的表头的名称。字段后加“自定义的名称”或加“as+自定义的名称” */


select salary*12*(1+commission_pct) from employees;
select salary*12*(1+nvl(commission_pct,0)) from employees;
select salary*12*(1+nvl(commission_pct,0)) as finallysalary from employees;
/* （nvl）当一个字段中含有null值，需用nvl自定义一个数值，否则，这个数值最后取值依旧是null。null在数据库中代表无限大。
另：nvl中的自定义内容，不仅仅是数值，还可以是日期等其它内容。 */


select department_id from employees;
select distinct department_id from employees;
select distinct department_id,first_name from employees;
/* （distinct） 字段去重。后若跟多列，则所有的都要一样才能执行。如：employees中的department_id、first_name都一样时才能进行去重。*/


select salary from employees where salary>10000;
select * from employees where salary>10000;
select salary as money from employees where money>10000;
/* （结果为FALSE。先执行where,而表中无money一项）where的执行在select之前。*/


select last_name from employees;
select * from employees where department_id=30 and salary>'5000';
select department_id,salary from employees where department_id=30 or salary>5000;
/* 逻辑关系操作：and、or、not。*/


select first_name from employees;
select salary from employees where first_name='David';
/* 查询特定的内容时需要有单引号，并区分大小写。如：特定的名称。*/


select salary,first_name from employees where salary>'5000' and salary<'10000';
select salary,first_name from employees where salary between '5000' and '10000';

select first_name,department_id from employees where department_id='60' or department_id='70' or department_id='90';
select first_name,department_id from employees where department_id in(60,70,90);
select first_name,department_id from employees where department_id not in(30,

60,90);
select first_name,department_id, salary from employees where department_id in(30,60,90) and salary > '5000';

select first_name from employees where first_name like 'S%';
select first_name from employees where first_name like '%i%d';
select first_name from employees where first_name like '%i%' or first_name like '%d';
/* (like)格式：like 'X%'。%代表零位或多位字符。"_"表示一位字符。*/

select first_name,commission_pct from employees where commission_pct is null;
/* 不能用"commission_pct=null"，因为null表无限，任何一个数值都不能与它匹配。*/
select first_name,commission_pct from employees where commission_pct is not null;
/* SQL比较操作：between...and...、in(list)、like、is null。*/


select salary,commission_pct from employees order by salary desc;
select salary,commission_pct from employees order by salary desc,nvl(commission_pct,0) asc;
/* 先按工资降序排列，再按提成升序排列。order by 后可以用逗号分隔多条条件，且应注意字段中的null值，需要用到nvl。*/
select salary*12 from employees order by salary*12 desc;
/* 默认情况下是升序排列asc（ascending order），降序排列的关键字是desc（descending order）。*/


select salary,first_name from employees where lower(first_name)='david';
select first_name,salary from employees where upper(first_name)='DAVID';
/* 大小写的转换只是针对等号左边的数据库中的数据，转变之后与右边的字符进行比照。*/
select first_name,salary from employees where initcap(first_name)='David';
select first_name,salary from employees where first_name= initcap('DAVID');
/* (initcap)也可以针对等号右边的字母进行转换。*/


select substr(first_name,2,2) from employees;
/* 从first_name中字段中的第2位开始查，截取2位字符。如：Steven截取得te。*/
selece length(firsth_name) from employees;
/* 计算first_name字段中，各数据的长度。*/
select substr(first_name,length(first_name)-1,2) from employees;
/* 截取first_name中的最后两个字符。如：steven，长度为6，减1剩5，即，从第5为开始，截取2位。*/



/* select(4) from(1) where(2) group by (having)(3) order by(5) 执行顺序。*/



select a.dept_name, r.first_name,c.region_name from dept a, employees r, regions c where a.dept_id=r.department_id and a.regions_id=c.region_id and r.first_name='Steven';
/* 列出first_name是Steven的在哪个地区上班(需要用到的两张表中若没有直接的关联字段，此时，需要另找一张表将两张数据联系在一起。既:a=b,b=c。*/



select * from employees;
select e.first_name,s.first_name from employees e, employees s where e.manager_id=s.e

mployee_id;
/* 列出员工名称和他的领导的名称。（一张表，两个视图。自连接）。*/
select e.first_name,s.first_name from employees e, employees s where e.manager_id=s.employee_id and s.first_name='John';
/* 领导John有哪些员工 */
select e.first_nam
e,s.first_name from employees e, employees s where e.manager_id=s.employee_id and e.first_name='Steven';
/* Steven的领导是谁 */
select distinct s.first_name from employees e, employees s where e.manager_id=s.employee_id;
/* 哪些人是领导（内连接）*/


select e.first_name,s.first_name from employees e, employees s where e.manager_id=s.employee_id(+);
/*
列出员工和领导的对应关系
外连接=内连接+内连接匹配不上的记录。即，输出所有的记录（一边）。外连接做“不是”，内连接做“是”。
*/
select e.first_name,st_name,e.manager_id from employees e, employees s where e.manager_id=s.employee_id(+) and s.manager_id is not null
/* 哪些人不是领导 */
select e.first_name from employees e, employees s where e.manager_id= s.employee_id;
/* 哪些人是员工 */
select d. department_id from employees e, employees d where e.department_id= d.employee_id;
/* 哪些部门有员工 */
select d. department_id from employees e, employees d where e.department_id(+)= d.employee_id and d.employee_id is null;
/* 哪些部门没有员工 */



select * from employees;
select avg(salary) from employees;
/* 表中所有工资的平均值(avg) */
select count(first_name) from employees;
/* 计算表中有多少个名字(count) */
select min(salary) from employees;
/* 显示出表中的最低工资(min) */
select max(salary) from employees;
/* 显示出表中的最高工资(max) */
select job_id,avg(salary),count(salary) from employees group by job_id;
/* 各个职位的平均工资及各个职位中的人数 */
select department_id,avg(salary) from employees where department_id='30' group by department_id;
/* 30部门的平均工资（显示部门号、平均工资）*/
select s.salgrade,count(salgrade) from employees e, salgrade s where e.salary between s.losal and s.hisal group by salgrade;
/* 各个工资级别有多少人 */
select d.dept_name,r.region_name,avg(e.salary) from employees e, dept d, regions r where e.department_id=d.dept_id and d.regions_id=r.region_id group by d.dept_name,r.region_name;
/* 各个部门的平均工资（显示部门名称、地区名称、平均工资）（要显示部门和地区，部门又在地区范围之内，所以分组的时候既要依照部门也要依照地区）*/


select first_name,min(salary) from employees group by first_name;
select first_name,salary from employees where salary=(select min(salary) from employees);
/* 谁的工资最低(子查询）*/


select first_name from employees where job_id in (select job_id from employees where first_name=&#

39;Steven');
select e2.first_name from employees e1, employees e2 where e1.job_id=e2.job_id and e1.first_name='Steven';
/* 谁和Steven做同一个职位(非关联子查询、inner join) */
select * from employees where employee_id in(select manager_id from employees);
/* 哪些人是领导(非关联子查询）*/
select d.department_id from employees e,empl
oyees d where e.department_id(+)=d.employee_id and d.employee_id is null;
select * from employees where department_id not in(select department_id from employees where department_id is not null);
/* 哪个部门没有员工 */
select * from employees where employee_id not in(select manager_id from employees where manager_id is not null);
/* 哪些人不是领导（非关联子查询）*/
select e.first_name from employees e,employees s where e.manager_id=s.employee_id(+) and e.manager_id is not null;
/* 哪些人是员工 */
select * from employees where (department_id,salary) in (select department_id,avg(salary) from employees group by department_id);
/* 哪些员工的工资和本部门的平均工资一样 */
select avg(salary), department_id from employees group by department_id having avg(salary) >(select avg(salary) from employees where department_id='30' group by department_id);
/* 哪个部门的平均工资比30部门的平均工资高 */


create table test1(
id number (9),
/* 长度为9的数字 */
name varchar2(10),
salary number (6,2),
/* 长度为6，小数点后两位。如：9999.99 */
commission number(2,4)
/* 长度为2，小数点后4位。如：0.0099 */
);


desc test1;
/* 查看表结构 */
insert into test1 values(001,'张三',1111.11,0.0024);
/* 插入数据 */
drop table test1;
/* 删除表 */


select sysdate from dual;
/* 显示系统日期（二位年）*/
select sysdate+1 from dual;
/* 系统时间+1日 */
select to_char(sysdate+1/24/60*10,'yyyy mm dd hh24:mi:ss') from dual;
/* 系统时间+10分钟 */


create table testdate(
t_date date,
speak varchar(50)
);
insert into testdate values(to_date('28 11 2011','dd mm yyyy'),'你好');
insert into testdate values(to_date('2011 11 28 14:33:07','yyyy mm dd hh24:mi:ss'),'很好');
/* 插入时间的格式 */
select to_char(t_date,'yyyy mm dd') from testdate;
/* 取出时间 */


select * from employees;
select * from employees where to_char(hire_date,'mm')=3;
/* 求出三月份入职的员工 */


select months_between(sysdate, to_date('2012 12 09','yyyy mm dd')) from dual;
/* 系统时间与指定时间之间的时间差 */
select add_months(sysdate, 5)from dual;
/* 系统时间+5个月，最后的时间 */
select next_day (sysdate,'星期五') from dual;
/* 时间日期之后的下一个星期五 */
select last_day(to_date('2011 9 11','yyyy mm dd')) from dual;

/* 2011年9月11号的最后一天。即：2011年9月30日 */
select trunc(add_months(sysdate, 1),'month') from dual;
select last_day(sysdate)+1 from dual;
/* 系统时间之后的下个月一号 */


drop table test1;
create table students(
stu_id number(10) constraint stu_id_pk primary key,
stu_name varchar2(5)
);
create table course(
cou_id number(10) constraint cou_id_pk primary key,
cou_name varchar2(10)
);
create table stu_cou(
stu_id number(10) references students(stu_id),
cou_id number(10) references course(cou_id),
grade varch
ar(10)
);