深入浅出MYSQL数据库
2019-10-23 19:08:30 0 举报AI智能生成
深入浅出MYSQL数据库-思维导图
作者其他创作
大纲/内容
深入浅出MYSQL数据库
6.MYSQL存储引擎
MyISAM存储引擎
默认MyISAM的表会在磁盘中产生3个文件
.frm
表结构文件
.MYD
数据文件
.MYI
索引文件
可以在创建表的时候指定数据文件和索引文件的存储位置，只有MyISAM表支持
DATA DIRECORY [=] 数据保存的绝对路径
INDEX DIRECTORY [=] 索引文件保存的绝对路径
MyISAM单表最大支持的数据量2的64次方条记录
每个表最多可以建立64个索引
如果是复合索引，每个符合索引最多包含16个列，索引值最大长度是1000B
MyISAM引擎的存储格式
定长（FIXED 静态）
是指字段中不包含VARCHAR/TEXT/BLOB
动态（DYNAMIC）
字段中包含了VARCHAR/TEXT/BLOB
压缩（COMPRESSED）
myisampack创建
InnoDB存储引擎
设计遵循ACID模型，支持事务，具有从服务崩溃中恢复的能力，能够最大限度保护用户的数据
原子性（Atomiocity）
是不可分割的最小操作单位，要么同时成功，要么同时失败。
一致性（Consistency）
事务操作前后，数据总量不变
隔离性（Isolation）
多个事务之间。相互独立。
持久性（Durability）
当事务提交或回滚后，数据库会持久化的保存数据。
支持行级锁，可以提升多用户并发时的读写性能
支持外键，保持数据的一致性和完整性
InnoDB拥有自己独立的缓存池，常用的数据和索引都在缓存中
对于INSERT、UPDATE、DELETE操作，InnoDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘的I/O，提高性能。
创建InnoDB表之后会产生两个文件
.frm表结构文件
.idb，数据和索引存储表空间中
所有的表都需要创建主键，最好配合上AUTO_INCREMENT，也可以放到经常查询的列作为主键。
事务
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
事务提交的两种方式
自动提交：
* mysql就是自动提交的
\t* 一条DML(增删改)语句会自动提交一次事务。
手动提交：
* Oracle 数据库默认是手动提交事务
* 需要先开启事务，再提交
修改事务的默认提交方式：
\t\t\t* 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交\t\t\t* 修改默认提交方式： set @@autocommit = 0;
事务的隔离级别
read uncommitted：读未提交
* 产生的问题：脏读、不可重复读、幻读
read committed：读已提交 （Oracle）
* 产生的问题：不可重复读、幻读
repeatable read：可重复读 （MySQL默认）
* 产生的问题：幻读
serializable：串行化
* 可以解决所有的问题
注意：隔离级别从小到大安全性越来越高，但是效率越来越低
数据库查询隔离级别：\t\t\t* select @@tx_isolation;
数据库设置隔离级别：\t\t\t* set global transaction isolation level
7.MYSQL数据操作
添加记录
不指定字段名称
INSERT tbl_name VALUE(value...)
按照建表时的字段顺序进行赋值
列出指定字段
INSERT ... SET 的形式
INSERT ... SELECT
一次添加多条记录
修改记录
如果不添加条件，整个表中的记录都会被更新
删除记录
DELETE FROM tbl_name [WHERE 条件]
如果不添加条件，表中所有记录都会被删除
DELETE清空数据表的时候，不会重置AUTO_INCREMEMT的值，可以通过ALTER语句将其重置为1
彻底清空数据表
TRUNCATE [TABLE] tbl_name;
清除表中所有记录
会重置AUTO_INCREMENT的值
查询记录
查询表中所有记录
SELECT * FROM tbl_name;
指定字段的信息
库名.表名
给字段起别名
给数据表起别名
表名.字段名的形式
去重name
SELECT DISTINCT name FROM tbl_name;
不能显示其他列
一般查询不重复记录使用group by
WHERE条件
筛选出符合条件的记录
比较运算符
<=> 和=的区别
<=>可以检测NULL值
IS [NOT] NULL
检测值是否为NULL或者NOT NULL
指定范围
[NOT] BETWEEN ... AND
指定集合
逻辑运算符
AND
逻辑与
OR
逻辑或
匹配字符串
[NOT] LIKE
%
任意长度的的字符串，零个，一个或多个
_
任意一个字符
例如：要求用户名中包含三
例如：用户名长度为三位的用户
GROUP BY 分组
把值相同的放到一个组里面，最终查询出的结果只会显示组中一条记录。
分组配合GROUP_CONCAT()查看组中某个字段的详细信息
配合聚合函数使用
COUNT()
如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来
如果写COUNT(*)会统计NULL值(不推荐使用)
SUM()
求和
MAX()
求最大值
MIN()
求最小值
AVG()
求平均值
聚合函数：将一列数据组为一个整体，进行纵向的计算。注意：聚合函数的计算排除null值。
配合WITH ROLL关键字使用
会在记录末尾添加一条记录，是上面所有记录的总和
HAVING子句对分组结果进行二次筛选
HAVING后面可以加通过聚合函数操作的列或者SELECT查询到的列
例如：查询分组后人数大于3的
SELECT addressGROUP_CONCAT(username) AS usersDetailCOUNT(*) AS totalUsersFROM userGROUP BY addrHAVING COUNT(*)>3;
HAVING和WHERE的区别：
WHERE在分组前限定，如果不满足则不参与分组，HAVINg在分组后限定，如果不满足则不会被查询出来。
WHERE后不可以跟聚合函数，HAVING可以进行聚合函数的判断
ORDER BY 排序
ORDER BY 字段名称 ASC(升序(默认))|DESC(降序);
LIMIT 限制结果集显示条数
LIMIT 值
显示结果集前几条记录
从offset开始，显示row_count条记录，offset从0开始
开始的索引=( 当前的页码 - 1 ) * 每页显示的条数
多表查询
笛卡尔积的形式
内连接的形式
查询两个表中符合连接条件的记录
外连接的形式
左外连接
先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的用NULL代替。
右外连接
先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的用NULL代替。
外键约束
只有InnoDB存储引擎支持外键
保持数据的一致性和完整性
创建外键
建表时指定外键
[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
子表的外键字段和主表的主键字段要相似；如果是数值型要求一致，并且无符号也要一直；如果是字符型，要求类型一致，长度可以不同。
如果外键字段没有创建索引，MySQL会自动添加索引
子表的外键关联的必须是父表的主键
外键约束的参照操作
CASCADE
从父表删除或更新，子表也更着删除或者更新，级联的操作
SET NULL
从父表删除或者更新记录，并且设置子表的外键列为NULL。
NO ACTION | RESTRIC
拒绝对父表做更新或者删除操作
动态添加外键
ALTER TABLE tbl_nameADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
动态添加外键之前，表中的记录一定是合法的记录，不能有脏值，否则外键添加不成功。
动态删除外键
ALTER TABLE tbl_nameDROP FOREIGN KEY fk_name;
特殊形式的查询
子查询
SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name);
内层语句查询的结果可以作为外层语句查询的条件
由IN引发的子查询
由比较运算符引出的子查询
由EXISTS引发的子查询
ANY SOME ALL
|运算符 | ANY |SOME| ALL ||<、>= |最小值|最大值|最大值|<、>= |最大值|最大值|最小值|| = |任意值|任意值| ||<>、！=| | |任意值|
CREATE ... SELECT
创建表的时候将查询的数据写进去
CREATE TABLE tbl_name1 LIKE tbl_name2;
创建一个同某个表结构一致的表
联合查询
UNION
会去掉表中重复的数据
UNION ALL
简单的合并
自身连接查询
无限级分类
8.MYSQL常用函数
数学函数
CEIL()
进一取整
FLOOR()
舍掉小数部分
ROUND()
四舍五入
TRUNCATE()
截取小数点后几位
MOD()
取余数
ABS()
取绝对值
PI()
圆周率
RAND()或者RANG(X)
0~1之间的随机数
EXP()
计算e的x次方
字符串函数
CHAR_LENGTH()
字符串的字符数
LENGTH()
返回字符串的长度
将字符串合并成一个字符串
如果拼接的字符串中有NULL，返回的结果就是NULL
以指定分隔符x拼接字符串
如果使用NULL进行拼接，返回的就是NULL
如果拼接的字符串有NULL，不会影响其他的
UPPER()、UCASE()、LOWER()、LCASE()
大小写转换
REVERSE()
反转函数
LEFT()|RIGHT()
返回字符串前几个或者后几个字符
TRIM()
去除前后空格
替换s中s1为s2
日期时间函数
CURDATE()、CURRENT_DATE()
返回当前日期
CURTIME()、CURRENT_TIME()
返回当前时间
NOW()、CURRENT_TIMESTAMP()、SYSDATE()
返回当前的日期时间
MONTH(CURDATE())
返回日期的月份
MONTHNAME(CURDATE())
返回月份的名字
DAYNAME(NOW())
返回星期几
DAYOFWEEK(NOW())
返回一周内的第几天
YEAR(NOW())、MONTH(NOW())、DAY(NOW())、HOUR(NOW())、MINUTE(NOW())、SECOND(NOW())
两个时间相差几天
DATE_FORMAT(日期，格式字符串)
STR_TO_DATE(字符串，日志格式)
其他常用函数
VERION()
版本
CONNECTION_ID()
当前连接数
USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()
当前登录的用户
LAST_INSERT_ID()
得到上一次插入操作产生的AUTO_INCREMENT的值
MD5()、PASSWORD()
进行加密
PASSWORD()是密码加密算法
常用SQL语句
查看上一步操作产生的警告信息
SHOW WARNINGS;
mysql注释
# 注释内容
-- 注释内容
CHECK TABLE tbl_name
检测表
REPAIR TABLE tbl_name
修复表
数据库设计
多表之间的关系
一对一
一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键
一对多（多对一）
在多的一方建立外键，只想一的一方的主键
多对多
多对多的关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。
数据库设计的范式
概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有所有范式要求
第一范式（1NF）
每一列都是不可分割的原子数据项
第二范式（2NF）
在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
第三范式（3NF）
在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
1.什么是数据库？
数据库是按照数据结构来组织、存储和管理数据的仓库
常见数据库
Oracle
DB2
SQL Server
MySQL
相关术语
数据库系统(Database System):DBS
数据库(Database)
数据库管理系统(DBMS)
应用开发工具
管理员及用户
SQL语言(结构化查询语言)
DDL(数据定义语言)
DML(数据操作语言)
DQL(数据查询语言)
DCL(数据控制语言)
2.MYSQL相关操作
配置文件
my.cnf是MYSQL的配置文件
登录/退出MYSQL
登录
mysql -uroot -p
mysql -uroot -proot(用户名密码为root)
得到版本号
mysql -V
mysql --version
登录的同时打开数据库
mysql -uroot -p -D databasename
退出
exit
quit
登录信息中需要掌握的
命令行结束符默认使用;或者\\g来结束
可以通过help或者\\h或者?加上相关关键字来查看手册
\\c可以取消当前命令的执行
SQL语句语法规范
常用的MySQL的关键字我们需要大写，库名、表名、字段名等使用小写
SQL语句支持拆行操作，拆分的时候不能将完整单词拆开
数据库名称、表名称、字段名称不要使用MySQL的保留字，如果必须使用，需要用反引号括起来
SELECT USER()
得到登录的用户
SELECT NOW()
得到当前时间
SELECT VERSION()
得到MySQL的版本信息
SELECT DATABASE()
得到当前打开的数据库
SQL分类
DCL(Data Control Language)数据控制语言(了解)\t\t用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等
3.数据库相关操作
创建数据库
CREATE {DATABASE|SCHEMA} db_name;
检测数据库名称是否存在，不存在则创建
CREATE DATABASE IF NOT EXISTS db_name;
创建数据库的同时指定编码方式
CREATE DATABASE IF NOT EXISTS db_name [DEFAULT] CHARACTER SET 'UTF-8'
注意
数据库名称最好有意义
名称不要包含特殊字符或者是MySQL关键字
查看当前服务器下全部数据库
SHOW DATABASE|SCHEMAS;
查看指定数据库的详情信息
SHOW CREATE DATABASE db_name;
修改指定数据库的编码方式
ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;
打开指定数据库
USE db_name;
SELECT DATABASE()|SCHEMA();
删除指定的数据库
DROP DATABASE db_name;
如果数据库存在则删除
DROP DATABASE IF EXISTS db_name;
备份个还原
图形化工具
命令行
备份
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
还原
\t\t\t1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径
4.数据表相关操作
数据表
是数据库最重要的组成部分之一，数据是保存在数据表中
数据表由行row和列column来组成
每个数据表至少有一列，行可以有零行一行或者多行组成
表名要求唯一，不要包含特殊字符，最好含义明确
创建表
完整性约束条件
unsigned
无符号，没有负数，从0开始
zerofill
零填充，当显示长度不够的时候可以使用前补0的效果填充至指定长度
NOT NULL
非空约束，也就是插入值的时候这个字段必须要有值
DEFAULT
默认值，如果没有插入记录的时候没有给字段赋值，就是用默认值
PRIMARY KEY
主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动的就禁止为空
UNIQUE KEY
唯一性，一个表中可以有多个字段为唯一索引，值不能重复，NULL值除外
AUTO_INCREMENT
FOREIGN KEY
查看当前数据库下已有数据表
SHOW TABLES;
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
查看指定数据表的详细信息
SHOW CREATE TABLE tbl_name;
查看表结构
DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;
删除指定的数据表
DROP TABLE [IF EXISTS] tbl_name;（直接删除表信息，最快）
TURNCATE TABLE tbl_name；（删除表数据，不删除表的结构，速度排第二）
DELETE FROM tbl_name WHERE case；(根据条件删除数据，不删除表结构，速度慢，一条一条删除)
复制表
create table 表名 like 被复制的表名;
表结构相关操作
添加字段
ALTER TABLE tbl_name ADD 字段名称 字段属性 [完整性约束] [FIRST|AFTER 字段名称]
删除字段
ALTER TABLE tbl_name DROP 字段名称
添加默认值
ALTER TABLE tbl_name;ALTER 字段名称 SET DEFAULT 默认值;
删除默认值
ALTER TABLE tbl_name;ALTER 字段名称 DROP DEFAULT;
修改字段类型、字段属性
ALTER TABLE tbl_nameMODIFY 字段名称 字段类型 [字段属性] [FIRST|AFTER 字段名称]
修改字段名称、字段类型、字段属性
ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 字段类型 [字段属性] [FIRST|AFTER 字段名称]
添加主键
ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)
删除主键
ALTER TABLE tbl_name DROP PRIMARY KEY;
添加唯一
ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX index_name (字段名称)
删除唯一
ALTER TABLE tbl_name DROP INDEX colunm_name;
修改数据表名称
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
RENAME TABLE tbl_name TO new_tbl_name;
修改AUTO_INCREMENT的值
ALTER TABLE tbl_name AUTO_INCREMENT=值;
添加自增
ALTER TABLE tbl_name MODIFY colunm_name INT AUTO_INCREAMENT;
删除自增
ALTER TABLE tbl_name MODIFY colunm_name INT;
5.MYSQL中的数据类型
数值型
整数型
TINYINT
1字节
SMALLINT
2字节
MEDIUMINT
3字节
INT
4字节
BIGINT
8字节
BOOL，BOOLEAN
等价于TINYINT(1)，0为false，其余为true，1字节
**注意还分有无符号**
浮点数
单精度浮点数，4字节，M是数字总位数，D是小数点后的位数。
双精度浮点数，8字节
定点数，M+2字节
定点数
如果超出范围，会截断，对于小数会四舍五入，显示最大值/最小值，不报错，警告。
字符串类型
CHAR(M)
M个字节，0<=M<=255
VARCHAR(M)
L+1个字节，L<M，且0<=M<=65535
TINYTEXT
L+1个字节，L<2的8次方
TEXT
L+2个字节，L<2的16次方
MEDIUMTEXT
L+3个字节，L<2的24次方
LONGTEXT
L+4个字节，L<2的32次方
1或2个字节，取决于枚举的个数
1、2、3、4或8个字节，取决于SET成员的数目
字符串类型备注
CHAR默认存储数据的时候，后面会用空格填充到指定长度；而在检索的时候拿掉后面空格；VARCHAR在保存的时候不进行填充，尾部的空格会留下。
CHAR效率高于VARCHAR，CHAR相当于拿空间换时间，VARCHAR拿时间换空间
TEXT列不能有默认值，检索的时候不存在大小写转换
枚举类型会自动过滤掉空格
日期时间类型
TIME
-838:59:59~838:59:59
DATE
1000-01-01~9999-12-31
DATETIME
1000-01-01 00:00:00~9999-12-31 23:59:59
TIMESTAMP
1970-01-01 00:00:01~2038-01-19 03:14:07
YEAR
1901~2155
DROP&DELETE
收藏
0 条评论
回复 删除
下一页
