MySQL learning notes Common SQL commands

Remote access

Mysql-h localhost-uroot-p

Modify password

Mysqladmin-uroot-proot Password Admin

If the original password is empty

Mysqladmin-uroot Password Admin

Display version number, status, process information, etc.

Mysqladmin Version Status proc

Start a service

net start MySQL

Turn off services

Mysqladmin-uroot-proot shutdown

View version and Time

Select version (), current_date ();

View current Time

SELECT now ();

View System users

SELECT USER ();

You can also count arithmetic.

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

Use the following command to load the text file "MyData.txt" into the Test1 table:

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

Such as:

112hua
213daming
Tab separated between fields (Welcome to AC http://www.111cn.net)

Delete a null row in a table

Delete from test1 where ' is NULL;

Delete Only one row

Delete from test1 where age= ' limit 1

Modify Column name (field name)

ALTER TABLE TESTTB change name NewName char;

Backing up a database

Mysqldump-uroot-p Test>aa.sql

Restore Database

Mysql-uroot-p Test1<d:/aa.sql

Create a user that allows him to log in everywhere and operate on all databases

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

Create a user so that he can only log on on this computer and access the test database only

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

modifying delimiters

delimiter;

Create a Trigger

Create Trigger TG1

After delete on test1

For each row

Begin

Delete from infos where id=old.id;

end;;

Self-copying of tables

INSERT into message (sender,getter,m_content,m_date) Select the sender,getter,m_content,m_date from message;

Left JOIN Access Query

SELECT * from a left jion B on a.id=b.id;

1. Export the entire database

Mysqldump-u user name-p--default-character-set=latin1 Database name > exported file name (database default encoding is latin1)
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql

2. Export a table

Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_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 Database

A: Common source commands
Enter the MySQL database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)
Mysql>source Wcnc_db.sql

B: Use the mysqldump command

Mysqldump-u Username-p dbname < Filename.sql

C: Use MySQL command

Mysql-u Username-p-D dbname < Filename.sql

is not a manual, so can not do everything, these are my usual summary of some.