SQL语言:DML、DCL、TCL
SQL语言
一、DML
DATA MANIPULATION LANGUAGE 数据操纵语句
只要发起一个DML语句,此时会产一个事务,这个事务是需要TCL语句来控制的;
- INSERT
插入数据到指定
INSERT INTO 表名 (employee_id,last_name,salary) VALUES(100,'liu',20000);
INSERT INTO 表名 values(值与表的列一对一进行匹配插入);
例:
INSERT 一次只能插入一条数据;
快速拷贝数据:
insert into emp select * from employees;
insert into emp select * from emp;
- UPDATE
修改(更新)指定表的现有数据
UPDATE EMP SET SALARY=300 ;
不加限制条件,是修改整个表的记录
UPDATE EMP SET SALARY=500 WHERE LAST_NAME='liu';
UPDATE EMP SET SALARY=500 WHERE SALARY=1;
UPDATE EMP SET SALARY=500 WHERE salary is null;
UPDATE EMP SET SALARY=500 WHERE salary is not null;
UPDATE EMP SET SALARY=500 WHERE LAST_NAME LIKE 'zh%';
例:
COMMIT;保存
ROLLBACK;撤回上一步
- DELETE
删除指定中的现有数据
DELETE FROM EMP;
DELETE EMP;
DELETE EMP WHERE salary >=10000;
delete from emp where salary < 10000;
例:
commit;
rollback;
- MERGE
组合,把INSERT、UPDATE、DELETE三个命令做判断
create table A_MERGE
(
id NUMBER not null,
name VARCHAR2(12) not null,
year NUMBER
);
create table B_MERGE
(
id NUMBER not null,
aid NUMBER not null,
name VARCHAR2(12) not null,
year NUMBER,
city VARCHAR2(12)
);
create table C_MERGE
(
id NUMBER not null,
name VARCHAR2(12) not null,
city VARCHAR2(12) not null
);
commit;
insert into A_MERGE values(1,'liuwei',20);
insert into A_MERGE values(2,'zhangbin',21);
insert into A_MERGE values(3,'fuguo',20);
commit;
insert into B_MERGE values(1,2,'zhangbin',30,'jilin');
insert into B_MERGE values(2,4,'yihe',33,'hljian');
insert into B_MERGE values(3,3,'fuguo',null,'shandong');
commit;
MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID)
WHEN MATCHED THEN
UPDATE SET A.YEAR=C.YEAR
WHEN NOT MATCHED THEN
INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);
commit;
查找a表和b表中 a.id和c.aid相同的行
然后把c.year赋值给a.year
如果没查到与c.aid相同的行则插入C.AID,C.NAME,C.YEAR到A表中
二、DCL
DATA CONTROL LANGUAGE 数据控制语句,允许指定用可以有哪些权限,做什么事情
GRANT
授权
数据库中对于权限管理有2种方式:
权限授予
角色来授予:角是权限或者子角色的的集合,数据库中有2类权限:
1---系统权限,创建会话,创建表、创建视图,创建索引等
select * from dba_sys_privs; 查询数据库中有哪些系统权限
2---对象权限,是指用户A想去访问用户B的对象,此时需要用户B把相关权限授予给A
也可以使用数据库管理员权限授权
SELECT * FROM DBA_TAB_PRIVS; 查询数据库中有哪些对象权限
在创建数据库的时候,系统就有一些预定义的角色
SELECT * FROM DBA_ROLES;
查询数据中所有的角色
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='DBA';
查询DBA角色包含哪些系统权限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='DBA';
查询DBA角色包含哪些对象权限
查询一个用户拥有什么角色:
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='LOUIZ';
查询当前用户拥有哪些权限:
select * from session_privs;
三、TCL
TRANSACTION CONTROL LANGUAGE 事务控制语句
COMMIT----确认提交数据,让数据生效
ROLLBACK----反悔,让数据不生效
四、练习
select employee_id,last_name,job_id,department_id
from employees
where department_id=90;
select last_name,job_id,department_id
from employees
where last_name ='Whalen';
select last_name, hire_date
from employees
where hire_date = '07-MAR-06';