学完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;

  ?

  以上两条语句相同

  ?