每日一得--oracle数据库操作sql收集笔记

数据定义语言DDL相关

创建表

create table tablename(id number primary key not null,column1 varchar2(),column2 varchar2(),column3 varchar2());

修改字段类型

alter table tablename modify   [字段名称]   [数据类型];

修改字段名称

alter table tablename rename column [修改字段名称] to [修改后字段名称];

添加字段

alter table tablename  add   [字段名称]   [字段类型]

删除字段

alter table tablename drop column [字段名称]

数据操作语言DML相关

查询数据库

select * from tablename

插入数据

insert into tablename(字段1，字段2，字段3) values(数据1，数据2，数据3);

更新数据

update tablename set  字段1 = 数据1 where 条件语句

删除数据

delete from tablename where 条件语句

TOP查询

select * from tablename where  rownum <= 10; //展示10天数据

模糊查询like

select * from tablename where column1  like ‘%xxx%’;  查询字段column1 中包含xxx字符串的数据

通配符

 %（替代一个或多个字符）  _（仅替代一个字符） 模糊查询条件使用

操作符IN 

操作符允许我们在 WHERE 子句中规定多个值。

操作符 BETWEEN ... AND 

between ... and ...会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
select * from tablename where column1 between [数据1] and [数据2];

JOIN关联表关联查询

INNER JOIN 关键字在表中存在至少一个匹配时返回行，
LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行，即使在右表 (Orders) 中没有匹配的行。
RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行，即使在左表 (Persons) 中没有匹配的行。
FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配，或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。

UNION 操作符

UNION操作符用于合并两个或多个 SELECT 语句的结果集。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
UNION去重，Employees_china与Employees_USA中相同数据只会列出一条记录
UNION ALL不去重，Employees_china与Employees_USA中相同数据会列出两条记录

INSERT INTO SELECT操作符

INSERT INTO SELECT 语句从一个表中选取数据，然后把数据插入另一个表中。
INSERT INTO SELECT语句常用于创建表的备份复件或者用于对记录进行存档。
insert into Employees_China select * from Employees_USA;

CHECK操作符

CHECK 约束用于限制列中的值的范围。
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
或
ALTER TABLE Persons
ADD CHECK (Id_P>0)

DEFAULT设置默认值

DEFAULT 约束用于向列中插入默认值。

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
撤销DEFAULT方法
ALTER TABLE Persons
MODIFY City  DEFAULT NULL

CREATE INDEX索引

创建索引，优化查询搜索速度

CREATE INDEX PersonIndex
ON Person (LastName) 

删除index
DROP INDEX index_name

视图VIEW

VIEW基于 SQL 语句的结果集的可视化的表，视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

日期相关

系统日期函数sysdate

insert into tablename(id,createdate) values(1,sysdate); //插入系统时间
insert into tablename(id,createdate) values(1,to_date('2015-03-11 16:03:10','yyyy-MM-dd HH24:mi:ss'));//插入相应格式数据的日期
select  to_char(createdate,'yyyy-MM-dd HH24:mi:ss') from tablename; //日期输出展示

oracle相关函数

字符函数

字符函数是oracle中最常用的函数，我们来看看有哪些字符函数：
lower(char)：将字符串转化为小写的格式。
upper(char)：将字符串转化为大写的格式。
length(char)：返回字符串的长度。
substr(char, m, n)：截取字符串的子串，n代表取n个字符的意思，不是代表取到第n个
replace(char1, search_string, replace_string)
instr(C1,C2,I,J) -->判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
问题：将所有员工的名字按小写的方式显示
SQL> select lower(ename) from emp;
问题：将所有员工的名字按大写的方式显示。
SQL> select upper(ename) from emp;
问题：显示正好为5个字符的员工的姓名。
SQL> select * from emp where length(ename)=5;
问题：显示所有员工姓名的前三个字符。
SQL> select substr(ename, 1, 3) from emp;
问题：以首字母大写,后面小写的方式显示所有员工的姓名。
SQL> select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;
问题：以首字母小写,后面大写的方式显示所有员工的姓名。
SQL> select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;
问题：显示所有员工的姓名，用“我是老虎”替换所有“A”
SQL> select replace(ename,'A', '我是老虎') from emp;
问题：instr(char1,char2,[,n[,m]])用法
SQL> select instr('azhangsanbcd', 'zhangsan') from dual; --返回2
SQL> select instr('oracle traning', 'ra', 1, 1) instring from dual; --返回2
SQL> select instr('oracle traning', 'ra', 1, 2) instring from dual; --返回9
SQL> select instr('oracle traning', 'ra', 1, 3) instring from dual; --返回0，根据条件，由于ra只出现二次，第四个参数3，就是说第3次出现ra的位置，显然第3次是没有再出现了，所以结果返回0。注意空格也算一个字符
SQL> select instr('abc','d') from dual;  --返回0

数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round等
我们讲最常用的：
round(n,[m]) 该函数用于执行四舍五入，
如果省掉m，则四舍五入到整数。
如果m是正数，则四舍五入到小数点的m位后。
如果m是负数，则四舍五入到小数点的m位前。
eg、SELECT round(23.75123) FROM dual; --返回24
SELECT round(23.75123, -1) FROM dual; --返回20
SELECT round(27.75123, -1) FROM dual; --返回30
SELECT round(23.75123, -3) FROM dual; --返回0
SELECT round(23.75123, 1) FROM dual; --返回23.8
SELECT round(23.75123, 2) FROM dual; --返回23.75
SELECT round(23.75123, 3) FROM dual; --返回23.751
trunc(n,[m]) 该函数用于截取数字。
如果省掉m，就截去小数部分，
如果m是正数就截取到小数点的m位后，
如果m是负数，则截取到小数点的前m位。
eg、SELECT trunc(23.75123) FROM dual; --返回23
SELECT trunc(23.75123, -1) FROM dual; --返回20
SELECT trunc(27.75123, -1) FROM dual; --返回20
SELECT trunc(23.75123, -3) FROM dual; --返回0
SELECT trunc(23.75123, 1) FROM dual; --返回23.7
SELECT trunc(23.75123, 2) FROM dual; --返回23.75
SELECT trunc(23.75123, 3) FROM dual; --返回23.751
mod(m,n)取余函数
eg、select mod(10,2) from dual; --返回0
SELECT MOD(10,3) FROM dual; --返回1
floor(n) 返回小于或是等于n的最大整数
ceil(n) 返回大于或是等于n的最小整数
eg、SELECT ceil(24.56) from dual; --返回25
SELECT floor(24.56) from dual; --返回24
abs(n) 返回数字n的绝对值
对数字的处理，在财务系统或银行系统中用的最多，不同的处理方法，对财务报表有不同的结果

日期函数

日期函数用于处理date类型的数据。默认情况下日期格式是dd-mon-yy 即“12-7 月-12”
(1)sysdate 返回系统时间
eg、SQL> select sysdate from dual;
(2)oracle add_months函数
oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间
eg、select add_months(sysdate,-6) from dual; --该查询的结果是当前时间半年前的时间
select add_months(sysdate,6) from dual; --该查询的结果是当前时间半年后的时间
(3)last_day(d)：返回指定日期所在月份的最后一天
问题：查找已经入职8个月多的员工
SQL> select * from emp where sysdate>=add_months(hiredate,8);
问题：显示满10年服务年限的员工的姓名和受雇日期。
SQL> select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10);
问题：对于每个员工，显示其加入公司的天数。
SQL> select floor(sysdate-hiredate) "入职天数",ename from emp;
或者
SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp;
问题：找出各月倒数第3天受雇的所有员工。
SQL> select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

转换函数

转换函数用于将数据类型从一种转为另外一种。在某些情况下，oracle server允许值的数据类型和实际的不一样，这时oracle server会隐含的转化数据类型
比如：
create table t1(id int);
insert into t1 values('10');--这样oracle会自动的将'10' -->10
create table t2 (id varchar2(10));
insert into t2 values(1); --这样oracle就会自动的将1 -->'1'；
我们要说的是尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换。
to_char()函数
你可以使用select ename, hiredate, sal from emp where deptno = 10;显示信息，可是，在某些情况下，这个并不能满足你的需求。
问题：日期是否可以显示 时/分/秒
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp;
问题：薪水是否可以显示指定的货币符号
SQL>
yy：两位数字的年份 2004-->04
yyyy：四位数字的年份 2004年
mm：两位数字的月份 8 月-->08
dd：两位数字的天 30 号-->30
hh24： 8点-->20
hh12：8点-->08
mi、ss-->显示分钟\秒
9：显示数字，并忽略前面0
0：显示数字，如位数不足，则用0补齐
.：在指定位置显示小数点
,：在指定位置显示逗号
$：在数字前加美元
L：在数字前面加本地货币符号
C：在数字前面加国际货币符号
G：在指定位置显示组分隔符、
D：在指定位置显示小数点符号(.)
问题：显示薪水的时候，把本地货币单位加在前面
SQL> select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp;
问题：显示1980年入职的所有员工
SQL> select * from emp where to_char(hiredate, 'yyyy')=1980;
问题：显示所有12月份入职的员工
SQL> select * from emp where to_char(hiredate, 'mm')=12;
           
to_date()函数
函数to_date用于将字符串转换成date类型的数据。
问题：能否按照中国人习惯的方式年—月—日添加日期。
eg、SELECT to_date('2012-02-18 09:25:30','yyyy-mm-dd hh24:mi:ss') FROM dual;

sys_context()系统函数

1)terminal：当前会话客户所对应的终端的标示符，如计算机名
2)language: 语言
3)db_name： 当前数据库名称
4)nls_date_format： 当前会话客户所对应的日期格式
5)session_user： 当前会话客户所对应的数据库用户名
6)current_schema： 当前会话客户所对应的默认方案名
7)host： 返回数据库所在主机的名称
通过该函数，可以查询一些重要信息，比如你正在使用哪个数据库？
select sys_context('USERENV','db_name') from dual;
注意：USERENV是固定的，不能改的，db_name可以换成其它,
eg、select sys_context('USERENV','language') from dual;
select sys_context('USERENV','current_schema') from dual;