Basic mysql syntax and mysql syntax

Basic mysql syntax and mysql syntax

1. DDL-add, delete, modify, and query

(1) SELECT-obtain data (SELECT * FROM table name WHERE condition)
(2) UPDATE-UPDATE data (UPDATE table name SET (field name = value, field name = value) WHERE condition)
(3) DELETE-DELETE data (delete from table name WHERE condition)
(4) insert into-INSERT data (insert into Table Name (field) VALUES (value ))

2. DDL-create and view

(1) CREATE-CREATE (create database/TABLE (if not exists) DATABASE name/TABLE name character set utf8)
(2) SHOW-view (show databases/TABLES view all DATABASES or TABLES)

3. ALTER-Modify Definition

(1) modify the default character set format of the DATABASE or TABLE-(alert database/table database name/TABLE name character set utf8)
(2) RENAME a TABLE name (alter table old TABLE name rename to new TABLE name)
(2) rename table-this statement is used to rename one or more tables (rename table (old TABLE name TO new TABLE name)/[old TABLE name TO new TABLE name, old table name TO new table name])
(3) field name modification-(alter table name CHANGE old field name new field name INTEGER)
(4) field type modification-(alter table name CHANGE old field name new field name BIGINT (field type ))
(5) ADD field-(alter table name add column title varchar (20) not null after id)
(6) Delete A field (alter table Name drop column title)

4. DROP-delete database/table

(Drop database/TABLE (if exists) DATABASE name/TABLE name)

5. CONSTRAINT-Constraints

(1) non-NULL constraint (not null) (alter table name MODIFY Field name type NULL)
(2) UNIQUE constraint (UNIQUE) (alter table Name drop index unique constraint name)
Constraint unique constraint name UNIQUE (field name, field name)
(3) primary key constraint (primary key auto-increment mode auto_increment) (alter table Name drop primary key)
Field name PRIMARY KEY
Constraint primary key constraint name primary key (primary key field name)
(4) foreign key constraint (alter table Name drop foreign key constraint name)
Constraint foreign key constraint name foreign key (foreign key field name) REFERENCES primary KEY table name (primary KEY field name)
(5) check Constraints

6. VIEW-VIEW

Create view name as SQL statement (cannot contain subqueries)
Drop view name

7. TRANSACTION-TRANSACTION

START TRANSACTION
SQL statement
COMMIT-submit
ROLLBACK-ROLLBACK

8. PROCEDURE-Stored PROCEDURE

Stored Procedure (the parameter types include (1) in (2) out (3) in and out)
Create procedure name ()
BEGIN
SQL statement
END
CALL name (real parameter) (in)
CALL name (@ real parameter) (out)
CALL name (real parameter, @ real parameter) (both in and out)
SELECT @ real Parameter
Use the @ symbol to add a variable name to define a variable (SET @ S = 10)
Drop procedure (if exists) Name

9. INDEX-INDEX

(1) Common Index
Create index index_name ON table name (column (length ))
Alter table name add index index_name ON (column (length ))
INDEX index_name (column (length ))
(2) unique index
Create unique index indexName ON table name (column (length ))
Alter table name add unique indexName ON (column (length ))
UNIQUE indexName (title (length ))
(3) full-text index
Create fulltext index name ON table name (field)
Alter table name add fulltext index_content (field)
FULLTEXT (content)
(4) single-column and multi-column Indexes
The query results of Multiple Single-Column indexes are different from those of a Single-Column index. because MySQL can only use one index when performing a query, one of the most restrictive indexes will be selected.
(5) Composite Index
Alter table name add index index_titme_time (title (50), time (10 ))
The establishment of such a composite index is actually equivalent to the establishment of the following two groups of composite indexes:
-Title, time
-Title