plsql笔记

查询：

select employee_name from employees;

select employee_name from employees where employee_age > 25;

select e.employee_name, s.salary from employees e, salary s where e.employee_id =  s.employee_id;

select distinct e.employee_name, s.salary from employees e, salary s where e.employee_id =  s.employee_id;

distinct关键字用于获取结果集中列e.employee_name 与 s.salary的唯一性组合（distinct相当于去重）

select e.employee_id, e.employee_name,sum(s.salary)  total_salary from employees e, salary s where e.employee_id =  s.employee_id group by e.employee_id, e.employee_name;

select e.employee_id, e.employee_name, sum(s.salary)  total_salary from employees e, salary s where e.employee_id =  s.employee_id group by e.employee_id, e.employee_name having (sum(s.salary)) > 10000;

select distinct e.employee_name, s.salary from employees e, salary s where e.employee_id = s.employee_id order by s.salary desc;

select e.employee_id, e.employee_name, sum(s.salary)  total_salary from employees e, salary s where e.employee_id =  s.employee_id group by e.employee_id, e.employee_name order by total_salary desc;

order by 子句与distinct关键字同时使用时，也必须遵循一个规则--------order by 子句所指定的排序列必须出现在select表达式中。

select * from employees where employee_id in (select employee_id from salary);子查询

联合语句：union(并集且去重),union all（并集不去重，效率高） , intersect（交集）, minus（差集）

联合语句可以进行混合运算，优先级相同，依次运行。

select student_id, student_name, student_age from a_students union select student_id, student_name, student_age from b_students;

两个查询的列数且数据类型必须相同。

minus是集合间的减法运算，该运算返回第一个集合中存在的，而第二个集合中不存在的记录。

连接：

自然连接natural join：原则，两个数据源的共有列，且具有相同的列值。

select * from employees natural join salary.

不能为公共列employee_id添加限定词，即不能写成employees.employee_id。

对于自然连接外的其他列可以使用限定词。

例如：

select employee_id, e.employee_name, s.month, s.salary from employees e natural join salary s ;

内连接inner join：

select e.employee_id, e.employee_name, s.month, s.salary from employees e  (inner/可省略) join salary s on e.employee_id = s.employee_id

on为指定搜索条件。

此举与where结果相同，当设计表比较多时使用join比较好。

select * from employees join salary on ... join company on ... join sales on ...;

外连接left join和right join：

左连接即以左表为基础，右连接即以右表为基础。

select e.employee_id, e.employee_name, s.month, s.salary from employees e left join salary s on e.employee_id = s.employee_id

本表以employees为基础，将salary表连接到本表，这样有些没有领工资的员工也会出现子啊结果中。

简写:

select e.employee_id, e.employee_name, s.month, s.salary from employees e,salary s where e.employee_id = s.employee_id(+)

s.employee_id(+)即表示salary为附属表，当多个数据源连接是最好不使用简写。

完全连接full join：是一个左连接和一个右连接的组合，最后去除重复记录。结果与两个数据源顺序无关。

select e.employee_id, e.employee_name, s.month, s.salary from employees e  full join salary s where e.employee_id = s.employee_id

层次化查询（遍历树）：

select market_id, market_name from market start with market_name = '亚洲'connect by prior market_id = parent_market_id;

start_with为起始条件，connect by 指定如何递归下一条记录，且遵循深度优先搜索策略，prior指前一条记录。prior market_id即为前一条记录的market_id 等于下一条记录的parent_market_id。

插入数据：

insert into students (student_id,student_name)values(1, '扎古斯那')

批量插入：

insert  into c_students(student_id, student_name) select student_id, student_name from students where student_id < 10;

更新数据:

update 表名 set 列1=“hhh”， 列2 = “1111”  where 。。。

删除数据：

delete from students where student_id > 10;

truncate table students;

truncate table删除全部数据，且不可回滚恢复。

创建数据库表：

create table student (student_name varchar 数据类型，student_age varchar 数据类型，。。。。)tablespace users.

增加字段

alter table student add(class_id number);

修改数据类型：

alter table student modify(class_id varchar2(20) );

删除已有列：

alter table student drop column class_id;

字段重命名：

alter table studentrename column student_id to id;

表空间转移：

alter table student move tablespace users;

删除数据库表：

drop table student;

drop table student cascade constraints;  cascade constraints将与本表主键用作外键的其他表的约束删除，否则只删除student时无法删除。

字符处理方法：

向左补全字符lpad（）函数：

lpad(string, padded_length, [pad_string])

select lpad('1', 4, '0') employee_no from dual; 结果为0001；

向右补全字符串-----rpad（）函数：

select rpad('1', 4, '*')  employee_no from dual;结果为1***；

返回字符串的小写-----lower（）函数：

select  username, password from dba_users where lower(username) = 'system';

返回字符串的大些形式----upper（）函数：

select  username, password from dba_users where upper(username) = 'system';

首字母大写initcap()：

select initcap ('we all like bike') new_string from dual;结果为We All Like Bike;

返回字符串长度length（）函数：

select length('12345') len from dual;

截取字符串substr（）函数：

substr(string, start_index, length)

select substr('123456789', 2, 3) sub_string from dual; 结果为234，oracle字符串下表从1开始

获取字符串出现的位置---instr（）函数

instr (string, sub_string, start_index, times)

select instr('123456789', '56', 2, 3) position from dual;从第2个字符搜索‘56’，直到第三次出现‘56’，因为没有，所以结果为0；

删除字符串左侧空格-----ltrim（）函数：

select ltrim('        000') new_str from dual;

删除字符串右侧空格-----rtrim（）函数：

select ltrim('000        ') new_str from dual;

删除字符串两侧空格-----trim（）函数：

select trim('       000       ') new_str from dual;

串联字符串-----concat（）函数：

select concat('hello', '  word') new_str from dual;结果为hello  word，concat只有两个参数，只能连接两个字符串。

翻译字符串-----translate（）函数：

反转字符串-----reverse（）函数：

select reverse('abcdfgh') from dual；结果为hgfedcba

复杂数据处理

求最大值-----max（）函数：

select max(employee_age) max_age from employee;

求最小值-----min（）函数：

select min(employee_name) min_name from employees;

求平均值-----avg（）函数:

select avg(employee_age) avg_age from employees;

求和-----sum（）函数：

select sum(employee_age) sum_age from employees;

统计记录数-----count（）函数：

统计单列： select count(employee_name)  count_name, count(employee_position)  count_position from employees;

统计所有列：select count(*) from employees;

利用count（1）进行统计：select count(1) from employees;