学完oracle的总结

 

创建session--grant create session to lisi;

 

创建table --grant create table  to lisi;

 

撤销table-revoke create table from lisi;

 

创建tablespace--grant create tablespace to lisi;

=============================================================================================

启动数据库：startup;

关闭数据库--shutdowm;

cmd--sqlplus/nolog

cmd---sqlplus--sqlplus "wen/wen  as sysdba"--进入sqlplus中

http://xp-201012061224:5560/isqlplus/login.uix

 

 

修改id用 update ban_ji set id=8 where id=1;(import)

 

创建用户：

create user 用户名 identified by 密码

删除前的保存一份

savepoint aa;

rollback to aa;

回滚完成

 

=============================================================================================

在DOS下启动ORACLE的监听--lsnrctl start

启动ORACLE的实例--oradim -starup -sid orcl

=============================================================================================

创建一个用户：create user lisi identified by lisi;

=============================================================================================

文档查询

1--

  cmd   sqlplus/nolog

SQL> conn /as sysdba

 已连接

3个默认的用户（sys/change_on_install as sysdba）（system/manager）（scott/tiger）

2--更改账户:alter user scott account unlock;

查询用户是什么用户:

select table_name from user_tables;

对scott用户的解锁

SQL>alter user scott account unlock;

设置密码为tiger

SQL>alter user scott identified by tiger;

 

SQL>conn scott/tiger

 

   show user;

   USER is "SCOTT"

 

   conn /as sysdba

   select username,account_status from dba_users;

   它会显示SCOTT----OPEN

   conn scott/tiger

 

=============================================================================================

 

 新建一个xue_sheng表

 create table xue_sheng(id integer, xing_ming varchar(30));

 

 

 查看表的字段和数据类型   DESC+表名

 alter table xue_sheng add nian_ling number;

   alter table xue_sheng   drop column nian_ling;

    drop  table xue_sheng;

  desc xue_sheng;

  这里就会增加nian_ling 这个字段。

查看当前用户表的命令

Select  table_name from user_tables;   

 

practice:

 SQL> desc xue_sheng13;

 名称                                      是否为空? 类型------字段

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

 

 ID                                                 NUMBER(38)

 XING_MING                                          VARCHAR2(12)

 ADDRESS                                            NUMBER

 PHONE_NUMBER                                       NUMBER

 ZUOAIID                                            NUMBER

 

SQL> insert into xue_sheng13 values(1,'daling');

insert into xue_sheng13 values(1,'daling')

            *

第 1 行出现错误:

ORA-00947: 没有足够的值

 

 

SQL> select * from xue_sheng13;

 

未选定行

 

SQL> insert into xue_sheng13 values(1,'liuwenewen',11,13893178965,1);

 

已创建 1 行。

 

SQL> select * from xue_sheng13;

 

        ID XING_MING       ADDRESS PHONE_NUMBER    ZUOAIID

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

•          1 liuwenewen           11   1.3893E+10          1

 

SQL> insert into xue_sheng13 values(2,'zhangxinling',12,110,2);

 

已创建 1 行。

 

SQL> insert into xue_sheng13 values(3,'yanghuibing',13,120,11);

 

已创建 1 行。

 

SQL> insert into xue_sheng13 values(3,'language',13,120,11);

 

已创建 1 行。

 

SQL> select * from xue_sheng13;

 

        ID XING_MING       ADDRESS PHONE_NUMBER    ZUOAIID

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

         1 liuwenewen           11   1.3893E+10          1

         2 zhangxinling         12          110          2

         3 yanghuibing          13          120         11

         3 language             13          120         11

 

SQL>

对数据记录的操作(select, insert, update, delete)

常用函数(count-计算,max-最大值,min-最小值, avg-平均值,sum-总和,decode-解释,dist-,inct)

=============================================================================================

学习一些SQL语句

 

 create table xue_sheng(id integer,xing_ming varchar(20),nian_ling number);

 

 insert into xue_sheng values(1,'zhangsan',24);

 

 insert into xue-sheng values(2,'liuwenwen',22);

 

  查询

SQL>select * from xue_sheng;

SQL>select xing_ming from xue_sheng;//从学生表中查询姓名的字段

 

插入数据

SQL>insert into xue_sheng values(3,'w shi shui ',11);

SQL>insert into xue_sheng(id ,nian_ling,xuehao) values(2,'w shi shui',5555);

 

查找order by desc（降序）或者 asc（升序）排序

SQL>select * from xue_sheng order by nian_ling desc;       默认ASC

查找字段为空或者非空

SQL>select * from xue_sheng where xing_ming is null;        IS NOT NULL

过滤重复字段

SQL>select distinct nian_ling from xue_sheng;

更新表字段

SQL>update xue_sheng set xing_ming='zhanwu';(慎用)

SQL>update xue_sheng set xing_ming='lisi' where id=2;

 

=============================================================================================

删除，   需要注意 delete from 表名. 表示把表的数据全部清空

 

所以我们使用时一般带上条件 where 例如：

SQL> delete from xue_sheng where id=2;

 

Select substr('Hello',1,3) from dual;

Sub

 

Hel

 

Select substr(ename,2) from emp;

表示每一个名字都从第二个字符开始，截至整个字符串结束

 

Select chr(65) from dual;

c

 

A

 

Ascii

求一个字符的ASCII码数值

ASCII('A')

 

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

65

 

Round

Select round(112.23) from dual;

Round(112.23)

 

 

 

=============================================================================================

 

一些常用函数

 

SQL>select count(*) from xue_sheng;               count--计数

SQL>select sum( nian_ling ) from xue_sheng;       sum---总数

SQL>select MAX( nian_ling ) from xue_sheng;       MAX---最大值

SQL>select MIN( nian_ling ) from xue_sheng;       MIN---最小值

SQL>select AVG( nian_ling ) from xue_sheng;       AVG---平均值

 

=============================================================================================

decode函数使用,可以理解成是一个判断分类函数

SQL>select sum(decode(nian_ling,24,1,0,)),sum(decode(nian_ling,22,1,0)) from xue_sheng;

 

  insert into xue_sheng(id,nian_ling) values(2,34);

SQL>select sum(decode(nian_ling,23,3,0)) n_23,sum(decode(nian_ling,22,3,0)) n-22 from xue_sheng;

 

 

===============================================================================================

 

分组查询 group by 模糊查询LIKE 表连接JOIN ON 子查询IN（又叫嵌套查询）

 

1--新建学生表：xue_sheng

 

create table xue_sheng(id integer,xing_ming varchar(25),xing_bie number,result number);

 

insert into xue_sheng values(1,'liuwenwen',1,100);

 

insert into xue_sheng values(2,'wangxiaoer',0,200);

 

insert into xue_sheng values(3,'w shi shui ',1,150);

 

insert into xue-sheng values(4,'ai ni ',1,300);

 

查询要求：分组显示男女同学的总分

SELECT XING_bie,sum(result) from xue_sheng group by xing_bie;

 

2,  模糊查询 或者 模糊查找

 

使用LIKE关键字，通用字符 '%'

 

select * from xue_sheng where xing_ming LIKE 'zhan%';

 

select * from xue_sheng where xing_ming LIKE '%g';

 

select * from xue_sheng where xing_ming LIKE '%a%';

 

===========================================================================

 

 

3, 表连接

 

新建一个班级表：ban_ji

 

create table ban_ji(id integer,ban_ji varchar(20));

 

insert into ban_ji values(1,'1-(1)');

 

insert into ban-ji values(2,'1-(2)');

 

insert into ban_ji values(3,'1-(3)');

==============================================================================

 

select id, xing_ming from xue_sheng;

 

select id,xing-ming,ban_ji from xue_sheng ,ban_Ji;

 

select id,xing_ming,ban_ji from xue_sheng, ban_Ji;

 

学生表，班级表一起查询；

 

select x.id, xing_ming,ban_ji from xue_sheng x,ban_ji b;

 

select x.id, xing-ming,ban_ji from xue_sheng x,ban_ji b where x.b_id=b.id;

 

select x.id, xing_ming,ban_ji from xue_sheng x  join ban-ji b on x.b_id=b.id;

 

 

=======================================================================================

 

设置行的宽度--setLinesize 400

 

 

 

========================================================================================

 

子查询 in()或者 not in() ,又叫嵌套查询

 

select * from xue_sheng where b_id in(1,3);//取出id=1 和id=3的两条语句

 

select * from xue_sheng where b_id=1 or b_id=3;

 

以上两条语句相同