Basic SQL syntax

SqlThe statement can be divided into the following3Categories.
?Ddl（Data Definition Languages) Statements: Data definition languages, which define different data segments,
The definition of database objects such as databases, tables, columns, indexes, and so on. The commonly used statement keywords mainly includeCreate、Drop、Alter
such as
?dml (data manipulation Language: data manipulation statements for adding, deleting, updating, and checking
database records. and check data integrity, commonly used statement keywords mainly include insertdelete< Span class= "Fontstyle2", udpate Select dcl (data Control language" statement: A Data control statement that controls the direct permission and
access level statements for different data segments. These statements define the database, table, field, user's access rights, and security level. Key
Statement keywords include grantrevoke

DDL Statements
DDL is the abbreviation of the data definition language, simply speaking, the object inside the database is created, deleted, modified
The operating language. The biggest difference between it and the DML language is that DML is just an operation on the table's internal data, not the table's fixed
Changes in semantics, structure, and other objects are not involved. DDL statements are more used by database administrators (DBAs),
Common developers are rarely used.
Here are some examples of how common DDL statements are used in MySQL.

--Create a table
--Create TABLE EMP (ename varchar), HireDate date,sal Decimal (10,2), Deptno Int (2));
--View the definition of a table
--DESC TableName

DESC EMP;
--View the SQL statement that created the table
Show CREATE TABLE emp \g;
--Delete Table
DROP TABLE TableName

--Modify the table, ALTER TABLE TableName MODIFY [COLUMN] column_definition [First | After Col_name]
ALTER TABLE EMP Modify ENAME varchar (30);
--Add Table field ALTER table TableName ADD [COLUMN] column_definition [First | After Col_name]
ALTER TABLE EMP Add column age int (3);

--Delete the table field with the following syntax:
--ALTER TABLE tablename DROP [COLUMN] Col_name
ALTER TABLE EMP drop age;

--The field is renamed with the following syntax:
--ALTER TABLE tablename change [COLUMN] Old_col_name column_definition[first| After Col_name]
ALTER TABLE EMP Change sal salary decimal (10,2);
-Note: Change and modify can modify the definition of the table, the difference is that the changes after the need to write two times column name, inconvenient.
--but the advantage of change is that the column name can be modified, modify not.

--Table renamed ALTER table TableName RENAME [to] New_tablename
ALTER TABLE EMP RENAME employee;

--Insert
INSERT into T_user (name,password,cost) VALUES (' dd ', ' Zhang123 ', ' n '), (' Ahah ', ' Lisi123 ', 1200);

--Update record updates tablename SET Field1=value1,field2.=value2,......fieldn=valuen [WHERE CONDITION]
Update t_user set name= ' Lisi ', password= ' Lisi123 ', cost=1200.00 where id = 4;

--Delete record delete from TableName [WHERE CONDITION]
DELETE from T_user WHERE id=1;

Note: Regardless of whether it is single table or multiple tables, no where condition will delete all records of the table, so be careful when you operate.

SELECT * from T_user;

--Go to heavy distinct
-Where DESC and ASC are sort order keywords, desc indicates descending order by field, ASC indicates ascending
--Arrange, if not write this keyword is sorted in ascending order by default.

--For a sorted record, if you want to display only a subset, not all, then you can use the Limit keyword to do so, with the following syntax for limit:
SELECT ... [LIMIT offset_start start offset, number of rows displayed Row_count]
By default, the starting offset is 0, just write the number of rows of records can be, at this time, the actual display is the first n records,
SELECT * from T_user ORDER by ID limit 6, 2;

Basic SQL syntax