数据库基础.docx

1、数据库基础创建DBlinkcreate database link link_nameconnect to username identified by “password”using(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DYCJ) ) );Truncate table tablename 删除表数据Oracle 11g下载地址lr5w

2、sqlplus /nologSQL conn /as sysdba;已连接。SQL password system更改 system 的口令新口令:重新键入新口令:口令已更改SQL alter user system identified by manager;用户已更改。DDL datadefinition language数据定义语言 create DML data manipulation language数据操作语言 insert update deleteTCL transaction control language事务控制语言DQL data query language数据查询

3、语言 selectDCL data control language数据控制语言 设置权限查看回收站中表select * from recyclebin; 恢复表SQLflashback table test_drop to before drop;或SQLflashback table BIN$b+XkkO1RS5K10uKo9BfmuA=$0 to before drop; select *from user_tab_comments 查询表注释select substr(name,1,2)from info 截取字符串SELECT * FROM qinyuanlei查表数据CREATE

4、TABLE EMPLOYEE_qinyuanlei(ID NUMBER(10),NAME VARCHAR(20),SALARY NUMBER(9,2),GENDER CHAR(1)DEFAULTM)创建表DESC EMPLOYEE_qinyuanlei查看列信息DROP table EMPLOYEE_qinyuanlei删除表RENAME EMPLOYEE_qinyuanlei TO qinyuanlei修改表名altertabletextrenamecolumntextpasstopassword 修改列名ALTER TABLE qinyuanlei ADD (BIRTH DATE)增加列A

5、LTER TABLE qinyuanlei DROP(BIRTH)删除列ALTER TABLE qinyuanlei MODIFY(ID NUMBER(20)修改列INSERT INTO qinyuanlei (ID,NAME,SALARY)VALUES(1,HAHA,5000)插入INSERT INTO qinyuanlei (ID,BIRTH)VALUES(2,TO_DATE(1999-05-05,YYYY-MM-DD)插入日期（一般采用这种格式）UPDATE qinyuanlei SET SALARY=10000 WHERE NAME=HAHA修改HAHA的工资为10000DELETE

6、FROM qinyuanlei (WHERE)当后面不加where时，删除表里全部数据DELETE FROM qinyuanlei WHERE NAME=HAHA删除name是HAHA的数据DELETE FROM qinyuanlei WHERE JOB IS NULL 删除job为NULL的数据，这里要用isSELECT ENAME 姓名,SAL 工资 FROM emp_qinyuanlei;-结果只显示后面的中文，这样可以容易看清SELECT ENAME,SAL FROM emp_qinyuanlei;SELECT * FROM emp_qinyuanlei;-concat(ename,:

7、)先把ename和:拼接起来，语法规则concat(char1,char2)SELECT CONCAT(CONCAT(ENAME,:),SAL)FROM emp_qinyuanlei;SELECT ENAME |:| SAL FROM emp_qinyuanlei;-LENGTH(char)查询长度规则SELECT SAL,LENGTH(SAL)FROM emp_qinyuanlei;-改变字符串全大写，小写，首字母大写SELECT ENAME,UPPER(ENAME),LOWER(ENAME),INITCAP(ENAME) FROM emp_qinyuanlei;-dual伪表 1.查询的内

8、容与任何一张表无关2.为了满足语法要求。好处：只显示一条，否则表里有多少条数据，就显示多少条。SELECT UPPER(NIHAO),LOWER(NIHAO),INITCAP(NI HAO)FROM DUAL;-结果：NIHAO nihao Ni Hao-trim(c2FROMc1)从c1中去掉c2这个字符 c2只能为一个字符SELECT TRIM(EFROMEEHAHE)FROM DUAL;-前后连续出现的e全部清除，结果为HAH.SELECT TRIM( E )FROM DUAL;-此格式用法和java一样，删除前后空格（连续的空格也一并删掉）-LTRIM(C1,C2)删除c1中左边的c2

9、字符SELECT LTRIM(EEEAAEGEGE,E)FROM DUAL;-RTRIM(C1,C2)右边SELECT RTRIM(EEEAAEGEGE,E)FROM DUAL;-LPAD RPAD 当需要显示N多位时，左右补足其他字符SELECT LPAD(SAL,10,-)FROM emp_qinyuanlei;SELECT RPAD(SAL,10,0)FROM emp_qinyuanlei;-SUBSTR 截取字符，字符index从1开始，和java从0开始不同SELECT SUBSTR(SADGWEG,5)FROM DUAL;-从第5个字符开始取SELECT SUBSTR(ASDGAS

10、H,3,4)FROM DUAL;-从第3个字符开始连续取4个-查看字符串中第一次出现in的位置SELECT INSTR(THINGINGING,IN)FROM DUAL;SELECT INSTR(THINGINGING,IN,4)FROM DUAL;-从字符串index=4的位置开始出现in的indexSELECT INSTR(THINGINGING,IN,4,2)FROM DUAL;-从字符串index=4的位置开始出现第2次in的index-若在给定条件下没有找到对应位置，则返回0-ROUND(N,M)和TRUNC(N,M)n为要判断的数，m为保留小数点的位数,n可以默认不写，只取整数位。

11、-ROUND和TRUNC区别在于，ROUND四舍五入，TRUNC直接舍去 TRUNCATE把。截短，缩短SELECT ROUND(45.678,8)FROM DUAL;-当8大于数据的小数点位数时，结果为原数据，不补0SELECT ROUND(45.678,2)FROM DUAL;-保留小数点后2位 结果：45.68SELECT ROUND(45.678,0)FROM DUAL;-不保留小数点后SELECT ROUND(145.678,-2)FROM DUAL;-保留小数点左2位 结果：100SELECT ROUND(145.678,-1)FROM DUAL;-保留小数点左1位 结果：150-

12、MOD(n,m)计算n除以m的余数，不显示商SELECT MOD(16,7)FROM DUAL;-CEIL(N)天花板 FLOOR(N)地板SELECT CEIL(15.79)FROM DUAL;-比当前数大的最接近的数 结果：16SELECT FLOOR(15.79)FROM DUAL;-比当前数小的最接近的数 结果：15SELECT * FROM EMP_QINYUANLEI;-插入当前系统时间SELECT SYSDATE,SYSTIMESTAMP FROM DUAL;-将字符串转换为日期SELECT TO_DATE(2013-11-14 03:50:33,YYYY-MM-DD HH24:

13、MI:SS)FROM DUAL;-结果只显示年月日，不显示时间，date精度不高-TO_CHAR 日期转换为字符串SELECT ENAME,TO_CHAR(HIREDATE,YYYY年MM月DD日)FROM EMP_QINYUANLEI;-除了特殊字符以及符号字符，其他都需要用双引号表示（中文，英文。）SELECT ENAME,TO_CHAR(HIREDATE,YYYY-MM-DD)FROM EMP_QINYUANLEI;-返回当前月的最后一天SELECT LAST_DAY(SYSDATE)FROM DUAL;SELECT LAST_DAY(TO_DATE(2010-1-2,YYYY-MM-D

14、D)FROM DUAL;-NEXT_DAY(N,M)当前N这个日期的周M， 周日-周六，1-7SELECT NEXT_DAY(SYSDATE,5)FROM DUAL;-5是星期四-ADD_MONTHS(D,N)为D这个指定时间增加N个月SELECT ENAME 名字,ADD_MONTHS(HIREDATE,20*12) 20周年 FROM EMP_QINYUANLEI;-列名 别名 别名不需要加引号，但别名不能以数字或符号开头，若以数字、符号开头别名需要用双引号括上-MONTHS_BETWEEN(DATE1,DATE2)计算两个日期之间间隔了多少个月 DATE1-DATE2 结果会有小数，比如

15、1.3个月=30*1.3天 去掉后面的小数用truncSELECT TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE),0)FROM EMP_QINYUANLEI;SELECT SYSDATE-HIREDATE FROM EMP_QINYUANLEI;-DATE日期类型在数据库中可以进行减法操作，结果是相差的天数-LEAST返回一堆参数中最小的一个，如果是日期，返回最早的那个，要求：参数类型要统一SELECT LEAST(1,10,234,6,87)FROM DUAL;-GREATEST最大的 概念同上，返回最大的一个SELECT GREATEST(1,10,234,

16、6,87)FROM DUAL;-EXTRACT(m FROM n)从参数n中提取指定的m数据SELECT EXTRACT(DAY FROM SYSDATE)FROM DUAL;-只能截取到年月日，不能有时分秒-SYSDATE和SYSTEIMSTAMP获取的是oracle的时间，不是当前系统时间SELECT SYSTIMESTAMP FROM DUAL;-温馨提示：oracle上获取的时间戳是格林梅置时间，零时区的，要加上8才到北京时间SELECT EXTRACT(HOUR FROM SYSTIMESTAMP)FROM DUAL;SELECT * FROM EMP_QINYUANLEI;NULL

17、UPDATE STUDENT SET GENDER=NULL WHERE SALARY IS NULL;-这里SET后面要使用等于null，当做判断语句where中，使用is null；不为空使用is not null;SELECT ENAME,COMM FROM EMP_QINYUANLEI WHERE COMM IS NOT NULL;-查询comm值非空的所有数据-所有数据库通用。-NVL(N1,N2)如果N1为null，替换N2，N1不为null，使用N1，N1和N2参数类型必须一致。SELECT NVL(COMM,0) 奖金 FROM EMP_QINYUANLEI;-NVL2(N1,

18、N2,N3)N1不是null，返回N2，如果N1为null，返回N3 N1!=NULL?N2:N3SELECT NVL2(COMM,SAL+COMM,SAL)FROM EMP_QINYUANLEI;-获取员工的工资：若有奖金返回工资+奖金，若没奖金，返回工资。SELECT TO_CHAR(SYSDATE,YYYY年MM月DD日 HH24时MI分SS秒)FROM DUAL;SELECT TO_DATE(31-05-2004,dd-mm-yyyy)FROM DUAL;-第三章SELECT 查询列 FROM 查询表 WHERE 增加条件多个列或者多张表，中间有逗号隔开，where不能用逗号，要使用A

19、ND,OR两个日期相比，需要格式一致吗？SELECT * FROM EMP_QINYUANLEI;SELECT LEAST(TO_DATE(2009/05/21,YYYY/MM/DD),HIREDATE)FROM EMP_QINYUANLEI WHERE ENAME=SMITH;结果是不需要的，后台自动转换为可识别的日期格式，然后相比较。- = = 2000;SELECT ENAME,SAL,COMM FROM EMP_QINYUANLEI WHERE COMM IS NOT NULL AND SAL500;-结果有comm为0的情况SELECT ENAME,SAL,COMM FROM EMP

20、_QINYUANLEI WHERE NVL(COMM,0)0 AND SAL500;-上面两句的差别在于comm为0的是否提取-AND OR 要注意添加括号SELECT ENAME,SAL,DEPTNO FROM EMP_QINYUANLEI WHERE SAL800 AND DEPTNO=20 OR DEPTNO=30;-LIKE 模糊查询 要使用%表示0到多个字符，等同于windows里查询用的（*） _单个字符SELECT ENAME,JOB FROM EMP_QINYUANLEI WHERE ENAME LIKE _M%;-意思就是名字中第2个字符是M，_M表示第3个字符是M。M严格区

21、分大小写-不区分大小写可以使用UPPER(ENAME) LIKE UPPER(_M%) LOWER(ENAME) LIKE LOWER(_m%)-IN NOT INSELECT ENAME,DEPTNO FROM EMP_QINYUANLEI WHERE DEPTNO IN(10,20);SELECT ENAME,DEPTNO FROM EMP_QINYUANLEI WHERE DEPTNO NOT IN(10,20);-DEPTNO IN(10,20)等同于DEPTNO=10 OR DEPTNO=20-NOT IN 不满足其中任意一项 IN 满足其中之一-BETWEEN.AND 规则：左边小

22、，右边大SELECT ENAME,SAL FROM EMP_QINYUANLEI WHERE SAL BETWEEN 500 AND 1000;-ANY任何一个 ALL所有 特点：可以把查询结果作为比较条件SELECT ENAME,SAL FROM EMP_QINYUANLEI WHERE JOB=SALESMAN;-SALESMAN的所有工资SELECT ENAME,SAL FROM EMP_QINYUANLEI WHERE SALALL(SELECT SAL FROM EMP_QINYUANLEI WHERE JOB=SALESMAN);-比所有SALESMAN都大SELECT ENAME

23、,SAL FROM EMP_QINYUANLEI WHERE SALANY(SELECT SAL FROM EMP_QINYUANLEI WHERE JOB=SALESMAN);-比任意一个SALESMAN大就行- + - * /SELECT ENAME,SAL FROM EMP_QINYUANLEI WHERE SAL*1660000;-DISTINCT有区别的,去重查找，用于查找有多少个分类，比如部门，职位查找-重点掌握，经常面试SELECT DISTINCT DEPTNO FROM EMP_QINYUANLEI;SELECT DISTINCT DEPTNO,JOB FROM EMP_QI

24、NYUANLEI;-必须job和deptno同时重复才删除-ORDER BY ASC升序（默认可以不写）DESC降序,这两个词写在要比较的项目末尾，如：ORDER BY SAL DESC或者ASCSELECT ENAME,DEPTNO,SAL FROM EMP_QINYUANLEI ORDER BY SAL;SELECT ENAME,DEPTNO,SAL,HIREDATE FROM EMP_QINYUANLEI WHERE DEPTNO=10 ORDER BY hiredate DESC;-结果中有null的时候，代表最大值，升序就在最下面，降序在最上面SELECT ENAME,SAL,DEP

25、TNO FROM EMP_QINYUANLEI ORDER BY DEPTNO ASC,SAL DESC;-多列排序，先满足前面，依次匹配-重点：-区别：DESC TABLE 查看表结构(description描述) - ORDER BY . DESC 降序排列(descend下降)-聚合函数 （分组函数）-凡是在SELECT中用了聚合函数，就不能出现其他不在聚合函数中的字段SELECT SUM(SAL),ENAME FROM EMP_QINYUANLEI;-报错：not a single-group group function-MAX MIN最大值和最小值，可以统计任何数据类型SELECT

26、 MAX(SAL+comm) 高 ,MIN(SAL+comm) 低 FROM EMP_QINYUANLEI;-因为里面有值为null，任何值和null运算结果都为null，正确的应该写为SELECT MAX(SAL+NVL(COMM,0) MAX_SAL,MIN(SAL+NVL(COMM,0) MIN_SAL FROM EMP_QINYUANLEI;-AVG SUM COUNT求平均值、总和、记录条数，只对数字操作，如果有null，忽略不计，不想忽略不计就需要使用NVL函数SELECT SUM(SAL),COUNT(SAL),AVG(SAL)FROM EMP_QINYUANLEI WHERE

27、DEPTNO=20;SELECT COUNT(*)FROM EMP_QINYUANLEI;-分组 GROUP BY 把某些值相同的分为一组 位置：where、from后面，order by 前面-SELECT里如果出现聚合函数同时又想出现某个字段时，那么该字段必须出现在GROUP BY句中。SELECT SUM(SAL),AVG(SAL),COUNT(SAL),MAX(SAL),MIN(SAL),DEPTNO FROM EMP_QINYUANLEI GROUP BY DEPTNO;SELECT SUM(SAL),AVG(SAL),COUNT(SAL),MAX(SAL),MIN(SAL),DEP

28、TNO FROM EMP_QINYUANLEI;-这句会报错not a single-group group functionSELECT ENAME,DEPTNO FROM EMP_QINYUANLEI GROUP BY DEPTNO;-报错not a GROUP BY expression，-重点：当使用GROUP BY 进行分组时，凡是在SELECT语句中出现的字段必须出现在GROUP BY 语句中-WHERE中不能使用聚合函数 WHERE MAX(SAL)4000 这个是错误的SELECT MAX(SAL),DEPTNO FROM EMP_QINYUANLEI WHERE SAL400

29、0 GROUP BY DEPTNO;-HAVING作用，在分组统计之后用于条件判断,必须跟在GROUP BY 后面，不能单独使用SELECT MAX(SAL),DEPTNO FROM EMP_QINYUANLEIGROUP BY DEPTNOHAVING MAX(SAL)4000;-WHERE HAVING 区别在于如果都是对原始数据进行条件判断，2个通用，否则比较统计后的条件判断，只能用HAVING，HAVING后面可以跟聚合函数SELECT MAX(SAL),DEPTNO FROM EMP_QINYUANLEIWHERE JOB NOT IN(PRISIDENT)GROUP BY DEPT

30、NOHAVING AVG(SAL)2000;-多个数据读取顺序-FROM有多张表，读取顺序从右往左，因此把数据少的放最右边-WHERE 也是从右往左，因此把过滤数据最多的放在最右边-GROUP BY 分组 左-右 -ORDER BY 排序 左-右-外键，保存着另一张表的主键值的那一列，作用，与另一列产生关系-含有外键的表在关联关系中，处于多的那一方-连接条件通常我们使用等值连接，做法，外键=主键SELECT * FROM DEPT_QINYUANLEI;SELECT * FROM EMP_QINYUANLEI;SELECT ENAME 姓名,SAL 工资,DNAME 部门,LOC 地址-好的编码习惯是：E.ENAME,E.SAL,D.DNAME,D.LOC所有字段都加上别名.FROM EMP_QINYUANLEI E,DEPT_QINYUANLEI D WHERE E.DEPTNO=D.DEPTNOAND D.LOC=DALLAS;SELECT ENAME 姓名,LOC 地址FROM EMP_QINY