MySQL learns a

Basic

Commands for the database:

View all databases: show databases;

View the currently used databases: Select database ();

Switch database: use database name;

Creating database: Create database name Charset=utf8;

Delete the database: drop database name;

Commands for data tables:

View all tables: show tables;

Creating Table: Create table table name (ID int auto_increment PRIMARY key NOT NULL,...)

Delete tables: drop table name;

Modify table: ALTER TABLE name add | Change | Drop column;

Commands for data:

　　Query: SELECT * from table name;

Add: INSERT into table name values (...);

Modified: Update table name SET field = value ...

Delete: delete from table name;

Tombstone: Add a column to the table, such as adding a column named Isdelete, type bool, and change the field value of the record that needs to be deleted to 1 instead of just deleting the record. Query, as long as the query isdelete=0 records can be.

Basic query:

SELECT * from table name;

Select Column Name 1, column name 2,... from table name;

Distinct keyword to eliminate duplicate rows

Select DISTINCT column name 1, column name 2,... from table name;

Conditional query:

SELECT * FROM table name where condition;

Fuzzy query:

Like

%: Denotes any number of characters

_: Denotes any one character

SELECT * FROM table name where column name like ' xx%x ';

Scope query:

In: Indicates a non-contiguous range

Query for records with ID 1 or 3 or 4

　　SELECT * FROM table name where ID in (1,3,4);

Between ... : expressed in a contiguous range

Query ID records from 1 to 4

　　SELECT * FROM table name where ID between 1 and 4;

Null judgment:

NOTE:null and ' (two single quotation marks nothing, which means an empty string) is different

Judgment: Is null

Aggregation:5 commonly used aggregate functions in MySQL

COUNT (*): Calculates the total number of rows, writes in parentheses * or column names

Select COUNT (*) from table name;

Max (column): Indicates the maximum value for this column

Select Max (ID) from table name;

Min (column): Indicates the minimum value to be calculated for this column

Select min (id) from table name;

SUM (column): Indicates that the column is evaluated and

Select SUM (ID) from table name;

AVG (column): Indicates the average of this column

　　　　Select AVG (ID) from table name;

Group:

　　GROUP By field: Indicates that the same data for this field is placed in a group and can only be queried after grouping The same data columns, the data columns that have differences cannot appear in the result set. You can count the grouped data and do the aggregation operations .

Select column 1, column 2, aggregation ... from table name Group BY column 1, column 2 ...

Data filtering after grouping:

　　　　Select column 1, column 2, aggregation ... from table name

Group BY column 1, column 2, column 3 ...

Having column 1,... Polymerization...

　　Note: The difference between having and where:

Where: Data filtering is performed on the table specified after from, and the original data is filtered

Having: means filtering the result set for group by (after grouping)

　　　　The original set---where---> Result set---Group---> result set---having--->

Sort:

SELECT * FROM table name order BY column 1 ASC | DESC, column 2 ASC | Desc,...

Paging:

SELECT * FROM table name limit Start,count

Indicates starting from start, gets count bar data, start index starting from 0

Example:

Known: Show m data per page, currently displaying page n (n starting from 1)

Total Pages:

Query total number of bars P1

Use P1 divided by M to get P2

P2 is the total number of pages if divisible

P2+1 is the total number of pages if not divisible

To find data on page n

First, the starting index of the nth page of data is computed as follows:

N Start

1 0 first page, starting from 0

2 m second page, starting from M

3 (n-1) *m page n, starting from (n-1) *m

SELECT * FROM table name limit (n-1) *m,m

Basic Summary:

The complete SELECT statement is written as follows:

SELECT DISTINCT * FROM table name

where ...

GROUP By ... having ...

ORDER BY ...

Limit Start,count

Execution order:

From table name

where ...

GROUP BY ...

SELECT DISTINCT *

Having ...

ORDER BY ...

Limit Start,count

Advanced : Relationships, views, transactions, indexes

Relationship:

Connection:

　　　　

　　

MySQL learns a