《数据库系统原理》读书笔记
2021-10-13 11:21:48 0 举报AI智能生成
登录查看完整内容
实操语句，适用于自考、日常使用
作者其他创作
大纲/内容
需要请联系1031574211
MariaDB与mysql 的操作命令一致，所以直接学习mysql即可
先创建有了mysql
后mysql被sun公司收购
后sun公司被Oracle公司收购，mysql的所有权归于Oracle
而创始人随后为mysql另开分支，创建了mariadb，并且其完全兼容mysql的所有命令和api
二者的关系
学习前提
mysql -u+(用户名） -p+(密码，如果不写，则后面输入密码时看不见内容）
登录
exit
直接ctrl+c（强制退出）
退出
show databases;
查看所有数据库
show tables;
查看该数据库中的表
use 数据库名字;
使用某个数据库
create databases 数据库名;
创建数据库
source 绝对路径;
导入数据库
desc 表名
查看表的数据结构
select version();
查看mysql数据库的版本号
select database();
查看当前使用的数据库
常用命令
概念：数据查询语言（凡是带有select 关键字的都是查询语句）
select ....from ...where ...group by ...having ...order by ...limit ...
写法
第一步：from
第二步：where
第三步：group by
第四步：having
第五步：select
第六步：order by
第七步：limit
语句顺序：
select * from 表名;
查看表内所有的数据
select 字段名 from 表名;
简单查询
查询多个字段
select dname as deptname from 表名;
select dname deptname from 表名;
select dname "dept name" from 表名;
select dname 'dept name' from 表名;
列起别名
使用as关键字，只是把显示的查询结果更改，原表列名不变注意：所有数据库当中，字符串同意使用单引号括起来 双引号在Oracle数据库中无法使用
列参与数学运算
= 等于
<> 或!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between .... and ... 两个值之间，等同于>= and <=
不可用 = null因为数据的null不是一个值，不能用等号衡量
is null 为空
is not null 非空
and 并且
or 或者
in不是一个区间，括号内的是具体值
select 名字，员工 from 表名 where 工资 = 800 or 工资 = 5000;
查询薪资是800或者500的员工
in 包含，相当于多个or （not in 不在这个范围中）
like 模糊查询，支持%或下划线匹配
where 列名 like '%T';
以T结尾
where 列名 like 'T%';
以T开头
% 匹配任意个字符
where 列名 like '_A%';
第二个字母为A
where 列名 like '__A%';
第三个字母为A
下划线，一个下划线只匹配一个字符
条件语句
找出工资大于2500并且部门编号为10或20的员工
select * from emp where 工资 > 2500 and (部门编号 = 10 or 部门编号 = 20);
当存在双重条件语句时，可加括号进行修饰
条件查询
默认情况下是按照列名2进行升序（由小到大）
select 列名 from 表名 order by 列名2;
按照列名2进行升序（由小到大）
select 列名 from 表名 order by 列名2 asc;
升序
按照列名2进行降序（由大到小）
select 列名 from 表名 order by 列名2 desc;
降序
查询员工名字和薪资要求按照薪资升序，如果薪资一样的话再按照名字升序排列
多个字段排序
按照列名2 进行排序（不建议再开发中这样写）
按照字段位置进行排序
排序
特点：一个输入对应一个输出
转换小写
lower
转换大写
upper
去字串
substr
字符串拼接
select name from student;
首字母大写
案例
concat
取长度
length
去空格
trim
将查询结果的数字进行格式化
将字符串varchar转换成date日期
str_to_date
通常用在查询日期方面，设置展示日期格式
格式化date日期成varchar字符串
date_format
now()可以获取当前时间
四舍五入
若为负数，则保留到负几的整数位
round
只生成小数，如果需要整数，请配合round使用
生成随机数
rand()
可将null转换成一个具体值
ifnull
case...when...then...then...else...end
数据处理函数/单行处理行数
多个输入对一个输出
因为分组函数需要在分组之后才能使用where执行的时候还没有分组
分组函数可以在select和order by 时使用
分组函数不可使用在where后面
select后面只能跟着参加分组的字段，以及分组函数
特点：
select ... from ... group by ...
格式：
1.from
2.where
3.group by
4.having
5.select
6.order by
执行顺序：
计数
count
求和
sum
平均数
avg
最大值
max
最小值
min
函数
可以对分组完成后的数据进一步的过滤
一般在having中使用的都是where语句无法实现的功能
having
找出每个部门，每个岗位的最高工资
优化策略：where和having，优先选择where，where实现不了的再选择having
找出每个部门的最高工资，要求显示最高薪资大于3000
找出每个部门的平均薪资，要求显示平均薪资高于2500
找出每个岗位的平均薪资，要求显示平均薪资大于1500的，出'aaa'岗位之外
分组函数/多行处理函数
去除关键字，放置在所有字段的最前方，表示把所有字段名去除重合数据
distinct
表的连接次数在只用where的时候会调用多次数据，效率不高
表连接使用join，可以减少调用次数，效率更高(99语法，语言更清晰)
inner 可省略
select ...from ainner join bon a和b的连接条件where 筛选条件
99语法
笛卡尔积现象
特点：完成能够匹配上这个条件的数据查询出来
和外连接不同，没有主次关系
等值连接
非等值连接
自连接
内连接
特点：在外连接中存在主次关系，会把主表的数据全部查询出来，并关联查询左边的表
左外连接和右外连接可以相互转换，更改right 和left即可
outer可以省略
左外连接
右外连接
外连接
全连接
连接查询
连接查询中，内连接和外连接可以联合使用
select .... from .... join ... on ... join ... on ... join ... on ...
语法
多表连接
select语句中嵌套select语句，被嵌套的语句被称为子查询
select ...(selecet)from ...(select)where ...(select)
比最低工资高的员工和工资(where的子查询)
每个岗位的平均工资的薪资等级（from的子查询）
该方式的子查询结果只能返回一个，不能多个
找出每个员工的部门名称，显示员工名和部门名（select的子查询）
子查询
合并工作岗位为'aaa'和'bbb'的员工
使用or的查询匹配次数满足笛卡尔积，查询次数成倍的翻使用unino的查询匹配可以减少匹配次数
特点
unino在结果集合并的时候要求列数相同
注意事项
合并
将查询结果的一部分取出来，通常使用在分页查询分页的作用是为了提高用户的体验，因为一次全部查出来，用户体验差，可以一页页翻
取出前五
limit 5
从下标0开始取，长度为5
用法
取出前五名的员工
注意：mysql中limit在order by 之后执行
limit (页数-1)*分页长度，分页长度
分页
limit
DQL
概念：数据操作语言（对表的数据进行insert、delete、update的都是DML）
可变长度的字符串
varchar（最长255）
定长字符串（不管实际多大，会分配固定长度的空间去存储，不恰当的时候会浪费空间）
char （最长255）
java的int
int（最长11）
java的long
bigint
单精度高的浮点型
float
双精度高的浮点型数据
double
%Y-%m-%d
短日期类型
date
%Y-%m-%d %h:%i:%s
长日期类型
datetime
字符大对象最多可以存储4G的字符串，例如存储一批那文章超过255个字符的都要采用CLOB字符大对象来存储
clob
二进制大对象专门来存储图片、声音、视频等流媒体往BLOB类型插入数据的时候，需要使用IO流
blob
数据类型
年
%y
月
%m
日
%d
时
%h
分
%i
秒
%s
mysql的日期格式
create table 表名（字段名1 数据类型，字段名2 数据类型，字段名3 数据类型);
create table emp2 as select * from emp;
快速复制建表
建表
当表不存在的时候会报错
drop table 表名;
如果这张表存在就删除
drop table if exists 表名;
删表
insert into 表名（字段名1、字段名2、字段名3...） value (值1、值2、值3...);
插入单条记录
插入多条记录
insert into dept_bark select * from dept;
将查询结果插入（很少用）
注意：插入时需要跟形参对应
插入数据
修改数据
不加条件会全部删除，但是比较慢，数据可以恢复
delete from 表名 where 条件;
效率较高，无法恢复
truncate table 表名
快速删表
删除数据
DML
概念：数据定义语言（对表的结构进行create、drop、alter的都是DDL）
字段不能为空
非空约束：no null
约束的字段不能重复，但可以为null
列级约束：单个字段具有唯一性
表级约束：多个字段联合起来具有唯一性
唯一性约束：unique
一种约束
主键约束
被加了主键约束的字段
主键字段
主键字段的每一个值叫做：主键值
主键值
表内只存在一个主键
单一主键
使用表级约束的多个主键
复合主键
主键值是一个自然数，和业务没关系
自然主键
主键值和业务挂钩，例如银行卡账号做主键值
业务主键
auto_increment
自增
相关术语
not null + unique（不能为空，同时不能重复）
主键值一般都是定长的，不建议使用varchar，一般使用int、bigint、char等
主键的特征
主键约束：Primary key（简称PK）
外键约束
该字段添加了外键约束
外键字段
外键字段中的每一个值
外键值
将班级名称对应编号
班级表
使用对应的班级编号
学生表
请设计数据库表，描述“班级和学生”的信息
业务背景
foreign key(子表字段) refereces 父表(父表字段)
格式
不一定是主键，但至少有unique约束，因为外键引用可以为null
子表中的外键引用的父表中的某个字段，被引用的这个字段必须时主键吗
可以为null
外键可以为null吗
问题
外键约束：foreign key（简称Fk）
分支主题
检查约束：check（mysql不支持，oracle支持）
约束
show create table 表名;
怎么添加/指定存储引擎
默认的存储引擎是：InnoDb
指定存储引擎
ENGING
默认的字符编码方式是：UTF8
指定这张表的编码方式
CHARSET
当前5.5.36支持8个，版本不同支持情况不同
有三个文件：格式文件（存储表结构）.frm、数据文件（存储表内容）.MYD、索引文件（存储表上的索引，减少搜索范围）.MYI
可被转换为压缩、只读表来节省空间
主要是主键或者具有unique约束的字段会自动创建索引
提示
MyISAM存储引擎
支持事物，支持数据库崩溃后自动恢复机制，非常安全
.frm格式文件
InnoDB存储引擎
表数据及索引存储在内存中，查询快
不能包含TEXT或BLOB字段
表级锁机制
不安全，关机之后数据消失
MEMORY存储引擎
常用存储引擎
九大存储引擎
存储引擎
DDL
清空事务性的日志文件，将数据持久化到数据库表中，标志这事务的成功结束
start transaction
DML语句
提交：commit
语句顺序
事务提交：commit
将之前的DML语句全部撤销，并且清空事务性的日志文件，标志这事务的失败结束
回滚只能回滚到上一次的提交点，但是mysql每执行一条语句，则提交一次（自动提交），需要另外关闭自动提交机制
回滚：rollback
事务回滚：rollback
说明事物师最小的工作单元，不可再分
原子性
同一个事务中，所有操作要么同时成功，要么同时失败
一致性
A事务和B事务之间具有一定的隔离A事务在操作一张表的时候，另一个事务B也操作这张表的话（多线程并发，具有安全问题）
隔离性
事务最终结束的一个保障
持久性
四个特性
事务A可以读取到事务B未提交的数据（脏读现象）
读未提交：read uncommitted（最低的隔离级别）
事务A只能读取到事务B提交之后的数据（不可重复读取数据）
读已提交：read committed
事务A开启之后，每一次在事务A读取到的数据都是一致的，即使事务B修改了数据仍然不会改变（幻影读，不够真实）
mysql 的默认事务隔离级别就是可重复读
可重复读：repeatable read
最高隔离级别，效率最低
事务排队，不能并发
序列化 / 串行化：serializable（最高的隔离级别）
隔离级别
select @@tx_isolation
查看当前事务隔离级别
set global transaction isolation level 隔离级别;
更改隔离级别
概念：事务控制语言（transaction）
TCL
在数据据库表的字段上添加的，为了提高查询效率存在的机制，相当于是一个目录在mysql当中以一个树的形式存在（自平衡二叉树，B-Tree）mysql中，竹简上，以及unique字段都会自动添加索引
条件一：数据量庞大
条件二：该字段经常出现在where后面
条件三：不常使用DML语句操作该字段
什么条件下，会考虑给字段添加索引
create index 索引名 on 表名（字段名）
创建索引
drop 索引名 on 表名
删除索引
explain sql语句
查看一条sql语句是否使用了索引进行检索
第一种情况：在使用模糊查询，并且条件是以“%”开头时索引无法使用
第二种情况：在使用or的时候会失效，如果使用or那么要求两边都添加索引，不然两边都会失效
第三种情况：联合索引
第四种情况：where当中的参与列进行了运算
第五种情况：在where当中索引列使用了函数
索引失效
索引（Index）
后面只能跟着DQL语句，并且形成的视图后修改其中的数据，先关联的表也会一起修改
create view 视图名 as DQL语句
新建视图
drop view 视图名
删除视图
insert into 视图名(字段名) values(值)
插入视图
update 视图名 set 字段名 = 值 where 条件
修改视图数据
简化select语句，简化开发
假设有一条非常复杂的sql语句，并且在不同的位置上反复的使用，每次使用都需要对这个sql语句重新的编写，很长很麻烦。这时候就可以利用视图来进行开发，利于后期的维护
在开发的应用
视图对应的语句只能是DQL语句。但是视图对象创建完成之后，可以对视图进行增删改查等操作
C：Create（增）
R：Retrive（查：检索）
U ：Update
D：Delete
增删改查，又叫做“CURD”，在公司内部统一沟通的术语
视图
mysqldump 数据库名>路径.sql -u root -p 密码
mysqldump 表名>路径 -u root -p 密码
指定数据表
数据导出
第一步：先登录到mysql数据库服务器上
第二步：创建数据库：create database 数据库名（需要和之前的库名保持一致）
第三步：使用数据库：use 数据库
子主题
数据导入
DBA
授权：grant
撤销权限：revoke
数据控制语言
DCL
要求任何一张表必须有主键，每一个字段原子性不可再分
第一范式
建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖
第二范式
建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖
第三范式
三范式是面试官经常问的，所以一定要记，可减少表中数据的冗余和空间的浪费
数据库设计三范式
Mysql & MariaDB
0 条评论
回复 删除
下一页
