数据库知识集锦.docx
《数据库知识集锦.docx》由会员分享，可在线阅读，更多相关《数据库知识集锦.docx（16页珍藏版）》请在冰豆网上搜索。
数据库知识集锦
Day01:
数据库中无论字段是什么类型，默认值都是NULL也就是说当我们向表中插入一条数据时，若对应的字段没有给定值，则默认就将NULL作为值插入到该字段上。
可以使用DEFAULT为某个字段指定默认值，这样就会同指定的值插入当前字段。
SQL语句本身不区分大小写，但行业中使用通常会将关键字全部都大写，其他内容全部都消协，形成差异来增加可读性。
但需要注意，字符串在数据库中使用单引号扩起来，并且区分大小写。
NOTNULL 约束
该约束可以是某个字段必须给值，在任何情况下，该字段的值不能为NULL。
修改表的结构：
1.添加新的字段，字段只能追加到表的最后而不是在现有字段中插入一个新字段
ALETERTABLEmyemp_lemon
ADD（添加的属性）
2.删除一个字段
3.修改现有字段
修改可以修改字段的长度，类型，默认值
DML语句
DML可以对表中的数据进行增，删，改
DML语句伴随事物控制的。
插入语句 INSERTINTO 不写默认全列插入
COMMIT 插入语句
ROLLBACK
INSERT 语句中可以不指定针对那些字段插入数据，若不指定，则是全列插入，这时候VALUES中给定的值的顺序，类型，个数必须与表中的字段一致赛可以。
插入日期建议使用TO_DATE函数
修改表中的数据，需要注意，不加WHERE条件，整张表中所有记录都会被修改
删除表中的数据：
DELETEFROMmyemp_lemontea
WHEREname='rose'
CREATETABLEdept_lemonteatea
（deptnoNUMBER
（2）,
dnameVARCHAR2（14）,
locVARCHAR2（13））;
DESCdept_lemonteatea;
INSERTINTOdept_lemonteateaVALUES
（10,'ACCOUNTING','NEWYORK'）;
INSERTINTOdept_lemonteateaVALUES
（20,'RESEARCH','DALLAS'）;
INSERTINTOdept_lemonteateaVALUES
（30,'SALES','CHICAGO'）;
INSERTINTOdept_lemonteateaVALUES
（40,'OPERATIONS','BOSTON'）;
SELECT*FROMdept_lemonteatea;
补充：
1.DB：
数据库
DBMS：
数据库管理系统
2.行（Row）是横排的数据，也被称为记录
列（Column）是总列的数据，也称为字段
3.SQL（结构化的查询语言）可分为：
数据定义语言（DDL），数据操纵语言（DML），事务控制语言（TCL），数据查询语言（DQL），数据控制语言（DCL）
4.数据定义语言用于建立，修改，删除数据库对象。
CREATE：
创建表和其他对象
ALTER：
修改表或其他对象的机构
DROP:
删除表或其他对象的结构
TRUNCATE:
删除表的数据，保留表的结构
5.数据操作语言（DML）包括：
INSERT：
将数据插到表中
UODATE：
更新数据表中已存在的数据
DELETE：
删除数据表中的数据
6.事务控制语言（TCL）
COMMIT：
提交，确认已经进行的数据改变、
ROLLBACKl:
回滚，取消已经进行的数据改变
SAVEPOINT：
保存点，使当前事务可以回到指定的保存点，便于取消部分改变
7.数据查询语言，用来查询数据结构：
SELECT
8.CHAR：
表示固定长度的字符类型
CHAR（N）形式：
表示固定长度的字符类型，N表示占用的字节数，最大长度为2000字节
9VARCHAR2：
表示变长的字符类型
VACHAR2（N）:
表示最多可以占用字节数
10.DATE:
用于定义日期时间的数据
默认格式是：
DD-MON-RR
11.DEEAULT：
通过DEFAULT子句给列指定默认的值
12.ALTERTABLE：
给表增加列，列只能添加在最后，不能插入到现有的列中
13.删除列：
ALTERTABLEempDROP （hiredate）
13.MODFIY：
修改表中列的数据类型
14.INSERTINTO：
给数据表增加记录
15.UPDATE....SET：
更新表中的记录，如果没有where子句，则表示全表的数据都会更新
16.DELETE：
删除表中的记录，如果where子句，则全表的数据都会删除
17.DELETE和TRUNCAT的区别：
delete可以有条件删除，truncat将全表的数据都删除
delete是DML语句，可以回退，truncat是DDL语句，立即生效，无法回退
如果是删除全表记录，且数据量较大的时候delete语句必truncat语句执行效率低。
Day02:
1.LENGTH（）函数返回字符串中字符的事务可以
SELECTename,LEGTH（ename）FROMemp
2.DUAL：
伪表
为了满足SELECT语法要求，但是要查询的数据有与现有的表中数据又没关系的时候使用。
3：
RTIM函数：
取出字符串左右两面的给定字符；给定的只能是单一字符
LTRIM与RTRIM：
分别取出字符串做，右边的字符，可以连续去除左边或右边出现的给定字符的内容，给定字符不关心顺序，只要当前字符串中包含给定的字符去除。
SELECTTRIM（'a'from'aaaatimeaaa'）FROMDUAL
4：
LPAD，RPAD：
补位函数
LPAD（str1，n，str2）：
显示str1的内容，要求显示n的长度，若str1的长度不足n，则在左侧补充若干个str2字符以达到长度。
需要注意str2必须是达内一的字符。
SELECTename，LPAD（sal,6,'*'）FROMemp
LPAD常被用来实现“右对齐” LPAD常被用来实现左对齐
当显示的位数不足时补充字符若显示的内容长度超过要显示的位数个字符显示，剩下的会被去除
5：
SUBSER函数：
用来截取字符串，截取给定字符串，从指定的位置对应的字符开始连续截取指定的字符
SELECTSUBSTR（'THINKINGINJAVA',10,2）FROMDUAL（数据库中字符串的首位计数从1开始，要之一与java的区分）第三个参数可以不指定，不指定则截取到末尾。
若第三个参数的大小超过字符长度时，也是截取到字符串末尾。
第二个参数写0，0也相当于从字符串第一个字符开始截取。
负数是从倒数第几个字符开始截取
6：
INSERT函数：
INSERT（char1，char2[,n[,m]]）:
查看char2在char1中的位置。
n：
从第几个字符处开始查找，若不写则默认为1；
m：
查看出现的第几次，若不写则默认1
SECELTINSERT（'thinkinginjava','in',4,2）FROMDUAL
7：
数字类型函数
ROUND（N，[，m]）:
用于四舍五入，对n进行，保留小数点后m位，m可以不屑，不写默认为0，相当于保留到整数，m可以是负数，保留十位以上的数字。
SECELTROUND（45.678,2）FROMDUAL
TRUNC函数，参数的含义与ROUND一致，效果仅仅是不进行四舍五入，所以是截取数字
SELECTTRUNC（45.678,2）FROMDUAL
SELECTTRUNC（45.567,-1）FROMDUAL
MOD（m，n）：
求m/n余数，r若n为0则直接返回m
SELECTename，MOD（SAL,1000）FROMDUAL
8：
CEIL，ELOOR:
CEIL:
返回大于给定参数的最小整数（向上取整数）
SELECTCEIL（45.678）FROMDUAL;//45
FLOOR：
返回小于给定参数的最大整数（向下取整）
SELECTFLOOR（45.678）FROMDUAL;//45
9：
日期
SYSDATE关键字：
该关键字对应ORACLE的一个内部函数，表示一个DATE类型的当前系统时间值。
SELECTSYSDATEFROMDUAL;
SYSTIMESAMP：
显示当前系统的时间戳类型的值。
SELESCTSYSTIMESAMPFROMDUAL
T0_DATE（）函数：
将一个字符串按照给定的日期格式转换为DATE类型的值，若日期格式字符串中出现了非英文符号的其他字符，这些字符需要使用双引号括起来，否则会抛出日期格式无法识别的错误。
SELECTTO_DATE（'2008-08-08','YYYY-MM-DDHH-MI-SS'）FROMDUAL
数据库中的日期时刻一比较大小的，时间晚的大，时间早的小；
可以对一个日期的值进行加法操作，结果为计算后的日期。
两个日期时间也可以进行减法操作，结果为相差的天数。
SELECTTO_DATE（'2008“年08“月”08“日”，'YYYY“年”MM“月”DD“日” HH24“时”MI“分”SS“秒”）FROMDUAL
TO_CHAR函数：
可以将给定日期按照给定的格式转化为一个字符串
SELECTTO_CHAR（SYSDATE'YYYY-MM-DDHH-MI-SS'）FROMDUAL
LAST_DAY函数返回给定日期对应月底日期，返回值依然是DATE类型。
SELECTLAST_DAY（SYSDATE）FROMDUAL;
ADD_MONTHS函数：
对给定的日期加上给定的月。
SELECTenameADD_MONTHS（hiredate,20*12）FROMemp;
MONTHS_BETWEEN函数：
计算两个日期之间相差多少个月，计算一句第一个日期减去第二个日期的差计算的。
SELECTename,MONTH_BETWEEN（SYSDATE,hiredate）FROMemp
NEXT_DAY函数：
返回距离给定日期最近的还没有过的周几是哪天，若给定的日期与要计算的周几正好是一天，则算出的是下周。
1表示的是周日，2表示的是周一，以此类推。
SELECTNEXT_DAY（SYSDATE,4）FROMDUAL
LEAST，GREATEST函数：
LEAST返回给定参数中最小的
SELECTLEAST（SYSDATE，'10-10月-08'）FROMDUAL;
GREATEST返回给定参数中最大的
SECELTGREATEST（SYSDATE,'10-10月-08）FROMDUAL
参数多少个都可以
EXTRACT：
提取当前系统时间中的数据
提取当前系统时间中的年：
SELECTEXTRACT（YEARFROMSYSDATE）FROMDUAL;
10：
空值操作
SELECT*FROMstudentWHEREgenderISNOTNULL
NULL与任何数字的运算结果都是NULL
空值函数
NVL（arg1,arg2）该函数会判断第一个参数是否为NULL，若是，该函数返回第二个参数的值，否则就将第一个参数值返回，所以该函数的作用是将NUL值替换为一个非NULL值
SELECTename,sal,comm,sal+NVL（comm,0）FROMemp;
NVL2（arg1,arg2,arg3）当arg1不为NULL时，显示arg2，是NULL显示arg3
SELECTename,NVL2（comm,'有奖金','没有奖金'）FROMemp;
SELECTename，NVL2（comm,sal+comm,sal）FROMemp;
补充：
1.CONCAT和“||”：
返回两个字符串链接后的结果
2.LENGTH：
用于返回字符串的长度，如果字符出为VARCHAR2返回的是实际长度，如果字符类型为CHAR，长度还要包括后补的空格
3.TRIM经常用来截取字符串前后的空格
Day03:
1.当SELECT语句中要查询的内容不是单纯的子句，而是一个函数或者是一个表达式时，查询的结果集中对应的该字段的字段名就是函数或者是表达式，这就不便于读取了，为此我们会这样的列加上一个别名，使得查询出来该字段的名字为这个别名增加了可读性，甚至在子查询中对这样的情况还要求必须写别名。
别名可以在字段之后使用空格分隔开，然后定义或者是在字段名之后使用“AS”然后在后面定义，但通常不需用使用“AS”
SELECTename，sal*12“sal”FORMemp
别名的书写不区分大小写，且不能使用空格，若有需要在别名中区分大小写或者加空格，那么别名需要被双引号括起来。
2.WHERE子句，与DML中一致，WHERE是用来限定条件的。
在SELECT语句中使用，可以只将满足WHERE要求的记录查询出来
查看10号部分的员工信息：
WHERE*FROMempWHEREdeptno=10;
查看职位是SALESMAN的员工信息
SELECT*FROMempWHEREjob=‘SALESMAN'
3.AND的优先级高于OR，所以提高优先级可以使用括号。
SELECTename,job,salFROMempWHEREsal>1000
AND（job='SALESMAN'ORjob=’CLERK‘）
4.LIKE关键字，用于模糊查询字符串内容
通配符：
%：
0到多个任意字符
_:
1个任意字符
5.IN（list）：
判断指定的内容的值是否等于列表中任意一项
IN常被用作判断指定值是否与子查询的结果内容一致，这是OR不可代替的
NOTIN（list）：
判断指定内容的值不等于列表中任意一项
查看职位是MANGER或是CLERK的员工信息
SELECTename，job，salFROMempWHEREjobIN（'MANGER','CLERK'）
6.BETWEEN...AND...:
判断指定内容的值在一个区间范围内
工资在1500到3000之间的员工信息
SELECTename，salFROMempWHEREsalBETWEEN1500AND3000；
7.ANY与ALL
判断指定内容>,>=,<,<=一个列表使用
举例：
>ANY：
大于列表中的其中之一，所以大于最小的即可
>ALL：
大于列表中所有，所以必须大于最大的
小于最大的
>ALL：
小于最小的
SELECTempno，ename，job，sal，deptnoFROMempWHEREsal>ANY（3500,4000,4500）
ANY与ALL列表通常不会写几个数据，否则没有意义，通常别表中是一个查询的结果，所以她们常用在判断子查询结果中使用
8.WHER语句可以使用函数或者表达式的结果作为过滤条件
SELECTename,sal,jobFROMempWHEREUPPER（ename）=UPPER（'rose'）;
SELECTename,sal,jobFROMsal*12>50000
9.DISTINCT关键字
去除重复行，DISTINCT后面的字段相同的将在查询结果中被去除
查看公司中共有哪些职位？
SELECTDISTINCTjobFROMemp
DISTINCT关键字必须紧跟在SELECT关键字之后。
DISTINCT后面可以是多个字段，若是多个字段，那么则是这些字段值的组合没有重复。
10.排序
ORDERBY语句：
用来排序查询结果集，ORDERBY 子句必须写在SELECT语句的最后面
ORDERBY 可以根据后面指定的字段的值进行升组或是降序排序
降序使用DESC，升序使用ASC，但升序ASC通常不写，因为默认的就是升序。
若按照多字段排序时，排序的优先级是按照第一个字段的排序方式排列，然后若第一个字段值一样，那么这些记录在按照第二个字段的排序方式排列，以此类推。
SELECTename，deptno，salFROMempORDERBYdeptnoDESC,salDESC
11.聚合函数
又叫多行函数，分组函数
作用：
将若干行记录进行统计，然后产生一个结果。
MAX（）MIN（）
统计指定内容中所有参与记录中的最大值与最小值
查看公司中最高工资与最低工资
SELECTMAX（sal）max_sal,MIN（sal）min_salFROMemp
AVG（）SUM（）
统计平均值和总和
聚合函数忽略NULL值。
若希望NULL值的记录参与统计，可以使用NVL函数现将这些记录转换为非NULL值
SELECTNVG（NVL（comm,0））FROMemp
COUNT（）
该函数统计的是非空记录有多少条，而并不关心具体每条记录的值是多少。
SELECTCOUNT（*）FROMemp
12.GROUPBY子句
GROUPBY 关键字后面可以跟着若干个字段，作用是根据给定的字段值看作一组若是多个字段，则是这些字段值的组合相同的记录看作一组。
SELECT语句中若使用了组函数，那么不再组函数中的其他字段必须出现在GROUPBY子句中！
查看每个部门的最高工资，最低工资，平均工资以及工资的总和
SELECTMAX（sal）,MIN（sal）,AVG（sal）,SUM（SAL）,deptnoFROMempGROUPBYdeptno
查看公司每个职位都有多少人?
SELECTCOUNT（*）,jobFROMempGROUPBYjob
WHERE语句不允许使用分组函数；
例：
我们若想员工工资大于2000，这说明我们已经将表中的数据查询出来了，并先进行统计，在统计的基础上在进行判断。
而WHERE 的过滤实际是在查询表的过程中进行的，表中的数据查询出那条记录石油WHERE的判断为准的，所有这里的where已经在实际需要盘读那的地方之前就进行完毕了
HAVING 子句，该子句必须跟在GROUPBY 子句之后不能单独定义，作用实在分组后，做统计，并用统计结果进行过滤
SELECTMAX（sal）,MIN（sal）,deptnoFROMempGROUPBYdeptnoHAVINGAVG（sal）>2000
13.关联查询
联合多张表进行查询，需要注意的是，N张表关联查询至少要有N-1个连接条件。
连接条件的目的在于让数据库知道表中的记录与另一张表中的记录是如何对应上的
查看SALES部门的员工名字
SELECTemp.enameFROMemp,deptWHEREemp.deptno=dept.deptnoANDdept.dname='SALES'
查看每个员工的名字以及部门的名称以及所在地
SELECTe.name,d.dname,d.locFROMempe,dept,dWHEREe.deptno=d.deptno
不添加连接条件，或者是链接条件无效，都会产生笛卡尔积。
笛卡尔积会产生大量无用的关联。
对于系统资源产生严重的破坏，严重时会导致系统瘫痪
查看每个员工的名字以及所在部门的名称以及所在地
SELECTe.name,d.dname,d.locFROMempeJOINdeptdONe.deptno=d.deptno
这样写的好处在于连接条件定义在ON子句中，WHERE里只定义过滤条件即可，条理更清晰
外连接：
除了将满足条件的数据查询出来以外，还可以将不满足连接条件的数据也都查询出来
外连接分为，左，右，全外连接
左外连接：
以左边的表为主，左边表中的所有记录都查询出来，当有不满足条件的记录时，来自右边的表中的字段全部为NULL
右外连接：
右表为主，左边补NULL
全外连接：
哪边不满足连接条件，哪边补NULL
自连接：
自己表中的一条数据对应自己表的多条数据。
自连接为了解决数据一样，但是又存在父子关系的情况，而且层级关系中有多少层不确定的情况。
自连接接单的说就是解决了树状结构的数据关系。
查看员工，以及其上司的名字？
SELECTe.name,m.enameFROMempeJOINempmONe.mgr=m.empno
补充：
1.查询语句执行的顺序：
查询语句依次执行的顺序依下列子句的次序：
1.from子句：
窒息该顺序从后往前，从左到右。
数据流较小的表尽量凡在后面
2.where子句：
执行顺序为自下而上，从右到左。
将能够过滤掉最大数量记录的条件鞋子where子句的最右边
3.groupby--执行的顺序从做往右分组。
最好在groupby前使用where将不需要groupby之前过滤掉
4.haing子句：
消耗资源。
尽量避免使用，having会在检索出记录之后对结果集进行过滤，需要排序等操作。
5.select语句：
少用*号，尽量取字段名称。
oracle在解析的过程中，通过查询数据字典将*依次转换为所有的列名，消耗时间
6.orderby子句：
执行顺序为从左到右，消耗资源
Day04:
1.子查询的作用是为其他SQL语句提供数据，以便其他SQL可以根据该数据进行操作。
子查询可以嵌套在DQL，DML，DDL中使用，最常见的地方在DQL中使用。
谁的工资比CLARK的工资高：
SELECTename，salFROMempWHEREsal>（SELECTsalFROMempWHEREename='CLARK'）
查看与JONES相同部门的员工？
SELECTename,sal,deptnoFROMempWHEREdeptno=（SELECTdeptnoFROMempWHEREename='JONES'）
DDL中使用子查询：
删除CLARK所在部门的员工
DELETEFROMempWHEREdeptno=（SELECTdeptnoFROMempWHEREename='CLARK'）;
提高CLARK所在部门员工工资的20%
UPDATEempSETsal*1.2WHEREdeptno=（SELECTdptnoFROMempWHEREename='CLARK'）
查找薪水比公司平均薪水高的员工
SELECTename,salFROMempWHEREsal>（SELECTAVG（sal）FROMemp）
查看与SALESMAN不同职位但是相同部门的其他员工
SELECTename,deptnoFROMempWHEREdeptnoIN（SELECTdeptnoFROMempWHEREjob=’SALEMANS'）;
查看谁的工资毕20号部门随便的一个员工工资高
SELECTename,sal,deptnoFROMempWHEREsal>ANY（SELECTsalFROMempWHEREdeptno=20）;
2.EXISTS关键字
用在WHERE子句中，气候要跟一个子查询，而EXISTS是返回为TRUE取决于后面的子查询是否能查询到数据。
查看员工的部门信息？
SELECTdeptno,dnameFROMdeptdWHEREEXISTS（SELECT*FROMempeWHEREd.deptno=e.deptno）
查询列出最低薪水高于部门30的最低薪水的部门的最低薪水？
SELECTMIN（sal）
升级会员 