MySQL学习笔记

本文主要是介绍MySQL学习笔记，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1、命令行连接数据库

net start mysql -- 启动MySQL服务

mysql -uroot -p123456 -- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host= 'localhost'; -- 修改用户密码

flush privileges; -- 刷新权限

net stop mysql -- 停止MySQL服务
-----------------------------------------------------------------
show databases; -- 注意分号结尾，查看所有的数据库

 use mysql -- 使用mysql表
 
 show tables; -- 查看数据库中所有表
 
 use student;
 describe stu; -- 显示student数据库的stu表的所有信息
 
 create database westos; -- 创建westos数据库
 
 show create database school; -- 查看创建数据库的语句
 
 show create table student; -- 查看创建表的定义语句
 
 desc student; -- 查看表结构
 
 EXPLAIN select * from user -- 查看SQL执行的状况EXPLAIN
 
 exit; -- 退出连接
 
 -- sql单行注释
 /*
 	sql多行注释
 */

2、操作数据库

MySQL关键字不区分大小写

2.1、创建数据库

CREATE DATABASE IF NOT EXISTS `school` CHARACTER SET utf8 COLLATE utf8_general_ci; -- 创建数据库并设置字符编码与字符集校对
CREATE DATABASE westos; -- 如果已经存在westos数据库，会报错
CREATE DATABASE IF NOT EXISTS westos; -- 如果已经存在westos数据库，不会报错只会警告

2.2、删除数据库

DROP DATABASE [IF EXISTS] westos;

2.3、使用数据库

USE `student`; -- 如果你的表名或者字段名是一个特殊字符，则需要带 `` 

2.4、查看数据库

SHOW DATABASES; -- 查看所有的数据库

3、数据库的列类型

3.1、数值

tinyint -- 十分小的数据 1个字节
smallint -- 较小数据 2个字节
mediumint -- 中等大小的数据 2个字节
int -- 标准的整数 4个字节 常用的
bigint -- 较大的数据 8个字节

3.2、浮点数

float -- 浮点数 4个字节
double -- 浮点数 8个字节(存在精度问题)

-- 金融计算的时候一般使用decimal:
DECIMAL(M,N) -- 字符串形式的浮点数
-- M表示：不包括小数点，不包括符号位，所能存数字的总位数（包括小数部分和整数部分） 
-- N表示：小数部分数字的位数，由此可知整数部分的位数为M-N
DECIMAL(4,2) -- 能够表示的数的范围 -99.99到99.99

3.3、字符串

char 字符串固定大小 0~255
varchar 可变字符串 0~65535 常用的 对应Java中的String
tinytext 微型文本 2^8 - 1
text 文本串 2^16 - 1 保持大文本

3.4、时间格式

date YYYY-MM-DD 日期格式
time HH:mm:ss 时间格式
datetime YYYY-MM-DDHH:mm:ss 最常用的时间格式
timestamp 时间戳 1970.1.1 到现在的毫秒数
year 年份表示

3.5、null

-- 没有值 未知
-- 注意：不要使用null进行运算

4、数据库的字段属性（重点）

4.1、Unsgned：

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

4.2、zerofill：

-- 0填充的
-- 不足位数，使用0来填充 如int(3),5 --> 005

4.3、自增(Auto Inor)：

-- 通常理解为自增，自动在上一条记录的基础上+1(默认)
-- 通常用来设计唯一的主键 index， 必须是整数类型
-- 可以自定义设计主键自增的起始值和步长

4.4、非空(Not Null)：

-- 若勾选PK设置为主键，则自动勾选Not Null
-- 若选择非空，则不允许字段为空(空指的是null,什么都不填指空字符串"")，如果为空会报错
-- 若不选择非空，如果不填写值，默认就是null

4.5、defult：

-- 设置默认值
-- sex，默认值为男，如果不指定该列的值，则会有默认的值

4.6、PK(主键)：

-- 是否为主键

4.7、创建数据库表实例

USE  school; -- 先使用到数据库再创建表
CREATE TABLE IF NOT EXISTS `student` ( -- 使用 `` 包括起来避免不小心与MySQL关键字重名
    `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(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '生日',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
     PRIMARY KEY (`id`) -- 设置id为主键
)ENGINE = INNODB DEFAULT CHARSET = utf8

4.8、创建数据库表的字符集编码

CHARSET = utf8

不设置的话，会是MySQL默认的字符集编码Latin1，不支持中文。

也可以在 my.ini 文件中配置默认的编码，不建议这样做，修改物理文件会导致代码通用性降低，即在自己电脑上能运行，在别人电脑上可能会失败。

character-set-server = utf8

5、MyISAM 和 InnoDB区别

-- 关于数据库引擎
/*
    INNODB 默认使用
    MYISAM 早些年使用
*/

常规使用

• MYISAM：节约空间,速度最快

• InnoDB：安全性高,事务的处理,多表多用户操作

在物理空间的位置

• 所有的数据库文件都存放在data目录下，一个文件夹就代表一个数据库

本质还是文件存储

• MySQL引擎在屋里文件上的区别：
  • InnoDB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
  • MYISAM 对应的文件：*.frm-表结构的定义文件, * .MYD-数据文件(data)， *.MYI-索引文件(index)

6、修改删除表

-- 修改表
ALTER TABLE `student` RENAME AS `student1`; -- 修改表名
ALTER TABLE `student1` ADD age INT(11); -- 添加表的字段
ALTER TABLE `student1` MODIFY age VARCHAR(11); -- 修改表的字段（修改约束）
ALTER TABLE `student1` CHANGE age age1 INT(11); -- 修改表的字段（字段重命名）
ALTER TABLE `student1` DROP age1; -- 删除表的字段

-- 删除表（所有的创建和删除操作尽量加上判断，以免出错）
DROP TABLE IF EXISTS `student1`; -- 删除表（如果表存在再删除）

7、MySQL数据管理

7.1、外键

概念
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外键作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

外键的创建方式

CREATE TABLE IF NOT EXISTS`grade` (
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
    PRIMARY KEY (`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
CREATE TABLE IF NOT EXISTS `student` ( -- 使用 `` 包括起来避免不小心与MySQL关键字重名
    `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(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '生日',
    `gradeid` INT(10) NOT NULL COMMENT '年级id',
     `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
     `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
     PRIMARY KEY (`id`), -- 设置id为主键
     -- 方式一：在创建表的时候添加外键约束
     CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 设置外键约束
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 方式二：创建表的时候没有外键关系，修改从表添加外键
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

删除具有主外键关系的表时 , 必须先删除具有外键的表（从表），再删除被引用的表（主表）。

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

以上的操作都是物理外键，数据库级别的外键，不建议使用，避免数据库过多造成困扰。

最佳实现：

• 数据库就是单纯的表，只用来存储数据，只有行（数据）和列（字段）

• 我们想使用多张表的数据，想使用外键（程序去实现）

7.2、DML语言（全部记住）

数据库意义 ： 数据存储、数据管理

管理数据库数据方法：

1. 通过SQLyog等管理工具管理数据库数据
2. 通过DML语句管理数据库数据

DML语言 ：数据操作语言，用于操作数据库对象中所包含的数据

包括 :

• INSERT (增加数据语句)

语法：insert into [表名]([字段名],[字段名]) values([值],[值]);

-- DML 添加语句（插入）
INSERT INTO `grade` (`gradename`) VALUES('大四'); -- 由于主键自增，我们可以省略
INSERT INTO `grade` (`gradename`) VALUES('大三'),('大四'); -- 可以同时插入多个值
INSERT INTO `grade` VALUES(4,'大四'); -- 如果不写表的字段，他就会一一匹配
INSERT INTO `student` (`name`,`pwd`,`sex`,`birthday`,`gradeid`,`address`,`email`)
VALUES('张三','1213','男','20210708160600',1,'江西','123456789@qq.com');

注意事项：

1. 一般写插入语句，字段和数据一定要一一对应
2. 字段或值之间用英文逗号隔开
3. ’ 字段1,字段2…’ 该部分可省略，但添加的值务必与表结构，数据列，顺序相对应，且数量一致
4. 可同时插入多条数据，values 后用英文逗号隔开，即VALUES(),()

• UPDATE (修改数据语句)

语法：UPDATE [表名] SET [字段名]=[修改值] WHERE [条件];

-- DML 修改语句（更新）
UPDATE `student` SET `name` = '刘翔'; -- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name` = '刘翔' WHERE `id` = 1; -- 修改id为1的name字段数据
UPDATE `student` SET `name` = '刘翔',`email` = '1232421342@qq.com' WHERE id = 1; -- 修改多个字段数据
UPDATE `student` SET `sex` = '女' WHERE `id` BETWEEN 2 AND 3; -- between ... and ... 表示一个区间
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '刘翔' AND `sex` = '女'; -- 修改值也可以是一个变量，此处将出生日期修改为了当前时间
UPDATE `student` SET `address` = '南昌' WHERE `id` <> 2; -- <> 与 != 一个意思

• DELETE (删除数据语句)

语法：DELETE FROM 表名 [WHERE 条件匹配];

-- DML 删除语句（删除）
DELETE FROM `student`; -- 删除全部数据（避免这样写），不会影响自增
DELETE FROM `student` WHERE `id` = 1; -- 删除id为1的数据
TRUNCATE `student` -- 删除全部数据，自增会归零，不会影响事务

8、DQL查询数据（最重点）

DQL( Data Query Language 数据查询语言 )

1. 所有的查询操作都用它 select
2. 简单的单表查询或多表的复杂查询和嵌套查询它都能做
3. 是数据库语言中最核心，最重要的语句
4. 使用频率最高的语句

语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2] -- 联合查询
  [WHERE ...] -- 指定结果需满足的条件
  [GROUP BY...] -- 指定结果按照哪几个字段来分组
  [HAVING] -- 过滤分组的记录必须满足的次要条件
  [ORDER BY  ...] -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

8.1、简单查询及表达式

-- DQL查询数据
SELECT * FROM student; -- 查询全部的学生
SELECT `studentno`,`studentname` FROM student; -- 查询指定字段
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS 学生; -- 别名，给结果起个名字，AS
SELECT CONCAT('姓名：',`studentname`) AS 新名字 FROM student; -- 字符串拼接函数 Concat(a,b)
SELECT DISTINCT `studentno` FROM `result`; -- 去除重复数据 关键字:DISTINCT
SELECT VERSION(); -- 查询系统版本
SELECT 100*3-1 AS 计算结果; -- 用来计算
SELECT @@auto_increment_increment; -- 查询自增的步长（变量）
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM result; -- 学员考试成绩 +1 分查看

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量...

where条件子句

作用：检索数据中符合条件的值

-- where条件子句
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=80 AND `studentresult`<=100;
-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 80 AND 100;
-- 除了1000号学生之外的同学成绩
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno` != 1000;
SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno` = 1000;

8.2、模糊查询

• 数值数据类型的记录之间才能进行算术运算
• 相同数据类型的数据之间才能进行比较

-- 模糊查询
-- like结合 %（代表0到任意个字符） _（一个字符）
SELECT `studentname`,`sex` FROM `student`
WHERE  `studentname` LIKE '周%'; -- 查询姓周的同学
SELECT `studentname`,`sex` FROM `student`
WHERE  `studentname` LIKE '%州%'; -- 查询名字中带州的同学
-- in
SELECT `studentno`,`studentname`,`sex` FROM `student`
WHERE  `studentno` IN (1000,1001,1002); -- 查询1000,1001,1002号同学
SELECT `studentno`,`studentname`,`sex` ,`address` FROM `student`
WHERE  `address` IN ('江西南昌'); -- 查询地址在江西南昌的同学
-- null 和 not null
SELECT `studentno`,`studentname`,`sex` ,`address` FROM `student`
WHERE  `address` = '' OR `address` IS NULL; -- 查询地址为 '' 或 null 的同学
SELECT `studentno`,`studentname`,`sex` ,`address` FROM `student`
WHERE  `borndate` IS NOT NULL; -- 查询有出生日期的同学

8.3、联表查询

• 七种JOIN理论

• 连接查询：如需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询

-- 联表查询 join
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
-- inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s -- 起别名
INNER JOIN result AS r
ON s.`studentno` = r.`studentno`;

-- right join 使用right join会查出result表中存在但student表中不存在的学生，在此情景下参与考试的学生一定也在student表中，因此查询结果与使用inner join一致
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s -- AS可省略 
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`;

-- left join 使用left join会查出student表中存在但result表中不存在的学生，即缺考的学生，subjectno与studentresult值均默认为null
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s -- AS可省略 
LEFT JOIN result r
ON s.`studentno` = r.`studentno`

-- 查询缺考学生
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student s -- AS可省略 
LEFT JOIN result r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL; 

• 等值查询

-- 等值查询
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s , result AS r -- 起别名
WHERE s.`studentno` = r.`studentno`;

-- 思考题，三表查询（查询参加了考试的同学信息：学号，学生姓名，科目名，分数）
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r -- 因为是要查询参加了考试的同学信息，所以以右表result为基准
ON r.`studentno` = s.`studentno`
LEFT JOIN SUBJECT sub -- 同理以左表为基准
ON r.`subjectno` = sub.`subjectno`;

• 自连接

  • 数据表与自身进行连接

  • 将一张表拆成两张表

-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

8.4、分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING AVG(`studentresult`)>80; -- 过滤分组的记录必须满足的次要条件

8.5、排序和分页

• 排序

-- 排序：升序 ASC ， 降序 DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询参加了C语言-2考试的同学信息：学号，学生姓名，科目名，分数，查询的结果根据成绩降序排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` =r.`subjectno`
WHERE `subjectname` = 'C语言-2'
ORDER BY studentresult DESC;

• 分页

-- 分页：缓解数据库压力，给人更好的体验
-- limit 起始值,页面的大小
-- 第一页 : limit 0,5 
-- 第二页 : limit 5,5 
-- 第三页 : limit 10,5 
-- …
-- 第N页 : limit (N-1)*5,5
-- 查找规律可以推导出计算每页的公式:
-- limit (pageNo-1)*pageSzie,pageSzie
-- [pageNo:页码,pageSize:单页面显示条数]
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` =r.`subjectno`
WHERE `subjectname` = 'C语言-2'
ORDER BY studentresult DESC
LIMIT 1,5;

8.6、嵌套查询(子查询)

• 在查询语句中的WHERE条件子句中，又嵌套了另一个查询语句

• 嵌套查询可由多个子查询组成，求解的方式是由里及外

• 子查询返回的结果一般都是集合，故而建议使用IN关键字

语法：

select * from [表名] 
where id in ( select id from [表名] )

-- ====================================== 嵌套查询(子查询) ================================
-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 查询C语言-2的所有考试结果（学号，科目编号，科目名称，成绩），降序排列
 SELECT `studentno`,`subjectno`,`studentresult`
 FROM `result`
 WHERE `subjectno` = (
     SELECT `subjectno`
     FROM `subject`
     WHERE `subjectname` = 'C语言-2'
 )
 ORDER BY `studentresult` DESC;

-- 查询C语言-2科目分数不小于80分的学号和姓名
-- 嵌套查询
SELECT `studentno`,`studentname`
FROM student
WHERE `studentno` IN (
     SELECT `studentno`
     FROM `result`
     WHERE `studentresult`>=80 AND `subjectno` = (
          SELECT `subjectno`
          FROM `subject`
          WHERE `subjectname` = 'C语言-2'   
     )   
);
-- 联表查询
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno` 
WHERE sub.`subjectname` = 'C语言-2' AND `studentresult` >= 80 -- 即使有两个INNER JOIN也只能有一个WHERE

9、MySQL函数

9.1、常用函数

-- ================================ 常用函数 =========================================
-- 数学运算
SELECT ABS(-8);        -- 绝对值
SELECT CEILING(9.4);   -- 向上取整
SELECT FLOOR(9.4);     -- 向下取整
SELECT RAND();         -- 返回一个 0~1 之间的随机数
SELECT SIGN(0);        -- 判断一个数的符号  0返回0，负数返回-1，正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航');  -- 返回字符串长度   
SELECT CONCAT('我','爱','你');               -- 拼接字符串
SELECT INSERT('我爱你',2,1,'超级喜欢');      -- 查询，从某个位置开始替换某个长度 
SELECT LOWER('LIUXIANF');                    -- 转小写
SELECT UPPER('liuxiang');                    -- 转大写
SELECT INSTR('liuxiang','g');                -- 返回第一次出现的子字符串的索引
SELECT REPLACE('喜欢','喜欢','爱');          -- 替换出现的指定字符串
SELECT SUBSTR('我喜欢你',1,3);               -- 返回指定的字符串
SELECT REVERSE('赵兄托你帮我办点事');        -- 反转字符串

-- 查询姓周的同学 替换姓为邹
SELECT REPLACE(`studentname`,'周','邹') -- 数据库中数据并没有变化
FROM `student`
WHERE `studentname` LIKE '周%';

-- 时间和日期函数（重要）
SELECT CURRENT_DATE();    -- 获取当前日期
SELECT CURDATE()          -- 获取当前日期
SELECT NOW()              -- 获取当前时间
SELECT LOCALTIME()        -- 本地时间
SELECT SYSDATE()          -- 系统时间

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 系统
SELECT SYSTEM_USER();     -- 系统当前用户
SELECT USER();            -- 系统当前用户
SELECT VERSION();         -- 系统版本

9.2、聚合函数（常用）

-- ==================================== 聚合函数 =========================================
-- 以下三种都能够统计表中的数据
SELECT COUNT(`studentname`) FROM student; -- count(指定列)，会忽略所有的 null 值
SELECT COUNT(*) FROM student; -- count(*),不会忽略所有的 null 值 变质：计算行数          
SELECT COUNT(1) FROM student; -- count(1),不会忽略所有的 null 值 变质：计算行数

SELECT SUM(`studentresult`) AS 总和 FROM result;
SELECT AVG(`studentresult`) AS 平均分 FROM result;
SELECT MAX(`studentresult`) AS 最高分 FROM result;
SELECT MIN(`studentresult`) AS 最低分 FROM result;

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING AVG(`studentresult`)>80; -- 过滤分组的记录必须满足的次要条件

9.3、数据库级别的MD5加密（扩展）

• MD5信息摘要算法：（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。主要增强算法复杂度和不可逆性。
• MD5 不可逆，但是具体的简单值的 md5 是一样的。
• MD5 破解网站的原理，背后有一个字典对照。

-- ================================ 测试MD5加密==========================================
CREATE TABLE `testmd5`(
    `id` INT(4) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

加密前：

-- 明文密码
INSERT INTO `testmd5` VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456');

加密后：

-- 加密
UPDATE `testmd5` SET `pwd` = MD5(`pwd`);

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小明',MD5('123456'));

-- 如何校验：将用户传递进来的密码进行MD5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name` = '小明' AND `pwd` = MD5('123456');

10、事务

• 事务就是将一组SQL语句放在同一批次内去执行

• 如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行

• MySQL事务处理只支持InnoDB和BDB数据表类型

10.1、ACID

• 原子性(Atomic)

要么全部成功，要么全部失败
整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性(Consist)

事务执行前后数据的完整性要保持一致
一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。
其主要特征是保护性和不变(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

• 持久性(Durable)

事务一旦提交便不可逆，被持久化到数据库中
在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

• 隔离性(Isolated)

事务在并发访问的时候，为每个用户开启单独事务，事务与事务之间要保持隔离
隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

10.2、隔离导致的问题

• 脏读

一个事务读取了另一个没有提交的事务
事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数

• 幻读

在同一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致
系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

• 不可重复读

在同一个事务内，重复读取表中的数据，表数据发生了改变
事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

10.3、基本语法

-- ========================================== 事务 ======================================
-- MySQL 是默认开启事务自动提交的
SET autocommit = 0; -- 关闭
SET autocommit = 1; -- 开启（默认的）

-- 手动处理事务流程
SET autocommit = 0; -- 关闭自动提交

-- 事务开启
START TRANSACTION; -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内
INSERT xx
INSERT xx

-- 提交：持久化（成功！）
COMMIT

-- 回滚：回到原来的样子（失败！）、
ROLLBACK

-- 事务结束
SET autocommit = 1; -- 开启自动提交

-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

10.4、测试事务，转账案例

-- 转账测试
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci; -- 设置字符编码与字符集校对
USE shop;

CREATE TABLE `account` (
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO `account` VALUE(1,'A',2000.00),(2,'B',10000.00);

-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE `account` SET money = money-500 WHERE `name` = 'A' -- A减500
UPDATE `account` SET money = money+500 WHERE `name` = 'B' -- B加500

COMMIT; -- 提交事务，一旦提交就被持久化，不可再回滚
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值

11、索引

MySQL官方对索引的定义为：索引(index)是帮助MySQL高效获取数据的数据结构

11.1、索引分类

• 主键索引 (PRIMARY KEY)
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
• 常规索引（KEY/INDEX）
  • 默认的，index，key 关键字来设置
• 全文索引（FULLTEXT）
  • 在特定的数据库引擎下才有，MyISAM
  • 快速定位数据

在一个表中，主键索引只能有一个，唯一索引可以有多个。

基础语法

-- ==================================== 索引 ==========================================
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM `student`;

-- 增加一个全文索引（索引名） 列名
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM `student`; -- 非全文索引

-- 删除索引
DROP INDEX `studentname` ON student;
ALTER TABLE student DROP INDEX `studentname`
ALTER TABLE student DROP PRIMARY KEY;

11.2、测试索引

-- ===================================== 插入100万条数据 ===============================
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0'COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data1 ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i < num DO
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
		VALUES(CONCAT('用户',i),'19224305@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i = i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data1(); -- 执行此函数 生成一百万条数据

-- 没有添加索引查询
SELECT * FROM `app_user` WHERE `name` = '用户9999';
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999';

-- 添加索引 CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM `app_user` WHERE `name` = '用户9999';
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999';

• 没有添加索引查询耗时：

• 添加索引查询耗时：

索引在小数据量的时候，作用不大，但是在大数据的时候，区别十分明显。

11.3、索引原则

1. 索引不是越多越好
2. 不要对经常变动的数据加索引
3. 小数据量的表建议不要加索引
4. 索引一般加在常用来查询的字段上

11.4、索引数据结构

1. hash类型的索引：查询单条快，范围查询慢
2. Btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

12、权限管理和备份

12.1、用户管理

• SQLyog创建用户

• 使用命令创建：本质是操作mysql数据库下的user表

-- ==================================== 用户管理 ======================================
-- 创建用户
CREATE USER `liuxiang` IDENTIFIED BY '123456';

-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456');
-- 修改指定用户密码
SET PASSWORD FOR liuxiang = PASSWORD('123456');

-- 重命名
RENAME USER liuxiang TO liuxiang1;

-- 用户授权 ALL PRIVILEGES 全部的权限 ， 库.表 
-- ALL PRIVILEGES 全部的权限，与root用户区别：不包含GRANT权限
GRANT ALL PRIVILEGES ON *.* TO liuxiang1;

-- 查看指定用户权限
SHOW GRANTS FOR liuxiang1;
-- 查看root用户权限
SHOW GRANTS FOR root@localhost;

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM liuxiang1;

-- 删除用户
DROP USER liuxiang1;

12.2、MySQL备份

1. 保证重要数据不丢失
2. 数据转移

MySQL数据库备份方法：

1. 直接拷贝物理文件
2. 在 SQLyog 这种可视化工具中手动导出

3. 使用命令行导出 mysqldump

# 导出
mysqldump -h主机 -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
mysqldump -hlocalhost -uroot -p123456 school testmd5 > D:/a.sql

# 导入 在登陆 MySQL 的情况下，使用source d:/a.sql 进行导入
mysql -uroot -p123456
use school #将表导入到 school 数据库下
source d:/a.sql

mysql -uroot -p123456 school < d:/a.sql # 另一种写法

13、规范数据库设计

13.1、为什么需要设计

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

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常（屏蔽使用物理外键）
• 程序的性能差

良好的数据库设计:

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统

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

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图 E-R 图

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

• 收集信息，分析需求

  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 评论表
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键的字，或者一些主字段）key : value
  • 说说表（发表心情.. id... content....create_time）

• 标识实体（把需求落地到每个字段）

• 标识实体之间的关系

  • 写博客：user --> blog
  • 创建分类：user --> category
  • 关注：user --> user
  • 友联：links
  • 评论：user --> user --> blog

13.2、三大范式

• 第一范式 (1st NF)

第一范式的目标是确保每列的原子性，如果每列都是不可再分的最小数据单元，则满足第一范式

• 第二范式(2nd NF)

第二范式是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

• 第三范式(3rd NF)

如果一个关系满足第二范式，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式。

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

• 规范化和性能的关系

阿里规范：关联查询的表不得超过三张表

1. 为满足某种商业目标，数据库性能比规范化数据库更重要（成本，用户体验）
2. 在数据规范化的同时，要综合考虑数据库的性能
3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间（增加冗余字段,，多表查询变单表查询）
4. 通过在给定的表中插入计算列，以方便查询

这篇关于MySQL学习笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！