實驗4：MySQL數(shù)據(jù)庫表數(shù)據(jù)的查詢操作
一、實驗目的
1．掌握SELECT 語句的基本語法格式。
2．掌握SELECT 語句的執(zhí)行方法。
3．掌握SELECT 語句的?GROUP BY 和?ORDER BY 子句的作用。
二、實驗內(nèi)容
- 驗證性實驗：在公司的部門員工管理數(shù)據(jù)庫的bumen表和yuangong表上進行信息查詢。
- 設計性試驗：將在student表和score表上進行查詢。
三、實驗步驟與實驗結(jié)果
（一）驗證性實驗
在公司的部門員工管理數(shù)據(jù)庫的bumen表和yuangong表上進行信息查詢。Bumen表和yuangong表的定義如表所示。
表bumen表的定義
| 字段名 |
字段描述 |
數(shù)據(jù)類型 |
主鍵 |
外鍵 |
非空 |
唯一 |
自增 |
| d_id |
部門號 |
INT(4) |
是 |
否 |
是 |
是 |
否 |
| d_name |
部門名稱 |
VARCHAR(20) |
否 |
否 |
是 |
是 |
否 |
| function |
部門職能 |
VARCHAR(20) |
否 |
否 |
否 |
否 |
否 |
| address |
工作地點 |
VARCHAR(30) |
否 |
否 |
否 |
否 |
否 |
表yuangong表的定義
| 字段名 |
字段描述 |
數(shù)據(jù)類型 |
主鍵 |
外鍵 |
非空 |
唯一 |
自增 |
| id |
員工號 |
INT(4) |
是 |
否 |
是 |
是 |
否 |
| name |
姓名 |
VARCHAR(20) |
否 |
否 |
是 |
否 |
否 |
| sex |
性別 |
VARCHAR(4) |
否 |
否 |
是 |
否 |
否 |
| birthday |
年齡 |
INT(4) |
否 |
否 |
否 |
否 |
否 |
| d_id |
部門號 |
INT(4) |
否 |
是 |
是 |
否 |
否 |
| salary |
工資 |
Float |
否 |
否 |
否 |
否 |
否 |
| address |
家庭住址 |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
1.創(chuàng)建bumen表
CREATE TABLE bumen(
d_id INT(4) NOT NULL UNIQUE PRIMARY KEY,
d_name VARCHAR(20) NOT NULL UNIQUE,
function VARCHAR(20) ,
address VARCHAR(30)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; ?
2.向bumen表中插入記錄。
INSERT INTO bumen
VALUES
(1001,'人事部','人事管理','北京'),
(1002,'科研部','研發(fā)產(chǎn)品','北京'),
(1003,'生產(chǎn)部','產(chǎn)品生產(chǎn)','天津'),
(1004,'銷售部','產(chǎn)品銷售','上海');
3.操作后查看表
SELECT * FROM bumen;
4.創(chuàng)建yuangong表
CREATE TABLE yuangong(
id int(4) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday INT(4),
d_id INT(4) NOT NULL,
salary Float,
address VARCHAR(50),
FOREIGN KEY(d_id)REFERENCES bumen(d_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
5.向yuangong表中插入記錄
INSERT INTO yuangong
VALUES
(8001,'韓鵬','男',25,1002,4000,'北京市海淀區(qū)'),
(8002,'張峰','男',26,1001,2500,'北京市昌平區(qū)'),
(8003,'歐陽','男',20,1003,1500,'湖南省永州市'),
(8004,'王武','男',30,1001,3500,'北京市順義區(qū)'),
(8005,'歐陽寶貝','女',21,1002,3000,'北京市昌平區(qū)'),
(8006,'呼延','男',28,1003,1800,'天津市南開區(qū)');
6.操作后查看表
SELECT * FROM yuangong;
7.然后在bumen表和yuangong表查詢記錄。查詢的要求如下：
（1）查詢yuangong表的所有記錄。
SELECT * FROM yuangong;
或者列出yuangong表的所有字段名稱。
SELECT id,name,sex,birthday,d_id,salary,address FROM yuangong;
（2）查詢yuangong表的第四條到第五條記錄。
SELECT id,name,sex,birthday,d_id,salary,address
FROM
yuangong
ORDER BY id LIMIT 3,2;
（3）從bumen表查詢部門號（d_id）、部門名稱（d_name）和部門職能（function）。
SELECT d_id,d_name,function FROM bumen;
（4）從yuangong表中查詢?nèi)耸虏亢涂蒲胁康膯T工的信息。先從bumen表查詢出人事部和科研部的部門號。然后到y(tǒng)uangong表中去查詢員工的信息。
SELECT * FROM yuangong
WHERE
d_id=ANY(
SELECT d_id FROM bumen
WHERE
d_name IN('人事部','科研部'));
或者使用下面的代碼。
SELECT * FROM yuangong
WHERE
d_id IN(
SELECT d_id FROM bumen
WHERE
d_name='人事部' OR d_name='科研部');
（5）從yuangong表中查詢年齡在25到30之間的員工的信息?？梢酝ㄟ^兩種方式來查詢。
第一種方式:
SELECT * FROM yuangong
WHERE
birthday BETWEEN 25 AND 30;
第二種方式:
SELECT * FROM yuangong
WHERE
birthday>=25 AND birthday<=30;
（6）查詢每個部門有多少員工。先按部門號進行分組，然后用COUNT()函數(shù)來計算每組的人數(shù)。
SELECT d_id,COUNT(id)
FROM
yuangong GROUP BY d_id;
或者給COUNT(id)取名為sum。
SELECT d_id,COUNT(id) AS sum
FROM yuangong
GROUP BY d_id;
（7）查詢每個部門的最高工資。先按部門號進行分組，然后用MAX()函數(shù)來計算最大值。
SELECT d_id,MAX(salary)
FROM yuangong
GROUP BY d_id;
（8）用左連接的方式查詢bumen表和yuangong表。
使用?LEFT JOINON 來實現(xiàn)左連接。
SELECT bumen.d_id,d_name,function,bumen.address,id,name,birthday,sex,salary,yuangong.address
FROM bumen
LEFT JOIN yuangong ON yuangong.d_id=bumen.d_id;
（9）計算每個部門的總工資。先按部門號進行分組，然后用SUM()函數(shù)來求和。
SELECT d_id,SUM(salary)
FROM yuangong
GROUP BY d_id;
（10）查詢yuangong表，按照工資從高到低的順序排列。
SELECT * FROM yuangong
ORDER BY salary DESC;
（11）從bumen表和yuangong表中查詢出部門號，然后使用UNION合并查詢結(jié)果。
SELECT d_id FROM yuangong
UNION
SELECT d_id FROM bumen;
（12）查詢家是北京市員工的姓名、年齡、家庭住址。這里使用?LIKE 關(guān)鍵字。
SELECT name,birthday,address
FROM yuangong
WHERE
address LIKE '北京%';
（二）將在student表和score表上進行查詢。Student表和score表的定義如表所示：
Student表的內(nèi)容
| 字段名 |
字段描述 |
數(shù)據(jù)類型 |
主鍵 |
外鍵 |
非空 |
唯一 |
自增 |
| Num |
學號 |
INT(10) |
是 |
否 |
是 |
是 |
否 |
| name |
姓名 |
VARCHAR(20) |
否 |
否 |
是 |
否 |
否 |
| Sex |
性別 |
VARCHAR(4) |
否 |
否 |
是 |
否 |
否 |
| birthday |
出生年份 |
DATETIME |
否 |
否 |
否 |
否 |
否 |
| bumen |
院系 |
VARCHAR(20) |
否 |
否 |
是 |
否 |
否 |
| address |
家庭住址 |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
score表的內(nèi)容
| 字段名 |
字段描述 |
數(shù)據(jù)類型 |
主鍵 |
外鍵 |
非空 |
唯一 |
自增 |
| Id |
編號 |
INT(10) |
是 |
否 |
是 |
是 |
否 |
| C_name |
課程名 |
VARCHAR(20) |
否 |
否 |
否 |
否 |
否 |
| Stu_id |
學號 |
INT(10) |
否 |
是 |
是 |
否 |
否 |
| grade |
成績 |
INT(10) |
否 |
否 |
否 |
否 |
否 |
1.創(chuàng)建student表
CREATE TABLE student(
num INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday INT(4),
bumen VARCHAR(20) NOT NULL,
address VARCHAR(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.創(chuàng)建score表
CREATE TABLE score(
id INT(10) NOT NULL UNIQUE PRIMARY KEY,
c_name VARCHAR(20),
stu_id INT(10) NOT NULL,
grade INT(10),
FOREIGN KEY(stu_id)REFERENCES student(num)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.向student表中插入記錄
INSERT INTO student
VALUES
(901,'張軍','男',1985,'計算機系','北京市海淀區(qū)'),
(902,'張超','男',1986,'中文系','北京市昌平區(qū)'),
(903,'張美','女',1990,'中文系','湖南省永州市'),
(904,'李五一','男',1990,'英語系','遼寧省阜新市'),
(905,'王芳','女',1991,'英語系','福建省廈門市'),
(906,'王桂','男',1988,'計算機系','湖南省衡陽市');
4.向score表中插入記錄
INSERT INTO score
(stu_id,c_name,grade,id)
VALUES
(901,'計算機',98,1),
(901,'英語',80,2),
(902,'計算機',65,3),
(902,'中文',88,4),
(903,'中文',95,5),
(904,'計算機',70,6),
(904,'英語',92,7),
(905,'英語',94,8),
(906,'計算機',90,9),
(906,'英語',85,10);
5.按照下列要求進行表操作
（1）查詢student表的所有記錄。
方法一：用”*“.
SELECT * FROM student;
方法二：列出所有的列名。
SELECT num,name,sex,birthday,bumen,address
FROM student;
（2）查詢student表的第二條到第四條記錄。
SELECT *
FROM student
ORDER BY num LIMIT 1,3;????
（3）從student表查詢所有學生的學號、姓名和院系的信息。
SELECT num,name,bumen
FROM student;??
（4）查詢計算機系和英語系的學生的信息。
方法一：使用IN關(guān)鍵字
SELECT *
FROM student
WHERE
bumen in ('計算機系','英語系');
方法二：使用OR關(guān)鍵字
SELECT *
FROM student
WHERE
bumen ='計算機系' OR
bumen = '英語系';
（5）從student表中查詢年齡為18到22歲的學生的信息。
方法一：使用BETWEEN AND 關(guān)鍵字來查詢
SELECT *
FROM student
WHERE (2022-birthday)
BETWEEN 18 AND 22;
方式二：使用?AND 關(guān)鍵字和比較運算符。
SELECT *
FROM student
WHERE
(2022-birthday)>=18 AND (2022-birthday)<=22;
（6）student表中查詢每個院系有多少人，為統(tǒng)計的人數(shù)列取別名sum_of_bumen。
SELECT bumen,COUNT(bumen) AS sum_of_bumen
FROM student
GROUP BY bumen;
（7）從score表中查詢每個科目的最高分。
SELECT c_name,MAX(grade)
FROM score
GROUP BY c_name;
（8）查詢李五一的考試科目（c_name）和考試成績（grade）。
SELECT c_name,grade
FROM score,student
WHERE
score.stu_id=student.num AND name='李五一';
（9）用連接查詢的方式查詢所有學生的信息和考試信息。
SELECT *
FROM score,student
WHERE
score.stu_id=student.num ;
（10）計算每個學生的總成績（需顯示學生姓名）。
SELECT name,SUM(grade)
FROM score,student
WHERE
score.stu_id=student.num
GROUP BY name;
（11）計算每個考試科目的平均成績。
SELECT c_name,avg(grade)
FROM score
GROUP BY c_name;
（12）查詢計算機成績低于95的學生的信息。
SELECT *
FROM student,score
WHERE
student.num=score.stu_id
AND c_name='計算機'
AND grade<95;
（13）查詢同時參加計算機和英語考試的學生的信息。
SELECT num,name,sex,birthday,bumen,address
FROM student,score
WHERE
student.num=score.stu_id
AND c_name='英語'
AND stu_id IN
(SELECT stu_id
FROM score
WHERE
c_name='計算機');
（14）將計算機成績按從高到低進行排序。
SELECT grade
FROM score
WHERE
c_name='計算機' ORDER BY grade DESC; 
（15）從student表和score表中查詢出學生的學號，然后合并查詢結(jié)果。
SELECT stu_id
FROM score
UNION
SELECT num
FROM student;
（16）查詢姓張或者姓王的同學的姓名、院系、考試科目和成績。
SELECT name,bumen,c_name,grade
FROM student,score
WHERE
student.num=score.stu_id
AND (name LIKE'張%' OR name LIKE'王%');
（17）查詢都是湖南的同學的姓名、年齡、院系、考試科目和成績。
SELECT name,(2020-birthday) AS age,bumen,c_name,grade
FROM student, score
WHERE
student.num=score.stu_id
AND address LIKE '湖南%';
四、觀察與思考
1、LIKE的通配符有哪些？分別代表什么含義？
| 通配符 |
含義 |
| % ? |
有零個或更多個字符組成的任意字符串 |
| _ |
任意單個字符 |
| [ ] |
用于指定范圍，例如[A ~ F]，表示A ~ F范圍內(nèi)的任何單個字符 |
| [ ^ ] |
表示指定范圍之外的，例如[ ^A ~ F ]，表示A ~ F 范圍以外的任何單個字符 |
2、知道學生的出生日期，如何求出其年齡？
答：一般可以使用year函數(shù)來計算已知出生日期求年齡的問題
year(getdate())-year(?)
3.IS能用“=”來代替嗎？如何周全地考慮“空數(shù)據(jù)”的情況？
答：不可以的，is是用來判斷null的，=是用來直接比較值的；= 用在2種情況下：一是判斷值是否相等，二是用于賦值用 is null去判斷是否真的是空。
4.關(guān)鍵字ALL和DISTINCT有什么不同的含義？關(guān)鍵字ALL是否可以省略不寫？
答：ALL表示所有的字段DISTINCT表示去掉重復項，只取一條，All關(guān)鍵字是可以省略，但是在select子句和union子句中不能省略all關(guān)鍵字
5.聚集函數(shù)能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？
答：可用于 select和having中，where是在先篩選后計算，having是先計算后篩選。
6.WHERE子句與HAVING子句有何不同？
答：where子句作用于表或視圖中得行,而having子句作用于形成的組；where子句限定查找的行，having子句限定查找的組；若查詢語句中同時有where子句和Having子句，執(zhí)行時先去掉不滿足where條件的行，然后分組，分組后再去掉不滿足having條件的組。where自居不能直接使用聚合函數(shù)，having子句條件中可以包含聚合函數(shù)
7.count(*)、count(列名)、count(distinct 列名)三者的區(qū)別是什么？通過一個實例說明。
count（*）：是統(tǒng)計所有的個數(shù)不管是否重復，是否為空值；
count(列名)：不統(tǒng)計為空值的行；
count（distinct 列名）：統(tǒng)計不重復的行數(shù)為多少；
例：
?
?8.內(nèi)連接與外連接有什么區(qū)別？
答：內(nèi)連接查詢是最常見的一種查詢，也稱為同等查詢，就是在表關(guān)系的笛卡爾積數(shù)據(jù)記錄中，保留表關(guān)系中所有相匹配的數(shù)據(jù)，而棄舍不匹配的數(shù)據(jù)
外連接可以查詢兩個或兩個以上的表，外連接查詢和內(nèi)連接查詢非常相似，也需要通過指定的字段進行連接，當該字符取值相等時，可以查詢出該表的記錄。而且，該字段取值不相等的記錄也可以才查出來。
9.“=”與IN在什么情況下作用相同？
答：當in的候選值只有一個的時候
五、實驗要求
按要求完成，詳細記錄操作步驟，書寫實驗報告。所有實驗環(huán)節(jié)均由每位學?生獨立完成，認真記錄操作過程，嚴禁抄襲他人實驗結(jié)果。文章來源：http://www.zghlxwxcb.cn/news/detail-753695.html
六、實驗總結(jié)
通過本次實驗學習了select語法，它是數(shù)據(jù)庫中使用頻率最高的SQL語句，實驗中涉及到了select的各種查詢，例如，分組查詢、條件查詢、限制查詢，還有多表查詢的連接。在實驗中出現(xiàn)了連接查詢后的笛卡爾積現(xiàn)象，后查看在查詢語句中缺少括號來維持條件的整體性，修正后笛卡爾積現(xiàn)象得以解決順利的完成了所有實驗。文章來源地址http://www.zghlxwxcb.cn/news/detail-753695.html
到了這里，關(guān)于數(shù)據(jù)庫系統(tǒng)原理及MySQL應用教程實驗四MySQL數(shù)據(jù)庫表數(shù)據(jù)的查詢操作的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
