Oracle SQL语法系列（一）

一、结构化查询语言（SQL）

1、数据操作语言（DML）

INSERT

UPDATE

DELETE

2、数据定义语言

CREATE

ALTER

DROP

RENAME

TRUNCATE

3、事务控制（TC）

COMMIT

ROLLBACK

SAVEPOINT

4、数据控制语言（DCL）

GRANT

REVOKE

附录：Linux下执行SQL脚本

@/tmp/create_table.sql

二、创建数据库

create database xiaoxu

user sys identified by 123456

user system identified by 123456

logfile

group 1 (‘/u01/logdata/redo01_xiaoxu.log’) size 20M,

group 2 (‘/u01/logdata/redo02_xiaoxu.log’) size 20M,

group 3 (‘/u01/logdata/redo03_xiaoxu.log’) size 20M,

maxlogfiles 6

maxlogmembers 5

maxloghistory 8

maxdatafiles 300

maxinstances 1

archivelog

force logging

datafile ‘/u02/dbdata/system_xiaoxu_01.dbf’ size 300M,

undo tablespace undotbs

datafile ‘/u03/dbdata/undo_xiaoxu_01.dbf’ size 80M,

default temporary tablespace temp

tempfile  ‘/u04/dbdata/temp_xiaoxu_01.dbf’ size 60M,

extent management local uniform size 1M,

character set AL32UTF8

time_zone = ‘Asia/BeiJing’;

 三、从数据库表中检索信息

 1、列运算

select 10 * (10 / 2 - 2)

from dual;

说明

（1）行标识符（伪劣）：rowid、rownum

（2）在别名中使用空格并保持别名文本的大小写形式，就必须使用双引号“”将其括起来。

（3）使用连接操作符 || 来合并列的输出结果

select first_name || ‘  ‘ || last_name as "Customer Name"

from  customers;

（4）理解空值

①select *  from customers

where dob is null;

②select customer_id,first_name,last_name,nvl(phone,‘not phone number‘) as phone_number

from customers;

（5）禁止显示重复行

select distinct customer_id

from purchases;

（6）比较操作符

any：任意值

all：所有值

=：等于

<>或!=：不等于

>：大于

>=：大于等于

<：小于

<=：小于等于

（7）SQL操作符

like

in

between

is null

is nan

还可以使用not使一个操作符的含义相反。

（8）使用like操作符

下划线_：匹配一个字符

百分号%：匹配任意个字符

（9）逻辑操作符

x AND y

x OR y

NOT x

注意：比较操作符的优先级高于AND，而AND的优先级要高于OR。

（10）排序

ORDER BY

ORDER BY 子句必须位于from或where子句之后。

2、添加、修改、删除行

（1）向表中添加行

insert into customers

values (6,‘xiaoxu‘,‘whit‘,‘08-9月-66‘,‘900-100-1111‘);

（2）修改表中的行

update customers

set last_name = ‘Orange‘

where customer_id = 6;

（3）删除行

delete from customers

where customer_id = 6;

四、连接

1、等值连接（=）

（1）连接两个表的SELECT语句

要在查询中将两个表连接起来，就需要在查询的FROM子句中同时指定两个表，在WHERE子句中指明两个表中的相关列。

select products.name,product_types.product_type_id

from products,product_types

where products.product_type_id = product_types.product_type_id

AND products.product_id = 3;

（2）连接四个表的select语句

select c.first_name,c.last_name,p.name as product,pt.name as type

from customers c,purchases pr,products p,product_types pt

where c.customer_id = pr.customer_id

and p.product_id = pr.product_id

and p.product_type_id = pt.product_type_id;

2、连接的三种类型

内连接：在连接条件中，如果某一行的列是空值，那么这行就不会返回

外连接：在连接条件中，即使某一行的列是空值，那么这行也会返回；分左外连接、右外连接

自连接：返回连接到同一个表中的行

（1）外连接

在非空值表的列的另一边使用加号（+）

select p.name,pt.name

from products p,product_types pt

where p.product_type_id = pt.product_type_id (+)

order by p.name;

在左外连接中，外连接操作符（+）在等于操作符的右边：

select p.name,pt.name

from products p,product_types pt

where p.product_type_id = pt.product_type_id (+)

order by p.name;

在右外连接中，外连接操作符（+）在等于操作符的左边：

select p.name,pt.name

from products p,product_types pt

where p.product_type_id (+)= pt.product_type_id

order by p.name;

（2）自连接

自连接是对同一个表进行的连接，要执行一个自连接，必须使用不同的表别名来标识在查询中每次对表的引用。

select w.first_name || ‘ ‘ || w.last_name || ‘ works for ‘ || m.first_name || ‘ ‘ || m.last_name

from employees w,employees m

where w.manager_id = m.employee_id

order by w.first_name;

使用标准的SQL语法执行连接（推荐）

（3）内连接（inner join .....on .....）

①两个表的内连接

select p.name,pt.name

from products p inner join product_types pt

on p.product_type_id = pt.product_type_id

order by p.name;

②多于两个表的内连接

对customers、purchases、products、product_types这4张表进行连接。

SELECT c.first_name,c.last_name,p.name as product,pt.name as type

FROM customers c INNER JOIN purchases pr

USING (customer_id)

INNER JOIN products p

USING (product_id)

INNER JOIN product_types pt

USING (product_type_id)

ORDER BY p.name;

连接逻辑图

使用USING关键字说明：

1）查询必须是等连接

2）等连接中的列必须同名

3）在USING子句中引用列时不要使用表名或别名，否则会出错

(4)全外连接（full outer join）

select p.name,pt.name

from products p full outer join product_types pt

using (product_type_id)

order by p.name;

（5）交叉连接

使用ON或USING子句，可以避免产生笛卡尔积。

3、子查询

子查询的类型有：

单行子查询

多行子查询

多列子查询

关联子查询

嵌套子查询

（1）单行子查询

①在where子句中使用子查询

select employee_id,last_name

from employees

where salary < any

(select low_salary

from salary_grades);

②在having子句中使用子查询

③在from子句中使用子查询（内联视图）

select product_id

from

(select product_id from products where product_id <3 );

（2）多行子查询

可以在多行子查询中使用any或all操作符。

在查询中any操作符之前，必须使用一个=、<>、<、>、>=、<=操作符

select employee_id,last_name

from employees

where salary < any

(select  low_salary

from salary_grades);

（3）关联子查询

关联子查询会引用外部SQL语句中的一列或多列

select product_id,product_type_id,name,price

from products outer

where price >

(select avg(price) from products inner

where inner.product_type_id = outer.product_type_id);

（4）包含子查询的UPDATE和DELETE语句

例如将员工ID为4的工资设置为子查询返回的高工资级别的平均值：

update employees

set salary =

(select avg(high_salary) from salary_grades)

where employee_id =4;

五、高级查询

1、集合操作符

说明

使用集合操作符时，所有查询返回的列数以及列的类型必须匹配，但列名可以不同。

select product_id,product_type_id,name

from products

union

select prd_id,prd_type_id,name

from more_products;

2、使用报表函数

查询2003年前三个月的每月销量总和（total_month_amount），以及所有产品类型销量的总和（total_product_type_amount）：

select month,prd_type_id,

sum(sum(amount) over (partition by month)) as total_month_amount,

sum(sum(amount)) over (partition by prd_type_id) as total_product_type_amount

from all_sales

where year = 2003 and month <= 3;

3、修改表的内容

复制表结构到新表

①数据一起复制：

create table 新表 as select * from 旧表    

②只复制表结构而不复制数据：

create table t2 as select * from t1 where id<1000；

注释：components表中的id最小值为1000，所以这里假如选择小于这个最小值的行，那么就是该表的结构。

六、数据库的完整性

1、主键约束

2、外键约束

3、数据库事务

事务的两个动作，即提交和回滚。同时，可以在事务的任何一个地方设置保存点（savepoint）。

事务的ACID特性

原子性：事务是原子的，一个事务包含的所有SQL语句是不可分割的工作单元。

一致性：事务必须确保数据库的状态保持一致。

隔离性：多个事务可以独立运行，彼此不会产生影响

持久性：事务一旦提交，数据库的变化就会永久被保留下来。

4、并发事务

5、事务锁

6、事务隔离级别

幻像读取

不可重复读取

脏读

7、查询闪回

 七、用户、特权和角色

1、创建用户及其权限

create user xiaoxu identified by 123456;

授权

grant create session,connect to xiaoxu;

修改密码

alter user xiaoxu identified by 1234567;

删除用户

drop user xiaoxu;

授予用户的两种有用角色是：CONNECT、RESOURCE。

向用户授予系统特权

grant create table,create user to xiaoxu;

查询授予用户的系统特权，以该用户登录数据库

Select * from user_sys_privs

撤销用户系统特权

revoke create table from xiaoxu;

2、对象特权

对象特权允许用户对数据库对象执行特定的操作。

①将employees表的select对象特权授予student用户

Grant select on employees to student;

②将employee表的last_name和salary列的update对象特权授予student，并可授予其他用户该权限

Grant update (last_name,salary) on employees to student with grant option;

说明：

若希望用户可将对象特权授予其他用户，使用grant选项

若希望用户可将系统特权授予其他用户，使用admin选项

③查询已授予的对象特权

Select * from user_tab_privs_made

Where table_name = ‘employees’;

④撤销用户的对象特权

撤销student用户对products表的insert特权

Revoke insert on products to student;

 3、角色

（1）创建角色

create role hr_manager;

（2）    为角色授权

Grant create user to hr_manager;

（3）    将角色授予用户

Grant hr_manager to student;

（4）    查询授予用户的角色

Select * from user_role_privs;

（5）    查询授予角色的系统特权

Select * from role_sys_privs;

（6）    查询授予角色的对象特权

Select * from role_tab_privs

Where role = ‘hr_manager’;

（7）    撤销角色

Revoke hr_manager from student;

（8）    从角色中撤销特权

Revoke all on product_types from hr_manager;

（9）    删除角色

Drop role hr_manager;

八、创建表、序列、索引和视图

1、表

（1）创建表

create table student (

id int constraint student_id_pk primary key,

status varchar2(40),

last_modified date default sysdate

);

（2）修改表

添加列

alter table student

add modified_by int;

修改列的数据类型

Alter table student

Modify status char(15);

删除列

Alter table student

Drop column status;

2、 约束

（1）添加check约束

（2）添加not null约束

（3）添加foreign key约束

（4）添加unique约束

（5）删除约束

Alter table student

Drop constraint student_id_pk;

（6）查询约束信息

Select * from user_constraints;

（7）    查询有关列的约束信息

Select * from user_cons_columns

Where table_name = ‘student’;