2007年3月21日
上午:
一.JDBC原理概述
1，JDBC是一套协议，是JAVA开发人员和数据库厂商达成的协议，也就是由Sun定义一组接口，由数据库厂商来实现，并规定了JAVA开发人员访问数据库所使用的方法的调用规范。
2，JDBC的实现是由数据库厂商提供，以驱动程序形式提供。
3，JDBC在使用前要先加载驱动。
JDBC对于使用者要有一致性，对不同的数据库其使用方法都是相同的。
驱动开发必须要实现Driver接口。
数据库驱动的实现方式
JDBC-ODBC桥接式
JDBC网络驱动，这种方式是通过中间服务器的协议转换来实现的
JDBC+本地驱动，这种方式的安全性比较差。
JDBC驱动，由数据库厂商实现。
二.JDBC的API
java.sql包和javax.sql包
Driver接口(驱动),在加载某一 Driver 类时，它应该创建自己的实例并向 DriverManager 注册该实例。这意味着用户可以通过调用以下程序加载和注册一个驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver")
DriverManager类（驱动管理器），它可以创建连接，它本身就是一个创建Connection的工厂(Factory)。
Connection接口，会根据不同的驱动产生不同的连接
Statement接口，发送sql语句
ResultSet接口（结果集），是用来接收select语句返回的查询结果的。其实质类似于集合。
下午:
三.JDBC应用步骤
1，注册加载一个driver驱动
2，创建数据库连接（Connection）
3，创建一个Statement（发送sql）
4，执行sql语句
5，处理sql结果（select语句）
6，关闭Statement
7，关闭连接Connection。
注意：6，7两个步骤势必须要做的，因为这些资源是不会自动释放的，必须要自己关闭
访问Oracle的数据库的驱动名字叫ojdbc14.jar,要使用这个驱动程序，要先将他加到环境变量CLASSPATH中。
注册加载驱动driver，也就是强制类加载
Class.forName(Driver包名.Driver类名)。
Driver d=new Driver类();//注意:这个方法不能用参数来构造
DriverManager.registerDriver(d);
Oracle的Driver的全名oracle.jdbc.driver.OracleDriver
mysql的Driver的全名com.mysql.jdbc.Driver
SQLServer的Driver的全名com.microsoft.jdbc.sqlserver.SQLServerDriver
创建连接
DriverManager.getConnection(String url,String username,String password);
Connection连接是通过DriverManager的静态方法getConnection(.....)来得到的，这个方法的实质是把参数传到实际的Driver中的connect()方法中来获得数据库连接的。
Oracle的URL值是由连接数据库的协议和数据库的IP地址及端口号还有要连接的数据库的库名（DatebaseName）
Oracle URL的格式
jdbc:oracle:thin:（协议）@XXX.XXX.X.XXX:XXXX（IP地址及端口号）:XXXXXXX（所使用的库名）
例：jdbc:oracle:thin:@192.168.0.20:1521:tarenadb
MySql URL的写法
例： jdbc:mysql://localhost:3306/tarena
SQLServer URL的写法
例：jdbc:microsoft:sqlserver://localhost:1433/test
java -Djdbc.drivers=驱动的完整类名
使用虚拟机参数,加载驱动 -D表示为虚拟机参数赋值
java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver:com.mysql.jdbc.Driver
四.JDBC基本方法
DriverManager:如果有多个驱动可用的话,DriverManager会选择其中一个.
Driver:可以选择固定的驱动
Driver driver = new oracle.jdbc.driver.OracleDriver();
String user = "sd0613";
String password = "sd0613";
Properties prop = new Properties();
prop.setProperty("user",user);
prop.setProperty("password",password);
driver.connect(url,properties);
executeQuery(sqlString);//返回结果集
executeUpdate(sqlString);//返回值为该次操作影响的记录条数,create table返回0
execute(sqlString);
//适用于不知道具体的操作是什么,返回值是boolean类型的
//如果返回值是true,代表执行查询操作;否则代表执行更新操作.
ResultSet
next()方法:
1.判断是否存在下一条记录
2.将游标移向下一条记录
getXXX(字段名或字段序号)//注意:字段序号从1开始
关闭问题:
使用Connection对象获得一个Statement，Statement中的executeQuery(String sql) 方法可以使用select语句查询，并且返回一个结果集 ResultSet通过遍历这个结果集，可以获得select语句的查询结果，ResultSet的next()方法会操作一个游标从第一条记录的前边开始读取，直到最后一条记录。executeUpdate(String sql) 方法用于执行DDL和DML语句，可以update，delete操作。
注意：要按先ResultSet结果集，后Statement，最后Connection的顺序关闭资源，因为Statement和ResultSet是需要连接时才可以使用的，所以在使用结束之后有可能其他的Statement还需要连接，所以不能先关闭Connection。
1.回忆下昨天的一些JDBC的配置
(1) 驱动：
ojdbc14.jar (Oracle)
mysql-connector-java-3.1.11-bin.jar(MySql)
(2) 实现了Driver接口的驱动类(程序中要加载的类)：
jdbc.oracle.driver.OracleDriver (Oracle)
com.mysql.jdbc.Driver (MySql)
(3)连接数据库的URL
jdbc:oracle:thin:@192.168.0.24:1521:tarena (Oracle)
jdbc:mysql://192.168.0.24:3306/test (MySql)
2.PreparedStatement概述
SQL语句传到数据库后，数据库会先对其编译再执行。在使用Statement时，如果要执行一组类似的SQL操作时，这样做效率很低，而且把不同类型的数据直接写在SQL语句中是比较麻烦的。这时应该用PreparedStatement来代替Statement，PreparedStatement 接口继承 Statement，并和他在两方面有所不同：
(1)PreparedStatement 实例包含已编译的 SQL 语句。这就是使语句先“准备好”。包含于 PreparedStatement对象中的SQL 语句可具有一个或多个 IN 参数。IN参数的值在 SQL 语句创建时未被指定。相反的，该语句为每个 IN 参数保留一个问号（“?”）作为占位符。每个问号的值必须在该语句执行之前，通过适当的setXXX 方法来提供。
(2)由于 PreparedStatement 对象已预编译过，所以其执行速度要快于 Statement 对象。因此，多次执行的 SQL 语句经常创建为 PreparedStatement 对象，以提高效率。
作为 Statement 的子类，PreparedStatement 继承了 Statement 的所有功能。另外它还添加了一整套方法，用于设置发送给数据库以取代 IN 参数占位符的值。同时，三种方法 execute()、 executeQuery() 和 executeUpdate() 已被更改以使之不再需要参数。这些方法的 Statement 形式（接受 SQL 语句参数的形式）不应该用于 PreparedStatement 对象。
3.创建 PreparedStatement 对象
以下的代码段（其中 con 是 Connection 对象）创建包含带两个 IN 参数占位符的 SQL 语句的 PreparedStatement 对象：
PreparedStatement pstmt = con.prepareStatement("UPDATE table4 SET m = ? WHERE x = ?");
pstmt 对象包含语句 "UPDATE table4 SET m = ? WHERE x = ?"，它已发送给DBMS，并编译好为执行作好了准备。
4.传递 IN 参数
在执行 PreparedStatement 对象之前，必须设置每个 ? 参数的值。这可通过调用 setXXX 方法来完成，其中 XXX 是与该参数相应的类型。例如，如果参数具有Java 类型 long，则使用的方法就是 setLong。setXXX 方法的第一个参数是要设置的参数的序数位置(从1开始)，第二个参数是设置给该参数的值。例如，以下代码将第一个参数设为 123456789，第二个参数设为 100000000：
pstmt.setLong(1, 123456789);
pstmt.setLong(2, 100000000);
5.ResultSetMetaData
元数据是用来描述数据的数据，ResultSetMetaData就是来描述结果集的列的类型和属性信息，比如可以通过它得到结果集的列数，列名等。具体可在API中查阅java.sql.ResultSetMetaData。
ResultSetMetaData对象可以通过ResultSet对象的getMetaData()来得到。
ResultSetMetaData对象有以下三个方法比较常用：
getColumnCount()：获得实际列数
getColumnName(int colnum)：获得指定列的列名
getColumnType(int colnum)：获得指定列的数据类型(Types里面的类型,存放的是整数)
6.JDBC是持久层的技术，是JAVA连接数据库目前最通用的手段。其他的持久层技术，比如接下来我们要学的Hibernate，底层也是由JDBC实现的。持久层是与业务无关的，具体的业务由业务层完成，当业务层需要和数据库进行交互时，就需要通过持久层来操作。
7.BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
由于System.in是字节流，我们需要把他转成字符流。并用BufferedReader包装后方便我们的操作。
8.为了区分表中不同的数据，我们要给放入表中持久化的每个对象都加上一个唯一的标识，这就是ID，ID是与业务无关的。ID的生成方法有很多，在Oracle数据库中我们一半利用Sequence来生成。
9.读取配置文件时，我们采用Properties对象。它是HashTable的子类，它有个load(InputStream inStream) 的方法可以直接从输入流中读取属性列表(键值对)。getProperty(String key) 方法用指定的键在此属性列表中搜索值。
1.Registering a driver
2.Establishing a connection to the datebase
3.Creating a statement
4.Executing a SQL
5.Processing the results
6.Closing down JDBC objects
JDBC第三天
上午:
一.事务（Transaction）
原子操作:不可再分的操作,一个操作不能再分成比它更细小的操作.
事务是针对原子操作的，要求原子操作不可再分，并且必须同时成功同时失败。
事务就是把一些非原子操作,变成原子操作,由应用服务器来提出要求,由数据库服务器来执行操作.
在JDBC中默认是自动提交的,如果要想使用事务,需要按以下步骤执行:
1.要调用con.setAutoCommite(false)方法(打开事务边界)，把自动提交（commit）置为false。
2.进行正常的数据库操作
3.如果操作成功了可以选择con.commit(),或者操作失败时选择con.roolback()------
（回滚：数据恢复到之前的情况）
注意:打开事务就要关闭自动提交,当不需要再使用事务的时候调用
setAutoCommite(true).
事务性资源（监控完整性）
二.事务并发产生的问题
三种并发产生的后果:
1，脏读:一个事务读取到了另外一个事务没有提交的数据。（Dirty Read）
2，重复读:一个事务读取到了另外一个事务提交的数据。它是要保持在同一时间点上读取到的数据相同，希望在一段时间内的数据是不变的。
3，幻读:一个事务读取到了另外一个事务提交的数据。用同样的操作读取两次,得到的记录数不相同。
三.事务隔离级别
五种控制级别:
TRANSACTION_NONE不使用事务。
TRANSACTION_READ_UNCOMMITTED 允许脏读。
TRANSACTION_READ_COMMITTED防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别----------------------
TRANSACTION_REPEATABLE_READ可以防止脏读和不可重复读，
TRANSACTION_SERIALIZABLE可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率
以上的五个事务隔离级别都是在Connection类中定义的静态常量，使用setTransactionIsolation(int level) 方法可以设置事务隔离级别。
如:con.setTransactionIsolation(Connection.REPEATABLE_READ);
下午:
四.JDBC2.0新特性
1.可滚动特性和可更新特性
JDBC1.0中是指游标的移动的方向和方式是单向,单步(相对)移动,功能比较简单.
JDBC2.0中游标可以双向,相对或者绝对移动.
可滚动结果集:这种结果集不但可以双向滚动，相对定位，绝对定位，并且还可以修改数据信息。
1)滚动特性
定位函数:aaa
boolean absolute(int row)，定位到指定的记录位置。定位成功返回true，不成功返回false。
void afterLast() ，把游标移动到最后一条记录的后面(逻辑位置)。 一定会有的
void beforeFirst() ，把游标移动到第一条记录的前面(逻辑位置)。
//由于第一条记录的前面和最后一条记录的后面这两个位置肯定存在,所以无需判断是否存在,返回值设为void.
boolean first()，把游标定位到第一条记录，相对定位；
boolean last()，把游标定位到最后一条记录 也是相对的概念。
//当结果集为空的时候,这两个方法会返回false.
boolean next()，此方法是使游标向下一条记录移动。
boolean previous() ，此方法可以使游标向上一条记录移动，前提是前面还有记录。
boolean relative(int rows) ，相对定位方法，参数值可正可负，参数为正，游标从当前位置向后移动指定值条记录，参数为负，游标从当前位置向前移动指定值条记录。
判断函数:
ifBeforeFirst()判断是否在在第一条记录之前.
ifAfterLast()判断是否在在最后一条记录之后.
ifFirst()判断是否为第一条记录.
ifLast()判断是否为最后一条记录.
要使用可滚动结果集时，需要一次设置更新特性与滚动特性,不能分开.
1.更新特性常量:
CONCUR_READ_ONLY 只读结果集 (默认的)
CONCUR_UPDATABLE 可更新结果集
2.滚动特性常量:
TYPE_FORWARD_ONLY ，该常量表示指针只能向前移动的 ResultSet 对象的类型。(默认)
双向滚动：
不敏感：TYPE_SCROLL_INSENSITIVE ，该常量指示可滚动但通常不受其他更改影响的 ResultSet 对象的类型。
敏感的：TYPE_SCROLL_SENSITIVE ，该常量指示可滚动并且通常受其他更改影响的 ResultSet 对象的类型。
//敏感:数据库改变,结果集改变.
语法:
Statement st=null;
st=con.createStatement(ReusltSet.TYPE_SCROLL_INSENSITIVE,ResuleSet.CONCUR_UPDATABLE)
在创建Statement的时候就要指定这两个参数,使用Statement,第一个参数代表滚动特性常量,第二个代表更新特性常量
----------------------------------------
2)可更新特性
a.moveToInsertRow();记录当前游标位置,将游标移到和结果集结构类似的缓冲区;
b.使用updateXxx(int column,columnType value)方法来更新指定列数据;
c.使用insertRow() 方法插入记录，加信结果集，更新
d.将游标指回原位，moveToCurrentRow() 。
2，3步，可循环
-----------------------------------------------------
能否使用JDBC2.0 ResultSet的新特性，要看使用的数据库驱动是否支持.
还有只能用于单表且表中有主键字段（可能会是联合主键），不能够有表连接，会取
可更新操作必须满足以下条件:
a.查询只能引用一张表.
b.不能包含任何连接操作.
c.必须把完整的主键查到结果集里面;
d.保证所有字段为非空字段并且没有默认值。
五.数据库元数据:
DatabaseMetaData dbmd = con.getMetaData();//得到数据库元数据
dbmd.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);//判断是否支持可更新操作
六.批量更新
优势:
1.节省传递时间
2.并发处理
PreparedStatement:
1.addBatch() 将一组参数添加到 PreparedStatement对象内部
2.executeBatch() 将一批参数提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。
Statement:
addBatch(String sql)方法会在批处理缓存中加入一条sql语句
executeBatch()执行批处理缓存中的所有sql语句。
注意：PreparedStatement中使用批量更新时，要先设置好参数后再使用addBatch()方法加入缓存。
批量更新中只能使用更新或插入语句
//
Statement stm=con.createStatement(int resultSetType,int resultSetConcurrency);创建的时候就要指明要什么样的结果集。
先可滚，后可更新
boolean absolute (int row)绝对定位，
afterLast()定位到最后一条记录的后面
2007年3月20日
*********************************
** oracle 学习笔记第一天 **
** author Ice Xu (XuBin) **
** date 2006-10-30 **
***********************************
初始化表的位置：
cd $ORACLE_HOME/rdbms cd demo summit2.sql
这个角本可以初始化练习用的表
set LANG = AMERICAN_AMERICA.US7ASCII
*********************************
我们目前使用的是oralce 9i 9201 版本
恢复练习表命令：
sqlplus openlab/open123 @summit2.sql
登陆oracle的命令：
sqlplus 用户名/密码
show user 显示当前登陆的身份.
set pause on
set pause off 分页显示.
oracle中默认日期和字符是左对齐，数字是右对齐
table or view does not exist ; 表或示图不存在
edit 命令用于自动打开vi修改刚修执行过的sql的命令。
修改方法二：
l 3 先定位到行 c /旧串/新串
执行出错时，利用错误号来查错误:
!oerr ora 942 (装完系统后会装一个oerr工具，用于通过错误号来查看错
误的具体信息)
想在sql中执行unix命令时，把所有的命令前加一个!就可以， 或者host( 用
于从sql从切换至unix环境中去)
/*** 初次使用时注意 ****
运行角本时的命令：
先切换到unix环境下，cd $oracle_home cd sqlplus cd demo 下面有两
个角本建表语句。
@demobld.sql
sqlplus nanjing/nanjing @demobid.sql 直接运行角本，后面跟当前目录或
者是绝对路径
保存刚才的sql语句： save 命令 第二次保存时要替换之前的角本
save 文件名 replace
把刚才保的sql重新放入 buffer中
spool 文件名
此命令会把所有的操作存在某个文件中去
spool off
练习1：查看s_emp表中员工的年工资
select first_name , salary*12 salary from s_emp;
给列起别名的命令：
利用关键字 as 或者用空格 "别名" 双引号内大小写敏感保持引号内容
原样输出，如果不加双引号时，默认为大写
拼接字段：
select first_name||last_name "employees" from s_emp ;
oracle中表达字符串用单引号来表达：
select first_name||' '||last_name from s_emp;(在两个字段之间拼接
一个空格)
查看当前用户所有的表：
练习2：(常用于批量更改数据)
set echo off
spool selecttab.sql;
select 'select * from ' || table_name ||' ; ' "table name " from
user_tables;
spool off;
set head off(去除第一行)
set feed off(去除最后一行)
练习3：(查出s_emp表中所有员工的一年的总收入)
select first_name , salary*12*( 1+nvl(commission_pct/100 , 0 ) ) "
year salary " from s_emp;
nvl函数 专用于处理空值的影响.
*******************************************************************
***************************************************************
下午：
column 定义格式化输出
column last_name Heading format a15;
column last_name;
column salary justify left format $99,999.00 ( 定义工资的显示形式
)
$ echo $LANG
zh_CN.hp15CN
$ echo $NLS_LANG
simplified chinese_china.zhs16cgbk
ORDER BY 排序 升序和降序 ASC 升序(默认) DESC 降序
select * from s_emp order by dept_id , salary desc 部门号升序，工
资降序
关键字distinct也会触发排序操作。
过滤操作： where 子句
select * from s_emp where dept_id=42; 查看部门号为42的所有员工
select * from s_emp where salary>1000 查看工资高于1000的所有员工
select salary from s_emp where first_name='Geroge' 找出名字为
Geroge的员工的工资数
select table_name from user_tables where table_name='S_EMP'; 查
某个具体表名时，表名的字符串必须要为大写
日期的默认的格式 DD-MON-RR(天-月-年)
BETWEEN AND 在什么之间 NOT BETWEEN AND
注意区间：[ ]是一个闭区间
IN( LIST) 在某个集合中 NOT IN (list)
空值会有影响 (等于list其中任何一个就行，为提高效率常把比例
高的放在前面)
LIKE 模糊配置 NOT LIKE
通配比较
IS NULL 是空
AND
OR
NOT
练习4:(找出表名以S_开头的所有表)对于一些特殊字符，要用到escape转义,
并不是一定要用\，escape后面定义是什么字符为转义字符，那就用哪个字符
select table_name from user_tables where table_name like 'S\_%'
escape '\';
当有多个条件时，要用逻辑运算符：AND OR
写对where语句:正确的数据类型判断、逻辑运算符
sql函数的作用：
sql函数的分类：单行函数、多行函数
单行函数： (dual 哑表 )
字符函数：
lower 转小写 select lower('SQLPLUS') from dual;-->
对纯字符串处理的时候
upper 转大写 select upper('sqlplus') from dual;
initcap 首字符大写 select initcap('tarena') from dual;
concat 连接字符串 select concat(first_name , last_name)
from s_emp;等效于||
substr 求子串 select substr('tarenasd0603' ,1,6) from
dual; (取前六个字符) select substr('tarenasd0603',-2) from dual;
(取后两个字符)
length 求字符长度 select length('tarena') from dual;
nvl 空值函数 两个参数的类型要匹配，统一的，表示：如果有，
则返回前面的参数，如果没有就返回后面的参数
eg:select first_name,salary from s_emp where lower(first_name)
='george';
select first_name , substr(first_name , -2 ) from s_emp; (查出
s_emp表中所有用户名字的最后两个字符)
默认的是从左向右，如果是-2则表示从右向左数
练习5： select first_name , salary from s_emp where lower
(first_name)='george';
数值函数：
round 函数(四舍五入) select round(45.935, 2) from dual; 不带参
数时默认为0位小数
trunc 函数(截取，不管后面的数字) select trunc(45.995, 1) from
dual;
日期函数：DD-MON-RR ,默认不显示世纪、时、分、秒 日期格式敏感
世纪、年、月、日
sysdate 返回当前系统时间 select sysdate from dual;
更改当前会话的设置格式：
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
select sysdate-1, sysdate+1, sysdate , sysdate+1 from dual; 注意
单位是以天为单位，也可以得到多少小时、多少分钟之后的时间
MONTHS_BETWEEN (DATE1 , DATE2 ) 求两个日期之前相差的月数
add_months(date , 4 ) 在 date上再添加4个月
select round(last_day(sysdate),'month') from dual;
select next_day(sysdate,'FRIDAY') from dual ; 求这个日期的下一个
FRIDAY
last_day 求月的最后一天
round 函数: select round(sysdate, 'MONTH') from dual; 参
数可以为： MONTH YEAR(看上半年还是下半年)
select trunc(last_day(sysdate)+1) from dual;
select add_months(trunc(sysdate, 'MONTH'), 1 ) from dual ;
关于日期的两种形式：
转换函数：
to_char显示日期：
从数字转化为char to_char(date,'格式')
从日期转化为char to_char(date, 'fmt' )
select to_char(sysdate, 'yyyy mm dd hh24:mi:ss') from dual;
select to_char(sysdate, 'fmyyyy mm
dd hh24:mi:ss') from dual;去掉前导名
select to_char(sysdate ,'YEAR MONTH
dy
eg:查出三月分入职的员工：select first_name,start_date from s_emp
where to_char(start_date,'mm')='03';
to_date表达日期：
字符转日期 select to_date('2000 11 20', 'yyyy mm dd ')
from dual;
select round(to_date('10-OCT-06'
,'dd-mon-RR') ) from dual;
to_number
字符转数字
select to_number('10') from dual ;
day2
where 条件一定是根据某个字段来进行过滤操作.
多表连接操作：
两表没有任何关联时会产生迪卡尔机：
select first_name , name from s_emp , s_dept;
等值连接：
练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响）
select first_name , name from s_emp e, s_dept d where e.dept_id=d.id;同时起了别名
select first_name , name from s_emp e, s_dept d where e.dept_id=d.id and e.first_name='George';具体到哪个人所在的部门
练习二：每个员工所在的部门和部门所在的地区
select first_name , name from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;
eg:select first_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id=d.id and d.region_id=r.id;
等值连接：
练习三:找出每个员工和每个员工的工资级别
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
自连接：当一个表的插入行之间有了关系时就发生了(又名：内连接)
select first_name , manager_id from s_emp;
查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id;
外连接：(防止空值时，用（+）的一方会模拟一条记录配置另一方)这就称为外连接,一个记录都不能少；
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id(+);
+号放在哪边就表示在哪边补空，来跟对方来匹配，使得数据一个都不会漏掉，这个例子中的领导有可能会没有(最高领导就再没有领导了，所以就
方法领导的那边)
标准写法：select e.deptno,d.name from emp e,dept d where e.deptno(+)=d.depton and e.depton is null;
查看员工分部的部门：
select distinct(deptno) from emp ;
找出没有员工的部门：(很经典的一个例子,用外连接来解决的标准做法,这是一种方式）
第一步：
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno;
第二步：
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno and e.deptno is null;
组函数（group function)：
group by 分组子句 对分组后的子句进行过滤还可以用having 条件 对分组后的条件进行过滤 where 是对记录进行过滤
avg(distinct | all )求平均值
count(distinct | all )统计
max(distinct | all ） 求最大值
min(distinct | all )求最小值
sum(distinct | all ) 求和
(所有组函数会忽略空值 , avg sum只能作用于数字类型）
求有提成员工的提成的平均值；
select avg(nvl(commission_pct ,0 ) ) from s_emp;
有多少人有提成：
select count( commission_pct ) from s_emp ;
count(*) 用于统计记录数：
select sum(commission_pct)/ count(*) from s_emp;
员工分部在多少个不同的部门：count 默认为作all的动作
select count(dept_id) from s_emp;
select count(distinct dept_id) from s_emp;
求各个部门的平均工资：group by 子句也会触发排序
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;
select dept_id , avg(salary) aa from s_emp group by dept_id ;
注意：group by 子句后面跟有条件只能是查询的结果中的字段,所以我们会人为在结果要加入一些group by 要用的字段
select region_id , count(*) from s_dept 此句会有错
select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确）
求各个部门不同工种的平均工资：
select dept_id , title, avg(salary) from s_emp group by dept_id , title ;
哪些部门的平均工资比2000高:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;
除了42部门以外的部门的平均工资:
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;(此种sql效率要高，先过滤再计算）
where 单行函数。
having 组函数。
求各个部门的平均工资:
// 这样统计不详细
select max(d.name) , avg (s.salary) from s_emp s, s_dept d where s.dept_id=d.id group by d.name;
//****这问题很经典，为了过 oracle sql 语法关而写max(d.name) ***
select max(d.name) , avg(e.salary) , max(r.name) from s_emp e, s_dept d , s_region r where e.dept_id = d.id and d.region_id=r.id group by d.id ;
下午：
关于子查询: Subqueries
找出所有员工中，工资最低的那个员工：( 利用子查询 ）
select first_name, salary from s_emp where salary = ( select min(salary) from s_emp) ;
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;(利用子查询可以解决)
子查询运行的顺序： 先运行子查询再运行主查询 子查询一般出现在运算符的右边
单值运算符：运算后面只能跟一个值
多值运算符：可以对两个以上的值进行操作
查询谁跟Smith干一样的活：
select last_name from s_emp where last_name='Smith';
//下种写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题
select last_name , title from s_emp where title =( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
//这种写法才考虑的比较全面
select last_name , title from s_emp where title in ( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
使用子查询时应注意： 单行子查询返回多个结果时会有错误 single-row subquery returns more than one value
查出哪些员工的工资比平均工资低：
select * from s_emp where salary < ( select avg(salary) from s_emp) ;
哪些部门的平均工资比32部门的平均工资要低：
第一步先查出各个部门的平均工资：
select min(avg(salary ) ) from s_emp group by dept_id;
第二步再查出哪个部门的工资是最低的:
select dept_id, avg(salary) from s_emp group by dept_id having avg(salary) = (select min(avg(salary) ) from s_emp group by dept_id ) ;
哪个部门里没有员工：
select deptno from dept where deptno not in ( select deptno from emp );
哪些人是普通员工：（用子查询形式来做）
select * from s_emp where id not in ( select manager_id from s_emp);
E--R图 实体关系图entity relation
开发流程先进行需求分析，进行系统设计，建表，再进行开发编码,测试最终产品上线试运行。
把软件设计模型转化为数据中的表，设计时要考虑性能的设计
第一范式：最简单的一种建方式，一张表只有一个主键。
第二范式：表的自连接存在原因，一张表，学生表中也有班级的信息。
第三范式：表连接存在的原因，两张表，其中一张表引用其它一张表。
约束：
为了保证数据的一致性，
primary key (pk) 主键约束 不允许有重复和空值(唯一且非空）
foregin key (fk) 外键约束 两张表parent table child table
unique key (uk) 唯一可以为空
not null
数据库设计时的注意：
索引： 为了提高效率而设计的一种与业务无关的
考虑表点用的物理空间：
考虑表之间的关系：
一对多关系： 利用FK+PK实现，多的一方引用外键
一对一关系： 可以利用FK+UK实现，
多对多关系： 通过中间增加一个附加表来实现，附加表利用联合主键来实现，联合起来的主键唯一。
DDL语句：数据库定义语句：
table (表）
view(示图）
sequence(序列号）
index(索引）
创建表语句：
create table [schema].表名 ( 字段名， 字段类型 约束条件); schema 默认就是当前用户,严格来访问表名完整的写法是schema.tablename
数据类型：
表名的命令规则： 首字母为字母，不得超过30个字符
char(size) 定长 不管是否达到最大宽度，都会点最大的宽度。
varchar2(size) 可变长 按实际的字节占用空间
number 所有的数字类型都称为number
number(n, m ) n------n位宽度 m-----小数点后的宽度
number(2,4)小数点后4 位，有效位2位 values(0.0099) 这样可以 values(0.01)这样出错
LONG 2GB 大文本一个表最我只允许定义一个LONG类型(不建议使用）
CLOB 大对象形式存放(在表里只存一个指针)
BLOB 存二进制大对象(声音，图像之类）
default 作用演示：
create table test(c1 number default 10, c2 number);
约束的演示：
主键约束的定义：
create table test(c number primary key ); 列级约束
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
外键约束的定义：(先定义父表，再定义子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
on delete cascade (及联删除，删除父表时子表也跟着删除）
on delete set null (及联删除父表时子表中引用的字段为null）
day3
不给约束起名字时，系统给约束起名时的规律为：数据库用户名_数字(约束名也不能重名）
定义一个约束的两种形式：
列级约束 表级约束
非空约束：
not null (利用desc可能看到）primary key 自动具有非空约束的特点
primary key约束：
主键约束的定义：
第一种定义形式：
create table test(c number primary key ); 列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
foregin key (fk) 外键约束:
(先定义父表，再定义子表）
create table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
check 约束：
create table test(c1 number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .
怎么创建一个角本文件： xxx.sql结尾
执行角本的方法：
在sqlplus环境中执行：@filename.sql
在shell环境中执行： sqlplus nanjing/nanjing @filename.sql
创建表的语法：
create table 表名 ( 字段名 字段类型 约束类型（可选））；
利用已知表建一张新表：注会把非空约束带过来，其它约束要自己添加
create table s_emp_42 as select * from s_emp where dept_id = 42;
只取要表结构，不想要表中数据的建表方式：
create table s_emp_copy as select * from s_emp where 1=2;
(这是一个小技巧,在JDBC的学习中会用到 where 1=1 的形式,注意体会）
查看一张表的约束：( 查数据字典示图)
desc user_constraints;(这个数据字典中会查到相应的信息）
select constraint_name, constraint_type from user_constraints where table_name='S_EMP';
P pk
R fk
C check
U UK
V 这种只定义在示图中(with check option 相当于组示图加了一个约束）
O 也是出现在示图中
非空约束和CHECK都是用C来表示
查看字段约束的方法：
desc user_cons_columns;
select column_name, position from user_cons_columns where constraint_name='S_EMP_ID_PK' ;
position 的含义：联合主键，约束名一样。
user_constraints user_cons_columns 两张表的约束名相等，表名相等，两张表一关联就可以查出所需的信息。
select constraint_name , r_constraint_name from user_constraints where constraint_type='R' and table_name='S_EMP' ;
数据库建立时，数据字典就会建好。
user_constraints; 自己拥有的
all_constraints; 你自己拥有的加上你可以访问的
dba_constraints 所有的
查看当前数据库数据字典的字典（这个示图很重要）
desc dict;
select table_name form dict where table_name like '%cons%;
示图：
user_objects; user_tables;
select distinct object_type from user_objects;
介绍事务的概念：
commit 提交，此时说明前面所有语句都成功执行
rollback 回退操作，此时会恢复至上一次提交时的状态。
savepoint 设置保存点
注意 insert into 后面可以跟子查询
insert into s_emp_42 select * from s_emp where dept_id =42;
UPDATE 修改字段值：
update s_emp set dept_id =10 where id =2 ;
update s_emp set commission_pct =10 ; 没有where条件时说明是改表中所有的值.
注意：如有外键引用时常会出现外键引用值没有找到等错误？
delete 删除记录命令语法：
delete from s_emp where dept_id=42;
delete form s_emp ; 没有where条件时说明删除表中所有的值
注意：如有外键引用时，删除一张表时常会出现不能删除的情况，
原因一 是因为此时正在有人操作表中记录
原因二 此表有其他的表引用，没能设及联删除：
delete 删除一张大表时空间不释放，非常慢是因为占用大量的系统资源，支持回退操作，空间还被这张表占用着。
truncate table 表名 (删除表中记录时释放表空间)
DML 语句：
表级共享锁： 对于操作一张表中的不同记录时，互不影响
行级排它锁：对于一行记录，oracle 会只允许只有一个用户对它在同一时间进行修改操作
wait() 等到行级锁被释放，才进行数据操作
drop一张表时也会对表加锁，DDL排它锁,所以在删除一张表时如果当前还有用户操作表时不能删除表
alter table 命令用于修改表的结构(这些命令不会经常用)：
增加约束：
alter table 表名 add constraint 约束名 primary key (字段）;
解除约束：(删除约束)
alter table 表名 drop primary key（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
alter tbale father drop primary key cascade ; (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
alter table 表名 drop constraint 约束名;
(怎样取一个约束名：1、人为的违反约束规定根据错误信息获取!
2、查询示图获取约束名!)
alter table 表名 disable from primary key ; (相当于把一个表的主键禁用)
alter table 表名 enable primary key ;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）
*******************************************************************
增加字段：
alter table 表名 add(字段字 字段类型)
删除字段：
alter table 表名 drop(字段)
alter tbale 表名 drop column 字段 ; (8i 以后才支持）
给列改名:920才支持
alter table 表名 rename column 旧字段名 to 新字段名;
修改字段
(此时应注意的问题，更改时要看具体值情况之间的转达换， 改为字符类型时，必须要为空)
alter table 表名 modify( 字段，类型)
更改表中的字段：
update 表名 set 字段 ＝ 值 where 条件
更改表名
rename 旧表名 to 新表名 ；
删除表：
trucate table 表名:(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表）
关于oralce中产生序列(sequence):
create sequence 序列名alter system flush shared_pool;
(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值）
sequence 的参数：
increment by n 起始值, start with n 递增量, maxvalue n 最大值, minvalue n 最小值，cycle | no cycle 轮回， cache n 绶存(第一次取时会一次取多少个id存起来）
查看 sequence 示图：
desc user_sequences ;
select sequence_name , cache_size , last_number from user_sequences where sequence_name like 's_';
select 序列名.currval from dual 查看当前的序列数
select 序列名.nextval from dual 查看下一个序列数，它会自动给当前的序列加１
为列：nextval currval
(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)
清空当前会话的内存：
alter system flush shared_pool;（执行此命令要有DBA权限，一般用户执行出错）
修改序列：(此命令不常用，只需了解就行不必深究）
alter sequence 序列名 修改项；
删除序列sequence
drop sequence 序列名;
创建示图： creating views（属于了解知识）
desc user_views;
select text from user_views where view_name='TEST1_V1' ;
示图就相当于一条select 语句,定义了一个示图就是定义了一个sql语句,示图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle 8i 以后的新示图）MV 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题， 主要应用在数据仓库中用要用于聚合表）
使用示图的好处：控制数据访问权限.
如何创建一个示图：
create or replace views test_vi as select * from test1 where c1=1;
此时往表test1（base table 基表）中插入数据时：表中没能变化，示图中的数据发生改变
从示图中插数据时相对应的表会发生改变:
往示图中插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创建时的select语句。
简单示图：能进行DML操作。
复杂示图：来源于多张表，不能执行DML操作。
关于rownum：
rownum 有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。rownum常用于分页显示。
练习：查询出第5条数据和第10条数据之间：
select first_name , rnum from ( select rownum rnum , first_name from s_emp where rownum <=10 ) where rnum between 5 and 10 ;
分面显示：
SELECT * FROM (SELECT a.*, rownum r FROM S_EMP a WHERE r between 5 AND 10 )；
练习：哪些员工的工资比本部门的平均工资高？
select first_name , salary , avgsal from s_emp e , ( select dept_id , avg (salary ) avgsal from s_emp group by dept_id ) a where e.dept_id =a.dept_id and e.salary > a.avgsal;
在示图上加一个 with check option 就相当于给示图加上了约束
create view test_v as select * from test where c =1 with check option ;
同义词:相当于别名的作用(***只需了解***）系统自建的同义词： user_tables
create synonym asd_s_emp for asd_0607.s_emp ;
目的就是为了给asd_0607_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;
create public synonym p_s_emp fro asd_0607.s_emp; 创建公共的同义词，但是要权限.
删除同义词：
drop synonym 同义词名称
创建索引： Creating indexes(概念很重要对系统的性能影响非常大）
建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。truncate 表时索引结构在，但是数据不存在。
full table scan 全表扫描
用索引就是为了快速定位数据：(理解时就以字典的目录为例）
查看表的rowid：
select rowid , first_name from s_emp;
rowid 定义的信息有： object block table
每条记录都有自己的rowid
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的，
创建一个索引：
create index 索引名 on 表名 ( 字段名）;
create insex testindex on test(c1, c2);
哪些字段应该建索引：
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率最高.
替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间
set verify on
set verify off;
相当于开关变量，用于控制是否显示新旧的sql语句
select id ,last_name ,salary from s_emp where title='&job_title';
更改交互的提示信息：
accept p_dname prompt ' 提示信息';
定义变量：
define p_dname='abc';
分页的实现语句：(可以正常运行)
select * from ( select rownum rnum , a.* from (select * from s_emp) a ) where rnum between 5 and 10 ;
-------------------------------------------------------------------------------------------------------------------------
1、关于约束的知识：
primary key约束：
主键约束的定义：
第一种定义形式：
create table test(c number primary key ); 列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
foregin key (fk) 外键约束:
(先定义父表，再定义子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
check 约束：
create table test(c1 number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .
****************************************************************************
2、关于针对表操作的语法知识：
创建表：
create table 表名 ( 字段名1 类型 约束条件， 字段名2 类型 约束条件 ）;
插入数据命令：
方式一：(指定字段名插入数据）
insert into 表名 ( 字段名 ) values ( 数据）；
方式二：
insert into 表名 values(数据1， 数据2）；
修改数据：
update table 表名 set ( 字段名 数据， 字段名 数据）；
****************************************************************************
3、关于alter table 命令知识:
alter table 命令用于修改表的结构(这些命令不会经常用)：
增加约束：
alter table 表名 add constraint 约束名 primary key (字段）;
解除约束：(删除约束)
alter table 表名 drop primary key（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
alter tbale father drop primary key cascade ; (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
alter table 表名 drop constraint 约束名;
(怎样取一个约束名：
a、人为的违反约束规定根据错误信息获取!
b、查询示图获取约束名!)
alter table 表名 disable from primary key ; (相当于把一个表的主键禁用)
alter table 表名 enable primary key ;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）
增加字段：
alter table 表名 add(字段字，字段类型)
删除字段：
alter table 表名 drop(字段)
alter tbale 表名 drop column 字段 ; (8i 以后才支持）
给列改名:920才支持
alter table 表名 rename column 旧字段名 to 新字段名;
修改字段
(此时应注意的问题，更改时要看具体值情况之间的转达换， 改为字符类型时，必须要为空)
alter table 表名 modify( 字段，类型)
更改表中的字段：
update 表名 set 字段 ＝ 值 where 条件
更改表名
rename 旧表名 to 新表名 ；
删除表：
trucate table 表名:(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表）
****************************************************************************
4、关于oralce中产生序列(sequence)
create sequence 序列名alter system flush shared_pool;
(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值）
sequence 的参数：
increment by n 起始值, start with n 递增量, maxvalue n 最大值, minvalue n 最小值，cycle | no cycle 轮回， cache n 绶存(第一次取时会一次取多少个id存起来）
查看 sequence 示图：
desc user_sequences ;
select sequence_name , cache_size , last_number from user_sequences where sequence_name like 's_';
select 序列名.currval from dual 查看当前的序列数
select 序列名.nextval from dual 查看下一个序列数，它会自动给当前的序列加１
为列：nextval currval
(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)
清空当前会话的内存：
alter system flush shared_pool;（执行此命令要有DBA权限，一般用户执行出错）
修改序列：(此命令不常用，只需了解就行不必深究）
alter sequence 序列名 修改项；
删除序列sequence
drop sequence 序列名;
****************************************************************************
5、创建示图： creating views（属于了解知识）
示图就相当于一条select 语句,定义了一个示图就是定义了一个sql语句,示图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle 8i 以后的新示图）MV 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题， 主要应用在数据仓库中用要用于聚合表）
使用示图的好处：控制数据访问权限.
如何创建一个示图：
create or replace views test_vi as select * from test1 where c1=1;
此时往表test1（base table 基表）中插入数据时：表中没能变化，示图中的数据发生改变
从示图中插数据时相对应的表会发生改变:
往示图中插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创建时的select语句。
简单示图：能进行DML操作。
复杂示图：来源于多张表，不能执行DML操作。
关于rownum：
rownum 有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。rownum常用于分页显示。
练习：查询出第5条数据和第10条数据之间：
select first_name , rnum from ( select rownum rnum , first_name from s_emp where rownum <=10 ) where rnum between 5 and 10;
练习：哪些员工的工资比本部门的平均工资高？
select first_name , salary , avgsal from s_emp e , ( select dept_id , avg (salary ) avgsal from s_emp group by dept_id ) a where e.dept_id =a.dept_id and e.salary > a.avgsal;
关于同义词：
同义词:相当于别名的作用(***只需了解***）系统自建的同义词： user_tables
create synonym asd_s_emp for asd_0607.s_emp ;
目的就是为了给asd_0607_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;
create public synonym p_s_emp fro asd_0607.s_emp; 创建公共的同义词，但是要权限.
删除同义词：
drop synonym 同义词名称
****************************************************************************
6、创建索引： Creating indexes(概念很重要对系统的性能影响非常大）
建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。truncate 表时索引结构在，但是数据不存在。
full table scan 全表扫描
用索引就是为了快速定位数据：(理解时就以字典的目录为例）
查看表的rowid：
select rowid , first_name from s_emp;
rowid 定义的信息有： object block table
每条记录都有自己的rowid
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的，
创建一个索引：
create index 索引名 on 表名 ( 字段名）;
create insex testindex on test(c1, c2);
哪些字段应该建索引：
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率最高.
替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间
set verify on
set verify off;
相当于开关变量，用于控制是否显示新旧的sql语句
select id ,last_name ,salary from s_emp where title='&job_title';
更改交互的提示信息：
accept p_dname prompt ' 提示信息';
定义变量：
define p_dname='abc';
posted @
2007-03-20 12:57 sunny 阅读(748) |
评论 (0) |
编辑 收藏
两表没有任何关联时会产生迪卡尔机：
select first_name , name from s_emp , s_dept;
等值连接：
练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响）
select first_name , name from s_emp, s_dept where s_emp.dept_id=s_dept.id;
练习二：每个员工所在的部门和部门所在的地区
select first_name , name from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;
非等值连接
练习三:查出每个员工和每个员工的工资级别)
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
自连接：
select first_name , manager_id from s_emp;
练习四:查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id;7fg
外连接：(防止空值时，用（+）的一方会模拟一条记录配置另一方)这就称为外连接,一个记录都不能少；
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id(+);
练习五：查看员工分部的部门：
select distinct(deptno) from emp ;
找出没有员工的部门：(很经典的一个例子,用外连接来解决的标准做法,这是一种方式，用子查询也可以实现）
第一步：
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno;
第二步：(!L_=N
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno and e.deptno is null;
练习六：查询员工有多少人有提成：
select count( commission_pct ) from s_emp ;
select sum(commission_pct)/ count(*) from s_emp;
练习七：员工分部在多少个不同的部门：
select count(dept_id) from s_emp;
select count(distinct dept_id) from s_emp;
练习八：求各个部门的平均工资：
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;
select dept_id , avg(salary) aa from s_emp group by dept_id ;
//体会下句sql
select region_id , count(*) from s_dept ****此句会有错,请多体会********
select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确）
练习九：求各个部门不同工种的平均工资：
select dept_id , title, avg(salary) from s_emp group by dept_id , title ;
练习十：查询哪些部门的平均工资比2000高:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;
练习十一：除了42部门以外的部门的平均工资:
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;(此种sql效率要高，先过滤再计算）
练习十二：求各个部门的平均工资:
//****这问题很经典，为了过 oracle sql 语法关而写max(d.name)
select max(d.name) , avg(e.salary) , max(r.name) from s_emp e, s_dept d , s_region r where e.dept_id = d.id and
d.region_id=r.id group by d.id ;
关于子查询: Subqueries
练习十三：找出所有员工中，工资最低的那个员工：( 利用子查询 ）
select first_name, salary from s_emp where salary = ( select min(salary) from s_emp) ;
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;
练习十四：查询谁跟Smith的工种一样：
select last_name from s_emp where last_name='Smith';
//下种写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题
select last_name , title from s_emp where title =( select title from s_emp where last_name='Smith' )and
last_name <> 'Smith' ;
//这种写法才考虑的比较全面
select last_name , title from s_emp where title in ( select title from s_emp where last_name='Smith' ) and
last_name <> 'Smith' ;
使用子查询时应注意： 单行子查询返回多个结果时会有错误 single-row subquery returns more than one value(
练习十五：查出哪些员工的工资比平均工资低：
select * from s_emp where salary < ( select avg(salary) from s_emp) ;
哪些部门的平均工资比32部门的平均工资要低：
第一步先查出各个部门的平均工资：
select min(avg(salary ) ) from s_emp group by dept_id;
第二步再查出哪个部门的工资是最低的:
select dept_id, avg(salary) from s_emp group by dept_id having avg(salary) = (select min(avg(salary) ) from
s_emp group by dept_id ) ;
练习十六：哪个部门里没有员工(用子查询的方式来实现）：
select deptno from dept where deptno not in ( select deptno from emp );
posted @
2007-03-20 12:48 sunny 阅读(423) |
评论 (0) |
编辑 收藏SQL*PLUS命令的使用大全
SQL*PLUS命令的使用大全
Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus语句。我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为
sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。
除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。
下面就介绍一下一些常用的sql*plus命令：
1. 执行一个SQL脚本文件
SQL>start file_name
SQL>@ file_name
我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。
2. 对当前的输入进行编辑
SQL>edit
3. 重新运行上一次运行的sql语句
SQL>/
4. 将显示的内容输出到指定文件
SQL> SPOOL file_name
在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。
5. 关闭spool输出
SQL> SPOOL OFF
只有关闭spool输出，才会在输出文件中看到输出的内容。
6．显示一个表的结构
SQL> desc table_name
7. COL命令：
主要格式化列的显示形式。
该命令有许多选项，具体如下：
COL[UMN] [{ column|expr} [ option ...]]
Option选项可以是如下的子句:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
1). 改变缺省的列标题
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- ---------
10 ACCOUNTING NEW YORK
sql>col LOC heading location
sql>select * from dept;
DEPTNO DNAME location
--------- ---------------------------- -----------
10 ACCOUNTING NEW YORK
2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：
Sql>select * from emp
Department name Salary
---------- ---------- ----------
10 aaa 11
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from emp
Employee
Department name Salary
---------- ---------- ----------
10 aaa 11
note: the col heading turn into two lines from one line.
3). 改变列的显示长度：
FOR[MAT] format
Sql>select empno,ename,job from emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
Sql> col ename format a40
EMPNO ENAME JOB
---------- ---------------------------------------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
4). 设置列标题的对齐方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
EMPNO ENAME JOB
---------- ---------------------------------------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边
5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
EMPNO ENAME
---------- ----------------------------------------
7369 SMITH
7499 ALLEN
7521 WARD
6). 格式化NUMBER类型列的显示：
SQL> COLUMN SAL FORMAT $99,990
SQL> /
Employee
Department Name Salary Commission
---------- ---------- --------- ----------
30 ALLEN $1,600 300
7). 显示列值时，如果列值为NULL值，用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text
8). 设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
COL1
--------------------
HOW ARE YOU?
SQL>COL COL1 FORMAT A5
SQL>COL COL1 WRAPPED
COL1
-----
HOW A
RE YO
U?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW A
9). 显示列的当前的显示属性值
SQL> COLUMN column_name
10). 将所有列的显示属性设为缺省值
SQL> CLEAR COLUMNS
8. 屏蔽掉一个列中显示的相同的值
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE SAL < 2500
ORDER BY DEPTNO;
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
9. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。
BREAK ON break_column SKIP n
SQL> BREAK ON DEPTNO SKIP 1
SQL> /
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
10. 显示对BREAK的设置
SQL> BREAK
11. 删除6、7的设置
SQL> CLEAR BREAKS
12. Set 命令：
该命令包含许多子命令：
SET system_variable value
system_variable value 可以是如下的子句之一：
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}
1). 设置当前session是否对修改的数据进行自动提交
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
2)．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句
SQL> SET ECHO {ON|OFF}
3).是否显示当前sql语句查询或修改的行数
SQL> SET FEED[BACK] {6|n|ON|OFF}
默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数
4).是否显示列标题
SQL> SET HEA[DING] {ON|OFF}
当set heading off 时，在每页的上面不显示列标题，而是以空白行代替
5).设置一行可以容纳的字符数
SQL> SET LIN[ESIZE] {80|n}
如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。
6).设置页与页之间的分隔
SQL> SET NEWP[AGE] {1|n|NONE}
当set newpage 0 时，会在每页的开头有一个小的黑方框。
当set newpage n 时，会在页和页之间隔着n个空行。
当set newpage none 时，会在页和页之间没有任何间隔。
7).显示时，用text值代替NULL值
SQL> SET NULL text
8).设置一页有多少行数
SQL> SET PAGES[IZE] {24|n}
如果设为0，则所有的输出内容为一页并且不显示列标题
9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SQL> SET SERVEROUT[PUT] {ON|OFF}
在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。
10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。
SQL> SET WRA[P] {ON|OFF}
当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。
11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。
SQL> SET TERM[OUT] {ON|OFF}
在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。
12).将SPOOL输出中每行后面多余的空格去掉
SQL> SET TRIMS[OUT] {ON|OFF}
13)显示每个sql语句花费的执行时间
set TIMING {ON|OFF}
14．修改sql buffer中的当前行中，第一个出现的字符串
C[HANGE] /old_value/new_value
SQL> l
1* select * from dept
SQL> c/dept/emp
1* select * from emp
15．编辑sql buffer中的sql语句
EDI[T]
16．显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行
L[IST] [n]
17．在sql buffer的当前行下面加一行或多行
I[NPUT]
18．将指定的文本加到sql buffer的当前行后面
A[PPEND]
SQL> select deptno,
2 dname
3 from dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SQL> L 2
2* dname
SQL> a ,loc
2* dname,loc
SQL> L
1 select deptno,
2 dname,loc
3* from dept
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
19．将sql buffer中的sql语句保存到一个文件中
SAVE file_name
20．将一个文件中的sql语句导入到sql buffer中
GET file_name
21．再次执行刚才已经执行的sql语句
RUN
or
/
22．执行一个存储过程
EXECUTE procedure_name
23．在sql*plus中连接到指定的数据库
CONNECT user_name/passwd@db_alias
24．设置每个报表的顶部标题
TTITLE
25．设置每个报表的尾部标题
BTITLE
26．写一个注释
REMARK [text]
27．将指定的信息或一个空行输出到屏幕上
PROMPT [text]
28．将执行的过程暂停，等待用户响应后继续执行
PAUSE [text]
Sql>PAUSE Adjust paper and press RETURN to continue.
29．将一个数据库中的一些数据拷贝到另外一个数据库（如将一个表的数据拷贝到另一个数据库）
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST
create emp_temp
USING SELECT * FROM EMP
30．不退出sql*plus，在sql*plus中执行一个操作系统命令：
HOST
Sql> host hostname
该命令在windows下可能被支持。
31．在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus：
!
sql>!
$hostname
$exit
sql>
该命令在windows下不被支持。
32．显示sql*plus命令的帮助
HELP
如何安装帮助文件：
Sql>@ ?\sqlplus\admin\help\hlpbld.sql ?\sqlplus\admin\help\helpus.sql
Sql>help index
33．显示sql*plus系统变量的值或sql*plus环境变量的值
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name]
LNO
PARAMETERS [parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER
1) . 显示当前环境变量的值：
Show all
2) . 显示当前在创建函数、存储过程、触发器、包等对象的错误信息
Show error
当创建一个函数、存储过程等出错时，变可以用该命令查看在那个地方出错及相应的出错信息，进行修改后再次进行编译。
3) . 显示初始化参数的值：
show PARAMETERS [parameter_name]
4) . 显示数据库的版本：
show REL[EASE]
5) . 显示SGA的大小
show SGA
6). 显示当前的用户名
show user