- Overview
- SELECT
- SELECT
- Special case: Multi-file SELECT
- Special cases: SELECT without FROM or SELECT using a virtual table
- Running a stored procedure
- INSERT
- INSERT
- INSERT with selection
- UPDATE
- DELETE
- CREATE TABLE
- Available types
- CREATE TABLE AS
- DROP TABLE
- ALTER TABLE
- CREATE INDEX
- DROP INDEX
- GRANT
- REVOKE
- CREATE VIEW
- DROP VIEW
- CREATE MATERIALIZED VIEW
- DROP MATERIALIZED VIEW
- REFRESH MATERIALIZED VIEW
- OPTIMIZE TABLE
The main SQL statements allowed in the SQL queries managed by WINDEV and WEBDEV are as follows: For more details, see the SQL documentation. Remarks: - These statements can be used:
- in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
- in the SQL code of queries run by HExecuteSQLQuery.
- The " character can be used as an identifier delimiter in SQL queries as specified by the SQL92 standard.
| | |  | Important | The query editor cannot be used to create queries in SQL using the INSERT, UPDATE or DELETE commands. |
SELECT The SELECT statement is used to find records in one or more files. Format: SELECT [ALL/DISTINCT] NamesOfItems AS NameOfItemsUsed FROM FileName AS UsedFileName [WHERE FileJoins [AND] SelectionConditions] [GROUP BY NameOfItems [HAVING Condition]] [ORDER BY NameOfItems] Example: The following SQL code is used to display the total sum of orders per customer living in Paris. The result will be grouped by customer name, by zip code and by city. Only the customers with a total amount of orders exceeding 3000 Dollars will be considered. The result will be sorted by total order amount in ascending order:
SELECT CUSTOMER.CustomerLastName AS CustomerLastName,
CUSTOMER.ZipCode AS ZipCode,
CUSTOMER.City AS City,
SUM(ORDERS.TotalIOT) AS TotalIOT
FROM CUSTOMER AS CUST, ORDERS AS ORD
WHERE CUST.CustomerNum = ORD.CustomerNum
AND ZipCode LIKE '75%'
GROUP BY CustomerLastName, ZipCode, City
HAVING TotalIOT > 3000
ORDER BY TotalIOT ASC Remarks: - The DISTINCT keyword deletes the duplicate records from the query result.
- For more details on selection conditions, see Selection conditions in an SQL query.
- A Select query can be created in the query editor. For more details, see Creating a select query.
- In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:
SELECT CustomerLastName, Age>18 AS Adult FROM Customer
- The SELECT statement can be used as a scalar expression, which means that the SELECT statement can be used instead a value or a column in the statements such as INSERT, UPDATE, SELECT, etc.
For example:
INSERT INTO TABLE (NAME, VALUE) VALUES ( 'Rome a Rick',
(SELECT MAX(NUMBER) FROM table2) )
UPDATE customer SET gdttl = (SELECT SUM(invoice.grand_total)
FROM invoice WHERE invoice.reference=customer.reference)
SELECT NamesOfItems AS NameOfItemsUsed,
(SELECT COUNT(*) FROM Table2 WHERE Item=NameOfItemsUsed)
FROM Table1
Remark: The SELECT query used as scalar expression must return a result on a single column and a single row. Special case: Multi-file SELECT The SELECT statement can also be used to select in a multi-file query all the columns of a data file via the following syntax:
SELECT Order_Line.*,Orders.OrderNum, ...
FROM Orders, Order_Line This syntax is very useful for joins and avoids entering the name of each item, which is a long and tedious operation. Special cases: SELECT without FROM or SELECT using a virtual table The SELECT statement can also be used to run queries that do not apply to tables: - by using the syntax without "FROM" clause.
Example:
- by using the virtual DUAL table. This virtual table contains a single item (named "dummy") and a single record.
Example:
SELECT CURRENT_DATE FROM DUAL
Running a stored procedure The SELECT statement can also be used to run stored procedures directly. The following syntax is used to run the stored procedure: SELECT NameOfStoredProcedure(NumericParameter,'StringParameter',....) [FROM DUAL] Remarks: - The stored procedure must be a stored function: it must always return a value. Otherwise, a runtime error will occur.
- If the name of the stored procedure is identical to the name of a function or procedure found in the project, its name must be prefixed by the name of the set of stored procedures.
- The result of a stored procedure can also be one of the parameters of a SELECT query.
INSERT The INSERT statement is used to add : - one record to a data file.
- multiple records to a data file.
It uses the following format: INSERT INTO FileName (NameOfItems) VALUES (ValueItems add 1), (ValueItems add 2), ... , (ValueItems add N) Examples: The following SQL code lets you insert: - a new customer in the CUSTOMER file (the complete customer information is provided).
INSERT INTO CUSTOMER
VALUES (77, 'Moore', 'Julia', '1 rue Giono',...
'69000', 'Lyon') - a new customer in the CUSTOMER file: only the customer's number, last name and first name are provided:
INSERT INTO CUSTOMER (CustomerNum, CustomerLastName, CustomerFirstName)
VALUES (77, 'Moore', 'Julia') - 3 products in a PRODUCT file:
INSERT INTO PRODUCT
VALUES ('Ref01', 'Mineral water'), ('Ref02', 'Butter'), ('Ref03', 'Sponge')
Remarks: - An Insert query can be created in the query editor. For more details, see Creating an insert query.
- To get the value of the automatic identifier in an INSERT query on HFSQL files (query run by HExecuteSQLQuery, HExecuteQuery or SQLExec), use the SQL.IDAuto variable.
- The INSERT command cannot be used to copy data from one HFSQL file to another.
- The H.NbRecModificationQuery variable is used to find out the number of inserted records.
INSERT with selection The INSERT statement can also be used to insert a selection of records made by a SELECT query. Its use format becomes: INSERT INTO FileName [(NameOfItems)] SELECT... Remarks: - The number of items to insert must be identical to the number of values returned by the SELECT query.
- All the files items are taken into account if no item is specified in the INSERT statement.
- This type of query cannot be created in the query editor.
The UPDATE statement is used to update the records in a file. It uses the following format: UPDATE FileName SET NameOfItem = Expression [WHERE Conditions] Examples: - The following SQL code is used to modify the title and the name of the customer named Montgomery:
UPDATE CUSTOMER
SET Title = 'Mrs',
CustomerLastName = 'Darwin'
WHERE CustomerLastName = 'Montgomery' - The following SQL code modifies the client name in the Customer file. For records with the same ID, the name of the client in the Customer file is replaced with the name in the Customer_Svg file:
UPDATE CUSTOMER, CUSTOMER_SVG
SET CUSTOMER.NAME = CUSTOMER_SVG.NAME
WHERE CUSTOMER.ID = CUSTOMER_SVG.ID
Notes about the use of the UPDATE statement in WINDEV: - The WHERE clause accepts the sub-selections (by SELECT), if the sub-selection is relative to another file.
- The SET expression accepts no sub-selection (no SELECT command just after SET). Similarly, no join can be performed in the UPDATE section of the query.
The following query is accepted:
UPDATE PRODUCT SET SalesPrice = 1000
WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS)
The following query is not accepted:
UPDATE Product
SET SalesPrice = (SELECT AVG(ORDERS.UnitPrice) FROM ORDERS
WHERE ORDERS.ProductRef = PRODUCT.ProductRef
GROUP BY ORDERS.ProductRef)
WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS) Similarly, the following query is not accepted:
UPDATE Article INNER JOIN PRODUCT ON (Article.ArticleID = Product.ProductID)
SET Article.SalesPrice = Product.SalesPrice
WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS) - An Update query can be directly created in the query editor. For more details, see Creating an update query.
- The quote is used as delimiter for the strings in the SQL queries. If the value to write must contain a quote, this value must be preceded by the backslash character ('\\'). For example:
UPDATE CUSTOMER
SET Address = 'Rue de l\'écluse'
WHERE CustomerLastName = 'Montgomery' - The H.NbRecModificationQuery variable is used to find out the number of updated records.
The DELETE statement is used to delete records from a file. It uses the following format: DELETE FROM FileName WHERE Conditions Example: The following SQL code is used to delete the customers born between 01/01/1971 and 01/01/1975:
DELETE FROM CUSTOMER
WHERE CUSTOMER.DateOfBirth BETWEEN '19710101' AND '19750101' The CREATE TABLE statement is used to describe and create a table. It uses the following format: CREATE TABLE TableName ( Description of item1, Description of item2, ... Description Index1, Description Index2, ... ) In this syntax: - the "Description of item" parameters correspond to:
ItemName type [DEFAULT 'value' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY] [AUTO_INCREMENT] [COMMENT 'caption'] [COLLATE 'language'] where:- ItemName: name that will be given to the item.
- Type: Type of item (chosen among the available types presented below).
- DEFAULT: defines the default value of the item.
- NOT NULL: prevents from assigning the NULL value to the item.
- UNIQUE: defines an item with or without duplicates.
- PRIMARY KEY: defines a primary key.
- AUTO_INCREMENT: defines an automatic identifier for items of type Integer.
- COMMENT: defines the caption of the item.
- COLLATE: defines the language for sorting the item (for example: 'EN_US').
- the "Description of index" parameters correspond to:
INDEX IndexName ( itm1 [ASC | DESC] [CI] [AI] [PI], itm2 ... ) [UNIQUE] where:- ASC and DESC: are used to define the sort direction.
- CI: specifies that the case is ignored.
- AI: specifies that accented characters are ignored.
- PI: specifies that the punctuation is ignored.
Remark: This type of query cannot be created with the wizard found in the query editor. Available types The available types are: | | CHARACTER | Character string | CHARACTER(Size) | String on size | VARCHAR(Size) | String on size | CHARACTER VARYING(Size) | String on size | CHAR VARYING(Size) | String on size | NVARCHAR(Size) | Unicode string on size | VARCHAR(Size) BINARY | Binary string on size | BINARY(Size) | Binary string on size | VARBINARY(Size) | Binary string on size | BLOB | Binary memo | CLOB | Text memo | TEXT | Text memo | NCLOB | Unicode memo | NTEXT | Unicode memo | NUMBER(Precision) | Integer | NUMBER(Precision, scale) | Integer | DECIMAL(Precision) | Real | DECIMAL(Precision, scale) | Real | TINYINT UNSIGNED | Unsigned 1-byte integer | SMALLINT UNSIGNED | Unsigned 2-byte integer | INTEGER UNSIGNED | Unsigned 4-byte integer | BIGINT UNSIGNED | Unsigned 8-byte integer | TINYINT | Signed 1-byte integer | SMALLINT | Signed 2-byte integer | INTEGER | Signed 4-byte integer | BIGINT | Signed 8-byte integer | FLOAT | 4-byte real | REAL | 8-byte real | DOUBLE PRECISION | 8-byte real | MONEY | Currency | DATE | DATE | DATETIME | Date time | TIME | Time | MOTDEPASSESÉCURISÉ | Secure password |
The CREATE TABLE AS statement describes a table and creates it from the result of a select query. The table columns have the names and types of data associated with the columns after the SELECT query has been run.It uses the following format: CREATE TABLE TableName AS SelectQuery [WITH NO DATA] In this syntax: - SelectQuery is a SELECT query. The query is run and the table is created from the result of this Select query.
- The "WITH NO DATA" option is used to create the table structure only (without the data found in the result of the Select query).
Caution: The index of the created table may differ from the one of the source table. Remark: This type of query cannot be created with the wizard found in the query editor. The DROP TABLE statement is used to physically delete a table. It uses the following format: DROP TABLE [ IF EXISTS ] name [, ...] Remarks: - IF EXISTS is used to avoid returning an error if the table does not exist.
- This type of query cannot be created with the wizard found in the query editor.
The ALTER TABLE statement is used to change the structure of an existing table. You can add or delete columns and indexes, change the type of the existing columns and rename the columns or the table. It uses the following format:ALTER TABLE TableName Action [, Action].... In this syntax: - the "Action" parameters correspond to one of the following actions:
ADD [COLUMN] Description of item ADD [COLUMN] (Description of item1 [,Description of item2]....) DROP [COLUMN] [IF EXISTS] ItemName DROP [COLUMN] [IF EXISTS] (NameItem1 [, NameItem2]...) ALTER [COLUMN] ItemName [SET DATA] TYPE alter_type_desc ALTER [COLUMN] ItemName SET DEFAULT <value> ALTER [COLUMN] ItemName DROP DEFAULT ADD [UNIQUE / PRIMARY KEY] INDEX [<IndexName>] (Description of index [, Description of index2], ...) DROP INDEX [IF EXISTS] IndexName DROP INDEX [IF EXISTS] (NameIndex1 [, NameIndex2]...) RENAME COLUMN ItemName TO New_ItemName New in version 28ADD PRIMARY KEY INDEX adds a primary key in an existing file.
- The "Description of item" parameters correspond to:
ItemName type [DEFAULT 'value' | DEFAULT NULL] [NOT NULL] [UNIQUE] [PRIMARY KEY] [AUTO_INCREMENT] [COMMENT 'caption'] [COLLATE 'language'] where:- ItemName: name that will be given to the item.
- Type: Type of item (chosen among the types presented above).
- DEFAULT: defines the default value of the item.
- NOT NULL: prevents from assigning the NULL value to the item.
- UNIQUE: defines an item with or without duplicates.
- PRIMARY KEY: defines a primary key.
- AUTO_INCREMENT: defines an automatic identifier for items of type Integer.
- COMMENT: defines the caption of the item.
- COLLATE: defines the language for sorting the item (for example: 'EN_US').
- SECUREPASSWORD: is used to determine that the item is of type "Password".
- the "Description of index" parameters correspond to:
ItemName [ASC | DESC] [CI] [AI] [PI] where:- ASC and DESC: are used to define the sort direction.
- CI: specifies that the case is ignored.
- AI: specifies that accented characters are ignored.
- PI: specifies that the punctuation is ignored.
Remarks: - This type of query cannot be created with the wizard found in the query editor.
- To use the modified table with ALTER (without an analysis related to the project), use HDeclareExternal after the ALTER TABLE query is executed.
The CREATE INDEX statement (equivalent to the ALTER TABLE statement) is used to create indexes. This statement is used to add indexes to an existing table. It uses the following format:CREATE [UNIQUE] INDEX [IndexName] ON TableName (Description of index [, Description of index]... ) In this code, the "Description of index" parameters correspond to: ItemName [ASC | DESC] [CI] [AI] [PI] where:- ASC and DESC: are used to define the sort direction.
- CI: specifies that the case is ignored.
- AI: specifies that accented characters are ignored.
- PI: specifies that the punctuation is ignored.
Remark: This type of query cannot be created with the wizard found in the query editor. You have the ability to use the CREATE INDEX statement on a materialized view. In this case, the TableName parameter corresponds to the name of a materialized view. If the view is refreshed, the indexes are automatically updated: there is no need to re-create the indexes.Example: Creating a materialized view and a composite key on this view.
CREATE MATERIALIZED VIEW MyMaterializedView AS
SELECT
Customer.State, Customr.Disp, Customer.MemberType, COUNT(*) AS Qty
FROM Customer
WHERE Customer.Balance>0 AND Customer.Type=2
AND Customer.Family IN ('A', 'D', 'O')
GROUP BY Customer.State, Customer.Disp, Customer.MemberType;
CREATE INDEX compkey ON MyMaterializedView (State ASC CI AI PI,
Disp ASC CI AI PI,MemberType ASC) The DROP INDEX statement is used to physically delete an index from a table. It uses the following format:DROP INDEX [ IF EXISTS ] NameIndex ON NameTable where IF EXISTS: prevents returning an error if the index does not exist. Remark: This type of query cannot be created with the wizard found in the query editor. GRANT queries are used to grant rights on an HFSQL server. This statement is equivalent to setting the element as hAllowed. It uses the following format:- Grant the requested rights on the specified tables to the specified users:
GRANT right[, right[, ... ]] ON [TABLE] table [, table [, ...]] TO user [, user [, ...]] - Grant the requested rights on the specified databases to the specified users:.
GRANT right[, right[, ... ]] ON DATABASE database [, database [, ...]] TO user [, user [, ...]] - Grant the requested rights on the server.
GRANT right[, right[, ... ]] TO user [, user [, ...]]
Remark: - This type of query cannot be created with the wizard found in the query editor.
- These queries cannot be used on an HFSQL Classic database, nor on a database accessed via Native Connectors.
Available rights The available rights are: | | SQL right | HFSQL right | INSERT | hRightsInsert | LOCK | hRightsLock | MANAGE RIGHTS | hRightsChangeRights | OWNER | hRightsChangeOwner | MANAGE DUPLICATE | hRightsManageDuplicate | SELECT | hRightsRead | MAINTENANCE | hRightsMaintenance | ALTER | hRightsAutoModif | UPDATE | hRightsUpdate | DELETE | hRightsDelete | DELETE FILE | hRightsDeleteFile | REFERENCES | hRightsChangeLink | CONNECT | hRightsConnection | ENCRYPTED CONNECT | hRightsEncryptedConnection | CREATE | hRightsCreateFile | DEBUG | hRightsDebug | RUN PROCEDURE | hRightsRunProcedure | MANAGE REFERENCES | hRightsManageIntegrity | MANAGE PROCEDURE | hRightsManageProcedure | MANAGE TRIGGER | hRightsManageTrigger | FORBID ACCESS | hRightsNoDatabaseAccess | BACKUP | hRightsBackup | DELETE DATABASE | hRightsDeleteDB | STOP | hRightsStopServer | CHANGE PASSWORD | hRightsChangePassword | CREATE DATABASE | hRightsCreateDB | DISCONNECT | hRightsDisconnectClient | SEND MESSAGE | hRightsSendMessageToClient | MANAGE TASK | hRightsManageTask | MANAGE SERVER | hRightsManageServer | MANAGE USER | hRightsManageUser | READ LOG | hRightsReadLogStat | PRIORITY | hRightsPriority | REPLICATE | hRightsServerReplication | SEE USER | hRightsSeeUser |
The REVOKE queries are used to remove rights (switch the rights to hInherit) on an HFSQL server. It uses the following format:- Set the requested rights as inherited on the specified tables for the specified users.
REVOKE right[, right [, ... ]] ON [TABLE] table [, table [, ...]] FROM user [, user [, ...]] - Set the requested rights as inherited on the specified databases for the specified users.
REVOKE right[, right [, ... ]] ON DATABASE database [, database [, ...]] FROM user [, user [, ...]] - Set the requested rights as inherited on the server.
REVOKE right[, right [, ... ]] FROM user [, user [, ...]]
Remark - This type of query cannot be created with the wizard found in the query editor.
- These queries do not work with HFSQL Classic or Native Connectors.
Available rights: The available rights are identical to the SQL GRANT command. The table of rights is available with the GRANT command. CREATE VIEW is used to create an SQL view. SQL views are equivalent to temporary files in memory. In most cases, an SQL view is extracted from a file or from several files. A view can be handled by a SELECT command.For more details on SQL views, see SQL view. It uses the following format: CREATE VIEW View [ ( Alias 1, Alias 2, ..., Alias N) ] AS SELECTQuery In this syntax: - View is the name of the view.
- Alias 1, Alias 2, Alias N represent the alias names of the items from the SELECT query.
- SELECTQuery is the SELECT query used to select the records found in the view.
SQL example: Creating an SQL view containing the company, the name and the phone of customers:
CREATE VIEW V_Customers
AS SELECT Company, NAME, Phone FROM Customers DROP VIEW is used to delete a view.DROP VIEW [IF EXISTS] View 1 [, View 2, ...View N] [RESTRICT | CASCADE] - If the IF EXISTS keyword is specified and the views do not exist, an error message is generated.
- View 1, View 2, ... View N represent the list of views to delete.
- If the RESTRICT keyword is specified, the view is not deleted if it is in use.
- If the CASCADE keyword is specified, the view is deleted as well as the dependent views.
A materialized view is a persistent view: a file is created on disk from the content of the view. This view can be handled later by an application other than the one that created it.CREATE MATERIALIZED VIEW is used to create a materialized view. CREATE MATERIALIZED VIEW View [ ( Alias 1, Alias 2, ...Alias N) ] AS SELECT statement [WITH [ NO ] DATA] - The WITH DATA keyword creates a view with data.
- The WITH NO DATA keyword creates an empty view without data.
Remark - This type of query cannot be created with the wizard found in the query editor.
- These queries cannot operate in HFSQL Classic or with Native Connectors.
DROP MATERIALIZED VIEW is used to physically delete a materialized view from the disk.DROP MATERIALIZED VIEW [IF EXISTS] View 1 [, View 2, ...View N] [RESTRICT | CASCADE] - If the IF EXISTS keyword is specified and the views do not exist, an error message is generated.
- View 1, View 2, ... View N represent the list of views to delete.
- If the RESTRICT keyword is specified, the view is not deleted if it is in use.
- If the CASCADE keyword is specified, the view is deleted as well as the dependent views.
REFRESH MATERIALIZED VIEW REFRESH MATERIALIZED VIEW refreshes the content of a materialized view on the disk. The view is entirely recalculated.REFRESH MATERIALIZED VIEW View [WITH [ NO ] DATA] - The WITH DATA keyword refreshes the view with the new data.
- The WITH NO DATA keyword refreshes the empty view without data.
New in version 28The OPTIMIZE TABLE command reorganizes and reindexes a data file, and rebuilds the indices of this file. This operation improves performance. This function is equivalent to using the WLanguage HIndex function to reorganize and reindex a data file. FileName represents the name of the file to be reorganized. Remark: - This function is locking: the data file cannot be accessed during the execution of the function. Make sure the file is not in use when the function is called.
- This function cannot be used with Native Connectors.
- By default, the reindexing operation is performed according to one of the following parameters: standard reindexing, full-text reindexing, reindexing in the background, 80% density. To use other parameters, use HIndex.
This page is also available for…
|
|
|