Basic MySQL database operations in Linux, linuxmysql

Basic MySQL database operations in Linux, linuxmysql

1. database creation commands:
First, download the MySQL software package: aptitude install mysql-server/mysql-client
The root user in MySQL is similar to the root user in Linux and has the highest permissions. Improper Operations may cause serious damage to Data. Therefore
It is a good habit to operate databases and users.
Mysql-u root-p + press ENTER-u followed by the user name.-p indicates that a password is required to log on to mysql. For the first time, enter the password of the root user account (enter the password of the root user account, this password is either in the installation process,
It is obtained by using mysqladmin .). If the logon fails, the reason may be that the original password is incorrectly configured. You can use the 'root' password to log on. If an error is still reported, see the following solution:
Http://www.cnblogs.com/kerrycode/p/4368312.html
Http://blog.csdn.net/yangxt/article/details/17200611
For the installation directory of mysql, refer:
Http://www.linuxidc.com/Linux/2014-10/108644.htm
The following command is available:
Mysqladmin-u root-p password + press enter (if there is a password before, it will be displayed after the password)
Enter password: Enter the new password (invisible)
Mysql> STATUS (\ s)-list information about the current mysql STATUS
Mysql> show databases;-displays the Database List
Mysql> USE DB_name;-select database DB_name
Mysql> show tables;-display the TABLES List under DB_name
Mysql> create database DB_name;-CREATE a new DATABASE. Of course, you should first log on as the root user. Normal users do 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 database DB_name. The logon password is user_psd. Permissions of this user: SELECT, INSERT, DELETE, UPDATE
Mysql DB_name-u user_name-p-use the newly created user_name to log on to the database DB_name
Mysql mytest-u root-p-use the root user to log on to the database DB_name. When we need to create a new table for the database DB_name, the root user's permissions are required, as follows:
Mysql> create table employees (
-> Empid int not null,-the column value of this field is not empty
-> Lastname varchar (30 ),
-> Firstname varchar (30 ),
-> Salary float,
-> Primary key (empid);-The column value of this field is unique. "primary key" indicates that this column is the primary key of the table. MySQL will automatically index this column.
Mysql> show columns from employees;-displays information about each field of employees (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. You can also view its internal structure.
Mysql> insert into table VALUES (...); -INSERT an item table, for example, 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 information of empid = 1
Mysql> delete from employees;-DELETE all tables in the specified database employees
Mysql> SELECT * FROM employees;-query all fields in the table employees. SELECT is the query command.
Mysql> SELECT datafields FROM table;-query the content specified by datafields in the field list (separate fields ). The following are three commonly used filter modifiers:
WHERE: displays a subset of data rows that meet specific conditions. For example, mysql> SELECT * FROM employees WHERE salary> 40000;
Order by: displays data rows in the specified ORDER.
LIMIT: displays only one subset of 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> 5000 & salary <8000;
+ ---------- + -------- +
| Lastname | salary |
+ ---------- + -------- +
| SAN | 1, 5500 |
| Silicon | 6500 |
| WU | 1, 7500 |
+ ---------- + -------- +
Mysql> SELECT * FROM employees1 order by lastname (ASC/DECS);-query the table employees1 BY lastname
Mysql> SELECT * FROM employees1 WHERE salary LIKE '6% ';-limit that salary is an item starting with number 6. The LIKE clause can be used instead of "= ".
Mysql> exit/quit-exit database software

2. Related operations after the database is created:
ALTER:
Mysql> alter table table_name...;-after creating a TABLE, we sometimes need to modify the structure of the object and use this as the command header.
Mysql> alter table table_name RENAME (AS) table_new_name;-TABLE RENAME
Mysql> alter table table_name ADD column Name Data Type [AFTER Insert Location];-ADD a field in 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 a specified Column
DROP:
Mysql> drop table table_name;-delete a specified TABLE
Mysql> drop database database_name;-delete a specified DATABASE
Mysqladmin-u root-p drop database_name;-you can also use the mysqladmin command to delete a specified database outside mysql.
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;-Conditions

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 is available)
INSERT (INTO) table1 select distinct * from table2;-duplicate records are not copied (mysql test is unavailable)
INSERT (INTO) table1 select top 5 * from table2;-first five records (mysql test unavailable)
Cross-database table replication:
INSERT (INTO) (current.) table1 select * from src_database.table2;-completely copy
INSERT (INTO) (current.) table1 select distinct * from src_database.table2;-duplicate records are not copied
INSERT (INTO) (current.) table1 select top 5 * from src_database.table2;-first five records
If table 1 does not exist, you should first create a table and make it have the same structure as the src_database before copying:
Create table table1 LIKE (src_database.) table2;-+ (src_database.) depends on whether the database is in the same
INSERT table1 SELECT * FROM (src_database.) table2;

4. Several Methods of database rename: http://www.cnblogs.com/allenhua/p/5393189.html
Take Method 4 as an example (mysqldump exports data and then imports it): 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 Permissions:
Mysql> grant all privileges on mytest1. * TO test @ localhost identified by 'test';-GRANT the user test ALL permissions ON the database mytest1.
Mysql> grant select, INSERT, DELETE,... ON mytest1. * TO test @ localhost identified by 'test';-GRANT the user the specified permission for test.
Mysql> delete from user WHERE user = 'test';-DELETE user test
Mysql> flush privileges;-Refresh User Permissions
Mysql> select * from user;-query the permissions of all users

6. Multi-Table query:
Mysql> SELECT salary FROM employees1
-> UNION (ALL)
-> SELECT salary FROM employees2;-merge the salary results of table employees1 and table employees2 without duplicates. + (ALL) the list can be repeated.
+ -------- +
| Salary |
+ -------- +
| 1, 4500 |
| 1, 5500 |
| 1, 6500 |
| 1, 7500 |
| 1, 8500 |
| 1, 9500 |
| 1, 10500 |
| 1, 11500 |
+ -------- +
Three JOIN query methods: http://www.runoob.com/mysql/mysql-join.html

The database structure of the two instances is as follows:
Mysql> SELECT * FROM tcount_tbl;
+ --------------- + -------------- +
| Runoob_author | runoob_count |
+ --------------- + -------------- +
| Cainiao tutorial | 10 |
| RUNOOB. COM | 20 |
| Google | 22 |
+ --------------- + -------------- +
Mysql> SELECT * from runoob_tbl;
+ ----------- + --------------- + ----------------- +
| Runoob_id | runoob_title | runoob_author | submission_date |
+ ----------- + --------------- + ----------------- +
| 1 | learning PHP | cainiao tutorial |
| 2 | learning MySQL | cainiao tutorial |
| 3 | learning Java | RUNOOB. COM |
| 4 | learn Python | RUNOOB. COM | 2016-03-06 |
| 5 | learning C | FK |
+ ----------- + --------------- + ----------------- +
Inner join (internal JOIN, or equivalent JOIN): obtains records of field matching relationships in two tables.

　　　　　　　　　　　　　　　　
Mysql> SELECT a. runoob_id, a. runoob_author, B. runoob_count FROM runoob_tbl
-> Inner join tcount_tbl B ON a. runoob_author = B. runoob_author;
It is equivalent:
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 | cainiao tutorial | 10 |
--> | 2 | cainiao tutorial | 10 |
| 3 | RUNOOB. COM | 20 |
| 4 | RUNOOB. COM | 20 |
+ ------------- + ----------------- + ---------------- +
Left join: obtains all records in the LEFT table even if no matching records exist in the right table.

　　　　　　　　　　　　　　　　
Mysql> SELECT a. runoob_id, a. runoob_author, B. runoob_count FROM runoob_tbl
-> Left join tcount_tbl B ON a. runoob_author = B. runoob_author;
+ ------------- + ----------------- + ---------------- +
| A. runoob_id | a. runoob_author | B. runoob_count |
+ ------------- + ----------------- + ---------------- +
--> | 1 | cainiao tutorial | 10 |
--> | 2 | cainiao tutorial | 10 |
| 3 | RUNOOB. COM | 20 |
| 4 | RUNOOB. COM | 20 |
| 5 | FK | NULL |
+ ------------- + ----------------- + ---------------- +
Right join: opposite to left join, which is used to obtain all records in the RIGHT table even if no matching records exist in the LEFT table.

　　　　　　　　　　　　　　　　
Mysql> SELECT a. runoob_id, a. runoob_author, B. runoob_count FROM runoob_tbl
-> Right join tcount_tbl B ON a. runoob_author = B. runoob_author;
+ ------------- + ----------------- + ---------------- +
| A. runoob_id | a. runoob_author | B. runoob_count |
+ ------------- + ----------------- + ---------------- +
--> | 1 | cainiao tutorial | 10 |
--> | 2 | cainiao tutorial | 10 |
| 3 | RUNOOB. COM | 20 |
| 4 | RUNOOB. COM | 20 |
| NULL | 22 |
+ ------------- + ----------------- + ---------------- +