查詢

計算列

select * from emp;
-- *通配符，表示所有的字段
-- from emp 從emp表查詢select empno, ename from emp;
select ename as "員工姓名", sal*12 as "年薪" from emp;-- as可以省略，用于設置字段名
-- 注意用雙引號將字段名括起來，單引號雖然可以但是無法移植select 5 from emp;
-- 輸出一個字段，字段的長度是emp記錄的個數，每行只有一個元素5，無字段名select 5;
-- 輸出一個字段，長度為1，元素為5，無字段名，不推薦，Oracle不知此

在Oracle中字段的別名不允許單引號括起來，但是SQL Server允許。

dictinct

select distinct deptno from emp;
-- distinct 會過濾重復的數據
select distinct comm from emp;
-- distinct 可以過濾重復的null
select distinct comm, deptno from emp;
-- distinct 可以過濾組合
select deptno ,distinct comm from emp;
--error，邏輯上有沖突

between

select * from emp 
where sal >=1500 and sal <= 3000select * from emp
where sal between 1500 and 3000--between 字段元素在某個范圍內

in

屬于若干個孤立的值

select * from emp where sal in (1500, 3000, 5000);
--等價于
--in表示孤立的值
select * from emp
where sal=1500 or sal=3000 or sal=5000;select * from emp where sal not in (1500, 3000, 5000);
--等價于
select * from emp
where sal<>1500 and sal<>3000 and sal<>5000;
--不等于<> !=是等價的，推薦使用第一種

top

select top 2 * from emp;
select top 15 percent * from emp;
--如果不是整數，向上取整，例如2.1->3
--使用top可是實現分頁查詢，Oracle不支持top，應該使用rownum<x的方式
--把工資在1500~3000之間的員工工資最高的兩個人的信息輸出
select top 2 * from emp
where sal between 1500 and 3000
order by sal desc

如果沒有使用order by則默認對主鍵進行排序

null

沒有值，空值

--輸出獎金非空的員工的信息
select * from emp where comm <> null;
--error:null不能參與布爾判斷--可以使用is和not is
select * from emp where comm is null;
--0和null不一樣，null表示空值，0表示整型數字
--任何類型的數據都允許為null--輸出每個員工的姓名 年薪（包含了獎金） comm假設是一年的獎金select ename as '姓名' , sal * 12 + comm as '年薪' from emp;
--error:null值無法參與運算
--任何數字和null運算結果都是nullselect ename as '姓名', sal * 12 + isnull(comm, 0) as '年薪' from emp
--correct
--isnull(字段， 默認值)表示如果字段為空返回默認值

order by

以某個字段排序

select * from emp order by sal 
-- 默認是按照升序排序， asc可以省略
select * from emp order by deptno, sal, comm
--先按照第一個字段排序，再將相同的按照第二個字段排序select * from emp order by deptno, sal, comm desc
--每一個desc或者asc只對一個字段生效
--即為一個字段指定的排序標準不會對其他字段產生影響

NULL值是所有值中最小的
order by常常和top一起使用

模糊查詢

select 字段的集合 from 表名 where 某個字段的名字 like 匹配的條件

匹配的條件通常含有通配符

• %任意0個或者多個字符，例如%A%表示包含字母A的字符串

select * from emp where ename like 'A%';

• _任意的單個字符
• [a-f]a到f之間的任意一個字符
• [a,f]a或者f
• [^a-f]不是a到f之間的字符

select * from emp where ename like '[^A-F]%'

• 如果想要查找%或者_，需要使用轉義字符進行轉義，格式為

select * from emp where name like '%\%%' escape '\'

escape后面跟的字符表示轉義標志字符，也可以為其他的，一般習慣用反斜杠

聚合函數

函數分為單行函數和多行函數

• 單行函數：每一行都進行操作，例如upper() lower()
• 多行函數：對多個行進行操作，例如max()

select LOWER(ename) as '姓名'from emp;select MAX(sal) from emp;

聚合函數的分類
多行函數返回一個值，通常返回統計分組信息

• max()
• min()
• avg()
• count()
  用count可以統計字段元素的個數，重復的也會統計，不包含Null值。如果不想統計重復的，可以使用distinct

select COUNT(deptno) from emp;			--返回14
select COUNT(distinct deptno) from emp;	-- 返回3
select MAX(sal) as '最高工資', MIN(sal) as '最低工資', COUNT(comm) as '獲得年終獎人數' from emp;

需要注意的是多行函數和單行函數不能混合使用。

group by

分組，只能查詢分組后的信息，后面只能是聚合函數或者是分組信息。通俗的講，只能查詢group by后面的和聚合函數。

select deptno, AVG(sal) as '部門平均工資'from empgroup by deptno
select deptno as '部門', job as '職位', AVG(sal) as '平均工資', COUNT(*) as '人數'from empgroup by deptno, joborder by deptno, job--可以根據多個信息分組，會首先根據第一個相等的分類，再根據第二個相等的。

having

對分組之后的信息進行過濾。同樣地，having后面也只能跟分組信息

--輸出部門平均工資大于2000的部門的部門編號 部門的平均工資
select deptno as '部門', AVG(sal) as '平均工資'from empgroup by deptnohaving AVG(sal) > 2000--輸出部門員工數大于3的部門編號和平均工資	
select deptno as '部門編號', AVG(sal) as '平均工資'from empgroup by deptnohaving COUNT(*) > 3

如果我們不使用group by只使用having，則會將每一個記錄都當作一個組進行過濾。

having和where的異同：

• 相同點：都是對數據過濾，保留有效的數據，不允許使用字段的別名
• 不同點：where是對原始記錄過濾，having是對分組之后的記錄過濾。
• 不同點：where不能和聚合函數搭配使用，having必須和聚合函數或者分組信息搭配使用，使用having之后select后面也只能是聚合函數或者分組信息

select COUNT(*)from emp where ename like '%A%'having AVG(sal) > 1500

如果我們使用as指定別名，別名只能用于輸出字段，不能在后面使用
語句的執行順序：

• from從表中迭代取出一個個記錄
  • 使用where判斷是否符合條件，如果不符合條件就舍棄，如果符合條件則執行下面語句
  • 使用group by按照條件對該記錄進行分組，同時計算having和select后面的分組信息

等待分組結束以后

• having循環迭代每個分組是否符合條件
  • 如果符合條件則將select后面的分組信息輸出
  • 如果不符合條件則舍棄該分組

最后對分組用order by進行排序，此時的order by后面同樣只能夠跟分組信息

--將工資大于1500的員工按照部門編號分組，并輸出組中人數大于2的組的部門編號和平均工資
select deptno as '部門編號', AVG(sal) as '平均工資'from emp where sal > 1500group by deptnohaving COUNT(*) > 2

select 的參數的相對位置是固定的，不能變化的。相對位置如下：

-- 把姓名不包含A的所有員工按照部門編號分組
-- 統計輸出部門平均工資大于2000的部門的部門編號 部門平均工資 部門人數select deptno as '部門編號', AVG(sal) as '平均工資', COUNT(*) as '部門人數'from emp where ename not like '%A%'group by deptnohaving AVG(sal) > 2000order by deptno

select語句的基本結構

SELECT select_list[INTO new_table_name]FROM table_name[WHERE search_conditions][GROUP BY group_by_list][HAVING group_conditions][ORDER BY order_list[ASC|DESC]]--其中order_list如果使用了GROUP BY 或者HAVING就必須也是分組信息