关闭

mysql, PostgreSQL,Oracle对比——sql,驱动和数据库URL

标签: mysqlpostgresql数据库
408人阅读 评论(0) 收藏 举报
分类:

Copyright 2016 by 蔡舒啸 保持署名-非商业性使用-相同方式共享 Creative Commons BY-NC-ND 3.0


目录:


mysql, postgreSQL, Oracle
不保证正确,不定时更新
如果和官网文档不一致,一切以各数据库官网文档为准

特点 Postgre mysql oracle
大小写敏感性 详情点我
加”“的表名区分大小写, 如”sUser”
默认window 表名不区分1,linux变量名,库名,表名和表的别名区分,列名与列的别名不区分 关键字 用户 密码 字段名 表名 序列名 触发器名等是不区分的;具体字段值就区分大小写。
数字转字符 to_char(123,’格式化语法’)2 CAST(123 AS CHAR) to_char(123),CAST(123 AS CHAR)3
导出数据表 pgAdminIII->菜单栏->工具->查询工具->输入任意select语句->选中sql语句F5执行->查询工具窗口菜单栏->导出->注意这里的分隔符,引用符,之后导入要用到

右键pgAdminIII主窗口左侧数据表->导入->选择文件,文件格式、分隔符、引用符要和之前导出的一致->导入

数据库驱动

spring oracle

<?xml version="1.0" encoding="UTF-8"?>  
<beans:beans xmlns:beans="http://www.springframework.org/schema/beans"  
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"  
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"  
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd  
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd  
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">  

<beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
<beans:property name="driverClassName" value="oracle.jdbc.OracleDriver" />  
<beans:property name="url" value="jdbc:oracle:thin:@localhost:1521/test" />  
<beans:property name="username" value="developer" />  
<beans:property name="password" value="developer" />  
</beans:bean>  


</beans:beans>  

postgre

<beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
<beans:property name="driverClassName" value="org.postgresql.Driver" />  
<beans:property name="url" value="jdbc:postgresql://127.0.0.1:5432/test" />  
<beans:property name="username" value="xxx" />  
<beans:property name="password" value="xxx" />  
</beans:bean>  


</beans:beans>  

sql语句

功能 mysql PostgreSQL
建表 CREATE TABLE haha
(
city varchar(80) not null primary key,
tsTime timestamp DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO haha(city) VALUES(‘FSD’),(‘ABC’),…;
INSERT INTO haha VALUES(‘FSD2’);
select * from haha;
delete from haha where city=’FSD2’;
改某字段 UPDATE table_name SET column1 = value1, column2 = value2… WHERE …;
注意:执行以后,该记录移至数据表的最后一行,所以无主键的表不能使用UPDATE,会导致乱序
增加列 ALTER TABLE table_name ADD column_name datatype;
删除列 ALTER TABLE table_name DROP COLUMN column_name;
改列类型 ALTER TABLE table_name MODIFY COLUMN column_name datatype;
添加NOT NULL约束 ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
添加PRIMARY KEY约束 ALTER TABLE table_name ADD CONSTRAINT myPK PRIMARY KEY (column1, column2…);
添加唯一约束 ALTER TABLE table_name ADD CONSTRAINT myUnique UNIQUE(column1, column2…);
添加CHECK约束 ALTER TABLE table_name ADD CONSTRAINT myCheck CHECK (CONDITION);
删除约束 ALTER TABLE table_name DROP CONSTRAINT myXxx;

约束

支持的约束 mysql PostgreSQL
NOT NULL T
UNIQUE T
PRIMARY Key T
FOREIGN Key T
CHECK所有值满足一定条件 T
EXCLUSION 排除约束,确保指定的列(S)或表达式(次)使用指定运算(​​次) T

使用序列Sequence

1 postgreSQL

转自http://www.cnblogs.com/wuhenke/archive/2010/08/01/1789721.html
4.11.1)我怎样创建一个序列号/自动递增的字段?
PostgreSQL 支持 SERIAL 数据类型。它在字段上自动创建一个序列和索引。例如:

CREATE TABLE person (
id SERIAL,
name TEXT
);
会自动转换为:

CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
id INT4 NOT NULL DEFAULT nextval(‘person_id_seq’),
name TEXT
);
参考 create_sequence 手册页获取关于序列的更多信息。

4.11.2)我如何获得一个插入的序列号的值?
一种方法是在插入之前先用函数 nextval() 从序列对象里检索出下一个 SERIAL 值,然后再显式插入。使用 4.11.1 里的例表,可用伪码这样描述:

new_id = execute(“SELECT nextval(‘person_id_seq’)”);
execute(“INSERT INTO person (id, name) VALUES (new_id, ‘Blaise Pascal’)”);
这样还能在其他查询中使用存放在 new_id 里的新值(例如,作为 person 表的外键)。 注意自动创建的 SEQUENCE 对象的名称将会是

__seq, 这里 table 和 serialcolumn 分别是你的表的名称和你的 SERIAL 字段的名称。

类似的,在 SERIAL 对象缺省插入后你可以用函数 currval() 检索刚赋值的 SERIAL 值,例如:

execute(“INSERT INTO person (name) VALUES (‘Blaise Pascal’)”);
new_id = execute(“SELECT currval(‘person_id_seq’)”);
4.11.3)使用 currval() 会导致和其他用户的冲突情况(race condition)吗?
不会。currval() 返回的是你本次会话进程所赋的值而不是所有用户的当前值。

4.11.4)为什么不在事务异常中止后重用序列号呢?为什么在序列号字段的取值中存在间断呢?
为了提高并发性,序列号在需要的时候赋予正在运行的事务,并且在事务结束之前不进行锁定, 这就会导致异常中止的事务后,序列号会出现间隔。

2 Mysql

自定义function - for循环

PostgreSQL

创建function

create or replace function 我的函数() returns void AS $$
declare ii integer;
begin
ii:=1;
FOR ii IN 1..5 LOOP
insert into 表B (select * from 表A where 表A.id=ii);
end loop;
end;
$$ LANGUAGE plpgsql;

调用function

select 我的函数();

用户管理

postgre

用户和角色(用户组)

pg简化了用户管理,组和角色都是role,用户和角色的区别是角色没有login权限。

功能 命令
查看用户和角色(用户组) \du
建角色(用户组) create role TestRole1;
建用户 create user TestUser1 with password ‘TestUser1’ login in role TestRole1;
添加用户到角色 GRANT group_role TO role1, … ;
从角色删除用户 REVOKE group_role FROM role1, … ;

用户权限

权限 PostgreSQL
增加 GRANT SELECT,INSERT,UPDATE,DELETE on ebd_sp_access_log,ebd_sp_calldmz_fang_log TO pierdata;
删除 REVOKE SELECT,INSERT,UPDATE,DELETE on ebd_sp_access_log,ebd_sp_calldmz_fang_log FROM pierdata;

一个例子

d0home=# create role dba;
d0home=# create user foo_admin with password '123456' login in role dba
d0home=# \du
显示如下

                                        List of roles
     Role name      |                         Attributes                         | Member of 
--------------------+------------------------------------------------------------+-----------
 dba                | Cannot login                                               | {}
 foo_admin          |                                                            | {dba}
 postgres           | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

mysql

高级技巧

where子句and(逻辑与)的优化

where子句多个and
postgre从左到右执行
mysql

巧用limit增加查询效率

表t_foo有100000行数据, 我们要取第一行.
select * from t_foo; – 需要5s返回
select * from t_foo limit 1; – 20ms返回

用group by,列出表中所有的不同的城市

表proj_name :
上海 xx小区
上海 xx小区
北京 xx小区
北京 xx小区
南京 xx小区

SELECT city FROM proj_name as t group by city;

结果:
上海
北京
南京

Tips: 在select指定的字段要么就要包含在Group By语句的后面,作为分组的依据;要么就要被包含在聚合函数中。

引自postgresql 8.1 用户手册:

聚合函数 功能
avg(expression) 所有输入值的均值(算术平均) smallint, int, bigint, real, double precision, numeric, 或 interval. 对于任何整数类型输入,结果都是 numeric 类型。 对于任何浮点输入,结果都是 double precision 类型。 否则和输入数据类型相同。 所有输入值的平均(算术平均)。
bit_and(expression) smallint, int, bigint, 或者 bit 和参数数据类型相同 所有非空输入值的按位 AND (与),如果没有,则为空
bit_or(expression) smallint, int, bigint, 或者 bit 和参数数据类型相同 所有非空输入值的按位 OR (或),如果没有,则为空
bool_and(expression) bool bool 如果所有输入值都是真,则为真,否则为假
bool_or(expression) bool bool 如果至少有一个输入值为真,则为着,否则为假
count(*) bigint 输入值的个数
count(expression) 任意 bigint 计算那些 expression 非 NULL 的输入的个数。
every(expression) bool bool 等效于 bool_and
max(expression) 任何数组,数值,字串或者日期/时间类型 与参数同类型 所有输入值中, expression 的最大值
min(expression) 任何数组,数值,字串或者日期/时间类型 与参数同类型 所有输入值中, expression 的最小值
stddev(expression) smallint, int, bigint, real, double precision, 或 numeric. 浮点数参数时是double precision, 否则 numeric。 输入值的标准采样偏差(sample standard deviation)
sum(expression) smallint, int, bigint, real, double precision, numeric, 或者 interval 对 smallint 或 int输入 输出类型为 bigint。对于bigint输入输出类型为 numeric, 浮点数输入的结果是 double precision。 否则和输入数据类型相同。 所有输入值的 expression 的总和
variance(expression) smallint, integer, bigint, real, double precision, 或者 numeric. 浮点数参数是 double precision, 否则是 numeric。 输出值的采样方差(标准采样偏差的平方)。

Having与Where的区别

where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,where条件中不能包含聚组函数,使用where条件过滤出特定的行。

having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数,使用having 条件过滤出特定的组,也可以使用多个分组标准进行分组。

查询第几行的数据

查第3行数据:
select * from t_foo OFFSET 3 limit 1 ;

说明:
OFFSET 3 从第3行开始查询
limit 1 仅查询一行;

根据A表某一列的数据查B表

select * from B where B.id in (select id from A) order by id;


  1. 更改windows中MySQL的设置具体操作:在MySQL的配置文件my.ini中增加一行:lower_case_table_names = 0。其中, 0区分1不区分。
  2. 请看postgre手册http://www.yiibai.com/manual/postgresql/functions-formatting.html
  3. 支持的类型见Oracle各个版本的手册
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:254153次
    • 积分:3824
    • 等级:
    • 排名:第8325名
    • 原创:122篇
    • 转载:63篇
    • 译文:7篇
    • 评论:32条
    最新评论