再有誰說還不會資料庫基礎知識點！就把這個直接甩過去

SQL語法基礎

基礎

模式定義了資料如何儲存、儲存什麼樣的資料以及資料如何分解等資訊，資料庫和表都有模式。

主鍵的值不允許修改，也不允許複用(不能使用已經刪除的主鍵值賦給新資料行的主鍵)。

SQL(Structured Query Language)，標準 SQL 由 ANSI 標準委員會管理，從而稱為 ANSI SQL。各個 DBMS 都有自己的實現，如 PL/SQL、Transact-SQL 等。

SQL 語句不區分大小寫，但是資料庫表名、列名和值是否區分依賴於具體的 DBMS 以及配置。

SQL 支援以下三種註釋:

# 註釋
SELECT *
FROM mytable; -- 註釋
/* 註釋1
   註釋2 */

資料庫建立與使用:

CREATE DATABASE test;
USE test;

建立表

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  col1 INT NOT NULL DEFAULT 1,
  col2 VARCHAR(45) NULL,
  col3 DATE NULL,
  PRIMARY KEY (`id`));

修改表

新增列

ALTER TABLE mytable
ADD col CHAR(20);

修改列和屬性（讀者@CodeHourra補充）

---ALTER TABLE 表名 CHANGE 原欄位名 新欄位名 欄位型別 約束條件
ALTER TABLE mytable 
CHANGE col col1 CHAR(32) NOT NULL DEFAULT '123';

刪除列

ALTER TABLE mytable
DROP COLUMN col;

刪除表

DROP TABLE mytable;

插入

普通插入

INSERT INTO mytable(col1, col2)
VALUES(val1, val2);

插入檢索出來的資料

INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;

將一個表的內容插入到一個新表

CREATE TABLE newtable AS
SELECT * FROM mytable;

更新

UPDATE mytable
SET col = val
WHERE id = 1;

刪除

DELETE FROM mytable
WHERE id = 1;

TRUNCATE TABLE 可以清空表，也就是刪除所有行。

TRUNCATE TABLE mytable;

使用更新和刪除操作時一定要用 WHERE 子句，不然會把整張表的資料都破壞。可以先用 SELECT 語句進行測試，防止錯誤刪除。

查詢

DISTINCT

相同值只會出現一次。它作用於所有列，也就是說所有列的值都相同才算相同。

SELECT DISTINCT col1, col2
FROM mytable;

LIMIT

限制返回的行數。可以有兩個引數，第一個引數為起始行，從 0 開始；第二個引數為返回的總行數。

返回前 5 行:

SELECT *
FROM mytable
LIMIT 5;

SELECT *
FROM mytable
LIMIT 0, 5;

返回第 3 ~ 5 行:

SELECT *
FROM mytable
LIMIT 2, 3;

排序

ASC : 升序(預設)
DESC : 降序

可以按多個列進行排序，並且為每個列指定不同的排序方式:

SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

過濾

不進行過濾的資料非常大，導致透過網路傳輸了多餘的資料，從而浪費了網路頻寬。因此儘量使用 SQL 語句來過濾不必要的資料，而不是傳輸所有的資料到客戶端中然後由客戶端進行過濾。

SELECT *
FROM mytable
WHERE col IS NULL;

下表顯示了 WHERE 子句可用的運算子

應該注意到，NULL 與 0、空字串都不同。

AND 和 OR 用於連線多個過濾條件。優先處理 AND，當一個過濾表示式涉及到多個 AND 和 OR 時，可以使用 () 來決定優先順序，使得優先順序關係更清晰。

IN 運算子用於匹配一組值，其後也可以接一個 SELECT 子句，從而匹配子查詢得到的一組值。

NOT 運算子用於否定一個條件。

萬用字元

萬用字元也是用在過濾語句中，但它只能用於文字欄位。

% 匹配 >=0 個任意字元；
\_ 匹配 ==1 個任意字元；
[ ] 可以匹配集合內的字元，例如 [ab] 將匹配字元 a 或者 b。用脫字元 ^ 可以對其進行否定，也就是不匹配集合內的字元。

使用 Like 來進行萬用字元匹配。

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 開頭的任意文字

不要濫用萬用字元，萬用字元位於開頭處匹配會非常慢。

計算欄位

在資料庫伺服器上完成資料的轉換和格式化的工作往往比客戶端上快得多，並且轉換和格式化後的資料量更少的話可以減少網路通訊量。

計算欄位通常需要使用 AS 來取別名，否則輸出的時候欄位名為計算表示式。

SELECT col1 * col2 AS alias
FROM mytable;

CONCAT() 用於連線兩個欄位。許多資料庫會使用空格把一個值填充為列寬，因此連線的結果會出現一些不必要的空格，使用 TRIM() 可以去除首尾空格。

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

函式

各個 DBMS 的函式都是不相同的，因此不可移植，以下主要是 MySQL 的函式。

彙總

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

文字處理

轉換為語音值

其中， SOUNDEX() 可以將一個字串轉換為描述其語音表示的字母數字模式。

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

日期和時間處理

日期格式: YYYY-MM-DD
時間格式: HH:MM:SS

mysql> SELECT NOW();

2018-4-14 20:25:11

數值處理

分組

分組就是把具有相同的資料值的行放在同一組中。

可以對同一分組資料使用匯總函式進行處理，例如求分組資料的平均值等。

指定的分組欄位除了能按該欄位進行分組，也會自動按該欄位進行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;

GROUP BY 自動按分組欄位進行排序，ORDER BY 也可以按彙總欄位來進行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

WHERE 過濾行，HAVING 過濾分組，行過濾應當先於分組過濾。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

分組規定:

GROUP BY 子句出現在 WHERE 子句之後，ORDER BY 子句之前；
除了彙總欄位外，SELECT 語句中的每一欄位都必須在 GROUP BY 子句中給出；
NULL 的行會單獨分為一組；
大多數 SQL 實現不支援 GROUP BY 列具有可變長度的資料型別。

子查詢

子查詢中只能返回一個欄位的資料。

可以將子查詢的結果作為 WHRER 語句的過濾條件:

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

下面的語句可以檢索出客戶的訂單數量，子查詢語句會對第一個查詢檢索出的每個客戶執行一次:

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

連線

連線用於連線多個表，使用 JOIN 關鍵字，並且條件語句使用 ON 而不是 WHERE。

連線可以替換子查詢，並且比子查詢的效率一般會更快。

可以用 AS 給列名、計算欄位和表名取別名，給表名取別名是為了簡化 SQL 語句以及連線相同表。

內連線

內連線又稱等值連線，使用 INNER JOIN 關鍵字。

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

可以不明確使用 INNER JOIN，而使用普通查詢並在 WHERE 中將兩個表中要連線的列用等值方法連線起來。

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

在沒有條件語句的情況下返回笛卡爾積。

自連線

自連線可以看成內連線的一種，只是連線的表是自身而已。

一張員工表，包含員工姓名和員工所屬部門，要找出與 Jim 處在同一部門的所有員工姓名。

子查詢版本

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自連線版本

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

自然連線

自然連線是把同名列透過等值測試連線起來的，同名列可以有多個。

內連線和自然連線的區別: 內連線提供連線的列，而自然連線自動連線所有同名列。

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

外連線

外連線保留了沒有關聯的那些行。分為左外連線，右外連線以及全外連線，左外連線就是保留左表沒有關聯的行。

檢索所有顧客的訂單資訊，包括還沒有訂單資訊的顧客。

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

組合查詢

使用 UNION 來組合兩個查詢，如果第一個查詢返回 M 行，第二個查詢返回 N 行，那麼組合查詢的結果一般為 M+N 行。

每個查詢必須包含相同的列、表示式和聚集函式。

預設會去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一個 ORDER BY 子句，並且必須位於語句的最後。

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

檢視

檢視是虛擬的表，本身不包含資料，也就不能對其進行索引操作。

對檢視的操作和對普通表的操作一樣。

檢視具有如下好處:

簡化複雜的 SQL 操作，比如複雜的連線；
只使用實際表的一部分資料；
透過只給使用者訪問檢視的許可權，保證資料的安全性；
更改資料格式和表示。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

儲存過程

儲存過程可以看成是對一系列 SQL 操作的批處理。

使用儲存過程的好處:

程式碼封裝，保證了一定的安全性；
程式碼複用；
由於是預先編譯，因此具有很高的效能。

命令列中建立儲存過程需要自定義分隔符，因為命令列是以 ; 為結束符，而儲存過程中也包含了分號，因此會錯誤把這部分分號當成是結束符，造成語法錯誤。

包含 in、out 和 inout 三種引數。

給變數賦值都需要用 select into 語句。

每次只能給一個變數賦值，不支援集合的操作。

delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //

delimiter ;

call myprocedure(@ret);
select @ret;

遊標

在儲存過程中使用遊標可以對一個結果集進行移動遍歷。

遊標主要用於互動式應用，其中使用者需要對資料集中的任意行進行瀏覽和修改。

使用遊標的四個步驟:

宣告遊標，這個過程沒有實際檢索出資料；
開啟遊標；
取出資料；
關閉遊標；

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # 定義了一個 continue handler，當 sqlstate '02000' 這個條件出現時，會執行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;

觸發器

觸發器會在某個表執行以下語句時而自動執行: DELETE、INSERT、UPDATE。

觸發器必須指定在語句執行之前還是之後自動執行，之前執行使用 BEFORE 關鍵字，之後執行使用 AFTER 關鍵字。BEFORE 用於資料驗證和淨化，AFTER 用於審計跟蹤，將修改記錄到另外一張表中。

INSERT 觸發器包含一個名為 NEW 的虛擬表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 獲取結果

DELETE 觸發器包含一個名為 OLD 的虛擬表，並且是隻讀的。

UPDATE 觸發器包含一個名為 NEW 和一個名為 OLD 的虛擬表，其中 NEW 是可以被修改的，而 OLD 是隻讀的。

MySQL 不允許在觸發器中使用 CALL 語句，也就是不能呼叫儲存過程。

事務管理

基本術語:

事務(transaction)指一組 SQL 語句；
回退(rollback)指撤銷指定 SQL 語句的過程；
提交(commit)指將未儲存的 SQL 語句結果寫入資料庫表；
保留點(savepoint)指事務處理中設定的臨時佔位符(placeholder)，你可以對它釋出回退(與回退整個事務處理不同)。

不能回退 SELECT 語句，回退 SELECT 語句也沒意義；也不能回退 CREATE 和 DROP 語句。

MySQL 的事務提交預設是隱式提交，每執行一條語句就把這條語句當成一個事務然後進行提交。當出現 START TRANSACTION 語句時，會關閉隱式提交；當 COMMIT 或 ROLLBACK 語句執行後，事務會自動關閉，重新恢復隱式提交。

透過設定 autocommit 為 0 可以取消自動提交；autocommit 標記是針對每個連線而不是針對伺服器的。

如果沒有設定保留點，ROLLBACK 會回退到 START TRANSACTION 語句處；如果設定了保留點，並且在 ROLLBACK 中指定該保留點，則會回退到該保留點。

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

字符集

基本術語:

字符集為字母和符號的集合；
編碼為某個字符集成員的內部表示；
校對字元指定如何比較，主要用於排序和分組。

除了給表指定字符集和校對外，也可以給列指定:

CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以在排序、分組時指定校對:

SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

許可權管理

MySQL 的賬戶資訊儲存在 mysql 這個資料庫中。

USE mysql;
SELECT user FROM user;

建立賬戶

新建立的賬戶沒有任何許可權。

CREATE USER myuser IDENTIFIED BY 'mypassword';

修改賬戶名

RENAME myuser TO newuser;

刪除賬戶

DROP USER myuser;

檢視許可權

SHOW GRANTS FOR myuser;

授予許可權

賬戶用 username@host 的形式定義，username@% 使用的是預設主機名。

GRANT SELECT, INSERT ON mydatabase.* TO myuser;

刪除許可權

GRANT 和 REVOKE 可在幾個層次上控制訪問許可權:

整個伺服器，使用 GRANT ALL 和 REVOKE ALL；
整個資料庫，使用 ON database.*；
特定的表，使用 ON database.table；
特定的列；
特定的儲存過程。

REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

更改密碼

必須使用 Password() 函式

SET PASSWROD FOR myuser = Password('new_password');

SQL語句練習

在上文學習了SQL的基本語法以後，本文將透過最經典的“教師-學生-成績”表來幫助你練習SQL。

構建如下表結構

還有一個Grade表，在如下的練習中體現

插入資料

下面表SQL和相關測試資料是我Dump出來的

-- MySQL dump 10.13  Distrib 5.7.17, for macos10.12 (x86_64)
--
-- Host: localhost    Database: learn_sql_pdai_tech
-- ------------------------------------------------------
-- Server version 5.7.28

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `COURSE`
--

DROP TABLE IF EXISTS `COURSE`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `COURSE` (
  `CNO` varchar(5) NOT NULL,
  `CNAME` varchar(10) NOT NULL,
  `TNO` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `COURSE`
--

LOCK TABLES `COURSE` WRITE;
/*!40000 ALTER TABLE `COURSE` DISABLE KEYS */;
INSERT INTO `COURSE` VALUES ('3-105','計算機導論','825'),('3-245','作業系統','804'),('6-166','資料電路','856'),('9-888','高等數學','100');
/*!40000 ALTER TABLE `COURSE` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `SCORE`
--

DROP TABLE IF EXISTS `SCORE`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `SCORE` (
  `SNO` varchar(3) NOT NULL,
  `CNO` varchar(5) NOT NULL,
  `DEGREE` decimal(10,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `SCORE`
--

LOCK TABLES `SCORE` WRITE;
/*!40000 ALTER TABLE `SCORE` DISABLE KEYS */;
INSERT INTO `SCORE` VALUES ('103','3-245',86.0),('105','3-245',75.0),('109','3-245',68.0),('103','3-105',92.0),('105','3-105',88.0),('109','3-105',76.0),('101','3-105',64.0),('107','3-105',91.0),('101','6-166',85.0),('107','6-106',79.0),('108','3-105',78.0),('108','6-166',81.0);
/*!40000 ALTER TABLE `SCORE` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `STUDENT`
--

DROP TABLE IF EXISTS `STUDENT`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `STUDENT` (
  `SNO` varchar(3) NOT NULL,
  `SNAME` varchar(4) NOT NULL,
  `SSEX` varchar(2) NOT NULL,
  `SBIRTHDAY` datetime DEFAULT NULL,
  `CLASS` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `STUDENT`
--

LOCK TABLES `STUDENT` WRITE;
/*!40000 ALTER TABLE `STUDENT` DISABLE KEYS */;
INSERT INTO `STUDENT` VALUES ('108','曾華','男','1977-09-01 00:00:00','95033'),('105','匡明','男','1975-10-02 00:00:00','95031'),('107','王麗','女','1976-01-23 00:00:00','95033'),('101','李軍','男','1976-02-20 00:00:00','95033'),('109','王芳','女','1975-02-10 00:00:00','95031'),('103','陸君','男','1974-06-03 00:00:00','95031');
/*!40000 ALTER TABLE `STUDENT` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `TEACHER`
--

DROP TABLE IF EXISTS `TEACHER`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `TEACHER` (
  `TNO` varchar(3) NOT NULL,
  `TNAME` varchar(4) NOT NULL,
  `TSEX` varchar(2) NOT NULL,
  `TBIRTHDAY` datetime NOT NULL,
  `PROF` varchar(6) DEFAULT NULL,
  `DEPART` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `TEACHER`
--

LOCK TABLES `TEACHER` WRITE;
/*!40000 ALTER TABLE `TEACHER` DISABLE KEYS */;
INSERT INTO `TEACHER` VALUES ('804','李誠','男','1958-12-02 00:00:00','副教授','計算機系'),('856','張旭','男','1969-03-12 00:00:00','講師','電子工程系'),('825','王萍','女','1972-05-05 00:00:00','助教','計算機系'),('831','劉冰','女','1977-08-14 00:00:00','助教','電子工程系');
/*!40000 ALTER TABLE `TEACHER` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-02-06 18:18:25

相關練習

1、查詢Student表中的所有記錄的Sname、Ssex和Class列。

select SNAME, SSEX, CLASS from STUDENT;

2、查詢教師所有的單位即不重複的Depart列。

select distinct DEPART from TEACHER;

3、查詢Student表的所有記錄。

select * from STUDENT;

4、查詢Score表中成績在60到80之間的所有記錄。

select *
from SCORE
where DEGREE > 60 and DEGREE < 80;

5、查詢Score表中成績為85，86或88的記錄。

select *
from SCORE
where DEGREE = 85 or DEGREE = 86 or DEGREE = 88;

6、查詢Student表中“95031”班或性別為“女”的同學記錄。

select *
from STUDENT
where CLASS = '95031' or SSEX = '女';

7、以Class降序查詢Student表的所有記錄。

select *
from STUDENT
order by CLASS desc;

8、以Cno升序、Degree降序查詢Score表的所有記錄。

select *
from SCORE
order by CNO asc, DEGREE desc;

9、查詢“95031”班的學生人數。

select count(*)
from STUDENT
where CLASS = '95031';

10、查詢Score表中的最高分的學生學號和課程號。

select
  sno,
  CNO
from SCORE
where DEGREE = (
  select max(DEGREE)
  from SCORE
);

11、查詢‘3-105’號課程的平均分。

select avg(DEGREE)
from SCORE
where CNO = '3-105';

12、查詢Score表中至少有5名學生選修的並以3開頭的課程的平均分數。

select
  avg(DEGREE),
  CNO
from SCORE
where cno like '3%'
group by CNO
having count(*) > 5;

13、查詢最低分大於70，最高分小於90的Sno列。

select SNO
from SCORE
group by SNO
having min(DEGREE) > 70 and max(DEGREE) < 90;

14、查詢所有學生的Sname、Cno和Degree列。

select
  SNAME,
  CNO,
  DEGREE
from STUDENT, SCORE
where STUDENT.SNO = SCORE.SNO;

15、查詢所有學生的Sno、Cno和Degree列。

select
  SCORE.SNO,
  CNO,
  DEGREE
from STUDENT, SCORE
where STUDENT.SNO = SCORE.SNO;

16、查詢所有學生的Sname、Cname和Degree列。

SELECT
  A.SNAME,
  B.CNAME,
  C.DEGREE
FROM STUDENT A
  JOIN (COURSE B, SCORE C)
    ON A.SNO = C.SNO AND B.CNO = C.CNO;

17、查詢“95033”班所選課程的平均分。

select avg(DEGREE)
from SCORE
where sno in (select SNO
              from STUDENT
              where CLASS = '95033');

18、假設使用如下命令建立了一個grade表:

create table grade (
  low  numeric(3, 0),
  upp  numeric(3),
  rank char(1)
);
insert into grade values (90, 100, 'A');
insert into grade values (80, 89, 'B');
insert into grade values (70, 79, 'C');
insert into grade values (60, 69, 'D');
insert into grade values (0, 59, 'E');

現查詢所有同學的Sno、Cno和rank列。

SELECT
  A.SNO,
  A.CNO,
  B.RANK
FROM SCORE A, grade B
WHERE A.DEGREE BETWEEN B.LOW AND B.UPP
ORDER BY RANK;

19、查詢選修“3-105”課程的成績高於“109”號同學成績的所有同學的記錄。

select *
from SCORE
where CNO = '3-105' and DEGREE > ALL (
  select DEGREE
  from SCORE
  where SNO = '109'
);

20、查詢score中選學一門以上課程的同學中分數為非最高分成績的學生記錄

select * from STUDENT where SNO
  in (select SNO
  from SCORE
  where DEGREE < (select MAX(DEGREE) from SCORE)
  group by SNO
  having count(*) > 1);

21、查詢成績高於學號為“109”、課程號為“3-105”的成績的所有記錄。

select *
from SCORE
where CNO = '3-105' and DEGREE > ALL (
  select DEGREE
  from SCORE
  where SNO = '109'
);

22、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。

select
  SNO,
  SNAME,
  SBIRTHDAY
from STUDENT
where year(SBIRTHDAY) = (
  select year(SBIRTHDAY)
  from STUDENT
  where SNO = '108'
);

23、查詢“張旭“教師任課的學生成績。

select *
from SCORE
where cno = (
  select CNO
  from COURSE
    inner join TEACHER on COURSE.TNO = TEACHER.TNO and TNAME = '張旭'
);

24、查詢選修某課程的同學人數多於5人的教師姓名。

select TNAME
from TEACHER
where TNO = (
  select TNO
  from COURSE
  where CNO = (select CNO
               from SCORE
               group by CNO
               having count(SNO) > 5)
);

25、查詢95033班和95031班全體學生的記錄。

select *
from STUDENT
where CLASS in ('95033', '95031');

26、查詢存在有85分以上成績的課程Cno.

select cno
from SCORE
group by CNO
having MAX(DEGREE) > 85;

27、查詢出“計算機系“教師所教課程的成績表。

select *
from SCORE
where CNO in (select CNO
              from TEACHER, COURSE
              where DEPART = '計算機系' and COURSE.TNO = TEACHER.TNO);

28、查詢“計算機系”與“電子工程系“不同職稱的教師的Tname和Prof

select
  tname,
  prof
from TEACHER
where depart = '計算機系' and prof not in (
  select prof
  from TEACHER
  where depart = '電子工程系'
);

29、查詢選修編號為“3-105“課程且成績至少高於選修編號為“3-245”的同學的Cno、Sno和Degree,並按Degree從高到低次序排序。

select
  CNO,
  SNO,
  DEGREE
from SCORE
where CNO = '3-105' and DEGREE > any (
  select DEGREE
  from SCORE
  where CNO = '3-245'
)
order by DEGREE desc;

30、查詢選修編號為“3-105”且成績高於選修編號為“3-245”課程的同學的Cno、Sno和Degree.

SELECT *
FROM SCORE
WHERE CNO = '3-105' AND DEGREE > ALL (
  SELECT DEGREE
  FROM SCORE
  WHERE CNO = '3-245'
)
ORDER by DEGREE desc;

31、查詢所有教師和同學的name、sex和birthday.

select
  TNAME     name,
  TSEX      sex,
  TBIRTHDAY birthday
from TEACHER
union
select
  sname     name,
  SSEX      sex,
  SBIRTHDAY birthday
from STUDENT;

32、查詢所有“女”教師和“女”同學的name、sex和birthday.

select
  TNAME     name,
  TSEX      sex,
  TBIRTHDAY birthday
from TEACHER
where TSEX = '女'
union
select
  sname     name,
  SSEX      sex,
  SBIRTHDAY birthday
from STUDENT
where SSEX = '女';

33、查詢成績比該課程平均成績低的同學的成績表。

SELECT A.*
FROM SCORE A
WHERE DEGREE < (SELECT AVG(DEGREE)
                FROM SCORE B
                WHERE A.CNO = B.CNO);

34、查詢所有任課教師的Tname和Depart.

select
  TNAME,
  DEPART
from TEACHER a
where exists(select *
             from COURSE b
             where a.TNO = b.TNO);

35、查詢所有未講課的教師的Tname和Depart.

select
  TNAME,
  DEPART
from TEACHER a
where tno not in (select tno
                  from COURSE);

36、查詢至少有2名男生的班號。

select CLASS
from STUDENT
where SSEX = '男'
group by CLASS
having count(SSEX) > 1;

37、查詢Student表中不姓“王”的同學記錄。

select *
from STUDENT
where SNAME not like "王%";

38、查詢Student表中每個學生的姓名和年齡。

select
  SNAME,
  year(now()) - year(SBIRTHDAY)
from STUDENT;

39、查詢Student表中最大和最小的Sbirthday日期值。

select min(SBIRTHDAY) birthday
from STUDENT
union
select max(SBIRTHDAY) birthday
from STUDENT;

40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。

select *
from STUDENT
order by CLASS desc, year(now()) - year(SBIRTHDAY) desc;

41、查詢“男”教師及其所上的課程。

select *
from TEACHER, COURSE
where TSEX = '男' and COURSE.TNO = TEACHER.TNO;

42、查詢最高分同學的Sno、Cno和Degree列。

select
  sno,
  CNO,
  DEGREE
from SCORE
where DEGREE = (select max(DEGREE)
                from SCORE);

43、查詢和“李軍”同性別的所有同學的Sname.

select sname
from STUDENT
where SSEX = (select SSEX
              from STUDENT
              where SNAME = '李軍');

44、查詢和“李軍”同性別並同班的同學Sname.

select sname
from STUDENT
where (SSEX, CLASS) = (select
                         SSEX,
                         CLASS
                       from STUDENT
                       where SNAME = '李軍');

45、查詢所有選修“計算機導論”課程的“男”同學的成績表

select *
from SCORE, STUDENT
where SCORE.SNO = STUDENT.SNO and SSEX = '男' and CNO = (
  select CNO
  from COURSE
  where CNAME = '計算機導論');

46、使用遊標方式來同時查詢每位同學的名字，他所選課程及成績。

declare
 cursor student_cursor is
  select S.SNO,S.SNAME,C.CNAME,SC.DEGREE as DEGREE
  from STUDENT S, COURSE C, SCORE SC
  where S.SNO=SC.SNO
  and SC.CNO=C.CNO;

  student_row student_cursor%ROWTYPE;

begin
  open student_cursor;
   loop
    fetch student_cursor INTO student_row;
    exit when student_cursor%NOTFOUND;
     dbms_output.put_line( student_row.SNO || '' || 

student_row.SNAME|| '' || student_row.CNAME || '' ||

student_row.DEGREE);
   end loop;
  close student_cursor;
END;
/

47、宣告觸發器指令，每當有同學轉換班級時執行觸發器顯示當前和之前所在班級。

CREATE OR REPLACE TRIGGER display_class_changes 
AFTER DELETE OR INSERT OR UPDATE ON student 
FOR EACH ROW 
WHEN (NEW.sno > 0) 

BEGIN 
   
   dbms_output.put_line('Old class: ' || :OLD.class); 
   dbms_output.put_line('New class: ' || :NEW.class); 
END; 
/ 


Update student
set class=95031
where sno=109;

48、刪除已設定的觸發器指令

DROP TRIGGER display_class_changes;

SQL 語句最佳化

負向查詢不能使用索引

select name from user where id not in (1,3,4);

應該修改為:

select name from user where id in (2,5,6);

前導模糊查詢不能使用索引

如:

select name from user where name like '%zhangsan'

非前導則可以:

select name from user where name like 'zhangsan%'

建議可以考慮使用 Lucene 等全文索引工具來代替頻繁的模糊查詢。

資料區分不明顯的不建議建立索引

如 user 表中的性別欄位，可以明顯區分的才建議建立索引，如身份證等欄位。

欄位的預設值不要為 null

這樣會帶來和預期不一致的查詢結果。

在欄位上進行計算不能命中索引

select name from user where FROM_UNIXTIME(create_time) < CURDATE();

應該修改為:

select name from user where create_time < FROM_UNIXTIME(CURDATE());

最左字首問題

如果給 user 表中的 username pwd 欄位建立了複合索引那麼使用以下SQL 都是可以命中索引:

select username from user where username='zhangsan' and pwd ='axsedf1sd'

select username from user where pwd ='axsedf1sd' and username='zhangsan'

select username from user where username='zhangsan'

但是使用

select username from user where pwd ='axsedf1sd'

是不能命中索引的。

如果明確知道只有一條記錄返回

select name from user where username='zhangsan' limit 1

可以提高效率，可以讓資料庫停止遊標移動。

不要讓資料庫幫我們做強制型別轉換

select name from user where telno=18722222222

這樣雖然可以查出資料，但是會導致全表掃描。

需要修改為

select name from user where telno='18722222222'

如果需要進行 join 的欄位兩表的欄位型別要相同

不然也不會命中索引。