Oracle 数据库基础SQL语句
–创建表空间
create tablespace tab_01 datafile 'd:\database\data\datafiles_01' size 50M;
–创建临时表空间
create temporary tablespace temp_01 tempfile 'd:\database\data\temp_01.dbf' size 50M;
–创建表格
create table crTale_01(
id number(11) primary key not null,
username varchar2(20) unique not null, --unique :表示不能相同
pwd varchar2(16) not null,
sex char(2) check(sex='男' or sex='女') not null,
age number(3) not null,
birthday date,
address varchar2(200)
);
–删除表格
drop table crTale_01
–添加表注释
comment on table crTale_01 is '测试表_01';
–字段注释
comment on column crTale_01.id is '主键id';
comment on column crTale_01.username is '姓名';
–插入数据
insert into crTale_01 values(1001,'江小白','123456','男',22,sysdate,'重庆市沙坪坝');
insert into crTale_01 values(1002,'江大黑','123456','女',21,sysdate,'重庆市沙坪坝');
insert into crTale_01 values(1003,'翠花','123456','女',21,sysdate,'重庆市沙坪坝');
–查询表格所有内容
select * from crTale_01
–修改数据
update crTale_01 set username='老王' where id=1001
–删除数据
delete from crTale_01 where id=1003
–提交事务
commit
同义词: 给查询的表格对象取一个别名,用于简化对象访问和提高对象访问的安全性.
–同义词有两种类型:
–私有同义词。(私有同义词只能被当前模式用户访问)
–共有同义词。(共有同义词可以被所有数据库用户访问)
–“ or replace” 表示同义词存在的情况下替换该同义词。
–创建私有同义词
create or replace synonym ta_01 for crTale_01;
–创建共有同义词
create public synonym ta_02 for crTale_01;
–通过别名查询
select * from ta_01
select * from ta_02
–删除同义词
drop synonym ta_01
索引:类似于书的目录,我们可以通过目录快速的找到我们查询内容的位置,不需要从第一页每页的开始查找。在数据库中,索引允许程序快速地找到表中的数据,而不必全表扫描。在数据库中,索引是表中数据和相应存储位置的列表。
索引是一种树状结构,从逻辑设计和物理设计实现两个方面进行分类。从逻辑设计方面看,可以分为单列索引或者组合索引、唯一性索引、非唯一性索引和基于函数的索引等;从物理设计实现角度看,索引可以分为分区索引和非分区索引、B树索引、正向索引和反向索引、位图索引等。
–创建索引
create index ta_sex on crTale_01(sex);
–创建唯一性索引
create unique index ta_id on crTale_01(id);
–创建位图索引
create bitmap index ta_sex on crTale_01(sex);
修改索引
–重建索引
alter index ta_sex rebuild
–合并索引
alter index ta_sex coalesce;
–删除索引
drop index ta_sex;
视图:是查询一个或多个表格的 select 语句的描述。视图创建后相当于 虚表或特殊的表来对待
–创建视图1
–视图名称与表明不能一样,其中 “or replace” 表示如果有同名的视图存在,则覆盖已有视图,既可以修改已有的视图
create or replace view view_01 as
-- sql 语句1
select * from crTale_01
–创建视图2
create view view_02 as
-- sql 语句2
select * from crTale_01 where sex='男';
–视图查询
select * from view_02
–删除视图
--具有drop view 和 drop any view 权限的用户才可以删除
drop view view_02;
序列:是一数据库对象,利用它可以生成唯一的整数,一般用来使序列自动的生成主键值。
–创建序列
create sequence my_num
start with 1003 --指定生成的第一个序列号
increment by 2 --指定序列号之间的间隔
maxvalue 99999 --指定序列号可生成的最大值
cache 20; --值 (缓存量:默认为20)| nocache (没有缓存)
–修改序列(不能修改 “start with ”参数)
cycle 如果达到最大值,重新开始| nocycle (默认)不重新开始
alter sequence my_num
maxvalue 9999
cycle;
–删除序列
drop sequence my_num;
–插入数据使用序列
insert into crTale_01 values(my_num.nextval,'江小白','123456','男',23,sysdate,'中国')
insert into crTale_01 values(my_num.nextval,'翠花','123456','男',23,sysdate,'中国')
函数
数值函数
– 1、ABS(n) 返回数字n的绝对值.
select 'ABS(-12.3):' || abs(-12.3) FROM DUAL;
– 2、CEIL(n) 返回大于等于数字n的最小整数.
select 'CEIL(5.3):' || ceil(5.3) FROM DUAL;
select 'CEIL(-5.3):' || ceil(-5.3) FROM DUAL;
select 'CEIL(5):' || ceil(5) FROM DUAL;
– 3、FLOOR(n) 返回小于等于数字n的最大整数.
select 'FLOOR(5.3):' || floor(5.3) FROM DUAL;
select 'FLOOR(-5.3):' || floor(-5.3) FROM DUAL;
select 'FLOOR(5):' || floor(5) FROM DUAL;
– 4、ROUND(n,[m]) 用于执行四舍五入运算。
– 如果省略 m ,则四舍五入至整数位;
– 如果 m 是负数,则四舍五入到小数点前 m 位;
– 如果 m 是正数,则四舍五入至小数点后 m 位。
select 'ROUND(2020.01091230):' || round(2020.01091230) from dual;
select 'ROUND(2020.01091230,3):' || round(2020.01091230,3) from dual;
select 'ROUND(2020.01091230,-3):' || round(2020.01091230,-3) from dual;
– 5、TRUNC(n,[m]) 用于截取数字。
– 如果省略数字 m ,则将数字 n 的小数部分截取;
– 如果数字 m 是正数,则将数字 n 截取至小数点后第 m 位;
– 如果数字 m 是负数,则将数字 n 截取至小数点前的 m 位;
select 'TRUNC(2020.01091230):' || trunc(2020.01091230) from dual;
select 'TRUNC(2020.01091230,3):' || trunc(2020.01091230,3) from dual;
select 'TRUNC(2020.01091230,-3):' || trunc(2020.01091230,-3) from dual;
字符函数
– 1、 LTRIM(char [,set]) 去掉字符串char 左端包含的set 中的任何字符。set 默认为空格.
select 'LTRIM:' || ltrim('this is','th') from dual;
– 2、LOWER(char) 将字符串转化为小写格式.
select lower('HELLO WORD') FROM DUAL;
– 3、UPPER(char) 将字符串转化为大写格式.
select upper('hello word') FROM DUAL;
– 4、LENGTH(char) 返回字符串长度.
select length('word') from dual;
– 5、RTRIM(char [,set]) 去掉字符串 char 右端包含的set 中的任何字符。 set 默认为空格.
select rtrim('hello word','or') from dual
– 6、REPLACE(char,search_string[,replace_string]) 将字符串中的字符替换为指定的字符.
select replace('hello word','word',',what are you doing ?') from dual;
转换函数
-- 1、TO_NUMBER() 将符合特定数字格式的字符串转变成数字值
– 这里解释哈下面的 ‘9999D9999’ 写法 ,“D” 前面“9” 的个数是根据前面 “2020.0109” 小数点前面有多少位数字决定的,
– 同理 “D” 后面的 “9” 位数也是一样的道理
select to_number('2020.0109','9999D9999') from dual;
– 2、TO_CHAR() 将日期型转变为字符串
select to_char(sysdate,'dd-mm-yyyy') from dual;
– 3 TO_DATE() 将符合特定格式的字符串转变为date类型的值.
select to_date('09-01-2020','dd-mm-yyyy') from dual;
– 4、NVL(expr1,expr2) 将NULL 转变为实际值。
– 如果expr1 是null ,则返回 expr2 ;
– 如果expr1 不是null,则返回expr1 .
– 参数expr1 和expr2 可以是任意数据类型,但两者的数据类型必须要匹配.
select nvl(username,'木有') from crTale_01 where id=1001
– 5、NVL2(expr1,expr2,expr3) 将NULL 转变为实际值。
– 如果expr1 是null ,则返回 expr3 ;
– 如果expr1 不是null,则返回expr2 .
– 参数expr1 可以是任意数据类型,而参数expr2 和expr3 可以是出long 之外的任何数据类型.
select nvl2(username,'存在','木有') from crTale_01 where id=1001
多表查询
使用集合操作符
– 1、 union 和 union all :表示 或者的意思 ,相当于 or
– union 返回查询检索到的所有不重复的数据
– union all 返回查询所有的数据,包括重复的数据
select * from crTale_01 where address='中国'
union
select * from crTale_01 where sex='男'
select * from crTale_01 where sex='男' or address='中国'
select * from crTale_01 where address='中国'
union all
select * from crTale_01 where sex='男'
– 2、minus : 表示 返回第一个查询检索到的数据减去第二个查询检索到的数据
select * from crTale_01 where sex='男'
minus
select * from crTale_01 where address='中国'
– 3、intersect : 表示返回两个查询都检索到的数据,相当于 and
select * from crTale_01 where sex='男'
intersect
select * from crTale_01 where address='中国';
select * from crTale_01 where sex='男' and address='中国';
连接查询:查询两张或者两张以上的表格或者视图
– 1、使用内连接.
select username,sex,remarks,crTale_02.ttime
from crTale_01,crTale_02
where crTale_01.id=crTale_02.id;
– 2、自连接
自连接是指在同一表之间的连接查询,它主要用在自参照表上显示上下级关系。
select username,sex,remarks,t2.ttime
from crTale_01 t1,crTale_02 t2
where t1.id=t2.id and t1.username='老王';
– 3、内连接
用于返回满足连接条件的记录;
关键词 :inner join
默认连接情况在没有指定的情况下都是内连接方式
select username,sex,age,remarks,t2.ttime
from crTale_01 t1 inner join crTale_02 t2
on t1.id=t2.id and t1.username='老王';
--等价于
select username,sex,age,remarks,t2.ttime
from crTale_01 t1, crTale_02 t2
where t1.id=t2.id and t1.username='老王';
外连接是内连接的扩展,它不仅返回满足连接条件的所有记录,还会返回不满足连接条件的记录
– 4、左连接
关键词 :left join
当使用左外连接时,不仅返回满足连接条件的所有记录,还会返回连接关键词“left join”左边表格中不满足连接条件的其他数据
select username,sex,age,remarks,t2.ttime
from crTale_01 t1 left join crTale_02 t2
on t1.id=t2.id and t1.username='老王';
– 4、右连接
关键词 :right join
当使用左外连接时,不仅返回满足连接条件的所有记录,还会返回连接关键词“left join”右边表格中不满足连接条件的其他数据
select username,sex,age,remarks,t2.ttime
from crTale_01 t1 right join crTale_02 t2
on t1.id=t2.id and t1.username='老王';
事物处理
事物用去确保数据库数据的一致性,事物处理和锁是两个紧密联系的概念。事物就是一个单元的工作。
– 1、用commit 提交事务
--修改数据
update crTale_01 set username='老王' where id=1001
--提交事务
commit
– 2、回退事务
回退可以撤销已进行的操作。
--修改数据
update crTale_01 set username='老王' where id=1001
--回滚事务
rollback
select username from crTale_01 where id=1001
结果为:江小白
– 3、设置保存点
savepoint a;
或者
exec dbms_transaction.savepoint('a')
– 4、取消部分事物
rollback to a;
或者
exec dbms_transaction.rollback_savepoint('a')
– 5、取消全部事物
rollback;
或者
exec dbms_transaction.rollback('a')
事物的ACID属性
数据库事物具有ACID特性。ACID是指原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durabilily)。ACID特性描述了事物处理的规则。
1、原子性
原子性表示事处理要么全部进行,要么全部撤销。
2、一致性
一致性表示事物处理要将数据库从一种状态转变成另一种状态。
3、隔离性
隔离性表示在事物处理提交之前,事务处理的效果不能由系统中其他事物处理看到。
4、持久性
持久性表示一旦提交事务处理,它就永远生效。
过程和函数
存储过程
–存在意义:需要在应用中经常需要通过执行特定的操作,基于这些操作建立一个特定的过程
– 1、 建立一个不带参数的过程
create or replace procedure time_out
is
begin
dbms_output.put_line('当前系统时间为:' || systimestamp);
dbms_output.put_line('当前系统时间为:' || to_char(sysdate,'yyyy-mm-dd'));
end;
--调用执行
begin
time_out;
end;
– 2、 建立一个带参数的过程
create or replace procedure inser_data(name varchar,pwd varchar2,sex char,age number,address varchar2)
is
begin
insert into crTale_01 values(my_num.nextval,name,pwd,sex,age,sysdate,address);
end inser_data;
--调用执行
begin
inser_data('小明','123654','男',20,'重庆市沙坪坝');
end;
– 3、 建立一个带OUT 参数的过程
--创建
create or replace procedure testOut(val1 number,val2 number,val out number)
is
begin
val:=val1+val2;
end;
--调用
declare
result number;
begin
testOut(10,20,result);
dbms_output.put_line(result);
end;
–删除过程
drop procedure testOut
函数
–存在意义:需要在应用中经常需要通过执行SQL 语句来返回特定数据
– 1、创建函数
--创建
--这里提醒一哈,在函数参数列表中的参数名不要和sql语句中的条件 字段同名(eg: inid 不和下面 where 语句中的名相同),
--创建函数可能没问题,但是调用就有问题了.
create or replace function get_username(inid number)
return varchar2
is
retname varchar2(30);
begin
select username into retname from crTale_01 where id=inid;
return retname;
end;
--调用方式1
declare
id number :=1001;
username varchar2(30);
begin
username:=get_username(id);
dbms_output.put_line('用户名为:' || username);
end;
--调用方式2
select get_username(1001) from dual;
--删除函数
drop function get_user