MySQL query is enough

chart 2020-11-09 22:18:39
mysql query


1. Conditions

Use where Clause to filter the data in the table , The result is true Lines will appear in the result set

  • The grammar is as follows :
select * from Table name where Conditions ;
example :
select * from students where id=1;
  • where A variety of operators are supported , To deal with the conditions
    • Comparison operator
    • Logical operators
    • Fuzzy query
    • Range queries
    • Empty judgment

Comparison operator

  • be equal to : =
  • Greater than : >
  • Greater than or equal to : >=
  • Less than : <
  • Less than or equal to : <=
  • It's not equal to : != or <>

example 1: The query number is greater than 3 Of the students

select * from students where id > 3;

example 2: The query number is not greater than 4 Of the students

select * from students where id <= 4;

example 3: It's not “ Huang Rong ” Of the students

select * from students where name != ' Huang Rong ';

example 4: Search for students who have not been deleted

select * from students where is_delete=0;

Logical operators

  • and
  • or
  • not

example 5: The query number is greater than 3 My female classmate

select * from students where id > 3 and gender=0;

example 6: The query number is less than 4 Or students who have not been deleted

select * from students where id < 4 or is_delete=0;

Fuzzy query

  • like
  • % Represents any number of arbitrary characters
  • _ Represents an arbitrary character

example 7: Look up the student surnamed Huang

select * from students where name like ' yellow %';

example 8: Check the surname Huang and “ name ” It's a student of one word

select * from students where name like ' yellow _';

example 9: Ask the students whose surname is Huang or Jing

select * from students where name like ' yellow %' or name like '% Jing ';

Range queries

  • in In a discontinuous range

example 10: The inquiry number is 1 or 3 or 8 Of the students

select * from students where id in(1,3,8);
  • between ... and ... In a continuous range

example 11: The query number is 3 to 8 Of the students

select * from students where id between 3 and 8;

example 12: The inquiry number is 3 to 8 Of boys

select * from students where (id between 3 and 8) and gender=1;

Empty judgment

  • Be careful :null And '' Is different
  • Sentenced to empty is null

example 13: Look up the students who didn't fill in the height

select * from students where height is null;
  • The verdict is not empty is not null

example 14: Look up the students who have filled in their height

select * from students where height is not null;

example 15: Look up the boys who fill in the height

select * from students where height is not null and gender=1;

priority

  • The order of priority from high to low is : parentheses ,not, Comparison operator , Logical operators
  • and Than or Calculate first , If it appears at the same time and wants to calculate first or, Need to combine () Use

2. Sort

For the convenience of viewing data , You can sort the data

grammar :
select * from Table name order by Column 1 asc|desc [, Column 2 asc|desc,...]
explain
  • Line data by column 1 Sort , If some ranks 1 When the values of are the same , According to the column 2 Sort , And so on
  • By default, columns are arranged from small to large (asc)
  • asc From smallest to largest , In ascending order
  • desc Sort from large to small , In descending order

example 1: Query not deleted boys information , In descending order of student number

select * from students where gender=1 and is_delete=0 order by id desc;

example 2: Show all student information , First, according to age --> Small order , At the same age From height to height --> Short sort

select * from students order by age desc,height desc;

3. Aggregate functions

In order to get statistics quickly , The following is often used 5 Aggregate functions

total

  • count(*) To calculate the total number of lines , Write stars and column names in brackets , The result is the same

example 1: Check the total number of students

select count(*) from students;

Maximum

  • max( Column ) To find the maximum value of this column

example 2: Query the maximum number of girls

select max(id) from students where gender=2;

minimum value

  • min( Column ) To find the minimum value of this column

example 3: Query the minimum number of students not deleted

select min(id) from students where is_delete=0;

Sum up

  • sum( Column ) Means to find the sum of this column

example 4: Query the total age of boys

select sum(age) from students where gender=1;
-- Average age
select sum(age)/count(*) from students where gender=1;

Average

  • avg( Column ) Means to find the average value of this column

example 5: Query the average number of undeleted girls

select avg(id) from students where is_delete=0 and gender=2;

4. grouping

group by

  1. group by The meaning of : Query results according to 1 One or more fields to group , The fields with the same value are a set of
  2. group by Can be used to group individual fields , It can also be used to group multiple fields

group by + group_concat()

  1. group_concat( Field name ) It can be used as an output field ,
  2. After the group , According to the grouping result , Use group_concat() To place a set of values for a field in each group
select gender from students group by gender;
+--------+
| gender |
+--------+
| male |
| Woman |
| Neutral |
| A secret |
+--------+
select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name) |
+--------+-----------------------------------------------------------+
| male | peng , Lau Andy , Jay Chou , Cheng Kun , Guo Jing |
| Woman | Xiao Ming , Little moon , Huang Rong , Joey wong , Liu Yifei , Jingxiang , Zhou Jie |
| Neutral | Venus |
| A secret | feng |
+--------+-----------------------------------------------------------+
select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| male | 3,4,8,9,14 |
| Woman | 1,2,5,7,10,12,13 |
| Neutral | 11 |
| A secret | 6 |
+--------+------------------+

group by + Set function

  1. adopt group_concat() Inspired by the , Now that we can count the set of values for a field in each group , So we can also do this with the set function Collection of values Do something
select gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age) |
+--------+----------------------+
| male | 29,59,36,27,12 |
| Woman | 18,18,38,18,25,12,34 |
| Neutral | 33 |
| A secret | 28 |
+--------+----------------------+
The gender is male / The average age of women
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| male | 32.6000 |
| Woman | 23.2857 |
| Neutral | 33.0000 |
| A secret | 28.0000 |
+--------+----------+
The gender is male / The number of women
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| male | 5 |
| Woman | 7 |
| Neutral | 1 |
| A secret | 1 |
+--------+----------+

group by + having

  1. having Conditional expression : Used to specify some conditions to output query results after grouping query
  2. having Function and where equally , but having It can only be used for group by
select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| male | 5 |
| Woman | 7 |
+--------+----------+

group by + with rollup

  1. with rollup The role of is : Add a new line at the end , To record the sum of all records in the current column
select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| male | 5 |
| Woman | 7 |
| Neutral | 1 |
| A secret | 1 |
| NULL | 14 |
+--------+----------+
select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age) |
+--------+-------------------------------------------+
| male | 29,59,36,27,12 |
| Woman | 18,18,38,18,25,12,34 |
| Neutral | 33 |
| A secret | 28 |
| NULL | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+

5. Get some lines

When the amount of data is too large , Looking at data on one page is a very cumbersome thing

grammar

select * from Table name limit start,count

explain

  • from start Start , obtain count Data

example 1: Before query 3 OK, boy information

select * from students where gender=1 limit 0,3;

Example : Pagination

  • It is known that : Each page shows m Data , The current display is n page
  • Find the total number of pages : This logic will be followed by python To realize
    • Total number of queries p1
    • Use p1 Divide m obtain p2
    • If you divide it, then p2 For the total number of pages
    • If you don't divide, then p2+1 Is the total number of pages
  • Please n Pages of data
select * from students where is_delete=0 limit (n-1)*m,m

When the columns of query results are from multiple tables , You need to join multiple tables into a large dataset , Select the appropriate column to return to

mysql Three types of join queries are supported , Respectively :

  • Internal connection query : The result of the query is the data matched by the two tables

  • Right connection query : The result of the query is the data matched by the two tables , Data unique to the right table , For data that does not exist in the left table, use null fill

  • Left connection query : The result of the query is the data matched by the two tables , Data unique to the left table , For data that does not exist in the right table, use null fill

grammar

select * from surface 1 inner or left or right join surface 2 on surface 1. Column = surface 2. Column

example 1: Use the inner link to query the class table and the student table

select * from students inner join classes on students.cls_id = classes.id;

example 2: Use the left link to query the class table and student table

  • Here we use as Name the table , The goal is to write simple
select * from students as s left join classes as c on s.cls_id = c.id;

example 3: Use the right link to query the class table and student table

select * from students as s right join classes as c on s.cls_id = c.id;

example 4: Query the names of students and classes

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
 

7. Subquery

Subquery

In a select In the sentence , Embedded in another select sentence , So embedded select Statements are called subquery statements

Main query

The main query object , Article 1 with a select sentence

The relationship between main query and sub query

  • Subqueries are embedded in the main query
  • The subquery is auxiliary to the main query , Or as a condition , Or act as a data source
  • Subqueries are statements that can exist independently , It's a complete select sentence

Sub query classification

  • Scalar subquery : The result returned by the subquery is a piece of data ( Line by line )
  • Column query : The result returned is a column ( A column with many lines )
  • Line sub query : The result is a line ( One row and many columns )

Scalar subquery

  1. Query the average age of class students
  2. Look for students older than average

Query the average height of class students

select * from students where age > (select avg(age) from students);

Column level subqueries

  • Query all class names of students in the class
    1. Find all the classes in the student list id
    2. Find the corresponding name in the class table
select name from classes where id in (select cls_id from students);

Row level subquery

  • demand : Find the oldest class , The tallest student
  • Row element : Synthesize multiple fields into a row element , Row elements... Are used in row level subqueries
select * from students where (height,age) = (select max(height),max(age) from students);

Specific keywords are used in subqueries

  • in Range
    • Format : Main query where Conditions in ( Column query )

summary

The full format of the query

SELECT select_expr [,select_expr,...] [
FROM tb_name
[WHERE conditional ]
[GROUP BY {col_name | postion} [ASC | DESC], ...]
[HAVING WHERE conditional ]
[ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
[ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]
  • complete select sentence
select distinct *
from Table name
where ....
group by ... having ...
order by ...
limit start,count
  • The order of execution is :
    • from Table name
    • where ....
    • group by ...
    • select distinct *
    • having ...
    • order by ...
    • limit start,count
  • In practice , It's just a combination of parts of a sentence , Not all of them

View

1. What is the view

Generally speaking , The view is just a line SELECT The result set returned after the statement is executed . So when we create views , The main job is to create this SQL On the query statement .

A view is a reference to several basic tables , A virtual table , Query the result of statement execution , Don't store specific data ( The basic table data has changed , The view changes as well );

Convenient operation , Especially query operations , Reduce the complexity of SQL sentence , enhance readability ;

2. Define views

It is suggested that v_ start

create view View name as select sentence ;

3. View view

The view table will list all the views as well

show tables;

4. Use view

The purpose of a view is to query

select * from v_stu_score;

5. Delete view

drop view View name ;
example :
drop view v_stu_sco;

6. The function of view

  1. Improved reusability , It's like a function
  2. Refactoring the database , It doesn't affect the operation of the program
  3. Improved security performance , It can be applied to different users
  4. Make the data clearer

Business

Transaction , It's a sequence of operations , These operations are either performed , Either not , It is an indivisible unit of work .

Four characteristics of transaction ( abbreviation ACID)

 

  • Atomicity (atomicity)

A transaction must be treated as an indivisible minimum unit of work , All operations in the entire transaction either all commit successfully , Either all failures roll back , For a transaction , It is impossible to perform only some of these operations , That's the atomicity of the transaction

  • Uniformity (consistency)

Databases are always moving from one consistent state to another .( In the previous example , Consistency ensures , Even in the execution of the third 、 The system crashes between four statements , There's no loss in checking accounts 200 dollar , Because the transaction didn't commit in the end , So the changes made in the transaction will not be saved to the database .)

  • Isolation, (isolation)

Generally speaking , Changes made by a transaction are made before the final submission , Is invisible to other transactions .( In the previous example , When the third statement is executed 、 Before the fourth sentence begins , At this point, another account aggregation program starts to run , The check account balance is not subtracted 200 dollar .)

  • persistence (durability)

Once the transaction is committed , Its changes will be permanently saved to the database .( Even if the system crashes , Modified data is not lost .)

Indexes

When there is a large amount of data in the database , Finding data can be slow

Optimization plan : Indexes

1. What is the index

Index is a special kind of file (InnoDB An index on a data table is an integral part of a table space ), They contain reference pointers to all records in the data table .

More generally speaking , A database index is like a table of contents at the front of a book , Can speed up the query speed of the database

2. Use of index

  • Look at the index
show index from Table name ;
  • Create index
    • If the specified field is a string , You need to specify the length , The recommended length is the same as the length when defining the field
    • If the field type is not a string , You can leave out the length section
create index The index name on Table name ( Field name ( length ))
  • Delete index :
drop index The index name on Table name ;
 
 
版权声明
本文为[chart]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云