数据库知识集锦.docx

1、数据库知识集锦Day01:数据库中无论字段是什么类型，默认值都是NULL也就是说当我们向表中插入一条数据时，若对应的字段没有给定值，则默认就将NULL作为值插入到该字段上。可以使用DEFAULT为某个字段指定默认值，这样就会同指定的值插入当前字段。SQL语句本身不区分大小写，但行业中使用通常会将关键字全部都大写，其他内容全部都消协，形成差异来增加可读性。但需要注意，字符串在数据库中使用单引号扩起来，并且区分大小写。NOT NULL约束该约束可以是某个字段必须给值，在任何情况下，该字段的值不能为NULL。修改表的结构：1.添加新的字段，字段只能追加到表的最后而不是在现有字段中插入一个新字段ALE

2、TER TABLE myemp_lemonADD(添加的属性)2.删除一个字段3.修改现有字段修改可以修改字段的长度，类型，默认值DML语句DML可以对表中的数据进行增，删，改DML语句伴随事物控制的。插入语句INSERT INTO不写默认全列插入COMMIT插入语句ROLLBACKINSERT语句中可以不指定针对那些字段插入数据，若不指定，则是全列插入，这时候VALUES中给定的值的顺序，类型，个数必须与表中的字段一致赛可以。插入日期建议使用TO_DATE函数修改表中的数据，需要注意，不加WHERE条件，整张表中所有记录都会被修改删除表中的数据：DELETE FROM myemp_lemon

3、teaWHERE name =roseCREATE TABLE dept_lemonteatea(deptno NUMBER(2),dname VARCHAR2(14) ,loc VARCHAR2(13) ) ;DESC dept_lemonteatea;INSERT INTO dept_lemonteatea VALUES(10,ACCOUNTING,NEW YORK);INSERT INTO dept_lemonteatea VALUES(20,RESEARCH,DALLAS);INSERT INTO dept_lemonteatea VALUES(30,SALES,CHICAGO);IN

4、SERT INTO dept_lemonteatea VALUES(40,OPERATIONS,BOSTON);SELECT *FROM dept_lemonteatea;补充：1.DB：数据库DBMS：数据库管理系统2.行（Row）是横排的数据，也被称为记录列（Column）是总列的数据，也称为字段3.SQL（结构化的查询语言）可分为：数据定义语言（DDL），数据操纵语言（DML），事务控制语言（TCL），数据查询语言（DQL），数据控制语言（DCL)4.数据定义语言用于建立，修改，删除数据库对象。CREATE：创建表和其他对象ALTER：修改表或其他对象的机构DROP:删除表或其他对象的结

5、构TRUNCATE:删除表的数据，保留表的结构5.数据操作语言（DML）包括：INSERT：将数据插到表中UODATE：更新数据表中已存在的数据DELETE：删除数据表中的数据6.事务控制语言（TCL）COMMIT：提交，确认已经进行的数据改变、ROLLBACKl:回滚，取消已经进行的数据改变SAVEPOINT：保存点，使当前事务可以回到指定的保存点，便于取消部分改变7.数据查询语言，用来查询数据结构：SELECT8.CHAR：表示固定长度的字符类型CHAR（N）形式：表示固定长度的字符类型，N表示占用的字节数，最大长度为2000字节9VARCHAR2：表示变长的字符类型VACHAR2（N):

6、表示最多可以占用字节数10.DATE:用于定义日期时间的数据默认格式是：DD-MON-RR11.DEEAULT：通过DEFAULT子句给列指定默认的值12.ALTER TABLE：给表增加列，列只能添加在最后，不能插入到现有的列中13.删除列：ALTER TABLE emp DROP（hiredate）13.MODFIY：修改表中列的数据类型14.INSERT INTO：给数据表增加记录15.UPDATE.SET：更新表中的记录，如果没有where子句，则表示全表的数据都会更新16.DELETE：删除表中的记录，如果where子句，则全表的数据都会删除17.DELETE和TRUNCAT的区别：

7、delete可以有条件删除，truncat将全表的数据都删除delete是DML语句，可以回退，truncat是DDL语句，立即生效，无法回退如果是删除全表记录，且数据量较大的时候delete语句必truncat语句执行效率低。Day02:1.LENGTH()函数 返回字符串中字符的事务可以SELECT ename,LEGTH(ename) FROM emp2.DUAL：伪表为了满足SELECT语法要求，但是要查询的数据有与现有的表中数据又没关系的时候使用。3：RTIM函数：取出字符串左右两面的给定字符；给定的只能是单一字符LTRIM与RTRIM：分别取出字符串做，右边的字符，可以连续去除左边

8、或右边出现的给定字符的内容，给定字符不关心顺序，只要当前字符串中包含给定的字符去除。SELECT TRIM(a from aaaatimeaaa) FROM DUAL4：LPAD，RPAD：补位函数LPAD（str1，n，str2）：显示str1的内容，要求显示n的长度，若str1的长度不足n，则在左侧补充若干个str2字符以达到长度。需要注意str2必须是达内一的字符。SELECT ename，LPAD(sal,6,*) FROM empLPAD常被用来实现“右对齐”LPAD常被用来实现左对齐当显示的位数不足时补充字符若显示的内容长度超过要显示的位数个字符显示，剩下的会被去除5：SUBSER

9、函数：用来截取字符串，截取给定字符串，从指定的位置对应的字符开始连续截取指定的字符SELECT SUBSTR(THINKING IN JAVA,10,2) FROM DUAL（数据库中字符串的首位计数从1开始，要之一与java的区分）第三个参数可以不指定，不指定则截取到末尾。若第三个参数的大小超过字符长度时，也是截取到字符串末尾。第二个参数写0，0也相当于从字符串第一个字符开始截取。负数是从倒数第几个字符开始截取6：INSERT函数：INSERT（char1，char2,n,m):查看char2在char1中的位置。n：从第几个字符处开始查找，若不写则默认为1；m：查看出现的第几次，若不写则默

10、认1SECELT INSERT(thinking in java,in,4,2) FROM DUAL7：数字类型函数ROUND（N，m）:用于四舍五入，对n进行，保留小数点后m位，m可以不屑，不写默认为0，相当于保留到整数，m可以是负数，保留十位以上的数字。SECELT ROUND(45.678,2) FROM DUALTRUNC函数，参数的含义与ROUND一致，效果仅仅是不进行四舍五入，所以是截取数字SELECT TRUNC(45.678,2)FROM DUALSELECT TRUNC(45.567,-1) FROM DUALMOD（m，n）：求m/n余数，r若n为0则直接返回mSELECT

11、 ename，MOD(SAL,1000) FROM DUAL8：CEIL，ELOOR:CEIL:返回大于给定参数的最小整数（向上取整数）SELECT CEIL(45.678)FROM DUAL;/45FLOOR：返回小于给定参数的最大整数（向下取整）SELECT FLOOR(45.678)FROM DUAL;/459：日期SYSDATE关键字：该关键字对应ORACLE的一个内部函数，表示一个DATE类型的当前系统时间值。SELECT SYSDATE FROM DUAL;SYSTIMESAMP：显示当前系统的时间戳类型的值。SELESCT SYSTIMESAMP FROM DUALT0_DATE

12、()函数：将一个字符串按照给定的日期格式转换为DATE类型的值，若日期格式字符串中出现了非英文符号的其他字符，这些字符需要使用双引号括起来，否则会抛出日期格式无法识别的错误。SELECT TO_DATE(2008-08-08,YYYY-MM-DD HH-MI-SS)FROM DUAL数据库中的日期时刻一比较大小的，时间晚的大，时间早的小；可以对一个日期的值进行加法操作，结果为计算后的日期。两个日期时间也可以进行减法操作，结果为相差的天数。SELECT TO_DATE(2008“年08“月”08“日”，YYYY“年”MM“月”DD“日”HH24“时”MI“分”SS“秒”)FROM DUALTO_

13、CHAR函数：可以将给定日期按照给定的格式转化为一个字符串SELECT TO_CHAR(SYSDATE YYYY-MM-DD HH-MI-SS) FROM DUALLAST_DAY函数返回给定日期对应月底日期，返回值依然是DATE类型。SELECT LAST_DAY(SYSDATE) FROM DUAL;ADD_MONTHS函数：对给定的日期加上给定的月。SELECT ename ADD_MONTHS(hiredate,20*12) FROM emp;MONTHS_BETWEEN函数：计算两个日期之间相差多少个月，计算一句第一个日期减去第二个日期的差计算的。SELECT ename,MONTH

14、_BETWEEN(SYSDATE,hiredate)FROM empNEXT_DAY函数：返回距离给定日期最近的还没有过的周几是哪天，若给定的日期与要计算的周几正好是一天，则算出的是下周。1表示的是周日，2表示的是周一，以此类推。SELECT NEXT_DAY(SYSDATE,4)FROM DUALLEAST，GREATEST函数：LEAST返回给定参数中最小的SELECT LEAST（SYSDATE，10-10月-08)FROM DUAL;GREATEST返回给定参数中最大的SECELT GREATEST(SYSDATE,10-10月-08)FROM DUAL参数多少个都可以EXTRACT：

15、提取当前系统时间中的数据提取当前系统时间中的年：SELECT EXTRACT(YEAR FROM SYSDATE)FROM DUAL;10：空值操作SELECT *FROM student WHERE gender IS NOT NULLNULL与任何数字的运算结果都是NULL空值函数NVL(arg1,arg2)该函数会判断第一个参数是否为NULL，若是，该函数返回第二个参数的值，否则就将第一个参数值返回，所以该函数的作用是将NUL值替换为一个非NULL值SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp;NVL2(arg1,arg2,arg3)当ar

16、g1不为NULL时，显示arg2，是NULL显示arg3SELECT ename,NVL2(comm,有奖金,没有奖金)FROM emp;SELECT ename，NVL2(comm,sal+comm,sal) FROM emp;补充：1.CONCAT和“|”：返回两个字符串链接后的结果2.LENGTH：用于返回字符串的长度，如果字符出为VARCHAR2返回的是实际长度，如果字符类型为CHAR，长度还要包括后补的空格3.TRIM经常用来截取字符串前后的空格Day03:1.当SELECT语句中要查询的内容不是单纯的子句，而是一个函数或者是一个表达式时，查询的结果集中对应的该字段的字段名就是函数或

17、者是表达式，这就不便于读取了，为此我们会这样的列加上一个别名，使得查询出来该字段的名字为这个别名增加了可读性，甚至在子查询中对这样的情况还要求必须写别名。别名可以在字段之后使用空格分隔开，然后定义或者是在字段名之后使用“AS”然后在后面定义，但通常不需用使用“AS”SELECT ename，sal*12 “sal” FORM emp别名的书写不区分大小写，且不能使用空格，若有需要在别名中区分大小写或者加空格，那么别名需要被双引号括起来。2.WHERE子句，与DML中一致，WHERE是用来限定条件的。在SELECT语句中使用，可以只将满足WHERE要求的记录查询出来查看10号部分的员工信息：WH

18、ERE *FROM emp WHERE deptno = 10;查看职位是SALESMAN的员工信息SELECT *FROM emp WHERE job=SALESMAN3.AND的优先级高于OR，所以提高优先级可以使用括号。SELECT ename,job,sal FROM emp WHERE sal1000AND(job=SALESMAN OR job=CLERK）4.LIKE关键字，用于模糊查询字符串内容通配符：%：0到多个任意字符_: 1个任意字符5.IN(list)：判断指定的内容的值是否等于列表中任意一项IN常被用作判断指定值是否与子查询的结果内容一致，这是OR不可代替的NOT I

19、N(list)：判断指定内容的值不等于列表中任意一项查看职位是MANGER或是CLERK的员工信息SELECT ename，job，sal FROM emp WHERE job IN(MANGER,CLERK)6.BETWEEN.AND.:判断指定内容的值在一个区间范围内工资在1500到3000之间的员工信息SELECT ename，sal FROM emp WHERE sal BETWEEN 1500 AND 3000；7.ANY与ALL判断指定内容,=,ANY：大于列表中的其中之一，所以大于最小的即可ALL：大于列表中所有，所以必须大于最大的ALL：小于最小的SELECT empno，en

20、ame，job，sal，deptno FROM emp WHERE sal ANY(3500,4000,4500)ANY与ALL列表通常不会写几个数据，否则没有意义，通常别表中是一个查询的结果，所以她们常用在判断子查询结果中使用8.WHER语句可以使用函数或者表达式的结果作为过滤条件SELECT ename,sal,job FROM emp WHERE UPPER(ename) = UPPER(rose);SELECT ename,sal,job FROM sal*12500009.DISTINCT关键字去除重复行，DISTINCT后面的字段相同的将在查询结果中被去除查看公司中共有哪些职位？S

21、ELECT DISTINCT job FROM empDISTINCT关键字必须紧跟在SELECT关键字之后。DISTINCT后面可以是多个字段，若是多个字段，那么则是这些字段值的组合没有重复。10.排序ORDER BY语句：用来排序查询结果集，ORDER BY子句必须写在SELECT语句的最后面ORDER BY可以根据后面指定的字段的值进行升组或是降序排序降序使用DESC，升序使用ASC，但升序ASC通常不写，因为默认的就是升序。若按照多字段排序时，排序的优先级是按照第一个字段的排序方式排列，然后若第一个字段值一样，那么这些记录在按照第二个字段的排序方式排列，以此类推。SELECT enam

22、e，deptno，sal FROM emp ORDER BY deptno DESC,sal DESC11.聚合函数又叫多行函数，分组函数作用：将若干行记录进行统计，然后产生一个结果。MAX( ) MIN( )统计指定内容中所有参与记录中的最大值与最小值查看公司中最高工资与最低工资SELECT MAX(sal) max_sal,MIN(sal) min_sal FROM empAVG( ) SUM( )统计平均值和总和聚合函数忽略NULL值。若希望NULL值的记录参与统计，可以使用NVL函数现将这些记录转换为非NULL值SELECT NVG(NVL(comm,0)FROM empCOUNT(

23、)该函数统计的是非空记录有多少条，而并不关心具体每条记录的值是多少。SELECT COUNT(*) FROM emp12.GROUP BY子句GROUP BY关键字后面可以跟着若干个字段，作用是根据给定的字段值看作一组若是多个字段，则是这些字段值的组合相同的记录看作一组。SELECT语句中若使用了组函数，那么不再组函数中的其他字段必须出现在GROUP BY子句中！查看每个部门的最高工资，最低工资，平均工资以及工资的总和SELECT MAX(sal),MIN(sal),AVG(sal),SUM(SAL),deptno FROM emp GROUP BY deptno查看公司每个职位都有多少人?S

24、ELECT COUNT(*),job FROM emp GROUP BY jobWHERE语句不允许使用分组函数；例：我们若想员工工资大于2000，这说明我们已经将表中的数据查询出来了，并先进行统计，在统计的基础上在进行判断。而WHERE的过滤实际是在查询表的过程中进行的，表中的数据查询出那条记录石油WHERE的判断为准的，所有这里的where已经在实际需要盘读那的地方之前就进行完毕了HAVING子句，该子句必须跟在GROUP BY子句之后不能单独定义，作用实在分组后，做统计，并用统计结果进行过滤SELECT MAX（sal),MIN(sal),deptno FROM emp GROUP BY

25、 deptno HAVING AVG(sal)200013.关联查询联合多张表进行查询，需要注意的是，N张表关联查询至少要有N-1个连接条件。连接条件的目的在于让数据库知道表中的记录与另一张表中的记录是如何对应上的查看SALES部门的员工名字SELECT emp.ename FROM emp,dept WHERE emp.deptno=dept.deptno AND dept.dname=SALES查看每个员工的名字以及部门的名称以及所在地SELECT e.name,d.dname,d.loc FROM emp e,dept,d WHERE e.deptno= d.deptno不添加连接条件，

26、或者是链接条件无效，都会产生笛卡尔积。笛卡尔积会产生大量无用的关联。对于系统资源产生严重的破坏，严重时会导致系统瘫痪查看每个员工的名字以及所在部门的名称以及所在地SELECT e.name,d.dname,d.loc FROM emp e JOIN dept d ON e.deptno=d.deptno这样写的好处在于连接条件定义在ON子句中，WHERE里只定义过滤条件即可，条理更清晰外连接：除了将满足条件的数据查询出来以外，还可以将不满足连接条件的数据也都查询出来外连接分为，左，右，全外连接左外连接：以左边的表为主，左边表中的所有记录都查询出来，当有不满足条件的记录时，来自右边的表中的字段全

27、部为NULL右外连接：右表为主，左边补NULL全外连接：哪边不满足连接条件，哪边补NULL自连接：自己表中的一条数据对应自己表的多条数据。自连接为了解决数据一样，但是又存在父子关系的情况，而且层级关系中有多少层不确定的情况。自连接接单的说就是解决了树状结构的数据关系。查看员工，以及其上司的名字？SELECT e.name,m.ename FROM emp e JOIN emp m ON e.mgr=m.empno补充：1.查询语句执行的顺序：查询语句依次执行的顺序依下列子句的次序：1.from子句：窒息该顺序从后往前，从左到右。数据流较小的表尽量凡在后面2.where子句：执行顺序为自下而上，

28、从右到左。将能够过滤掉最大数量记录的条件鞋子where子句的最右边3.group by-执行的顺序从做往右分组。最好在group by前使用where将不需要group by之前过滤掉4.haing子句：消耗资源。尽量避免使用，having会在检索出记录之后对结果集进行过滤，需要排序等操作。5.select语句：少用*号，尽量取字段名称。oracle在解析的过程中，通过查询数据字典将*依次转换为所有的列名，消耗时间6.order by子句：执行顺序为从左到右，消耗资源Day04:1.子查询的作用是为其他SQL语句提供数据，以便其他SQL可以根据该数据进行操作。子查询可以嵌套在DQL，DML，D

29、DL中使用，最常见的地方在DQL中使用。谁的工资比CLARK的工资高：SELECT ename，sal FROM emp WHERE sal(SELECT sal FROM empWHERE ename= CLARK)查看与JONES相同部门的员工？SELECT ename,sal,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename=JONES)DDL中使用子查询：删除CLARK所在部门的员工DELETE FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE enam

30、e=CLARK);提高CLARK所在部门员工工资的20%UPDATE emp SET sal*1.2 WHERE deptno=(SELECT dptno FROM emp WHERE ename=CLARK)查找薪水比公司平均薪水高的员工SELECT ename,sal FROM emp WHERE sal(SELECT AVG(sal) FROM emp)查看与SALESMAN不同职位但是相同部门的其他员工SELECT ename,deptno FROM emp WHERE deptno IN(SELECT deptno FROM emp WHERE job=SALEMANS);查看谁的工

31、资毕20号部门随便的一个员工工资高SELECT ename,sal,deptno FROM emp WHERE salANY(SELECT sal FROM emp WHERE deptno=20);2.EXISTS关键字用在WHERE子句中，气候要跟一个子查询，而EXISTS是返回为TRUE取决于后面的子查询是否能查询到数据。查看员工的部门信息？SELECT deptno,dname FROM dept d WHERE EXISTS(SELECT *FROM emp e WHERE d.deptno=e.deptno)查询列出最低薪水高于部门30的最低薪水的部门的最低薪水？SELECT MIN(sal)