二、限制选择行
1. 按指定的规则排序
SELECT expr FROM table [ORDER BY {column, expr} [ASC | DESC] ];
默认的排序是ASC升序(由小到大)
还可以ORDER BY 字段名的位置[1]| [2] ASC| DESC;
当字段名很复杂或者是算术表达式时用字段名显示的位置排序很方便.
2. 用WHERE限制选择行(1)
比较操作符 = > < >= <= != <> ^= 与NULL比较不能用上面的比较操作符 ANY SOME ALL
SQL操作符 BETWEEN … AND… IN LIKE IS NULL
NOT BETWEEN … AND… NOT IN NOT LIKE IS NOT NULL
逻辑操作符 AND OR NOT
3. 用WHERE限制选择行(2)
比较顺序(可以用括号改变它们的顺序)
(1). = < > >= <= in like is null between
(2). and
(3). Or
注意: char和varchar2的比较规则有不同:
char比较时会忽略字符串后面的空格. varchar2会计算字符串后面的空格
4. LIKE操作
% 零到任意多个字符 _ 一个字符
例如: 字段名 like 'M%' 字段名 like '%m%' 字段名 like 'job_'
如果要找含下划线的字符, 要加反斜线 例如:字段名 like '%X/_Y%' escape '/'
5. 日期字段的比较
举例:
日期字段 between to_date('2001-12-12','YYYY-MM-DD') and to_date('2002-02-01','YYYY-MM-DD')
日期字段> to_date('2001-12-12','YYYY-MM-DD') and日期字段<= to_date('2002-02-01','YYYY-MM-DD');
6. 不能用到索引的比较操作符
IS NULL
IS NOT NULL
LIKE '%m%'
三、单行函数
1. 数字函数
ABS 取绝对值 POWER 乘方 LN 10为底数取幂
SQRT 平方根 EXP e的n次乘方 LOG(m,n) m为底数n取幂
数学运算函数:ACOS ATAN ATAN2 COS COSH SIGN SIN SINH TAN TANH
CEIL 大于或等于取整数
FLOOR 小于或等于取整数
MOD 取余数
ROUND(n,m) 按m的位数取四舍五入值如果round(日期): 中午12以后将是明天的日期. round(sysdate,'Y')是年的第一天
TRUNC(n,m) 按m的位数取前面的数值如果trunc(日期), 确省的是去掉时间
2. 字符函数
CHR 按数据库的字符集由数字返回字符
CONCAT(c1,c2) 把两个字符c1,c2组合成一个字符, 和 || 相同
REPLACE(c,s,r) 把字符c里出现s的字符替换成r, 返回新字符
SUBSTR(c,m,n) m大于0,字符c从前面m处开始取n位字符,m等于0和1一样,
m小与0,字符c从后面m处开始取n位字符
TRANSLATE(c,f1,t1) 字符c按f1到t1的规则转换成新的字符串
INITCAP 字符首字母大写,其它字符小写
LOWER 字符全部小写
UPPER 字符全部大写
LTRIM(c1,c2) 去掉字符c1左边出现的字符c2
RTRIM(c1,c2)
TRIM(c1,c2) 去掉字符c1左右两边的字符c2
LPAD(c1,n,c2) 字符c1按制定的位数n显示不足的位数用c2字符串替换左边的空位
RPAD(c1,n,c2)
3. 日期函数
ADD_MONTHS(d,n) 日期值加n月
LAST_DAY(d) 返回当月的最后一天的日期
MONTHS_BETWEEN(d1,d2) 两个日期值间的月份,d1NEXT_DAY(d) 返回日期值下一天的日期
SYSDATE 当前的系统时间
DUAL是SYS用户下一个空表，它只有一个字段dummy
4. 转换函数(1)
TO_CHAR(date,'日期显示格式')
TO_CHAR(number) 用于显示或报表的格式对齐
TO_DATE(char,'日期显示格式')
TO_LOB 把long字段转换成lob字段
TO_NUMBER(char) 用于计算或者比较大小
4. 转换函数(2)
to_date里日期显示格式
YYYY 年 YEAR YYY YY Y
Q 季度
MM 月 MONTH MON
W 星期 (week of month) WW, IW (week of year)
(说明：周计是按ISO标准,从1月1日的星期数到后面七天为一周,不一定是从周一到周日)
DD 日 DAY DY
HH24 小时 HH12 HH
MI 分钟
SS 秒
如果想固定日期的显示格式可以在数据库的参数文件initorasid.ora里新写一行参数 NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss可以在UNIX环境变量或者NT的注册表里的设置 NLS_DATE_FORMAT=yyyy-mm-dd hh24:mi:ss
4. 转换函数(3)
如果想固定日期的显示格式可以用alter session命令改变
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
它的作用顺序如下:
initialization parameter
Environment variable
ALTER SESSION command
4. 转换函数 (4)
to_char(number)里数字显示格式
9 数字位
0 数字前面补0 to_char(-1200,'00000.00')
. 小数点的位置
, 标记位置的逗号 用在数字显示格式的左边
L 根据数据库字符集加货币符号 to_char(-1200,'L9999.99')
B 把数字0显示为空格,用在数字显示格式的右边
MI 右边的负数标记 to_char(-1200,'9999.99MI')
PR 括起来的负数 to_char(-1200,'9999.99PR')
EEEE 用指数方式表示 to_char(-1200,'9999.99EEEE')
5. 输入字符,返回数字的函数
instr(c1,c2) 字符c2出现在c1的位置, 不出现, 返回0, 常用于模糊查询
length? 按数据库的字符集,算出字符c的长度,跟数据库的字符集有关, 一个汉字长度为1
6. 有逻辑比较的函数NVL(EXPR1, EXPR2)函数
解释: IF EXPR1=NULL RETURN EXPR2
ELSE RETURN EXPR1
DECODE(AA，V1，R1，V2，R2....)函数
解释: IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL
举例: decode(id,1,'dept sale',2,'dept tech'
////////////////////////////////////////////////////////
四、从多个表里选取数据记录
1. 数据表间的连接
简单的连接语法:
SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
WHERE 表名1.字段名 = 表名2. 字段名 [ AND ……] ;
SELECT 字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
WHERE 表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;
有(+)号的字段位置自动补空值
连接的分类:
等于的连接 =
不等于的连接 != BETWEEN … AND … IN 注意IN和OR不能一起用
外连接 有一个字段名(+) , 没有满足的条件补空值
自连接 同一个表自己跟自己连接 例如找重复记录
2. 数据表间的连接例子
删除table_name表里字段名email重复的记录:
SQL>delete from table_name t1
where t1.rowid >
(select min(rowid) from table_name t2
where t1.email = t2.email
group by email
having count(email) > 1);
找到手机用户的服务区域:
SQL> select a.handphoneno,nvl(c.name,'null'),a.totalscore
from topscore a,chargeoperator cc,chargeoperatorinfo c
where substr(a.handphoneno,1,7)=cc.hpnohead(+)
and cc.chargetype=c.chargetype(+)
order by a.totalscore desc;
3. 数据表间的连接技巧
连接N个表, 需要N-1个连接操作
被连接的表最好建一个单字符的别名, 字段名前加上这个单字符的别名
BETWEEN .. AND.. 比用 >= AND <= 要好
连接操作的字段名上最好要有索引
连接操作的字段最好用整数数字类型
有外连接时, 不能用OR或IN的比较操作
4. 如何分析和执行SQL语句
写多表连接SQL语句时要知道它的分析执行计划的情况.
Sys用户下运行@/ORACLE_HOME/sqlplus/admin/plustrce.sql
产生plustrace角色
Sys用户下把此角色赋予一般用户 SQL> grant plustrace to &username;
一般用户下运行@/ORACLE_HOME/rdbms/admin/utlxplan.sql
产生plan_table
SQL> set time on; 说明：打开时间显示
SQL> set autotrace on; 说明：打开自动分析统计，并显示SQL语句的运行结果
SQL> set autotrace traceonly; 说明：打开自动分析统计，不显示SQL语句的运行结果
接下来你就运行测试SQL语句，看到其分析统计结果了。
一般来讲，我们的SQL语句应该避免大表的全表扫描。
SQL> set autotrace off; 说明：关闭自动分析统计 |
五、集合函数 经常和group by一起使用
1. 集合函数列表
AVG (DISTINCT | ALL | N) 取平均值
COUNT (DISTINCT | ALL | N | expr | * ) 统计数量
MAX (DISTINCT | ALL | N) 取最大值
MIN (DISTINCT | ALL | N) 取最小值
SUM (DISTINCT | ALL | N) 取合计值
STDDEV (DISTINCT | ALL | N) 取偏差值,如果组里选择的内容都相同,结果为0
VARIANCE (DISTINCT | ALL | N) 取平方偏差值
2. 使用集合函数的语法
SELECT column, group_function FROM table
WHERE condition GROUP BY group_by_expression
HAVING group_condition ORDER BY column;
3. 使用count时的注意事项
SELECT COUNT(*) FROM table;
SELECT COUNT(常量) FROM table;
都是统计表中记录数量,如果没有PK后者要好一些
SELECT COUNT(all 字段名) FROM table;
SELECT COUNT(字段名) FROM table;
不会统计为NULL的字段的数量
SUM,AVG时都会忽略为NULL的字段
4. 用group by时的限制条件
SELECT字段名不能随意, 要包含在GROUP BY的字段里
GROUP BY后ORDER BY时不能用位置符号和别名
限制GROUP BY的显示结果, 用HAVING条件
5. 例子
SQL> select title,sum(salary) payroll from s_emp
where title like 'VP%' group by title
having sum(salary)>5000 order by sum(salary) desc;
找出某表里字段重复的记录数, 并显示
SQL> select (duplicate field names) from table_name
group by (list out fields) having count(*)>1;
6. 判断题(T/F)
(1) Group functions include nulls in calculations [F]
(2) Using the having clause to exclude rows from a group calculation [F]
解释:
Group function 都是忽略NULL值的 如果您要计算NULL值, 用NVL函数
Where语句在Group By前把结果集排除在外Having语句在Group By后把结果集排除在外
7. 在SQL*PLUS里可使用的其它命令:
Ctrl^C 终止正在运行的SQL语句
remark /*...*/ -- 注释符号
HOST 可执行的操作系统下的命令 有些unix可以用 !
BREAK ON column_name SKIP n [ ON column_name SKIP n ]
按字段的名称column_name分隔显示,更清晰,SKIP n 是在分隔处空行的数量n
BREAK ON ROW SKIP n 每一行间隔都放n个空行
COMPUTE 集合运算符 OF 字段1 ON 字段2 按字段2对字段1进行集合运算
COMPUTE后面可以跟的集合运算符:
SUM MINIMUM MAXIMUM AVG STD VARIANCE COUNT NUMBER
8.在SQL*PLUS里可使用的其它命令举例:(scott用户)
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SAL ON REPORT 在全部结果集后面算合计
select ename,sal from emp where job='SALESMAN';
COMPUTE AVG LABEL avg OF SAL ON REPORT 在全部结果集后面算平均值
/ 再次执行上次的sql语句
break on DEPTNO skip 2 on JOB skip 1 在BREAK字段结果集后面算合计
COMPUTE SUM OF SAL ON DEPTNO
SELECT DEPTNO,JOB,ENAME,SAL FROM EMP ORDER BY DEPTNO,JOB;
SQL> CLEAR BREAKS; 清除设置的BREAK条件
SQL> CLEAR COMPUTES; 清除设置的COMPUTE条件
六、子查询
1. 查询语句可以嵌套
例如: SELECT …… FROM (SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;
2. 何处可用子查询?
当查询条件是不确定的条件时
DML(insert, update,delete)语句里也可用子查询
HAVING里也可用子查询
3. 两个查询语句的结果可以做集合操作
例如:
并集UNION(去掉重复记录)
并集UNION ALL(不去掉重复记录)
差集MINUS,
交集INTERSECT
4. 子查询的注意事项
先执行括号里面的SQL语句，一层层到外面
内部查询只执行一次
如果里层的结果集返回多个，不能用= > < >= <=等比较符要用IN.
5. 子查询的例子(1)
SQL> select title,avg(salary) from s_emp
group by title Having avg(salary) =
(select min(avg(salary)) from s_emp
group by title);
找到最低平均工资的职位名称和工资
5. 子查询的例子(2)
子查询可以用父查询里的表名
这条SQL语句是对的:
SQL>select cty_name from city where st_code in
(select st_code from state where st_name='TENNESSEE' and
city.cnt_code=state.cnt_code);
说明：父查询调用子查询只执行一次．
6.取出结果集的80 到100的SQL语句
ORACLE处理每个结果集只有一个ROWNUM字段标明它的逻辑位置,
并且只能 用ROWNUM<100, 不能用ROWNUM>80。
以下是经过分析后较好的两种ORACLE取得结果集80到100间的SQL语句( ID是唯一关键字的字段名 )：
语句写法：
SQL>select * from (
( select rownum as numrow, c.* from (
select [field_name,...] from table_name where 条件1 order by 条件2) c)
where numrow > 80 and numrow <= 100 )
order by 条件3;
七、在执行SQL语句时绑定变量
1. 接收和定义变量的SQL*PLUS命令
ACCEPT
DEFINE UNDEFINE
&
2. 绑定变量SQL语句的例子(1)
SQL> select id, last_name, salary from s_emp where dept_id = &department_number;
Enter value for department_number: 10
old 1: select id, last_name, salary from s_emp where dept_id=&department_number;
new 1: select id, last_name, salary from s_emp where dept_id= 10
SQL> SET VERIFY OFF | ON;可以关闭和打开提示确认信息old 1和new 1的显示.
3. 绑定变量SQL语句的例子(2)
SQL> select id, last_name, salary
from s_emp
where title = '&job_title';
Enter value for job_title: Stock Clerk
SQL> select id, last_name, salary
from s_emp
where hiredate >to_date( '&start_hire_date','YYYY-MM-DD');
Enter value for start_hire_date : 2001-01-01
把绑定字符串和日期类型变量时，变量外面要加单引号
也可绑定变量来查询不同的字段名
输入变量值的时候不要加;等其它符号
4. ACCEPT的语法和例子
SQL> ACCEPT variable [datatype] [FORMAT] [PROMPT text] [HIDE]
说明： variable 指变量名 datatype 指变量类型，如number,char等 format 指变量显示格式 prompt text 可自定义弹出提示符的内容text hide 隐藏用户的输入符号
使用ACCEPT的例子：
ACCEPT p_dname PROMPT 'Provide the department name: '
ACCEPT p_salary NUMBER PROMPT 'Salary amount: '
ACCEPT pswd CHAR PROMPT 'Password: ' HIDE
ACCEPT low_date date format 'YYYY-MM-DD' PROMPT“Enter the low date range('YYYY-MM-DD'):”
4. DEFINE的语法和例子
SQL> DEFINE variable = value
说明： variable 指变量名 value 指变量值
定义好了变良值后, 执行绑定变量的SQL语句时不再提示输入变量
使用DEFINE的例子：
SQL> DEFINE dname = sales
SQL> DEFINE dname
DEFINE dname = “sales” (CHAR)
SQL> select name from dept where lower(name)='&dname';
NAME
-------------------------
sales
sales
SQL> UNDEFINE dname
SQL> DEFINE dname
Symbol dname is UNDEFINED
5. SQL*PLUS里传递参数到保存好的*.sql文件里
SQL> @ /路径名/文件名 参数名1[,参数名2, ….]
SQL> start /路径名/文件名 参数名1[,参数名2, ….]
注意事项:
一次最多只能获取9个&变量, 变量名称只能是从&1,&2到&9
变量名后不要加特殊的结束符号
如果在SQL*PLUS里要把&符号保存在ORACLE数据库里,要修改sql*plus环境变量define
SQL> set define off;
八、概述数据模型和数据库设计
1. 系统开发的阶段:
Strategy and Analysis
Design
Build and Document
Transition
Production
2. 数据模型
Model of system in client's mind
Entity model of client's model
Table model of entity model
Tables on disk
3. 实体关系模型 (ERM)概念
ERM ( entity relationship modeling)
实体 存有特定信息的目标和事件 例如: 客户,订单等
属性 描述实体的属性 例如: 姓名,电话号码等
关系 两个实体间的关系 例如:订单和产品等
实体关系模型图表里的约定
Dashed line (虚线) 可选参数 “may be”
Solid line (实线) 必选参数 “must be”
Crow's foot (多线) 程度参数 “one or more”
Single line (单线) 程度参数 “one and only one”
4. 实体关系模型例子
每个订单都必须有一个或几个客户
每个客户可能是一个或几个订单的申请者
5. 实体关系的类型
1:1 一对一 例如: 的士和司机
M:1 多对一 例如: 乘客和飞机
1:M 一对多 例如: 员工和技能
6. 校正实体关系的原则
属性是单一值的, 不会有重复
属性必须依存于实体, 要有唯一标记
没有非唯一属性依赖于另一个非唯一的属性
7. 定义结构时的注意事项
减少数据冗余
减少完整性约束产生的问题
确认省略的实体,关系和属性
8. 完整性约束的要求
Primary key 主关键字 唯一非NULL
Foreign key 外键 依赖于另一个Primary key,可能为NULL
Column 字段名 符合定义的类型和长度
Constraint 约束条件 用户自定义的约束条件,要符合工作流要求
例如: 一个销售人员的提成不能超过它的基本工资
Candidate key 候选主关键字 多个字段名可组成候选主关键字, 其组合是唯一和非NULL的
9. 把实体关系图映射到关系数据库对象的方法
把简单实体映射到数据库里的表
把属性映射到数据库里的表的字段, 标明类型和注释
把唯一标记映射到数据库里的唯一关键字
把实体间的关系映射到数据库里的外键
其它的考虑:
设计索引,使查询更快
建立视图,使信息有不同的呈现面, 减少复杂的SQL语句
计划存储空间的分配
重新定义完整性约束条件
10. 实体关系图里符号的含义
PK 唯一关键字的字段
FK 外键的字段
FK1,FK2 同一个表的两个不同的外键
FK1,FK1 两个字段共同组成一个外键
NN 非null字段
U 唯一字段
U1,U1 两个字段共同组成一个唯一字段
