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:
- % in LIKE clause is similar to * in regular expression and can match any 0 or more characters
- Match any single character in LIKE clause
- 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:
- 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.
- 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:
- 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 |
- 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 );