MSSQL MYSQL ORACLE一些常用的命令

 有些不准确的，请指正

1.查看数据库版本
MSSQL: select @@version;
MySQL: select version();
ORACLE: select * from v$version;

2.查看当前用户名
MSSQL: select user_name();
MySQL: select user();
ORACLE: select user from user_tables;  select username from v$session; 不知道是不是这样

3.查看当前数据库名
MSSQL: select db_name();
MySQL: select database();
Oracle: select name from v$database; show parameter db_name； 好象是多行,这个不对

oracle还有实例名
select instance_name from v$instance；
show parameter instance 


4.查看所有数据库名
MSSQL: Select Name From MasteSysDatabases order By Name; 2000
        SELECT NAME FROM MASTER.DBO.SYSDATABASES;2005
MySQL: show databases; select schema_name from information_schema.schemata;
Oracle: 

5.查看所有表名
MSSQL: Select Name From SysObjects Where XType='U' order By Name;//'U':用户表;'S':系统表(2000);
       SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;(2005)
Mysql: show tables; select table_name from information_schema.tables where table_schema=database();
Oracle: select TABLE_NAME from all_tables;

6.查看所有列名
MSSQL: SELECT A.NAME,B.NAME FROM SYSOBJECTS A,SYSCOLUMNS B WHERE A.ID=B.ID; 2005 带表名
        Select Object_Id('TableName'),Name From SysColumns ;2000是这样？好象不对
MySQL: select table_name,COLUMN_NAME from INFORMATION_SCHEMA.columns; 带表名
Oracle: select table_name,column_name from all_tab_columns;


7.查询几条记录
MSSQL: select top 5 * from xnet order by id desc;
Mysql: select * from xnet limit 0,5;
Oracle: select * from xnet where rownum=5;


oracle的一些查询
第N条记录
select * from (select rownum no,t.* from xnet t) where no=N;
第5-10条记录
select * from (select rownum no,xnet.* from xnet where rownum<=10) where no>=5;

MSSQL
第5-10条记录
select top 10 * from tablename where id not in (select top 5 id from tablename order by id asc) 

其实还是MySQL方便
要查询第5到10条记录
select * from xnet limit 4,5;

8.用户管理(没有完整)
MSSQL:
新增用户
sp_addlogin 'xnet','111111','xnet_db'

添加到数据库
sp_grantdbaccess 'xnet'
sp_addrolemember 'db_owner', 'xnet'  db_owner权限？

授予该用户语句权限
use xnet_db
grant create,select,update table to xnet

删除登录
EXEC sp_droplogin 'xnet'

MySQL:
grant all privieleges on database_name.* to xnet@localhost identified by 'xnet';

只给xnet对xnet_db中的table1表有select权限
grant select on xnet_db.table1 to xnet@localhost identified by 'xnet'; 

删除用户aaa
use mysql;
delete from user where user='aaa';
flush privileges;

修改密码:
use mysql;
update user set password=password('10086') where user='xnet';
flush privileges;
使用flush privileges命令使密码生效。


oracle:
添加一个xnet xnet_pass的用户
create user xnet identified by "xnet_pass" default tablespace TS_XNET_DATA temporary tablespace TEMP; 

给用户赋予权限
grant connect to xnet;
grant resource,create session to xnet;  开发角色
grant dba to xnet;--授予DBA权限
grant unlimited tablespace to xnet;--授予不限制的表空间
grant select any table to xnet;--授予查询任何表
grant select any dictionary to xnet;--授予 查询 任何字典

删除用户
drop user xnet cascade;

9.新建数据库
MSSQL:
CREATE DATABASE Archive ON PRIMARY
( 
NAME = testdb,
FILENAME ='d:\mssql\data\testdb.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
LOG ON
( NAME = testlog1,
FILENAME ='d:\mssql\data\testlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20);
MySQL:CREATE DATABASE rewin;
Oracle: 有点复杂，不写了

10.备份与恢复
MSSQL:
Backup Database xnet_db To disk='d:\Backup\xnet_db_Full_20090911.bak'; 对xnet_db完全备份
restore database xnet_db from disk='d:\Backup\xnet_db_Full_20090911.bak'; 对xnet_db从完全备份中恢复
MySQL:
mysqldump -uroot -p123456 xnet_db>d:\backup\xnet_db.sql;  对xnet_db完全备份
mysql -uroot -p123456 xnet_db<d:\backup\xnet_db.sql 对xnet_db恢复
也可以用mysql命令行
mysql>use xnet_db;
mysql>source d:\backup\xnet_db.sql
Oracle:
exp USERID=system/oracle@erp owner=xnet file=d:\logback\xnet.dmp 备份用户xnet所有表和数据
imp userid=system/oracle@erp fromuser=xnet file=d:\logback\xnet.dmp ignore=y 恢复用户xnet所有表和数据