Basic usage of mysql _ MySQL

Basic usage of mysql to create a database: create database [if not exist] name [character set encoding collate checking rule] display the database creation information: delete the show create database name database: drop database [if exist] name backup database: mysqldump-u username-p database name> (path) file name. SQL (windows command, script file, to exit SQL command window quit) database recovery (1): Source file name. SQL (SQL command) 1. create database name; 2. restore the data Source file. SQL database recovery (2): (change 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; change the table column name: alter table table_name change column name new_name type; 1. insertion 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 window closed after the failure, permanently valid should change the configuration file) modify the data in the table: update table_name set col_name = value where ...; Delete a record: delete from table_name where ...; You cannot delete the values of a column or the table. when you delete a column, you must note the integrity of the references of other tables. truncate can also delete the records in the 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 is displayed by alias: select (col1 + col2 + col3) as total from table_name (can be used without) in fuzzy query, % represents one or more characters. _ represents a character. order by is placed after the select statement... Order by col_name asc/desc; count: select count (*) from table_name; count the total number of rows of sum function: select sum (column name) from table_name; count the total data in the column AVG returns the average value Max/min returns the maximum value min group: when grouping columns with a aggregate function, use the having clause to filter where at the end. instead of using the where clause to define a primary key (primary key), do not allow null. repeated primary keys cannot be defined to automatically increase auto_increment to define uniqueness: unique non-null: not null; foreign key constraint defined: constraint ordersid_FK foreign key (ordersid) references others (id );