MySQL basic syntax

1, ddl-additions and deletions to check

(1) Select-Get Data (SELECT * from table name WHERE condition)
(2) Update-Updates 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)

2. ddl-Create and view

(1) Create-Creates (creation database/table (IF not EXISTS) library name/table name CHARACTER SET UTF8)
(2) Show-view (show Databases/tables view all libraries or tables)

3, alter-Modify the definition

(1) Modify library or table default character set format-(ALERT database/table library name/table name CHARACTER set UTF8)
(2) Table name Rename-(ALTER table name RENAME to new table name)
(2) RENAME table-This statement is used to rename one or more Tables (RENAME table (old 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 () not NULL after ID)
(6) Delete field-(ALTER table name drop COLUMN title)

4. drop-Delete Library/table

(DROP database/table (IF EXISTS) library name/table name)

5. CONSTRAINT-Constraint

(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 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 Constraint

6. View-Views

CREATE view name as SQL statement (cannot contain subqueries)
DROP View Name

7. TRANSACTION-Business

START TRANSACTION
SQL statements
Commit-Commit
ROLLBACK-Rollback

8. PROCEDURE-Stored Procedure

Stored procedures (parameter types are (1) in (2) out (3) in and out)
CREATE PROCEDURE name ()
BEGIN
SQL statements
END
Call naming (argument) (in)
Call naming (@ real parameter) (out)
Call naming (actual argument, @ argument) (in and out)
SELECT @ real parameter
Define a variable with the @ sign plus the variable name (SET @s=10)
DROP PROCEDURE (IF EXISTS) naming

9, index-index

(1) General 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 indexing
CREATE Fulltext index index name on table name (field)
ALTER table name ADD fulltext index_content (field)
Fulltext (content)
(4) Single-column index and multicolumn index
Multiple single-column indexes differ from the query effect of a single multicolumn index because MySQL can use only one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes.
(5) Combined index
ALTER table name ADD INDEX index_titme_time (title (), Time (10))
Creating such a composite index is actually equivalent to establishing the following two sets of composite indexes:
–title,time
–title

MySQL basic syntax