Postgresql installation and basic operation
Installation
1 add source
Sudo rpm-Uvh https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-3.noarch.rpm
2 installation
Sudo yum install postgresql94-server postgresql94-contrib
3 verify that the installation is successful
Sudo rpm-aq | grep postgres
The following results should occur
Postgresql94-libs-9.4.6-1PGDG.rhel6.x86_64
Postgresql94-server-9.4.6-1PGDG.rhel6.x86_64
Postgresql94-9.4.6-1PGDG.rhel6.x86_64
Postgresql94-contrib-9.4.6-1PGDG.rhel6.x86_64
4 switch to the postgres account, initialize and start the database
Su postgres
/ usr/pgsql-9.4/bin/initdb-pgdata=/data/postgres-locale=C-encoding=utf8
/ usr/pgsql-9.4/bin/pg_ctl-D / data/postgres-l / data/postgres/psotgres.log start
2.5 use psql to connect to the database (non-postgres users can be used)
Psql-U postgres
Library operation
\ h: check the explanation of the SQL command, such as\ h select.
\?: view a list of psql commands.
\ l: list all databases.
\ C [database_name]: connect to other databases.
\ d: lists all tables for the current database.
\ d [table_name]: lists the structure of a table.
\ du: list all users.
\ e: open a text editor.
\ conninfo: lists the current database and connection information.
Log into the database
Psql-U postgres-W (password)-h 127.0.0.1
Create a database
Create database databa_name
Backup database
Pg_dump-h 127.0.0.1-U postgres databasename > / data/backup_name.sql
Import database
Psql-h localhost-U postgres-d databasename < / data/backup_name.sql
Modify database password
ALTER USER postgres WITH PASSWORD 'postgres'
Table operation
Create a new table
CREATE TABLE user_tbl (name VARCHAR (20), signup_date DATE)
Insert data
INSERT INTO user_tbl (name, signup_date) VALUES ('Zhang San', '2013-12-22')
Select record
SELECT * FROM user_tbl
Update data
UPDATE user_tbl set name ='Li Si 'WHERE name =' Zhang San'
Delete record
DELETE FROM user_tbl WHERE name ='Li Si'
Add a field
ALTER TABLE user_tbl ADD email VARCHAR (40)
Update structure
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL
Rename field
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup
Delete a field
ALTER TABLE user_tbl DROP COLUMN email
Rename the form
ALTER TABLE user_tbl RENAME TO backup_tbl
Delete form
DROP TABLE IF EXISTS backup_tbl
