MySQL
基本的命令行操作
mysql -uroot -p --链接用户名为 root 的数据库
update mysql.user set authentication_string=password("123456") where user='root' and Host='localhost'; --修改用户密码，本质就是修改语句，信息存在mysql数据库的user表中
flush privileges; --刷新权限
------------
show databases; --查看所有的数据库
use school --切换数据库，use + 数据库名
show tables; --查看数据库中所有的表
describe 表名; --查看表中所有的数据
create databases 数据库名; --创建一个数据库
exit; --退出链接
操作数据库
简单了解
- MYSQL 关键字不区分大小写
- 如果你的数据库名或者表名是特殊字符就需要加上 `` 环绕
--创建数据库
CREATE DATABASE IF NOT EXISTS learn_test
--删除数据库
DROP DATABASE IF EXISTS learn_test
--使用数据库
USE `learn_test` --`环绕`
列类型
数值
字符串
时间日期
null
- 没有值、未知
- 注意不要使用null进行运算，没有意义
字段属性
Unsigned
- 无符号的整数
- 该字段不能声明为负数
zerofill
- 0填充的
- 不足的位数用0来填充
自增
- 在上一条记录的基础上 + 1
- 通常来设置主键，必须是整数类型
- 可以自定义设计逐渐的初始值和步长
非空
- 设置为非空，该字段不赋值会报错
默认
- 设置默认值
- 不指定该列的值则初始值就是默认值
每一个表都需要有的字段
- id --- 主键
- version --- 乐观锁
- is_delete --- 伪删除
- gmt_create --- 创建时间
- gmt_update --- 更新时间
获取SQL语句
SHOW CREATE DATABASE 数据库名字 --获取建库SQL语句
SHOW CREATE TABLE 表名字 --获取建表SQL语句
DESC 表名字 --显示表的结构
数据表的类型
- INNODB 默认使用
- MYISAM 早些年使用的
物理空间存在的位置
- 所有数据库文件都存在data目录下
- 本质还是文件存储
修改删除表
修改
ALTER TABLE 表名 RENAME AS 新表名 --修改表名
ALTER TABLE 表名 ADD 字段名 字段属性 --添加新字段
ALTER TABLE 表名 MODIFY 字段名 字段属性 --修改约束
ALTER TABLE 表名 CHANGE 字段名 新字段名 新字段属性 --重命名字段
删除
ALTER TABLE 表名 DROP 字段名 --删除字段
DROP TABLE IF EXISTS 表名 --如果存在标则删除表
注意事项
- 字段名使用``包裹
- SQL大小写不敏感，但是建议使用小写，方便查看
- 所有的符号全部用英文
MySQL 数据管理
外键
仅了解
- 一般不在数据库级别使用外键约束
- 一般用程序实现外键
- 一切外键概念必须在应用层解决
DML语言
全部记住
- 数据操作语言
--增
INSERT INTO 表名 (字段名) VALUES (数据)
--删
DELETE FROM 表名 WHERE 判断条件 --如果没有指定条件会删除所有的列
TRUNCATE 表名 --清空表
--改
UPDATE 表名 SET 字段名=新数据 WHERE 判断条件 --如果没有指定条件会更改左右的列
- WHERE 后面可以跟的判断语句
- DELETE和TRUNCATE的区别
- 相同点：都删除数据而不删除表结构
- 不同点
- TRUNCATE 重新设置 自增列 计数器会归零
- TRUNCATE 不会影响事务
DQL语言
- 所有的查询操作都用它 SELECT
- 简单、复杂的查询都能做
- 数据库中最核心的语言，最重要的语句
- 使用频率最高
--查询所有
SELECT * FROM 表名
--查询指定字段
SELECT 字段名 FROM 表名
SELECT DISTINCT 字段名 FROM 表名 --去重查询
--取别名
SELECT 字段名 AS 字段别名 FROM 表名 AS 表别名
--函数 -> CONCAT(a,b) 凭借字符串
SELECT CONCAT('姓名：',字段名) AS 字段别名 FROM 表名
--奇怪的用法
SELECT VERSION() --查询系统版本
SELECT 1000-7 AS 别名 --计算
SELECT @@auto_increment_increment --查询自增的步长
模糊查询
-- % 任意个数量的字符
-- _ 一个字符
SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '张%'
-- IN 有其中的某个值
SELECT 字段名 FROM 表名 WHERE 字段名 IN (a,b,c)
联表查询
- 例子
- 多加一张表也是类似的操作
自链接
仅了解
-
自己的表和自己的表链接，核心：一张表拆成两张一样的表
-
例子
分页和排序
- 排序
- 降序 ：后面补上
ORDER BY 表名 DESC - 升序 ：后面补上
ORDER BY 表名 ASC
- 降序 ：后面补上
- 分页
- 限制查询的范围
LIMIT a,b查询从 a 开始的 b 条数据
语法
子查询
- 本质：在 WHERE 语句中嵌套一个查询语句
- 就是在 WHERE 中使用 SELECT 语句来获得查询条件
函数
- 基本函数
-- 数学函数
SELECT ABS() --绝对值
SELECT CEILING() --上取整
SELECT FLOOR() --下取整
SELECT RAND() --返回一个 0 ~ 1之间的随机数
SELECT SIGN() --判断一个数字的符号，负数返回 -1，整数返回 1
-- 字符串函数
SELECT CHAR_LENGTH --获得字符串长度
SELECT CONCAT(a,b) --拼接字符串
SELECT INSERT() --查询、替换
SELECT LOWER() --转小写
SELECT UPER() --转大写
SELECT INSTR() --查询字串第一次出现的位置
SELECT REPLACE() --替换字符串
SELECT SUBSTR() --返回指定的子串
SELECT REVERSE() --反转字符串
- 聚合函数
- 了解 MD5 加密
事务
- SQL 执行 A 给 B 转账
- SQL 执行 B 收到 A 的钱
将一组SQL放在一个批次中去执行
ACID原则
原子性
- 要么都成功，要么都失败
一致性
- 事务前后的数据完整性要保证一致
隔离性
- 事务的隔离性是多个用户访问数据库的时候，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，事务之间要相互隔离
持久性
- 事务一旦提交则不可逆，被持久化到数据库中
事务隔离可能出现的问题
脏读
- 指一个事务读取了另一个事务未提交的数据
不可重复读
- 在一个事务内读取表中的某一行数据，多次读取结果不同
虚读
- 在一个事务内读取到了其他事务插入的数据，导致前后读取不一致
MySQL中的事务
索引
- 通过索引可以更方便的获取内容
分类
-
主键索引 PRIMARY KEY
- 唯一的标识，主键不可重复，只能有一个列作为主键
-
唯一索引 UNIQUE KEY
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
-
常规索引 KEY / INDEX
- 默认的
-
全文索引 FullText
- 在特定的数据库引擎下才有
现在好像都有了
- 快速定位数据
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash 类型的索引
B-Tree ：InnoDB 默认的数据结构
权限管理和备份
权限管理
- 建议用可视化面板管理
备份
为什么要备份：
- 保证重要的数据不丢失
- 数据转移
MySQL 数据备份的方式
- 直接拷贝物理文件
- 在可视化工具中手动导出
- 使用命令行导出
数据库设计
软件开发中，关于数据库的设计
- 分析需求
- 概要设计：设计关系图 E-R 图
设计数据库的步骤
三大范式
为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 删除异常
第一范式
- 要求每一列都是不可分割的原子数据项
第二范式
- 前提：满足第一范式
- 每张表只做一件事情
- 确保每一列都和主键相关而不是主键的一部分相关
第三范式
- 前提：满足第一和第二凡是
- 确保数据表中的每一列的数据都和主键直接相关而不是间接相关
规范性和性能的问题
关联查询的表不得超过三张
- 考虑商业化的需求和目标，成本，用户体验
- 实际中数据库的性能更加重要
- 在规范性能的问题的时候，需要适当考虑规范性
- 故意给某些表增加一些冗余的字段，从多表查询编程单表查询
