编程语言：SQL-结构化查询语言
- DDL - 数据定义语言：create / drop / alter
- DML - 数据操作语言：insert / delete / update / select
- DCL - 数据控制语言：grant / revoke
创建学生表（tb_student）
#如果存在名为school的数据为就删除它 drop database if exists school; #创建名为school的数据库并指定默认的字符集为utf-8 create database school default charset utf8; #切换到school数据为上下文环境 use school; #创建学生表 create table tb_student ( stuid int not null comment '学号', stuname varchar(20) not null comment '姓名', stusex bit default 1 comment '性别', stubirth date comment '生日', primary key(stuid) #外键 foreign key (collid) references tb_college (collid) ); #修改学生表 alter table tb_student add column stuaddr varchar(255); alter table tb_student change column stuaddr stuaddr varchar(511); alter table tb_student drop column stuaddr; #向学生表插入数据 insert into tb_student values(1001,'张三',1,'1982-1-2','广东广州'); insert into tb_student(stuid,stuname) values(1002,'李四'); insert into tb_student(stuid,stuname,stusex) values(1003,'张博',default),(1004,'丁瑶瑶',1),(1005,'徐秋月',0); #删除学生 delete from tb_student where stuid=1001; delete from tb_student where stusex=0; #更新操作 update tb_student set stuaddr='广东广州' where stuid in (1002,1003); update tb_student set stuaddr='广东广州' where stuid between 1002 and 1003; update tb_student set stubirth='1991-10-2',stuaddr='北京' where stuname='张博'; #查询所有学生 select * from tb_student #查询学生性别 select stuname as 姓名, case stusex when 1 then '男' else '女' end as 性别 from tb_student #查询女生姓名出生日期 select stuname,stubirth from tb_student where stusex=0 #查询限定日期 select * from tb_student where stubirth>='2020-1-1' and stubirth<='2022-3-1' select * from tb_student where stubirth between '2020-1-1' and '2022-3-1' #模糊查询(姓李) select stuname,stusex from tb_student where stuname like '李%' #模糊查询(姓李，两个字) select stuname,stusex from tb_student where stuname like '李_' #模糊查询(姓李，三个字) select stuname,stusex from tb_student where stuname like '李__' #模糊查询(姓名有李的/张的) select stuname,stusex from tb_student where stuname like '%李%' or stuname like '%张%' #查询没有录入地址的学生信息 select * from tb_student where stuaddr is null; #查询已经录入地址的学生信息 select * from tb_student where stuaddr is not null; #查询学生的家庭住址（去重） select distinct stuaddr from tb_student where stuaddr is not null #查询男学生姓名和生日（从大到小排序） select stuname,stubirth from tb_student where stusex=1 order by stubirth #查询男学生姓名和生日（从小到大排序） select stuname,stubirth from tb_student where stusex=1 order by stubirth desc #查询年龄（从小到大排序） select stuname,year(now()-year(stubirth)) as 年龄 from tb_student where stusex=1 order by stubirth desc #查询年龄最大/最小的学生出生日期 select min(stubirth) from tb_student select max(stubirth) from tb_student #查询人数 select count(stuid) from tb_student #查询男女生的人数 select stusex,count(stusex) from tb_student group by stusex #查询男女生年龄最大的日期 select stusex,min(stubirth) from tb_student group by stusex
创建学院表（tb_college）
#创建学院表 create table tb_college ( colid int auto_increment comment '编号', colname varchar(31) not null comment '名称', website varchar(1023) comment '网址', primary key(colid) ); #插入学院数据 insert into tb_college (colname) values ('计算机学院'),('外国语言学院'),('经济管理学院'); #添加学生表学院列 alter table tb_student add column colid int; #修改学生表添加外键约束 alter table tb_student add constraint fk_student_colid foreign key(colid) references tb_college(colid); #添加学生表学院列数据 update tb_student set colid=1 where stuid between 1001 and 1002; update tb_student set colid=2 where stuid in(1003,1004); update tb_student set colid=3 where stuid=1005;
创建老师表（tb_teacher）
#创建教师表 create table tb_teacher ( teaid int not null comment '工号', teaname varchar(20) not null comment '姓名', teatitle varchar(10) default '助教' comment '职称', collid int not null comment '所属学院', primary key(teaid), foreign key(collid) references tb_college(collid) );
创建课程表（tb_course）
#创建课程表 create table tb_course ( couid int not null comment '编号', couname varchar(50) not null comment '名称', coucredit int not null comment '学分', teaid int not null comment '授课老师', primary key (couid), foreign key (teaid) references tb_teacher(teaid) ); #插入课程表数据 insert into tb_course values (1,'数据库',2,1), (2,'数学',4,2), (3,'信号与信息处理',3,3), (4,'操作系统',2,4), (5,'数据结构',1,5), (6,'通信与信息系统',1,6); #查询所有课程名称及学分（投影和别名） select couname,coucredit from tb_course select couname as 课程名称,coucredit as 学分 from tb_course
创建选课记录表（tb_score）
#创建选课记录表 create table tb_score ( scid int auto_increment comment '选课记录编号', stuid int not null comment '选课学生', couid int not null comment '所选课程', scdate datetime comment '选课时间日期', scmark decimal(4,1) comment '考试成绩', primary key(scid), foreign key(stuid) references tb_student(stuid), foreign key(couid) references tb_course(couid) ); #插入选课数据信息 insert into tb_score values (101,1001,1,now(),54), (102,1002,2,now(),93), (103,1003,3,now(),21), (104,1004,4,now(),43), (105,1005,5,now(),54); #添加唯一性约束（一个学生选课程只能选一次） alter table tb_score add constraint uni_score_stuid_couid unique(stuid,couid); #查询学生选课的所有日期（去重） select distinct scdate from tb_score
