MySQL建學生表資料庫

-- 建立SRS資料庫
drop database if exists SRS;
create database SRS default charset utf8 collate utf8_bin;

-- 切換到SRS資料庫
use SRS;

-- 建立學院表
create table tb_college
(
collid int not null auto_increment comment '學院編號',
collname varchar(50) not null comment '學院名稱',
collmaster varchar(20) not null comment '院長姓名',
collweb varchar(511) default '' comment '學院網站',
primary key (collid)
);

-- 新增唯一約束
alter table tb_college add constraint uni_college_collname unique (collname);

-- 建立學生表
create table tb_student
(
stuid int not null comment '學號',
sname varchar(20) not null comment '學生姓名',
gender bit default 1 comment '性別',
birth date not null comment '出生日期',
addr varchar(255) default '' comment '籍貫',
collid int not null comment '所屬學院編號',
primary key (stuid)
);

-- 新增外來鍵約束
alter table tb_student add constraint fk_student_collid foreign key (collid) references tb_college (collid);

-- 建立教師表
create table tb_teacher
(
teaid int not null comment '教師工號',
tname varchar(20) not null comment '教師姓名',
title varchar(10) default '' comment '職稱',
collid int not null comment '所屬學院編號'
);

-- 新增主鍵約束
alter table tb_teacher add constraint pk_teacher primary key (teaid);

-- 新增外來鍵約束
alter table tb_teacher add constraint fk_teacher_collid foreign key (collid) references tb_college (collid);

-- 建立課程表
create table tb_course
(
couid int not null comment '課程編號',
cname varchar(50) not null comment '課程名稱',
credit tinyint not null comment '學分',
teaid int not null comment '教師工號',
primary key (couid)
);

-- 新增外來鍵約束
alter table tb_course add constraint fk_course_tid foreign key (teaid) references tb_teacher (teaid);

-- 建立學生選課表
create table tb_score
(
scid int not null auto_increment comment '選課編號',
sid int not null comment '學號',
cid int not null comment '課程編號',
seldate date comment '選課時間日期',
mark decimal(4,1) comment '考試成績',
primary key (scid)
);

-- 新增外來鍵約束
alter table tb_score add constraint fk_score_sid foreign key (sid) references tb_student (stuid);
alter table tb_score add constraint fk_score_cid foreign key (cid) references tb_course (couid);

插入資料

-- 插入學院資料
insert into tb_college (collname, collmaster, collweb) values 
('計算機學院', '左冷禪', 'http://www.abc.com'),
('外國語學院', '嶽不群', 'http://www.xyz.com'),
('經濟管理學院', '風清揚', 'http://www.foo.com');

-- 插入學生資料
insert into tb_student (stuid, sname, gender, birth, addr, collid) values
(1001, '楊逍', 1, '1990-3-4', '四川成都', 1),
(1002, '任我行', 1, '1992-2-2', '湖南長沙', 1),
(1033, '王語嫣', 0, '1989-12-3', '四川成都', 1),
(1572, '嶽不群', 1, '1993-7-19', '陝西咸陽', 1),
(1378, '紀嫣然', 0, '1995-8-12', '四川綿陽', 1),
(1954, '林平之', 1, '1994-9-20', '福建莆田', 1),
(2035, '東方不敗', 1, '1988-6-30', null, 2),
(3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
(3755, '項少龍', 1, '1993-1-25', null, 3),
(3923, '楊不悔', 0, '1985-4-17', '四川成都', 3);

-- 插入老師資料
insert into tb_teacher (teaid, tname, title, collid) values 
(1122, '張三丰', '教授', 1),
(1133, '宋遠橋', '副教授', 1),
(1144, '楊逍', '副教授', 1),
(2255, '範遙', '副教授', 2),
(3366, '韋一笑', '講師', 3);

-- 插入課程資料
insert into tb_course (couid, cname, credit, teaid) values 
(1111, 'Python程式設計', 3, 1122),
(2222, 'Web前端開發', 2, 1122),
(3333, '作業系統', 4, 1122),
(4444, '計算機網路', 2, 1133),
(5555, '編譯原理', 4, 1144),
(6666, '演算法和資料結構', 3, 1144),
(7777, '經貿法語', 3, 2255),
(8888, '成本會計', 2, 3366),
(9999, '審計學', 3, 3366);

-- 插入選課資料
insert into tb_score (sid, cid, seldate, mark) values 
(1001, 1111, '2017-09-01', 95),
(1001, 2222, '2017-09-01', 87.5),
(1001, 3333, '2017-09-01', 100),
(1001, 4444, '2018-09-03', null),
(1001, 6666, '2017-09-02', 100),
(1002, 1111, '2017-09-03', 65),
(1002, 5555, '2017-09-01', 42),
(1033, 1111, '2017-09-03', 92.5),
(1033, 4444, '2017-09-01', 78),
(1033, 5555, '2017-09-01', 82.5),
(1572, 1111, '2017-09-02', 78),
(1378, 1111, '2017-09-05', 82),
(1378, 7777, '2017-09-02', 65.5),
(2035, 7777, '2018-09-03', 88),
(2035, 9999, date(now()), null),
(3755, 1111, date(now()), null),
(3755, 8888, date(now()), null),
(3755, 9999, '2017-09-01', 92);

查詢

-- 查詢所以學生的資訊
select * from tb_student;
-- 查詢所有課程名稱及學分（投影和別名）
select cname as 課程名稱, credit as 學分 from tb_course;
-- 查詢所有女學生的姓名和出生日期(篩選)
select sname, birth from tb_student where gender=0;
-- 查詢所有80後學生的姓名、性別和出生日期(篩選)
select sname as 姓名, if(gender, '男','女') as 性別, birth as 出生
from tb_student
where birth between '1980-1-1' and '1989-12-31';
-- 其他資料庫統一的寫法 如果性別輸出是1 就是男 負責就是女 別名性別
select sname as 姓名, case gender when 1 then '男' else '女' end as 性別, birth as 出生
from tb_student
where birth between '1980-1-1' and '1989-12-31';
-- 查詢姓”楊“的學生姓名和性別(模糊)
select sname ,gender from tb_student where sname like '楊%';
-- 查詢姓”楊“名字兩個字的學生姓名和性別(模糊)
select sname ,gender from tb_student where sname like '楊_';
-- 查詢姓”楊“名字三個字的學生姓名和性別(模糊)
select sname ,gender from tb_student where sname like '楊__';
-- 查詢名字中有”不“字或“嫣”字的學生的姓名(模糊)
select sname ,gender from tb_student where sname like '%不%' or sname like '%嫣%';
-- 查詢沒有錄入家庭住址的學生姓名(空值)
select sname from tb_student where addr is null;
-- 查詢錄入了家庭住址的學生姓名(空值)
select sname from tb_student where addr is not null;
-- 查詢學生選課的所有日期(去重)
select distinct seldate from tb_score;
-- 查詢學生的家庭住址(去重)
select distinct addr from tb_student where addr is not null;
-- 查詢男學生的姓名和生日按年齡從大到小排列(排序)
SELECT sname, year(now())-year(birth) as 年齡 
from tb_student 
where gender=1 order by birth asc; 
-- max() / min() / sum() / avg() / count()-->數量
-- 查詢年齡最大的學生的出生日期(聚合函式)
select min(birth)
from tb_student;
-- 查詢年齡最小的學生的出生日期(聚合函式)
select max(birth)
from tb_student;
-- 查詢男女學生的人數按照人數從多到少排序(分組/聚合函式/排序)
select if(gender,'男學生','女學生') as 性別, count(stuid) as 人數
from tb_student group by gender order by 人數 desc;
-- 查詢課程編號為1111的課程的平均成績(篩選和聚合函式)
select avg(mark) from tb_score where cid=1111;
-- 統計有多少學生選來1111這門課
select count(cid) from tb_score where cid=1111;
-- 查詢學號為1001的學生所有課程的平均分(篩選和聚合函式)
select avg(mark) from tb_score where sid=1001;
-- 查詢每個學生的學號和平均成績(分組和聚合函式)
select sid as 學號, avg(mark) as 平均分 from tb_score group by sid;
-- 查詢平均成績大於等於90分的學生的學號和平均成績
select sid as 學號, avg(mark) as 平均分 from tb_score 
group by sid having 平均分>=90;
-- 子查詢 - 在一個查詢中又使用到了另外一個查詢的結果
-- 查詢年齡最大的學生的姓名(子查詢)
select sname from tb_student
where birth=(select min(birth) from tb_student);
-- 查詢年齡最大的學生姓名和年齡(子查詢+運算)
select sname as 姓名, year(now())-year(birth) as 年齡
from tb_student
where birth=(select min(birth) from tb_student);
-- 查詢選了兩門以上的課程的學生姓名(子查詢/分組條件/集合運算)
select sname as 姓名 from tb_student
where stuid in(
    select sid from tb_score group by sid having count(sid)>2
);
-- 查詢學生的姓名和所在學院的名稱(連線)
-- 連線查詢（聯結查詢/聯接查詢）
-- 如果查詢多表沒有任何限制條件那麼將產生迪卡爾集
-- 如果查詢多張表中有同名的列表那麼需要使用完全限度名 表.列名
select sname, collname 
from tb_student,tb_college
where tb_student.collid=tb_college.collid;

-- 內連線
select sname, collname from tb_student t1
inner join tb_college t2
on ti.collid=t2.collid;
-- 查詢學生姓名、課程名稱以及成績(連線)
select sname, cname, mark
from tb_student, tb_course, tb_score
where sid=stuid and cid=couid;

-- 內連線
select sname, cname, mark
from tb_student
inner join tb_score on sid=stuid
inner join tb_course on cid=couid;
-- 查詢選課學生的姓名和平均成績(子查詢和連線查詢)
select sname, avgmark from tb_student t1 inner join
(select sid, avg(mark) as avgmark from tb_score group by sid) t2
on stuid=sid;
-- 查詢每個學生的姓名和選課數量(左外連線和子查詢)
-- 左外連線
select sname, avgmark from tb_student t1 left join
(select sid, avg(mark) as avgmark from tb_score group by sid) t2
on stuid=sid;
-- 右外連線
select sname, avgmark from
(select sid, avg(mark) as avgmark from tb_score group by sid) t1
right outer join tb_student t2
on stuid=sid;
-- 全外連線MySQL不支援

-- 事務（transaction）:多個操作不可分割要麼全成功要麼全失敗
-- 事務的ACID特性
-- 原子性(Atomicity):事務中的操作不可分割
-- 一致性(Consistency):事務前後資料狀態要一致
-- 隔離性(Isolation):多個併發事務不能看到對方的中間狀態
-- 永續性(Duration):事務完成後事務的影響要反映在物理儲存上(事務完成後資料要持久化)

-- begin/start transaction;-->開啟事務
-- delete from tb_score; --> 刪除名為tb_score的表
-- select * from tb_score; --> 查詢tb_score的所有資訊
-- commit;-->提交事務
-- rollback;-->回滾事務