Data query and multi-table operation in the second stage MySQL function library table

A beginner of PHP's accompanying notes, record their growth!

I. Table structure modification (DDL)

1. Add a field to the table structure
A.alter Table Name
ADD field name data type [Field Properties | constraints]
[first| After field name];

B. Adding multiple fields
ALTER Table Table Name
ADD field name data type [Field Properties | constraints]
[first| After field name],
ADD field name data type [Field Properties | constraints]
[first| After field name] ... ；

2 Delete a field
ALTER Table Table Name
DROP Field Name,
DROP field name ...;

3. Modifying fields
ALTER Table Table Name
MODIFY field name data type [Field Properties | constraints]
[first| After field name];
4. Modify the field name
ALTER Table Table Name
Change old field name new field name data type [Field Properties | constraints]
[first| After field name];

5. Add default values
ALTER Table Table Name
ALTER field name SET default value;
6. Delete default values
ALTER Table Table Name
ALTER field name DROP DEFAULT;

7. Add a PRIMARY key
ALTER Table Table Name
ADD PRIMARY KEY (field name);
8. Delete primary key
ALTER TABLE key| Inde table Name
DROP PRIMARY KEY;
Note: If you have a auto_incrment attribute, you must first delete the
Auto_incrment to delete a primary key

9. Add Uniqueness
A.alter Table Name
ADD UNIQUE key| INDEX (field name);
B.a.alter Table Name
ADD [CONSTRAINT constraint name] UNIQUE key| INDEX (field name);
10 Deletion of uniqueness
ALTER Table Table Name
DROP key| The name of the INDEX uniqueness constraint;
Description: If adding uniqueness does not set a Uniqueness constraint name,
Default To Field name
11. Modify the table name
A. ALTER table name
RENAME [as| To] a new table name;
B. RENAME table old name
To the new table name;
12. Reset Auto-Grow
ALTER Table Table Name
Auto_increment=1;
Two. Help
? Command
Help command
\h command

Three. Operations on data in tables (DML)
(1) Add (INSERT)
a. Addition of table fields is not listed (values are assigned from the first field to the last field)
INSERT [into] table name
Value[s] (value, value, value ...);
B. Do not omit field additions (add a record)
INSERT [into] table name (field name, field name ...)
Value[s] (value, value ...);
C. Do not omit field additions (add multiple Records)
INSERT [into] table name (field name, field name ...)
Value[s]
(value, Value ...),
(value, Value ...) ...;
D.insert ... SET (only one record can be added)
INSERT [into] table name SET field name = value,
Field name = value ...;
(2) Updates (update)
UPDATE table name SET field name = value,
Field name = value ... [WHERE condition];
Description
If the update does not have a where condition, the fields in the table
Values are the same value record
(3) Remove (delete)
A.delete from table name [WHERE condition];
Description: If the delete does not have a where condition,
All the data in the table will be emptied,
Auto_increment does not reset
B. TRUNCATE table name;
Description: Clears all data in the table,
and auto_increment Reset
Four. Query for data in the table (DQL)
1. Query all fields (efficiency bottom)
SELECT * from table name;

2. List field queries
SELECT Field name |expr, field name |expr ...
from table name;
3. Alias a field
SELECT field name [as] Alias
from table name;
4. Alias the table
SELECT Field Name
From table name [as] alias;
5. Table name (alias). Field name (the table from which the field comes from)
SELECT table name (alias). Field Name
From table name [as] alias;
6. Database name. Table name (which database the table comes from)
SELECT Field Name
From database name. Table name;
7.
SELECT Field name |expr, field name |expr ...
From table name
[WHERE condition]
[GROUP by Field]
[Having condition]
[ORDER by Field]
[LIMIT [$offset,] $length];
7.1 WHERE Condition: Conditional filtering
(1) Comparison operators
> >= < <= = (equals)! = <>
<=> (judging null)
(2) is [NOT] null (judge null)

(3) [NOT] between ... and Range values

(4) [NOT] in (value, value ...) a value

(5) Logical operators implement conditional filtering
! Non-
&& and
|| OR OR

(6) Like to implement Fuzzy query
[NOT] Like ' string '

Key words
(1)%: Contains any number of characters (0, 1, multiple)
(2) _: contains any one character

7.2 Group By field: grouping, the same value in the field
into a group
Description
A. The grouping value displays a result if multiple values are displayed
Small numbered results, generally displaying grouped fields
B. Grouping combined with aggregation functions
COUNT (*): Gets the number of each group, including null
COUNT (field): does not contain a null value
AVG (field): Gets the average in each group
Max (field): Get the maximum value in each group
Min (field): Gets the minimum value in each group
SUM (field): Get and in each group

7.3 Having condition: two-time conditional filtering
Description
A. WHERE-to-field conditional filtering
B. Having a condition filter on a running result,
Combine grouping using

7.4 Order By field: sort fields
Description
ORDER by field [ASC Ascending | Desc Descending]

7.5
LIMIT [$offset,] $length: Show N Records
Description
A. $offset: Offset, starting number, numbering starting from 0
B. $length: Show record (row) length
C. Implementing the paging principle of Web programs
Calculation $offset = (Current page-1) * Display length
$offset = ($curpage-1) * $pagesize

Data query and multi-table operation in the second stage MySQL function library table