走进数据库数据库!

发布时间：2023-08-25 13:00

1、初识MySQL

JavaEE : 企业级Javan Web前端(页面:展示,数据!)后台(连接点: 连接数据库JDBC,链接前端(控制,控制视图跳转,和给前端传递数据))数据库(存数据, ITxt, Excel, word)

只会写代码，学好数据库，基本混饭吃！
操作系统，数据结构与算法！当一个不错的程序猿！
离散数学，散字电路，体系结构，编译原理，+实战经验，高级程序猿_{优秀的程序猿}

1.1、为什么学习数据库

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

1.2、什么是数据库

数据库（DB,DataBase) 概念：数据仓库，软件，安装在操作系统（window,inux,mac、
.….)之上！SQL,可以存储大量的数据。500万！
作用：存储数据，管理数据Excel

1.3、数据库分类

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

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

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

1.4、MySQL简介

MySQL是一个关系型数据库管理系统
前世：瑞典MySQLAB公司
今生：属于Oracle旗下产品
MySQL是最好的RDBMS(Relational Database Management System,关系数据库管理系统)应用软件之一。
开软的数据库软件~
体积小、速度快、总体拥有成本低，招人成本较低，所有人必须会
中小型网站、或者大型网站，集群！
官网：https://www.mysql.com
官网下载地址：https://dev.mysql.com/downloads/mysql/

安装建议：
1、尽量不要使用exe，注册表，否侧卸载很麻烦
2、尽可能使用压缩包安装~

1.5、安装Mysql

教程：教你安装mysql 含有下载资源

1.6、安装Sqlyog

无脑安装下一步完成运行
注册
打开连接数据库

2、操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据
mysql关键字不分区大小写

2.1、操作数据库

1、创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
2、删除数据库
DROR DATABASE [IF EXISTS] westos
3、使用数据库
– tab 健的上面,如果你的表名或者字段名是一个特殊字符,就需要带`‘’ USE ‘school’
4、查看数据库
show datanase

2.2、数据库的列类型

数值：

tinyint 十分小的数据 1个字节
smallint 较小的数据 2字节
mediumint 中等大小数据 3字节
int 标准的整数 4字节
bigint 较大的数据 8字节
float 浮点数 4字节
double 浮点数 8字节
decimal 字符串形式的浮点数金融计算时候，一般使用

字符串：

char 字符串 0~255
varchar 可变字符串 0~65535
tinytext 微型文本2^8-1
text 文本串 2^16-1

时间日期

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

null

没有值，未知

2.3、数据库的字段属性

Unsigned

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

zerofill

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

自增

自动在上一条的基础上加1
通常设置唯一主键 index

非空 Null not null

假设设置为 not null 如果不给他值会报错
null 不填写值，默认就是null

默认

设置默认的值

2.5、数据表类型

INNODB 默认使用
MYISAM 早些年使用

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍

常规使用操作：

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

在物理空间存在的位置所有的数据文件都存在data目录下本质还是文件的存储

2.6、修改删除表

3、mysql数据管理

3.1、外键

3.2、DML语言

3.3、添加

3.4、修改

3.5、删除

4、DQL查询数据

4.1、DQL

4.2、指定查询字段

4.3、条件子句

模糊查询

4.4、联表查询

自连接

4.5、分页和排序

分页

语法：limit(查询起始下标，pageSize)

4.6、子查询

4.7、分组过滤

5、Mysql函数

官网：https://dev.mysql.com/doc/

5.1、常用函数

5.2、聚合函数（常用）

5.3、数据库级别的MD5加密

6、事务

6.1、什么是事务

要么都成功，要么都失败

1、sql执行，a给b转账 a 1000 —> b 200
2、sql执行 b收到a的钱 a 800 --> b 400

将一组sql放在一个批次中去执行

事务原则：

acid原则原子性，一致性，隔离性，持久性（脏读，幻读…）

原子性

要么都成功，要么都失败

一致性

事务前后的完整性要保证一至，1000

持久性 – 事务提交

事务一旦提交则不可逆，被持久化到数据库

隔离性


执行事务

7、索引

7.1、索引分类

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

7.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(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用户表'

批量插入数据：100w

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'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

7.3、索引准则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表建议不要加索引
索引一般应加在查找条件的字段

8、权限管理和备份

8.1、用户管理

使用SQLyog 可视化管理
基本命令

/* 用户和权限管理 */ ------------------
用户信息表：mysql.user

-- 刷新权限
FLUSH PRIVILEGES

-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  - 只能创建用户，不能赋予权限。
  - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  - 密码也需引号，纯数字密码也要加引号
  - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 设置密码
SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码

-- 删除用户 DROP USER kuangshen2
DROP USER 用户名

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有权限
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表

-- 查看权限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
   -- 查看当前用户权限
  SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

权限解释

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE    -- 允许使用UPDATE
USAGE    -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限


/* 表维护 */

-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

8.2、MySQL备份

数据库备份必要性

保证重要数据不丢失
数据转移

1.MySQL数据库备份方法：
1.1直接拷贝数据库文件和相关配置文件.
1.2在SQLyog这种可视化工具中手动导出

1.3使用命令行导出 mysqldump

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source 备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件