--concat连接两个字符串
select concat('hello',' world') from dual;
--instr返回c2在c1次出现的位置,从i开始搜
select instr('Messississiq','i',3,2) from dual;
--保留参数,不四舍五入
select trunc(1234.567,2) from dual;--不四舍五入
--取余数
select mod(13,3) from dual;
--自然连接
select s.s_id,s.s_name,s.s_age,c.c_name from student s natural join sclass c;
--有join 就用on代替where
select * from student s join sclass c on s.c_id=c.c_id;
--消除相同数据项使用Distinct来消除
select distinct department_id from employees;
--查询员工薪资大于8000并且小于12000的所有信息
select last_name,salary from employees where salary>=8000 and salary<=12000;
select * from employees where salary between 8000 and 12000;
-- 与 或 非的体现形式
|| or !=
--查询姓名中带b的人(分大小写)
select last_name from employees where last_name like '%b%';
--%代表不限字符数量的查询,结果之前或者之后带有指定字符的数据
--_代表指定字符查询结果的指定字符数量
select last_name from employees where last_name like 'B%';
select last_name from employees where last_name like '%a';
select * from student where s_name like '_福';--未查到
select * from student where s_name like '_福_';--已查到
--指定查询部门编号为60的人
select last_name,department_id from employees where department_id=60;
--指定查询部门编号为60和90的人
select last_name,department_id from employees where department_id in(60,90);
--指定查询部门编号不为空的人的姓名(is null,is not null)
select last_name,department_id from employees where department_id is null;
select last_name,department_id from employees where department_id is not null;
--on关键字,如果在使用join连接查询时,那么消除笛卡尔积必须使用on代替where
select s.s_id,s.s_name from student s join sclass c on s.c_id=c.c_id;
--left outer join 和right outer join的区别:
--左外链接显示结果以左表为主要显示对象
--右外链接显示结果以右表为主要显示对象
select s.s_id,s.s_name from student s right outer join sclass c on s.c_id=c.c_id;
select * from employees e left outer join departments d on d.department_id=e.department_id;
--full outer join 全外链接 左右都全显示
select * from employees e full outer join departments d on d.department_id=e.department_id;
--查询名为Abel的人所在的部门哪些人大于他的工资
--1.先查询Abel的工资
select salary from employees where last_name='Abel';
--2.查询Abel所在的部门
select department_id from employees where last_name='Abel';
--3.查询哪些人大于他的工资
select last_name,salary,department_id from employees where department_id=(select department_id from employees where last_name='Abel')
and salary>(select salary from employees where last_name='Abel');
--查询编号为105的雇员所在的部门的所有人的编号,姓氏和薪水
--1.查询编号为105的雇员所在的部门
select department_id from employees where employee_id=105;
--2.查询该部门中所有人的编号,姓氏和薪水
select employee_id,last_name,salary from employees where department_id=(select department_id from employees where employee_id=105);
--查询编号为105的雇员所在的部门的人高于105的薪水的人的编号,姓氏和薪水
--1.查询编号为105的雇员所在部门
select department_id from employees where employee_id=105;
select salary from employees where employee_id=105;
--2.查询该部门所有人的薪水
select salary from employees where department_id=(select department_id from employees where employee_id=105);
--3.查询该部门薪水高于105的人
select last_name,salary from employees where department_id=(select department_id from employees where employee_id=105)
and salary>(select salary from employees where employee_id=105);
--查询员工105所在部门的高于部门平均工资的人的编号,姓氏和薪水
--1.首先查询编号为105的人所在的部门
select department_id from employees where employee_id=105;
--2.查询该部门的平均工资
select avg(salary) from employees where department_id=(select department_id from employees where employee_id=105);
--3.查询该部门高于平均工资的人
select last_name,salary from employees where department_id=(select department_id from employees where employee_id=105)
and salary>(select avg(salary) from employees where department_id=(select department_id from employees where employee_id=105));
--查询公司哪些人的薪水和部门编号为60的部门里的人的薪水相同
--1.查询公司里所有人的薪水
select salary from employees;
--2.查询部门编号为60的部门的所有人的薪水
select salary from employees where department_id=60;
--3.查询哪些人和部门60的人的薪水相同
select last_name,department_id,salary from employees where salary in (select salary from employees where department_id=60);
--查询公司里哪些人的工资高于部门编号为60的人里面最高的人的工资
--1.查询公司里所有人的工资
select salary from employees;
--2.查询部门编号为60的部门里谁的工资最高
select max(salary) from employees where department_id=60;
--3.查询公司所有人里面谁的工资高于部门60的最高人的工资
select last_name,salary from employees where salary>(select max(salary) from employees where department_id=60) order by salary;
--查询每个部门的平均工资,要求查询出部门编号和部门平均工资
--1.查询每个部门的编号
select department_id from departments;
--2.查询每个部门编号和部门平均工资
select department_id,avg(salary) from employees group by department_id;
--查询每个部门的平均工资大于8000的部门的部门名称和平均工资
select d.department_name as "部门名称",avg(e.salary) as "平均工资" from departments d,employees e
where e.department_id=d.department_id
group by d.department_name having avg(e.salary)>8000 order by avg(e.salary);
--视图
view
--复制表和数据
create table tmpStu2
as
select * from student;
select * from tmpStu2;
--复制表结构而不复制数据的需求(只需要一个不成立的条件让其无法复制表数据)
create table tmpStu
as
select * from student where 1=2;
select * from tmpStu;
--创建语法:create view 视图名
--as
-- 查询语句;
create view avgSalary
as
select d.department_name as "部门名称",avg(e.salary) as "平均工资" from
employees e,departments d where e.department_id=d.department_id
group by d.department_name;
select * from avgSalary;
--文件夹包在Indexes
--加多了程序会变慢,一般一个表加一个就好
create index selectSname
on student(s_name);
--索引:建立在字段上面,主要体现在查询速度上
--前提是有海量的查询数据
--文件夹包在Procedures
--procedure 创建一个存储过程 eAvg out输出参数,返回一个number类型
--存储过程类似于java中的方法,可以有返回值或者参数
--调用,,显示在SQL右边的输出,表中
--数据库系统内调用存储过程方式如下
--如果有返回值,使用declare 返回值名称 返回值数据类型
--存储过程名称(按照声明时的顺序填入参数)
--触发器:在特定环境下执行的特定的语句
--当删除学生表中的数据时,自动将所删除的数据添加到临时表中(以便于找回)