MySQL学习笔记

忆亦何为

已于 2022-09-30 15:05:42 修改

阅读量612

点赞数 1

文章标签： mysql 数据库学习

于 2022-09-30 14:07:10 首次发布

本文链接：https://blog.csdn.net/weixin_45832694/article/details/126840624

版权

一、 SQL语句分类

DQL：数据库查询语言，查询语句，凡是select语句都是DQL。
DQL执行顺序：
Select 5
From 1
Where 2
Group by 3
Having 4
Order by 6
Limit 7
DML：数据操作语言，insert delete update，对表当中的数据进行增删改。
DDL：数据定义语言，create drop alter，对表结构进行增删改。
TCL：事务控制语言，commit提交事务，rollback回滚事务。
DCL：数据控制语言，grant授权、revoke撤销权限。

二、 Mysql命令

登录：mysql  –u账号 –p密码，不想显示密码可以不写密码
查看数据库：show databases；
创建数据库：create database 数据库名字；
使用数据库：use 数据库名字；
查看表：show tables；
导入数据库：source 路径
查看表结构：desc 表名字；
查看建表语句：show create table 表名；
查看存储引擎：show engines \G
查看表数据：select 字段（所有字段*） from 表名；（*效率比较低）
查询的字段可以参与数学运算：select 字段*12 from 表名；
可以给字段重命名：select 字段*12 as新字段名 from 表名；（as可以省略。）

Mysql字符串需要用单引号‘’括起来，重命名的字段如果是中文需要用单引号括起来（’中文’），不然会错误

查看当前使用的数据库：select database()；
查看mysql的版本号：select version()；
退出数据库：exit；
查看创建表语句：show create table 表名字；
删除表：drop table if exists 表名；如果这张表存在就删除。
删除数据库：drop database 数据库名；
删除数据：delete from 表名 where 条件；没有where，整张表数据全部删除。
删除大表：truncate table 表名；表截断不可回滚，永久丢失，手动滑稽。
条件查询：select 字段 from 表名 where 条件；

运算符
如果NULL参与运算结果一定是NULL。

符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN AND	在两值之间	>=min&&<=max遵循左小右大，使用字符遵循左闭右开
NOT BETWEEN	不在两值之间
IN	在集合中	使用的时候要加括号
NOT IN	不在集合中	使用的时候要加括号
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配	%代表多个字符，_代码一个字符，例如‘%O%’，需要模糊查询_或者%需要加转义字符\|
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空
&&或and	并且
\|\|或or	或者	与上面一起用的时候and的优先级比较高

查看数据排序：select 字段 from 表 （where 条件） order by 字段 asc升序/desc降序/或者不写默认升序；

如果多个字段，越靠前的字段越起到主导作用，只有前面字段无法完成排序的时候，才启用后面的字段。
当一个语句有order by的时候，select后面只能跟分组函数和参与分组的字段。
having子句，对分组之后进一步的处理：select 字段 from 表（where 条件） order by 字段having 条件；
效率比较低，建议如果能够使用where过滤尽量使用where。
分组函数/聚合函数/多行处理函数：分组函数自动忽略NULL，分组函数不能加在where里面，因为分组函数在order by后执行，而分组函数是在order by之后执行，如果没写order by，默认自成一组，也就是没写还是会执行order by。

count（字段）计数，count（*）统计总记录条数
sum（字段）求和
avg（字段）平均值
max（字段）最大值
min（字段）最小值

单行处理函数：输入一行处理一行
Ifnull（字段，处理值）把字段当中的null当作处理值处理

去除重复记录：select distinct 字段 from 表名；

distinct只能出现在所有字段最前面。

查询结果集相加：查询语句 union 查询语句；

条件是两个查询语句是相同列数，而且显示的是第一种查询结果的列表名。
子查询：select语句当中嵌套select语句，被嵌套的select语句是子查询。主要出现在select（比较少用）、from、where里。

创建表： 
create table 表名{
字段名1 数据类型 约束，
字段名2 数据类型 约束，//列级约束
约束（字段名1，字段名2）//表级约束
…….
}；

插入数据：insert into 表名（字段名1，字段名2，…）value（值1，值2，…）

字段数量和值数量要相同，并且数据类型要对应相同，如果插入字段的数量少于表中的字段数，其他字段默认值（一般为NULL）。
插入一行数据：insert into 表名 value（值1，值2，…）
一次插入多行数据：insert into 表名 value（值1，值2，…），（值1，值2，…）
一行和多行这种方式，value里面的值的顺序需要与表中的字段对应，不能多也不能少。

表的复制
create table 表名 as select语句；
将查询结果当作表创建出来。

insert into 表名 select语句；
将查询结果插入一张表中，查询的结果需要与插入表一样。

修改数据：update 表名 set 字段名1=值1，字段名2=值2… where 条件。
如果没有条件整张表全部更新。
	
查看事务的全局隔离级别：select @@global.tx_isolation；旧版本
新版本：select @@global. transaction _isolation；

设置事务的全局隔离级别：set global transaction isolation level 隔离级别；

Mysql远程登录：mysql –h IP地址 –u账户 –p密码
查看sql语句的执行情况：explain 语句；

给字段添加索引：create index 索引名 on 表名（字段名）；
删除索引：drop index 索引名 on 表名；

创建视图：create view 视图名 as select语句；
删除视图：drop view 视图名；

三、数据类型

关于mysql数据类型：比较常用int，bight长整型，float，double，char，varchar，date，BLOB二进制大对象（存储图片、视频等流媒体信息），CLOB字符大对象（存储大文本）

a) 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

b) 日期和时间类型

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038-1-19 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

c) 字符串类型

类型	大小	用途
CHAR	0-255 bytes	固定长字符串
VARCHAR	0-65535 bytes	可变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

四、连接查询

笛卡儿积现象

当两张表进行连接查询的时候，没有任何条件限制，最终的查询结果条数是两张表记录条数的乘积。
怎么避免笛卡儿积：加条件进行过滤，避免笛卡儿积，匹配次数还是一样的，只是进行了过滤。

内连接之等值连接

表别名：当进行连接查询的时候通常会给表取一个别名
好处：执行效率高，可读性好，可区分名字重复的字段。
内连接之等值连接：条件是等量关系。

SQL92:select e.字段，d.字段 from 表A e 表B d where e.字段=d.字段;
SQL99:select e.字段，d.字段 from 表A e （intter省略了）join表B d on e.字段=d.字段;

join on：表A join 表B on 连接条件（后面还可以跟where）；

内连接之非等值连接

内连接之非等值连接：连接条件是非等量关系。

select e.字段，d.字段 from 表A e （intter省略了）join表B d on（例如：e.字段 between d.字段 and d.字段）；

内连接之自连接

内连接之自连接：一张表看作两张表，自己连自己。

select e.字段，d.字段 from 表A e （intter省略了）join表A d on表B d on e.字段=d.字段;

外连接

外连接：主表的数据会无条件查询出来

左连接

左连接：select e.字段，d.字段 from 表A（主表） e  left （outer省略了）join表A d on表B d on e.字段=d.字段;

右连接

右连接：select e.字段，d.字段 from 表A e  right （outer省略了）join表A d（主表） on表B d on e.字段=d.字段;

多表连接

多表连接：表A  join  表B  on 连接条件 join 表C  on 连接条件….

A表和B表先连接，连接之后A表继续跟C表进行连接，…….
需要哪张表进行外连接，就在哪个表的join上加上left或right。

五、 Limit及标准的sql分页

概念：是sql语句最后执行的环节，取结果集中的部分数据，这是MySQL特有的，其他数据库没有，Oracle有一个相同的机制叫rownum，以后分页查询基本就靠limit。
语法机制：
limit startIndex，length startIndex表示起始位置，length表示取几个，startIndex不写，默认为0开始。
标准的分页查询：
pageNo：显示第几页。pageSize：每页显示条数。
第pageNo页：limit （pageNo-1）*pageSize，pageSize

六、约束

概念：在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中的数据的合法性、有效性、完整性。
常见的约束：

非空约束（not null）：不能为NULL，只有列级约束没有表级。唯一约束（unique）：不能重复，但可以为NULL，联合唯一约束（表级约束），指联合起来不能重复。
主键约束（primarykey）：既不能为NULL也不能重复，起到唯一标识的作用，一张表的主键约束只能有一个，有主键约束的字段和值称为主键字段和主键值。
- 分类：
  根据主键字段的数量来划分：单一主键（常见的），复合主键（不建议使用，违背三范式）
  根据主键性质划分：自然主键（主键值为自然数，自增的（auto_increment以一递增，Oracle自增机制为序列对象（sequence））），业务主键（主键值和系统业务挂钩，不建议使用）。
外键约束（foreign key）：有外键约束的字段和值称为外键字段和外键值。A表中有外键约束引用B表的的字段，此时A表有外键约束的字段的值只能是B表引用字段的值，则称A表为子表，B表为父表，外键值可以为NULL，被引用的字段不一定为主键，但至少为唯一约束。
删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，再添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，再删除父表。
foreign key（字段名） references 父表（父字段名）。
检查约束（check）：目前mysql不支持，oracle有这个约束。

七、存储引擎（了解）

在建表的时候，mysql会自动加上存储引擎，默认为InnoDB方式，默认采用的字符集为UTF8，Oracle中对应的机制叫做表的存储方式。
常见的存储引擎：
MyISAM引擎：最常见的引擎。
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。
InnoDB引擎：默认的缺省引擎。
优点：支持事务、行级锁、外键（级联更新、级联删除）等，数据更安全。在mysql数据库崩溃之后提供自动恢复机制。
缺点：无法压缩，无法转换成只读。
在这里插入图片描述

MEMORY引擎：以前叫HEPA引擎，每个表均以.frm格式文件表示。
缺点：不支持事务，数据容易丢失断电就没，因为所有数据和索引都存储在内存当中，不能存储CLOB或BLOB字段。
优点：查询速度最快。

八、事务

概念：一个事务是完整的逻辑单元，不可再分。要么同时成功，要么同时失败，不允许有两条事务中一条成功，一条失败。事务的存在是为了保证数据的完整性和安全性。只有DML语句才支持事务。
机制：
例如执行insert语句…
开启事务：记录到缓存中
执行成功后，把这个执行记录到数据库的操作历史中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。
提交commit或回滚rollback事务：提交事务就是把缓存的操作执行并清空，回滚事务是把缓存清空并不执行
事务四大特性：ACID

A：原子性：事务是最小单元，不可再分
C：一致性：事务必须保证多条语句同时成功或同时失败
I：隔离性：事务A与事务B之间具有隔离
存在隔离级别，理论上隔离级别包括4个：
- 第一级别：读未提交read uncommitted，对方事务未提交，当前事务可以读到对方未提交的数据。存在脏读（dirty
  read）现象：表示读到了脏的数据。
- 第二级别：读已提交readcommitted，对方提交事务之后的数据，当前事务可以读取到。解决脏读现象，但存在不可重复读现象。
- 第三级别：可重复读repeatable read，解决了不可重复读现象，但存在读取到的数据是幻象。
- 第四级别：序列化/串行化serializable，解决了所有问题，效率低，需要事务排队。
- Oracle默认的级别是第二级别，MySQL默认的级别是第三级别
D：持久性：最终数据必须持久化到硬盘文件中，事务才算成功结束。
MySQL的事务默认情况是自动提交，只要执行任意一条DML语句就提交一次，可以关闭自动提交start transaction。

九、索引

概念：索引是帮助Mysql高效获取数据的排好序的数据结构，相当于一本书的目录，通过目录可以找到对应的资源。
在数据库方面，查询一张表有两种方式：
第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）
索引可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
添加索引是给字段添加的。
主键和具有唯一约束unique约束自动会添加索引。
考虑添加索引的考虑条件：

数据量庞大（根据需求和环境）
该字段很少DML操作
该字段经常出现在where子句中

索引实现原理：遇过B tree 缩小扫描范围，底层索引进行排序、分区，索引会携带数据在表中的物理地址，最终通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位表中的数据，效率最高。
索引分类：

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加一个索引
主键索引：主键自动添加索引
唯一索引：唯一约束的字段自动添加索引

索引什么时候失效：

（1）对列进行计算或者是使用函数，则该列的索引会失效
（2）不匹配数据类型，会造成索引失效
（3）where语句中使用了IS NULL或者IS NOT NULL，会造成索引失效
（4）使用了反向操作，该索引将不起作用
（5）使用了link（模糊查询）操作，索引就将不起作用，第一个通配符使用的是%
（6）在WHERE中使用OR时，有一个列没有索引，那么其它列的索引将不起作用

索引的数据结构：二叉树、红黑树、Hash表、B-Tree

二叉树：从根节点开始查找，大于根节点，向右查找，反之向左查找，弊端：如果存取是一个有序数，二叉树就会变成一个链表。
红黑树：也叫做二叉平衡树，当存取是一个有序数时，会自动平衡树的结构。弊端：如果存取的数非常大，树的高度就会很高，查找时依然很慢。
Hash表：把索引的值进行计算，得到的hash值与所对应的磁盘地址存到另一张表里，查找时根据hash值寻找对应的磁盘地址，然后找出对应的数据。弊端：不能进行范围查找。
B-Tree：叶节点具有相同的深度，叶节点指针为空；所有索引不重复；节点中的数据索引从左到右递增排列。
B+Tree（B-Tree的变种）：非叶子节点不存储data，只存储索引（冗余），可以放更多的索引；叶子节点包含所有索引字段；叶子节点用指针连接，提高区间访问的性能。

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。
聚集索引和非聚集索引是有叶子节点存储区别的

十、视图

概念view：站在不同的角度去看到数据，同一张表通过不同的角度去看待数据。对视图进行增删改查，会影响原表数据。
面向视图操作：所有的sql语句对视图都能操作。
视图的作用：视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，只对视图对象进行CRUD。

十一、 DBA命令（了解）

a) 新建用户
create user 用户名 identified by ‘密码’；
密码为空则该用户不需要密码。
给用户授权……这个对Java没啥用。
b) 导入导出
导出整个数据库
在windows的dos命令窗口中执行，不用登录进去：mysqldump 数据库>路径/文件名.sql –u用户 –p密码
导出指定库下的指定表
在windows的dos命令窗口中执行：mysqldump 数据库表名> 路径/文件名.sql -u用户 –p密码
导入：
登录MYSQL数据库管理系统之后执行：source 路径；

十二、数据库设计三范式（重点）

概念：设计表的依据，按照三范式设计的表不会出现数据冗余。

第一范式：任何一张表都应该有主键，并且每个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
多对多？三张表，关系表两个外键。
第三范式：建立在第二基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
班级与学生，一对多？两张表，多的表加外键。
注意：在实际开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。
最后：一对一，两种方案：一、主键共享。二、外键唯一

忆亦何为

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL学习笔记

A表中有外键约束引用B表的的字段，此时A表有外键约束的字段的值只能是B表引用字段的值，则称A表为子表，B表为父表，外键值可以为NULL，被引用的字段不一定为主键，但至少为唯一约束。索引实现原理：遇过B tree 缩小扫描范围，底层索引进行排序、分区，索引会携带数据在表中的物理地址，最终通过索引检索到数据后，获取到关联的物理地址，通过物理地址定位表中的数据，效率最高。只有DML语句才支持事务。概念：在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中的数据的合法性、有效性、完整性。
复制链接

扫一扫