Mysql学习笔记（连载中）

Mysql01 使用CMD连接数据库

-- 在控制台连接数据库（需将位置切换到mysql所在地址）mysql -u root -ppassword: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 安全性高，事务的处理，多表多用户操作

在物理空间的位置

所有的数据库文件都存在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#服务端使用的字符集默认为utf8mb4character-set-server=utf8mb4#创建新表时将使用的默认存储引擎default-storage-engine=INNODB#默认使用“mysql_native_password”插件认证#mysql_native_passworddefault_authentication_plugin=mysql_native_password[mysql]#设置mysql客户端默认字符集default-character-set=utf8mb4[client]#设置mysql客户端连接服务端时默认使用的端口port=3306default-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=utf8CREATE 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 修改UPDATE3.5 删除DELET