1, Sort by keyword

Format:

select Field 1, Field 2 from Table name ORDER BY Field 1, Field 2 ASC|DESC;

Example:

There is one in the database info Table, which records the student's id，Name, score, address and hobbies
create table info (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbid int(5));

insert into info values(1,'liuyi',80,'beijing',2);
insert into info values(2,'wangwu',90,'shengzheng',2);
insert into info values(3,'lisi',60,'shanghai',4);
insert into info values(4,'tianqi',99,'hangzhou',5);
insert into info values(5,'jiaoshou',98,'laowo',3);
insert into info values(6,'hanmeimei',10,'nanjing',3);
insert into info values(7,'lilei',11,'nanjing',5);

1.1. Single field and multi field sorting:

1)Single field sorting:
#Sort by score. If it is not specified by default, it is sorted in ascending order asc
select name,score from info order by score [asc];
#In descending order, use DESc
select name,score from info order by score desc;
2)Multi field sorting principle:
order by Subsequent parameters, using"，"The priority is determined in order,
for example:select id,name,hobby from info order by hobby desc,id asc;
order by After the first parameter, the second field is meaningful only when the same value appears

1.2. Interval judgment and query non duplicate records:

Interval judgment:
① and/or - and/or

mysql> select * from info where score >70 and score <=90;
mysql> select * from info where score >70 or score <=90;

② Nested / multiple conditions with parentheses

mysql> select * from info where score >70 or (score >75 and score <90);

Query non duplicate records:

Format: select distinct field from Table name﹔
Example: mysql> select distinct hobbid from info;
ps: distinct Must be placed at the beginning
distinct When multiple fields are de duplicated, several fields will be filtered only when they are repeated at the same time

2, Group results

Overview: the results of SQL queries can also be grouped using the group by statement. Group by is usually used in combination with aggregate functions. Common aggregate functions include COUNT, SUM, AVG, MAX and MIN, When grouping by, you can group the results by one or more fields.

Syntax:
select column_name,aggregate_function(column_name)from table_name where column_name operator value group by column_name;
selet Fields, aggregate functions from Table name,(where Field name (matching) numerical value) group by Field name;
Example:
select count (name), level from player where level>=45 group by level;    #Group player s. The filter range / condition is' name 'with ievel greater than or equal to 45. Players with the same level will be divided into one group by default

combination where Statement, filter groups with scores greater than or equal to 80, and calculate the number of students
mysql> select count(name),hobbid from info where score>=80 group by hobbid;
combination order by Arrange the calculated number of students in ascending order
mysql> select count(name),score,hobbid from info where score>=80 group by hobbid order by count(name) asc;

3, limit result entry ⭐⭐⭐)

Overview: when querying with MySQL SELECT statement, the result set returns all matching record rows. Sometimes you only need to return the first row or the first few rows. In this case, you need to use the LIMIT clause.

Format:
select column1, column2, ... FROM table_name LIMIT [offset,] number
offset  Index subscript
number  Number of digits after index subscript
LIMIT The first parameter of is the position offset (optional parameter), which is the setting MySQL Which line to display from. If the first parameter is not set,
The display will start from the first record in the table. It should be noted that the position offset of the first record is 0, the second record is 1, and so on. The second parameter is to set the maximum number of returned record rows.

Example: combination order by Statement by id The first three rows are displayed in ascending order of size
mysql> select id,name from info order by id limit 3;

4, Set alias (alias - as)

Overview: during MySQL query, when the table name is long or some fields in the table are long, in order to facilitate writing or use the same table for many times,
You can set aliases for fields, columns, or tables. When using the alias directly, it is concise and clear to enhance readability

Syntax:
For column aliases: SELECT column_name AS alias_name FROM table_name;
For table aliases: SELECT column_name(s) FROM table_name AS alias_name;

AS Can be omitted. in use AS After, you can use alias_name replace table_name，among AS Statement is optional. AS The later alias is mainly used to provide temporary names for columns or tables in the table. It is used in the query process. The actual table name or field name in the library will not be changed

Example of column alias setting:
select name as full name,score as achievement from info;
If the table is long, you can use AS Set an alias for the table and use the alias directly in the query process
 Temporary settings info The alias for is i
 Table data alias setting example:
select i.name as full name,i.score as achievement from info as i;

Usage scenario:
1. When querying complex tables, aliases can shorten the length of query statements
2. When multiple tables are connected to query (easy to understand and shorten sql statements)

PS: when setting an alias for a table, ensure that the alias does not conflict with the names of other tables in the database.
Column aliases are displayed in the results, while table aliases are not displayed in the results and are only used when executing queries.

AS can also be used AS an operator for join statements.

Example: Creating t1 Table, will info Insert all query records of table t1 Table (constraints in the original table, such as primary keys, will not be copied)
mysql> create table t1 as select * from info;
Example: Creating test1，take info Data insertion in table test1，At the same time, those with scores greater than 60 were screened out
create table test1 as select * from info where score >=60;

5, Wildcard

Wildcards are mainly used to replace some characters in the string and query the relevant results through the matching of some characters.
Generally, wildcards are used together with like (fuzzy query) and cooperate with WHERE clause to complete the query task.

There are two common wildcards:
%: The percent sign indicates zero, one or more characters		
_: The underscore indicates a single character 					

Example:
mysql> select id,name from info where name like 'c%';  #Query records whose names start with c
mysql> select id,name from info where name like 'c_ic_i';  #The query name is a record with one character between c and i
mysql> select id,name from info where name like '%g%';   #Query the record with g in the middle of the name
mysql> select id,name from info where name like 'tang___';   #Query the name record of the last 3 characters of tang
mysql> select id,name from info where name like 's%_';     #Query records with names beginning with s

6, Subquery

Subquery is also called inner query or nested query, which means that one query statement is also nested with another query statement.
The sub query statement is executed before the main query statement, and its results are returned to the main query as outer conditions for further query filtering.

Same table:
select name,score from info where id in (select id from info where score >80);
Subject sentence: select name,score from info where id
 Substatement: select id from info where score >80
in    #Fixed syntax for connecting main and sub statements

Different tables:
mysql> create table ky11 (id int);
mysql> insert into ky11 values(1),(2),(3);
mysql> select id,name,score from info where id in (select * from ky11);

The sub statement can be the same as or different from the table queried by the subject sentence
Subqueries can be used not only in SELECT statements, but also in INERT, UPDATE and DELETE.
During nesting, new subqueries can also be nested inside subqueries, that is, they can be nested at multiple levels.
Syntax:
< expression > [not] in < subquery >

When the expression is equal to a value in the result set returned by the subquery, it returns TRUE; otherwise, it returns FALSE. If NOT keyword is enabled,
The return value is the opposite. It should be noted that the sub query can only return one column of data. If the requirements are complex, one column can not solve the problem,
You can use multi-layer nesting to deal with it. In most cases, subqueries are used with SELECT statements

Example:
mysql> select name,score from info where id in (select id from info where score>80);   #Query records with scores greater than 80
mysql> insert into t1 select * from info where id in (select id from info);             #Delete all records in t1 and re insert the records in info table
mysql> update info set score=50 where id in (select * from ky11 where id=2);  # Change the score of caicai to 50
mysql> delete from info where id in (select id where score>80);  #DELETE is also applicable to sub queries to DELETE records with scores greater than 80
mysql> delete from t1 where id not in (select id where score>=80);  #You can also add NOT before IN, which is opposite to IN. It means negative (that is, it is NOT IN the result set of the sub query). Delete records with scores NOT greater than or equal to 80

Subquery - alias as

mysql> select id from (select id,name from info);  #The contents of the id and name fields in the info table are used as the part of the "content" output id
 An error will be reported because:
select * from The table name is in standard format, and the above query statement,"Table name"The location of is actually a result set, mysql Not directly identifiable
 At this time, an alias is set for the result set to "save" select a.id from a"Query this result set as one"surface"
select a.id from (select id,name from info) a;

Subquery - exists

selectt count(*) as number from tmp where exists (select id from tmp where name='zhangsan')
as number take count Statistical results as number(Column name) return
exits: Boolean value to judge whether the following sub query statement is true
where: Followed by conditional judgment
 plus exists Just to judge exists Whether the subsequent condition is true. If it is true, the subject sentence matching will be executed normally. If it is not true, the subject sentence query will not be executed PS: count Is count, sum For summation, use sum Summation combination exists，If the sub query result set is not valid, the output is null 

view
The virtual table in the database does not contain real data, but is mapped. It is similar to a mirror / reflection. It dynamically saves the result set (data) and is applied to tables that provide different result sets for different people

Scope of action:
select * from info;			#The part shown is the info table
select * from view_name;	#One or more tables displayed

create view v_score as select * from info where score>=80;  #Students who meet 80 points are shown in the view
(This result will change dynamically, and different views can be displayed to different groups (such as permission ranges)
view a chart
select * from v_score;
Modify original table data
update info set score='60' where name='wangwu';
view a chart
select * from v_score;

Example of multi table view creation:
Create second table test01 
create table test01 (id int,name varchar(10),age char(10));
insert into test01 values(1,'zhangsan',20);
insert into test01 values(2,'lisi',30);
insert into test01 values(3,'wangwu',29);
mysql> create view v_info(id,name,score,age) as select info.id,info.name,info.score,test01.age from info,test01 where info.name=test01.name;    #To create a view, you need to output id, student name, score and age
mysql> select * from v_info;

If you modify the data view of the original table, the table will change accordingly:(Modify view table,The original table data will also change)
mysql> update info set score='60' where name='liuyi';
mysql> update v_score set score='120' where name='tianqi';

Try to distinguish from table:
1),The view is compiled sql sentence. Not the table
2),The view has no actual physical records. And the watch has.
show table status\G
3),The table only uses physical space, while the view does not occupy physical space. The view is only the existence of logical concept. The table can be modified in time, but the view can only be modified with created statements
4),View is a method to view a data table. You can query the data composed of some fields in the data table SQL A collection of statements. From a security point of view, the view can not give users access to the data table, so they do not know the table structure.
5),A table belongs to a table in the global mode and is a real table; A view is a table in local mode and is a virtual table.
6),View creation and deletion only affect the view itself, not the corresponding basic table. (however, updating the view data will affect the basic table.)
7),The fields calculated by function or compound function cannot be modified in the modification table
8),Convenient query:The index speed is fast, and multi table query can be performed at the same time, which is faster(The view does not save real data, and the view is essentially similar select)
9),Safer

7, NULL value

NULL characters are often encountered during the use of SQL statements. NULL is usually used to represent the missing value, that is, the field has no value in the table.
If you restrict some fields from being empty when creating a table, you can use the NOT NULL keyword,
If it is not used, it can be blank by default.
When inserting a record into a table or updating a record, if the field has no NOT NULL and no value, the field of the new record will be saved as NULL. It should be noted that the NULL value is different from the field with the number 0 or spaces,
A field with a NULL value has no value. In SQL statements, you can use IS NULL to determine whether a field in the table is a NULL value. On the contrary, you can use IS NOT NULL to determine whether it is a NULL value.

Query the info table structure. The name field cannot be null

The difference between null value and null value ⭐

The null value is 0 in length and does not occupy space, NULL The length of the value is null，Occupied space
is null Cannot determine null value
 Null use"="Or "<>"To handle (!=)
count()When calculating, NULL Will be ignored and null values will be added to the calculation

alter table info add column addr varchar(50);
update info set addr='nj' where score >=70;
select count(addr) from info;  #Detection null will not be added to the statistics
update info set addr='' where name='wangwu';  #Modify one of the data in info table to null value ''
select count(addr) from info;    #Detected null values are added to the statistics

query null value
mysql> select * from info where addr is NULL;
Query non empty value
mysql> select * from info where addr is not null;