Oracle数据库管理（3）——SQL语言

一、SQL命令分为4种：

（A）数据定义语言（DDL）：创建表、删除表、修改表结构
（B）数据操纵语言（DML）：增、删、改数据。查数据是否属于DML，不同的人有不同的看法
（C）事务控制语言（TCL）
（D）数据控制语言（DCL）：授予权限和收回权限

二、数据类型

1、字符型

常用的有CHAR、VARACHAR2 、LONG。
CHAR用于存固定长度的字符串，存储的值为字母数字，可存储的长度为1~2000字节。
VARCHAR2用于存不定长度的字符串，存储的值为字母数字，可存储的长度为1~4000字节。oracle也支持VARCHAR类型，但不建议使用，因为oracle在以后的版本中将逐渐取消对VARCHAR类型的支持。
LONG用于存不定长度的字符串，可存储的长度为1~2GB字节。

2、数值型

可以存储整数、浮点数（即小数）和实数。可存储负10的38次方到正10的38次方。表示方法为

NUMBER(p[,s])  //p表示精度，s表示小数点后位数。例如NUMBER(5,2)表示小数点前3位，小数点后2位

如果用“NUMBER(3,2)”创建表,插入“1.9999999”，则会四舍五入保留两位小数，实际存储“1.23”；如果插入“99.1”，则会报错

如果省略“,s"，。则代表存储整数，依然是四舍五入保留整数部分。

3、日期类型

常用的类型有DATE、TIMESTAMP。
DATE存储日期和时间，精确到1秒。
TIMESTAMP存储日期、时间和时区，精确到秒后小数点6位。

4、RAW类型

RAW和LONGRAW用来存储二进制数据，RAW可存储2000字节；LONGRAW可存储2G字节

5、LOB类型

LOB可以存储大对象数据，例如一部高清电影，甚至一个中等规模的数据库。oracle11g之前的版本，LOB可存储最大4G字节；oracle11g中，LOB可最大存储128T字节。
LOB类型常用的有CLOB、
CLOB可以存储大量字符数据。
BLOB可以存储二进制数据，例如音频、视频。一般为了媒体文件的安全，可以把媒体文件存在BLOB里，其他情形不建议这么做，因为读取会很慢。
BFILE是存放操作系统中二进制文件的指针。

6、伪列

伪列像一个表列，但没有存储在表中，伪列可以从表中查询，但不能插入、更新和删除他们的值，常用的伪列有ROWID和ROWNUM。
ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数。功能和SQLSERVER中的TOP类似。

三、数据定义语言

1、创建表

语法：

CREATE  TABLE  表名(列名1  数据类型1[,列名2  数据类型2, .......]);

举例

create table student2(name char(3),age number(2),addr varchar2(20));
create table student2 as select * from student1;  //创建一个与表1的内容完全一样的表2
create table student2 as select * from student1 where 1>2;  //创建一个与表1的表结构完全一样的表2，但没有数据

2、修改表结构

（1）添加列：

ALTER  TABLE  表名  ADD  列名  数据类型

举例：

ALTER TABLE student2 add telephone varchar2(11);

（2）修改列
语法：

ALTER  TABLE  表名  MODIFY  列名  数据类型;

举例：

alter table student2 modify name varchar2(20);

（3）删除列
语法：

alter table 表名 drop column 列名;

举例：

alter table student drop column age;

（4）重命名列
语法：

alter table 表名 rename column 原列名 to 新列名;

举例：

SQL> desc tab1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(3)

SQL> alter table tab1 rename column id to name;

表已更改。
SQL> desc tab1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 NAME                                               NUMBER(3)

3、查看表结构

语法：

desc 表名;

4、删除表

语法：

drop table 表名;
drop table 表名 purge;  //永久删除表

5、重命名表

语法：

rename 原表名 to 新表名：

SQL> rename t1 to tab1;
表已重命名。

6、查看现有哪些表

语法：

select * from tab;

4、数据操纵语言

1、插入数据

两种插入方式：

insert into 表名 values(数据1[，数据2，......])
insert into 表名（列名1[，列名2，......]） values(数据1[,数据2，......]);

举例：

insert into t1 values(1,"tom","11-2月 -80");  //默认情况下插入日期需要安照这种格式插入
insert into t1 values(2,"jim",to_date('19880531','yyyymmdd'));  //按照“年年年年月月日日”的格式插入日期
insert into t1 values(3,"jack",null);插入含空值的列
insert into t1(a,c) values(4,"25-12月 -71");  //在有多列的表中选择若干列插入数据，其余列插入空值
insert into t2 select * from t1 where a>2;  //将表1的内容插入表2，

2、查询数据

select  sysdate from dual;  //查看当前时间
select * from t1 where a is null;  //查询值为空值的行
select * from t1 where a is not null;  //查询值为非空值的行
select * from t1 where a = 1;  //查询值为某个固定值的行
select * from t1 where a>2;  //查询值为某个范围的行
select count(*) from t1 where a>2;  //统计某个条件的行数
select * from t1 where name like 'A%';  //匹配查询，“%”表示匹配任意多个任意字符
select * from t1 where name like 'A_';  //匹配查询，“_”表示匹配1个任意字符
select * from t1 order by id desc;  //降序排列
select * from t1 order by id;  //升序排列
select id 学号,name "姓   名" from t1;  //使用别名显示列名。若别名中间存在空格，需要加双引号
select '学号是'||sid,'姓名是'||sname from student;  //使用“||”将字符串与查询结果进行连接
select sid,sum(score) from studentscore group by sid;  //按照某一列的数据进行分组。例如这里求每个学生的总成绩
select sid,avg(score) from studentscore group by sid having(avg(score)>60);  //求平均成绩大于60分的学生
select sid,avg(score) from studentscore group by sid having(avg(score)>(select avg(score) from studentscore));  //求平均成绩大于所有学生的平均成绩的学生
select s.sid,s.name,a.sid,a.city from students s,addr a where s.sid = a.sid; //多表查询，查询两张表中sid相等的学生ID、姓名和城市
select s.sid,s.name,a.sid,a.city from students s inner join addr a on s.sid = a.sid;  //多表查询的另一种方式，也是查询两张表中sid相等的学生ID、姓名和城市
select s.sid,s.name,a.sid,a.city from students s,addr a where s.sid = a.sid(+);  //左外连接，s表为左表，左表中的sid都显示，右表只显示与左表中sid相同的sid
select s.sid,s.name,a.sid,a.city from students s left outer join addr a on s.sid = a.sid; //左表连接的另一中方式
select s.sid,s.name,a.sid,a.city from students s,addr a where s.sid(+) = a.sid; //右外连接，a为右表，右表中的sid都显示，左表只显示与右表中sid相同的sid
select s.sid,s.name,a.sid,a.city from students s right outer join addr a on s.sid = a.sid; //右表连接的另一中方式
select s.sid,s.name,a.sid,a.city from students s full outer join addr a on s.sid = a.sid; //全连接，两张表的sid都会显示

sql操作符的优先级：

运算符	符号	优先级
算数运算符	“+”、“-”、“*”、“/”	优先级最高
链接操作符
比较操作符	“<”、“>”、“=”
NOT
AND
OR		优先级最低

3、删除数据

语法：

delete from 表名 [where 条件];

4、修改数据

语法：

update 表名 set 列名1=值1[,列名2=值2 ....... where 条件]

举例：

update t1 set a = 'It''s a dog'; //输入的值中如果有单引号，使用2个单引号转意为1个单引号

5、清除数据

语法

truncate table 表名;

truncate与delete的区别：delete需要执行commit后才会删除，而truncate不需要。delete执行后会记录在日志中，可以通过回滚回复数据，而truncate执行后不会记录在日志中。使用truncate时一定要非常小心！！！