Oracle、mySql、sql Server常用sql

一、 ORACLE数据库

1、查询字段长度

select * from table1 where length(字段名称)>2;

2、查询用户下表的个数

Select  count(*)  from  user_tables;

Select table_name from all_tables where owner=’scott’;

3、查询当前用户的所在表空间，使用百分比，和内存大小

select b.file_id　　文件ID,

b.tablespace_name　　表空间,

b.file_name　　　　　物理文件名,

b.bytes　　　　　　　总字节数,

(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,

sum(nvl(a.bytes,0))　　　　　　　　剩余,

sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id and b.tablespace_name='TS_TAB_JNLBK_01'

group by b.tablespace_name,b.file_name,b.file_id,b.bytes

order by b.tablespace_name;

 

4、数据库表空间使用情况（只能查看当天数据）

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

       D.TOT_GROOTTE_MB "表空间大小(M)",

       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2), '990.99') "使用比",

       F.TOTAL_BYTES "空闲空间(M)",

       F.MAX_BYTES "最大块(M)"

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

          FROM SYS.DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) F,

       (SELECT DD.TABLESPACE_NAME,

               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

          FROM SYS.DBA_DATA_FILES DD

         GROUP BY DD.TABLESPACE_NAME) D

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

 ORDER BY 1 ASC;

 

5、查询各表的大小及按照大小排序

select * from (

select segment_name,sum(bytes)/1024/1024 MB

FROM Dba_Segments

where owner = 用户名 

group by segment_name

order by 2 DESC

);

6、查询某个表的所有字段

SELECT * from user_tab_columns where table_name =’表名称’;

7、查看数据库服务器字符集

select * from nls_database_parameters;

8、查看客户端字符集环境

select * from nls_instance_parameters;

9、查看会话字符集环境

select * from nls_session_parameters;

10、查看数据库字符集

select * from nls_database_parameters;

11、oracle过期解决办法

1、查看用户的proifle是哪个，一般是default：

sql>SELECT username,PROFILE FROM dba_users;

2、查看指定概要文件(如default)的密码有效期设置：

sql>SELECT * FROM dba_profiles s WHERE s.profile=\’DEFAULT\’ AND resource_name=\’PASSWORD_LIFE_TIME\’;

3、 将密码有效期由默认的180天修改成“无限制”：

sql>ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

修改之后不需要重启动数据库，会立即生效。

4、 修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示;

 已经被提示的帐户必须再改一次密码，举例如下：

$sqlplus / as sysdba

sql> alter user smsc identified by <原来的密码> ----不用换新密码

oracle11g启动参数resource_limit无论设置为false还是true，密码有效期都是生效的，所以必须通过以上方式进行修改。以上的帐户名请根据实际使用的帐户名更改。

 

12、把一个表中的字段内容复制到另外一个字段

①Update 表名称  set 字段1=字段2（被复制的表）;

②Update 表名称  set 字段1=’name ’|| 字段2（被复制的表）;

13、oracle  spool的用法

①Spool d:\filename.sql（路径）

②生成的需要打印的sql

③Spool off   ——生成关闭

④@d:\filename.sql  ——读取执行文件里面的sql

在生成的sql文件最前面需要加上以下内容

spool on set echo on #是否显示执行的命令内容 set feedback on  #是否显示* set heading on  #是否显示字段 set heading on  #去字段空格

最后需要添加 Spool off

14、数据排序时处理空值

Select * from 表 order by 字段 nulls last（first）;

15、查询数据库某个用户的数据量大小

select sum(bytes)/1024/1024/1024 from dba_segments t where t.OWNER='WDHT';

16、扩展表空间

Alter tablespace user（表空间名称）add datafile '/opt/oracle/oradata/esop/so_data02.dbf'（文件路径） size 200M（文件大小）;

17、连接数据库命令

方法一：sqlplus 用户名/密码@192.168.208.120:1521/orcl

方法二： ①sqlplus /nolog

   ②connect username/password@(连接标识符)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

二、 SQL SERVER 数据库

1、查询用户表的个数

select COUNT(1) from kd0003.dbo.sysobjects where xtype ='U';

2、查看当前用户所有表

方法一：select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME ;

方法二：select Name from sysobjects where xtype='u' and status>=0;

 

 

 

 

 

三、 Mysql 数据库

字典表

1. INFORMATION_SCHEMA.SCHEMATA  --数据库中所有数据库信息

可查看用户、字符集、

 

2. INFORMATION_SCHEMA.TABLES  --存放数据库中所有数据库表信息

可查看  用户、表名称、表类型（表or视图）、表的数据条数、

3. INFORMATION_SCHEMA.COLUMNS  --所有数据库表的列信息

可查看 表名称以及每个表的字段名称、字段数据类型

 

4. INFORMATION_SCHEMA.STATISTICS  --存放索引信息

5. INFORMATION_SCHEMA.USER_PRIVILEGES  --

6. INFORMATION_SCHEMA.SCHEMA_PRIVILEGES

7. INFORMATION_SCHEMA.TABLE_PRIVILEGES

8. INFORMATION_SCHEMA.COLUMN_PRIVILEGES

9. INFORMATION_SCHEMA.CHARACTER_SETS

10. INFORMATION_SCHEMA.COLLATIONS

11. INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY

12. INFORMATION_SCHEMA.TABLE_CONSTRAINTS

13. INFORMATION_SCHEMA.KEY_COLUMN_USAGE ---存放数据库里所有具有约束的键信息

14. INFORMATION_SCHEMA.ROUTINES

15. INFORMATION_SCHEMA.VIEWS  --存放所有视图信息

16. INFORMATION_SCHEMA.TRIGGERS  --触发器信息

1、查看用户下表的个数

SELECT COUNT( * ) FROM information_schema.tables WHERE TABLE_SCHEMA = '用户';

2、查看所有可用的字符集

show character set;