Basic usage of mysql

Welcome to the Linux community forum and interact with 2 million technical staff to create a database: createdatabase [ifnotexist] name [characterset encoding collate checking rules] display the database creation Information: Delete the showcreatedatabasename database: dropdatabase [ifexist] name backup data

Welcome to the Linux community forum and interact with 2 million technical staff> go to create a database: create database [if not exist] name [character set encoding collate checking rule] displays the database creation Information: show create database name database deletion: drop database [if exist] name backup data

Welcome to the Linux community forum and interact with 2 million technicians>

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 (for windows commands and script files, exit the 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): (changed to) mysqldump-u username-p database name <(PATH) file name. SQL (for windows commands and script files, 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 );