- 论坛徽章:
- 0
|
约束类型: 实体完整性 列的唯一性,保证数据的唯一性:alter table nn add constraint pk_nn primary Key(n1); 域完整性: 整形的插入字符型报错 参照完整性 在表mm的n1字段上添加一个参照完整性约束,参照表nn的n1字段。 alter table mm add constraint fk_mn froeign key(n1) references nn(n1); 查看约束: select * from user_constraints where table_name='tablename'; 删除约束: alter table tablename drop constraint constraintname; 管理索引 在列上创建索引 create index my_mm_idx on mm(n1); 创建位图索引(值少) create bitmap index bit_*** on mm(***); 创建唯一索引: create unique index un_*** on mm(n1); 删除索引: drop index un_***; 数据定义语言DDL create alter drop 数据控制语言
数据操纵语言 create table abc(a varchar2(10),b char(10)); alter table abc add c number; drop table abc; alter table abc drop column c;//删除c字段 grant select on dept to tt_user;//授权tt_user可以查看dept表 revoke select on dept from tt;//收回用户tt查看dept表 insert into abc(a,b) values('abc','xy'); update abc set b='ttt'; update abc set b='ttt' where a='abc'; delect from abc;//清空abc里面的数据 delect from abc where a='abc';//删除a为'abc'的记录 select length('abcdef') from dual; select length('ab好cd') from dual;//一个汉字占两个字节。 select ltrim(' abc') from dual;//截断左边的空格 select length(ltrim(' abc')) from dual; select rtrim('abc ') from dual;//截断右边的空格 select trim(' abc ') from dual;//截断左右两边的空格 select substr('abcdef',2,3) from dual; select substr('abcdefg',length('abcdefg')-3+1,3) from dual;//取最后3个字符 select substr('abcdefg',1,3) from dual;//去左边三个字符 select sysdate from dual; select current_date from dual; alter session set nls_date_format='dd-mon-yyyy hh:mi:ss'; select next_day(sysdate,'星期三') from dual;//查询下一个星期三的日期 select max(price) from books;//求价格的最大值 select min(price) from books;//……最小值 select sum(price) from books;//……总和 select avg(price) from books;//……平均值 select count(price) from books;//查询包含价格的记录数 select count(*) from books;//查询字段的行数 //统计男女人数 select sum(decode(***,'男',1,0)) 男人数,sum(decode(***,'女',1,0)) 女人数 from e;// //对空值进行判断 select * from aa where a2 is null;//为空 select * from aa where a2 is not null;//不为空 select * from aa order by a1 asc;//升序排列 select * from aa order by a1 desc;//降序排列 select distinct * from aa;//去掉重复行 //分组进行查询 select pub,sum(price*qty) from books group by pub;//按出版社进行汇总 //***函数经常和having字句连用,聚类函数不能放在where字句当中。 select pub,sum(price*qty) from books group by pub having sum(price)>300; //计算a1重复出现的次数 select a1,count(a1) from aa group by a1 having count(a1)>1; //模糊查询 select * from aa where a1 like 'a_';//以a字符打头只有两个字符的记录 select * from aa where a1 like 'a%';//以a字符打头的记录 select * from aa where a1 like '%a';//以a字符结尾的记录 select * from aa where a1 like '%a%';//含a字符的记录 表的连接 员工表 create table e(eid number,ename varchar2(20),*** char(2),id number); insert into e(eid,ename,***,id) values(001,'赵1','男',01); insert into e(eid,ename,***,id) values(002,'钱2','女',02); insert into e(eid,ename,***,id) values(003,'孙3','男',03); insert into e(eid,ename,***,id) values(004,'李4','女',04); insert into e(eid,ename,***,id) values(005,'王5','女',05); insert into e(eid,ename,***,id) values(006,'张6','男',06); 部门表: create table d(id number,name varchar2(20)); insert into d(id,name) values(01,'A部门'); insert into d(id,name) values(02,'B部门'); insert into d(id,name) values(03,'C部门'); insert into d(id,name) values(04,'D部门'); insert into d(id,name) values(05,'E部门'); insert into d(id,name) values(06,'F部门');
//SQL语句 select eid 编号,ename 姓名,*** 性别,id 所在部门 from e; //内连接 select eid 编号,ename 姓名,*** 性别,d.id 所在部门 from e,d where e.id=d.id; select eid 编号,ename 姓名,*** 性别,d.id 所在部门 from e join d on e.id=d.id; //外连接包括左连接和右连接 //左连接:以左边的表为主全显示出来,能显示则显示,不能显示则用空值替代 select eid 编号,ename 姓名,*** 性别,d.id 所在部门 from e,d where e.id=d.id(+); //右连接:以右边的表为主全显示出来,能显示则显示,不能显示则用空值替代 select eid 编号,ename 姓名,*** 性别,d.id 所在部门 from e,d where e.id(+)=d.id; //子查询,包括无关子查询和相关子查询,子查询里面不能出现*。 select * from e where id in (select id from d); select * from e where id in (select id from d where id=e.id); select * from e where exists (select id from d); select * from e where not exists (select * from d);//此处可以使用* //表连接,合并 select id from e union select id from d; //两个表都有,intersect,oracle独有的 select id from e intersect select id from d; //批量加入 insert into e(eid,ename) select id,name from d; //复制表 create table ttt as (select * from e); create table ttt as (select eid,ename from e where eid='001');
|
|