4月4MYSQL日常练习

drop database if exists xsxk;
create database if not exists xsxk;
use xsxk;
show databases;
-- 4请根据以下需要存储的数据，设计各表结构即表中各列定义
-- 5.1学生表设学号为主键，要求学生姓名不能为空
-- 5.2教师表设工号为主键，设置职称列默认值为“讲师”
-- 5.3为教师表教师姓名添加非空约束
-- 5.4创建课程表，无需设置主键和外键
-- 5.5为课程表在第三列后添加一列remark，设置为自动增长
-- 5.6为课程表设置教师表的外键授课教师工号
-- 5.7创建选课表，建立联合主键为课程号和学号，并设置外键。
drop table if exists student; -- 5.1学生表设学号为主键，要求学生姓名不能为空
create table student(
stuid char(10) not null primary key comment'学号',
stuname char(10) not null comment'姓名',
stusex char(2) not null comment'性别',
studate date comment'出生日期',
address varchar(20) comment'专业'
);
SELECT*FROM student;
INSERT INTO student VALUES('10101001','张永峰','男','1993-08-01 00:00:00','电子商务101');
INSERT INTO student VALUES('10101002','何小丽','女','1992-11-03 00:00:00','电子商务101');
INSERT INTO student VALUES('10101003','张宇','男','1992-08-21 00:00:00','电子商务101');
INSERT INTO student VALUES('10102001','王斌','男','1991-07-14 00:00:00','网络技术101');
INSERT INTO student VALUES('10102002','包玉明','女','1993-11-15 00:00:00','网络技术101');
INSERT INTO student VALUES('10102003','孙平平','女','1992-02-27 00:00:00','网络技术101');
INSERT INTO student VALUES('10102004','翁静静','女','1992-05-09 00:00:00','网络技术101');
INSERT INTO student VALUES('11101001','刘淑芳','女','1994-06-10 00:00:00','电子商务111');
INSERT INTO student VALUES('11101002','王亚旭','男','1993-03-18 00:00:00','电子商务111');
INSERT INTO student VALUES('11101003','高磊','男','1993-05-11 00:00:00','电子商务111');

drop table if exists teacher;
create table teacher(
teaid char(4) not null primary key comment'教师编号',
teaname varchar(20) not null comment'教师姓名',
teasex char(2) not null comment'性别',
tearank varchar(10) not null comment'学历',
teatitle varchar(20) not null comment'职称'
);
-- 5.2教师表设工号为主键，设置职称列默认值为“讲师”
ALTER TABLE teacher ALTER COLUMN teatitle SET DEFAULT'讲师';
-- 5.3为教师表教师姓名添加非空约束
ALTER TABLE teacher MODIFY teaname varchar(20) not null;
SELECT*FROM teacher;
INSERT INTO teacher VALUES('t001','吴亚飞','男','本科','讲师');
INSERT INTO teacher VALUES('t002','李琦','男','硕士研究生','副教授');
INSERT INTO teacher VALUES('t003','王艳红','女','硕士研究生','讲师');
INSERT INTO teacher VALUES('t004','马志超','男','博士研究生','教授');
INSERT INTO teacher VALUES('t005','万丽','女','硕士研究生','助理讲师');

drop table if exists lesson;
create table lesson(
lesid char(4) not null comment'课程编号',
lesname varchar(10) not null comment'课名',
lesnumber char(2) not null comment'课程数量',
lesmark float not null comment'学分',
teaid char(4) not null comment'职称'
);
ALTER TABLE lesson add remark INT PRIMARY KEY AUTO_INCREMENT;
-- 5.6为课程表设置教师表的外键授课教师工号
ALTER TABLE lesson ADD FOREIGN KEY (teaid) references teacher(teaid);
-- 5.5为课程表在第三列后添加一列remark，设置为自动增长
SELECT*FROM lesson;
INSERT INTO lesson VALUES('c001','文学欣赏','40','1.5','t001',null);
INSERT INTO lesson VALUES('c002','中国历史文化','60','1.5','t002',null);
INSERT INTO lesson VALUES('c003','视频编辑','70','1.5','t003',null);
INSERT INTO lesson VALUES('c004','音乐欣赏','40','1.5','t004',null);

drop table if exists lessoninformation;
create table lessoninformation(
stuid char(8) not null comment'学生信息',
lesid char(4) not null comment'课程编号',
innumber varchar(10) not null comment'选课人数'
);
-- 5.7创建选课表，建立联合主键为课程号和学号，并设置外键
ALTER TABLE lessoninformation
ADD FOREIGN KEY (stuid) references student(stuid);
SELECT*FROM lessoninformation;
INSERT INTO lessoninformation VALUES('10101001','c001','73');
INSERT INTO lessoninformation VALUES('10101001','c003','81');
INSERT INTO lessoninformation VALUES('10101001','c004','51');
INSERT INTO lessoninformation VALUES('10101002','c001','78');
INSERT INTO lessoninformation VALUES('10101003','c003','69');
INSERT INTO lessoninformation VALUES('10102001','c001','50');
INSERT INTO lessoninformation VALUES('10102002','c002','68');
INSERT INTO lessoninformation VALUES('10102003','c003','95');
INSERT INTO lessoninformation VALUES('10102002','c004','75');
INSERT INTO lessoninformation VALUES('10102002','c001','85');
INSERT INTO lessoninformation VALUES('10102003','c002','78');
INSERT INTO lessoninformation VALUES('10102003','c003','67');
INSERT INTO lessoninformation VALUES('10102003','c004','null');
INSERT INTO lessoninformation VALUES('10102003','c001','49');
INSERT INTO lessoninformation VALUES('10101001','c002','67');
INSERT INTO lessoninformation VALUES('10101001','c004','62');
INSERT INTO lessoninformation VALUES('10101002','c001','67');
INSERT INTO lessoninformation VALUES('10101002','c002','null');
INSERT INTO lessoninformation VALUES('10101003','c002','88');
INSERT INTO lessoninformation VALUES('10101003','c003','90');
INSERT INTO lessoninformation VALUES('10101003','c004','82');