分类:数据库


SQLServer数据库转MySQL数据库


准备工作:
1.安装的有SQLServer服务并有对应的数据库
2.安装的有Mysql数据库并创建一个与SQLServer数据库同名的数据库
3.安装的有Navicat Premium数据库可视化工具

开始转换:
1.打开Navicat并配置两个数据库的连接
2.打开刚创建的MySQL数据库并点击右侧的“导入向导”
3.选中ODBC点击下一步
4.选中导入从中的Microsoft OLE DB Provider for SQL Server并点击下一步
5.输入用户名和密码并选择对应的数据库测试连接
6.如连接成功就可疯狂下一步
7.在下一步的过程中我们可以对要同步的表进行筛选
8.确认表之后向导会对表结构进行预测,你可以确认下是否有要调整的
9.向导提供条件查询,如果有太多不想要的数据或者字段类型有问题导致导入出错可以设置这里。
10.最后向导模式选择添加
11.大功告成

补充:
这个东西对SQLServer转MySQL数据类型转换的支持不是很好,要手动设置。

但是导入数据没有问题,最好的方式是:
先使用工具:mss2sql将SQLServer的表转换成MYSQL的表,然后在使用此工具的ODBC导数据。
http://blog.csdn.net/andrew_wx/article/details/6832404

问题记录:
1.SQLServer数据导入到MySQL时,mss2sql对bit类型的处理不太好,
所以有bit类型的字段表导入后要更改长度为8,否则后续的导入操作将会失败。
2.数据导入的时候,如果是局域网数据库,那么要选择的类型为:
SQL Native Client
3.选择连接属性时要慎重,如果中间不正确则确认其它几种属性的作用
选择正确的属性

4.SQL要修改的地方
Insert自增列要给Null(如果是简写不写列名的话);
分页大致上都要改为MySQL的Limit;
表名不要出现dbo.等前缀;

5.主键自增等字段设置会出现丢失,切记检查一下

PostgreSQL表修改


-- 修改表字段类型
alter table '表名' alter  COLUMN '字段名' type '要修改的字段类型' ;
-- 添加表字段
ALTER TABLE '表名' ADD '字段名' '要修改的字段类型';
-- 删除表字段
ALTER TABLE '表名' DROP '字段名'; 
-- 表的重命名
ALTER TABLE '表名' RENAME TO '新表名';
-- 更改列的名字
ALTER TABLE '表名' RENAME '字段名' to '新字段名';

PostgreSQL创建只读账号


-- 回收创建表的权限
 revoke create on schema public from public;
-- 创建账号
create user readonly with encrypted password 'pwd';
alter user readonly set default_transaction_read_only=on;
-- 给只读账号连接数据的权限
grant connect on database 数据库名 to readonly;
-- 在指定数据库下执行
grant usage on schema 可配置多个schema to readonly;
grant select on all sequences in schema public to readonly;
grant select on all tables in schema public to readonly;
-- 新建的表也要给只读账号权限
alter default privileges in schema public grant select on tables to readonly;
说明:只做记录备用,未测试;
     该命令存在缺陷,如一个库存在多个schema需一一赋予权限,可能并非是最好的处理方式

PostgreSQL物化视图


-- 物化视图用于固定查询的结果
-- 需要定期更新,相当于重新执行查询命令
-- 主要用于替代查询时间过长且数据不会发生很大变化的情况
-- 创建语法:
 CREATE MATERIALIZED VIEW '视图名称' AS '查询命令';
-- 更新语法:
 REFRESH MATERIALIZED VIEW '视图名称'

PostgreSQL查询数据库的连接信息


select * from pg_stat_activity;
-- pg_stat_activity为系统视图
-- 可根据where限定数据库名,连接ip,表名等字段查询相关信息
query_start:active状态的查询开始时间,如果状态不是active的,那么就是最后一次查询开始的时间

state:运行状态,可以为几种值。
active:正在执行查询;
idle:等待新的命令;
idle in transaction:后端是一个事务,但是尚未执行查询;
idle in transaction(aborted):和idle in transaction类似,除了事务执行出错。

query:执行的查询文本(即SQL)。
如果状态是active,那么就是正在执行的SQL;
如果是其他状态,则展示最后一次执行的SQL。

所以可以用:
select count(*) from pg_stat_activity where state='idle';
查询闲置连接数。如果数字过大,可以认为是有问题的(如连接忘记关闭)。
如果想进一步定位到有问题的SQL,可以如下查询:
select query,count(*) as num from pg_stat_activity where state='idle' group by query order by num desc;
杀掉进程
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作

MySQL字符串拼接


--实例为拼接date+time

--方法一(concat(s1,s2,...))

concat(DATE_FORMAT($date,'%Y-%m-%d'),' ',$time)

--方法二(concat_ws(分割符,s1,s2,...))

CONCAT_ws(' ',DATE_FORMAT($date,'%Y-%m-%d'),$time)


PostgreSQL跨库联查


--导入支持(windows下使用,其他环境不支持)

create extension dblink

--方法一(缺陷:默认端口号5432)

select * from
dblink ( 'host=$IP port=$端口号 dbname=$数据库名 user=$用户名 password=$密码','$sql') as a(
$表结构(类似于建表命令 字段名 字段类型,....)
)

--方法二

--建立连接
SELECT dblink_connect('$连接名(自定义)','host=$IP dbname=$数据库名 user=$用户名 password=$密码');
--查看连接
select dblink_get_connections();

--断开所有连接
select dblink_disconnect();

--断开指定连接
select dblink_disconnect('$连接名');

--查询(如创建连接时没有定义连接名,则此处可省略)
SELECT * FROM dblink('$连接名','$sql')AS a ($表结构);

PostgreSQL查询数据库中所有表的行数


--联合两层schema并比较对应表的行数是否相等(需在vacuum后才准确,否则会有增删浮动)

SELECT a.relname,a.reltuples merge,b.reltuples center,a.reltuples=b.reltuples FROM (
SELECT relname, reltuples
FROM pg_class r JOIN pg_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'merge'
) a FULL JOIN (
SELECT relname, reltuples
FROM pg_class r JOIN pg_namespace n
ON (relnamespace = n.oid)
WHERE relkind = 'r' AND n.nspname = 'center'
) b ON a.relname=b.relname

postgresql根据结果集中某字段限定该字段相同值只获取20条


SELECT
T .company_id,
T .business_description_cn,
T .business_scope_cn,
T .company_tag_cn,
T .industry_id,
T .industry,
T .company_name_cn
FROM
(
SELECT
company_id,
business_description_cn,
business_scope_cn,
company_tag_cn,
industry_id,
config."dictionary".name_cn AS industry,
company_name_cn,
ROW_NUMBER() OVER(PARTITION BY industry_id) AS ROW
FROM
company_profile
LEFT JOIN config."dictionary" ON company_profile.industry_id = config."dictionary". ID
WHERE
industry_id IS NOT NULL
AND company_status_id LIKE '2%'
) T WHERE ROW <= 20

postgresql创建简单搜索视图


--创建search_company
CREATE MATERIALIZED VIEW search_company AS SELECT DISTINCT a.company_id,
a.ticker,
a.company_status_id,
a.company_name_cn,
a.company_name_en,
a.company_shortname_cn,
a.company_shortname_en,
b.brand_name,
a.year_founded,
c.name_cn AS company_status_name_cn,
c.name_en AS company_status_name_en
FROM ((company_profile a
LEFT JOIN brand_info b ON (((a.company_id)::text = (b.company_id)::text)))
LEFT JOIN config.dictionary c ON (((a.company_status_id)::text = (c.id)::text)))
WHERE a.company_status_id!='3.3'
ORDER BY a.ticker;

--导入自定义函数(扩展包)
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION zhcfg (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION zhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;
create extension pg_trgm;
create or replace function record_to_text(anyelement) returns text as $$
select $1::text;
$$ language sql strict immutable;

--创建全文索引
create index idx_search_company_gist on search_company using gist (record_to_text(search_company) gist_trgm_ops);
create index idx_search_company_gin on search_company using gin (record_to_text(search_company) gin_trgm_ops);

--检测索引是否正常
/*+ BitmapScan(search_company idx_search_company_gin) */ select * from search_company where record_to_text(search_company) ~ '经纬' and company_status_id like'3%' limit 99;