MySQL数据表的创建与管理
作业
https://sharenjun.github.io/media/posts/15/
注意事项
MySQL 8.0.16之前的版本不支持CHECK约束，CHECK约束会被忽略。从MySQL 8.0.16版本开始，CHECK约束得到了支持和强制执行。
安装
https://downloads.mysql.com/archives/community
用管理员方式运行cmd
进去改密码。
登录成功。
设置远程登录。
创建表
create table teacher (
tchNo char(10),#教职工号
tchName varchar(10),#教师姓名
tchRand varchar(10),#教师职称
tchDepat varchar(20)#教师所在分院
);
create table course (
corNo char(10),#课程号
corName varchar(40),#课程名
corTime tinyint,#课时
corCredit decimal(4,1),#课程学分
corDepart varchar(20)#开课分院
);
create table teachcourse (
corNo varchar(10),#课程号
tchNo char(10),#教职工号
teachTime varchar(40),#教师授课时间
teachAddress varchar(20),#教师授课地点
teachsScore decimal(4,1)#学评教分数
);
create table userinfo (
userName char(10),#用户名
userPsd char(10)# 密码
);
create table grade(
stuNo char(8),#学生学号
corNo varchar(10),#课程号
score tinyint,#学生成绩
remark text #备注
);
修改表与设置约束部分
1.
alter table userinfo add userID int primary key auto_increment;
2.
alter table userinfo change column userPsd userPassword varchar(30) not null;
3.
alter table grade drop column remark;
4.
alter table course add primary key (corNo);
alter table student add primary key (stuNo);
alter table teacher add primary key (tchNo);
alter table userinfo add primary key (userID);
5.
再添加外键约束之前需要查看每个表中是否有重复的主键。这就是我们需要的外键
#添加 teachcourse 表的外键约束
alter table teachcourse add constraint fk_corNo foreign key (corNo) references course (corNo);
alter table teachcourse add constraint fk_tchNo foreign key (tchNo) references teacher (tchNo);
#添加 grade 表的外键约束
alter table grade add constraint fk_stuNo foreign key (stuNo) references student (stuNo);
alter table grade add constraint fk_corNo foreign key (corNo) references course (corNo);
6.
#student表
alter table student modify column stuName varchar(20) not null;
#teacher表
alter table teacher modify coulmn tchName varchar(10) not null;
#course表
alter table course modify coulmn corName varchar(40) not null;
#userinfo表
alter table userinfo
modify coulmn userName char(10) not null,
modify column userPassword varchar(30) not null;
#teachcourse表
无
#grade表
无
7.
#设置唯一约束
alter table userinfo add unique (userName);
8.
alter table grade alter score set default 0;
9.
alter table student add constraint chk_stuNo check(stuNo regexp(“^[0-9]+$”));
10.
alter table grade add constraint chk_score check(score between 0 and 150);
11.
删除之前先备份数据库。
#删除主键约束
##首先移除自增
alter table userinfo modify column userID int;
alter table userinfo drop primary key;
#添加主键约束
alter table userinfo add primary key (userID);
alter table userinfo modify column userID int auto_increment;
#删除外键约束
alter table teachcourse drop foreign key fk_corNo;
#添加外键约束
alter table teachcourse add constraint fk_corNo foreign key (corNo) references course(corNo);
#检查是否非空约束
select constraint_name from information_schema.key_column_usage where table_name=”userinfo” and column_name = “userName”;
#删除非空约束
alter table userinfo modify column userName char(10) default null;
#添加非空约束
alter table userinfo modify column userName char(10) not null;
#删除唯一约束
alter table userinfo drop index userName;
#添加唯一约束
alter table userinfo add unique(userName);
#删除检查约束
alter table student drop check chk_stuNo;
#添加检查约束
alter table student add constraint chk_stuNo check(stuNo,regexp(“^[0-9]+$”));
#删除默认约束
alter table grade alter column score drop default;
#添加默认约束
alter table grade alter column score set default 0;
#开始测试约束
主键约束
插入,当我们插入两个重复的stuNo的时候就会报错。
非空约束
alter table userinfo modify column userName char(10) not null;
alter table userinfo modify column userPassword char(10) not null;
insert into userinfo (userName, userPassword, userID) value(‘’,’’,’3’);
Check约束
insert into student (stuNo, stuName, stuClass) values (‘A202001’, ‘王五’, ‘计算机科学与技术’);–失败的
默认值约束
insert into grade (corNo,stuNo) values (1,2);
唯一约束测试
包含主键约束、非空约束、唯一约束
外键约束测试
重新创建两个表
命令:
#外键约束测试
CREATE DATABASE three_work;
– 创建students表
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);
– 创建enrollments表
CREATE TABLE enrollments (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_name VARCHAR(100) NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
– 插入学生数据
INSERT INTO students (student_name) VALUES (‘Alice’);
INSERT INTO students (student_name) VALUES (‘Bob’);
– 插入选课数据，其中student_id引用students表中存在的学生ID
INSERT INTO enrollments (student_id, course_name) VALUES (1, ‘Math’);
INSERT INTO enrollments (student_id, course_name) VALUES (2, ‘Physics’);
插入错误数据
– 尝试插入一个不存在的学生ID，这应该违反外键约束
INSERT INTO enrollments (student_id, course_name) VALUES (3, ‘Biology’); – 应该失败，因为没有ID为3的学生
记得开启外键约束。
SET GLOBAL foreign_key_checks = 1;
FLUSH PRIVILEGES;
NET STOP MYSQL;
NET START MYSQL;
知识问答
Create database 数据库；
Ues database;
Create table table_name(变量名 类型,…);
主键:primary key
外键:foreign key
默认值约束:default
唯一约束:unqiue
主键
外键
默认值约束