Oracle实例

Oracle 数据库创建导入

sqlpius        //启动sqlplus
yuan as sysdba     //yuan账户登入数据库
exit          //断开连接

create user yuan identified by Orcl1234;  //添加用户
grant connect,resource,DBA to yuan;       //赋权限
connect yuan@orcl        //OT 用户仅存在于 ORCL 数据库中，因此，必须在 CONNECT 命令中明确指定用户名为 ot@orcl。 


@F:\website\yuan_schema.sql   //文件中执行 SQL 语句

Oracle 基础知识

select语句

select id,name,address,website
from customers;

Order By子句

使用ORDER BY子句中的UPPER()函数来区分客户名称的大小写：

selct id,name
from customers
order by upper(name);   

Distinct用法

从state列中检索具有多个NULL值的数据：

select distinct state
from locations
order by state NULLs first;

Where子句

select * 
from products
where p_name like'kingston%' and p_price>500
and id IN（1，1000）
order by p_date；

Fetch子句⭐

使用 WITH TIES 选项的行限制子句
这两个附加行在 quantity 列的值与第 10 行 quantity 列的值相同

select p_name，quantity
from inventories
inner join products
using（product_id）
order by quantity desc
OFFSET 10 ROWS                    //跳过库存最高的前10个产品
fetch next 100 rows with ties;   //并列值

fetch first 1 percent rows only;   //前1%

In子句⭐

Oracle IN 运算符可以用来确定值是否与列表或子查询中的任何值相匹配

返回负责订单状态为取消(Canceled)的销售员的ID，名字和姓氏:

select employees_id,first_name,last_name
from employees
where
employee_id in(select distinct saleman_id from orders where status='Canceled')
order by first_name;

Oracle 事务

use rollback segment 实例

declare l_name varchar(100);l_age number;l_sex varchar(2）;
begin       
commit;           -- 为保证set transaction是事务的第一条语句，先使用commit或rollback来结束掉前面可能存在的事务
set transaction read only name '查询报表';   --使用name给事务命名
select name
into l_name from student where student_id=1001;
select age
into l_age from student where student_id=1001;
select sex
into l_sex from student where student_id=1001;
commit;     --终止只读事务

LOCK TABLE语句

LOCK TABLE 语句可以用来锁定表、表分区或表子分区。

LOCK TABLE suppliers IN SHARE MODE NOWAIT;

这个例子会锁定 suppliers 表在共享模式，而不是等待锁定被释放。

Oracle 外键创建⭐

我们在supplier表上创建了一个名为supplier_pk的主键。
它只包含一个字段 - supplier_id字段。
然后，在products表上创建了一个名为fk_supplier的外键，该表根据supplier_id字段引用supplier表。

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)       --主键
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);             --外键

使用ALTER TABLE语句创建

我们也可以创建一个具有多个字段的外键，如下例所示：

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);

Oracle 级联删除外键

级联删除是指当主表中的一条记录被删除，那么子表中所关联的记录也相应的自动删除。

CREATE TABLE语句定义级联删除的语法：

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE CASCADE    --级联删除
);

用ALTER TABLE语句定义级联删除

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name)
  ON DELETE CASCADE;    

删除外键

我们就成功的删除了这个新建的fk_supplier外键。

ALTER TABLE products 
DROP CONSTRAINT fk_supplier    --删除外键

禁用外键

alter table products
disable constraint fk_supplier;

启用外键

alter table products
enable constraint fk_supplier;

Oracle 字符串函数⭐

Ascii()函数

Ascii()函数可以返回代表指定字符的数字值代码

ascii('t')
Result: 116

Asciistr()函数

Asciistr()函数可以使用数据库字符集将任何字符集中的字符串转换为ASCII字符串

asciistr('A B C Ä Ê')
Result: 'A B C 0C4 0CA'

Chr()函数

根据数字代码返回字符，其功能和ASCII函数相反

chr(116)
Result: 't'

Compose()函数

使用Compose()函数可以返回一个Unicode字符串

compose ('o' || unistr('\0308'))
Result: ö

Unistring	结果字符
unistr(‘\0300’)	重音符(`)
unistr(‘\0301’)	锐音符(‘)
unistr(‘\0302’)	(^)
unistr(‘\0303’)	~
unistr(‘\0308’)	变音符(¨)

Concat()函数

可以用于将两个字符串连接在一起

select concat(CONCAT(CONCAT('A', 'B'),'C'),'D')
from dual;
-- Result: 'ABCD'

连接单引号

由于参数包含在单引号中，在引号内使用2个额外的单引号来表示生成的连接字符串中的单引号。

select concat('Let' ''s', 'learn Oracle')
from dual;
--Result: Let's learn Oracle

|| 连接运算符

可以将两个或两个以上的字符串连接在一起。

连接空格字符

将值连接在一起时，可能需要添加空格字符来分隔连接的值。

select 'Dave' || ' ' || 'Anderson'
from dual;
-- Result: 'Dave Anderson'

--一列由 customers 表中的 first_name 和 last_name 字段(用空格分隔)组成。 结果集中的列将被别名为 customer_name。
SELECT first_name || ' ' || last_name AS customer_name
FROM customers;

Convert()函数

可以将字符串从一个字符集转换为另一个字符集

SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') 
   FROM DUAL; 

CONVERT('ÄÊÍÕØABCDE' 
--------------------- 
A E I ? ? A B C D E ? 

Dump()函数

使用Dump()函数可以返回一个varchar2值，这个值包含了数据类型代码、字节长度和表达式的内部表示形式

DUMP('Tech')
-- Result: 'Typ=96 Len=4: 84,101,99,104'

DUMP('Tech', 10)
-- Result: 'Typ=96 Len=4: 84,101,99,104'

DUMP('Tech', 16)
-- Result: 'Typ=96 Len=4: 54,65,63,68'

DUMP('Tech', 1016)
-- Result: 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'

DUMP('Tech', 1017)
-- Result: 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'

Initcap()函数⭐

Initcap()函数可以将每个单词中的第一个字符设置为大写，其余的设置为小写。

initcap('tech on the oraok');
-- Result: 'Tech On The Oraok'

INITCAP('GEORGE BURNS');
-- Result: 'George Burns'

Oracle 运算符

案例1、求2018年上学期数学的平均成绩。

select b.coursename,sum(a.score)/count(1)
from score a,course b
where a.courseid=b.courseid and a.courseid='R20180101'
group by b.coursename;

Oracle逻辑运算符⭐

案例2、查看2018年上学期数学成绩在85-95分之间的同学：

select a.*,b.coursename,c.stuname
    from score a,course b,stuinfo c
where a.courseid=b.courseid
    and a.stuid=c.stuid and a.score>='85'and a.score<='95'