新建数据库练习作业（1）

新建数据库练习作业（1）

-- 创建数据库school 
create database if not exists school default charset utf8mb4;

-- 创建学生表、老师表、学院表、课程表 （每张表的字段不低于4个）
use school;
create table if not exists `school`.`t_student`
(
`stu_id` int unsigned not null comment '学生的学号',
`stu_name` varchar(4) not null comment '姓名',
`stu_gender` boolean default 1 comment '性别',
`stu_tel` varchar(11) comment '手机号码',
`stu_NP` varchar(5) comment '籍贯',
primary key(stu_id)
)engine=INNODB;
alter table `school`.`t_student` change `stu_gender` `stu_gender1` char(1) default '男' COMMENT '性别';

create table if not exists `school`.`t_teacher`
(
`tea_id` int unsigned not null comment '老师的学号',
`tea_name` varchar(4) not null comment '姓名',
`tea_title` varchar(5) comment '职称',
`tea_tel` varchar(11) comment '手机号码',
primary key(tea_id)
)engine=INNODB;

create table if not exists `school`.`u_college`
(
`col_id` int unsigned not null comment '学院编号',
`col_name` varchar(3) not null comment '院名',
`col_num` int unsigned not null comment '人数',
`col_dean` varchar(4) not null comment '院长姓名',
primary key(col_id)
)engine=INNODB;

create table if not exists `school`.`u_course`
(
`cou_id` int unsigned not null comment '课程编号',
`cou_name` varchar(3) not null comment '课程名',
`cou_num` int unsigned not null comment '人数',
`cou_teacher` varchar(4) not null comment '任课老师',
primary key(cou_id)
)engine=INNODB;

-- 给每张表插入数据学生表-至少15条数据、老师表、学院表、课程表-至少5个 
insert into school.t_student(stu_id, stu_name, stu_gender1, stu_tel,stu_NP) values 
(201601, '娜美', '女', '12345678901', '广东深圳'),
(201602, '山治', '男', '12347907342', '辽宁大连'),
(201603, '乌索普', '男', '13065789423', '辽宁盘锦'),
(201604, '海伦', '女', '13645787568', '广东福建'),
(201605, '汤姆', '男', '12345634563', '新疆库尔勒'),
(201606, '杰克', '男', '14589054679', '云南大理'),
(201607, '婕妤', '女', '13145678902', '四川成都'),
(201608, '秦三', '男', '13245789601', '四川绵阳'),
(201609, '顾寒烟', '女', '18576409872', '陕西宝鸡'),
(201610, '张诗怡梅', '女', '18867453201', '甘肃兰州'),
(201611, '李四', '男', '17738724971', '河北石家庄'),
(201612, '王五', '男', '19945368702', '天津'),
(201613, '白展堂', '男', '18597342568', '北京'),
(201614, '灰原哀', '女', '13756482390', '陕西西安'),
(201615, '柯南', '男', '18087024568', '四川德阳');

insert into school.t_teacher(tea_id, tea_name, tea_title, tea_tel) values 
(201001, '娜美', '讲师', '13456789023'),
(200723, '罗强', '副教授', '15367895462'),
(201302, '蔡徐坤', '教授', '18597342567'),
(201406, '乌索普', '副教授', '17738745679'),
(201529, '徐英', '教授', '17890563423'),
(201113, '张强', '讲师', '13467895604');

insert into school.u_college(col_id, col_name, col_num, col_dean) values 
(201001, '理学院', 234, '彭书记'),
(200723, '计科院', 1256, '李渊'),
(201302, '经管院', 367, '吴悦'),
(201406, '石工院', 3478, '李萌'),
(201529, '化工院', 1569, '华源'),
(201113, '地科院', 4365, '王丽');insert into school.u_course(cou_id, cou_name, cou_num, cou_teacher) values 
(3201001, '高数', 15467, '彭书记'),
(5200723, '英语', 1256, '李渊'),
(8201302, '数据库', 367, '吴悦'),
(0201406, '线代', 3478, '李萌'),
(3201529, '大物', 1569, '华源'),
(7201113, '通信', 4365, '王丽');

结果：

图2：

图3：

图4：