MySQL详细学习教程（建议收藏）_mysql教程

目录

• 1、初识数据库
• • 1.1、什么是数据库
  • 1.2、数据库分类
  • 1.3、相关概念
  • 1.4、MySQL及其安装
  • 1.5、基本命令
• 2、操作数据库
• • 2.1、操作数据库
  • 2.2、数据库的列类型
  • 2.3、数据库的字段属性
  • 2.4、创建数据库表
  • 2.5、数据库存储引擎
  • 2.6、修改数据库
• 3、MySQL数据管理
• • 3.1、外键
  • 3.2、DML语言
  • • 1. 添加 insert
    • 2. 修改 update
    • 3. 删除 delete
• 4、DQL查询数据
• • 4.1、基础查询
  • 4.2、条件查询
  • 4.3、分组查询
  • 4.4、连接查询
  • 4.5、排序和分页
  • 4.6、子查询
  • 4.7、MySQL函数
  • • 1. 常用函数
    • 2. 聚合函数
• 5、数据库级别的MD5加密
• 6、事务
• • 6.1、事务原则：ACID
  • 6.2、事务并发导致的问题
  • 6.3、隔离级别
  • 6.4、执行事务的过程
• 7、索引
• • 7.1、索引的分类
  • • 主键索引（PRIMARY KEY）
    • 普通索引（KEY / INDEX）
    • 唯一索引（UNIQUE KEY）
    • 全文索引（FULLText）
  • 7.2、索引的使用
  • • 1. 索引的创建
    • 2. 索引的删除
    • 3. 显示索引信息
    • 4. explain分析sql执行的情况
  • 7.3、测试索引
  • 7.4、索引原则
• 8、explain关键字
• 9、权限管理和备份
• • 9.1、用户管理
  • 9.2、数据库备份
• 10、三大范式
• 11、数据库驱动和JDBC
• • 1. 第一个JDBC程序
  • 2. JDBC对象
  • • DriverManager
    • Statement
    • ResultSet
  • 3. 封装jdbc工具类
  • • 1. 编写数据库配置文件
    • 2. 编写工具类
    • 3. 测试
  • 4. SQL注入问题
  • 5. PreparedStatement对象
  • 6. 事务案例

1、初识数据库

1.1、什么是数据库

数据库：DB（DataBase）

概念：数据仓库，软件，安装在操作系统之上

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

---

1.2、数据库分类

关系型数据库：SQL（Structured Query Language）

• MySQL、Oracle、Sql Server、DB2、SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储
• 通过外键关联来建立表与表之间的关系

非关系型数据库：NoSQL（Not Only SQL）

• Redis、MongoDB
• 指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

---

1.3、相关概念

DBMS（数据库管理系统）

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

---

1.4、MySQL及其安装

• MySQL最新版8.0.21安装配置教程~

---

1.5、基本命令

所有的语句都要以分号结尾

show databases;	--查看当前所有的数据库
use 数据库名;	--打开指定的数据库
show tables;	--查看所有的表
describe/desc 表名;	--显示表的信息
create database 数据库名;	--创建一个数据库
exit	--退出连接


--		--单行注释
#		--单行注释
/*...*/		--多行注释
1
2
3
4
5
6
7
8
9
10
11

---

2、操作数据库

2.1、操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;
1

2、删除数据库

DROP DATABASE [if EXISTS] 数据库名;
1

3、使用数据库

--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;
1
2

4、查看数据库

SHOW DATABASES;
1

---

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

datetime

最常用的时间格式

YYYY-MM-DD HH：mm：ss

timestamp

时间戳，1970.1.1到现在的毫秒数

year

年份表示

null

• 没有值，未知
• 不要使用NULL值进行计算

---

2.3、数据库的字段属性

UnSigned

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

ZEROFILL

• 0填充的
• 不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

• 通常理解为自增，自动在上一条记录的基础上默认+1

• 通常用来设计唯一的主键，必须是整数类型

• 可定义起始值和步长

  • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
  • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

• 默认为NULL , 即没有插入该列的数值
• 如果设置为NOT NULL , 则该列必须有值

DEFAULT

• 默认的
• 用于设置默认值
• 例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

---

拓展：每一个表，都必须存在以下五个字段：

名称

描述

id

主键

version

乐观锁

is_delete

伪删除

gmt_create

创建时间

gmt_update

修改时间

---

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(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
1
2
3
4
5
6
7
8
9
10

注意点：

• 表名和字段尽量使用``括起来

• AUTO_INCREMENT 代表自增

• 所有的语句后面加逗号，最后一个不加

• 字符串使用单引号括起来

• 主键的声明一般放在最后，便于查看

• 不设置字符集编码的话，会使用MySQL默认的字符集编码Latin1，不支持中文，可以在my.ini里修改

格式：

CREATE TABLE IF NOT EXISTS `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]
1
2
3
4
5
6

常用命令：

SHOW CREATE DATABASE 数据库名;-- 查看创建数据库的语句
SHOW CREATE TABLE 表名;-- 查看表的定义语句
DESC 表名;-- 显示表的具体结构
1
2
3

---

2.5、数据库存储引擎

INNODB

• 默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM

• 早些年使用，节约空间，速度较快

MYISAM

INNODB

事务支持

不支持

支持

数据行锁定

不支持

支持

外键约束

不支持

支持

全文索引

支持

不支持

表空间大小

较小

较大，约为2倍

数据库文件存在的物理空间位置：

• MySQL数据表以文件方式存放在磁盘中

  • 包括表文件 , 数据文件 , 以及数据库的选项文件
  • 位置 : Mysql安装目录data（目录名对应数据库名 , 该目录下文件名对应数据表）
    

MySQL在文件引擎上区别：

• INNODB数据库文件类型就包括**.frm**、.ibd以及在上一级目录的ibdata1文件
• MYISAM存储引擎，数据库文件类型就包括
  • .frm：表结构定义文件
  • .MYD：数据文件
  • .MYI：索引文件

---

2.6、修改数据库

修改

修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

修改字段 :

• ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
• ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段 : ALTER TABLE 表名 DROP 字段名

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

-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);

-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

删除

语法：DROP TABLE [IF EXISTS] 表名

• IF EXISTS为可选 , 判断是否存在该数据表

• 如删除不存在的数据表会抛出错误

  – 删除表(如果存在再删除)
  DROP TABLE IF EXISTS teachers;

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

---

3、MySQL数据管理

3.1、外键

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见