Mysql

01 使用CMD连接数据库

-- 在控制台连接数据库（需将位置切换到mysql所在地址）
mysql -u root -p
password:12345

-- 修改mysql账户密码及权限，安装配置完后慎用
update mysql.user set authentication_string = password('12345') where user = 'root' and Host = 'localhost';-- 修改用户密码
flush privileges; -- 刷新权限

--------------------------------------------------
-- 所有语句需要使用;作为结尾
show databases; -- 查看所有数据库

use test01; -- 切换数据库 use+数据库名

show tables; --查看数据库中所有的表
describe `user`; --显示某张表的信息

create database test01; -- 创建一个数据库

exit; -- 退出连接

-- 单行注释
-- 下面是多行注释
/*
hello
*/

• 数据库xxx语言 CRUD 增删改查
  • DDL 定义
  • DML 操作
  • DQL 查询
  • DCL 控制

02 操作数据库

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

mysql关键字不区分大小写！！

2.1 操作数据库

• 创建数据库

CREATE DATABASE test01; -- 创建数据库01
-- 但问题是如果test01存在，系统会报错
-- 所以一般会加入一个判断条件
CREATE DATABASE IF NOT EXISTS test01;

• 删除数据库

DROP DATABASE IF EXISTS test01;

• 使用数据库

-- tab键上方有一个``，如果数据库的表名或者字段名是一个特殊字符，就需要添加
USE `test01`;

• 查询数据库

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

2.2 数据库列数据类型

数字

2.3 数据库字段属性（重要）

• unsigned：

  • 无符号的整数
  • 声明了该列不能填充负数

• zerofill：

  • 0填充的
  • 不足位数在前面用0来填充，例如int(3),5-->005

• 自增

  • 通常理解为自增，自动在上一条记录的基础上+1（默认）
  • 通常用来设计唯一的主键~index，必须是整数类型
  • 可以自定义自增步长
  • Navicat中Mysql自增设置在选项中

• 非空 NULL not NULL
  • 假如设置为not null ，如果不给他赋值就会报错！
  • NULL，如果不填写值，默认就是NULL！
• 默认：
  • 设置默认的值
  • 例如 sex，默认值为男性，如果没有特别指定该列的值，则会有默认的值！
  • 需要注意，在Navicat中，默认值设置，自增，无符号以及填充零都在下方

• 拓展

/*每一个表都必须存在以下五个字段，未来做项目都有用，表示一个记录存在的意义
id 主键
is_delete 伪删除
gmt_create 创建时间
gmt_update 更新时间
*/

2.4 创建数据库表（重点）

-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL语句创建
-- 学号int 登陆密码varchar(20) 姓名 性别varchar(2) 出生日期(datetime) 家庭住址 email
-- 注意点，使用英文()，表的名称和字段尽可能使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用单引号括起来；
-- 所有的语句后面加,(英文的),最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键

CREATE TABLE IF NOT EXISTS `studentinformation`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' 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] `表名`(
    `字段名` 列类型 [属性] [索引][注释],
    `字段名` 列类型 [属性] [索引][注释],
    ·······
    `字段名` 列类型 [属性] [索引][注释]
)[表类型][字符集设置][注释]

2.5 数据表的类型

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

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

常规使用操作：

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

在物理空间的位置

所有的数据库文件都存在data目录下

所以数据库的本质还是文件的存储

Mysql引擎在物理文件上的区别

• InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM对应文件
  • *.frm 表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）
• 这里有必要提到的是，MySQL8开始删除了原来的frm文件，并采用 Serialized Dictionary Information (SDI), 是MySQL8.0重新设计数据词典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中。
  如何可以查看表结构信息，官方提供了一个工具叫做ibd2sdi，在安装目录下可以找到，可以离线的将ibd文件中的冗余存储的sdi信息提取出来，并以json的格式输出到终端。可参考一篇CSDN博客。https://blog.csdn.net/philipslu/article/details/102499234

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

CHARSET = utf8

也可以采取在数据库安装目录下新建一个ini文件的方式进行默认设置。不如不单独设置，系统会默认为一个latin字符集，我的ini文件格式如下：

#设置3306端口
port=3306
#设置mysql的安装目录   ----------是你的文件路径-------------
basedir=E:\WorkappPackage\Mysql\mysql-8.0.32-winx64
#设置mysql数据库的数据的存放目录  ---------是你的文件路径data文件夹自行创建
#datadir=E:\WorkappPackage\Mysql\mysql-8.0.32-winx64\data
#允许最大连接数
max_connections=200
#允许连接失败的次数。
max_connect_errors=10
#服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

• 前面有关于文件路径以及data保存路径的设置需要根据实际情况进行调整。后面的端口设置为系统默认的3306。其他相关内容可参考文章：https://blog.csdn.net/weixin_42820850/article/details/129108669

2.6 修改删除表

修改

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE test01 Rename AS test02

-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE test02 ADD age INT(10)

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE test02 MODIFY age VARCHAR(10)
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE test01 CHANGE age age1 INT(1) -- 字段重命名

-- 删除表的字段
ALTER TABLE test01 DROP age1

• 可以观察到，所有对于表的修改都是以ALTER开头的

删除

-- 删除表(加入判断条件，如果存在再删除)
DROP TABLE IF EXISTS test01

• 需要注意，所有的创建和删除尽量加上判断，以免出现报错

03 MySQL数据管理

3.1 数据库级别外键（了解）

方式一：在创建表的时候，增加外键语句和关联语句等约束（比较复杂）

CREATE TABLE IF NOT EXISTS `studentinformation`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
		`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
		`gradeid` INT(3) NOT NULL COMMENT'年级id',
		`address` VARCHAR(100) DEFAULT 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 IF NOT EXISTS `grade`(
    `gradeid` INT(3) NOT NULL COMMENT'年级id',
		`gradename` VARCHAR(10) NOT NULL COMMENT'年级名称',
		PRIMARY KEY(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

• 运行结果如下

• 可以看到此时由于外键关联关系的存在，原表在删除以及对某些字段内容进行修改时都收到了约束
• 删除时需要先删除引用的表，然后再回来删除被引用的表

方法二：正如上面所示，可以在外键部分手动添加，这里不做展开，与上面运行结果类似即可

方法三：

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


-- 外部创建外键约束
CREATE TABLE IF NOT EXISTS `studentinformation`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
		`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
		`gradeid` INT(3) NOT NULL COMMENT'年级id',
		`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT'电子邮箱',
		PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
-- 外部添加外键约束
ALTER TABLE `studentinformation`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)

-- 结构
ALTER TABLE 表
ADD CONSTRAINT 约束名 FOREIGN KEY(`作为外键的列`)

• 但需要注意的是，以上的操作均为物理外键，是在数据库级别进行操作的外键，一般不推荐使用。（避免数据库过多造成困扰）

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 如果想使用多张表的关联操作，即使用外键（在程序中实现）

参考文章：https://www.cnblogs.com/rjzheng/p/9907304.html

3.2 DML语言（需要全部记住）

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

DML语言：数据操作语言

• INSERT
• UPDATE
• DELETE

3.3 添加INSERT

格式

-- 插入语句（添加）
-- INSERT INTO 表名 (`字段名1`,`字段名2`,`字段名3`) VALUES('第一条数据字段1数据','第一条数据字段2数据','第一条数据字段3数据'),('第二条数据字段1数据','第二条数据字段2数据','第二条数据字段3数据')····

-- 例：
INSERT INTO `grade`(`gradename`)VALUES('大一')
-- 需注意的是，跟在表名后面的括号内容可以选择表中的部分字段，但是需要观察表结构，那些非空且没有默认值的或者没设置自增的字段必须包括在内，否则会报错。还需注意，选定了括号内容后，VALUES后面的括号内的结构需与前面保持一致。如果想一次性添加多条数据，需要在括号中间用“,”隔开。

-- 错误示例：
INSERT INTO `grade` VALUES('大二')
-- 此处在表名`grade`后面没有括号，因此Mysql就会默认格式为全部字段。依照上面所述，数据库会对VALUES后面数据进行匹配。因此会报错。
-- 正确示例：
INSERT INTO `grade` VALUES(2,'大二')

-- 插入多条数据
INSERT INTO `grade`(`gradename`)
VALUES ('大三'),('大四')

• 再次强调，必须注意标点符号的中英文格式！

注意事项：

• 字段和字段之间用英文逗号隔开
• 字段是可以省略的，但是后面括号内的值需要一一对应，不能少
• 可以同时插入多条数据，VALUES后面的值，需要使用隔开即可

3.4 修改UPDATE

3.5 删除DELET