首页 > 代码库 > Oracle SQL语法系列(一)

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 typeFROM customers c INNER JOIN purchases prUSING (customer_id)INNER JOIN products pUSING (product_id)INNER JOIN product_types ptUSING (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、集合操作符

 

操作符

说明

Union all

返回包括重复的行

Union

返回不包括重复的行

intersect

返回两个查询检索出的共有行

minus

 

 

说明

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

select product_id,product_type_id,namefrom productsunionselect prd_id,prd_type_id,namefrom 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_amountfrom all_saleswhere 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’;

 

Oracle SQL语法系列(一)