四种关系型数据库的基本操作sql命令语句

四种数据库：MySQL、SQL server、Oracle、PostgreSQL

数据库链接
mysql: jdbc:mysql://172.18.16.200:3306/databaseName sqlserver:
jdbc:sqlserver://127.0.0.1:1433;DatabaseName=databaseName oracle:
jdbc:oracle:thin:@localhost:1521:databaseName postgresql:
jdbc:postgresql://172.18.16.203:5432/databaseName
数据库驱动
mysql: com.mysql.jdbc.Driver sqlserver: com.microsoft.sqlserver.jdbc
.SQLServerDriver oracle: oracle.jdbc.driver.OracleDriver(不通用） postgresql: org
.postgresql.Driver
获取数据库
mysql: show databases sqlserver: SELECT Name FROM Master..SysDatabases where
Name<>'master' and Name<>'model' and Name<>'msdb' and Name<>'tempdb' ORDER BY
Name oracle:select name from v$database postgresql: SELECT * FROM pg_database
where datname<>'template0' and datname<>'template1' ORDER BY datname(除模板数据库以为）
获取数据库表
mysql: select TABLE_NAME from information_schema.tables where table_schema=
'databaseName' order by TABLE_NAME sqlserver: SELECT * FROM
INFORMATION_SCHEMA.TABLESORDER BY TABLE_NAME oracle: select TABLE_NAME from
all_tablesWHERE owner='databaseName' order by TABLE_NAME postgresql: SELECT *
FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%'
ORDER BY tablename
获取视图
mysql: select TABLE_NAME from information_schema.VIEWS where table_schema=
'databaseName' order by TABLE_NAME sqlserver: select name from sys.objects where
type = 'V' oracle: select TABLE_NAME from all_views where table_schema=
'databaseName' order by TABLE_NAME postgresql: SELECT * FROM pg_views WHERE
schemaname='public'
创建、删除数据库
mysql、sqlserver、oracle： create database databaseName postgresql: create
database databaseName template templateDatabaseName 删除数据库: drop database
databaseName
postgresql 断开数据库链接会话语句: SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname='"+databaseName+"' AND pid<>pg_backend_pid()

获取数据库表列信息
mysql: select COLUMN_NAME AS columnName, DATA_TYPE AS dataType,
CHARACTER_OCTET_LENGTH colLength, IS_NULLABLEAS isNull, COLUMN_DEFAULT AS
defaultValue, COLUMN_KEYAS isPrimarykey from information_schema.columns where
table_name ='tableName' and table_schema = (select database()) sqlserver: SELECT
a.name columnName, b.name datatype, (CASE WHEN ( SELECT COUNT (*) FROM
sysobjectsWHERE ( name IN ( SELECT name FROM sysindexes WHERE (id = a.id) AND (
indidIN ( SELECT indid FROM sysindexkeys WHERE (id = a.id) AND ( colid IN (
SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype ='PK')) > 0 THEN 1 ELSE 0 END ) isPrimaryKey, CASE WHEN (charindex('int'
, b.name) >0) OR (charindex('time', b.name) > 0) THEN NULL ELSE
COLUMNPROPERTY(a.id, a.name,'PRECISION') END AS colLength, ( CASE WHEN
a.isnullable =1 THEN 1 ELSE 0 END ) isNull, Replace( Replace(IsNull(e. TEXT, ''
),'(', ''), ')', '' ) defaultValue FROM syscolumns a LEFT JOIN systypes b ON
a.xtype = b.xusertypeINNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U'
AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id LEFT
JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.class AND f.minor_id = 0 WHERE
b.nameIS NOT NULL AND d.name = N'tableName' oracle: 待定 postgresql: select
column_nameas columnName,data_type as dataType,
coalesce(character_maximum_length,numeric_precision,-1) as colLength, case
is_nullablewhen 'NO' then 0 else 1 end as canNUll,column_default as
defaultValue,case when position('nextval' in column_default)>0 then 1 else 0 end
as IsIdentity, case when b.pk_name is null then 0 else 1 end as isPrimaryKey
from information_schema.columns left join ( select pg_attr.attname as
colname,pg_constraint.connameas pk_name from pg_constraint inner join pg_class
on pg_constraint.conrelid = pg_class.oid inner join pg_attribute pg_attr on
pg_attr.attrelid = pg_class.oidand pg_attr.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attr.atttypid where pg_class.relname =
'tableName' and pg_constraint.contype='p' ) b on b.colname =
information_schema.columns.column_namewhere table_schema='public' and
table_name='tableName'
表结构修改
alter table 表名 alter column UnitPrice decimal(18, 4) not null
修改、删除主键
mysql: ALTER TABLE `test2` DROP PRIMARY KEY ,ADD PRIMARY KEY (主键)
持续更新中…

• « 上一篇：Linux下batch命令使用教程详解
• » 下一篇：contentEditable实现div可编辑，控制插入节点（兼容IE）