PostgreSQL Foundation Finishing (i)

1. Create a database:
1) Login Bin directory, createdb.exe-u Postgres-e mydb;
-U indicates the login user name of this operation, if not write will take Windows login account, such as Administrator, will prompt no creation permission;

2. Log in to the database:
1) using the SQL Shell that comes with Postgre, select the database at login to create the library

3. CRUD
Writing habits: SQL statement uppercase, other lowercase
3.1) Create a table:
CREATE TABLE Users (username char (PRIMARY) KEY, password char (20));
3.2) Insert data:
INSERT into users (username, password) VALUES (' Fredric ', ' Fredric '), (' Sinny ', ' sinny ');
3.4) Query data:
SELECT * from users;
3.5) Delete data:

DELETE from users WHERE username = "Fredric";

4. JOIN
Prepare:
CREATE TABLE Clubs (clubname char () PRIMARY KEY, note text);
CREATE TABLE Customers (username char) PRIMARY key, Clubname char, FOREIGN KEY (clubname) REFERENCES Clubs (club name));

INSERT into clubs (clubname, note) VALUES (' MyClub ', ' good Clubs ');
INSERT into clubs (clubname, note) VALUES (' myclub1 ', ' good Clubs ');
INSERT into Customers (username, clubname) VALUES (' Fredric ', ' MyClub ');

4.1) INNER JOIN
SELECT * FROM clubs INNER joins customers on clubs.clubname = Customers.clubname;
Return Data MyClub
4.2) Outter Join (for example, left join)
SELECT * FROM clubs left JOIN customers on clubs.clubname = Customers.clubname;
returns data MyClub and Myclub1

5. Index
5.1) Create an index
CREATE INDEX clubs_index on clubs USING Hash (clubname);
CREATE index Clubs_index on clubs USING btree (clubname);//b Tree type index supports multiple fields
Create unique index clubs_index on clubs;//unique indexes, Postgre generates a unique index on the primary key by default
5.2) Delete index
DROP INDEX Clubs_index;

6. Aggregation Functions
6.1) COUNT
SELECT COUNT (*) from clubs;
6.2) Min/max
SELECT MIN (clubname) from clubs;//Output MyClub
SELECT MAX (clubname) from clubs;//Output MYCLUB1

7. GROUP by
SELECT * FROM Clubs GROUP by (Clubname);
SELECT COUNT (*) from clubs GROUP by (note);//1,2
SELECT Count (*) from clubs GROUP by (note) have COUNT (*) > 1;//2
Note: The SQL execution sequence from the WHERE---"GROUP by" , "aggregate function", "have", "Order By" SELECT;　　

8. Business
BEGIN TRANSACTION;
DELETE from customers WHERE clubname = ' myclub ';
DELETE from clubs WHERE clubname = ' myclub ';
COMMIT;//Execute Delete
ROLLBACK;//Rollback Operation

savepoint; The location of the rollback for the save point
For example:
savepoint MyPoint;
ROLLBACK to MyPoint;

PostgreSQL Foundation Finishing (i)