1. 数据库基础
-
基础术语
- DB:database 数据库
- DBMS:database management system 数据库管理系统
- RDBMS:relational 关系型 数据库管理系统
-
其他数据库
- mongodb : 文档型
- redis : key-value
- NoSQL: not only sql
-
术语
- 表:
- 表名:dept、emp、salgrade
- 表描述的是E-R(entry-relational 实体-关系模型)E.F.CODD
- 行:一个实体
- 列:属性
- 值:行列的交叉
- 表:
-
SQL:structured query language 结构化查询语言
- 分类
- DQL:查询 query
- DML:manipulate操纵: 增删改
- DDL:定义
- DCL:控制control
- 分类
-
SQL
- 关键字:如select、from不区分大小写
- 列名也不区分大小写
- 关键字和列名之间要有空格(1个或多个)
-
写SQL的方法
1. 找数据在哪里 from 1.1 如果是多个表中, 2. 条件是什么 where 3. 是否要排序, 排序的依据是什么 order by 4. 输出是什么 select
-
书写SQL的顺序
select from where order by
-
你写的SQL应该在任何的条件下都成立,深刻的理解语义(业务)
A 3000 B 2000 C 1000 D 4000 题目是:查询工资最高的三个人 select * from emp where sal>=2000
-
表达式前后的数据类型要一致
-
列别名只能在select、order by中使用,不能用在where、group by等中(ORACLE)
-
表的别名一旦定义,就不能用原名,只能用别名
select dname,dept.deptno,ename,job,grade --不认识DEPT了 from dept d,emp e,salgrade s where d.deptno=e.deptno and e.sal between s.losal and s.hisal
2. 环境搭建
所需资源
- clearwin2003.rar #干净的虚拟机
- oracle10g.iso #oracle服务端
- vmware workstation #虚拟机软件
- plsqldev804.exe #plsql开发者工具
- keygen.exe #plsql的注册机
- class-client-10201-win32.zip #oracle客户端
虚拟机的配置
-
找到clearwin2003的压缩包
-
解压
- 不要跟别的混合在一起
- 不要释放到有特殊字符的目录下(中文、英文)
- 不要太深
- 注意盘符
-
双击vmx文件打开虚拟机
-
改名(区别于其他的虚拟机)
-
虚拟机-设置
- CD/DVD:使用ISO镜像文件:选择C:\上课软件\oracle\oracle10g\oracle10G.ISO
- 网络适配器:桥接
-
开启虚拟机
-
登录系统
- administrator:51testing
-
修改计算机名:局域网里面重名太多所以要修改
- 右键我的电脑->属性
- 计算机名->更改(不是计算机描述),建议:姓名拼音+口袋号,如wuxianfeng74
- 重启计算机生效
-
再次登录
服务端的搭建
-
打开我的电脑,双击光盘
-
开始安装
-
选择安装方法
- 全局数据库名:orcl74
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D65ENNPG-1604501852490)(D:\work\吴贤峰的课件\共享文件\Oracle\Oracle课程大纲127.assets\image-20200629094811410.png)]
- 先决条件检查:勾选警告
- 安装
- 口令管理
- 去掉scott的勾选(锁定用户,不要锁定)
- 设置新口令:a123456
- 下一步,退出
- 弹出的网页关闭掉
验证
- 右键我的电脑,管理,服务和应用程序->服务
- 看到有5个ORACLE开头的服务
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b47QM5kH-1604501852492)(D:\work\吴贤峰的课件\共享文件\Oracle\Oracle课程大纲127.assets\image-20200629101814502.png)]
- 监听:TNSListener,必须是已启动
- 处理客户端的链接请求
- 这个监听特别脆弱,状态可能是假像(看着已启动,其实死了)
- 重启监听,看看能否恢复到已启动 #要删除再增加或者重新配置)
- 服务:serviceORCL74,必须是已启动
客户端的搭建
- 找到C:\上课软件\oracle\oracle10G,class-client-win32.zip
- 解压到当前目录
- 进入目录,找到setup.exe双击运行
- 注意事项
- 这个软件可以反复装,不建议这么做
- 去点击卸载产品,看看是否已安装
- 下一步
- 选择安装类型:管理员
- 一路下一步
- ORACLE NET CONFIGURATION ASSITANT : 网络配置助手(配置连接)
- 下一步
- 命名方式:下一步
- 服务名:ORCL74 #看你的虚拟机里面的管理->服务和应用程序->最后一个ORACLE服务的尾巴
- 协议:TCP
- *主机名:*写你要连接的服务器的IP地址
- 默认端口号:1521
- 是,进行测试
- ORA-01017 : invalid username/password login denied 无效的用户名/密码,登录被拒绝
- 出现这个提示已经成功了,如果要测试成功,可以点击更改登录,输入正确的用户名密码
- *网络服务名:*名字没有要求,任意都可以,但是要做到见名知意,建议:服务名_IP地址后2位
- 一路下一步
PL/SQL的安装
- 找到找到C:\上课软件\oracle\PLSQL DEVELOPER ,plsqldev804.exe
- 安装路径:要去掉Program files(x 86),这个部分不合法
- 一路下一步
- 双击PLSQL
- 输入用户名密码和选择网络服务名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vEEWVyjM-1604501852493)(D:\work\吴贤峰的课件\共享文件\Oracle\Oracle课程大纲127.assets\image-20200629104330614.png)]
- 左上角必须是scott@网络服务名的格式
每天要做的事情
-
打开虚拟机
-
看下IP地址是否变更
-
如果变更
- 方法一:
- 客户端,开始-程序-oracle开头的程序->配置和移植工具->Net Configuration Assistant
- 本地net服务名配置
- 下一步
- 添加
- 服务名->协议->主机名->是进行->网络服务名
- 方法二:
- 开始-运行-netca
- 本地net服务名配置
- 同上
- 方法三:
- C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
- 直接编辑这个文件即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BXPGEbpL-1604501852495)(D:\work\吴贤峰的课件\共享文件\Oracle\Oracle课程大纲127.assets\image-20200629105703963.png)]
- 方法一:
-
重新打开PLSQL软件,选择你修改后的DATABASE
回家自己安装
-
windows10
- 要用管理员运行(PLSQL,Network configuration assistant)
-
材料
3 DQL
3.1 简单SELECT
3.1.1 查询指定列
-
语法
select 列名 --选择列名 from 表名 --在哪个表里面
-
语法
select 列名1,列名2... --如果要选择多列,列与列之间用逗号隔开 from 表明
-
请问公司里面有哪些部门,显示部门编号
select deptno from dept
-
请问公司里面有哪些部门,显示部门名称
select dname from dept
-
请问公司里面有哪些职员,显示职员姓名
select ename from emp
-
请问公司里面有哪些职员,显示职员编号
select empno from emp
-
显示公司里所有职员编号和职员姓名
select empno,ename from emp
-
练习
-
查询公司里面部门的信息,显示部门编号和部门的名字
-- 1. 在excel中找答案 -- 2. 数据在哪里 dept 输出是什么 deptno dname select deptno,dname from dept
-
查询公司里面工资的等级,显示工资等级和对应的上下限
select grade,losal,hisal from salgrade
-
查询公司里面的职员信息,显示职员的名称和职员编号,以及职员的工作
select ename,empno,job from emp
-
3.1.2 查询所有列
-
语法
select * --*代表所有列 from emp
- 符号代表了特殊的含义
知识点
1. select * from 表,其输出的顺序是建表的顺序
2. select *后不能在前后加列名了
select 列名可以重复写
3. 但是select * 如果不知道表里面有多少数据,不建议做,如果数据特别多,会导致查询很慢
-
查询公司里面部门的信息,显示所有信息
select deptno,dname,loc from dept select * from dept
-
查询公司里面所有职员的信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp; select * from emp;
3.1.3 四则运算
知识点
1. 数字类型可以做四则
2. 字符(串)类型不可以直接做加减法
3. 日期类型可以做加减法,单位默认是天 --如果要加年、月、分、时、秒,要用到特殊的方法
4. 没有值不是0,不是空格,是NULL是空值,NULL是未知数,未知数做四则仍然是未知数
-
查询员工的工资,给每个人加100块,显示职员姓名和加100块后的结果
select ename,sal+100 from emp
-
练习
-
查询员工年薪,显示员工的姓名和年薪,年薪的定义是13个月的工资
select ename,13*sal from emp
-
试着对姓名做加减法,比如名字后面加个ER,如何解决[TODO]
select ename+ER --标识符无效 from emp select ename+'ER' --无效的数字 from emp select ename+2 --无效的数字 from emp select ename+"ER" --标识符无效 from emp
-
试着对日期做加减法,加的单位是?天
select hiredate+1 from emp
-
查询员工年薪,显示员工的姓名和年薪,年薪的定义是13个月的工资和奖金*[TODO]*
select ename,13*sal+comm from emp
-
3.1.4 列别名
知识点
1. 列别名 要用空格和列名分隔开
2. 列别名如果有特殊的要求,要用双引号引起来(ORACLE)
3. 默认情况下别名的列全部大写(Oracle)
4. 列别名只能在select、order by中使用,不能用在where、group by等中(ORACLE)
-
语法
select 列名 [AS] 别名 --[]可选,AS可以写可以不写 from 表名
-
查询员工的编号和姓名
select empno ename from emp select empno as ename from emp
-
查询员工年薪,显示员工的姓名和年薪,年薪的定义是13个月的工资,输出列是姓名和年薪
select ename 姓名, sal*13 年薪 from emp
-
查询员工年薪,显示员工的姓名和年薪,年薪的定义是13个月的工资,输出列是NAME和YearSal
select ename NAME, sal*13 "YearSal" from emp select ename NAME, sal*13 YearSal --输出的仍然是YEARSAL,全大写 from emp
-
查询员工年薪,显示员工的姓名和年薪,年薪的定义是13个月的工资,输出列是姓 名和年 薪
SELECT ename "姓 名",sal*13 "年 薪" from emp
3.1.5 排重
-
语法
select distinct 列名 from 表名 select distinct 列名1,列名2... --多列的组合排重 from 表名
-
查询公司里面有哪些部门有人,显示部门编号
select distinct deptno from emp
-
查询公司里面有哪些职位
select distinct job from emp
-
查询公司里面有哪些不同的部门不同的职位 --组合排重 #仅作了解
select distinct deptno, job from emp
3.2 where子句
-
语法
select from [where condition] CONDITIAN:条件表达式 a>3 表达式返回2个结果:TRUE/FALSE 真/假 对/错 1/0 过滤出来的是:符号条件的(真的、TRUE、对的)
3.2.1 算术运算
知识点
1. 等于号是单等于
2. 不等于可以是!=或者<>,推荐前者
3. 如果是字符串,要用单引号引起来(ORACLE),值区分大小写
4. null不能直接比较
5. 时间不能直接比较(ORACLE)
-
运算符
大于 > 小于 < 等于 = --==不能两个等号,在很多的技术中=是赋值的意思,==是等于的意思,但是SQL中例外。 不等于 != --<>都可以,但是推荐用!=,因为更通用 大于等于 >= 小于等于 <=
-
练习
-
查询公司里面工资超过1500的人都有哪些
select * from emp where sal>1500
-
查询部门10都有哪些人
select * from emp where deptno=10 select * from emp where deptno='10'--不推荐的,oracle给你转换了,把'10'转换成了10
-
查询编号比7902小的人都有哪些
select * from emp where empno<7902
-
查询工资不是3000人都有哪些
select * from emp where sal!=3000 select * from emp where sal<>3000
-
查询ALLEN的工作是什么
select job from emp where ename=allen --标识符无效 select job from emp where ename=ALLEN --标识符无效 select job from emp where ename='allen' --没有查到小写allen的人 select job from emp where ename="ALLEN" --标识符无效 select job from emp where ename='ALLEN' --对的
-
查询职位不是CLERK的人都有哪些
select * from emp where job!='CLERK'
-
查询工资不低于2500的人在哪些部门工作
select distinct deptno from emp where sal>=2500
-
-
练习
-
查询1982年以后入职的人*[TODO]*
select * from emp where hiredate>'1982-12-31' --文字与格式字符串不匹配 select * from emp where hiredate>1982-12-31 --数据类型不一致,前面是date,后面是number
-
查询没发奖金的人的都有哪些*[TODO]*
select * from emp where comm=null --没有结果 select * from emp where comm=0 --只有0 ???
-
查询发了奖金的人都有哪些 [TODO]
select * from emp where comm>0 --基于都是正数
-
3.2.2 区间 between … and …
知识点
1. 上下限都是包含的
2. 上下限不能互换位置
-
语法
select from [where 待比较的值 between 下限 and 上限 ]
-
练习
-
查询工资范围在1600-3000之间的人都有哪些
select * from emp where sal between 1600 and 3000
-
查询职员编号范围在7788到7979之间的人都有哪些
select * from emp where empno between 7788 and 7979 select * from emp where empno between 7979 and 7788 --没有结果
-
查询1800是什么工资等级
select grade from salgrade where 1800 between losal and hisal
-
-
也可以对字符串进行操作,但结果可能有点出乎意料
select * from emp where ename between 'A' and 'O' --下限不在里面,用的是ename去比较
3.2.3 多选 in
知识点
1. 多个预期的值是或者的关系
2. in一个值也是可以的,等价于等于,可以用等于就可以用in来替代,等于是in的一个特列(只有一个预期值)
- 语法
select
from
[ where 待比较的值 in(预期值1,预期值2,...) ]
-
练习
-
查询在部门10或20中的人都有哪些
select * from emp where deptno in (10,20)
-
查询做CLERK或者SALESMAN或者ANALYST的人都有哪些
select * from emp where job in ('CLERK','SALESMAN','ANALYST')
-
查询SCOTT、ALLEN、SMITH的职位
select JOB from emp where ENAME IN ('SCOTT','ALLEN','SMITH')
-
查询在芝加哥的部门名称是什么?(用2种方法实现)
select dname from DEPT where loc ='CHICAGO' select dname from DEPT WHERE LOC IN ('CHICAGO')
-
查询职员编号是7788、7902、7369、8000、7979的人都有哪些
select * from emp where empno in (7788,7902,7369,8000,7979) --没有匹配到就没有输出,输出的是匹配的值
-
3.2.4 模糊匹配 like
- 常见的几种用法
- 以什么开头的,例如姓吴,吴%
- 包含什么,例如名字中含有贤,%贤%
- 以什么结尾,例如名字峰结尾,%峰
- 通配符
- %:0个或多个字符
- _:1个字符
知识点
1. 通配符 %和_
2. 转义的语法
3. 三种常见的做法:开头/包含/结尾
-
语法
select from where 待比较的值 like 表达式 --表达式往往包括%或者_或者组合 select from where 带比较的值 like 表达式 escape '\' --反斜杠\,正斜杠/ '\%' --去掉%的特殊含义 --声明\是转义符 -- oracle支持声明其他的字符作为转义符,比如escape '?' 这样呢?就是转义符了,但是强烈的不推荐 -- 因为大多数的技术都是默认用\作为转义符的
-
练习
-
查询公司里面姓S的人都有哪些
select * from emp where ename like 'S%' --字符串仍然是要用单引号括起来,也是区分大小写
-
查询职位中含有MAN的人在哪些部门工作
select * from emp where job like '%MAN%'
-
查询名字以TH结尾的人都有哪些
select * from emp where ename like '%TH'
-
查询工作地倒数第四个字符是Y的是哪个部门
select * from dept where loc like '%Y___' --后面有3个下划线
-
查询名字第三个字符是R的人都有哪些
select * from emp where ename like '__R%' --R前面2个下划线
-
-
请问
-
下面的表达式什么含义
S%_ --S开头的2个字符以上的字符串 __A%A__ --前后都是2个下划线 --正三是A,倒三是A的至少6个字符的字符串
-
-
准备工作
INSERT INTO EMP VALUES(8000,'MIL_LER','CL%ERK',7782,to_date('23-1-1985','dd-mm-yyyy'),1400,NULL,10);
-
练习
-
查询职位包含%的人都有哪些
select * from emp where job like '%\%%' escape '\' -- 把%想成是普通的字符A,最后把A替换成\A
-
查询名字包括_的人都有哪些
select * from emp where ename like '%\_%' escape '\'
-
-
准备工作
INSERT INTO EMP VALUES(8001,'MIL_LER','CL%%ERK',7782,to_date('23-1-1985','dd-mm-yyyy'),1400,NULL,10);
-
查询职位中含有2个%的人都有哪些
select * from emp where job like '%\%%\%%' escape '\' --查询含有2个%的人都有哪些 select * from emp where job like '%\%\%%' escape '\' --这是查询两个连续的%的人都有哪些
3.2.5 空值比较 is null
-
语法
select from where 待比较的值 is null --查询的就是指定 值为空值的数据
-
练习
-
查询没有奖金记录的人都有哪些(0认为有奖金记录)
select * from emp where comm is null
-
查询公司里面最高领导是谁
--没有领导的人就是最高领导 -- mgr为空 select * from emp where mgr is null
-
3.2.6 任意/任一 all/any
知识点
>all:大于最大值
>any:大于最小值
<all:小于最小值
<any:小于最大值
-
请执行下面的语句说出其含义
select * from emp where sal > all(1000,2000,3000) --含义是:大于任意值(大于最大值)
-
改写上面的语句,分别说出其含义
select * from emp where sal > any(1000,2000,3000) select * from emp where sal < all (1000,2000,3000) select * from emp where sal < ANY(1000,2000,3000)
3.2.7 逻辑运算符
-
常见的逻辑运算符
与 and 或 or 非 not
-
非 not
算术比较符(> < = != >= <=)没有not语句,自己包含了(>对应<=,=就对应!=) between and ===> not between and in ===> not in like ===> not like is null ===> is not null
-
练习
-
查询职位不是PRESIDENT或者SALEMSMAN的人都有哪些
select * from emp where job not in ('PRESIDENT','SALESMAN')
-
查询名字不以A开头的人都有哪些
select * from emp where ename not like 'A%'
-
查询有奖金记录的人都有哪些(0认为有奖金记录)
select * from emp where comm is not null
-
查询工资不在1600-5000范围内的人都有哪些
select * from emp where sal not between 1600 and 5000 --上下限都不包含
-
-
-
优先级
and or not 优先级: not>and>or select * from EMP where ename not like '%S' or (sal >= 2000 AND SAL<=3000) --结果有5000 select * from EMP where ( ename not like '%S' or sal >= 2000) AND SAL<=3000 --这样没有5000,所以系统默认的是and优先
- 自己写代码推荐用括号把逻辑相同的部分括在一起
-
与 and
-
或 or
- 组合多个条件
- 与的话,条件都为真才显示
- 或的话,条件任意一个为真就显示
-
练习
-
查询名字叫ALLEN或者职位是CLERK的人都有哪些
select * from emp where ename='ALLEN' or job='CLERK'
-
查询在部门10或者部门名字是SALES的部门信息
select * from dept where deptno=10 or dname='SALES'
-
查询职位不是CLERK且不在30部门的人都有哪些
select * from emp where job!='CLERK' and deptno!=30
-
查询职位不是ANALYST或SALESMAN,并且工资比1800多人都有哪些
select * from emp where job not in ('ANALYST','SALESMAN') AND SAL>1800
-
查询名字中不含有AR,且工资超过(含)1600,但低于3500(不含)的人都做哪些工种
select JOB from EMP where ENAME NOT LIKE '%AR%' and sal>=1600 and sal <3500
-
查询没发奖金的人都有哪些
-- 为空或者=0 select * from emp where comm is null or comm = 0
-
3.3 排序 order by
知识点
1. 排序方式2种,asc默认,desc要写
2. 可以根据别名来排序
3. nulls first和nulls last的语法
4. 可以多列排序,先排主要列,再排次要列...
-
语法
select from [where condition] [order by 列名 ] --按指定的列名来排序,方式是? select from [where condition] [order by 列名 [ASC|DESC] ] --按指定的列名来排序,方式是升序或者降序 -- asc 升序 -- desc 降序 -- asc是默认可以不写 select from order by 列1 [ASC|DESC],列2 [ASC|DESC]... --多列排序,在第一列的基础上去排序第二列
-
语法
select from order by 列名 [NULLS FIRST|NULLS LAST] --空值在前或者在后
-
练习
-
查询公司里面所有职员的信息,按工资从高到底排序
select * from emp order by sal desc
-
查询公司里面所有职员的信息,按入职时间由远及近排序(两种方法)
select * from emp order by hiredate asc select * from emp order by hiredate
-
-
练习
-
查询公司里面的职员信息,首先按照职位升序排列,再按照员工姓名的字典序倒序排列
select * from emp order by job,ename desc
-
查询部门10或30中,工资不低于1000的人的职员信息,要求按照奖金从高到低排序
select * from emp where deptno in (10,30) and sal>=1000 order by comm desc nulls last
-
查询员工的信息,输出姓名,职员编号,入职时间,按员工的工资降序排列,要求年薪不低于25000
select ename,empno,hiredate from emp where sal*13>=25000 order by sal
-
查询员工的信息,输出姓名,职员编号,入职时间,年薪(13个月的工资),列名是YEARSAL,按员工的年薪降序排列,要求年薪不低于25000
select ename,empno,hiredate,sal*13 YEARSAL from emp where sal*13>=25000 order by sal*13 --也可以不*13 select ename,empno,hiredate,sal*13 YEARSAL from emp where YEARSAL>=25000 --执行报错,标识符无效 order by YEARSAL DESC --在ORDER BY可以使用
-
-
看下面的语句输出,说明其含义 --仅作了解
select ename,sal,hiredate from emp order by 1
- 可以根据列的位置排序,第一列就是1,依次类推,但是一定不能超出列的个数
3.4 函数
- f(x)=3x+1
- f是函数名
- x是参数
- f(3)=3*3+1=10 --此处10是函数的返回值,3是实参
- 参数可以是1个,可以没有,可以有多个,也可以不定个
3.4.1 dual表
-
oracle提供的一个测试表(虚拟表)
-
单行单列
-
不要修改它
-
练习
-
要求返回Hello的小写形式
select lower('Hello') from dual
-
要求返回Hello的大写形式
select upper('Hello') from dual
-
-
dual的其他作用 --仅作了解
select 3+3 from dual --计算器 select user from dual --查看当前用户 select sysdate from dual --查看当前的系统时间