Shulou(Shulou.com)06/01 Report--

Mark notes

1 Login [postgres@hostnfsd ~]$ psql --The default login user is the current system user, and the default login database with the same name as the current system user

Password:

postgres=#

[postgres@hostnfsd ~]$ psql -U testuser -d exampledb-U specify user-d specify database-p specify port-h server

2 Change the password

postgres=# \password postgres

Enter new password:

Enter it again:

postgres=# alter user testuser password '12345678';

Create database user dbuser

CREATE USER dbuser WITH PASSWORD 'password';

Create a user database

CREATE DATABASE exampledb OWNER dbuser;

Give all permissions to the exampledb database to dbuser, otherwise dbuser can only log on to the console without any database operation permissions.

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

IV. Console commands

In addition to the\password (set password) and\q (exit) commands already used, the console provides a number of other commands.

\h: View the interpretation of SQL commands, such as\h select.\?: View psql command list.\ l: List all databases.\ c [database_name]: Connect to another database.\ d: Lists all tables of the current database.\ d [table_name]: Lists the structure of a table.\ du: List all users.\ e: Opens the text editor.\ conninfo: Lists information about the current database and connections.

V. Database operations

Basic database operations are performed using the SQL language.

#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 records

SELECT * FROM user_tbl;

Update the data

UPDATE user_tbl set name = 'Li Si' WHERE name = 'Zhang San';

#Delete records

Delete FROM user_tbl WHERE name = '' ;

#Add fields

ALTER TABLE user_tbl ADD email VARCHAR(40);

Update the 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 fields

ALTER TABLE user_tbl DROP COLUMN email;

#Form Renaming

ALTER TABLE user_tbl RENAME TO backup_tbl;

#Delete table

DROP TABLE IF EXISTS backup_tbl;

(End)

Reference:

http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.