MySQL basic Chitian Summary, teach you to quickly use the database (1)

1. Display all databases of the data server

show databases;

2. Create a database

Create database if not exits + DB name;

3. Deleting a database

Drop DATABASE + DB name

4. Using the default database

Use + database name;

5. Create a data table

CREATE table if not exits + table name (

Column name + column type,

Column name + column type (comma separated, last one does not need commas)

）;

6. Show tables that already exist under the database

Show tables;

(Displays a table that begins with X)

Show tables like ' x percent ';

7. Display Information Daquan

desc + table name;

--List Database
SHOW DATABASES;
--List database data tables
Show TABLES from database name;
--list data tables and table status information
Show TABLE STATUS from database name;
--List table fields
Show columns from table name from database name;
--list fields and details
Show full COLUMNS from table name;
--List Field full properties
Show full fields from table name;
--List Table index
Show INDEX from table name;
--List DB Server status
-Show STATUS;
--list MySQL system environment variables
--SHOW VARIABLES;
--List execution commands
--show processlist;
--List A user right
Show GRANTS for Root;
8. Three methods of deletion in the database and their differences

Delete

Usage: delete+from+ table name, delete data, keep table structure, can rollback (meaning to recover data). If the amount of data is large, it will be slow.

Truncate

Usage: truncate + table name; Delete data look, keep the table structure, can not roll back, delete all data at one time, relatively fast. Delete content, free space, but do not delete definition

Drop

Usage: Drop + table name delete data and table structure, delete fastest, delete content and definition, free space

9. Inserting data into a table

Insert into table name (column name 1, column name 2, column name 3) VALUES (value one, value two, value three); (previous column name type corresponds to subsequent insert type)

MultiRow Insertion Method

Method One:

INSERT into + table name (column name 1, column name 2) VALUES (value 1, value 2), (value 1, value 2) (value 1, value 2), separated by commas, end with semicolons, only one values is required

Method Two:

INSERT into + table name (column name 1, column name 2) +select ' value 1 ' value 2 ' union ALL + Select ' value 1 ' value 2 ' union all+select ' value 1 ' value 2 '; Note that the middle does not need to split the symbol, ending with a semicolon end, do not need "union ALL" to add data, the character type plus single quotation marks, the numeric type does not need

MySQL basic Chitian Summary, teach you to quickly use the database (1)