MySQL Basic statement--Add, delete, check, change

1. Creation, modification and deletion of database

Create:

CREATE {DATABASE | SCHEMA} [IF not EXISTS] db_name [create_specification];

For example:

CREATE DATABASE IF not EXISTS test CHARACTER SET ' GBK ' COLLATE ' gbk_chinese_ci ';

Modify:

ALTER {DATABASE | SCHEMA} [db_name] alter_specification;

You can usually only modify the database character set and collation.

Delete:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

2. Creation, modification, deletion of tables

Create: Three ways to create a table

Define an empty table directly

CREATE TABLE [IF not EXISTS] tb_name (field Name field definition,...) [Table_options];

Querying data from other tables and creating a new table: The format definition for the field will not exist

CREATE TABLE [IF not EXISTS] tb_name [(Field Name field Definition,...)] [Table_options] select_statement;

Create an empty table as a template with another table

CREATE TABLE [IF not EXISTS] tb_name like old_tb_name;

Modify:

ALTER TABLE tbl_name [alter_specification [, alter_specification] ...];

Alter_specification:

Add: Adds fields, indexes, constraints.

Change | MODIFY: Modify the field definition, change can modify the field name, and MODIFY not.

Drop: Delete fields, indexes, constraints.

RENAME [To|as]: Renames the table name.

CONVERT to CHARACTER set: Modifies the default character set.

Delete:

DROP TABLE [IF EXISTS] Tbl_name

3. Creation and deletion of indexes

Create:

CREATE INDEX index_name on Tb_name (col_name [(length)] [ASC | DESC],...);

Length: The size of the index, which indicates how long the character is compared from the left.

ASC: Sort in ascending order.

DESC: Sort in descending order.

Delete:

DROP INDEX index_name on Tbl_name;

4. Query operations

simple query : projection, selection

SELECT select-list from TB WHERE qualification;

FROM clause: the relationship to query.

WHERE clause:

Boolean relationship Expressions: =, <, >, >=, <=.

Logical relationships: And, or, not.

Between ... And ... : Between WHO and who.

Like:

%: Any character of any length
_: Any single character

REGEXP, Rlike: Similar to like, supports regular expressions.

In: Do discrete values, expressed in a list.

is [not] null: empty.

ORDER by Field_name [asc| DESC]: Sorts the results after the query.

Field aliases: As

Limit clause: limit [offset,]count

Offset: Represents the offset and skips the result of the previous offset bar that matches the condition.

Count: Displays the result of the previous count bar that meets the criteria.

Aggregation: SUM (), MIN (), MAX (), AVG (), COUNT ().

GROUP BY: Grouping

Having qualification: Filters the conditions in the grouping.

Multi-table query :
Connection:
Cross join: Cartesian product.
Natural join: All rows with the same name on the same property.

Outer joins: whichever is left or right, NULL is displayed if there is no corresponding value in the other table.
Left outer connection: Tb_name ieft JOIN tb_name on condition
Right outer connection: Tb_name-join Tb_name on condition
Self-connect: Joins the query results from the same table.

Sub-query :

Subqueries are used in comparison operations: Subqueries can only return a single value.
In (): Use subquery in.
Use subqueries in from.

MySQL Basic statement--Add, delete, check, change