Orcale数据库SQL语言总结

DDL( Data definition language )数据定义语言：

一、常用数据类型：

1. varchar:可变字符串类型；<=4000
2. char：定长的字符串；<=2000

3. number(p,s)：p表示精度，s表示小数位数，最大位数是38位
   number(6)表示6位数字的 整数
   number（6,2） 表示6位数字的小数，小数点后面两位，前面4位

4. date :日期格式（7字节），oracle 默认格式为：”dd-mm-yy”

5. Timestamp:时间戳,精确度比较高的日期类型
6. Time:时间格式
7. Blob:存储大的二进制多媒体文件。<=4GB
8. Clob:存储大的二进制文本文件。 <=4GB
9. binary_float:浮点型，32位；
10. binary_double :双精度型，64位；
11. bfile :外部的二进制文件

*Create*

1 . 表：
创建表：
语法格式：CREAT TABLE 表名（属性名1 数据类型（长度），属性名2 数据类型……属性名n 数据类型）；
例如：创建一个student表：

create table student(  sno number(2),  sname varchar(9) DEFAULT 'xxx',  sage number(3),  sgender varchar(3),  address varchar(100),  telephone number(11));

2. 主键

Primary key，要求被定义为主键的字段的值具有唯一性和非空性；
使用alter table语句定义主键
使用alter table修改主键状态
在创建表时定义主键

           constraint p1_sid primary key(sid)               --定义sid为主键。

同时也是一种约束：
例如：给某一字段加上主键可以使用primary key ;

CREATE TABLE　student(    Sid number(5) PRIMARY KEY,    Sname varchar2(20),    Sage number(3),    Sgender char(2) DEFAULT '男',    Address varchar2(100),    Telpone varchar2(12),    email varchar2(30))

主键约束本身就有非空的约束和唯一约束，而且一个表只能有一个主键。
 将约束直接定义在相应的字段之后的约束称为列级约束。
 将约束定义所有字段之后的约束我们称为表级约束。
 给约束定义名称最好使用表级约束。

3. 外键
Foreign key，要求被定义为外键的字段的值必须来源于所引用字段的值， 外键字段和所引用的字段，名称可以不一样，但是两者的数据类型和长度必须一致；
使用alter table语句定义主键
使用alter table修改主键状态
在创建表时定义主键

       constraint f1_sid foreign key(sid) references student(sid)   --定义sid为外键，值来源于student表的sid

4. 约束
约束可以比较好控制数据的完整性。
约束的分类

 主键约束：主键表示唯一标识，本身不能空且不能重复。（PK）
 唯一约束：只表此列的值是唯一，但是可以为空。（UK）
 检查约束：表示一个列的值是否是合法数据。（CK）
 非空约束：表示此列不能出现空值。
 主-外键约束：表示两张表之间约束。（FK）

1. 检查约束（check）：检查数据的合法性；

将表中student 中的年龄范围定义在 0-150，性别必须是‘男’或‘女’

CREATE TABLE　student(    id number(5),--主键 sno varchar2(8) UNIQUE,--代表学员编号    Sname varchar2(20),    Sage number(3) CHECK(sage BETWEEN 0 AND 150),    Sgender char(2) DEFAULT '男' CHECK(sgender IN(‘男’,’女’)),    Address varchar2(100),    Telpone varchar2(12),    email varchar2(30),    CONSTRAINT student_sid_pk PRIMARY KEY(id), CONSTRAINT student_sname_uk UNIQUE(sname))

列级约束 ： 将约束直接定义在相应 的字段之后的约束称为列级约束；表级约束 （关键字constraint）： 将约束定义在所有字段之后的约束称为表级约束；

也可以用表级约束来定义检查约束：

CREATE TABLE　student(    id number(5),--主键 sno varchar2(8) UNIQUE,--代表学员编号    Sname varchar2(20),    Sage number(3),    Sgender char(2) DEFAULT '男' CHECK(sgender IN('男','女')),    Address varchar2(100),    Telpone varchar2(12),    email varchar2(30),    CONSTRAINT student_sid_pk PRIMARY KEY(id),CONSTRAINT student_sname_uk UNIQUE(sname),CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150))

1. 非空约束（not null）：必填字段不能为空；
   非空约束不可以使用表级约束来定义，只能用列级约束来定义；

   使用关键字 not null;

CREATE TABLE　student(    id number(5),--主键 sno varchar2(8) UNIQUE NOT NULL,--代表学员编号    Sname varchar2(20),        Sage number(3),    Sgender char(2) DEFAULT '男' CHECK(sgender IN('男','女')),    Address varchar2(100),    Telpone varchar2(12),    email varchar2(30),    CONSTRAINT student_sid_pk PRIMARY KEY(id),CONSTRAINT student_sname_uk UNIQUE(sname),CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150))

–如果修改表时，要添加一个非空约束，而表中的记录已经为空，则设置为默认；

alter table student add constraint sno NOT NULL (sno default 12345 );

1. 唯一性约束（unique）：保证数据的某项内容不重复；

REATE TABLE　student(    id number(5),--主键 sno varchar2(8) UNIQUE,--代表学员编号    Sname varchar2(20) UNIQUE,    Sage number(3),    Sgender char(2) DEFAULT '男',    Address varchar2(100),    Telpone varchar2(12),    email varchar2(30),    CONSTRAINT student_sid_pk PRIMARY KEY(id))

唯一性约束可以用表级约束constraint 关键字来定义唯一性约束；

CREATE TABLE　student(    id number(5),--主键 sno varchar2(8) UNIQUE,--代表学员编号    Sname varchar2(20),    Sage number(3),    Sgender char(2) DEFAULT '男',    Address varchar2(100),    Telpone varchar2(12),    email varchar2(30),    CONSTRAINT student_sid_pk PRIMARY KEY(id), CONSTRAINT student_sname_uk UNIQUE(sname))

可查看当前用户的所有约束，查看Oracle 数据字典，user_constraint

select constraint_name from user_constraints where owner='skye'

索引
—-加快数据的读取速度和完整性检查；
①建立索引：（create index 索引名称 on 表（字段1，字段2））

create index emp_ename_index on emp(ename);

②唯一索引：（CREATE UNIQUE INDEX　索外名称 ON 表(字段1，字段2)）

create unique index emp_ename_index on emp(ename);

③非唯一索引：

CREATE INDEX　索外名称 ON 表(字段1，字段2)

create index emp_ename_index on emp(ename);
④反序索引：
CREATE INDEX　索外名称 ON 表(字段1，字段2) reverse

–反序索引：
create index emp_ename_index on emp(ename);
⑤位图索引：
CREATE BITMAP INDEX　索外名称 ON 表(字段1，字段2)

–位图索引：
create bitmap index emp_ename_index on emp(ename);
⑥函数索引：
CREATE INDEX　索外名称 ON 表(函数())

–函数索引：
create index emp_ename_index on emp(substr(ename,1,2));
–创建一个带有substr函数的Oracle函数索引
⑦索引的删除：
CREATE INDEX　索外名称 ON 表(函数())

--索引的删除：drop index emp_ename_index;

⑧索引的重命名：
ALTER INDEX　索引名称　TO 新名称

alter index emp_ename_index to emp_ename_index1;

⑨索引的重建：（ALTER INDEX 索引名 REBUILD）

alter index emp_ename_index rebuild;

⑩清理的碎片：（ALTER INDEX 索引名 COALESCE）

alter index emp_ename_index coalesce;

6 视图

保存一个复杂的查询结果，简化查询操作，提高效率；
基本语法：

create [force] view 视图名称 [(column 1,column 2,.....)] as select 子查询；

使用视图之前必须要授权；

打开 sqlplus /nolog 命令行窗口，首先进行视图操作的授权： SQL> conn sys/orcl as sysdba    已连接。    SQL> grant create any view to scott;    授权成功。    SQL> conn scott/skye;    已连接。    SQL>

*视图和表使用同一个命名空间，因此命名不能重复；

更改视图的两种方式：
1. 删除原视图，创建新的视图：

drop view emp_dept20;create view emp_dept20 as select * from emp where deptno = 20;

1. 也可以用replace更新视图：

create or replace view emp_dept20 as select * from emp where deptno = 20;

7 序列
序列是Oracle自动生成，不重复的整数值。也是一种数据库对象，可以被多个用户共享。
1. 语法：

CREATE SEQUENCE 序列名称[INCREMENT BY n] ——–递增值,默认1[START WITH n] ——–序列的起始值，默认1[MAXVALUE n] ——–序列的是大值 默认nomaxvalue[MINVALUE n] ——–序列的最小值 默认nominvalue[cycle|nocycle] ——–是否循环 默认nocycle[cache n|nocache] ——–是否缓存 默认nocache[order|noorder] ——–按请求顺序由小到大

1. 序列的两个比较重要的属性：

① currval : 序列的当前值；

② nextval : 序列的下一个值；

1. 删除序列： drop sequence 序列名；

2. 更改序列：alter sequence 序列

8 同义词
相当于对象的别名，使用同义词可以方便访问其它用户的对象,缩短对象名字的长度。
1. 语法：
① 创建同义词：

create synonym emp for scott emp;

② 删除同义词：

drop synonym emp;

• 同义词使用之前需要向sysdba进行声明，如下操作：

SQL> conn sys/orcl as sysdba;已连接。SQL> select * from scott.emp;SQL> create synonym emp for scott.emp;同义词已创建。

DML( Data Manipulation language )数据定义语言：

*1. insert (插入)*

语法：
语法格式1： insert into 表名 values(值1，值2……，值n）；

SELECT * FROM student;         INSERT INTO student VALUES('S001','张三',‘M’,30，13089247856) ；

语法格式2：insert into 表名（字段名1，字段名2，……，字段名n） values(值1,值2,……，值n);

注意：值和字段的对应关系（按顺序对应），好处是可以根据自身的值和字段的对应关系，有选择性进行插入操作。

 INSERT INTO student(sname,ssex,sid,sage) VALUES('李四','F','s0002',20);

对于日期格式的一般转换：

to_date('2015-12-9' , 'YYYY-MM-DD');

*2. update（修改）*
语法：
update 表名 set 赋值表达式 [where 条件]
例如：

SELECT * FROM student;          UPDATE student SET sage=40;          UPDATE student SET sage=(sage+sphone)-sage,sphone=(sage+sphone)-sphone;          UPDATE student SET sage=sage+1;          UPDATE student SET sage=sage+1 where ssec='F';          UPDATE student SET sname=replace(sname,'张','陈');

*merge(合并update和insert语句)*
语法：

merge into usersusing doctoron (user.use_id = doctor.doctorid)when match thenuodate set users.user_name = doctor.doctornamewhen on match then merge_insert_clause;

*delete(删除)*
语法：

delete from 表名称 where 列名称 = 值;delete from table_name;

DQL( Data Query language )数据查询语言：

select 用来对数据进行查询，获取用户想要的信息。

语法格式：

1. select ： 查询的内容，是必选关键字，后面跟要查询的内容，一般以字段为主、也可以是常量、表达式（包含字段）。
   –from： 查询内容的来源，是必选关键字，来源可以是表格、多个表格、其他的查询语句等；
   –where：条件，可选关键字，一般用来指定查询的条件，即用来过滤数据；
   –group by：字段，可选关键字，用来实现分组查询；
   –having：条件，可选关键字，是用来对分组之后的结果进行过滤；
   –order by：字段，可选关键字，用来实现排序操作；

2. || 用来实现字符串、变量的拼接操作的

示例：

SELECT sname,sage,sage||'岁' FROM student;         SELECT sname,ssex FROM student;         SELECT sname,ssex,case WHEN ssex='M' THEN '男' ELSE '女' END FROM student;

3.条件

加查询条件是用来过滤数据的，过滤的基本单位是行，常见的关键字有：（> /< /= />= /<=/ !=/ <> /between..and.. /like/ in /all /any /exists/not exists等；多个条件的连接符有：and\or!

示例：

--查询年龄大于28岁的所有学生的信息      SELECT * FROM student WHERE sage>28;--查询年龄大于等于28岁的所有学生的信息；     SELECT * FROM student WHERE sage>=28;--查询所有男生信息      SELECT * FROM student WHERE ssex='M';      SELECT * FROM student WHERE ssex!='F';      SELECT * FROM student WHERE ssex<>'F';--查询学号比s0010靠前的学生的信息。      SELECT * FROM student WHERE sid<'s0010';

1）between..and..

语法格式：字段 between 值1 and 值2 等价于 字段>=值1 and 字段<=值2；是一个独立、完整的字段，不可拆分。

示例：--查询年龄在23-28岁之间（包含）的学生信息。             SELECT * FROM student WHERE sage>=23 AND sage<=28;             SELECT * FROM student WHERE sage BETWEEN 23 AND 28;        --查询年龄在23-28岁之间（包含）的女生信息。             SELECT * FROM student WHERE sage BETWEEN 23 AND 28 AND ssex='F';             SELECT * FROM student WHERE sage>=23 AND ssex='F'AND sage<=28;

2）like:实现的是模糊查询，一般来说会结合两个特殊符号使用，%通配符，匹配任意多个字符；_表示匹配一个字符。

示例：--查询所有姓张的同学信息。            SELECT *  FROM student WHERE sname LIKE '张%';         --查询所有姓张，姓名总共为2个字的同学信息。            SELECT *  FROM student WHERE sname LIKE '张_';         --查询所有姓张，姓名总共为3个字的同学信息。            SELECT *  FROM student WHERE sname LIKE '张__';            SELECT *  FROM student WHERE sname LIKE '%张';

3)in：是一种枚举用法，字段in（值1，值2，……，值n）等价于 字段=值1 or 字段=值2 or … or 字段=值n。

示例：--查询年龄等于27岁或者28岁的学生的信息。            SELECT * FROM student WHERE sage=27 OR sage=28;            SELECT * FROM student WHERE sage IN (27,28);

4)distinct：用来修饰字段，表示唯一查询、去除重复值。

示例：--查询所有选课了的学生的学号。            SELECT  sid FROM score;            SELECT  DISTINCT sid FROM score;

5)别名：可以应用在查询内容和来源中。

语法格式： 原名 as 别名，一般来说，as是省略的， 原名 别名。

SELECT  sname,sage FROM student; SELECT  sname as 姓名,sage 年龄 FROM student;SELECT  sname,sage+1 sage FROM student; SELECT  sname sage FROM student; --语法没问题，从应用层面是有问题的。查询学生姓名，但结果是列名改为了sage。

6）嵌套

SQL语句中，查询语句是可以被嵌套使用。嵌套是通过括号()实现。每一条查询语句的结果本身就是一个表、是一些值的集合，可以被嵌套使用在值或者来源的部分。

–查询所有选修oracle课程的学生的姓名。
SELECT sname FROM student WHERE sid IN (SELECT sid FROM score WHERE cid=(SELECTcid FROM course WHERE cname=’oracle’));

–查询某一位同学所选修的课程的名称。（已经学生姓名）
SELECT cname FROM course WHERE cid IN (SELECT cid FROM score WHERE sid in (SELECT sid FROM student WHERE sname=’张三’));

7)all: 使用and来对表达式进行分解

–字段 > all(值1，值2，。。。，值N) 等价于： 字段>值1 and 字段>值2 and… and 字段>值N。
–字段 < all(值1，值2，。。。，值N) 等价于： 字段<值1 and 字段<值2 and… and 字段<值N。

示例：查询比所有女生年龄都大的男生信息。

     SELECT * FROM student WHERE ssex='M' AND sage>39;         SELECT sage FROM student WHERE ssex='F'; --21\25\39         SELECT * FROM student WHERE ssex='M' AND sage>ALL(SELECT sage FROM student WHERE ssex='F');         --等价于  SELECT *FROM student WHERE ssex='M' AND sage>21 AND sage>25 AND sage>39;

8）any: 就是用or来对表达式进行分解

–字段 > any(值1，值2，。。。，值N) 等价于： 字段>值1 or 字段>值2 or … or 字段>值N。

示例：–查询比任一女生年龄都大的男生信息。
“`
SELECT * FROM student WHERE ssex=’M’ AND sage>ANY(SELECT sage FROM student WHERE ssex=’F’);

4.聚合函数（分组函数/组函数）   常见的聚合函数有：count()\avg()\min()\max()\sum(), 括号里加字段或字段表达式。示例：--查询男生的数量  ```      SELECT count(*) FROM student WHERE ssex='M';         --查询有成绩的学生的数量            SELECT count(distinct sid) FROM score;          --查询女生的最小年龄            SELECT min(sage) FROM student WHERE ssex='F';          --查询某一位学生的平均成绩            SELECT avg(grade) FROM score WHERE sid='s0001';          --查询年龄最大的学生的姓名             SELECT* FROM student WHERE sage=(SELECT max(sage) FROM student);

5.分组查询

语法格式： group by 分组字段

作用：表示会将表格中所有的数据按照分组字段进行分组处理、分组字段的值相同的行会被合并为一条记录、即一组；分组字段以外的字段是无法再被独立查询，只能通过聚合函数来实现查询，分组字段可以是多个，用逗号分隔。

示例：--查询男生和女生的数量           SELECT ssex,COUNT(*) FROM student GROUP BY ssex;        --查询男生和女生的平均年龄、最大年龄、最小年龄           SELECT ssex,AVG(sage),MAX(sage),min(sage) FROM student GROUP BY ssex;        --查询每一门课程被选的数量           SELECT cid,COUNT(DISTINCT sid) FROM score GROUP BY cid;

DCL( Data Control language )数据控制语言：

针对权限部分的管理
*1. grant*
指定权限
*2. revoke*