MySQL basic common command _ MySQL

Source: Internet
Author: User
Tags mysql commands import database
Common MySQL commands: bitsCN.com 1. start and close

1.1 run the following command to start mysql in Linux:

  • A. rpm Package installation: service mysqld start
  • B. install the source code package:/usr/local/mysql/bin/mysqld_safe -- user = mysql &

1.2 command for restarting mysql in Linux:

  • A. rpm Package installation: service mysqld restart
  • B. install the source code package:
    • Disable mysql first

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

    • Start mysql/usr/local/mysql/bin/mysqld_safe -- user = mysql &

1.3 run the following command to disable mysql in Linux:

  • A. rpm Package installation: service mysqld stop
  • B. install the source code package:
    • Method 1:/usr/local/mysql/bin/mysqladmin-uroot-p shutdown
    • Method 2. killall mysqld // force terminate MySQL database service, which may cause table corruption. We do not recommend that you use

2. database connection

2.1 connect to MySQL

Format:$ Mysql_dir/bin/mysql [-h host address]-u username-p user password, press enter and prompt to enter the password.

2.2 exit MySQL

Format:Exit/quit

3. change the password

3.1 mysqladmin command

Format:Mysqladmin-u username-p old password new password

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

./Mysqladmin-uroot password '000000 ′

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

Example 2: change the root password to abc123.

./Mysqladmin-uroot-p123456 password abc123

3.2 UPDATE user statement

First, log on to mysql using the root account, and then execute:

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

3.3 set password statement

Similarly, first use the root account to log on to mysql, and then execute:

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

4. create a user and authorize the user

4.1 CREATE USER

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

Create user is used to CREATE a new MySQL account. To use create user, you must have the global create user permission for the mysql database or the INSERT permission. For each account, create user creates a new record in the mysql. user table without permission. If the account already exists, an error occurs.

You can use the self-selected identified by clause to specify a password for your account. The user value and password are given in the same way as the GRANT statement. Note that to specify a PASSWORD in plain text, ignore the PASSWORD keyword. To specify the PASSWORD as the mixed value returned by the PASSWORD () function, it must contain the keyword PASSWORD.

Example 1: Create 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 use the GRANT statement

The best way is to use the GRANT statement because it is more accurate and has fewer errors. GRANT is provided from MySQL 3.22.11. it is mainly used to GRANT permissions to accounts, but can also be used to create new accounts and GRANT permissions at the same time.

Note: When mysql runs on no_auto_create_user, the new user password must be provided. Otherwise, the new user cannot be created.

Format:

GRANT privileges ON databasename.tablename TO 'username'@'host' identified by 'password'; 

Example 1: add a user test1 with the password abc so that he can log on to any host and have all permissions on all databases.

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

grant all privileges on *.* to 'test1'@'%' identified  by 'abc';flush privileges;

Example 2: add a user named test2 with the password abc so that the user can only log on to localhost and query, insert, modify, and delete the database david.

grant select,insert,update,delete on david.* to 'test2'@'localhost' identified by 'abc';flush privileges;

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

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

For more information about grant, Google + Baidu.

4.3 directly operate the MySQL authorization table

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

Example 1: Create user test3 and grant it the same permissions as the test2 user in example 2.

mysql> insert into mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv) values('localhost', 'test3', PASSWORD('password'),'Y','Y','Y','Y');Query OK, 1 row affected, 3 warnings (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> 
mysql> select Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv from mysql.user where User='test3';+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+| Host      | User  | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv |+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+| localhost | test3 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | Y           | Y           | Y           | Y           | +-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+1 row in set (0.00 sec)mysql> 

When an account is created using INSERT, the reason for using flush privileges is to tell the server to re-read the authorization table. Otherwise, the change takes effect only after the server is restarted. If you use GRANT, you do not need to use flush privileges.

When an INSERT statement is used, the PASSWORD () function is used to encrypt the PASSWORD. The GRANT statement automatically encrypts the PASSWORD, so no PASSWORD () is required ().

'Y' value to enable account permissions.

V,Database operations

You must first log on to mysql. the related operations are performed at the mysql prompt, and each command ends with a semicolon.

5.1 create a database

Command: create database <数据库名> ;

Example 1: create a database named test

mysql> create database test;Query OK, 1 row affected (0.00 sec)mysql>

5.2 display all databases

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

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema | | david              | | mysql              | | test               | +--------------------+4 rows in set (0.00 sec)mysql> 

5.3 delete a database

Command: drop database <数据库名> ;

Example 2: Delete a database named test

mysql> drop database test;Query OK, 0 rows affected (0.00 sec)mysql> 

5.4 connect to the database

Command: use <数据库名> ;

Example 3: connect to the david database

mysql> use david;Database changedmysql> 

5.5 view the currently used database

Command: select database ();

mysql> select database();+------------+| database() |+------------+| david      | +------------+1 row in set (0.00 sec)mysql> 

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             | +-----------------+1 row in set (0.00 sec)mysql> 

VI. table operations

A database should be connected before the operation.

6.1 create a table

Create table
 <表名>
  
(
  <字段名1> 
   <类型1>
    
[,..
    <字段名n> 
     <类型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(20) not null,    -> sex int(4) not null default '0',    -> degree double(16,2));Query OK, 0 rows affected (0.04 sec)mysql> 

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

Create table tab_new like tab_old; (only table structure is supported)

Create table tab_new as select * from tab_old; (including both the table structure and table data)

Contains only the table structure:

mysql> create table myclass2 like myclass;Query OK, 0 rows affected (0.00 sec)mysql> 

Including both the table structure and 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 |   1 | 20130417.16 | +----+-------+-----+-------------+1 row in set (0.02 sec)mysql> 

6.2 obtain the table structure

Command:

Desc table name; orshow columns from table name;

Obtain the table structure of myclass & myclass2

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 delete a table

Command: drop table <表名> ;

For example, delete 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        | +-----------------+3 rows in set (0.00 sec)mysql> 

6.4 Change table name

Command: rename table original table name to new table name;

For example, change the name of myclass2 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        | +-----------------+3 rows in set (0.00 sec)mysql> 

6.5 add fields to the table

Command: alter table name, add, other field types;

For example, a passtest field is added to the myclass table. 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(20)     | NO   |     |         |                | | sex      | int(4)       | NO   |     | 0       |                | | degree   | double(16,2) | YES  |     | NULL    |                | | passtest | int(4)       | YES  |     | 0       |                | +----------+--------------+------+-----+---------+----------------+5 rows in set (0.00 sec)mysql> 

VII. data operations

7.1 Insert data

Command: insert <表名> [( <字段名1> [,.. <字段名n> ])] Values (value 1) [, (value n)];

For example, insert the following records into the myclass table. if it is left blank, the default value is used.

mysql> insert into myclass (id, name, sex, degree, passtest) values(1, 'david', 1, 80.56, 78);     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 query table data

A. query all rows

Command: select <字段1,字段2,...> From <table name> where <expression>;

Example 1: View all data in the myclass table

mysql> select * from myclass;+----+-------+-----+--------+----------+| id | name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | david |   1 |  80.56 |       78 | |  2 | sandy |   0 | 100.00 |       90 | |  3 | renee |   0 |  90.34 |        0 | |  4 | china |   0 |   NULL |        0 | +----+-------+-----+--------+----------+4 rows in set (0.00 sec)mysql> 

Example 2: query information about table david

mysql> select * from myclass where name='david';+----+-------+-----+--------+----------+| id | name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | david |   1 |  80.56 |       78 | +----+-------+-----+--------+----------+1 row in set (0.00 sec)mysql> 

B. query the first few rows of data

For example, view the first two rows of data in the myclass table.

mysql> select * from myclass limit 2;+----+-------+-----+--------+----------+| id | name  | sex | degree | passtest |+----+-------+-----+--------+----------+|  1 | david |   1 |  80.56 |       78 | |  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 |       78 | |  2 | sandy |   0 | 100.00 |       90 | +----+-------+-----+--------+----------+2 rows in set (0.01 sec)mysql> 

7.3 delete table data

Command: delete from table name where expression;

For example, delete a record numbered 4 in 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 |       78 | |  2 | sandy |   0 | 100.00 |       90 | |  3 | renee |   0 |  90.34 |        0 | +----+-------+-----+--------+----------+3 rows in set (0.00 sec)mysql> 

7.4 modify table data

Update table name set field = new value ,... Where condition;

For example, modify the record numbered 1 in the myclass table and 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 |       78 | |  2 | sandy |   0 | 100.00 |       90 | |  3 | renee |   0 |  90.34 |        0 | +----+-------+-----+--------+----------+3 rows in set (0.00 sec)mysql> 

8,Data import and export

8.1 export the entire database

Command: mysqldump-u username-p database name> exported file name

[root@TS-DEV bin]# ./mysqldump -uroot -p david > /tmp/david/david.sqlEnter password: [root@TS-DEV bin]# ll /tmp/david/total 4-rw-r--r-- 1 root root 2764 Apr 17 17:13 david.sql[root@TS-DEV bin]# 

8.2 export a table

Command: mysqldump-u user name-p database name table name> exported file name

[root@TS-DEV bin]# ./mysqldump -uroot -p david myclass > /tmp/david/david_myclass.sqlEnter password: [root@TS-DEV bin]# ll /tmp/david/total 8-rw-r--r-- 1 root root 1854 Apr 17 17:16 david_myclass.sql-rw-r--r-- 1 root root 2764 Apr 17 17:13 david.sql[root@TS-DEV bin]# 

8.3 export a database structure

Command: mysqldump-u root-p-d -- add-drop-table test> test_db. SQL

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

[root@TS-DEV bin]# ./mysqldump -uroot -p -d --add-drop-table david > /tmp/david/david_db.sqlEnter password: [root@TS-DEV bin]# 

8.4 import database

A. Common source commands

Go to the 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. use the mysql command

Create the database renee to be imported first.

mysql> create database renee;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | renee              | | sandy              | +--------------------+4 rows in set (0.00 sec)mysql> 

Import data

[root@TS-DEV bin]# ./mysql -uroot -p -D renee < /tmp/david/david_myclass.sql Enter password: [root@TS-DEV bin]#

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 |       78 | |  2 | sandy |   0 | 100.00 |       90 | |  3 | nancy |   0 |  90.34 |        0 | +----+-------+-----+--------+----------+3 rows in set (0.00 sec)mysql> 

For more import and export commands, Google + Baidu.

BitsCN.com
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.