What are the basic syntaxes of mysql?

1. DML-Add, Delete, Modify and Query

(1) SELECT - Get 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) 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 the default character set format of the library or table - (ALERT DATABASE/TABLE library name/table name CHARACTER SET utf8)
(2) Rename the 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 table name CHANGE old field name new field name INTEGER)
(4) Field type modification - (ALTER TABLE table name CHANGE old field name new field Name BIGINT (field type) )
(5) Add field - (ALTER TABLE table name ADD COLUMN title varchar (20) NOT NULL AFTER id)
(6) Delete field - (ALTER TABLE table name DROP COLUMN title )

4. DROP-delete library/table

(DROP DATABASE/TABLE (IF EXISTS) library name/table name)

5. CONSTRAINT - Constraints

(1) Non-null constraints (NOT NULL) (ALTER TABLE table name MODIFY field name type NULL)
(2) Unique constraints (UNIQUE) (ALTER TABLE 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 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 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 constraints

6, VIEW - view

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

7, TRANSACTION - Transaction

START TRANSACTION
SQL statement
COMMIT - Submit
ROLLBACK - Rollback

8, PROCEDURE - Stored procedure

Stored procedure (parameter types are (1) in (2) out (3 )in and out at the same time)
CREATE PROCEDURE naming()
BEGIN
SQL statement
END
CALL naming(actual parameter)(in)
CALL naming(@actual parameter) (out)
CALL naming (actual parameter, @actual parameter) (both in and out)
SELECT @actual parameter
Use the @ symbol plus the variable name to define a variable (SET @S=10 )
DROP PROCEDURE (IF EXISTS) Naming

9, INDEX - Index

(1) Ordinary index
CREATE INDEX index_name ON table name (column (length))
ALTER TABLE 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 table name ADD UNIQUE indexName ON (column(length))
UNIQUE indexName (title(length))
(3) Full-text index
CREATE FULLTEXT INDEX index name ON table name (field)
ALTER TABLE table name ADD FULLTEXT index_content (field)
FULLTEXT (content)
(4) Single-column index and multi-column index
Multiple single-column indexes and a single multi-column index The query effect is different, because when executing the query, MySQL can only use one index, and will select the most restrictive index from multiple indexes.
(5) Combined index
ALTER TABLE table name ADD INDEX index_titme_time (title(50), time(10))
Establishing such a combined index is actually equivalent to establishing the following two sets of combined indexes. ：
–title,time
–title

The above is the detailed content of What are the basic syntaxes of mysql?. For more information, please follow other related articles on the PHP Chinese website!