CN111813799A - Database query statement generation method and device, computer equipment and storage medium - Google Patents
Database query statement generation method and device, computer equipment and storage medium Download PDFInfo
- Publication number
- CN111813799A CN111813799A CN202010719022.9A CN202010719022A CN111813799A CN 111813799 A CN111813799 A CN 111813799A CN 202010719022 A CN202010719022 A CN 202010719022A CN 111813799 A CN111813799 A CN 111813799A
- Authority
- CN
- China
- Prior art keywords
- query
- sub
- database
- main
- information
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 43
- 238000012216 screening Methods 0.000 claims abstract description 20
- 230000000007 visual effect Effects 0.000 claims abstract description 17
- 238000004590 computer program Methods 0.000 claims description 11
- 238000010276 construction Methods 0.000 abstract description 2
- 238000010586 diagram Methods 0.000 description 15
- 238000012790 confirmation Methods 0.000 description 3
- 238000001514 detection method Methods 0.000 description 3
- 238000012545 processing Methods 0.000 description 3
- 230000000694 effects Effects 0.000 description 2
- 230000001360 synchronised effect Effects 0.000 description 2
- 238000004458 analytical method Methods 0.000 description 1
- 238000013459 approach Methods 0.000 description 1
- 238000004364 calculation method Methods 0.000 description 1
- 230000002596 correlated effect Effects 0.000 description 1
- 230000000875 corresponding effect Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000005457 optimization Methods 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Software Systems (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application relates to a database query statement generation method, a database query statement generation device, computer equipment and a storage medium. The method comprises the following steps: acquiring query demand information; screening a related data chart from a database according to the query demand information, and analyzing to obtain a demand field; associating a plurality of data charts to complete visual modeling and generate a view model; and configuring a requirement field for the view model and then generating a database query statement. By adopting the method, the fields to be queried can be listed from the required query result, then the visual data source editing operation is established for each query field for visual construction, the user can quickly and accurately generate the query sentence with a complex structure, the incidence relation between the data sources at all levels and the data source at the same level in the view model can be intuitively known, and thus the database query sentence is generated, and the query result is verified.
Description
Technical Field
The present application relates to the field of database application technologies, and in particular, to a method and an apparatus for generating a database query statement, a computer device, and a storage medium.
Background
The current enterprise information data is gradually formed, but the data is dispersed in a network due to inconsistent information time and execution strength, the data forms are inconsistent, the dispersed data are often associated with each other to a certain extent, the data are simply stored if isolated, the stored information is basically complete, and the application often needs to conveniently, efficiently and reliably access the relatively independent and associated data.
To solve the above problems, the existing method is to use a database view designer or write SQL query statements and then call them when a user calls a database statement or view. However, such approaches tend to be time consuming and inefficient when writing complex query statements, and are limited in that the view designer cannot refer to complex query statements. Therefore, to improve the performance of database systems, optimization of queries is essential.
Disclosure of Invention
In view of the foregoing, it is desirable to provide a database query statement generation method, apparatus, computer device, and storage medium, which can improve the generation efficiency of a database query statement.
A database query statement generation method comprises the following steps:
acquiring query demand information;
screening a related data chart from a database according to the query demand information, and analyzing to obtain a demand field;
associating a plurality of data charts to complete visual modeling and generate a view model;
and configuring a requirement field for the view model and then generating a database query statement.
As an implementation manner, the screening the relevant data chart from the database according to the query requirement information includes the following steps:
analyzing the query demand information to obtain main query information and sub-query information;
and screening a relevant data chart from a database according to the main query information and the sub query information.
As an implementation mode, the visual modeling generation view model is completed by associating a plurality of data charts, and comprises the following steps:
establishing a main query according to the data chart screened by the main query information;
establishing a sub-query according to the data chart screened by the sub-query information;
and associating the main query and the sub-query to generate a view model.
As an implementation manner, the building of the sub-query according to the data graph screened by the sub-query information includes the following steps:
and if the number of the sub-queries is multiple, associating the multiple sub-queries.
As an implementation, the method further comprises the following steps:
judging whether the sub-query needs to establish a lower sub-query;
if so, establishing a lower-level sub-query, and associating the lower-level sub-query with the sub-query and the main query.
As an implementation manner, the association includes a query manner, the query manner includes a connection manner, a group query manner, and a combined query manner, and the connection manner includes one or more of a left connection, a right connection, and a full connection.
As an implementation mode, the database query statement is generated after the requirement field is configured for the view model, wherein the requirement field comprises a fixed field and/or a custom field, and the custom field is manually edited under the condition that no relevant requirement field exists in the designer sample library.
A database query statement generation device comprises a demand acquisition module, a data table screening module, a view modeling module and a query statement generation module;
the demand acquisition module is used for acquiring inquiry demand information;
the data table screening module is used for screening a relevant data chart from a database according to the query demand information and analyzing to obtain a demand field;
the view modeling module is used for associating a plurality of data tables to complete visual modeling and generate a view model;
and the query statement generation module is used for configuring a requirement field for the view model and then generating a database query statement.
A computer device comprising a memory and a processor, the memory storing a computer program, the processor implementing the following steps when executing the computer program:
acquiring query demand information;
screening a related data chart from a database according to the query demand information, and analyzing to obtain a demand field;
associating a plurality of data charts to complete visual modeling and generate a view model;
and configuring a requirement field for the view model and then generating a database query statement.
A computer-readable storage medium having stored thereon a computer program, the computer program being executed by a processor for:
acquiring query demand information;
screening a related data chart from a database according to the query demand information, and analyzing to obtain a demand field;
associating a plurality of data charts to complete visual modeling and generate a view model;
and configuring a requirement field for the view model and then generating a database query statement.
According to the method, the device, the computer equipment and the storage medium for generating the database query statement, the fields needing to be queried can be listed from the needed query result, then the visual data source editing operation is established for each query field to carry out visual construction, a user can quickly and accurately generate the query (SQL) statement with a complex structure, the incidence relation between each level of data source and the same level of data source in the view model can be intuitively known, the database query statement is generated, and the query result is verified.
Drawings
FIG. 1 is a diagram of an application environment in which a method for generating a database query statement according to an embodiment is implemented;
FIG. 2 is a flowchart illustrating a method for generating a database query statement in one embodiment;
FIG. 3 is a diagram of database table associations in one embodiment;
FIG. 4 is an embodiment of an item file;
FIG. 5 is a warehousing entry header in one embodiment;
FIG. 6 is a warehouse entry list table in one embodiment;
FIG. 7 is a delivery list header in one embodiment;
FIG. 8 is a listing of outbound documents in one embodiment;
FIG. 9 is a view of a view designer in one embodiment;
FIG. 10 is a database form in one embodiment;
FIG. 11 is a diagram of creating a master query in one embodiment;
FIG. 12 is a diagram that illustrates the establishment of a warehousing sub-query and an ex-warehouse sub-query, in one embodiment;
FIG. 13 is a diagram that illustrates adding data sources for in-warehouse sub-queries and out-warehouse sub-queries, under an embodiment;
FIG. 14 is a diagram that illustrates the establishment of associations for in-warehouse sub-queries and out-warehouse sub-queries to a base database table according to database table associations, under an embodiment;
FIG. 15 is a diagram of configuring fields for binned sub-queries in one embodiment;
FIG. 16 is a diagram that illustrates configuring fields for ex-warehouse sub-queries, in one embodiment;
FIG. 17 is a diagram that illustrates associating a main query with a sub-query, in one embodiment;
FIG. 18 is a selection view for a field in one embodiment;
FIG. 19 is a diagram of a configuration of custom fields in one embodiment;
FIG. 20 is a diagram that illustrates configuring fields for associated main queries and sub-queries, in one embodiment;
FIG. 21 is a diagram of a database query statement that is generated in one embodiment;
FIG. 22 is a diagram that previews effects for data in one embodiment;
fig. 23 is a block diagram showing the configuration of a database query statement generation apparatus according to an embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
The database query statement generation method provided by the application can be applied to the application environment shown in fig. 1. Wherein the terminal 102 communicates with the server 104 via a network. The user can acquire or input the depth image and the infrared image containing the face object through the terminal 102, and then the depth image and the infrared image are transmitted to the server 104 through the network for face living body detection. The terminal 102 may be, but is not limited to, various personal computers, notebook computers, smart phones, tablet computers, and portable wearable devices. The server 104 may perform living body detection according to the depth image and the infrared image containing the human face object acquired or input by the terminal 102, and output a living body detection result. The server 104 is implemented as a stand-alone server or a server cluster composed of a plurality of servers.
In an embodiment, as shown in fig. 2, a database query statement generation method is provided, which is described by taking the application of the method to the terminal in fig. 1 as an example, and includes the following steps:
s100: acquiring query demand information; in this embodiment, the query requirement table sample is submitted for the user.
S200: screening a related data chart from a database according to the query demand information, and analyzing to obtain a demand field; analyzing the submitted query requirement table sample, determining a relevant data chart and a requirement field required by query, wherein the data chart comprises a database table and a view, confirming the query mode of the database table or the view, and starting configuration through a view designer after confirmation.
S300: associating a plurality of data charts to complete visual modeling and generate a view model; and configuring query modes and incidence relation fields for the screened database table or view in a view designer.
S400: and configuring a requirement field for the view model and then generating a database query statement.
Specifically, in step S200, the query requirement information is analyzed to obtain main query information and sub query information, and the relevant data graph is screened from the database according to the main query information and the sub query information.
Specifically, in step S300, a main query and a sub-query are established according to the data graph screened by the main query information;
and associating the main query and the sub-query to generate a view model.
In one embodiment, step S300 further includes the following steps:
judging whether a subordinate sub-query needs to be established or not;
if so, establishing a lower-level sub-query, and associating the lower-level sub-query with the sub-query and the main query to generate a view model.
Wherein, the main query process is established as follows: and establishing the main query by taking the database table or view required by the main query as a main table. If other database tables or views are needed for establishing the main query, the other database tables or views are used as the auxiliary table, and meanwhile, the auxiliary table is associated with the main table. In this embodiment, the association relationship is a left connection mode through the main key and the external key. However, other association relations can be selected according to requirements, and in the application, the left connection mode through the main key and the outer key is not limited to be adopted, and a right connection mode or a full connection mode can be also adopted.
Building sub-query procedure as the main query procedure described above, a sub-query is first built with the database table or view required by the main query as the main table. If other database tables or views are needed for establishing the sub-query, the sub-query is used as a secondary table, and meanwhile, the secondary table is associated with the main table. In this embodiment, the main table and the sub table of the sub query are also associated by the left-link connection mode of the main key and the foreign key.
The association includes both a connection relationship of the database table or the view and a data processing relationship of the database table or the view, and therefore, in this embodiment, the association includes a query manner, which includes a connection manner, a grouping query manner, and a merging query manner. The connection mode is to perform associative connection (one or more combinations of left link, right link and full link) on a database table or view, the grouping query mode is to summarize data according to a grouping (one or more sub-queries are a grouping, in this embodiment, one sub-query), and the merging query mode is to generate a more detailed data detail table, inventory details, and the like.
In another embodiment, if there are multiple sub-queries, then the multiple sub-queries are associated. And if the sub-query is the same-level sub-query, associating the same-level sub-query with the main query. If the sub-queries are subordinate sub-queries, then associating one or more subordinate sub-queries with their associated sub-queries. Whether the query is a peer sub-query or a lower sub-query, after the query is associated, the query needs to be associated with the main query for establishing the view model. However, the main query only establishes a relationship with the first-level sub-query, the first-level sub-query establishes a relationship with the lower-level sub-query, and the relationship is not established across levels.
In another embodiment, after the view model is established, the method further comprises the following steps:
the requirement fields are configured for the view model, and include fixed fields and/or custom fields. Wherein custom fields are manually edited and added without an associated requirement field in the designer sample library.
The establishing of the association comprises the association of the sub-query and the association of the main query and the sub-query. Therefore, after the sub-queries establish the association, the related fields are configured for the sub-queries; and after the main query and the sub query are associated, configuring relevant fields for the main query and the sub query.
In summary, the database query statement generation method includes acquiring the relevant data chart and the requirement field by analyzing the query requirement information, after acquiring the analysis information, firstly establishing the main query and the sub-query, and simultaneously selecting the connection mode to generate the view model, and then generating the database query statement by configuring the requirement.
In order to make the aforementioned objects, features and advantages of the present application more comprehensible, the present application is described in further detail with reference to the first table, the accompanying fig. 3 and the specific examples.
As shown in the following table one, a user provides a query requirement table sample which requires to realize inventory receiving, dispatching and storage query;
Analyzing the submitted query requirement table sample, determining a database table or view required by the query, simultaneously obtaining a requirement field comprising an initial quantity and a current warehousing quantity, and designing a database table association relation according to the found database table or view as shown in FIG. 3;
finding five related database tables in the database, and entering a designer by using the five database tables to establish a view model, wherein the five database tables are respectively shown in FIGS. 4-8;
as shown in fig. 9, an item transceive inventory query view model is created and entered into the designer;
as shown in fig. 10-11, a primary query is established for the view model by selecting a base table object archive Ck001 according to the database table association;
selecting a basic data table for the warehousing sub-query and the ex-warehousing sub-query according to the database table association relation, wherein the database table of the warehousing sub-query is as follows: the warehousing main table is Ck004, and the detail table is Ck 005; the database table of the ex-warehouse sub-query is an ex-warehouse main table Ck006, and the ex-warehouse detail table is Ck 007;
as shown in fig. 12-14, an association relationship is established for the warehousing sub-query and the ex-warehouse sub-query to the base data table according to the database table association relationship. The association modes are left-connection modes, and the warehousing relation is Ck004.GUID ═ Ck005.Ck004_ GUID. The ex-warehouse relationship is Ck006.GUID is Ck007.Ck006_ GUID;
as shown in fig. 15-16, fields are configured for warehousing and ex-warehouse sub-queries, after a left-hand relation is established for warehousing sub-queries, the head of the warehousing entry list and the detailed fields of the warehousing entry list enter a selection range, and a field ck005.ck001_ Guid to be grouped, a newly-defined calculation field initial quantity and the current-stage warehousing entry are selected; after the outbound sub-query establishes the left-hand relation, the head of the outbound list and the detailed fields of the outbound list enter a selection range, and the fields needing grouping Ck007.Ck001_ Guid, the newly-defined initial number of the calculated fields and the outbound of the current period are selected;
as shown in fig. 17, an association relationship is established between the main query and the warehousing sub-query and the ex-warehouse sub-query. The connection mode is a left connection mode, and Ck001.Guid is warehouse-in, Ck001_ Guid and Ck001.Guid is warehouse-out, Ck001_ Guid;
as shown in fig. 18-20, after the main query and the sub-query are correlated, all fields in the main query, the warehousing sub-query and the ex-warehouse sub-query enter the selection range, the ID, the article name, the unit and the warehouse location in the required Ck001 are selected, a custom field is created, the number of initials in the warehousing sub-query and the ex-warehouse sub-query is selected to calculate the number of initials, the warehousing number in the warehousing sub-query is selected, the ex-warehouse number in the ex-warehouse sub-query is used as the number of occurrences of the current period, and the number of initials for warehousing, the number of initials for ex-warehouse, the warehousing number of ex-warehouse and the ex-warehouse are used for the newly created custom field to calculate the number of initials for;
as shown in fig. 21, a database query statement is generated for the user to invoke;
as shown in fig. 22, the query effect is previewed according to the generated database query statement, and whether the database query statement is expected (i.e. the relevant content of table one) can be determined according to the preview.
It should be understood that, although the steps in the flowchart of fig. 2 are shown in order as indicated by the arrows, the steps are not necessarily performed in order as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a portion of the steps in fig. 2 may include multiple sub-steps or multiple stages that are not necessarily performed at the same time, but may be performed at different times, and the order of performance of the sub-steps or stages is not necessarily sequential, but may be performed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.
In one embodiment, as shown in fig. 23, a database query statement generation apparatus is provided, which includes a requirement obtaining module 100, a data table screening module 200, a view modeling module 300, and a query statement generation module 400.
The requirement obtaining module 100 is configured to obtain query requirement information; the data table screening module 200 is configured to screen a relevant data table from a database according to the query requirement information, and analyze the relevant data table to obtain a requirement field; the view modeling module 300 is used for associating a plurality of data tables to complete visual modeling and generate a view model; the query statement generation module 400 is configured to configure the requirement field for the view model and then generate a database query statement.
For specific limitations of the database query statement generation apparatus, reference may be made to the above limitations on the database query statement generation method, which is not described herein again. The modules in the database query statement generation device may be wholly or partially implemented by software, hardware and a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.
In one embodiment, a computer device is provided, comprising a memory and a processor, the memory storing a computer program, characterized in that the processor realizes the following steps when executing the computer program.
S100: acquiring query demand information; in this embodiment, the query requirement table sample is submitted for the user.
S200: screening a related data chart from a database according to the query demand information, and analyzing to obtain a demand field; analyzing the submitted query requirement table sample, determining a relevant data chart and a requirement field required by query, wherein the data chart comprises a database table and a view, confirming the query mode of the database table or the view, and starting configuration through a view designer after confirmation.
S300: associating a plurality of data charts to complete visual modeling and generate a view model; and configuring query modes and incidence relation fields for the screened database table or view in a view designer.
S400: and configuring a requirement field for the view model and then generating a database query statement.
Specifically, in step S200, the query requirement information is analyzed to obtain main query information and sub query information, and the relevant data graph is screened from the database according to the main query information and the sub query information.
Specifically, in step S300, a main query and a sub-query are established according to the data graph screened by the main query information;
and associating the main query and the sub-query to generate a view model.
In one embodiment, step S300 further includes the following steps:
judging whether a subordinate sub-query needs to be established or not;
if so, establishing a lower-level sub-query, and associating the lower-level sub-query with the sub-query and the main query to generate a view model.
Wherein, the main query process is established as follows: and establishing the main query by taking the database table or view required by the main query as a main table. If other database tables or views are needed for establishing the main query, the other database tables or views are used as the auxiliary table, and meanwhile, the auxiliary table is associated with the main table. In this embodiment, the association relationship is a left connection mode through the main key and the external key. However, other association relations can be selected according to requirements, and in the application, the left connection mode through the main key and the outer key is not limited to be adopted, and a right connection mode or a full connection mode can be also adopted.
Building sub-query procedure as the main query procedure described above, a sub-query is first built with the database table or view required by the main query as the main table. If other database tables or views are needed for establishing the sub-query, the sub-query is used as a secondary table, and meanwhile, the secondary table is associated with the main table. In this embodiment, the main table and the sub table of the sub query are also associated by the left-link connection mode of the main key and the foreign key.
The association includes both a connection relationship of the database table or the view and a data processing relationship of the database table or the view, and therefore, in this embodiment, the association includes a query manner, which includes a connection manner, a grouping query manner, and a merging query manner. The connection mode is to perform associative connection (one or more combinations of left link, right link and full link) on a database table or view, the grouping query mode is to summarize data according to a grouping (one or more sub-queries are a grouping, in this embodiment, one sub-query), and the merging query mode is to generate a more detailed data detail table, inventory details, and the like.
In another embodiment, if there are multiple sub-queries, then the multiple sub-queries are associated. And if the sub-query is the same-level sub-query, associating the same-level sub-query with the main query. If the sub-queries are subordinate sub-queries, then associating one or more subordinate sub-queries with their associated sub-queries. Whether the query is a peer sub-query or a lower sub-query, after the query is associated, the query needs to be associated with the main query for establishing the view model. However, the main query only establishes a relationship with the first-level sub-query, the first-level sub-query establishes a relationship with the lower-level sub-query, and the relationship is not established across levels.
In another embodiment, after the view model is established, the method further comprises the following steps:
the requirement fields are configured for the view model, and include fixed fields and/or custom fields. Wherein custom fields are manually edited and added without an associated requirement field in the designer sample library.
The establishing of the association comprises the association of the sub-query and the association of the main query and the sub-query. Therefore, after the sub-queries establish the association, the related fields are configured for the sub-queries; and after the main query and the sub query are associated, configuring relevant fields for the main query and the sub query.
Those skilled in the art will appreciate that the architecture shown in fig. 22 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
In one embodiment, a computer-readable storage medium is provided, having a computer program stored thereon, which when executed by a processor, performs the steps of:
s100: acquiring query demand information; in this embodiment, the query requirement table sample is submitted for the user.
S200: screening a related data chart from a database according to the query demand information, and analyzing to obtain a demand field; analyzing the submitted query requirement table sample, determining a relevant data chart and a requirement field required by query, wherein the data chart comprises a database table and a view, confirming the query mode of the database table or the view, and starting configuration through a view designer after confirmation.
S300: associating a plurality of data charts to complete visual modeling and generate a view model; and configuring query modes and incidence relation fields for the screened database table or view in a view designer.
S400: and configuring a requirement field for the view model and then generating a database query statement.
Specifically, in step S200, the query requirement information is analyzed to obtain main query information and sub query information, and the relevant data graph is screened from the database according to the main query information and the sub query information.
Specifically, in step S300, a main query and a sub-query are established according to the data graph screened by the main query information;
and associating the main query and the sub-query to generate a view model.
In one embodiment, step S300 further includes the following steps:
judging whether a subordinate sub-query needs to be established or not;
if so, establishing a lower-level sub-query, and associating the lower-level sub-query with the sub-query and the main query to generate a view model.
Wherein, the main query process is established as follows: and establishing the main query by taking the database table or view required by the main query as a main table. If other database tables or views are needed for establishing the main query, the other database tables or views are used as the auxiliary table, and meanwhile, the auxiliary table is associated with the main table. In this embodiment, the association relationship is a left connection mode through the main key and the external key. However, other association relations can be selected according to requirements, and in the application, the left connection mode through the main key and the outer key is not limited to be adopted, and a right connection mode or a full connection mode can be also adopted.
Building sub-query procedure as the main query procedure described above, a sub-query is first built with the database table or view required by the main query as the main table. If other database tables or views are needed for establishing the sub-query, the sub-query is used as a secondary table, and meanwhile, the secondary table is associated with the main table. In this embodiment, the main table and the sub table of the sub query are also associated by the left-link connection mode of the main key and the foreign key.
The association includes both a connection relationship of the database table or the view and a data processing relationship of the database table or the view, and therefore, in this embodiment, the association includes a query manner, which includes a connection manner, a grouping query manner, and a merging query manner. The connection mode is to perform associative connection (one or more combinations of left link, right link and full link) on a database table or view, the grouping query mode is to summarize data according to a grouping (one or more sub-queries are a grouping, in this embodiment, one sub-query), and the merging query mode is to generate a more detailed data detail table, inventory details, and the like.
In another embodiment, if there are multiple sub-queries, then the multiple sub-queries are associated. And if the sub-query is the same-level sub-query, associating the same-level sub-query with the main query. If the sub-queries are subordinate sub-queries, then associating one or more subordinate sub-queries with their associated sub-queries. Whether the query is a peer sub-query or a lower sub-query, after the query is associated, the query needs to be associated with the main query for establishing the view model. However, the main query only establishes a relationship with the first-level sub-query, the first-level sub-query establishes a relationship with the lower-level sub-query, and the relationship is not established across levels.
In another embodiment, after the view model is established, the method further comprises the following steps:
the requirement fields are configured for the view model, and include fixed fields and/or custom fields. Wherein custom fields are manually edited and added without an associated requirement field in the designer sample library.
The establishing of the association comprises the association of the sub-query and the association of the main query and the sub-query. Therefore, after the sub-queries establish the association, the related fields are configured for the sub-queries; and after the main query and the sub query are associated, configuring relevant fields for the main query and the sub query.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in the embodiments provided herein may include non-volatile and/or volatile memory, among others. Non-volatile memory can include read-only memory (ROM), Programmable ROM (PROM), Electrically Programmable ROM (EPROM), Electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), Dynamic RAM (DRAM), Synchronous DRAM (SDRAM), Double Data Rate SDRAM (DDRSDRAM), Enhanced SDRAM (ESDRAM), Synchronous Link DRAM (SLDRAM), Rambus Direct RAM (RDRAM), direct bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).
The technical features of the above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the invention. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present patent shall be subject to the appended claims.
Claims (10)
1. A database query statement generation method is characterized by comprising the following steps:
acquiring query demand information;
screening a related data chart from a database according to the query demand information, and analyzing to obtain a demand field;
associating a plurality of data charts to complete visual modeling and generate a view model;
and configuring a requirement field for the view model and then generating a database query statement.
2. The method for generating the database query statement according to claim 1, wherein the step of screening the relevant data charts from the database according to the query requirement information comprises the following steps:
analyzing the query demand information to obtain main query information and sub-query information;
and screening a relevant data chart from a database according to the main query information and the sub query information.
3. The database query statement generation method according to claim 2, wherein the associating of the plurality of data charts through visual modeling to generate a view model comprises the steps of:
establishing a main query according to the data chart screened by the main query information;
establishing a sub-query according to the data chart screened by the sub-query information;
and associating the main query and the sub-query to generate a view model.
4. The method for generating the query statement of database according to claim 3, wherein the step of building the sub-query based on the data graph screened by the sub-query information comprises the steps of:
and if the number of the sub-queries is multiple, associating the multiple sub-queries.
5. The database query statement generation method according to claim 2, further comprising the steps of:
judging whether the sub-query needs to establish a lower sub-query;
if so, establishing a lower-level sub-query, and associating the lower-level sub-query with the sub-query and the main query.
6. The method according to claim 4, wherein the association includes a query manner, the query manner includes a connection manner, a group query manner, and a combined query manner, and the connection manner includes one or more of a left-hand connection, a right-hand connection, and a full-joint connection.
7. The method according to claim 1, wherein the database query statement is generated after configuring the requirement field for the view model, wherein the requirement field comprises a fixed field and/or a custom field, and the custom field is manually edited when there is no related requirement field in the designer sample library.
8. A database query statement generation device is characterized by comprising a demand acquisition module, a data table screening module, a view modeling module and a query statement generation module;
the demand acquisition module is used for acquiring inquiry demand information;
the data table screening module is used for screening a relevant data chart from a database according to the query demand information and analyzing to obtain a demand field;
the view modeling module is used for associating a plurality of data tables to complete visual modeling and generate a view model;
and the query statement generation module is used for configuring a requirement field for the view model and then generating a database query statement.
9. A computer device comprising a memory and a processor, the memory storing a computer program, wherein the processor implements the steps of the method of any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method of any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010719022.9A CN111813799B (en) | 2020-07-23 | 2020-07-23 | Database query statement generation method, device, computer equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010719022.9A CN111813799B (en) | 2020-07-23 | 2020-07-23 | Database query statement generation method, device, computer equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111813799A true CN111813799A (en) | 2020-10-23 |
| CN111813799B CN111813799B (en) | 2024-01-19 |
Family
ID=72862570
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010719022.9A Active CN111813799B (en) | 2020-07-23 | 2020-07-23 | Database query statement generation method, device, computer equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111813799B (en) |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112632037A (en) * | 2020-12-24 | 2021-04-09 | 山东浪潮通软信息科技有限公司 | Method and device for graphically defining query data set |
| CN112799659A (en) * | 2021-01-12 | 2021-05-14 | 杨飞 | Method, device and terminal for automatically generating data interface without programming |
| CN113468208A (en) * | 2021-07-19 | 2021-10-01 | 网易（杭州）网络有限公司 | Method and device for generating data query statement, server and storage medium |
| CN113836149A (en) * | 2021-11-29 | 2021-12-24 | 深圳市明源云科技有限公司 | Enterprise data query method, enterprise data query device, terminal and computer readable storage medium |
| CN114238378A (en) * | 2021-12-17 | 2022-03-25 | 平安证券股份有限公司 | Query method, device, computer equipment and medium based on SQL query engine |
| WO2024066094A1 (en) * | 2022-09-27 | 2024-04-04 | 北京柏睿数据技术股份有限公司 | Cross-data-source visual construction method and system for database view |
Citations (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102257494A (en) * | 2008-12-22 | 2011-11-23 | 北方电讯网络有限公司 | Selective database replication |
| CN103093000A (en) * | 2013-02-25 | 2013-05-08 | 用友软件股份有限公司 | Database query modeling system and database query modeling method |
| US20140019473A1 (en) * | 2012-07-13 | 2014-01-16 | Sap Ag | Database View Modeling Using Existing Data Model |
| CN107203640A (en) * | 2017-06-14 | 2017-09-26 | 成都四方伟业软件股份有限公司 | The method and system of physical model are set up by database log |
| CN107391739A (en) * | 2017-08-07 | 2017-11-24 | 北京奇艺世纪科技有限公司 | A kind of query statement generation method, device and electronic equipment |
| US20180025058A1 (en) * | 2016-07-19 | 2018-01-25 | TmaxData Co., Ltd. | Technique for processing query in database management system |
| CN109840257A (en) * | 2018-12-15 | 2019-06-04 | 中国平安人寿保险股份有限公司 | Data base query method, device, computer installation and readable storage medium storing program for executing |
| CN110008232A (en) * | 2019-04-11 | 2019-07-12 | 北京启迪区块链科技发展有限公司 | Generation method, device, server and the medium of structured query sentence |
| CN110019555A (en) * | 2017-12-26 | 2019-07-16 | 中国科学院沈阳自动化研究所 | A kind of relation data semantization modeling method |
| CN110321344A (en) * | 2019-05-20 | 2019-10-11 | 平安普惠企业管理有限公司 | Information query method, device, computer equipment and the storage medium of associated data |
| CN110413605A (en) * | 2018-04-26 | 2019-11-05 | 中移(苏州)软件技术有限公司 | A kind of method and apparatus of data visualization |
-
2020
- 2020-07-23 CN CN202010719022.9A patent/CN111813799B/en active Active
Patent Citations (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102257494A (en) * | 2008-12-22 | 2011-11-23 | 北方电讯网络有限公司 | Selective database replication |
| US20140019473A1 (en) * | 2012-07-13 | 2014-01-16 | Sap Ag | Database View Modeling Using Existing Data Model |
| CN103093000A (en) * | 2013-02-25 | 2013-05-08 | 用友软件股份有限公司 | Database query modeling system and database query modeling method |
| US20180025058A1 (en) * | 2016-07-19 | 2018-01-25 | TmaxData Co., Ltd. | Technique for processing query in database management system |
| CN107203640A (en) * | 2017-06-14 | 2017-09-26 | 成都四方伟业软件股份有限公司 | The method and system of physical model are set up by database log |
| CN107391739A (en) * | 2017-08-07 | 2017-11-24 | 北京奇艺世纪科技有限公司 | A kind of query statement generation method, device and electronic equipment |
| CN110019555A (en) * | 2017-12-26 | 2019-07-16 | 中国科学院沈阳自动化研究所 | A kind of relation data semantization modeling method |
| CN110413605A (en) * | 2018-04-26 | 2019-11-05 | 中移(苏州)软件技术有限公司 | A kind of method and apparatus of data visualization |
| CN109840257A (en) * | 2018-12-15 | 2019-06-04 | 中国平安人寿保险股份有限公司 | Data base query method, device, computer installation and readable storage medium storing program for executing |
| CN110008232A (en) * | 2019-04-11 | 2019-07-12 | 北京启迪区块链科技发展有限公司 | Generation method, device, server and the medium of structured query sentence |
| CN110321344A (en) * | 2019-05-20 | 2019-10-11 | 平安普惠企业管理有限公司 | Information query method, device, computer equipment and the storage medium of associated data |
Non-Patent Citations (1)
| Title |
|---|
| 李莉娇;: "基于EPDM模型的数据查询与编辑系统的设计及应用", 中国管理信息化, no. 21, pages 44 - 47 * |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112632037A (en) * | 2020-12-24 | 2021-04-09 | 山东浪潮通软信息科技有限公司 | Method and device for graphically defining query data set |
| CN112632037B (en) * | 2020-12-24 | 2023-04-07 | 浪潮通用软件有限公司 | Method and device for graphically defining query data set |
| CN112799659A (en) * | 2021-01-12 | 2021-05-14 | 杨飞 | Method, device and terminal for automatically generating data interface without programming |
| CN113468208A (en) * | 2021-07-19 | 2021-10-01 | 网易（杭州）网络有限公司 | Method and device for generating data query statement, server and storage medium |
| CN113836149A (en) * | 2021-11-29 | 2021-12-24 | 深圳市明源云科技有限公司 | Enterprise data query method, enterprise data query device, terminal and computer readable storage medium |
| CN114238378A (en) * | 2021-12-17 | 2022-03-25 | 平安证券股份有限公司 | Query method, device, computer equipment and medium based on SQL query engine |
| WO2024066094A1 (en) * | 2022-09-27 | 2024-04-04 | 北京柏睿数据技术股份有限公司 | Cross-data-source visual construction method and system for database view |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111813799B (en) | 2024-01-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111813799A (en) | Database query statement generation method and device, computer equipment and storage medium | |
| CN108874926B (en) | Mass data query method, device, computer equipment and storage medium | |
| US7747616B2 (en) | File search method and system therefor | |
| CN111061475B (en) | Software code generating method, device, computer equipment and storage medium | |
| EP2800013A1 (en) | Integration database framework | |
| US20200342029A1 (en) | Systems and methods for querying databases using interactive search paths | |
| CN112286934A (en) | Database table importing method, device, equipment and medium | |
| CN112579705B (en) | Metadata acquisition method, device, computer equipment and storage medium | |
| CN114780641A (en) | Multi-library multi-table synchronization method and device, computer equipment and storage medium | |
| CN111737981A (en) | Vocabulary error correction method and device, computer equipment and storage medium | |
| JP5194581B2 (en) | Document processing apparatus and document processing program | |
| CN110457401B (en) | Data storage method and device, computer equipment and storage medium | |
| CN110727777A (en) | Knowledge graph management method and device, computer equipment and storage medium | |
| CN112199443A (en) | Data synchronization method and device, computer equipment and storage medium | |
| CN112527813A (en) | Data processing method and device of business system, electronic equipment and storage medium | |
| CN115543428A (en) | Simulated data generation method and device based on strategy template | |
| CN113076365B (en) | Data synchronization method, device, electronic equipment and storage medium | |
| CN109871214B (en) | Program code generation method, program code generation device, computer device, and storage medium | |
| CN110580333A (en) | data table processing method, searching method, device, equipment and storage medium | |
| CN114356945A (en) | Data processing method, data processing device, computer equipment and storage medium | |
| CN112416966A (en) | Ad hoc query method, apparatus, computer device and storage medium | |
| US9916400B1 (en) | User defined object pusher for multi-user CAx environment | |
| CN110245151B (en) | Data point group query method and device, computer equipment and storage medium | |
| US11138174B2 (en) | Electronic database and method for forming same | |
| CN111291159A (en) | Patent retrieval method, device, system, computer equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |