----------------SQL语言基础-----------------:摘自《Oracle + 11g + 从入门到精通》
1.SQL的主要功能
1)数据定义功能(DDL)
2)数据查询功能(DQL)
3)数据操纵功能(Data Manipulation Lauguage,DML)
4)数据控制功能(DCL)
2.语句编写规则
1)关键字、对象名和列名不区分大小写
2)字符值和日期值区分大小写
3.数据定义
1)创建表
CREATE TABLE <表名>
(
<列名> <数据类型> [列级完整性约束条件]
[,<列名> <数据类型> [列级完整性约束条件]] ...
[,<表级完整性约束条件>]
);
2)创建视图:虚表,只存放视图的定义,不存放视图对应的数据
CREATE VIEW <视图名> [(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION] --表示对视图进行UPDATE、INSERT和DELETE操作时,要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)
3)创建索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名> [<次序>][,<列名> [<次序>]]...);
4)删除表
DROP TABLE <表名>; --删除表后,数据,索引会删除,但是视图定义不会
5)删除视图
DROP VIEW <视图名>
6)删除索引
DROP INDEX <索引名>
7)修改建立好的基表,SQL语言中,没有提供删除属性列的语句
ALTER TABLE <表名>
[ADD ,<新列名> <数据类型> [完整性约束]]
[DROP <完整性约束名>]
[MODIFY <列名> <数据类型>];
4.数据查询
SELECT [ALL|DISTINCT] TOP n[PERCENT] WITH TIES select_list
[INTO [ new table name]]
[
FROM {table_name|view_name}[(optimizer_hints)]
[,{table_name2|view_name2}[(optimizer_hints)]]
[...{table_name2|view_name2}[(optimizer_hints)]]
]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]
1)LIKE表达式中,通配符'%'代表0个、1个或多个任意字符,'_'代表一个任意字符。
2)使用GROUP BY需要满足的条件
a. 在SELECT子句的后面只可以有两类表达式:统计函数和进行分组的列名
b. 在SELECT子句中的列名必须是进行分组的列,除此之外添加其他的列名都是错误的,但是,
GROUP by子句后面的列名可以不出现在select子句中
c.如果使用了where子句,那么所有参加分组计算的数据必须首先满足where子句指定的条件
d.默认情况下,将按照GROUP BY子句指定的分组列升序排序,如果需要重新排序,可以使用ORDER BY子句指定新的排列顺序。
3)HAVING子句对GROUP BY分组的结果做进一步的筛选。
4)多表连接查询
逗号连接,JOIN连接
FROM join_table1 join_type join_table2
[ON(join_condition)]
join_type:
a.内连接(INNER JOIN)可省略INNER),使用ON指定连接表的连接条件,可以用于多表
b.自然连接(natural join):Oracle会将第一个表中的那些列与第二个表中具有相同名称的列进行连接,实际中应用较少
c.外连接(LEFT OUTER JOIN 或 LEFT JOIN; RIGHT OUTER JOIN或RIGHT JOIN;FULL OUTER JOIN或FULL JOIN)
与内连接不同的是,外连接不只列出与连接条件相匹配(on)的行,还列出左表(左外连接时)、右表(右外连接时)
或两个表(全外连接时)中所有符合搜索条件(WHERE,HAVING)的数据行。
在执行完全外连接时,系统开销很大,因为Oracle实际上会执行一个完整的左连接查询和右连接查询,然后再将其结果集合并,并消除重复的记录行。
d.自连接:自引用式外键意味着表中的一个列可以是该表主键的一个外键。例如,EMPLOYEES表的MANAGER_ID列可以是另一行的EMPLOYEE_ID
select em1.last_name "manager",em2.last_name "employee"
from employees em1 left join employees em2
on em1.employee_id = em2.manager_id
order by em1.employee_id
5)集合操作
UNION,UNION ALL, INTERSECT,MINUS
UNION运算会将合集中的重复记录滤除,这是UNION运算和UNION ALL 运算唯一不同的地方。
6)子查询
是一个select语句,可以在select、insert、update或delete语句中使用。虽然大部分子查询是在select语句的where子句中实现,但实际上
它的应用不仅仅局限于此。例如,可以在SELECT和HAVING子句中使用
a.IN关键字: where column_name in (select...)
b.EXISTS关键字:where exists(select * from...),只考虑是否满足判断条件,不考虑数据内容,返回TRUE OR FALSE
c.比较运算符:适用于子查询返回结果只包含一个值。>avg(...)
5.数据操纵
1)一般INSERT语句
INSERT INTO [user.]table [@db_link][(column1 [,column2]...)]
VALUES(express1[,express2]...)
使用desc table_name 命令查看表中各列的定义次序。
2)批量INSERT语句
INSERT INTO [user.]table [@db_link][(column1 [,column2]...)] Subquery
INSERT INTO指定的列名可以与SELECT指定的列名不同,但是其数据类型必须相匹配,即SELECT返回的数据必须满足表中列的约束
3)UPDATE语句
UPDATE table_name
SET {column1 = express1[,column2 = express2],
column1[,column2] = (select query)}
[WHERE condition]
4)DELETE语句
DELETE FROM table_name
[WHERE condition]
5)TRUNCATE语句
删除表中所有的记录,速度要比DELETE语句快,因为它不会产生回滚信息,不能被撤销。
TRUNCATE TABLE TABLE_NAME
6.数据控制
事务管理功能和数据保护功能
1)GRANT 语句
GRANT <权限>[,<权限>]...
[ON<对象类型><对象名>]
TO<用户>[,<用户>]
[WITH GRANT OPTION]
对于不同类型的操作对象有不同的操作权限,对属性列和视图的操作包括SELECT/INSERT/UPDATE/DELETE/ALLPRIVILEGES。
对基表的操作包括SELECT/INSERT/UPDATE/DELETE/ALTER/INDEX/ALLPRIVILEGES
对数据库可以有建立表(CREATETAB)的权限
例:
GRANT SELECT ON TABLE DEPARTMENT TO User2,User3
GRANT UPDATE(EMPLOYEE_ID),SELECT ON TABLE IT_EMPLOYEES TO User4;
2)REVOKE语句:收回权限的操作会级联下去
REVOKE <权限>[,<权限>]...
[ON<对象类型><对象名>]
FROM<用户>[,<用户>]
7.Oracle常用函数
1)字符类函数
ASCII(<c1>) 返回c1中第一个字母的ASCII码,c1是字符串
CHR(<i>) 求i对应的ASCII字符
CONCAT(C1,C2) 将C2连接到C1后面,和||操作符效果一样
INITCAP(C1),将C1中每个单词的第一个字母大写,其他字母小写返回。单词由空格、控制字符、标点符号限制
INTSTR(C1,[C2,<i>[,j]]) 返回c2在c1中第j次出现的位置,搜索从c1的第i个字符开始。当没有发现需要的字符时
返回0,如果i为负数,搜索将从右到做进行,但是位置还是从左到右来计算,i和j默认值为1
INSTRB(C1,[C2,<i>[,j]]) 与INSTR()函数一样,只是其返回的是字节,对于单字节INSTRB()等于INSTR()。
LENGTH(C1) 该函数用于返回c1的长度,如果c1为null,那么将返回NULL值
lower(c1) 该函数用于返回c1的小写字符,经常出现在where子串中。
LTRIM(c1,c2) 将c1中最左边的字符去掉,使其第一个字符不在c2中,如果没有c2,那么c1就不会改变。
REPLACE(C1,C2[,C3]) 用c3代替出现在c1中的c2后返回,其中c1,c2,c3都是字符串
SUBSTR(C1,<i>[,j]) 从c1的第i位开始返回长度为j的子字符串,如果j为空,则直到串的尾部。
TRIM(c1)用于将c1的前后空格删除
2)数字类函数
ABS(n),ACOS(n),ASIN(n),ATAN(n),CEIL(n),COS(n),COSH(n),EXP(n),FLOOR(n),LN(n),LOG(n1,n2),MOD(n1,n2),POWER(N1,N2)
ROUND(n1,n2)[四舍五入], SIGN(n)[返回-1,0,1],SIN(n),SINH(n),SQRT(n),TAN(n),TANH(n),TRUNC(n1,n2)[截尾]
3)日期类函数
ADD_MONTHS(d,<i>) LAST_DAY(d) MONTHS_BETWEEN(d1,d2) NEW_TIME(d1,tz1,tz2) SYSDATE
4)转换类函数
CHARTORWID(c1)
CONVERT(c1,dset[,sset])
ROWIDTOCHAR()
TO_CHAR(x[,fmt[nlsparm,]])
TO_DATE(c1[,fmt[nlsparm,]])
TO_MULTI_BYTE(c1)
TO_NUMBER(c1[,fmt[nlsparm,]])
TO_SINGLE_BYTE(c1)
5)聚集类函数,返回基于多个行的单一结果
AVG(x[{DISTINCT|ALL}])
COUNT(x[{DISTINCT|ALL}])
MAX(x[{DISTINCT|ALL}])
MIN(x[{DISTINCT|ALL}])
STDDEV(x[{DISTINCT|ALL}]) --标准差
SUM(x[{DISTINCT|ALL}])
VARIANCE(x[{DISTINCT|ALL}]) --方差