分組查詢
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
第二個查詢員工的工作記錄表的員工id和工作id。
select employee_id ,job_id from job_history
<br>
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
第二個查詢員工的工作記錄表的員工id和工作id。
select employee_id ,job_id from job_history
<br>
下面分別對其進行集合運算
1、UNION並集運算符
UNION運算符從兩個查詢中返回消除重複之後的結果，注意union和union all的區別。
--union 去重複數據
--union all 不會去重複數據
select employee_id ,job_id from employee
union
select employee_id ,job_id from job_history
輸入結果如下：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
第二個查詢員工的工作記錄表的員工id和工作id。
select employee_id ,job_id from job_history
<br>
下面分別對其進行集合運算
1、UNION並集運算符
UNION運算符從兩個查詢中返回消除重複之後的結果，注意union和union all的區別。
--union 去重複數據
--union all 不會去重複數據
select employee_id ,job_id from employee
union
select employee_id ,job_id from job_history
輸入結果如下：
2、INTERSECT交集運算符
交集就是顯示兩個集合都有數據，如下面的代碼：
select employee_id ,job_id from employee
INTERSECT
select employee_id ,job_id from job_history
運算結果如下：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
第二個查詢員工的工作記錄表的員工id和工作id。
select employee_id ,job_id from job_history
<br>
下面分別對其進行集合運算
1、UNION並集運算符
UNION運算符從兩個查詢中返回消除重複之後的結果，注意union和union all的區別。
--union 去重複數據
--union all 不會去重複數據
select employee_id ,job_id from employee
union
select employee_id ,job_id from job_history
輸入結果如下：
2、INTERSECT交集運算符
交集就是顯示兩個集合都有數據，如下面的代碼：
select employee_id ,job_id from employee
INTERSECT
select employee_id ,job_id from job_history
運算結果如下：
3、MINUS差集運算符
差集和上面介紹的兩個運算不同，它不滿足交換律，也就是說兩天查詢語句先後順序不同，得到的結果是不一樣的。如下面代碼：
select employee_id ,job_id from employee
MINUS
select employee_id ,job_id from job_history
運算結果如下：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
第二個查詢員工的工作記錄表的員工id和工作id。
select employee_id ,job_id from job_history
<br>
下面分別對其進行集合運算
1、UNION並集運算符
UNION運算符從兩個查詢中返回消除重複之後的結果，注意union和union all的區別。
--union 去重複數據
--union all 不會去重複數據
select employee_id ,job_id from employee
union
select employee_id ,job_id from job_history
輸入結果如下：
2、INTERSECT交集運算符
交集就是顯示兩個集合都有數據，如下面的代碼：
select employee_id ,job_id from employee
INTERSECT
select employee_id ,job_id from job_history
運算結果如下：
3、MINUS差集運算符
差集和上面介紹的兩個運算不同，它不滿足交換律，也就是說兩天查詢語句先後順序不同，得到的結果是不一樣的。如下面代碼：
select employee_id ,job_id from employee
MINUS
select employee_id ,job_id from job_history
運算結果如下：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
第二個查詢員工的工作記錄表的員工id和工作id。
select employee_id ,job_id from job_history
<br>
下面分別對其進行集合運算
1、UNION並集運算符
UNION運算符從兩個查詢中返回消除重複之後的結果，注意union和union all的區別。
--union 去重複數據
--union all 不會去重複數據
select employee_id ,job_id from employee
union
select employee_id ,job_id from job_history
輸入結果如下：
2、INTERSECT交集運算符
交集就是顯示兩個集合都有數據，如下面的代碼：
select employee_id ,job_id from employee
INTERSECT
select employee_id ,job_id from job_history
運算結果如下：
3、MINUS差集運算符
差集和上面介紹的兩個運算不同，它不滿足交換律，也就是說兩天查詢語句先後順序不同，得到的結果是不一樣的。如下面代碼：
select employee_id ,job_id from employee
MINUS
select employee_id ,job_id from job_history
運算結果如下：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
第二個查詢員工的工作記錄表的員工id和工作id。
select employee_id ,job_id from job_history
<br>
下面分別對其進行集合運算
1、UNION並集運算符
UNION運算符從兩個查詢中返回消除重複之後的結果，注意union和union all的區別。
--union 去重複數據
--union all 不會去重複數據
select employee_id ,job_id from employee
union
select employee_id ,job_id from job_history
輸入結果如下：
2、INTERSECT交集運算符
交集就是顯示兩個集合都有數據，如下面的代碼：
select employee_id ,job_id from employee
INTERSECT
select employee_id ,job_id from job_history
運算結果如下：
3、MINUS差集運算符
差集和上面介紹的兩個運算不同，它不滿足交換律，也就是說兩天查詢語句先後順序不同，得到的結果是不一樣的。如下面代碼：
select employee_id ,job_id from employee
MINUS
select employee_id ,job_id from job_history
運算結果如下：
分組查詢
一、組函數
組函數的操作是基於一批行進行的，從而每一組得出一個結果。常用的分組函數有：
AVG：求平均值
COUNT：統計數量
MAX：求最大值
MIN：求最小值
SUM：求和
STDDEV：標準差
VARIANCE：方差
示例如下：
--查詢全體員工的平均工資
select round(avg(salary),2) as 平均工資 from employee
--統計全部員工的數量
select count(*) from employee
--統計員工總工資
select sum(salary) from employee
--查詢最高工資，最低工資
select max(salary),min(salary) from employee
select min(salary) from employee where dep_id=2
二、GROUP BY子句
使用GROUP BY子句將表中的行分成幾個小組，然後再進行相關統計。
--統計各個部門的平均工資
select dep_id , avg(salary) from employee group by dep_id
----統計各個部門的平均工資 ,按照平均工資從高到低排列
select dep_id , avg(salary) as 平均工資 from employee group by dep_id order by 平均工資 desc
--多個列的分組統計
select dep_id,grps, avg(nvl(salary,0)) from employee group by dep_id,grps
三、HAVING子句
你無法通過WHERE子句限定組，但是你可以使用HAVING子句限定組，同時不能在WHERE子句中使用組函數。示例代碼如下：
--先做條件查詢 過濾數據後在做分組統計查詢
--統計各個部門所有姓劉的員工的平均工資。按照平均工資從高到低排列
select dep_id,avg(salary) as 平均工資 from employee
where employee_name like '劉%'
group by dep_id
order by 平均工資
-- 對統計後的結果進行條件查詢的時候就需要使用HAVING子句了。
-- 統計各個部門的平均工資，顯示平均工資大於2000的工資。
select dep_id , avg(salary) as 平均工資 from employee group by dep_id having avg(salary) > 2000
四、注意問題。
1、在SELECT列表中的任何列或者表達式不是一個累積函數，則它必須出現在GROPU BY子句中，如下面這樣寫是錯誤的。應為employee_name並沒有參與分組統計。
--統計各個部門的平均工資
select dep_id ，employee_name, avg(salary) from employee group by dep_id
2、對null值的處理，如果有一個用戶的工資的值是null值，那麼在統計的時候會如何處理呢，可能不同的數據庫有不同的處理，這樣就會導致歧義性，因為null值比較特殊，所以我們在設計數據庫的時候就應該儘量限定為非空值，當然如果已經發生了null值的數據，那麼我們也可以使用NVL函數強迫組函數包括空值，顯示說明如何處理空值。
--null 值的處理 nvl （列名，默認值） 如果該列為null，就是用默認值
select avg(nvl(salary,0)) from employee
22.1增刪改
增刪改
作為應用程序的開發人員，對數據庫的最基本的操作就是增刪改查四個操作，昨天我們已經學習了基本的數據庫查詢，今天我們將會學習兩個增刪改操作和高級查詢。增刪改都會改變數據庫表的內容，而且數據一旦提交，就很難恢復，所以這些操作大家必須要謹慎，但是這些操作基本上也比較簡單，記住語法就可以了。
一、插入語句
增加數據就是往表插入數據，基本的語法格式是：
insert into 表名(列名1,列名2,列名3,...) values(值1,值21,值3,...);
該語法一次只插入一行到表中，按照表中列的順序對應的列出其數值，如果需要插入日期和字符數值，那麼要把數值放入單引號中,
當然表的列名可能不會全部列出，那麼該列就會默認設置為NULL值，當然你可以顯式定義然後把NULL賦給這個列。示例代碼如下：
insert into department(DEP_ID,DEP_NAME) values(4,'財務部門');
從另外一張表拷貝行，不要使用VALUES子句，INSERT子句中插入列的數量要和子查詢中的數量相匹配如下面這個例子：
insert into department(DEP_ID,DEP_NAME) select em_id ,dep_name from dep2;
二、更新語句
使用UPDATA語句修改已經存在的行，基本的語法格式是：
update 表名 set 列名1=新值,列名1=新值,列名2=新值 [where condition]
凡是符合條件的都會更新，所以一次更新可以多於一行，如果沒有條件，那麼就表示更新所有的內容。
示例如下：
--把全部部門名稱改為行政部
update department set dep_name='行政部'
所以一般更新操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
update department set dep_name='行政部' where dep_id=2
三、刪除語句
你可以使用DELETE語句從一個表中刪除已經存在的行，基本的語法格式是：
delete from 表名 [where condition]
如果你忽略了WHERE子句，表中所有的行都會被刪除，也就等於清空表的數據了。如果你確定要清空表的內容，那麼你可以直接使用截斷表的語法。示例如下：
--清空表
delete from department
--截斷
truncate department
所以一般刪除操作都會帶上where語句限定條件，如下面這個例子，只把dep_id為2的部門名稱修改了。
delete from department where dep_name='新的部門名稱'
增刪改這些操作未必都能成功執行的，必須符合數據庫的約束條件才能正常提交
集合查詢
一、建立今天課程所需要的數據庫
1、建表
我們先設計今天要操作的表的結構，使用powerDesigner設計的E-R圖如下：
2、生成sql語言如下：
create table department
(
dep_id INT not null,
dep_name VARCHAR(200),
constraint PK_DEPARTMENT primary key (dep_id)
);
create table employee
(
employee_id int not null,
dep_id INT,
employee_num CHAR(10),
employee_name VARCHAR(50),
salary NUMBER(9,2),
job_id VARCHAR(200) not null,
constraint PK_EMPLOYEE primary key (employee_id)
);
create table job_history
(
his_id INT not null,
employee_id int,
job_id VARCHAR(200),
job_date DATE,
constraint PK_JOB_HISTORY primary key (his_id)
);
create table emp_result
(
emp_result_id int not null,
employee_id int,
emp_source_id int,
point NUMBER(5,2),
constraint PK_emp_result primary key (emp_result_id)
);
create table emp_source
(
emp_source_id int not null,
emp_source_name VARCHAR2(100),
constraint PK_emp_source primary key (emp_source_id)
);
alter table employee
add constraint FK_EMPLOYEE_DEPARTME foreign key (dep_id)
references department (dep_id);
alter table job_history
add constraint FK_HIST_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_EMPLOYEE foreign key (employee_id)
references employee (employee_id);
alter table emp_result
add constraint FK_Result_REFERENCE_Source foreign key (emp_source_id)
references emp_source (emp_source_id);
3、初始化數據如下：
insert into department(dep_id,DEP_NAME) values(1,'軟件開發部');
insert into department(dep_id,DEP_NAME) values(2,'銷售部門');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(1,1,'劉德華',2500,'java程序員');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(2,1,'張學友',6000,'DBA');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(3,1,'謝霆鋒',2000,'網頁美工');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(4,1,'奧巴馬',2000,'項目經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(5,2,'劉備',6000,'銷售經理');
insert into employee(employee_id,DEP_ID,EMPLOYEE_NAME,SALARY,JOB_ID) values(6,2,'張飛',1200,'業務員');
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(1,1,'java程序員',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(2,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(3,1,'java程序員',to_date('2012-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(4,1,'java程序員',to_date('2010-1-1','yyyy-mm-dd'));
insert into JOB_HISTORY(HIS_ID,EMPLOYEE_ID,JOB_ID,JOB_DATE) values(5,2,'DBA',to_date('2002-1-1','yyyy-mm-dd'));
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(1,'語文');
insert into emp_source(EMP_SOURCE_ID,EMP_SOURCE_NAME) values(2,'數學');
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(1,1,1,90);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(2,1,2,70);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(3,2,1,50);
insert into emp_result(EMP_RESULT_ID,EMPLOYEE_ID,EMP_SOURCE_ID,POINT) values(4,2,1,40);
大家可以先把表建好，插入初始化數據後再做進行學習。
二、集合查詢
我們先學習第一個比較簡單的內容就是數據庫的集合查詢，這裡集合和我們數學的集合論是一樣的。這裡主要學習的就是並、交、差幾個集合運算。如下圖：
如下面兩個查詢語句，第一個查詢員工表的員工id和工作id。
select employee_id ,job_id from employee
輸入為：
第二個查詢員工的工作記錄表的員工id和工作id。
select employee_id ,job_id from job_history
<br>
下面分別對其進行集合運算
1、UNION並集運算符
UNION運算符從兩個查詢中返回消除重複之後的結果，注意union和union all的區別。
--union 去重複數據
--union all 不會去重複數據
select employee_id ,job_id from employee
union
select employee_id ,job_id from job_history
輸入結果如下：
2、INTERSECT交集運算符
交集就是顯示兩個集合都有數據，如下面的代碼：
select employee_id ,job_id from employee
INTERSECT
select employee_id ,job_id from job_history
運算結果如下：
3、MINUS差集運算符
差集和上面介紹的兩個運算不同，它不滿足交換律，也就是說兩天查詢語句先後順序不同，得到的結果是不一樣的。如下面代碼：
select employee_id ,job_id from employee
MINUS
select employee_id ,job_id from job_history
運算結果如下：