linux MySql选课系统数据库 DML操作（第二天）

选课系统数据库

修改数据库的默认编码为utf8

推荐文章：https://blog.csdn.net/weixin_39944944/article/details/113151863

数据库模型图

PK表示 primary key FK表示 foreign key 
箭头的指向指向的是主表，也就是提供数据的那部分。
classes表是班级表，student表是学生表，choose表是选课表，course表是课程表，teacher表是教师表。
class_no:班级编号
class_name:班级名称
department_name:院系名称
student_no:学号
student_name:学生姓名
student_contact:学生联系方式
choose_no:选课编号
score:课程成绩
choose_time:选课时间
course_no:课程编号
teacher_no:教师编号
teacher_name:教师名字
teacher_contact:教师联系方式
course_no:课程编号
course_name:课程名
up_limit:选课人数上限
description:课程描述信息
status:课程的状态

表结构

班级表classes

字段名	数据类型	约束	描述
class_no	int	主键 自增长	班级编号
class_name	char(20)	唯一、非空	班级名称
department_name	char(20)	非空	院系名称

学生表student

字段名	数据类型	约束	描述
student_no	char(11)	主键	学号
student_name	char(10)	非空	学生姓名
student_contact	char(20)	非空	联系方式
class_no	int	外键	班级编号

教师表teacher

字段名	数据类型	约束	描述
teacher_no	char(10)	主键	教师编号
teacher_name	char(10)	非空	教师姓名
teacher_contact	char(20)	非空	联系方式

课程表course

字段名	数据类型	约束	描述
course_no	int	主键、自增长	课程编号
course_name	char(10)	非空	课程名称
up_limit	int	默认60	上限人数
description	varchar(100)	非空	描述
status	char(6)	默认 ‘未审核’	状态
teacher_no	char(10)	非空、唯一、外键	授课教师工号

选课表choose

字段名	数据类型	约束	描述
choose_no	int	主键、自增长	选课编号
student_no	char(11)	非空、外键	学号
course_no	int	非空、外键	课程编号
score	tinyint unsigned		成绩
choose_time	datetime	非空	选课时间

数据库表的实现

我的外键名命名规则：主表_从表_fk

班级表

create database choose;
use choose;
# 创建课程表
create table classes(
	class_no int auto_increment primary key, # 班级编号 主键 自增长
    class_name char(20) not null unique, # 班级名称 不允许为空、且不重复
    department_name char(20) not null  # 院系名称不允许为空
);

学生表

create table student(
	student_no char(11) primary key, # 学号主键
    student_name char(10) not null, # 学生姓名
    student_contact char(20) not null, # 联系方式
    class_no int, # 外键 习惯性写在最后面。
    constraint classes_student_fk foreign key(class_no) references classes(class_no)
);

教师表

create table teacher(
	teacher_no char(10) primary key,
    teacher_name char(10) not null, # 教师姓名
    teacher_contact char(20) not null # 教师联系方式
);

课程表

create table course(
	course_no int auto_increment primary key,
    course_name char(10) not null,
    up_limit int default 60,
    description varchar(100) not null,
    status char(6) default '未审核',
    teacher_no char(10) not null unique,
    constraint teacher_course_fk foreign key(teacher_no) references teacher(teacher_no)
);

选课表

create table choose(
	choose_no int auto_increment primary key,
    student_no char(11) not null,
    course_no int not null,
    score tinyint unsigned,
    choose_time datetime not null, # 选课时间，由now()函数自动生成
    constraint student_choose_fk foreign key(student_no) references student(student_no),
    constraint course_choose_fk foreign key(course_no) references course(course_no)
);

DML操作

表记录的插入

insert插入一行数据

insert into 表名[(字段列表)] values(值列表);
当字段列表不写的时候，那么值列表必须按照表的全部字段和顺序进行填写。
create table teacher(
	teacher_no char(10) primary key,
    teacher_name char(10) not null, # 教师姓名
    teacher_contact char(20) not null # 教师联系方式
);
insert into teacher values('001','lihua','19293090121');
# 等同于
insert into teacher(teacher_no,teacher_name,teacher_contact)
values('002','huahua','19293094121');
# 等同于
insert into teacher(teacher_name,teacher_no,teacher_contact)
values('lisha','003','19296090121');

包含自增长或者允许为空或者有默认值的插入数据，可以不填写。
# 自增长或者为空的时候
insert into classes 
values(null,'2018自动化1班','机电工程');
insert into classes(class_name,department) 
values('2018自动化2班','机电工程');
# 默认值 使用default填写
insert into course 
values(null,'C语言',default,'无','已审核','001');

insert插入多行数据

insert into 表名[(字段列表)] values(值列表1),
(值列表2),(值列表3),...(值列表n);
# 插入多个数据
insert into student values
	('2018001','张三','19209389213',1),
    ('2018002','李四','19898389213',2),
    ('2018003','王五','19209389893',1),
    ('2018004','花花','19093389213',3);

insert…select语句

在 insert语句中使用 select子句可以将源表的查询结果添加到目标表中。常用于备份数据
语法格式：
	insert into 目标表名[(字段列表1)] select (字段列表2) 
	from 源表 [where 条件表达式];
# eg 创建类似表，并插入数据
create table new_student like student;
insert into new_student select * from student;

表记录的更新

语法：
update 表名 set 字段名1=值1[,字段名2=值2...] 
[where 条件表达式];
create table exam(
	stu_no int auto_increment primary key,
    exam_score tinyint unsigned,
    regular_score tinyint unsigned
);
# 测试数据
insert into exam values
(null,80,85),(null,99,90),
(null,65,70),(null,52,null),
(null,20,null);
# 更新
# 没有条件的更新 全部字段都要更新
update exam set exam_score=exam_score+5;
# 有条件的更新
update exam set exam_score=100 
where exam_score>100;
update exam set exam_score=60 
where exam_score>=55 and exam_score<60;
# 更改多个字段
update exam set exam_score=60,regular_score=60 
where stu_no>3;

表记录的删除

删除delete

语法：
delete from 表名 [where 条件表达式];
# 还是上面的exam表进行测试
delete from exam where exam_score<70;
# 删除表中的所有数据
delete from exam;

截断truncate

# truncate删除
truncate table table_name;
# 截断表，功能上相当于没有where子句的delete语句。
truncate table exam;

删除和截断的不同1
# 使用delete删除后，她的自增长不会变成从1开始的，
# 而是继续原来的进行自增长。
# 使用截断truncate则不一样，他会清空，
# 自增长从1开始进行自增长的。

删除和截断的不同2
# truncate使用更加的严格的，不能删除主表，
# 即使从表中是没有数据的。
# 对于从表没有数据，delete是可以删除主表的，
# 但是truncate是不可以的。

注意：截断、删除、更新、插入等等都要考虑约束的。