mysql基础(二)补充

-- 学生选课系统
-- 1. 创建学生选课系统
create database srs default charset utf8;
-- 2. 切换数据库
use srs;
-- 3. 创建学生表TbStudent
create table tbstudent (
-- 主键stuid，姓名stuname，
    stuid int not null,
    stuname varchar(20) not null,
-- 性别stusex，生日 stubirth,
    stusex bit default 1,
    stubirth datetime not null,
-- 电话stutel，住址stuaddr
    stutel char(11),
    stuaddr varchar(255),
-- 照片stuphoto (以二进制存）
    stuphoto longblob,
    primary key(stuid)
)
-- 修改学生表删除stutel列
alter table TbStudent drop column stutel;
-- 查看学生表结构
desc TbStudent;
-- 如果表TbCourse已经存在就删除它
drop table if exists TbCourse;
-- 4. 创建课程表TbCourse
create table tbcourse (
-- 主键cosid， 班 级 名 称 cosname，
    cosid int not null,
    cosname varchar(50) not null,
-- 学分coscredit，课程描述cosintro
    coscredit tinyint not null,
    cosintro varchar(255) default null,
    primary key(cosid)
);
-- 5. 学生选课记录表TbSC
create table tbsc (
-- 主键 scid，学生外键 sid， 班级外键cid，
    scid int auto_increment not null,
    sid int not null,
    cid int default null,
-- 创建日期scdate，分数score
    scdate datetime not null,
    score float,    
    primary key(scid),
    foreign key (sid) references TbStudent (stuid),
    foreign key (cid) references TBCourse (cosid)
);

insert into TbStudent values (1001, '张三丰', default, '1978-1-1', '成都市一环路西二段17号', null);
insert into TbStudent (stuid, stuname, stubirth) values (1002, '郭靖', '1980-2-2');
insert into TbStudent (stuid, stuname, stusex, stubirth, stuaddr) values (1003, '黄蓉', 0, '1982-3-3', '成都市二环路南四段123号');
insert into TbStudent values (1004, '张无忌', 1, '1990-4-4', null, null);
insert into TbStudent values 
(1005, '丘处机', 1, '1983-5-5', '北京市海淀区宝盛北里西区28号', null),
(1006, '王处一', 1, '1985-6-6', '深圳市宝安区宝安大道5010号', null),
(1007, '刘处玄', 1, '1987-7-7', '郑州市金水区纬五路21号', null),
(1008, '孙不二', 0, '1989-8-8', '武汉市光谷大道61号', null),
(1009, '平一指', 1, '1992-9-9', '西安市雁塔区高新六路52号', null),
(1010, '老不死', 1, '1993-10-10', '广州市天河区元岗路310号', null),
(1011, '王大锤', 0, '1994-11-11', null, null),
(1012, '隔壁老王', 1, '1995-12-12', null, null),
(1013, '郭啸天', 1, '1977-10-25', null, null);

insert into TbCourse values 
(1111, 'C语言程序设计', 3, '大神级讲师授课需要抢座'),
(2222, 'Java程序设计', 3, null),
(3333, '数据库概论', 2, null),
(4444, '操作系统原理', 4, null);

insert into TbSC values 
(default, 1001, 1111, '2016-9-1', 95),
(default, 1002, 1111, '2016-9-1', 94),
(default, 1001, 2222, now(), null),
(default, 1001, 3333, '2017-3-1', 85),
(default, 1001, 4444, now(), null),
(default, 1002, 4444, now(), null),
(default, 1003, 2222, now(), null),
(default, 1003, 3333, now(), null),
(default, 1005, 2222, now(), null),
(default, 1006, 1111, now(), null),
(default, 1006, 2222, '2017-3-1', 80),
(default, 1006, 3333, now(), null),
(default, 1006, 4444, now(), null),
(default, 1007, 1111, '2016-9-1', null),
(default, 1007, 3333, now(), null),
(default, 1007, 4444, now(), null),
(default, 1008, 2222, now(), null),
(default, 1010, 1111, now(), null);

-- 查询所有学生信息
select * from tbstudent;

-- 查询所有课程名称及学分(投影和别名)
select * from tbcourse;
-- 查询所有女学生的姓名和出生日期(筛选)
select stuname as '女生名字', stubirth as '出生日期' from tbstudent where stusex=0;
-- 查询所有80后学生的姓名、性别和出生日期(筛选)
select stuname  as '80后学生名字', stusex as '性别', stubirth as '出生日期' from tbstudent where year(stubirth) between 1980 and 1989;
-- 查询姓王的学生姓名和性别(模糊)
select stuname as '学生名字', stusex as '性别' from tbstudent where stuname like '王%';
-- 查询姓郭名字总共两个字的学生的姓名(模糊)
select stuname as '学生名字' from tbstudent where stuname like '__';
-- 查询姓郭名字总共三个字的学生的姓名(模糊)
select stuname as '学生名字' from tbstudent where stuname like '郭__';
-- 查询名字中有王字的学生的姓名(模糊)
select stuname as '学生名字' from tbstudent where stuname like '%王%';
-- 查询没有录入家庭住址和照片的学生姓名(多条件筛选和空值处理)
select stuname as '学生名字' from tbstudent where stuaddr is null and stuphoto is null;
-- 查询学生选课的所有日期(去重)
select distinct scdate as '选课日期' from tbsc;
-- 查询学生的姓名和生日按年龄从大到小排列(排序)
select stuname as '学生名字', stubirth as '出生日期' from tbstudent order by stubirth;
-- 查询所有录入了家庭住址的男学生的姓名、出生日期和家庭住址按年龄从小到大排列(多条件筛选和排序)
select stuname as '学生名字', stubirth as '出生日期', stuaddr as '家庭住址' from tbstudent where stusex=1 and stuaddr is not null order by stubirth;
-- 查询年龄最大的学生的出生日期(聚合函数)
select min(stubirth) as '最大出生日期', stuname as '学生名字' from tbstudent;
-- 查询年龄最小的学生的出生日期(聚合函数)
select max(stubirth) as '最大出生日期', stuname as '学生名字' from tbstudent;
-- 查询男女学生的人数(分组和聚合函数)
select count(*) as '人数', stusex as '性别' from tbstudent group by stusex;
-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数)
select avg(ifnull(sc.score, 0)), sc.cid from (select cid as id, score as s from tbsc where cid=1111) t1 join tbsc sc on t1.id=sc.cid;
-- 查询学号为1001的学生所有课程的总成绩(筛选和聚合函数)
select sum(score) as '总分', cid as '课程编号' from tbsc where sid=1001;
-- 查询每个学生的学号和平均成绩, null值处理成0(分组和聚合函数)
select stu.stuid as '学生学号', stu.stuname as '学生名字',avg(ifnull(sc.score, 0)) as '平均成绩' from tbstudent stu left join tbsc sc on stu.stuid=sc.sid group by stu.stuid;
-- 查询平均成绩大于等于90分的学生的学号和平均成绩
select * from (select stu.stuid as stu_id, stu.stuname as stu_name,avg(ifnull(sc.score, 0)) as a from tbstudent stu left join tbsc sc on stu.stuid=sc.sid group by stu.stuid) t1 where t1.a>=90;
-- 查询年龄最大的学生的姓名(子查询)
select * from tbstudent where stubirth=(select min(stubirth) from tbstudent);
-- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
select * from (select count(t1.id) as num, stuname from (select stu.stuid as id, stu.stuname, sc.cid from tbstudent stu join tbsc sc on stu.stuid=sc.sid) t1 group by t1.id) t2 where num>2;
-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stu.stuid, stu.stuname, avg(ifnull(sc.score, 0)) from tbstudent stu join tbsc sc on stu.stuid=sc.sid group by stu.stuid;
-- 查询学生姓名、所选课程名称和成绩(连接查询)
select * from (select stu.stuname, sc.cid, sc.score from tbstudent stu join tbsc sc on stu.stuid=sc.sid) t2 join tbcourse course on t2.cid=course.cosid;
-- 查询每个学生的姓名和选课数量(左外连接和子查询)
select stu.stuname, ifnull(t1.num, 0) from tbstudent stu left join (select count(sid) as num, sid from tbsc group by sid) t1 on t1.sid=stu.stuid;

注意：在关系型数据库中，含有外键的那张表无法直接删除数据，必须进行连删，可以用下面的语句

alter table TbSC add constraint fk_sid foreign key (sid) references TbStudent (stuid) on delete cascade on update cascade;
alter table TbSC add constraint fk_cid foreign key (cid) references TBCourse (cosid) on delete set null on update cascade;

如若还是不理解为什么会出现这种情况，可以自行百度，或者查看runoob菜鸟教程，注意：不是打广告不是打广告不是打广告，重要的是说三遍！