oracle知识点总结

来源：互联网发布：python 画时间轴分钟编辑：程序博客网时间：2024/05/22 09:40

Oracle 9i internet

Oracle 10g grid(网格存储)

Oracle 11g

一.SQL语言的分类
SQL语言共分为四大类：数据查询语言DQL(SELECT)，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL
      数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
      子句组成的查询块：
      SELECT<字段名表>
      FROM<表或视图名>
      WHERE<查询条件>

2 .数据操纵语言DML
      数据操纵语言DML主要有三种形式：
      1)插入：INSERT
      2)更新：UPDATE
      3)删除：DELETE

3. 数据定义语言DDL
      数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：
      CREATETABLE/VIEW/INDEX/SYN/CLUSTER
      || | | |
      表视图索引同义词簇

DDL操作是隐性提交的！不能rollback

4. 数据控制语言DCL
      数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
      数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
      1)GRANT：授权。

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚---ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK;

3) COMMIT [WORK]：提交。

在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。

(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；

(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；

查看Oracle数据库名和实例名

用系统dba身份登录：conn system/briup;

查看数据库名

select name from v$database;

desc v$database;

查看实例名

selectinstance_name from v$instance;

descv$instance;

dir---查询

alter session set nls_language='american';-----修改CMD中的语言环境

@xxx -----导入xxx文件;

sqlplus system/briup ------登录系统管理员账号

conn briup/briup------切换briup账户

desceibe / desc s_emp -----查询表内的元素

1. 定长字符串:char()

变长字符串：varchar()

Oracle特有的：varchar2()

2. date:年月日时分秒世纪毫秒星期季度

时间戳 1970.1.1 ---- 至今所经历的毫秒数

select last_name,salary,commission_pct

from s_emp;

col last_name for a20---规定列的大小为20；

select last_name,start_date,salary

from s_emp;

select * from s_dept;------“*”查询表内的所有信息

col name for a15

select 10+20

from s_emp;

//这个表专门用来做测试的

select 10+20

from dual;

//所有员工每年的工资

select last_name,salary*12

from s_emp;

select last_name,salary*12+1000

from s_emp;

select last_name,(salary+500)*12 as "Total Salary"

from s_emp;

select last_name,(salary+500)*12 "Total Salary"

from s_emp;

select first_name||last_name,salary,start_date

from s_emp;

col first_name||last_name for a30

select first_name||' '||last_name,salary,start_date

from s_emp;

//查询员工信息员工每月工资（考虑提成比例）

//nvl(E1,E2)的功能为：如果E1为null,则函数返回E2,否则返回E1本身

//nvl2(E1,E2,E3) 的功能为：如果E1为null,则函数返回E3, 如果E1不为null,则函数返回E2

select last_name,salary*(1+nvl(commission_pct,0)/100)

from s_emp;

//查询公司所有部门 s_dept

select id,name

from s_dept;

//进行去重 distinct

select distinct name

from s_dept;

select distinct name,region_id

from s_dept;

/-----执行缓冲区里面的上一条sql命令

l -----选择缓冲区里面的内容

对文件的一些操作

saveC:\Users\hxs\Desktop\jd1710.sql ---------在桌面上创建一个文件保存缓存区里面的数据

clear buffer ------清除缓存区

get C:\Users\hxs\Desktop\jd1710.sql -----得到文件里面的内容

edit C:\Users\hxs\Desktop\jd1710.sql ----用本地编辑器打开文件内容

spool ----录制屏幕 spool off----停止录制

spool C:\Users\hxs\Desktop\jd1710.txt

spool off

col----- 设置查询列的样式

CLE[AR] – Clears anycolumn formats. ------清除设置过的样式

FOR[MAT] format– Changes the display of the column using a format model.-----格式控制，更改显示效果

HEA[DING] text – Sets thecolumn heading. -----有关表头的设置

JUS[TIFY] {align}– Aligns the column heading to be left, center, or right.------控制对齐方式

col last_name heading'Employee|name' format a15

select last_name,title from s_emp;

col salary for 999,999.999--------写9小数点前数不够的话不补充，留空白

select salary from s_emp;

col salary for 000,000.000 ----------写0小数点前不够的话用0补充

col salary for $000,000.000 ------$外国货币符号，显示

col salary for L000,000.000---------L指的是中文货币符号，显示

col commission_pct null 0-------null值用0替换

select commission_pct,salary from s_emp;

select salary,last_name, title from s_emp order by salary ;

select salary,last_name, title ,dept_id from s_emp order bysalary,dept_id desc ;

//起别名

select salary*12,last_name name , title ,dept_id froms_emp order by name desc ;

order by 可以写的子项

1.列名（不论在select子句中是否出现过）

2.别名

3.表达式

4.select 子项的编号（从1开始）

select last_name,commission_pct from s_emp order by 2;

select last_name,title,dept_id

from s_emp

where last_name='Magee';

//查询工资是1500-2000之间的员工信息

select last_name,salary

from s_emp

where salary between 1515 and 1550;-----------闭区间

//查询 41 ，43， 39 号部门的员工信息(in 的用法)

select last_name,title,dept_id

from s_emp

where dept_id in (39,41,43);

//查询名字以_开头的员工信息

select last_name,salary

from s_emp

where last_name like '\_%' escape '\';-------转义字符，不解析转义字符后面跟着的那个字符；

//查询没有提成比例的员工信息

select last_name,commission_pct,title

from s_emp

where commission_pct is null;

//查询 41，,43号部门工资范围在1000-1200的员工信息

select last_name,dept_id,salary

from s_emp

where (dept_id=41 or dept_id =43) and salary between 1000 and 1200;

select last_name,title,dept_id

from s_emp

where dept_id in (39,41,43)

order by salary desc;

//有关字符的操作

select lower(last_name) ll,upper(first_name) uf,initcap('helloworld') ih

from s_emp;

select concat(concat(first_name,' '), last_name)

from s_emp;

//查询所有员工last_name后两个字母

select substr(last_name, length (last_name-1), 2)

from s_emp;

//查询名字中包含tom(不区分三个字母的大小写)的员工信息

whereupper(last_name) like '%TOM%';

//四舍五入

selectround(45.932,-2),round(45.932,-1),round(55.932,-2),trunc(45.932,0),trunc(45.932,-1)

from dual;

//哑表 dummy-------做测试的表

select sysdate -----------sysdate 没有参数，称为‘伪列’；

from dual;

select sysdate+10 -------当前时间向后推10天

from dual;

select systimestamp -----详细的日期；

from dual;

select rownum,rowid,last_name

from s_emp;

rownum---显示行号，一般用于分页显示

rownum 使用小于或者小于等于可以查出来

只有使用大于等于1才能查出

等于1的时候能查到

//查询s_emp 第11-20行的数据

select last_name,salary

from s_emp

where rownum <=20;

select MONTHS_BETWEEN('01-9月-95','11-1月-94')

from dual;

//to_date('25-5月-95','DD-MOD-YY')------日期类型转换

select ROUND(to_date('25-5月-95','DD-MON-YY') )

from dual;

selectto_char(sysdate,'YYYY,MM,D,DD,DDD,YEAR,MONTH,ddsp,DAY,DY,HH24:Mi:SSAM')

from dual;

select start_date,sysdate-start_date from s_emp;

//YY和RR（千年虫）的区别？

select to_char(salary,'L999,000.00')

from s_emp;

select to_number('100')

from s_emp;

select to_date('05-9月-17')

from s_emp;

第四章----多表查询

连接查询

多表查询，要查询的数据来自于多张表

一.等值连接和不等值连接（内连接，自然连接）

内连接只适用于查询外键没有null值列的数据

//查询一下员工信息（所在部门的名称）

两表内没有重复的列名

select last_name,title,name

from s_emp,s_dept

where dept_id = id;

两表内有重复的列名（推荐）

select e.last_name,e.title, d.name

from s_emp e,s_dept d

where e.dept_id = d.id;

//查询员工信息，所在部门名称，所在区域名称

select e.last_name, d.name, r.name

from s_emp e, s_dept d, s_regionr

where e.dept_id = d.id and d.region_id = r.id;

二.外连接（左，右，全）

(1)左外连接 -------可以查询到建立不起联系的数据

把左表（join 左边的数据）中的所有数据都查出来，无论是否建立连接

//查询所有员工的信息和部门名称（即使某些员工没部门）

//插入没部门的员工，

insert into s_emp(id,last_name)values(99,'李狗蛋');

select last_name from s_emp;

Select e.last_name, d.name

from s_emp e left outer join s_dept d

on e.dept_id = d.id;

// 简化的左外连接 -----也只能在oracle中使用

select e.last_name, d.name

from s_emp e , s_deptd

wheree.dept_id = d.id(+);

(2)右外连接

把右表（join 右边的数据）中的所有数据都查出来，无论是否建立连接

//查询所有部门员工的信息，即便某些部门没有员工

//插入没员工的部门，

insert into s_dept(id,name) values(99,'miss部');

select e.last_name,d.name

from s_emp e right outerjoin s_dept d

on e.dept_id=d.id;

select e.last_name,d.name

from s_dept d left outerjoin s_emp e

on e.dept_id=d.id;

//简化的右外连接

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id(+)=d.id;

(3)全外连接（没有简化的全外连接）

select e.last_name,d.name

from s_emp e fullouter join s_dept d

one.dept_id=d.id;

三.自连接 ---自己凭空想象的表

自连接----解决问题的思路，一张表分为逻辑上的两张或多张表。

//查询一下员工信息以及每个员工的经理信息

Select id,last_name,manager_id

from s_emp;

select e.id,e.last_name name,m.last_name manager

from s_emp e, s_emp m

where e.manager_id = m.id;

集合操作符----连接的另一实现方式

union union all（全集）

minus(补集) intersect（交集）

//全外连接---中间交集取了一次，未交部分取一次

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id=d.id(+)

union

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id(+)=d.id;

// union all 中间交集取了两次，未交部分取一次

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id=d.id(+)

union all

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id(+)=d.id;

//补集

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id=d.id(+)

minus

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id(+)=d.id;

//取11~20行的数据----过于麻烦，一般不这样做

select rownum,last_name

from s_emp

where rownum <=20

minus

select rownum,last_name

from s_emp

where rownum <=10;

// intersect（交集）

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id=d.id(+)

intersect

select e.last_name,d.name

from s_emp e ,s_dept d

where e.dept_id(+)=d.id;

第五章 Group functions---组函数

Select后面的字段，必须要么包含在group by中，要么包含在having后的聚合函数中

AVG (DISTINCT|ALL|n)

COUNT (DISTINCT|ALL|expr|*)-----统计行数

MAX (DISTINCT|ALL|expr)

MIN (DISTINCT|ALL|expr)

STDDEV (DISTINCT|ALL|n) -----标准差

SUM (DISTINCT|ALL|n)

VARIANCE (DISTINCT|ALL|n) -----方差

组函数

selectavg(salary),min(salary),stddev(salary)

from s_emp;

//max里面也可以比较字符数型的

select max(last_name),min(last_name)

from s_emp;

//count

select count(*)-----统计行数，（）内加列名

from s_emp；

//查询每个部门的平均工资

select dept_id,avg(salary)

from s_emp

group by dept_id;

//查询平均工资大于1100元的部门id

select dept_id,avg(salary)

from s_emp

group by dept_id

havingavg(salary)>1100;-----------having 筛选条件，分组之后的条件筛选

Subqueries----子查询

//查询和Smith在同一个部门的员工信息

select last_name,dept_id

from s_emp

where dept_id in (select dept_id

from s_emp

where last_name= 'Smith');

//查询 11~20行

select r,last_name

from (

select rownumr,last_name

from s_emp

)

where r between 11 and 20;

员工工资低于平均工资的员工的信息？

//查询平均工资大于32号部门的平均工资的部门id？

select avg(salary)

from s_emp

group by dept_id

having dept_id = 32;

select dept_id

from s_emp

group by dept_id

having avg(salary) > (

select avg(salary)

from s_emp

group by dept_id

having dept_id = 32

)

//查询和smith相同的部门并且相同职称员工的信息？

select dept_id,title

from s_emp

where last_name = 'Smith'

select last_name,dept_id,title

from s_emp

where (dept_id,title) = (

select dept_id,title

from s_emp

where last_name = 'Smith'

)

//查询部门内平均工资比公司内平均工资高的部门的员工信息

//公司平均工资

select avg(salary)

from s_emp

//部门id

select dept_id

from s_emp

gourp by dept_id

havingavg(salary)>(

selectavg(salary)

from s_emp

)

// 员工

selectlast_name,dept_id

from s_emp

where dept_id in (

selectdept_id

from s_emp

gourp bydept_id

havingavg(salary)>(

selectavg(salary)

froms_emp

)

//替换变量

Selectlast_name,dept_id

From s_emp

Where dept_id = &var1;

Select &v1,&v2

From &v3

Where &v4 = &v5;

Select last_name,dept_id

From s_emp

Where last_name = ‘&var1’;

1.给替换变量指定默认值

definesvar1 = ‘’’Smith’’’

2.设定默认值之后怎么再次接收别的值

acceptvar1 prompt ‘请输入名字:’

3.用下标来指定替换变量

select last_name,dept_id

from s_emp

where last_name = &1

# 主要

(#) 次要

* 必须的

E-R图转化成表的思维步骤

1 每一个实体要转化成一张表

2 实体与实体之间的关系用表怎么表示

转化成表之后用外键来表示关系

1:1

外键加到任何一张表上

1:n

外键加到多的一方

n:n

构建一张桥表，桥表中两列外键分别指向两张表

3 实体中的属性，转化成表中的列。要考虑属性数量够不够

范式

第一范式：

行和列交叉的地方只有唯一的一个值

Student

id name class xb_id

10 zs 1班 1

系别表

id name

1 机械系

第二范式：

表中的每一列只能通过主键列去唯一标示

student

id name class xb_id xb_name

xb_id xb_name

select xb_name

from student

where id =

第三范式

表中的非主键列不能依赖于其他非主键列

创建表的语句

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr][column_constraint],

[table_constraint]);

drop table customer;

create table customer(

id number(7)constraint cus_id_pk primary key,

name varchar2(25)constraint cus_name_nn not null,

phone varchar2(20) constraintcus_phone_uq unique,

emp_id number(7)constraint cus_emp_id_fk references s_emp(id)

);

insert into customer

values(NULL,'jack','50190390',10);

insert into customer

values(10,'jack','50190290',10);

insert intocustomer

values(11,'jack','50190290',10);

insert into customer

values(12,'jack','50190390',999);

主键约束 -------primarykey

既可以写成列级约束，也可以写成表级约束

drop table customer;

create table customer(

id number(7),

name varchar2(25),

phone varchar2(20),

emp_id number(7),

primary key(id)

);

外键约束------foreign key .. references ..

既可以写成列级约束，也可以写成表级约束

drop table customer;

create table customer(

id number(7),

name varchar2(25),

phone varchar2(20),

emp_id number(7),

primary key(id),

foreign key(emp_id) referencess_emp(id)

);

非空约束------not null

只能写成列级

唯一约束-------unique

既可以写成列级约束，也可以写成表级约束

drop table customer;

create table customer(

id number(7),

name varchar2(25),

phone varchar2(20),

emp_id number(7),

primary key(id),

foreign key(emp_id)

references s_emp(id),

unique(phone)

);

检查型约束--------check

既可以写成列级约束，也可以写成表级约束

commission_pct NUMBER(4,2)

CHECK(commission_pct IN

(10,12.5,15,17.5,20)));

drop table student;

create table briup.student(

gender varchar2(10)

check(gender in('男','女'))

)；

insert into student

values('女');

2 联合主键

多个列组合起来成为主键

多个列的组合唯一，每个列都是非空

create table t_s_tab(

t_id number(7),

s_id number(7),

primary key(t_id,s_id)

)

赋默认值

create tablestu(

id number(7),

name varchar2(25) default'李狗蛋'

);

insert into stu(id)

values(10);

创建表可以使用子查询，原表中只有not null约束会复制到新表中

create table jd1710_emp

select id,last_name,salary

from s_emp;

数据字典表

数据字典表会存放：

oracle所有数据库用户名和密码；

授予用户的权限；

数据库中使用的所有对象的名字；

表上的约束信息；

审计信息；

USER Objectsowned by user 当前用户所拥有的数据对象相关的表

ALL Objects user has access rights 所有人都有操作权限的数据对象相关的表

DBA All database objects 数据库管理员操作的表

V$ Server performance 服务端性能的表

//查询briup用户创建的所有表（表名）

数据字典表，规模庞大。

dictionary

1 通过模糊查询（从需求中提取关键字）从dictionary找到相关的若干张系统表

select table_name

from dictionary

where upper(table_name)like 'USER%TABLE%';

2 使用desc命令查看这些系统表有哪些列

descuser_tables；

3 选取合适的列进行查看

select table_name,num_rows

from user_tables;

//查询briup用户创建的视图

select table_name

from dictionary

where upper(table_name)like 'USER%VIEW%';

USER_TABLES

USER_VIEWS

USER_INDEXS ----和用户相关的索引

USER_CONSTRAINTS -----用户添加的约束

//查询briup用户创建的约束

1. selecttable_name

from dictionary

where upper(table_name)like 'USER% CONSTRAINTS %';

2. select owner,constraint_name, constraint_type,table_name

from user_constraints;

操作数据

一 .--------INSERT

insert into

values

jd1710_emp

insert into jd1710_emp

values(30,'Jack',2000);

insert into jd1710_emp(last_name,id)

values('Tom',31);

create table jd1710_cus(

id number(7) primary key,

name varchar2(20)

);

create table jd1710_ord(

id number(7) primary key,

name varchar2(20),

cus_id number(7) referencesjd1710_cus(id)

);

insert into jd1710_cus

values(1,'Jack');

insert into jd1710_cus

values(2,'Tom');

insert into jd1710_ord

values(1,'食品订单',1);

insert into jd1710_ord

values(2,'餐具订单',1);

插入数据可以写子查询

jd1710_emp

delete from jd1710_emp; ------------删除这个表内的所有数据

insert into jd1710_emp

(id,last_name,salary)

select id,last_name,salary

from s_emp

where salary>1200;

二.--------更新数据

update jd1710_emp

set last_name = 'Jack'

三.---------删除数据

cus ord

1. 级联删除在删除命令后面添加关键字

delete from jd1710_cus

where id = 1 cascade;

2 .创建含有外键表的时候，在外键后面就指定级联删除

drop table jd1710_ord;

create table jd1710_ord(

id number(7) primary key,

name varchar2(20),

cus_id number(7) referencesjd1710_cus(id) on delete cascade

);

insert into jd1710_ord

values(1,'食品订单',1);

insert into jd1710_ord

values(2,'餐具订单',1);

四.-------事务控制

事务：一组sql命令的集合（DML(有关数据的增删改),SELECT(查新语句),DDL(),DCL）

代表业务逻辑中的一个行为，这个行为涉及到的sql命令会有多条

一个事务中可以包含多条DML,SELECT

一个事务中只能包含一条 DDL,DCL

怎么开启事务

只要是执行DML 或者执行 select

ACID-----事物的四个特性

Atomicity 原子性

Consistency 一致性---- 数据一致（事物执行前和事物执行后）

Isolation 隔离性-----数据之间不做信息交换

Durability 持久性事务完成之后事务中所操作的数据会被持久保存

s_emp

李狗蛋

insert into jd1710_emp

values(301,'Terry',1200);

insert into jd1710_emp

values(302,'Terry',1200);

会话

一次的新连接，连接到服务端

commit

提交事务，只有事务提交以后，事务中操作的数据

才能持久化保存，别的会话才能看到

1 oracle 的sqlplus 事务需要手动提交；

2 mysql 的客户端会自动提交事务；

3 jdbc 自动提交事务；

哪些行为能触发事务提交？

1 DDL DCL

2 正常退出sqlplus：exit

DML典型的开启事务

rollback 事务的回滚(事务结束)，在事务没提交之前

都可以使用rollback让数据回归到事

务开启之前的状态。

rollback to 不会让事务结束

savepoint 回滚点，记录某些关键sql语句执行的结果

可以在回退的时候，不恢复原始状态

当事务结束的时候（commit,rollback），

该事物内声明的回滚点全部被删除

update jd1710_emp

set last_name = 'Jack';

//保存回滚点

update jd1710_emp

set last_name = '李狗蛋'

where id = 101;

savepoint p1;

update jd1710_emp

set last_name = '李狗蛋'

where id = 102;

savepoint p2;

update jd1710_emp

set last_name = '李狗蛋'

where id = 201;

五.---------------修改表结构

1.添加新列

alter table jd1710_emp

add (

first_name varchar2(25)unique

);

2.删除列 (考虑外键)

//删除外键，需要有特殊操作

alter table jd1710_cus

drop column id;

alter table jd1710_emp

drop column first_name;

3.修改列

只能修改列的数据类型，

和添加删除not null约束,如果原列中有null

insert into jd1710_ord

values(10,null,null);

alter table jd1710_ord

modify (

name varchar2(25) notnull

);

4.添加约束-------表级约束

not null添加不了，因为not null写不了表级的

alter table jd1710_emp

add constraint emp_salary_uq unique(salary);

起名字

5.删除约束

//从数据字典里

select constraint_name,

constraint_type,table_name

from user_constraints

order by table_name;

alter table jd1710_emp

drop constraint SYS_C004196;

6.删除表

drop table stu;

7.修改表的名字

renamejd1710_emp to j_emp；

8.修改列名

alter table j_emp

rename column salary to money;

9.清空表空间

truncate table j_emp;

1 DDL

delete from j_emp;

1 DML

2 delete 删除数据，不会立刻把数据删除，而是把要删除的数据添加一个标记，select语句不会显示出带有删除标记的数据，从而得到删除数据的效果。

*这就有可能导致表空间不够用，这时就需要使用truncate清除表空间。

10序列

使用方式类似于 Random

new Rondom(). nextInt(10);

序列使用

1 创建序列

2 通过nextval关键字，取得序列中的下一个值

CREATE SEQUENCE name

[INCREMENT BY n] 步长（两个数字之间

的间隔），默认为1

[START WITH n] 从某个数字开始递增，默认值为1

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}] 可以一次拿多个值

create sequence id_seq

increment by 2

start with 10;

currval--- ----------------当前序列

nextval-------------------下一个序列

select id_seq.nextval

from dual;

insert into j_emp

values(id_seq.nextval,'hello',1000);

daul

mysql

create table test(

id int auto increment

)

num

----

6.视图

视图是表的一种映像

create view 视图名

子查询

connect,resource

grantcreate any view to briup; -----创建任何视图

a-------简单视图

创建视图使用子查询，只来自一张表

仅用了where和order by

create view v_emp

select id,last_name,salary

from s_emp

where salary > 1500;

查

select * from v_emp

增删改

1 如果对视图进行操作，会影响原表

update v_emp

set last_name='hello world'

where id = 5;

2 如果对原表进行操作，会影响视图

update s_emp

set last_name='Tom'

where id = 1;

b-------复杂视图

创建视图使用的子查询包含了groupby

或者有连接查询

//查询员工信息以及员工所在部门的名称

create view v_emp_dept

select e.id id,e.last_namename,

e.salary salary,d.namedept_name

from s_emp e,s_dept d

where e.dept_id = d.id;

查---- -----可以随便查

删除修改增加-----不能进行这些操作

or replace---------修改视图

create or replace viewv_emp

select id,last_name,title

from s_emp;

WITH READ ONLY------设置视图为只读

在简单视图后面加WITH READ ONLY

create or replace view v_emp

select id,last_name,title

from s_emp

WITH READ ONLY;

WITH CHECK OPTION --------检查不允许更改建立视图所用的条件列

create or replace view v_emp

select id,last_name,title

from s_emp

where last_name = 'jack';

update v_emp

set last_name ='Tom'

where id =20;

select * from v_emp;-------id为20的那列已经不在了；

create or replace view v_emp

select id,last_name,title

from s_emp

where last_name = 'jack'

WITH CHECK OPTION ;

update v_emp

set id = 201

where id = 19;

删除视图

drop view 视图名

七.索引

索引是添加在列上的一种数据库对象

目的提高针对于此列的查询效率

--------基于规则

--------基于代价

自动创建索引

给你的列添加了主键约束或者唯一性约束，也就所谓的添加了索引

手动添加索引

create index 名字

on 表名(列名)

drop index 名字

补充的知识点——————行列转换

一，下面是表中的内容，请根据查询结果编写sql语句

姓名学科成绩

张三数学 80

张三语文 85

张三英语 83

李四数学 85

李四语文 90

李四英语 87

查询结果

姓名数学语文英语

张三 80 85 83

李四 85 90 87

涉及到行和列的转换 --如果这个行是“语文”，就选此行作为列

//列转行

create table s_test(

name varchar(20),

object varchar2(20),

score int

);

insertinto s_test

values('李四','英语',87);

方法一，

select  name as 姓名,max(case object when'语文'then score else0end) as 语文,

max(case object when'英语'then score else0end ) as 英语,

max(case object when'数学'then score else0end )  as 数学from  s_test

groupby nameorderby name;

只要是组函数都可以

方法二，

selectname,

sum(decode (object, '语文', score, 0))数学,

sum(decode (object,'英语 ',score, 0))语文,

sum(decode (object,'数学',score, 0))英语

froms_test

groupby name;

Pivot是行转列

Unpivot是列转行

/*

pivot(

　　聚合函数(要转成列值的列名)

for 要转换的列

in（目标列名）

*/

select  '姓名', avg(语文) as语文, avg(英语) as英语, Avg(数学) as 数学

from s_testpivot(    avg(Score)

for object     in (语文,英语,数学)    )as NewScoresgroupby Studentorderby Student asc

JDBC

Jdbc-----Java DataBase Connector

jdbc 是sun 公司定义的一套用于连接关系型数据库的标准接口

JDBC是Sun公司制定的一个可以用Java语言连接数据库的技术。

一、JDBC基础知识

JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC为数据库开发人员提供了一个标准的API，据此可以构建更高级的工具和接口，使数据库开发人员能够用纯 Java API 编写数据库应用程序，并且可跨平台运行，并且不受数据库供应商的限制。

1、跨平台运行：这是继承了Java语言的“一次编译，到处运行”的特点；

2、不受数据库供应商的限制：巧妙在于JDBC设有两种接口，一个是面向应用程序层，其作用是使得开发人员通过SQL调用数据库和处理结果，而不需要考虑数据库的提供商；另一个是驱动程序层，处理与具体驱动程序的交互，JDBC驱动程序可以利用JDBCAPI创建Java程序和数据源之间的桥梁。应用程序只需要编写一次，便可以移到各种驱动程序上运行。Sun提供了一个驱动管理器，数据库供应商——如MySQL、Oracle，提供的驱动程序满足驱动管理器的要求就可以被识别，就可以正常工作。所以JDBC不受数据库供应商的限制。

JDBCAPI可以作为连接Java应用程序与各种关系数据库的纽带，在带来方便的同时也有负面影响，以下是JDBC的优、缺点。

优点如下：

操作便捷：JDBC使得开发人员不需要再使用复杂的驱动器调用命令和函数；

可移植性强：JDBC支持不同的关系数据库，所以可以使同一个应用程序支持多个数据库的访问，只要加载相应的驱动程序即可；

通用性好：JDBC-ODBC桥接驱动器将JDBC函数换成ODBC；

面向对象：可以将常用的JDBC数据库连接封装成一个类，在使用的时候直接调用即可。

缺点如下：

访问数据记录的速度受到一定程度的影响；

更改数据源困难：JDBC可支持多种数据库，各种数据库之间的操作必有不同，这就给更改数据源带来了很大的麻烦

二、JDBC连接数据库的流程及其原理

1、在开发环境中加载指定数据库的驱动程序。例如，接下来的实验中，使用的数据库是Oracle，所以需要去下载Oracle支持JDBC的驱动程序(其实这个地方并不需要去官网上下载jdbc驱动，本地安装的Oracle中就有，是一个ojdbc14.jar的文件)；而开发环境是MyEclipse，将下载得到的驱动程序加载进开发环境中(具体示例的时候会讲解如何加载)。

2、在Java程序中加载驱动程序。在Java程序中，可以通过“Class.forName(“指定数据库的驱动程序”)” 方式来加载添加到开发环境中的驱动程序，例如加载Oracle的数据驱动程序的代码为： Class.forName(“oracle.jdbc.driver.OracleDriver”)

3创建数据连接对象：通过DriverManager类创建数据库连接对象Connection。DriverManager类作用于Java程序和JDBC驱动程序之间，用于检查所加载的驱动程序是否可以建立连接，然后通过它的getConnection方法，根据数据库的URL、用户名和密码，创建一个JDBC Connection 对象。如：Connectionconnection = DriverManager.geiConnection(“连接数据库的URL", "用户名","密码”)。其中，URL=协议名+IP地址(域名)+端口+数据库名称；用户名和密码是指登录数据库时所使用的用户名和密码。具体示例创建Oracle的数据库连接代码如下：

conn=DriverManager.getConnection(url, user,password);

4、创建Statement对象：Statement 类的主要是用于执行静态 SQL 语句并返回它所生成结果的对象。通过Connection 对象的createStatement()方法可以创建一个Statement对象。例如：Statement statament = connection.createStatement();

//具体示例创建Statement对象代码如下：

// Statement statamentMySQL=connectMySQL.createStatement();

5、调用Statement对象的相关方法执行相对应的SQL 语句：通过execuUpdate()方法用来数据的更新，包括插入和删除等操作，例如向staff表中插入一条数据的代码：

statement.excuteUpdate( "INSERTINTO staff(name, age, sex,address, depart, worklen,wage)" + " VALUES('Tom1', 321, 'M', 'china','Personnel','3','3000' ) ") ;

通过调用Statement对象的executeQuery()方法进行数据的查询，而查询结果会得到 ResulSet对象，ResulSet表示执行查询数据库后返回的数据的集合，ResulSet对象具有可以指向当前数据行的指针。通过该对象的next()方法，使得指针指向下一行，然后将数据以列号或者字段名取出。如果当next()方法返回null，则表示下一行中没有数据存在。使用示例代码如下：

ResultSetresultSel = statement.executeQuery( "select * from staff" );

6、关闭数据库连接：使用完数据库或者不需要访问数据库时，通过Connection的close() 方法及时关闭数据连接。

JAVA使用JDBC访问数据库的步骤:

1. 得到数据库驱动程序

2. 创建数据库连接

3. 执行SQL语句

4. 得到结果集

5. 对结果集做相应的处理(增,删,改,查)

6. 关闭资源:这里释放的是DB中的资源

package com.briup.jdbc.day1;

importjava.net.ConnectException;

importjava.sql.Connection;

importjava.sql.DriverManager;

importjava.sql.Statement;

publicclass BasicJDBC {

public static void main(String[] args)throws Exception{

//1.注册驱动

//驱动程序：数据库与jvm之间传递数据的程序

// 注册驱动的三种方式：

//a.这个类的静态代码块中，就写了把这个类的对象放到工厂所维护的集合中

//b. OracleDriver构造器中也做了把驱动类的对象放到工厂所维护的集合中

//c. 驱动类的源码中有一段代码 System.getProperty("jdbc.driver");

// 又做了注册驱动的事情

Class.forName("oracle.jdbc.driver.OracleDriver");

//b.neworacle.jdbc.driver.OracleDriver();

// 写在代码中是这么写,那么写在程序外，程序外传参怎么传？如下：

// java -cp xx com.briupxxxx

// -D jdbc.driver=oracle.jdbc.driver.OracleDriver

//c. System.setProperty("jdbc.driver","oracle.jdbc.driver.OracleDriver");

//2.获得连接对象(连接对象不为null,也不抛异常)

// 协议：子协议：别名（底层协议）：@ip：port：XE

// ?key=value&key1=value2

String url ="jdbc:oracle:thin:@localhost:1521:XE";

String user ="briup";

String pwd ="briup";

Connection conn=DriverManager.getConnection(url, user, pwd);

System.out.println(conn);

//3.Statement

//普通的Statement对象 DML DDL DCL

//PerpareStatement对象

// (1) 可变的sql

// (2)批量插入

//CallableStatement对象执行PL/SQL

Statement stat =conn.createStatement();

//4. 执行sql

String sql = "insertinto s_emp(id,last_name) "

+"values(994,'jd1710')";

// execute(sql)

//增删改查

//true 有结果集（执行的是查询语句） false 没结果集（执行的是增删改语句）

// executeQuery(sql)

//查

//executeUpdate(sql)

//增删改

// 返回值：操作过的行数

boolean flag =stat.execute(sql);

//int a =stat.executeUpdate(sql);

System.out.println(flag);

//（可选）5.操作结果集（sql语句为select）

//6.关闭资源

conn.close();

}

三、JDBC应用示例实验

实验步骤：

S1、下载ojdbc14.jar驱动文件，并将该文件放到你的项目中去；

S2、在MyEclipse中的项目中添加Oracle驱动程序：在项目名上右键->Build Path ->Add External Archiver然后选择你刚才放在项目中的文件，点确定即可。

S3、打开Oracle的各项服务，并在Oracle中建一张表。

S4、编写MyEclipse与Oracle的连接程序：

[cpp] view plaincopyprint?<SPANstyle="FONT-SIZE: 16px">import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.* ;

public class JDBC_Test {

//orcl为oracle数据库中的数据库名，localhost表示连接本机的oracle数据库

//1521为连接的端口号

private static Stringurl="jdbc:oracle:thin:@localhost:1521:orcl";

//system为登陆oracle数据库的用户名

private static String user="system";

//manager为用户名system的密码

private static String password="manager";

public static Connection conn;

public static PreparedStatement ps;

public static ResultSet rs;

public static Statement st ;

//连接数据库的方法

public void getConnection(){

try {

//初始化驱动包

Class.forName("oracle.jdbc.driver.OracleDriver");

//根据数据库连接字符，名称，密码给conn赋值

conn=DriverManager.getConnection(url, user, password);

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}

//测试能否与oracle数据库连接成功

public static void main(String[] args) {

JDBC_Test basedao=new JDBC_Test();

basedao.getConnection();

if(conn==null){

System.out.println("与oracle数据库连接失败！");

}else{

System.out.println("与oracle数据库连接成功！");

}

</SPAN>

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.* ;

public class JDBC_Test {

//orcl为oracle数据库中的数据库名，localhost表示连接本机的oracle数据库

//1521为连接的端口号

private static Stringurl="jdbc:oracle:thin:@localhost:1521:orcl";

//system为登陆oracle数据库的用户名

private static String user="system";

//manager为用户名system的密码

private static String password="manager";

public static Connection conn;

public static PreparedStatement ps;

public static ResultSet rs;

public static Statement st ;

//连接数据库的方法

public void getConnection(){

try {

//初始化驱动包

Class.forName("oracle.jdbc.driver.OracleDriver");

//根据数据库连接字符，名称，密码给conn赋值

conn=DriverManager.getConnection(url, user, password);

} catch (Exception e) {

// TODO: handle exception

e.printStackTrace();

}

//测试能否与oracle数据库连接成功

public static void main(String[] args) {

JDBC_Test basedao=new JDBC_Test();

basedao.getConnection();

if(conn==null){

System.out.println("与oracle数据库连接失败！");

}else{

System.out.println("与oracle数据库连接成功！");

}

S5、如果上述的连接已经建立，就可以利用JDBC中的Java API对数据库进行操作了，具体的查询，插入，删除，更新操作如下：

[cpp] view plaincopyprint?<SPANstyle="FONT-SIZE: 16px">代码转载自：http://blog.csdn.net/cxwen78/article/details/6863696

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JDBC_Test {

// 创建静态全局变量

staticConnection conn;

static Statement st;

public static void main(String[] args) {

insert(); //插入添加记录

update(); //更新记录数据

delete(); //删除记录

query(); //查询记录并显示

}

/* 插入数据记录，并输出插入的数据记录数*/

public static void insert() {

conn = getConnection(); // 首先要获取连接，即连接到数据库

try {

String sql = "INSERT INTO staff(name, age, sex,address, depart,worklen,wage)"

+ " VALUES ('Tom1',32, 'M', 'china','Personnel','3','3000')"; // 插入数据的sql语句

st = (Statement) conn.createStatement(); // 创建用于执行静态sql语句的Statement对象

int count = st.executeUpdate(sql); // 执行插入操作的sql语句，并返回插入数据的个数

System.out.println("向staff表中插入 " + count + " 条数据"); //输出插入操作的处理结果

conn.close(); //关闭数据库连接

} catch (SQLException e) {

System.out.println("插入数据失败" + e.getMessage());

}

/* 更新符合要求的记录，并返回更新的记录数目*/

public static void update() {

conn = getConnection(); //同样先要获取连接，即连接到数据库

try {

String sql = "update staff set wage='2200' where name ='lucy'";// 更新数据的sql语句

st = (Statement) conn.createStatement(); //创建用于执行静态sql语句的Statement对象，st属局部变量

int count = st.executeUpdate(sql);// 执行更新操作的sql语句，返回更新数据的个数

System.out.println("staff表中更新 " + count +" 条数据"); //输出更新操作的处理结果

conn.close(); //关闭数据库连接

} catch (SQLException e) {

System.out.println("更新数据失败");

}

/* 查询数据库，输出符合要求的记录的情况*/

public static void query() {

conn = getConnection(); //同样先要获取连接，即连接到数据库

try {

String sql = "select * from staff"; // 查询数据的sql语句

st = (Statement) conn.createStatement(); //创建用于执行静态sql语句的Statement对象，st属局部变量

ResultSet rs = st.executeQuery(sql); //执行sql查询语句，返回查询数据的结果集

System.out.println("最后的查询结果为：");

while (rs.next()) { // 判断是否还有下一个数据

// 根据字段名获取相应的值

String name =rs.getString("name");

int age =rs.getInt("age");

String sex =rs.getString("sex");

String address =rs.getString("address");

String depart =rs.getString("depart");

String worklen =rs.getString("worklen");

String wage =rs.getString("wage");

//输出查到的记录的各个字段的值

System.out.println(name +" " + age + " " + sex + " " + address

+ " " +depart + " " + worklen + " " + wage);

}

conn.close(); //关闭数据库连接

} catch (SQLException e) {

System.out.println("查询数据失败");

}

/* 删除符合要求的记录，输出情况*/

public static void delete() {

conn = getConnection(); //同样先要获取连接，即连接到数据库

try {

String sql = "delete from staff where name = 'lili'";// 删除数据的sql语句

st = (Statement) conn.createStatement(); //创建用于执行静态sql语句的Statement对象，st属局部变量

int count = st.executeUpdate(sql);// 执行sql删除语句，返回删除数据的数量

System.out.println("staff表中删除 " + count +" 条数据\n"); //输出删除操作的处理结果

conn.close(); //关闭数据库连接

} catch (SQLException e) {

System.out.println("删除数据失败");

}

/* 获取数据库连接的函数*/

public static Connection getConnection() {

Connection con = null; //创建用于连接数据库的Connection对象

try {

Class.forName("com.mysql.jdbc.Driver");// 加载Mysql数据驱动

con = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/myuser", "root","root");// 创建数据连接

} catch (Exception e) {

System.out.println("数据库连接失败" + e.getMessage());

}

return con; //返回所建立的数据库连接

}

什么是JDBC?

Java语言访问数据库的一种规范,是一套API

JDBC (Java Database Connectivity)API，即Java数据库编程接口，是一组标准的Java语言中的接口和类，使用这些接口和类，Java客户端程序可以访问各种不同类型的数据库。比如建立数据库连接、执行SQL语句进行数据的存取操作。

JDBC规范采用接口和实现分离的思想设计了Java数据库编程的框架。接口包含在java.sql及javax.sql包中，其中java.sql属于JavaSE，javax.sql属于JavaEE。这些接口的实现类叫做数据库驱动程序，由数据库的厂商或其它的厂商或个人提供。

为了使客户端程序独立于特定的数据库驱动程序，JDBC规范建议开发者使用基于接口的编程方式，即尽量使应用仅依赖java.sql及javax.sql中的接口和类。

JDBC驱动程序:

什么是JDBC驱动程序?

这些是各个数据库厂家根据JDBC的规范制作的JDBC实现类

JDBC驱动程序的四种类型:

1. 第一种类型的驱动程序的实现是通过将JDBC的调用全部委托给其它编程接口来实现的，比如ODBC。这种类型的驱动程序需要安装本地代码库，即依赖于本地的程序，所以便携性较差。比如JDBC-ODBC桥驱动程序

2. 第二种类型的驱动程序的实现是部分基于Java语言的。即该驱动程序一部分是用Java语言编写，其它部分委托本地的数据库的客户端代码来实现。同类型1的驱动一样，该类型的驱动程序也依赖本地的程序，所以便携性较差

3. 第三种类型的驱动程序的实现是全部基于JAVA语言的。该类型的驱动程序通常由某个中间件服务器提供，这样客户端程序可以使用数据库无关的协议和中间件服务器进行通信，中间件服务器再将客户端的JDBC调用转发给数据库进行处理

4. 第四种类型的驱动程序的实现是全部基于JAVA语言的。该类型的驱动程序中包含了特定数据库的访问协议，使得客户端可以直接和数据库进行通信

JDBC类结构:

DriverManager

Driver Driver

Connection Connection

Statement Statement

Resultset Resultset

DriverManager:这个是一个实现类,它是一个工厂类,用来生产Driver对象的

这个类的结构设计模式为工厂方法

Driver:这是驱动程序对象的接口,它指向一个实实在在的数据库驱动程序对象,那么这个数据库驱动程序对象是从哪里来的呢?

DriverManager工厂中有个方法:getDriver(String URL),通过这个方法可以得到驱动程序对象,这个方法是在各个数据库厂商按JDBC规范设计的数据库驱动程序包里的类中静态实现的,也就是在静态块中

Connection:这个接口可以制向一个数据库连接对象,那么如何得到这个连接对象呢?

是通过DriverManager工厂中的getConnection(String URL)方法得到的

Statement:用于执行静态的SQL语句的接口,通过Connection中的createStatement方法得到的

Resultset:用于指向结果集对象的接口,结果集对象是通过Statement中的execute等方法得到的

JAVA使用JDBC访问数据库的步骤:

1. 得到数据库驱动程序

2. 创建数据库连接

3. 执行SQL语句

4. 得到结果集

5. 对结果集做相应的处理(增,删,改,查)

6. 关闭资源:这里释放的是DB中的资源

设置classpath:

1. 在java文件中起的包名一定要是工程基目录下的子目录,classpath:基目录

2. .jar包,需要将这个.jar包的路径包括这个文件的全名添加到classpath中来

Oracle连接字符串的书写格式:

“oracle:jdbc:thin:@ip:1521: 数据库名”,”数据库用户名”,”数据库密码”

简单的例子:

package moudule1.first;

import java.sql.*;

public class FirstJdbc

{

publicstatic void main(String[] args)

{

String sql="select * from yuchen_user";

Connection con=null;

Statement st=null;

ResultSet rs=null;

try

{

Class.forName("oracle.jdbc.driver.OracleDriver");

con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:name","scott","tiger");

st=con.createStatement();

rs=st.executeQuery(sql);

while(rs.next())

{

System.out.println(rs.getInt("id"));

System.out.println(rs.getString("name"));

}

}catch(Exception e)

{

e.printStackTrace();

}finally

{

try

{

con.close();

}catch(Exceptione)

{}

try

{

st.close();

}catch(Exceptione)

{

}

try

{

rs.close();

}catch(Exceptione)

{

}

常用数据库的驱动程序及JDBC URL:

Oracle数据库:

驱动程序包名：ojdbc14.jar

驱动类的名字：oracle.jdbc.driver.OracleDriver

JDBC URL：jdbc:oracle:thin:@dbip:port:databasename

说明：驱动程序包名有可能会变

JDBC URL中黑色字体部分必须原封不动的保留，为该驱动识别的URL格式。红色字体部分需要根据数据库的安装情况填写。其中各个部分含义如下：

dbip –为数据库服务器的IP地址，如果是本地可写：localhost或127.0.0.1。

port –为数据库的监听端口，需要看安装时的配置，缺省为1521。

databasename –为数据库的SID，通常为全局数据库的名字。

举例如果要访问本地的数据库allandb，端口1521，那么URL写法如下：

jdbc:oracle:thin:@localhost:1521:allandb 下载地址如下:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

SQL Server数据库

驱动程序包名：msbase.jar mssqlserver.jarmsutil.jar

驱动类的名字：com.microsoft.jdbc.sqlserver.SQLServerDriver

JDBCURL：jdbc:microsoft:sqlserver://dbip:port;DatabaseName=databasename

说明：驱动程序包名有可能会变

JDBC URL中黑色字体部分必须原封不动的保留，为该驱动识别的URL格式。红色字体部需要根据数据库的安装情况填写。其中各个部分含义如下：

dbip –为数据库服务器的IP地址，如果是本地可写：localhost或127.0.0.1。

port –为数据库的监听端口，需要看安装时的配置，缺省为1433。

databasename –数据库的名字。

举例如果要访问本地的数据库allandb，端口1433，那么URL写法如下：

jdbc: microsoft: sqlserver:@localhost:1433; DatabaseName =allandb

下载地址：http://www.microsoft.com/downloads/details.aspx

MySQL数据库

驱动程序包名：mysql-connector-java-3.1.11-bin.jar

驱动类的名字：com.mysql.jdbc.Driver

JDBCURL：jdbc:mysql://dbip:port/databasename

说明：驱动程序包名有可能会变

JDBC URL中黑色字体部分必须原封不动的保留，为该驱动识别的URL格式。红色字体部需要根据数据库的安装情况填写。其中各个部分含义如下：

dbip –为数据库服务器的IP地址，如果是本地可写：localhost或127.0.0.1。

port –为数据库的监听端口，需要看安装时的配置，缺省为3306。

databasename –数据库的名字。

举例如果要访问本地的数据库allandb，端口1433，那么URL写法如下：

jdbc:mysql://localhost:3306/allandb

下载地址：http://dev.mysql.com/downloads/connector/j/

Access数据库

驱动程序包名：该驱动程序包含在JavaSE中，不需要额外安装。

驱动类的名字：sun.jdbc.odbc.JdbcOdbcDriver

JDBCURL：jdbc:odbc:datasourcename

说明：该驱动只能工作在Windows系统中，首先需要在操作系统中建立一个可以访问Access数据库的本地数据源(ODBC)，如果名字为allandb，那么URL写法如下：

jdbc:odbc:allandb

PreparedStatement接口:

预编译的sql语句对象

作用: 解决了书写sql语句时一些特殊的字符与sql保留字符冲突的问题,非常方便

/**

*知识点:

*PreparedStatement接口及方法的使用

*程序目标:

*java文件:

*PreparedInsert.java:连接数据库,插入一条数据

*JdbcUtil.java:实现一个工具类,功能:1.连接数据库 2.关闭资源

package moudule1.preparedstatement;

import java.sql.*;

import moudule1.com.*;

public class PreparedInsert

{

publicstatic void main(String[] args)

{

String sql="insert into yuchen_user (id,name) values (?,?)";

System.out.println(sql);

Connection con=null;

PreparedStatement ps=null;

try{

con=JdbcUtil.getConnection();

ps=con.prepareStatement(sql);

ps.setInt(1,2);

ps.setString(2,"zhangsan");

ps.executeUpdate();

ps.setInt(1,3);

ps.setString(2,"lisi");

ps.executeUpdate();

}catch(Exception e){

e.printStackTrace();

}finally{

JdbcUtil.close(con,ps);

}

package moudule1.com;

import java.sql.*;

public class JdbcUtil{

publicstatic Connection getConnection() throws Exception{

Class.forName("oracle.jdbc.driver.OracleDriver");

returnDriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:name","scott","tiger");

}

publicstatic void close(Connection con,Statement st){

close(con);

close(st);

}

publicstatic void close(Connection con,Statement st,ResultSet rs){

close(con,st);

close(rs);

}

publicstatic void close(Connection con){

try{

con.close();

}catch(Exception e){

}

public staticvoid close(Statement st){

try{

st.close();

}catch(Exception e){

}

publicstatic void close(ResultSet rs){

try{

rs.close();

}catch(Exception e){

}

数据库的增删改查的例子:

/**

*知识点:

*JDBC+SQL+ORACLE

*程序目标:

*UserDao.java:实现了数据库的增删改查

*JdbcUtil.java:工具类,有连库和关闭资源的方法

package moudule1.idus;

import java.sql.*;

import moudule1.com.*;

public class UserDao{

privateString sql;

privateConnection con;

privateStatement st;

privateResultSet rs;

publicUserDao(){

sql=null;

con=null;

st=null;

rs=null;

}

publicvoid insert(){

sql="insert into yuchen_user (id,name) values(";

sql+="4,'zhouwu')";

System.out.println(sql);

try{

con=JdbcUtil.getConnection();

st=con.createStatement();

st.executeUpdate(sql);

}catch(Exception e){

e.printStackTrace();

}finally{

JdbcUtil.close(con,st);

}

publicvoid delete(){

sql="delete from yuchen_user where id=2";

System.out.println(sql);

try{

con=JdbcUtil.getConnection();

st=con.createStatement();

st.executeUpdate(sql);

}catch(Exception e){

e.printStackTrace();

}finally{

JdbcUtil.close(con,st);

}

publicvoid update(){

sql="update yuchen_user set name='liumang' where id=1";

System.out.println(sql);

try{

con=JdbcUtil.getConnection();

st=con.createStatement();

st.executeUpdate(sql);

}catch(Exception e){

e.printStackTrace();

}finally{

JdbcUtil.close(con,st);

}

publicvoid select(){

sql="select * from yuchen_user";

System.out.println(sql);

try{

con=JdbcUtil.getConnection();

st=con.createStatement();

rs=st.executeQuery(sql);

while(rs.next()){

System.out.println(rs.getInt(1));

System.out.println(rs.getString(2));

}

}catch(Exception e){

e.printStackTrace();

}finally{

JdbcUtil.close(con,st,rs);

}

publicstatic void main(String[] args){

UserDao ud=new UserDao();

ud.select();

ud.insert();

ud.select();

ud.update();

ud.select();

ud.delete();

ud.select();

}

一些常用的方法:

/**

*知识点:

*execute方法,getResultSet(),getUpdateCount()

*程序目标:

*JdbcUtil.java:工具类,连接数据库,关闭资源

*sqlExecutor.java:命令行参数输入sql语句,并执行该语句

package moudule1.fangfa;

import java.sql.*;

import moudule1.com.*;

public class sqlExecutor{

publicstatic void main(String[] args){

Connection con=null;

Statement st=null;

try{

con=JdbcUtil.getConnection();

st=con.createStatement();

boolean str=st.execute(args[0]);

if(str){

ResultSet rs=st.getResultSet();

while(rs.next()){

System.out.println(rs.getInt("id")+":"+rs.getString("name"));

}

rs.close();

}else{

int row=st.getUpdateCount();

System.out.println(row);

}

}catch(Exception e){

e.printStackTrace();

}finally{

JdbcUtil.close(con,st);

}

2.补充
JDBC连接MySQL

加载及注册JDBC驱动程序

Class.forName("com.mysql.jdbc.Driver");

Class.forName("com.mysql.jdbc.Driver").newInstance();

JDBC URL 定义驱动程序与数据源之间的连接

标准语法：

<protocol（主要通讯协议）>:<subprotocol（次要通讯协议，即驱动程序名称）>:<data source identifier（数据源）>

MySQL的JDBC URL格式：

jdbc:mysql//[hostname][:port]/[dbname][?param1=value1][&param2=value2]….

示例：jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password

常见参数：

user 用户名

password 密码

autoReconnect 联机失败，是否重新联机（true/false）

maxReconnect 尝试重新联机次数

initialTimeout 尝试重新联机间隔

maxRows 传回最大行数

useUnicode 是否使用Unicode字体编码（true/false）

characterEncoding 何种编码（GB2312/UTF-8/…）

relaxAutocommit 是否自动提交（true/false）

capitalizeTypeNames 数据定义的名称以大写表示

建立连接对象

Stringurl="jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password";

Connectioncon = DriverManager.getConnection(url);

建立SQL陈述式对象（Statement Object）

Statement stmt= con.createStatement()；

执行SQL语句

executeQuery()

String query = "select* from test";

ResultSetrs=stmt.executeQuery(query);

结果集ResultSet

while(rs.next())

{rs.getString(1);rs.getInt(2);}

executeUpdate()

Stringupd="insert into test (id,name) values(1001,xuzhaori)";

intcon=stmt.executeUpdate(upd);

execute()

示例：

try{
}

catch(SQLException sqle)

{

}

finally

{

}

Java类型和SQL类型技术手册P421

PreparedStatement（预编语句）

PreparedStatement stmt =conn.prepareStatement("insert into test(id,name)values(?,?)");

stmt.setInt(1,id);

stmt.setString(2,name);

注：一旦设定语句的参数值后，就可以多次执行改语句，直到调用clearParameters（）方法将他清除为止

CallableStatement（预储程序）技术手册P430

JDBC2.0使用

ResultSet对象中的光标上下自由移动

Statementstmt =con.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSetrs=stmt.executeQuery("select * from test");

public Statement createStatement(int resultSetType,intresultSetConcuttency) throws SQLException

resultSetType

TYPE_FORWARD_ONLY 只能使用next（）方法。

TYPE_SCROLL_SENSITIVE 可以上下移动，可以取得改变后的值。

TYPE_SCROLL_INSENSITIVE 可以上下移动。

resultSetConcuttency

CONCUR_READ_ONLY 只读

CONCUR_UPDATABLE ResultSet对象可以执行数据库的新增、修改、和移除

直接使用ResultSet对象执行更新数据

新增数据

Statement stmt＝con.createStatement（ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_PUDATABLE）;

ResultSetuprs=stmt.executeQuery("select * from test");

uprs.moveToInsertRow();

uprs.updateInt(1,1001);

uprs.updateString(2,"许召日");

uprs.insertRow;

更新数据

Statement stmt＝con.createStatement（ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_PUDATABLE）;

ResultSetuprs=stmt.executeQuery("select * from test");

uprs.last();

uprs.updateString("name","xuzhaori");

uprs.updateRow;

删除数据

Statement stmt＝con.createStatement（ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_PUDATABLE）;

ResultSetuprs=stmt.executeQuery("select * from test");

uprs.absolute(4);

uprs.deleteRow();

批处理

con.setAutoCommit(false); 关闭自动认可模式

Statement stmt=con.createStatement();

int[] rows;

stmt.addBatch("insert into test values(1001,xuzhaori)");

stmt.addBatch("insert into test values(1002,xuyalin)");

rows=stmt.executeBatch();

con.commit(); 没有任何错误，执行批处理stmt.executeBatch();

JNDI-数据源（Data Source）与连接池（Connection Pool）

Tomcat的JDBC数据源设置技术手册P439

连接池工具－Proxool Var 0.8.3 技术手册P446

设置web.xml

<?xml version="1.0"encoding="ISO-8859-1"?>

<web-appxmlns="http://java.sun.com/xml/ns/j2ee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"

version="2.4">

….

<servlet-name>ServletConfigurator</servlet-name>

<servlet-class>org.logicalcobwebs.proxool.configuration.ServletConfigurator</servlet-class>

<init-param>

<param-name>propertyFile</param-name>

<param-value>WEB-INF/classes/Proxool.properties</param-value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

后端统计端口添加下列

<servlet-name>Admin</servlet-name>

<servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>Admin</servlet-name>

<url-pattern>/Admin</url-pattern>

</servlet-mapping>

….

</web-app>

配置Proxool.properties

jdbc-0.proxool.alias=JSPBook

jdbc-0.proxool.driver-class=com.mysql.jdbc.Driver

jdbc-0.proxool.driver-url=jdbc:mysql://localhost:3306/sample_db?user=root&password=browser&useUnicode=true&characterEncoding=UTF-8

jdbc-0.proxool.maximum-connection-count=10

jdbc-0.proxool.prototype-count=4

jdbc-0.proxool.house-keeping-test-sql=selectCURRENT_DATE

jdbc-0.proxool.verbose=true

jdbc-0.proxool.statistics=10s,1m,1d 后端统计接口添加此行

jdbc-0.proxool.statistics-log-level=DEBUG

使用Proxool连接池

Connection con =DriverManager.getConnection("proxool.JSPBook");

Statement stmt =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

String query = "SELECT * FROMemployee";

ResultSet rs =stmt.executeQuery(query);

阅读全文

0 0