当前位置:网站首页>Mysql database explanation (VII)
Mysql database explanation (VII)
2022-04-23 15:19:00 【C chord~】
Catalog
5、 ... and . Query does not duplicate records
7、 ... and . Limit result entries
introduction :
Many of the statements used in the production environment are not basic statements , It's an advanced statement 、 For example, filter query 、 Multi table query, etc . therefore MySQL High level statements are very important .
One . Common query
1.1 Sort by single field
select Field 1, Field 2, Field 3 from indicate order by Field ;
asc Expressing ascending order ( Default )
desc Representation of descending order
1.2 Conditions of the query
select Field 1, Field 2, Field 3 from Table name where filter ;
1.3 Multi field sorting
select Field 1, Field 2, Field 3 from indicate order by Field , Field ;
order by The first parameter after that is only when the same value appears , The second field makes sense
Four . Interval judgment
AND/OR —— And / or
nesting / Multiple conditions
5、 ... and . Query does not duplicate records
select distinct Field from Table name ﹔
1.distinct Must be placed at the beginning
2.distinct You can only use fields that need to be de duplicated .
3.distinct When removing multiple fields , The meaning is : Only when several fields are repeated at the same time can they be filtered .
6、 ... and .. Group results
select Field , Aggregate functions from Table name (where Field name ( matching ) The number ) group by Field name ;
group by Usually used in conjunction with aggregate functions , Common aggregation functions include : Count (count)、 Sum up (sum)、 Average (avg)、 Maximum (max)、 minimum value (min),group by When grouping, the results can be grouped according to one or more fields
7、 ... and . Limit result entries
select Field from Table name limit [offset,] number
limit The first parameter is the position offset ( Optional parameters ), It's settings mysql Which line to start with
If the first parameter is not set , It will start from the first record in the table .
The first offset is 0, The second is 1
offset Subscript for index
number Is the number of digits after the index subscript
8、 ... and . Set alias
Alias of column
select Field as Field alias Table name
The table alias
select Field from Table name as Alias
as It can be omitted
stay mysql When inquiring , When the name of the table is long or some fields in the table are long , To facilitate writing or use the same table multiple times , You can set aliases for fields, columns, or tables
Convenient operation , enhance readability
When querying complex tables , Aliases can shorten query statements
When querying multiple tables ( Easy to understand 、 Shorten SQL sentence )
Nine . Connection statement
AS Can be used as a join statement
for example : create table info as select * from info1;
there AS What it does :
1、 Create a new table lucien, Define table structure , Insert table data ( And working The table is the same )
2、 however “ constraint " Has not been “ Copy ” To come over , But if the primary key is set in the original table , Then the attached table : default The field will be set to one by default 0
Ten . wildcard
Wildcards are mainly used to replace some characters in a string , Query the relevant results through the matching of some characters
Usually wildcards are and like( Fuzzy query ) Used together , And cooperate with where Clause to complete the query task
There are two common wildcards
%: The percent sign represents any number of characters
_: The underline represents a single character
11、 ... and . Subquery
Definition :
- Subqueries are also called inner queries or nested queries , It means that another query statement is nested in one query statement
- The sub query statement is executed before the main query statement , Seven results are returned to the main query as outer conditions for further query filtering
- The sub statement can be the same as the table queried by the subject sentence , It can be different
Example :
select id,name from info where hobby in (select hobby from info1 where hobby>3);
in Is the syntax that associates the main query with the sub query
Subquery :
example :
select name,hobby from ljm
The above command can query the of a table name and hobby Information
Take the result as “ surface ” When querying , We also need aliases
So let's say we type in :select hobby from (select name,hobby from info);
Will report a mistake , because select name,hobby from info What you get is a result set , Not a watch ,mysql Is unrecognizable
So we need to set an alias for the result set , such mysql You can treat the result set as a table .
Subquery (exists)
select count(*) as num from info where exists(select id from info where id>3);
as num take count The statistical results are taken as num return
exists Boolean judgment ,where The following statement holds , The Boolean value is 0, Then proceed to the previous operation , Otherwise, the return value is 0
When using sum Summation combination exists , If the query result is not valid , The return value is null
Twelve . View
- A view is a table derived from one or more tables , It's a virtual existence table
- Only the definition of view is stored in the database , Instead of storing the data in the view , The data is stored in the original table
- When using views to query data , The database system will take the corresponding data from the original table
- The data in the view depends on the data in the original table , Once the data in the table changes , The data displayed in the view will also change
- Virtual tables in the database , This virtual table does not contain real data , Just mapping
- deals / inverted reflection in water , Dynamically save the result set ( data )
effect :
- 1. Simplify the operation , You can define a view for frequently used queries , So that users do not have to specify conditions for the same query operation
- 2. Increase data security , Through view , Users can only query and modify the specified data
- 3. Improve the logical independence of tables , Views can mask the impact of changes in the original table structure
summary :
- In most cases of using views, in order to ensure data security , Improve query efficiency
- Create a virtual table according to the filter criteria , When modifying data in a table , When the data in the original table no longer meets the filter criteria , The table in the view will no longer display this data
13、 ... and .null value
null The difference between a value and a null value ( Air and vacuum )
- null, Does not occupy a space
- Empty characters and 0, Occupancy space
- is null Cannot determine null value
- Null use "=“ perhaps ”<>" To deal with it (!=)
- count() When calculating ,NULL Will ignore , Null values are added to the calculation
版权声明
本文为[C chord~]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231406064045.html
边栏推荐
- adobe illustrator 菜單中英文對照
- JUC learning record (2022.4.22)
- OPPO数据湖统一存储技术实践
- 我的树莓派 Raspberry Pi Zero 2W 折腾笔记,记录一些遇到的问题和解决办法
- 重定向和请求转发详解
- Async void caused the program to crash
- Tun model of flannel principle
- LeetCode153-寻找旋转排序数组中的最小值-数组-二分查找
- 大文件如何快速上传?
- Set onedrive or Google drive as a drawing bed in upic for free
猜你喜欢
What is the role of the full connection layer?
My raspberry PI zero 2W tossing notes record some problems encountered and solutions
How to upload large files quickly?
Share 20 tips for ES6 that should not be missed
Leetcode167 - sum of two numbers II - double pointer - bisection - array - Search
API gateway / API gateway (II) - use of Kong - load balancing
机器学习——逻辑回归
setcontext getcontext makecontext swapcontext
Design of digital temperature monitoring and alarm system based on DS18B20 single chip microcomputer [LCD1602 display + Proteus simulation + C program + paper + key setting, etc.]
Tencent has written a few words, Ali has written them all for a month
随机推荐
Hj31 word inversion
Leetcode149 - maximum number of points on a line - Math - hash table
tcp_ Diag kernel related implementation 1 call hierarchy
JS - implémenter la fonction de copie par clic
调度系统使用注意事项
C language super complete learning route (collection allows you to avoid detours)
Basic operation of sequential stack
asp. Net method of sending mail using mailmessage
LeetCode167-两数之和II-双指针-二分-数组-查找
Llvm - generate for loop
Comparaison du menu de l'illustrateur Adobe en chinois et en anglais
On the day of entry, I cried (mushroom street was laid off and fought for seven months to win the offer)
After time judgment of date
LeetCode162-寻找峰值-二分-数组
C language super complete learning route (collection allows you to avoid detours)
Redis主从同步
Use of common pod controller of kubernetes
async关键字
Nacos program connects to mysql8 0+ NullPointerException
我的树莓派 Raspberry Pi Zero 2W 折腾笔记,记录一些遇到的问题和解决办法