Operation of MySQL

First, the operation of the database

　　

• Create DATABASE (Increase)

　　Keyword: Create

　　Syntax form: CREATE DATABASE name [database option]; where there are two database options: One is a character set and one is a collation rule (which can be the default).

　　

• View Database (check)

　　1. See which databases are available: show databases;

　　

　　2. View the creation creation statement for the database: show create database name.

　　

• Deleting a database (delete)

Keyword: drop

Syntax form: DROP database name.

　　

• Modify Database (change)

Keyword: alter

Syntax: ALTER DATABASE name new library option;

　　

Second, data table operation

　　First of all, all the data tables should belong to a specific database! Therefore, any operation on the data table requires that the database to which it belongs is specified first!

There are two ways of doing this:

1, the specified database is displayed

　　　　

2, specify the default database

　　　　

• Create data table (increment)

　　

• View Data Sheet (check)

1. View all data tables under the current database

　　

2. Fuzzy query

Keyword: Like wildcard: _ can represent any single character,% can represent any character

　　

3. View the creation statement for a table

　　

In general, we can use \g instead of a statement terminator to make the result look more organized:

　　

4. View the structure of the table

Syntax: DESC table name

　　

• Delete Data sheet (deleted)

Syntax: DROP table name is more complete syntax: drop table if exists name; (prevents deletion of a nonexistent table error)

　　

• Modify data table (change)

1. Modify the table name

Syntax: ALTER TABLE old table name rename to new table name

　　

In addition, we can also use rename syntax to implement the data table movement:

2. Modifying the column definition

Keywords: superior command keyword ALTER TABLE, subordinate command keyword Add (add), drop (delete), change (rename), modify (Modify)

Grammar:

Add a column: ALTER TABLE name add new column Name field type

　　　　

Delete a column: ALTER TABLE name drop field name

　　　　

3. Modify the field type: ALTER TABLE table name modify field name new field type

　　　　

4. Modify field sort: Alter TABLE name modify field name data type first; ALTER TABLE name modify field name 1 data type after field Name 2; (place field name 1 after field name 2)

　　　　

5. Rename field: ALTER TABLE name change original field new field type;

　　

6. Modify Table Options

Syntax: ALTER TABLE table name tables option information

　　

Modifying the storage engine of a data table

　　

Data operation of data table (increase, delete, check and change of data)

• Inserting data

　　Syntax: INSERT into table name (field list) VALUES (list of values);

　　　

• Querying data

　　Syntax: Select *| field list from table name [query condition];

　　

• Delete data

　　Syntax: delete from table name [delete condition]

　　

• modifying data

Syntax: Update table name set field 1 = new value 1, field 2 = new value 2 ... [Modify conditions]

　　

Operation of MySQL