Aggregate query + union query + sub query
1, Aggregate query
1. Aggregate function
Common aggregate functions can be used to count the total number and calculate the average value. Common aggregate functions include:
These functions are relatively simple and can be understood through several SQL statements
count
-- How many students are there in the table
select count(*) f ...
Posted by lmg on Sun, 31 Oct 2021 17:23:15 -0700
Optimization of 20 SQL statements with twice the result with half the effort
Optimization of 20 SQL statements with twice the result with half the effort
1. Use the where condition to limit the data to be queried to avoid returning redundant rows
Suppose the business scenario is to query whether a user is a member
Before optimization:
List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1 ...
Posted by edking1 on Sun, 31 Oct 2021 13:51:16 -0700
MySQL database -- master-slave replication and read-write separation
preface
In the actual production environment, if the reading and writing of MySQL database are operated in one database service, it can not meet the actual needs in terms of security, high availability, high concurrency and so on. Generally speaking, it is through master-slave replication (master slave) to synchronize data, and then improv ...
Posted by genie on Sun, 31 Oct 2021 11:45:34 -0700
Application development based on python and MySQL database: student information score management system version 1.0
preface
python is an interpretative and object-oriented programming language, which can be applied to many life and work scenes to meet people's different needs. MySQL is an open source and free relational database, which is deeply trusted by users and enterprises. However, the establishment of database (or table) and the use of operati ...
Posted by Plakhotnik on Sun, 31 Oct 2021 10:55:40 -0700
Niu Ke SQL question brushing record
summary
on must be used with join
SQL1 finds all the information of the latest employee
select*from employees order by hire_date desc limit 1;
select * from employees
where hire_date = (select max(hire_date) from employees);
ORDER BY sorts the result set according to the specified column. By default, ORDER BY DESC is in ascending ...
Posted by easmith on Sun, 31 Oct 2021 06:13:42 -0700
Database class notes 11 (slow query log)
analysis
● 1. Observe and run for at least one day to see the slow production ● 2. Start the slow query log, set the threshold value, for example, slow SQL is more than 5 seconds, and grab it ● 3.explain + slow SQL analysis ● 4.show profile ● 5. Optimize the parameters of SQL database server (O & M or DBA)
summary
● 1. Enable and capture ...
Posted by sw9 on Sat, 30 Oct 2021 20:30:39 -0700
Read only instance (slave master-slave) delay troubleshooting
The following methods are applicable to real-time viewing of read-only delay (master-slave delay), that is, the problem can be confirmed only when the delay occurs. Historical delay is not applicable. Parallel replication has been enabled in the following environments.
Chapter 1: how to judge no delay Step 1: get the master instance informatio ...
Posted by JoeyT2007 on Sat, 30 Oct 2021 10:37:38 -0700
MySQL incremental backup and recovery
introduction
Full backup is to back up all data contents. There are a lot of duplicate data in the backup data, and the time of full backup and recovery is very long. The solution to the problem of full backup is to use incremental backup. Incremental backup is to back up the files or contents added or changed since the last backup.
1 ...
Posted by pristanski on Sat, 30 Oct 2021 04:36:06 -0700
mysql database advanced statement - medium
catalogue
1, Regular expression
1. Introduction
2. Common matching types
3. Query mode
2, Operator
1. Arithmetic operator
2. Comparison operator
3. Logical operator (Boolean)
4. Bitwise operator
3, Connection query
1. Inner connection
2. Left connection
3. Right connection
summary
1, Regular expression
1. Introduc ...
Posted by candy2126 on Sat, 30 Oct 2021 02:33:32 -0700
Database design a circle of friends
The structure of the circle of friends really gives me a headache - how to implement it in a multi-layer architecture and how to write the script; And the ghost virtual machine, kind of learning is not good at self castration. Do it from the beginning, then.
Each image has its own URL: uniform resource location address protocol: / / hostname [ ...
Posted by ginginca on Sat, 30 Oct 2021 00:02:40 -0700