MySQL数据库创建、删除、修改

一：建库建表

我们以学校体系进行建表。将数据库命名为school。

以下代码中的大写均可小写不影响。如CREATE DATABASE与create database相同

四个关键的实体分别是学院、老师、学生和课程，其中，学生跟学院是从属关系，这个关系从数量上来讲是多对一关系，因为一个学院可以有多名学生，而一个学生通常只属于一个学院；同理，老师跟学院的从属关系也是多对一关系。一名老师可以讲授多门课程，一门课程如果只有一个授课老师的话，那么课程跟老师也是多对一关系；如果允许多个老师合作讲授一门课程，那么课程和老师就是多对多关系。简单起见，我们将课程和老师设计为多对一关系。学生和课程是典型的多对多关系，因为一个学生可以选择多门课程，一门课程也可以被多个学生选择，而关系型数据库需要借助中间表才能维持维持两个实体的多对多关系。最终，我们的学校选课系统一共有五张表，分别是学院表（tb_college）、学生表（tb_student）、教师表（tb_teacher）、课程表（tb_course）和选课记录表（tb_record），其中选课记录表就是维持学生跟课程多对多关系的中间表。

使用create database进行创建数据库。

-- 如果存在名为school的数据库就删除它
DROP DATABASE IF EXISTS `school`;-- 创建名为school的数据库并设置默认的字符集和排序方式
CREATE DATABASE `school` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

 CREATE DATABASE school：创建一个名为school的新数据库。

DEFAULT CHARACTER SET utf8mb4：指定数据库的默认字符集为utf8mb4。utf8mb4是UTF-8编码的一个变体，支持完整的Unicode字符集，包括一些特殊的表情符号和更广泛的字符范围（如某些语言中的特殊字符）。

COLLATE utf8mb4_general_ci：指定数据库的默认排序规则为utf8mb4_general_ci。general_ci表示不区分大小写的排序规则（ci代表Case Insensitive），这意味着在字符串比较时，不会区分大小写。

切换到我们的数据库 使用USE＋数据库名称

USE `school`;

创建学院表：

-- 创建学院表
CREATE TABLE `tb_college`
(
`col_id`    int unsigned AUTO_INCREMENT      COMMENT '编号',
`col_name`  varchar(50)  NOT NULL            COMMENT '名称',
`col_intro` varchar(500) NOT NULL DEFAULT '' COMMENT '介绍',
PRIMARY KEY (`col_id`)
);

其中col_id、col_name、col_intro是字段名称，表示存储编码的列。

int是整型，varchar是字符型（50）代表最大50个字符长度。

unsigned是约束修饰符，表示字段仅存储非负整数（范围变为0到约42亿），适合编号这种无负值的场景。

auto_increment是约束修饰符，表示该字段的值会自动递增。通常用于主键字段，以便在插入新记录时自动生成唯一的编号。

comment是注释说明，便于维护时理解字段含义。

not null 约束条件，表示该字段不允许存储NULL值（必须始终有值），确保名称必填。

default为默认值设置，表示如果插入新记录时未提供col_intro的值，则该字段将默认设置为空字符串''。这确保了即使没有提供介绍信息，字段也不会为NULL。

primary key col_id 是表示主键为col_id，主键是表中用于唯一标识每条记录的字段或字段组合。

创建学生表：

CREATE TABLE `tb_student`
(
`stu_id`    int unsigned NOT NULL           COMMENT '学号',
`stu_name`  varchar(20)  NOT NULL           COMMENT '姓名',
`stu_sex`   boolean      NOT NULL DEFAULT 1 COMMENT '性别',
`stu_birth` date         NOT NULL           COMMENT '出生日期',
`stu_addr`  varchar(255) DEFAULT ''         COMMENT '籍贯',
`col_id`    int unsigned NOT NULL           COMMENT '所属学院',
PRIMARY KEY (`stu_id`),
CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`)
);

其中大部分的约束与上述学院表类似，不过多赘述。

下面这段表示一个外键的约束定义。

CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`)
)

constraint fk_student_col_id定义了一个为 fk_student_col_id的约束，名称可以变化，命名为了数据库中识别和管理约束。

foreign key col_id 指定col_id字段为外键。外键用于建立当前表（假设为tb_student）与另一个表之间的引用关系。

references tb_college col_id 指定外键引用的目标表和字段。在这里，tb_college是目标表，col_id是目标表中的字段。这意味着当前表中的col_id字段的值必须存在于tb_college表的col_id字段中。

创建教师表：

CREATE TABLE `tb_teacher`
(
`tea_id`    int unsigned NOT NULL                COMMENT '工号',
`tea_name`  varchar(20)  NOT NULL                COMMENT '姓名',
`tea_title` varchar(10)  NOT NULL DEFAULT '助教' COMMENT '职称',
`col_id`    int unsigned NOT NULL                COMMENT '所属学院',
PRIMARY KEY (`tea_id`),
CONSTRAINT `fk_teacher_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`)
);

创建课程表：

CREATE TABLE `tb_course`
(
`cou_id`     int unsigned NOT NULL COMMENT '编号',
`cou_name`   varchar(50)  NOT NULL COMMENT '名称',
`cou_credit` int          NOT NULL COMMENT '学分',
`tea_id`     int unsigned NOT NULL COMMENT '授课老师',
PRIMARY KEY (`cou_id`),
CONSTRAINT `fk_course_tea_id` FOREIGN KEY (`tea_id`) REFERENCES `tb_teacher` (`tea_id`)
);

创建选课记录表：

CREATE TABLE `tb_record`
(
`rec_id`   bigint unsigned AUTO_INCREMENT COMMENT '选课记录号',
`stu_id`   int unsigned    NOT NULL       COMMENT '学号',
`cou_id`   int unsigned    NOT NULL       COMMENT '课程编号',
`sel_date` date            NOT NULL       COMMENT '选课日期',
`score`    decimal(4,1)                   COMMENT '考试成绩',
PRIMARY KEY (`rec_id`),
CONSTRAINT `fk_record_stu_id` FOREIGN KEY (`stu_id`) REFERENCES `tb_student` (`stu_id`),
CONSTRAINT `fk_record_cou_id` FOREIGN KEY (`cou_id`) REFERENCES `tb_course` (`cou_id`),
CONSTRAINT `uk_record_stu_cou` UNIQUE (`stu_id`, `cou_id`)
);

ps：其中数据库名、表名、字段名都被反引号（`）包裹起来，反引号并不是必须的，但是却可以解决表名、字段名等跟 SQL 关键字（SQL 中有特殊含义的单词）冲突的问题。

二：删除表和修改表

DROP TABLE `tb_student`;
或者
DROP TABLE IF EXISTS `tb_student`;

需要注意的是，如果学生表已经录入了数据而且该数据被其他表引用了，那么就不能删除学生表，否则上面的操作会报错。

修改表，添加一个新列

例如给学生表添加一个联系电话的列。

ALTER TABLE `tb_student` ADD COLUMN `stu_tel` varchar(20) NOT NULL COMMENT '联系电话';

ps：如果新增列的时候指定了非空约束（not null），那么学生表不能够有数据，否则原来的数据增加了 stu_tel 列之后是没有数据的，这就违反了非空约束的要求；当然，我们在添加列的时候也可以使用默认值约束来解决这个问题。

修改表，修改列的数据类型

例如将学生表的 stu_sex 修改为字符。

ALTER TABLE `tb_student` MODIFY COLUMN `stu_sex` char(1) NOT NULL DEFAULT 'M' COMMENT '性别';

 修改表，修改列的命名

例如将学生表的 stu_sex 修改为 stu_gender。

ALTER TABLE `tb_student` CHANGE COLUMN `stu_sex` `stu_gender` boolean DEFAULT 1 COMMENT '性别';

 修改表，删除约束条件

例如删除学生表的 col_id 列的外键约束。

ALTER TABLE `tb_student` DROP FOREIGN KEY `fk_student_col_id`;

修改表，添加约束条件

例如给学生表的 col_id 列加上外键约束。

ALTER TABLE `tb_student` ADD FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`);
或者
ALTER TABLE `tb_student` ADD CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`);

 修改表的名字

例如将学生表的名字修改为 tb_stu_info。轻易不要修改数据库或者表的名字。

ALTER TABLE `tb_student` RENAME TO `tb_stu_info`;

三：常用的库、表、数据等查询方式

 查看所有的数据库

show databases;

切换到特定库 

使用use加库名。

use school

展示库中的表

show tables;

 展示表中的属性

show columns from 表名

show columns from tb_college;

 

 查看表中的数据

select 列名 from 表名

select col_name from tb_college;