作為測試人員最常用的資料庫查詢語句,本篇博文做了一個具體的總結,以下只是為了概述查詢方法而舉的例子,並不一定是最好的查詢方式。 紅色標記的地方是該查詢語句中重點想要表達的查詢方法。 一. 新建一個資料庫test: create database test; 二. 依次創建三個表:學生表Student ...
作為測試人員最常用的資料庫查詢語句,本篇博文做了一個具體的總結,以下只是為了概述查詢方法而舉的例子,並不一定是最好的查詢方式。
紅色標記的地方是該查詢語句中重點想要表達的查詢方法。
一. 新建一個資料庫test: create database test;
二. 依次創建三個表:學生表Student,課程表Course,學生選課表SC
CREATE TABLE Student
(
Sno char(9) PRIMARY KEY, #學號
Sname char(20) UNIQUE, #學生姓名
Ssex char(2), #學生性別
Sage SMALLINT, #學生年齡
Sdept char(20) #學生所在系
);
CREATE TABLE Course
(
Cno char(4) PRIMARY KEY, #課程號
Cname char(40) NOT NULL, #課程名稱
Cpno char(4), #先修課,學這門課之前要先修的課程
Ccredit SMALLINT, #學分
FOREIGN KEY (Cpno) REFERENCES Course(Cno) #外鍵
);
CREATE TABLE SC
(
Sno char(9) , #學號
Cno char(4) , #課程號
Grade SMALLINT, #成績
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Cno) REFERENCES Course(Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno)
);
三. 對錶結構進行增刪改(以Student表為例)
1.增加表欄位
alter table student add column S_entrance date;
2.刪除表欄位
alter table student drop column S_entrance;
3.修改欄位類型
alter table student modify column Sage INT;
4.修改欄位名字(需重新定義該欄位類型)
alter table student change Sage snew varchar(5) comment“修改的欄位”;
5.刪除外鍵
alter table course drop foreign key course_ibfk_1;(course_ibfk_1為自動生成的外鍵ID)
四. 對錶的值進行增刪查改(以Student表為例)
1.新增student值:
insert into student (Sno,Sname,Ssex,Sage,Sdept) values ("20190101","李亮","男",20,"CS");
insert into student (Sno,Sname,Ssex,Sage,Sdept) values ("20190102","李白","男",19,"MA");
insert into student (Sno,Sname,Ssex,Sage,Sdept) values ("20190103","劉晨","女",20,"IS");
當該表所有欄位都給了值的情況下,也可以省略欄位,預設給所有欄位插入值,eg:
insert into student values ("20190101","李亮","男",20,"CS");
2.新增Course表
由於建表的時候,我們將Cpno欄位作為Cno的外鍵了,所以在建表的時候,同時插入這兩個欄位的值,Cpno欄位會先判斷Cno是否有值,如果沒有就會報錯:a foreign key constraint fails 。
我們可以通過先刪除外鍵再添加外鍵的方式插入數據。
insert into course values ("1","數學","3",4);
insert into course values ("2","語文","5",3);
insert into course values ("3","英語","2",4);
3.新增SC表
INSERT INTO `sc` VALUES ('20190101', '2', 90);
INSERT INTO `sc` VALUES ('20190102', '2', 88);
INSERT INTO `sc` VALUES ('20190102', '3', 92);
INSERT INTO `sc` VALUES ('20190103', '1', 59);
4.刪除表的所有記錄(student表為例)
delete from student;
5.刪除學號為20190101的記錄
delete from student where Sno="20190101";
6.查詢表的所有記錄
select * from student;
7.修改劉晨學生的年齡
update student set Sage=19 where Sname="劉晨";
五. 細說資料庫查詢-單表查詢
1.查詢學生表的所有記錄
select * from student;
2.查詢學生表的所有學號
select Sno from student;
3.查詢出的是學生的出生年份
select 2019-Sage from student;
4.對查詢學號重命名
select sno as newname from student;
5.查詢所有課程號並去重
select distinct Cno from Sc;
6.查詢大於等於60分的記錄
select * from sc where grade>=60;
7.查詢60-70分之間的記錄
select * from sc where grade between 60 and 70;
8.查詢在MA和IS系的學生姓名和學號
select sname,sno from student where sdept in("MA","IS");
9.查詢姓劉的學生的詳細情況
select * from student where sname like "劉%";
10.查詢名字姓劉且姓名是兩個字的學生的詳細情況
select * from student where sname like "劉_";
11.查詢成績是空的學生的記錄
select * from sc where grade is null;
12.查詢成績不為空的學生的記錄
select * from sc where grade is not null;
13.查詢MA系或者IS系的且年齡在20歲以下的學生姓名
select sname from student where sname=20 and sdept="MA" or sdept="IS" ;
14.查詢出學生表的所有記錄並按照年齡的升序排序(降序用desc代替asc)
select * from student order by Sage asc;
15.統計學生個數
select count(*) from student ;
16.統計所有課程中最高分,平均分,最低分min,總分
select max(grade),avg(grade), min(grade),sum(grade) from sc ;
17.統計各個課程號及相應的選課人數
select cno,count(cno) from sc group by cno ;
六. 細說資料庫查詢——連接查詢(一個查詢涉及兩張表)
1.查詢每個學生及其選修課程的課程號(等值連接)
select student*,sc* from student ,sc where student.sno=sc.sno;
因為以上查詢結果中,sno和sno1是重覆的列,查詢的時候只查詢其中一個就可。這樣的查詢叫做自然連接。
2.查詢每個學生及其選修課程大於等於90分的課程號
select student*,sc* from student ,sc where student.sno=sc.sno and sc.grade>=90;
3.查找每門課的間接先修課
Course表的內容如下,可看出,課程1數學的先修課是課程3英語,英語的先修課是課程2語文。
也就是課程1的間接先修課是課程2語文。我們可以將course表自身連接查詢,也就是將表取兩個別名:first,second
查詢命令:
select FIRST.cno,SECOND.cpno from course first,course SECOND where `first`.cpno=`SECOND`.Cno ;
查詢後結果:
4.查詢每個學生的學生姓名,學號,選修課程名及成績(多表連接)
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno
and sc.cno=course.cno;
七. 細說資料庫查詢——嵌套查詢(一個查詢塊嵌套在另一個查詢塊的條件中)
1.查找所有跟“劉晨”同 一個系的學生
- 先查找出劉晨所在系:select sdept from student where sname="劉晨"
- 然後查找出在以上系的學生:select sno,sname from student where sdept=(select sdept from student where sname="劉晨")
查詢前:
查詢後:
2.找出每個學生超過他自己選修課程的平均成績的課程號
select sno,cno from sc x where grade >=(select avg(grade) from sc y where y.sno=x.sno);
3.查詢非IS系中比IS系中任意一個學生年齡都小的學生年齡和姓名
select sname,sage from student where sage<ANY(select sage from student where sdept="IS") and sdept<>'IS';
4.查詢所有選修了1號課程的學生姓名
select sname from student where exists(select * from sc where sno=student.sno and cno='1');
八. 細說資料庫查詢——集合查詢
1.查詢“IS”系的所有學生和年齡不大於19歲的學生的並集
select * from student where sdept='IS' union select * from student where sage<=19;
2.查詢“IS”系的所有學生和年齡不大於19歲的學生的交集
select * from student where sdept='IS' intersect select * from student where sage<=19;
3.查詢“IS”系的所有學生與年齡不大於19歲的學生的差集
select * from student where sdept='IS' except select * from student where sage<=19;
九. 細說資料庫查詢——基於派生表的查詢
子查詢出現在from子句中,生成的是臨時的派生表
1.查詢每個學生超過他自己選修課程的平均成績的課程號
select sc.sno,sc.cno from sc,(select sno,avg(Grade) as avg_grade from sc group by sno)as x
where x.sno=sc.sno and sc.grade>=x.avg_grade;