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