Oracle笔记(4)
一、注意事项:
大小写不敏感,即不区分大小写。提倡关键字大写,便于阅读和调式。
“!”在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 :开始保存细节
命令行工具:
DESCRIBE s_dept
List:列出缓冲区中最后一条SQL语句。
edit:编辑存缓冲区中的最后一条SQL语句。
当保存退出后,可以 ls /(最后一条语句的;要删除掉)
四、SELECT语句:选择操作、投影操作。
user_tables
别名:一般都加双引号,这样就可以在别名中使用空格,并且区分大小写;
连接符:
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;
NVL函数:NVL(字段名,value)表示如果查询的字段名为null时则该字段的查询输出为value;
排他锁: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表示不包含值。与空格、05是不同的。) 排序作为最大值。
如: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’);
排序:优先排第一个,派完第一才派第二,一次类推;
查询中子句的执行顺序:(SQL执行器的执行顺序)
1:from子句
2:where子句
3:group by子句
4:having
5:select子句
6:order by 子句
组函数只能出现在from group by having order by子句中
dual表:伪表;不存放任何业务数据;
用来当对一些表达式或常量的时候,可以从这个伪表来补充完SQL语句的完整性。
八、单行函数:
函数一般在数据上执行,它给数据的转换和处理提供了方便。不同的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:按当前环境显示相关的货币符号;
安装Orcal的时候所选择字符集确定;
后面也可以通过环境变量来设定:
设成中文:setenv NLS_LANG 'SIMPLIFIED CHINESE_CHINA,ZHSIGGBK'
美国:AMERICAN-AMERICA,USTASCII
. 和,:在固定位置出现“.”点 和“,”逗号;不够位时,四舍五入。
例题: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):把字符转换成数字
九、链接
1、等值连接和不等值连接
两个以上的表之间的连接
2、自连接
应用:部门级别之间或者会员之间都可以
3、外连接
如果多表关联时,当有些关联字段为null时,用内关联的不被教唆出来的。
内链接:(同一张表的两张虚拟表)严格匹配两表的记录。
外链接分左链接和右链接:
会使用一方表中的所有记录去和另一格表中的记录按条件匹配,空值也会匹配,这个表中的所有记录都会显示,
数据库会模拟出记录去和那些不匹配的记录匹配。
左链接 加号在右面
如:有 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(+);
先把a中的都查出来,排除重复的,然后和b匹配,不管b在a中有没有匹配上,a中的都取;
左右顺序有区别,这是另外新建一个表,要显示的是第二个表格的内容。
+放在没有匹配行的表一侧,令表格能完整显示出来。
标准写法:内连接用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;
select c.name name, e.first_name "wname"
--from s_customer c, s_emp e
--where c.sales_rep_id = e.id(+)
from s_customer c left outer join s_emp e
on c.sales_rep_id = e.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(DISTINCT|ALL|n):平均值 (忽略值为NULL的行,但不能用 AVG(*))
COUNT(DISTINCT|ALL|expr|*): 计数 (Count(列)不计算空值;但 COUNT(*)表示统计表中所有行数,包含空值)
MAX:最大值 (忽略列值为 NULL 的行。但有些DBMS还允许返回文本列中的最大值,
在作用于文本数据时,如果数据按照相应的列排序,则 MAX()返回最后一行。)
MIN:最小值 (忽略值为 NULL 的行。不能用 MIN(*)。一般是找出数值或者日期值的最小值。
但有些DBMS还允许返回文本列中的最小值,这时返回文本最前一行)
SUM:求和 (忽略值为 NULL 的值。SUM 不能作用于字符串类型,而 MAX(),MIN()函数能。也不能 SUM(*))
如果出现某些字段没有经过聚合函数处理的会出现歧义。
经典面试:(t_temp表中有year和salary两个字段);
要求按年份分组,查出其前面各工资和,并给出相应的结果;
select y.year, sum(s.salary)
from t_temp y, t_temp s
where s.year <= y.year
group by y.year
order by y.year
子查询:查询语句的嵌套
不相关自查询:
在where子句中嵌套一个子查询 先执行子查询,然后再执行主查询。
子查询里的字段也可以有多个(个数和顺序和要比较的要一致)
可以用于任意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 ) 表示此属性唯一且非空
Attribute:属性,描述用户实体的相应属性 一般用小写表达。
--------:代表可有可无.
-: 实线代表有关系.
工具:
windows: visio
sysbase: PowerDesiginer
约束:针对表中的字段进行定义的。
PK:primary key (主键约束,PK=UK+NN)保证实体的完整性,保证记录的唯一
主键约束,唯一且非空,并且每一个表中只能有一个主键;有两个字段联合作为主键;
只有两个字段放在一起唯一标识记录,叫做联合主键(Composite Primary Key)。
FK:foreign key (外建约束)保证引用的完整性,外键约束,外键的取值是受另外一张表中的主键或
唯一值的约束,不能够取其他值,也可以为空;只能够引用主键或唯一键的值;被引用的表,
叫做parent table(父表),引用方的表叫做child table(子表);
child table(子表),要想创建子表,就要先创建父表,后创建子表,记录的插入也是如此,先父表后子表,
删除记录,要先删除子表记录,后删除父表记录,
要修改记录,如果要修改父表的记录要保证没有被子表引用。要删表时,要先删子表,后删除父表。
U:unique key(唯一键 UK),值为唯一,不能重复。 (UID)
在有唯一性约束的列,可以有多个空值,因为空值不相等。
NN:NOT NULL,不能为空。
index(索引)是数据库特有的一类对象,实际应用中一定要考虑索引,view(示图)
数量关系: 一对一关系
多对一关系 左边有多个对右边的一个
一对多关系 左边有一个和右边的多个对
多对多关系 左边有多个和右边的多个对应
多对一的主键和外键的设计:以多中的表为主表(即在多的表中添加一中的主键作为多表中的
外键). 实例:用户与论坛帖子
一对一是多对一的一个特例,只是在外键上加上唯一键。(unique key)
也可以把主键同时当作外键来用(这时要考虑如果主表中的记录删除了另外一个表的记录
是否要删除的问题) 实例:丈夫和妻子
多对多中的主键和外键的设计:添加多一张表;添加的表的主键为复合主键(多对多中的两个
表中的主键相组合); 实例:学生于课表
范式:
好处:降低数据冗余;减少完整性问题;标识实体,关系和表
第一范式(First normal form:1Nf),每一个属性说一件事情。所有的属性都必须是单值,也就是属性只表示单一的意义。
(记录可以重复,会有大量冗余,没有任何限制)
第二范式(2N范式),最少有一个属性要求唯一且非空PK,其他跟他有关联(记录不可重复,但是数据可能会出现冗余)。
实例:比如说员工表(employee)中的部门不依赖员工的编号
第三范式(3N范式),非主属性只能依赖于主属性,不能依赖于其他非主属性。(解决数据冗余问题,不能存在推理能得出的数据)
一般情况会做到第三范式。
(在第二范式基础上把不依赖于非主属性独立出来设计另外一张表,
利用外键,唯一键来添加关系)
DDL语句:
创建表: Create Table 表名
(字段名1 类型(数据长度)(default ...) 约束条件,
字段名2 类型(数据长度) 约束条件 );
建表的名称:
必须字母字符开头;最多30字符;只能使用“A~Z、a~z、0~9、_、$、#”;
同一目录下不能有同名的表;表名不能跟关键字、特殊含义字符同样。
数据类型:
varchar2(size):变长,当是' '字符串就当作null来处理; [标准是varchar]
char(size):定长,交换时较快,但会占用多余空间。
number(p,s)
p:精度,有效位数;从整数位第一个不是0的整数开始算,包含小数,但不包含小数点;
当s>0时,有效位长度是p;当s<0时,有效位长度为p+|s|;
s:刻度,小数位数;
先检查小数位,多余的四舍五入;再检查有效位,如果位数超出有效位,则该数是放不进;
clob: character data up to 4GB (character large object)
blob:binary data up to 4GB
date:日期类型,默认格式:dd-mon-rr
可以表示到:世纪,年,月,日,时,分,秒
其他的格式:
如: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));
user_constraints表存放对表或字段的约束条件
对某表的字段添加约束:
column [constraint constraint_name] constraint_type
column,...
[constraint constraint_name] constraint_type(column,...)
当是创建复合主键时只能用表级约束来创建。
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语句(清空表格),它更快。
级联删除:在从表的外键引用中添加on delete cascade
查询:(select ) 行,共享锁
修改:(DML) 行,排它锁;表,共享锁;
事务控制:对insert into , update, delete;
数字字典表:
Sequence:排列。存储物理地址(生成不重复的值,是一个共享的数据)
代理主键就可以使用Sequence对象来生成
业务主键就不可以这样子生成;
一般俄代理主键是使用数值(查询效率高),
创建Sequence对象:
create sequence name
[increment by n]
[start with n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache n | nocache}]
使用sequence对象:(name为sequence对象名)
name.nextval
查看当前窗口使用到的sequence对象值:
name.currval
sequence对象存储在user_sequences表中
其他数据库生成不重复的值:
SQL Server:Indentity
MySQL:auto_increment;
Index:索引。依附于表,为提高检索速度。
主键会自动创建索引和手动创建两种.
索引树(B-Tree)
应用索引的场合
在使用索引时有时会失效.
存储索引值的表:
user_indexes
user_ind_columns
注意创建索引的条件:
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 视图名
只有是简单视图也可以进行DML操作。
简单视图:对一张表,没有函数,不包含分组语句;
利用视图对原始的某行数据删除的限制条件:
用视图修改数据的条件:
利用视图添加数据的条件:
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 除运算。
exists 通过子查询的条件来过滤主查询的内容。
学生表(stu),课程表(course),学生选课表(stu_course)
统计选了所有课程的学生:
select s.id, s.name
from stu s
where s.id in (select sid
from stu_course sc
group by sc.sid
having count(sid) = (select count(c.id)
from course c)
);
对结果集进行样式转换: (decode 函数)
DECODE(col | expression, search1, result1
[, search2, result2,
...
default])
select id,
decode(id, 1, 'south america',
2, 'north america',
3, 'Africa',
4, 'Asia',
'No Region')
from
day5 12:
select job, max(decode(deptno, 10, cn)) dept_10
,max(decode(deptno, 20, cn)) dept_20
,max(decode(deptno, 30, cn)) dept_30
,max(decode(deptno, 40, cn)) dept_40
from(
select job, deptno, count(empno) cn
from emp
group by deptno, job
)
group by job
case
when then
end 函数:
查询员工加薪后的工资(工资大于1000,加20%; 小于2000,加15%;大于2000,加10%)
select first_name "name", case
when salary < 1000 then salary * (1 + 0.2)
when salary between 1000 and 2000 then salary * (1 + 0.15)
when salary > 2000 then salary * (1 + 0.1)
else salary
end salary
from s_emp
添加字段(列):
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。清空后不释放内存。
alter table子句
rename :重新修改表名 (主意可以修改的用户)
truncate table 表名
delete DML (数据可以恢复)
truncate DDL (不支持事务,操作了就不可以恢复)