SQL database Basic statement

SQL Features--"

1) Comprehensive unification. SQL is a set of data definition, data manipulation and data control in one, language peaks Unified, can be completed independently of the database life cycle of all activities.

2) high degree of non-process. SQL language is a highly non-procedural language, when the data operation only need to point out "what to do", without pointing out "how to do", the storage path is transparent to the user, improve the independence of the data.

3) Set-oriented operation mode. The SQL language adopts a set-oriented operation mode, and its operation formation and finding result can be a set of tuples.

4) Two modes of use. The first (self-contained language): The user can enter SQL commands on the terminal keyboard, manipulate the data, and the second (embedded language): Infiltrate the SQL language into the high-level language program.

5) Simple language, easy to learn and easy to use. The SQL language feature is extremely strong, complete the core function with only 9 verbs, including the following 4 categories:

• Data query: Select
• Data definition: CREATE, DROP, ALTER
• Data manipulation: INSERT, Uodate, DELETE
• Data control: GRANT, Revork

SQL data definition:

1. Create a table

CREATE table< Table name > (< column name >< data type >[column-level integrity constraint (null| UNIQUE)]

< column name >< data type >[, column-level integrity constraint] ...

[,< table-level integrity constraints;]);

2. Modify and delete changes

1) Modify the table

ALTER table<>[add< new column name >< data type >[integrity constraint]]

[drop< integrity constraint conditions;]

[modify< column names >< data types >];

2) Delete Table

　　　DROP table< Table name >

3. Index creation and deletion

1) Building the Index

　　creat [unique][cluster]index< Index name >

on< Table name > (< column name >[< order (ASC (Ascending, default) | DSC (Descending))>][,< column name >[< order;] ...);

2) Delete Index

　　　DROP index< Index name >

4. View creation and deletion

1) Create a view

　　　　creat View name (list name)

As SELECT query statement

[with CHECK OPTION]//indicates that the update, insert, delete row satisfies the predicate condition in the view definition for the update,insert,delect operation

SQL data Query statement:

1.SELECT Basic Structure

SELECT [all| DISTINCT] < target column expression >[,< target column expression;

From < table name or view name >[,< table name or view name;]

[WHERE < conditional expressions;]

[GROUP by < column name 1>[having< conditional expression;]]//Group

[ORDER by < column name 2>[asc| DESC]]//Sort

2. Simple query

Simple query only need to be practical three reserved words select,from,where

3. Connection Query

　　Query design more than two tables, then salutation as a connection query

SELECT < table name 1>,< table name 2>

4. Sub-query and aggregation functions

1) subquery (nested query)

One select-from-where query block can be embedded in another query block. Allow multiple nesting in SQL

2) Aggregation function: A function that is a collection of values that returns a single value for input

SQL provides 5 predefined set function averages Avg, Min min, max max, sum sum, count counts

For this any and all, you must use the comparison operator at the same time (using odd functions to implement subqueries is usually more efficient than using all and any queries directly)

5. Group queries

　1) Group by pairs of tuples for molecular

2) Having if the tuple is in some way added before grouping, the unnecessary grouping is empty and can be followed by a HAVING clause after the GROUP BY clause.

6. Renaming operations

　　Old-name as New-name

7. String manipulation (case sensitive)

　% matches any string

_ Match any one character

8. View Query

SQL Data Update:

1. Insert

INSERT into Basic table name (field name [, Field name] ... ）

VALUES (constants, [, constants]; query statements

INSERT into base table name (list name)

SELECT Query Statement

2. Delete

　Delect from base table name

[WHERE Condition expression]

3. Modifications

Updata Basic Table Name

Set column name = value base expression (, column name = value Base expression ...)

[WHERE Condition expression]

SQL Access control:

1. Authorization statement

GRANt < permissions >[,< permissions; [on< Object type >< object name;] to< User name >[,< user name;] ...

[With GRANT OPTION]

Common permissions for Operations

Object	Object type	Operation Permissions
Property column	TABLE	SELECT, Insert,update,delect,all privileges
View	TABLE	SELECT, Insert,update,delect,all privileges
Basic table	TABLE	SELECT, Insert,update,delect,alter,index,all privileges
Database	DATABASe	Createtab permissions to establish a table that can be granted to a normal user by a DBA

• Public: Users who accept permissions can be single or multiple specific users, and the public parameter can assign permissions to all users
• With GRANT OPTION: If this is the case, then the user who gets the permission can also assign permissions to other users.

2. Retract a permission statement

　revoke< rights >[,< permissions; [on< Object type >< object name;]

from< Users >[,< users >]...;

Embedded SQL:

Pl/i and C-language reference formats:

EXEC sql<sql Statements >

Reference format for COBOL languages:

EXEC Sql<sql Statement >end-exec

SQL database Basic statement