Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to master SQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to master SQL".

Example 1

We first connect to the MySQL server from the terminal and create a database.

~ $sudo mysql-u root

We will be prompted for a password. We are now connected to the MySQL server on the computer.

The following command creates a database called Retail.

Mysql > create database retail; mysql > use retail

We are not in a retail database that does not yet contain any tables.

Example 2

We will first create a table called "customer" using the create table command.

Mysql > create table customer (- > cust_id int primary key,-> age int,-> location varchar (20),-> gender varchar (20)->)

We define the name of the column and the associated data type in parentheses. Specify the cust_id column as the primary key.

The primary key is a column that uniquely identifies each row. Just like the index of the panda data box.

Example 3

We will create a second table, called the order.

Mysql > create table orders (- > order_id int primary key,-> date date,-> amount decimal (5dag2),-> cust_id int,-> foreign key (cust_id) references customer (cust_id)-> on delete cascade->)

At the beginning, we mentioned that relational tables are related to each other by sharing columns. The columns associated with the two tables are foreign keys.

A foreign key is something that associates a table with another table. The foreign key contains the primary key of another table.

The cust_id column in the order table is a foreign key and correlates the order table with the customer table. We specify this condition when we create the table.

On the last line, we specify another condition with the phrase "on delete cascading". It tells MySQL what to do when deleting a row from the customer table. Each row in the order table belongs to a customer. Each row in the customer table contains a unique customer ID and represents a customer. If the row in the customer table is deleted, it means that we no longer have the customer. As a result, orders belonging to this customer no longer have an associated customer ID. Cascading when deleting means that orders that do not have an associated customer ID will also be deleted.

Example 4

The retail database now contains two tables. We can use the show table command to view the tables that exist in the database.

Mysql > show tables; +-+ | Tables_in_retail | +-+ | customer | | orders | +-+

Note: commands in SQL end with a semicolon (";").

Example 5

The desc or describe command outlines the table in terms of column names, data types, and some other information.

Mysql > desc orders +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | order_id | int (11) | NO | PRI | NULL | date | date | YES | NULL | amount | decimal (5L2) | YES | | NULL | cust_id | int (11) | YES | MUL | NULL | | +-+- -+

Example 6

We can modify the existing table. For example, the alter table command can be used to add new columns or delete existing columns.

Let's add a column "is_sale" to the order table.

Mysql > alter table orders add is_sale varchar (20)

We write column names and data types as well as the add keyword.

Mysql > desc orders +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | order_id | int (11) | NO | PRI | NULL | date | date | YES | NULL | amount | decimal (5L2) | YES | | NULL | cust_id | int (11) | YES | MUL | NULL | is_sale | varchar (20) | YES | | NULL | | +-+-+

The is_sale column has been added to the order table.

Example 7

Alter table can also be used to delete columns that have slightly changed syntax.

Mysql > alter table orders drop is_sale

Use the drop keyword instead of the add keyword. We also don't have to write data types to delete columns.

Example 8

We have tables, but they don't contain any data. One way to populate a table is the insert statement.

Mysql > insert into customer values (- > 1000, 42, 'Austin',' female'->)

The specified value is inserted into the column in the same order. Therefore, we need to keep the order consistent.

Example 9

We can insert multiple rows at the same time by separating each row.

Mysql > insert into customer values-> (1001, 34, 'Austin',' male'),-> (1002, 37, 'Houston',' male'),-> (1003, 25, 'Austin',' female'),-> (1004, 28, 'Houston',' female'),-> (1005, 22, 'Dallas',' male'),->

I added some rows and populated the orders table in the same way.

There are other ways to populate tables with data. For example, we can use load data infile or load data local infile statements to load csv files.

Example 10

The delete from statement can be used to delete existing rows in a table. We need to identify the row to delete by providing conditions. For example, the following statement deletes the line with order ID 17.

Mysql > delete from orders-> where order_id = 17

If we do not specify a condition, all rows in the given table are deleted.

Example 11

We can also update existing rows. Let's update a row in the order table.

+-+ | order_id | date | amount | cust_id | +-+ | 1 | 2020-10-01 | 24. 40 | 1001 | +-+

This is the first row in the order table. We would like to change the order amount to 27.40.

Mysql > update orders-> set amount = 27.40-> where order_id = 1; mysql > select * from orders limit 1 +-+ | order_id | date | amount | cust_id | +-+ | 1 | 2020-10-01 | 27.40 | 1001 | +-+

We write the updated value after the set keyword. Identify the row to update by providing a condition after the where keyword.

Example 12

If you want to create a table by copying the structure of an existing table, you can use a create table statement with the like keyword.

Mysql > create table orders_copy like orders; mysql > show tables; +-+ | Tables_in_retail | +-+ | customer | | orders | | orders_copy | +-+

The orders_copy table has the same structure as the orders table, but does not contain any data.

Example 13

We can also create a copy of an existing table by using create table and select statements to use data together.

Mysql > create table new_orders-> select * from orders

It seems to be a combination of two separate statements. The first row creates the table, and the second row populates the table with data from the orders table.

Example 14

The drop table statement can be used to delete tables in the database.

Mysql > drop table orders_copy, new_orders; mysql > show tables; +-+ | Tables_in_retail | +-+ | customer | | orders | +-+

We have successfully deleted the table created in the previous example.

We have two relational tables in the database. The following example shows how we can use a select query to retrieve data from these tables.

Example 15

The simplest query is to look at all the columns in the table.

Mysql > select * from orders-> limit 3 +-+ | order_id | date | amount | cust_id | +-+ | 1 | 2020-10-01 | 27.40 | 1001 | | 2 | 2020-10-01 | 36.20 | 1000 | | 3 | 2020-10-01 | 1002 | +-+

"*" selects all columns, while the limit keyword constrains the number of rows to display.

Example 16

By writing column names instead of "*", we can only select certain columns.

Mysql > select order_id, amount-> from orders-> limit 3; +-+-+ | order_id | amount | +-+-+ | 1 | 27.40 | | 2 | 36.20 | | 3 | 65.45 | +-+ |

Example 17

We can use the where clause to specify the conditions of the row to be selected. The following query will return all orders for 2020Mel 10Mel 01.

Mysql > select * from orders-> where date = '2020-10-01' +-+ | order_id | date | amount | cust_id | +-+ | 1 | 2020-10-01 | 27.40 | 1001 | | 2 | 2020-10-01 | 36.20 | 1000 | | 3 | 2020-10-01 | 1002 | +-+

Example 18

The where clause accepts multiple conditions. Let's add another condition to the query in the previous example.

Mysql > select * from orders-> where date = '2020-10-01' and amount > 50 +-+ | order_id | date | amount | cust_id | +-+ | 3 | 2020-10-01 | 65.45 | 1002 | +-+

Example 19

We may want to sort the query results, which can be done by using the order by clause.

The following query will return the orders of 2020Mel 10Mel 02 and sort them by amount.

Mysql > select * from orders-> where date = '2020-10-02'-> order by amount +-+ | order_id | date | amount | cust_id | +-+ | 5 | 2020-10-02 | 18.80 | 1005 | | 6 | 2020-10-02 | 21.15 | 1009 | | 4 | 2020-10-02 | 34.40 | 1001 | 7 | 2020-10-02 | 1008 | 8 | 2020-10-02 | 1002 | +-+

Example 20

By default, the order by clause sorts rows in ascending order. We can use the desc keyword to change it to descending order.

Mysql > select * from orders-> where date = '2020-10-02'-> order by amount desc +-+ | order_id | date | amount | cust_id | +-+ | 8 | 2020-10-02 | 41.10 | 1002 | | 4 | 2020-10-02 | 34.40 | 1001 | | 7 | 2020-10-02 | 34.40 | 1008 | 6 | 2020-10-02 | 1009 | 5 | 2020-10-02 | 1005 | +-+

Example 21

SQL is a universal language and can also be used as a data analysis tool. It provides many features to analyze and transform data when querying from a database.

For example, we can calculate the unique number of days in the order table.

Mysql > select count (distinct (date)) as day_count-> from orders; +-+ | day_count | +-+ | 4 | +-+

The order table contains orders with 4 different dates. The "as" keyword is used to rename the columns in the query results. Otherwise, the column will be named "count (distinct (date))".

Example 22

There are four days in the order form. We can also find out how many orders there are every day. The group by clause will help us accomplish this task.

Mysql > select date, count (order_id) as order_count-> from orders-> group by date +-+-+ | date | order_count | +-10-01 | 2020-10-01 | 3 | 2020-10-02 | 5 | 2020-10-03 | 6 | 2020-10-04 | 2 | +-+ +

We calculate the order and group it by date column.

Example 23

We will calculate the average daily order amount and sort the results in descending order according to the average amount.

Mysql > select date, avg (amount)-> from orders-> group by date-> order by avg (amount) desc +-+-+ | date | avg (amount) | +-+-+ | 2020-10-01 | 43.016667 | | 2020-10-04 | 42.150000 | | 2020-10-03 | 37.025000 | | 2020-10-02 | 29.970000 | +-- -+

Example 24

We want to modify the query in the previous example and include only days where the average amount is greater than 30.

Mysql > select date, avg (amount)-> from orders-> group by date-> having avg (amount) > 30-> order by avg (amount) desc +-+ | date | avg (amount) | +-+-+ | 2020-10-01 | 43.016667 | | 2020-10-04 | 42.150000 | | 2020-10-03 | 37.025000 |

It is important to note that the order of statements in the query is important. For example, if you put the order by clause before the having clause, an error occurs.

Example 25

We want to find out the maximum order quantity per day.

Mysql > select date, max (amount)-> from orders-> group by date +-+-+ | date | max (amount) | +-+-+ | 2020-10-01 | 65.45 | | 2020-10-02 | 41.10 | | 2020-10-03 | 80.20 | 2020-10-04 | 50.10 |

Example 26

We are going to combine multiple aggregate functions in the select statement. To illustrate this point, let's elaborate on the previous example. We would like to see the difference between the maximum order and the minimum order for each customer. We also want to sort the results according to the difference in ascending order and display the first three results.

Mysql > select cust_id, max (amount)-min (amount) as dif-> from orders-> group by cust_id-> order by dif desc-> limit 3 +-+-+ | cust_id | dif | +-+-+ | 1007 | 46.00 | 1009 | 28.95 | 1002 | 24.35 | +-+

The diff column is obtained by subtracting the minimum number from the maximum number.

Example 27

We now switch to the customer table. Let's find out how many female and male customers there are in each city. We need to write both the location and gender columns in the group by clause.

Mysql > select location, gender, count (cust_id)-> from customer-> group by location, gender +-+ | location | gender | count (cust_id) | +-+ | Austin | female | 2 | | Austin | male | 1 | | Dallas | | female | 2 | Dallas | male | 2 | Houston | female | 2 | | Houston | male | 1 | +-+ |

Example 28

The customer table and order table are related to each other based on the cust_id column. We can use SQL joins to query data from two tables.

We want to see the average order amount for each city in the customer table.

Mysql > select customer.location, avg (orders.amount) as avg-> from customer-> join orders-> on customer.cust_id = orders.cust_id-> group by customer.location +-+-+ | location | avg | +-+-+ | Austin | 33.333333 | | Dallas | 34.591667 | Houston | 44.450000 | +-+-+

Because we select columns from two different tables, we will use the associated table name to specify the column name. The second, third, and fourth rows of the above query join the customer andorders table together based on the cust_id column in each table.

Note that the column names do not have to be the same. No matter what column names we provide using the "on" keyword, we will compare or match based on these columns.

Example 29

We would like to see the average age of customers who placed orders in 2020Mel 10-03.

Mysql > select avg (c.age) as avg_age-> from customer c-> join orders o-> on c.cust_id = o.cust_id-> where o.date = '2020-10-03; +-+ | avg_age | +-+ | 30.0000 | +-+

We can also use aliases for table names. It is convenient when we need to type the table name multiple times.

Instance 30

We want to check the location of the customer with the largest order.

Mysql > select c.location, o.amount-> from customer c-> join orders o-> on c.cust_id = o.cust_id-> where o.amount = (select max (amount) from orders)-> +-+-+ | location | amount | +-+-+ | Dallas | 80.20 | +-+-+

In this query, we have a nested select statement. The amount condition is calculated using a separate select statement in the order table.

You can accomplish this task in other ways. I chose this approach to introduce the concept of nested queries.

Thank you for your reading, the above is the content of "how to master SQL", after the study of this article, I believe you have a deeper understanding of how to master SQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.