oracle常用操作

1.登录

sqlplus sys/123456 as sysdba;

(connect HR/1234;)

2.由于sys是系统管理员，它有很多表，都是用来管理其他用户的表,查询当前用户是哪一个用户

  由于在oracle中一个用户就是一些表的集合。所以，一个用户就可以当成一个数据库来对象。

desc dba_users;

3.设置一个字段占多长

column username format a16;

4.解锁用户

alter user HR identified by1234 account unlock;

5.远程链接oracle

5.1安装远程的SQLplus

配置一个环境变量：

       NLS_LANG=SIMPLIFIED CHINESE

       NLS = Native Language Support 本地语言支持

       _lang=Language 

sqlplus hr/1234@192.164.30.101/XE

5.2、用工具Sqldeveloper工具。

6.远程登录

sqlplus sys/123456@192.164.30.103:1521/orcl as sysdba

7.查询当前用户有几个表

select * from tab;

TNAME            TABTYPE           CLUSTERID

---------------- --------------------------

BONUS            TABLE

DEPT             TABLE

EMP              TABLE

SALGRADE         TABLE

STUD             TABLE

8.创建用户

create user czb identified by 1234  --用户及密码

default tablespace users  --指定用户的表空间

temporary tablespace temp   --用户临时表空间

account unlock;   --不锁定

再用sys登录给czb设置权限

grant all privilege to czb;

9.DDL和DML

DDL

建表：

create table stud(

    id int ,

    name varchar(30)

  );

删表：

drop table stud;

添加一个字段

alter table stud

     add age int;

修改字段名：

  alter table stud

        rename column addr to address;

修改字段类型：

alter table stud 

    modify age int;

删除一个字段：

   alter table stud

          drop column age;

DML:数据操纵语言

CRUD –Create(写入数据insert)

                     Read (读取数据select) – 复杂

                     Update(修改为数据update)

                     Delete (删除数据delete);

增

insert into stud values(1,'李四');

删：

--删除名称为李四的

deletefrom emp where name='李四' and id=4;

--删除全部

deletefrom emp;

rollback;

--在删除时还可以使用另一个关键字

--清空整个表中的数据

truncatetable emp;

查

只查询一部分字段。

select *from emp;

--只查询两个字段

selectid,name from emp;

--可以任何的设置顺序

selectage,name from emp;

--在查询时，可以给字段设置别名

select idas 编号,name 名称,age as 年龄from emp;

--在查询时使用 where子句

select *from emp;

--查询年龄为33且名称为Jack

select *from emp where name='Jack' and age=33;

--查询名称为Jack或年龄为44的

select *from emp where name='Jack' or age=44;

--查询年龄不是33

select *from emp where age!=33;

select *from emp where age<>33;

--用in查询存在于些某些之内的

select *from emp where age!=33 and age!=44 and age!=55;

--可以修改成用in实现

select *from emp where age not in(33,44,88);

--查询33到55间的人员

select *from emp where age>=33 and age<=55;

--直接使用between

select *from emp where age between 44 and 55;

--is查询

insertinto emp(id,name) values(5,'Jerry');

select *from emp;

commit;

--查询哪一个人没有age,对于null值，必须要使用is关键字

select *from emp where age is null;

改：

--设置所的有年龄为0

updateemp set age=0;

--同时修改两个列

updateemp set age=1,name='Jack';

--带条件的修改

updateemp set name='张三',age=34 where id=1;

--修改所有id为4的

updateemp set name='李四',age=19 where id=4;

commit;

--------总结--------------

创建表：

create table stud(

       id int

);

//添加新的字段

alter table stud

   add age int;

 

删除一个字段

alter table stud

       drop  column age;

//修改一个字段名称

alter table stud

       rename colunn  age to myage;

 

//修改字段类型

alter table stud

       modify age varchar(30);

 

//修改表名

rename stud to stud2;

//删除表

drop table stud2;

 

//清空回收

purge recyclebin;

10.数据字典：

某用户的所有表：

 user_tables等同于tab

select * from tab;

或

select table_name form user_tables;

11.查询时关键字

Sum – 和

Max 最大

Min 最小

Avg 平均

Count 计数

--去掉查询中的重复的行

select distinct name from emp;

--统计，人数

select count(distinct name) from emp;

聚合分组 group by.

       Having– 对聚合以后的数据进行再过虑。

select name,avg(score) as ag from studgroup by name

 having avg(score)>=60

12.分页：

Sqlserver：top 查询前几行.

select * from cars order by id;

--第一页 start = (第1页-1)*3=0

select top 3 * from cars

  where id not in(select top 0 id from cars);

--第二页

select top 3 * from cars

  where id not in(select top 3 id from cars);

--第三页

select top 3 * from cars

  where id not in(select top 6 id from cars);

Oracle: rownun

--查询第一页,在oracle中，有一个特别字段，rownum.,当你使用 rownum时，其他字段也必须要写上去

--第一页 start=(第1页-1)*pageSize=0,end=start+pageSize=0+3=3;

select * from

 (select rownum num,id,msg from cars) cc

 where cc.num>0 and cc.num<=3;

--第二页-start=(第2页-1)*pageSize=3,     end=3+pageSize=3+3=6;

select * from

 (select rownum num,id,msg from cars) cc

 where cc.num>3 and cc.num<=6;

--第三页 - start=(第3页-1)*3=6 ,end= 9;

select * from

 (select rownum num,id,msg from cars) cc

 where cc.num>6 and cc.num<=9;

Mysql:limit (最好用的)

SELECT * FROM cars;

--从每0行，以后那一行开始，不包含0，后面的三行

--第一页

SELECT * FROM cars LIMIT 0,3;

--第二页

SELECT * FROM cars LIMIT 3,3;

13.主键：

第一种方式：

createtable bike(

  id varchar(32) primary key,

  name varchar(30)

);

--声明主键的第二种方式

createtable t5(

 id int,

 nm varchar(30),

 --声明主键constraint：约束

 constraint t5_pk primary key(id)

);

--第三种方式

createtable t6(

 id int,

 nm varchar(30)

);

--修改t6表，添加约束

altertable t6

  add constraint t6_pk primary key(id);

14.约束：

非空约束/默认

--非空约束

createtable u2(

  id int,

  age integer not null

);

insertinto u2(id,age) values(1,99);

--默认默认值

createtable u3(

  id int,

  age int default -1

);

检查约束 – check 用于一些相对比较复杂的检查

create table u4(

  id int,

  sex char(1) check (sex in('1','0'))

);

15.主外键

15.1.一对多

15.2.一对一

主键对主键的一对一：

createtable person1(

   id int,

   nm varchar(30),

   constraint p1_pk primary key(id)

);

 

--子表

createtable card1(

  cardid int,

  gov varchar(100),

  expire varchar(19),

  --设置cardid主键

  constraint card_pk primary key(cardid),

  --同时又person1的外键

  constraint card_fk1 foreign key(cardid)references person1(id)

);

主键对唯一的一对一

createtable person2(

   id int,

   nm varchar(30),

   constraint p_pk2 primary key(id)

);

 

createtable card2(

   cardid int,

   gov varchar(100),

   --再多创建一个列

   pid int,

  --创建主键

  constraint c_pk2 primary key(cardid),

  --再指唯一

  constraint c_uq unique(pid),

  --设置它为外键

  constraint c_fk foreign key(pid) referencesperson2(id)

);

15.3多对多

--查学生，选择了什么课

selects.name as sname,c.name as cname

from studs,course c,sc

wheres.id=sc.sid and c.id=sc.cid;

--用sql92

selects.name as sname,c.name as cname

from studs inner join sc on s.id=sc.sid

            inner join course c on sc.cid=c.id;

           

--查询哪些学生没有选择课

selects.name as sname

from studs left join sc on s.id=sc.sid

            left join course c on sc.cid=c.id

wherec.name is null;

 

--哪些课没有人选

selects.name as sname,c.name as cname

from studs right join sc on s.id=sc.sid

            right join course c on sc.cid=c.id;

 16.pl/sql块

16.1变量的声明

 set serveroutput on;

 declare

      v_a number"=0; - -有默认值

     v_b integer;

     v_id stud.id%type; - - 用某个表的某个字段的类型

  begin

     nm:='Jack';

     v_id:=32;

     dbms_output.put_line('your name is:'||nm' your id is:'||v_id);

end;

调试pl /sql块的权限 

 grant debug connect session to scott;

 grant debug any procedure to scott;

接受用户的输入

accept  aaa prompt '请输入你的年龄';

declare 

     age integer;

begin 

     age:=&aaa;

     dbms_output.put_line('age is: '||age);

end;

查询stud表中有多少记录

declare 

    v_count integer;

    v_avg number(10,2);

begin

   select count(1),avg(age) into v_count,v_avg from stud;

   dbms_output.put_line('人数: '||v_count||'avg is: '||v_avg);

end; 

16.2.条件语句、循环语句

case when then 语句

直接查询时使用case语句

select id,name,age,(case sex when '1' then '男' else ‘女' end) as sex from stud;

第二种方法

select id,name,age,(case when sex='1' then '男' else '女' end) as sex from stud;

多个when

select id,name,age,(case when sex='1' then '男' when sex='2' then '不知道' else '女' end) as sex from stud;

在pl/sql块中使用，查询是男是女

declare 

    v_result varchar(30);

    v_id integer;

begin

   v_id:=&id;

   select sex into v_result from stud where id=v_id;'

   v_result :=

       when '1' then '男'

      when '0' then '女'

      else '不知道'

      end;

   dbms_output.put_line('编号为'||v_id||'的是:‘||v_result);

end;

loop语句

1、loop exit  end loop

declare 

    v_i integer:=1;

    v_sum integer:=0;

begin

     loop

           if v_i>100 then

              exit

           end if;

           dbms_output.put_line('sum is:'||v_sum);

           v_sum:=v_sum+v_i;

          v_i:=v_i+1;

      end loop;

      dbms_output.put_line('sum is:'||v_sum);

end;

2.loop代码exit when 条件 ；end loop

declare 

      v_i integer:=1;

      v_sum integer:=0;

begin

    loop

    exit when v_i>100;

            v_sum:=v_sum+v_i;

            v_i:=v_i+1;

     end loop;

     dbms_output.put_line('result sum is'||v_sum);

end;

3.while 条件loop ...代码 end loop

declare 

     v_i integer:=1;

     v_sum integer:=0;

begin 

     while v_i<=100 loop

          v_sum:=v_sum+v_i;

          v_i:=v_i+1;

      end loop;

      dbms_output.put_line('while result sum is:'||v_sum);

end;