内容包括三大项: 1.oracle基本操作语句 2.SQLServer基本操作语句 3.各种数据库连接方法 oracle基本操作语句******************************************************** 打开服务器 net start oracleservicebinbo打开监听器 lsnrctl start关闭服务器 net stop oracleservicebinbo关闭监听器 lsnrctl stop===============================================================清屏clear screen****************************************************************数据字典 ===========desc user_views(关键词) ****************************************************************===============================================================查看当前用户的角色 SQL>select * from user_role_privs;===============================================================查看当前用户的系统权限和表级权限 SQL>select * from user_sys_privs;SQL>select * from user_tab_privs;===============================================================查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users;===============================================================换用户 conn as sysdbasystsinghuasqlplus "sys/tsinghua as sysdba"conn sys/zl as sysdba===============================================================修改表结构 alter table test modify(name not null);alter table test add(name varchar2(20));alter table test drop column sex;alter table test set unused column sex;alter table test drop unused columns;===============================================================更改用户密码 sql>alter user 管理员 identified by 密码； ===============================================================创建表空间的数据文件 sql>create tablespace test datafile 'd:\oracle\binbo.dbf' size 10m;===============================================================创建用户 sql>create user 用户名 identified by 用户名; ===============================================================bfile类型实例 创建目录 create directory tnpdir as 'c:\';删除目录 drop directory tnpdir授权 crant read on directory tn pdir to scott;建表create table bfiletest(id number(3), fname bfile);添加数据insert into bfiletest values(1,bfilename('TMPDIR','tmptest.java'));===============================================================查看用户 sql>show user===============================================================检查语句是否有错 show error===============================================================锁定用户 sql>alter user 用户名 account lock ===============================================================解除用户 sql>alter user 用户名 account unlock ===============================================================删除用户 sql>drop user zl;===============================================================给用户创建表权限 sql>grant create table to 用户名; ===============================================================授管理员权限 sql>grant dba to 用户名; ===============================================================给用户登录权限 sql>grant connect to 用户名 ===============================================================给用户无限表空间权限 sql>grant unlinmited tablespace to 用户名; ===============================================================收回权限 sql>revoke dba from 用户名; ===============================================================查看用户下所有的表 SQL>select * from user_tables;===============================================================查看名称包含log字符的表 SQL>select object_name,object_id from user_objects where instr(object_name,'LOG')>0;===============================================================查看某表的创建时间 SQL>select object_name,created from user_objects where object_name=upper('&table_name');===============================================================查看某表的大小 SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');===============================================================查看放在ORACLE的内存区里的表 SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; =============================================================== 再添加一个表空间的数据文件 sql>alter tablespace test add datafile 'd:\oracle\test1.dbf' size 10m;===============================================================建表 SQL>create table studen(stuno int,stuname varchar(8) not null,stubirth date default to_date('1987-5-9','YYYY-MM-DD')); 向表结构中加入一列 SQL>alter table studen add(stuphoto varchar(9)); 从表结构中删除一列 SQL>alter table studen drop column stuphoto; 修改表一列的长度 SQL>alter table studen modify(stuno number(4)); 隐藏将要删除的一列 SQL>alter table studen set unused column stuphoto; 删除隐藏的列 SQL>alter table studen drop unused columns; 向表中加入约束 SQL>alter table studen add constraint pk primary key(stuno); 删除约束 SQL>alter table studen drop constraint pk; ===============================================================创建表 sql>create table 用户名(name varchar2(20),password varchar(20)) tablespace 空间名; ===============================================================添加字段sql>alter table test add(column_x char(10) not null);===============================================================更改字段sql>alter table emp modify(column_x char (20));===============================================================删除字段 如待删除域属于某个索引，则不允许删除操作，必须将此域先设置为NULL。 sql>alter table emp modify(column_x null);sql>update emp set column_x=null;sql>commit;sql>alter table emp drop(column_x);===============================================================选择表空间 sql>alter user 用户名 default tablespace test; ===============================================================管理员删除别的用户中的表 sql>drop table 用户名.表名; ===============================================================退出sql>exit;===============================================================默认进入 sql>sqlplus "/ as sysdba"===============================================================查看数据库 sql>show parameter block;===============================================================写大量语句用记事本，新建方式。 输入"ed"回车 保存后输入"/"运行; ===============================================================查询用户有多少表 sql>select * from tab;===============================================================SQLServer取时间 sql>select getdateoracle 取时间 sql>sysdate;===============================================================操作表结构数据库定义语言命令 (不记录在日志文件中) create table建表 sql>create table test(name varchar2(20),age date,sex char(2));sql>insert into test(name,age,sex) values('aa',sysdate,'男'); sql>insert into test(name,age,sex) values('bb',to_date('1888-8-8',"yyyy-aa-dd hh24:mi:ss"),'男'); sql>select * from test;===============================================================查询男和女总数 sql>select sex,count(sex) from test group by sex;---------------------------------------------------------------test表中数据输入test1表中 SQLSserver---select * into test1 from test;oracle---create table test1 as select * from test;---------------------------------------------------------------更改会话时间 sql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';---------------------------------------------------------------sql>show parameter block 表和视图 sql>show parameter date 查数据结构 ---------------------------------------------------------------SQLServer中 --删除表中相同数据 sql>create table test1 as select distinct * from test;--删除表数据 sql>truncate table test;--把test中数据输入到test1中 sql>insert into test(select * from test1);---------------------------------------------------------------rowid(表中存储地址相当表id)和rownum(表序号)称伪列(用法) sql>select name,age,sex,rowid,rownum from test1;查出前三行 sql>select * from test where rownum<=3;查出后三行 sql>select * from (select name n,age a,sex s,rownum r from test) where r>(select count(*) from test)-3;删除后三行 SQL> delete from test where name not in(select name from test where rownum<=(select count(*) from test)-3);删除相同行 sql>delete from test where rowid not in(select max(rowid) from test group by name,age,sex);删除所有表 sql>select 'drop table' ||tname|| ':' from tab;sql>spool c:\test.sql;sql>select 'drop table' ||tname|| ':' from tab;sql>spool offsql>@c:\test.sql;---------------------------------------------------------------alter table修改表 truncate table节段表(只删除数据) drop table删除表 ===============================================================查看表结构 desc 表名; ===============================================================查出成绩的前三名 sql>select * from (select * from stu order by score desc) where rownum<=3;===============================================================更改字符集SQL>startup mountSQL>alter system enable restricted session;SQL>alter system set job_queue_processes=0;SQL>alter dataｂａｓｅ open; SQL>alter dataｂａｓｅ character set ZHS16GBK; SQL>shutdownSQL>startup===============================================================将一张表或几张表中的域重新组合后插入新表。 假定原先的两张表为emp,work，现选择部分数据域合并为emp_work 建立emp_work SQL>insert into emp_new select a.no, sysdate, a.name, b.service_duration from emp a, work b where a.no=b.no;SQL>commit;这样的方式仍然要使用回滚段，为加快数据迁移速度，可将insert替换成insert /*+APPEND*/（大小写不论），指示oracle以直通方式直接写数据文件，绕过回滚空间。 SQL>insert /*+APPEND*/ into emp_new select a.no, sysdate, a.name, b.service_duration from emp a, work b where a.no=b.no;SQL>commit;===============================================================DDL数据定义语言(create,alter,drop) DML数据操纵语言(insert,select,delete,update) TCL事务控制语言(commit,savepoint,rollback) DCL数据控制语言(GRANT REVOKE) =============================================================== 一个表中的某一列输到另一个表中 insert into stu1(name)(select name from stu);===============================================================事务 rollback;insert into stu1(name)(select name from stu);commit;提交 ===============================================================COMMIT - 提交并结束事务处理 ROLLBACK - 撤销事务中已完成的工作 SAVEPOINT – 标记事务中可以回滚的点 SQL> update order_master set del_date ='30-8月-05' WHERE orderno <= 'o002'; SQL> savepoint mark1;SQL> delete FROM order_master WHERE orderno = 'o002';SQL> savepoint mark2;SQL> rollback TO SAVEPOINT mark1;SQL> COMMIT;===============================================================换名 set sqlprompt "scott>";===============================================================GRANT 授予权限 SQL> GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION;REVOKE 撤销已授予的权限 SQL> REVOKE SELECT, UPDATE ON order_master FROM MARTIN;===============================================================比较操作符 SQL> SELECT vencode,venname,tel_no FROM vendor_master WHERE venname LIKE 'j___s';SQL> SELECT orderno FROM order_master WHERE del_date IN (‘06-1月-05’,‘05-2月-05'); SQL> SELECT itemdesc, re_level FROM itemfile WHERE qty_hand < max_level/2;===============================================================逻辑操作符 SQL> SELECT * FROM order_master WHERE odate > ‘10-5月-05' AND del_date < ‘26-5月-05’; ===============================================================集合操作符将两个查询的结果组合成一个结果 SQL> SELECT orderno FROM order_master MINUS SELECT orderno FROM order_detail;-----------------------------------------------------------------select * from scott.stuunion (all)重复的去掉[intersect把相同的取出来][minus显示不相同的数] select * from stu-----------------------------------------------------------------显示相同的数据 select name from stu intersect select name from stu1;===============================================================连接操作符 连接操作符用于将多个字符串或数据值合并成一个字符串 SQL> SELECT (venname|| ' 的地址是 ' ||venadd1||' '||venadd2 ||' '||venadd3) address FROM vendor_master WHERE vencode='V001';===============================================================操作符的优先级 SQL 操作符的优先级从高到低的顺序是： 算术操作符 --------最高优先级 连接操作符 比较操作符 NOT 逻辑操作符 AND 逻辑操作符 OR 逻辑操作符 --------最低优先级 ===============================================================用来转换空值的函数 NVLNVL2NULLIFSELECT itemdesc, NVL(re_level,0) FROM itemfile;SELECT itemdesc, NVL2(re_level,re_level,max_level) FROM itemfile;SELECT itemdesc, NULLIF(re_level,max_level) FROM itemfile;===============================================================GROUP BY和HAVING子句 GROUP BY子句 用于将信息划分为更小的组 每一组行返回针对该组的单个结果 HAVING子句 用于指定 GROUP BY 子句检索行的条件 SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category HAVING p_category NOT IN ('accessories');===============================================================ROW_NUMBER (row_number)返回连续的排位，不论值是否相等 RANK(rank) 具有相等值的行排位相同，序数随后跳跃 DENSE_RANK(dense_rank) 具有相等值的行排位相同，序号是连续的 SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANKFROM emp e, dept d WHERE e.deptno = d.deptno;===============================================================日期函数ADD_MONTHS(当前只加月) alter session set nls_date_format='yyyymmdd hh24miss';select add_months(sysdate,2) from dual;----------------------------------------------------------------MONTHS_BETWEEN(前面时间减后面时间=得之间月差) select months_between(sysdate,to_date('2007-6-10','yyyy-mm-dd')) from dual;----------------------------------------------------------------LAST_DAY(求得当前月的最后一天) select last_day(sysdate) from dual;----------------------------------------------------------------ROUND(round年-月-日-->四舍五入) select round(2.3) from dual;select round(to_date('2007-6-10','yyyy-mm-dd'),'year') from dual;select round(to_date('2007-6-10','yyyy-mm-dd'),'month') from dual;
