MySQL脑图
2022-01-02 17:00:30 86 举报AI智能生成
登录查看完整内容
MySQL脑图
作者其他创作
大纲/内容
MySQL
其他
表的相关操作
语法格式：create table 表名（字段名1 数据类型；字段名2 数据类型；.....）；
注：相关数据类型：
BLOB（二进制大对象）主要存储图片、视频等流媒体信息
CLOB（字符大对象）存储较大文本，比如，可以存储4G的字符串
表名在数据库当中一般建议以：t_表名或tbl_表名来命名。
insert语句插入数据
语法格式：insert into表名 （字段名1，字段名2.....）values （值1，值2...）；
一次插入多行数据：例：insert into t_student （name，age）values（‘Lisa’25），（‘jack’23）；
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
例：insert into t_student(name，age)values（‘Lisa’，25）；name和age可以和表结构中的顺序不同，但是两者的值要一一对应。
当没有按表中的数据类型的个数插入时，其他未被插入所有字段自动插入NULL。
当一条insert语句执行成功之后，表格中必然会多一行记录，即使多的这行记录当中某些字段为NULL，后期也没办法再执行，insert中插入的语句只能用update进行更新。
删除操作：（Delete）
删除表：
例：drop table if exists t_studnet;
语法：drop table 表名；
删除表中的大数据：
truncate table 表名；（通用，表被截断，不可回滚，永久丢失）
删除数据：
delete from 表名 where 条件；（注意：没有where条件则全部删除）
修改数据（Update）：
语法格式：update 表名 set 字段名1=值1，字段名2=值2.....where 条件；注意：若没有where条件，则整张表的数据全部被更新。
CRUD操作：（Create增、Retrieve检索、Update修改、Delete删除）
表的复制：
语法：create table 表名 as select 语句；将查询结果当作表创建出来。
约束：
理解：在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中的数据的合法性、有效性、完整性。
非空约束（not null）：
约束的字段不能为NULL
唯一约束（unique）：
约束的字段不能重复
主键约束（primary key）：
约束的字段既不能为NULL，也不能重复（简称PK）
作用：
1.表的设计三范式中有要求，第一范式就是要求任何一张表都应该有主键
2.主键值是这行记录在这张表当中的唯一标识
分类：
根据主键字段的字段数量分：
单一主键（推荐使用且是常用的）
复合主键：多个字段联合起来添加一个主键约束
根据主键性质分：
自然主键：主键值最好就是一个和业务没有任何关系的自然数。（推荐使用）
业务主键：主键值和业务挂钩。例：银行卡号做主键；身份证号作为主键等。
相关术语：
主键约束：primary key
注：一张表的主键约束只能有一个。
主键字段：id字段添加primary key之后，id叫做主键字段
主键值：id字段中的每一个值都是主键值
mysql中提供主键值自增（重要）：
oracle当中也有类似功能，提供了自增机制，叫做：序列（sequence）对象。
外键约束（foreign key）（FK）：
1.即用其他表中的字段作为约束条件，使其成为当前使用的表中的主键。当前表叫做子表，被引用的表叫做父表。
2.外键字段引用其他表中的某个字段时，被引用的字段不一定是主键，但至少具有unique约束。
检查约束（check）：
注意Oracle数据库有check约束，但是MySQL没有，目前MySQL不支持约束。
存储引擎（了解）：
理解：存储引擎这个名字只在MySQL中存在，oracle中有对应的机制。（其实就是表的存储方式）
MySQL默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF-8
常见的存储引擎：
1.InnoDB
表的结构存储在xxx.frm文件中，数据存储在tablespace这样的表空间中，无法被压缩，无法转换成只读。
优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。（InnoDB支持级联删除和级联更新）
2.MylSAM
优点：可被压缩，节省存储空间，并可转换为只读表，提高检索效率。
缺点：不支持事务
MylSAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中的数据）
xxx.MYI（存储表中索引的文件）
MylSAM是MySQL最常用的存储引擎，但不是默认的。
3.MEMORY（以前称HEPA引擎）
优点：查询速度快
缺点：不支持事务，数据容易丢失，因为所有的数据和引擎都存储在内存当中。
重点内容
事务（可掌握）：
理解：一个事务是一个完整的业务逻辑单元，不可再分。（可理解为业务）
例：银行转账，A向B转账，A需执行转账命令，B也必须成功收到，整个业务才算完成。两者同时成功或同时失败。
和事务相关的语句只有：DML语句（数据操作语言）（insert、delete、update）
事务的存在是为了保证数据的完整性、安全性。
四大特性（ACID）：
1.原子性
事务是最小的工作单元，不可再分
2.一致性
事务必须保证多条DML语句同时成功或同时失败
3.隔离性
事务A和事务B具有隔离
隔离级别：
第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。读未提交存在脏读（Dirty read）现象：表示读到了脏的数据
第二级别：读已提交（read committed）
对方事务提交之后的数据我方可以读，解决了脏读现象。
存在问题：不可重复读
注：MySQL中的事务是支持自动提交的，只要执行一条DML，则提交一次。
第三级别：可重复读（repeatable read）
解决了不可重复读问题
注：oracle数据库默认的隔离级别是：读已提交。MySQL数据库默认的是可重复读。
存在问题：读取到的数据是幻象，不是真实数据，即读取到的数据不及时，不是最新数据。
第四级别：序列化读/串行化读（serializable）
解决了所有问题，但是效率低，需要事务排队。
4.持久性
即最终数据必须持久化到硬盘文件当中，事务才算成功结束。（成功保存数据）
相关语句：
start transaction；
作用：关闭自动提交机制，开启事务机制。
演示事务隔离级别：
第一级别
set global transaction isolation level read uncommitted；
第二级别：
set global transaction isolation level read committed；
第三级别：
set global transaction isolation level repeatable read；
rollback（回滚事务）
相当于返回到建表的初始记录第一步（返回后进行其他操作），当执行commit语句操作后，事务机制方可结束
索引：
理解：相当于目录，通过目录可以快速找到对应的资源。有两种检索方式：全表扫描，根据索引扫描。
底层采用的数据结构是B+Tree
添加索引是给某一个字段或某些字段添加索引。
创建索引对象：
create index 索引名称 on 表名（字段名）；
注：当数据量庞大，该字段很少进行DML操作，且经常出现在where子句当中，此时可考虑给字段加索引。
例：给薪资sal字段添加索引：create index emp_sal_index on emp(sal);
另：查看sql语句的执行语句，例： explain select ename，sal from emp where sal=5000；
删除索引对象：
drop index 索引名称 on 表名；
注：主键和具有unique约束的字段会自动添加索引。根据主键查询效率比较高，推荐使用。
1.单一索引：给单个字段添加索引； 2. 复合索引：给多个字段联合起来一起添加1个索引
注意：当使用模糊查询的时候，第一个通配符是%，此时索引失效。
例：select ename from emp where ename like ‘%A%’；（索引失效）
3.主键索引：主键上会自动添加索引； 4.唯一索引：有unique约束的字段会自动添加索引
视图（了解）
作用：视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，Java程序员只对视图对象进行CRUD。（方便简化开发，利于维护。）
数据库设计三范式（重点掌握）：
理解：设计范式就是设计表的依据，按照这个三范式设计的表不会出现数据冗余。
第一范式：
任何一张表都应该有主键，并且每个字段原子性不可再分。
注：在实际开发中，以满足客户的需求为主，有时候会拿冗余换执行速度。
第二范式：
建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖。
多对多？三张表，关系表两个外键
部分依赖的理解：
暂略
第三范式：
建立在第二范式的基础上，所有非关键字段直接依赖主键，不能产生传递依赖。
一对多？两张表，多的表加外键
基础定义及语法
sql、DB、DBMS区别
1.SQL：结构化查询语言，是一门标准通用的语言，标准的sql适合于所有的数据库产品
2.DBMS：数据库管理系统，常见的有（MySQL，oracle，DB2，Sybase，SQLserver）
3.DB：数据库，数据库实际上在硬盘中以文件的形式存在
表
1.表table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强
2.一个表包括行和列，行被称为数据/记录，列被称为字段
sql语句分类
1.DQL（数据查询语言）：查询语句，凡是select语句都是DQL。
2.DML（数据操作语言）：对表当中的数据进行增删改
3.DDL（数据定义语言）：；对表结构进行增删改
4.TCL（事务控制语言）：commit提交事务，rollback回滚事务，
5.DCL（数据控制语言）：grant授权，revoke撤销权限等
基础命令及语句
常用命令：
查看当前使用的是哪个数据库：select database（）；
查看MySQL的版本号：select version（）；
\\c命令: 结束一条语句 exit 退出MySQL
查看表结构：desc 表名；
删除数据库：drop database 数据库名；
语句：
查看创建表的语句：show create table 表名；
简单的查询语句（DQL）：select 字段名1，字段名2，字段名3........from 表名；
给查询结果的列重命名？
select 列名 as 别名 from 表名；（as关键字可省略）
查询所有的字段
select * from 表名；（实际开发中不建议使用，效率较低）
注：任何一条sql语句都以“；”结尾；sql语句不区分大小写。
注：标准sql语句中要求字符串使用单引号括起来，虽然MySQL支持双引号，尽量别用
查询
条件查询
语法格式：select 字段，字段....from 表名 where 条件 ；
执行顺序：先from，然后where 最后select
例：between 1000 and 3000 ；
<> 不等于
例：<>3000 表示不等于3000
注：在数据库当中NULL不是一个值，表示为空，判断是否为空不能用=，必须使用is null或is not null
条件查询in
in（xxx，yyy）；in后面的值不是区间，而是具体的值
模糊查询like
必须掌握两个符号，%和__ %代表任意多个字符，__代表任意一个字符
排序
select...... form ....... where.....order by ......；默认是升序排列
select.......form......where.......order by... asc ；升序
select.......form......where.......order by... desc ；降序
注；执行顺序order by是最后执行的。
多表查询（连接查询）
理解：就是多张表连接起来一起查询，得出结果。语法分类：SQL92，SQL99（常用的新语法）
内连接
等值连接
最大特点就是条件是等量关系
SQL92语法：
例：select s.name c.name from student s ，class c where s.name=c.name；
SQL99语法：
例： select s.name c.name from student s inner join class c on s.name=c.name；
两者的变化：
92语法中的“，”改为join，where改为on
inner可省略，带inner目的是可读性更好一些
99语法结构更清晰，表连接的条件和真正过滤的where条件进行分离
语法：select...... from A join B on 连接条件 where 筛选条件；
非等值连接
最大特点就是连接条件中的关系是非等量关系
即on后的条件是一些判断条件（比如区间等）
自连接
一张表看作两张表，自己连接自己。
内外连接区别：
内：
假设A和B表进行连接，使用内连接的话，凡是A表和B表能匹配上的记录查询出来。AB两张表没有主副之分，两张表是平等的。
外：
假设A和B进行连接，使用外连接的话，AB两张表中一张是主表，一张是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
外连接
左外连接（左连接）：
左边表示主表
语法：select.......from......left outer join ....on......；（其中outer可省略）
右外连接（右连接）：
右边表示主表
语法：select.......from......right outer join......on......；（outer可省略）
外连接最重要的特点是：主表的数据无条件的全部查询出来。
多表查询：
语法：select.....from A join B join C on.....
表示：A表和B表先进行表连接，连接之后A表继续和C表进行表连接。
全连接（略）
起别名
select 字段，字段 from xxx a，yyy b where..... （即表xxx的别名为a，表yyy的别名为b）
注：笛卡尔积现象：当两张或多张表进行连接查询时，没有任何条件限制，最终的查询结果条数是两张或多张表记录条数的乘积。
其他查询
子查询（select语句当中嵌套select语句，被嵌套的select语句是子查询。）
可以出现在：select....（select）； from..（select）； where ...（select）；
union（可以将查询结果集相加）
语法：select......from......where..... union select....from.......where.... ；
功能：可以将两张不相干的表中的数据拼接在一起显示。
limit（重点，以后所用的分页查询全靠它）
limit是mysql特有的，其他数据库没有，不通用（oracle中有相同的机制，叫做rownum）
limit取结果集中的部分数据，这是他的作用
语法结构：
limit startIndex，length startIndex表示起始位置，从0开始，0表示第一条数据，length表示长度，取几条数据。
例：取出表中工资前5名的员工：
法2：select ename，sal from emp order by sal desc limit 5；
limit是sql语句最后执行的一个环节：
select.......from......where......group by.....having......order by.....limit.....；执行顺序：5 1 2 3 4 6 7
补充：
完整的select语句：select........from.......where........group by.......having.......order by 执行顺序：5 1 2 3 4 6
查询结果的去重：
select distinct xxx from 表名；（表示从表中去除xxx中重复的数据）
***关键字distinct 只能出现在所有字段的最前面（若select后面的字段有多个，则表名这多个字段联合一起去重）
分组函数（多处理函数）
count（计数）
sum（求和）
注：分组函数自动忽略null，都是对某一组数据进行操作的
avg（平均值）
分组函数不可直接使用在where子句当中，因为group by是在where执行之后才会执行的。
max（最大值）
凡是有null参与的运算结果一定是null（数据库的死规定）牢记！！！
min（最小值）
当一条sql语句没有group by的话，整个表自成一组
0 条评论
回复 删除
下一页
