MySQL資料庫操作（6）查詢

標籤：auto   des   分隔字元   computer   tail   from   4條   操作   name   

準備工作

建立並進入資料庫：

CREATE DATABASE `info`;USE `info`;

#建立學院表：

CREATE TABLE `department`(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20) NOT NULL);

#建立學生表：

CREATE TABLE `student`(`s_id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20) NOT NULL,`dept_id` INT,FOREIGN KEY(`dept_id`) REFERENCES `department` (`id`));

#建立學生的詳細資料表：

CREATE TABLE `stu_details`(`s_id` INT PRIMARY KEY,`age` INT,`sex` CHAR(1),FOREIGN KEY(`s_id`) REFERENCES `student` (`s_id`));

#建立課程表：

CREATE TABLE `course`(`id` INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(20) NOT NULL);

#建立中間表：

CREATE TABLE `select`(`s_id` INT,`c_id` INT,PRIMARY KEY (`s_id`,`c_id`),FOREIGN KEY (`s_id`) REFERENCES `student`(`s_id`),FOREIGN KEY (`c_id`) REFERENCES `course`(`id`));

#查看當前存在的表：

SHOW TABLES;

#往學院表中添加資料：

INSERT INTO `department`(`name`)VALUES(‘English‘),(‘Art‘),(‘Computer‘),(‘Alchemy‘);

#往學生表中添加資料：

INSERT INTO `student`(`name`,`dept_id`)VALUES(‘David‘,1),(‘Lucy‘,3),(‘Lily‘,3),(‘John‘,4),(‘Jack‘,2),(‘Alfred‘,3);

#往學生詳細資料表中添加資料：

INSERT INTO stu_detailsVALUES(1,18,‘M‘),(4,20,‘M‘),(3,16,‘F‘),(2,19,‘F‘);

#往課程表中添加資料：

INSERT INTO `course`(`name`)VALUES (‘Math‘),(‘Python‘),(‘Test‘), (‘Music‘);

#往中間表中添加資料：

INSERT INTO `select`VALUES(1,2),(1,4),(2,1),(2,4),(4,1),(4,2),(4,4);

查詢

查詢所有記錄：
SELECT * FROM 表名;
例：

SELECT * FROM `department`;SELECT * FROM `student`;SELECT * FROM `stu_details`;SELECT * FROM `course`;SELECT * FROM `select`;

查詢指定列記錄：

SELECT 列名1,列名2 FROM 表名;
例：

SELECT s_id,sex FROM `stu_details`;

查詢指定條件下的記錄：

SELECT 列名 FROM 表名 WHERE 條件;
例：

SELECT s_id,sex FROM `stu_details` WHERE age<19;

查詢後為列取別名：

SELECT 列名 [AS] 別名 FROM 表名;
別名僅僅是顯示在表頭上
例：

SELECT name AS `nn` ,dept_id AS dd FROM student WHERE s_id>=2;SELECT name nn ,dept_id dd FROM student WHERE s_id>=2;

模糊查詢：

SELECT 列名 FROM 表名 WHERE 條件列 LIKE 匹配字串;
% 匹配0~n個字元
_ 匹配一個字元
例：

SELECT * FROM student WHERE name LIKE ‘L%‘;SELECT * FROM student WHERE name LIKE ‘__fr%‘;

邏輯運算子 OR AND ：

例：

SELECT s_id,sex FROM `stu_details` WHERE age<20 AND sex=‘M‘;SELECT s_id,sex FROM `stu_details` WHERE age>=20 OR sex=‘F‘;

判斷為null，不用 = ，只能用 IS， IS NOT

例：

SELECT * FROM student WHERE dept_id IS NULL;SELECT * FROM student WHERE dept_id IS NOT NULL;

IN ， NOT IN

SELECT * FROM student WHERE dept_id NOT IN (1,3);SELECT * FROM student WHERE dept_id IN (1,3);

排序`ORDER BY`：

`ASC`升序(預設) `DESC`降序
例：
按學生學號升序輸出

SELECT * FROM `select` ORDER BY `s_id`;

按課程id降序輸出

SELECT * FROM `select` ORDER BY `c_id` DESC;

限制顯示資料的數量`LIMIT`：

LIMIT n 從第0條開始，往後面取n條
LIMIT m,n 從第m條開始，往後面取n條
例：
按學生學號升序輸出的前4條資料

SELECT * FROM `select` ORDER BY `s_id` LIMIT 4; 

按學生學號升序輸出的從第2條開始的4條資料（資料索引從0開始）

SELECT * FROM `select` ORDER BY `s_id` LIMIT 2,4;

分組查詢`GROUP BY` ：

GROUP BY 只能跟彙總函式一起用， 一般用 做統計
一個分組內值可能會不同的列，不用用作select，例如：SELECT s_id , age ,sex FROM stu_details GROUP BY sex;是非法的，因為每個sex分組中的元素，s_id和age都是不同的。
例：

SELECT sex , MAX(age) FROM stu_details GROUP BY sex;

將組內每個元素拼成字串，並且用指定的分隔字元來分隔

SELECT dept_id,GROUT_CONCAT(s_id,name SEPARATOR ‘,‘) FROM student GROUP BY `dept_id`;

彙總函式：

----------------------------------------------------
| AVG(column)| 返回某列的平均值 |
| COUNT(column)| 返回某列的行數（不包括 NULL 值） |
| COUNT(*)| 返回被選行數 |
| FIRST(column)| 返回在指定的域中第一個記錄的值 |
| LAST(column)| 返回在指定的域中最後一個記錄的值 |
| MAX(column)| 返回某列的最高值 |
| MIN(column)| 返回某列的最低值 |
| SUM(column)| 返回某列的總和 |
----------------------------------------------------
HAVING分組條件：
HAVING 後的欄位必須是SELECT後出現過的，並且可以使用別名
WHERE：帶著條件去表裡面，刪選內容。
HAVING：SELECT出來了結果，再進行一次刪選。
例：

SELECT dept_id ,count(dept_id) AS n FROM student GROUP BY `dept_id` HAVING n=1;

 

子查詢
出現在其他SQL語句內的SELECT字句。
1)嵌套在查詢內部
2)必須始終出現在圓括弧內
3)可以包含多個關鍵字或條件
把另一個SQL的結果，當成資料，直接拿過來用。
例：
# 求出學生的平均年齡

SELECT AVG(`age`) FROM `stu_details`;

#尋找出大於平均年齡的資料

SELECT * FROM `stu_details` WHERE `age`>18.25;

#將平均數的SQL語句作為子查詢放入上一條語句中

SELECT * FROM `stu_details` WHERE `age`>(SELECT AVG(`age`) FROM `stu_details`);

#選出學院為Computer和English的學生，先選出學院ID，再找出符合的學生ID

SELECT * FROM student WHERE dept_id IN (SELECT id FROM department WHERE name IN (‘Computer‘,‘English‘) );

 

MySQL資料庫操作（6）查詢