mysql学习笔记

本笔记是学习b站遇见狂神说所记

1 为什么要学mysql

1. 岗位需求：工资高
2. 现在的世界，大数据时代，的数据库者得天下
3. 被迫需求：存数据
4. 数据库是所有软件体系中最核心的存在

1.1 什么是数据库

数据库（DB，database）
概念：数据仓库，软件，安装在操作系统（window，linux，mac…）之上，可以存储大量的数据。
作用：存储数据，管理数据

1.2 数据库的分类

关系型数据库：（SQL）

• mysql，oracle，sql server，DB2，SQLlite
• 通过表和表之间，行和列之间的关系进行存储数据

非关系型数据库：(NoSQL) Not Only

Redis,MongDB
非关系型数据库，对象存储，通过对象的自身的属性来决定。

DBMS(数据库管理系统)

• 数据库管理管理软件，科学有效的管理我们的数据。维护和获取数据；
• MySQL，数据库管理系统！

1.3 MySQL简介

MySQL是一个关系型数据库管理系统

• 前世：瑞典MySQL AB公司
• 今生：属于Oracle旗下产品
• MySQL是最好的 RDBMS (Relational DatabaseManagement System，关系数据库管理系统) 应用软件之一。
• 开源的数据库软件。体积小、速度快、总体拥有成本低
  中小型网站、或者大型网站，集群！

1.4安装mysql

docker版

 - docker pull mysql:5.7 
 - docker images docker 
 - docker run --name mysql-5.7 -p 3306:3306 -v /mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=Huawei123 -d --restart=always mysql:5.7

• docker run: 运行 Docker 容器的命令。
• –name mysql-5.7: 指定容器的名称为 “mysql-5.7”。
• -p 3306:3306: 将容器的 3306 端口映射到主机的 3306 端口。这样- 可以通过主机上的端口访问容器中的 MySQL 服务。
• -v /mysql:/var/lib/mysql: 将主机上的 /mysql 目录与容器中的 /var/lib/mysql 目录进行共享（数据卷挂载），用于持久化 MySQL 数据目录中的数据。
• -e MYSQL_ROOT_PASSWORD=Huawei123: 设置 MySQL 的 root 用户的密码为 “Huawei123”。这是通过环境变量的方式传递给容器的。
• -d mysql:5.7: 使用 mysql:5.7 镜像创建并运行容器。-d 参数表示在后台（守护进程）模式下运行容器。
• –restart=always 是 Docker 命令中一个可选的参数，用于指定容器的重启策略。它将容器配置为在 Docker 守护进程启动时自动重新启动。

rpm版

安装前清空环境

 1. rpm -qa | grep mysql 
 2. rpm -qa | grep mariadb 
 3. yum remove mariadb-libs-5.5.68-1.el7.x86_64 -y 
 4. rpm -qa | grep mariadb

• rpm -qa | grep mysql：这个命令用于在当前系统上列出所有已安装的 RPM（Red Hat Package Manager）软件包，并使用管道符号 | 将结果传递给 grep 命令进行筛选。
• rpm -qa | grep mariadb：这个命令类似于上一个命令，但是在这里使用 grep mariadb 来筛选包含 “mariadb” 的软件包。Mariadb 是一个 MySQL 分支，与 MySQL 兼容并提供了类似于 MySQL
  的功能。因此，运行此命令可以找到与 Mariadb 相关的软件包。
• yum remove mariadb-libs-5.5.68-1.el7.x86_64 -y：这个命令的目的是卸载指定的 Mariadb 软件包
• rpm -qa | grep mariadb: 这个命令用于再次列出所有已安装的 RPM 软件包，并使用 grep 筛选出包含 “mariadb” 的软件包。如果之前成功执行了第三步的卸载命令，运行该命令将不会显示与 Mariadb 相关的软件包。

下载安装包：

https://downloads.mysql.com/archives/community/

 1. mkdir mysql 
 2. tar -xvf mysql-5.7.42-1.el7.x86_64.rpm-bundle.tar -C /root/mysql 
 3. cd /root/mysql yum -y install mysql-community-*
 4. systemctl enable --now mysqld.service

• mkdir mysql: 这个命令用于在当前目录下创建一个名为 “mysql” 的文件夹（目录）。

• tar -xvf mysql-5.7.42-1.el7.x86_64.rpm-bundle.tar -C /root/mysql:
  这个命令用于解压缩名为 “mysql-5.7.42-1.el7.x86_64.rpm-bundle.tar” 的文件，并将解压后的文件提取到
  “/root/mysql” 目录下。

• cd /root/mysql yum -y install mysql-community-*: 这个命令首先将工作目录更改为 “/root/mysql”，然后使用 yum 命令安装以 “mysql-community-” 开头的所有软件包。这意味着会安装与“mysql-community-” 相关的任何软件包，如 “mysql-community-server” 等。

• systemctl enable --now mysqld.service: 这个命令用于启用并立即启动名为
  “mysqld.service” 的 MySQL 服务。“systemctl enable” 命令用于将服务设置为开机自启动，而 “–now” 参数则立即启动服务。

查看密码：

vim /var/log/mysqld.log

 1. mysql -uroot -p 
 #输入粘贴的密码
 2. ALTER USER 'root'@'localhost' IDENTIFIED BY 'Huawei@1234!'; 
 3. grant all privileges on *.* to 'root'@'%' identified by 'Huawei@1234!'
    with grant option; 
 4. FLUSH PRIVILEGES;
 5. exit
 6. systemctl restart mysqld.service

• ALTER USER 'root'@'localhost' IDENTIFIED BY 'Huawei@1234!';:
  这个命令用于更改 MySQL 数据库中的 ‘root’ 用户在 ‘localhost’ 主机上的密码。将密码更改为’Huawei@1234!'。请注意，这里使用的是单引号。

• grant all privileges on *.* to 'root'@'%' identified by 'Huawei@1234!' with grant option;: 这个命令用于授予 ‘root’
  用户在所有数据库上的所有权限，并且可以从任意主机（‘%’）连接。密码设置为 ‘Huawei@1234!’。使用 ‘with grant
  option’ 参数可以使 ‘root’ 用户有权授予其他用户权限。

• FLUSH PRIVILEGES;: 这个命令用于刷新 MySQL 的权限，使更改立即生效。

• exit: 这个命令用于退出 MySQL 控制台环境。

• systemctl restart mysqld.service: 这个命令用于重启 MySQL 服务，以便应用所做的更改。

SQLyog安装参考下面博客：

https://blog.csdn.net/weixin_43364551/article/details/116754626

2.基础操作

每一个sqlyog的执行操作，本质就是对应了一个sql，可以在历史记录中查看

2.1 连接后发现会有默认的表

2.1 查看版本

SELECT VERSION()

2.2 创建数据库

右键点击主机选择创建数据库

2.3创建表

右键点击表，选择创建表



查看日志，使用的命令
CREATE：用于在数据库中创建新的表、视图、存储过程等对象。
TABLE：关键字，表示要创建一个表。
school：数据库名，表示在该数据库中创建表。
student：表名，表示要创建的表的名称。
id：列名，用于表示学员的ID。
INT(10)：数据类型，表示整数类型，长度为10.
NOT NULL：约束条件，表示该列不允许为空。
COMMENT：列注释，用于给列添加注释或描述信息。
name：列名，用于表示学员的姓名。
VARCHAR(100)：数据类型，表示可变长度的字符串，最大长度为100。
age：列名，用于表示学员的年龄。
AUTO_INCREMENT：约束条件，表示该列的值自动递增。
KEY(age)：索引，用于在 age 列上创建一个索引，提高查询性能。
PRIMARY KEY(id)：主键约束，将 id 列标记为表的主键。
ENGINE=INNODB：存储引擎选项，指定使用 Innodb 存储引擎。
CHARSET=utf8：字符集选项，指定使用 UTF-8 字符集存储数据。
COLLATE=utf8_general_ci：排序规则选项，指定使用不区分大小写的 UTF-8 字符集排序规则。

2.4 写入数据

右键点击，打开表

2.5链接数据库

命令行链接

 1. mysql -u root -p 链接数据库
 2. 
 3. ALTER USER 'root'@'localhost' IDENTIFIED BY 'Huawei@1234!';
 4. 

我们修改密码都是修改表中的数据

 1. mysql> use school;   --- 切换数据库
 2. Database changed
 3. ---
 4. mysql> show tables; --- 查看数据库中所有的表
 6. ---
 7. mysql> describe student; -- 显示数据库中所有的信息

 1. mysql> create database westos;  --- 创建一个数据库
 2. Query OK, 1 row affected (0.00 sec)
 3. ------
 4. -- hello  ---单行注释
 5. /*
hello
table
databases
*/

3. 操作数据库

操作数据库 > 操作数据库中的表 > 操作数据库中表的数据

3.1、操作数据库

1、创建数据库

 1. CREATE DATABASE [IF NOT EXISTS] westos --创建一个名为 “westos” 的数据库，如果这个数据库不存在的话

2、删除数据库

 1. DROP DATABASE [IF EXISTS] westos --删除数据库管理系统中名为 “westos” 的数据库，如果该数据库存在的话

3、使用数据库

 1. USE `school` --tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``

4、查看数据库

 1. SHOW DATABASES -- 查看所有的数据库

3.2 数据库的列类型

数值

字符串

时间日期

null

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

Unsigned ：

zerofill:

扩展

1. 在MySQL中，当你定义一个整数字段时，如int数据类型，它的长度是指可以存储的最大整数位数，并不限制实际存储的整数位数。
2. 如果你定义一个int(1)字段，它会被解释为有符号的范围为-9到9的整数。然而，这并不意味着你只能存储一位数字。
3. MySQL允许你在int字段中存储比定义长度更长的整数值。这是因为定义的长度仅仅是用来指定显示宽度，并不限制实际存储的数值大小。如果你尝试存储超过所定义的长度的整数值，MySQL会根据需要自动调整字段的长度。
4. 因此，无论你定义的长度是1还是3，你都可以存储一个3位数的整数。这对于确保数据的完整性和正确性是非常重要的。要限制整数位数，你可以在应用程序或数据库层面进行验证和验证规则的设置，以确保只允许存储特定长度的整数值。

目标: 在 schoo1 数据库中创建学生表（列，字段）

- 学号（int）
- 登录密码（varchar(20)）
- 姓名（varchar）
- 性别（varchar(2)）
- 出生日期（datetime）
- 家庭住址（varchar）
- email（varchar）

注意点：
- 使用英文括号（）。
- 表名和字段名尽量使用连字符（-）。
- 自增字段使用单引号括起来。
- 字符串类型使用单引号括起来。
- 所有语句后面加逗号（英文的）。
- 最后一个字段不需要加主键（PRIMARY KEY），一般一个表只有一个唯一的主键！

CREATE TABLE IF NOT EXISTS `school2`(
 `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` VARCHAR(50) NOT NULL DEFAULT '无' COMMENT '姓名',
 `pwd` VARCHAR(50) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
 `birthday` DATETIME DEFAULT NULL COMMENT '出生年月',
 `address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',
 `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

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

3.2.1 常用命令

查看创建数据库和表的命令

SHOW CREATE DATABASE student;

显示表的结构
DESC school

3.3、数据库表的类型

以下的图表展示了myisam和innodb的区别：

	MyISAM	InnoDB
事务支持	不支持	支持
并发控制	表级锁	行级锁和表锁，默认为行级锁
外键支持	不支持	支持
崩溃恢复速度	较慢	较快
存储空间	较小	较大
备份及恢复	支持文件格式，方便跨平台转移	支持在线热备份，备份及恢复方便
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
全文索引	支持	不支持

3.4、物理空间存在的位置

> /var/lib/mysql

InnoDB 引擎：
- 表结构的定义文件：每个 InnoDB 表只有一个 *.frm 文件，它包含了该表的结构定义信息。
- 数据文件：InnoDB 表的数据文件被命名为 ibdata1 文件，存储了表的数据内容和索引。
- 索引文件：InnoDB 表的索引信息也存储在 ibdata1 文件中。

MyISAM 引擎：
- 表结构的定义文件：与 InnoDB 类似，每个 MyISAM 表也有一个 *.frm 文件，包含了该表的结构定义。
- 数据文件：MyISAM 表的数据存储在以表名为前缀的 *.MYD 文件中。它包含了表的实际数据。
- 索引文件：MyISAM 表的索引存储在以表名为前缀的 *.MYI 文件中。

总结起来，InnoDB 引擎使用一个统一的数据文件（ibdata1）来存储表的数据和索引，而 MyISAM 引擎使用单独的数据文件（.MYD）和索引文件（.MYI）来分别存储表的数据和索引。

设置数据库表的字符集编码

CHARSET=utf8

MySQL的默认字符集编码是Latin1，它不支持中文字符。如果要支持中文字符，可以在创建数据库或表时显式地指定字符集编码为UTF-8。

3.5、修改删除表

所有的创建和删除操作尽量加上判断，以免报错~

• 修改表名

ALTER TABLE [school2] RENAME TO [schoo3];

---

• 增加表的字段

ALTER TABLE tables ADD age INT(11)

---

• 修改表

ALTER TABLE tables MODIFY age VARCHAR(11)

---

• 字段重命名

ALTER TABLE tables CHANGE age age1 INT(5)

---

• 删除表的字段

ALTER TABLE tables DROP age1

4、MySQL数据管理

4.1、外键（了解即可）

• 学生表的 gradeid 字段 要去引用年级表的 gradeid
• 定义外键key
• 给这个外键添加约束 (执行引用)，references 引用

方式一：

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`(
 `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生id',
 `name` VARCHAR(50) NOT NULL COMMENT '学生姓名',
 `pwd` VARCHAR(50) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别', 
 `birthday` DATETIME COMMENT '出生日期',
 `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
 `email` VARCHAR(50) COMMENT '邮箱',
  PRIMARY KEY(`id`),
  KEY `FK_greadeid` (`gradeid`),
  CONSTRAINT `FK_greadeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 

删除有外键关系的表的时候，必须要先删除引用别人的表 (从表)，再删除被引用的表 (主表)

---

方式二: 创建表成功后，添加外键约束

CREATE TABLE IF NOT EXISTS `student`(
 `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生id',
 `name` VARCHAR(50) NOT NULL COMMENT '学生姓名',
 `pwd` VARCHAR(50) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别', 
 `birthday` DATETIME COMMENT '出生日期',
 `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
 `email` VARCHAR(50) COMMENT '邮箱',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 

ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (gradeid);

以上的操作都是物理外键，数据库级别的外键，我们不建议使用!(避免数据库过多造成困扰，这里了解即可~)

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)
• 我们想使用多张表的数据，想使用外键 (程序去实现)

4.2、DML语言（全部记住）

• insert 插入
• update 修改
• delete 删除

4.2.1 insert

语法：插入一条记录并指定字段名：

INSERT INTO 表名 (字段名1, 字段名2, 字段名3) VALUES ('值1', '值2', '值3');

列：

INSERT INTO `grade`(`gradeid`,`gradename`) VALUE('2','王五')

4.2.2 update 修改

update 表名 set colnum_name = value,[colnum_name = value, ....] where [条件]

---

UPDATE student SET name=‘狂神’ WHERE id = 1;

• UPDATE：表示要进行数据更新操作。
• student：是数据库表的名称，表示要对名为 “student” 的表进行操作。
• SET：用于设置要更新的字段和对应的新值。
• name='狂神'：表示要将名为 “name” 的字段的值更新为 “狂神”。
• WHERE id = 1：是一个条件语句，它指定了要更新的数据行。这里表示只更新 “id” 字段等于 1 的数据行。

---

UPDATE student SET name=‘好无聊’;

• 没有指定条件语句 (WHERE 子句)，表示这条语句将会将表中所有数据行的 “name” 字段更新为 “好无聊”。

---

UPDATE student SET name=‘张三’,pwd=‘123456’,sex=‘女’ WHERE id=1;

• 修改多个属性，用逗号隔开

---

UPDATE studentSET name=‘甲方’ WHERE name=‘好无聊’ AND sex=‘女’

• 通过多个条件定位数据

---

注意：

• colnum_name 是数据库的列，尽量带上``
• 条件，筛选的条件，如果没有指定，则会修改所有的列
• 多个设置的属性之间，使用英文隔开
• value，是一个具体的值，也可以是一个变量

• CURRENT_TIME 是一个函数，用于获取当前的时间值。
  

4.2.3 删除

delete命令
语法：delete from 表命

---删除数据（避免这样写，会全部删除）
DELETE FROM `student`

---删除指定数据
DELETE FROM `student` WHERE id = 1;

TRUNCATE 命令
作用：完全清空一个数据库表，表的结构和索引约束不会改变！

---清空`student`表
TRUNCATE `student`

• 相同点：都能删除数据，都不会删除表结构
• 不同：

CREATE TABLE `test`(
 `id` INT(20) NOT NULL AUTO_INCREMENT,
 `coll` VARCHAR(20) NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `test`(`coll`) VALUES('一'),('二'),('三');

DELETE FROM `test` ---不会影响自增

TRUNCATE TABLE `test` 

DELETE FROM test --不会影响自增

TRUNCATE TABLE test —自增会归零

• InnoDB 自增列会重1开始（存在内存当中，断电即失）
• MyISAM 继续从上一个资增量开始（存在文件中，不会丢失）

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

5.1、DQL

(Data Query LANGUAGE:数据查询语言)

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做~
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
drop table if exists `student`;
create table `student`(
   `studentno` int(4) not null comment '学号',
   `loginpwd` varchar(20) default null,
   `studentname` varchar(20) default null comment '学生姓名',
   `sex` tinyint(1) default null comment '性别，0或1',
   `gradeid` int(11) default null comment '年级编号',
   `phone` varchar(50) not null comment '联系电话，允许为空',
   `address` varchar(255) not null comment '地址，允许为空',
   `borndate` datetime default null comment '出生时间',
   `email` varchar (50) not null comment '邮箱账号允许为空',
   `identitycard` varchar(18) default null comment '身份证号',
   primary key (`studentno`),
   unique key `identitycard`(`identitycard`),
   key `email` (`email`)
 )engine=myisam default charset=utf8;

-- 创建年级表
drop table if exists `grade`;
create table `grade`(
   `gradeid` int(11) not null auto_increment comment '年级编号',
   `gradename` varchar(50) not null comment '年级名称',
   primary key (`gradeid`)
 ) engine=innodb auto_increment = 6 default charset = utf8;

-- 创建科目表
drop table if exists `subject`;
create table `subject`(
   `subjectno`int(11) not null auto_increment comment '课程编号',
   `subjectname` varchar(50) default null comment '课程名称',
   `classhour` int(4) default null comment '学时',
   `gradeid` int(4) default null comment '年级编号',
   primary key (`subjectno`)
 )engine = innodb auto_increment = 19 default charset = utf8;

-- 创建成绩表
drop table if exists `result`;
create table `result`(
   `studentno` int(4) not null comment '学号',
   `subjectno` int(4) not null comment '课程编号',
   `examdate` datetime not null comment '考试日期',
   `studentresult` int (4) not null comment '考试成绩',
   key `subjectno` (`subjectno`)
 )engine = innodb default charset = utf8;

-- 插入学生数据 其余自行添加 这里只添加了2行
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

5.2、指定查询字段

语法：SELECT

SELECT [ALL | DISTINCT]
  (* | table.* | [table.]field1 [AS alias] [, [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_count OFFSET offset}]; -- 指定查询的记录从哪条至哪条

5.2.1 查询全部学生

SELECT * FROM `student`

5.2.2 查询特定的表

SELECT  `studentno`,`studentname` FROM `student`

5.2.3 设置别名

SELECT `studentno` AS `学号`,`studentname` AS `姓名` FROM `student` AS s

5.2.4 连接函数 concat（a，b）

SELECT CONCAT('学号：',`studentno`,'姓名：',`studentname`) AS '新名字' FROM student

5.3 去重 distinct

作用: 去除SELECT 查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM `result` -- 查询全部的考试成绩
SELECT `studentresult` AS '成绩' FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentresult` FROM result  -- 发现重复数据，去重

---

SELECT VERSION() -- 查询系统版本 (函数)
SELECT 100*2-1 AS 技术结果是 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的长(变量)

---

学员考试成绩 + 1分查看

SELECT `studentno`,`studentresult`+1 AS '提分后' FROM `result`

数据库中的表达式: 文本值，列，Null，函数，计算表达式，系统变量…
select 表达式 from表

5.4 where

作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成! 结果 布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b	逻辑与，两个都为真，结果为真
or II	a or b a II b	逻辑或，其中一个为真，则结果为真
not !	not a ! a	逻辑非，真为假，假为真!

‘||’输出不了上图用的‘II’代替

---

where筛选数据

-- ========================================
SELECT `studentno`,`studentresult` FROM `result`

-- 查询考试成绩在 95~100 分之间
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` >= 95 AND `studentresult` <=100

SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` >= 95 && `studentresult` <=100

-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000;


SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno` = 1000;

-- 查询学号是1000或者1001 
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno` = 1000 OR `studentno` = 1001;

模糊查询：比较运算符

操作符	语法	描述
IS NULL	a IS NULL	如果操作符为 NULL，结果为真
IS NOT NULL	a IS NOT NULL	如果操作符不为 NULL，结果为真
BETWEEN	a BETWEEN b AND c	若 a 在 b 和 c 之间，则结果为真
LIKE	a LIKE b	SQL 匹配，如果 a 匹配 b，则结果为真
IN	a IN (a1, a2, a3, …)	如果 a 在给定的值列表中的任意一个值，结果为真

---

5.5模糊查询

-- 查询姓刘的同学
-- like结合 %号(代表0到任意个字符) _(一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘%'

-- 查询姓刘的同学，名字后面只有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘_'

-- 查询名字中间有嘉字的同学 %嘉%
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '_%嘉%'

-- ======in(具体的一个或者多个值)========
-- 查询 1000,1001,1002号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1000,1001,1002);

-- 查询在北京的学生
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE `address` IN ('北京');

-- ============ null not null ===============
-- 查询地址为空的学生 null
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE address='' OR address IS NULL

-- 查询不为空的出生日期的同学
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE `borndate` IS NOT NULL

-- 查询没有出生日期的同学
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE `borndate` IS NULL

---

5.6 联表查询

-- =============== join ===============
-- 查询参加了考试同学的学号，姓名，科目编号，分数
SELECT * FROM `student`

SELECT * FROM `result`

/*思路
1.分析需求，分析查询的字段来自哪些表，2个表（连接查询）
2.确定使用哪种连接查询？ 7种
确定交叉点（这个两个表中哪个数据是相同的）
判断的条件：学生表的中 studentno = 成绩表 stundentno 相同
*/
-- join （连接的表） on （判断的条件）连接查询
-- where   等职查询


-- inner join
SELECT s.studentno,studentname,subjectno,studentresult
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.studentno = r.studentno

-- Right Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM `student` AS s
RIGHT JOIN `result` AS r 
ON s.studentno = r.studentno;

-- Left Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM `student` AS s 
LEFT JOIN `result` AS r 
ON s.studentno = r.studentno


-- 搜索没有考试成绩的
SELECT s.studentno,studentname,subjectno,studentresult
FROM `student` AS s 
LEFT JOIN `result` AS r 
ON s.studentno = r.studentno
WHERE studentresult IS NULL;



-- 思考题(查询了参加考试的同学信息: 学号，学生姓名，科目名，分数)
SELECT s.studentno,studentname,subjectname,studentresult
FROM `student` AS s
RIGHT JOIN `result` AS r
ON r.studentno  = s.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno

当然，这是一个更简洁的表格以帮助说明这三种连接操作：

连接类型	描述
Inner Join	如果表中至少有一个匹配，就返回行
Left Join	会从左表中返回所有的值，即使右表中没有匹配
Right Join	会从右表中返回所有的值，即使左表中没有匹配

---

CREATE TABLE category (
  categoryid INT PRIMARY KEY,
  pid INT,
  categoryName VARCHAR(50)
) ENGINE=INNODB 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, 1, '办公信息');

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父表

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子表

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

代码：

SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

变成这样

习题：

-- 查询学员所属的年级(学号，学生的姓名，年级名称)
SELECT s.`studentno`,`studentname`,`gradename`
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.`gradeid` = g.`gradeid`;


-- 查询学生的年级和科目
SELECT s.`studentno`,s.`studentname`,g.`gradename`,sub.subjectname
FROM `student` AS s
LEFT JOIN `grade` g
ON s.`gradeid` = g.`gradeid`
LEFT JOIN `subject` AS sub
ON sub.`gradeid` = s .`gradeid`

-- 查询了参加 数据库结构-1 考试的同学信息: 学号，学生姓名，科目名，分数
SELECT s.`studentno`,s.`studentname`,sub.`subjectname`,`studentresult`
FROM `student` AS s
LEFT JOIN `subject` AS sub
ON s.gradeid = sub.`gradeid`
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE sub.`subjectname` = '数据库结构-1'

5.7 分叶查询

-- ==============分页 limit 和排序 order by ==========

-- 排序：从低到高-升序：ASC ,从高到低-降序：DESC

SELECT s.`studentno`,s.`studentname`,sub.`subjectname`,`studentresult`
FROM `student` AS s
LEFT JOIN `subject` AS sub
ON s.gradeid = sub.`gradeid`
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`
ORDER BY studentresult ASC

-- 分页
SELECT s.`studentno`,s.`studentname`,sub.`subjectname`,`studentresult`
FROM `student` AS s
LEFT JOIN `subject` AS sub
ON s.gradeid = sub.`gradeid`
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`
ORDER BY studentresult DESC
LIMIT 1,5
/*
第一页 limit 0,5      (1-1)*5
第二页1imit 5,5         (2-1)*5
第三页limit 10,5        (3-1)*5
limit 0,5第N页     (n-1)* pagesize，pagesize
[pagesize:页面大小]
[(n-1)* pagesize:起始值]
[n :当前页 ]
[数据总数/页面大小 = 总页数]
*/

语法：limit （查询起始下标，pagesizeD ）

-- 模糊查询 like
-- 查询 JAVA第一学年 课程成须排名前上的学生，并且分数要大于80 的学生信息(学号，姓名，深程名称，分数)
SELECT s.`studentno`,s.`studentname`,sub.`subjectname`,r.`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON s.gradeid = sub.`gradeid`
WHERE sub.`subjectname` LIKE '%Java%' AND r.`studentresult`>=80
ORDER BY `studentresult` DESC
LIMIT 1,5;

-- 查询 数据库结构 的所有考试结果 (学号，科目编号，成绩)，降序排列
-- 方式一：使用连接查询
SELECT s.`studentno`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `subject` AS sub
ON s.gradeid = sub.gradeid
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE sub.`subjectname` LIKE '%数据库结构%'
ORDER BY sub.`subjectname` DESC

5.8 select语法

select语法

SELECT [ALL | DISTINCT]
  [* | table.* | table.field1 [AS alias1] [, table.field2 [AS alias2], ...]]
FROM table_name [AS table_alias]
[LEFT JOIN | RIGHT JOIN | INNER JOIN table_name2] -- 联合查询
[WHERE ...] -- 指定结果需要满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING ...] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,] row_count | row_count OFFSET offset}] -- 指定查询的记录范围

注意：方括号 [] 表示可选项，括号 () 表示必选项

---

6、mysql函数

官网：

https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html

-- 数学函数
SELECT ABS(-8)
-- 求绝对值。将返回 8。

SELECT CEILING(9.4)
-- 向上取整。将返回 10。

SELECT FLOOR(9.4)
-- 向下取整。将返回 9。

SELECT RAND()
-- 返回一个介于 0 和 1 之间的随机数。

SELECT SIGN(10)
-- 判断一个数的符号。正数返回 1，负数返回-1。


-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航')
-- 用于获取字符串的长度。将返回字符串的字符数。

SELECT CONCAT('我', '爱', '你们')
-- 用于连接字符串。将返回 '我爱你们'。

SELECT INSERT('我爱编程helloworld', 1, 2, '超级热爱')
-- 这个函数的意思是：在字符串 ‘我爱编程helloworld’ 的第 1 个位置（即开头）开始，删除 2 个字符，然后插入新的字符串 ‘超级热爱’，所以它的作用相当于用 ‘超级热爱’ 替换了 ‘我爱’。因此，这个函数应该返回 ‘超级热爱编程helloworld’。

SELECT LOWER('Kuangshen')
-- 用于将字符串转换为小写字母。将返回 'kuangshen'。

SELECT UPPER('Kuangshen')
-- 用于将字符串转换为大写字母。将返回 'KUANGSHEN'。

SELECT INSTR('kuangshen', 'h')
-- 返回第一次出现指定子串 'h' 的索引位置。将返回 7。

SELECT REVERSE('清晨我上马')
-- 用于将字符串反转。将返回 '马上我晨清'。

SELECT REPLACE('狂神说坚持就能成功', '坚持', '努力')
-- 语法是 REPLACE('原字符串', '要替换的子串', '替换后的内容')。‘坚持’ 替换为 ‘努力’，所以最终结果应为 ‘狂神说努力就能成功’。

SELECT SUBSTR('狂神说坚持就能成功', 6)
-- 用于提取字符串的子串，从指定位置开始。将返回 '说坚持就能成功'。

-- 查询姓 周的同学，名字 皱
SELECT REPLACE(studentname,'周', '邹') FROM student
WHERE studentname LIKE '%周%'

SELECT REPLACE(studentname, '周', '邹') FROM student WHERE studentname LIKE '%周%'


-- 时间和日期函数（记住）
获取当前日期：
 `CURRENT_DATE()` 或 `CURDATE()`：返回当前日期。

获取当前时间：
 `NOW()`：返回当前日期和时间。
 `LOCALTIME()`：返回当前本地时间。

获取日期和时间的具体部分：
 `YEAR(NOW())`：返回当前年份。
 `MONTH(NOW())`：返回当前月份。
 `DAY(NOW())`：返回当前日期的天数。
 `HOUR(NOW())`：返回当前小时数。
 `MINUTE(NOW())`：返回当前分钟数。
 `SECOND(NOW())`：返回当前秒数。

获取系统信息：
 `SYSTEM_USER()` 或 `USER()`：返回当前登录用户。
 `VERSION()`：返回数据库系统的版本信息。

6.2、聚合函数

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

-- ========================== 聚合函数 ===============
-- 都能够统计 表中的数据 （想查询一个表中有多少个记录，就使用这个count（） ）
SELECT COUNT(`studentname`) FROM `student` -- Count(字段)，会忽略所有的 nu11 值
SELECT COUNT(*) FROM `student` -- 不会忽略 null 值，本质 计算行数
SELECT COUNT(1) FROM `result` -- 不会忽略忽略所有的 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`

-- 查询不同课程的平均分，最高分，最低分
-- 核心:(根据不同的课程分组）
SELECT s.`studentno`,`studentname`,sub.`subjectname`,`studentresult`,`subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON sub.`gradeid` = s.`gradeid` AND sub.`subjectno` = r.`subjectno`
GROUP BY r.`subjectno` 
HAVING 平均分 > 80

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

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

-- 明文密码
INSERT INTO `testmd5` VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456');

-- 加密
UPDATE `testmd5` SET pwd=MD5(`pwd`) WHERE id = 1
UPDATE `testmd5` SET pwd=MD5(`pwd`) -- 加密全部的密码
UPDATE `testmd5` SET pwd=MD5(`pwd`) WHERE id != 1

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

SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

7、事务

事务原则 : ACID 原则 原子性，一致性，隔离性，持久性
(脏读，幻读…)

原子性 (Atomicity)
要么都成功，要么都失败
一致性 (Consistency)
事务前后的数据完整性要保证一致 ，1000
持久性 (Durability) — 事务提交
事务一旦提交则不可逆，被持久化到数据库中!
隔离性 (lsolation)
隔离性是指多个并发事务的执行互不干扰，每个事务都感觉不到其他事务的存在，以确保并发事务的正确性和一致性。

隔离所导致的一些问题

1. 脏读（Dirty Read）：一个事务读取了另一个事务尚未提交的数据，即事务 A 读取了事务 B 修改但尚未提交的数据。

2. 不可重复读（Non-Repeatable Read）：在一个事务内读取表中的某一行数据，多次读取结果不同。 (这个不一定是错误，只是某些场合不对)

3. 幻读（Phantom Read）：是指在一个事务内读取到了别的事务插入的数据，，导致前后读取不一致。

需要注意的是，不可重复读和幻读的区别在于对数据的修改操作类型。不可重复读主要涉及到修改或删除数据，而幻读主要涉及到插入数据。

这些问题的出现是由于并发事务的存在，为了解决这些问题，数据库提供不同的隔离级别，从而控制事务之间的相互影响和数据一致性。选择合适的隔离级别可以根据具体的需求和业务场景进行权衡。

---

-- 关闭自动提交，开启事务
SET autocommit = 0;

-- 执行多个操作，属于同一个事务
-- ...

-- 提交事务，将多个操作结果一起提交
COMMIT;

-- 回滚事务，取消之前的操作
ROLLBACK;

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

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO SAVEPOINT savepoint_name;

-- 撤销保存点
RELEASE SAVEPOINT 保存点名 

练习题-支付：

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

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


INSERT INTO account(`name`,`money`) 
VALUES ('A',2000),('B',10000.00)

-- 模拟转账: 事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION  -- 开启一个事务

UPDATE `account` SET `money`=`money`-500 WHERE `name`= 'A';
UPDATE `account` SET `money`=`money`+500 WHERE `name`= 'B';

COMMIT; -- 提交事务,持久化
ROLLBACK; -- 回滚

SET autocommit = 1 -- 恢复默认值

8、索引

MysQL官方对索引的定义为: 索引(lndex) 是帮助MySQL高效获取数据的数据结构。 提升速度
提取句子主干，就可以得到索引的本质: 索引是数据结构。

索引是用于快速定位和访问数据库中数据的结构。下面整理一下主键索引、唯一索引、常规索引和全文索引的特点：

1. 主键索引 (PRIMARY KEY):

   • 唯一标识每一行数据，不可重复。
   • 只能有一个列作为主键。
   • 用于确保表中的每一行都有一个唯一标识符。

2. 唯一索引 (UNIQUE KEY):

   • 避免重复值出现，确保列的唯一性。
   • 可以包含多个列，多个列组合起来标识唯一性。
   • 允许重复，并且允许 NULL 值的存在。

3. 常规索引 (KEY/INDEX):

   • 用于提高查询性能和加快数据检索。
   • 默认的索引类型，可以使用 “KEY” 或 “INDEX” 关键字来设置。
   • 可以包含多个列，多个列的组合作为索引的键。

4. 全文索引 (FullText):

   • 用于全文搜索，只在特定的数据库引擎中存在，如 MyISAM。
   • 可以快速搜索文本内容，而不是仅限于精确匹配。
   • 提供了通过匹配关键字的方法来快速定位数据。

索引的选择取决于查询的需求和数据库引擎的支持。通过合理使用索引，可以提高数据库的查询性能和数据检索速度。

---

索引的使用有两个主要方面：在创建表时添加索引和在表创建后添加索引。

1. 在创建表时添加索引：
   在创建表时，可以通过在相关字段上指定索引来创建索引。例如，在创建表时，可以使用以下语法为字段添加索引：

   CREATE TABLE 表名 (
     列名 数据类型,
     INDEX/KEY 索引名 (列名)
   );
   

2. 在表创建后添加索引：
   可以在已创建的表上添加索引。可以使用 ALTER TABLE 语句来添加索引。例如，使用以下语法在表中添加索引：

   ALTER TABLE 表名 ADD INDEX/KEY 索引名 (列名);
   

3. 显示所有索引信息：
   可以使用 SHOW INDEX FROM 语句来查看表中的索引信息。例如，使用以下语法查看名为 “student” 表的索引信息：

   SHOW INDEX FROM student;
   

4. 增加全文索引：
   如果使用的数据库引擎支持全文索引，可以使用 ALTER TABLE 语句来增加全文索引。例如，使用以下语法为 “student” 表的 “studentName” 列添加全文索引：

   ALTER TABLE student ADD FULLTEXT INDEX 索引名 (列名);

请注意，全文索引只在特定的数据库引擎（如 MyISAM）中可用。

在使用索引时，请注意以下几点：

• 索引可以提高查询性能，但同时会增加对数据表的写操作的开销（如插入、更新、删除），因此要慎重选择添加索引的字段。
• 添加过多的索引可能会导致性能下降，因为每个索引都需要占用额外的存储空间和维护成本。
• 根据查询需求选择合适的索引类型，如主键索引、唯一索引、常规索引或全文索引。

---

ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);

这是一个用于在 “school” 数据库中的 “student” 表上添加全文索引的 ALTER TABLE 语句。

语句解释如下：

• ALTER TABLE school.student: 指定要修改的表，即 “student” 表，位于 “school” 数据库中。
• ADD FULLTEXT INDEX: 指定要添加的索引类型是全文索引。
• studentname: 指定要添加索引的列名，即 “studentname” 列。请注意，反引号 ` 是用于转义列名，以防止与关键字冲突。
• (studentname): 括号中的内容是可选的，用于指定索引的名称，即 “studentname”。

这条 ALTER TABLE 语句将在 “student” 表的 “studentname” 列上创建一个名为 “studentname” 的全文索引。全文索引允许对 “studentname” 列进行全文搜索，以便更快地定位和检索相关的数据。请确保所使用的数据库引擎支持全文索引功能，如 MyISAM。

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

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

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

-- EXPLAIN 分析sg1执行的状况


EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT *FROM student WHERE MATCH(studentName) AGAINST('刘');

---

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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
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), '24736743@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_data();

SELECT * FROM app_user WHERE `name`='用户9999'; -- 总耗时: 0.382 sec


EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';

CREATE INDEX id_app_user_name ON app_user(`name`) --创建索引

创建索引前

创建索引后

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

8.1 索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上!

这篇文章写的很好可以看看

https://blog.codinglabs.org/articles/theory-of-mysql-index.html

8数据库备份

9、权限管理和备份

9.1、用户管理

SQL yog 可视化管理

SQL命令操作

创建用户

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY "密码"
CREATE USER kuangshen IDENTIFIED BY  '123456'

-- 修改密码 （修改当前用户的密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码 （修改指定用户的密码）
SET PASSWORD FOR kuangshen = PASSWORD('123456789')

-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER kuangshen TO kuangshen2

-- 用户授权 all privileges 全部权限 ，库.表
-- ALL PRIVILEGES 除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2

-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限
SHOW GRANTS FOR root

-- ROOT用户权限: GRANT ALL PRIVILEGES ON *.* To "root'@'localhost' WITH GRANT OPTION

-- 撤销权限 REVORE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2


-- 删除用户
DROP USER kuangshen2 

-- 都需要执行保存
FLUSH PRIVILEGES;

9.2 MySQL备份

sqlyon化

命令化

mysqldump -h 127.0.0.1 -u root -pHuawei@1234! school > 1.sql

#导入
#登录的情况下，切换到指定的数据库
source 备份文件

假设你要备份数据库，防止数据丢失把数据库个朋友! sql文件给别人即可!

10、规范数据库设计

为什么需要数据库设计？
当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

数据冗余,存储空间浪费
数据更新和插入的异常
程序性能差

良好的数据库设计 :

节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

需求分析阶段: 分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
设计数据库步骤

收集信息

与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
标识实体[Entity]
标识数据库要管理的关键对象或实体,实体一般是名词
标识每个实体需要存储的详细信息[Attribute]

标识实体之间的关系[Relationship]

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效的信息

10.1、三大范式

(规范数据库的设计)
第一范式(1NF):
原子性: 保证每一列不可再分

第二范式(2NF):
前提:满足第一范式
每张表只描述一件事情

第三范式(3NF):
前提:满足第一范式和第二范式

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

10.2、规范性 和 性能的问题

关联查询的表不得超过三张表
为什么？
因为在商业中查询数据，会很频繁，导致压力过大，所以需要让一下多表查询的事变为单表查询

• 考虑商业化的需求和目标，(成本，用户体验!) 数据库的性能更加重要
• 在规范性能的问题的时候，需要适当的考虑一下 规范性!
• 故意给某些表增加一些冗余的字段。 (从多表查询中变为单表查询)
• 故意增加一些计算列 (从大数据量降低为小数据量的查询: 索引)

11、JDBC(重点)

这没有java基础真的不会