MySQL Defragment _2_ database operations

The database operation is divided into three parts, namely, DDL (database, table operation) DML (data Delete and change) DQL (check)

"DDL" CREATE | ALTER | DROP

Database

"CREATE DATABASE"

CREATE datebase database name DEFAULT charracter SET UTF8 COLLATE UTF8 general_ci;

"View Database"

SHOW DATABASES;

"Modify Database"

ALTER database name CHARACTER SET UTF8;

"Delete Database"

DROP database name;

"Use (switch) database"

Use database name

"Data Table"

"Data Type"

Value (number) type

-INT

-Float/double

-DECIMAL exact value: more for the amount

Date type

-Dtae (YYYY-MM-DD)

-DATETIME (Yyyy-mm-dd hh:mm:ss)

-TIMESTAMP (timestamp: Unique identifier)

"String Type"

-CHAR (num) length fixed string

-VARCHAR (num) only limits the maximum length of a string

Constraints

* "PRIMARY KEY constraint" PRIMARY key-unique non-repeatable

* "PRIMARY key self-increment constraint" auto_increment

"Create data Table"

CREATE TABLE data table name (    field name 1 data type,    // field name equivalent to each column header    field name 2 data type,    ...);

Example

CREATE TABLEMyUser (//identity (unique, non-repeatable) IDINT PRIMARY KEYauto_increment, nameVARCHAR( -), PWDVARCHAR( -), ageINT, emailVARCHAR( -), addrVARCHAR( -) );

"Delete data table"

DROP  table data table name;

"View data table Structure"

DESC table name;

"DML" additions and deletions-INSERT | UPDATE | DELETE

"New Data" INSERT

[Usage 1]

INSERT into Table name values (field value 1, field value 2,...);

Note

-How many fields are in front, and how many field values are behind them

　　　　-If the field is a primary key self-increment, use NULL to complete the

[Usage 2]

INSERT into table name (field name 1, field name 2,..) VALUES (field value 1, field value 2,...)

Note

-Field names correspond to field values

-The Current Data table field is allowed to be empty

"Update Data" update

[Usage 1]

UPDATE table name SET field name = field value;

Note

-All field values (whole columns) corresponding to field names have been modified

[Usage 2]

UPDATE table name SET field name = field value WHERE field name = field value;

[Usage 3]

UPDATE table name SET field Name 1 = field value 1, field name 2 = field value 2 WHERE field name = field value;

"Delete Data"

[Usage 1]

Dalete from table name;

Note

-Delete all data from the specified table

[Usage 2]

DELETE from table name WHERE field name = field value;

Problem

-basically do not use DELETE statements in the actual operation, in order to prevent user regret

SQL Action (delete)

-Physical Delete: delete

-Tombstone: Surface removal

Adds a (state|status) field to the specified table that represents the status of the current record

-A value of 1: Indicates a normal record

-A value of 0: indicates record deletion

"DQL" Check

"Basic Query"

[Usage 1]

SELECT * from table name;

[Usage 2]

SELECT Field name 1, field name 2,... from table name;

"Conditional basic Query"

Usage

SELECT * FROM table name WHERE field name = field value;

"Complex condition Query"

-and multiple conditions satisfy both

-OR multiple conditions as long as one is satisfied

-In (SET) indicates that a field contains multiple values

SET-use ', ' between multiple field values

-= indicates that the field value is the specified value

-Between ... And ...

-is null match null value

"Sort Query" order By field name

ASC-ordinal sort, default value

DESC-Reverse order

　　-SELECT * FROM table name WHERE condition ORDER by asc| DESC

MySQL Defragment _2_ database operations