Summary of basic simple grammar for first knowledge of MySQL

One, DDL (data definition Language) statement: the database definition language.

These statements define different data segments, databases, tables, columns, indexes, and other database objects. Common statement Keywords: create, drop, alter, and so on.

2. CREATE DATABASE dbname (eg:mysql>create db test1;     View all databases in the system: mysql>show databases; ）
4. Select database : Usedbname (Eg:mysql>use test1;      View all data sheets in test1: mysql>show tables; ）
6. Delete databases :drop Database dbname; (eg: mysql>drop database test1;
   
8. Creating Tables : Create TABLEtablename(column_name_1 column_type_1 constraints,  

Column_name_1 column_type_1 Constraints,

...                                                         ）; View the definition of a table:desc tablename

5. Sometimes you need to view the SQL statement that created the table .

In addition to seeing the staff table definition, you can see information such as the engine (storage engine) and CharSet (character set) of the table. The "\g" option means that records can be arranged vertically in a field to better display a record with longer content.

6. Delete Tables :drop TABLE tablename (Eg:mysql>drop test;)

7. Modify the table :

(1) Modify table type,ALTER table tablename Modify[column] column_definition [first| After Col_name]

Eg: Modify the ename field definition of the table emp to change varchar (10) to Varcahr (20)

(2) Add table field,ALTER table tablename ADD [COLUMN] column_definition [first| After Col_name]

Eg: new field in table EMP-age, type int (3)

(3) Delete table field,ALTER table tablename DROP [COLUMN] col_name

Eg: delete the field age

(4) Rename the field,ALTER TABLE tablename Change [COLUMN] Old_col_name column_definition [first| After Col_name]

Eg: rename age to Age1, and modify type int (4)

(5) Modify the order of fields, add and Modify fields (add| Change| MODIFY) syntax, you can modify the position of the field in the table First|after column_name. (add defaults to last, Change/modify does not change the location by default)

Eg: new field Birth date (type of birth) added after ename

Eg: Modify field age, put in front

8. Change table name :ALTER TABLE TableName RENAME [to] new_tablename

Eg: renaming the table ENP to EMP1

Two, DML (data Manipulation language) statement: Manipulation statements.

Used to add, delete, update, and query database records, and to check data integrity. Frequently used statement keywords: INSERT, delete, update, select, and so on.

1. Insert record :INSERT INTO TableName (Filed1,filed2,..., filedn) VALUES (Values1,values2,..., values n);

eg

You can also not specify a field name, but the order after values should be the same as the order in which the fields are arranged

Insert multiple records: INSERT INTO tablename (filed1,filed2,..., Filedn)

VALUES

(Values1,values2,..., VALUESN) ,

(Values1,values2,..., VALUESN) ,

(Values1,values2,..., VALUESN) ;

2. Update record: Updated tablename SET filed1=value1,filed2=value2,..., Filedn=valuen [WHERE CONDITION]

Eg: Change the salary (SAL) in the form emp ename to ' Lisa ' to 4000:

(multiple table updates are typically used to dynamically update the fields of another table based on the field of one table)

3. Delete Record : delete fromtablename [WHERE CONDITION]

Eg: delete all records of ename as ' Dony ' in emp

You can delete data from multiple tables at once in MySQL:delete t1,t2,..., tn from T1,t2,..., TN [WHERE CONDITION] (regardless of single table or multi-table, No where condition will delete all records of the table)

4. Query record :SELECT * FROM TableName [WHERE CONDITION]

Query non-repeating record keyword distinct

Sort and limit, keyword: ORDER by syntax:SELECT * FROM TableName [WHERE CONDITION] [ORDER by filed1 [Desc/asc],filed2 [DESC/ASC],.. ., Filedn [DESC/ASC]]

Do not write sort order keywords by default ASC is sorted ascending .

Sorted records, limit limits Display section:SELECT ...              [LIMIT Offset_start,row_count] Where Offset_start represents the starting offset of the record, and Row_count represents the number of rows displayed (the limit and order by mates use the split-screen display for recording)

Aggregation

SELECT [Filed1,filed2,..., Filedn] Fun_name

From TableName

[WHERE Where_contition]

[GROUP by Filed1,filed2,..., Filedn [with ROLLUP]

[Having where_contition]

parameter Description: Fun_name: Represents the aggregation operation to be done (aggregate function), SUM (sum), COUNT (*) (number of records), Max, Min ....

GROUP By: Represents the fields to be aggregated by category, such as the number of employees to be classified by department, and the department should be written behind group by.

With ROLLUP: An optional statement that indicates whether the aggregated results of the classification are re-aggregated.

The HAVING keyword indicates that the results of the classification are being abused under conditions.

table Connection : Left connection, right connection. (Complex, detailed follow-up)

subquery : Keywords in, not in, =,! =, exists, not exists, etc. (after release)

Record Union: After querying the data of two tables according to certain query criteria, the results are merged together and displayed. keyword, Union, UNION ALL

SELECT * from T1

Union/union All

SELECT * FROM T2

...

Union/union All

SELECT * from tn;

UNION ALL merges the result set directly, and the Union is distinct the result of the Union all once, removing the result of the duplicate record.

Third, DCL (Data Control Language) statement:

Statements that control the permissions and access levels of different data segments, which define the database, table, field, user access, and security level. Main statement keywords: Grant, revoke, and so on.

It is primarily used by DBAs to manage object permissions in the system, and is seldom used by general developers.

Summary of basic simple grammar for first knowledge of MySQL