relational database Common SQL statement syntax Daquan

CREATE TABLE syntax

CREATE TABLE < table name >(< column name >< data type >[ column-Level integrity constraints  ]                  [,< Column name > < data type >[column-level integrity constraint ]] ...);

Column-level integrity constraints have NULL[ nullable  ], notnull[ not null ],  Unique[ unique ], can be used in combination, but cannot repeat and the opposite relationship exists simultaneously.

Example

--Create student TablesCREATE TABLEStudent (IdINT  not NULL UNIQUE PRIMARY KEY, NameVARCHAR( -) not NULL, AgeINT NULL, GenderVARCHAR(4)NULL);

Delete Table syntax

DROP TABLE < table name >;

Example

-- Delete Student Table DROP TABLE Student;

Clear table syntax

TRUNCATE TABLE < table name >;

Example

-- Delete Student Table TRUNCATE TABLE Student;

Modify Table Syntax

--Adding ColumnsALERTTABLE <Table name> [ADD < new column name > < data type >[column-level integrity constraints]]--Delete ColumnALERTTABLE <Table name> [DROP column < column name >]--Modifying ColumnsALERTTABLE <Table name> [MODIFY Columns < column names > < data types > [column-Level integrity constraints]]

Example

-- Add student table ' Phone ' column TABLE ADD VARCHAR (NULL; -- Delete student table ' Phone ' column TABLE DROP COLUMN Phone; -- Modify student table ' Phone ' column TABLE VARCHAR (NULL;

SQL query statement syntax

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 > [having < conditional expression >]]  [ORDER by < column name > [asc| DESC]...]

Order of SQL query statements:SELECT, from,WHERE,GROUPby,have,order  by. SELECT, from is required, and the HAVING clause can only be used with GROUP by.

Example

SELECT *  from Student   WHERE Id>ten  GROUPby haveAVG>    ORDERbyDESC

SQL INSERT statement syntax

--inserting data that does not existINSERT  into <Table name> [(field name [, Field name]...)]VALUES(constant[, Constants]...);--inserting the data of a query into a data tableINSERT  into <Table name> [(field name [, Field name]...)]SELECTQuery statement;

Example

--inserting data that does not existINSERT  intoStudent (Name,age,gender)VALUES('Andy', -,'female');--inserting the data of a query into a data tableINSERT  intoStudent (Name,age,gender)SELECTName,age,gender fromstudent_tWHEREId>Ten;

SQL UPDATE statement syntax

UPDATE < table name >SET column Name = Value Expression [, column name = value Expression ... ]  [WHERE condition expression ]

Example

-- Add ID to age of (10,100) plus 1 UPDATE SET Age= age+1WHERE ID>ten and ID<  -

SQL DELETE statement syntax

DELETE  from < table name >[WHERE condition expression ]

Example

-- delete data records with ID less than 10 DELETE  from WHERE Id<ten;

CREATE INDEX syntax

CREATE [UNIQUE] [CLUSTER] INDEX < index name >on< table name >(< column name > [< order >] [,< column name >[< order >]] ...);

UNIQUE : Indicates that each index value for this index only corresponds to a unique data record cluster: Indicates that the index established is a clustered index order: Optional ASC (Ascending) or desc (descending), default ASC

Example

-- Build Student Table index: Single field ID index reverse CREATE UNIQUE INDEX  on DESC ); -- Build Student Table index: Multiple field IDs, name index reverse CREATE UNIQUE INDEX  on DESC DESC);

Delete index syntax

DROP INDEX < index name >;

Example

-- Delete Student Table index Index_sid DROP INDEX Index_sid;

CREATE VIEW syntax

CREATE VIEW < view name >as  SELECT  query clause   [  With CHECK OPTION]

query clause: A subquery can be any SELECT statement, but it is not always allowed to contain an ORDER BY clause and a distinct phrase;  with CHECK OPTION: Indicates that updates are guaranteed for update,INSERT, delete operations.

Example

CREATE VIEW View_stu_man  as SELECT *  from WHERE = ' male '  with CHECK OPTION

Delete View syntax

DROP VIEW < view name >;

Example

DROP VIEW View_stu_man;

Access control for SQL

access control is the control of the user's data storage permissions, as determined by the DBA. SQL standard statements include SELECT,INSERT, update, and delete

Grammar

--1. AuthorizationGRANT <Permissions>[,< Permissions >]... ..[On < object type > < object name >]   to <User>[,< users >]... ..[With GRANT OPTION]--2. Withdrawal of AuthorizationREVOKE <Permissions>[,< Permissions >]... ..[On < object type > < object name >]   from <User>[,< users >]...

 with GRANT OPTION: Specify this clause to indicate that the user can assign permissions to other users

Example

-- Authorized GRANT SELECT,INSERT,UPDATEonTABLEto withGRANT  OPTION-  revoke authorization REVOKESELECT,INSERT,  UPDATEonTABLE from User_admin

Source Address: 1190000005054208

relational database Common SQL statement syntax Daquan