创建复合主键:create table mapping(adminId integer,roleId integer,primary key(adminId,roleId));
@H_
502_0@
@H_
502_0@ 这样我的两个字段就都成为主键了。
@H_
502_0@
@H_
502_0@ 创建符合主键的同时建立外键:
@H_
502_0@
@H_
502_0@ myd=# create table mapping(adminId integer references admin(id),roleId integer references role(id),roleId));
@H_
502_0@
@H_
502_0@ 这样我的的两个字段既是主键的组成部分,又分别参照了别的表的字段。
@H_
502_0@
@H_
502_0@ 创建符合主键的同时建立外键并级联
删除级联跟新:
@H_
502_0@
@H_
502_0@ myd=# create table mapping(adminid integer references admin(id) on delete cascade on update cascade,roleid integer references role(id) on delete cascade on update cascade,primary key(adminid,roleid));
@H_
502_0@
@H_
502_0@ 下面是
提示信息:
@H_
502_0@ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "mapping_pkey" for table "mapping"
@H_
502_0@ CREATE TABLE
@H_
502_0@
@H_
502_0@ 多表连接:
@H_
502_0@ myd=# select (admin.id,admin.name,role.name) from admin,mapping,role where admin.id=mapping.adminid and mapping.roleid=role.id;
@H_
502_0@
@H_
502_0@ 多表连接同时指定列的别名:
@H_
502_0@ myd=# select admin.id as id,admin.name as name,role.name as role from admin,role where admin.id=mapping.adminid and mapping.roleid=role.id;
@H_
502_0@
@H_
502_0@ 对已经存在的表
添加主键:
@H_
502_0@ alter table t add primary key(id);
@H_
502_0@ 其中t为表明,id为要设置为主键的字段,注意,如果字段中有重复值,会创建失败。
@H_
502_0@
@H_
502_0@ distinct去重
@H_
502_0@ select count(distinct col) from A;
@H_
502_0@
@H_
502_0@ select count(1) from (select 1 from A group by col) alias;
@H_
502_0@
@H_
502_0@ copy导入
文件和导出
文件:
@H_
502_0@ COPY 命令可以
快速的导入数据到 Postgre
sql 中,
文件格式类似CVS之类。适合批量导入数据,比 \i 和恢复数据表快。
@H_
502_0@
@H_
502_0@ 导出表数据到
文件或 STDOUT :
@H_
502_0@
@H_
502_0@ COPY tablename [(column [,...])]
@H_
502_0@ TO {'filename' | STDOUT}
@H_
502_0@ [[WITH]
@H_
502_0@ [BINARY]
@H_
502_0@ [OIDS]
@H_
502_0@ [DELIMITER [AS] 'delimiter']
@H_
502_0@ [NULL [AS] 'null string']
@H_
502_0@ [CSV [HEADER]
@H_
502_0@ [QUOTE [AS] 'quote']
@H_
502_0@ [ESCAPE [AS] 'escape']
@H_
502_0@ [FORCE NOT NULL column [,...]]
@H_
502_0@
@H_
502_0@ 导入
文件或者 STDIN 到表中:
@H_
502_0@
@H_
502_0@ COPY tablename [(column [,...])]
@H_
502_0@ FROM {'filename' | STDIN}
@H_
502_0@ [[WITH]
@H_
502_0@ [BINARY]
@H_
502_0@ [OIDS]
@H_
502_0@ [DELIMITER [AS] 'delimiter']
@H_
502_0@ [NULL [AS] 'null string']
@H_
502_0@ [CSV [HEADER]
@H_
502_0@ [QUOTE [AS] 'quote']
@H_
502_0@ [ESCAPE [AS] 'escape']
@H_
502_0@ [FORCE QUOTE column [,...]]
@H_
502_0@
@H_
502_0@ 导出表 employee 到默认
输出 STDOUT:
@H_
502_0@
@H_
502_0@ p
sql> COPY employee TO STDOUT;
@H_
502_0@ 1 JG100011 Jason Gilmore jason@example.com
@H_
502_0@ 2 RT435234 Robert Treat rob@example.com
@H_
502_0@ 3 GS998909 Greg Sabino Mullane greg@example.com
@H_
502_0@ 4 MW777983 Matt Wade matt@example.com
@H_
502_0@
@H_
502_0@ 导出表 employee 到
sql 文件:
@H_
502_0@
@H_
502_0@ p
sql> COPY employee TO '/home/smallfish/employee.
sql';
@H_
502_0@
@H_
502_0@ 从
文件导入数据:
@H_
502_0@
@H_
502_0@ p
sql> COPY employeenew FROM '/home/smallfish/employee.
sql';
@H_
502_0@ p
sql> SELECT * FROM employeenew;
@H_
502_0@ employeeid | employeecode | name | email
@H_
502_0@ ------------+--------------+---------------------+---------------
@H_
502_0@ 1 | JG100011 | Jason Gilmore | jason@example.com
@H_
502_0@ 2 | RT435234 | Robert Treat | rob@example.com
@H_
502_0@ 3 | GS998909 | Greg Sabino Mullane | greg@example.com
@H_
502_0@ 4 | MW777983 | Matt Wade | matt@example.com
@H_
502_0@ (4 rows)
@H_
502_0@
@H_
502_0@
输出对象ID(OIDS):
@H_
502_0@
@H_
502_0@ p
sql> COPY employee TO STDOUT OIDS;
@H_
502_0@ 24627 1 GM100011 Jason Gilmore jason@example.com
@H_
502_0@ 24628 2 RT435234 Robert Treat rob@example.com
@H_
502_0@ 24629 3 GS998909 Greg Sabino Mullane greg@example.com
@H_
502_0@ 24630 4 MW777983 Matt Wade matt@example.com
@H_
502_0@
@H_
502_0@ 指定导出间隔符,默认是 \t ,这里为 | :
@H_
502_0@
@H_
502_0@ p
sql>COPY employee TO STDOUT DELIMITER '|';
@H_
502_0@ 1|GM100011|Jason Gilmore|jason@example.com
@H_
502_0@ 2|RT435234|Robert Treat|rob@example.com
@H_
502_0@ 3|GS998909|Greg Sabino Mullane|greg@example.com
@H_
502_0@ 4|MW777983|Matt Wade|matt@example.com
@H_
502_0@
@H_
502_0@ 导入
文件数据,指定间隔符为 | :
@H_
502_0@
@H_
502_0@ p
sql> COPY employeenew FROM '/home/smallfish/employee.
sql' DELIMITER |;
@H_
502_0@
@H_
502_0@ 导出指定字段的数据:
@H_
502_0@
@H_
502_0@ p
sql> COPY employee (name,email) TO STDOUT;
@H_
502_0@ Jason Gilmore jason@example.com
@H_
502_0@ Robert Treat rob@example.com
@H_
502_0@ Greg Sabino Mullane greg@example.com
@H_
502_0@ Matt Wade matt@example.com
@H_
502_0@
@H_
502_0@ 为 NULL 字段设置默认值:
@H_
502_0@
@H_
502_0@ p
sql> COPY employee TO STDOUT NULL 'no email';
@H_
502_0@ Jason Gilmore no email
@H_
502_0@ Robert Treat rob@example.com
@H_
502_0@ Greg Sabino Mullane greg@example.com
@H_
502_0@ Matt Wade no email
@H_
502_0@
@H_
502_0@ 导出为CVS格式:
@H_
502_0@
@H_
502_0@ p
sql> COPY employee (name,email) TO '/home/smallfish/employee.csv' CSV HEADER;
@H_
502_0@
@H_502_0@
@H_502_0@
@H_502_0@
@H_502_0@
@H_502_0@
@H_502_0@
MysqL导出数据到文件: select *(或者字段名) from tablename into outfile "/root/aa.txt";
MysqL导入到MysqL中: load data infile "/root/aa.txt" into table tablename; table需要提前创建好。
--使用SELECT ... INTO OUTFILE 以逗号分隔字段的方式将数据导入到一个文件中:
SELECT * INTO OUTFILE 'D:\\log1.txt' FIELDS TERMINATED BY ',' FROM log.log1
--将刚刚导出的文件log1.txt导入到表log1相同结构的log2中:
LOAD DATA INFILE 'D:\\log1.txt' INTO TABLE aa.log2 FIELDS TERMINATED BY ','
-- 使用select * into outfile 导出:
SELECT * INTO OUTFILE 'd:\\test.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test.table
-- 导入
LOAD DATA INFILE '/tmp/fi.txt' INTO TABLE test.fii FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FIELDS TERMINATED BY','字段间分割符 OPTIONALLY ENCLOSED BY'"'将字段包围 对数值型无效 LINES TERMINATED BY'\n'换行符