The classification of SQL statements in the count of Mysql _mysql

1: Data definition language (DDL)

For creating, modifying, and deleting data structures within a database, such as: 1: Creating and Deleting Databases (CREATE DATABASE | | Drop DATABASE); 2: Create, modify, rename, delete table (CREATE TABLE | | ALTER table| | RENAME table| | DROP TABLE); 3: Create and delete indexes (CreateIndex | | DROP INDEX)

2: Data Query Language (DQL)

To query data from one or more tables in a database (SELECT)

3: Data Manipulation Language (DML)

Modify the data in the database, including inserts (insert), update (update), and delete (delete)

4: Data Control Language (DCL)

For access to the database, such as: 1: Grant access to the user (grant); 2: Revoke user access (Remoke)

Q: When do I use drop? When do I use delete?

A: It can be seen from the above, for structural deletion, such as database deletion, table deletion, index deletion, etc. when using drop, and for the deletion of data is delete.

managing databases and Tables

1: Creating the database-----CREATE database name

For example: CreateDatabase Instant

Creates a table-----a CREATE TABLE table name (column list data type < column constraint >);

For example: CREATE TABLE Student (

Id INT Primart KEY;

Name VARCHAR (50));

Note: If you create a local temporary table (visible only in a connection to create a temporary table), add a # Before the table name, such as creating a global temporary table (visible to all connections), plus # # before the table name;

Creating index-----CREATE index index name on table name (column name ...) )；

For example: (non-unique index) CREATE index nameindex on Friend (Nmae);

(unique index) CREATE unique index nameindex on Friend (Name);

2: Connection Database---Use database name

For example: useinstant;

3: Delete database-----Drop DB database name

For example: Dropdatabase Instant;

Delete Table-----drop TABLE Table name

For example: DROP TABLE Student;

Delete Index---Drop indexfriend.phonenoindex (specify table name and index name)

4: Copy table-----SELECT * from my_friends from Friends; (This replication does not replicate table conventions)

Replication table structure does not replicate data: SELECT * from My_friendsfrom Friends WHERE 1=0;

5: Modify the table-----

1: Add new column ... altertable Friends ADD Address VARCHAR (50);

2: Change Definition ... Altertable Friends MODIFY Phone DEFAULT (' I'm on Oh ');

3: Delete Column ... Altertable Friends DROP cloumn Phoneno;

Ensure data integrity

One: Classification:

1: Entity integrity;

2: Domain integrity;

3: Application integrity;

4: User-defined integrity;

two: To achieve:

1: Create a Non-empty constraint------NOT NULL

2: Set PRIMARY KEY constraint------PRIMARY key

3: Set the unique constraint-------unique

4: Specify DEFAULT constraint-------defaults

5: Set CHECK constraints-------checks

6: AutoNumber column----------IDENTITY

7: FOREIGN KEY constraint----------FOREIGN key

changing data using DML statements

1: Insert data: (single line) insert into table name values Value column;

For example: INSERT intostudent (Id,name) VALUES (1, ' John ');

(Multiple lines) INSERT into table column name SELECT (statement)

For example: INSERT intostudent (id,name) SELECT id+2,name from Students;

2: Table copy: SELECT column name into new table name from table name;

For example: SELECT * INTOStudent2 from Student;

3: Update data: Update table name SET column n = new value ... WHERE (filter condition);

For example: UPDATE Student SET id = 2,age =20 WHERE name = ' John '

Update to delete data: Update Student age= nullwhere name = ' John '

4: Delete data: Delete from table name WHERE (filter bar

For example: DELETE fromstudent WHERE name = ' John

Note: Delete all table data to remove filter conditions, or use TRUNCATE table name

Simple data query

1: Query: SELECT column name from table name;

For example: SELECT ID from Student;

Query full table data: SELECT * from Student;

2: Table name prefix: SELECT student.id from Student;

3: List alias: SELECT T. column A as a,t. Column B as b,t. column C as C from table as T;

4: Computed column: SELECT ID, mark*2 as Marksfrom Student;

SELECT FirstName + ' +lastname as FullName from Student;

5: Exclude duplicate data: SELECT DISTINCT column A from table name; \

For example: SELECT DISTINCT name from Student;

6: Qualified row count query: SELECT top rowcount columa,columb from Table;

For example: SELECT TOP5 ID, name from Student;

7: The conditional query WHERE

8: Range Query BETWEEN

9: Definition Set relationship in

10: Fuzzy query Like (single character _ multiple characters%)

11: null Data Control: SELECT column A, column B from table name WHERE column C is not NULL;

SELECT column A, column B from table name WHERE column C is NULL;

12: Sort: Ascending. Asc

Descending... DESC

aggregate functions and groupings

1:select COUNT (specification) from table name;

A: Number Count

B: Sum sums

C: Avg Value

D: Max Max

F: Min value

2: GROUP BY..

3: Result set Processing: SELECT Studentid,avg (Mark) as Averagemark from Studentexam GROUP Bystudentid have avg (Mark) <50 OR avg (Mark) & gt;70;

4:exists;

5:all;

6:any;

7:union;

8: Keep Duplicate lines: UNION all;

9: Intersection and difference: INTERSECT EXCEPT

joins

1: INNER JOIN: Join

2: Outer joins: 1: Left OUTER join: A/OUTER Join

2: Right outer Union: Rightjoin or right-hand outerjoin

3: All-in-whole: Full Join or fully OUTER join

The above is a small series for everyone to bring the number of MySQL SQL statements in the classification of all the content, I hope that many support cloud Habitat Community ~