catalogue
3, Query non duplicate records
2. The difference between NULL and NULL values
1, Sort by keyword
Use the select statement to query the required data from the mysql database. If you sort the query results, you can use the order by statement to complete the sorting, and finally return the sorted results to the customer.
select field from Table name order by field ASC|DESC
ASC|DESC
- ASC is ranked in ascending order, which is the default sorting method, that is, ASC can be omitted
- DESC is sorted in descending order
- order by can also further filter the query results through the where clause
- You can sort multiple fields
1. Single field sorting
select * from working order by id desc; according to id The fields are arranged in descending order select * from working where id>3 order by id; use where Filter and sort
2. Multi field sorting
select * from ph order by id desc,hobby desc; Multi field sorting by criteria will sort by the first field. select * from ph order by hobby desc,id desc; If there are duplicate fields when sorting by criteria, the duplicate fields will be arranged by the second field.
2, Or / and Application
select * from ljm where id>2 and id<5; select * from ljm where id>2 or id<5; nesting/Multiple conditions select * from ljm where id>2 or (id>3 and id <5);
3, Query non duplicate records
select distinct field from Table name;
- distinct must be placed at the beginning
- distinct can only operate with fields that need to be de duplicated
- distinct de duplication of multiple fields means that several fields can be filtered only when they are repeated at the same time
4, Group results
The results of SQL query can also be grouped by using the group by statement
Group by is usually used in combination with aggregation functions. Common aggregation functions include count, sum, avg, max and min. group by can group results by one or more fields
select field,Aggregate function from Table name (where Field name(matching) numerical value) group by Field name;
5, Limit result entries
When the mysql select statement is used for query operation, the result set returns all matching records. Sometimes you just need to return the first line or the first few lines. In this case, you need to use the limit clause.
select field from Table name limit [offset,] number
- The first parameter of limit is the position offset (optional parameter), which sets the line from which mysql starts
- If the first parameter is not set, it will be displayed from the first record in the table.
- The first offset is 0 and the second is 1
- offset is the index subscript
- number is the digits after the index subscript
6, Set alias
- During mysql query, when the table name is long or some fields in the table are long, you can set aliases for field columns or tables to facilitate writing or use the same table multiple times
- Convenient operation and enhanced readability
Alias of the column select field as Field alias table name Table alias select field from Table name as alias as Can be omitted
Usage scenario
- When querying complex tables, aliases can shorten query statements
- When multiple tables are connected to query (easy to understand and shorten SQL statements)
7, Join statement
AS Can be used as a join statement For example: create table lucien as select * from ljm; there AS Role: 1,Create a new table lucien,Define table structure,Insert table data(And working Same table) 2,But "constraints""Not "copied",However, if the primary key is set in the original table,So what's in the attached table: default The field is set to 0 by default
8, 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 the where clause to complete the query task
- There are two common wildcards
- %: the percentage sign represents any number of characters
- _: underscore indicates a single character
9, Subquery
1. Definition
- 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 the seven results are returned to the main query as outer conditions for further query filtering
- The sub statement can be the same as or different from the table queried by the subject sentence
Example: select id,name from ljm where hobby in (select hobby from ljm where hobby>3); in Is the syntax that associates the primary query with the subquery
- Subqueries can be used not only in select, but also in insert, update and delete
- Support multi-layer nesting
- The in statement is used to determine whether a value is in a given set (result set). In is often used with select
- You can use not in to negate the result set
2. Subquery - alias
Example: select name,hobby from ljm The above commands can query the of a table name and hobby information We also need aliases when querying the results as "tables" For example, we enter: select hobby from (select name,hobby from ljm); Will report an error because select name,hobby from ljm You get a result set, not a table, mysql Is unrecognizable So we need to set an alias for the result set, so mysql You can treat the result set as a table.
3. Subquery - exists
select count(*) as num from working where exists(select id from working where id>3); as num take count Statistical results as num return exists Boolean judgment, where If the following statement is true, the Boolean value is 0, the previous operation will be performed, otherwise the return value is 0 When used sum Summation combination exists ，If the query result is not valid, the return value is null
10, View
- A virtual table in a database. This virtual table is a dynamic mapping of real data
- This virtual table can dynamically save the result set
- Because there is a dynamic synchronization relationship between the view and the real table, when we modify the virtual table, the real data will also be affected
- But the view has nothing but a name. It is a projection, so it doesn't occupy space
- We only need to set its permissions to ensure its security
- We can define display conditions for views and display different web content for different people
11, NULL value
1. Definition
- NULL is usually used to represent the missing value, that is, there is no value when changing the field in the table.
- If you restrict some fields from being empty when creating a table, you can use the NOT NULL keyword. If you do not use it, it can be empty 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, and the field with the value NUL 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.
2. The difference between NULL and NULL values
- The null value is 0 in length and does not occupy space
- The length of null value is null, occupying space
- is null cannot determine null value
- Null values are processed with "=" or "< >"
- During count() calculation, NULL will be ignored and NULL value will be added to the calculation