《SQL21日自学通》笔记

最新推荐文章于 2022-07-31 00:52:39 发布

surealin

最新推荐文章于 2022-07-31 00:52:39 发布

阅读量416

点赞数

分类专栏：数据库文章标签： SQL Oracle 数据结构数据挖掘应用服务器

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

之前断断续续学了很多sql的知识也使用了不少，但是一直想做一个总结性的笔记来备查备忘
这次找到了一个电子书《SQL21天自学通》感觉还不错，（电子书放在附件中了）快速的看了一下把其中自己感觉有帮助的地方摘录下来，权当温故知新吧^^

[color=red][/color]select [color=red]DISTINCT[/color] amount from checks;
使用了DISTINCT 所有只有不重复的数据才会被显示

SELECT ITEM WHOLESALE (WHOLESALE+0.15) RETAIL FROM PRICE
你不但可以创建一个新列而且还可以对它安自己的需要进行重命名你可以按语法：“列名别名”来对任何一个[color=red]列进行重命名[/color]（注意在列名与别名之间有空格）

SELECT * FROM FRIENDS WHERE AREACODE>=300
在这个语句中300没有使用引号，[color=red]对于数字型字段是不需要加引号的[/color]

SELECT * FROM FRIENDS WHERE FIRSTNAME <> 'AL'
[color=red]<>相当于!=[/color]

SELECT * FROM VACATION WHERE LASTNAME [color=red]NOT LIKE 'B%'[/color]SELECT * FROM PRICE WHERE WHOLESALE [color=red]IS NOT NULL[/color]

SELECT NAME FROM SOFTBALL [color=red]UNION[/color] SELECT NAME FROM FOOTBALL
返回了两个表中的10个记录，它们是不重复的
SELECT NAME FROM SOFTBALL [color=red]UNION ALL[/color] SELECT NAME FROM FOOTBALL
UNION ALL 与UNION 一样对表进行了合并但是它不去掉重复的记录
SELECT * FROM FOOTBALL INTERSECT SELECT * FROM SOFTBALL
返回两个表中共有的行
SELECT * FROM FOOTBALL [color=red]MINUS [/color]SELECT * FROM SOFTBALL
是存在于第一个表中但不存在于第二个表中的记录

SELECT * FROM FRIENDS WHERE STATE [color=red]IN[/color]('CA','CO','LA')
相当于SELECT * FROM FRIENDS WHERE STATE= 'CA' OR STATE ='CO' OR STATE =
'LA'

SELECT * FROM PRICE WHERE WHOLESALE [color=red]BETWEEN [/color]0.25 AND 0.75
相当于SELECT * FROM PRICE WHERE WHOLESALE<0.25 AND WHOLESALE>
0.75

SELECT COUNT(NAME) NUM_BELOW_350 FROM TEAMSTATS
WHERE HITS/AB <.35
等效于SELECT [color=red]COUNT(*) [/color]NUM_BELOW_350 FROM TEAMSTATS
WHERE HITS/AB <.35
结果是一样的,因为你所选择的NAME 列与WHERE 子句并不相关

SELECT [color=red]SUM(SINGLES)[/color] TOTAL_SINGLES FROM TEAMSTATS
WHERE HITS/AB >=.300
SUM操作对象是某列，求满足where要求的该列总和，注意只能操作于数字

SELECT [color=red]AVG(HITS)/AVG(AB)[/color] TEAM_AVERAGE FROM
TEAMSTATS
AVG 返回某一列的平均值

SELECT [color=red]MAX(NAME)[/color] FROM TEAMSTATS
MAX找出某列的最大值，可以操作于字符串
注：如果我想知道得分最多的人是谁怎么办？
SELECT NAME FROM TEAMSTATS WHERE HITS=MAX(HITS)
报错：ORA-00934 group function is not allowed here
这一信息提示你[color=red]汇总函数无法在WHERE 子句中使用[/color]
那该如何是好呢，别急后面会有子查询来完成这样的需求^^

SELECT [color=red]ADD_MONTHS(ENDDATE,2)[/color] FROM PROJECT
ADD_MONTHS的功能是将给定的日期增加若干个月
[color=red]INITCAP[/color]将参数的第一个字母变为大写，此外其它的字母则转换成小写
[color=red]LOWER[/color]将参数转换为全部小写字母而[color=red]UPPER[/color]则把参数全部转换成大写字母
还有很多日期函数数学函数字符函数这里就不列出来了要用的时候直接google一下即可

如果你想知道的是对每一个PAYEE花了多少钱时又该怎么办呢？
SELECT PAYEE, SUM(AMOUNT) FROM CHECKS [color=red]GROUP BY [/color]PAYEE
SELECT 子句有一个正常的列之后是一个汇总函数。
如果它的后边只有FROM CHECKS 子句的话那么你将会看到：
SELECT PAYEE SUM AMOUNT FROM CHECKS;
Dynamic SQL Error：invalid column reference
该信息表明SQL无法把正常的列和汇总函数结合在一起，这时就[color=red]需要GROUP BY 子句它可以对SELECT 的结果进行分组后再应用汇总函数[/color]
SELECT PAYEE MAX(AMOUNT) MIN(AMOUNT) FROM CHECKS GROUP BY REMARKS
报错，分析下原因：先按REMARKS分组，那么就是说每组REMARK 是一行，但是发现了在某组REMARK 字段中找到了不同的PAYEE，那就没法显示了。

下边的这条语句的目的是返回分组后平均工资低于38000 的组：
SELECT TEAM, AVG(SALARY) FROM ORGCHART WHERE AVG(SALARY)< 38000 GROUP BY TEAM
报错：-Invalid aggregate reference
错误产生的原因是由于汇总函数不能工作在WHERE 子句中如果想要让这个查询工作的话我们需要一些新东西――HAVING 子句
SELECT TEAM, AVG(SALARY) FROM ORGCHART GROUP BY TEAM [color=red]HAVING [/color]AVG(SALARY)< 38000;
注意：不要在having子句中使用非汇总函数，因为得到的结果很可能不是你想要的。
HAVING 子句允许使用多个条件：
SELECT TEAM AVG(SICKLEAVE), AVG(ANNUALLEAVE) FROM ORGCHART GROUP BY TEAM HAVING AVG(SICKLEAVE)< 25 AND AVG(ANNUALLEAVE)> 20
也可以在HAVING 中使用在SELECT 中没有指出的字段进行汇总：
SELECT TEAM AVG(SICKLEAVE), AVG(ANNUALLEAVE) FROM ORGCHART GROUP BY TEAM HAVING COUNT(TEAM)> 1
也可以在HAVING子句中带逻辑操作符：
SELECT TEAM AVG(SALARY) FROM ORGCHART GROUP BY TEAM HAVING TEAM IN ('PR','RESEARCH');
只要记住：[color=red]having是用来对group by所得到的组来进行筛选的[/color]

切记：无论在什么情况下，进行分组SELECT 语句中出现的字段只能是在GROUP BY 中出现过的才可以。这个原因上面已经解释过了。^^

注意：WHERE 子句与ORDER BY 子句常在对单行进行处理时看到；
而GROUP BY 和HAVING 子句常用在对数据进行汇总操作上
如果把它们结合起来使用会有出人意料的结果。
[color=red]WHERE 子句会在分组前将一些记录过滤掉[/color]。
建议是最好不好混用这些子句，在处理实际的数据库时会有不可预知的结果，没试过不知道会有什么结果。。

SELECT * FROM TABLE1,TABLE2
你会发现联合的结果其实就是将TABEL1 中的每一行与TABEL2 中的每一行都接合了起来。
等值连接：
SELECT E.EMPLOYEE_ID, E.LAST_NAME, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE E.EMPLOYEE_ID = EP.EMPLOYEE_ID
AND E.LAST_NAME = 'SMITH';
如果你在联合表的时候没有使用WHERE 子句，你执行的其实是笛卡尔联合也就是笛卡尔叉积，这种联合会对FROM 中指出的表进行完全的组合。如果每个表有200个记录的话，那么所得到的结果将会有40000 行（200 *200），这太大了所以除非你确实是想对表中的所有记录进行联合，否则一定不要忘记使用WHERE 子句。

RIGHT OUTER JOIN 会令SQL 返回右边表集内的全部记录
不要对内部联合和外部联合操太多的心大多数的SQL 产品会判断应该在你的查询中使用哪一种联合。

子查询：
SELECT O.ORDEREDON, O.PARTNUM, P.DESCRIPTION, O.QUANTITY, O.REMARKS, FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND O.PARTNUM = (SELECT PARTNUM FROM PART WHERE DESCRIPTION = "ROAD BIKE")
子查询嵌套：
SELECT C.NAME, C.ADDRESS, C.STATE, C.ZIP FROM CUSTOMER C WHERE C.NAME IN (SELECT O.NAME FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM
AND O.QUANTITY * P.PRICE> (SELECT AVG(O.QUANTITY * P.PRICE) FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM));

[color=red]UPDATE [/color]COLLECTION [color=red]SET [/color]WORTH = 900 WHERE ITEM = 'STRING';
[color=red]INSERT INTO[/color] COLLECTION [color=red]VALUES[/color]('CHIA PET', 5,'WEDDING GIFT');

[color=red]CREATE TABLE [/color]BILLS ( NAME CHAR(30), AMOUNT NUMBER, ACCOUNT_ID NUMBER NOT NULL);

视图常常被称为虚表，它是用CREATE VIEW 语句来建立的
[color=red]CREATE VIEW[/color] CREDITCARD_DEBTS [color=red]AS [/color]SELECT * FROM DEBTS WHERE ACCOUNT_ID = 4;
视图所能进行的操作用以上的操作都能实现，而且其实在数据库底层就是通过上述的各种语句来实现视图的各种操作，但是视图还是有存在的意义。
视图主要用于以下方面：
1 提高用户数据的安全性：比如你只想让用户访问BILLS表中的NAME 字段你需要创建一个名字叫BILLS_NAME 的视图；
2 进行单位换算
3 创建一个新格式的虚表
4 使复杂查询的构筑简单化：234都是为了方便使用

[color=blue]-----------------------分割线：以上是前十天的内容-------------------------[/color]
如果你是一个临时用户只需要偶而使用SQL 从数据库获得数据的话，那么前十天的的主题已经为你提供了足够的内容。但是如果你想开发可以在使用数据库系统下运行的专业应用程序（这在当前在很普遍的），那么你在今后四天中讲到的内容事务控制、安全、内嵌SQL、语句数据库过程将会对你有很大的帮助。

事务控制或者说事务处理是指关系数据库系统执行数据库事务的能力。事务是指在逻辑上必须完成的一命令序列的单位，单元工作期是指事务的开始和结束时期。[color=red]如果在事务中产生的错误那么整个过程可以根据需要被终止[/color]，[color=red]如果每一件事都是正确的那么结果将会被保存到数据库中[/color]。

事务的开始与结束
[color=red]SET TRANSACTION [/color]READ ONLY; //事务建立
SELECT * FROM CUSTOMERS WHERE NAME = 'Bill Turner';
[color=red]COMMIT[/color]; //事务提交

事务的回滚
SET TRANSACTION;
INSERT INTO CUSTOMERS VALUES ("Bubba MacDowell", "2222 Blue Lake Way", "Austin", "TX", 39874);
[color=red]ROLLBACK[/color]; //回滚
SELECT * FROM CUSTOMERS;
注：在COMMIT 语句运行以后，在事务中的所有动作都会得到确认，这时在使用ROLLBACK命令就太晚了。

允许你在当前事务中设一个保存点，从这一点开始如果你使用了ROLLBACK 命令，那么系统将会回到保存点时的状态，而在保存点之前的语句将会得到确认
SQL> SET TRANSACTION
SQL> UPDATE BALANCES SET CURR_BAL = 25000 WHERE ACCOUNT_ID = 5
SQL> [color=red]SAVEPOINT [/color]save_it
SQL> DELETE FROM BALANCES WHERE ACCOUNT_ID = 5
SQL> ROLLBACK TO [color=red]SAVEPOINT [/color]save_it
SQL> COMMIT
SQL> SELECT * FROM BALANCES

创建用户名密码：
CREATE USER Bryan [color=red]IDENTIFIED BY [/color]CUTIGER;
用户名是Bryan，密码是 CUTIGER

赋予与移除角色（三种）（Connect 最少而DBA 则拥有全部的访问能力）：
[color=red]GRANT [/color]CONNECT/RESOURCE/DBA TO Bryan;
[color=red]REVOKE [/color]CONNECT/RESOURCE/DBA FROM Bryan;

该命令将允许系统中的所有用户都具有在自己的模块中创建视图和访问视图的能力：
GRANT CREATE VIEW TO [color=red]PUBLIC[/color];

[color=blue]------------------------分割线：开始第三周-------------------------------[/color]
[color=red]PL/SQL[/color] 是一项ORACLE 的技术它可以让SQL 像过程型语言一样工作。

ROWID 是存在于ORACLE 数据库的每一个表中的预定义列。ROWID 以二进制格式
存储和确定表中的每一列，索引就是使用ROWID 指向数据的。

1 在 [color=red]DECLARE[/color] 部分包括了定义的变量和其它的对象如常量和指针这一部分在PL/SQL 块中是可以选择的。

DECLARE
  customer char(30);//变量
  fiscal_year number(2) NOT NULL := '97';//:=赋初值，这可能是个常量
  cursor employee_cursor is select * from employees;//指针

2 [color=red]PROCDURE[/color] 部分包括条件语句和SQL 语句块可以对它进行控制它是PL/SQL的必须部分
一个PROCDURE 部分的基本结构:

BEGIN
  open a cursor;
  condition1;
  statement1;
  condition2;
  statement2;
  ...
  close the cursor;
END

来看一个例子：

DECLARE
  cursor employee_cursor is
    select emp_id, emp_name from employees;
  id_num employees.emp_id%TYPE;
  name employees.emp_name%TYPE;
BEGIN
  open employee_cursor;
  loop
    fetch employee_cursor into id_num, name;
  end loop;
  close employee_cursor;
END

在这个例子中是把当前的指针所指的行的对应数据填入变量id_num 和name 中，这两
个变量是在DECLARE 部分中定义的。

IF...THEN语法：

IF condition1 THEN
  statement1
ELSIF condition2 THEN
  statement2
ELSE
  statement3

LOOP 本身是一个无限的循环，它经常在指针中使用，如果你想终止这种循环，你必
须指定在什么时候退出。例如，在循环中翻阅指针的时候，你可以指定当指针处于最后一行
的时候退出循环，见下例：

BEGIN
  open employee_cursor
  LOOP
    FETCH employee_cursor into employee_record
    EXIT WHEN employee_cursor%NOTFOUND
    statement1
  END LOOP
  close employee_cursor
END

%NOTFOUND 是指针的一种属性，它表明在当前指针中没有任何数据。在这个例
子中，如果指针没有发现数据就会退出循环，假如你在循环中忽略了这条语句循环将会一
直进行下去。
WHILE-LOOP 则是在当条定条件满足时执行特定的语句而当条件不在满足时就会从循环中退出转而执行下一条语句。
当然还有FOR-LOOP，这里就不列出来了，有兴趣的话可以google一下。

3 [color=red]EXCEPTION[/color] 告诉了PL/SQL 如何处理指定的错误并按用户的定义进行处理它也是PL/SQL 的可选择部分。
在语句块中的异常可以[color=red]由RAISE 语句来激活[/color]，异常可以由程序员进行准确地激活，然而当数据库产生内部错误时它会被自动激活或由默认的数据库服务来调用

BEGIN
  DECLARE
    exception_name EXCEPTION
  BEGIN
    IF condition THEN
      RAISE exception_name
    END IF
  EXCEPTION
    WHEN exception_name THEN
      Statement
  END
END

PL/SQL中也有事务控制语句COMMIT和ROLLBACK，这里就不举例了

[color=red]SQL*PLUS[/color]：这种SQL 是针对ORACLE 的RDBMS 的。
在SQL*PLUS 中你想启动一个文件时，不必一定要输入它的扩展名，数据库默认你要执
行的文件是有扩展名的，与此类似，当你在SQL 提示符下创建编辑一个文件如SAVE GET EDIT 你也不必指定文件的扩展名。

[color=blue]-------------------分割线：常见的SQL错误及解决方法------------------------[/color]
1 Table or View Does Not Exist
如果你已经知道了表是存在的，而你仍然收到了错误信息呢？有时你收到这个信息是因为表并不存在，但是也可以是由于安全原因——也就是说表是存在的，但是你没有权限访问它，这个错误用数据库服务人员的话来精确地说就是“你没有权限来访问这个表”

2 Invalid Username or Password
确认你的密码是正确的吗？如果你确认你输入的用户名和密码是正确的，那么在你访问多个数据库时确认你要联接的数据库是正确的

3 FROM Keyword Not Specified
如果在SELECT 中的列名之后没有逗号，那么查询的处理机制会认为没有FROM 关键字

4 Group Function Is Not Allowed Here
任何组函数不可能在GROUP BY 子句中使用

5 Invalid Column Name
确认列名是正确的？确认表名是正确的？

6 Missing Keyword
创建视图的时候是不是缺了AS？

7 Missing Left Parenthesis
括号左右都完整吗？

8 Column Ambiguously Defined
注意：[color=red]在CREATE VIEW 语句中是不能使用ORDER BY 子句的，要用GROUP BY 来代替[/color]。
查询处理器要在ORDER BY 子句之前寻找结束标志（分号或正斜线）
因为处理器认为ORDER BY 不是CREATE VIEW 的一部分。
而由于在ORDER BY 之前没有结束标志，所以错误返回并指明是在ORDER BY 子句所在行。

9 Oracle Not Available
你没有得到SQL*PLUS 的提示，数据库可能当掉了。检查数据库的状态，同样如果你在访问多个数据库的话，你要确认你的连接是正确的。

10 TNS:listener Could Not Resolve SID Given in Connect Descriptor
在ORACLE 数据库中这个错误很常见， listener 在请求客户与远程服务通讯时产生了
错误，这里是你试图连接数据库，不论是数据库名字错误还是LISTENER 当掉都是有可能
的。你检查一个数据库的名字然后再输入一次，如有必要将这个问题告知数据库管理员。

11 Insufficient Privileges During Grants
你没有将指定权限分配给其他用户的权限

...当然还有很多常见错误这里就不一一列举了^^

[color=blue]--------------------------------结束语---------------------------------[/color]
关于数据库关于SQL有很多值得学习的知识，这里列出的只是LZ在浏览性学习了《SQL21日自学通》这本电子书后做的摘录，都是些比较基础的知识。还有更多知识需要通过实践或者各种途径的充电来等待我们去挖掘^^