Summary of common MySql commands

Summary of common MySql commands

User

Logon: mysql-u username-p Password

Add User: insert into mysql. user (host, user, password, select_priv ,...) value ('server name', 'username', 'Password', 'authorization ',...);

Manage databases:

Show database;

Create database;

Delete: drop database T

Open Database use T

Manage data tables:

Table creation: create table name (field name, field type, field attribute, field name, field type, field attribute );

Display data table structure: describe name

Modify Field Type: alter table Database Name change Field name new field name;

Add field: alter table database name add field Name field type

Delete field: alter table Database Name drop field name

Drop table

Table Name Change: rename table name to new table name

Manage records

Add record: insert into Table Name (field name) value ('field value ')

Batch Processing: source SQL. SQL

Delete record: delete from table name where condition expression

Delete all records from table names

Modify update table name set field name = field value where condition expression

Query a data table:

Query all: select * from Table Name

Query order: select ID card number, name, mobile phone from member order by ID card number

Multi-Table query:

Join query: select * form region, member where region. id = member. id

Three-table join query select * from nation, member, book where nation. id = member. id and region. id = book. id

Left join select * form a left join on B where a. id = B. id

Select * from a inner join on B where a. id = B. id

Wildcard:

Wildcard characters are used for string processing. % matches all characters. _ matches one character.

Query all users with the specified name in the user table.

Select * form user where name like 'sheet %'

Except for

Select * form user where name not like 'sheet %'

Find out that the names of all surnamed Zhang are two words.

Select * form user where name like 'sheet _'

Function:

Count (*) count the number of records

Select count (*) from member

Sum (field name) for the specified field name

Avg calculates the average value for the specified field name

Max (field name) calculates the maximum value

Min (field name) calculates the minimum value

Select count (distinct id) from member ID deduplication count