数据库基础.docx
《数据库基础.docx》由会员分享，可在线阅读，更多相关《数据库基础.docx（23页珍藏版）》请在冰豆网上搜索。
数据库基础
创建DBlink
createdatabaselinklink_name
connecttousernameidentifiedby“password”
using
'（DESCRIPTION=
（ADDRESS_LIST=
（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.1.117）（PORT=1521））
）
（CONNECT_DATA=
（SERVER=DEDICATED）
（SERVICE_NAME=DYCJ）
）
）';
Truncatetabletablename删除表数据
Oracle11g下载地址
lr5w
sqlplus/nolog
SQL>conn/assysdba;
已连接。
SQL>passwordsystem
更改system的口令
新口令:
重新键入新口令:
口令已更改
SQL>alterusersystemidentifiedbymanager;
用户已更改。
DDLdatadefinitionlanguage数据定义语言create
DMLdatamanipulationlanguage数据操作语言insertupdatedelete
TCLtransactioncontrollanguage事务控制语言
DQLdataquerylanguage数据查询语言select
DCLdatacontrollanguage数据控制语言设置权限
查看回收站中表
select*fromrecyclebin;
恢复表
SQL>flashbacktabletest_droptobeforedrop;
或
SQL>flashbacktable"BIN$b+XkkO1RS5K10uKo9BfmuA==$0"tobeforedrop;
select*fromuser_tab_comments查询表注释
selectsubstr（name,1,2）frominfo截取字符串
SELECT*FROMqinyuanlei查表数据
CREATETABLEEMPLOYEE_qinyuanlei（
IDNUMBER（10）,NAMEVARCHAR（20）,SALARY
NUMBER（9,2）,GENDERCHAR
（1）DEFAULT'M'）创建表
DESCEMPLOYEE_qinyuanlei查看列信息
DROPtableEMPLOYEE_qinyuanlei删除表
RENAMEEMPLOYEE_qinyuanleiTOqinyuanlei修改表名
alter table text rename column textpass to password修改列名
ALTERTABLEqinyuanleiADD（BIRTHDATE）增加列
ALTERTABLEqinyuanleiDROP（BIRTH）删除列
ALTERTABLEqinyuanleiMODIFY（IDNUMBER（20））修改列
INSERTINTOqinyuanlei（ID,NAME,SALARY）VALUES（1,'HAHA',5000）插入
INSERTINTOqinyuanlei（ID,BIRTH）VALUES（2,TO_DATE（'1999-05-05','YYYY-MM-
DD'））插入日期（一般采用这种格式）
UPDATEqinyuanleiSETSALARY=10000WHERENAME='HAHA'修改HAHA的工资为10000
DELETEFROMqinyuanlei（WHERE）当后面不加where时，删除表里全部数据
DELETEFROMqinyuanleiWHERENAME='HAHA'删除name是HAHA的数据
DELETEFROMqinyuanleiWHEREJOBISNULL删除job为NULL的数据，这里要用is
SELECTENAME姓名,SAL工资FROMemp_qinyuanlei;--结果只显示后面的中文，这样可以容易看清
SELECTENAME,SALFROMemp_qinyuanlei;
SELECT*FROMemp_qinyuanlei;
--concat（ename,':
'）先把ename和:
拼接起来，语法规则concat（char1,char2）
SELECTCONCAT（CONCAT（ENAME,':
'）,SAL）FROMemp_qinyuanlei;
SELECTENAME||':
'||SALFROMemp_qinyuanlei;
--LENGTH（char）查询长度规则
SELECTSAL,LENGTH（SAL）FROMemp_qinyuanlei;
--改变字符串全大写，小写，首字母大写
SELECTENAME,UPPER（ENAME）,LOWER（ENAME）,INITCAP（ENAME）FROMemp_qinyuanlei;
--dual伪表1.查询的内容与任何一张表无关2.为了满足语法要求。
好处：
只显示一条，否则表里有多少条数据，就显示多少条。
SELECTUPPER（'NIHAO'）,LOWER（'NIHAO'）,INITCAP（'NIHAO'）FROMDUAL;--结果：
NIHAOnihaoNiHao
--trim（c2FROMc1）从c1中去掉c2这个字符c2只能为一个字符
SELECTTRIM（'E'FROM'EEHAHE'）FROMDUAL;--前后连续出现的e全部清除，结果为HAH.
SELECTTRIM（'E'）FROMDUAL;--此格式用法和java一样，删除前后空格（连续的空格也一并删掉）
--LTRIM（C1,C2）删除c1中左边的c2字符
SELECTLTRIM（'EEEAAEGEGE','E'）FROMDUAL;
--RTRIM（C1,C2）右边
SELECTRTRIM（'EEEAAEGEGE','E'）FROMDUAL;
--LPADRPAD当需要显示N多位时，左右补足其他字符
SELECTLPAD（SAL,10,'-'）FROMemp_qinyuanlei;
SELECTRPAD（SAL,10,'0'）FROMemp_qinyuanlei;
--SUBSTR截取字符，字符index从1开始，和java从0开始不同
SELECTSUBSTR（'SADGWEG',5）FROMDUAL;--从第5个字符开始取
SELECTSUBSTR（'ASDGASH',3,4）FROMDUAL;--从第3个字符开始连续取4个
--查看字符串中第一次出现in的位置
SELECTINSTR（'THINGINGING','IN'）FROMDUAL;
SELECTINSTR（'THINGINGING','IN',4）FROMDUAL;--从字符串index=4的位置开始出现in的index
SELECTINSTR（'THINGINGING','IN',4,2）FROMDUAL;--从字符串index=4的位置开始出现第2次in的index
--若在给定条件下没有找到对应位置，则返回0
--ROUND（N,M）和TRUNC（N,M）n为要判断的数，m为保留小数点的位数,n可以默认不写，只取整数位。
--ROUND和TRUNC区别在于，ROUND四舍五入，TRUNC直接舍去TRUNCATE把。
。
。
截短，缩短
SELECTROUND（45.678,8）FROMDUAL;--当8大于数据的小数点位数时，结果为原数据，不补0
SELECTROUND（45.678,2）FROMDUAL;--保留小数点后2位结果：
45.68
SELECTROUND（45.678,0）FROMDUAL;--不保留小数点后
SELECTROUND（145.678,-2）FROMDUAL;--保留小数点左2位结果：
100
SELECTROUND（145.678,-1）FROMDUAL;--保留小数点左1位结果：
150
--MOD（n,m）计算n除以m的余数，不显示商
SELECTMOD（16,7）FROMDUAL;
--CEIL（N）天花板FLOOR（N）地板
SELECTCEIL（15.79）FROMDUAL;--比当前数大的最接近的数结果：
16
SELECTFLOOR（15.79）FROMDUAL;--比当前数小的最接近的数结果：
15
SELECT*FROMEMP_QINYUANLEI;
--插入当前系统时间
SELECTSYSDATE,SYSTIMESTAMPFROMDUAL;
--将字符串转换为日期
SELECTTO_DATE（'2013-11-1403:
50:
33','YYYY-MM-DDHH24:
MI:
SS'）FROMDUAL;--结果只显示年月日，不显示时间，date精度不高
--TO_CHAR日期转换为字符串
SELECTENAME,TO_CHAR（HIREDATE,'YYYY"年"MM"月"DD"日"'）FROMEMP_QINYUANLEI;--除了特殊字符以及符号字符，其他都需要用双引号表示（中文，英文。
。
）
SELECTENAME,TO_CHAR（HIREDATE,'YYYY-MM-DD'）FROMEMP_QINYUANLEI;
--返回当前月的最后一天
SELECTLAST_DAY（SYSDATE）FROMDUAL;
SELECTLAST_DAY（TO_DATE（'2010-1-2','YYYY-MM-DD'））FROMDUAL;
--NEXT_DAY（N,M）当前N这个日期的周M，周日-周六，1-7
SELECTNEXT_DAY（SYSDATE,5）FROMDUAL;--5是星期四
--ADD_MONTHS（D,N）为D这个指定时间增加N个月
SELECTENAME名字,ADD_MONTHS（HIREDATE,20*12）"20周年"FROMEMP_QINYUANLEI;
--列名别名别名不需要加引号，但别名不能以数字或符号开头，若以数字、符号开头别名需要用双引号括上
--MONTHS_BETWEEN（DATE1,DATE2）计算两个日期之间间隔了多少个月DATE1-DATE2结果会有小数，比如1.3个月=30*1.3天去掉后面的小数用trunc
SELECTTRUNC（MONTHS_BETWEEN（SYSDATE,HIREDATE）,0）FROMEMP_QINYUANLEI;
SELECTSYSDATE-HIREDATEFROMEMP_QINYUANLEI;--DATE日期类型在数据库中可以进行减法操作，结果是相差的天数
--LEAST返回一堆参数中最小的一个，如果是日期，返回最早的那个，要求：
参数类型要统一
SELECTLEAST（1,10,234,6,87）FROMDUAL;
--GREATEST最大的概念同上，返回最大的一个
SELECTGREATEST（1,10,234,6,87）FROMDUAL;
--EXTRACT（mFROMn）从参数n中提取指定的m数据
SELECTEXTRACT（DAYFROMSYSDATE）FROMDUAL;--只能截取到年月日，不能有时分秒
--SYSDATE和SYSTEIMSTAMP获取的是oracle的时间，不是当前系统时间
SELECTSYSTIMESTAMPFROMDUAL;
--温馨提示：
oracle上获取的时间戳是格林梅置时间，零时区的，要加上8才到北京时间
SELECTEXTRACT（HOURFROMSYSTIMESTAMP）FROMDUAL;
SELECT*FROMEMP_QINYUANLEI;
NULL
UPDATESTUDENTSETGENDER=NULLWHERESALARYISNULL;
--这里SET后面要使用等于null，当做判断语句where中，使用isnull；不为空使用isnotnull;
SELECTENAME,COMMFROMEMP_QINYUANLEIWHERECOMMISNOTNULL;--查询comm值非空的所有数据
--所有数据库通用。
--NVL（N1,N2）如果N1为null，替换N2，N1不为null，使用N1，N1和N2参数类型必须一致。
SELECTNVL（COMM,0）奖金FROMEMP_QINYUANLEI;
--NVL2（N1,N2,N3）N1不是null，返回N2，如果N1为null，返回N3N1!
=NULL?
N2:
N3
SELECTNVL2（COMM,SAL+COMM,SAL）FROMEMP_QINYUANLEI;--获取员工的工资：
若有奖金返回工资+奖金，若没奖金，返回工资。
SELECTTO_CHAR（SYSDATE,'YYYY"年"MM"月"DD"日"HH24"时"MI"分"SS"秒"'）FROMDUAL;
SELECTTO_DATE（'31-05-2004','dd-mm-yyyy'）FROMDUAL;
--第三章
SELECT查询列FROM查询表WHERE增加条件
多个列或者多张表，中间有逗号隔开，where不能用逗号，要使用AND,OR
两个日期相比，需要格式一致吗？
SELECT*FROMEMP_QINYUANLEI;
SELECTLEAST（TO_DATE（'2009/05/21','YYYY/MM/DD'）,HIREDATE）FROMEMP_QINYUANLEIWHEREENAME='SMITH';
结果是不需要的，后台自动转换为可识别的日期格式，然后相比较。
--><<>=>=<=
SELECTENAME,SAL,COMMFROMEMP_QINYUANLEIWHERECOMMISNULLANDSAL>2000;
SELECTENAME,SAL,COMMFROMEMP_QINYUANLEIWHERECOMMISNOTNULLANDSAL>500;--结果有comm为0的情况
SELECTENAME,SAL,COMMFROMEMP_QINYUANLEIWHERENVL（COMM,0）>0ANDSAL>500;
--上面两句的差别在于comm为0的是否提取
--ANDOR要注意添加括号
SELECTENAME,SAL,DEPTNOFROMEMP_QINYUANLEIWHERESAL>800ANDDEPTNO=20ORDEPTNO=30;
--LIKE模糊查询要使用%表示0到多个字符，等同于windows里查询用的（*）_单个字符
SELECTENAME,JOBFROMEMP_QINYUANLEIWHEREENAMELIKE'_M%';--意思就是名字中第2个字符是M，'__M'表示第3个字符是M。
M严格区分大小写
--不区分大小写可以使用UPPER（ENAME）LIKEUPPER（'_M%'）LOWER（ENAME）LIKELOWER（'_m%'）
--INNOTIN
SELECTENAME,DEPTNOFROMEMP_QINYUANLEIWHEREDEPTNOIN（10,20）;
SELECTENAME,DEPTNOFROMEMP_QINYUANLEIWHEREDEPTNONOTIN（10,20）;
--DEPTNOIN（10,20）等同于DEPTNO=10ORDEPTNO=20
--NOTIN不满足其中任意一项IN满足其中之一
--BETWEEN...AND规则：
左边小，右边大
SELECTENAME,SALFROMEMP_QINYUANLEIWHERESALBETWEEN500AND1000;
--ANY任何一个ALL所有特点：
可以把查询结果作为比较条件
SELECTENAME,SALFROMEMP_QINYUANLEIWHEREJOB='SALESMAN';--SALESMAN的所有工资
SELECTENAME,SALFROMEMP_QINYUANLEIWHERESAL>ALL（SELECTSALFROMEMP_QINYUANLEIWHEREJOB='SALESMAN'）;--比所有SALESMAN都大
SELECTENAME,SALFROMEMP_QINYUANLEIWHERESAL>ANY（SELECTSALFROMEMP_QINYUANLEIWHEREJOB='SALESMAN'）;--比任意一个SALESMAN大就行
--+-*/
SELECTENAME,SALFROMEMP_QINYUANLEIWHERESAL*16>60000;
--DISTINCT有区别的,去重查找，用于查找有多少个分类，比如部门，职位查找
--重点掌握，经常面试
SELECTDISTINCTDEPTNOFROMEMP_QINYUANLEI;
SELECTDISTINCTDEPTNO,JOBFROMEMP_QINYUANLEI;--必须job和deptno同时重复才删除
--ORDERBYASC升序（默认可以不写）DESC降序,这两个词写在要比较的项目末尾，如：
ORDERBYSALDESC或者ASC
SELECTENAME,DEPTNO,SALFROMEMP_QINYUANLEIORDERBYSAL;
SELECTENAME,DEPTNO,SAL,HIREDATEFROMEMP_QINYUANLEIWHEREDEPTNO=10ORDERBYhiredateDESC;
--结果中有null的时候，代表最大值，升序就在最下面，降序在最上面
SELECTENAME,SAL,DEPTNOFROMEMP_QINYUANLEIORDERBYDEPTNOASC,SALDESC;
--多列排序，先满足前面，依次匹配
--重点：
--区别：
DESCTABLE查看表结构（description描述）
--ORDERBY..DESC降序排列（descend下降）
--聚合函数（分组函数）
--凡是在SELECT中用了聚合函数，就不能出现其他不在聚合函数中的字段
SELECTSUM（SAL）,ENAMEFROMEMP_QINYUANLEI;--报错：
notasingle-groupgroupfunction
--MAXMIN最大值和最小值，可以统计任何数据类型
SELECTMAX（SAL+comm）高,MIN（SAL+comm）低FROMEMP_QINYUANLEI;
--因为里面有值为null，任何值和null运算结果都为null，正确的应该写为
SELECTMAX（SAL+NVL（COMM,0））MAX_SAL,MIN（SAL+NVL（COMM,0））MIN_SALFROMEMP_QINYUANLEI;
--AVGSUMCOUNT求平均值、总和、记录条数，只对数字操作，如果有null，忽略不计，不想忽略不计就需要使用NVL函数
SELECTSUM（SAL）,COUNT（SAL）,AVG（SAL）FROMEMP_QINYUANLEIWHEREDEPTNO=20;
SELECTCOUNT（*）FROMEMP_QINYUANLEI;
--分组GROUPBY把某些值相同的分为一组位置：
where、from后面，orderby前面
--SELECT里如果出现聚合函数同时又想出现某个字段时，那么该字段必须出现在GROUPBY句中。
SELECTSUM（SAL）,AVG（SAL）,COUNT（SAL）,MAX（SAL）,MIN（SAL）,DEPTNOFROMEMP_QINYUANLEIGROUPBYDEPTNO;
SELECTSUM（SAL）,AVG（SAL）,COUNT（SAL）,MAX（SAL）,MIN（SAL）,DEPTNOFROMEMP_QINYUANLEI;--这句会报错notasingle-groupgroupfunction
SELECTENAME,DEPTNOFROMEMP_QINYUANLEIGROUPBYDEPTNO;--报错notaGROUPBYexpression，
--重点：
当使用GROUPBY进行分组时，凡是在SELECT语句中出现的字段必须出现在GROUPBY语句中
--WHERE中不能使用聚合函数WHEREMAX（SAL）>4000这个是错误的
SELECTMAX（SAL）,DEPTNOFROMEMP_QINYUANLEI
WHERESAL>4000
GROUPBYDEPTNO;
--HAVING作用，在分组统计之后用于条件判断,必须跟在GROUPBY后面，不能单独使用
SELECTMAX（SAL）,DEPTNOFROMEMP_QINYUANLEI
GROUPBYDEPTNO
HAVINGMAX（SAL）>4000;
--WHEREHAVING区别在于如果都是对原始数据进行条件判断，2个通用，否则比较统计后的条件判断，只能用HAVING，HAVING后面可以跟聚合函数
SELECTMAX（SAL）,DEPTNOFROMEMP_QINYUANLEI
WHEREJOBNOTIN（'PRISIDENT'）
GROUPBYDEPTNO
HAVINGAVG（SAL）>2000;
--多个数据读取顺序
--FROM有多张表，读取顺序从右往左，因此把数据少的放最右边
--WHERE也是从右往左，因此把过滤数据最多的放在最右边
--GROUPBY分组左-右
--ORDERBY排序左-右
--外键，保存着另一张表的主键值的那一列，作用，与另一列产生关系
--含有外键的表在关联关系中，处于多的那一方
--连接条件通常我们使用等值连接，做法，外键=主键
SELECT*FROMDEPT_QINYUANLEI;
SELECT*FROMEMP_QINYUANLEI;
SELECTENAME姓名,SAL工资,DNAME部门,LOC地址--好的编码习惯是：
E.ENAME,E.SAL,D.DNAME,D.LOC所有字段都加上别名.
FROMEMP_QINYUANLEIE,DEPT_QINYUANLEID
WHEREE.DEPTNO=D.DEPTNO
ANDD.LOC='DALLAS';
SELECTENAME姓名,LOC地址
FROMEMP_QINY
升级会员 