数据库所有操作总结1课案.docx
- 文档编号:10042067
- 上传时间:2023-02-08
- 格式:DOCX
- 页数:18
- 大小:21.86KB
数据库所有操作总结1课案.docx
《数据库所有操作总结1课案.docx》由会员分享,可在线阅读,更多相关《数据库所有操作总结1课案.docx(18页珍藏版)》请在冰豆网上搜索。
数据库所有操作总结1课案
查询语句
基本数据查询
select内容from表名;
selectdistinctjobfromemp;去除重复行
限定查询(查询满足条件的行)
SELECT*|列名FROM表名WHERE条件;
SELECTempno,ename,commFROMempWHEREcommISNOTNULL;
限定查询----ISNULL和ISNOTNULL的使用
SELECTempno,ename,commFROMempWHEREcommISNULL查询谁没有奖金
限定查询----AND的使用
查询工资大于1500,并且可以领取奖金的雇员
SELECTempno,ename,sal,commFROMEMPWHEREsal>1500ANDcommISNOTNULL;
限定查询----OR的使用
查询工资大于1500和可以领取奖金的雇员
SELECTempno,ename,sal,commFROMEMPWHEREsal>1500ORcommISNOTNULL;
限定查询----使用NOT对条件整体取反
查询工资不大于1500并且不能领取资金的雇员
SELECTempno,ename,sal,commFROMempWHERENOT(sal>1500ANDcommISNOTNULL);
限定查询----BETWEEN...AND...的使用
查询基本工资大于等于1500并且小于等于3000的雇员
SELECTempno,ename,sal,commFROMempWHEREsalBETWEEN1500AND3000
限定查询----IN的使用
查询出雇员编号是7369,7499,7521的雇员的具体信息
SELECT*FROMempWHEREempnoIN(7369,7499,7521);
限定查询----NOTIN的使用
查询出雇员编号不是7369,7499,7521的雇员的具体信息
SELECT*FROMempWHEREempnoNOTIN(7369,7499,7521);
限定查询----LIKE的使用
查询中雇员的名字第二个字符是M的雇员信息
SELECTempno,ename,comm,salFROMempWHEREenameLIKE'_M%';
说明:
_匹配一个字符,%匹配0个或多个字符
限定查询----LIKE匹配日期
查询1982年入职的所有雇员的信息
SELECT*FROMempWHEREhiredateLIKE'%82%'
限定查询----LIKE匹配数字
查询工资中包含5的雇员信息
SELECT*FROMempWHEREsalLIKE'%5%'
对结果排序----ORDERBY
带有ORDERBY子句的SQL语句基本格式
SELECT列...FROM表WHERE条件ORDERBY列
查询员工资大于1500的信息,按工资排序
SELECT*FROMempWHEREsal>1500ORDERBYsal
查询工资大于1500员工的信息,按工资降序,按雇佣日期升序排序
SELECT*FROMempWHEREsal>1500ORDERBYsalDESC,hiredateASC
说明:
ASC排序,DESC降序,默认ASC
左、右外连接
查询员工编号,姓名,所在部门号,部门名称,将没有员工的部门也显示出来
SELECTe.ename,d.deptno,d.dnameFROMempe,deptdWHEREe.deptno(+)=d.deptno;
注:
(+)在左边,表示右连接,会列出右表中出现但是没有在左表中出现的行
交叉连接(CROSSJOIN):
用来产生笛卡尔积的
SELECT*FROMempCROSSJOINdept;
自然连接(NATURALJOIN):
自动进行关联字段的匹配
SELECT*FROMempNATURALJOINdept;
USING子句:
直接指定操作关联列
SELECT*FROMempJOINdeptUSING(deptno);
ON子句:
用户自己编写连接条件
SELECT*FROMempJOINdeptONemp.deptno=dept.deptno;
RIGHTJOIN:
右外连接
SELECTe.empno,e.ename,d.deptno,d.dnameFROMempeRIGHTJOINdeptdONe.deptno=d.deptno;
子查询---IN的使用
查询和SMITH或JONES在同一部门,同一职位工作的员工
SELECT*FROMempWHERE(deptno,job)IN(SELECTdeptno,jobFROMempWHEREenameIN('SMITH','JONES'));
子查询---ANY的使用
=ANY:
与IN操作符的效果一致
查询和SMITH或JONES在同一部门,同一职位工作的员工
SELECT*FROMempWHERE(deptno,job)=ANY(SELECTdeptno,jopFROMempWHEREenameIN('SMITH','JONES'));
>ANY:
只要大于子查询中的任何一个值即可
SELECT*FROMempWHEREsal>ANY(SELECTMIN(sal)FROMEMPGROUPBYdeptno);
>ALL:
比最大的值大
SELECT*FROMempWHEREsal>ALL(SELECTMIN(sal)FROMempGROUPBYdeptno);
只要小于子查询中的任何一个值即可 SELECT*FROMempWHEREsal 比最小的值小 SELECT*FROMempWHEREsal 插入语句 intsertinto表名values(值1值2值3…..)插入的数值的字段类型要与定义的时候一样 intsertinto表名values(值1,值2,null,值3………)有不想写的可以用null来标示 intsertinto表名(字段1.字段2,字段3………)values(值1值2值3……)类型对应 注意: 数值的类型个数顺序不能超过最大值 插入记录到表中INSERT的语法 INSERTINTO表名(字段名1,字段名2,......)VALUES(值1,值2......); 为MYEMP中增加一条记录 INSERTINTOmyemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7899,'张三','清洁工','7369','14-2月-1995',9000,300,40) 注: 给定的值要和指定的字段数一致 当为表中的所有列都插入数据数,可以采用如下简单写法 INSERTINTO表名VALUES(值1,值2......); INSERTINTOmyempVALUES(7991,'李四','清洁工','7369','14-2月- 1995',9000,300,40) 注: 要求给定值的数目和表中列的数目一致,并且值的顺序和表中列的顺序一致。 新增加一条记录到MYEMP中,但是该员工没有奖金也没有上级领导 第一种写法: INSERTINTOmyemp(empno,ename,job,hiredate,sal,deptno) VALUES(7899,'张三','清洁工','14-2月-1995',9000,40) 第二种写法: (用null来指定没有值的列) INSERTINTOmyempVALUES(7898,'王五','清洁工',null,'14-2月- 1995',9000,null,40) 新增加一条记录到MYEMP中,但日期格式按'1988-09-09'的方式指定 �TO_DATE函数的使用 INSERTINTOmyemp(empno,ename,job,hiredate,sal,deptno) VALUES(7896,'赵六','清洁工',TO_DATE('1984-09-09','yyyy-mm-dd'),9000,40); 修改记录 update表名set字段=值,字段=值,字段=值…….where条件; 注意: 条件必须写 删除记录 deletefrom表名where条件; 注意: 条件必须写 ROLLBACK(回滚) 在sqlplus中如果使用ROLLBACK的话那么将撤销操作直到上次执行commit的时候的状态 标量函数的使用 转化大小写 selectupper(‘sddfa’)fromdualselectlower(‘sddfa’)fromdual 将首字母大写其余小写 selectinitcap(ename)fromemp; 连接字符串: selectcount(deptno)fromdept; selectcontact(‘hello’,’woeld’)fromdual; select‘abc’||‘jkh’||‘sdf’fromdual; 求子串: selectsubstr(字符串,起始位,结束位)fromdual;起始位写0或者1都可以代表第一位 selectsubstr(字符串,起始位)fromdual;从起始位开始往后截取 求字符串长度: selectlength(ename)fromemp; 字符串替换 selectreplace(原始字符串,想要被替换的字符串,替换字符串)fromdual; 输出字符串的后三位 selectename,substr(ename,length(ename)-2)fromdual; selectenamesubstr(ename,-3)fromdual; 四舍五入 selectround(132.78454)fromdual;四舍五入取整 selectround(132.78345,2)fromdual;四舍五入并且指定保留小数点的位数 selectround(1323.7858416,-2)fromdual;小数不要并且整数位两位取整本代码得1300 截断小数位 selecttrunc(546541.151)fromdual不管四舍五入直接截断小数5465541 selecttrunc(789.536,2)fromdual截断小数指定小数的数位789.53 selecttrunc(789.536,-2)fromdual截断小数并且对整数进行截断700 取余 selectmod(10,3)fromdual;取余数1 运算规律: 日期+数字=日期 日期--数字=日期 日期—日期=数字 显示系统时间 selectsysdatefromdual; selectto_char(sysdate,'yyyy-mm-dd,hh24: mi: ss')fromdual; 显示某本门员工进入公司星期数: selectempno,ename,round((sysdate-hiredate)/7)fromempwheredeptno=10; 在指定的日期加上指定的月数后的日期: selectadd_months(sysdate,2)fromdual; 求出给定日期范围的月数: selectempnoenamemonth_between(sysdate,hiredate)fromemp; 下一个给定的星期是那个日期: selectnext_day(sysdate,’星期一’)fromdual; 求出给定日期所在的月份的最后一天的日期 selectlast_day(sysdate)fromdual; 转换函数 转换成字符串(TO_CHAR) 查询雇员号,姓名,以及入职的年份 SELECTempno,ename,TO_CHAR(hiredate,'yyyy')FROMemp; 说明: yyyy匹配年份,mm匹配月份,dd匹配日 使用TO_CHAR设置日期的显示格式 SELECTempno,ename,TO_CHAR(hiredate,'yyyy-mm-dd')FROMemp; 使用fm去掉前导0 SELECTempno,ename,TO_CHAR(hiredate,'fmyyyy-mm-dd')FROMemp; 通过TO_CHAR设置数值的格式 SELECTempno,ename,TO_CHAR(sal,'99,999')FROMemp; 说明: 9代表1位数字 显示金额 SELECTempno,ename,TO_CHAR(sal,'$99,999')FROMemp; 说明: $代表美元,L代表本地 转换成数字(TO_NUMBER) SELECTTO_NUMBER('123')+TO_NUMBER('123')FROMdual; 转换成日期(TO_DATE) SELECTTO_DATE('2009-07-31','yyyy-mm-dd')FROMdual; 通用函数 查询每个员工的年收入 SELECTempno,ename,(sal+NVL(comm,0))*12incomeFROMemp; 说明: (COMM,0)当comm的值为null时,用0替换 常用的列函数 SUM(expression)求和 MAX(expression)求最大值 MIN(expression)求最小值 COUNT(expression)统计记录数 COUNT(DISTINCTCOLNAME)统计去除重复行记录数 SQL语句的执行顺序 FROM WHERE GROUPBY HAVING SELECT ORDERBY 创建表 createtable表名(字段1数值类型defultxx,字段2数值类型,……………); defult是默认值可写可不写 createtable表名2as表名1复制表2到表1中 createtable表名as(子查询)复制子查询结果这张表,当子查询结果没有记录时这时只会复制表结构(数据类型)而不会增加数据(有时目的只是复制表结构那么就使用这种方法比如查询语句中使用select1=2from表名) 删除表 droptable表名; 注意: delete是删除表里面的数据 修改表结构 增加一列: altertable表名add(列名称列数据类型defult默认值) 注意: defult和默认值可写可不写 删除列: altertable表名dropcolumn列名称 注意: 不管有没有数据都会删除 修改列的数据类型: altertable表名modify(列名称数据类型defult默认值) 注意: 修改的时候(和原来相同数据类型)长度要求不能小于数据中数据的最小长度,但是能增大,要想改变数据类型的话,必须将字段下的数据全部删除才可以更改数据类型 表名重命名 rename表名(旧)to表名(新); 截断表 truncate表名; 注意: 清空表中的所有数据立即释放资源,该操作不可回滚 ROWNUM 表示行号,是一个伪列,可以在每一张表中出现 应用: 查询表中记录的前五行 SELECT* FROMEMP WHEREROWNUMBETWEEN<=5; 现在要查询从6到10条记录,使用子查询 SELECT*FROM(SELECTROWNUMnum,empno,ename,salFROMemp)eeWHERErownumBETWEEN6AND10; 视图 创建视图 语法: CREATEVIEW视图名称AS子查询 例如: 创建部门20员工的雇员信息,包含EMPNO,ENAME,SAL,DEPTNO CREATEVIEWempv20AS SELECTempno,ename,sal,deptnoFROMemp; 注: 当创建视图以后,可以像操作表一样的操作视图 注: 当创建视图时,视图的操作会影响到对表的操作,这是不安全的,我 们可以采用如下选项来创建视图 �WITHCHECKOPTION不能更新创建视图的条件 �WITHREADONLY创建只读视图 删除视图 语法: DROPVIEW视图名称 DROPVIEWempv20;删除视图empv20; 注: 当删除视图所在的表时,则视图也不能再被使用 序列 创建序列 语法: CREATESEQUENCE序列名称 [MAXVALUEnum|NOMAXVALUE] [MINVALUEnum|NOMINVALUE] [INCREMENTBYnumSTARTWITH10] [CACHEnum|NOCACHE]; [CYCLE|NOCYCLE] 创建序列CREATESEQUENCEmyseq; 序列的操作 nextVal: 取得序列的下一个内容 currVal: 取得当前序列的内容 select序列名称.nextvalfromdual. 创建表验证序列的操作 �CREATETABLEtestseq( nextNUMBER; CURRNUMBER; ) INSERTINTOtestseqVALUES(myseq.nextVal,myseq.currVal); 将以上插入语句,执行5次,观察效果,不难得出结论,nextVal每次都会加1,而 currVal都是取当前值 创建序列指定每次增长的增量 CREATESEQUENCEmyseqINCREMENTBY2; 创建序列指定开始的序列,默认的序列从1开始。 CREATESEQUENCEmyseqINCREMENTBY2STARTWITH10; 创建一个循环序列1,3,5,7,9。 CREATESEQUENCEmyseqMAXVALUE9INCREMENTBY2 STARTWITH1CACHE2CYCLE; 数据库的常用操作语言: showuser;察看当前连接的用户 connectscott/passwored采用scott的用户名/密码连接数据库 desctable_name;察看tableName表结构 quit|exit;退出 disconnect;断开连接 clearscreen;清屏,相当于Windows下的cls命令 select*fromtab;列出当前用户下的所有表。 @path执行path指定的脚本文件 Oracle常用基本数据类型: varchar2/varchar变长字符串 char定长字符串 Integer整型 number(m,n)数字型 smallint短整型 float浮点数 decimal十进制数字 date日期型 SQL分类 DML(DataManipulationLanguage,数据操作语言) 用于检索或修改数据 DDL(DataDefinitionLanguage,数据定义语言) 用于定义数据的结构,如创建修改或者删除数据库对象 DCL(DataControlLanguage,数据控制语言) 用于定义数据库用户的权限 约束 constraint约束名约束字段 主键约束(PRIMARYKEY) CONSTRAINTpersion_pid_pkPRIMARYKEY(pid); 非空约束: nameVARCHAR2(30)NOTNULL, 唯一约束: nameVARCHAR2(30)UNIQUENOTNULL,在创建表时声明 CONSTRAINTperson_name_ukUNIQUE(pid)在表的结尾声明 检查约束: (check) ageNUMBER(3)NOTNULLCHECK(agebetween1and150), 主外键约束: (如一本书要属于一个人) CREATETABLEperson( pidVARCHAR2(18), nameVARCHAR2(30)NOTNULL, ageNUMBER(3)NOTNULL, birthdateDATE, sexVARCHAR2 (2)DEFAULT'男' CONSTRAINTperson_pid_pkPRIMARYKEY(pid), CONSTRAINTperson_name_ukUNIQUE(name), CONSTRAINTperson_age_ckCHECK(ageBETWEEN1AND150), CONSTRAINTperson_sex_ckCHECK(sexIN('男','女','中')) ); CREATETABLEbook( bidNUMBERPRIMARYKEYNOTNULL, bnameVARCHAR(30), bpriceNUMBER(5,2), pidVARCHAR2(18), CONSTRAINTperson_book_pid_fkFOREIGNKEY(pid)REFERENCESperson(pid) ); 增加约束 ALTERTABLE表名称ADDCONSTRAINT约束名称约束类型(约束字段) 例: 分别增加主键约束和外键约束给book表 ALTERTABLEbookADDCONSTRAINTbook_bik_pkPRIMARYKEY(bik); ALTERTABLEbookADDCONSTRAINTperson_book_pid_fkFOREIGN KEY(pid)REFERENCESperson(pid)ONDELETECASCADE; 约束的命名规范(建议) PRIMARYKEY: 表名称_主键名称_pk; UNIQUE: 表名称_字段名称_uk CHECK: 表名称_字段名称
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 所有 操作 总结 课案
![提示](https://static.bdocx.com/images/bang_tan.gif)