朋友工作的原因，端午节也没闲着，陪这个祸害整理了一下数据库的 sql 指令，并且整理配套的习题进行练习使用，我正好也没啥事，疫情的原因啥也干不了，就陪他整理一波，顺便薅一波羊毛，嘿嘿嘿

不过，因为真的是文字太多了，所以只展示一部分，需要完整文档以及 sql 文件的，关注公众号：Java 架构师联盟，后台添加小助手获取吧

第一天：

第一节课：数据库基本概念学习

1、数据 书

其实就是表示信息的一种特殊符号的集合

例如：图片、txt、音频、视频等等

引入：互联网的发展交互的其实就是数据

2、数据库：图书馆

把用来存储数据的东西称之为数据库。

3、数据库管理系统 应用程序 图书馆管理系统

将数据按照指定的方式存储到数据库中的系统称之为数据库管理系统

可以高效率的取出存储好的数据（重点）

数据库应用系统 —- 给普通用户使用的，像淘宝网等

4、数据库系统

数据库管理员

数据库

数据库管理系统

数据库应用系统

用户

5、

oradata：数据库相关信息

product：数据库管理系统的相关信息

bin：数据库管理系统软件的启动目录

jdbc：数据库和 java 连接所需的 jar 包

network：数据库管理系统所需网络配置目录

log 目录：oracle 异常日志信息存储目录

第二节课：常见数据库及数据库存储结构

1、数据库存储数据结构

网状结构

层次结构

二维表结构：

二维表结构可以清晰的将数据与数据之间的关系表述清楚

注意：

单纯的存储数据很简单，但是存储好的数据又要将数据之间的关系描述清楚

就会比较困难，所以采用二维表的结构存储数据会比较合理

2、数据库管理系统

oracle: 大型数据库管理系统

mysql：中小型

DB2：中小型

Acess：小型

Sql Server：大型数据库

…..

3、oralce 的历史介绍 参照 PPT

第三节课：oralce 的安装及 oracle 目录结构介绍及卸载

1、oracle 的安装：

参照百度搜索“oracle11g 安装图解”

全局数据库名: 默认是 orcl，是用来唯一标示数据库的名字

SID：在程序中唯一标示数据的名字

2、oracle 的服务：（需要开启的服务）

orcaleServiceorcl:orcale 启动服务

OracleOraDb11g_home1TNSListener:oracle 监听器服务

OracleDBConsoleorcl: 控制台服务

3、oracle 账户：

sys: 超级管理员

system：管理员

scott：普通用户 默认密码：root

4、oracle 修改密码：

在 cmd 命令行中使用命令：sqlplus / as sysdba

然后使用命令：

alter user 用户名 identified by 新密码;

在 cmd 命令行中使用命令：sqlplus /nolog

然后使用命令：conn as sysdba

然后输入一个已知的用户名和密码

然后使用命令：

alter user 用户名 identified by 新密码;

5、新建用户：

必须使用 System 账户

第四节课：oracle 目录及卸载

1、oracle 的目录介绍：

oradata：数据库存储文件的目录

db_home:

network >admin: 配置网络服务和监听器服务

jdk:oracle 自带 jdk

deinstall: 卸载命令

jdbc: 与 java 交互的 jar 包

2、oracle 的卸载：

使用 oracle 自带的程序卸载

删除 app 目录

删除注册表

删除环境变量

第四节课：oracle 的监听器及服务器配置

问题 1：

我的电脑装了 oralce，同桌怎么查看数据？

客户端：

针对普通用户使用的软件，主要是用来发送用户的请求。

服务器：

接受用户的请求，并处理用户的请求。

问题 2：张三和李四都装了 oracle，王五装了客户端，王五要访问张三，怎么访问？

ip

tcp

数据库全局名

端口号

问题 3：

从服务器的角度观察，假如 1000 个请求过来，怎么区分那些是请求数据库的

监听器

第三方工具：plsql（不是客户端）

配置本地网络服务:(客户端)

第一种使用暴力方式直接操作：

修改：C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

ORCL = > 网络服务名

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 服务器 ip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

第二种方式：

使用图形界面：NCA

配置监听器:（服务器端）

常见问题：

配置监听器，计算机名必须是英文的

第一天的主要任务是安装数据库和配置数据库的网络服务，第二天正式进行 sql 语句的学习

===========================================================================================================================================

第二天：

第一节课：sql 语言介绍

1、数据库表的介绍

emp 表：员工表

dept 表：部门表

salgrady：薪资水平表

Balance：

2、基本的查询语句：

知识点：

select * from 表名 查询表中所有的数据

select 字段名 1，字段名 2，..from 表名 查询指定字段的所有值，不同字段之间使用逗号隔开

使用算术表达式：select 字段名 (运算符），字段名 ( 运算符）…from 表名

案例：

select * from emp— 查询所有员工信息

select ename,job,sal from emp– 查询员工姓名，工作，薪资

查询所有员工一年的工资，以及每位员工年终 3000 后的所有薪资（不包括津贴）

select ename,job,sal*12,sal*12+3000 from emp 使用运算符

第二节课：

1、使用别名

as 可以省略不写，如果有特殊字符获取空格使用双引号添加别名

select ename 姓名,job 工作,sal*12 年资,sal*12+3000 年薪 from emp 直接在字段后面添加别名

select ename “ 姓名 ”,job 工作,sal*12’年资’,sal*12+3000 年薪 from emp 使用双引号添加别名, 不能使用单引号

select ename as 姓名,job “ 工作 ”,sal*12 年资,sal*12+3000 年薪 from emp 使用 as 关键字添加别名

select ename “ 姓 名 ”,job 工作,sal*12 年资,sal*12+3000 年薪 from emp 如果别名中有空格或者其他特殊字符，使用双引号

2、使用链接符：

select ename,job,sal from emp;

select ename||job,sal from emp; 使用 || 符号进行字符链接

select ename||’ 的工作是 ‘||job||’ 并且月工资是 ‘||sal from emp; 字符链接，普通字符使用单引号

3、去除重复 distinct

select * from emp;

select job from emp– 发现工作是重复的，而工作种类是需要去除重复的

问题：查看员工工作种类

select distinct job from emp 使用 distinct 关键字去除重复值

问题；怎么多字段去除重复

select distinct job,sal from emp; 多字段去除重复，去除的是重复的一整条信息

4、排序

知识点：

select 字段名，字段名 …from 表名 order by 字段名

select *from 表名 order by 字段名

讲解：

– 查询员工姓名，工作，薪资

select ename,job,sal from emp

– 查询按照薪资排序的员工姓名，工作，薪资

select ename,job,sal from emp order by sal; 使用 order by 排序

select ename,job,sal from emp order by sal desc; 使用 desc 关键从大到小排序

select ename,job,sal from emp order by sal asc; 使用 asc 关键从小到大排序，默认是从小到大排序

select * from emp order by sal,ename 多字段作为排序条件的时候，优先按照第一个字段排序，然后依次按照其他字段排序。

select ename,job,sal 工资 from emp order by 工资 使用别名

select ename,job,sal from emp order by sal*12 使用算术表达式

第三节课：简单的 where 子句

使用 where 条件进行结果筛选

语法：select * from 表名 where 条件；注意：条件中字段值区分大小写，字段名不区分大小写，字段值使用单引号括起来

1、在 where 中使用算术表达式 = ,< , > ,>= ,<= ,<>

2、使用 order by 对筛选结果进行排序，order by 出现在 where 后面

查看工资等于 1250 的员工信息

select * from emp where sal=’1250′– 筛选条件是个数字也可以使用单引号

查看工作等于 CLERK 的员工信息

select * from emp where job=’CLERK’– 在筛选条件中字段值如果是字符需要加上单引号

select * from emp where job=’clerk’– 在 sql 语句中字段的值是区分大小写的

select * from emp where JOB=’CLERK’– 在 sql 中字段是不区分大小写的

查看工资大于 1250 的员工姓名和工作

select ename,job ,sal from emp where sal>’1250′ order by sal– 使用 order by 对筛选结果进行排序，order by 出现在 where 后面

查看工资大于等于 2000 的员工信息

select * from emp where sal>=2000;

查看工资小于等于 2000 的员工信息；

select * from emp where sal<=2000

查看工资不等于 1500 的员工信息

select * from emp where sal<>1500 order by sal

查看入职日期在 81 年后的员工信息

select * from emp where hiredate>’1981 年 12 月 31 号 ’

select * from emp where hiredate>’1981/12/31′

select * from emp where hiredate>’31-12 月 -1981′– 使用日期的默认格式查询符合要求的数据，日 - 月 - 年

第四节课：where 子句使用关键字

使用 where 子句进行结果的筛选

知识点：where 查询条件中使用关键字

1、and 用于多条件的与筛选：select * from 表名 where 条件 and 条件 and 条件 ….

2、or 用于多条件的或筛选：select * from 表名 where 条件 or 条件 or 条件 ….

3、in 用于多条件的或筛选：select * from 表名 where 字段名 in(值，值，值 ….)

4、like 用于模糊查询：select * from 表名 where 字段名 like ‘% 值 %’ 包含

5、is null 和 is not null 用来判断字段是否为空 select * from 表名 where 字段名 is null

讲解：在 where 子句中使用关键字（and, or ,like, is null,is not null,between and,）

– 查询工资在 2000-3000 之间的员工信息

select * from emp where sal>=2000 and sal<=3000– 使用 and 关键字进行 ” 与 ” 的多条件筛选；

select * from emp where sal between 2000 and 3000;– 使用 between and 关键字进行筛选；

– 查询工作为 SALESMAN,ANALYST,MANAGER 的员工信息

select * from emp where job=’SALESMAN’ or job=’ANALYST’ or job=’MANAGER’– 使用 or 关键字进行 ” 或 ” 的多条件筛选

select * from emp where job in(‘SALESMAN’,’ANALYST’,’MANAGER’);– 使用 in 关键字进行 ” 或 ” 的多条件筛选

select * from emp where job=’ANALYST’

– 查询姓名中包含 s 的，以 s 开头的，以 s 结尾的，第二个字符为 A 的。

select * from emp where ename like ‘%S%’;– 使用 like 关键字，姓名中包含 S 的,% 代表任意多个字符

select * from emp where ename like ‘S%’;– 使用 like 关键字，以 S 开头的

select * from emp where ename like ‘%S’;– 以 S 结尾的

select * from emp where ename like ‘_A%’– 使用 ”_” 指定位置包含指定字符的信息，”_” 代表任意一个字符

————– 查询名字中包含下划线的用户信息

select * from emp where ename like ‘%A_%’escape ‘A’;– 使用 escape 关键字将普通字符设置成为转译字符。

– 查询有津贴的员工信息

select * from emp where comm is not null;

select * from emp where comm is null;– 查询没有津贴的员工信息

第五节课：函数学习

– 查询工作为 SALESMAN，MANAGER 并且工资大于 2500 的员工信息

–1、使用小括号提升 where 筛选条件的执行优先级别

–2、and 的优先级别高于 or

select * from emp

select * from emp where job=’SALESMAN’ or job=’MANAGER’ and sal>2500

select * from emp where (job=’SALESMAN’ or job=’MANAGER’) and sal>2500

———————————————————————————————————–

使用函数 单行函数 多行函数 转换函数 其他函数

– 单行函数学习：不改变原始数据，只改变结果

—1、字符函数

select * from emp;

select INITCAP(ename) from emp;–initcap 函数将首字母大写

select lower(ename)from emp;–lower 字母小写

select replace(ename,’S’,’M’) from emp;–replace 替换

–2、数值函数 –Math

—- 伪表 dual

select * from dual

select abs(-3) 绝对值，ceil(3.1415926) 向上取整,floor(3.1415926) 向下取整,power(2,3) 幂,round(3.4) 四舍五入 from dual

–3、日期函数

select months_between(’13-12 月 -2016′,’13-10 月 -2016′) from dual–months_between 两个日期之间的月份数

第六节课：多行函数学习

多行函数：

max: max(字段名) 返回此字段的最大值

min：min(字段名) 返回此字段的最小值

avg：avg(字段名) 返回平均值

sum：sum（字段名）返回字段的和

count:count

–count(*)，用来查询表中有多少条记录

–count（字段名），用来查询某个字段有值的个数

–count(distinct 字段名), 可以先去除重复再计数。

注意：

– 多行函数不能和普通字段直接出现在查询语句中，除非 group by

– 多行函数和单行函数不能直接出现在查询语句中，除非 group by

查看员工的最高工资

select max(sal),ename from emp– 多行函数不能和普通字段直接出现在查询语句中，除非 group by

select max(sal)，lower(ename) from emp– 多行函数和单行函数不能直接出现在查询语句中，除非 group by

select ename from emp

查看员工的最低工资

select min(sal) from emp

查看员工的平均工资

select avg(sal) from emp

查看所有的员工工资之和

select sum(sal) from emp

查询公司有多少员工

select * from emp

select count(*) from emp– 使用 count(*) 来查看一张表中有多少条记录

查询有津贴的员工人数

select count(comm) from emp– 使用 count(字段名), 查询该字段有值的记录数

select count(ename) from emp

查询公司有多少工作种类

select count(job) from emp

select count(distinct job) from emp

========================================================================================================================================

第三天：

第一节课：转换函数学习

转换函数: 在转换的时候改变的是数据的类型，数据内容不会改变，可以指定格式。

1、to_number：将数字字符转换为数字类型的数值，to_number(数字字符)

2、to_char：将数字转换为字符类型，将日期转换为字符类型 to_char(数字 / 日期)

3、to_date：将字符类型的日期转换为日期类型：to_date(char)

————————————————————————————–

number–>char 转换的时候使用的是默认格式，

select to_char(123) from dual– 将数字转换为字符类型，使用 to_char(number)

select sal, to_char(sal) from emp

number–>char 使用指定的格式

to_char(number,’ 格式 ’)，格式：

$ 代表美元符，9 代码数字占位。例如，L999,999,999 表示三位一组的显示方式 L 代表人民币符号，0 代表保留数字个数，不足使用 0 补充。

select to_char(sal),to_char(sal,’L999,999,999′) from emp

select to_char(sal),to_char(sal,’L0000.00′) from emp

char—>number to_number(数字字符)

select to_number(‘123’) from dual

char—>date，转换的字符必须是日期格式的字符串，默认格式 dd-mm-yyyy

注意：因为字符串有很多，所以在字符转换为日期的时候，需要指定格式，因为日期是具备一定格式的字符组合。

字符转换为日期的时候，指定的格式为字符的日期顺序，无需指定间隔符。

yyyy：表示年 mm: 表示月 dd 表示日

select to_date(’05-12 月 -2016′) from dual;

select to_date(‘2016-05-12′,’yyyy-mm-dd’) from dual– 使用指定的格式将指定的日期字符串转换为日期

select to_date(’12-05-2016′,’mm/dd/yyyy’) from dual

date—>char

注意：因为日期本身就具备一定的格式在不是指定格式的情况下会默认使用 dd-mm-yyyy 格式显示数据

指定的格式会作为日期转换为字符串类型的显示格式存在。例如：

yyyy-mm-dd’‘2016-12-05’

yyyy/mm/dd’ ‘2016/12/05’

yyyy” 年 ”mm” 月 ”dd” 日 ”‘ 2016 年 12 月 05 日

select hiredate,to_char(hiredate) from emp– 日期转换为字符的时候，不指定格式使用默认格式：dd-mm-yyyy

select hiredate,to_char(hiredate,’yyyy-mm-dd’) from emp;– 使用指定格式将日期转换为字符串类型

select hiredate,to_char(hiredate,’yyyy/mm/dd’) from emp;– 使用指定格式将日期转换为字符串类型

select hiredate,to_char(hiredate,’yyyy” 年 ”mm” 月 ”dd” 日 ”‘) from emp;– 使用指定格式将日期转换为字符串类型

查询入职日期在 81 年 10 月 20 日后的员工信息

第一种：自动转型

select * from emp where hiredate>’20-10 月 -1981′

第二种：将日期转换为字符串

select * from emp where to_char(hiredate,’yyyy-mm-dd’)>’1981-10-20′

第三种：

select * from emp where hiredate>to_date(‘1981-10-20′,’yyyy/mm/dd’)

第二节课：其他函数：

单行函数：lower

多行函数:min max sum avg count

转换函数:to_number to_char to_date

—————————————————————————————–

其他函数：

1、nvl():nvl( 字段名，执行)– 相当于 java 中的 if 条件判断

2、nvl2():nvl2( 字段名，值，值)– 相当于 java 中的 If(){}else{} 判断

3、decode():decode( 字段名，条件 1，执行内容 1，条件 2，执行内容 2，条件 3，执行内容 3，默认执行内容)

相当于 java 中 if(){}else if(){}else if(){}…else{}

查询所有员工的月薪及姓名和工作

select * from emp

select ename,sal 基本工资,comm 绩效,sal+comm 月薪 from emp

select ename,sal 基本工资,comm 绩效,sal+nvl(comm,0) 月薪 from emp

查询所有员工的月薪及姓名和工作

select ename,sal 基本工资,comm 绩效,nvl2(comm,sal+comm,sal) 月薪 from emp

显示员工的职称

select ename,job,decode(job,’MANAGER’,’ 经理 ’,’SALESMAN’,’ 销售人员 ’,’ 普通员工 ’) from emp

第三节课：使用 group by 分组

在多行函数中不能直接使用普通字段，除非 group by

在多行函数中不能直接使用单行函数，除非 group by

group by 学习：

—1、使用 group by 进行数据分组 select 多行函数，分组字段 from 表名 group by 分组字段

—2、多字段进行分组的时候，按照字段顺序进行分组，第一条件分组完成后，继续使用其他条件依次分组。

—3、group by 依然可以和 order by 联合使用

—4、可以和单行函数联合进行分组，注意使用了单行函数那么在查询语句中必须也要使用

查询最高工资和员工数

select max(sal),count(*) from emp

查询不同部门的最高工资

select * from emp order by deptno

select deptno,max(sal) from emp group by deptno– 使用 group 进行分组查询，分组的字段可以出现在查询中，其他字段依然不可以

查询不同工作岗位的员工数

select * from emp for update

select lower(job),count(*) from emp group by lower(job)– 使用单行函数进行分组

查询不同部门的不同工作岗位的人数

select deptno,job ,count(*) from emp group by deptno,job– 使用多字段组合进行分组

select deptno,job ,count(*) from emp group by deptno,job order by deptno

查询不同部门的不同工作岗位的并且人数大于 1 的信息 t count(*) from emp where count(*)>3 group by deptno

selec

select deptno,job ,count(*) from emp where count(*)>1 group by deptno,job order by deptno

查询部门号大于 10 的不同部门的不同工作岗位的人数

select deptno,job ,count(*) from emp where deptno>10 group by deptno,job order by deptno

使用 having 进行分组后筛选

having 学习：

–1、使用 group by 分组后在进行数据筛选的时候，where 中不能出现多行函数，所以使用新的关键字 having 进行条件筛选

–2、where 条件筛选的执行顺序：from–>where—>group –>select

–3、having 条件筛选的执行顺序：from–>group by –>having–>select

–4、where 的执行效率比 having 要高，能使用 where 的情况下尽量不要使用 having

查询不同部门的不同工作岗位的并且人数大于 1 的信息

使用 where 语句进行筛选

where 条件语句 sql 执行顺序：from–>where—>group –>select

select count(*) from emp where count(*)>1 group by deptno，job

使用 having 语句进行筛选

having 条件语句的执行顺序:from–>group by –>having–>select

select deptno, count(*) from emp group by deptno having count(*)>5

select deptno,job ,count(*) from emp group by deptno,job having deptno>10 order by deptno

第四节课：插入数据学习及数据的备份

单表查询语句（select）

1、插入数据 (insert)

1、语法规范 insert into 表名 (字段 1，字段 2，字段 3，….)values(‘ 值 1 ′,’ 值 2 ’,’ 值 3 ’…..)

2、主键：用来唯一标识一条数据的字段通常设置主键，主键是唯一不可以重复的

3、如果插入的数据是全字段数据，字段可以省略不写。部分字段，必须加上字段说明和字段值，但是主键不能为空

4、事务的提交：如果一个事件是由多个动作组成，只要有一个动作没有执行成功则自动将数据回滚到原始状态，此们技术称之为事务

保证数据的安全和完整

事物的提交：

使用第三放插件的提交按钮

使用 commit 语句

5、增加删除修改的数据 sql 语句执行完毕后，不会立马进入数据的写入

需要手动的对数据进行提交，如果数据有问题还可以回滚

select * from dept for update

在北京新建了一个名为 LOL 学院的新部门，请插入

insert into dept(deptno,dname,loc)values(’50’,’lol 学院 ’,’ 北京 ’);

主键是唯一不可以重复的

insert into dept(deptno,dname,loc)values(’50’,’ 教学部 ’,’ 北京 ’);

如果插入的数据是全字段数据，字段可以省略不写。部分字段，必须加上字段说明和字段值，但是主键不能为空

insert into dept values(’80’,’ 教学部 ’,’ 北京 ’);

insert into dept values(’90’,’ 教学部 ’,’ 北京 ’);

insert into dept values(100′,’ 教学部 ’,’ 北京 ’);

insert into dept values(‘110′,’ 教学部 ’,’ 北京 ’);

2、创建数据的备份

（1）create table 表名 as 查询语句，创建的是和查询结果一样的表，查询结果是什么就会备份一个相同的表

（2）insert into 表名 查询语句，注意：查询出来的结果在结构上必须和插入数据的表相同，字段个数必须相同

（3）注意：备份表只有字段和数据相同，并不会备份约束。

1、备份完整的数据和表

select * from dept;

create table tdept as select * from dept;– 备份 dept 表和数据，只能备份数据和字段

select * from tdept

2、备份完整表

create table tdept1 as select * from dept where 1>2– 备份表，不备份数据。

3、备份部分数据和表.create table 表名 as 查询语句，创建的是和查询结果一样的表，查询结果是什么就会备份一个相同的表

create table tdept2 as select dname,loc from dept

select *from tdept2

4、给备份表添加数据 insert into 表名 查询语句，注意：查询出来的结果在结构上必须和插入数据的表相同，字段个数必须相同

select * from tdept1

insert into tdept1 select dname,loc from dept where deptno>40

select *from tdept2

insert into tdept2 select dname,loc from dept where deptno>40

第五节课：数据的更新和删除：

更新数据:update

语法结构：update 表名 set 字段名 1 =’ 字段值 1 ′, 字段名 2 =’ 字段值 2 ’，…where 条件

将部门 70 的名字改为教学部 2

update dept set dname=’ 教学部 2 ′ where deptno=’70’

update dept set dname=’ 教学部 2 ′,loc=’ 上海 ’ where deptno=’70’

select * from dept

删除数据：delete

1) 删除语句：delete 表名 where 条件

– 删除部门标号为 70 的数据

delete dept where deptno=’70’— 删除指定数据

select * from dept

delete tdept— 清空表数据

truncate table tdept— 清空表数据建议使用 truncate 关键字，但是此关键字不能回滚数据

===================================================================================================================================

关注公众号：Java 架构师联盟，后台回复 mysql 获取数据库相关资料