MySQL Database Common command notes

Connection database: Mysql-h localhost-u root-p 000000

Exit: Exit;    \q; Quit

SET foreign_key_checks = 0; Disable foreign keys

Indicates that the current command is not executing or waiting for command execution to confirm;

\c used to remove the effect

' >, ' > Indicates that the previous MySQL statement is missing quotation marks

Informaction_schema Primary Store database Database object Information table information Column information Permission information data dictionary

MySQL library, stored MySQL permissions information and server uptime information.

Performance_schema records the save process, details history, etc. (performance dictionaries)

Data types in MySQL database

Complete constraints for columns:

Primary key: PRIMARY key

Self-increment: auto_increment

Defaults: Default '

Not empty: NOT NULL

is empty: null

Unsigned: UNSIGNED

Database operations:

Create database name; Can be added if not EXISTS library name (indicates that it does not exist)

Delete database drop DB name;

View database show DATABASES;

Set the character set default Charset=utf8 when creating a database;

Modify the database character set alter the library name Charset=utf8;

View database character set show CREATE database name;

Select database Use library name;

Operation of the table:

View table show TABLES;

CREATE TABLE table name (field [field constraint]);                    Engine=myisam DEFAULT Charset=utf8; You can precede the table name with if exists to determine if there is

Modify table The original table name RENAME the new table name;

Backup table CREATE table new table name SELECT * from the original table name; Does not back up to primary key and character set

Delete table [IF EXISTS] Table name;

View key table statement show CREATE table; \g represents a full form display

Field actions:

View field DESC table name; Field field name type NULL is null default value extra extra information

Add a field ALTER table name add new field name data type [constraint] positional parameter (at last Add): first indicates which after adding to the top

Modify field data type ALTER TABLE name MODIFY field name new field type [new constraint]

Modify field all ALTER table name change original field name new field name new field type [new constraint]

Delete field ALTER table name drop field name

operator in MySQL: +-*/or div% or mod = <>! = >= <=

MySQL function: Select database (); View your current database

Select MD5 (); Calculate MD5 Encryption

Count (); Calculate the length of the data

SUM (); Accumulation and operation

Concat (); Glue the fields together

TRUNCATE TABLE name; Reset self-increment and empty data tables

Data manipulation:

Add data INSERT into table name (' Field name 1 ', ' Field Name 2 ' ...) values (' Value 1 ', ' Value 2 '); Use anti-quotes in field names to prevent keyword errors

INSERT into table name values (' Value 1 ', ' Value 2 '); All fields in the entire table must be written

INSERT into table name (' Field name 1 ', ' Field Name 2 ' ...) values (' Value 1 ', ' Value 2 '), (' Value 1 ', ' Value 2 ') add more than one data at a time

View all field contents in a table select * from table name; * indicates a wildcard character

Not in indicates no

Like fuzzy match% means any character _ denotes one character

How many data in the query data table is a SELECT COUNT (*) from table name; * Indicates that all fields can be changed to a field then the Statistics field

Sort query order By field ASC (positive order) DESC (reversed)

Sort by ID Positive order SELECT * from the user ORDER by ID ASC;

Limit number of Queries Limit:select field from table name limit query number

Grouping Query GROUP BY

Query how many people in each department SELECT COUNT (*), department from the GZ GROUP by department;

Query Total Payroll for each department SELECT SUM (payroll), department from GZ GROUP by department;

Multi-table Union query SELECT table 1. fields, table 2. Fields ... From table 1, table 2 WHERE table 1. field = Table 2. Fields Note: Multi-table Federated queries must have an associated field to do

Order in which query conditions are used: SELECT statement [WHERE condition] [GROUP BY condition] [having condition not speaking two filters] [order BY condition] [limit condition]

Delete data delete from table name WHERE condition

Delete the sex=0 data in the User1 table delete from User1 WHERE sex=0;

Delete the id=7 data in the User1 table delete from User1 WHERE id=7;

Delete data from the User1 table in id = 3 and id=6 delete from User1 WHERE id=3 OR id=6;

Delete the data from the User1 table in sex = 1 and age = 40, delete from User1 WHERE sex=1 and age=40;

Delete all data for id>10 and id<5 in the User1 table delete from User1 WHERE id>10 or id<5;

Delete all data for id>7 and id<9 in the User1 table delete from User1 WHERE id>7 and ID <9;

Delete all data for id>=7 and id<=9 in the User1 table delete from User1 WHERE id>=7 and ID <=9;

Delete the User1 table in the ID 1 4 5 6 7 delete from User1 WHERE ID in (1,4,5,6,7,10);

Delete data for even numbers of GID fields in a table (not used, inefficient) delete from User1 WHERE mod 2=0;

Modify data UPDATE table name SET field name = value, field name = value WHERE condition

Modify the sex=1,age=18 condition in the User1 table: Data with ID 1,3,5,7,9,10 UPDATE user1 SET sex=1,age=18 WHERE ID in (1,3,5,7,9,10);

Modify the Name= ' Zhangsanfeng ' condition in the User1 table age=40 UPDATE user1 SET name= ' Zhangsanfeng ' WHERE age=40;

Modify the Name=xiaohuahua age=28 condition in the User1 table is that the gender is null for the UPDATE user1 SET name= ' Xiaohuahua ', age=28 where sex is null;

Modify the age=20 condition in the User1 table to be sex not null for UPDATE user1 SET age=20 WHERE sex is not null;

Export command mysqldump [-h localhost]-u user name-p password Library name [table name] > exported file name

Import command Mysql-h host address-u user name-p password Select the name of the library to be transferred in < The file address to import

MySQL Regular (NO)

Inquire

Matches the data in the user name that begins with Z with the end of the G and the middle is any content

SELECT * from user WHERE name REGEXP ' ^z[a-z]+g$ ';

Matches the data in the user name beginning with Z

SELECT * from user WHERE name REGEXP ' ^z ';

Matches the data in the user name ending with g

SELECT * from user WHERE name REGEXP ' g$ ';

Match any one of the letters between a-h in the user name

SELECT * from user WHERE name REGEXP ' [A-h] ';

MySQL Database Common command notes