MySQL Common operations

Go to MySQL Database

Mysql-uroot-plizheng123

1. Commands to view library files

show databases;

2. How to access the MySQL library file

Use MySQL

How to access the Discuz library file

Use Discuz

How to access the test library file

Use test

3. How to view Users

Select User ();

4. View the version information

Select version ();

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

5. View the table

Show tables;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

6. View Fields

Desc Pre_ucenter_vars;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

7. Create a new table

Show CREATE TABLE pre_ucenter_vars\g;

Show CREATE TABLE pre_forum_post\g;

8. Set permissions on the library file for the specified IP

% indicates all IP

Grant all on discuz.* to ' user1 ' @ ' 192.168.11.% ' identified by ' lizheng123 ';

9. Sometimes after setting permissions, the file does not have the zodiac, and you need to refresh permissions

Flush privileges;

10. View queues

Show Processlist;

11. View variables, all variables can be defined in/ETC/MY.CF

Show variables;

Methods of modifying variables directly in the data

Set global max_connections=200;

To view modified variable values

Show variables like ' max_connec% ';

13. View Status

Show status;

View the status of a parameter

Show status like '%buffer% ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

To view the error log for a database

The log directory is defined in/etc/init.d/mysqld.

Go to Catalog

Cd/data/www

View the error log (starting with the host name. err file)

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Instance

1. Create a new database

Create Datebase Lizheng;

2. Enter the database

Use Lizheng;

3. Create a new table

CREATE TABLE test1 (' id ' int (4), ' name ' char (+) ') Engine=myisam DEFAULT CHARSET=GBK;

4. Displaying information about a table

Show tables;

5. View the contents of a table

Desc test1;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

6. View the statement that created the table

Show CREATE TABLE test1\g;

7. Inserting data

INSERT into test1 values (1, ' Lizheng ');

8. View data

SELECT * from Test1;

9. Continue inserting data

INSERT into Test1 value (2, ' Zhang ');

INSERT into Test1 value (3, ' Wang ');

10. You can insert only one field

Insert INTO test1 (' id) values (4);

11. Can update data

Update test1 set id=5 where name= ' Wang ';

12. Delete the contents of a row of tables

Delete from test1 where name= ' Wang ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

13. Clear a table's data, you can specify a database in the specified table name

TRUNCATE TABLE lizheng.test1;

14. Delete a table

drop table test1;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

15. Delete a library file

Drop database Lizheng;

16. Fix a table

Repair table discuz.pre_forum_post;

MySQL Backup and recovery

Backing Up the database

mysqldump-uroot-plizheng123 discuz >/data/discuz.sql

You can edit this file

Recovering a Database

Mysql-uroot-plizheng123 Discuz </data/discuz.sql

A table under the backup database

mysqldump-uroot-plizheng123 discuz pre_forum_post>/data/post.sql

Restore a table

Mysql-uroot-plizheng123 Discuz </data/post.sql

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

MySQL Common operations