要求:
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)