mysql-base (SQL statement)

SQL statements:

DDL (data definition Languages) statement: The definition language, the operand: Data segment, database, table, column, index, etc.

Database:

Created: Create DATABASE dbname;

Delete: Drop DATABASE dbname;

Table:

Created: Create TABLE tablename (

ID INT (5) auto_increment (auto-add keyword) PRIMARY key,

Name VARCHAR () not NULL);

Delete: Drop TABLE tablename;

Modified: ALTER TABLE tablename RENAME new_tablename; (Modify table name)

ALTER TABLE tablename MODIFY name VARCHAR (10); (Modify the properties of a column)

ALTER TABLE tablename Add column_name VARCHAR [first/(after Col_name)] (as inserted position); (Add a column)

ALTER TABLE tablename DROP col_name; (delete a column)

ALTER TABLE tablename Change old_col_name new_col_name VARCHAR [first/(after Col_name)]; (Modify column name)

（！ Change and Modefy difference: Changes can modify the column name, modify can not, but changed two times the column names have to write. ）

DML (data manipulation Languages) statement: manipulating statements; manipulating objects: Table additions and deletions

Add: INSERT into tablename (col1_name,col2_name) values (value1,value2);

Delete: Delete from TableName [WHERE CONDITION];

Check: SELECT * from TableName;

Change: UPDATE tablename Set col_name = ' value ' where id = 1;

Query the upgraded version (also updated):

One:

SELECT Dep,count (*)

From TableName

GROUP by DEP

Having COUNT (*) >= 2;

Both:

SELECT *

From TableName

WHERE col_name= ' abc '

LIMIT 0,2 (0,2 means: From the beginning of the sequence number 0 after the two data, eg:2,2 meaning, from the beginning of the sequence 1 after two)

ORDER by Col_name DESC;

Three:

SELECT *

From TableName

WHERE DEP in (

SELECT DEP

From tablename_1); (! If the subquery record number is unique, in can be substituted with "=". ）

Four:

SELECT DEP from TableName

Union/(UNION ALL)

SELECT DEP from tablename_2

Knowledge Point: A connection query for a table.

With ROLLUP: summarizes the results of the classification aggregation.

The difference between having and where: where is a conditional filter before aggregation, having a conditional filter after aggregation, and where you can prioritize where to use where so that queries can

To achieve a certain degree of optimization.

The difference between Union and UNION all: Union is a distinct of the result after the union all, eliminating the result of duplicate records.

DCL (Data Control Languages) statement: The language in which the information is controlled (rarely used by developers); manipulating objects: Data licensing, access levels

Authorization: Grant Select,insert on tablename.* to ' Z1 ' @ ' localhost ' IDENTIFY by ' 123 ';

Retract: REVOKE INSERT on tablename.* from ' Z1 ' @ ' localhost ';

Knowledge Points: Metadata, data for data, such as table name names, and other tables of various property names.

INFORMATION_SCHEMA database is used to record the metadata information in MySQL, this database is a virtual database, there is no physical existence of related directories and files, all views.

View:

Schemata: all database information.

TABLES: Table information in the database

COLUMNS: Column information in a table

STATISTICS: Table index Information

　　　　　

　　　　　　　　　　　

mysql-base (SQL statement)