postgreSQL vs MySQL 命令行对比
原文：http://www.cnblogs.com/qiyebao/p/4749146.html
# 服务启动
PostgreSQL
- 1)#service postgresql start
- 2)#/etc/init.d/postgresql start
- 3)#su – postgresql
- $pg_ctl start
MySQL
- 1)#service mysqld start
- 2)#/etc/init.d/mysqld start
- 3)#safe_mysqld&
# 第一次进入数据库
PostgreSQL
\#su – postgres
$createdb （建名为postgres的数据库）
$psql
1
2
3
2
3
MySQL
\#mysql
mysql> (出现这个提示符说明成功)
1
2
2
# 创建用户：(用户Ajian，密码：123)
PostgreSQL
#su – postgres
$psql
=#create user ajian with password ‘123’;
1
2
3
2
3
MySQL
#grant all privileges on *.* to ajian@”%” identified by “123″
-- (注意：同还可以分配权限，这里是ALL)
1
2
2
# 创建数据库
PostgreSQL
#su – postgres
$psql
=#create database My with owner = ajian template = template1 encoding=’UNICODE’;
1
2
3
2
3
MySQL
1)#mysql
Mysql>create database My;
2)#mysqladmin create My
1
2
3
2
3
# 查看用户和数据库
PostgreSQL
#su – postgres
$psql
=#\l (查看数据库)
=#\du (查看用户)
=#\c 从一个数据库中转到另一个数据库中，如template1=# \c sales 从template1转到sales
1
2
3
4
5
2
3
4
5
MySQL
1)#mysql
Mysql>show databases; (看数据库)
2)#mysqlshow
use dbname;
1
2
3
4
2
3
4
# 新建用户登录
PostgreSQL 1、首先修改配置文件
# vi /var/lib/pgsql/data/pg_hba.conf(在最后加)
host all all 127.0.0.1 255.255.255.255 md5
2、再重启服务： #service postgresql restart
3、登录：#psql –h 127.0.0.1 –U ajian My
Password:
**MySQL**
```bash
1)#mysql –u ajian –p (带口令登录)
2)#mysql
Mysql>use My;
(不带口令登录一般用于本机)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 创建表(employee)
PostgreSQL
=#create table employee(
(#employee_id int primary key,
(#name char(8),
(#sex char(2));
1
2
3
4
5
6
7
2
3
4
5
6
7
MySQL
>create table employee(
->employee_id int primary key,
->name char(8),
->sex char(2));
1
2
3
4
5
6
7
2
3
4
5
6
7
# 查看表
PostgreSQL
=#\dt
1
MySQL
>show tables;
1
# 查看表的结构：
PostgreSQL
=#\d employee
1
MySQL
>sescribe employee;
1
# 向表中添加数据：
PostgreSQL
=#insert into employee values
-#(‘1’,’zhang’,’F’);
-#(‘2’,’chen’,’M’,);
1
2
3
4
5
2
3
4
5
MySQL
>insert into employee values
->(‘1’,’zhang’,’F’);
->(‘2’,’chen’,’M’,);
1
2
3
4
5
2
3
4
5
# 查看表的数据
PostgreSQL
=#select * from emlpoyee
1
MySQL
>select * from emlpoyee;
1
# 索引相关
PostgreSQL
## 创建索引(IN_employee)
=#create index IN_employee on employee(name);
## 查看索引
=#\di
## 删除索引
=#drop index IN_employee on employee;
## 重建索引
=#reindex table employee;(重建employee所有的)
=#reindex index IN_employee;(重建指定的)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
MySQL
## 创建索引(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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 删除表
PostgreSQL
=#drop table employee;
1
MySQL
>drop table employee;
1
# 删除数据库：(注意命令前面的标志)
PostgreSQL
1)=#drop database ajian;
2)$dropdb ajian
1
2
3
2
3
MySQL
1）>drop database ajian;
2)#mysqladmin drop ajian
1
2
3
2
3
上次更新: 2023/03/19, 15:09:33