内容包括三大项:
2.SQLServer基本操作语句
3.各种数据库连接方法
************************oracle基本操作语句*************
打开服务器
net start OracleServiceORCL
打开
lsnrctl start
关闭服务器
net stop OracleServiceORCL
关闭
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 sysdba
sys
tsinghua 18:10 2009-7-11
sqlplus "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:\';
oracle登录命令
删除目录
drop directory tnpdir
授权
grant 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 unlimited 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 getdate
oracle 取时间
sql>sysdate;
创建序列让表的id自增
create sequence idh start with 1 increment by 1;
但仍需要手动插入，如下所示：(idh是上面所创建的序列名)
insert into department stval,'就业部门','frank','135********');
若需要让其自动插入，配置一个触发器……
也可以这样来创建表
create table department(id int primary key not null,dep_name varchar2(50),dep_manager varchar2(50),dep_telphone varchar2(13));
操作表结构数据库定义语言命令
(不记录在日志文件中)
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>cre
ate 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 off
sql>@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 mount
SQL>alter system enable restricted session;
SQL>alter system set job_queue_processes=0;
SQL>alter database open;
SQL>alter database character set ZHS16GBK;
SQL>shutdown
SQL>startup
将一张表或几张表中的域重新组合后插入新表。
假定原先的两张表为emp,work，现选择部分数据域合并为emp_work
建立emp_work
SQL>insert into emp_new , sysdate, a.name, b.service_duration from emp a, work b =b.no;
SQL>commit;
这样的方式仍然要使用回滚段，为加快数据迁移速度，可将insert替换成insert /*+APPEND*/（大小写不论），指示oracle以直通方式直接写数据文件，绕过回滚空间。
SQL>insert /*+APPEND*/ into emp_new , sysdate, a.name, b.service_duration from emp a, work b =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 表 TO 用户 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.stu
union (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 逻辑操作符 --------最低优先级
用来转换空值的函数
NVL
NVL2
NULLIF
SELECT 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 DENRANK
FROM 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;
select round(to_date('2007-6-10','yyyy-mm-dd'),'day') from dual;
----------------------------------------------------------------
NEXT_DAY(下一星期的星期二）
select next_day(to_date('2007-6-10','yyyy-mm-dd'),'星期二') from dual;
----------------------------------------------------------------
TRUNC(trunc)
----------------------------------------------------------------
EXTRACT(extract)
select extract(year from date '1998-03-07') from dual;
select extract(month from to_date ('1998-03-07','yyyy-mm-dd')) from dual;
----------------------------------------------------------------
2008年2月有多少天
inbo---->select extract(day from last_day(to_date ('2008-02-07','yyyy-mm-dd'))) from dual;
-
---------------------------------------------------------------
2003-4-3与1956-3-1之间有多少天
inbo---->select round(months_between(to_date('2003-4-3','yyyy-mm-dd'),to_date('1956-3-1','yyyy-mm-dd'))/12) from dual;
把两边的9去掉
select trim('9' from '9999ddddddd99999') from dual;
去空格
select trim(' ' from ' 9999ddddddd99999') from dual;
函数 输入 输出
Initcap(char) Select initcap(‘hello’) from dual; Hello
Lower(char) Select lower(‘FUN’) from dual; fun
Upper(char) Select upper(‘sun’) from dual; SUN
Ltrim(char,set) Select ltrim( ‘xyzadams’,’xyz’) from dual; adams
Rtrim(char,set) Select rtrim(‘xyzadams’,’ams’) from dual; xyzad
Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual; back
Replace(char,searchstring,[rep string]) Select replace(‘jack and jue’ ,’j’,’bl’) from dual; black and blue
Instr (char, m, n) Select instr (‘worldwide’,’d’) from dual; 5
Substr (char, m, n) Select substr(‘abcdefg’,3,2) from dual; cd
Concat (expr1, expr2) Select concat (‘Hello’,’ world’) from dual; Hello world
数字函数接受数字输入并返回数值结果
函数 输入 输出
Abs(n) Select abs(-15) from dual; 15
Ceil(n) Select ceil(44.778) from dual; 45
Cos(n) Select cos(180) from dual; -.5984601
Cosh(n) Select cosh(0) from dual; 1
Floor(n) Select floor(100.2) from dual; 100
Power(m,n) Select power(4,2) from dual; 16
Mod(m,n) Select mod(10,3) from dual; 1
Round(m,n) Select round(100.256,2) from dual; 100.26
Trunc(m,n) Select trunc(100.256,2) from dual; 100.25
Sqrt(n) Select sqrt(4) from dual; 2
Sign(n) Select sign(-30) from dual; -1
字符函数
查看有多少个字符
SQL> SELECT LENGTH('frances') FROM dual;
---------------------
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论