Oracle SQL语句实例

Oracle SQL语句实例

1. alter user scott account unlock;--给用户解锁   
2. connect sys/bjsxt as sysdba; --以系统超级管理员登陆   
3. drop user liuchao cascade; --删除用户liuchao   
4. exp -- 导出用户的相应信息   
5. create user liuchao identified by liuchao default tablespace users quota 10M on users--  用户名是liuchao密码也是liuchao   
6.                                                                     --默认的表空间是users大小为10M   
7. grant create session, create table, create view to liuchao; --create session 是登陆的权限。   
8. imp --导入数据用的   
9.   
10.   
11. /**   
12.  * 第一大类：select语句   
13.  */   
14. desc emp;--描述表emp   
15. select ename from emp where rownum<=5; --在Oracle取数据的时候，其实尾部都带了一行rownum   
16. select enanem from (select rownum r, ename from emp) where r>10; --当用大于号的时候，只能这么操作，直接向上面那样操作不行。   
17. select ename, sal*12 from emp;   
18. select 2*3 from emp; -- 表里面有多少行就显示多少行   
19. desc dual;   
20. select 2*3 from dual;-- 只有一个结果   
21. select sysdate from dual;--显示系统时间   
22. select ename, sal*12 annual_sal from emp;--起个别名，annual_sal中间不能有空格，如果想有空格先用" "套起来。   
23.                                          --加双引号还有个作用，可以区分大小写。不加的话都会转换为大写。   
24. select ename, sal*12+comm from emp;--任何含有空值的数学表达式，最后结果都是空值。   
25. select ename, sal*12+nvl(comm,0) from emp;--处理掉空值   
26. select ename || 'asdfgg' from emp;--在sql语句中连接字符串都用 || 符号。   
27. select ename || 'asd''fgg' from emp;--可以用两个单引号代表一个单引号。   
28. select ename, sal, comm from emp where comm is null;--对于空值的处理不能用=符号，而要用is。   
29. select ename, sal, comm from emp where comm is not null;   
30. select ename, sal, comm from emp where sal in(800, 1500, 2000);--in 语句：取其中的一个值   
31. select ename, sal, comm from emp where ename in('SMITH', 'KING', 'ABC');--in 语句也可对字符串进行操作   
32. select ename, sal, hiredate from emp where hiredate > '20-2月-81';--必须按照相关的格式，但是年份可写成'20-2月-1981'   
33. select ename from emp where ename like "_A%";--_代表一个字母，%代表多个字母   
34. select ename from emp where ename like "%\%";--使用转义字符来处理特殊符号。   
35. select ename from emp where ename like "%$%" escape '$';--可以自己指定转义字符。   
36. --常用的单行函数upper() lower()    
37. select substr(ename,2,3) from emp; --从第二个字符开始截取，一共截取三个字符。   
38. select chr(65) from dual; --将一个数字转换为相对应的字母。   
39. select ascii('A') from dual; --将一个字母转换为ASCII码。   
40. select round(23.652) from dual; --四舍五入，24   
41. select round(23.652,2) from dual; --四舍五入到小数点后面二位，23.65   
42. select round(23.652,-1) from dual; --20   
43. select to_char(sal,'$99,999.9999') from emp; --转换格式   
44. select to_char(sal,'L99,999.9999') from emp; --L代表本地货币，8111会转换为￥8,111,0000   
45. select to_char(sal,'L00,000.0000') from emp; --L代表本地货币，8111会转换为￥08,111,0000   
46. select to_char(hiredae,'YYYY-MM-DD HH:MI:SS') from emp; --这个比较重要   
47. select to_char(hiredae,'YYYY-MM-DD HH24:MI:SS') from emp; --24进制的小时   
48. select ename, hiredate, from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');   
49. select sal from emp where sal>to_number('$1,250.00','$9.999.99');   
50. --常用的组函数AVG()   MIN() MAX() 组函数很重要，比单行函数重要。   
51. select count(*) from emp; --查询表里面有多少条记录。   
52. select count(distinct deptno) from emp; --有多少个唯一的单独的部门编号。   
53. select e1.ename ,e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; --自连接 sql1992的标准   
54. select e1.ename ,e2.ename from emp e2 join emp e2 on (e1.mgr=e2.empno);--自连接 sql1999的标准   
55. select ename, dname from emp cross join dept; --交叉连接，笛卡尔积   
56. grant create table, create view to scott; --给用户权限。   
57. create view v$_count as select count(distinct deptno) from emp; --创建视图。   
58. --视图就是一个子查询或者一张表，只不过这是一张虚表。   
59.   
60. /**   
61.  * 第二大类：DML语句：insert update delete  
62.  */   
63. create table emp2 as select * from emp; --备份 emp 表   
64. update emp2 set sal=sal*2, ename=ename || '-' where deptno=10; --update语句   
65. delete from emp2; -- 删除整个表的数据。   
66. delete from emp2 where ename='aaa'; -- 删除某几行。   
67.   
68. --transaction（DML语句会产生）自动提交： 当用户正常断开连接的时候，遇到DDL（create table）语句 或者 DCL(grant user)的时候��   
69. --varchar2：没有长度限制 char:定长，定长字符串效率更高一点，但是会浪费空间。很多的算法比如说Hashtale就是拿空间换时间。   
70. --long也是变长的，最大的字节数可以达到两个G,VARCHAR2最多可以规定到4K也就是4096。   
71.   
72. --五个约束条件：非空，唯一，主键，Check，缺省。约束在数据库里面也是一种对象，我们可以给他起名字，如果不给他起，数据库会默认产生一个名字。   
73. create table stu(   
74.     id number(6),   
75.     name varchar2(20) constraint stu_name_nn not null,--字段级的约束   
76.     sex number(1),   
77.     age number(3),   
78.     sdate date,   
79.     grade number(2) default 1,   
80.     class number(4) references class(id),--外键约束被参考的字段必须是主键   
81.     email varchar2(50),   
82.     constraint stu_name_email_uni unique(email,name)-- 表级约束，对好几个字段的合集加约束。email 和 name 两个字段合起来需要是唯一的。   
83. )   
84.   
85. --ID比Email更适合做主键，ID是数字类型，email是字符串类型，当你建一个主键的时候会随着主键建立一个索引，索引对数字的查询速度更快。   
86. --语法上来说主键约束相当于非空和唯一约束的组合，在逻辑意义上代表着单独的每条记录。   
87. --check约束用的并不是很多，大多数时候我们写数据校验是在java程序里面。   
88.   
89. --alter table 语句对于程序员来说用的并不多，对于数据库管理员用的很多   
90. alter table stu add(addr varchar2(100));   
91. alter table stu drop addr;   
92. alter table stu modify(addr varchar2(90));--修改精度   
93. alter table stu drop constraint stu_class_fk;   
94.   
95. drop table stu;--删除一张表   
96.   
97. /**   
98.  *下面查出来的叫做数据字典表   
99.  */   
100. select table_name from user_tables;--查询当前用户目录下有哪些表   
101. select view_name from user_views;--查询当前用户目录下有哪些表   
102. select constraints_name from user_constraints;    
103. select constraints_name,table_name from user_constraints;    
104.   
105. --ORACLE存储了所有数据字典表的表   
106. desc dictionary;   
107.   
108. create index idx_stu_email on stu(email);--建立索引   
109. drop inext idx_stu_email;   
110.   
111. /**   
112.  * 当我们对某张表加了主键约束或者唯一约束，Oracle会帮你给这两个字段加索引。   
113.  * 索引的作用：为某个字段建立索引之后，别人去访问某个字段的数据时，效率会更高。   
114.  * 但是建立索引之后向某个字段插入或者更改数据的效率会变低：   
115.  * 因为插入数据的同时还要向索引表插数据。   
116.  * 所以不要轻易建立索引，一般是某个字段访问速度很慢的时候。   
117.  *    
118.  * 视图的作用：1、简化我们的查询，但是会增加我们维护的支出。   
119.  *            2、给一些第三方的客户看产品信息，屏蔽了一些关键信息。   
120.  *                比方说更改了某张表结构，那视图也得跟着改。   
121.  * 视图可以用来更新数据，但是我们很少这么去用他。   
122.  */   
123.   
124. create sequence seq;   
125. insert into article values(seq.nextval,'a','b');--自动递增