Basic operation of "Symfoware OPEN" database

Symfoware Open System
"DDL" (data definition Language)
A data definition language that defines and manages the language of all objects in a SQL database. The main commands are create, alter, DROP, etc., the DDL is mainly used in the initialization of defining or altering tables (table) structures, data types, links between tables, and constraints, and most of them use when establishing tables.
1, create: created;
2, alter: modify;
3, drop: delete;
4, TRUNCATE: delete; (with drop, delete view truncate-delete-drop.txt)
5, COMMENT: comments;

"DML" (Data Manipulation language)
Data manipulation language, SQL processing data and other operations called data manipulation language. Mainly Select, UPDATE, INSERT, DELETE, these 4 commands are used to manipulate the data in the database.
1, SELECT: query;
2, insert: inserting;
3, update: Updates;
4, delete: remove;

"DCL" (Data Control Language)
The Data Control language, which is used to grant or reclaim certain privileges of accessing the database, and to control the time and effect of database manipulation transactions, and to monitor the database.

1, keywords and references are not case-sensitive, the use of the keyword used in uppercase, the use of lowercase reference characters;
2. Note: Start with/* and end with */;

"Data Definition"
Create a table
CREATE TABLE Products (
Product_no Integer,
Name text,
Price numeric
);

Delete a table
DROP TABLE Products;

Set Default values:
CREATE TABLE Products (
Product_no Integer,
Name text,
Price Numeric DEFAULT 9.99
);
or serial number self-increment 1
CREATE TABLE Products (
Product_no integer DEFAULT nextval (' Products_product_no_seq '),
Name text,
Price numeric
);

Where: Products_product_no_seq for
CREATE SEQUENCE Products_product_no_seq
START with 1
INCREMENT by 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

For special ellipsis forms:
CREATE TABLE Products (
Product_no SERIAL,
...
);

Constraints
1. Inspection and restriction
CREATE TABLE Products (
Product_no Integer,
Name text,
Price numeric CHECK (Price > 0)
);

2. Non-null restriction
CREATE TABLE Products (
Product_no Integer Not NULL,
Name text not NULL,
Price numeric
);

3, the uniqueness of the restrictions
CREATE TABLE Products (
Product_no Integer,
Name text,
Price Numeric,
UNIQUE (Product_no)
);

4, PRIMARY KEY constraints
CREATE TABLE Products (
Product_no integer PRIMARY KEY,
Name text,
Price numeric
);

5, FOREIGN KEY constraints
CREATE TABLE Products (
Product_no integer PRIMARY KEY,
Name text,
Price numeric
);
CREATE TABLE Orders (
order_id integer PRIMARY KEY,
Product_no integer REFERENCES products (product_no),
Quantity integer
);

6. Exclusive restriction

"Table Actions"
1. Append columns
ALTER TABLE Products ADD COLUMN description text;
The column initial value is null.

2. Delete Columns
ALTER TABLE Products DROP COLUMN description;
If there is a dependency, cascade delete
ALTER TABLE Products DROP COLUMN description CASCADE;

3. Additional Restrictions
ALTER TABLE Products ADD CHECK (name <> ");
ALTER TABLE Products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE Products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
Alter TABLE products ALTER COLUMN product_no SET is not NULL;

4, the elimination of restrictions
(common) ALTER TABLE products DROP CONSTRAINT some_name;
(non-NULL) ALTER TABLE products ALTER COLUMN product_no DROP not NULL;

5. Modify default values
Set column default value: Alter TABLE products ALTER COLUMN price set default 7.77;
Cancel default: Alter TABLE products ALTER COLUMN price DROP default;
defaults to NULL if the default value is canceled.

6. Modify the data type
Alter TABLE products ALTER COLUMN price TYPE numeric (10,2);

7. Modify column names
ALTER TABLE Products RENAME COLUMN product_no to Product_number;

Permissions
1. Grant Permissions
GRANT UPDATE on accounts to Joe;
2. Reclaim Granted Permissions
REVOKE all on accounts from public;

Mode
1. Create a mode
CREATE SCHEMA MySchema;
When accessing a table in a schema:
Database.myschema.table
Because the public mode is used by default, the following two sentences are the same
CREATE TABLE Products (...);
CREATE TABLE public.products (...);

2. Delete mode
DROP SCHEMA MySchema;
If there are objects in the pattern, cascade Delete
DROP SCHEMA MySchema CASCADE;

"Data Manipulation"
Inserting 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 = ' John Doe ' WHERE name = ' Zhang San ';

Deleting records
DELETE from user_tbl WHERE name = ' John Doe ';

"Complex Query"
Structure
SELECT * FROM statement where statement Group by statement having statement ORDER BY statement
SELECT
[All | DISTINCT [On (expression [, ...])]
* | expression [as Output_name] [, ...]
[From From_item [, ...]]
[WHERE condition]
[GROUP by expression [, ...]]
[Having condition [, ...]]
[{UNION | INTERSECT | EXCEPT} [all] select]
[ORDER by expression [ASC | DESC | USING operator] [, ...]
[For UPDATE [of TableName [, ...]]
[LIMIT {Count | All}]
[OFFSET start]

From statement
From Table_reference [, table_reference [, ...]]

Table combination Type
1. Result set operation
A. Union Union:select Column1, column2 from table1 UNION SELECT column1, column2 from table2
Note: Union All is the same as the Union operator, but "does not" remove duplicate entries from the result set.
B. Intersection Join:select * from table1 as a joins table2 B on A.name=b.name
C. Difference set not In:select * FROM table1 WHERE name is not in (SELECT name from table2)
D. Cartesian product Cross Join:select * from table1 cross JOIN table2

2, 3 kinds of connection
1) Cross join:
Without a WHERE clause, it will return the Cartesian product of the two tables connected, and the number of rows returning the result is equal to the product of two table rows;
From T1 Cross JOIN T2
Same as the following two types of notation
From T1, T2
From T1 INNER JOIN T2 on TRUE

It is generally not recommended to use this method, because if there is a WHERE clause, the data table of the rows that tend to be the product of the two table row numbers is then selected from the Where condition. Therefore, if the two tables that require communication are too large, it will be very, very slow and not recommended.

2) Inner connection INNER join:
SELECT * FROM table1 INNER JOIN table2
--equivalent connection (= number applied to the connection condition, does not remove duplicate columns);
SELECT * FROM table1 as a INNER joins Table2 as B on A.column=b.column;
--Unequal connection (>,>=,<,<=,!>,!<,<>);
SELECT * FROM table1 as a INNER joins Table2 as B on A.column<>b.column
--Natural Connection NATURAL join: (will remove duplicate columns).
SELECT * FROM Employees natural join departments;

A natural connection can only be an equivalent join of a property of the same name, whereas an inner join may use a using or an ON clause to specify a join condition, in which two fields are equal (can be different names).

Attention:
If the connection condition is not specified in the inner connection, it is the same as the cross-join result of the Cartesian product, but unlike the Cartesian product, the data table with the product of the number of rows of the Cartesian product is not so complicated that the inner connection is more efficient than the cross-connection of the Cartesian product.

3) Outer connection OUTER join:
1) left outer connection [OUTER] Join:
Displays data rows that match the criteria, while displaying data rows on the left side of the data table that do not match the criteria, with no corresponding entry on the right to display null
For example SELECT * from table1 as a left [OUTER] JOIN on A.column=b.column

2) Right-side connection-[OUTER] Join:
Displays data rows that match the criteria, while displaying data rows that do not match the criteria on the right side of the table, with no corresponding entry on the left showing null
For example SELECT * from table1 as a right [OUTER] JOIN on A.column=b.column

3) Full outer connection:
Displays data rows that match the criteria, displaying both left and right rows of data that are not eligible, and showing null on the left and right sides
For example SELECT * FROM table1 as a full JOIN on A.column=b.column

Attention:
Specifies the inner join of the condition, returning only the entry that meets the join condition. The outer joins are different, and the returned results include not only rows that meet the join criteria, but also all rows of data that have the left table (when left outer joins), the right table (when connected right), or both sides (when connected at all).

WHERE statement
SELECT ... From FDT WHERE C1 > 5
SELECT ... From FDT WHERE C1 in (1, 2, 3)
SELECT ... From FDT WHERE C1 in (SELECT C1 from T2)
SELECT ... From FDT where C1 in (SELECT c3 from t2 where C2 = fdt.c1 + 10)
SELECT ... From FDT where C1 between (SELECT C3 from t2 where c2 = Fdt.c1 +) and 100
SELECT ... From FDT where EXISTS (SELECT C1 from T2 where C2 > Fdt.c1)

Limit and Offset
Elect select_list
From Table_expression
[LIMIT {number | All}] [OFFSET number]
If a limit count is given, the return does not exceed so many rows, and the limit all is the same as omitting the limit clause.
Offset describes how many rows are ignored before starting to return rows.
Offset 0 is the same as omitting the offset clause. If both offset and limit appear, the offset line is ignored before the limit line is computed.

If you use limit, it is a good idea to constrain the result rows to a unique order by using the ORDER BY clause. Otherwise you'll get an unpredictable subset of the rows of the query (you might want the tenth to 20th row, but in what order 10 to 20?). Unless you declare order BY, the order is not known).

Rows ignored by the offset clause still need to be computed inside the server, so a large offset may not be efficient enough.

Sequence
A Sequence object (also called a sequence builder) is a special single-line table created with create sequence. A sequence object is typically used to generate a unique identifier for a row or table.
Create a sequence
Method One: Specify the field type as serial type directly in the table;
Method Two: Create the sequence name first, and then specify the sequence in the new Table column property, which requires the syntax of the int type to create the sequence:
CREATE
[Temporary | TEMP] SEQUENCE name [INCREMENT [by] INCREMENT]
[MINVALUE MINVALUE | NO MINVALUE] [MAXVALUE MAXVALUE | NO MAXVALUE]
[Start [with] start] [Cache Cache] [[NO] CYCLE]
[owned by {table.column | NONE}]

Sequence functions
Nextval (Regclass): Increments the Sequence object to its next value and returns the value (bigint type, hereinafter).

Currval (Regclass):
Returns the value of the sequence that was last nextval caught in the current session.
(An error is reported if Nextval has never been called on the sequence in this session.) )

Setval (Regclass, bigint):
Resets the counter value of the Sequence object. Setting the Last_value field of a sequence to a specified value and setting its is_called field to TRUE indicates that the next nextval will increment the sequence before returning a value.

Setval (Regclass, bigint, Boolean):
Resets the counter value of the Sequence object. The function is equivalent to the Setval function above, except that is_called can be set to TRUE or false. If it is set to false, the next Nextval returns the value, and the subsequent nextval begins incrementing the sequence.

Instance
=# Create sequence Id_seq increment by 1 minvalue 1 no maxvalue start with 1;

CREATE TABLE TBL (ID int4 NOT NULL default Nextval (' Id_seq '), name text);

"Pl/pgsql"
Advantages of using Pl/pgsql
SQL is the language of PostgreSQL and most other relational databases used as command languages. It is portable and easy to learn to use. However, all SQL statements must be performed independently by the database server.
This means that your client application must send each command to the database server, wait for it to process the command, receive the result, do some arithmetic, and then send another command to the server. All of these things can generate interprocess communication, and if your client is on another machine, it can even cause network overhead.
If you use Pl/pgsql, then you can put a block of operations and a series of commands in the database server, so that you have the power of the process language and simplify the use of SQL, thus saving a lot of time, because you do not have to pay the client/server communication overheating. This can lead to significant performance gains.

Structure
Create or Replace function MyFunction () returns integer
As
$$
Declare
MyVar integer:=30;
Begin
Raise notice ' myvar is% ', myvar; --print 30
--Create a child block
Declare
MyVar varchar:= ' Hello World ';
Begin
Raise notice ' myvar is% ', myvar; --print Hello World
End

Raise notice ' myvar is% ', myvar; --print 30
return myvar;
End
$$
Language plpgsql;

The start and end functions can use $$, or single quotes can be used;
A declare....begin....end can be seen as a block, if a child block is in another block, end ends with a; end, the entire function ends with the last end.
"--" indicates a comment;

Tip: When you create a function with Pgadmin, you only need to fill in the code between the $$ in the code options.

Two methods for passing parameters
1. CREATE FUNCTION Sales_Tax (REAL)
RETURNS Real AS
$$
BEGIN
RETURN $ * 0.06;
END;
$$
LANGUAGE Plpgsql;

2.
CREATE FUNCTION add_three_values (v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS Anyelement as
$$
BEGIN
RETURN V1 + v2 + v3;
END;
$$
LANGUAGE Plpgsql;

Copy type
Variable%type;
For example, if you have a field in the users table called user_id. To declare a variable of the same type as the users.user_id, you can write: user_id users.user_id%type;

Row type
Name Table_name%rowtype;
A row variable can be declared to be the same as the row type of an existing table or view, either by using the Table_name%rowtype notation, or by declaring that its type is the name of a composite type.

CREATE FUNCTION Merge_fields (t_row tablename)
RETURNS text as
$$
DECLARE
T2_row Table2name%rowtype;
BEGIN
SELECT * into T2_row from Table2name WHERE ...;
RETURN T_ROW.F1 | | t2_row.f3 | | T_row.f5 | | T2_row.f7;
END;
$$
LANGUAGE Plpgsql;
SELECT Merge_fields (t.*) from tablename t WHERE ...;

Control structure
Return from function:
1. RETURN
RETURN expression;
A return with an expression is used to terminate the function, and then the value of expressions is returned to the caller.

2. RETURN NEXT
RETURN NEXT expression;
Return next is not actually returned from the function; it simply saves the value of the expression. Execution continues with the next statement in the Pl/pgsql function. As the subsequent return command executes, the result set is set up. The last one does not require a return of the argument, causing the control to exit the function.

Conditions
· IF ... Then
IF Boolean-expression Then
Statements
END IF;

· IF ... Then ... ELSE
IF Boolean-expression Then
Statements
ELSE
Statements
END IF;

· IF ... Then ... ELSE IF
IF demo_row.sex = ' m ' Then
Pretty_sex: = ' man ';
ELSE
IF demo_row.sex = ' F ' Then
Pretty_sex: = ' woman ';
END IF;
END IF;

· IF ... Then ... Elsif ... Then ... ELSE
IF Boolean-expression Then
Statements
[Elsif Boolean-expression Then
Statements
[Elsif Boolean-expression Then
Statements
...]]
[ELSE
Statements]
END IF;

· IF ... Then ... ELSEIF ... Then ... ELSE
ELSEIF is the alias of Elsif.

Simple loop
LOOP
[<<label>>]
LOOP
Statements
END LOOP;
Loop defines an unconditional loop, infinite loop, until terminated by exit or return statement.

EXIT
EXIT [Label] [when expression];
If the label is not given, exit the inner loop, and then execute the statement that follows the end loop. If a label is given, it must be a nested loop block or a tag of the block at the current or higher level. The named block or loop is then terminated, and the control falls on the statement that follows the end statement of the corresponding loop/block.

While
While amount_owed > 0 and gift_certificate_balance > 0 loops
--You can do some calculations here.
END LOOP;

For
For I in 1..10 LOOP
--Here you can put some expressions
RAISE NOTICE ' I is% ', I;
END LOOP;

Catch error
INSERT into Mytab (FirstName, LastName) VALUES (' Tom ', ' Jones ');
BEGIN
UPDATE mytab SET firstname = ' Joe ' WHERE lastname = ' Jones ';
X: = x + 1;
Y: = x/0;
EXCEPTION
When Division_by_zero Then
RAISE NOTICE ' caught Division_by_zero ';
RETURN x;
END;
Tip: Entering and exiting a block containing the exception clause is much more expensive than the block that is not included. Therefore, do not use exception when unnecessary.

Errors and messages
RAISE level ' format ' [, variable [, ...]];
Possible levels are debug (write information to server log), log (write information to server log, higher priority), Info,notice and Warning (write information to server log and forward to client application, Priority escalation) and exception throw an error (usually exiting the current transaction).

In the format string,% is replaced by the external representation of the next optional parameter. To emit a text of%, you want to write a percent. Note that the optional parameter must be a simple variable, cannot be an expression, and the format must be a simple string literal.
RAISE NOTICE ' calling cs_create_job (%) ', v_job_id;
The value of v_job_id will replace the% in the string

"Trigger function"
The trigger function definition looks very similar to the normal function definition except that it has a return value type of trigger with no parameters.
CREATE FUNCTION emp_stamp () RETURNS Trigger as $$ ...

When a pl/pgsql function is called as a trigger, the system automatically creates some special variables in the outermost block. These variables are:

NEW
The data type is record. For row-level triggers, it has new rows of data resulting from an INSERT or update operation. For a statement-level trigger, its value is null.

Old
The data type is record. For row-level triggers, it has old rows of data that were modified or deleted by the update or delete operation. For a statement-level trigger, its value is null.

Tg_name
The data type is name, which holds the name of the trigger that was actually called.

Tg_when
The data type is text and, depending on the trigger definition information, its value is before or after.

Tg_level
The data type is text and, depending on the trigger definition information, its value is row or statement.

Tg_op
The data type is text, and its value is insert, update, or delete, which indicates the type of action that triggered the trigger.

Tg_relid
The data type is OID, which represents the OID of the table the trigger acts on.

Tg_relname
The data type is name, which represents the names of the tables that the trigger acts on. It works the same as the following variable tg_table_name.

Tg_table_name
The data type is name, which represents the names of the tables that the trigger acts on.

Tg_table_schema
The data type is name, which represents the mode of the table in which the trigger acts.

Tg_nargs
The data type is integer, which represents the number of parameters that the CREATE TRIGGER command passes to the trigger procedure.

Tg_argv[]
The data type is an array of type text. Represents all parameters that the CREATE TRIGGER command passes to the trigger procedure. The subscript starts from 0. Tg_argv[0] represents the first parameter. If the subscript is less than 0 or greater than or equal to Tg_nargs, a null value will be returned.

Create a Trigger
Defining Trigger Simplified syntax
CREATE TRIGGER Name
{before | After | INSTEAD of}{event [OR ...]
On table_name
[for [each] {ROW | STATEMENT}]
Ececute PROCEDURE function_name ()

Example:
Employee Pay Table
CREATE TABLE EMP (
EmpName text,
Salary Integer,
Last_date timestamp,
Last_user text
);

Trigger function
CREATE OR REPLACE FUNCTION emp_stamp () RETURNS Trigger as
$$
BEGIN
--Check if EmpName and salary are given.
IF New.empname ISNULL Then
RAISE EXCEPTION ' empname cannot be null ';
END IF;
IF New.salary ISNULL Then
RAISE EXCEPTION '% cannot has null salary ', new.empname;
END IF;
-No salary for employees
IF New.salary < 0 Then
RAISE EXCEPTION '% cannot have a negative salary ', new.empname;
END IF;
--remember when someone changed their salary
New.last_date: = ' Now ';
New.last_user: = Current_User;
RETURN NEW;
END;
$$
LANGUAGE Plpgsql;

Trigger
Whenever a row is inserted or updated in a table, the current user name and time are recorded in the entry, and it is guaranteed that the employee name is given and that the salary is a positive number.
CREATE TRIGGER Emp_stamp
Before INSERT OR UPDATE
On EMP
For each ROW
EXECUTE PROCEDURE Emp_stamp ();

Tip: Creating a Trigger in Pgadmin is an action in a table, and a trigger is associated with a table.

Test:
Insert into Emp_stamp (empname,salary) VALUES (' WANGSJ ', 10000);
SELECT * from EMP;
Results:
"WANGSJ"; 1000; " 2014-12-04 07:10:24.941 ";" Postgres

Insert into Emp_stamp (empname,salary) VALUES (' WANGSJ ',-10000);
Results:
Fatal error **********
Error: WANGSJ cannot has a negative salary
SQL Status: P0001

Reference:
Http://blog.sina.com.cn/s/blog_4c6e822d0102dsqz.html
http://blog.csdn.net/johnny_83/article/details/2223147
http://blog.163.com/dazuiba_008/blog/static/363349812012102133028849

This article is from the "Night" blog, be sure to keep this source http://icyore.blog.51cto.com/8486958/1596294

Basic operation of "Symfoware OPEN" database