MySQL usage notes are constantly updated

Start
Mysqld

Login
Mysql-u username-P Password

Stop
Mysqladmin-u username Shutdown

View Current Character Set settings
Show variables like 'character/_ set/_ % ';

Set Character Set
Set character_set_results = GBK;
Set the character set of client, connection, and results at one time
Set names 'ust8'
Set names should be set to the character set of the command line, instead of the database

 

 

View the table's common script Structure
Show create table table_name;

 

View table structure

Desc table_name

 

Modify the character set of a table
Alter table tablename convert to Character Set utf8;

 

Backup and recovery database

1. Export the entire database

Mysqldump-u username-P Database Name> exported file name

Mysqldump-u wcnc-p -- dufalut-character-set = GBK smgp_rj_wcnc> wcnc. SQL

It is best to add character sets when exporting, especially when exporting using command lines.

2. Export a table

Mysqldump-u user name-P database name Table Name> exported file name

Mysqldump-u wcnc-P smgp_rj_wcnc users> wcnc_users. SQL

3. Export a database structure

Mysqldump-u wcnc-p-d -- add-drop-Table smgp_apps_wcnc> D: wcnc_db. SQL

-D no data -- add-drop-table adds a drop table before each create statement.

4. Export stored procedures and functions

Mysqldump-u wcnc-p-r smgp_rj_wcnc> wcnc. SQL

-R: Export stored procedures and functions

5. Import the database

Common source commands

Go to the MySQL Database Console,

For example, MySQL-u root-P

Mysql> Use Database

Then run the source command. The following parameter is the script file (for example,. SQL used here)

Mysql> source D: wcnc_db. SQL

How to modify MySQL Character Set

Modifying the default Character Set of MySQL is implemented by modifying its configuration file. Generally, there are two situations:

Windows Platform

The MySQL configuration file in Windows is my. ini, which is usually in C:/Windows/My. ini or C:/winnt/My. ini.

Default-character-set = GBK # Or gb2312, big5, utf8

Then restart MySQL

Service MySQL restart

Or

/Etc/init. d/MySQL restart

Or use another method to restart the instance.

UNIX platform

The MySQL configuration file in Linux is my. CNF, which is usually/etc/My. CNF. If you cannot find it, run the find command to find it:

Find/-INAME my. CNF add

Default-character-set = GBK # Or gb2312, big5, utf8

Then restart MySQL

Net stop MySQL
Net start MySQL

It takes effect.

Table engine:
The default MySQL table engine is MyISAM, which does not support transactions and only stores data. It has the advantage of high speed.
InnoDB is not mysql fast, but it supports transactions.

 

Create a user to assign Permissions

Add: mysql> grant all on DB. *
'Username' @ localhost (IP) identified by 'Password ';
Refresh the permission table mysql> flush privileges;
Delete: mysql> use MySQL;
Mysql> Delete from user where username = 'username ';
Modify: mysql> use MySQL;
Mysql> Update user set username = 'username', password = PASSWORD ('Password ');...
After modification, execute the command;
Flush privileges;
Refresh permission

Configure MYSQL:

1. Find a configuration file of huge. ini in the folder, open it, and add two rows in the [mysqld] field: basedir = C:/mysql6

Datadir = C:/mysql6/Data

Save it as my. ini file and put it in the C:/Windows directory.

2. open cmd, enter the bin directory of the folder, that is, C:/mysql6/bin, enter mysqld-NT-install, and load the MySQL service. The system will prompt that the installation is successful, enter Net start MySQL to start the MySQL service.

3. Enter mysql-uroot-P in the bin directory and press enter to enter the password. If the initial password is empty, press enter to enter the MySQL welcome page.

The initial configuration has ended.

If you want to exit the service, first enter net stop MySQL on the terminal, and then enter mysqld-NT-Remove in the bin directory to completely terminate the MySQL service.

The new version of MySQL to load Windows Services is:

Mysqld -- install MySQL -- defaults-file = D:/MySQL/My. ini
MySQL is the service name

Uninstall Service

Mysqld-Remove

 

 

 

Generate UUID

Select UUID () from tablename;

 

View connections

Show processlist;

 

View database Parameters

Show variables;

Define the block so that the ";" error will not be reported when writing the stored procedure in the command line.

Delimiter //

......

//

Delete table data without transactions

Truncate table table_name