第一部分 基本语法
//拼接表字段
select id || 'is' || name from admin
select * from emp where ename like '%s%';//模糊查询
select * from emp where mgr is null ; //查询表列为空的
to_date('2011-12-12','yyyy-mm-dd') //插入时间格式 to_date()
//给表字段取别名
select id as 这是ID , name as "这是 名字" from admin
//清除重复出现的值
select distinct id,name from admin
//用户定义降序
select * from admin order by id desc //asc升序 //desc降序
//查询当前时间
select sysdate from admin
第二部分 字符函数
//截取字符串
select substr (name,1,1) as 用户名 from admin //(1,1)表示第一个开始,和第一个数
//取出字段的长度
select length(name) from admin
//取出字符串为’张’当前所在的位置为几，找不到则为0 … 区分大小写
select name ,instr(name,'张') from admin
// lpad左侧补全, rpad右侧补全
select lpad(name,10, '-') from admin
select rpad(name,10, '-') from admin
//给字段截取前后空格
select trim(name) from admin
//给字段为张的全部替换为zhang
select name ,replace(name,'张','zhang') from admin
第三部分 数值函数
第四部分 表达式语法
//case表达式语句 给用户区别名用法,相当与if else
select id,name,
case id when 1 then '优'
when 2 then '良'
when 3 then '差'
else '未知'
end 部门
from admin
//decode()函数给用户区别名用法,与上面意思一个样
select id,name,
decode(id,1,'优',2,'良',3,'差','未知') as 成绩
from admin
//函数嵌套,单行函数可以嵌套使用,嵌套层次无限制
select id,name,initcap(trim(rpad(name,10,'-'))) from admin
第五部分 分组函数
//函数里面可以是字段名,也可以是表达式,日期类型
select avg(id) as 平均数,max(id) as 最大数,min(id) as 最小数,sum(id) as 总和 from admin
//查询总数,不能有重复和不能为空的id总数
select count(distinct(id)) as 总数 from admin
//求总和,如果id字段为空,则设置字段为0. 然后用总和除以总数最后得出结果
select sum(nvl(id,0)) / count(id) as 总和除以总数 from admin
//查询id为1的数量有多少个 , 根据平均分排序
select count(id) as 记录数 from admin where id =1 group by id having id=1 order by avg(id)
//查询id大于3 having筛选 id<5 之间的数总数
select count(id) as 记录数 from admin where id > 3 group by id having id < 5
第六部分 多表连接
//emp表和dep表结构
--emp表,
create table emp(empNo number,eName varchar2(20), sal number, deptNo number);
insert into emp(empNo,eName,sal,deptno) values(7369,'smith',800,20);
insert into emp values(7499,'allen',1600,30);
insert into emp values(7521,'ward',1250,30);
insert into emp values(7566,'jones',2975,20);
insert into emp values(7654,'martin',1250,30);
insert into emp values(7902,'ford',300,20);
insert into emp values(7934,'miller',1300,10);
drop table emp
select * from emp
--dep表
create table dep(deptNo number,dName varchar2(20),loc varchar2(20));
insert into dep values(10,'accounting','new york')
insert into dep values(20,'research','dallas')
insert into dep values(30,'sales','chicago')
insert into dep values(40,'operations','boston')
insert into dep values(50,'advertisement','beijing')
drop table dep
select * from dep
// 根据deptNo编号 对应查找出相应的数据
select emp.empno,emp.ename,emp.sal,emp.deptno,dep.deptno,dep.dname,dep.loc
from emp,dep
where emp.deptno = dep.deptno;
// Join字句连接的方法查询两张表数据, using(deptNo)参照列进行连接
// right left 左右连接 全外连接 full outer
select emp.empno,emp.ename,emp.sal,deptno,deptno,dep.dname,dep.loc
from emp join dep
using(deptno);
// on字句连接的方法查询两张表数据,
On( emp.deptno = dep.deptno and sal > 1000);
// 根据deptNo编号 对应查找出相应的数据 根据sal和loc进行筛选
select e.empno,ename,sal,d.deptno,d.deptno,dname,loc
from emp e,dep d
where e.deptno = d.deptno and ( e.sal > 1000 or d.loc='chicago' );
// e和d对应 d和l对应 的数据 进行三表查询,,
select e.empno,ename,sal,d.deptno,d.deptno,dname,loc,l.id
from emp e,dep d,loc l
where e.deptno = d.deptno and l.id=e.id
// Join 进行三表查询,,
select e.empno,ename,sal,d.deptno,d.deptno,dname,loc,l.id
from emp join dep using(deptNo)
natural join locatins; //也可以继续 join 表名 using(关联列)
第七部分 子查询
// 简单的子查询语法,,如果有多个返回值
select * from emp where sal > ( select sal from emp where sal = 800 )
+ ( select sal from emp where sal = 300 )
//使用 in等于列表中任何一个 not in 不在这里面的所有列表
select * from emp where sal not in (select avg(sal) from emp group by deptno)
//使用 any 和子查询返回的任意一个值比较 查最小
select * from emp where sal > any (select avg(sal) from emp group by deptno)
//使用 all和子查询返回的所有值比较 查最大
select * from emp where sal > all (select avg(sal) from emp group by deptno)
//返回前面五条最高记录, rownum相当与 sqlserver里面top rownum也可当虚拟主键
select * from (select * from emp order by sal desc) where rownum <=5
//分页子查询语句,, rownum定义的虚拟主键,别名myno where 进行筛选
select * from (select rownum myno , a.* from (select * from emp order by sal desc) a )
where myno > 4 and myno <=7
//希望员工scott的岗位,工资,补助与smith员工一样
Update emp set (job,sal,comm) = (select job,sal,comm from emp where = 'smith') where ename = 'scott';
第八部分 DML与事务控制
//事物必须满足ACID属性,
à原子性(Atomicity):有一个地方出错,前面成功的也撤销..应是一个整体..必须恢复以前数据.要么全部成功,要么全部失败..
à一致性(Consistency):数据必须处于一致性,如B级工资300升到500级别也从B升到A,但是数据保持不一致,只上调了级别没上调工资..
à隔离性(Isolation):多个事物 多个用户操作一个数据, 如修改字段,其他事物不受影响..直到事物都成功了, 要么看到的数据之前和之后的状态 中间的被隔离开
à持久性(Durability):事物结束后永久保存在数据库中..不能撤销
//insert into 插入表中所有的数据
Insert into admin(id,name) select id,name from admin;
insert into admin values(6,'老百');
回滚点savepoint p1;
insert into admin values(6,'老千');
回滚 rollback p1;
提交 commit;
set autocommit on;
set autocommit off;
第九部分 数据库对象
//创建 test 表
create table test(
id number(10),
name varchar2(20),
hiredate date default sysdate,
salary number(8,2) default 0
);
// 将test 表的字段数据注入到test1里面来
create table test1(用户id,用户名字)
as select id,name from test
// 给test表增加mm gg 两个字段 默认值是,,...
alter table test
add(
gg varchar2(20) default '你是男人',
mm varchar2(20) default '你是女人'
)
// 使用modify给字句修改现有字段,包括字段的数据类型,大小和默认值
alter table test
modify(
gg varchar2(20) default '你是猪',
mm varchar2(20) default '你真笨'
);
// 使用drop删除表中字段行 drop table 表名即可删除表
alter table test
drop(gg,mm);
// 使用truncate清空表所有的记录,不能进行条件的删除,表结构还在
create table test1 as (select name,hiredate,salary from test)
truncate table test1
// 使用rename讲表test1名字替换成test
rename test1 to test
// 查看当前用户拥有的所有表的名字
select * from user_tables;
// 查询当前用户可访问的所有表的名字
select * from all_tables;
// 查询当前用户拥有所有对象的类型
select distinct object_type from user_objects;
// 查询用户拥有所有对象的类型
select * from dba_tables;
第十部分 表约束
create table mg
(
id number(3) primary key, //主键约束
gg number not null unique,//唯一约束
mm number constraint mg_mm not null, //非空约束
constraint mg_id_fk foreign key(id) references admin(id); //外键约束
);
//给admin表增加 check约束
create table admin
(
id number(3) check(id>=5 and id<=10),
name varchar2(20) check(length(name)>4 and length(name) <=20)
)
//建好表之后在添加约束 和 删除约束
create table student
(
sid number(3),
sname varchar2(20),
saddress varchar2(20),
tid number(3)
)
alter table student
add constraint studetn_pk_sid primary key(sid);
alter table student
add constraint studetn_sname check(length(sname)>4);
alter table student
add constraint studetn_saddress saddress default '湖南岳阳';
alter table student
add constraint studetn_fk_tid foreign key(tid) references tracher(tid);
alter table student
drop constraint studetn_pk_sid
create table tracher
(
tid number(3)
)
alter table tracher
add constraint tracher_pk_id primary key(tid);
第十一部分 视图,索引
//添加简单的视图
create or replace view v1
as select * from emp
desc v1
drop view v1;
//添加简单的索引
create index myindex on emp(ename);
drop index myindex
第十二部分 基本数据命令
Conn scott/sa //切换用户账户
Show user //查询属于那个用户
Desc //查看表结构
Disconnect //断开数据库连接
Password //修改用户密码
Exit //断开,退出窗口
Start 加路径. //要导入sql文件
Edit 加路径 //需要打开的修改的文件
Spool 加路径 spool off保存 //把结果保存到文件里面去
Setpagesize //一页显示多少条数据
Create user (admin)用户名 indentified by (sa)密码 //创建用户和密码
Drop user (admin)用户名 //删除用户
Grant connect to (admin)用户名 //给用户授予连接权限 用户才能进行登录
Grant resource to (admin)用户名 //给有用授予创建表的权限 才能创建表
Grant select on emp to (admin)用户名 //用其他用户登录 给admin表授予查询emp表权限
Grant update on emp to (admin)用户名 //给用户授予改emp表的权限
Grant all on emp to (admin)用户名 //给用户授予增删改emp表的权限
Revoke select on emp from admin //收回权限使用 revoke 上面都可收回
Grant select on emp to admin with grant option //给admin授予可以给别人授予这张表查询权限
Revoke select on emp to admin with grant option //撤销admin授予可以给别人授予这张表查询权限
Alter user admin(用户名) account unlock; //给用户解锁
第十三部分 sequence自增
Create sequence myseq //自增
Drop sequence myseq //删除自增
Insert into test values(myseq.nextval);
Insert into test values(myseq.currval)
/*创建序列*/create sequence s_id increment by 1 start with 1 nomaxvalue nocycle;/*创建触发器*/create or replace trigger myTrigger before insert on myTable referencing old as old_value new as new_value for each row begin new_value.userid= s_id.nextval; end; end;
oracle都用sequence
可以直接用native，hibernate会根据底层数据库自行判断采用sequence。
<id name="id" type="long" column="ID">
<generator class="native"/>
</id>
或直接用sequence
<generator class="sequence">
<param name="sequence">user_id_sequence</param>
</generator>
这种情况首先要先在数据库中建立了user_id_sequence的序列
--文献来源百度文库