创建数据库基础1

要求：

1.建立一张表：表里面有多个字段， 需要包含我们今天讲过每一个数据类型
2.使用6种约束，必须要有样例:
未涉及的知识： select *from tb_name;
insert into tb_name values(列1的值,列2的值,列3的值....)

步骤：

1、创建表(建立一张表：表里面有多个字段， 需要包含我们今天讲过每一个数据类型)

#创建article_tb表
CREATE TABLE `article_tb` (
  `art_id` int DEFAULT NULL,
  `art_title` varchar(255) DEFAULT NULL,
  `art_content` text,
  `art_binary_content` blob,
  `art_intro` tinytext,
  `art_label` set('network','java','python') DEFAULT NULL,
  `art_content2` mediumtext,
  `art_content3` longtext,
  `art_auther_gender` enum('M','F') DEFAULT NULL,
  `art_update_date` date DEFAULT NULL,
  `art_create_time` time DEFAULT NULL,
  `art_create_year` year DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

#查看表结构
mysql> desc article_tb;
+--------------------+--------------------------------+------+-----+---------+-------+
| Field              | Type                           | Null | Key | Default | Extra |
+--------------------+--------------------------------+------+-----+---------+-------+
| art_id             | int                            | YES  |     | NULL    |       |
| art_title          | varchar(255)                   | YES  |     | NULL    |       |
| art_content        | text                           | YES  |     | NULL    |       |
| art_binary_content | blob                           | YES  |     | NULL    |       |
| art_intro          | tinytext                       | YES  |     | NULL    |       |
| art_label          | set('network','java','python') | YES  |     | NULL    |       |
| art_content2       | mediumtext                     | YES  |     | NULL    |       |
| art_content3       | longtext                       | YES  |     | NULL    |       |
| art_auther_gender  | enum('M','F')                  | YES  |     | NULL    |       |
| art_update_date    | date                           | YES  |     | NULL    |       |
| art_create_time    | time                           | YES  |     | NULL    |       |
| art_create_year    | year                           | YES  |     | NULL    |       |
+--------------------+--------------------------------+------+-----+---------+-------+

2、创建表（使用6种约束，必须要有样例:）

#创建学生表
CREATE TABLE `student_info` (
  `stu_no` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `stu_name` varchar(20) NOT NULL,
  `stu_age` tinyint DEFAULT NULL,
  `stu_gender` char(1) DEFAULT 'M',
  `stu_class` int DEFAULT NULL COMMENT '关联到班级表',
  `stu_id` char(18) DEFAULT NULL,
  PRIMARY KEY (`stu_no`),
  CONSTRAINT `student_info_chk_1` CHECK ((`stu_age` between 0 and 100)),
  CONSTRAINT `student_info_chk_2` CHECK ((`stu_gender` in (_utf8mb4'M',_utf8mb4'F')))
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

#创建班级表
 CREATE TABLE `class` (
  `class_id` int NOT NULL AUTO_INCREMENT,
  `class_name` varchar(30) NOT NULL,
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

#添加外键约束（学生表的stu_class字段和班级表的class_id字段关联）
alter table student_info add constraint student_class_fk foreign key(stu_class) references class(class_id);

#添加学生表的stu_id为唯一约束
alter table student_info add constraint unique index(stu_id);

#查看学生表的表结构
mysql> show create table student_info\G
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `stu_no` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `stu_name` varchar(20) NOT NULL,
  `stu_age` tinyint DEFAULT NULL,
  `stu_gender` char(1) DEFAULT 'M',
  `stu_class` int DEFAULT NULL COMMENT '关联到班级表',
  `stu_id` char(18) DEFAULT NULL,
  PRIMARY KEY (`stu_no`),
  UNIQUE KEY `stu_id` (`stu_id`),
  KEY `student_class_fk` (`stu_class`),
  CONSTRAINT `student_class_fk` FOREIGN KEY (`stu_class`) REFERENCES `class` (`class_id`),
  CONSTRAINT `student_info_chk_1` CHECK ((`stu_age` between 0 and 100)),
  CONSTRAINT `student_info_chk_2` CHECK ((`stu_gender` in (_utf8mb4'M',_utf8mb4'F')))
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)