目录
三.Sql(Structured Query Language)讲解
一.数据库管理系统概念
(DBMS database Management System)
- 概念: 数据库管理系统是将数据以某种特定的数据格式 进行组织存储 和 管理。
- 数据库的分类:
- 关系型数据库 : 是以二维表的方式存储数据,而表与表之间的数据存在着交叉引用的关系。
--常见的关系型数据库:Oracle \ SqlServer \ Mysql \ DB2
- 非关系型数据库: 是针对于处理海量数据 和 复杂的数据结构存储而诞生。
-- 常见的非关系型数据库:Redis / MongoDB / HBase
二.Oracle数据库
- 基本概念:
- 表(table) : 是存储数据的基本单元
- 行 (row) : 也称为entity(实体) , 是表中存储的一条实际的数据
- 列 (column) : 也称为 field (字段),代表的是一条实际数据的一个属性
- 主键 (primary key) : 主键用于唯一的标识表中的一行数据 。
- 安装Oracle数据库:
注意: 安装目录不要有中文, 建议放在磁盘的根目录下
卸载Oracle需要使用控制面板的卸载程序 或 运行Oracle的安装程序,按照提示操作
- 关于Oracle的服务操作:
进入到系统服务列表: 在开始 -- 运行 -- services.msc
OracleServiceXE : 是oracle数据库的核心服务, 必须保证正常启动才能使用Oracle.
OracleXETNSListener : Oracle远程访问的服务监听, 必须开启才能通过网络远程访问.
注意:
当无法访问Oracle时, 检查两个服务是否正常启动, 如果启动的情况下还是访问不了可以重启服务。
先操作OracleXETNSListener 然后操作 OracleServiceXE
- 访问Oracle
1》 SqlPlus : 是由 Oracle系统提供的一种基于命令窗口的访问方式 cmd --> sqlplus
2》 Isqlplus : 是由Oracle系统提供的,基于浏览器的访问方式。
http://IP:8080/apex 比如: http://127.0.0.1:8080/apex 或 http://localhost:8080/apex
注意: 在Oracle11g 之后没有提供浏览器的访问方式。 目前我下载的是Oracle10g 可以使用
3》 使用第三方客户端访问 plsqlDevelopor :
- 基本命令
1》 查看指定表的结构: desc 表名
2》 解锁账户 hr : alter user hr account unlock; // 学习使用的账户
3》 数据库密码忘记操作:
开始 --> sqlplus --> conn /as sysdba / 以管理员身份登录, 仅限于本地使用
修改密码: alter user 用户名 identified by “密码”;
三.Sql(Structured Query Language)讲解
- Sql全称结构化查询语言,是一种在关系型数据库下使用的一种标准查询语言。可以在Oracle mysql sqlServer等关系型数据库中使用。
- 基本的查询
- 语法: select 字段1,字段2 from tabName;
- 说明:
from 后面指定表名
select 后面指定字段名
PS: 书写查询语句的思路 首先要确定从哪张表查询 , 其次确定查询表中的哪些字段
每一条sql语句结束,加上一个 ; 号, 告诉编辑器您写完了
命令的关键字不区分大小写, 数据库最终执行之前都会把sql转成大写的。
案例:
- 查询部分字段 :
-- 查询员工表的编号、first_name、 last_name、工资
select employee_id,first_name,last_name,salary from employees;
- 查询所有字段:
-- 查询员工表所有字段的信息
select * from employees; | * 是一个通配符,代表所有字段
PS: 注意 * 在执行阶段,oracle系统会将其解析成表中的实际字段之后,再去执行命令。在实战开发中不建议使用*的方式,效率低。
- 允许对查询结果的字段做算数运算 + - * /
-- 查询所有员工的年薪
select first_name,last_name,salary*12
from employees;
PS: 只有number类型的字段能做算数运算
注意对于+ 号在oracle里只是算数运算,不能做字符串拼接
日期类型也可以做算数运算, 只能做 + - 运算, 运算的单位是天
- 字段起别名
-- 查询所有员工的年薪 | 字段名展示位 ”年薪“
Select first_name,last_name,salary*12 as “年薪”
From employees;
PS: 关于别名加双引号和不加双引号 的区别 : 如果别名为英文字符, 加双引号严格区分大小写,不加双引号不区分大小写
- 字符串拼接 : 字符串1||字符串2 ==》 字符串1字符串2
-- 查询所有员工的姓名 | 展示的格式为 Steven_King
Select first_name||’_’||last_name
From employees;
PS:双引号里面的内容,出现在表头部分 单引号里面的内容,出现在表的结果集中
- .排序查询 -- order by 【重点】
语法 : select 字段1,字段2.. From tabName order by 字段 asc/desc;
说明:
asc[默认] 代表升序 | desc 代表降序
- 案例 :
-- 查询所有员工的信息, 按照工资的升序排列(从小到大)
select * from employees order by salary asc;
-- 查询所有员工的信息, 按照工资的降序排列 | 如果工资相同则按照员工编号的升序排列
Select * From employees order by salary desc ,employee_id asc;
PS: asc/desc 作用于前面的字段
-- 查询所有员工的信息,按照提成 降序排列展示
select * from employees order by commission_pct desc;
PS: 在oracle系统中 null 值最大 .
- 条件查询 -- where 【重点】
语法 : select 字段1,字段2... From tabName where 条件 order by 字段 asc/desc;
说明:
Where筛选数据的方式,会到指定的表中逐行进行筛选
案例:
- 等值查询 : = !=(<>)
-- 查询员工工资为24000的人员信息
Select *
From employees where salary=24000;
-- 查询员工工资不等于24000的人员信息
Select *
From employees where salary <> 24000;
-- 查询last_name 为King的人员信息
Select *
From employees where last_name=’King’;
PS: 对于where条件中的字符串使用单引号
- 关系比较 : > >= < <=
-- 查询员工工资大于10000的人员信息
Select *
From employees where salary > 10000;
- 逻辑运算 : and(并且) or(或者)
-- 查询工资 大于8000 小于12000的人员信息
Select *
From employees where salary>8000 and salary <12000;
- 区间查询 : between ... and
-- 查询工资 8000到12000之间的人员信息 | 包括8000和12000的人员
Select *
From employees where salary between 8000 and 12000; | 包含边界值
- 枚举查询 : in (条件1,条件2...)
-- 查询工资为 2100、8000、12000、24000的人员信息
Select *
From employees where salary in (2100,8000,12000,24000);
- 对null值的处理
-- 查询没有提成人员的信息
Select
From employees where commission_pct is null; | commission_pct=null ERROR! .
-- 查询有提成人员的信息
Select *
From employees where commission_pct is not null;
- 模糊查询 : like 条件 | _ -匹配一个字符 % - 匹配 0-n个字符
-- 查询first_name由4个字符组成的人员信息
Select *
From employees where first_name like ‘____’;
-- 查询first_name以大写字母D开头的人员信息
Select *
From employees where first_name like ‘D%’;
-- 查询first_name以大写S开头的 由5个字符组成的人员信息
Select *
From employees where first_name like ‘S____’;
-- 查询first_name 不包含字母 a的人员信息
Select *
From employees where first_name not like ‘%a%’;
- .查看系统的当前时间
select sysdate from dual;
Select systimestamp from dual; // 时间比较精确, 精确到秒小数后6位
PS: dual(称为 哑表或虚表 )表是oracle数据库系统提供的,旨在维护sql语句的完整性 . 这个表只有一行一列.
四.函数
- 单行函数 【掌握】
- 特点: 单行函数作用于表中的每一行数据产生一个结果.
- to_char : 日期转字符串
语法 : to_char(日期类型,格式化字符串)
常用的格式化字符串:
年: yyyy |
月: MM |
日: dd |
时: HH |
分: MI |
秒: SS |
星期: day |
案例 :
-- 查询今天是星期几
Select to_char(sysdate,’day’)
From dual;
-- 查询当前系统时间 展示 年/月/日 时:分:秒 星期
Select to_char(sysdate,’yyyy/MM/dd HH:MI:SS day’)
From dual;
- to_date : 字符串转日期 | 是将一个字符串转换成oracle系统默认的日期格式展示
语法 : to_date(格式化日期字符串,格式化字符串)
案例:
-- 查询2018年8月24日 是星期几
分析:
Select to_date(‘2018-08-24’,’yyyy-mm-dd’) // 把字符串转换成日期格式
From dual;
实现:
Select to_char(to_date(‘2018-08-24’,’yyyy-mm-dd’),’day’) from dual;
- 几个常用的小函数:
指定小数的精确位数 : round(值, n) | n代表指定值小数位精确位数
取模 : mod(值1,值2) | 返回值1模值2的结果
对于null值处理: nvl(值1,值2) | 如果值1为null,返回值2 , 如果值1不为null返回值1
查询一个指定值的字符长度 :
length(字符串) | 返回字符串的长度 单位是字符
lengthb(字符串) | 返回字符串的长度 单位是字节
PS: 目前我们的oracle系统,一个中文默认占3个字节 .
- 组函数(聚合函数) 【重点】
特点 : 作用于每一组数据产生一个结果 .
常用的组函数:
sum(字段) : 求一组数据的和
avg(字段) : 求一组数据的平均值
max(字段) : 求最大值
min(字段) : 求最小值
PS: sum()与avg() 只能作用于数值类型的字段
max()与min() 可以作用于所有类型的字段 | 没有什么实质的意义
- count(*) : 统计查询
count(参数) : 参数可以是* | 1 | 一个指定的字段
PS: count(字段) 进行统计查询, 会自动忽略null值 。
案例:
-- 求所有员工工资的总和 、平均工资、最高工资、最低工资
Select sum(salary),avg(salary),max(salary),min(salary)
From employees;
-- 查询员工表一共多少条数据
Select count(*) from employees;
Select count(1) from employees;
PS: 以上的两种方式是等效的
-- 统计查询所有有提成人员的数量
Select count(commission_pct) from employees; | count统计时自动忽略null值
- 分组查询 : group by 【掌握】
1. 语法 : select 字段1,字段2 from tabName where ... group by 字段 order by ... ;
2. 说明 :
2.1 只有出现在group by子句中的字段,才能单独出现在select子句中
2.2 如果一个字段没有出现在group by子句,但是还想出现在select子句中,那么必须配合组函数使用 | 只是语法上的,没有实战价 值
2.3 如果group by子句中的字段使用了某个单行函数,那么 select子句中对应也必须使用相同的单行函数
-- 查询员工表各个部门的平均工资 、以及部门编号
分析:
① 确定分组条件 : 部门 department_id
② 对没组数据按需求 ,求结果 : avg(salary)
Select department_id,avg(salary)
From employees group by department_id;
-- 查询1997年各个月份入职的员工人数
分析:
① 筛选出1997年入职的人
② 根据月份进行分组
③ 进行统计
Select to_char(hire_date,’mm’),count(*)
From employees where to_char(hire_date,’yyyy’)=’1997’
Group by to_char(hire_date,’mm’) order by to_char(hire_date,’mm’);
-- 查询统计各个部门各个职位员工的人数
分析:
① 确定分组条件 : department_id job_id
② 统计查询 count(*)
Select department_id,job_id,count(*)
from employees
Group by department_id,job_id;
PS: group by 允许指定多个分组条件 .
- having子句 : 【了解】
- 作用: 进行按条件的筛选数据,类似于where功能
- 语法: select 字段.... from tabName where ... group by ... having ... order by ... ;
- 案例:
-- 查询1997年各个月份入职员工的人数, 人数大于5的月份
Select to_char(hire_date,’mm’),count(*)
From employees where to_char(hire_date,’yyyy’)=’1997’
Group by to_char(hire_date,’mm’) having count(*) > 5;
-- 查询 60 80 90 号部门各个部门的平均工资
① where
Select department_id,avg(salary) from employees where department_id in(60,80,90)
Group by department_id;
② having
Select department_id,avg(salary) from employees group by department_id
Having department_id in(60,80,90);
PS: 如果使用where和having 同时可以解决某个问题,优先选用where .
总结:
Select语句执行顺序:
Select 字段 ... From tabName where ... Group by ... Having ... Order by ...;
- From 确定从哪张表查询
- Where 按照条件初次对数据筛选
- Group by 对筛选的数据进行分组
- Having 对分组后的数据再次进行筛选
- Select 生成结果集
- Order by 对生成的结果集进行排序
五.伪列
- 什么是伪列
伪列是由oracle系统提供的一种列,在表中并不实际存在。 通过select * 是查询不到的,必须手动指定才能查询到。
- 常见的伪列
rowid --> select col,..rowid from tabName;
rownum --> select col...,rownum from tabName;
- 详细讲解
rowid 是一个由oracle系统为表中的每一条记录生成的一个18位的字符,是通过每条数据的物理地址计算得到,
唯一的标识表中的每一条数据。
如果知道一条数据的rowid可以作为查询条件,而且效率较高。
Select employee_id,first_name,last_name ,rowid
From employees where rowid=’AAAC9EAAEAAAABXAAA’;
rownum 是对出现在查询结果中的数据进行排序,永远从1开始。
案例:
- 查询员工表前五名员工
Select employee_id,first_name,last_name,rownum
From employees where rownum<=5;
- 查询员工表工资最高的前5个人
Select employee_id,first_name,last_name,salary,rownum
From employees where rownum<=5
Order by salary desc; ERROR!
PS: 这个sql语句查询的结果是错误的, 原因是 order by的执行是在生成结果集之后,导致rownum排序错乱 . 需要使用子查询解决
- 当rownum与 select * 一起使用时,需要给表起别名,然后select 别名.*,rownum from ...
Select e.*,rownum
From employees e;
- 查询员工表 第六到第十名人员的信息
Select e.*,rownum
From employees e where rownum>=6 and rownum<=10; ERROR! .
PS: rownum在where 中做查询条件的时候, 只能做 =1 >=1 <n <=1 的运算,不能做 大于运算。
六.子查询 | 嵌套查询
- 子查询就是嵌套查询。
- 语法:
- 查询结果是一行一列
- 查询结果是多行一列
- 查询结果是多行多列
PS: 子查询的结果都可以作为where查询条件
3.案例
3.1 查询工资最高的人员信息 - 结果一行一列
- 查询出最高工资 rel1
Select max(salary) from employees; --rel1
- 查询出最高工资的人员
Select * from employees where salary=rel1;
- 合并
Select * from employees where salary=(Select max(salary) from employees);
3.2 查询工资高于平均工资的人员信息 - 结果一行一列
- 查询平均工资 rel1
Select avg(salary) from employees;
- 查询工资高于平均工资的人员
Select * from employees where salary>rel1;
-合并
Select * from employees where salary>(Select avg(salary) from employees);
3.3 查询与King在一个部门的人员信息 - 结果多行一列
- 查询出King所在部门
Select department_id from employees where last_name=’King’; rel1
- 查询出于King是一个部门的人员
Select * from employees where department_id in rel1 ;
- 合并
Select * from employees where department_id
in (Select department_id from employees where last_name=’King’);
3.4 查询工资最高的前5个人员信息 -结果多行多列
- 按工资排序 | 降序 rel1
Select * from employees order by salary desc;
- 将rel1作为一个虚表再次进行查询,取出工资最高的前5个人
Select *
From rel1 where rownum <=5;
- 合并
Select *
From (Select * from employees order by salary desc) where rownum <=5;
提高: 【了解】
- 查询员工表中人员信息 employee_id,salary | 工资相同的只保留一个
Select e1.employee_id,e1.salary
From employees e1
Where rowid=(select max(rowid) from employees e2 where e1.salary=e2.salary);
- 子查询的提高 -- 分页查询 【重点】
- 分页查询使用的技术点 : rownum 子查询
- 什么是分页查询 : 实质上就是将数据库表中的信息,进行分段的展示。
3.分页查询案例
3.1 查询员工表 第6 到 第 10名人员的信息
- 给rownum 起别名 | 伪装成表中的实际字段
Select e.*, rownum as rn from employees; rel1
- 将rownum的别名作为查询条件
Select * from rel1 where rn >=6 and rn <=10;
- 合并
Select * from
(Select e.*, rownum as rn from employees)
where rn >=6 and rn <=10;
3.2 查询工资最高的第6 到 第10个人的信息
- 按照工资排序 | 降序
Select * from employees order by salary desc; rel1
- 给rownum起别名
Select r.*,rownum as rn from rel1 r; rel2
- 查询出最高工资的第6 到 第10个人员信息
Select * from rel2 where rn>=6 and rn <=10;
- 合并
Select * from
(Select r.*,rownum as rn from
(Select * from employees order by salary desc) r)
where rn>=6 and rn <=10;
3.3 查询80号部门 工资最高的 第4 到 第 6 个人员信息
- 查询80号部门所有人员 并按工资排序 | 降序
Select * from employees where department_id=80 order by salary desc; rel1
- 给rownum 起别名
Select r.*, rownum as rn from rel1 r; rel2
- 查询出工资最高的第4 到 第6个人员
Select * from rel2 where rn>=4 and rn<=6;
- 合并
Select * from
(Select r.*, rownum as rn from
(Select * from employees where department_id=80 order by salary desc) r)
where rn>=4 and rn<=6;
七.表连接查询 【重点】
1. 未来数据会存在于数据库的多张表中,如果需要将1-n张表的数据, 一起展示出来 ,需要使用表连接查询。
2. 语法:
Select t1.col...,t2.col from tab1 t1 关键字 tab2 t2 on 表连接条件 where ... Group by ... Having ... Order by ...;
- 展示所有字段
Select t1.*,t2.* from tab1 t1 关键字 tab2 t2 on 表连接条件 where ... Group by ... Having ... Order by ...;
- 案例分类演示 :
3.1 内连接 -- inner join
-- 查询员工的编号、姓名、工资、部门编号、部门名称
Select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name
From employees e inner join departments d on e.department_id = d.department_id;
PS: 内连接会自动忽略null, 不满足连接条件的不会出现在查询结果中。
3.2 外连接 【重点】
-左外连接 -- left [outer] join 【重点】
--查询员工编号、姓名、工资、部门编号、部门名称
Select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name
From employees e left outer join departments d on e.department_id=d.department_id;
PS: 左外连接 左表称为主表, 右表称为辅表, 主表的记录全部出现在查询结果中。如果主表的记录与辅表的记录没有满足对应关系,自动补空.
-右外连接 -- right [outer] join
--查询员工编号、姓名、工资、部门编号、部门名称
Select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name
From employees e right outer join departments d on e.department_id=d.department_id;
PS: 右外连接 右表为主表 , 左表为辅表, 主表记录全部出现, 没有对应关系的辅表中自动补空.
-全外连接 -- full [outer] join
--查询员工编号、姓名、工资、部门编号、部门名称
Select e.employee_id,e.first_name,e.salary,e.department_id,d.department_name
From employees e full outer join departments d on e.department_id=d.department_id;
PS: 两张表的记录全部出现的查询结果。
3.3 自连接 | 强调的是在一张表里面进行连接查询
自连接就是把一张表想象成两张表。
-- 查询员工的信息,以及员工的直接领导是谁
Select e1.employee_id,e1.first_name,e2.first_name
From employees e1 left join employees e2 on e1.manager_id=e2.employee_id;
PS:
把e1 当成普通员工信息表
把e2 当成领导的信息表
3.4 多表连接
-- 查询员工的编号、姓名、部门编号、部门名称、部门所在地的编号、部门所在地的名称
- 明确查询的表 : employees departments locations
- 明确连接的条件 :
Select e.employee_id,e.first_name,e.last_name,e.department_id,
D.department_name,
L.location_id,l.city
From employees e left join departments d on e.department_id=d.department_id
Left join locations l on d.location_id=l.location_id;
PS: 多表连接时, 本质上还是两张两张的连接。 比如 : 上面的案例 是 员工表先与部门表连城一个大表, 再用这张大表与下一张表进行连接 。
一般三张表连接就是最多的了.
3.5 笛卡尔连接 -- cross join 【没有实战价值】
-语法 :
Select t1.*,t2.* From tab1 t1 cross join tab2 t2;
等效的写法
Select t1.*,t2.* From tab1 t1,tab2 t2;
PS: 结果集信息的条数, 是被连接表的积 .
小结:
书写表连接查询的思路 :
- 明确查询的表
- 确定表连接查询条件
- 指定查询的字段
八.集合运算查询 【了解】
-- 查询80 90 部门的人员信息
Select * from employees where department_id in(80,90); --set1
-- 查询 50 80 部门人员的信息
Select * from employees where department_id in (50,80); --set2
- 并集 -- union | union all
-- 求set1 与set2 的并集
Select * from employees where department_id in(80,90)
union
Select * from employees where department_id in (50,80);
PS:不保留重复的记录
Select * from employees where department_id in(80,90)
Union all
Select * from employees where department_id in (50,80);
PS:两个结果集的所有记录都保留
- 差集 -- minus
Select * from employees where department_id in(80,90)
Minus
Select * from employees where department_id in (50,80);
- 交集 -- intersect
Select * from employees where department_id in(80,90)
Intersect
Select * from employees where department_id in (50,80);
九.建表 【重点】
1.基本语法结构 :
Create table 表名(
字段1 数据类型 约束,
字段2 数据类型 default [默认值] 约束
); // 最后一个字段结束没有 逗号
2.标示符命名规范
2.1 由字母、数字、下划线、$、#组成
2.2 需要使用字母作为开头
2.3 长度最多30个字符
2.4不能与关键字重名 比如: user | order
3.数据类型
3.1 数值类型
Number(3) -- 代表数值最长3位,整数
Number(6,2) -- 存储小数,数值总长度6位,小数占2位
3.2 字符串类型
Varchar2: 可变长的字符串类型, 根据实际存储数据的大小开辟空间(在最大限度范围内)。
最多存储4000个字符长度。
Varchar2(10) -- 代表最多存储10个字符长度
Char : 定长字符串类型,按照声明的最大限制长度开辟空间。最多存储2000个字符长度。
Char(10) -- 代表最多存储10个字符长度
使用场景:
Varchar2多用于,值的情况比较复杂,不确定值的大小的场景。
Char 多用于,值的情况确定的场景.
3.3 日期类型
Date // 年-月-日 时:分:秒
timestamp // 时间比较精确
3.4 大数据类型
Clob : 存储字符型大对象,最多存储4G
Blob : 存储二进制型大对象, 最多存储4G
Long : 存储字符型大对象,最多存储2G
4.约束 : 规定了当前字段存储值的条件。
4.1 主键约束 - primary key
- 如果一个字段指定了 primary key ,此字段为主键。
-主键的值必须唯一,并且不能为null. 一张表中只能设置一个主键。
- 主键可以是一个字段,也可以是多个字段组合成主键。
4.2 唯一约束 - unique
- 规定当前字段的值必须唯一,可以为null, 如果值为null,则可以重复。
4.3 非空约束 - not null
- 规定当前字段必须有值
4.4 检查约束 - check(条件)
- 在向指定字段插入数据时, 会根据检查约束的条件, 去匹配合法的值。 不满足条件的不允许添加。
比如:
性别字段 sex check(sex in(‘男’,’女’))
手机号 mobile check(length(mobile)=11)
邮箱 email chek(email like ‘%_@_%.com’)
密码 password check(length(password)=6)
4.5 外键约束 - foreign key
- 指定外键约束 : 字段名 数据类型 references 表名(字段名)
- 外键的作用: 用于体现本表记录与其他表记录之间的关系。 外键的值可以不唯一,可以为null. 如果有值,值必须来源于其他表的主键或唯一键。
5.建表练习
5.1 建表分析
- 根据需求分析有哪些独立的实体 | 有哪些数据要存 确定表名
- 分析表与表之间的关系 | 一对一 一对多/多对一 多对多
- 明确表中字段有哪些,都是什么含义
5.2 创建表
① 单表练习
-- 用户登陆信息表
Create table t_user(
Id number(9) primary key ,
Username varchar2(20) not null unique,
Password varchar2(6) check(length(password)=6)
);
添加数据到表:
Insert into tabName values(值1,值2...); 全部字段添加
Insert into tabName(col1,col2,col3...) values(值1,值2,值3...); 部分字段添加
② 有关系的表练习
-- 班级信息表 | 一的一方 也称为父表 先创建
Create table clazz139(
Id number(3) primary key,
Name varchar2(20) not null unique,
Create_time date
);
-- 学生信息表 | 多的一方 外键放在多的一方 也称为子表
Create table students(
Id number(3) primary key,
Name varchar2(20) not null,
Age number(3) ,
Mobile varchar2(11) check(length(mobile)=11),
Sex char(3) check(sex in (‘男’,’女’)),
Cid number(3) references clazz139(id) // 外键约束,有值 值必须来自其他表的主键或唯一键
);
- 多对多关系表 : 多对多关系 其实是两个双向的一对多, 我们需要引入第三张中间表,也称为关系表。
create table course( id number(3) primary key, name varchar2(20) ); insert into course values(1,'corejava'); insert into course values(2,'oracle');
create table stus( id number(3) primary key, name varchar2(20), sex char(3), age number(3) ); insert into stus values(1,'xiaoming','男',19); insert into stus values(2,'xiaoqiang','男',20);
create table stu_course( sid number(3) references stus(id), cid number(3) references course(id), primary key(sid,cid) ); insert into stu_course values(1,1); insert into stu_course values(1,2); insert into stu_course values(2,1); insert into stu_course values(2,2); |
十.操作表的数据 -CRUD 【重点】
- 查询 - select
- 添加
Insert into tabName values(值1,值2...); // 全部字段添加
Insert into tabName(col1,col2...) values(值1,值2); //部分字段添加
- 删除
Delete from tabName; 全表删除 谨慎!
Delete from tabName where 条件;
PS:
注意必须记得加条件 .
如果操作的是有关系的表,需要先删除子表中的引用记录,才能删除父表的记录。
- 修改
Update tabName set 字段1=新值1,字段2=新值2 where 条件 ; //更新指定条件匹配的记录数据
Update tabName set 字段1=新值1,字段2=新值2; // 会将全表数据都更新
PS: 更新时记得加条件 .
十一.SQL命令的分类
1.DQL : 数据查询语言
Select 语句.
2.DML : 数据操纵语言
2.1 Insert into ...
2.2 Delete from ...
2.3Update tableName set ...
3.DDL : 数据定义语言
3.1 Create table tabName .../ 创建表
3.2 Drop table tabName; // 删除表
PS:
对于有关系的表,需要删除子表,再删除父表。
直接删除父表: drop table tabName cascade constraint; // 会先解除父子关系,然后再删除,谨慎操作!
3.3 Alter : 修改表结构 【了解】
- 添加字段: alter table tabName add 列名 数据类型 约束;
- 修改字段约束: alter table tabName modify 列名 数据类型 约束;
- 删除字段: alter table tabName drop column 列名;
- 修改字段名: alter table tabName rename column oldCol to newCol;
案例:
alter table t_user add mobile varchar2(11) check(length(mobile)=11); alter table t_user rename column name to username; alter table t_user modify mobile varchar2(12) check(length(mobile)=11); alter table t_user drop column mobile; |
- DCL : 数据控制语言 【知道就行】
4.1 grant : 授权
Grant 权限 to 用户;
4.2 revoke : 撤销权限
Revoke 权限 from 用户;
PS: 只有管理员账户可以操作权限.
- TCL : 事务控制语言 【重点】
Commit; -- 提交
Rollback; -- 回滚
十二、数据库中的其他对象
- 序列 - sequence 【重点】
1.1 序列是由oracle系统提供的,一个自增长的序列号。主要作用, 未来用于在向表添加数据时生成表的主键。
1.2 序列的使用:
- 创建序列: create sequence 序列名; // 序列名可读性要高 建议 表名_seq
- 查看序列: select 序列名.nextval from dual;
- 使用序列生成表的主键值:
Insert into tabName (序列名.nextval,其他字段值....);
案例:
create table t_user( id number(3) primary key, name varchar2(20), password varchar2(6) );
insert into t_user values(user_seq.nextval,'xiaoheihei','111111'); |
- 删除序列 : drop sequence 序列名;
1.3 创建一个特殊的序列:
- 创建从指定数值开始的序列: create sequence seqName start with n;
- 创建自定义自增长步长的序列: create sequence seqName increment by n;
-
- 视图 - view 【了解】
主要用于简化sql查询命令的书写。
基本使用:
- 创建视图 : create view viewName as selec ....; // 创建视图的时候,本质上是将一个select命令进行了保存。
- 查询视图: 语法与查询表一致
Select ... From viewName;
- 删除视图 : drop view viewName;
案例: // 简化分页查询
create view empPage_view as select e.*,rownum as rn from (select * from employees order by salary desc) e;
使用视图: select * from empPage_view where rn>=6 and rn<=10; |
PS: 使用视图对于查询效率没有任何提升, 因为视图的本质还是一个表的查询语句.
- 索引 - index 【了解】
索引可以提高查询效率。Oracle数据库会为每一张表的主键自动创建索引,
当我们使用主键作为查询条件的时候,系统自动启用主键的索引。
索引基本使用:
-创建索引: create index 索引名 on 表名(列名);
-删除索引: drop index 索引名;
PS: oracle系统启用索引的时机,在将一个有索引的列作为where查询条件的时候.
索引会占用内存空间, 大量的索引会消耗服务器资源,所以,不是所有的情况使用索引都好。
建议主键自动索引一般。 如果某一个字段会频繁作为查询条件,可以建立索引.
十三.事务控制
- 什么是事务: 【重点】
事务就是业务 , 使用户操作数据库的最小单元,一个事务由1-n条sql命令组成。
组成一个事务的sql命令,是一组不可分割的整体,要么一起成功,要么一起失败。
- 事务的边界 :
begin : 从第一条sql命令开始.
end : 事务结束正常需要用户给出明确的指令。
提交commit; /回滚 rollback;
- 显示提交:
Commit; // 给出commit;
- 隐式提交:
正常退出数据库客户端; 使用退出命令.
- 显示回滚: rollback;
- 隐式回滚: 不正常退出客户端. 比如: 断电 、地震 等等.
PS: 对于 insert / delete / update操作, 完成后 必须要做事务控制。 要么提交 要么回滚.
- 事务的原理:
Oracle数据库会为每一个连接到服务器的客户端,开辟一小段空间,用于暂时的缓存当前客户端的sql命令。这小段空间称为回滚段.
当事务结束时, 需要手动给出明确指令:
Commit; 将回滚段中当前事务的操作sql提交到数据库服务器,并保存。
Rollback; 丢弃回滚段中所有的sql命令.
- 事务的特性:ACID 【面试点】
A- atomic : 原子性。事务操作是一组不可分割的整体,要么一起成功,要么一起失败。
C- consistency : 一致性。 事务前后的数据要一致。
I - Isolation :隔离性。 同时连接到数据库的多个客户端之间事务的隔离级别。
Oracle默认的隔离级别是读提交。
4.4 D - Durability :持久性。 事务结束后对数据库数据的影响是永久的。
只为回顾方便---hello right