• List is not in group by clause and contains nonaggre


    Causes of the problem: MySQL 5.7.5 and above functions rely on the detection function. If only is enabled_ FULL_ GROUP_ By SQL mode (by default), MySQL will reject queries that select lists, having conditions, or order by lists, refer to non collection columns that are neither named nor functionally dependent on them in the group […]

  • SQL with as usage


    1、 The meaning of with as With as phrase, also known as subquery factoring, can define an SQL fragment that will be used by the whole SQL statement. It can improve the readability of SQL statements, and it can also provide data in different parts of union all. For union all, a union all statement […]

  • Oracle indexing and SQL optimization


    Database index: The index has a single column indexOn compound index If a field of a table has a primary key constraint and a unique constraint, Oracle will automatically propose a unique index on the corresponding constraint column. Database index is mainly used to improve access speed. Construction principle: 1. Indexes should always be built […]

  • Improve 30 SQL query optimization methods for 10 million level MySQL data


    See what you use: 1. To optimize the query, we should try to avoid full table scanning. First, we should consider building indexes on the columns involved in where and order by. 2. Try to avoid judging null value of field in where clause, otherwise it will cause the engine to give up using index […]

  • 12800 words! SQL grammar quick manual (full of dry goods, recommended collection!)


    Author: silence and voidOriginal text: https://juejin.im/post/5c7e52… This paper focuses on the general syntax of relational database. Limited to space, this article focuses on the usage, will not expand to explain the characteristics, principles. 1、 Basic concepts Database terminology Database– a container (usually a file or group of files) that holds organized data. Data table– a […]

  • Oracle database optimization skills (1)


    Oracle database optimization skills (1) 1. Join order in where clause In Oracle database, the execution order of where clause isBottom upAccording to this principle, the connection between tables must be written before other where conditions, and those conditions that can filter out the maximum number of records must be written before other where conditions […]

  • About Oracle


    Common operations 1. View the creation table parameters Extract the complete DDL SELECT DBMS_METADATA.GET_DDL(‘TABLE’,’table_name’) FROM DUAL; 2. Specify the number of returned records select XXX from XXX where rownum 3. Query all the values of the specified column and display only a single record for each value Select needs to query the column name of […]

  • C + + exception capture and handling


    In reading projects developed by others, you may often see code that uses exceptions, and you may rarely see code that uses exception handling. When to use exception and when not to use exception? After learning the basic concept and grammar of exception, we will explain it later. (1) Exception throw and catch statements //1. […]

  • Statistical summary group merging of database


    When retrieving table data, it is often necessary to summarize or calculate the results, for example, to find the total score of a course in the student achievement database, to count the number of people in each score segment, and so on. 1. Statistical function Statistical functions are used to calculate the data in the […]

  • MySQL learning notes: combination index – leftmost principle


    Series articles: MySQL learning notes: combination index – leftmost principle MySQL learning notes: Composite Index – index push down MySQL learning notes: index failure new table CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_ Increment comment ‘Auto increment primary key’, `Uname ` varchar (100) not null default ‘comment’ name ‘, `Nickname ` varchar (255) […]

  • MySQL learning notes: index failure


    Series articles: MySQL learning notes: combination index – leftmost principle MySQL learning notes: Composite Index – index push down MySQL learning notes: index failure When type in explain is range and rows is too large: For the meaning of rows and the enumeration value of type, please refer to:EXPLAIN Output Format Query threshold example: General […]

  • Oracle exception handling


    In the process of statement execution, due to various reasons, the statement can not be executed normally, which may cause larger error or the whole system crash. Therefore, PS / SQL provides an exception handling method to prevent this kind of situation. In the process of code running, whenever an error occurs, PL / SQL […]