【MySQL练习案例】

话不多说，直接上代码：

代码展示：

—创建数据库—

create database lixiaowei;

—显示数据库—

show databases;

—打开数据库—

use lixiaowei;

create database schoolDB;

use schoolDB;

-----2

create table stu(
	学号 char(10) not null primary key,
	姓名 char(10) not null,
	性别 char(2) not null,
	出生日期 date,
	地区 varchar(20),
	民族 char(10),
	班级编号 char(6)
);

create table cou(
	课程号 char(6) not null primary key,
	课程名 varchar(20) not null,
	学分 int not null,
	学时 int not null,
	学期 char(2),
	前置课 char(6)
);

create table sco(
	学号 char(10) not null,
	课程号 char(6) not null,
	成绩 float(5,2),
	primary key(学号,课程号)
);

create table cla(
	班级编号 char(6) not null primary key,
	班级名称 varchar(20) not null,
	院系 varchar(30) not null,
	年级 int,
	人数 int
);

-------3----

alter table stu
	add foreign key(班级编号) references cla(班级编号);

alter table cou
	add foreign key (前置课) references cou(课程号);

alter table sco
	add foreign key(学号) references stu(学号),
	add foreign key(课程号) references cou(课程号);

------------4-----------

alter table sco
add check(0<=成绩<=100);

alter table stu
add check (性别 in (`男`,`女`));

----------5------------

–cla–

insert into cla values('AC1301','会计13-1班','会计学院',2022,35);

insert into cla values('AC1302','会计13-2班','会计学院',2022,36);

insert into cla values('CS2201','计算机1班','计算机学院',2022,38);

insert into cla values('IS2101','信息系统1班','信息学院',2021,null);

insert into cla values('IS2201','信息系统2班','信息学院',2022,30);

insert into cla values('DS2201','计算机应用技术1班','电商学院',2022,39);

insert into cla values('DS2204','计算机应用技术4班','电商学院',2022,33);

–cou–

insert into cou values(11003,'管理学',2,32,2,null);

insert into cou values(11005,'会计学',3,48,2,null);

insert into cou values(21001,'计算机基础',3,48,1,null);

insert into cou values(21002,'Office高级应用',3,48,2,21001);

insert into cou values(21004,'程序设计',4,64,2,21001);

insert into cou values(21005,'数据库',4,64,4,21004);

insert into cou values(21006,'操作系统',4,64,5,21001);

insert into cou values(31001,'管理信息系统',3,48,3,21004);

insert into cou values(31002,'信息系统_分析与设计',2,32,4,31001);

insert into cou values(31005,'项目管理',3,48,5,31001);

insert into cou values(41001,'体育',2,24,2,null);

insert into cou values(41002,'省情',2,12,1,null);

insert into cou values(41005,'计算机英语',3,24,2,null);

–stu–

insert into stu values(2022110101,'陈薇','女','1999-8-23','云南','汉','AC1301');

insert into stu values(2022110102,'李天威','男','2001-06-08','贵阳','汉','AC1301');

insert into stu values(2022110103,'顺子','男','2000-5-02','六盘水','汉','AC1301');

insert into stu values(2022110201,'杨婷敏','女','2000-1-23','南明区','汉','AC1302');

insert into stu values(2022110202,'陈永','男','2000-5-02','贵阳','汉','AC1302');

insert into stu values(2022310101,'明橘','女','2000-2-5','贵阳','汉','IS2101');

insert into stu values(2022310103,'周汕','女','2000-3-8','毕节','汉','IS2101');

insert into stu values(2022210101,'董勇','男','2001-9-8','哈尔滨','汉','CS2201');

insert into stu values(2022210102,'小粘','女','1999-5-18','广东','汉','CS2201');

insert into stu values(2022410101,'骆超','男','2000-1-23','贵阳','汉','IS2201');

insert into stu values(2022410102,'刘银','男','1999-6-6','贵阳','汉','IS2201');

insert into stu values(2022110104,'周韦','女','2000-05-23','上海','汉','AC1301');

insert into stu values(2022410103,'韦月华','女','2000-10-28','毕节','汉','IS2201');

—sco—

insert into sco values(2022110101,11003,92);

insert into sco values(2022110101,21001,95);

insert into sco values(2022110102,11003,92);

insert into sco values(2022110101,31001,94);

insert into sco values(2022110103,11003,99);

insert into sco values(2022110103,21001,90);

insert into sco values(2022110104,11003,99);

insert into sco values(2022110104,21001,91);

insert into sco values(2022110201,11003,92);

insert into sco values(2022110201,21004,88);

insert into sco values(2022110202,21001,91);

insert into sco values(2022110202,31002,92);

insert into sco values(2022210101,21002,88);

insert into sco values(2022210101,21002,86);

insert into sco values(2022210102,31002,90);

insert into sco values(2022210102,21002,88);

insert into sco values(2022310101,41005,88);

insert into sco values(2022310103,41001,90);

insert into sco values(2022410101,21004,88);

insert into sco values(2022410102,31002,86);

insert into sco values(2022410102,31005,82);

insert into sco values(2022410101,21005,90);

insert into sco values(2022110101,41001,99);

—练习—

insert into stu values(502001,'赵轮椅','男','1999-08-06','毕节','汉','IS2101');

insert into stu (学号,姓名,性别)
values(500102,'姜悦','女');

update stu set 地区=concat(地区,'省或市');

update stu
set 出生日期='1999-2-26',班级编号='AC1302'
where 姓名='刘音';

delete from stu
where 出生日期<'1997-01-01';

select * from stu where 姓名 like '周%';

select * from stu where 姓名 like '李__';

select 课程名,学分 from cou where 学时>=36;

select 课程名,学期 from cou where 前置课<=>null;

上一章链接：MySQL案例

每日一言：

这个世界上肯定有另一个我，坐着我不敢做的事，过着我想过的生活。

持续更新中…

[^1]本文章仅供参考学习，若有不足，还请在评论区或私信指正。