MySQL Common basic commands

first, start and close

1.1 Linux command to start MySQL:

• A. RPM Package Installation: Service mysqld start

• B. Source package Installation:/usr/local/mysql/bin/mysqld_safe--user=mysql &

1.2 Linux to restart MySQL command:

• A. RPM Package Installation: Service mysqld restart
• B. Source Package Installation:
  • Turn off MySQL first

    /usr/local/mysql/bin/mysqladmin-uroot-p shutdown

  • Re-start Mysql/usr/local/mysql/bin/mysqld_safe--user=mysql &

1.3 Linux shut down the mysql command:

• A. RPM Package Installation: Service mysqld stop
• B. Source Package Installation:
• Method 1,/usr/local/mysql/bin/mysqladmin-uroot-p shutdown
• Method 2, Killall mysqld//forcibly terminating MySQL database service, may cause table corruption, not recommended

Second, the database connection

2.1 Connecting MySQL

format:$mysql _dir/bin/mysql [-H host address]-u user name-P user password, enter the password prompt.

2.2 Exiting MySQL

format:exit/quit

Third, change the password

3.1 mysqladmin Command

format:mysqladmin-u username-p Old password password new password

Example 1: Add a password to root 123456. First enter the directory $mysql_dir/bin under Terminal, and then type the following command

./mysqladmin-uroot Password ' 123456′

Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

Example 2: Then change the root password to abc123.

./mysqladmin-uroot-p123456 Password abc123

3.2 UPDATE User statement

First log in to MySQL with the root account and then execute:

UPDATE mysql.user SET password=password (' 123456 ') WHERE user= ' root '; FLUSH privileges;

3.3 SET PASSWORD Statement

Again, first log in to MySQL with the root account and then execute:

SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' abc123 ');

Iv. creating users and authorizations

4.1 CREATE USER

CREATE user User [identified by [PASSWORD] ' PASSWORD ']    [, user [identified by [PASSWORD] ' PASSWORD ']] ...

The create user is used for creating a new MySQL account. To use the Create user, you must have the global Create user permission for the MySQL database or have the INSERT permission. For each account, create user creates a new record in the Mysql.user table with no permissions. If the account already exists, an error occurs.

Using the optional identified by clause, you can specify a password for the account. The user value and password are given the same method as the GRANT statement. In particular, to specify a password in plain text, you need to ignore the password keyword. To specify the password as a mixed value returned by the password () function, you need to include the keyword password.

Example 1: Creating a new user David & Sandy

mysql> create user ' david ' @ ' localhost ' identified by ' password '; Query OK, 0 rows Affected (0.00 sec) mysql>

mysql> create user ' Sandy ' @ ' localhost ' identified by PASSWORD ' *2470c0c06dee42fd1618bb99005adca2ec9d1e19 '; Query OK, 0 rows Affected (0.00 sec) mysql>

4.2 Using the GRANT statement

The best approach is to use the GRANT statement because it is more accurate and less error-wise. Grant was provided from MySQL 3.22.11 and its primary purpose is to authorize the account, but it can also be used to establish a new account and authorize it at the same time.

Note: You will not be able to create a new user when MySQL is running on No_auto_create_user to provide a new user's password.

Format:

Example 1, add a user test1, password is ABC, so that he can log on any host, and all the database has all permissions.

First, use the root user to connect to MySQL, and then type the following command:

Grant all privileges on * * to ' test1 ' @ ' percent ' identified by  ' ABC '; flush privileges;

Example 2, add a user test2, the password is ABC, so that he can only login on localhost, and the database David can query, insert, modify, delete operations.

Grant Select,insert,update,delete on david.* to ' test2 ' @ ' localhost ' identified by ' ABC '; flush privileges;

If you do not want to test2 have a password, you can call another command to erase the password.

Grant Select,insert,update,delete on david.* to ' test2 ' @ ' localhost ' identified by ';

Grant more usage, please google+baidu yourself.

4.3 Direct operation MySQL Authorization form

In addition to grant, you can create the same account directly with the INSERT statement, and then use flush privileges to tell the server to overload the authorization table.

Example 1: Create a user test3 with the same permissions as the Test2 user in 4.2 cases 2.

Mysql> Select Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv from Mysql.user where user= ' test 3 ', +-----------+-------+-------------------------------------------+-------------+-------------+-------------+- ------------+| Host      | User  | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv |+-----------+-------+-------------------------------------------+-------------+-------------+----- --------+-------------+| localhost | Test3 | *2470c0c06dee42fd1618bb99005adca2ec9d1e19 | Y           | Y           | Y           | Y           

When you create an account with insert, the reason for using flush privileges is to tell the server to reread the authorization table. Otherwise, the changes will not take effect until you restart the server. With GRANT, you do not need to use flush privileges.

When using the INSERT statement, the password () function is used to encrypt the password. The GRANT statement automatically encrypts the password, so password () is not required.

The ' Y ' value enables account permissions.

Five, Library Operations

You must log in to MySQL first, and the operation is performed at the prompt of MySQL, and each command ends with a semicolon.

5.1 Creating a Database

Command: Create databases < database name >;

Example 1: Creating a database named Test

mysql> CREATE DATABASE test; Query OK, 1 row Affected (0.00 sec) mysql>

5.2 Show All databases

Command: show databases; (note: There is a last s)

Mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | David              | | mysql              | | test               

5.3 Deleting a database

Command: Drop databases < database name >;

Example 2: Deleting a database named Test

5.4 Connecting the database

Command: Use < database name >;

Example 3: Connecting the David Database

5.5 Viewing the currently used database

Command: Select database ();

Mysql> Select Database (); +------------+| Database () |+------------+| David      

5.6 table information contained in the current database

Command: Show tables; (note: There is a last s)

Mysql> Show tables;+-----------------+| Tables_in_david |+-----------------+| Emp             

Vi. operation of the table

You should connect to a database before the operation.

6.1 Creating a Table

CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]); CREATE TABLE tablename (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..);

Mysql> CREATE TABLE MyClass (-    ID int (4) NOT null primary key auto_increment,    name Char (a) NOT NULL ,    sex int (4) NOT null default ' 0 ',    

Add: Create a new table based on an existing table.

CREATE table tab_new like Tab_old; (table structure only)

CREATE TABLE Tab_new as SELECT * from Tab_old; (Both table structure and table data included)

Contains only the table structure:

Contains both the table structure and the table data:

Mysql> INSERT into MyClass values (1, ' David ', 1, 20130417.16); Query OK, 1 row affected (0.02 sec) mysql> mysql> mysql> CREATE TABLE MYCLASS3 as SELECT * from MyClass; Query OK, 1 row affected (0.07 sec) records:1  duplicates:0  warnings:0mysql> select * FROM myclass3;+----+---- ---+-----+-------------+| ID | Name  | sex | degree      |+----+-------+-----+-------------+|  1 | David |   

6.2 Getting the table structure

Command:

DESC table name; orshow columns from table name;

Get MyClass & MYCLASS2 table structure

Mysql> desc myclass;+--------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+----------------+| ID | Int (4) | NO | PRI | NULL | auto_increment | | name | char (20) |     NO |         |                | | | sex | Int (4) |     NO | |                0 | | | degree | Double (16,2) |     YES | |                NULL | | +--------+--------------+------+-----+---------+----------------+4 rows in Set (0.00 sec) mysql> Show columns from myclass2;+--------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+----------------+| ID | Int (4) | NO | PRI | NULL | auto_increment | | name | char (20) |     NO |         |                | | | sex | Int (4) |     NO | |                0 | | | degree | Double (16,2) |     YES | |                NULL | |  +--------+--------------+------+-----+---------+----------------+4 rows in Set (0.00 sec) mysql>

6.3 Deleting a table

Command: DROP table < table name >;

Example: Deleting a table with a table named MYCLASS3

mysql> drop table MYCLASS3; Query OK, 0 rows Affected (0.00 sec) mysql> Show tables;+-----------------+| Tables_in_david |+-----------------+| EMP             | | MyClass         | | myclass2        

6.4 Changing table names

Command: Rename table name to new table name;

Example: Change a table myclass2 name to MYCLASS4

Mysql> Rename table Myclass2 to MYCLASS4; Query OK, 0 rows affected (0.02 sec) mysql> Show tables;+-----------------+| Tables_in_david |+-----------------+| EMP             | | MyClass         | | myclass4        

6.5 Adding fields to a table

Command: ALTER TABLE name add field type other;

Example: In table MyClass, a field passtest is added, the type is int (4), and the default value is 0.

Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '; Query OK, 1 row affected (0.04 sec) records:1  duplicates:0  warnings:0mysql> desc myclass;+----------+------- -------+------+-----+---------+----------------+| Field    | Type         | Null | Key | Default | Extra          |+----------+--------------+------+-----+---------+----------------+| ID       | int (4)       | NO   | PRI | NULL    | auto_increment | | name     | char |     NO | | | |                | sex      | int (4)       | NO   |     | 0       |                | | degree   | double (16,2) | YES  |     | NULL    |                | | passtest | INT (4)       | YES  |     | 0       |                

Vii. Data manipulation

7.1 Inserting data

Command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)];

Example: Insert the following record into the MyClass table, leaving the default value blank for use.

Mysql> INSERT into MyClass (ID, name, sex, degree, passtest) VALUES (1, ' David ', 1, 80.56, +);     Query OK, 1 row Affected (0.00 sec) mysql> insert into MyClass values (2, ' Sandy ', 0, 100, 90); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into MyClass (ID, name, sex, Degree) VALUES (3, ' Renee ', 0, 90.34); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into MyClass (ID, Name) VALUES (4, ' China '); Query OK, 1 row Affected (0.00 sec) mysql>

7.2 Querying the data in a table

A. Querying all rows

Command: Select < Field 1, field 2,...> from < table name > where < expression >;

Example 1: Viewing all data in a table MyClass

Mysql> SELECT * from myclass;+----+-------+-----+--------+----------+| ID | Name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | David |   1 |  80.56 |       | |  2 | Sandy |   0 | 100.00 |       | |  3 | Renee |   0 |  90.34 |        0 | |  4 | China |   0 |   NULL |        

Example 2: Querying a table David related information

Mysql> SELECT * from MyClass where name= ' David ', +----+-------+-----+--------+----------+| ID | Name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | David |   1 |  80.56 |       

B. Querying the first few rows of data

Example: Viewing the first 2 rows of data in a table MyClass

Mysql> select * from MyClass limit 2;+----+-------+-----+--------+----------+| ID | Name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | David |   1 |  80.56 |       | |  2 | Sandy |   0 | 100.00 |       90 | +----+-------+-----+--------+----------+2 rows in Set (0.00 sec) mysql>

Or:

Mysql> SELECT * from MyClass ORDER by ID limit 2;+----+-------+-----+--------+----------+| ID | Name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | David |   1 |  80.56 |       | |  2 | Sandy |   0 | 100.00 |       

7.3 Deleting data from a table

Command: Delete from table name where expression;

Example: Deleting a record with number 4 in table MyClass

Mysql> Delete from MyClass where id=4; Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from myclass;+----+-------+-----+--------+----------+| ID | Name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | David |   1 |  80.56 |       | |  2 | Sandy |   0 | 100.00 |       | |  3 | Renee |   0 |  90.34 |        

7.4 Modifying data in a table

Update table name Set field = new value,... where condition;

Example: Modifying a record numbered 1 in the MyClass table to change the degree value to 89.99

mysql> update MyClass set degree=89.99 where id=1; Query OK, 1 row Affected (0.00 sec) Rows matched:1  changed:1  warnings:0mysql> select * from myclass;+----+--- ----+-----+--------+----------+| ID | Name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | David |   1 |  89.99 |       | |  2 | Sandy |   0 | 100.00 |       | |  3 | Renee |   0 |  90.34 |        

Eight, Import and export of data

8.1 Exporting the entire database

Command: Mysqldump-u user name-p database name > exported file name

8.2 Exporting a table

Command: Mysqldump-u user name-P database name Table name > exported file name

8.3 Exporting a database structure

Command: Mysqldump-u root-p-D--add-drop-table Test > Test_db.sql

-D No data--add-drop-table add a drop table before each CREATE statement

8.4 Importing a database

A. Common source commands

Access MySQL Database console

#./mysql-uroot-p

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | +--------------------+2 rows in Set (0.00 sec) mysql> CREATE database Sandy; Query OK, 1 row Affected (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Sandy | +--------------------+3 rows in Set (0.00 sec) mysql> use sandydatabase changedmysql> Source/tmp/david/ David.sqlquery OK, 0 rows Affected (0.00 sec) ... Query OK, 2 rows Affected (0.00 sec) Records:2 duplicates:0 warnings:0...query OK, 3 Rows Affected (0.00 sec) Records: 3 duplicates:0 warnings:0...query OK, 0 rows Affected (0.00 sec) mysql> Show tables;+-----------------+| Tables_in_sandy |+-----------------+| EMP | | MyClass | | MYCLASS4 | +-----------------+3 rows in Set (0.00 sec) mysql> SELECT * from emp;+------+-------+| ID | Name |+------+-------+| 1 |    David | | 2 | Sandy |  +------+-------+2 rows in Set (0.00 sec) mysql>

B. Using the MySQL command

Create the database Renee you want to import first.

mysql> CREATE DATABASE Renee; Query OK, 1 row Affected (0.00 sec) mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | | mysql              | | Renee              | | | sandy              

Import data

[Email protected] bin]#/mysql-uroot-p-D Renee </tmp/david/david_myclass.sql Enter password: [[email protected] B in]#

View data

mysql> use Renee;database changedmysql> show tables;+-----------------+| Tables_in_renee |+-----------------+| MyClass         | +-----------------+1 row in Set (0.00 sec) mysql> SELECT * from myclass;+----+-------+-----+--------+--- -------+| ID | Name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | David |   1 |  89.99 |       | |  2 | Sandy |   0 | 100.00 |       | |  3 | Nancy |   0 |  90.34 |        

MySQL Common basic commands