sql语句3

//数据库更新操作----------------------------------------------------------------------------------------------------------
//将表结构及数据完成的复制出来
create table myemp as select * from emp;

select * from myemp where empno=7899;

//添加数据-------------------------------------------------------------------------------------------------------------------
insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7899,'张三','清洁工',7368,to_date('1995-2-14','yyyy/mm/dd'),9000,300,40);
insert into myemp values(7900,'王五','清洁工',null,to_date('1996-3-02','yyyy-mm-dd'),9000,null,40 );

//修改数据------------------------------------------------------------------------------------------------------------------------
update myemp set comm=1000;
update myemp set sal=5000 where empno=7899;
update myemp set mgr=null where empno=7899;
update myemp set comm=null where empno in(7899,7900,7788);

//删除数据-------------------------------------------------------------------------------------------------------------------------
delete from myemp where empno=7899;
delete from myemp where comm is not null;

//删除表的全部内容---------------------------------------------------------------------------------------------------------------
delete from myemp;

//事务处理------------------------------------------------------------------------------------------------------------------------------
commit 提交事务
rollback 回滚事务

//常用数据类型
varchar、varchar2、number、date、clob(大文本数据可存放4G)、blob(二进制数据可存放4G)

//复制表------------------------------------------------------------------------------------------------------------
//复制表结构和内容
create table tmep as (select * from emp);
//只复制表结构
create table tmep as (select * from emp where 1=2);

//创建表------------------------------------------------------------------------------------------------------------
create table person(
pid varchar2(18),
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default'男'
);

//primary key 主键约束
create table person(
pid varchar2(18)primary key,
name varchar2(200),
age number(3),
birthday date,
sex varchar2(2) default'男'
);

//not null 非空约束
create table person(
pid varchar2(18)primary key,
name varchar2(200) not null,
age number(3) not null,
birthday date,
sex varchar2(2) default'男'
);

//unique 唯一约束
create table person(
pid varchar2(18)primary key,
name varchar2(200) unique not null,
age number(3) not null,
birthday date,
sex varchar2(2) default'男'
);

//check 检查约束
create table person(
pid varchar2(18)primary key,
name varchar2(200) unique not null,
age number(3) not null check(age between 0 and 150),
birthday date,
sex varchar2(2) default'男' check(sex in('男','女','中'))
);

//foreign key 主-外键约束
create table person(
pid varchar2(18),
name varchar2(200) not null,
age number(3) not null ,
birthday date,
sex varchar2(2) default'男' ,
cityid varchar(18),
constraint person_pid_pk primary key(pid),
constraint person_name_uk unique(name),
constraint person_age_ck check(age between 0 and 150),
constraint person_sex_ck check(sex in('男','女','中')),
constraint city_person_cityid_fk foreign key(cityid) references city(cityid)
);

//修改约束-------------------------------------------------------------------------------------------------------
//添加约束
alter table person add constraint person_pid_pk primary key(pid);
alter table person add constraint person_name_uk unique(name);
alter table person add constraint city_person_cityid_fk foreign key(cityid) references city(cityid);

//删除约束
alter table person drop constraint person_name_uk;

select * from person;

//删除表------------------------------------------------------------------------------------------------------------
drop table person;

//修改表----------------------------------------------------------------------------------------------------------------
//添加列
alter table person add(address varchar(200) default'暂无地址');

//修改表结构
alter table person modify(name varchar(100) default'无名氏');

//修改表名
rename person to tperson;

//截断表 (清空表数据，立刻释放资源不需要回滚)
truncate table person;

//rownum 伪列
select rownum,empno,ename,job,sal,hiredate from emp;
//分页查询
select * from (select rownum rn,empno,ename,job,sal,hiredate from emp where rownum <= 10) temp where temp.rn>5

登陆管理员用户（sysdba）为当前用户分配常见视图权限：grant create view to scott;

//创建视图
create view empv20 as select empno,ename,job,hiredate,deptno from emp where deptno=20 with read only;

select * from empv20;

//删除视图
drop view empv20;

//替换视图
create or replace view as select empno,ename,job,hiredate from emp where deptno=20;

select * from emp where empno=7369;

//修改视图
update empv20 set job='CLERK' where empno=7369;

//序列-------------------------------------------------------------------------------------------
//创建序列
create sequence myseq;

//increment by n 每次增长幅度
create sequence myseq increment by 2;

//start with 指定开始位置
create sequence myseq increment by 2 start with 10;

//循环序列
create sequence myseq maxvalue 10 increment by 2 start with 1 cache 2 cycle;

//删除序列
drop sequence myseq;

create table testseq(
next number,
curr number
);
insert into testseq values(myseq.nextval,myseq.currval);
select * from testseq;
drop table testseq;

//同义词---------------------------------------------------------------------------------------
//创建同义词
create synonym semp for SCOTT.EMP;
//删除同义词
drop synonym emp;

//用户管理---------------------------------------------------------------------------------------
//创建用户--在管理员用户下
create user test identified by test123;

//分配session权限给用户
grant create session to test;

//一次性把多个权限分配给用户
grant connect,resource to test;

//修改用户密码
alter user test identified by test1234;

//用户密码失效
alter user test password expire;

//锁住用户
alter user test account lock;

//用户解锁
alter user test account unlock;

//将scott用户的emp表的查询及删除权限给test用户
grant select,delete on scott.emp to test;

//回收权限
revoke select,delete on scott.emp from test;