show databases;
create database dxj;
use dxj;
/*创建student表*/
create table if not exists student
(
studentno char(11) not null comment'学号',
sname char(8) not null comment'姓名',
sex enum('男','女') default '男' comment'性别',
birthdate date not null comment'出生日期',
entrance int(3) not null comment'入学成绩',
phone varchar(12) not null comment'电话',
Email varchar(20) not null comment'电子信箱',
primary key(studentno)
);
describe student;
/*创建student表完成*/
/*向student表插入数据*/
insert into student values
('18122221324','何白露','女','2000-12-04',879,'13178978999','heyy@sina.com '),
('18125111109','敬横江','男','2000-03-01',789,'15678945623','jing@sina.com '),
('18125121107','梁一苇','女','1999-09-03',777,'13145678921','bing@126.com '),
('18135222201','凌浩风','女','2001-10-06',867,'15978945645','tang@163.com '),
('18137221508','赵临江','男','2000-02-13',789,'12367823453','ping@163.com '),
('19111133071','崔依歌','女','2001-06-06',787,'15556845645','cui@126.com '),
('19112100072','宿沧海','男','2002-02-04',658,'12545678998','su12@163.com'),
('19112111208','韩山川','男','2001-02-14',666,'15878945612','han@163.com '),
('19122203567','封月明','女','2002-09-09',898,'13245674564','jiao@126.com'),
('19123567897','赵既白','女','2002-08-04',999,'13175689345','pingan@163.com'),
('19126113307','梅惟江','女','2003-09-07',787,'13245678543','zhu@163.com');
select *from student;
/*向student表插入数据完成*/
/*创建课程信息表course*/
create table if not exists course
(
courseno char(6) not null,
cname char(6) not null,
type char(8) not null,
period int(2) not null,
exp int(2) not null,
term int(2) not null,
primary key(courseno)
);
describe course;
/*建立课程信息表course完成*/
/*向course表插入数据*/
insert into course values
('c05103','电子技术','必修',64,16,2),
('c05109','C语言','必修',48,16,2),
('c05127','数据结构','必修',64,16,2),
('c05138','软件工程','选修',48,8,5),
('c06108','机械制图','必修',60,8,2),
('c06127','机械设计','必修',64,8,3),
('c06172','铸造工艺','选修',42,16,6),
('c08106','经济法','必修',48,0,7),
('c08123','金融学','必修',40,0,5),
('c08171','会计软件','选修',32,8,8);
select *from course;
/*向course表插入数据完成*/
/*建立学生分数表score*/
create table if not exists score
(
studentno char(11) not null,
courseno char(6) not null,
daily float(3,1) default 0,
final float(3,1) default 0,
primary key(studentno,courseno)
);
/*建立学生分数表score完成*/
describe score;
/*向学生分数表score插入数据*/
insert into score value
('18122210009','c05103',87.0,82.0),
('18122210009','c05109',77.0,91.0),
('18122221324','c05103',88.0,62.0),
('18122221324','c05109',91.0,77.0),
('18125111109','c08106',79.0,99.0),
('18125111109','c08123',85.0,92.0),
('18125111109','c08171',77.0,92.0),
('18125121107','c05103',74.0,91.0),
('18125121107','c05109',89.0,62.0),
('18135222201','c05109',99.0,92.0),
('18135222201','c08171',95.0,82.0),
('18137221508','c08106',78.0,95.0),
('18137221508','c08123',78.0,89.0),
('18137221508','c08171',88.0,98.0),
('19111133071','c05103',82.0,69.0),
('19111133071','c05109',77.0,82.0),
('19112100072','c05109',87.0,86.0),
('19112100072','c06108',97.0,97.0),
('19112111208','c05109',85.0,91.0),
('19112111208','c06108',89.0,95.0),
('19122111208','c06127',78.0,67.0),
('19122203567','c05103',65.0,98.0),
('19122203567','c05108',88.0,89.0),
('19122203567','c06127',79.0,88.0),
('19123567897','c05103',85.0,77.0),
('19123567897','c06127',99.0,99.0),
('19126113307','c06108',66.0,82.0),
('19126113307','c08171',88.0,79.0);
select *from score;
/*向学生分数表score插入数据完成*/
/*建立教师信息表teacher*/
create table if not exists teacher
(
teacherno char(16) not null comment'教师编号',
tname char(8) not null comment'教师姓名',
major char(10) not null comment'专业',
prof char(10) not null comment'职称',
department char(16) not null comment'部门',
primary key(teacherno)
);
/*建立教师信息表teacher完成*/
describe teacher;
/*向教师信息表teacher插入数据*/
insert into teacher value
('t05001','苏超然','软件工程','教授','计算机学院'),
('t05002','常杉','会计学','助教','管理学院'),
('t05003','孙释安','网络安全','教授','计算机学院'),
('t05011','卢敖治','软件工程','副教授','计算机学院'),
('t05017','茅佳峰','软件测试','讲师','计算机学院'),
('t06011','夏南望','机械制造','教授','机械学院'),
('t06023','葛庭宇','铸造工艺','副教授','材料学院'),
('t07019','韩既乐','经济管理','讲师','管理学院'),
('t8017','时观','金融管理','副教授','管理学院');
select *from teacher;
/*向教师信息表teacher插入数据完成*/
/*建立纽带表teach_course*/
create table if not exists teach_course
(
teacherno char(6) not null,
courseno char(6) not null,
primary key(teacherno,courseno)
);
/*建立纽带表teach_course完成*/
/*向纽带表teach_course插入信息*/
insert into teach_course value
('t05001','c05109'),
('t05002','c05127'),
('t05003','c05127'),
('t05011','c05138'),
('t05017','c05127'),
('t06011','c06127'),
('t06023','c06172'),
('t07019','c08123'),
('t08017','c08106');
select *from teach_course;
/*向纽带表teach_course插入信息完成*/
/*创建选课表sc*/
create table sc
(
sc_no int(6) not null auto_increment,
studentno char(11) not null,
courseno char(6) not null,
teacherno char(6) not null,
sc_time timestamp not null default now(),
primary key(sc_no)
);
/*创建选课表sc完成*/
/*创建表sc_course*/
CREATE TABLE `se_course` (
`sc_no` int(0) NOT NULL AUTO_INCREMENT,
`studentno` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`courseno` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`teacherno` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`sc_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`sc_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
insert into se_course set studentno='19120000111',courseno='c01236',teacherno='t01237';
/*创建库mysqltset*/
create database mysqltest;
use mysqltest;
Create table example(today datetime, name char(20) );
desc example;
/*
create temporary table tmp_emp1 (name varchar(10) not null, value integer not null );*/
/*
show tables;
alter table student add address varchar(30) not null after Email;
select *from student;
alter table course modify type enum('必修','选修') default '必修';
desc course;
alter table student drop address;
*/
/*用已有表建一个相同表
create table student01 as select * from student;
/*添加字段并且插入数据
alter table student01 add fields mediumblob ;
select * from student01;
insert into student01 values('18122221329','代学俊','男','2001/12/9','877','13178978997', 'heyy1@sina.com ', '云�
