MySQL Common Commands Collection

One, DCL statements (Data control statements)

1, authorized remote access, for IP and user, db

Grant {Privilege List} on {dbname}.* to ' {user} ' @ ' {IP} ' identified by ' {pwd} ' with GRANT option

Ex:grant all privilege on * * to ' root ' @ '% ' identified by ' * * * ' with GRANT option; --% represents all IP

Grant all privileges on * * to ' exam ' @ ' 192.168.0.% ' identified by ' * * * ' with GRANT option; --Specifies that the IP segment is authorized for all permissions, using the specified account/password

Note: In the Mysql.user table, if there is no localhost/127.0.0.1 such a record (by default), then the connection through SSH will also fail, if it is the library on their own machine, because the default is localhost

II. DDL statements (data definition statements)

1. Create user {Usrname} identified by ' {pwd} ';

2, CREATE database {dbname} character set ' UTF8 ' collate ' utf8_general_ci ';

III. DML statements (data manipulation statements)

Show databases//list MySQL server database

Show tables [from db_name]//List Database data table

Show table status [from db_name]//List data table and table state information

Desc tb_name//List data table information

Show full columns/fields from Tbl_name//List table field complete properties

Show index from Tbl_name [from db_name]//List table index

Show status//list DB Server status

Show variables//list MySQL system environment variables

Show Processlist//list the execution process of the DB

Show grants for user//List a user right

Show index from DB.TBNAME//view table indexes

Show (Full) processlist//view session process, all display fully, no top 100

Kill ID//Kill a session, ID can be seen through show processlist

Batch update between two tables (unlike SQL Server, Oracle, etc., because the FROM statement is not allowed in MySQL update)

Update Moa_ios_devicetoken M1, Moa_ios_devicetoken m2 set m1.device_token=m2.device_token where m1.user_id=m2.user_id and m1.line_id in (1,2,4) and m2.line_id in (5,6,8);

MySQL export table structure and table Data mysqldump usage: (mysqldump usage premise, must have DB, and use db)

The following command-line specific usage is as follows: Mysqldump-u username-p password-d/-t The name of the script in the database name table; (-D for structure,-t for data, no representation of structure and data)

1, export the database as DBNAME table structure (where the username is root, the password is DBPASSWD, the generated script is named Db.sql)

Mysqldump-uroot-pdbpasswd-d dbname >db.sql;

2. Export the database as dbname a table (test) structure

Mysqldump-uroot-pdbpasswd-d dbname test>db.sql;

3. All objects in the database, excluding data

Mysqldump-uroot-pdbpasswd-r--no-data dbname >db.sql;

4. Complete export database (including Table object, PROC, FUNC, TRIGGER, data)

Mysqldump-uroot-pdbpasswd-r dbname >db_all.sql;

5. Full Import database (including Table object, PROC, FUNC, TRIGGER, data)

SOURCE Db_all.sql; --Some say with mysqldump-uroot-pdbpasswd dbname

To modify a table field's statement:

1. Add field: Alter TABLE tbname add ColumnName field type [allow non-null]

2. Field name: ALTER TABLE tbname change oldcolumnname newcolumnname field type [allow non-null]

3, change the field type: ALTER TABLE tbname modify ColumnName field type [whether non-null allowed];

4. Delete field: ALTER TABLE tbname drop column columnName;

5. Modify field Comment: Alter TABLE tbname modify column columnName field type default defvalue comment ' field comment ';

MySQL start stop: service MySQL start mysqladmin-uroot-ppwd shutdown

MySQL Connection authorization: Grant all privileges on im.* to [email protected] '% ' identified by ' 123456 '--Im is my own library,% represents any connection

Common Operation Highlights:

1. How to delete duplicate records:

A gets a duplicate record that is inserted into the temporary table.

CREATE TABLE User_info_a Select Recordid,name,userid,min (RecordID) as mid from User_info Group by UserID have count (1) & Gt 1;

B-Cascade Table Delete Record

Delete user_info from user_info,user_info_a where User_info.userid=user_info_a.userid and User_info.recordid>user_ Info_a.mid;

C DROP TABLE User_info_a

2. Easy way to import Excel into MySQL (take a person's example here)

A pending Import data

B Post-construction data by table structure

The e column is the constructed SQL, which can then be copied directly into TXT and saved as a. sql file, which is imported into the library via source.

e-column notation: = "INSERT into User_info (recordid,companyid,department,app_sys_ids,userjid,userid,name,creator,createdate) VALUES (default, ' ZTE ', ' &trim ', ' A1052 ', ' & ' ', ' "'" ' "'" "@moaservice. zte.com.cn '," &C1052& "," & d1052& ", ' admin ', now ());"

Generated SQL, with the first example: INSERT INTO User_info (Recordid,companyid,department,app_sys_ids,userjid,userid,name,creator, CreateDate) VALUES (default, ' ZTE ', ' n ', ' + ', ' [email protected] ', ' 10009240 ', ' Yu Kun ', ' admin ', now ());

Iv. Comment Statements

MySQL supports 3 kinds of annotations, #,/**/,--, the last--must be followed by a space, otherwise invalid, this is different from Oracle, use should be noted.

How do i reset my password when I lose my root password?

Mysqld_safe--skip-grant-tables&

Mysql-u Root MySQL

mysql> UPDATE user SET Password=password ("New password") WHERE user= ' root '; mysql> FLUSH privileges;

Vi. How to install the ZIP package for MySQL under Windows?

After extracting the MySQL tarball, cmd to the bin directory of its extracted directory, run the following command: Mysqld-install, you can install MySQL as a Windows service. This service can then be set to self-boot, or manually started; Command line start/stop: net start mysql/net stop MySQL

MySQL Common Commands Collection