文章目录
1 数据库相关概念
- 数据:数据是对现实生活事务描述的符号.
- 数据库:数据库是用来存储数据的库.
- 数据库管理系统:用户直观用来操作数据库的软件.
- 数据库应用系统:在数据库管理系统之上的系统,专门给普通用户进行使用的系统.
- 数据库系统:用户+数据库管理系统+数据库+数据库应用系统+数据库管理员.
- 常见数据库存储结构:二维表结构存储:数据,数据的描述,数据的关系.
- 常见的数据库管理系统:Oracle,MySQL,SQL SEVER,DB2,Access.
2 数据库的发展阶段
- 网状数据库.
- 层次数据库.
- 关系数据库:
- 使用关系(二维表)结构存储与管理数据.
- 采用结构化查询语言(SQL)作为客户端与数据库服务器之间沟通的桥梁.
- 目前主流的数据库技术.
- 对象数据库:
- 把面向对象的方法和数据库技术结合起来.
- 可以使数据库系统的分析/设计最大程度的与人们对客观世界的认识相一致.
- NOSQL数据库:
- Not Only SQL数据库,泛指非关系数据库,如MongoDB.
3 Oracle相关介绍
3.1 软件相关介绍
- Oracle是市场目前很流行的大型数据库,适用于大型项目的数据存储.
- 作用:合理地管理数据的存储和读取.
- 版本:OracleXE,PLSQL.
- 安装内容:
- 一个数据库管理系统和多个数据库.
- SID:数据库的唯一标识符.
- 软件服务介绍:
- OracleServiceXE:相当于Oracle的开关.
- OracleXETNSListener:相当于Oracle的监听器.
3.2 软件目录结构介绍
- E:\app\ChenZhuJi:安装路径.
- E:\app\ChenZhuJi\oradata:数据库相关信息.
- E:\app\ChenZhuJi\product:数据库管理系统的相关信息.
- 11.2.0\dbhome_1\bin目录:数据库管理系统软件的启动目录.
- 11.2.0\dbhome_1\jdbc目录:数据库和java连接所需的jar包.
- 11.2.0\dbhome_1\netWork目录:数据库管理系统所需的网络配置目录.
- 11.2.0\dbhome_1\log目录:Oracle异常日志信息存储目录.
3.3 软件原理
3.3.1 软件体系架构
- B/S架构:Browser/Sever(浏览器模式).
- C/S架构:Client/Sever(客户端服务器模式).
- Oracle是基于C/S机构的,基本交互流程:Oracle客户端–(P,端口号,协议,库名)→Oracle服务器→数据库.
3.3.2 本地网络服务配置
本地网络服务配置文件目录为:
E:\app\ChenZhuJi\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora该文件存储的是键值对,Oracle客户端会自动加载其中的文件内容,连接配置好的数据库.
例如:设置一个名为ORCL的数据库
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
3.3.3 监听器配置
常见错误:ora-12514.
解决方式:在E:\app\ChenZhuJi\product\11.2.0\dbhome_1\network\admin\listener.ora文件中增加以下代码,再重启监听服务OracleXNETNSListener:
(SID_DESC= (GLOBAL_DBNAME=ORCL) (ORCALE_HOME=E:\app\ChenZhuJi\product\11.2.0\dbhome_1) (ORACLE_NAME=ORCL) )
3.4 Oracle账户介绍
- OracleXE自带账户:XE
- 账户名:
- System:系统用户(管理员).
- Sys:超级用户(工程师用).
- 区别:Sys账号比system账户的权限要多.
- 注意:
- OracleXE版本安装好后,不自带普通用户的;
- Oracle 11G和Oracle 10G自带一个普通用户scott,默认密码为tiger.
3.5 Oracle新建账户
必须使用System账户进行用户创建:打开plsql,使用System账户登录,注意登录身份选择sysdba.
新建SQL命令窗口(执行命令):
--创建用户 --create user 用户名 identified by 密码 create user scott identified by tiger; --给用户赋权限 --赋予数据库登录连接权限 grant connect to scott; --赋予数据库资源操纵权限 grant resource to scott;
3.6 Oracle忘记用户密码的解决方法
- cmd打开window命令窗口→输入命令:sqlplus/nolog→输入命令:conn/as sysdba→输入命令:alter user 要修改的用户名 identified by 新的密码
- 注意:可能提示sqlplus不是内部命令的错误,是因为Oracle的环境变量配置错误.
4 关系型数据库的基本概念
- 关系:整个二维表.
- 关系名:表格名称.
- 元组:行数据(记录).
- 属性:列数据(字段).
- 属性名:列名称(字段名).
- 主键:唯一确定元组的属性组(关键字).
- 域:属性的取值范围.
5 SQL语言简介
- 概念:结构化语句.
- 作用:操作数据,管理用户,管理事务.
- 分类:
- DQL(数据查询语言):select;
- DDL(数据定义语言):create,alter,drop;
- DCL(数据控制语言):grant,revoke;
- DML(数据操作语言):insert,update,delete;
- TCL(事务控制语言):SAVEPOINT,SET TRANSACTION,COMMIT.
6 SQL语句的单表查询
查询表的所有数据:
语法:select *from 表名;
select *from emp;
查询表中指定字段的值:
语法:select 字段名1,字段名2… from emp;
select empno from emp;
as:查询结果中的字段使用别名
语法:在字段名后使用关键字:字段名 as “别名”,as关键字可以省略不写,别名中没有特殊字符时双引号也可以省略.
- select 字段名1 别名1,字段名2 别名2 from 表名;
- select 字段名1 “别名1”,字段名2 “别名2” from 表名;
- select 字段名1 as “别名1”,字段名2 as “别名2” from 表名;
select empno "员工编号",ename 员工姓名,sal as "薪水" from emp;
连接符(||’’||)的使用:
语法:select 字段名||‘字符’||字段名||…from 表名;
注意:||为SQL语句的字符连接符,使用在select和from之间,字符连接格式为:字段名||‘字符’||字段名,一个拼接好的连接在结果集中是作为一个新的字段显式,可以使用别名优化字段显示.
select empno || '的姓名是'|| ename from emp; --输出结果14列:(员工编号)的姓名是(员工姓名)
distinct:去除重复
语法:select distinct 字段名1,字段名2,… from 表名;
注意:去除重复的规则是按照行进行去除的,多行数据完全相同只取其一.
select distinct job from emp; --输出结果5行.
order by:排序
语法:select 字段名,字段名,… from 表名 order by 字段名1,字段名2… desc/asc;
先按字段1排序,如果字段1相同,再按照字段2…进行排序.
注意:desc表示降序排列,asc表示升序排列,默认是asc.
select *from emp order by empno; --将员工表全部信息按照员工编号升序排列输出. select * from emp order by sal desc,empno; --先按工资降序排列,如果工资相同,按员工编号升序排列.
字段的逻辑运算:
select empno,sal+1000 from emp; --将员工编号和其工资加1000输出where:筛选查询
语法:select 字段名,字段名,…from表名 where 筛选条件;
单筛选条件:
语法:使用运算符进行筛选=,>,>=,<,<=,<>.
注意:如果条件中的值为字符,必须使用单引号括起来.
select empno,ename,sal+comm as 薪资 from emp; --查询所有的员工的工资信息 select * from emp where ename='SMITH'; --查询SMITH的个人信息 select empno,ename,sal,sal+comm from emp where ename='SMITH'; --查询SMITH的薪资信息 select * from emp where sal > 1000; --查询工资大于1000的员工信息 select * from emp where sal <> 3000 order by sal; --查询工资不等于3000的员工信息 select * from emp where hiredate >= '01-1月-1981' order by hiredate; --查看入职日期在81年后的员工信息,按照入职时间升序排列. --注意:Oracle默认的日期格式为:日-月-年,示例'03-1月-1981
多筛选条件:
and:使用and关键字,多条件同时成立的筛选使用and关键字进行条件连接.
多个条件使用and关键进行连接，筛选的是符合所有条件的数据
语法:select * from 表名 where 筛选条件1 and 条件2 and …
select * from emp where sal >= 2000 and sal < 3000; --查询工资在2000-3000之间的员工信息 select * from emp where comm is not null and comm > 0; --查询奖金大于0且不为null的员工的全部信息
or:使用or关键字,进行或条件的筛选.
select * from emp where job='SALESMAN' or job= 'ANALYST' or job= 'MANAGER' order by job; --查询工作为SALESMAN,ANALYST,MANAGER的员工信息.
():使用小括号可以提升条件的执行级别,使用了小括号的级别是最高的.
and关键字的执行级别高于or.
select * from emp where (job = 'SALEMAN' or job = 'MANAGER') and sal > 2000; --查询工作为SALEMAN或MANAGER且工资大于2000的员工的全部信息.
like:模糊查询.
%:表示任意多个的任意字符.
查询包含指定字符的数据:select * from 表名 where 字段名 like ‘%字符%’;
select * from emp where ename like '%S%'; --查询名字中带有S的员工的信息.查询以指定字符开头的数据:select * from 表名 where 字段名 like ‘字符%’;
select * from emp where ename like 'S%'; --查询名字以S开头的员工的信息.查询以指定字符结尾的数据:select * from 表名 where 字段名 like ‘%字符’;
select * from emp where ename like '%S'; --查询以S结尾的员工的信息.
_:表示一个任意字符.
查询指定位置为指定字符的数据:select * from 表名 where 字段名 like ‘_字符%’;
select * from emp where ename like '_A%'; --查询名字中第二个字符为A的员工信息.
escape:将指定的字符变为转义字符.
语法:select * from 表名 where 字段名 like ‘%字符2字符1%’ escape’字符2’ ;
转义字符可以将特殊字符转为普通字符.
select * from emp where ename like '%/_%' escape '/'; --查询名字中带_的员工信息.
is null/is not null:是否为空.
- 字段值为null:select * from 表名 where 字段名 is null;
- 字段值不为null:select * from 表名 where 字段名 is not null;
in:使用in关键字,也可以进行或筛选,但是in中的内容只能为一个字段的值.
select * from emp where job in('SALESMAN','ANALYST','MANAGER'); --查询工作为SALESMAN,ANALYST,MANAGER的员工信息.between and:使用between and 关键字进行条件连接,包含两头的数据.
select * from emp where sal between 2000 and 3000; --查询工资在2000-3000之间的员工信息.
7 Oracle函数的使用
概念:Oracle提供的个用来进一步修饰或者处理数据的方法.
使用:在书写SQL语句时直接使用即可.
单行函数:每行数据返回一个结果.
字符函数:
INITCAP(char):首字母大写.
select INITCAP(ename) from emp;--首字母大写LOWER(char):转换为小写.
select LOWER(ename) from emp;--将名字转成小写输出UPPER(char):转换为大写.
select UPPER(ename) from emp;--将名字转成大写输出LTRIM(char,set):左裁剪.
select LTRIM(ename,'SMI') from emp;--将名字最左边为SMI的裁掉SMIRTRIm(char,set):右裁剪.
select RTRIM(ename,'TH') from emp;--将名字最左边为TH的裁掉THTRANSLATE(char,from,to):按字符翻译.
select TRANSLATE(ename,'ADC','154') from emp;--将名字中所有A换成1,D换成5,C换成4REPLACE(char,search_str,replace_str):字符串替换.
select REPLACE(ename,'SM','sz') from emp;--将名字中所有的SM换成szINSTR(char,substr):查找子串位置.
select INSTR(ename,'SM') from emp;--查询子串SM在名字中的位置SUBSTR(char,pos,len):取子字符串.
select SUBSTR(ename,0,3) from emp;--获取名字中的前三个字符CONCAT(char1,char2):连接字符串.
select CONCAT(ename,'傻二四') from emp;--所有名字后面连接傻二四
数值函数:
- ABS(n):取绝对值.ABS(-15)结果为15
- CEIL(n):向上取整.CEIL(2.4)结果为3
- SIN(n):正弦.SIN(1.571)结果为0.999
- COS(n):余弦.COS(0)结果为1
- SIGN(n):取符号.SIGN(-2.4)结果为-1
- FLOOR(n):向下取整.FLOOR(2.4)结果为2
- POWER(m,n):m的n次幂.POWER(2,4)结果为16
- MOD(m,n):取余数.MOD(10,3)结果为1
- ROUND(m,n):四舍五入.ROUND(2.424,2)结果为2.42
- TRUNC(m,n):截断小数.TRUNC(24.2424,2)结果为24.24
- SQRT(n):平方根.SQRT(4)结果为2
- 注意:使用数值函数之后输出的是一个伪表,不是真实存在的表,是为了方便进行数据的验证而临时存在的表.
日期函数:
MONTHS_BETWEEN:返回两个日期间的月份.
select MONTHS_BETWEEN('25-3月-2019','08-3月-1998') from emp; --输出结果252.548ADD_MONTHS:返回把月份数加到日期上的新日期.
select ADD_MONTHS('25-3月-2019',5) from emp; --2019/8/25NEXT_DAY:返回指定日期后的下一个星期几对应的新日期.
select NEXT_DAY('25-3月-2019','星期一') from emp; --2019/4/1LAST_DAY:返回指定日期所在的月的最后一天.
select LAST_DAY('25-3月-2019') from emp; --2019/3/31ROUND:按指定格式对日期进行四舍五入.
select ROUND(TO_DATE('25-3月-2019'),'YEAR') from emp;--2019/1/1 select ROUND(TO_DATE('25-3月-2019'),'MONTH') from emp;--2019/4/1 select ROUND(TO_DATE('25-3月-2019'),'DAY') from emp;--2019/3/24,按星期四舍五入TRUNC:对日期按照指定方式进行截断.
select TRUNC(TO_DATE('25-3月-2019'),'YEAR') from emp;--2019/1/1 select TRUNC(TO_DATE('25-3月-2019'),'MONTH') from emp;--2019/3/1 select TRUNC(TO_DATE('25-3月-2019'),'DAY') from emp;--2019/3/24
多行函数:多行数据返回一个结果.
作用:对查询的数据进行统计.
使用:select 多行函数名(字段名),多行函数名(字段名)…from 表名;
max:返回该字段的最大值.
select * from emp where sal = (select max(sal) from emp); --查看最高工资的员工的信息min:返回该字段的最小值.
select * from emp where sal = (select min(sal) from emp); --查看最低工资的员工的信息avg:返回该字段的平均值.
select avg(sal) as "平均工资" from emp; --查看所有员工的平均工资sum:返回该字段的和.
select sum(sal) as "工资总和" from emp; --查看所有员工的工资总和count:统计个数.
count(*):返回表的记录数;
count(字段名):返回非空值的数量;
count(distnct 字段名):返回去除重复后的字段值得数量.
select count(*) from emp; --统计记录个数
注意:多行函数不能和普通字段和单行函数混用,除非分组.
转换函数:
TO_NUMBER(数值类型的字符):将字符转换为数值.
注意:数值和字符之间的转换可以隐式转换,to_number可以不写.
select to_number('123') + 2 from emp;--125
TO_CHAR(数值或者日期):将数值或者日期转换为字符.
将数值转换为字符:
select to_char(123) from emp;--123数值转换成字符可以指定显示格式:
- 9:表示位置占位,例如999,999,999会将数字按照三个一组使用逗号隔开;
- L:表示人民币符号,$表示美元符号;
- 0:也可以进行占位分组,但是如果真实数据位数不足,会使用0补位.
select to_char(123456789,'999,999,999') from emp; --123,456,789 select to_char(123456789,'L999,999,999') from emp; --￥123,456,789 select to_char(123456789,'$999,999,999') from emp; --$123,456,789 select to_char(123456789,'0000,000,000') from emp; --0123,456,789
将日期转换为字符:to_char(‘要转换的日期’,‘转换格式’);
注意:如果不指明转换格式,则使用默认格式日月年,例如:‘01-1月-1981’.
常用转换格式:
- yyyy-mm-dd
- yyyy/mm/dd
- ’ yyyy “年” mm “月” dd “日” ’
select to_char(hiredate) from emp; --使用默认格式将日期转换为字符. select to_char(hiredate,'yyyy-mm-dd') from emp; --使用指定格式将日期转换为字符.
TO_DATE(‘日期格式的字符’,‘日期格式’):将字符转换为日期.
注意:字符必须符合日期格式;
Oracle默认的转换格式为日月年,例如:'01-1月-2019’是一个日期.
常用日期格式:
yyyy-mm-dd
yyyy/mm/dd
select * from emp where hiredate > to_date('1982-01-01','yyyy-mm-dd'); --查询员工入职时间在1982年以后的信息.
通常新增数据使用to_date(),查询数据使用to_char()
其他函数:
nvl(字段名,默认值):
如果字段值不为null,则返回字段值,如果字段值为null,则返回默认值.
select ename as "姓名",sal + nvl(comm,0) as "薪资" from emp; --查询员工的工资信息
nvl2(字段名,处理1,处理2):
如果字段值不为null,则执行处理1,字段值为null执行处理2.
select ename as "姓名",nvl2(comm,sal+comm,sal) as "薪资" from emp; --查询员工的工资信息
decode(字段名,值1,处理1,值2,处理2,值3,处理3,…,公共处理):
如果字段的值和decode中的条件值相同则执行对象的处理,如果没有相同的值则执行公共处理.
select decode(job,'MANAGER','经理','PRESIDENT','董事长','SALEMAN','销售','普通员工') from emp; --显示员工的职称
8 Oracle的分组
语法:group by 分组字段名 having 条件
注意:
- 使用了分组之后,在select语句中只允许出现分组字段和多行函数;
- 如果是多字段分组,则先按第一个字段分组,然后每个小组继续按照第二个字段继续分组,以此类推;
- 在where子句中不允许出现多行函数.
关键字having:
作用:针对分组进行分组后的数据筛选,允许使用多行函数.
注意:having关键字必须和分组结合使用,不允许单独使用.
where和having的比较:
- where子句不允许出现多行函数,having子句允许出现多行函数;
- where子句和having都可以使用普通字段进行筛选,但是where效率高于having.
- where执行顺序:from->where->group by->select->order by
- having执行顺序:from->group by->select->having->order by
- 在分组语句中,使用where进行字段级别的筛选,使用having进行多行函数的筛选
代码示例:
select deptno as "部门编号",max(sal) as "最高工资" from emp group by deptno; --查询不同部门的部门编号和最高工资 select count(*) from emp where deptno > 10 group by deptno,job; --查询部门编号大于10的不同部门的不同工作岗位的人数,where写法 select count(*) from emp group by deptno,job having deptno > 10; --查询部门编号大于10的不同部门的不同工作岗位的人数,having写法
9 Oracle的增删改
增加数据
语法:insert into 表名(字段名,字段名,…) values(值,值2,值3…);
注意:
- 主键必须给值,允许为空的字段可以不给值;
- 插入语句表名后跟的字段名为要赋值的字段,值和字段必须是一一对应的;
- 如果是全字段插入,可以省略部分字段名部分,格式:insert into 表名 values(值1,值2,…);
insert into dept(deptno,dname,loc)values('50','傻二四','揭阳'); --在部门中新增一个新的部门信息. insert into dept values('50','傻二四','揭阳'); --全字段插入,省略字段名. insert into dept(deptno,dname) values('50','傻二四'); --插入部分字段.
删除数据
删除表中的所有记录:delete from 表名;
delete from emp;--清空表删除表中的所有记录:truncate table 表名;效率高于delete.
truncate table emp;--清空表删除指定的数据,只要符合条件就会删除:delete from 表名 where 条件;
delete from emp where deptno = 10;--删除部门编号为10的数据
修改数据
将字段的值全部改成新的值:update 表名 set 字段名=新的值,字段名=新的值…;
update dept set dname='小狗狗',loc='揭阳'; --将表中dname列全部改成小狗狗,loc列全部改成揭阳将符合条件的数据的字段改为新的值:update 表名 set 字段名=新的值,字段名=新的值…where 条件;
update dept set dname='傻二四',loc='揭阳' where deptno = 10; --将表中deptno为10的数据的dname列全部改成小狗狗,loc列全部改成揭阳
数据备份
注意:数据备份只会备份表的结构和表的数据,约束不会备份.
表级别的备份:
全部备份:create table 新的表名 as select * from 备份表名;
create table deptBak as select * from dept;--全部备份部分备份:create table 新的表名 as select 字段名,字段名,… from 备份表名;
create table deptBak2 as select deptno,dname from deptno;
数据整体插入:insert into 插入表名 select * from 表名;
注意:查询语句的结果的字段数据必须和插入表明的字段数量一致,类型要一致.
insert into deptBak2 select deptno,dname from dept;
注意:增加修改删除数据的SQL语句执行完毕后,不会立马进行数据的写入,还需要手动对数据进行提交,如果数据有问题还可以回滚.
在一张表中,某个字段的值时非空唯一的,将此字段设置为主键.主键的作用是唯一的标识一条数据.
10 多表联合查询
定义:当需要获取的数据分布在多张表中时,考虑使用联合查询.
SQL92多表查询
笛卡尔积:将多个表的数据进行一一对应,所得到的结果为多个表的笛卡尔积;结果的数量为所有表的数量的乘积.
select * from emp,dept;--输出结果为70条数据等值连接:先做表的笛卡尔积,然后筛选,筛选条件为等值筛选.
条件为字段的值相同,但是字段的名字是可以不同的.
--查询员工姓名,工作,薪资,部门名称 select ename,job,sal,dname from emp,dept where emp.deptno = dept.deptno; select ename,job,sal,dname from emp "E",dept "D"where "E".deptno = "D".deptno;--表名可以使用别名可以在select子句中使用字段直接获取数据,但是效率比较低,建议字段前加上表名.如果是公共字段,则必须声明表名.
不等值连接
--查询员工姓名,工作,工资,工资等级. select ename,job,sal,grade from emp "E",salgrade "S" where "E".sal between "S".losal and "S".hisal;自连接:自己连接自己.
--查询员工姓名,工作,薪资和上级领导姓名. select "E1".ename as "员工姓名","E1".job as "工作","E1".sal as "薪资","E2".ename as "领导姓名" from emp "E1",emp "E2" where "E1".MGR = "E2".empno;外连接:加在右边显示左边对应字段没有值的数据,加在左边显示右边没有值的数据.
左外连接:
--查询员工姓名,工作,薪资,部门名称及没有部门的员工信息. select * from emp e,dept d where e.deptno = d.deptno(+);右外连接:
--查询员工姓名,工作,薪资,部门名称及没有员工的部门信息. select * from emp e,dept d where e.deptno(+) = d.deptno;
SQL99多表查询
笛卡尔积:使用cross join 关键字;
语法:select 内容 from 表名1 cross join 表名2;
select * from emp cross join dept;
自然连接
特点:底层先笛卡尔积,然后按照所有的同名同值字段自动进行筛选;注意同名同值.
如果想按照字段名不同,但是值相同进行等值筛选怎么办?
使用using关键字:指明使用指定的字段对联合查询的结果进行等值筛选;指明的字段必须是两表的同名同值字段.
语法:select 内容 from 表名1 inner join 表名2 using(字段名1,字段名2,…);
select * from emp inner join dept using(deptno); --输出结果14行数据
如果想按照部分字段结果进行筛选怎么办?
使用on关键字进行自定义连接条件筛选(等值筛选,不等值筛选),普通筛选条件使用where进行筛选,不要使用on进行性筛选,这样可以使SQL语句可读性增加.
语法:select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
select * from emp inner join dept on emp.deptno = dept.deptno where sal > 2000;
自然连接语法:select 内容 from 表名1 natural join 表名2;
select * from emp natural join dept;--输出结果14行数据
外连接
左外连接:
语法:select 内容 from 表名 left outer join 表名 on 连接条件;
--查询员工姓名,工作,薪资,部门名称及没有部门的员工信息. select * from emp e left joindept d where e.deptno = d.deptno;
右外连接
语法:select 内容 from 表名 right outer join 表名 on 连接条件;
--查询员工姓名,工作,薪资,部门名称及没有员工的部门信息. select * from emp e right join dept d where e.deptno = d.deptno;
全外连接
语法:select 内容 from 表名 full outer join 表名 on 连接条件;
--查询员工姓名,工作,薪资,部门名称,没有部门的员工信息及没有员工的部门信息. select * from emp e full join dept d where e.deptno = d.deptno;
自连接:
--查询员工姓名,工作,薪资和上级领导姓名. select "E1".ename as "员工姓名","E1".job as "工作","E1".sal as "薪资","E2".ename as "领导姓名" from emp "E1" inner join emp "E2" on "E1".MGR = "E2".empno;注意:
- 依然可以给表添加别名;
- 如果使用on或者using关键字对结果进行筛选,必须使用inner join作为表与表的连接,其中inner可以省略不写;
- 外连接outer关键字可以省略不写;
- 依然可以继续使用having,分组,排序等.
SQL92与SQL99的区别:
SQL92易于书写,难以阅读且SQL92的语句结构不清晰,语法:
select 内容(别名/连接符/去除重复/Oracle函数/逻辑运算) from 表名1,表名2,表名3... where 条件(连接条件/普通筛选条件/where子句关键字) group by 分组字段 having 多行函数筛选 order by 排序字段SQL99难以书写,易于阅读,语法:
select 内容 from 表名1 inner join 表名2 on 连接条件 ... where 普通筛选条件 group by 分组 having 多行函数筛选 order by 排序代码案例:
--查询员工姓名,部门名称,工资等级 --SQL99写法: select "E".ename as "员工姓名","D".dname as "部门名称","S".grade as "工资等级" from emp "E" join dept "D" on "E".deptno = "D".deptno join salgrade "S" on "E".sal between "S".losal and "S".hisal; --SQL92写法: select "E".ename as "员工姓名","D".dname as "部门名称","S".grade as "工资等级" from emp "E",dept "D",salgrade "S" where "E".deptno = "D".deptno and ("E".sal between "S".losal and "S".hisal);
11 Oracle子查询
使用时机:当查询的筛选调剂不明确时,考虑使用子查询.
单行子查询
使用时机:筛选条件不明确,需要执行一次查询,并且查询结果只有一个字段且值只有一个数据.
语法:select 内容 from 表名 where 字段名 比较运算符 子查询语句;
注意:\where子句中允许出现查询语句,该查询语句称为子查询.
--查询所有比雇员clark工资高的员工信息. select * from emp where sal > (select sal from emp where ename ='CLARK');
多行子查询
使用时机:子查询的结果只有一个字段但是字段有N个值,考虑使用多行子查询,其实就是使用关键字any
关键字any:任意.
语法:select 内容 from 表名 where 字段名 比较运算符 any 子查询语句
--查询工资高于任意一个CLERK的所有员工的信息 select * from emp where sal >any(select sal from emp where job = 'CLERK');
关键字all:所有的.
语法:select 内容 from 表名 where 字段名 比较运算符 all 子查询语句;
–-查询所有工资高于SALESMAN的员工的信息 select * from emp where sal > all(select sal from emp where job = 'SALESMAN');
关键字in:任意存在
语法:select 内容 from 表名 where 字段名 (not)in 子查询语句;
--查询部门20中和部门10的雇员工作一样的员工的信息. select * from emp where job in (select job from emp where deptno = 10) and deptno = 20;
12 Oracle的账户管理
- 权限:具备某类事务的操作能力,此能力称为权限.
- 角色:一系列权限的集合.
- 特殊账户:
- system账户:管理账户.
- 特点:具备大部分Oracle的操作权限,主要用来管理普通账户及Oracle的数据.
- 使用者:Oracle数据维护工作人员.
- Sys账户:超级管理员账户.
- 特点:具备system的所有权限,同时又具备其他权限.
- 使用者:Oracle工程师,程序员.
- system账户:管理账户.
- 创建账户:
- 使用system账户,并使用dba身份登录管理系统.
- 创建用户:create user 用户名 identified by 密码;
- 维护账户:
- 赋予权限:grant 权限或者角色名 to 用户名;
- grant connect to 用户名;–给用户赋予登录权限
- grant resources to 用户名;–给用户赋予资源操作权限
- grant dba to 用户名;–给用户赋予dba权限
- select * from scott.emp;–查看其他用户的表,使用用户名.表名
- 删除权限:revoke 权限或者角色名 from 用户名;
- revoke dba from 用户名;–移除权限
- 赋予权限:grant 权限或者角色名 to 用户名;
- 删除账户:drop user 用户名;–删除账户
13 二维表管理
13.1 创建表
- 语法:create table 表名(字段名 类型,字段名 类型,…)
- 数据类型:
- number类型:数值类型
- 整数类型:number(a):总长度为a.
- 浮点数:number(a,b):总长度为a,小数位长度为b,小数位可以不写.
- varchar2类型:字符类型
- varchar2(ln):ln表示字符的最大长度,实际存储内存长度是根据字符大小来匹配,但是最大不能超过ln.
- char类型:字符类型
- char(ln):不滚字符数据长度是多大,直接开辟ln大小的空间存储数据.
- 特点:存储效率高于varchar2
- date类型:日期类型
- number类型:数值类型
13.2 约束
主键约束:非空唯一.
添加方式:
直接在创建表的字段后使用 primary key;
sId number(10) primary key,在创建表的语句的最后面使用 constraints pk_表名_字段名 primary key (主键列名);
constraints pk_student_sId primary key(sId),在创建表后使用语句:alter table 表名 add constraints pk_表名_字段名 primary key (主键列名);
alter table student add constraints pk_student_sId primary key(sId);
删除主键约束:
语法:alter table 表名 drop constraints 主键的约束名;
alter table student drop constraints pk_student_sId;--删除主键约束
非空约束
添加方式:
直接在创建表的字段后使用not null关键字;
sname varchar2(20) not null,在创建表的语句的最后面使用语句:constraints ck_表名_字段名 check(字段名 is not null)
constraints ck_student_sname check(sname is not null),在创建表后使用语句:alter table 表名 add constraints ck_表名_字段名 check(字段名 is not null)
alter table student add constraints ck_student_sname check(sname is not null);
删除非空约束:
语法:alter table 表名 drop constraints 非空约束的约束名;
alter table student drop constraints ck_student_sname;
检查约束
添加方式:
直接在创建表的字段后使用check(检查条件);
sage number(3) check(sage > 0 and sage < 150),在创建表的语句的最后面使用语句:constraints ck_表名_字段名 check(检查条件);
constraints ck_student_sage check(sage > 0 and sage < 150),在创建表后使用语句:alter table 表名 add constraints ck_表名_字段名 check(检查条件);
alter table student add constraints ck_student_sage check(sage > 0 and sage < 150);
删除检查约束:
语法:alter table 表名 drop constraints 检查约束的约束名;
alter table student drop constraints ck_student_sage;
唯一约束
添加方式:
直接在创建表的字段后使用unique关键字.
sqq number(30) unique,在创建表的语句的最后面使用语句:constraints un_表名_字段名 unique(字段名);
constraints un_student_sqq unique(sqq),在创建表后使用语句:alter table 表名 add constraints un_表名_字段名 unique(字段名);
alter table student add constraints un_student_sqq unique(sqq);
删除唯一约束:
语法:alter table 表名 drop constraints 唯一约束的约束名;
alter table student drop constraints un_student_sqq;
外键约束
概念:当一张表的某个字段的值需要依赖另外一张表的某个字段的值得时候,使用外键约束;其中主动依赖的表称为子表,被依赖的表称为父表,外键加在子表中.
作用:当在子表中插入的数据在父表中不存在,就会自动报错.
外键的选取:一般选取父表的主键用作子表的外键.
外键的缺点:无法直接删除父表数据,除非级联删除.
级联删除:在添加外键约束时使用关键字 on delete cascade
- 特点:当删除父表数据时,自动删除子表相关所有数据.
- 缺点:无法保留子表历史数据,可以使用关键字on delete set null:
- 特点:删除父表数据时,将子表中的依赖字段的值设置为null,
- 注意:子表依赖字段不能添加非空约束.
添加方式:
在子表中的字段后直接使用语句:references 父表名(字段);
cid number(10) references clazz(cid)在创建表语句的最后面使用语句:constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
constraints fk_student_cid foreign key(cid) references clazz(cid);在创建表后使用语句:alter table 表名 add constraints fk_子表名_字段名 foreign key(字段名) references 父表名(字段名)
alter table student constraints fk_student_cid foreign key(cid) references clazz(cid);
删除外键:
语法:alter table 表名 drop constraints 外键约束名;
alter table student drop constraints fk_student_cid;
13.3 修改表
添加新的字段:
语法:alter table 表名 add 字段名 类型;
-–在学生表中添加新的字段. alter table student add sphone number(11);
修改原有字段:
修改字段类型:
语法:alter table 表名 modify 字段名;
alter table student modify sphone varchar2(11);
修改字段名:
语法:alter table 表名 rename column 字段名 to 新的字段名;
alter table student rename column sphone to phone;
删除字段:
语法:alter table 表名 drop column 字段名;
alter table student drop column phone;
修改表名:
语法:rename 原有表名 to 新表名;
rename student to student2;
13.4 删除表
语法:drop table 表名;
drop table student;
14 Oracle序列
创建序列:
语法:
- 创建默认序列:create sequence 序列名;
- 创建自定义序列:create sequence 序列名 start with 初始值 increment by 增长值
特点:
- 默认开始是没有值的,也就是指针指在了没有值得位置;
- 序列名.nextval每次执行都会自增一次,默认步长为1;
- 序列名.currval可以查看当前序列的值,开始时默认是没有值的.
--创建默认序列 create sequence cc; select cc.curval from dual;--查看序列的当前值 select cc.nextval from dual;--查看序列自增后的值 --创建自定义序列 create sequence aa start with 100 increment by 5;--从100开始,每次自增5作用:作为主键使用,动态地获取数据的值,这样新增数据的时候极大地避免了主键的冲突.
使用:序列名.nextval作为主键值,如:
create sequence cc; insert into teacher values(cc.nextval,'小狗狗');
注意:主键是非空唯一就可以了,不需要主键的值是连续的.
删除序列:
语法:drop sequence 序列名;
drop sequence aa;
15 Oracle视图
特点:
保护真实表,隐藏重要字段的数据,保护数据;
在视图中的操作会映射执行到真实表中;
可以手动开启只读模式:
使用关键字with read only.
create view tea as select tname from teacher with read only;--视图为只读模式
注意:视图的创建必须拥有dba权限.
创建视图:
语法:create view 视图名 as select 对外提供的内容 from 真实表名;
create view empview as select empno,ename from emp;
删除视图:
语法:dropp view 视图名;
drop view empview;
16 Oracle索引
作用:提升查询效率
特点:显式地创建,隐式地执行,Oracle会自动给表的主键创建索引.
创建索引:
语法:create index 索引名 on 表名(字段名);
create index index_teacher_tname on teacher(tname);
删除索引:
语法:drop index 索引名;
drop index index_teacher_tname;
17 Oracle分页查询
为什么要有分页查询?
当一个表中的数据量特别大的时候,如果一次性全部显示给用户,则会造成页面过于庞大,体验极差,此时可以使用分页查询.
rownum关键字:
Oracle对外提供的自动给查询结果编号的关键字,与每行的数据没有关系.
rownum关键字只能做<或者<=的判断,不能进行>或者>=的判断.
--查询员工信息的第6-10条数据 select * from (select rownum r,e.* from emp e where rownum <= 10) t where t > 5;
分页规律总结:
- 每页显示m条数据,查询第n页数据:select * from (select rownum r,表名.* from 要分页的表 where rownum <= mn) t where r > m(n-1);
- 注意:要分页的表既可以是一个真实的表,也可以是一个查询语句.
18 一些题目
--显示正好为5个字符的员工的姓名.
select * from emp where length(ename) = 5;
--显示满36年服务年限的员工的姓名和受雇日期.
select ename,hiredate from emp where floor( (sysdate - hiredate) / 365 ) > 36;
--显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select ename as "姓名",hiredate as "受雇日期",floor((sysdate - hiredate) / 365 ) as "服务年限"
from emp order by "服务年限" desc;
--显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
select ename as "姓名",to_char(hiredate,'yyyy') as "加入公司的年份",
to_char(hiredate,'mm') as "加入公司的月份" from emp
order by "加入公司的月份" asc,"加入公司的年份" asc;
--显示在(任何年份的)2月受聘的所有员工.
select * from emp where to_char(hiredate,'mm') = 02;
--显示在一个月为30天的情况所有员工的日薪金,忽略余数.
select ename as "姓名",trunc( ( sal + nvl(comm,0) )/30,0 ) as "日薪" from emp;
--以年月日的方式显示所有员工的服务年限.
select floor(months_between(sysdate,hiredate) / 12) as "年",
floor(mod(months_between(sysdate,hiredate),12)) as "月",
loor(mod(sysdate-hiredate,30)) as "日" from emp;
--查询员工"KING"和"FORD"管理的员工信息及其领导信息.
select "E1".ename as "员工","E2".ename as "领导","E3".ename as "下级"
from emp "E1",emp "E2",emp "E3"
where "E1".mgr = "E2".empno(+) and "E3".mgr = "E1".empno
and "E1".ename in('KING','FORD');
--列出各种工作的最低工资以及从事此工作的雇员姓名.
select "X".job as "工作","X"."最低工资","E".ename as "雇员姓名"
from emp "E",(select min(sal) as "最低工资",job from emp group by job) "X"
order by "X"."最低工资";
--列出所有部门的详细信息和部门人数.
select * from dept "D"
join (select count(*) as "部门人数","D".deptno as "部门编号" from emp "E",dept "D"
where "E".deptno(+) = "D".deptno group by "D".deptno) "M"
on "D".deptno = "M"."部门编号";
--查询员工信息:员工的名字,老板的名字.
select "E".ename as "员工名字","M".ename as "老板名字"
from emp "E",(select *from emp where mgr is null) "M";