MySql

MySql学习笔记

0 认识MySql

0.1 MySql特性

0.1.1 MySql注释

• -- Sql注释是两个-一个空格

• 多行注释与C语言一致/**/

0.1.2 MySql结构

• 数据库-表-数据

0.1.3 MySql的数据文件

• 文件都存储在data文件夹下

0.2 听听就好

 CRUD 增删改查
 每一个表都要有以下五个字段
 id 主键
 `version` 乐观锁
 is_delete 伪删除
 gmt_create 创建时间
 gmt_update 修改时间

1 数据库定义语言 DDL

1.1 连接数据库

 mysql -uroot -p密码 -- 连接数据库

1.2 修改密码

 update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost';

1.3 刷新权限

 flush privileges;  -- 刷新权限

1.4 退出连接

 exit; -- 退出连接

 

2 数据库操作语言 DML

2.1 数据库的操作

2.1.1 创建数据库

 CREATE DATABASE [IF NOT EXISTS] 数据库名; -- [如果不存在就]创建数据库 *方括号为可删去内容*

2.1.2 删除数据库

 DROP DATABASE IF EXISTS 数据库名; -- 删除数据库 

2.1.3 使用数据库

 USE 数据库名; -- *如果表名或字段名是特殊字段，在前后加`，比如`user`*

2.1.4 查看数据库

 SHOW DATABASES; -- 查看所有的数据库
 SHOW CREATE DATABASE 数据库名; -- 查看该数据库的创建语句构造 

2.2 表的操作

2.2.1 关于表的前置知识

2.2.1.1 表列的类型

• 数值

   整型
   tinyint   1字节
   smallint  2字节
   mediumint 3字节
   int       4字节 *常用类型
   bigint    8字节
   浮点型
   float     4个字节
   double    8个字节
   字符串浮点型（全精度）
   decimal   

• 字符

   char       0~255
   varchar    0~65535 *常用类型
   tinytext   0~255
   text       0~65535

• 时间日期

   date       YYYY-MM-DD
   time       HH:mm:ss
   datetime   YYYY-MM-DD HH:mm:ss *常用类型
   timestamp  1970-01-01到现在的毫秒数
   year       YYYY

• null

   没有值or未知
   不要使用null进行运算

2.2.1.2 数据库的字段属性

• unsigned

   无符号整数
   声明了该列不能声明为负数 

   

• zerofill

   用0填充满

• 自增 AUTO_INCREAMENT

   通常理解为在上一条记录上+1
   通常用来设计唯一的主键
   可以自定义设置主键自增的起始值和步长

• 非空

   假设设置为not null
   那么不填值就会报错

   

• 默认值

   设置不填充时的默认值

2.2.1.3 数据表的类型/数据库的引擎

表类型	INNODB(默认)	MYISAM(旧)
事务支持	支持	不支持
数据行锁定	支持	不支持
外键约束	支持	不支持
全文索引	不支持	支持
表空间大小	较大 2倍	较小 1倍
速度	慢	快
安全性能	高	低

INNODB对应文件

• *.frm 表结构定义文件

• 上级目录下的ibdata1文件

MYSIAM对应文件

• *.frm - 表结构定义文件

• *.MYD - 数据文件(data)

• *.MYI - 索引文件 (index)

2.2.1.4 数据表的字符编码

 CHARSET = 字符编码符

• utf8是最常见的汉字编码集

2.2.2 创建表

2.2.2.1 标准格式

 CREATE TABLE [IF NOT EXISTS] `表名` (
     `字段名` 列类型 [属性] [索引] [注释],
     `字段名` 列类型 [属性] [索引] [注释],
     .....
     `字段名` 列类型 [属性] [索引] [注释],
     主键('字段名')
 )[表类型][字符集设置][注释]

2.2.2.2 注意事项

 表名和字段尽量用`标注
 字符串用单引号括起来
 语句后加逗号
 PRAIMARY KEY 唯一主键

2.2.2.3 样例需求

 创建一个命名为school的数据库
 创建学生表，使用SQL创建
     学号int
     密码varchar(0)
     姓名varchar(2)
     性别varchar(2)
     出生日期datatime
     家庭住址varchar(20)
     email varchar(20)

2.2.2.4 样例代码

 CREATE TABLE IF NOT EXISTS student(
     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
     `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
     `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
     `sex` VARCHAR(20) NOT NULL DEFAULT '女' COMMENT '性别',
     `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
     PRIMARY KEY(`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8

2.2.3 查询表

2.2.3.1 显示所有的表

 SHOW TABLES; -- 显示所有的表

2.2.3.2 查看表的细节

 DESC 表名; -- 查看该表的细节
 DESCRIBE 表名; -- 同上

2.2.3.3 解析表的构造

 SHOW CREATE TABLE 数据库名; -- 查看该表的创建语句构造

2.2.4 修改表

2.2.4.1 修改表名

 ALTER TABLE 旧表名 RENAME AS 新表名; -- 修改表名

2.2.4.2 增加表的字段

 ALTER TABLE 表名 ADD 字段 类型; -- 增加表的字段

2.2.4.3 MODIFY 字段类型修改

 ALTER TABLE 表名 MODIFY 字段名 新类型/约束名; -- 字段类型修改

2.2.4.4 CHANGE 字段名字修改

 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型; -- 字段重命名

2.2.4.5 字段删除

 ALTER TABLE 表名 DROP 字段名; -- 删除表的字段

2.2.5 删除表

 DROP TABLE IF EXISTS 表名; -- 删除表

2.3 连接数据库中的表的数据

2.3.1 物理外键(工程中不使用)

表A的某个字段链接到表B上

A是主表，B是从表

 ALTER TABLE 表名; -- 打开表
 ADD CONSTRAINT 外键名 FOREIGN KEY (本表的字段名) REFERENCES 外表名 (外表的字段名); -- 创建外键

• 数据库只用来存数据即可

2.3.2添加

 INSERT INTO 表名([字段1,字段2,字段3])VALUES(值1,值2,值3); -- 可以根据字段顺序调整顺序
 INSERT INTO 表名 VALUES(值1,值2,值3); -- 值必须按顺序一一对应
 INSERT INTO 表名 VALUES(值1,值2,值3),(值1,值2,值3); -- 同时插入多条

2.3.3修改 & 加密

 UPDATE 表名 SET 字段名 = 新值; -- 无条件修改一个属性
 UPDATE 表名 SET 字段名 = 新值 WHERE 条件; -- 有条件修改一个属性
 UPDATE 表名 SET 字段名1 = 新值1,字段名2 = 新值2... WHERE 条件; -- 有条件修改多个属性
 ​
 UPDATE 表名 SET pwd=MD5(pwd) WHERE 条件; -- MD5加密

条件操作符见3.2

2.3.4删除

2.3.4.1 DELETE

 DELETE FROM 表名; -- 全部删除，不推荐使用
 DELETE FROM 表名 WHERE 条件; -- 有条件的删除

2.3.4.2 TRUNCATE

 TRUNCATE 表名; -- 清空所有的值，并归零计数器，不影响事务

DELETE删除在重启数据库后

INNODB自增列会从1开始

MYISAM自增列会从上一个自增量开始

3 数据库查询语言 DQL

3.1 SELECT-基本结构

 SELECT [ALL | DISTINCT] -- DISTINCT 去重
 {* | 表名.* | [表名.字段名[AS 别名][,表名.字段名[AS 别名]][,...]]}
 FROM 表名1 AS 别名
     [LEFT | RIGHT | INNER JOIN 表名2] -- 联合查询
     [WHERE ...] -- 指定结果满足的条件
     [GROUP BY ...] -- 指定结果按照哪几个字段来分组
     [HAVING] -- 过滤分组记录满足的次要条件
     [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
     [LIMIT {[offset.]row_count | row_countOFFSET offset}]; -- 指定数量分页

3.2 筛选与过滤

3.2.1 WHERE-条件子句

条件操作符

操作符	含义
=	等于
<>或!=	不等于
>	大于
<	小于
>=	大于或等于
<=	小于或等于
AND &&	和
OR ||	或
NOT !	非
BETWEEN X AND Y	[x,y]闭区间
IS NULL	是NULL
IS NOT NULL	不是NULL
X LIKE Y	X和Y通配符匹配('%'不定长通配,'_'一长度通配)
IN (...,...,...,...)	查询是否在集合中

例:ID = 1 AND ID > 3，即除ID为2之外的所有

3.2.2 常用函数

• 绝对值函数 ABS()

• 向上取整CEILING()

• 向下取整FLOOR()

• 随机数RAND()

• 符号判断函数SIGN()

• 字符串长度函数CHAR_LENGTH()

• 字符串拼接函数CONCAT("1","2","3")

• 字符串替代函数，1从X到Y替换成2INSERT("1",X,Y,"2")

• 小写转换函数LOWER()

• 大写转换函数UPPER()

• 返回第一次出现的子串索引 INSTR("长","子串")

• 替换关键子串REPLACE("长","被替换","替换")

• 返回指定的子字符串SUBSTR("长",截取位置,截取长度)

• 字符串反转REVERSE("")

• 获取当前日期CURRENT_DATE()

• 获取当前日期CURDATE()

• 获取当地时间NOW()

• 本地时间LOCALTIME()

• 系统时间SYSDATE()

• 年YEAR(NOW())

• 月MONTH(NOW())

• 日DAT(NOW())

• 小时HOUR(NOW())

• 分钟MINUTE(NOW())

• 秒SECOND(NOW())

• 查询系统用户SYSTEM_USER()

• 查询用户USER()

• 查询版本VERSION()

3.2.3 聚合函数

函数名	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

3.2.4 分组过滤

 GROUP BY 字段名 -- 通过什么字段分组
 HAVING 条件子句 -- fen'zu'hou

3.3 多个表的连接与查询

3.3.1 JOIN-联表查询

3.3.1.1 两表连接

两个表集合的查询集合，集合中的元素是表的字段

 SELECT 别名.字段名,字段名... -- 两表均有的重复列的才需要写别名确定是哪个
 FROM 左表名 AS 左别名
 (INNER|LEFT|RIGHT) JOIN 右表名 AS 右别名
 /*
 INNER会在两边找 只要返回就行
 LEFT会在左边找 即使右边的值是NULL
 RIGHT会在右边找 即使左边的值是NULL
 */
 WHERE|ON 条件 

join on 连接查询(在连接时查询过滤)

where 等值查询(连接后再查询值过滤)

3.3.1.2 多表连接

 SELECT
 FROM
 XXX JOIN
 ON
 XXX JOIN
 ON
 ...

3.3.2 自连接

自己和自己连接，核心是把一张表拆成两张表，树结构

 SELECT  别名1.字段名1 AS '父',别名2.字段名1 AS '子'
 FROM 表名1 AS 别名1,表名2 AS 别名2;

3.3.3 子查询

 SELECT 字段名 FROM 表名
 WHERE 字段名 = (SELECT ....)嵌套查询

3.4 ORDER BY-排序

 SELECT 字段名
 FROM 表名
 ORDER BY 字段名 ASC|DESC; -- 升序ASC，降序DESC 

3.5 LIMIT-分页

 LIMIT -- 起始下标,显示数量
 LIMIT (n-1)*pageSize,pageSize -- 当前为第n页

4. 事务

• 例子：转账，本质上把一组SQL放在一起执行

4.1 ACID原则

• 原子性(Atomicity)：针对同一个事务，要么都成功要么都失败

• 一致性(Consistency)：转账双方的总金额应当是不变的

• 隔离性(Isolation)：多人同时给一个人转账不会发生影响

• 持久性(Durability)：事务未提交发生意外，会回档。而提交后不可逆。

4.2 容易出现的问题

• 脏读：一个事务读取了另一个事务未提交的数据

• 重读：不一定是错误，一般是场合不对

• 幻读：读着读着新输入的表更新了

4.3 事务语法

 SET autocommit = 0|1; -- 0不自动提交,1自动提交 取0为手动处理事务
 START TRANSACTION -- 标记一个事务启动
 INSERT XX
 INSERT XX
 ​
 COMMIT -- 提交：持久化-成功
 ROLLBACK -- 回滚：回到原来的样子-失败
 ​
 SET autocommit = 1; -- 开启自动提交
 ​
 SAVEPOINT 保存点名; -- 设置一个事务的保存点
 ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
 RELEASE SAVEPOINT 保存点; -- 删除保存点
 ​

1. 关闭自动提交

2. 开启一i个服务

3. 提交并到4或者回滚并到1

4. 开启自动提交

5 索引

索引的本质是让SQL快速获取数据的数据结构

5.1 索引的分类

• 主键索引 (PRIMARY KEY)

  主键索引不可重复，仅有一列是主键索引

• 唯一索引(UNIQUE KEY)

  避免重复的列出现，唯一索引可以重复，

• 常规索引(KEY/INDEX)

  默认的，INDEX，KEY关键字来设置

• 全文索引(FULLTEXT)

  在特定的数据库引擎下才有，MYISAM

  目的是快速定位数据

5.2 基础语法

EXPLAIN 分析后面语句的运行状况

SHOW INDEX FROM 表名

ALTER TABLE 表名 ADD 索引类 (我没学完整，没懂)

5.3 索引原则

• 索引不是越多越好

• 经常变动的不要加索引

• 小数据量不要做索引

• 在常查询少修改的字段上索引

6 数据库管理语言DCL

6.1 用户管理

用户信息储存在mysql.user下

 CREATE USER 用户名 IDENTIFIED BY ‘密码’; -- 创建新用户
 SET PASSWORD = PASSWORD('新密码'); -- 修改按当前用户密码
 SET PASSWORD FOR 用户名 = PASSWORD('新密码'); -- 修改一个目标用户的密码
 RENAME USER 旧用户名 TO 新用户名; -- 用户的重命名
 GRANT ALL PRIVILEGES ON *.* TO 用户名; -- 给某个用户某个表的全部权限
 SHOW GRANTS FOR 用户名; -- 查看指定用户的权限
 REVOKE ALL PRIVILEGES ON *.* TO 用户名; -- 去掉某个用户某个表的全部权限
 DROP USER 用户名; -- 删除用户

6.2 MySql备份

为什么要备份：

• 保证重要的文件不丢失

• 数据转移

MySql数据库备份的方式

• 直接拷贝物理文件

• 在SqlYog中手动导出

• 使用命令行导出导入

 -- mysqldump -h 主机 -u 用户名 -p 密码 数据库 [表1,表2,表3] >物理磁盘位置/文件名 -- 导入
 -- source 物理磁盘位置/文件名 -- 导出

7 规范数据库设计

当数据库比较复杂的时候我们就需要设计了

糟糕的数据库设计：

• 数据冗余

• 数据插入和删除都麻烦，异常 //屏蔽使用物理外键

• 程序性能差

良好的数据库设计：

• 节省内存空间

• 保证数据库的完整性

• 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的请求

• 概要设计：设计关系图E-R图

设计数据库的步骤：(个人博客)

1. 收集信息-分析需求

   用户表

   分类表

   文章表

   友链表

   自定义表

这一章没有做完笔记，见狂神视频

8 三大范式

8.1为什么要数据规范化

• 信息重复

• 更新异常

• 插入异常

  无法显示正常信息

• 删除异常

  丢失有效的信息

8.2 第一范式：原子性

每一列不可再分

8.3 第二范式：独立性

每一张表的每一个值都只和主键相关

8.3 第三范式：直接性

每一张表的每一个值都和主键直接相关

8.4 规范性和性能的冲突

关联查询的表不得超过三张

商业化需求会故意增加可计算列和冗余数据，空间换时间(个人理解)

成本用户体验和可扩展性的冲突

9 JDBC

9.1 数据库驱动

程序只能通过驱动程序和数据库打交道

9.2 JDBC简介

JAVA内置了JDBC和驱动打交道，于是JAVA程序员不再需要学习驱动（硬件层）只需要学习JDBC（软件层）就行了

狂神MYSQL课P38之后未学习