oracle/mysql/sqlserver常用的复杂sql语句操作

数据库常见问题处理

 
 
 
一、 mysql库问题操作命令集：
 
1、 查看当前用户下所有的table、view、column数量:

SELECT u.table_num, v.view_num,
(c1.column_num1+c2.column_num2) column_num,(u.table_num + v.view_num +
c1.column_num1+c2.column_num2+ 1) count_all
from
(select count(*) table_num from sysobjects where xtype='U') u,
(select count(*) view_num from sysobjects where xtype='V') v,
(select count(*) column_num1 from syscolumns where id in (select u.id from sysobjects u where xtype='U')) c1,
(select count(*) column_num2 from syscolumns where id in
(select v.id from sysobjects v where xtype='V')) c2

2、 查看当前用户下所有的表:

select *  from sysobjects where xtype='U'

3、 查看当前用户下所有的view：

select * from sysobjects where xtype='V'

4、 查看当前用户下面所有的列:

select * from syscolumns where id in (select u.id from sysobjects u where xtype='U')

5、 查看mysql版本号:

select version();

or

服务器上直接：mysql –v

6、Mysql支持哪些数据类型:

1、数值数据类型：
整数类型：tinyint、smallint、mediumint、int、bigint
浮点小数类型: float、double
定点小数类型:decimal
2、时间/日期类型:Year、time、date、datetime、timestamp
3、字符串类型:
文本字符串: char、varchar、tinytext、text、mediumtext、longtext、enum
Set
二进制字符串: bit、binary、varbinary、tinyblob、blog、mediumblob、longblob

7、Mysql服务启动:

**1**、net start/stop/status mysql
**2**、service mysqld start/stop/status

8、Mysql服务器上使用:

**1**、使用mysql数据库:
Mysql –uroot –p密码
Use mysql;
Select…

**2**、mysql命令窗口中删除: ctrl+backspace

9、Mysql修改字符集（client/server/database）:

**1**、查看所有字符集: show variables like '%char%';

**2**、在my.cnf（windows上是my.ini）中修改成一下即可:
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

注意：如果上面配置改好后，发现mysql库已经修改成功，然后自己的库还没有修改成功，直接用：alter database mdm_master CHARACTER SET utf8;  来修改数据库编码

**3**、查看表的字符集: show  create table 表;

**4**、修改表的字符集: 
alter table 中文表2  DEFAULT CHARACTER SET utf8 COLLATE
utf8_general_ci;

10、查看mysql的安装路径:

**1**、whereis mysql

11、Mysql常见操作:

服务器端操作:
**1**、显示当前用户所有数据库: show databases;
**2**、使用某个数据库: use database;
客户端操作(通用mysql语法):
**1**、创建序列：
create
sequence SEQ_MD_CORPORE_CTN
increment
by 1
start
with 1
maxvalue 9999999;

12、Mysql不能本机localhost访问或者不能远程访问:

**1**、第一步，如果root也不能登录，
可以在my.cnf中的[mysqld]标签下添加:  kip-grant-tables  
就可以直接不输入密码用root进入mysql
update mysql.user set authentication_string=password('root') where user='root'
grant all privileges on *.* to 'root'@'localhost'  identified by 'root';
flush privileges

**2**、第二步：grant all privileges on database.table(可以写成*.*表示所有库下所有表) to ‘用户’@‘%’(%表示匹配所有ip)  dentified by 密码;
改好之后可以通过user表查询：select user，host from user；来查看远程ip是否已经设置好。

13、windows下如何修改mysql默认端口号

**1**、	cmd命令行管理员身份登录 mysql -u root -p 密码
**2**、	查看默认端口号：show global variables like 'port';
**3**、	在mysql的安装路径下找到my.ini文件修改port=3306，改为想要的端口号，比如3308；
**4**、	在管理配置里重启mysql服务可生效；
**5**、	登录新的端口号：mysql -u root -p 密码 -P 修改后的端口号

 
二、 oracle数据库操作命令集:
 
1、 查看指定用户下所有table、view、column数量:

SELECT
t.table_num,
v.view_num, c.column_num, (t.table_num + v.view_num + c.column_num + 1)
count_all FROM
(select count(*) table_num from dba_tables where owner='CTMSPROD') t,
(select count(*) view_num from dba_views where owner = 'CTMSPROD') v,
(select count(column_name) column_num from dba_tab_columns where owner='CTMSPROD') c

2、查看用户下面的所有表：

select *  from dba_tables where owner='CTMSPROD'

3、查看用户下面所有的view：

select *  from dba_views where owner = 'CTMSPROD'

4、查看用户下面所有的列：

select *from dba_tab_columns where owner='CTMSPROD'

5、服务器端操作命令:

1> 创建用户：create user test identified by test;
赋予权限: grant connect, resource to test;
修改用户密码: alter user test identified by 123456;

2> 查看oracle数据库最近表的操作:

select
SQL_TEXT, last_active_time from v$sql where last_active_time <=
to_date('2018-12-3 0:00:00','yyyy-mm-dd hh24:mi:ss') 
and SQL_TEXT like '%YPXT%' order by
last_active_time desc

3> 进入oracle用户命令：

Su – oracle 进入oracle用户
Sqlplus/nolog （sqlplus登录）
Conn sh/sh

Oracle命令窗口中删除字符：ctrl+backspace
或者直接用sqlplus登录: sqlplus /as sysdba

4> oracle的用户sh被锁定：alter user sh account unlock
加锁用户: alter user system account lock;

5> oracle用户身份有几种:
分三种: normal（普通用户）登录、sysdba（系统管理员）登录、sysoper（系统操作员）登录，三种身份对应的权限各不同

6> 查看当前登录的用户: show user;

7> 查看实例启动状态：select status from v$instance
启动实例: startup 实例名;
查看当前实例名: select instance_name from v$instance;

8> 开启和关闭数据库: startup启动 shutdown (还可以immediate立即关闭、abort粗暴终止)

9> 查看oracle版本: select * from v$version;

10> oracle监听(启动、终止、状态): lsnrctl start/stop/status

11> 查看系统所有用户(dba权限): select * from dba_users;
查看你当前登录账号权限管理的用户: select * from all_users;
查看当前用户信息：select * from user_users;

12> oracle数据库启动
1、登录 su - oracle ,sqlplus / as sysdba
2、开启数据库 SQL> 输入startup
3、打开Oracle监听 在oracle账户下 lsnrctl start
4、关闭数据库防火墙 service iptables stop

6、Oracle常见命令:

1> 查看所有用户：select * from all_users;
2> 查看表的创建时间: select created from dba_objects where object_name = 'table_name';
3> oracle如何删除某个用户下的所有数据: DROP USER DMS CASCADE;
4> oracle查看当前用户下所有表的大小:

select OWNER, t.segment_name,
t.segment_type, sum(t.bytes / 1024 / 1024/1024) GG from dba_segments t where
t.owner = 'DBTEST'  and
t.segment_type='TABLE' group by OWNER, t.segment_name, t.segment_type order by
GG desc;

oracle查看单张表的大小:

select segment_name as tablename,
bytes/1024/1024/1024||'G' from user_segments where segment_name='表名';（表名大写）

5>、查看oracle字符集：select userenv('language') from dual;

7、Oracle支持的数据类型:

字符串类型: char、nchar、varchar、varchar2、nvarchar2 数字类型:
number、integer、float、binary_float、binary_double 日期类型: date、timestamp
LOB类型: clob、nclob、blob、bfile RAW&LONG类型: long、raw、long raw

8、如何监控某个用户下别人对我们表的删除等操作？

思路是通过在用户(database)上建立ddl的触发器，然后建立一张记录表，一有对表的删除，直接从系统的V$session中拉取操作的用户IP等信息
https://www.haowuliaoa.com/article/info/348865.html
https://www.2cto.com/kf/201811/787387.html
这两个地址可以解决我们的问题，亲测可用，已实现。
对于Oracle9i以上版本，需修改参数：
alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
重启数据库：
shutdown
startup
第二种方法：直接将触发器和日志表都建在sys用户下，不用修改参数
建在sys用户下，是最简便省力的方法

9、rac数据库：more /etc/hosts
ps -ef|grep smon

10、oracle数据库中.dbf、.ora、.ctl都是文件，起什么作用？

.dbf文件用于存储数据库数据的文件，列如表中记录、索引、数据字典信息等，可以通过系统数据字典DBA_DATA_FILES查看相关信息。与逻辑角度的表空间对应，一个表空间可以有多个数据文件，但一个数据文件只能属于一个表空间。
.log重做日志，用于记录对数据库的修改信息。日志文件是备份与恢复的重要手段。
.ctl控制文件，用于描述数据库的物理结构。存放有数据文件和日志文件等信息。 .ora参数文件，记录数据库名、控制文件路径、进程等信息。
上面的三种文件(除了日志文件)，任意一种出错，数据库都可能不能正常运行。参数文件可以修改数据库某些参数，但是需要重启数据库才生效。

11、查看数据库各实例占用内存
Select * FROM DBA_DATA_FILES; 来查询oradata文件夹的位置
接着用du -sh * 查询每个文件的占用大小

12、linux oracle修改最大连接数

第一步,切换到oracle账户，su oracle
第二步，用sysdba登陆, sqlplus / as sysdba

1. 查看processes和sessions参数 　　SQL> show parameter processes 　　NAME TYPE VALUE 　　db_writer_processes integer
   1 　　gcs_server_processes integer 0
   　　job_queue_processes integer 10
   　　log_archive_max_processes integer 2 　　processes
   integer 50 　　SQL> show parameter sessions 　　NAME
   TYPE VALUE 　　license_max_sessions integer
   0 　　license_sessions_warning integer 0
   　　logmnr_max_persistent_sessions integer 1 　　sessions
   integer 60 　　shared_server_sessions integer
2. 修改processes和sessions值 　　SQL> alter system set processes=300 scope=spfile;(注：此处如果提示错误：ORA-32001，则说明DB是以pfile启动的，需要修改为用spfile启动，具体修改方法如下：
   SQL>show parameter spfile查一下是使用什么文件启动的。
   SQL> show parameter spfile;
   要动态修改一定要用spfile启动。如果现在是用pfile启动，可以这样切换成spfile启动：
   SQL>create spfile from pfile;
   SQL>shutdown immediate;
   SQL>startup;
   用show paramer spfile 查看如果values对应有值，表示是spfile启动的。否则就是pfile启动的。) 　　系统已更改。 　　SQL> alter
   system set sessions=335 scope=spfile; 　　系统已更改。
3. 修改processes和sessions值必须重启oracle服务器才能生效 　　ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下：
   　　sessions=(1.1*process+5) 第三步：重启oracle，SQL> shutdown immediate;
   SQL>startup;
   8> oracle基本原理：
   https://www.jianshu.com/p/9401f0a96395

三、 sqlserver数据库操作命令集：
 
1、查看当前数据库表、视图、列的数量:

SELECT
t.table_num,
v.view_num, c.column_num, (t.table_num + v.view_num + c.column_num + 1)
count_all
FROM
(SELECT
count(*) table_num FROM information_schema.TABLES  where table_schema = 'test' and table_type =
'BASE TABLE' GROUP BY table_schema) t,
(SELECT
count(*) view_num FROM information_schema.TABLES  where table_schema = 'test' and table_type =
'VIEW' GROUP BY table_schema) v,
(SELECT count(*) column_num FROM
information_schema.COLUMNS WHERE table_schema = 'test') c

2、查看数据库所有的表：

SELECT * FROM
information_schema.TABLES  where
table_schema = 'test' and table_type = 'BASE TABLE' GROUP BY table_schema

3、查看数据库所有的视图:

SELECT * FROM
information_schema.TABLES  where
table_schema = 'test' and table_type = 'BASE TABLE' GROUP BY table_schema

4、查看数据所有的列：
SELECT * FROM
information_schema.COLUMNS WHERE table_schema = ‘test’

5、服务器端常用命令:

1> 启动和停止sqlserver数据库服务:
net start/stop/status mssqlserver

2> Sqlserver支持的数据类型:

数值类型：bigint、int、smallint、tinyint、bit、decimal、numeric、money、smallmoney、float、real
日期/时间类型: datetime、smalldatetime、date、time、timestamp 文本和图像类型:
text、ntext、image 字符数据类型:
char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max) 二进制数据类型:
binary、varbinary、varbinary(max) 其他数据类型:
sql_variant、uniqueidentifier、xml、cursor、table

 
四、PostgreSQL数据库常见问题：
 
1> postgresql数据库如何登录：
su - postgres 切换到postgres用户
psql -U postgres -d postgres 使用postgres用户登录(这样默认进去的数据库也是postgres)

2> 如何切换数据库：
\c mydb
3> 如何赋权：

**1、GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mdmp;  //赋予当前数据所有表所有权限给mdmp用户
**2、GRANT ALL PRIVILEGES ON tuser TO wechart;    //赋予wechart用户，tuser表的所有权限

4> 如何删除用户/角色:

**1、drop user（role） test;  但是删除的时候经常有依赖而无法删除。接着可以根据依赖哪个数据库的提示，来回收权限：
revoke  all on database postgres from test;
**2、新建用户：create user test with password '';
**3、新建数据库：create database dbtest owner username;