Oracle SQL语句实例

alter user scott account unlock;--给用户解锁
connect sys/bjsxt as sysdba; --以系统超级管理员登陆
drop user liuchao cascade; --删除用户liuchao
exp -- 导出用户的相应信息
create user liuchao identified by liuchao default tablespace users quota 10M on users--  用户名是liuchao密码也是liuchao
																	--默认的表空间是users大小为10M
grant create session, create table, create view to liuchao; --create session 是登陆的权限。
imp --导入数据用的


/**
 * 第一大类：select语句
 */
desc emp;--描述表emp
select ename from emp where rownum<=5; --在Oracle取数据的时候，其实尾部都带了一行rownum
select enanem from (select rownum r, ename from emp) where r>10; --当用大于号的时候，只能这么操作，直接向上面那样操作不行。
select ename, sal*12 from emp;
select 2*3 from emp; -- 表里面有多少行就显示多少行
desc dual;
select 2*3 from dual;-- 只有一个结果
select sysdate from dual;--显示系统时间
select ename, sal*12 annual_sal from emp;--起个别名，annual_sal中间不能有空格，如果想有空格先用" "套起来。
					 					 --加双引号还有个作用，可以区分大小写。不加的话都会转换为大写。
select ename, sal*12+comm from emp;--任何含有空值的数学表达式，最后结果都是空值。
select ename, sal*12+nvl(comm,0) from emp;--处理掉空值
select ename || 'asdfgg' from emp;--在sql语句中连接字符串都用 || 符号。
select ename || 'asd''fgg' from emp;--可以用两个单引号代表一个单引号。
select ename, sal, comm from emp where comm is null;--对于空值的处理不能用=符号，而要用is。
select ename, sal, comm from emp where comm is not null;
select ename, sal, comm from emp where sal in(800, 1500, 2000);--in 语句：取其中的一个值
select ename, sal, comm from emp where ename in('SMITH', 'KING', 'ABC');--in 语句也可对字符串进行操作
select ename, sal, hiredate from emp where hiredate > '20-2月-81';--必须按照相关的格式，但是年份可写成'20-2月-1981'
select ename from emp where ename like "_A%";--_代表一个字母，%代表多个字母
select ename from emp where ename like "%\%";--使用转义字符来处理特殊符号。
select ename from emp where ename like "%$%" escape '$';--可以自己指定转义字符。
--常用的单行函数upper() lower() 
select substr(ename,2,3) from emp; --从第二个字符开始截取，一共截取三个字符。
select chr(65) from dual; --将一个数字转换为相对应的字母。
select ascii('A') from dual; --将一个字母转换为ASCII码。
select round(23.652) from dual; --四舍五入，24
select round(23.652,2) from dual; --四舍五入到小数点后面二位，23.65
select round(23.652,-1) from dual; --20
select to_char(sal,'$99,999.9999') from emp; --转换格式
select to_char(sal,'L99,999.9999') from emp; --L代表本地货币，8111会转换为￥8,111,0000
select to_char(sal,'L00,000.0000') from emp; --L代表本地货币，8111会转换为￥08,111,0000
select to_char(hiredae,'YYYY-MM-DD HH:MI:SS') from emp; --这个比较重要
select to_char(hiredae,'YYYY-MM-DD HH24:MI:SS') from emp; --24进制的小时
select ename, hiredate, from emp where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal>to_number('$1,250.00','$9.999.99');
--常用的组函数AVG() 	MIN() MAX() 组函数很重要，比单行函数重要。
select count(*) from emp; --查询表里面有多少条记录。
select count(distinct deptno) from emp; --有多少个唯一的单独的部门编号。
select e1.ename ,e2.ename from emp e1, emp e2 where e1.mgr = e2.empno; --自连接 sql1992的标准
select e1.ename ,e2.ename from emp e2 join emp e2 on (e1.mgr=e2.empno);--自连接 sql1999的标准
select ename, dname from emp cross join dept; --交叉连接，笛卡尔积
grant create table, create view to scott; --给用户权限。
create view v$_count as select count(distinct deptno) from emp; --创建视图。
--视图就是一个子查询或者一张表，只不过这是一张虚表。

/**
 * 第二大类：DML语句：insert update delete
 */
create table emp2 as select * from emp; --备份 emp 表
update emp2 set sal=sal*2, ename=ename || '-' where deptno=10; --update语句
delete from emp2; -- 删除整个表的数据。
delete from emp2 where ename='aaa'; -- 删除某几行。

--transaction（DML语句会产生）自动提交： 当用户正常断开连接的时候，遇到DDL（create table）语句 或者 DCL(grant user)的时候。
--varchar2：没有长度限制 char:定长，定长字符串效率更高一点，但是会浪费空间。很多的算法比如说Hashtale就是拿空间换时间。
--long也是变长的，最大的字节数可以达到两个G,VARCHAR2最多可以规定到4K也就是4096。

--五个约束条件：非空，唯一，主键，Check，缺省。约束在数据库里面也是一种对象，我们可以给他起名字，如果不给他起，数据库会默认产生一个名字。
create table stu(
	id number(6),
	name varchar2(20) constraint stu_name_nn not null,--字段级的约束
	sex number(1),
	age number(3),
	sdate date,
	grade number(2) default 1,
	class number(4) references class(id),--外键约束被参考的字段必须是主键
	email varchar2(50),
	constraint stu_name_email_uni unique(email,name)-- 表级约束，对好几个字段的合集加约束。email 和 name 两个字段合起来需要是唯一的。
)

--ID比Email更适合做主键，ID是数字类型，email是字符串类型，当你建一个主键的时候会随着主键建立一个索引，索引对数字的查询速度更快。
--语法上来说主键约束相当于非空和唯一约束的组合，在逻辑意义上代表着单独的每条记录。
--check约束用的并不是很多，大多数时候我们写数据校验是在java程序里面。

--alter table 语句对于程序员来说用的并不多，对于数据库管理员用的很多
alter table stu add(addr varchar2(100));
alter table stu drop addr;
alter table stu modify(addr varchar2(90));--修改精度
alter table stu drop constraint stu_class_fk;

drop table stu;--删除一张表

/**
 *下面查出来的叫做数据字典表
 */
select table_name from user_tables;--查询当前用户目录下有哪些表
select view_name from user_views;--查询当前用户目录下有哪些表
select constraints_name from user_constraints; 
select constraints_name,table_name from user_constraints; 

--ORACLE存储了所有数据字典表的表
desc dictionary;

create index idx_stu_email on stu(email);--建立索引
drop inext idx_stu_email;

/**
 * 当我们对某张表加了主键约束或者唯一约束，Oracle会帮你给这两个字段加索引。
 * 索引的作用：为某个字段建立索引之后，别人去访问某个字段的数据时，效率会更高。
 * 但是建立索引之后向某个字段插入或者更改数据的效率会变低：
 * 因为插入数据的同时还要向索引表插数据。
 * 所以不要轻易建立索引，一般是某个字段访问速度很慢的时候。
 * 
 * 视图的作用：1、简化我们的查询，但是会增加我们维护的支出。
 *            2、给一些第三方的客户看产品信息，屏蔽了一些关键信息。
 * 				  比方说更改了某张表结构，那视图也得跟着改。
 * 视图可以用来更新数据，但是我们很少这么去用他。
 */

create sequence seq;
insert into article values(seq.nextval,'a','b');--自动递增