Basic operation of MySQL database under Linux

1. Create database-related commands:
First, download the MySQL-related package: Aptitude install Mysql-server/mysql-client
The root user in MySQL is similar to the root user under Linux and has the highest privileges, which can be a serious hazard to the data if improperly manipulated. Therefore, for each application, create a
The corresponding database and operating user is a good habit.
Mysql-u root-p-U followed by user name,-p means password login required, first entry to MySQL with root user (enter the password of the root user account, this password is either during the installation process,
It is either obtained using the Mysqladmin tool. ）。 If it fails, it may be that the original password is not configured correctly and you can try to log in with the password ' root '. If you still have an error, refer to the following workaround:
Http://www.cnblogs.com/kerrycode/p/4368312.html
http://blog.csdn.net/yangxt/article/details/17200611
MySQL's related installation directory can be consulted:
Http://www.linuxidc.com/Linux/2014-10/108644.htm
The following command attempts to be available:
Mysqladmin-u root-p Password + ENTER (if previous password, it will be displayed after password)
Enter Password: Enter new password (not visible)
Mysql> status (\s)-lists current MySQL related status information
Mysql> SHOW DATABASES; -Show Database list
mysql> use db_name; -Select Database db_name
Mysql> SHOW TABLES; -Displays a list of tables under Db_name
mysql> CREATE DATABASE db_name; -Create a new database, of course, first you should log in as the root user, and the normal user does not have the right to create a database
Mysql> GRANT select,insert,delete,update on db_name to user_name identified
> by ' USER_PSD '; -Grant the new user user_name the specified permission for the database db_name, the login password user_psd. Permissions that the user has: Select,insert,delete,update
MySQL db_name-u user_name–p-Log in to the database with the newly created user user_name db_name
MySQL mytest-u root-p-Log in to the database with the root user db_name, when we need to create a new table for the database db_name, we need the root user's permission, as follows, the process of creating a new table:
Mysql> CREATE TABLE Employees (
-> Empid int not null,-the field column value is not empty
LastName varchar (30),
FirstName varchar (30),
-Salary float,
-PRIMARY KEY (Empid))-The field column value is unique, "primary key" means that the column is the primary key of the table, and MySQL automatically indexes the column
Mysql> SHOW COLUMNS from employees; -Display Employees field information (sometimes we need to know the structure of the table to determine the format of the inserted content)
Mysql> SHOW CREATE TABLE employees; -Displays the creation process of the table employees, as well as its internal structure
mysql> INSERT into Table VALUES (...); -Item table Insert a message such as:mysql> INSERT INTO Employees VALUES (1, ' Blum ', ' Rich ', 25000.00);
If:mysql> INSERT into employees VALUES (1, ' Blum ', ' Barbara ', 45000.00);
Then: ERROR 1062 (23000): Duplicate entry ' 1 ' for key 1
Mysql> DELETE from Employees WHERE Empid = 1; -delete the Empid = 1 message
Mysql> Delete FROM Employees-deletes all tables employees the specified database
Mysql> SELECT * FROM Employees; -Query all field contents of table employees, select is Query command
mysql> SELECT datafields from table; -Query the field list datafields the specified content (between fields with "," split). The three commonly used filter modifiers are as follows:
WHERE: Displays a subset of the data rows that meet certain criteria. such as:mysql> SELECT * FROM Employees WHERE salary > 40000;
ORDER BY: Displays the data rows in the specified order.
LIMIT: Displays only a subset of the data rows.
e.g.:
Mysql> SELECT * FROM Employees;
+-------+----------+-----------+--------+
| Empid | LastName | FirstName | Salary |
+-------+----------+-----------+--------+
| 0 | ER | ZHANG | 4500 |
| 1 | SAN | ZHANG | 5500 |
| 2 | SI | ZHANG | 6500 |
| 3 | WU | ZHANG | 7500 |
| 4 | LIU | ZHANG | 8500 |
| 5 | QI | ZHANG | 9500 |
+-------+----------+-----------+--------+
Mysql> SELECT LastName, salary from Employees WHERE salary > && salary < 8000;
+----------+--------+
| LastName | Salary |
+----------+--------+
| SAN | 5500 |
| SI | 6500 |
| WU | 7500 |
+----------+--------+
Mysql> SELECT * from Employees1 ORDER by LastName (Asc/decs); -Sort query table by field LastName EMPLOYEES1
Mysql> SELECT * from employees1 WHERE salary like ' 6% '; -Limit salary to start with the number 6, like words can be used instead of "="
Mysql> Exit/quit-Exiting the database software

2. Related actions after creating a new database:
Alter:
mysql> ALTER TABLE table_name ...; -After creating a table, sometimes we need to modify the underlying structure, using this as the command header
mysql> ALTER TABLE table_name RENAME (AS) table_new_name; -Table Rename
mysql> ALTER TABLE table_name ADD column list data type [after insertion position]; -Add a field information to the table
mysql> ALTER TABLE table_name Change column Name column new name new data type; -Specify column Rename
mysql> ALTER TABLE table_name DROP column name; -Delete the specified column
DROP:
mysql> DROP TABLE table_name; -Delete the specified table
mysql> DROP DATABASE database_name; -delete the specified database
Mysqladmin-u root-p drop database_name-also use the mysqladmin command to delete the specified database outside of the MySQL software
UPDATE:
+-------+----------+-----------+--------+
| Empid | LastName | FirstName | Salary |
+-------+----------+-----------+--------++-------+----------+-----------+--------+
| 5 | QI | ZHANG | 9500 | | 5 | QI | ZHANG | 9500 |
| 7 | BA | zahng | 9500 | -->| 6 | BA | zahng | 9500 |
+-------+----------+-----------+--------++-------+----------+-----------+--------+
Mysql> UPDATE employees1
SET Empid = 6
-WHERE LastName = ba;-Qualification

3. Data replication between database tables: http://www.jb51.net/article/47562.htm
Replication between tables in the same database:
INSERT (INTO) table1 select * from table2; -Full replication (MySQL test available)
INSERT (into) table1 SELECT DISTINCT * from table2; -No duplicate record (MySQL test not available)
INSERT (into) table1 select Top 5 * from table2; -First five records (MySQL test not available)
Cross-database Inter-table replication:
INSERT (in) (current.) Table1 SELECT * from Src_database.table2; -Full replication
INSERT (in) (current.) Table1 SELECT DISTINCT * from Src_database.table2; -Duplicate records are not duplicated
INSERT (in) (current.) Table1 SELECT Top 5 * from Src_database.table2; -First Five records
If Table1 does not exist, you should first create the table and make its structure identical to the src_database structure to copy:
CREATE TABLE table1 like (src_database.) table2; -+ (src_database.) Depends on whether the same database
INSERT table1 SELECT * FROM (src_database.) table2;

4. Several ways to rename a database: http://www.cnblogs.com/allenhua/p/5393189.html
Take method Four as an example (mysqldump Export data and import): MyTest-Mytest1
Mysqldump-u root-p mytest > Mytest_dump. Sql
Mysql-u root-p-E "CREATE DATABASE mytest1"
Mysql-u root-p Mytest1 < Mytest_dump. Sql
Mysql-u root-p-E "DROP DATABASE mytesst"

5. Database user Rights Related:
Mysql> GRANT All privileges the mytest1.* to [e-mail protected] identified by ' Test '; -Grant user test all permissions for database Mytest1
Mysql> GRANT SELECT, INSERT, DELETE,... On mytest1.* to [e-mail protected] identified by ' Test '; -Grant user test specified permissions
Mysql> DELETE from user WHERE user = ' test ';
mysql> FLUSH privileges;
Mysql> select * from user; -Check the rights of all users

6. Multi-Table query:
Mysql> SELECT Salary from Employees1
UNION (All)
SELECT salary from Employees2; -merge employees1 with employees2 Two tables salary results, no duplicates. + (All) full list of repeatable
+--------+
| Salary |
+--------+
| 4500 |
| 5500 |
| 6500 |
| 7500 |
| 8500 |
| 9500 |
| 10500 |
| 11500 |
+--------+
Three ways to join query: http://www.runoob.com/mysql/mysql-join.html
Mysql> SELECT * from TCOUNT_TBL;
+---------------+--------------+
| Runoob_author | Runoob_count |
+---------------+--------------+
| Beginner's Tutorial | 10 |
| runoob.com | 20 |
| Google | 22 |
+---------------+--------------+
Mysql> SELECT * from RUNOOB_TBL;
+-----------+---------------+---------------+-----------------+
| runoob_id | Runoob_title | Runoob_author | Submission_date |
+-----------+---------------+---------------+-----------------+
| 1 | Learn PHP | Beginner's Tutorial | 2017-04-12 |
| 2 | Learn MySQL | Beginner's Tutorial | 2017-04-12 |
| 3 | Learn Java | runoob.com | 2015-05-01 |
| 4 | Learn Python | runoob.com | 2016-03-06 |
| 5 | Learn C | FK | 2017-04-05 |
+-----------+---------------+---------------+-----------------+
INNER join (inner JOIN, or equivalent connection): Gets the record of the field matching relationship in two tables
Mysql> SELECT a.runoob_id, A.runoob_author, B.runoob_count from Runoob_tbl a
-INNER JOIN tcount_tbl b on a.runoob_author = B.runoob_author;
Equivalent to:
Mysql> SELECT a.runoob_id, A.runoob_author, B.runoob_count from Runoob_tbl A, tcount_tbl b
WHERE a.runoob_author = B.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | A.runoob_author | B.runoob_count |
+-------------+-----------------+----------------+
-->| 1 | Beginner's Tutorial | 10 |
-->| 2 | Beginner's Tutorial | 10 |
| 3 | runoob.com | 20 |
| 4 | runoob.com | 20 |
+-------------+-----------------+----------------+
Left join: Gets all records of the left table, even if the right table does not have a matching record
Mysql> SELECT a.runoob_id, A.runoob_author, B.runoob_count from Runoob_tbl a
-Left JOIN tcount_tbl b on a.runoob_author = B.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | A.runoob_author | B.runoob_count |
+-------------+-----------------+----------------+
-->| 1 | Beginner's Tutorial | 10 |
-->| 2 | Beginner's Tutorial | 10 |
| 3 | runoob.com | 20 |
| 4 | runoob.com | 20 |
| 5 | FK | NULL |
+-------------+-----------------+----------------+
Right join: Opposite to the left join, used to get all records of the right table, even if there are no matching records
Mysql> SELECT a.runoob_id, A.runoob_author, B.runoob_count from Runoob_tbl a
Right JOIN tcount_tbl b on a.runoob_author = B.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | A.runoob_author | B.runoob_count |
+-------------+-----------------+----------------+
-->| 1 | Beginner's Tutorial | 10 |
-->| 2 | Beginner's Tutorial | 10 |
| 3 | runoob.com | 20 |
| 4 | runoob.com | 20 |
| NULL | NULL | 22 |
+-------------+-----------------+----------------+

Basic operation of MySQL database under Linux