postgre和mysql_PostgreSQL与MySQL命令的使用比较

服务启动：

1)#service postgresql start

2)#/etc/init.d/postgresql start

3)#su  postgresql

$ pg_ctl start

PostgreSQL的进程号:1210、1207、

服务启动：

1)#service mysqld start

2)#/etc/init.d/mysqld start

3)#safe_mysqld&

MySQL的进程号为1663

第一次进入数据库：

#su – postgres

$createdb  (建名为postgres的数据库)

$psql

第一次进入数据库：

#mysql

mysql>    (出现这个提示符说明成功)

创建用户：(用户Ajian，密码：123)

#su – postgres

$ psql

=#create user ajian with password ‘123’

创建用户：(用户Ajian，密码：123)

#grant all privileges on *.* toajian@"%" identified by "123"

(注意：同还可以分配权限，这里是ALL)

创建数据库(My)：

#su – postgres

$psql

=#create database My with owner = ajian template = template1 encoding=’UNICODE’;

创建数据库(My)：

1)#mysql

Mysql>create database My;

2)#mysqladmin create My

查看用户和数据库：

#su – postgres

$ psql

=#\l         (查看数据库)

=#\du        (查看用户)

查看用户和数据库：

1)#mysql

Mysql>show databases;   (看数据库)

2)#mysqlshow

新建用户登录：

(首先修改配置文件)

# vi /var/lib/pgsql/data/pg_hba.conf(在最后加)

host all all 127.0.0.1 255.255.255.255 md5

再重启服务：#service postgresql restart

登录：#psql –h 127.0.0.1 –U ajian My

Password:

新建用户登录：

1)#mysql –u ajian –p  (带口令登录)

2)#mysql

Mysql>use My;

(不带口令登录一般用于本机)

创建表(employee)：

=#create table employee(

(#employee_id int primary key,

(#name char(8),

(#sex char(2));

创建表：

>create table employee(

->employee_id int primary key,

->name char(8),

->sex char(2));

查看表：

=#\dt

查看表：

>show tables;

查看表的结构：

=#\d employee

查看表的结构：

>sescribe employee;

向表中添加数据：

=#insert into employee values

-#(‘1’,’zhang’,’F’);

-#(‘2’,’chen’,’M’,);

向表中添加数据：

>insert into employee values

->(‘1’,’zhang’,’F’);

->(‘2’,’chen’,’M’,);

查看表的数据：

=#select * from emlpoyee

查看表的数据：

>select * from emlpoyee;

创建索引(IN_employee)：

=#create index IN_employee on employee(name);

查看索引：

=#\di

删除索引：

=#drop index IN_employee on employee;

重建索引：

=#reindex table employee;(重建employee所有的)

=#reindex index IN_employee;(重建指定的)

创建索引(IN_employee)：

1)>create index IN_employee on employee(name);

2)>alter table employee add index IN_employee(name);

查看索引：

>show index from employee;

删除索引：

1)>drop index IN_employee on employee;

2)>alter table emlpoyee drop index IN_employee;

删除表：

=#drop table employee;

删除表：

>drop table employee;

删除数据库：(注意命令前面的标志)

1)=#drop database ajian;

2)$dropdb ajian

删除数据库：(注意命令前面的标志)

1)>drop database ajian;

2)#mysqladmin drop ajian

最近学习PGSQL。来比较一下他和MYSQL自增字段的不同点。

1、自增序列。MYSQL从最后一个ID自增。

测试数据。

1,I love this girl.

2,I hate this girl.

3,Sheismy girl.

4,Sheisyour girl.

MYSQL：mysql>createdatabasetest;

Query OK,1rowaffected(0.10 sec)

mysql>use test

Databasechanged

mysql>createtablet(id intnotnullauto_incrementprimarykey,

->usernamechar(20)notnull);

Query OK,0rowsaffected(0.02 sec)

mysql>loaddatainfile'/tmp/test.sql'intotablet fields terminatedby',';

Query OK,4rowsaffected(0.00 sec)

Records:4 Deleted:0 Skipped:0 Warnings:0

mysql>select*fromt;

+----+-------------------+

|id|username|

+----+-------------------+

|1|I love this girl.|

|2|I hate this girl.|

|3|Sheismy girl.|

|4|Sheisyour girl.|

+----+-------------------+

4rowsinset(0.00 sec)

mysql>insertintotvalues(6,'This is inserted');

Query OK,1rowaffected(0.00 sec)

mysql>insertintot(username)values('This is last');

Query OK,1rowaffected(0.00 sec)

mysql>select*fromt;

+----+-------------------+

|id|username|

+----+-------------------+

|1|I love this girl.|

|2|I hate this girl.|

|3|Sheismy girl.|

|4|Sheisyour girl.|

|6|Thisisinserted|

|7|Thisislast|

+----+-------------------+

mysql>truncatetablet;

Query OK,0rowsaffected(0.00 sec)

mysql>insertintot(username)values('This is last');

Query OK,1rowaffected(0.00 sec)

mysql>insertintot(username)values('This is last');

Query OK,1rowaffected(0.00 sec)

mysql>insertintot(username)values('This is last');

Query OK,1rowaffected(0.00 sec)

mysql>select*fromt;

+----+--------------+

|id|username|

+----+--------------+

|1|Thisislast|

|2|Thisislast|

|3|Thisislast|

+----+--------------+

3rowsinset(0.00 sec)

PGSQL从1开始逐个尝试。

[root@localhost~]#psql-Upostgres-hlocalhost

。。。

postgres=#createdatabasetest;

CREATEDATABASE

postgres=#\c test

You are now connectedtodatabase"test".

test=#createtablet(id serialnotnull,usernamechar(20)notnull);

NOTICE:CREATETABLEwillcreateimplicitsequence"t_id_seq"forserialcolumn"t.id"

CREATETABLE

test=#\d t;

Table"public.t"

Column|Type|Modifiers----------+---------------+------------------------------------------------

id|integer|notnulldefaultnextval('t_id_seq'::regclass)

username|character(20)|notnull

test=#copytfrom'/tmp/test.sql'withcsv;

COPY4

test=#select*fromt;

id|username----+----------------------

1|I love this girl.

2|I hate this girl.

3|Sheismy girl.

4|Sheisyour girl.

(4rows)

test=#insertintotvalues(6,'This is inserted');

INSERT0 1

test=#insertintot(username)values('This is last');

ID1重复

ERROR:duplicatekeyviolatesuniqueconstraint"t_pkey"

test=#insertintot(username)values('This is last');

ID2重复

ERROR:duplicatekeyviolatesuniqueconstraint"t_pkey"

test=#insertintot(username)values('This is last');

。。。

ID5没有。插入

INSERT0 1

test=#insertintot(username)values('This is last');

ID6又重复

ERROR:duplicatekeyviolatesuniqueconstraint"t_pkey"

test=#insertintot(username)values('This is last');

...

INSERT0 1

test=#insertintot(username)values('This is last');

INSERT0 1

test=#insertintot(username)values('This is last');

INSERT0 1

test=#select*fromt;

id|username----+----------------------

1|I love this girl.

2|I hate this girl.

3|Sheismy girl.

4|Sheisyour girl.

6|Thisisinserted

5|Thisislast

7|Thisislast

8|Thisislast

9|Thisislast

(9rows)

看一下DELETE操作。

test=#deletefromt;

DELETE9

test=#insertintot(username)values('This is last');

INSERT0 1

test=#insertintot(username)values('This is last');

INSERT0 1

test=#insertintot(username)values('This is last');

INSERT0 1

test=#select*fromt;

id|username----+----------------------

10|Thisislast

11|Thisislast

12|Thisislast

(3rows)

这个和MYSQL一样的。

TRUNCATE虽然和MYSQL一样可以快速清空表数据。可是ID还是从最后一个开始增加的，如果想从１开始的话，就得用setval函数来设置。

test=#truncatetablet;

TRUNCATETABLE

test=#insertintot(username)values('This is last');

INSERT0 1

test=#insertintot(username)values('This is last');

INSERT0 1

test=#insertintot(username)values('This is last');

INSERT0 1

test=#select*fromt;

id|username----+----------------------

13|Thisislast

14|Thisislast

15|Thisislast

(3rows)

至于怎么从1重新开始。还在学习中。。。

2、得到刚刚插入的自增ID。

在MYSQL里面：

mysql>truncatetablet;

Query OK,0rowsaffected(0.00 sec)

mysql>insertintot(username)values('This is last');

Query OK,1rowaffected(0.00 sec)

mysql>selectlast_insert_id();

+------------------+

|last_insert_id()|

+------------------+

|1|

+------------------+

1rowinset(0.00 sec)

在POSTGRESQL里面：

test=#droptablet

test-#;

DROPTABLE

test=#createtablet(id serialnotnullprimarykey,usernamechar(20)notnull);

NOTICE:CREATETABLEwillcreateimplicitsequence"t_id_seq"forserialcolumn"t.id"

NOTICE:CREATETABLE/PRIMARYKEYwillcreateimplicitindex"t_pkey"fortable"t"

CREATETABLE

test=#\d t

Table"public.t"

Column|Type|Modifiers----------+---------------+------------------------------------------------

id|integer|notnulldefaultnextval('t_id_seq'::regclass)

username|character(20)|notnull

Indexes:

"t_pkey"PRIMARYKEY,btree(id)

test=#insertintot(username)values('This is test name');

INSERT0 1

test=#select*fromt;

id|username----+----------------------

1|Thisistest name

(1row)

test=#selectcurrval('t_id_seq');

currval---------

1

(1row)

test=#

3、设置自增ID的开始值。

MYSQL:

mysql>altertablet auto_increment=3;

Query OK,1rowaffected(0.01 sec)

Records:1 Duplicates:0 Warnings:0

mysql>insertintot(username)values('This is last');

Query OK,1rowaffected(0.00 sec)

mysql>select*fromt;

+----+--------------+

|id|username|

+----+--------------+

|1|Thisislast|

|3|Thisislast|

+----+--------------+

2rowsinset(0.00 sec)

POSTGRESQL:t_girl=#selectsetval('t_id_seq',1,false);

setval--------

1

(1row)

Time:19.554 ms

t_girl=#insertintot(username)values('wangwei'),('meimei');

INSERT0 2

Time:1.882 ms

t_girl=#select*fromt;

id|username----+----------------------

1|wangwei

2|meimei

(2rows)

Time:0.598 ms