MySQL query operation

Posted by the-hardy-kid on Sun, 16 Feb 2020 05:48:27 +0100

Introduction to MySQL database Learning notes (2)

Simple query

SELECT column_name1, column_name2 FROM table_name;
#select name,phone from person;
SELECT * FROM table_name;
#select * from person; * indicates all contents in the query table

Conditional query

where clause is required for condition query in SQL, which can be used to insert, modify, delete or query the records of specified conditions.

Single condition query

SELECT column_name FROM table_name WHERE column_name operator value
#select name,phone from person where name = 'bob';
#select age from person where age between 18 and 25;

The operators include:

operator describe
= (= = =) Be equal to
< or =! No less than
> greater than
< less than
>= Greater than or equal to
<= Less than or equal to
between A and B Equivalent to > = A and < = b

Multi criteria query

SELECT column_name FROM table_name 
WHERE condition1 AND condition2 OR condition3
#Select * from person where age > 18 and sex = 'male';
operator describe
and Indicates that the conditions on the left and right sides are valid at the same time
or It means that there is only one condition on the left and right sides

Use of operators IN and LIKE

Operator IN

Operator IN is used to filter multiple values of a field IN the WHERE clause.

SELECT column_name FROM table_name WHERE column_name IN(value1, value2, ...);

Generally, the use of in can avoid frequent use of or, for example, the following example:

select * from person where id=1 or id = 3 or id = 6 or id = 7;

To write with the operator in is to:

select * from person where id in(1,3,6,7);

Operator LIKE

Operator LIKE is used to query all records containing the xxx string

SELECT column_name FROM table_name WHERE column_name LIKE '%value%'
#select name from person where name like 'Wang%'; filter out all the people with the surname Wang

Explain:

  1. % in LIKE clause is similar to * in regular expression and can match any 0 or more characters
  2. Match any single character in LIKE clause
  3. If there is no% and NUU in the LIKE clause, it is equivalent to the effect of operator =

Use of ORDER BY

In the process of using SQL, you need to sort query results in many cases, which requires order by.

SELECT column_name1, column_name2
FROM table_name1, table_name2
ORDER BY column_name, column_name [ASC|DESC]
#select name,age from person order by salary desc;

Explain:
ASC is in ascending order, DESC is in descending order, otherwise, it is in ascending order by default

Use of LIMIT

LIMIT clause is used in SELECT statement to restrict the number of records to be returned, and LIMIT is usually used to implement paging.

SELECT column_name1, column_name2
FROM table_name1, table_name2
LIMIT [offset,] row_count
#select name,age,salary from person limit 0,10; return 10 records

Explain:

  1. Offset is the offset of the first line to return. The offset of the first row of records in the table is 0, not 1.
  2. Row count is the maximum number of rows to return.

limit paging formula:

limit (page-1)*row_count, row_count

Use of GROUP BY

group by means to group data according to some rules. It must be used in combination with aggregation function. After grouping data, count, sum, avg, max, min and other operations can be performed.

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
# select sex,count(*) from person group by sex

Explain:

  1. Aggregate? Function represents an aggregate function.
    An aggregate function evaluates a set of values and returns a single value.
    There are five common aggregate functions for MySQL, which are:
function Explain
count Returns the total number of eligible records
sum Returns the sum of the specified columns, ignoring null values
avg Returns the average value of the specified column, ignoring null values
min Returns the minimum value of the specified column, ignoring null values
max Returns the maximum value of the specified column, ignoring null values
  1. group by can group one or more columns.

Use of group ABCD concat

Group concat is used with groupby to splice the values of a column according to the specified separator. The default separator for MySQL is comma.

group_concat([distinct] column_name [order by column_name asc/desc ] [separator 'Separator'])

Use of HAVING

The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with aggregate functions. HAVING clause can filter the grouped data twice.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

The specific effect is as follows

Use of DISTINCT

Distinct is used to return unique and different values of columns in the query (de duplication), and supports single or multiple columns. In practical application, it is very common for a column in a table to contain duplicate values. If you want to get all the different values of a column when querying data, you can use distinct.

SELECT DISTINCT column_name, column_name
FROM table_name;

Use of table connections

Table join is to associate tables through certain join conditions, and then get data from multiple tables.

SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column1 = table2.column2;

Table connection can be divided into three ways: internal connection, self connection and external connection

Inner connection: only matching rows are connected

select A.c1, B.c2 from A join B on A.c3 = B.c3
#Take out A.c1, B.c2 records in two tables that meet the conditions of A.c3 = B.c3 at the same time

External connection: including left external connection, right external connection and all external connection (mysql cannot be used).

Left outer join: contains all the rows of the left table (whether there is a matching row in the right table or not), and all the matching rows in the right table
select A.c1, B.c2 from A left join B on A.c3 = B.c3
#Take out the records of A.c1 and b.c2 that meet the conditions of A.c3 = B.c3 in both tables and the records that do not meet the conditions in table A

Right outer join: contains all the rows of the right table (whether there is a matching row in the left table or not), and all the matching rows in the left table
select A.c1, B.c2 from A right join B on A.c3 = B.c3
Outer join: contains all the rows of the left and right tables (whether there is a matching row in another table or not)
select A.c1, B.c2 from A full join B on A.c3 = B.c3

Self join

Self join is a special kind of table join, which means that the linked tables are physically the same table, but logically multiple tables. Self join is usually used for data hierarchy in tables, such as area table, menu table, commodity classification table, etc.

SELECT A.column, B.column FROM table A, table B
WHERE A.column = B.column;

Use of subqueries IN and EXISTS

Sub query is also called internal query or nested query, which embeds query statement in WHERE clause of SQL query.

Subquery IN

SELECT column_name FROM table_name 
WHERE column_name IN(
 SELECT column_name FROM table_name [WHERE]
);

Subquery EXISTS

EXISTS is a Boolean operator in a subquery that tests whether the internal query returns any rows. Put the data of the main query into the sub query for condition verification, and decide whether to keep the data result of the main query according to the verification result (TRUE or FALSE).

SELECT column_name1 
FROM table_name1 
WHERE EXISTS (
 SELECT * FROM table_name2 WHERE condition
 );
22 original articles published, 85 praised, 10000 visitors+
Private letter follow

Topics: MySQL SQL less Database