第三章SQL查询与函数.docx
《第三章SQL查询与函数.docx》由会员分享，可在线阅读，更多相关《第三章SQL查询与函数.docx（25页珍藏版）》请在冰豆网上搜索。
第三章SQL查询与函数
SQL查询与函数
一、SQL概述
SQL（StructuredQueryLanguage）结构化查询语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
同时也是数据库脚本文件的扩展名。
ØSQL语言主要包含5个部分
✓数据定义语言DataDefinitionLanguage（DDL），用来建立数据库、数据对象和定义其列。
例如：
CREATE、DROP、ALTER等语句。
✓数据操作语言DataManipulationLanguage（DML），用来插入、修改、删除、查询，可以修改数据库中的数据。
例如：
INSERT（插入）、UPDATE（修改）、DELETE（删除）语句
✓数据查询语言（DataQueryLanguage,DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。
例如：
SELECT（查询）
✓数据控制语言DataControllingLanguage（DCL），用来控制数据库组件的存取允许、存取权限等。
例如：
GRANT、REVOKE、COMMIT、ROLLBACK等语句。
✓事务控制语言（TransactionalControlLanguage，TCL），用于维护数据的一致性，包括COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）3条语句
二、Oracle的数据类型
类型
参数
描述
字符类型
Char
1~2000字节
固定长度字符串，长度不够的用空格补充
varchar2
1~4000字节
可变长度字符串，与CHAR类型相比，使用VARCHAR2可以节省磁盘空间，但查询效率没有char类型高
数值类型
Number（m,n）
m（1~38）
n（-84~127）
可以存储正数、负数、零、定点数和精度为38位的浮点数，其中，M表示精度，代表数字的总位数；N表示小数点右边数字的位数
日期类型
Date
7字节
用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日，7个字节分别表示世纪、年、月、日、时、分和秒
二进制数据类型
Raw
1~2000字节
可变长二进制数据，在具体定义字段的时候必须指明最大长度n
longraw
1~2GB
可变长二进制数据
LOB数据类型
Clob
1~4GB
只能存储字符数据
Nclob
1~4GB
保存本地语言字符集数据
Blob
1~4GB
以二进制信息保存数据
三、DDL语言
1.Createtable命令
用于创建表。
在创建表时，经常会创建该表的主键、外键、唯一约束、Check约束等
Ø语法结构
createtable表名（
[字段名][类型][约束]
………..
PRIMARYKEY（column1,column2）,
FOREIGNKEY（column1,column2,…..column_n）,
REFERENCEStablename（column1,column2,…..column_n）
）
Ø例子：
createtablestudent（
stuNochar（32）primarykey,--主键约束
stuNamevarchar2（20）notnull,--非空约束
cardIdchar（20）unique,--唯一约束
sexchar
（2）check（sex='男'orsex='女'）,--检查约束
addressvarchar2（100）default'地址不详'--默认约束
）;
createtablemark（
midintprimarykey,--主键约束
stuNochar（32）notnull,
courseNamevarchar2（20）notnull,--非空约束
scorenumber（3）notnullcheck（score>=0andscore<=100）,
--非空约束,检查约束
foreignkey（stuno）referencesstudent（stuNo）
--表级外键约束
）;
2.Altertable命令
对已经存在的表进行修改，可以新增或删除字段，修改字段名或其类型和类型长度。
Ø修改表名
altertableold_tablerenametonew_table
Ø修改字段名
altertabletable_namerenamecolumnold_columntonew_column
Ø添加字段
altertabletable_nameadd（new_columnvarhcar2（200）default‘null’,new_column2number）
Ø修改数据类型
altertabletable_namemodifiy（filednamevarchar2（100））
Ø删除字段
altertabletable_namedropcolumncolumn_name
Ø添加主键
altertabletable_nameaddconstraintpk_nameprimarykey（column_id）
Ø删除主键
altertabletable_namedropconstraintpk_name
Ø添加外键,检查,唯一约束
altertabletable_nameaddconstraintfk_nameforeignkey（column_id）references主表（主键）
添加检查约束
Altertablestuaddconstraintck_snamecheck（length（sname）>=2）
添加唯一约束
altertablestuaddconstraintuq_snameunique（sname）
Ø删除外键
altertabletable_namedropconstraintfk_name
3.Droptable命令
用于从数据库中删除表及全部数据
droptabletable_name[purge];--purge:
清除缓存
4.Truncatetable命令
可以快速删除表的记录并释放空间，不使用事务处理，速度快且效率高，但无法回滚事务。
truncatetabletable_name
5.其他create命令
ØCREATEINDEX：
创建数据表索引
ØCREATEPROCEDURE：
创建存储过程
ØCREATEFUNCTION：
创建用户函数
ØCREATEVIEW：
创建视图
ØCREATETRIGGER：
创建触发程序
ØCREATESEQUENCE
6.SEQUENCE
在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。
Ø创建语法
CREATESEQUENCEsequence_marks
INCREMENTBY1--每次加几个
STARTWITH1--从1开始计数
NOMAXVALUE--不设置最大值
NOCYCLE--一直累加，不循环
CACHE10--使序列号预分配,默认NOCACHE
Ø列子
SELECTsequence_marks.currvalfromdual
insertintotable_namevalues（sequence_marks.nextval）
Ø删除
dropsequencesequence_marks
四、DML语言
1.INSERT语句
Insertintotable_name（column1,column2…….column_n）values（val1….）
--插入多条
insertinto表名（列名.....）select列名....from源表名
insertintostu
select1,'abc'fromdualunion
select2,'abcd'fromdual;
2.UPDATE语句
updatetable_namesetcolumn1=value,….where[condition…]
3.DELETE语句
Deletefromtable_namewhere[condition…]
五、运算符
1.算术运算符[+,-,*,/,mod（10,3）]
2.关系运算符[>,>=,<,<=,!
=,<>,=]BETWEEN…AND、IN、LIKE和ISNULL
3.逻辑运算符andornot
4.集合操作符union,unionall,minus,INTERSECT
✓union:
取出2个表数据合并，去除重复记录
✓unionall:
取出2个表数据合并，保留重复记录
✓minus:
取出2个表中不同数据
✓Intersect:
取出2个表中共有数据【交集】
5.连接操作符||
六、DQL语言
1.概述
Ø基本语法
SELECTcolumn_list[*查询所有数据]
[INTOnew_table]
FROMtable_source
[WHEREsearch_condition]
[GROUPBYgroup_by_expression]
[HAVINGsearch_condition]
[ORDERBYorder_expression[ASC|DESC]]
Ø例子
SQL>select*fromem--查询所有数据
SQL>selectename,jobfromem--查询指定的字段数据
SQL>select*fromempwheresal>1000--加条件
2.聚合函数
聚合函数对一组值执行计算并返回单一的值。
聚合函数忽略空值。
聚合函数经常与SELECT语句的GROUPBY子句一同使用。
不能在WHERE子句中使用组函数。
ØAVG（expression）:
返回集合中各值的平均值
--查询所有人都的平均工资
selectavg（sal）fromemp
ØCOUNT（expression）:
以Int32形式返回集合中的项数
--查询工资低于2000的人数
selectcount（*）fromempwheresal<2000
ØMAX（expression）:
返回集合中的最大值
--查询最高工资
selectmax（sal）fromemp
ØMIN（expression）:
返回集合中的最小值
--查询最低工资
selectmax（sal）fromemp
ØSUM（expression）:
返回集合中所有值的总和
--查询部门编号为20的工资总和
selectsum（sal）fromempwheredeptno=20
3.排序函数
用于根据指定的列对结果集进行排序
ØORDERBY语句
--查询所有信息并按工资排序
select*fromemporderbysal
Ø升序（asc）
--查询所有信息并按工资升序排序
select*fromemporderbysalasc
Ø降序（desc）
--查询所有信息并按工资降序排序
select*fromemporderbysaldesc
ØROWNUM与ORDERBY【ROWNUM：
伪列，只能使用<,<=,!
=】
--查询工资最高的5个人信息
selecte.*,rownum编号from（select*fromemporderbysaldesc）ewhererownum<=5
【或rownum!
=6】
4.分组函数
分组函数作用于一组数据，并对一组数据返回一个值。
ØGROUPBY子句
--查询每个部门的平均工资
selectavg（sal）fromempgroupbydeptno
ØHAVING子句
--查询部门的平均工资大于2000
selectavg（sal）fromempgroupbydeptnohavingavg（sal）>2000
5.连接查询
连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。
通过连接运算符可以实现多个表查询。
Ø内连接
内连接也叫连接，是最早的一种连接。
还可以被称为普通连接或者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。
✓等值连接:
select*fromempinnerjoindeptonemp.deptno=dept.deptno
select*fromemp,deptwhereemp.deptno=dept.deptno
✓不等值连接:
select*fromempinnerjoindeptonemp.deptno!
=dept.deptno
Ø外连接
外连接分为三种：
左外连接，右外连接，全外连接。
对应SQL：
LEFT/RIGHT/FULLOUTERJOIN。
通常我们省略outer这个关键字。
写成：
LEFT/RIGHT/FULLJOIN。
✓左外连接（leftjoin）:
是以左表的记录为基础的
select*fromempleftjoindeptonemp.deptno=dept.deptno
select*fromemp,deptwhereemp.deptno=dept.deptno（+）
✓右外连接（rightjoin）:
和leftjoin的结果刚好相反,是以右表（BL）为基础的
select*fromemprightjoindeptonemp.deptno=dept.deptno
select*fromemp,deptwhereemp.deptno（+）=dept.deptno
✓全外连接（fulljoin）:
左表和右表都不做限制，所有的记录都显示，两表不足的地方用null填充
select*fromempfulljoindeptonemp.deptno=dept.deptno
Ø交叉连接
交叉连接即笛卡儿乘积，是指两个关系中所有元组的任意组合。
一般情况下，交叉查询是没有实际意义的。
select*fromcrossfulljoindept
6.常用查询
✓like模糊查询【Oracle通配符只支持%与_】
--查询姓名首字母为S开始的员工信息
select*fromempwhereenamelike'S%'
--查询姓名第三个字母为A的员工信息
select*fromempwhereenamelike'__A%'
✓isnull/isnotnull查询
--查询没有奖金的雇员信息
select*fromempwherecommisnull
--查询有奖金的雇员信息
select*fromempwherecommisnotnull
✓in查询
--查询雇员编号为7566、7499、7844的雇员信息
select*fromempwhereempnoin（7566,7499,7844）
✓exists/notexists查询（效率高于in）
--查询有上级领导的雇员信息【查询原理:
一条一条读写记录】
select*fromempewhereexists
（select*fromempwhereempno=e.mgr）
--查询没有上级领导的雇员信息
select*fromempewherenotexists
（select*fromempwhereempno=e.mgr）
✓all查询
--查询比部门编号为20的所有雇员工资都高的雇员信息
select*fromempwheresal>all（selectsalfromempwheredeptno=20）
✓union合并不重复
select*fromempwherecommisnotnull
union
select*fromempwheresal>3000
✓unionall合并重复
select*fromempwherecommisnotnull
unionall
select*fromempwheresal>3000
✓intersect和minus
minus:
取出2个表中不同数据
createtableemp2asselect*fromempwhererownum<=5
--取出emp中与emp2不同数据
select*fromempminusselect*fromemp2
Intersect:
取出2个表中共有数据【交集】
select*fromempintersectselect*fromemp2
7.子查询
当一个查询是另一个查询的条件时，称之为子查询。
子查询是一个SELECT语句，它嵌套在一个SELECT、SELECT...INTO语句、INSERT...INTO语句、DELETE语句、或UPDATE语句或嵌套在另一子查询中。
✓在CREATETABLE语句中使用子查询
--创建表并拷贝数据
createtabletemp（id,name,sal）asselectempno,ename,salfromemp
✓在INSERT语句中使用子查询
--当前表拷贝
insertintotemp（id,name,sal）select*fromtemp
--从其他表指定字段拷贝
insertintotemp（id,name,sal）selectempno,ename,salfromemp
✓在DELETE语句中使用子查询
--删除SALES部门中的所有雇员
deletefromempwheredeptnoin
（selectdeptnofromdeptwheredname='SALES'）
✓在UPDATE语句中使用子查询
--修改scott用户的工资和smith的工资一致
updateempsetsal=（selectsalfromempwhereename='SMITH'）whereename='SCOTT'
--修改black用户的工作,工资,奖金和scott一致
updateempset（job,sal,comm）=（selectjob,sal,commfromempwhereename='SCOTT'）whereename='BLAKE'
✓在SELECT语句中使用子查询
--查询和ALLEN同一部门的员工信息
select*fromempwheredeptnoin
（selectdeptnofromempwhereename='ALLEN'）
--查询工资大于部门平均工资的雇员信息
select*fromempe
（selectavg（sal）sal,deptnofromempgroupbydeptno）t
wheree.deptno=t.deptnoande.sal>t.asal
七、TCL语言
1.COMMIT
commit--提交事务
2.ROLLBACK
rollbacktop1--回滚到指定的保存点
rollback--回滚所有的保存点
3.SAVEPOINT
savepointp1--设置保存点
4.只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其它dml操作的事务，它的作用是确保用户只能取得某时间点的数据。
settransactionreadonly
八、oracle函数
1.字符串函数
字符串函数是oracle中比较常用的，下面我们就介绍些常用的字符串函数:
✓concat：
字符串连接函数，也可以使用’||’
--将职位和雇员名称显示在一列中
Selectconcat（'Hello','world'）fromdual;--->Helloworld
selectename||'（'||job||'）'fromemp
✓length：
返回字符串的长度
--查询雇员名字长度为5个字符的信息
select*fromempwherelength（ename）=5
✓lower：
将字符串转换成小写
--以小写方式显示雇员名
selectlower（ename）fromemp
✓upper：
将字符串转换成大写
--以大写方式显示雇员名
selectupper（ename）fromemp
✓substr：
截取字符串
--只显示雇员名的前3个字母
selectsubstr（ename,0,3）fromemp
✓replace：
替换字符串
--将雇员的金额显示为*号
selectename,replace（sal,sal,’*’）fromemp;
✓instr：
查找字符串
--查找雇员名含有’LA’字符的信息
select*fromempwhereinstr（ename,’LA’）>0
selectinstr（'abcabc','c',-1）fromdual;-->6【负数从后开始】
2.日期函数
✓sysdate：
返回当前session所在时区的默认时间
--获取当前系统时间
selectsysdatefromdual
✓add_months：
返回指定日期月份+n之后的值，n可以为任何整数
--查询当前系统月份+2的时间
selectadd_months（sysdate,2）fromdual
--查询当前系统月份-2的时间
selectadd_months（sysdate,-2）fromdual
✓last_day：
返回指定时间所在月的最后一天
--获取当前系统月份的最后一天
selectlast_day（sysdate）fromdual
✓months_between：
返回月份
升级会员 