Mysql notes-common SQL commands

Mysql applications often use commands such as data backup, permission management, mysql stop and start, and some common database operation commands. I will sort them out below.

Remote Access

Mysql-h localhost-uroot-p

Change Password

Mysqladmin-uroot-proot password admin

If the original password is empty

Mysqladmin-uroot password admin

Displays the version number, status, and process information.

Mysqladmin version status proc

Start the service

Net start mysql

Close service

Mysqladmin-uroot-proot shutdown

View version and time

Select version (), current_date ();

View Current Time

Select now ();

View system users

Select user ();

Arithmetic Operations

Select (25 + 6) as ad2d, (6*3) as xxx;

Use the following command to load the secret file named mydata.txt to the test1 table:

Load data local infile "c:/mydata.txt" into table test1;

For example:

Baihua
213 daming
Fields are separated by tabs (Welcome to exchange http://www.bKjia. c0m)

Delete NULL rows in a table

Delete from test1 where age is NULL;

Delete only one row

Delete from test1 where age = '12' limit 1

Modify column name (field name)

Alter table testtb change name newname char;

Back up database

Mysqldump-uroot-p test> aa. SQL

Restore database

Mysql-uroot-p test1 <d:/aa. SQL

Create a user so that he can log in to all locations and operate all databases.

Grant select, insert, update, delete on *. * to darex @ "%" identified by "root ";

Create a user so that he can only log on to the local machine and only access the test database.

Grant select, insert, update, delete on test. * to locdarex @ localhost identified by "root ";

Modify the delimiter

Delimiter;

Create a trigger

Create trigger tg1

After delete on test1

For each row

Begin

Delete from infos where id = old. id;

End ;;

Table self-replication

Insert into message (sender, getter, m_content, m_date) select sender, getter, m_content, m_date from message;

Left join query

Select * from a left jion B on a. id = B. id;

1. Export the entire database

Mysqldump-u username-p -- default-character-set = latin1 Database Name> exported file name (the default database encoding is latin1)
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL

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 add a drop table before each create statement

4. Import the database

A: 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 wcnc_db. SQL

B: Use the mysqldump command.

Mysqldump-u username-p dbname <filename. SQL

C: Use the mysql Command

Mysql-u username-p-D dbname <filename. SQL

It is not a manual, so it cannot be comprehensive. These are my summary.