第4-1章用sql語言訪問數(shù)據(jù)庫

1、2022/7/111第4章 用SQL語言訪問數(shù)據(jù)庫SQL語言的概念SQL語句表達式的建立事務概念事務處理ORACLE9i教程本次課程內(nèi)容: 4.1 SQL語言的概念4.2 通過查詢檢索數(shù)據(jù) Oracle的SQL介紹 1. SQL簡介 早期的數(shù)據(jù)庫管理系統(tǒng)常常為不同范疇的任務使用一個獨立的語言,而SQL將這些任務全部統(tǒng)一在單個語言下了。 SQL成了關系數(shù)據(jù)庫事實上工業(yè)標準。 SQL的特點:數(shù)據(jù)存儲于表給定條件即可查詢交互性好命令類型:數(shù)據(jù)定義語言(DDL)數(shù)據(jù)控制語言(DCL)數(shù)據(jù)操縱語言(DML)2.SQL環(huán)境交互式聯(lián)機使用嵌入式SQL Plus是Oracle系統(tǒng)的支持工具之一,利用它可以完成

2、數(shù)據(jù)的建立、存取、數(shù)據(jù)的操縱、查詢等。操作命令:P74顯示表結(jié)構(gòu)緩沖區(qū)操作文件操作4. SQL命令集（1）查詢命令SELECT 這是用得最普遍的命令，它是SQL的最主要、最核心的功能。查詢的意思是用它來從數(shù)據(jù)庫檢索數(shù)據(jù)查詢數(shù)據(jù)庫 SELECT子句一個SELECT命令通?？梢苑纸鉃槿齻€部分： 查找什么數(shù)據(jù)？從哪里查找？查找條件是什么？故常用以下子句來表達from p1,p2pnwhere group by having order by 條件表達可以用關系運算、邏輯運算和SQL運算符。SELECT命令的常用形式如下: select 列1，列2列N from 表1，表N 【where 條件】 【o

3、rder by 列1asc|desc列2asc|desc】;Where子句的比較運算符：，, , , =Where子句的比較運算符:AND, OR, NOT, IN, NOT IN, EXISTSdesc scott.emp;desc scott.dept;顯示表結(jié)構(gòu):顯示全部/部分字段:select * from scott.emp;select empno,ename,job from scott.empselect distinct job from scott.emp簡單條件查詢:select empno,ename,job from scott.emp where job=MANAG

4、ERselect empno,ename,sal from scott.emp where sal=CLERK and sal=CLERK or sal=2000select empno,ename,job from scott.emp where not job=CLERK排序查詢:select empno,ename,job from scott.emp where job=CLERK order by job asc,sal descselect empno,ename,job,sal from scott.emp group by job,empno,ename,sal having

5、sal=2000select empno,ename,job,sal from scott.emp where sal=(select sal from scott.emp where ename=WARD);簡單嵌套查詢:select emp.empno,emp.ename,emp.job,emp.sal from scott.empwhere sal in (select sal from scott.emp where ename=WARD);帶in的嵌套查詢:select emp.empno,emp.ename,emp.job,emp.sal from scott.empwhere s

6、al any(select sal from scott.emp where job=MANAGER);帶any的嵌套查詢:select sal from scott.emp where job=MANAGERselect emp.empno,emp.ename,emp.job,emp.sal from scott.empwhere sal 2975 or sal2850 or sal2450;select emp.empno,emp.ename,emp.job,emp.sal from scott.empwhere sal =some(select sal from scott.emp wh

7、ere job=MANAGER);帶some的嵌套查詢:select sal from scott.emp where job=MANAGERselect emp.empno,emp.ename,emp.job,emp.sal from scott.empwhere sal =2975 or sal=2850 or sal=2450;select emp.empno,emp.ename,emp.job,emp.sal from scott.empwhere sal all(select sal from scott.emp where job=MANAGER);帶all的嵌套查詢:select

8、 sal from scott.emp where job=MANAGERselect emp.empno,emp.ename,emp.job,emp.sal from scott.empwhere sal 2975 and sal2850 and sal2450;select emp.empno,emp.ename,emp.job,emp.sal from scott.emp,scott.deptwhere exists(select * from scott.emp where scott.emp.deptno=scott.dept.deptno);帶exists的嵌套查詢:(select

9、 deptno from scott.emp)union(select deptno from scott.dept);并操作的嵌套查詢:(select deptno from scott.emp)intersect(select deptno from scott.dept);交操作的嵌套查詢:(select deptno from scott.dept)minus(select deptno from scott.emp);差操作的嵌套查詢:select mgr, mgr/100,ceil(mgr/100) from scott.emp;Ceil函數(shù):select mgr, mgr/100

10、,floor(mgr/100) from scott.emp;floor函數(shù):select mgr, mod(mgr,1000), mod(mgr,100), mod(mgr,10) from scott.emp;mod函數(shù):select mgr, power(mgr,2),power(mgr,3) from scott.emp;power函數(shù):select mgr, round(mgr/100,2),round(mgr/1000,2) from scott.emp;round函數(shù):select mgr, mgr-7800,sign(mgr-7800) from scott.emp;sign函

11、數(shù):select avg(mgr) 平均薪水 from scott.emp;avg函數(shù):select count(*) 記錄總數(shù) from scott.emp;count函數(shù):select count(distinct job ) 工作類別總數(shù) from scott.emp;select min(sal) 最少薪水 from scott.emp;min函數(shù):select max(sal) 最高薪水 from scott.emp;max函數(shù):select sum(sal) 薪水總和 from scott.emp;sum函數(shù):小結(jié)本次課程主要介紹了結(jié)構(gòu)化查詢語言的概念，主要功能和基本應用方法,相應的開發(fā)工具SQL Plus及表的操作1、創(chuàng)建用戶的SQL語句是_Create user_,更