学完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
---------- ------------ ---------- ------------ ----------
?
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;
?
以上两条语句相同
?
