文章目錄

• 5 關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言
• • 5.1 MySQL簡(jiǎn)介
  • • 5.1.1 MySQL的發(fā)展歷史
    • 5.1.2 數(shù)據(jù)庫(kù)的用戶(hù)接口
    • 5.1.3 SQL及其概念辨析
    • 5.1.4 SQL查詢(xún)語(yǔ)言的分類(lèi)
  • 5.2 入手
  • • 5.2.1 回顧——基本和虛表
    • 5.2.2 數(shù)據(jù)準(zhǔn)備
    • 5.2.3 常用命令
    • 5.2.4 導(dǎo)入數(shù)據(jù)
    • 5.2.5 簡(jiǎn)單的查詢(xún)
    • 5.2.6 條件查詢(xún)
    • • 5.2.6.1 條件查詢(xún)格式
      • 5.6.2.1 Between and
      • 5.2.6.3 is null
      • 5.2.6.4 or
      • 5.2.6.5 and和or的優(yōu)先級(jí)問(wèn)題
      • 5.2.6.6 in
      • 5.2.6.7 like
    • 5.2.7 排序
  • 5.3 漸入
  • • 5.3.1 分組函數(shù)
    • 5.3.2 單行處理函數(shù)
    • 5.3.3 回到分組函數(shù)
    • 5.3.4 分組
    • 5.3.5 多字段分組查詢(xún)
    • 5.3.6 部分limit
    • 5.3.7 小結(jié)
  • 5.4 連接查詢(xún)
  • • 5.4.1 概述
    • 5.4.2 連接查詢(xún)分類(lèi)
    • 5.4.3 連接查詢(xún)細(xì)講
    • • 5.4.3.1 原理
      • 5.4.3.2 等值連接
      • 5.4.3.2 非等值連接
      • 5.4.3.3 自連接
      • 5.4.3.4 外連接
      • 5.4.3.5 全連接
      • 5.4.3.6 多張表做連接
    • 5.4.4 子查詢(xún)
    • • 5.4.4.1 概述
      • 5.4.4.2 集合成員資格
      • 5.4.4.2 集合的比較
      • 5.4.4.3 from子查詢(xún)
    • 5.4.5 謂詞查詢(xún)
    • • 5.4.5.1 some查詢(xún)
      • 5.4.5.2 all查詢(xún)
      • 5.4.5.3 空關(guān)系測(cè)試
      • 5.4.5.4 重復(fù)元組存在性測(cè)試
      • 5.4.5.5 with 子句
  • 5.5 集合運(yùn)算
  • • 5.5.1 并運(yùn)算
    • 5.5.2 交運(yùn)算
    • 5.5.3 差運(yùn)算
    • 5.5.4 空運(yùn)算
  • 5.6 表結(jié)構(gòu)
  • • 5.5.1 建表和刪表
    • 5.5.2 插入數(shù)據(jù)
    • 5.5.3 拷貝表
    • 5.5.4 修改表的數(shù)據(jù)
    • 5.5.5 刪除表中數(shù)據(jù)
    • 5.5.6 后話(huà)
  • 5.7 約束
  • • 5.7.1 非空約束
    • 5.7.2 唯一性約束
    • 5.7.3 主鍵約束
    • 5.7.4 外鍵約束
  • 5.8 視圖
  • 5.9 數(shù)據(jù)庫(kù)數(shù)據(jù)的導(dǎo)入導(dǎo)出
  • 5.10 用戶(hù)管理
  • • 5.10.1 用戶(hù)操作
    • 5.10.2 權(quán)限控制

5 關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言

在這一小節(jié)中，我們將會(huì)首先使用MySQL進(jìn)行我們的講解，而后如果有機(jī)會(huì)，我會(huì)簡(jiǎn)單介紹一下SQLserver。

對(duì)于MySQL的下載和安裝可以前往MySQL官網(wǎng)進(jìn)行下載。安裝過(guò)程可以參照Win10安裝MySQL詳細(xì)教程_蔚來(lái)不是夢(mèng)的博客-CSDN博客_win10安裝mysql進(jìn)行安裝。

5.1 MySQL簡(jiǎn)介

5.1.1 MySQL的發(fā)展歷史

1986年發(fā)布了SQL-86

1989年發(fā)布了SQL-89

然后發(fā)布了92、99、03、06、08

在這里特別標(biāo)明的1986和1989常作為軟件工程師考題而出現(xiàn)，望各位知悉。

5.1.2 數(shù)據(jù)庫(kù)的用戶(hù)接口

什么是數(shù)據(jù)庫(kù)的用戶(hù)接口？

用戶(hù)使用數(shù)據(jù)庫(kù)時(shí)對(duì)數(shù)據(jù)庫(kù)進(jìn)行的操作，DBMS要想方設(shè)法滿(mǎn)足，其提供的命令和語(yǔ)言就叫用戶(hù)和數(shù)據(jù)庫(kù)的接口。DBMS提供多種用戶(hù)接口來(lái)針對(duì)不同的適用人群。其中接口包括：

• 查詢(xún)語(yǔ)言
• 圖形化工具（GUI）
• APIs
• 類(lèi)庫(kù)

5.1.3 SQL及其概念辨析

SQL的概念可以歸結(jié)為以下幾點(diǎn)：

• 結(jié)構(gòu)化查詢(xún)語(yǔ)言，是一門(mén)標(biāo)準(zhǔn)通用的語(yǔ)言。標(biāo)準(zhǔn)的sql適合于所有的數(shù)據(jù)庫(kù)產(chǎn)品。
• SQL屬于高級(jí)語(yǔ)言，只要能看得懂英語(yǔ)單詞的，寫(xiě)出來(lái)的sql語(yǔ)句，可以讀懂什么意思。
• SQL語(yǔ)句在執(zhí)行的時(shí)候，實(shí)際上內(nèi)部也會(huì)進(jìn)行編譯，然后再執(zhí)行sql（sql語(yǔ)句的編譯由DBMS完成。）
• SQL是一個(gè)非過(guò)程化的查詢(xún)語(yǔ)言

而其和DB和DBMS的區(qū)別如下：

• DB：DataBase（數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)實(shí)際上在硬盤(pán)上以文件的形式存在）

• DBMS: DataBase Managemeng System（數(shù)據(jù)庫(kù)管理系統(tǒng)，常見(jiàn)的有：MySQL、Oracle、DB2、Sybase、sqlServer。。。）

5.1.4 SQL查詢(xún)語(yǔ)言的分類(lèi)

SQL按其功能可以分為幾大部分：

• DDL（數(shù)據(jù)定義語(yǔ)言）：數(shù)據(jù)定義語(yǔ)言，用于定義、撤銷(xiāo)和修改數(shù)據(jù)模式，creat、drop、alter。
• DQL（數(shù)據(jù)查詢(xún)語(yǔ)言）：查詢(xún)語(yǔ)句，凡是select語(yǔ)句都是DQL。
• DML（數(shù)據(jù)操縱語(yǔ)言）:用于增刪改數(shù)據(jù)，insert、delete、update。
• DCL（數(shù)據(jù)訪(fǎng)問(wèn)權(quán)限控制語(yǔ)言）：用于數(shù)據(jù)訪(fǎng)問(wèn)權(quán)限的控制。grant授權(quán)，revoke撤銷(xiāo)權(quán)限。
• TCL（事務(wù)控制）：commit提交事務(wù)，rollback回滾事務(wù)。
• embeddedSQL and dynamicSQL（嵌入式SQL和動(dòng)態(tài)SQL）：嵌入式和動(dòng)態(tài)SQL。其作用是定義SQL語(yǔ)句如何嵌入到通用編程語(yǔ)言?xún)?nèi)部，如C、C++和java等。

5.2 入手

為了方便上手，我們不會(huì)先討論表是怎么創(chuàng)建的，而要先從最簡(jiǎn)單卻又最復(fù)雜的查詢(xún)語(yǔ)句開(kāi)始講起。但在此之前，我們需要弄懂一些概念，這些概念我們?cè)谇懊嬉呀?jīng)簡(jiǎn)要提到過(guò)，這里稍微回顧一下。

5.2.1 回顧——基本和虛表

我們?cè)谇懊嬖?jīng)談?wù)撨^(guò)基表和虛表（視圖）。

基表是真真實(shí)實(shí)存在的，他的數(shù)據(jù)顯式地存儲(chǔ)在數(shù)據(jù)庫(kù)中，或者換一種說(shuō)法就是，你當(dāng)時(shí)存的時(shí)候什么樣基表就長(zhǎng)什么樣。

而虛表是僅有邏輯定義，可以根據(jù)其定義從其他表（包括視圖）中導(dǎo)出，但不作為一個(gè)表顯式地存儲(chǔ)在數(shù)據(jù)庫(kù)中。換一種說(shuō)法就是，比如你數(shù)據(jù)庫(kù)里面已經(jīng)有個(gè)基表了，然后我通過(guò)某些要求過(guò)濾了一些條件，查詢(xún)出來(lái)的表就是虛表，虛表實(shí)際上不存在數(shù)據(jù)庫(kù)里，他只是通過(guò)一些計(jì)算和邏輯語(yǔ)言提取出來(lái)的。

當(dāng)基表的模式修改時(shí)，通過(guò)定義適當(dāng)?shù)囊晥D，仍可以為用戶(hù)提供修改前的數(shù)據(jù)模式，避免修改應(yīng)用程序，從而有利于提高數(shù)據(jù)的邏輯獨(dú)立性。也就是說(shuō)，即使你基表改了，但是為了視圖還是和以前一樣，我們可以在基表的基礎(chǔ)上做一些其他的操作，使他改變操作后算出來(lái)的虛表和之前沒(méi)改的虛表一模一樣。

5.2.2 數(shù)據(jù)準(zhǔn)備

在下面的練習(xí)中我們會(huì)用到大量的查詢(xún)操作，但這都是建立在數(shù)據(jù)庫(kù)中有表的基礎(chǔ)上，所以我們會(huì)提供數(shù)據(jù)先建立幾張表。

DROP TABLE IF EXISTS EMP; DROP TABLE IF EXISTS DEPT; DROP TABLE IF EXISTS SALGRADE;CREATE TABLE DEPT(DEPTNO int(2) not null ,DNAME VARCHAR(14) ,LOC VARCHAR(13),primary key (DEPTNO)); CREATE TABLE EMP(EMPNO int(4) not null ,ENAME VARCHAR(10),JOB VARCHAR(9),MGR INT(4),HIREDATE DATE DEFAULT NULL,SAL DOUBLE(7,2),COMM DOUBLE(7,2),primary key (EMPNO),DEPTNO INT(2) );CREATE TABLE SALGRADE( GRADE INT,LOSAL INT,HISAL INT );//插入數(shù)據(jù) INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); commit;INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17' , 800, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20' , 1600, 300, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22' , 1250, 500, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02' , 2975, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28' , 1250, 1400, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01' , 2850, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09' , 2450, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19' , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17' , 5000, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08' , 1500, 0, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788, '1987-05-23' , 1100, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03' , 950, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03' , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23' , 1300, NULL, 10); commit;INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); commit;

在下面，我會(huì)教你如何導(dǎo)入這份數(shù)據(jù)。

需要注意的是，雖然SQL通用，但是建表語(yǔ)句有部分語(yǔ)法是不通用的，所以上面的表數(shù)據(jù)只適用于MySQL，如果將其導(dǎo)入SQLserver是會(huì)報(bào)錯(cuò)的。

在下面的敘述中，我時(shí)常會(huì)交叉使用用術(shù)語(yǔ)字段和屬性，實(shí)際上兩個(gè)術(shù)語(yǔ)指的都是同一件事物。

5.2.3 常用命令

查看當(dāng)前使用的是哪個(gè)數(shù)據(jù)庫(kù)？

select database();

查詢(xún)數(shù)據(jù)庫(kù)版本也可以使用

select version();

結(jié)束一條語(yǔ)句？

\c

退出mysql？

exit

查看創(chuàng)建表的語(yǔ)句

show create table emp;

5.2.4 導(dǎo)入數(shù)據(jù)

我們下面會(huì)將5.2.2中準(zhǔn)備的數(shù)據(jù)導(dǎo)入MySQL里我們建立的數(shù)據(jù)庫(kù)。如果你是第一次學(xué)習(xí)，建議跟著我照做即可。

• 第一步：登錄mysql數(shù)據(jù)庫(kù)管理系統(tǒng)。

• 第二步：查看有哪些數(shù)據(jù)庫(kù)

  show databases;

  (這個(gè)不是sql語(yǔ)句，屬于MySQL命令)

• 第三步：創(chuàng)建屬于我們自己的數(shù)據(jù)庫(kù)

  Create database bjpowernode;

  (這個(gè)不是sql語(yǔ)句，屬于MySQL命令)

• 第四步：使用bjpowernode數(shù)據(jù)

  Use bjpowernode;

  (這個(gè)不是sql語(yǔ)句，屬于MySQL命令)

• 第五步：查看當(dāng)前使用的數(shù)據(jù)庫(kù)有哪些表?

  Show tables;

• 第六步：初始化數(shù)據(jù)

  Mysql>source D:\MySQL\bjpowernode.sql（這里source后面跟的是上面sql文件的數(shù)據(jù)，如果你沒(méi)有該文件，建議你打開(kāi)記事本把5.2.2中的sql復(fù)制進(jìn)去然后重命名為bjpowernode.sql）

當(dāng)我們使用source進(jìn)行初始化數(shù)據(jù)庫(kù)后，此時(shí)數(shù)據(jù)庫(kù)中有三張表，我們可以使用show table進(jìn)行查看。

mysql> show tables;
±----------------------+
| Tables_in_bjpowernode |
±----------------------+
| dept |
| emp |
| salgrade |
±----------------------+
3 rows in set (0.00 sec)

5.2.5 簡(jiǎn)單的查詢(xún)

如果需要?jiǎng)h除一個(gè)數(shù)據(jù)庫(kù)

drop database bjpowernode;

如果需要查看一個(gè)表的結(jié)構(gòu)

desc 表名

如desc dept;

±-------±------------±-----±----±--------±------+

| Field | Type | Null | Key | Default | Extra |

±-------±------------±-----±----±--------±------+

| DEPTNO | int(2) | NO | PRI | NULL | |（部門(mén)編號(hào)）

| DNAME | varchar(14) | YES | | NULL | |（部門(mén)名稱(chēng)）

| LOC | varchar(13) | YES | | NULL | | （部門(mén)位置）

±-------±------------±-----±----±--------±------+

利用如下代碼可以查詢(xún)表中所有數(shù)據(jù)

select * from 表名

查詢(xún)表中數(shù)據(jù)的語(yǔ)法如下：

Select 字段名1，字段名2，字段名3，…from 表名；

這里需要注意的是，任何一條sql語(yǔ)句都以";"結(jié)尾并且sql語(yǔ)句不區(qū)分大小寫(xiě)。在老版的Mysql中如果你一條sql語(yǔ)句不寫(xiě)分號(hào)是會(huì)報(bào)錯(cuò)的，但是新版不會(huì)報(bào)錯(cuò)。為了養(yǎng)成良好的習(xí)慣，我們應(yīng)該加分號(hào)。

比如我們想查看員工的年薪

select ename,sal*12 from emp;

需要注意的是，我們查詢(xún)的字段是可以進(jìn)行運(yùn)算的，比如上述的sql語(yǔ)句，我想查詢(xún)員工表中員工名和薪水，但是我要的是年薪，表中給的是月薪，這時(shí)只需要對(duì)月薪乘12即可。

對(duì)于上面的查詢(xún)，我們發(fā)現(xiàn)查詢(xún)得出的表中年薪的字段是sal* 12。如果對(duì)sal*12難受，我們可以重命名

select ename,sal*12 as yearsal from emp;

這里的as是可以省略的。

如果想起中文名也可以

select ename,sal*12 as "年薪" from emp;

需要注意的是，這里用中文單引號(hào)和雙引號(hào)都可以，但是建議單引號(hào)，因?yàn)閱我?hào)其他數(shù)據(jù)庫(kù)也通用，不然你這里的數(shù)據(jù)庫(kù)可以用其他不能用就會(huì)導(dǎo)致很尷尬。

如果要查詢(xún)所有的字段，即查看整張表的數(shù)據(jù)。

select*from emp;

在實(shí)際開(kāi)發(fā)中是不推薦使用*的，因?yàn)槿绻粡埍淼膶傩苑浅６?#xff0c;會(huì)導(dǎo)致查出來(lái)的數(shù)據(jù)非常多，這在實(shí)際開(kāi)發(fā)中查詢(xún)的時(shí)候效率十分低下。

對(duì)于查詢(xún)出來(lái)的結(jié)果有些可能會(huì)重復(fù)，此時(shí)我們可以用distinct去重

select distinct job from emp;

需要注意的是，distinct關(guān)鍵字必須放在所有字段的最前面，因?yàn)槿绻幱谥虚g會(huì)導(dǎo)致去重不對(duì)等，如

select ename,distinct job from emp;

以上的sql語(yǔ)句是錯(cuò)誤的，為什么？因?yàn)閖ob是去重了，但是ename沒(méi)有去重，導(dǎo)致兩條屬性值結(jié)果不一樣多。

distinct出現(xiàn)在最前面表示后面的字段聯(lián)合起來(lái)去重。

如果你想顯式地指明你需要保留重復(fù)元組，可以使用all關(guān)鍵字，但是保留重復(fù)元組是默認(rèn)的，所以我們?cè)谑褂弥袑⒉粫?huì)使用all關(guān)鍵字。

5.2.6 條件查詢(xún)

5.2.6.1 條件查詢(xún)格式

條件查詢(xún)語(yǔ)法格式如下：

Select 字段，字段…… From 表名 Where 條件；

例如查詢(xún)員工表中工資待遇為5000的員工姓名

select ename from emp where sal = 5000 ;

例如想要查看員工名為smith的工資

select sal from emp where ename = 'smith';

不只是中文要加單引號(hào)，只要是字符串都要加單引號(hào)，不知道是不是字符串可以用desc去查詢(xún)表的結(jié)構(gòu)。

如果想要找出工資高于3000的員工

select ename from emp where sal>3000;

如果想要找出工資不等于3000的員工

select ename from emp where sal <> 3000;

如果你學(xué)過(guò)一些編程語(yǔ)言，也可以用以下的方式：

select ename from emp where sal != 3000;

5.6.2.1 Between and

如果想要找出工資在1100和3000之間的員工，包括1100和3000，可以如下

select ename from emp where sal >=1100 and sal<=3000;

也可以如下

select ename from emp where sal between 1100 and 3000;

這里要注意下，between必須左小右大，并且所劃的區(qū)間是閉區(qū)間。

Between and 除了可以使用在數(shù)字方面之外，還可以使用在字符串方面。

select ename from emp where ename between 'A' and 'D';

這里要注意的是，字符串用between and 取頭字母二十四字母區(qū)間，而且所劃區(qū)間左閉右開(kāi)。

5.2.6.3 is null

如果想要找出哪些人沒(méi)有津貼，可以如下

select ename,sal,comm from emp where comm is null;

需要注意的是，在sql中NULL不是0，不是一個(gè)值，而是代表沒(méi)有這個(gè)數(shù)據(jù)，為空。

如果想要找出哪些人的津貼不為空，可以如下

select ename,sal,comm from emp where comm is not null;

5.2.6.4 or

如果想要找出工作崗位是MANAGER和SALESMAN的員工，可以如下

select ename,job from emp where job = 'MAnager' or job ='salesman';

5.2.6.5 and和or的優(yōu)先級(jí)問(wèn)題

如果我們想要找出薪資大于1000的并且部門(mén)編號(hào)是20或30部門(mén)的員工。可以如下：

select ename,sal,deptno from emp where sal>1000 and deptno=20 or deptno = 30;

這里觀察下表發(fā)現(xiàn)，有一項(xiàng)數(shù)據(jù)出了問(wèn)題。

±-------±--------±-------+
| ename | sal | deptno |
±-------±--------±-------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 |
| FORD | 3000.00 | 20 |
±-------±--------±-------+

這說(shuō)明and的運(yùn)算優(yōu)先級(jí)大于or運(yùn)算優(yōu)先級(jí)，在實(shí)際運(yùn)用中并不需要記住這些優(yōu)先級(jí)，不確定的優(yōu)先級(jí)直接加小括號(hào)括起來(lái)先運(yùn)算即可。即如下所示

mysql> select ename,sal,deptno from emp where sal>1000 and (deptno=20 or deptno = 30);

5.2.6.6 in

在前面我們?cè)?jīng)這樣：找出工作崗位是MANAGER和SALESMAN的員工，當(dāng)時(shí)我們使用的是or。當(dāng)然，我們可以使用in來(lái)解決

select ename,job from emp where job in ('SALESMAN','MANAGER');

需要注意的是，這里in后面的是值集，不是區(qū)間。

5.2.6.7 like

找出員工表中員工名字含有o的，可以如下：

select ename from emp where ename like '%o%';

在這里我們需要注意的是，在模糊查詢(xún)中需要掌握兩個(gè)特殊的符號(hào)，一個(gè)是%一個(gè)是_，%代表匹配任意多個(gè)字符， _代表匹配一個(gè)字符。

如果我們想要找到名字中第二個(gè)字母是A的，可以如下：

select ename from emp where ename like '_a%';

如果要找出名字中有下劃線(xiàn)的，為了避免使用下劃線(xiàn)時(shí)被誤判為想要使用匹配字符功能，我們可以在其前面加上轉(zhuǎn)義字符\。

這里要有個(gè)關(guān)于數(shù)據(jù)類(lèi)型的模糊查詢(xún)問(wèn)題，對(duì)于char和varchar，其模糊查詢(xún)出來(lái)的結(jié)果可以有點(diǎn)不一樣。

varchar是可變的，也就是說(shuō)模糊查詢(xún)查的結(jié)果就是我們想要的結(jié)果；而對(duì)于char來(lái)說(shuō)，一旦你設(shè)char里容納4個(gè)字節(jié)而只存放3個(gè)字節(jié)，那么其余空間將會(huì)全部空格補(bǔ)齊。也就是說(shuō)，用like去查"l_%"，那么“ _ ”所匹配的很有可能是空格。

舉個(gè)比較簡(jiǎn)單的例子，如果我們使用char(3)來(lái)存放"劉云"這個(gè)名字，那么"like劉_ _"是可以把"云"后面所在的空格也查出來(lái)的，也就是說(shuō)，使用char(3)存放劉云而使用like劉 _ _查詢(xún)，是可以把劉云這個(gè)結(jié)果查出來(lái)的。

5.2.7 排序

對(duì)于排序，我們先給出一個(gè)例子：按照工資排序，找出員工名和薪資。

select ename,sal from emp order by sal;

也就是說(shuō)，關(guān)鍵詞order by后跟想要排序的屬性。但是根據(jù)上述給出的sql語(yǔ)句可知，其對(duì)sal的排序是一個(gè)升序排列。

在排序中，若order by [屬性名] [排序方式]中的排序方式未給出，那么其默認(rèn)升序。如果需要手動(dòng)指定升序可以使用asc，而指定降序可以使用desc。

如果我們要按照工資的升序降序排列，當(dāng)工資相同的時(shí)候再按照名字的升序排列。如下所示：

select ename,sal from emp order by sal desc,ename asc;

需要注意的是，這里有必要說(shuō)一下各個(gè)關(guān)鍵詞的執(zhí)行順序：

mysql> select-> 字段 3-> from-> 表名 1-> where-> 條件 2-> order by 4

5.3 漸入

5.3.1 分組函數(shù)

SQL中提供了五個(gè)固有的聚集函數(shù)，分別是：

• count 計(jì)數(shù)

• sum 求和

• avg 平均值

• max 最大值

• min 最小值

需要注意的是，sum和avg的輸入必須是數(shù)字值集，而其他函數(shù)可以作用在非數(shù)字值集上。

在有些書(shū)上，也把分組函數(shù)叫做聚集函數(shù)，其作用是以一個(gè)值集作為輸入，返回單個(gè)值的函數(shù)。而且他還有例外一個(gè)名字，叫多行處理函數(shù)。多行處理函數(shù)的特點(diǎn)就是：輸入多行，最終輸出一行。

既然有多行處理函數(shù)，肯定也有一個(gè)單行處理函數(shù)，也就是，先找到一行，處理一行，然后再繼續(xù)下一行。

這么說(shuō)可能有點(diǎn)模糊，我們?cè)谙旅娴睦又袝?huì)詳細(xì)體會(huì)。

我們?nèi)绻獙?duì)津貼做一個(gè)統(tǒng)計(jì)，我們可以這么干：

select count(comm) from emp;

查出來(lái)的結(jié)果你試試就知道是4，也就是說(shuō)，count具有的計(jì)數(shù)并不是和sum一樣的計(jì)算總和的功能，而是統(tǒng)計(jì)一個(gè)關(guān)系中符合條件元組的個(gè)數(shù)。并且在這里例子中，分組函數(shù)是自動(dòng)忽略NULL的。

我們時(shí)常用count來(lái)計(jì)算一個(gè)關(guān)系中所有元組的個(gè)數(shù)，如下所示：

select count(*) from emp;

5.3.2 單行處理函數(shù)

單行處理函數(shù)和分組函數(shù)是相對(duì)的，其輸入一行處理一行。對(duì)于數(shù)據(jù)為空的位置來(lái)說(shuō)，如果空數(shù)據(jù)參與運(yùn)算，那么算出來(lái)的一切結(jié)果都會(huì)為空。

為此，如果我們想改變這種情況，我們可以用單行處理函數(shù)ifnull()來(lái)解決，ifnull又叫空處理函數(shù)，是單行處理函數(shù)的一種，它的作用是把某個(gè)字段中所有為null的位置填上自己指定的默認(rèn)值。

select ename,ifnull(comm,0) as comm from emp;

5.3.3 回到分組函數(shù)

分組函數(shù)有一個(gè)需要注意的點(diǎn)是，我們不能在where關(guān)鍵字后跟分組函數(shù)，如我們想找出工資高于平均工資的員工：

select avg(sal) from emp where sal>avg(sal);ERROR 1111 (HY000): Invalid use of group function

上面的SQL語(yǔ)句是錯(cuò)誤的。導(dǎo)致這個(gè)的原因?qū)嶋H上是因?yàn)樵谑褂梅纸M函數(shù)的時(shí)候，實(shí)際上SQL是先where后分組再分組函數(shù)，分組實(shí)際上和分組函數(shù)沒(méi)有啥關(guān)系，分組的關(guān)鍵字是group by。一般來(lái)說(shuō)分組后才會(huì)使用分組函數(shù)。

換言之，順序來(lái)解釋就是：

select 5 from 1 where 2 group by 3 having 4 order by 6

你可以理解為分組函數(shù)的順序是3.5。:happy:

那回到上述問(wèn)題，我們?nèi)绾卧诒苊夥纸M函數(shù)跟在where后的情況下解決找出工資高于平均工資的員工這個(gè)問(wèn)題？

這時(shí)候我們可以使用嵌套子查詢(xún)，即n個(gè)sql語(yǔ)句可以合成一句來(lái)寫(xiě)。在這里我們先簡(jiǎn)單介紹體會(huì)一下：

select ename,sal from emp where sal>(select avg(sal) from emp ) ;

5.3.4 分組

既然前面說(shuō)到group by關(guān)鍵字，這一小節(jié)肯定是要著重介紹了，與group by一同出場(chǎng)的還有having關(guān)鍵字。

前面我們說(shuō)過(guò)，where的執(zhí)行順序優(yōu)先于group by，那么當(dāng)where篩選條件后得出的新關(guān)系拿來(lái)用group by分組得出新關(guān)系2，如果我們想再次做篩選該如何呢？這時(shí)候就需要使用having關(guān)鍵字了。having關(guān)鍵字的作用就是對(duì)分組之后的數(shù)據(jù)進(jìn)行二次過(guò)濾。

如果我們不僅希望分組函數(shù)作用于單個(gè)元組集上，而且希望將其作用到一組元組集上，用group by這個(gè)關(guān)鍵字就可以實(shí)現(xiàn)。group by用于構(gòu)造分組，它可以對(duì)一個(gè)關(guān)系中屬性值相同的元組分于一個(gè)組中。

要體會(huì)group by和having兩者的用處，請(qǐng)隨我看下面的例題：

我們?nèi)绻页雒總€(gè)工作崗位的最高薪資：

select job, max(sal) from emp group by job;

試想，如果在對(duì)工資取一個(gè)最大數(shù)的情況下，如果沒(méi)有分組，job該取什么值和這個(gè)Max(sal)對(duì)應(yīng)呢？如果對(duì)Job分組，那么job就會(huì)分成：

job

CLERK

SALESMAN

MANAGER

ANALYST

PRESIDENT

分組完成后會(huì)在每個(gè)小組中，取一個(gè)最大的sal來(lái)填入max(sal)的位置。

jobmax(sal)

ANALYST	3000.00
CLERK	1300.00
MANAGER	2975.00
PRESIDENT	5000.00
SALESMAN	1600.00

這也是為什么分組函數(shù)常常聯(lián)合使用且名為分組函數(shù)的緣故，任何一個(gè)分組函數(shù)都是在group by語(yǔ)句執(zhí)行結(jié)束之后才會(huì)執(zhí)行的。當(dāng)一條sql語(yǔ)句沒(méi)有g(shù)roup by的話(huà)，整張表的數(shù)據(jù)會(huì)自成一組。

你可以理解為：

select avg(sal) from emp; = select avg(sal) from emp group by;

5.3.5 多字段分組查詢(xún)

對(duì)于多個(gè)屬性同時(shí)分組，一定要注意一件事：出現(xiàn)在select語(yǔ)句中的但沒(méi)有被分組的屬性只能出現(xiàn)在group by。換句話(huà)說(shuō)任何沒(méi)有出現(xiàn)在group by子句中的屬性如果出現(xiàn)在select子句中的話(huà)，只能出現(xiàn)于分組函數(shù)內(nèi)部。下面我們拿一個(gè)十分簡(jiǎn)單的例子來(lái)說(shuō)明：

如果我們要找出每個(gè)工作崗位的最高工資：

select max(sal),job from emp group by job; #yes select ename,max(sal),job from emp group by job; #no

很明顯，第一條是對(duì)的，因?yàn)榈诙l的ename出現(xiàn)在select卻沒(méi)有出現(xiàn)在group by中，這就意味著sal通過(guò)分組函數(shù)分組了，job通過(guò)group by關(guān)鍵字分組了，就只有ename沒(méi)有分組。

以上第二條SQL語(yǔ)句在Oracle數(shù)據(jù)庫(kù)中無(wú)法執(zhí)行，執(zhí)行報(bào)錯(cuò)。

在SQLserver數(shù)據(jù)庫(kù)總無(wú)法執(zhí)行，執(zhí)行報(bào)錯(cuò)，報(bào)錯(cuò)提示屬性沒(méi)有被聚集。

在Mysql數(shù)據(jù)庫(kù)中可以執(zhí)行，但是執(zhí)行結(jié)果矛盾。

如果我們想要多個(gè)字段分組，我們可以在group by中填上需要分組的屬性，如下面的例子一樣：我們需要找出不同崗位的最高薪資。

select deptno,job,max(sal)fromempgroup bydeptno,job;

5.3.6 部分limit

limit用于分頁(yè)查詢(xún)，其為MySQL數(shù)據(jù)庫(kù)特有的關(guān)鍵字，其他數(shù)據(jù)庫(kù)中沒(méi)有，需要注意的是，Oracle中有一個(gè)相同的機(jī)制，叫做rownum。

limit用于選取集合中的部分?jǐn)?shù)據(jù)，它的語(yǔ)法機(jī)制為[結(jié)果集] limit startIndex , length。其中startIndex表示起始位置，從0開(kāi)始，length表示取幾個(gè)。

讓我們用一個(gè)例子來(lái)講述上面的知識(shí)點(diǎn)：我們想要找出工資排名在第4到第9的員工，我們可以采用下面的方式：

select ename,sal from emp order by sal desc limit 3,6;

5.3.7 小結(jié)

在原則上，如果可以在where中過(guò)濾的數(shù)據(jù)，盡量在where中過(guò)濾，效率較高。having的過(guò)濾是專(zhuān)門(mén)對(duì)分組之后的數(shù)據(jù)進(jìn)行過(guò)濾的。這一塊部分實(shí)際上涉及到查詢(xún)優(yōu)化的相關(guān)知識(shí)，在后面，我們會(huì)繼續(xù)深入探討查詢(xún)優(yōu)化的相關(guān)知識(shí)。

5.4 連接查詢(xún)

5.4.1 概述

在上面的小節(jié)中，我們一直都是在對(duì)一張表對(duì)查詢(xún)，可在實(shí)際開(kāi)發(fā)中，一個(gè)業(yè)務(wù)一般對(duì)應(yīng)多張表，比如學(xué)生和班級(jí)，是不可能讓你在一張表查的如此開(kāi)心的。

有些同學(xué)會(huì)感覺(jué)到疑惑，既然多表聯(lián)查如此麻煩，那我們把學(xué)生和班級(jí)的數(shù)據(jù)都統(tǒng)一在一張表上不就可以了嗎？但事實(shí)是，這涉及到關(guān)系數(shù)據(jù)庫(kù)的規(guī)范化，如果把數(shù)據(jù)全部集中在一張表上，那就會(huì)造成大量的冗余，比如學(xué)生表中的學(xué)生都是在一個(gè)班上，那么如果兩表合一，就會(huì)造成一張表上出現(xiàn)多次同個(gè)班級(jí)的情況。

5.4.2 連接查詢(xún)分類(lèi)

連接查詢(xún)根據(jù)年代來(lái)劃分可以分為兩種，其中我們普遍在學(xué)習(xí)的應(yīng)該是SQL92版本的，而SQL99的版本就比較新穎，語(yǔ)法也比較簡(jiǎn)潔明了。

另一種劃分方式是根據(jù)連接方式來(lái)劃分，如下所示：

• 內(nèi)連接
  • 等值連接
  • 非等值連接
  • 自連接
• 外連接
  • 左外連接（左連接）
  • 右外連接（右連接）
• 全連接（很少見(jiàn)）

5.4.3 連接查詢(xún)細(xì)講

5.4.3.1 原理

在SQL中的連接查詢(xún)?cè)砗完P(guān)系代數(shù)中的連接查詢(xún)?cè)韺?shí)際上是一回事，也就是說(shuō)，同樣是使用加了限制的笛卡爾積。需要注意的是，使用連接查詢(xún)看似查詢(xún)條數(shù)少于笛卡爾積，但是實(shí)際上它是先根據(jù)多表多笛卡爾積后再篩選符合條件的，所以實(shí)際上查詢(xún)時(shí)間是不會(huì)減少的。

5.4.3.2 等值連接

這些術(shù)語(yǔ)看似很?chē)樔?#xff0c;實(shí)際上很簡(jiǎn)單，就是根據(jù)某某條件相等來(lái)做連接。需要注意的是，現(xiàn)在在企業(yè)中很少有人會(huì)用老版的SQL92寫(xiě)法，而是采用新式SQL99寫(xiě)法。下面讓我們看看兩者區(qū)別如何：

如果我們要找出每一個(gè)員工的部門(mén)名稱(chēng)，要求顯示員工名和部門(mén)名。采用SQL92寫(xiě)法如下：

select ENAME ,DNAME from emp e,dept d where e.deptno = d.deptno;

SQL92之所以舍棄，官方的說(shuō)辭是，where里面寫(xiě)的是表連接的條件，不是過(guò)濾條件，條件結(jié)構(gòu)不清晰。

而在SQL99里，我們這么寫(xiě)：

select e.ENAME,d.DNAME from emp e (inner) join dept d on e.DEPTNO = d.DEPTNO;

其中inner可寫(xiě)可不寫(xiě)，寫(xiě)出來(lái)的好處是協(xié)同開(kāi)發(fā)的伙伴能夠看出是內(nèi)連接，可讀性好。相比于SQL92，SQL99的SQL語(yǔ)法結(jié)構(gòu)更加清晰一些：表的連接條件和where過(guò)濾條件分離了。

5.4.3.2 非等值連接

內(nèi)連接中的非等值連接最大的特點(diǎn)就是：連接條件中的關(guān)系是非等量關(guān)系。如我們想要找每個(gè)員工所處工資等級(jí)，并且顯示對(duì)應(yīng)名字和工資。

select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.LOSAL and s.HISAL

所謂的非等值連接，無(wú)非就是連接的條件從等價(jià)條件轉(zhuǎn)為非等價(jià)條件。

5.4.3.3 自連接

所謂的自連接，就是把自己的一張關(guān)系看成兩張，自己連接自己的關(guān)系。以題為例：如果我們找出每個(gè)員工的上級(jí)領(lǐng)導(dǎo)，要求顯示員工名和對(duì)應(yīng)的領(lǐng)導(dǎo)名。【備注：mgr經(jīng)理，empno工號(hào)】

select e.ename "員工名",e2.ename "老板名" from emp e join emp e2 on e.mgr = e2.empno;

5.4.3.4 外連接

在講述完自連接的同時(shí)，我們引入了外連接。假設(shè)A和B表進(jìn)行連接，使用內(nèi)連接的話(huà)，凡是A表和B表能夠匹配上的記錄查詢(xún)，就是內(nèi)連接，AB兩張表實(shí)際上并沒(méi)有主副之分，兩張表是平等的。

而對(duì)于外連接，假設(shè)A和B表進(jìn)行連接，使用外連接的話(huà)，AB兩張表中一張表是主表，一張表是副表，主要查詢(xún)主表匯總的數(shù)據(jù)，稍等著查詢(xún)副表，當(dāng)副表中的數(shù)據(jù)沒(méi)有和主表中的數(shù)據(jù)匹配上，副表自動(dòng)模擬出NULL與之匹配。

外連接主要分為左外連接和右外連接，分別對(duì)應(yīng)左邊的表是主表和右邊的表是主表。而對(duì)于左連接和右連接，實(shí)際上是相對(duì)的，主要看你兩張表哪張放左哪張放右。

讓我們看一下實(shí)際的例子：如果我們需要找出每個(gè)員工的上級(jí)領(lǐng)導(dǎo)，相對(duì)于上個(gè)例子，我們還要找出最頂級(jí)的上司。

由于在上個(gè)例子中我們使用的是內(nèi)連接的方式，所以最上級(jí)的領(lǐng)導(dǎo)實(shí)際上是不存在的，既然不存在，就匹配不到自己的上級(jí)的，既然匹配不出來(lái)，那么他對(duì)應(yīng)連接出來(lái)的元組是不顯示的。而對(duì)于外連接來(lái)說(shuō)，指定員工表作為主表，那么主表的所有元組都會(huì)存在于連接的結(jié)果中，即使某條元組匹配不到另一張表中對(duì)應(yīng)的元組，它也會(huì)用NULL補(bǔ)上。

回到本題的例子，我們可以用左外連接來(lái)解決這個(gè)問(wèn)題：

select e.ename "員工名",e2.ename "老板名" from emp e left（out） join emp e2 on e.mgr = e2.empno;

5.4.3.5 全連接

對(duì)于全連接來(lái)說(shuō)，不僅在開(kāi)發(fā)中即為罕見(jiàn)，在考試中也幾乎不考查，所以我們這里就不做過(guò)多講解了。

5.4.3.6 多張表做連接

在使用SQL99的寫(xiě)法對(duì)兩張表做連接時(shí)，我們使用的是from 表一 join 表二 on 條件這種寫(xiě)法，那如果我們要多張表連接呢？我們可以使用A join B join C ... on 條件。其表示的意思是：A表和B表先進(jìn)行表連接，連接之后A表繼續(xù)和C表進(jìn)行連接。

5.4.4 子查詢(xún)

5.4.4.1 概述

SQL中提供嵌套子查詢(xún)機(jī)制，子查詢(xún)是嵌套在另一個(gè)查詢(xún)中的select-from-where表達(dá)式。子查詢(xún)嵌套在where子句中，通常用于對(duì)集合的成員資格、集合的比較以及集合的基數(shù)進(jìn)行檢查。

上面的表述可以寫(xiě)為如下形式：

select…(select)from…(select)where…(select)

5.4.4.2 集合成員資格

SQL允許測(cè)試元組在關(guān)系中的成員資格。在前面的學(xué)習(xí)中，我們使用in來(lái)檢測(cè)元組是否為集合中的成員，集合是由select子句產(chǎn)生的一組值構(gòu)成的，連接詞not in則測(cè)試元組是否不是集合中的成員。

讓我們用一個(gè)例子來(lái)體會(huì)這里要講的知識(shí)：我們需要找出等于平均薪資的員工信息。在3.3中，我們初次體會(huì)到了嵌套子查詢(xún)，對(duì)于本題來(lái)說(shuō)，實(shí)際上就是在測(cè)試集合成員資格，我們可以把這個(gè)題目歸為以下步驟：

找出員工的平均薪資

找出等于平均薪資的員工

也就是說(shuō)，我們?cè)谡页鰡T工的平均薪資后，用in來(lái)判斷所求員工的薪資是否等于符合平均薪資。

select * from emp where sal = (select avg(sal) from emp); //寫(xiě)法一 select * from emp where sal in (select avg(sal) from emp);//寫(xiě)法二

同樣地，我們也可以用not in來(lái)完成找出不等于平均薪資的員工信息。

5.4.4.2 集合的比較

集合的比較比較典型的例子是3.3的例子，這里就不再細(xì)講了。在這里，我們使用的是where子查詢(xún)，意在比對(duì)兩個(gè)集合。

select * from emp where sal > (select avg(sal) from emp);

5.4.4.3 from子查詢(xún)

from子查詢(xún)實(shí)際上是進(jìn)行一個(gè)套娃的過(guò)程，也就是通過(guò)某種查詢(xún)查詢(xún)出一個(gè)大表，再?gòu)倪@個(gè)大表中進(jìn)行查詢(xún)。

比較具體的例子是：我們要找出每個(gè)部門(mén)的平均薪水的薪資等級(jí)。

首先我們可以先按部門(mén)編號(hào)分組，這樣的話(huà)得到是每個(gè)部門(mén)的平均薪水。

select deptno,avg(sal) avgsal from emp group by deptno;

然后我們?cè)賹⑸厦娌樵?xún)出來(lái)的大表當(dāng)做臨時(shí)表，讓其和salgrade表做連接，條件是其每個(gè)部門(mén)平均薪水處于每個(gè)薪資等級(jí)最高薪水和最低薪水之間。

select t.*,s.grade from (select DEPTNO ,avg(sal) as avgsal from emp group by DEPTNO) t join salgrade s on t.avgsal between s.LOSAL and s.HISAL;

5.4.5 謂詞查詢(xún)

謂詞查詢(xún)實(shí)際上都是應(yīng)用于集合的比較，通常對(duì)應(yīng)至少、至多等術(shù)語(yǔ)。其中集合的獲取可以使用前面講過(guò)的子查詢(xún)。

5.4.5.1 some查詢(xún)

some意為至少比某一個(gè)如何，如果我們要查詢(xún)某某至少比集合內(nèi)的某一個(gè)要大，可以使用>some [集合]來(lái)表示。

在SQL中關(guān)鍵詞any同義于some，只不過(guò)在早期的版本中僅允許使用ant，后來(lái)的版本為了避免和英語(yǔ)中的any一詞在語(yǔ)義上的混淆，又添加了一個(gè)可選擇的關(guān)鍵詞some。

5.4.5.2 all查詢(xún)

all意味所有，如果我們要查詢(xún)某某比集合內(nèi)所有的要大，可以使用> all [集合]來(lái)表示。

5.4.5.3 空關(guān)系測(cè)試

SQL除了上面兩個(gè)謂詞，還可以使用exists來(lái)測(cè)試子查詢(xún)的結(jié)果中是否存在所需元組。如果我們要查詢(xún)某某存在集合，可以使用exists [集合]來(lái)表示。如我們要查找工資為800且名字為史密斯的工作人員信息，我們可以：

select * from emp where sal = 800 and exists (select * from emp where ename = "SMITH");

同樣地，如果我們想要表示某某不存在所需集合，可以用not exists [集合]表示。

從上面的exists來(lái)看，其還可以用于且(和)運(yùn)算。

5.4.5.4 重復(fù)元組存在性測(cè)試

SQL提供一個(gè)布爾函數(shù)，用于測(cè)試在一個(gè)子查詢(xún)的結(jié)果中是否存在重復(fù)元組。如果作為參數(shù)的子查詢(xún)結(jié)果中沒(méi)有重復(fù)的元組，unique將返回true值。

5.4.5.5 with 子句

with子句用于建立臨時(shí)表，相比于from子查詢(xún)，其語(yǔ)法結(jié)構(gòu)更加清晰明了，如：查找銷(xiāo)售員崗位且名字為SIMTH的工作人員信息。

with SALESMAN as (select * from emp where JOB = "CLERK") select * from emp where ENAME = "SMITH";

需要注意的是，為臨時(shí)表指定名字時(shí)，格式并非原名 as 重命名，而是重命名 as 查詢(xún)所得表。

5.5 集合運(yùn)算

SQL作用在關(guān)系上的union，intersect和except運(yùn)算對(duì)應(yīng)我們高中數(shù)學(xué)集合論中學(xué)習(xí)的$\cup 、\cap 、?$運(yùn)算，在SQL中，這些集合運(yùn)算都是會(huì)對(duì)運(yùn)算結(jié)果做自動(dòng)去重。通過(guò)對(duì)下面例子的學(xué)習(xí)，我們能夠更加深入地去了解。

5.5.1 并運(yùn)算

我們都知道，并對(duì)應(yīng)邏輯運(yùn)算中的或，當(dāng)我們的題意中有或的字眼時(shí)，我們就要考慮使用并運(yùn)算，其語(yǔ)法規(guī)則為集合1 union 集合2如：查找工資為800或者1300的員工名以及工資信息。

select ename,sal from emp where sal = 800 union select ename,sal from emp where sal = 1300;

5.5.2 交運(yùn)算

交對(duì)應(yīng)邏輯運(yùn)算的和（且），當(dāng)我們的題意中有和或者且的字眼時(shí)就要考慮用交運(yùn)算。其語(yǔ)法規(guī)則為集合1 intersect 集合2。如：查找工資為800且職位為clerk的員工信息。

(select * from emp where sal = 800) intersect (select * from emp where job = clerk);

需要注意的是，在MySQL中是不支持intersect的，由前面所學(xué)的知識(shí)可知，以上的題目完全可以轉(zhuǎn)化為以下形式：

select * from emp where sal = 800 and job = clerk);

不僅MySQL不支持intersect，現(xiàn)在很多DBMS基本上都不支持。在MySQL中實(shí)際上如果真想使用交運(yùn)算，可以使用過(guò)我們?cè)?.4.5.3中學(xué)到的exists關(guān)鍵字，如本題我們還可以寫(xiě)為：

select * from emp where sal = 800 and exists (select * from emp where job = "clerk");

在前面我們說(shuō)過(guò)，集合運(yùn)算會(huì)對(duì)結(jié)果自動(dòng)去重，如果你想不讓其去重，可以使用intersect all。

5.5.3 差運(yùn)算

差對(duì)應(yīng)中文語(yǔ)義中的在...但不在...，其語(yǔ)法規(guī)則為集合1 except 集合2如：查找工資為800但職位不是銷(xiāo)售員(SALESMAN)的員工信息。

select * from emp where sal = 800 except select * from emp where job = salsman;

同樣地，上面的except在MySQL中同樣不支持，由前面所學(xué)知識(shí)可知，以上的題目完全可以轉(zhuǎn)化為以下形式：

select * from emp where sal = 800 and job!="salesman";

如果在Oracle，其使用關(guān)鍵字minus來(lái)代替except。如果想保留所有重復(fù)，可以使用except all來(lái)代替except。

5.5.4 空運(yùn)算

對(duì)于某個(gè)值和空值做運(yùn)算，其實(shí)際上是無(wú)法運(yùn)算的，所以為了解決這個(gè)問(wèn)題，除了true和false之外我們引入第三個(gè)邏輯值unknown。

對(duì)于and運(yùn)算來(lái)說(shuō)：true和unknown的結(jié)果是unknown，false和unknown結(jié)果是false，unknown and unknown結(jié)果是unknown。

對(duì)于or來(lái)說(shuō)：true和true or unknown的結(jié)果是true，false or unknown結(jié)果是unknown，unknown or unknown結(jié)果是unknown。

對(duì)于not來(lái)說(shuō)：not unknown的結(jié)果是unknown。

對(duì)于SQL來(lái)說(shuō)，我們可以使用關(guān)鍵詞null來(lái)測(cè)試控制，如：查找名為史密斯且其提成為空的員工信息。

select * from emp where ENAME = "SMITH" and COMM is null

5.6 表結(jié)構(gòu)

5.5.1 建表和刪表

如果我們需要建一個(gè)表，我們可以使用如下形式：

create table 表名（字段名 1 數(shù)據(jù)類(lèi)型，字段名 2 數(shù)據(jù)類(lèi)型，字段名 3 數(shù)據(jù)類(lèi)型，<完整性約束1><完整性約束2>...）；

對(duì)于MySQL中字段的數(shù)據(jù)類(lèi)型常見(jiàn)的有：

int整數(shù)型（java中的int）

bigint	長(zhǎng)整型	（java中的long）
float	浮點(diǎn)型	（java中的 float double）
char	定長(zhǎng)字符串	（String）
varchar	可變長(zhǎng)字符串	（StringBuffer/StringBuilder）
data	日期類(lèi)型	(對(duì)應(yīng)java中的java.sql.data類(lèi)型)
BLOB	二進(jìn)制大對(duì)象	（存儲(chǔ)圖片、視頻等流媒體信息）binary large object（對(duì)應(yīng)java的object）
CLOB	字符大對(duì)象	（存儲(chǔ)較大文本，比如，可以存儲(chǔ)4G的字符串。）character large object（對(duì)應(yīng)java的object）

其中容易混淆的兩個(gè)數(shù)據(jù)類(lèi)型當(dāng)然是char和varchar了，在5.2.6.7中我們?cè)?jīng)談過(guò)此事。他們兩個(gè)的區(qū)別在于，假如我指定char（6），那么不管我輸入啥字符，只要不超6個(gè)空間，它都是給6個(gè)空間，但是如果是varchar（6），他是一種智能的類(lèi)型，能判斷你輸入的字符是占多少個(gè)空間（前提是不超6），并且分配對(duì)應(yīng)字符的空間。

varchar不是一定要使用的，像生日，性別的這種數(shù)據(jù)字段不發(fā)生改變的時(shí)候，是定長(zhǎng)的，那我們就采用char。而當(dāng)一個(gè)字段的數(shù)據(jù)長(zhǎng)度不確定的時(shí)候，例如：簡(jiǎn)介、姓名等都是采用varchar。

在前面的學(xué)習(xí)中，我們時(shí)常發(fā)現(xiàn)存儲(chǔ)的數(shù)據(jù)常常是一些整數(shù)啊字符串啊之類(lèi)的結(jié)構(gòu)化數(shù)據(jù)，實(shí)際上表中數(shù)據(jù)是可以放一些非結(jié)構(gòu)化的數(shù)據(jù)比如視頻音樂(lè)等。假設(shè)有個(gè)電影表t_movie

idname(varchar)playtime(data/char)haibao(BLOB)history(CLOB)

1 2 3	蜘蛛俠 … …

一般來(lái)說(shuō)，我們不會(huì)直接將視頻放到表里面，而是將硬盤(pán)里面的路徑放到表里。而圖片就可能會(huì)放到表里。

說(shuō)完上面的知識(shí)點(diǎn)，讓我們來(lái)簡(jiǎn)單創(chuàng)建一個(gè)表吧！如果我們要?jiǎng)?chuàng)建一個(gè)學(xué)生表，學(xué)生信息包括:學(xué)號(hào)、姓名、性別、班級(jí)編號(hào)、生日

• 學(xué)號(hào)：bigint

• 姓名：varchar

• 性別：char

• 班級(jí)編號(hào)：int

• 生日：char

使用SQL語(yǔ)句我們可以這么創(chuàng)建：

create table t_student(no bigint,name varchar(255),sex char(1),classno varchar(255),birth char(10));

創(chuàng)建表的時(shí)候可以指定表為NULL時(shí)的默認(rèn)值，如：

create table t_student(no bigint,name varchar(255),sex char(1) default 1,classno varchar(255),birth char(10));

如果我們要?jiǎng)h除這個(gè)表，可以使用drop table if exists 表名來(lái)刪除。

5.5.2 插入數(shù)據(jù)

我們現(xiàn)在需要對(duì)這個(gè)空表插入幾個(gè)數(shù)據(jù)，插入數(shù)據(jù)的語(yǔ)法格式為：

insert into 表名(字段名1，字段名2，字段名3..)values(值1，值2，值3)

需要注意的是：這里要求字段的數(shù)量和值的數(shù)量相同，并且數(shù)據(jù)類(lèi)型要對(duì)應(yīng)相同。

我們來(lái)對(duì)上一小節(jié)創(chuàng)建的空表添加數(shù)據(jù)吧！如下所示：

insert into t_student(no,name,sex,classno,birth)values(1,'BaKa愛(ài)','1','gaosan1ban','1950-10-12');

MySQL為我們提供了簡(jiǎn)寫(xiě)方式：字段名和值能對(duì)得上就行，比如表中no字段對(duì)應(yīng)1，你可以寫(xiě)成：

insert into t_student(name,sex,classno,birth,no)values('zhangsan','1','gaosan1ban','1950-10-12',2);

如果表中有（學(xué)號(hào)、姓名、性別、班級(jí)編號(hào)、生日）五個(gè)字段，如果你只插入一個(gè)字段和值，那么其他字段的值為空。

insert into t_student(name) values('喬峰');

當(dāng)一條insert語(yǔ)句執(zhí)行成功之后，表格當(dāng)中必然會(huì)多一行記錄。即使多的這一行記錄當(dāng)中某些字段是NULL，后期也無(wú)法再執(zhí)行insert語(yǔ)句插入數(shù)據(jù)了，只能使用update進(jìn)行更新。

也就是說(shuō)，insert的最小操作單位是元組，不是屬性。

特別地，如果你寫(xiě)插入語(yǔ)句，不寫(xiě)字段名，那么后面的值必須寫(xiě)滿(mǎn)表所有的字段，一個(gè)都不能漏。比如說(shuō)表中有（學(xué)號(hào)、姓名、性別、班級(jí)編號(hào)、生日）五個(gè)字段，那么你可以寫(xiě)：

insert into t_student values(1,'zhangsan','1','gaosan1ban','1950-10-12');

字段可以省略不寫(xiě)，但是后面的value對(duì)數(shù)量和順序都有要求。

當(dāng)然我們也可以多行添加，如：

insert into t_student(name,sex,classno,birth,no) values ('BaKa愛(ài)','1','gaosan1ban','1950-10-12',2), ('喬峰','2','gaosan1ban','1955-09-28',4);

5.5.3 拷貝表

如果我們想要把查詢(xún)的結(jié)果作為一張新表置于數(shù)據(jù)庫(kù)，我們可以使用語(yǔ)法create table 表名 as select 語(yǔ)句來(lái)完成這個(gè)工作。如：

create table emp1 as select * from emp;

這個(gè)語(yǔ)法是不是和使用格式with 表名 as 查詢(xún)結(jié)果來(lái)創(chuàng)建臨時(shí)表有點(diǎn)相似呢？

如果我們想要把查詢(xún)結(jié)果插入另一張表，我們可以使用格式insert into 表名 查詢(xún)結(jié)果，如：

insert into dept1 select * from dept;

5.5.4 修改表的數(shù)據(jù)

我們前面說(shuō)過(guò)insert的最小操作單位是元組，也就是說(shuō)元組一旦插入元組中的屬性就無(wú)法修改，如若想修改，就要使用本小節(jié)的update關(guān)鍵字。其語(yǔ)法格式為：

update 表名 set 字段名1 = 值1，字段名2 = 值2…where 條件；

其中where條件是必須的，否則這條更新語(yǔ)句會(huì)根據(jù)你修改的東西將表中所有修改的屬性全部更新。如：我們要將前面小節(jié)中創(chuàng)建的表中高三二班的字段值改為高三三班。

update t_student set classno = 'gaosan3ban' where no = 1;

5.5.5 刪除表中數(shù)據(jù)

刪除表中數(shù)據(jù)的語(yǔ)法格式為：delete from 表名 where 條件，需要注意的是where條件也是必須的，否則表中數(shù)據(jù)將全被刪除，但是表依然存在。即：數(shù)據(jù)庫(kù)存留一張空表。

如果需要?jiǎng)h除表我們可以使用5.5.1中講述的drop table if exists 表名進(jìn)行刪除，需要注意的是此時(shí)被刪除的表是可以通過(guò)日志回滾來(lái)尋回被刪除的表的，如果需要永久刪除表可以使用truncate table 表名。

5.5.6 后話(huà)

在實(shí)際開(kāi)發(fā)中，對(duì)于表中的結(jié)構(gòu)去修改實(shí)際情況發(fā)生概率是很低的，除非在建表的時(shí)候你沒(méi)有考慮好，而且就算真的結(jié)構(gòu)安排不合理，我們也是可以用工具完成即可。修改表的結(jié)構(gòu)，實(shí)際上就是對(duì)之前的設(shè)計(jì)進(jìn)行了否定。并且在修改表結(jié)構(gòu)的時(shí)候，其SQL語(yǔ)句不會(huì)出現(xiàn)在java代碼當(dāng)中。

5.7 約束

在5.5.1中我們談?wù)摰膭?chuàng)建表的格式問(wèn)題，當(dāng)時(shí)我們并沒(méi)有深究完整性約束，只是簡(jiǎn)單創(chuàng)建了一張表，那么什么是完整性約束呢？

在創(chuàng)建表的時(shí)候，可以給表的字段添加相應(yīng)的約束，添加約束的目的是為了保證表中數(shù)據(jù)的合法性、有效性、完整性。比如登錄QQ賬號(hào)，你總不可能密碼為空就能登錄吧？你總不可能QQ賬號(hào)是英文的吧？

讓我們引出下面要敘述的一些約束：

• 非空約束（not null）：結(jié)束的字段不能為NULL
• 唯一約束（unique）：約束的字段不能重復(fù)
• 主鍵約束（primary key）：約束的字段不能為NULL，也不能重復(fù)（簡(jiǎn)稱(chēng)PK）
• 外鍵約束（foreign key）：約束的字段不能為NULL，也不能重復(fù)（簡(jiǎn)稱(chēng)FK）
• 檢查約束（check）：控制屬性域

以上的約束在大多數(shù)數(shù)據(jù)庫(kù)是支持的，但是需要注意的是MySQL是沒(méi)有check約束的。

對(duì)于約束，我們可以在指定單個(gè)字段擁有該約束，也可以指定全局約束。

5.7.1 非空約束

我們對(duì)單個(gè)屬性加上非空約束，用一個(gè)例子來(lái)說(shuō)明：

create table t_user(id int,username varchar(255) not null, //對(duì)用戶(hù)名采用了非空約束password varchar(255));

這時(shí)候如果插入語(yǔ)句寫(xiě)了：

insert into t_user(id,password) values(1,'123');

那么其會(huì)報(bào)錯(cuò)：

ERROR 1364 (HY000): Field 'username' doesn't have a default value

5.7.2 唯一性約束

唯一約束修飾的字段具有唯一性，不能重復(fù)，但可以為NULL。因?yàn)镹ULL不是值。如下面為例，我們需要給某字段添加唯一性約束：

create table t_user(id int,username varchar(255) unique //指定用戶(hù)名唯一 );

當(dāng)然，我們可以全局指定約束，即所有字段都擁有該約束：

create table t_user(id int,usercode varchar(255),username varchar(255),unique(usercode,username));

unique（）指的是聯(lián)合起來(lái)不能重復(fù)。也就是新添加的元組要滿(mǎn)足usercode和username和以前添加過(guò)的元組不會(huì)重復(fù)。這種約束我們也叫表級(jí)約束。需要注意的是，對(duì)于大多數(shù)約束來(lái)說(shuō)都擁有表級(jí)約束的功能，而非空約束沒(méi)有表級(jí)約束。

5.7.3 主鍵約束

如果我們要指定某個(gè)字段作為主鍵，我們只需在其后面添加primary key即可，如：

drop table if exists t_user;create table t_user(id int primary key,username varchar(255),email varchar(255));

id是主鍵，因?yàn)樘砑恿酥麈I約束，主鍵字段中的數(shù)據(jù)不能為NULL，也不能重復(fù)。

在數(shù)據(jù)庫(kù)雜談（二）中我們談過(guò)，一個(gè)關(guān)系數(shù)據(jù)庫(kù)的表必須要有主鍵，且主鍵具有唯一性和非空性。主鍵充當(dāng)表中元組的唯一標(biāo)識(shí)，可用于找任何一條元組。

在實(shí)際開(kāi)發(fā)中，主鍵的性質(zhì)決定了主鍵擁有兩大分類(lèi)：自然主鍵和業(yè)務(wù)主鍵。

• 自然主鍵：主鍵值最好就是一個(gè)和業(yè)務(wù)沒(méi)有任何關(guān)系的自然數(shù)。

• 業(yè)務(wù)主鍵：主鍵值和系統(tǒng)的業(yè)務(wù)掛鉤，比如：銀行卡卡號(hào)，身份證號(hào)碼（不推薦用）

上述兩種主鍵中我們并不推薦使用業(yè)務(wù)主鍵，因?yàn)橐坏I(yè)務(wù)改變，主鍵也隨之丟失，舉個(gè)例子，銀行卡號(hào)報(bào)廢，主鍵消失，整條元組也會(huì)消失。

我們可以利用MySQL提供的功能來(lái)添加自然主鍵。

drop table if exists t_user;create table t_user(id int primary key auto_increment, //指定id為自然主鍵username varchar(255));

當(dāng)指定id字段為自然主鍵時(shí)，MySQL會(huì)自動(dòng)維護(hù)一個(gè)從1開(kāi)始遞增的數(shù)字作為主鍵值。如下所示：

mysql> select * from t_user;+----+----------+| id | username |+----+----------+| 1 | a || 2 | b || 3 | c || 4 | d || 5 | e |+----+----------+

當(dāng)然，其他DBMS也提供相似的機(jī)制，如Oracle當(dāng)中提供了一個(gè)自增機(jī)制，叫做序列（sequence），但是我們?yōu)榱苏疹櫝鯇W(xué)者這里不做過(guò)多講解。

5.7.4 外鍵約束

如果要對(duì)某個(gè)屬性添加外鍵約束，只需在全局添加foreign key關(guān)鍵字即可，如：

drop table if exists t_student;drop table if exists t_class;create table t_class(cno int,cname varchar(255),primary key(cno));create table t_student(sno int,sname varchar(255),classno int,foreign key(classno) references t_class(cno)//指定classno為外鍵，其對(duì)應(yīng)t_class表中的cno);

指定外鍵約束后，t_student中的classno字段引用t_class表中的cno字段，此時(shí)t_student表叫做子表，t_class表叫做父表。由于classno對(duì)cno產(chǎn)生依賴(lài)，在對(duì)表做操作的時(shí)候就有嚴(yán)格要求了：

• 刪除數(shù)據(jù)的時(shí)候，先刪除子表，再刪除父表。

• 添加數(shù)據(jù)的時(shí)候，先添加父表，再添加子表。

• 創(chuàng)建表的時(shí)候，先創(chuàng)建父表，再創(chuàng)建子表。

• 刪除表的時(shí)候，先刪除子表，再刪除父表。

5.8 視圖

我們?cè)谶@一章的最開(kāi)始就提到了基表和虛表的概念，實(shí)際上視圖就是虛表，視圖是從一個(gè)或幾個(gè)基本表（或視圖）中導(dǎo)出的虛擬的表。在系統(tǒng)的數(shù)據(jù)字典中僅存放了視圖的定義，不存放視圖對(duì)應(yīng)的數(shù)據(jù)。

數(shù)據(jù)字典后面我們會(huì)做講解，別急。

那知道了視圖的概念，我們?nèi)绾蝿?chuàng)建視圖呢？

//創(chuàng)建視圖 create view myview as select empno,ename from emp;//刪除視圖 drop view myview;

既然視圖叫做虛表，那就意味著視圖本質(zhì)上也是一張表，虛表作為基表的映射。如果我們對(duì)虛表進(jìn)行操作，那么是會(huì)反映到基表上的。視圖可以隱蔽表中的實(shí)現(xiàn)細(xì)節(jié)。保密級(jí)別較高的系統(tǒng)，數(shù)據(jù)庫(kù)只對(duì)外提供相關(guān)的視圖，java程序員只對(duì)視圖對(duì)象進(jìn)行CRUD。視圖實(shí)際上創(chuàng)建后是不會(huì)數(shù)據(jù)冗余的，即不會(huì)在硬盤(pán)上創(chuàng)建一份視圖。他的原理實(shí)際上是查詢(xún)改寫(xiě)。

5.9 數(shù)據(jù)庫(kù)數(shù)據(jù)的導(dǎo)入導(dǎo)出

誒，有些人想說(shuō)我想把寫(xiě)的SQL語(yǔ)句全部導(dǎo)出來(lái)，要等下拷貝去公司用，那么可以使用下面兩個(gè)操作。

這一小節(jié)針對(duì)的是程序員而非學(xué)生，如果是為了應(yīng)付考試無(wú)需理會(huì)該小節(jié)。

導(dǎo)入整個(gè)數(shù)據(jù)庫(kù)

在window的dos命令窗口中執(zhí)行： mysqldump 數(shù)據(jù)庫(kù)名稱(chēng)>導(dǎo)出路徑\完整文件名 -u用戶(hù)名名稱(chēng) -p密碼

導(dǎo)出指定庫(kù)下的某個(gè)表

在window的dos命令窗口中執(zhí)行： mysqldump 數(shù)據(jù)庫(kù)名稱(chēng) 表名>導(dǎo)出路徑\完整文件名 -u用戶(hù)名名稱(chēng) -p密碼

5.10 用戶(hù)管理

5.10.1 用戶(hù)操作

這一部分實(shí)際上作為考試很少考察，但還是需要知道，以防丟分

查詢(xún)用戶(hù)

use mysql select * from user;

創(chuàng)建用戶(hù)

create user '用戶(hù)名'@'主機(jī)名' identified by '密碼';

修改用戶(hù)密碼

alter user '用戶(hù)名'@'主機(jī)名' identified with mysql_native_password by '新密碼';

刪除用戶(hù)

drop user '用戶(hù)名'@'主機(jī)名';

5.10.2 權(quán)限控制

常見(jiàn)權(quán)限表

權(quán)限說(shuō)明

all,all privieges	所有權(quán)限
select	查詢(xún)數(shù)據(jù)
insert	插入數(shù)據(jù)
update	修改數(shù)據(jù)
delete	刪除數(shù)據(jù)
alter	修改表
drop	刪除數(shù)據(jù)庫(kù)/表/視圖
create	創(chuàng)建數(shù)據(jù)庫(kù)/表

查詢(xún)權(quán)限

show grants for '用戶(hù)名'@'主機(jī)名';

授予權(quán)限

grant 權(quán)限列表 on 數(shù)據(jù)庫(kù)名 表名 to '用戶(hù)名'@'主機(jī)名';

撤銷(xiāo)權(quán)限

revoke 權(quán)限列表 on 數(shù)據(jù)庫(kù)名.表名 from '用戶(hù)名'@'主機(jī)名';

多個(gè)權(quán)限之間，使用逗號(hào)分割；

授權(quán)時(shí)，數(shù)據(jù)庫(kù)名和表名可以使用*進(jìn)行通配，代表所有;

總結(jié)

以上是生活随笔為你收集整理的数据库杂谈（五）——关系数据库语言的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。