关于SQL一些基本的函数

字符串操作：

concat('Hello', 'World') HelloWorld    连接字符串内容

 

substr('HelloWorld',1,5) Hello     substr（字符串，第一个字符位置,结束字符位置）

 

length ('HelloWorld') 10     字符串长度

 

instr ('HelloWorld', 'W') 6     instr（字符串，要查询的字符) 字符在字符串的位置

 

lpad (salary,10,'*') *****24000    lpad（字符串，想要的总长度，补全字符）左边补全

 

rpad (salary, 10, '*') 24000*****   repad（字符串，想要的总长度，补全字符）右边补全

 

trim ('H' FROM 'HelloWorld') elloWorld   在指定字符串中去除指定字符

 

trim(' HelloWorld') HelloWorld   去除字符串前后空格

 

trim('Hello World') Hello World    去除字符串前后空格

 

大小写转换：

lower ('SQL Course') sql course   将字符串所有大写转换成小写

 

upper('SQL Course') SQL COURSE  将字符串所有小写转换成大写

 

initcap ('SQL course') Sql Course  将字符串首字母转换成大写

 

数字操作函数：

round(45.926, 2) 45.93    默认整数四舍五入 或者 按照所取位数四舍五入

 

trunc(45.926, 2) 45.92   默认整数取整  或者按照所取位数取值

 

mod(1600, 300) 100   取余

 

日期操作：

Months_between ('01-SEP-95','11-JAN-94') 19.6774194        计算两个日期的差

 

add_months ('11-JAN-94',6) 11-Jul-94   加六个月

 

NEXT_DAY ('01-SEP-95','FRIDAY') 8-Sep-95

 

NEXT_DAY ('01-SEP-95',1) 3-Sep-95

 

NEXT_DAY ('1995-09-01',1) ORA01861:literal does not match format string

 

NEXT_DAY (to_date('1995-09-01','YYYY-MM-DD'),1) 3-Sep-95

 

LAST_DAY('01-FEB-95') 28-Feb-95

 

ROUND('25-JUL-95','MONTH') 1-Aug-95

 

ROUND('25-JUL-95' ,'YEAR') 1-Jan-96

 

TRUNC('25-JUL-95' ,'MONTH') 1-Jul-95

 

TRUNC('25-JUL-95','YEAR') 1-Jan-95

 

单行函数

to_char (date, 'format_model') ;     日期到字符串的转换

 

to_char(number, 'format_model')    数字到字符串的转换

 

to_number(char[, 'format_model'])    字符串到数字的转换

 

to_date(char[, 'format_model'])     字符串到日期的转换

其他常用单行函数

nvl (expr1, expr2) 如果expr1为空，这返回expr2

 

nvl2 (expr1, expr2, expr3) 如果expr1为空，这返回expr3（第2个结果）否则返回expr2 nullif (expr1, expr2)     如果expr1和expr2相等，则返回空

 

coalesce (expr1, expr2, ..., exprn)    如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到 一个不为NULL的值 或者如果全部为NULL，也只能返回NULL

条件表达式

case when ..............then

     when…………..then

        ………….

     Else……..

  End   ”…”

 

Decode(条件colum，与colum相等，结果，与colum相等，结果…………………，结果参数) 列名

多表关联查询

交叉连接：相当于没有连接条件的多表关联查询，结果是个笛卡尔乘积

table1 cross join table2

 

自然连接：相当于Oracle的“等于连接”，只不过是让系统自己去找两张表中字段名相同的字段作为 “等于连接”条件；（注意如果两个表中有相同的列名，但字段类型不一样，这会引发一个错误）

Table1 natural join table2

 

Using子句：Using子句可看成是 自然连接 的一种补充功能，我们知道自然连接会让系统自动查找两张表中的 所有列名相同的字段，并试图建立“等于连接”；但有的时候我们不期望这么做，而只是期望某个特定的字段作为“等于连接”的条件，这种情况下可以使用Using 子句来做限制

Using(参数名)

 

内连接：相当于Oracle的“等于链接” ， 关键字：inner join。

Table1 join table2 on 条件 join table1(table2) on 条件

 

外连接：可细分为左外连接、右外连接、全外连接

左外连接：table1 left outer join table2 on 条件

右外连接：table1 right outer join table2 on 条件

全外连接：table1 full outer join table2 on 条件

分组计算函数

1、求和 (sum)

2、求平均值(avg)

3、计数（count）

4、求标准差(stddev)

5、求方差(variance)

6、求最大值(max)

7、求最小值(min)

8、求行数(row_number())

使用方法：select 分组计算函数() from table group by 计算依据

 

count(*)  返回满足选择条件的所有行的行数，包括值为空的行和重复的行

count(expr)    返回满足选择条件的且表达式不为空行数。

count(distinct expr)   返回满足选择条件的且表达式不为空，且不重复的行数。

 

不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句

Select 分组计算函数()，参数

from table

group by 计算依据

having 分组计算函数()限制条件，如：=,>,<等

子查询

注意：

单行比较必须对应单行子查询（返回单一结果值的查询）； 比如=  ， >

多行比较必须对应多行子查询（返回一个数据集合的查询）；比如 IN  , > ANY, > ALL 等

Select colum

From table，(select …from…where…)

where colum><= (select…from…where)

DML语句

Insert对应的列名和value，如果没写，则为null

Insert into table(colum………..) values(value………………..)

Insert into table(colum………..) select … from… where….

 

Update table set colum = value where …..

 

删除某些符合条件的记录:Delete from table where………..

删除一张表中的所有记录:delete from table

数据库对象-表

表的命名要求和表中列的命名要求：

 

1、必须以字母开头

2、长度不能超过30个字符

3、只能包含 A–Z, a–z, 0–9, _, $, and #

4、不能与数据库中的已有对象重名

5、不能使用Oracle  数据库的保留

 

数据类型：

VARCHAR2(size)   可变长字符串

CHAR(size)    定长字符串

NUMBER(p,s)    可变长数值

DATE  日期时间

LONG  可变长大字符串，最大可到2G CLOB 可变长大字符串数据，最大可到4G

RAW and LONG RAW  二进制数据

BLOB   大二进制数据，最大可到4G

BFILE   存储于外部文件的二进制数据，最大可到4G

ROWID   64进制18位长度的数据，用以标识行的地址

TIMESTAMP   精确到分秒级的日期类型（9i以后提供的增强数据类型）

INTERVAL   YEAR TO MONTH 表示几年几个月的间隔（9i以后提供的增强数据类型-极其少见）

INTERVAL DAY TO SECOND   表示几天几小时几分几秒的间隔（9i以后提供的增强数据类型-极其 少见） 

数据库对象-约束

约束的概念：Oracle 数据库使用“约束”来阻止对数据库表中数据的不合法的“增删改”动作。

 

常用的约束有如下几种：

NOT NULL      （非空约束）

UNIQUE        （唯一性约束）

PRIMARY KEY   （主键约束）

FOREIGN KEY   （外键约束）

CHECK         （自定义约束）

 

约束的创建方法：

1、在创建表的时候同时创建约束

2、另外单独创建约束

如：

CREATE TABLE employees

(employee_id  NUMBER(6),          

first_name   VARCHAR2(20),         ...        

job_id       VARCHAR2(10) NOT NULL,      

CONSTRAINT emp_emp_id_pk               

PRIMARY KEY (EMPLOYEE_ID));

单独创建约束语法：

ALTER TABLE tablename ADD CONSTRAINT  constraintname        

constrainttype (column1,…);

使用集合操作

两个表总数据相加

UNION： 去除重复记录

SELECT employee_id, job_id

FROM   employees

UNION

SELECT employee_id, job_id

FROM   job_history;

 

UNION  ALL：保留重复记录

SELECT employee_id, job_id, department_id

FROM   employees

UNION ALL

SELECT employee_id, job_id, department_id

FROM   job_history

ORDER BY  employee_id;

 

INTERSECT：取交集

SELECT employee_id, job_id

FROM   employees

INTERSECT

SELECT employee_id, job_id

FROM   job_history;

 

MINUS：取差集

SELECT employee_id,job_id

FROM   employees

MINUS

SELECT employee_id,job_id

FROM   job_history;