MySQL

Common commands

function The command The instance
Check the mysql running status sudo systemctl status mysqld
Start the service sudo systemctl start mysqld
Close the service sudo systemctl stop mysqld

MySQL client tool

2.1 Operation based on the built-in client

The Mysql account and password are root/ROOTroot_1

  • The login

    mysql -u root -p
Copy the code

  • Viewing the database list

    show databases; #3) Check the database list, notice the end of the command use "semicolon"Copy the code

  • Deleting a Database

    drop database world; #4) Delete the databaseCopy the code

  • Creating a database

    create database world; #5) Create the databaseCopy the code

  • Using the database

    use world; #6) Use the databaseCopy the code

  • View tables

    show tables; #7) View the table corresponding to the current data (world)Copy the code

  • exit

    exit;
Copy the code

2.2 Operations based on third-party graphics clients

Initialize the SQL script

  • SQL –>/opt/data [XFTP]
  • Log in to the MySQL cli client
  • Use the empty database World
  • Initialize the script in database world: use the command source
[hadoop@hadoop01 ~]$mysql -uroot -p #1
mysql> use world; Use world # 3)

mysql> show tables; #4) No data tables before introduction
Empty

mysql> source /opt/data/world.sql; #5) To initialize the script, upload it using XFTP first
mysql> show tables; #6) Indicates that the script was successfully initialized
Copy the code

MySQL common operations

View the city table structure

Desc table name;Copy the code

Retrieves the continent involved in the country table

DISTINCT: go to heavy

select DISTINCT continent #selectFollowed by the columnfrom country #fromFollowed by the name of the tableCopy the code

Retrieve the city table

Search: City id, city name, district

#1Retrieves the specified columnSELECT id,name,District
from city

#2) Search city ('New York'.'Kabul')SELECT id,name,District
from city
where name in('New York'.'kabul'#) filterCopy the code

Sort order by

Country continent = country continent = country continent = country continent = country continent = name = country continent

select Continent,name
from country
order by Continent desc,name asc
Copy the code

Group by group

Operation table: country table

Count the average population of each continent
select Continent, avg(Population) as avg_popu
from country
group by Continent
order by avg_popu desc
Copy the code

The average population of each continent is more than 1 million
select Continent, avg(Population) as avg_popu
from country
group by Continent
HAVING avg_popu > 10000# screeningorder by avg_popu desc

Copy the code

Inner join

Table from city, country

Search :(city name name, district corresponding to city, country name of city [name of country table])

select c1.name,c1.District,c2.name as countryName
FROM city as c1
join country as c2
on c1.CountryCode = c2.Code
Copy the code