多表连接查询
介绍一下表别名。如果在多表查询的时候出现了多个相同名字的列。于是我们就采用了表别名的方式,是给各个表一个简短的名称。
例如:select em.employee_id,em.last_name,dep.department_name
from employees em,department dep
where em.department_id = dep.department_id
and dep.department_name = 'Shipping';
如果表指定了别名,那么语句中所有的子句都必须使用别名。不允许使用实际的表名。
简单连接
简单连接仅仅是通过select和from来连接多个表实现的。通过笛卡儿积所生成的表。(两个表的行数的积)。
join 连接
分为内连接,外连接,自连接,自然连接。
大体语法 from 表名 连接类型 表名 [连接条件]
内连接:
inner join此关键词中的inner可以省略,但是必须有on子句作为连接条件,
进行多表查询的时候,查询的结果集中仅包含符合查询条件和连接条件的行。例如:
select a.*,b.* from a inner join b on a.id=b.parent_id 就是简单的把两个表连接起来。
自然连接:
oracle将第一个表中的列与第二个表中相同的列进行自然连接。不需要用on 来指定。有好有坏。现实中表名相同但实际含义不一定相同。
select em.employeeid,em.firstname,lastname,dep.departmentname from
from employees em natural join deparment dep
where dep.departmentname='sales';
外连接:
与内连接不同的是,外连接除了会返回所需要的结果集,还会返回一部分或全部不匹配的行。
分为:左外连接(列出左表中所有符合搜索条件的数据行),右外连接(列出右表中所有符合搜索条件的数据行),和全连接三种(列出两个表表中所有符合搜索条件的数据行)。
例如:
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
左外连接:
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
右外连接:
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
全连接:
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
自连接
用户可能拥有自引用式外键。一个表中的某一列可能是该表主键的一个外键。比如employees表的manager_id列可以是另一行的employee_id。
集合操作:
集合操作就是两个或多个sql查询结果合并构成符合查询。常见的有union(并运算)union all,intersect(交运算),minus(差运算)
union(并运算) 将查询结果相加,结果等同于集合运算中的并运算。相当于or
例如:
select employee_id,last_name
from HR.EMPLOYEES
where last_name like 'C%' or last_name like 'S%' (这个列出c,s开头的雇员)
union
select employee_id,last_name
from HR.EMPLOYEES
where LAST_NAME like 'S%' or last_name like 'T%'; (列出S,T开头的雇员)
from HR.EMPLOYEES
where last_name like 'C%' or last_name like 'S%' (这个列出c,s开头的雇员)
union
select employee_id,last_name
from HR.EMPLOYEES
where LAST_NAME like 'S%' or last_name like 'T%'; (列出S,T开头的雇员)
结果就是C或者S或者T开头的雇员的信息均被列出。
union all
与union不同之处是操作符形成的结果集中包含有两个子结果集中重复的行。
intersect(交运算)
相当于and,是交集运算。列出c,s开头的雇员,列出S,T开头的雇员,结果是返回以S开头的雇员。
,minus(差运算)
在两个给定的集合之间的差,该集合操作符会返回所有从第一个查询中返回的,但是没有在第二个查询中返回的记录。
按上面的例子来说:列出c,s开头的雇员,列出S,T开头的雇员,结果是返回以C开头的雇员。
子查询
子查询和连接查询一样,都提供了使用单个查询访问多个表中数据的方法。子查询是一个select语句,它可以在select,insert,update,delete语句中使用。也可以在select和having子句中使用子查询。
in 关键字
/*查询所有部门在1700地区的雇员信息*/
select employee_id,last_name,department_id
from HR.EMPLOYEES
where department_id in ( (首先执行括号内的子查询,然后再执行外层查询)
select department_id
from departments
where location_id = 1700);
select employee_id,last_name,department_id
from HR.EMPLOYEES
where department_id in ( (首先执行括号内的子查询,然后再执行外层查询)
select department_id
from departments
where location_id = 1700);
exists 关键字
只注重子查询是否返回行,如果返回有值,则为true,否则为false。
select employee_id,last_name from employee em
where exists(
select * from department dep
where em.department_id=dep.department_id
and location_id =1700); 这个和in相同。
where exists(
select * from department dep
where em.department_id=dep.department_id
and location_id =1700); 这个和in相同。
比较运算符=,<>,<,>,<=,>=
数据操纵
包括插入、删除、和修改三种操作。对呀insert,delete,update。oracle中还有truncate,call,explain plan、lock table、merge。
insert 是插入语句
insert into 要插入的表名【数据库链接名】【列名】 values (要插入的值);
例如:insert into jobs(job_id,job_title,min_salary,max_salary) values (' * ','*',300,800);
批量insert
使用select语句替换values,由select语句提供添加的数据。
语法:insert into 要插入的表名【数据库链接名】【列名】 select查询语句
相当于从其他表里读出来,放到要添加的这个表里。
方法2:
insert into persons
(id_p, lastname , firstName, city )
values
(200,'haha' , 'deng' , 'shenzhen'),
(201,'haha2' , 'deng' , 'GD'),
(202,'haha3' , 'deng' , 'Beijing');
update语句:修改表中的一列或多列的值。
语法: update 表名 set 用于设置要更新的列以及各列的新值
where 限定的条件;(限定指定的表。)
update employees
set salary = salary * 1.15
where job_id ='it_prog';
delete 语句
用来删除表或者表中的哪一条记录。
delete from 表名 where 限定条件
比如:delete from employees where employee_id =107;
truncate 语句
用户用于删除表中所有的记录,则建议使用truncate语句。但是执行的truncate操作也不能被撤销。
truncate table it_employees;
数据控制,主要介绍sql的安全性控制。关系到授权什么的。
grant 和 revoke 语句来决定授权
授权的结果存入数据字典。
当用户提出操作请求时,根据授权情况进行检查,决定是否执行操作请求。
grant 用于向用户操作权限。一般格式为grant 权限。
语法: grant 权限 on 要把那张表的权限赋值给用户 to 用户
其中,grant 权限名称 on TABLE 表名 to 用户 WITH GRANT OPTION。后面这句话的意思是允许该用户再将权限给其他用户,如果不加的话则不行。
相关权限。
对象 | 对象类型 | 操作权限 |
属性列 | table column | select,insert,update,delete,all privileges |
视图 | table view | select,insert,update,delete,all privileges |
基表 | table | select,insert,update,delete,all privileges,index |
数据库 | database | createtable |
比如:grant select on table employees to user1;
grant all privileges on table employees,jobs to user2,user3;
REVOKE语句
将授予的权限收回。一般格式:
revoke 权限 on 表名 from 用户
revoke update on table employees from user4;