Database Operations Command: MySQL

Install MySQL First:
Sudo?apt-get?install?mysql-server?mysql-client?



1. Terminal starts Mysql:/etc/init.d/mysql start;(Stop, restart.) ）
2. Login Mysql:mysql-uroot-p (login with root account) and enter the password;
3. View all database names: show databases;
4. Select a database operation: use database_name;
5. View all the table names under the current database: show tables;
6. Creation of a database: create databases database_name;
7. Delete a database: Drop DB database_name;
8. Create a table: The CREATE TABLE mytest (UID bigint () NOT NULL, uname varchar (a) not null);
9. Delete a table: drop table mytest;
10.SQL INSERT statement: INSERT INTO table_name (COL1,COL2) values (value1,value2);
SQL UPDATE statement: UPDATE table_name set col1= ' value1 ', col2= ' value2 ' where where_definition;
SQL query statement: SELECT * FROM table_name where .... (the most complex statement)
SQL DELETE statement: DELETE from table_name where ...
14. Add a table structure field: Alert table table_name Add column field1 date, add column Field2 time ...
15. Delete the table structure field: Alert table table_name drop field1;
16. View the structure of the table: Show columns from table_name;
17.limit use: SELECT * FROM table_name limit 3;//only 3 rows per page
SELECT * FROM table_name limit 3,4//from the third start of the query results, four results are displayed.
This is a good place to use for paging.
18. Sort the results of the query: SELECT * FROM table_name ORDER by Field1,orderby Field2; multiple sorting
19. Exit Mysql:exit;

Refer to the MySQL official website for steps: https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/
1, download. deb package to local, for example: Download Mysql-apt-config_0.8.3-1_all.deb
wget Https://dev.mysql.com/get/mysql-apt-config_0.8.3-1_all.deb
2. Switch to the directory where the downloaded packages are located
3. Install the downloaded package using the sudo dpkg-i command, for example:
sudo dpkg-i mysql-apt-config_0.8.3-1_all.deb
Note: The installation information needs to be configured during the period, if default, the OK item in the option is selected directly
4. Update package Information
sudo apt-get update
5. Install MySQL
sudo apt-get install Mysql-server
Note: You need to enter the root user's password during the period
6. Check Service status
sudo service MySQL status

==================== Start/Stop mysql================
Start: sudo service MySQL start
Stop: sudo service mysql stop

======================mysql Command ===================
* Login: Mysql-u root-p < Enter >
Then enter the password
* View all database names: show databases;
* Select a database operation: use <database_name>;
* View all table names under the current database: show tables;
* Create a database: Crate <database_name>;
* Delete a database: drop databases <database_name>;
* View table structure: Desc <table_name>

* Page by page display results, similar to command line more command: Pager mor; MORE: http://blog.csdn.net/devin223/article/details/46455147

* Delete users: Drop user ' username ' @ ' host '; (It is best to use this command to remove users, and if you delete them directly by using delete from Mysql.user, there will be a lot of inconsistent data, this user exists in some tables, creating this user again or the actions associated with this user will cause a failure).

===================mysql SQL Operations =============
* Limit: can be used for pagination processing

=================== Remote Connection mysql================

1, log in MySQL server MySQL, create and a user for the client connection

* Grant all on *. * to ' mysql ' @ ' 192.168.61.130 ' identified by ' 123 ';

* Flush privileges;

2. Modify the MySQL configuration file:

* sudo vi/etc/mysql/mysql.conf.d/mysql.cnf

* Modify bind-address = 0.0.0.0

3. Restart MySQL

* Service MySQL Stop

* Service MySQL Start

4, the client to connect, test success.

Mysqldump-h host-u user name-P database name > exported database name. sql


Some of the more commonly used parameters are:
-P or--port to connect to the server port, if the MySQL port is not 3306, it is necessary to use this parameter
-D or--no-data no detailed data, only the structure of the exported data
--add-drop-table when creating a table, drop the already existing table with the same name [typically followed by the-D parameter]


The following is an example of a example database:
1. Export all data for the entire example database (including table structure, including data)

[SQL]View PlainCopy

1. Mysqldump-h 127.0.0.1-u root-p Example > Example.sql

2. Simply export the structure of the table

[SQL]View PlainCopy

1. Mysqldump-h 127.0.0.1-u root-p-D --add-drop-table example > Example.sql

Import, there are many ways, the most single is the source command, the first connection to the database. Then use source to import the file of the specified path.

Connect to MySQL:

[SQL]View PlainCopy

1. Mysql-u root-p

Create the database first, because there are no statements to create the database in the exported file, and if the database is already built, you do not have to create it.

[SQL]View PlainCopy

1. CREATE database example; (databases can be different names)

To switch databases:

[SQL]View PlainCopy

1. use example;

Import the specified SQL file:

[SQL]View PlainCopy

1. mysql>source/path/example.sql;

Database Operations Command: MySQL