MySQL学习笔记2：数据库表的增删改与外键认识

1、前言

学习视频源自：【狂神说Java】MySQL最新教程通俗易懂

B站视频链接：https://www.bilibili.com/video/BV1NJ411J79W

【狂神说Java】MySQL最新教程通俗易懂

笔记为看视频自行撰写，仅限学习交流使用

1.1、上一篇文章

MySQL学习笔记1：数据库连接与字段认识

3、操作数据库

3.4创建数据库表（重点）

--  注意点,使用英文(),表的名称和字段尽量使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用单引号括起来
-- 所有的语句加,(英文的)，最后一个字段不用加
-- 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] 表名(
 '字段名' 列类型 [属性] [索引] [注释],
 '字段名' 列类型 [属性] [索引] [注释],
    ...
 '字段名' 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令

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

3.5数据表的类型

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

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

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就是对应一个存储

本质还是文件的存储

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

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

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

CHARSET=utf8

不设置的话，会是MySQL默认的字符集编码(不支持中文)

在my.ini中配置默认的编码

character-set-server=utf8

3.6修改删除表

修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段 重命名，修改约束
-- ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE student1 MODIFY age VARCHAR(11) --修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性
ALTER TABLE student1 CHANGE age age1 INT(11) --字段重命名
-- 删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE student1 DROP age

删除

-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS student1

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

注意点：

• ``字段名使用这个包裹
• 注释 – /* */
• SQL关键字大小写不敏感，建议写小写
• 所有的符号全部用英文

4、MySQL数据管理

4.1、外键

方式一、在创建表的时候，增加约束

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

-- 学生表的gradeid字段 要去引用年级表的id
-- 定义外键key
-- 给这个外键添加约束（执行引用）
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 '学生的年级',
    `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 `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 '学生的年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建表的时候没有外键关系

ALTER TABLE `student1` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

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

以上操作都是物理外键，数据库级别的操作，不建议使用，避免数据库太多造成困扰，这里了解即可

最佳实践

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