MySQL is an open-source relational database management system (RDBMS). Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.
Environment
I will operate the MySQL Server 5.7 in Ubuntu 16.04.
Linux Commands
Install MySQL client:
|
1 |
sudo apt-get install mysql-client |
Connect the remote MySQL cluster:
|
1 |
mysql -u root -p -h 13.12.79.55 -P 3306 |
For security, you aren’t allowed to enter the password with the parameter -p.
MySQL Commands
The SQL in MySQL is case insensitive.
Check the status of MySQL Server:
|
1 |
status; |
Create a database:
|
1 |
create database yhiblog; |
View all database:
|
1 |
show databases; |
Delete a database:
|
1 |
drop database test; |
Change the using database:
|
1 |
use yhiblog; |
View the using database:
|
1 |
SELECT DATABASE(); |
Show all tables of one database:
|
1 |
show tables from yhiblog; |
Create a table with at least one column:
|
1 2 3 4 5 |
create table user( UserID int, Name varchar(255), Mobile varchar(255) ); |
Show the status of table:
|
1 |
explain yhiblog.user; |
Add a column to table:
|
1 2 |
alter table user add address varchar(255); |
Change the data type of column:
|
1 |
alter table user modify column Address varchar(200); |
Change the column name and data type:
|
1 |
ALTER TABLE user CHANGE Mobile Phone varchar(200); |
Add a row to table:
|
1 |
INSERT INTO user (Name, Phone) VALUES ('yhi', '236668888'); |
Select the rows with specific fields from a table:
|
1 |
select * from user; |
Change the specific field of rows that fulfill the condition that the value is null:
|
1 2 3 |
update user set name='yhiblog' where name is null; |
Delete records in a table:
|
1 2 |
delete from user where phone='2343565456'; |
Using the wildcard pattern to select the rows from table:
|
1 |
select * from user where address like '%r'; |
% symbol matches any length of any character.
_ symbol matches one character.
Select the rows contain one of the values(1 & 5):
|
1 |
select * from user where userid in (1 ,5); |
Select the rows whose the value of specific field is inside the range [0-10]:
|
1 |
select * from user where userid between 0 and 10; |
Show the warning of the last command:
|
1 |
SHOW WARNINGS; |
Select the min value of the field with the alias name from the table:
|
1 |
select min(phone) as google from user; |
Select some rows meet the condition and calculate the sum of the values of fields, return with random fields:
|
1 |
select sum(name) from user where userid=0; |
Select three of all rows in default order from one table:
|
1 |
select * from user limit 3; |
Return the number of the rows satisfy the condition with a random fields:
|
1 |
select count(phone) from user where userid=0; |