MySQL add-check, backup restore, authorization and other basic common commands

Add Create library, table

Check which tables are available, which libraries

Deleting a library Deletes a table

Change table Data

MySQL login mysql-uroot-p (with password)Most of the MySQL commands are with;

Add Create (da1 library name ta1 table name)

Creating a library Create database da1;

CREATE TABLE ta1 (' id ' int (4), ' name ' char (40));

ID/1th Field name int/type/Shaping 4 bits 2nd is the name type is a char string up to 40 (you can also specify Engine=myisam deeault CHARSET=GBK)

CREATE TABLE T1 (' id ' int (4), ' name ' char (+) ') Engine=myisam Deeault CHARSET=GBK; (To prevent garbled, it is best to specify a character set)

Check

See which libraries show databases;

View a table for a library use da1; Show tables;

View the table's fields desc ta1;

View Build Table statement Show create TABLE tb\g;

View database version select version ();

View MySQL status show status;

View MySQL queue show processlist;

Cat. Mysql_history view MySQL command history and save the command history before exiting after each exit

By deleting

Delete database drop databases da1;

Delete tables drop table da1.ta1;

Empty the entire table (clean up the data, the table is still there) TRUNCATE TABLE ta1;

Change

Inserting table Insert Ta2 (Id,name) VALUES (1, ' Zhangsan ')

Updata ta1 set id=3 where name= ' 2222 ';

The name in TAB1 is replaced by the ID of 2222 with 3 select * from TAB1 view

Update ta1 set name= ' AAA ' where id=1;

The ID in TAB1 is 1 and the name is replaced by the AAA select * from TAB1 view

Delete a row of data delete from ta1 where name= ' 1 ' select * from Tab1 view

Delete this line of name equals 1 in the TAB1 table

MySQL database backup

Backup Mysqldump-uroot-p da1 >1.sql

Recovery Mysql-uroot-p da1 <1.sql

Back up only one table Mysqldump-uroot-p da1 ta1 > 2.sql

Specify character set Mysqldump-uroot-p--default-character-set=utf8 da1 >1.sql when backing up

Restore also specifies charset mysql-uroot-p--default-character-set=utf8 da1 < 1.sql

Authorization = Create user and authorize

Grant all on * user1 identified by ' 123456 ';

on is followed by the specified library or the corresponding table

All represents all permissions, followed by the specified

User1 user by behind and password

SELECT * from Mysql.user where user= ' user1 ' \g;

View the fields of the user table of the MySQL library for users user1

Grant all on da1.* to ' user2 ' @ ' 192.168.239.140 ' identified by ' 123456 ';

' User2 ' @ ' 192.168.239.140 ' is assigned to the user user2, and the source ip192.168.239.140 is specified to use

Grant all on da1.* to ' User3 ' @ ' percent ' identified by ' 123456 ';

(% represents all hosts)

Change Password UPDATE mysql.user SET password=password ("Newpwd") WHERE user= ' username ';

Other commands

Which user is currently Select users ();

Current Library Select database ();

Query row Select COUNT (*) from

Select COUNT (*) from Mysql.user; query Mysql.user a few lines

Fix Repair table Last display OK fix succeeded

Example Repair table Pre_forum_attachtype; Fix Pre_forum_attachtype

This is with the help of Pre_forum_faq.frm and Pre_forum_faq under/DATA/MYSQL/DA1. MyD two specified files to restore Pre_forum_faq. MYI

This article is from the Linux system Learning extension blog, so be sure to keep this source http://zhangxiaoxiong.blog.51cto.com/11657691/1784953

MySQL add-check, backup restore, authorization and other basic common commands