Summary of several PostgreSQL database operations

Create a table

Syntax: as follows

CREATE TABLE table_name (column_name column_type (parametes) options,...);

Attention:

⑴sql statement is not case sensitive

⑵ usually ignores whitespace-whitespace should be used to make SQL commands more readable.

⑶ table names and fields cannot be the same as SQL reserved words

The example teacher how to create TABLE Cd_collection:

CREATE TABLE Cd_collection

(

ID int NOT NULL,

Title varchar (NOT NULL),

Artist varchar () NOT NULL,

Year varchar (#) NOT NULL,

Rating varchar (NOT NULL)

);

Inserting data into a table

Syntax: as follows

Insert in the order of columns in a database table

INSERT INTO table_name values (' value1 ', ' value2 ', ' value3 ', ...);

Not in the same order as the columns in the database table (special insert)

INSERT INTO table_name values (COLUMN1, COLUMN4) VALUES (' value1 ', ' value2 ');

Fill in multiple rows of data in an INSERT statement using the following syntax

INSERT INTO table_name values (' value1 ', ' value2 '), (' Value3 ', ' value4 ');

Notice:value1 and value2 inserted into first column, value3 and Value4 inserted into second column.

INSERT into cd_collection values (9, ' Nevermind ', ' Nirvana ', ' 1991 ', ' NULL ');

If the column in the middle position is empty, you need to explicitly declare NULL in the INSERT statement.

Retrieving data from the database

Select Column1, Column2, COLUMN3 from table_name where Search_criteria;

Column1, Column2, COLUMN3 indicates the column name of the column that needs to be returned.

If you need to return all columns, you can use the wildcard character * to display all the columns that match the search criteria.

SELECT * from Cd_collection;

Select title from Cd_collection;

Select title, year from Cd_collection;

Select (title| | " ("| | year| | ")") As Titleyear from Cd_collection;

SELECT * from cd_collection where rating = 5;

SELECT * from cd_collection where rating =5 and year!=2003;

Start Postmaster:

$postmaster –d/usr/local/pgsql/data &

Creating a database in PostgreSQL

At the shell prompt, issue the following command:

#su –postgres

$createdb Database #在不登陆和不使用psql的情况下创建数据库

#create database testdatabase; #在psql里创建数据库

$psql testdatabase; #启动psql程序并链接到testdatabase数据库

To switch databases under the command line

$\c database_name

Create a user that allows access to the database or create multiple new database users under the Postgres user

$createuser pgtest (with password)

Y

Y

To create a database user at the command line with a postgres user

Psql Testdatabase

Create user pgtest (with password);

To delete a database user

$dropuser Pgtest

You can also use Psql to log in to the database and then use the Drop User command

$psql Testdatabase

testdatabase=# drop user pgtest;

Testdatabase=#\q

Granting and revoking privileges in PostgreSQL: Grant and REVOKE statement implementations

The following is the syntax for the GRANT statement:

GRANT what_to_grant on where_to_grant to user_name;

The following statement grants the user pgtest all privileges on the database testdatabase

GRANT all on testdatabase to Pgtest;

Revoke privileges

REVOKE all on testdatabase from Pgtest;

PostgreSQL Command Line Client

Psql can accept several parameters

-H hostname link Remote host hostname (if the database server is not on the local system)

-P n Specifies the port number to which the client should connect. Note that this is the lowercase p

-u username connect the database to user username

-W prompts for a password after linking the database.

-?                                   Display Help message