SQL笔记.docx
《SQL笔记.docx》由会员分享，可在线阅读，更多相关《SQL笔记.docx（15页珍藏版）》请在冰豆网上搜索。
SQL笔记
SQL
基本，oracle数据库中的sql，但基本上都是标准sql语句
标准SQL，允许个个数据库拥有自己的语句
普通话方言
DDL数据定义语句创建表，表操作
DML数据操作语句insertupdatedelete
Select语句
增删改查
desc表明
sql语句中，不区分大小写
select*from表明
select列名,[列名…..]from表明
列名as别名
as可以不写
别名默认全部转成大写,要不转必须加””
别名，可以不加””，别名中有特殊字符，则必须加””
Distinct
去掉重复行，对某个字段而言
运算符
算术运算符
+，-，*，/
无效数字
（插入空值用null）,
注意！
！
！
！
！
空值就是没有值，就是没有数据，空值!
=默认值
如果算术表达式中遇到空值？
则整个表达式的运算结果就是空值
比较运算符
=><>=<=!
=（<>）
并置运算符
||（连接合并字符串）
空值时，会把空值作为空字符串处理””
逻辑运算符
andornot
其他的
betweenand
in
where子句
条件查询
select列名列表from表明where条件
字符串要注意用’’引起来
日期也要用’’
条件字符串大小写敏感
匹配查询
like
不定长通配符：
任意长度（可以0）%%内容%
定长通配符:
指定长度_精确匹配
转义字符\
空值查询
isnull来查询空值
isnotnull非空值
排序
orderby
select列名列表from表明orderby列名asc[desc]
默认升序asc
每个字段都可以排序，排序时可以使用字段别名
函数
单行函数
多行函数
Char函数
Upper
Lower
Initcap
ltrim
rtrim
selectname||name,concat（name,name）fromteacher;//将后面的name连接到前面的name后面。
selectname,substr（name,1,3）fromteacher;//输出name第一到第三个字符串。
selectname,instr（name,'a'）fromteacher;
selectname,lpad（name,10,'$'）,rpad（name,10,'$'）fromteacher;
selectname,length（name）fromteacher;
数值函数
日期函数
selectsysdate,ADD_MONTHS（sysdate,4）fromteacher;
数据类型
数字Number
字符串CHARVARCHAR2
日期DATETIMESTAMP
GREATEST（列1,列2,列3）
selectsysdate,ADD_MONTHS（sysdate,4）,GREATEST（sysdate,ADD_MONTHS（sysdate,4））fromdual;
LEAST
SELECTSYSDATE,
LAST_DAY（SYSDATE）as"Last",//返回该月的最后一天。
LAST_DAY（SYSDATE）-SYSDATE"DaysLeft"
FROMDUAL;
SELECTSYSDATE,'03-7月-10',MONTHS_BETWEEN（SYSDATE,'03-9月-10'）
FROMDUAL;//Months_between（f,s）日期f和s间相差月数
SELECTNEXT_DAY（sysdate,'星期日'）"NEXTDAY"FROMDUAL;//从sysdate之后的下一个星期日。
数据类型转换
隐式转换
显示转换
SELECTSYSDATE,to_char（sysdate,'YYYY-MM-DDWW'）
FROMDUAL;
SELECTSYSDATE,to_date（'1999/09/07','YYYY/MM/DD'）
FROMDUAL;
selectto_number（'100'）+20fromdual
NVL函数
空值替代函数
selectid,id+1000,nvl（id,0）+1000fromteacher;
多表查询
droptablestudent;
droptableclassroom;
createtableclassroom（
idnumber,
namevarchar2（20）
）;
createtablestudent（
idnumber,
namevarchar（20）,
agenumber,
cidnumber
）;
Select表名.列名,………from表1,……where表1.列名join_operator表2.列名
join_operator
等值连接equljoin
非等值连接nonequljoin
自然连接natualjoin
交叉连接crossjoin
外部连接outerjoin
自连接selfjoin
等值连接
selectstudent.id,student.name,student.cid,classroom.name
fromclassroom,student
whereclassroom.id=student.cid
selectstudent.id,student.name,student.cid,classroom.name
fromclassroomJOINstudentONclassroom.id=student.cid//效果同上面语句。
selectb.id,b.name,b.cid,a.name
fromclassrooma,studentb
wherea.id=b.cid
selectb.*,a.name
fromclassrooma,studentb
wherea.id=b.cid
非等值连接
常用betweenand进行一个区间查询
createtableagelevel（
idnumber,
lnamevarchar2（20）,
lminnumber,
lmaxnumber
）;
selecta.*,b.lname
fromstudenta,agelevelb
wherea.agebetweenb.lminandb.lmax;
自然连接
select列列表from表1naturaljoin表2using（列名）
2张表中的列名进行连接，列名相同，并且列的属性也必须相同
using处理2个表中列名，及属性都相同的列有多个的情况
交叉连接
笛卡尔
外联接outerjoin
左外联接
select*fromstudent,classroomwherestudent.cid=classroom.id（+）;
select*from
studentleftouterjoinclassroomonstudent.cid=classroom.id
右外联接
select*fromstudent,classroomwherestudent.cid（+）=classroom.id;
select*from
studentrightouterjoinclassroomonstudent.cid=classroom.id
全联接
select*from
studentfullouterjoinclassroomonstudent.cid=classroom.id
自联接
自己联接自己表
多行函数
count（*）
count（列名），如果此列中数据为空，则，不在统计范围之内,重复不计。
selectcount（*）,count（id）,count（cid）fromstudent;
SUM求和
selectsum（age）,avg（age）,max（age）,min（age）fromstudent;
STDDEV标准差
Variance方差
GroupBy
Select列列表from表名where条件groupby分组要求having查询条件
selectcount（*）fromstudentgroupbycid;
必须是多行函数，或者此列必须出现在分组要求中
ORA-00979:
不是GROUPBY表达式
selectcid,count（*）
fromstudent
whereage>25
groupbycid
havingcount（*）>3;
where只有满足where条件的数据才参与统计，对原始数据的筛选
having对统计结果的筛选
先根据where处的条件进行筛选，然后按groupby子句所规定的列进行分组，然后再应用having子句对分组后的结果进行再一次的筛选
子查询
条件也是未知的，需要查询才能知道
selectagefromstudentwherename='hhhh';
select*fromstudentwhereage=26;
select*fromstudentwhereage=（selectagefromstudentwherename='hhhh'）;
先执行where里面的sql，先执行子查询，再执行外层查询
ORA-01427:
单行子查询返回多个行
ORA-00913:
值过多
子查询，返回的数据是一行一列------单行子查询
子查询要用（）括起来
selectcid,avg（age）fromstudentgroupbycidhaving?
;
selectavg（age）fromstudent;
selectcid,avg（age）fromstudentgroupbycidhavingavg（age）>（selectavg（age）fromstudent）;
多行子查询
子查询查询出来的结果是一列多行的
多行运算符in、any、all、notin
In等于列表中的任意元素
select*fromstudentwhereagein（selectagefromstudentwherename='张三'）;
notin不在列表里面，不等于列表中的任意元素
any
any,=any
=any等于列表中的任意元素和in效果一样
>any大于里面的最小值
All
all
>all大于里面最大值
表的创建与管理
数据库中的对象
表：
存储数据
视图：
用来显示一张或多张表中的全部或部分数据，视图并不真正的存储数据
序列：
帮助生成主键
索引：
提高查询速度
同义词：
对象别名
表:
Createtable表名
（
列名数据类型[null|notnull],
列名数据类型[null|notnull],
……..
列名数据类型[null|notnull]
）
数据字典
作用
列名
数据类型
数据长度
是否可以为null
主键
id
number
否
姓名
name
varchar2
否
班级外键id
cid
number
添加列
Altertable表名add列名数据类型;
添加到表的最后，不能指定顺序
添加一列notnull
添加没有notnull的列
为所有行增加添加列的数据
把此列改为notnull
修改列
Altertable表名modify列名数据类型
增加字符串的宽度
增加数字的宽度
删除列
Altertable表名dropcolumn列名
重命名
Rename原表名to新表名
删除表
Droptable表名
DML和数据完整性
Insert语句
Insertinto表名（列名,……）values（值,…..）;
如果插入的数据的个数和表中的列及数量一样，则列名列表可以不写
insertintostudent（name,id）values（'100',80）;
Update语句
Updatetableset列=值,[列=值]where条件
updatestudentsetname='其二千万',age=200whereid=100;
delete语句
delete表名where条件
数据完整性
实体完整性：
每一行数据都要有唯一标识主键
域完整性：
数据必须在合法范围之内
引用完整性：
（外键），保证这个表中的列的值与另一个表中的列的值匹配
用户自定义完整性：
用户自己建立的规则
约束：
实施完整性
主键约束
外键约束
唯一键约束
检查约束
Notnull（非空）约束
主键约束：
Primarykey主键约束表中每条记录的唯一性
只有主键才能代表记录中的某条记录
每个表都要有主键
主键必填且不能重复
createtablestudent1
（
idnumberprimarykey,
namevarchar2（20）
）
createtablestudent2
（
idnumber,
namevarchar2（20）,
constraintpk_studentprimarykey（id）
）
指明那一列是主键，并且主键约束要有名字（约束名）
pk_开头
外键约束
Foreignkey把此表中的列与另一个表中的primarykey列关联起来
关联的必须是主键！
！
！
！
createtableclassroom2（
idnumberprimarykey,
namevarchar2（20）
）
createtablestudent2（
idnumber,
namevarchar2（20）,
cidnumber,
constraintpk_studentprimarykey（id）,
constraintfk_student_classroomforeignkey（cid）referencesclassroom2（id）
）
唯一键
Uniquekey一列数据是不能重复
允许null
createtablestudent3（
idnumber,
namevarchar2（20）,
cidnumber,
constraintuk_nameunique（name）
）
Notnull约束
namevarchar2（20）notnull
namevarchar2（20）constraintnn_namenotnull
检查约束
Check
agenumberconstraintck_agecheck（agebetween0and200）
事务
几个操作，要么全做，要么全部做。
业务逻辑
原子性，一致性，隔离性，持久性。
隐式事务
autocommit
commit才能真正的把数据写到数据库中。
显式事务
事务回滚Rollback
锁
行锁，自动
上锁
解锁，事务结束
隐式锁，显式锁
视图
视图是从一个表或多个表中导出的行或列的数据的集合。
视图可以当表来使用，但视图并不是真正的存储数据，可以修改视图中的数据，但还是修改了表中的数据。
简单视图，支持DML操作，不能包含组合函数。
复杂视图，多个表中查出的数据，可以包含组合函数，不支持DML操作。
createviewv_stuasselectid,namefromstudent;
select*fromv_stu;
withreadonly
withcheckoption
创建视图时不能使用orderby
Rename原视图名to新视图名
Dropview视图名
TOP-N分析
伪列：
不是表中真实存在的列
Rownum：
符合查询条件的第一条记录，rownum=1
先查出记录，准备分配给他rownum，检查条件（where），如果不满足条件，rownum不会真正分配给他，如果满足条件，则，rownum正在分配给此记录
查询年龄最大的5个人
select*fromstudentwhererownum<=5orderbyagedesc;错误的
先拿出记录，在排序
先排序在拿记录
select*from（select*fromstudentorderbyagedesc）whererownum<=5;
子查询或临时视图
Rownum只能是<或<=
查询年龄排名5-10
selectrownum,a.*from（select*fromstudentorderbyagedesc）a;
先排序
select*fromstudentorderbyagedesc
再把排完序的结果加上rownum
selectrownumr,a.*from（select*fromstudentorderbyagedesc）a
然后再在此结果中进行刷选，但不是使用rownum，而是使用原先查询结果中的rownum
select*from（selectrownumr,a.*from（select*fromstudentorderbyagedesc）a）wherer<=10andr>5;
序列
Oracle的特点
解决主键自动增长问题
Sequence
Create
createsequenceseq_stu6
incrementby1
startwith1
取序列的值
Dual系统表一行一列的
伪列nextvalcurrval
selectseq_stu6.nextvalfromdual;
取序列的下一个值
selectseq_stu6.currvalfromdual;
取序列的当前值
insertintostudent6values（seq_stu6.nextval,'三大'）;
升级会员 