Basic usage of mysql

Create database: create database [if not exist] name [character set encoding collate checking rule]

Show create database name

Delete A database: drop database [if exist] name

Backup Database: mysqldump-u username-p Database Name> (PATH) file name. SQL (windows Command, script file, to exit the SQL command Window quit)

Database restoration (1): Source file name. SQL (SQL command)

1. create database name;

2. Restore the data Source file. SQL

Database recovery (2) :( Step 2 changed to) mysqldump-u username-p database name <(PATH) file name. SQL (windows Command, script file, to exit the SQL command Window quit)

View the table structure: desc table_name;

Change the table name: rename table table_name to new_name;

Alter table table_name change column name new_name type;

1. Insert failure solution:

Show variables like 'chara %'

Set character_set_client = gb2312 Show the solution after the failure: set character_set_results = gb2312 (only valid for this time window closed after the failure, permanently valid should change the configuration file) Modify Table data: update table_name set col_name = value where ...; Delete a record: delete from table_name where ...; The values of a column cannot be deleted or the table cannot be deleted. When deleting a column, pay attention to the integrity of the reference with other tables. Truncate can also delete records in a table (destroy the table first and then recreate the table structure) Query statement: select [distinct] (filtering out duplicate data) col1, col2 from table_name; Multiple Data operations can be performed: select col_name + 10 from table_name; select (col1 + col2 + col3) from table_name Display by alias: select (col1 + col2 + col3) as total from table_name (can be used without) In fuzzy search, % Represents one or more characters. _ represents one character. Order by is placed behind the select statement for sorting... Order by col_name asc/desc; Count statistics: select count (*) from table_name; count the number of rows Sum aggregate function: select sum (column name) from table_name; count the total data of this column AVG returns the average value. Max/min returns the maximum and minimum values. Group by: groups columns. When Aggregate functions exist, use the having clause to filter where at the end, instead of where. Defining the primary key (primary key) is not allowed to be empty and repeated is not allowed Define auto_increment for automatic primary key growth Definition uniqueness: unique Non-null: not null; Define foreign key constraints: constraint ordersid_FK foreign key (ordersid) references others (id );