SQL学习心得.docx
《SQL学习心得.docx》由会员分享，可在线阅读，更多相关《SQL学习心得.docx（47页珍藏版）》请在冰豆网上搜索。
SQL学习心得
SQL（StructuredQueryLanguage）
---结构化查询语言
SQL是在关系数据库中执行数据操作，检索，维护所使用的标准语言，可以用来查询数据，操作数据，定义数据，控制数据
执行SQL语句时用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。
------数据库对象通常包含表，视图，索引，序列
【数据定义语言DDL】-->表结构
DataDefinitionLanguage，用于建立，修改，删除数据库对象，不需要事务的参与，自动提交。
——CREATE：
创建表或其他对象的结构
CREATETABLEtable_name（
column_namedatatype[DEFAULTexpr],
………………………………………………
）；
Eg：
--创建表emp
CREATETABLEemp（
idNUMBER（10）,
nameVARCHAR2（20）,
genderCHAR
（1）,
birthDATE,
salaryNUMBER（6,2）,
jobVARCHAR2（30）,
deptidNUMBER
（2）
）;
——ALTER：
修改表或其他对象的结构
修改表名：
RENAMEold_nameTOnew_name（新表名不能使数据库中已有的表）
增加列：
ALTERTABLEtable_nameADD
（column_namedatatype[DEFAULT],
……………………………）；（新增列只能在表的最后一列追加）
删除列：
ALTERTABLEtable_nameDROP
（column_name）；（删除不需要的列）
删除字段需要从每行中删掉该字段占据的长度和数据，并释放在数据块中
占据的空间，如果表记录比较大，删除字段可能需要比较长的时间。
修改列：
ALTERTABLEtable_nameMODIFY
（column_namedatatype[DEFAULT],
……………………………）；（修改仅对以后插入的数据有效，修改字段前的所有数据不受影响）
修改时一般类型不改，改长度，尽量往长里改，因为如果表中已经有数据的
情况下，把长度由大改小，有可能不成功
——DROP:
删除表或其他对象的结构
DROPTABLEtable_name
——TRUNCATE：
删除表数据，保留表结构
TRUNCATETABLEtable_name
可以通过DESCtable_name查看表结构
【数据操作语言DML】-->表结构中的数据
DataManipulationLanguage，用于改变数据表中的数据，和事务是相关，执行完DML操作后必须经过事务控制语句提交后才真正的将改变应用到数据库中
——INSRET：
将数据插入到数据表中
INSERTINTOtable_name[（column[,column…]）]
VALUES（value[,value…]）;（每执行一次增加一条记录）
指定向哪些列插入对应的值，没有指定的列：
若
设有默认值（DEFAULT），那么插入的就是该默
认值，否则插入null,若某列设为notnull，执
行INSERT语句时又没指定该列，那么插入会抛
出违反不为空的约束条件，若不写指定的列，默
认所有列插入，每一列的值VALUE都不能少
——UPDATE:
更新数据表中已存在的数据
UPDATEtable_name
SETcolumn=value[,column=value]……
[WHEREcondition];--若不写where子句，全表所有行的column都被更新
——DElETE：
删除表中的数据
DELETE[FROM]table_name
[WHEREcondition];--若不写where子句，全表所有行数据都被删除
另注意与DDL中的TRUNCATE的区别：
*二者都是删除表记录，DELETE可以有条件的删（WHERE），TRUNCATE是将表数据全部删除
*DELETE是DML，可以回退（ROLLBACK），TRUNCATE是DDL，立即生效，无法回退
*如果删除的是全部表记录，且数据量较大，TRUNCATE速度更快
【事务控制语言TCL】
TransactionControlLanguage，用来维护数据的一致性
——COMMIT：
提交，确认已经进行的数据改变
——ROLLBACK：
回滚，取消已经进行的数据改变
——SAVEPOINT：
保存点，使当前事务可以回退到指定的保存点，便于取消部分
改变
Eg：
DDL范畴，控制表结构不需要TCL参与，自动提交
DML范畴，控制表结构中的数据经TCL确认后，才会真正生效，
否则是“假象”
【数据控制语言DCL】
DataControlLanguage，用于执行权限的授予和收回操作
——GRANT：
授予，用于给用户或角色授予权限
——REVOKR：
用于回收用户或角色已有的权限
——CREATEUSER：
创建用户
【数据查询语言DQL】
DataQueryLanguage,用来查询需要的语句
★补充知识点★
数据类型：
定义表中每一列可以使用的数据格式和范围，用来保证数据类型的格式和有效性
Ø字符串操作：
Oracle中的字符串是用单引号（‘’）括起来的，注意与Java的区别
1.字符串类型：
CAHR，VARCHAR2表示字符串数据类型，用来在表中存放字符串信息
几点说明：
——在数据库中CHAR，VARCHAR表示的是字符串，注意与java的区别
——CHAR（N）,VARCHAR2（N）指定的是字节数，不是字符数
——VARCHAR2是Oracle独有的数据类型，和其他数据库中的VARCHAR作用一样，加2是Oracle为以后新增一个字符类型，而又不改变原有
VARCHAR定义的长远考虑
LONG：
VARCHAR2的加长版，也是存储变长字符串，最多可达2GB的字
符串数据，LONG有诸多限制：
每张表只能有一个LONG类型列；
不能作为主键；不能建立索引；不能出现在查询条件中……
CLOB：
LONG的改进版，存储定长或变长字符串，最多可达4GB的字符串
数据，Oracle建议用CLOB替代LONG
Eg：
UTF-8环境下：
varchar2英文占1个字节，中文占3个字节，nvarchar2英
文占2个字节，中文占2个字节
GBK环境下：
varchar2英文占1个字节，中文占2个字节，nvarchar2英
文占1个字节中文占3个字节
2.字符串函数：
【补充】虚表dual的概念：
在数据库中，我们想要测试某个表达式的结果只能使用SELECT
语句来实现
DUAL，虚表，没有这么一个表，只是为了满足SELECT的语法要求
我们常用虚表来测试表达式的结果
当SELECT后没有一张表的字段参与时，FROM后就用虚表
a:
CONCAT（char1,char2）:
返回char1和char2连接后的结果
相当于Java中的“+”
等价操作：
连接操作符“||”
CONCAT的参数只能有两个，若多个字符串CHAR连接，需嵌套使
用，如果char1和char2任何一个为NULL，相当于连接了一个
空格
SELECTCONCAT（'I',CONCAT（'LIKE','Oracle'））
FROMdual
SELECT'I'||'LIKE'||'Oracle'
FROMdual
b:
LENGTH（char）：
获取字符串的长度（字符个数）
若字符类型是VARCHAR2，返回字符的实际长度
若字符类型是CHAR，长度还有包括补充的空格
SELECTename,LENGTH（ename）
FROMemp_yys
c:
UPPER（char）：
字符串转大写
SELECTupper（'asfadg'）FROMdual
d:
LOWER（char）：
字符串转小写
SELECTlower（'asAAAS'）FROMdual
e:
INITCAP（char）：
将字符串中每个单词的首字母转大写，其他字符
小写，单词之间用空格或非字母分隔
SELECTinitcap（'as%NGSYisaT'）FROMdual
f:
TRIM（c2FROMc1）：
去掉字符串c1两侧的t2
g:
LTRIM（c1,[c2]）：
从c1的左侧截去c2
h:
RTRIM（c1,[c2]）：
从c1的右侧截去c2
注：
TRIM，LTRIM，RTRIM中，如果没有c2，就截去空格
TRIM的c2只能是单一字符，LTRIM，RTRIM中的c2可以是多字符，
且多字符不管顺序，只要出现就截
若c1中不包含c2，则无法截取，原样输出c1
在LTRIM，RTRIM中，若c2不是c1的最左/右端，则无法截取，原样输出c1
SELECTTRIM（'1'FROM'1sd1'）FROMdual
SELECTLTRIM（'eaaeeeehaha','ae'）FROMdual
SELECTRTRIM（'aaahaha','o'）FROMdual
i:
LPAD（原字符串,总长度,填充字符串）：
左补齐
j:
RPAD（原字符串,总长度,填充字符串）：
右补齐
要求显示N个字符，若char1的值不足长度，则在L/R补充若干个char2，以达到N个字符，不够则补，刚好则原样，超了则择取到N
（都是从左—>右读取（截取）字符，像计算器看到从右—>左的效
果，只是在左—>右读取时加空格显示的效果）
SELECTLPAD（'haha',10,'OK'）FROMdual
SELECTRPAD（'yys'，20,'verygood'）FROMdual
k:
SUBSTR（char,[m[,n]]）：
将字符串char从第m开始，获取n个长度大小的子字符串，在oracle中字符串的下标从1开始
说明：
m：
若果m=0，则从首字符开始，m为负数，则从尾部开始（从后往前……，-3，-2，-1），倒数第m个，向后获取n个字符
n：
如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止
SELECTSUBSTR（'abcde',2,8）FROMDUAL
SELECTSUBSTR（'abcde',-3,2）FROMDUAL
l:
INSTR（char1,char2，[,n[,m]]:
返回字符串char2在源字符串char1中的位置
n，从第几个字符开始找不写默认从第一个开始找
n为负数，从后开始计数，且往前找，注意与SUBSTR
的区别（SUBSTR是从后开始计数，往后截取）
m，指定第m次出现不写默认第一次出现
如果在char1中没有找到子串char2，返回0n不能为负数，否则会报错其实找子字符串只是看字符串的第一个字符
SELECTINSTR（'abcabcabc','abc'）FROMDUAL
SELECTINSTR（'abcabcabc','abc',6）FROMDUAL
SELECTINSTR（'abcabcabc','abc',1,3）FROMDUAL
SELECTINSTR（'abcabcabc','abc',-3,2）FROMDUAL
充分说明了，找子字符串只是看字符串的第一个字符，往前找
前提是必须是abc，abcd就不包含在源字符串中
会返回0
m:
REPLACE（char,'m','*'）：
将字符串char中的'm'，替换成'*'，注意：
replace不会替换原始字符串，仅影响显示结果
SELECTREPLACE（'abcabcabc','b','*'）FROMDUAL
Ø数值操作：
1.数值类型：
NUMBER（p，s）用来在表中存放数值类型的数据
—>p表示数字的总位数，取值为1-38，不写（NUMBEE（*，s）），p默
认为38
—>s表示小数点后面的位数，不写[,s]只有p表示纯整数
整数部分开头有再多的0，只要除去开头的0不超过p-s就OK
小数部分结尾有再多的0，只要除去结尾的0不超过s就OK
2.数值函数：
a:
ROUND（要处理的数字m[,小数位数n]）：
对数字进行四舍五入
*n必须是整数，为正数，四舍五入到小数点后n位
为0，四舍五入到整数位，n缺省，默认为0
为负数，四舍五入到小数点前n位
n为0，保留到个位，看小数点后一位
n为-1，保留到十位，看个位
n为-2，保留到百位，看十位
………………
SELECTROUND（45.678,2）FROMdual
SELECTROUND（45.678）FROMdual
SELECTROUND（43455.678,-3）FROMdual
b:
TRUNC（m[,n]）：
按位截取数字m
m，n含义与ROUND的m，n一样，只不过，只舍不进位
SELECTtrunc（45.678,2）FROMdual
SELECTTRUNC（45.678,-1）FROMdual
c:
CEIL（n）：
上取整——天花板，大于等于n的最小整数
SELECTCEIL（3248.999）FROMDUAL
d:
FLOOR（n）：
下取整——地板，小于等于n的最大整数
SELECTfloor（34564.4343）FROMDUAL
e:
MOD（m,n）：
取m/n的余数，n若为0，直接返回m
SELECTmod（9,0）FROMdual
Ø日期操作：
Oracle中的字符串是用单引号（‘’）括起来的，注意与Java的区别
1.日期类型：
Date：
年月日时分秒，占7字节
——保存日期和时间，能表示的日期范围
公元前4712年1月1日~公元9999年12月31日
TIMESTAMP（timestamp）：
年月日时分秒.小数秒最高精度可达纳秒（ns）
占7或者11字节
CREATETABLEtest_1（
C1DATE，
C2TIMESTAMP）；
2.日期关键字：
SYSDATE，其本质就是一个Oracle内部的函数，返回当前系统时间，精确到秒默认显示格式：
DD—MON—RR
CREATETABLEtest_2（
registerDateDATEDEFAULTSYSDATE）；
columntypedefuultexpr
SELECTSYSDATEFROMDUAL
SYSTIMESTAMP，返回当前系统时间，精确到毫秒
SELECTsystimestampFROMDUAL
3.日期转换函数：
*TO_DATE（要转换的字符串[，转换格式[，指定的日期语言]]）
将字符串按照指定格式转换为日期类型
‘xxx-xx-xx’‘xxxx/xx/xx’‘xxxx’’年’’xx”月”’
装换格式是按习惯自行编写的，是字符串所以用
单引号括起来，若中间非关键字或符号的其他字符
时，在把这些字符双引号括起来
Eg‘yyyy“年”mm“月”dd“日”’
在日期格式字符串中大小写不区分，其他字符串中大小写区分
转化格式的代词
SELECTTO_DATE（
'2014年09月14','YYYY"年"MM"月"DD'）FROMdual
说明：
1.实际上已经按照YYYY"年"MM"月"DD的日期格式，只不过Oracle的控制台输出格式为DD—MON—RR
2.需要转换的字符串里出现的非符号字符时，不需要再加“”，
只有转换格式字符串中出现的非关键字或符号的其他字符
时，才把这些字符用双引号括起来
*TO_CHAR（data[，转换格式[，指定的日期语言]]）
将日期类型数据data按照装换格式输出字符串
SELECTTO_CHAR（
sysdate,'YYYY"年"MM"月"DD"日"HH24:
MI:
SS'）
FROMdual
4.日期常用函数：
a：
LAST_DAY（data）:
返回给定日期date所在月的最后一天
SELECTLAST_DAY（
to_date（'2014年9月14日','YYYY"年"MM"月"DD"日"'））
FROMDUAL
b：
NEXT_DAY（date,char）
给定日期离给定日期最近的下一个星期几（char决定）
中文环境下，char可写成’星期三‘
英文环境下，char可写成‘WEDNESDAY’
为避免麻烦，可直接使用数字1-7表示周日----周六
SELECTNEXT_DAY（SYSDATE,'星期三'）FROMDUAl
SELECTNEXT_DAY（'4-9月-14',5）FROMDUAL
说明：
获取的是距当前时间最近的周四，若给定的时间刚好是周四，及周四以后则获取的是下周周四，周四之前，则获取本周周四
注意是按外国的周算一周时间为：
日一二三四五六
c：
ADD_MONYHS（date,i）:
返回给定日期加上i个月后的日期值
i可以是任何数字，大部分时候取正值整数
i为小数，会被截取整数后再参与运算
i为负数，即减去i个月后的日期值
SElECTADD_MONTHS（'14-9月-14'，2.6）FROMDUAl
date默认格式为DD—MON—RR所以写‘14-9月-14’
d：
MONTHS_BETWEEN（date1,data2）：
获取date1和date2之间隔了多少个月
是date1-date2，若果date2比date1的时间晚，会得到负数
除非两个日期之间隔整数月，否则结果会带小数
SELECTmonths_between（'1-1月-14','3-3月-14'）FROMDUAl
e：
LEAST（expr1[，expr2[，expr3]]……）
GREATEST（expr1[，expr2[，expr3]]……）
比较函数，返回结果是参数列表中最大或最小的值
参数类型必须一致，或可转（在比较之前，参数列表的第二个参数会被自动转换为第一个参数的数据类型，可以转，则继续比较，不可以转报错）
SELECTLEAST（22,99.9）FROMDUAL
SELECTgreatest（sysdate,'1-1月-14'）FROMDUAl
f：
EXTRACT（dateFROMdatetime）:
从参数datetime中提取参数date指定的数据，比如extract（year/month/dayfrom日期变量）
SELECTextract（MINUTEFROMSYSTIMESTAMP）FROM DUAl
Ø空值操作：
*数据类型未知或暂时不存在
*数据库中字段无论是什么类型，默认值都是NULL
*若使用了DAFAULT关键字指定了默认值，则使用指定的
*在创建表的时候，可以为列添加非空约束，被约束的列在插入数据时必须给值，更新数据时，不能将该列的值设为空
*DAFULT和NOTNULL不约束同一字段
*判断是否为空，不能写=null要写ISNULL
插入值INSERT，更新（UDATE）时，可以写column=null
*NULL和任何数字运算结果还是NULL
空值函数：
NVL（expr1，expr2）:
若expr1为空，则取expr2的值（不管expr2是否为空
egNVL（null，null）结果为null）
若expr1不为空，则还是expr1，expr2没用
expr1和expr2可以是任何数据类型，但这两个参数的类型必须一致
SELECTNVL（1,8）FROM DUAl
SELECTNVL（null，'Good'）FROMDUAL
NVL2（expr1,expr2,expr3）:
若expr1为NULL，返回expr3
若expr1不为NULL，返回expr2
SELECTNVL2（null，'Good'，'Better'）FROMDUAL
SELECTNVL2（'YYS'，'Good'，'Better'）FROMDUAL
【查询语句】
SELECT[ALL|DISTINCT][<目标列表达式>[,…n]]——4
FROM<表名或视图名>[,<表名或视图名>,…]——1
WHERE<条件表达式>——2
GROUPBY<列名1>［HAVING<条件表达式>］］——3
ORDERBY<列名2>[ASC|DESC],…］；——5
说明：
1.其中SELECT和FROM语句为必选子句，其他子句为任选子句；
2.SELECT[ALL|DISTINCT][<目标列表达式>[,…n]]子句
指明查询结果集的目标列。
<目标列表达式>是指查询结果集中包含的列名，
可以是直接从基本表或视图中投影得到的字段、与字段相关的表达式或数据
统计的函数表达式，目标列还可是常量。
DISTINCT说明要去掉重复的元组：
数据表中有可能存储相同数据的行，当执行查询操作时，默认情况会显
示所有行，当重复数据没有意义需要去掉时，使用DISTINCT实现
根据单列去重SELECTDISTINCTdeptnoFROMemp
根据多列去重SELECTDISTINCTdeptno，jobFROMemp
只能是保证这几列的组合是没有重复的，单列值还是有可能重复
SELECT（DISTINCTdeptno）,jobFROMemp是没有意义的
DISTINCT后面要
升级会员 