SQL应用初级指南

（一）SQL 简介

SQL 诞生于IBM 公司在加利福尼亚San Jose 的试验室中，在七十年代SQL 由这里开发出来。最初它们被称为结构化查询语言（Structured Query Language） ，并常常简称为sequel。

S： Structured -- 结构

L ：Language -- 语言

Q ：Query -- 查询（如果你直译的话）。

 

（二）简单的查询

-- 将字符执行 '加' 操作

SELECT FIRSTNAME || LASTNAME ENTIRENAME FROM FRIENDS

-- 出错!

SELECT FIRSTNAME + LASTNAME ENTIRENAME FROM FRIENDS

-- NAME 是不重复的

SELECT NAME FROM SOFTBALL UNION SELECT NAME FROM FOOTBALL

-- NAME 会重复

SELECT NAME FROM SOFTBALL UNION ALL SELECT NAME FROM FOOTBALL

-- 两表中都存在的 NAME (相交查询)

SELECT NAME FROM SOFTBALL INTERSECT SELECT NAME FROM FOOTBALL

-- 存在于 FOOTBALL 表中但不存在于 SOFTBALL 表中

SELECT NAME FROM FOOTBALL MINUS SELECT NAME FROM SOFTBALL

（三）函数：对数据的进一步处理

-- 汇总函数

-- VARIANCE(方差),不是标准中所定义的但它却是统计领域中的一个至关重要的数值

-- STDDEV,返回某一列数值的标准差

SELECT COUNT(AB),AVG(AB),MIN(AB),MAX(AB),STDDEV(AB),VARIANCE(AB),SUM(AB) FROM TEAMSTATS

-- 日期,时间函数

-- ADD_MONTHS (变动月份,加上负数等于减少月份)

SELECT TASK,STARTDATE,ORIGINAL_END,ADD_MONTHS(ENDDATE,2) FROM PROJECTS

-- LAST_DAY (返回指定月份的最后一天)

SELECT ENDDATE,LAST_DAY(ENDDATE) FROM PROJECTS

-- MONTHS_BETWEEN (给定的两个日期中有多少个月)

SELECT STARTDATE,ENDDATE,MONTHS_BETWEEN(STARTDATE,ENDDATE)FROM PROJECTS

-- NEW_TIME (调整所在时区时间)

SELECT ENDDATE,NEW_TIME('EDT','PDT',ENDDATE) FROM PROJECTS

-- NEXT_DAY (返回给定日期后的第一个指定日期)

SELECT STARTDATE,NEXT_DAY(STARTDATE,'FRIDAY') FROM PROJECTS

-- SYSDATE (系统当前时间)

SELECT SYSDATE FROM PROJECTS

-- 数学函数

-- ABS 绝对值

SELECT ABS(A) FROM NUMBERS

-- CEIL 和 FLOOR (CEIL 返回与给定参数相等或比给定参数大的最小整数, FLOOR 则正好相反)

SELECT CEIL(JAVA),FLOOR(CSHARP) FROM SCORE

-- 还有些并不常用的三角函数,平方等等就不举例了

-- 字符函数

-- CHR 返回与所给数值参数等当的字符,返回的字符取决于数据库所依赖的字符集.

-- 数值32处显示为空白,因为32 在ASCLL 码表中是空格

SELECT CODE, CHR(CODE) FROM CHARACTERS

-- CONCAT 将两个字符串连接起来 与操作符 '||'相同

SELECT CONCAT(FIRSTNAME,LASTNAME) FROM CHARACTERS

-- INITCAP 将参数的第一个字母变为大写,其它的字母则转换成小写

select INITCAP(FIRSTNAME) from cover_info

-- LOWER 和 UPPER 转换大小写

SELECT FIRSTNAME,UPPER(FIRSTNAME), LOWER(FIRSTNAME) FROM CHARACTERS

-- LPAD 与 RPAD

-- 这两个函数最少需要两个参数,最多需要三个参数.

-- 第一个参数是需要处理的字符串,第二个参数是需要将字符串扩充的宽度

-- 第三个参数表示加宽部分用什么字符来做填补,第三个参数的默认值为空格,但也可以是单个的字符或字符串

SELECT LASTNAME,LPAD(LASTNAME,20,'*') FROM CHARACTERS

-- LTRIM 与 RTRIM 去除左右指定字符,默认为空格

-- 至少需要一个参数,最多允许两个参数.

-- 第一个参数是字符串,第二个参数也是一个字符或字符,串默认则是空格

-- 如果第二个参数不是空格的话,那么该函数将会像剪除空格那样剪除所指定的字符

SELECT LASTNAME,RTRIM(LASTNAME) FROM CHARACTERS

-- REPLACE 替换字符

-- 该函数需要三个参数,第一个参数是指定字符串,第二个参数是预替换内容

-- 第三个参数则是需要替换成的字符串

SELECT LASTNAME,REPLACE(LASTNAME,'ST','**') FROM CHARACTERS

-- SUBSTR 截取字符串

-- 这个函数有三个参数,允许你将目标字符串的一部份输出

-- 第一个参数为目标字符串,第二个字符串是将要输出的子串的起点,第三个参数是将要输出的子串的长度

-- 如果没有第三个参数将会输出字符串余下的部分

SELECT FIRSTNAME SUBSTR(FIRSTNAME,2,3) FROM CHARACTERS

-- TRANSLATE 字符替换

-- Translate函数与replace函数及其相似，都是用来做字符替换的，但又有所区别

-- replace是将整个字符替换为指定的字符，但translate是一个字符一个字符对应替换!

SELECT TRANSLATE('abc3243dbc','abc','efg') FROM DUAL // Result: efg3243dfg

SELECT REPLACE('abc3243dbc','abc','efg') FROM DUAL // Result: efg3243dbc

-- 从上面的sql可以看出，replace函数是整体替换,而translate是将a替换为e,b替换为f，c替换为g

-- INSTR 相当于C#中 string.IndexOf()方法,但又有所区别,返回结果是位置而非字符下标

SELECT INSTR('TAYLOR','O',2,1) FROM CHARACTERS // Result: 5

-- LENGTH 返回指定字符串的长度

SELECT LENGTH(FIRSTNAME) FROM CHARACTERS

-- 转换函数

-- TO_CHAR

-- 该函数的最初功能是将一个数字转换为字符型,不同的解释器可能会使用它来转换其它的数据类型

-- 例如日期型转换为字符型或者是拥有更多的参数

SELECT TO_CHAR(12345) FROM CONVERT

-- TO_NUMBER 将字符转换为数值

SELECT TO_NUMBER('12345') FROM CONVERT

 

（四）复杂子句组合

-- 复杂子句组合

SELECT PAYEE,SUM(AMOUNT),TOTAL, COUNT(PAYEE) FROM CHECKS WHERE AMOUNT >= 100 

GROUP BY PAYEE HAVING SUM(AMOUNT) > 50

 

（五）表的联合

-- 交叉联合 (表联合的原型)

SELECT * FROM TABLE1,TABLE2 // 返回两表行数的乘积

-- 等值联合

SELECT C.NAME, C.ADDRESS, O.QUANTITY * P.PRICE, TOTAL, P.DESCRIPTION

FROM ORDERS O, PART P, CUSTOMER C

WHERE O.PARTNUM=P.PARTNUM AND O.NAME = C.NAME ORDER BY C.NAME

-- 外部联合与内部联合

-- 就像不等值联合与等值联合相对应一样,外部联合是与内部联合相对应的

-- 内部联合的行数取决于 WHERE 子句的结果

SELECT P.PARTNUM, P.DESCRIPTION, P.PRICE O.NAME, O.PARTNUM

FROM PART P JOIN ORDERS O ON ORDERS.PARTNUM = 54 // 内联

-- 返回右边表集内的全部记录,ORDERS.PARTNUM<>54 的行,则补以空值显示

SELECT P.PARTNUM, P.DESCRIPTION, P.PRICE, O.NAME, O.PARTNUM 

FROM PART P RIGHT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54 // 外部右联合

-- 与内部联合的结果一样,因为你使用的是左联合,因此 PART 表决定返回的行数

-- 而PART 表比 ORDERS 表小,所以 SQL 把其余的行数都扔掉了

SELECT P.PARTNUM, P.DESCRIPTION, P.PRICE, O.NAME, O.PARTNUM

FROM PART P LEFT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54  // 内部左联合

-- 在一些解释器中使用+号来代替外部联合,+号的意思就是显示全部内容包括不匹配的内容

-- 这条语句将会联合两个表,标有+号的employee_id 将会全部显示包括不满足条件的记录

select e.name, e.employee_id, ep.salary, ep.marital_status 

from e,ployee_tbl e,employee_pay_tbl ep

where e.employee_id = ep.employee_id(+) and e.name like '%MITH'

-- 表的自我联合 SELECT * FROM TABLE1,TABLE1

-- 这种联合用于检查内部数据的一致性

-- 如果你的零件生产部门的某人犯了迷糊输入了一个已经存在的零件号时将会发生什么呢?

-- 这对于每一个人来说都是一个坏消息,发票会开错,你的应用程序会崩溃

SELECT F.PARTNUM, F.DESCRIPTION, S.PARTNUM, S.DESCRIPTION

FROM PART F, PART S 

WHERE F.PARTNUM = S.PARTNUM AND F.DESCRIPTION <> S.DESCRIPTION // Result: PARTNUM 重复的记录将被找出

 

(六) INSERT AND UPDATE

-- 利用 INSERT 向临时表中复制数据

INSERT INTO INVENTORY (ITEM, COST, REMARKS) SELECT ITEM, WORTH, REMARKS FROM COLLECTION

 

(七) 创建和操作数据库,数据表

-- 创建数据库

-- 这可以很简单,也可以很复杂,大多数数据库都允许你创建库时同时指定一些杂项

-- 例如数据库的尺寸,日志文件大小,物理路径等等

CREATE DATABASE database_name // 这是最简单的一种语法

-- 创建表

CREATE TABLE BILLS 

(

  NAME CHAR(30) NOT NULL,

  AMOUNT NUMBER,

  ACCOUNT_ID NOT NULL

)

-- 用一个已经存在的表来建表

CREATE TABLE NEW_BILLS(NAME, AMOUNT, ACCOUNT_ID)

AS (SELECT * FROM BILLS WHERE AMOUNT < 50)

-- ALTER TABLE 语句可以帮你做两件事

-- 1. 加入一列到已经存在的表中

ALTER TABLE BILLS MODIFY NAME CHAR(40)

-- 2. 修改已经存在的表中的某一列

ALTER TABLE NEW_BILLS ADD COMMENTS CHAR(80)

-- DROP TABLE 删除表

-- 可以从数据库中删除一个指定的表以及与之相关联的索引和视图

-- 一旦这个命令发出以后就没有办法可以彻消它,它最常用在你删除一个已经使用完毕的临时表

DROP TABLE table_name

-- DROP DATABASE 删除数据库

DROP DATABASE database_name

 

(八) 创建视图和索引

视图：

常常被称为虚表，使用 CREATE VIEW 语句来建立，在视图建立以后你可以对视图采用如下命令：SELECT、INSERT、INPUT、UPDATE、DELETE。

注：

用户可以通过创建视图来查询特定的数据。如果你的表有 50 列且有成千上万个记录，但是你只需要其中两列的话，你可以创建视图来选择这两列然后从视图中查询。你会发现查询在数据返回时间上与原来有相当大的不同。

视图可以以比数据在数据库表中的存储情况更为便捷的方式来返回数据。当需要连续进行几个复合的查询时，例如在存储过程和应用程序中时，视图也是非常方便的。

-- 创建视图

CREATE VIEW VIEW_COMPANY_INFO (NAME, STATE) AS

SELECT * FROM COMPANY

-- 创建视图的视图 (这也是允许的,有时还是必须的)

CREATE VIEW VIEW_COMPANY_NAME (NAME) AS

SELECT NAME FROM VIEW_COMPANY_INFO

-- 视图中列的重命名 (因此,程序中使用视图也隐藏了真是数据库中字段,安全性得以提高)

CREATE VIEW ENVELOPE (COMPANY, MAILING_ADDRESS) AS

SELECT NAME, ADDRESS + " " + CITY + ", " + STATE FROM COMPANY;

在视图中修改数据的一些限制：

1. 对于多表视图你不能使用 DELETE 语句

2. 除非底层表的所有非空列都已经在视图中出现，否则你不能使用 INSERT 语句。

3. 如果对一个归并的表格插入或更新记录 那么所有被更新的记录必须属于同一个物理表。
4. 如果你在创建视图时使用了 DINTINCT 子句那么你就不能插入或更新这个视图中的记录。
5. 你不能更新视图中的虚拟列（它是用计算字段得到的）。

 

索引：

       是与磁盘上数据的存储方式不同的另外一种组织数据的方法。索引的特例是表中记录依据其在磁盘上的存储位置显示，索引可以在表内创建一个列或列的组合，当应用索引以后数据会按照你使用 CREATE INDEX 语句所定义的排序方式返回给用户。通过对正确的特定的两个表的归并字段进行索引可以获得明显的好处。

       在数据库中使用索引可以让 SQL 使用直接访问方式（另一种是顺序访问方式，通常要查询所有的数据）。SQL 采用树形结构来存储和返回索引数据。用以指示的数据存储在树的最末端也就是叶子，它们被称为结点也可以叫叶子。每一个结点中有一个指向其它结点的指针，结点左边的值只是它的双亲结点，结点右边的值则是孩子结点或叶子。SQL 将从根结点开始直到找到所需要的数据。

       当查询没有使用索引的表时查询通常是全表搜索后才会得到结果。全表搜索会让数据库服务程序遍历过表中的所有记录然后返回给定条件的记录，这种方法就好比从图书馆的第一号书架的第一本书找起直到找到了你所需要的书一样。你或许会使用卡片柜以更快地找到所需的书，索引可以让数据库服务程序快速地定位到表中的确定行，幸运的是这个树结构不需要由你来制作。

使用索引的技巧：

1. 对于小表来说，使用索引对于性能不会有任何提高
2. 当你的索引列中有极多的不同的数据和空值时，索引会使性能有极大的提高
3. 当查询要返回的数据很少时索引可以优化你的查询，比较好的情况是少于全部数据的25%，如果你要返回的数据很多时索引会加大系统开销
4. 索引可以提高数据的返回速度，但是它使得数据的更新操作变慢，在对记录和索引进行更新时请不要忘记这一点！如果要进行大量的更新操作在你执行更新操作时请不要忘记先删除索引，当执行完更新操作后只需要简单的恢复索引即可。对于一次特定的操作，系统可以保存删除的索引18 个小时，在这个时间内数据更新完后你可以恢复它。
5. 索引会占用你的数据库的空间，如果你的数据库管理系统允许你管理数据库的磁盘空间，那么在设计数据库的可用空间时要考虑索引所占用的空间
6. 对字段的索引已经对两个表进行了归并操作，这一技术可以极大地提高归并的速度
7. 大多数数据库系统不允许你对视图创建索引，如果你的数据库系统允许这样做那么可以使用这种方法来在 SELECT 语句中对视图的数据进行排序。很不巧一些数据库
系统中也不允许在视图中使用 ORDERY BY 子句
8. 不要对经常需要更新或修改的字段创建索引，更新索引的开销会降低你所期望获得的性能
9. 不要将索引与表存储在同一个驱动器上，分开存储会去掉访问的冲突从而使结果返回得更快

-- 一般索引

CREATE INDEX ID_INDEX ON BILLS(ACCOUNT_ID)

-- 复合索引

-- 虽然是对两个字段进行索引,但索引在物理结构上只有一个

CREATE INDEX ID_CMPD_INDEX ON BILLS(ACCOUNT_ID,AMOUNT)