MySQL Grammar summary

Set the character encoding used by the SQL statement: set names UTF8;

Determines whether the specified database exists: DROP db if EXISTS library;

Start using the specified database: use library;

Create database for creating databases;

Create data table and field settings file type UTF8;

INSERT into table VALUES (field);

SQL statement Classification:

(1) DDL: Data Definition Language-defining column----of tables Create, DROP, ALTER, truncate

(2) DML: Data manipulation language----record line------of the action table INSERT, DELETE, UPDATE

(3) DQL: Data Query Language---does not affect the table row and column-----"SELECT

(4) DCL: Data Control Language-control user's permission------GRANT, REVOKE

Query syntax:

Find all databases: show databases "S" refers to negative numbers

Find all tables in the library: show tables;

Find all data in a table: SELECT * FROM table; "*" refers to a global variable

Find data for a field: Select field from table;

Query data: SELECT * from XXX;
Example:
(1) Select Id,username,password from T_user;
(2) Select Id,username,password,gender from t_user where gender = ' male ';
(3) Select Id,username,password,gender from t_user where gender is null;
Add data: Insert XXX (ID, username) VALUES (xx, "xxx");
Example:
INSERT into T_user (ID, username) VALUES ("hehehe");
INSERT into T_user (ID, gender, username, age, password) values (15, ' Male ', ' Shihu ', 18, ' 123456 ');
INSERT into T_user values (+, ' Benladeng ', ' 123456 ', ' Bin Laden ', ' nan ', 18);
3
Modify data: Update tablename set xx=xx,xxx=xx where xxx=xxx and xxx=xxx;
4
Delete data: Delete from tablename where xx=xxx and xxx = xxx or xxx = xxx;

Querying Date type data
SELECT * FROM table WHERE Time field = ' 2011-04-08 '
Note: Different databases have differences in date type data:
(1) Mysql:select * from tb_name WHERE birthday = ' 2011-04-08 '
(2) SQL Server:select * from tb_name WHERE birthday = ' 2011-04-08 '
(3) Access:select * from tb_name WHERE birthday = #2011 -04-08#

Querying for non-empty data
SELECT * from Tb_name WHERE address <> "ORDER BY addtime Desc
Note:<> is equivalent to! = in PHP

Query Top N Records
SELECT * from Tb_name LIMIT 0, $N;
The limit statement is used in conjunction with other statements, such as an order by, and uses a variety of SQL statements to make the program very flexible

Query after N records
SELECT * from Tb_stu ORDER by ID ASC LIMIT $n

Query n records starting at a specified location
SELECT * from Tb_stu ORDER by ID ASC LIMIT $_post[begin], $n
Note: The ID of the data is starting from 0

The first n records in the query statistics results
SELECT *, (Yw+sx+wy) as total from Tb_score ORDER by (yw+sx+wy) DESC LIMIT 0, $num

Querying data for a specified time period
SELECT the field to find from table name WHERE field name between initial value and terminating value
SELECT * from Tb_stu WHERE age between 0 and 18

Query statistics by month
SELECT * from Tb_stu WHERE month (date) = ' $_post[date] ' ORDER by date;
Note: The following functions are available in the SQL language, which makes it easy to query by year, month, and day
Year (data): Returns the value corresponding to the A.D. in the data expression
Month (data): Returns the value of the month in the data expression
Day (data): Returns the numeric value of the date in the data expression

Query for records that are larger than the specified criteria
SELECT * from Tb_stu WHERE Age>$_post[age] ORDER by age;

Query results do not show duplicate records
SELECT DISTINCT field name from table name WHERE query condition
Note: The distinct in the SQL statement must be used in conjunction with the WHERE clause, otherwise the output information will not change and the field cannot be replaced with *

A query that does not combine conditions with predicates
(1) Not Berween ... And ... Query on rows between start and end values can be changed to < start value and > End value
(2) is not null to query for non-null values
(3) Is null to query for null values
(4) In this style, depending on whether the keyword used is included in the list or excluded from the list, the search expression can be a constant or a column name, and the column name can be a set of constants, but more of a subquery

Displays the number of duplicate records and records in the data table
SELECT Name,age,count (*), age from tb_stu WHERE age = ' + ' GROUP by date

Query the data in descending/ascending order
SELECT field name from Tb_stu WHERE condition order BY field desc Descending
SELECT field name from Tb_stu WHERE condition ORDER by field ASC Ascending
Note: When sorting a field without specifying a sort order, the default is ASC ascending

Multi-criteria query for data
SELECT field name from Tb_stu WHERE condition ORDER by field 1 ASC Field 2 DESC ...
Note: The query information is ordered in order to jointly limit the output of the record, in general, because it is not a single condition limit, so there are some differences in the output effect.

Sort the results of a statistic
The function sum ([All] field name) or sum ([DISTINCT] field name) can be used to sum the fields, sum all records for all of the fields in the function, and sum the fields for all of the fields that are not repeating records for DISTINCT.
such as: SELECT name,sum (Price) as Sumprice from Tb_price GROUP by name

SELECT * from Tb_name ORDER by Mount Desc,price ASC

Single row data grouping statistics
SELECT Id,name,sum (Price) as title,date from Tb_price GROUP by PID ORDER by title DESC
Note: When the grouping statement group by sort statement order by is present in the SQL statement, the grouping statement is written before the sort statement, otherwise an error occurs

Multi-column data grouping statistics
Multi-column data grouping statistics are similar to single row data grouping statistics
SELECT *,sum (Field 1* field 2) as (new Field 1) from table name GROUP by field ORDER by new Field 1 DESC
SELECT Id,name,sum (Price*num) as Sumprice from Tb_price GROUP by PID ORDER by Sumprice DESC
Note: The group BY statement is usually followed by a series that is not an aggregate function, that is, a column that is not to be grouped
Multi-table grouping statistics
SELECT A.name,avg (A.price), B.name,avg (B.price) from tb_demo058 as a,tb_demo058_1 as B WHERE a.id=b.id GROUP by B.type;

MySQL Grammar summary