02. SQL note

先登陆服务器： telnet 192.168.0.23 公帐号： openlab-open123 tarena-tarena
再进入SQL：sqlplus sd0807/sd0807 帐号：sd0807－密码同样 公帐号：openlab-open123

设置环境变量：
ORACLE_SID=oral10g\ --变局部变量
export ORACLE_SID --变全局变量
unset ORACLE_SID --卸载环境变量
ORACLE_HOME=... --安装路径；直接用一句语句也可以，如下
export ORACLE_HOME=/oracledata/.../bin:

一、注意事项：
大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调式。
“！”在SQL环境下执行Unix命令。
SQL语句是由简单的英语单词构成；这些英语单词称为关键字/保留字，不做它用。SQL由多个关键字构成。
SQL语句由子句构成，有些子句是必须的，有些是可选的。
在处理SQL语句时，其中所有的空格都被忽略(空格只用来分开单词，连续多个空格当一个用)。
SQL语句可以在一行上写出，建议多行写出，便于阅读和调试。
多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号，但特定的DBMS可能必须在单条SQL语句后加分号。
SQL语句的最后一句要以 “；”号结束

二、写子句顺序
Select column,group_function
From table
[Where condition]
[Group by group_by_expression]
[Having group_condition]
……
[Order by column]; --最后

三、常用简单语句：
clear screen：清屏
edit：编辑刚才的一句。
desc/describe：(列出所有列名称)
用法： DESCRIBE [schema.]object[@db_link]
dual：亚表，临时用。如：desc dual；／from dual；
rollback：回溯，回溯到上次操作前的状态，把这次事务操作作废，只有一次(DDL和DCL语句会自动提交，不能回溯)。
可以用commit语句提交，这样就回溯不回了。
set pause on\off ：设置分屏(设置不分屏)
set pause "please put an enter key" 且 set pause on：设置带有提示的分屏
oerr ora 904 ：查看错误
set head off ：去掉表头
set feed off ：去掉表尾
保存在oracle数据库中的所有操作细节：
spool oracleday01.txt ：开始记录
spool off ：开始保存细节

四、SELECT语句：选择操作、投影操作。
select：从一个或多个表中检索一个或多个数据列。包含信息：想选择什么表，从什么地方选择。必须要有From子句。(最常用)
当从多张表里查询的时候，会产生笛卡尔积；可用条件过滤它。
当两个表有相同字段时必须加前缀，列名前需加表名和“.”，如“s_emp.id”。
1、用法：SELECT columns,prod2,prod3<列> FROM Table1,table2<表名> 分号结束
如： select id from s_emp;
select last_name,name from s_emp,s_dept where s_emp.dept_id=s_dept.id;--列表每人所在部门
SELECT * FROM Products; --检索所有列。
数据太多时，最好别使用上句，会使DBMS降低检索和应用程序的性能。(*通配符)
2、对数据类型的列可进行运算(如加减乘除)。
3、对列起别名：有直接起别名，加AS起别名，用双引号起别名等三种方法
(单引号，引起字符串；双引号，引起别名。起别名有符号，或者区分大小写时，必须用双引号)
多表查询时，可给表起别名。(给列起别名，列<空格>列别名；给表起别名，表<空格>表别名；)。
如：Select first_name EMPLOYEES, 12*(salary+100) AS MONEY, manager_id "ID1" From s_emp E;
4、字段的拼接,可用双竖线(双竖线只能用于select语句里)。不同的DBMS可能使用不同的操作符;拼接的字段同样可以起别名。
如：Select first_name ||' '|| last_name || ', '|| title "Employees" From s_emp;

排他锁：Select id,salary From s_emp where id=1 For Update;
可以阻止他人并发的修改，直到你解锁。
如果已有锁则自动退出：Select id,salary From s_emp where id=1 For Update NoWait;
FOR UPDATE ：可以再加 OF 精确到某格。如： ... For Update OF salary ...
注意要解锁。

五、ORDER BY 子句，排序
Order by：按某排序列表(默认升序 asc，由低到高；可加 desc，改成降序由高到低)
检索返回数据的顺序没有特殊意义，为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句。
ORDER BY 子句取一个或多个列的名字。
对空值，按无穷大处理(升序中，空值排最后；降序中排最前)。
1、用法：Select prod_id,prod_price,prod_name From Products Order By prod_price,prod_name;
(从左到右执行排序，先排price)
ORDER BY子句中使用的列将是为显示所选择的列，但是实际上并不一定要这样，用非检索的列排序数据是完全合法的。
为了按多个列排序，列名之间用逗号分开。
2、支持按相对列位置进行排序。
输入 SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY 2，3 --(2指price，3指name)
3、升序、降序。默认是升序(asc，从小到大排序)，想降序时用desc。
如：SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC;
注意：DESC 关键字只应用到直接位于其前面的列名。如果想在多个列上进行排序，必须对每个列指定DESC关键字。
升序是默认的，可不写，但降序必须写。


六、WHERE子句，选择、过滤
其后只能跟逻辑语句，返回值只有ture或false
如： select last_name,salary from s_emp where salary＝1000;--找出工资1000的人

WHERE子句操作符：
1、逻辑比较运算符
＝ 等于
！= 不等于，还有(<> ^= 这两个同样表示不等于)
> 大于
>= 大于等于
< 小于
<= 小于等于

2、SQL 比较运算符
between…and… ：在两者之间。(BETWEEN 小值 AND 大值)
如：select last_name,salary from s_emp where salary between 1000 and 1500；
--工资1000到1500的人，包括1000和1500。
in(列表)：在列表里面的。
如：select last_name,dept_id from s_emp where dept_id in(41,42);第41、42部门的人
like ： 包含某内容的。模糊查询
可以利用通配符创建比较特定数据的搜索模式，通配符只能用于文本，非文本数据类型不能使用通配符。
通配符在搜索模式中任意位置使用，并且可以使用多个通配符。
通配符%表示任何字符出现任意次数；还能代表搜索模式中给定位置的0个或多个字符。下划线匹配单个任意字符。
如：select table_name from user_tables where table_name like 'S\_%' escape'\';
' 找出“S_“开头的,由于下划线有任意字符的含义，故需另外定义转移符。
但习惯用“\”，为方便其他程序员阅读和检测，一般不改用其他的。
like 'M%'：M开头的 like '_a%'：第二个字符是a的 like '%a%'所有含a的
(“_”表示一个任意字符；“%”表示任意多个任意字符。)
单引号里面的内容，大小写敏感。单引号用来限定字符串，
如果将值与串类型的列进行比较，则需要限定引号；用来与数值列进行比较时，不用引号。
is null：是空。(NULL表示不包含值。与空格、0是不同的。)
如：SELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL;


七、高级检索(逻辑运算符)：
通常我们需要根据多个条件检索数据。可以使用AND或OR、NOT等连接相关的条件
计算次序可以通过圆括号()来明确地分组。不要过分依赖默认计算次序，使用圆括号()没有坏处，它能消除二义性。

and：条件与
如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 AND vend_id=‘DELL’
or：条件或 (注： and 的优先级比 or 更高，改变优先级可用括号)
如 SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 OR vend_id=‘DELL’
not：条件非。否定它之后所跟的任何条件
否定的SQL 比较运算符： NOT BETWEEN； NOT IN； NOT LIKE； IS NOT NULL：
(注意，按英语习惯用 is not，而不是 not is)
NOT 与 IN 在一起使用时，NOT 是找出与条件列表不匹配的行。
IN 列表里有 NULL 时不处理，不影响结果；用 NOT IN 时，有 NULL 则出错，必须排除空值再运算。
in ：选择列表的条件
使用IN操作符的优点： 在长的选项清单时,语法直观； 计算的次序容易管理；
比 OR 操作符清单执行更快；最大优点是可以包含其他 SELECT 语句，使用能够动态地建立 WHERE 子句。
如 SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id IN(‘DELL’,’RBER’,’TTSR’);


八、单行函数：
函数一般在数据上执行，它给数据的转换和处理提供了方便。不同的DBMS提供的函数不同。
函数可能会带来系统的不可移植性(可移植性:所编写的代码可以在多个系统上运行)。
加入注释是一个使用函数的好习惯。
大多数SQL实现支持以下类型的函数： 文本处理， 算术运算， 日期和时间， 数值处理。

Null：空值
空值当成无穷大处理，所有空值参与的运算皆为空。
空值与空值并不相等，因为空值不能直接运算。
如：prod_price="" 这种写法是错的(不要受到corejava的影响)
prod_price=NULL 这种写法是错的(不要受到corejava的影响)
prod_price IS NULL 这种写法才是对的
NVL：处理空值，把空值转化为指定值。可转化为日期、字符、数值等三种(注意：转化时，两参数必须要同类型)
如：NVL(date, '01-JAN-95') NVL(title,'NO Title Yet') NVL(salary,0)
错误写法：
Select last_name,title,salary*commission_pct/100 COMM From s_emp;--没提成的人没法显示工资
正确写法：
Select last_name,title,salary*NVL(commission_pct,0)/100 COMM From s_emp;--把提成是空值的转化为0

DISTINCT：过滤重复
把重复的行过滤掉；多个字段组合时，只排除组合重复的。
DISTINCT必须使用列名，不能使用计算或者表达式。
所有的聚合函数都可以使用。如果指定列名，则DISTINCT只能用于COUNT(列名)，DISTINCT不能用于COUNT(*)。
如：Select Distinct name From s_dept; Select Distinct dept_id,title From s_emp;

文本处理：
TRIM()/LTRIM()/RTIRM()：去空格。只能去掉头和尾的空格，中间的不理。
trim(' heo Are fdou ') --> heo Are fdou
输入：select trim(' heo Are fdou ') from dual; -->：heo Are fdou
LOWER：转小写
lower('SQL Course') --> sql course
UPPER：转大写
upper(' SQL Course') --－>SQL COURSE
INITCAP：首字母转大写，其余转小写
initcap(SQL Course') '--> Sql Course

CONCAT：合成。双竖线只能在select语句里面用，这个可用于任何语句。
Concat('Good','String') --> GoodString
SUBSTR：截取。
Substr('String', 1 ,3) --> Str
第一个数字“1”，表示从第几个开始截取；若要从倒数第几个开始，用负数，如“－2”表示倒数第2个。
上式中第2个数字“3”表示截取多少个。
LENGTH：统计长度。
Length('String') --> 6
NVL：转换空值

日期和时间处理：
Oracle日期格式：DD-MMM-YYYY (D代表日期date，M代表月month，Y代表年year)
如：SELECT prod_name (DAY表示完整的星期几，DY显示星期的前三个字母)
FROM Products
WHERE prod_time BETWEEN
to_date(’01-JAN-2008’)
AND to_date(’31-DEC-2008’);
日期可以进行加减，默认单位是1天。日期与日期可以相减，得出天数；日期与日期但不能相加。
sysdate －> 系统的当天
Months_Between('01-Sep-95','11-Jan-94') --> 19.774194 相差多少个月,Between里面也可以填函数。
Add_months('11-Jan-94',6) --> 11-Jul-94 增加多少个月
Next_day('01-Sep-95','Friday') --> '08-Sep-95' 下一个星期五。其中的'Friday'可用6替代，因为星期日＝1
Last_day('01-Sep-95') --> '30-Sep-95' 这个月的最后一天


数值处理：可以运用于代数，三角，几何
ROUND：四舍五入
Round(45.925,2) －> 45.93 Round(45.925,0) －> 46 Round(45.925,-1) －> 50
逗号前一个数是要处理的数据源，后一个参数表示保留多少位小数。
后一参数是负数时，表示舍去小数点前的几位，例3是舍去个位及其后的。不写后一参数时，默认不保留小数。
TRUNC：舍去末位。直接舍去，不会进位。
Trung(45.925,2) －> 45.92 Trung(45.925,2) －> 45.92 Trung(45.925,2) －> 45.92
日期的舍取：


常用的数值处理函数有：
ABS() 绝对值 ABS(-5741.5854) --> 5741.5854
PI() 圆周率 注意：oracle中不支持 PI()函数；MYSql 支持PI()函数。
SIN() 正统值 Oracle还支持COS()、ASIN()、ACOS()函数
SQRT() 平方根


转化：
TO_CHAR(number,'fmt')：把数值转换成字符串
显示数字的命令
9：正常显示数字；
0：显示包括0的数值形式，空位强制补0；
$：以美元符号显示货币；
L：按当前环境显示相关的货币符号；
. 和，：在固定位置出现“.”点 和“，”逗号；不够位时，四舍五入。
例题：SQL> select 'Order'||To_char(id)||
2 'was filled for a total of'
3 ||To_char(total,'fm$9,999,999')
4 from s_ord
5 where ship_date ='21-SEP-92';

TO_NUMBER(char)：把字符转换成数字


九、链接
内链接：严格匹配两表的记录。
外链接分左链接和右链接:
会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，
数据库会模拟出记录去和那些不匹配的记录匹配。
左链接 加号在右面
如：有 TABLE1 TABLE2
1的一条记录在2里面没有匹配上，那么1里面的记录保留
2的一条记录在1里面没有匹配上 ，那么2丢弃
右链接正好相反
--例题：哪些人是领导。
select distinct b.id,b.last_name manager
from s_emp a,s_emp b
where a.manager_id=b.id(+);
左右顺序有区别，这是另外新建一个表，要显示的是第二个表格的内容。
+放在没有匹配行的表一侧，令表格能完整显示出来。

标准写法：内连接用INNER，左连接用LEFT，右连接用RIGHT。
select distinct b.id,b.last_name manager
from s_emp a LEFT join s_emp b
ON a.manager_id=b.id;


十、组函数：
分组允许将数据分为多个逻辑组，以便能对每个组进行聚集计算。
Group：分组
Group by：分组。(默认按升序对所分的组排序；想要降序要用 order by)可以包括任意数目的列。
如果嵌入了分组，数据将在最后规定的分组上进行汇总。
GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数。
*如果在SELECT 中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。
除聚合计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL，它们将分为一组。

Having：过滤。分组之后，不能再用where，要用having 选择过滤。Having不能单独存在，必须跟在group by后面。
WHERE在数据分组前进行过滤，HAVING在数据分组后过滤。
可以在SQL中同时使用 WHERE和HAVING，先执行WHERE，再执行HAVING。


聚合函数：
AVG：平均值 (忽略值为NULL的行，但不能用 AVG(*))
COUNT：计数 (Count(列)不计算空值；但 COUNT(*)表示统计表中所有行数，包含空值)
MAX：最大值 (忽略列值为 NULL 的行。但有些DBMS还允许返回文本列中的最大值，
在作用于文本数据时，如果数据按照相应的列排序，则 MAX()返回最后一行。)
MIN：最小值 (忽略值为 NULL 的行。不能用 MIN(*)。一般是找出数值或者日期值的最小值。
但有些DBMS还允许返回文本列中的最小值，这时返回文本最前一行)
SUM：求和 (忽略值为 NULL 的值。SUM 不能作用于字符串类型，而 MAX()，MIN()函数能。也不能 SUM(*))


子查询：查询语句的嵌套
可以用于任意select 语句里面，但子查询不能出现 order by。
子查询总是从内向外处理。作为子查询的SELECT 语句只能查询单个列，企图检索多个列，将会错误。
如：找出工资最低的人select min(last_name),min(salary) from s_emp;
或者用子查询select last_name,salary from s_emp where salary=(select min(salary) from s_emp);

E-R图：属性: E(Entity) -R(Relationship)
* (Mandatory marked 强制的) 强制的非空属性
o (Optional marked 可选的) 可选属性(可以有值也可以没有)
#* (Primary marked ) 表示此属性唯一且非空

约束：针对表中的字段进行定义的。
PK：primary key (主键约束，PK=UK+NN)保证实体的完整性，保证记录的唯一
主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，
只有两个字段放在一起唯一标识记录，叫做联合主键(Composite Primary Key)。
FK：foreign key (外建约束)保证引用的完整性,外键约束,外键的取值是受另外一张表中的主键或唯一值的约束,不能够取其他值,
只能够引用主键会唯一键的值，被引用的表，叫做parent table(父表)，引用方的表叫做child table(子表);
child table(子表)，要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，
删除记录，要先删除子表记录，后删除父表记录，
要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。
U：unique key(唯一键 UK)，值为唯一，不能重复。
在有唯一性约束的列，可以有多个空值，因为空值不相等。
NN：NOT NULL，不能为空。
index(索引)是数据库特有的一类对象，实际应用中一定要考虑索引，view(示图)

数量关系: 一对一关系
多对一关系
一对多关系
多对多关系

范式：
好处：降低数据冗余；减少完整性问题；标识实体，关系和表
第一范式(First normal form：1Nf)，每一个属性说一件事情。所有的属性都必须是单值，也就是属性只表示单一的意义。
(记录可以重复，会有大量冗余，没有任何限制)
第二范式(2N范式)，最少有一个属性要求唯一且非空PK，其他跟他有关联(记录不可重复，但是数据可能会出现冗余)。
第三范式(3N范式)，非主属性只能依赖于主属性,不能依赖于其他非主属性。(解决数据冗余问题,不能存在推理能得出的数据)
一般情况会做到第三范式。


创建表： Create Table 表名
(字段名1 类型(数据长度)(default ...) 约束条件,
字段名2 类型(数据长度) 约束条件 );
建表的名称：
必须字母开头；最多30字符；只能使用“A～Z、a～z、0～9、_、$、#”；
同一目录下不能有同名的表；表名不能跟关键字、特殊含意字符同样。
如：create table number_1 (n1 number(2,4), n2 number(3,-1), n3 number);
create table t_sd0808(id number(12) primary key,name varchar(30) not null);
MySQL的： create table student (oid int primary key, ACTNO varchar(20) not null unique,
BALANCE double); --MySQL的number类型分小类了，Oracle只有number，且MySQL的数值型不用定大小
Oracle的： create table t_ad (oid number(15) primary key,
ACTNO varchar(20) not null unique,BALANCE number(20));

INSERT：插入(或添加)行到数据库表中的关键字。
插入方式有以下几种：插入完整的行；插入行的一部分；插入某些查询的结果。
对于INSERT操作，可能需要客户机/服务器的DBMS中的特定的安全权限。
插入行(方式一) INSERT INTO products VALUES(2008,’TV’,222.22,’US’);
依赖于表中定义的顺序，不提倡使用。有空值时需要自己补上。
插入行(方式二) INSERT INTO products(id,name,price,vend_name) VALUES(2008,’TV’,222.22,’US’);
依赖于逻辑顺序，会自动补上空值，提倡使用。

插入检索出的数据：可以插入多条行到数据库表中
INSERT INTO products(*,*,*,*)
SELECT *,*,*,*
FROM products_copy;
如果这个表为空，则没有行被插入，不会产生错误，因为操作是合法的。
可以使用WHERE加以行过滤。

复制表： 将一个表的内容复制到一个全新的表(在运行中创建，开始可以不存在)
CREATE TABLE 新表名 AS
SELECT *
FROM 表名;

INSERT INTO 与 CREATE TABLE AS SELECT 不同，前者是导入数据，而后者是导入表。
任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY。
可利用联接从多个表插入数据。不管从多少个表中检索数据，数据都只能插入到单个表中。

更新数据 UPDATE 语句
需要提供以下信息：要更新的表；列名和新值；确定要更新的哪些行的过滤条件。
UPDATE 表名
SET vend_name = ‘HP’,
prod_name = ‘NEWCOMPUTER’
WHERE vend_name = ‘IBM’;
--UPDATE 语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。也可以将一个列值更新为 NULL。

删除数据 DELETE 语句
DELETE
FROM products
WHERE prod_name = ‘COMPUTER’;
全行删除，不要省略WHERE，注意安全。
DELETE不需要列名或通配符。删除整行而不是删除列。DELETE是删除表的内容而不是删除表。
如果想从表中删除所有内容，可以使用TRUNCATE TABLE语句(清空表格)，它更快。

数字字典表：
Sequence:排列。存储物理地址

Index:索引。依附于表，为提高检索速度。

View:视图。看到表的一部分数据。
限制数据访问。简化查询。数据独立性。本质上是一个sql查询语句。
Create[or Relace][Force|noForce] View 视图名
[(alias[,alias]…)] 别名列表
As subquery
[With Check Option [Constraint ……]]
[With Read Only]
注意：有些DBMS不允许分组或排序视图，不能有 Order by 语句。可以有 Select 语句。
删除视图： DROP VIEW 视图名

Rownum:纬列。内存里排序的前N个。
在where语句中，可以用＝1，和<=N 或 <N；但不能用＝N 或 >N。
因为这是内存读取，没有1就丢弃再新建1。只能从1开始。需要从中间开始时，需二重子rownum语句需取别名。
经典应用： Top-n Analysis (求前N名或最后N名)
Select [查询列表], Rownum
From (Select [查询列表(要对应)]
From 表
Order by Top-N_字段)
Where Rownum <= N

分页显示：
--取工资第5～10名的员工(二重子rownum语句，取别名)
select rn,id,last_name,salary
From ( select id,last_name,salary,Rownum rn
From (Select id,last_name,salary
from s_emp
order by salary desc)
where rownum <= 10)
where rn between 5 and 10;


Union：合并表
Select … Union Select… 把两个Select语句的表合并。
要求两表的字段数目和类型按顺序对应。合并后的表，自动过滤重复的行。
Intersect：交。 同上例，把两个Select表相交。
Minus：减。 把相交的内容减去。
not exists 除运算。


添加字段(列)：
Alter Table 表名
Add (column dataype [Default expr][Not Null]
[,column datatype]…);
添加有非空限制的字段时，要加Default语句
字段名字不可以直接改名，需要添加新字段，再复制旧字段后删除旧字段。
添加约束： Alter Table 表名
Add [CONSTRAINT constraint] type (column);
添加非空约束时，要用Modify语句。
查看约束名时，可以违反约束再看出错提示；或者查看约束字典desc user_constraints

减少字段：
Alter Table 表名
Drop (column [,column]…);
删除约束： Alter Table 表名
Drop CONSTRAINT column;
或： Alter Table 表名
Drop Primary Key Cascade;

暂时关闭约束，并非删除：
Alter Table 表名
Disable CONSTRAINT column Cascade;
打开刚才关闭的约束：
Alter Table 表名
Enable CONSTRAINTcolumn;

修改字段：
Alter Table 表名
Modify (column dataype [Default expr][Not Null]
[,column datatype]…);
修改字段的类型、大小、约束、非空限制、空值转换。

删除表：
会删除表的所有数据，所有索引也会删除，约束条件也删除，不可以roll back恢复。
Drop Table 表名 [Cascade Constraints];
加 [Cascade Constraints] 把子表的约束条件也删除；但只加 [Cascade]会把子表也删除。

改表名：
Rename 原表名 To 新表名;

清空表格：
TRUNCATE TABLE 表名;
相比Delete,Truncate Table清空很快，但不可恢复。清空后释放内存。
Delete 删除后可以roll back。清空后不释放内存。