/*
1:数据定义语言(DDL Data Definition Language)
1: create table xxx(
列名 数据类型,
。。。。。。
)
2:drop table xxx;
3:truncate table xxx;
4:alter table xxx
1:添加列
alter table xxx
add(列名 数据类型,
。。。。。。)
2:删除列
alter table xxx
drop column 列名;
3:修改列的数据类型
alter table xxx
modify (列名 新的数据类型,
。。。。。
)
4:修改列名
alter table xxx
rename column 列名 to 新的列名;
5: 修改表名
rename 表名 to 新的表名;
2:数据操作语言(DML Data Manipulation Language)
DML 都依赖事务支持 commit rollback;
1:insert into 表名(列名1,.......列名n)
values(列名1值,.....列名n值);
2:update 表名 set 列名1= 列名1新值, ...列名n= 列名n新值
[where 列名x=值 ]
3:delete from 表名 [where 列名x=值 ]
3:事务控制语言(Transaction Control Language TCL)
commit rollback
4:数据查询语言(DQL Data Query Language
select
5:数据控制语言(DCL Data Control Language)
grant revoke
*/
select e.ename,e.sal*12 yearsal from emp e
where e.sal>10000;
select distinct deptno,job from emp order by deptno;
select * from emp where substr(ename,1,1)='A';
select * from emp where ename like 'A%'
select * from emp where sal=30;
select * from emp where deptno in(10,20)
select * from emp where deptno=any(10,20);
select * from emp where ename like '%\_%' escape '\';
-- 生成一个订单号 生成规则 yyyyMMdd 跟一个
--10位的number类型的值 如不足10位就自动在左边补0
-- 201704250000000001
--201704250000000002 .... 201704250000010000
select to_char(sysdate,'yyyyMMdd')||
lpad(seq_student.nextval,10,'0') from dual;
create table ztkj_student(
id number,
name varchar2(10)
)
alter table ztkj_student
add constraint ztkj_student_pk primary key(id);
select * from ztkj_student;
/*创建序列 */
drop sequence seq_student;
create sequence seq_student
start with 1
increment by 1
maxvalue 9999999999;
select seq_student.nextval from dual;--查询序列的下一个值
select seq_student.currval from dual;--查询序列的当前值
insert into ztkj_student(id,name)
values (seq_student.nextval,'cccc')
create sequence seq_games
start with 1000
increment by 1
maxvalue 99999999999;
--cycle;
seq_games.nextval --获取下一个值
seq_games.currval --获取当前值
select seq_games.nextval from dual;
create table t_games(
id number,
name varchar2(50),
infos varchar2(4000),
constraint games_pk primary key (id)
)
insert into t_games(id,name,infos)
values(seq_games.nextval,'捕鱼达人','xxxxx');
select * from emp e,dept d where e.deptno=d.deptno;
--列出每个员工的名字、工资以及工资级别
select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
select deptno,count(1) from emp
where sal>2
group by deptno
having count(1)>2
select * from emp where deptno not in
(30,null);
select * from emp;
/* 快速创建表的方法 */
create table emp30 as
select * from emp where deptno=30;
select * from emp;
select * from emp30;
/*集合运算 */
select * from emp
union
select * from emp30;
/* union 求并集 自动去重 */
select * from emp
union all
select * from emp30;
/* union all 求并集 不去重 */
select * from emp
intersect
select * from emp30;
/* intersect 求交集 */
select * from emp30
minus
select * from emp;
/* minus 求差集 */
/*索引 相当于书的标签 用于提示查询速度
但是会降低 添 删 改 的效率
*/
/* 创建 索引 */
create index emp_enamesalIndex on emp(ename,sal);
/*删除索引 */
drop index emp_salIndex;
select * from emp where sal=1250;
select * from emp where lower(ename)='king';
select * from emp where ename=upper('king');
/*视图 view
视图是 查询结果的一个结果集 虚拟的表
创建视图
create view 视图名 as select ...
*/
drop view empview20;
create view empview20 as
select * from emp where deptno=20;
select * from empview20;
update empview20 set deptno=30;
/* with check option
可以保证视图的创建条件不能被改变
*/
drop view ztempview20;
create view ztempview20 as
select * from emp where deptno=20
with check option;
update ztempview20 set deptno=30;
update ztempview20 set sal=sal+100;
select * from ztempview20
/*with read only
可以保证视图的的数据不能修改*/
create view ztempview30 as
select * from emp where deptno=30
with read only;
update ztempview30 set sal=sal+100;
/* rownum 伪列 rownum 只能<= 不能 >*/
select * from
(select e.*,rownum n from emp e) t where t.n<=3
select * from
(select e.*,rownum n from emp e) t
where t.n between 7 and 9;
select * from
(select e.*,rownum n from emp e
where rownum<10 )t
where t.n>6