Summary of basic knowledge and operations of MySQL database

This article uses examples to explain the basic knowledge and operations of MySQL database. Share with you for your reference, the details are as follows:

database

1. Modify the data table

Add a column:

ALTERTABLE tab_name ADD col_name column_defi[FIRST|AFTER col_name];

You can specify the position of the new column, either at the very beginning (FIRST) or after a column (AFTER...), otherwise the new column is placed at the end by default.

Add multiple columns:

ALTERTABLE tab_name ADD (col_name1 column_defi1, col_name2 column_defi2 ...);

Delete a column:

ALTERTABLE tab_name DROP col_name;

To delete multiple columns or add a new column after deleting them, just add a comma after drop col_name and then write drop/add.

Add constraints:

ALTERTABLE tab_name ADD PRIMARY KEY (index_column);

The primary key can be replaced by other constraints such as UNIQUE and FOREIGN KEY, and the default constraint is SET DEFAULT.

To delete a constraint:

ALTERTABLE tab_name DROP {INDEX|KEY} index_name

To delete a foreign key:

ALTER TABLE tab_name DROP FOREIGN KEY fkey_name

The foreign key constraint name fkey_name can be obtained by viewing the constraint item through show create table tab_name;, for example

CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES

Modify the column definition:

ALTERTABLE tab_name MODIFY col_name col_defi;

Modify the column name:

ALTERTABLE tab_name CHANGE old_colname new_name col_defi;

Modify the data table name:

RENAMETABLE old_name TO new_name;

2. Data Table Operation

Insert a record:

INSERT tab_name[(col_name)] VALUES (val1,val2...)

The column name is optional. If you do not set the column name, the default is to insert each column. The second insertion method: INSERT tab_name SET col_name=val...The third method: insert tab_name select…, insert the query results into the data table.

Update log:

UPDATE tab_name SET col_name=val [WHERE condition];

If the where condition is not filled in, all records will be updated by default.

Deleting records:

DELETE FROM tab_name [WHERE condition];

Delete the records that meet the conditions. If you do not fill in where, all records will be deleted.

Query records:

SELECT col_name1,col_name2... /*Select the column name to be queried*/

FROM tab_name/*Select the data table to be queried*/

WHERE condition/*Query condition*/

GROUP BY col_name [ASC|DESC]/*Query result grouping*/

HAVING condition/*Filter the query group*/

ORDER BY col_name[asc|desc]/*Sort the query results by row*/

LIMIT [n1,]n2/*Returns n2 results starting from n1. If n1 is not filled in, it will return from the beginning by default*/

3. Subquery

The parent query and the subquery are connected by comparison symbols. When the subquery returns multiple results, the results can be modified by ANY or ALL.

Multi-table update: Update this table with reference to another table.

Inner join: INNERJOIN ON join_condition , returns the items that meet the conditions in the left and right tables.

Left outer join: LEFTJOIN ON join_condition , returns all items in the left table that meet the condition in the right table.

Left outer join: RIGHT JOIN ON join_condition , returns all items in the right table that meet the condition in the left table.

4. Database Functions

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.