MySQL

1.初始MySQL

1.javaEE

企业级开发web，分为前端，后台，数据库。

• 前端：页面展示数据

• 后台：连接点----连接数据库JDBC；连接前端，控制视图跳转和给前端传递数据

• 数据库：存数据，txt，Excel，word

2.为什么学习数据库

• 岗位需求
• 大数据时代，得数据库者得天下
• 被迫需求：存数据
• 数据库是所有软件体系中最核心的存在 DBA

 

3.什么是数据库

概念：数据库（DB，DataBase），数据仓库，是一个软件要安装在操作系统（window，linux，mac…）之上。SQL可以存储大量的数据500万以下都没问题。

作用：存储数据，管理数据。

 

4.数据库分类

关系型数据库：行列

• MySQL，Oracle，Sql Server，DB2，SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储。学生信息表，考勤表…

非关系型数据库：键值对

• Redis，MongDB，NoSQL（Not Only）
• 非关系型数据库，对象存储，通过对象的自身属性来决定

DBMS（数据库管理系统）

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

 

5.MySQL简介

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

前世：瑞典MySQL AB公司开发

今生：属于 Oracle 旗下产品

• 在 WEB 应用方面，MySQL是最好的**RDBMS(**Relational Database Management System，关系数据库管理系统) 应用软件之一。

• MySQL关系型数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。

• 体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，还可以做集群，因此一般中小型或者大型网站的开发都选择 MySQL 作为网站数据库。

• 官网：https://www.mysql.com/

 

6.安装MySQL

1. 解压

2. 配置环境变量path新建bin目录的路径粘进去

3. 新建mysql配置文件，必须以ini结尾，这里我建的是my.ini。然后打开输入下方代码。

   注意：basedir换成自己的目录，datadir会自己生成

   [mysqld]
   port=3306
   basedir=D:\c\mysql-8.0.26\
   datadir=D:\c\mysql-8.0.26\Data\
   skip-grant-tables
   

4. 启动管理员模式下的cmd，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)

5. 再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件

6. net start mysql（启动mysql），然后用命令 mysql -uroot -p进入mysql管理界面（密码可为空）

7. 进入之后修改密码，输入如下代码

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

8. 刷新权限

   flush privileges;
   

9. 修改 my.ini文件删除最后一句skip-grant-tables

10. exit退出mysql

    net stop mysql停止
    net start mysql启动
    

11. 然后再输入密码即可启动mysql

最后再次用mysql就net start mysql，mysql -u root -p1234就可以了！

另外：要彻底删掉原来mysql的配置用sc delete mysql

 

7.SQLyog

• SQLyog是mysql的可视化工具，首要要连接本地MySQL数据库！

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

• 要记住固定的语法和关键字。

• 在阿里有个规范：每一个表都必须包括下面五个字段，未来做项目用，表示一个记录存在的意义！

  id 主键
  `version` 乐观锁
  is_delete 伪删除
  gmt_create 创建时间
  gmt_update 修改时间
  

创建第一个表：

1. 新建一个数据库school：

2. 新建一张表student：

3. 打开表：

4. 添加多条记录,点击刷新键并保存：
    

8.命令符操作数据库

快捷键：Ctrl+c 强行终止

-- 所有的sql语句都要用分号结尾
show databases;-- 查看所有数据库
use school;-- 切换数据库use 数据库名; 
show tables;-- 查看数据库中的所有的表
describe student; -- 查看表格中的所有信息
create database cmdbase; -- 创建数据库
exit;-- 退出连接

 
 

2.操作数据库

流程：操作数据库—>操作数据库中的表—>操作表中的信息

数据库xxx语言：CRUD增删改查！

• DDL 定义

• DML 操作

• DQL 查询（data query language）

• DCL 控制

1.操作数据库

创建数据库

CREATE DATABASE IF NOT EXISTS cmdbase

删除数据库

DROP DATABASE IF EXISTS hello
DROP DATABASE cmdbase

使用数据库

-- 如果你的表名或者字段名是特殊的字符就需要带``，use `school`
USE school

查看数据库

SHOW DATABASES 

 

2.数据库的列类型

• 数值

  类型	描述	大小
  tinyint	十分小的数据	一个字节
  smallint	比较小的数据	两个字节
  mediumint	中等大小的数据	三个字节
  int	标准的整数	四个字节
  bigint	较大的数据	八个字节
  float	浮点数	八个字节
  double	浮点数	八个字节
  decimal	字符串型的浮点数，一般在金融里用，因为精度问题。

  DECIMAL(6,2)-- 总共能存6位数字，末尾2位是小数，字段最大值9999.99（小数点不算在长度内）
  

• 字符串

  类型	描述	大小
  char	固定大小的字符串	0~255
  varchar	可变字符串	0~65535
  tinytext	微型文本	2^8-1
  text	文本串，保存大文本	2^16-1

• 时间日期

  类型	格式
  data	YYY-MM-DD	日期格式
  time	HH:MM:SS	时间格式
  datatime	YYY-MM-DD HH:MM:SS	最常用的时间格式
  timestamp	时间戳	1970.1.1.00到现在的毫秒数
  year	年份

• null

  • 没有值，未知

  • 不要使用null进行运算，运算完的结果也是null

 

3.数据库的字段属性

Unsigned

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

Zerofill

• 0填充，不足的位数使用零来填充

自增

• 默认在上一条操作的基础上+1
• 通常用来设置唯一的主键，必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空

• 如果设置成为了not null，如果不给数据就会报错
• null，如果不填写值，默认就是null

默认

• 设置默认的值
• sex，如果默认为男，那么不指定该列的值，就会有默认值

 

4.创建数据库表

-- 注意使用英文括号，表的名称一般用``括起来
-- AUTO_INCREMENT自增
-- COMMENT注释
-- DEFAULT默认值
-- 字符串使用单引号引起来
-- 所有语句后面加,(英文的)，最后一个不用加
-- PRIMARY KEY主键，一般一个表只有一个主键，放在最后很清晰
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(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`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 

格式：

CREATE TABLE [IF NOT EXISTS] `student`(
     `字段名` 列类型 [属性] [索引] [注释],
     `字段名` 列类型 [属性] [索引] [注释],
      ……
     `字段名` 列类型 [属性] [索引] [注释]
)ENGINE = [表类型][字符集设置][注释]

 

5.数据表的类型

1.常用语句

可以查看sql语句（偷懒）

SHOW CREATE DATABASE school-- 查看创建按数据库的语句
SHOW CREATE TABLE student1-- 查看student1数据表的定义语句
DESC student1-- 显示表的结构  

 

2.数据库引擎

	INNODB（默认使用）	MYISAM（早些年）
事务支持	支持	不支持
数据行锁定	支持（行锁）	不支持（表锁）
外键约束	支持	不支持
全文索引	不支持	支持
表空间的大小	较大，约2倍	较小

优点：

• INNODB：安全性高，事务的处理，多表多用户操作
• MYISAM：节约空间，速度快

在物理空间存在的位置：所有的数据库文件都存在data目录下，一个文件夹就是一个数据库。本质上还是文件的存储。

数据库引擎在物理文件上的区别

• innoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• myisam对应文件
  • *.frm表结构的定义文件
  • *.MYD数据文件（data）
  • *.MYI索引文件（index）

 

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

• 创建表的时候设置，CHARSET=utf8

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

• 也可以在my.ini中配置默认的编码

  character-set-server=utf8
  

 

4.修改删除表

修改表ALTER

-- 修改表名：ALTER TABLE 旧名字 RENAME AS 新名字
ALTER TABLE student RENAME AS student0
-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 属性 
ALTER TABLE student0 ADD sex VARCHAR(2)
-- 修改表的属性：ALTER TABLE 表名 MODIFY 原有字段 属性
ALTER TABLE student0 MODIFY age VARCHAR(10)
-- 修改字段名，而且可以修改属性：ALTER TABLE 表名 CHANGE 原字段名 新字段名 属性
ALTER TABLE student0 CHANGE age age1 INT(3)
-- 删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE student0 DROP age1

删除表DROP

DROP TABLE IF EXISTS student0

 

6.一些注意点

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

• – 单行注释（sql的本来注释）
  /**/sql多行注释

• mysql不区分大小写！

• 表或者字段的名称一般用``括起来

• 所有符号全部英文

 
 

3.MySQL数据管理

1.外键

方式一：在创建表的时候，增加约束，比较麻烦

-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用),references引用
CREATE TABLE IF NOT EXISTS `student1`(
    `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 '学生年级',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '电子邮箱',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE= INNODB DEFAULT CHARSET=utf8 

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE= INNODB DEFAULT CHARSET=utf8 

注意：删除有外键关系的表时，要先删除引用了别人的表（从表），再删除被引用的表（主表）。

 

方式二：在创建表的时候，没有写约束

-- ALTER TABLE 表名 ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列`) REFERENCES `表名`(`字段`)
ALTER TABLE student1 ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

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

最佳方案：

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

 

2.DML语言

数据库操作语言

• insert
• update
• delete

1.添加

-- 添加字段里的数据
-- 公式：insert into `表名`(`字段1`,`字段二`,`字段三`) value('值1'),('值2'),('……')
INSERT INTO `grade`(`gradename`) VALUE('大四')
-- 一般写添加我们一定要字段和数据一一匹配！
-- 如果省略了字段，那么值一定要按照全部字段写全，并一一对应！
INSERT INTO `grade`(`gradename`) VALUE('大三'),('大二'),('大一')
INSERT INTO `student1`(`name`,`pwd`,`gradeid`) VALUE('小明','123aaa','2')

 

2.修改

-- 公式：UPDATE `表名` SET `字段1`='值1',`字段2`='值2' WHERE 条件
-- 修改学生名字，带了条件
UPDATE `student1` SET `name`='小红' WHERE id = 1;
-- 不指定条件下，会改动整张表
UPDATE `student1` SET `name`='小红';
-- 修改多个属性，逗号隔开
UPDATE `student1` SET `name`='小绿',`pwd`='123rrr',`birthday`= '2000-1-1' WHERE id=1;
-- value可以是具体的值，也可以是变量
UPDATE `student1` SET  `birthday`= CURRENT_TIME WHERE `name`= '小红' AND sex = '男';

 

3.where子句

条件：where子句运算符 id等于某个值，大于某个值，在某个区间修改……

尽量使用英文字符即关键字

操作符（操作符会返回布尔值）	含义
=	等于
<> != NOT…=…	不等于
>,<,<=,>=
BETWEEN…AND…	在…和…之间
AND &&	多个条件都成立
OR ||	任一条件成立即可

UPDATE `student1` SET `name`='小红' WHERE `name`= '小粉' AND sex = '男';

 

4.删除

delete

-- 公式：DELETE FROM `表名` WHERE 条件
-- 避免使用delete删除整张表
DELETE FROM `grade` WHERE `gradeid`=1

TRUNCATE

-- 完全清空一张数据库表，但是表的结构和索引约束不会变
TRUNCATE `grade`;

两者区别：

• 相同点：都能删除表，但都不会删除表结构
• 不同点：
  • TRUNCATE重新设置自增，计数器会归零
  • TRUNCATE不会影响事务

CREATE TABLE `test`(
    `id` INT(2) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL ,
    PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO `test`(`name`) VALUE('1'),('2'),('3');
-- 不会影响自增
DELETE FROM `test`;
-- 自增会归零
TRUNCATE TABLE `test`;

关于delete删除问题，重启数据库现象：

• innoDB 自增列会从1开始，数据存在内存档中的，断电即失去
• MyISAM 继续从上一个自增量开始，存在文件中的，不会丢失

 
 

4.DQL查询数据

SELECT 字段 AS 别名
FROM 表名 AS 别名
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[LIMIT…]

注意 Where，GroupBy，Having，OrderBy Limit顺序。

执行步骤：

1. 先从from字句一个表或多个表选择字段
2. 将where条件应用于1的表，保留满足条件的进行下一步
3. GroupBy 将2的结果分成多个组
4. Having 将条件应用于3组合的条件过滤，只保留符合要求的组
5. Order By对结果进行排序
6. Limit对结果进行分页

 

1.select

-- 公式：SELECT `字段1`,`字段2`,…… FROM `表名`;
SELECT * FROM `student1`;
SELECT `name`,`pwd` FROM `student1`;
-- 别名：给要查的结果取一个名字AS，也可以给表取别名
SELECT `name` AS 姓名,`pwd` AS 密码 FROM `student1` AS 学生;
-- 连接函数：concat(a,b)
SELECT CONCAT('姓名：',`name`) AS 新名字 FROM `student1`;

 

去重distinct

去除select语句查询的重复内容，重复的内容只显示一条。

SELECT DISTINCT `name` FROM `student1`;

 

一些查询操作

SELECT VERSION();-- 查询系统版本（函数）
SELECT 100*3-1;-- 用来计算（表达式）
SELECT @@auto_increment_increment;-- 查询自增的步长（变量）
-- 学生年级id加一查看
SELECT `gradeid`+1 AS 升学 FROM`student1`;

 

在指定条件下查询

SELECT `name` FROM `student1` WHERE `name`!= '小红';

 

2.模糊查询

比较运算符	含义
IS NULL	操作符为NULL，结果为真
IS NOT NULL	操作符不为NULL，结果为真
BETWEEN…AND…	在两者之间结果为真
Like	SQL匹配
In	某个数据在一组数据之中，则为真

-- 查询姓大的同学
-- like结合 %(0到任意个字符) _(一个字符) __(两个字符)
SELECT `name` FROM `student1` WHERE `name` LIKE('大%'); -- 姓大的
SELECT `name` FROM `student1` WHERE `name` LIKE('大_'); -- 姓大的两个字
SELECT `name` FROM `student1` WHERE `name` LIKE('大__');-- 姓大的三个字
SELECT `name` FROM `student1` WHERE `name` LIKE('%铭%');-- 名字中间有铭这个字的
-- 查询id是3~5的学生  in(具体的一个或多个值)
SELECT `id`,`name` FROM `student1` WHERE `id` IN (3,4,5);
-- 查询出生日期是否为空
SELECT `id`,`name` FROM `student1` WHERE `birthday` IS NULL;
SELECT `id`,`name` FROM `student1` WHERE `birthday` IS NOT NULL;

 

3.联表查询

JOIN

七种join理论：

思路：

• 分析需求，分析要查询的字段来自哪些表（连接查询）

• 确定要使用哪种连接（7种）

• 确定 交叉点 （俩个表中相同的字段）

• 加上判断条件

-- SELECT `字段` FROM `表名` AS 别名 xxx JOIN `另一个表名` AS 别名 ON 交叉条件 WHERE 等值条件;
-- 查询共同的数据
SELECT s.`id`,`name`,`score` FROM `student1` AS s INNER JOIN `grade` AS g
ON s.`id`=g.`id`;
-- 查询左表中的数据（student1），即使右表没有
SELECT s.`id`,`name`,`score` FROM `student1` AS s LEFT JOIN `grade` AS g
ON s.`id`=g.`id`;
-- 查询右表中的数据（grade），即使左表没有
SELECT s.`id`,`name`,`score` FROM `student1` AS s RIGHT JOIN `grade` AS g
ON s.`id`=g.`id`;
-- 查询右表中生日非空的数据
SELECT s.`id`,`name`,`score` FROM `student1` AS s RIGHT JOIN `grade` AS g
ON s.`id`=g.`id` 
WHERE `birthday` IS NOT NULL;

查询多张表：先查两张表，再依次增加。

SELECT s.`id`,`name`,`score` FROM `student1` AS s LEFT JOIN `grade` AS g ON s.`id`=g.`id`
INNER JOIN `test` AS t ON g.`id`=t.`id`;

 

4.自连接

自己的表和自己的表连接，核心：一张表拆成两张一样的表。

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 DEFAULT CHARSET = utf8
INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
VALUE('2','1','信息技术'),('3','1','软件开发'),('4','3','数据库'),('5','1','美术设计'),('6','3','web开发'),('7','5','ps技术'),('8','2','办公信息');

父类：

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

子类：

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

查询父类所对应的子类他两之间的关系：

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

-- 查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`;

 

5.分页和排序

排序 ：ORDER BY 字段 ASC/DESC

CREATE TABLE `test`(
    `id` INT(2) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL ,
    PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO `test`(`id`,`name`) VALUE('2','张一'),('5','李二'),('3','王三'),('7','赵四'),('1','孙五'),('4','高六'),('6','程七')
SELECT `id`,`name` FROM `test`
WHERE `id`!= 1
-- 排序order by：通过那个字段排序，升序(ASC)还是降序(DESC) 
ORDER BY `id` ASC ;

 

分页

分页原因：缓解数据库压力，给人的体验更好。当下也有瀑布流：一般用在图片上，抖音等短视频也是瀑布流。

CREATE TABLE `test`(
    `id` INT(2) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL ,
    PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
INSERT INTO `test`(`id`,`name`) VALUE('2','张一'),('5','李二'),('3','王三'),('7','赵四'),('1','孙五'),('4','高六'),('6','程七')
SELECT `id`,`name` FROM `test`
WHERE `id`!= 1
ORDER BY `id` ASC 
-- 分页：LIMIT 起始值下标 页面大小 
-- 第一页0,3   (1-1)*3
-- 第二页3,3   (2-1)*3
-- 第三页6,3   (3-1)*3
-- 第n页(n-1)*pageSize,pageSize
-- 数据总数/页面大小=总页数
LIMIT 0,3;

 

6.子查询/嵌套查询

在where里嵌套，由里及外。

SELECT `id`,`gradename` FROM `grade`
WHERE `id`=ANY(
   SELECT `id` FROM `student1`
   WHERE `birthday` IS NOT NULL
)

在select里嵌套，由里及外。

SELECT `name` `id` FROM `student1`  WHERE `id` IN (
   SELECT `id` FROM `grade` WHERE `score`>=60 AND `address`=(
      SELECT `address` FROM `test`  WHERE `money` >10
    )
)

 
 

5.MySQL函数

1.常用函数

SELECT ABS(-2)-- 绝对值
SELECT CEILING(2.2)-- 向上取整
SELECT FLOOR(2.2)-- 向下取整
SELECT RAND()-- 随机数，返回0~1间的随机数
SELECT SIGN(-5)-- 判断一个数的符号  0=0 负数=-1 正数=1

-- 操作字符串
SELECT CHAR_LENGTH('疫情快快过去')-- 查询字符串长度
SELECT CONCAT('我','爱','你')-- 拼接字符串
SELECT INSERT('我爱萨摩耶',1,2,'我超级爱')-- 插入，替换：从某个位置开始替换几个长度
SELECT LOWER('Java') -- 小写字母
SELECT UPPER('Java') -- 大写字母 
SELECT INSTR('java','a')-- 返回第一次出现的子串的索引
SELECT REPLACE('是金子总会发光','总会','会快速')-- 替换出现的指定字符串
SELECT SUBSTR('是金子总会发光',6,7)-- 返回指定的子字符串，截取的位置从哪到哪
SELECT REVERSE('耶摩萨')-- 反转

-- 查询姓大的同学，把大换成王
SELECT REPLACE(`name`,'大','王') FROM `student1` WHERE `name` 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()

 

2.聚合函数

-- 统计表中的数据总数
SELECT COUNT(`score`) FROM `grade`-- count(列)，会忽略所有的null值
SELECT COUNT(*) FROM `grade`-- count(*)不会忽略null，本质：计算行数
SELECT COUNT(1) FROM `grade`-- count(1)不会忽略null，本质：计算行数，*和1区别：1只走一列，*走所有列

SELECT SUM(`score`) AS 总分 FROM `grade`
SELECT AVG(`score`) AS 平均分 FROM `grade`
SELECT MAX(`score`) AS 最高分 FROM `grade`
SELECT MIN(`score`) AS 最低分 FROM `grade`

练习：

 

3.数据库级别的MD5函数加密

MD5定义：信息摘要算法，一种被广泛使用的密码散列函数。MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。广泛应用于普通数据的加密保护领域。

MD5破解原理：本质上并不是破解，因为具体值的MD5是一样，破解只是把常见的密码弄了个集合（md5加密后的值，md5加密前的值），复杂的密码就无法破解出来。

CREATE TABLE `testmd5`(
   `id` INT(4) NOT NULL,
   `name` VARCHAR(30) NOT NULL,
   `pwd` VARCHAR(50) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET= utf8
-- 明文密码
INSERT INTO `testmd5` VALUE(1,'张一','123456'),(2,'王二','808765'),(3,'李三','09876'),(4,'唐四','876')
-- 加密
UPDATE `testmd5` SET `pwd`=MD5(`pwd`) WHERE `id`=1
-- 插入的时候加密
INSERT INTO `testmd5` VALUE(5,'赵武',MD5('122233456'))
-- 校验
SELECT * FROM `testmd5` WHERE `name`='赵武' AND `pwd`=MD5('122233456') 

 
 

6.事务

1.事务概念

事务就是要么都成功，要么都失败。将一组SQL语句放在一个批次去执行。

1.SQL执行 A给B转账 A1000—>转200给B（B100）

2.SQL执行 B收到A的钱 A800，B300

 

事务原则：ACID原则

1. 原子性（Atomicity）：
   事务中的操作要么都发生，要么都不发生。

     A转钱成功，B也收到了钱
   

2. 一致性（Consistency）：
   事务前后数据的完整性必须保持一致，符合逻辑运算。

   转账双方加起来的总钱数一定是不变的
   

3. 隔离性（Isolation）：
   针对多个用户同时操作，主要是排除其他事务对本次事务的影响。

   A,C同时给B转钱，不会互相影响
   

4. 持久性（Durability）：

   • 事务没有提交，发生故障，恢复到原来状态。
   • 事务已将提交，发生故障，没有影响，数据已经被持久化到数据库，是不可逆的。

 

事物的隔离级别

1. 脏读：指一个事务读取了另外一个事务未提交的数据。

   A：1000   B：100   C：500
   
   A--->200给B=300，同时C--->200给B
   
   但最终C读取的B是200，最终B还是300，而不是400
   

2. 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。

   这个不一定是错误，只是某些场合不对，就譬如读秒数，秒数是一直在变的
   

3. 虚读（幻读）：在一个事务内读取到了别的事务插入的数据。

   一般是行影响，譬如本来是三行的数据，结果却读出来了四行
   

参考博客连接：https://blog.csdn.net/dengjili/article/details/82468576

 

2.测试事务实现转账

1.测试事务步骤：

-- 手动处理事务
SET autocommit=0 -- 关闭自动提交（MySQL是默认开启事务提交的）
-- 开启事务，标记一个事务的开启，从这个之后的sql都在同一个事物内
START TRANSACTION
INSERT xxx
INSERT xxx
-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 结束事务
SET autocommit=1 -- 开启自动提交

-- 中间可能会有的步骤
SAVEPOINT 保存点名字 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点：关卡性游戏的重生点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

 

2.模拟转账

 -- 创建数据库和表
 CREATE DATABASE `bank` CHARACTER SET utf8 COLLATE utf8_general_ci
 USE bank
 CREATE TABLE `account`(
     `id` INT(3) NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(50) NOT NULL,
     `money` DECIMAL(9,2) NOT NULL,
     PRIMARY KEY(`id`)
 )ENGINE=INNODB DEFAULT CHARSET=utf8
 INSERT INTO `account`(`name`,`money`) VALUE('A',1000.00),('B',100.00)
 
 -- 转账
 SET autocommit=0 -- 关闭自动提交
 START TRANSACTION -- 开启事务
 UPDATE `account` SET `money`=`money`-200 WHERE `name`='A'
 UPDATE `account` SET `money`=`money`+200 WHERE `name`='B'
 COMMIT; -- 提交
 ROLLBACK; -- 回滚
 SET autocommit=1; -- 开启自动提交

 
 

7.索引

1.索引的分类

索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引在小数据里用处不大，但是在大数据里会很快！

分类：

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

 
索引的使用：

1. 在创建表时，给字段增加索引
2. 创建完毕后，增加索引

 -- 显示所有的索引信息
 SHOW INDEX FROM `student1`
 -- 增加一个全文索引（索引名）列名
 ALTER TABLE `school`.`student1` ADD FULLTEXT INDEX `name`(`name`)
 -- EXPLAIN分析sql执行的状况,用EXPLAIN才能看到我们要找的数据
 EXPLAIN SELECT * FROM `student1` -- 非全文索引
 EXPLAIN SELECT * FROM `student1` WHERE MATCH(`name`) AGAINST('大') -- 一行行执行

EXPLAIN在有索引的时候，其实相当于直接定位到要找的东西，就会很快，直接定位到那一行。

 

2.索引的测试

 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(3) 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用户表'
 
 -- 插入一百万条数据 :1min39sec
 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`) VALUE(CONCAT('用户1',i),'3521945355@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'; -- 1.901s
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'; -- 0.130s
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999';

 

3.索引原则

• 索引不是越多越好
• 不要对进程变动的数据加索引，这样索引会改变，每次都会很慢
• 小数据量的不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构：

• Hash类型的索引
• Btree：innoDB的默认数据结构

 
 

8.权限管理和备份

1.用户管理

SQLyog可视化管理：

 

SQL命令操作

用户表：mysql.user

-- 创建用户:CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER moli IDENTIFIED BY '1111'
-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('1234')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR moli = PASSWORD('1111')
-- 重命名:RENAME USER 原来的名字 TO 新名字
RENAME USER moli TO moli1
-- 用户授权：授予全部权限 库.表。 ALL PRIVILEGES除了给别人授权，其它都能干
GRANT ALL PRIVILEGES ON *.* TO moli1
-- 查询指定用户权限;GRANT ALL PRIVILEGES ON *.* TO 'moli1'@'%'
SHOW GRANTS FOR moli1
-- 查询root用户权限：GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
SHOW GRANTS FOR root@localhost
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM moli1
-- 删除用户
DROP USER moli1

 

2.MySQL备份

MySQL备份方式：

• 直接拷贝物理文件，就是那个data文件夹

• 在sqlyog这种可视化工具中手动导出

  

• 使用命令行导出:mysqldump

  -- mysqldump -h主机 -u用户 -p密码 数据库 表>物理磁盘位置:/文件名
  mysqldump -hlocalhost -uroot -p1234 school student1 test>D:/a.sql
  
  -- 导入：先登上mysql，再切换到指定的数据库，最后导入
  mysql -uroot -p1234
  use school;
  source D:/a.sql
  -- 另一种导入
  mysql -u用户名 -p密码 数据库<备份文件名
  

备份的作用：

• 备份数据库，防止数据丢失
• 把数据库给朋友，直接把sql文件给别人即可

 
 

9.规范数据库设计

1.为什么要规范设计

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

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会很麻烦，甚至异常。因此避免使用物理外键
• 程序的性能差

良好的数据库设计：

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

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

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

 

2.设计数据库的步骤：

个人博客

• 收集信息，分析需求

  • 用户表（用户登录注销，用户的个人信息）

  

  • 分类表（文章分类，谁创建的）

  

  • 文章表（文章的信息）

  

  • 评论表
    

  • 友链表（别人的链接）
    

  • 自定义表（系统信息，某个关键字，或者一些主字段，key value）

  • 粉丝表：是一个中间表
    

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

• 标识实体之间的关系

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

 

3.数据库的三大范式

数据规范化原因：

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

 

三大范式

第一范式（1NF）：

原子性：保证每一列不可再分

第二范式（2NF）：

• 前提：满足第一范式
• 每张表只描述一件事情

第三范式（3NF）：

• 前提：满足一二范式
• 第三范式需要保证数据表中的每一列数据都和主键直接相关，而不能间接相关

 

规范性和性能问题：

关联查询的表不得超过三张表！（阿里规定）

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