mysql建表、插入、修改、复制、删除操作

#创建学生信息表：学生信息：学号，姓名，班级，性，年龄，电话，身份证号，家庭住址，微信号，邮箱，院系

create table t_student_info(
stuno int(10) primary key auto_increment comment '学号-主键',
sname varchar(60) comment '姓名',
clazz varchar(20) comment '班级',
sex char(2) default '男' comment '性别',
age int(3) comment '年龄',
phone varchar(16) comment '电话',
idcard varchar(18) comment '身份证号',
address varchar(255) comment '家庭住址',
wechat varchar(30) comment '微信号',
email varchar(20) comment '邮箱',
department varchar(10) comment '院系'
);

# 插入数据

insert into t_student_info
values(1,20240101, '张三', 'yun01', '女', 18, '13767032897','36220119910214687x','湖南长沙','wx001','610382988@qq.com', 'lgn');

insert into t_student_info(sutno, sname, clazz,age, phone,idcard,address,wechat,email,department)
values(20240102, '李四', 'yun01', 19, '13767032898','36220119910214688x','湖南长沙','wx002','610382989@qq.com', 'lgn');

# 复制一张表结构(如果有数据会将数据在创建表的时候复制进去)

create table t_student_info_tmp  select * from t_student_info;

# 复制表数据

insert into t_student_info_tmp select * from t_student_info;

#修改字段

#删除字段

alter table t_student_info drop wechat;

#添加字段

alter table t_student_info add wechat varchar(20) not null comment '微信号';

#修改字段名字

alter table t_student_info change sutno stuno int(10) not null comment '学号';

#修改字段类型

alter table t_student_info modify age int(4) comment '年龄',

#添加约束(not null除外)

alter table t_student_info add constraint unique_con unique(stuno);

#删除约束(删除唯一约束的时候需要用index来标注删除)

alter table t_student_info drop index unique_con;

#修改表名

rename table t_student_info to t_student;

#对学生表中姓名字段添加索引

create index sname_index on t_student_info(sname);

#创建所有的表

create table t_course(
	id int primary key auto_increment,
	course_no varchar(20) not null unique comment '课程号',
	cname varchar(50) not null comment '课程名称',
	create_time DATETIME not null comment '课程创建时间',
	department varchar(50) not null comment '院系'
);
insert into t_course
values
(1, 'python-01', 'python基础', now(), 'lgn'),
(2, 'python-02', 'python科学计算', now(), 'lgn'),
(3, 'java-01', 'Java基础', now(), 'lgn'),
(4, 'java-02', 'JavaWEB', now(), 'lgn'),
(5, 'C-01', 'C语言基础', now(), 'lgn'),
(6, 'C-02', '数据结构', now(), 'lgn'),
(7, 'Music-01', '声乐',now(),'wyf'),
(8, 'Music-02', '钢琴',now(),'wyf'),
(9, 'Music-03', '小提琴',now(),'wyf'),
(10,'Music-04', '二胡',now(),'wyf');
create table t_teacher(
	id int primary key auto_increment,
	teacher_no varchar(20) not null unique comment '教师号',
	tname varchar(50) not null comment '姓名',
	gender varchar(10) default '男' not null comment '性别',
	phone varchar(20) not null comment '电话 ',
	id_card varchar(20) not null comment '身份证号',
	department varchar(50) not null comment '院系'
);
insert into t_teacher
values
(1, 'T001', '张老师','男', '13767032798','362201199102144657','lgn'),
(2, 'T002', '文老师','女', '13767032797','362201199102144656','lgn'),
(3, 'T003', '郭老师','男', '13767032796','362201199102144655','lgn'),
(4, 'T004', '陈老师','女', '13767032795','362201199102144654','wyf'),
(5, 'T005', '任老师','男', '13767032794','362201199102144653','wyf'),
(6, 'T006', '丁老师','女', '13767032792','362201199102144652','wyf');

create table t_department(
	id int primary key auto_increment,
	dept_no varchar(20) not null unique comment '院系号',
	dname varchar(50) not null comment '名称',
	introduction varchar(255) comment '简介',
	create_time DATETIME not null comment '创建时间'
);
insert into t_department 
values
(1, 'lgn', '理工农', '这是理工农学院', now()),
(2, 'wyf', '文艺法', '这是文艺法学院', now());

create table t_score(
	id int primary key auto_increment,
	stuno int(10) not null comment '学号',
	course_no varchar(20) not null comment '课程号',
	score int(3) not null comment '成绩',
	foreign key (stuno) references t_student_info(stuno),
	foreign key (course_no) references t_course(course_no)
);
insert into t_score
values
(1, 20240101, 'java-01', 90),
(2, 10001, 'java-01', 88),
(3, 20240101, 'python-01', 80),
(4, 10001, 'python-01', 89),
(5, 20240101, 'Music-01', 90),
(6, 10001, 'Music-01', 88);

create table t_teaching(
	id int primary key auto_increment,
	course_no varchar(20) not null comment '课程号',
	teacher_no varchar(20) not null comment '教师号',
	classroom varchar(50) comment '教师',
	foreign key (course_no) references t_course(course_no),
	foreign key (teacher_no) references t_teacher(teacher_no)
);