SQL grammar manual
SELECT
use:
Remove the data of the specified column from the specified table
grammar:
SELECT Column_name (s) from table_name
Explanation:
Select the data column from the database and allow you to select one or more data columns or data lines from one or more data sheets. SELECT statement is quite complicated, but the main clauses can be summarized:
SELECT SELECT_LIST [INTO new_TABLE] from table_source [Where search_condition] [GROUP BY Group_BY_EXPIPRESSION] [HAVING Search_Condition] [ORDER BY ORDER_EXPISSION [ASC | DESC]]
example:
The data in the "Persons" table has
Lastname Firstname Address City Hansen Ola Timoteivn 10 Sandnes Svendson Tove Borgvn 23 Sandnes Pettersen Kari Storgt 20 Stavanger
Select the data name "lastname", "firstname"
Select Lastname, Firstname from Persons
Return the result:
Lastname Firstname Hansen Ola Svendson Tove Pettersen Kari
Select the data of all fields
Select * from Persons
Return the result:
Lastname Firstname Address City Hansen Ola Timoteivn 10 Sandnes Svendson Tove Borgvn 23 Sandnes Pettersen Kari Storgt 20 Stavanger
WHERE
use:
It is used to specify a standard for selecting queries
grammar:
Select Column from Table Where COLMN CONDITION VALUE
The following operator can be used in WHERE:
=, <>,>, <,> =, <=, Beetween, LIKE
Note: Do not equal in some sql versions <> can be written as! =
Explanation:
SELECT statement Returns the data for the WHERE clause in TRUE
example:
Select people living in "Sandnes" from the "Persons" table
Select * from persons where city = 'sandnes'
The data in the "Persons" table is:
Lastname Firstname Address City Year Hansen Ola Timoteivn 10 Sandnes 1951 SvenDson Tove Borgvn 23 Sandnes 1978 Svendson Stale Kaivn 18 Sandnes 1980 Pettersen Kari Storgt 20 Stavanger 1960
Return the result:
Lastname Firstname Address City Year Hansen OLA TIMOTEIVN 10 Sandnes 1951 Svendson Tove Borgvn 23 Sandnes 1978 SvenDson Stale Kaivn 18 Sandnes 1980
And & or OR
use:
And and or or more conditions are used to connect two or more in the WHERE clause
Explanation:
And in combination with two Boolean expressions, only in both expressions are True.
When combined with two Boolean expressions, as long as one of the conditions is True, OR passes back TRUE.
example:
Original data in the "Persons" table:
Lastname Firstname Address City Hansen Ola Timoteivn 10 Sandnes Svendson Tove Borgvn 23 Sandnes Svendson Stephen Kaivn 18 Sandnes
Use the And Operators to find data in the "Persons" table "TOVE" and LastName is "Svendson"
Select * from person = 'TOVE' and lastname = 'svendson'
Return the result:
Lastname Firstname Address City SvenDson Tove Borgvn 23 Sandnes
Use the OR operator to find data in the "persons" table in the "Persons" table or "TOVE" or lastname "Svendson"
SELECT * from person = 'Tove' or lastname = 'svendson'
Return the result:
Lastname Firstname Address City SvenDson Tove Borgvn 23 Sandnes SvenDson Stephen Kaivn 18 Sandnes
You can also combine and or or to form a complex expression in brackets), such as:
SELECT * from person = 'tove' or firstname = 'stephen') And lastname = 'svendson'
Return the result:
Lastname Firstname Address City SvenDson Tove Borgvn 23 Sandnes SvenDson Stephen Kaivn 18 Sandnes
Between ... and
use:
Specify the range that needs to return data
grammar:
Select column_name from table_name where column_name betWeen value1 and value2
example:
Original data in the "Persons" table
Lastname Firstname Address City Hansen OLA TIMOTEIVN 10 Sandnes Nordmann Anna Nest 18 Sandnes Pettersen Kari Storgt 20 Stavanger Svendson Tove Borgvn 23 Sandnes
Returns LastName to "Hansen" to "Pettersen" data:
Select * from person 'hansn' and 'petsen'
Return the result:
Lastname Firstname Address City Hansen OLA TIMOTEIVN 10 Sandnes Nordmann Anna Nest 18 Sandnes Pettersen Kari Storgt 20 Stavanger To display data outside the specified range, you can also use the NOT operator:
SELECT * from Persons Where Lastname NOTBETWEEN 'HANSEN' and 'PETTERSEN'
Return the result:
Lastname Firstname Address City SvenDson Tove Borgvn 23 Sandnes
Distinct
use:
DistINCT keyword is used as a unique value
grammar:
Select Distinct Column-Name (s) from Table-Name
Explanation:
When there is a repeated value in the column-name (s), the return result is only one
example:
Ordest data in the "Orders" table
Company OrderNumber Sega 3412 W3Schools 2312 TRIO 4678 W3Schools 6798
Returns the only value in the Company field with a DistINCT keyword:
Select Distinct Company from Orders
Return the result:
Company Sega W3Schools Trio
ORDER BY
use:
Specify the order of result set
grammar:
Select column-name (s) from table-name order by {order_by_expression [ASC | DESC]}
Explanation:
Specify the sort of the result set, can be sorted in the way as ASC (increasing mode, from the lowest value to the highest) or DESC (decrement mode, from the highest value to the lowest), the default method is ASC
example:
Original data in the "ORDERS" table:
Company OrderNumber Sega 3412 ABC SHOP 5678 W3Schools 2312 W3Schools 6798
Returns the result set according to the ascending order of the Company field:
Select Company, OrderNumber from Orders ORDER BY COMPANY
Return the result:
Company OrderNumber Abc Shop 5678 Sega 3412 W3Schools 6798 W3Schools 2312
Returns the result set according to the descending order of the Company field:
Select Company, OrderNumber from Orders Order by Company DESC
Return the result:
Company OrderNumber W3Schools 6798 W3Schools 2312 Sega 3412 ABC SHOP 5678
GROUP BY
use:
Grouping result sets is often used with the summary function.
grammar:
Select Column, Sum (Column) from Table Group by Column
example:
Original data in the "Sales" table:
Company Amount W3Schools 5500 IBM 4500 W3Schools 7100
Follow the companies field to find the total of each Company's AMOUT: Select Company, SUM (Amount) from sales group by Company
Return the result:
Company SUM (Amount) W3Schools 12600 IBM 4500
Having
use:
Specify the group or summary search criteria.
grammar:
Select Column, Sum (Column) from Table Group by Column Having Sum (Column) Condition Value
Explanation:
Having is usually used at the same time as the Group By clause. When you don't use Group by, Having is similar to where clauses.
example:
Original data in the "Sales" table:
Company Amount W3Schools 5500 IBM 4500 W3Schools 7100
Follow the COMPANY field to find data for each Company's AMOUT at 10,000 data:
Select Company, SUM (Amount) from sales group by Company Having Sum (Amount)> 10000
Return the result:
Company SUM (Amount) W3Schools 12600
Join
use:
You will use Join when you want to select a result set from two or more.
example:
The data in the "EMPLOYEES" table is as follows, (where ID is the primary key):
ID Name 01 Hansen, OLA 02 Svendson, Tove 03 Svendson, Stephen 04 Pettersen, Kari
The data in the "ORDERS" table is as follows:
ID Product 01 Printer 03 Table 03 Chair
Select the data with the ID of Employees ID and Orders:
Select Employees.name, Orders.Product from Employees, Orders Where Employees.Id = Orders.ID
Return the result:
Name Product Hansen, Ola Printer Svendson, Stephen Table Svendson, Stephen Chair
Or you can also use the Join keyword to complete the above operations:
Select Employees.Name, Orders.Product from Employees Inner Join Ors ON Employees.id = Orders.id
Inner Join Syntax:
SELECT FIELD1, FIELD2, FIELD3 from first_table inner join second_table on first_table.keyfield = second_table.ForeIgn_Keyfield
Explanation:
The result set returned by Inner Join is all the matched data in two tables.
LEFT JOIN Syntax:
SELECT FIELD1, FIELD2, FIELD3 from first_table left join second_table on first_table.keyfield = second_table.ForeIgn_Keyfield
Use "Employees" table to go to the left to join the "Orders" table to find relevant data: select Employees.name, Orders.Product from Employees Left Join Ors ON Employees.Id = Orders.ID
Return the result:
Name Product Hansen, Ola Printer Svendson, Tove Svendson, Stephen Table Svendson, Stephen Chair Pettersen, Kari
Explanation:
LEFT JOIN Returns all rows in "first_table" although there is no matching data in "Second_Table".
Right Join Syntax:
Select Field1, Field2, Field3 from first_table right join second_table on first_table.keyfield = second_table.foreign_keyfield
Use "Employees" to go to the right out of the "Orders" table to find the relevant data:
Select Employees.name, Orders.Product from Employees Right Join Ors ON Employees.Id = Orders.id
Return the result:
Name Product Hansen, Ola Printer Svendson, Stephen Table Svendson, Stephen Chair
Explanation:
Right Join returns all rows in "Second_Table" although there is no matching data in "first_table".
Alias
use:
Available in a table, result set, or column, take a logical name for them
grammar:
Give the column acquisition name:
SELECT Column As Column_Alias from Table
Take the alias for the table:
SELECT Column from Table as Table_Alias
example:
Original data in the "Persons" table:
Lastname Firstname Address City Hansen Ola Timoteivn 10 Sandnes Svendson Tove Borgvn 23 Sandnes Pettersen Kari Storgt 20 Stavanger
Run the following SQL:
Select Lastname As Family, Firstname As Name from Persons
Return the result:
Family Name Hansen Ola Svendson Tove Pettersen Kari
Run the following SQL:
Select Lastname, FirstName from Persons as Employees
Return the result:
The data in Employees is:
Lastname Firstname Hansen Ola Svendson Tove Pettersen Kari
INSERT INTO
use:
Insert a new line in the table
grammar:
Insert a line of data
INSERT INTO TABLE_NAME VALUES (Value1, Value2, ....)
Insert a line of data on the specified field
INSERT INTO TABLE_NAME (Column1, Column2, ...) VALUES (Value1, Value2, ....)
example:
Original data in the "Persons" table:
Lastname Firstname Address City Pettersen Kari Storgt 20 Stavanger
Run the following SQL insert a row of data:
INSERT INTO PERSONS VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')
The data in the "Persons" table is:
Lastname Firstname Address City Pettersen Kari Storgt 20 Stavanger Hetland Camilla Hagabakka 24 Sandnes
Run the following SQL insert a row of data on the specified field:
INSERT INTO PERSONS (Lastname, Address) Values ('Rasmussen', 'Storgt 67')
The data in the "Persons" table is:
Lastname Firstname Address City Pettersen Kari Storgt 20 Stavanger Hetland Camilla Hagabakka 24 Sandnes Rasmussen Storgt 67
Update
use:
Update the original data in the table
grammar:
Update table_name set color_name = new_value where column_name = Some_Value
example:
Original data in the "Person" table:
Lastname Firstname Address City Nilsen Fred Kirkegt 56 Stavanger Rasmussen Storgt 67
Run the following SQL updates the lastname field in the Person table for "rasmussen" to "Nina":
Update Person set firstname = 'nina' where lastname = 'rasmussen'
The data in the "Person" table in the update is:
Lastname Firstname Address City Nilsen Fred Kirkegt 56 Stavanger Rasmussen Nina Storgt 67
Similarly, use UPDATE statements to update multiple fields at the same time:
Update person set address = 'stien 12', city = 'stavanger' where lastname = 'rasmussen'
The data in the "Person" table in the update is:
Lastname Firstname Address City Nilsen Fred Kirkegt 56 Stavanger Rasmussen Nina Stien 12 Stavanger
Delete
use:
Delete the data in the table
grammar:
Delete from table_name where column_name = Some_Value
example:
Original data in the "Person" table:
Lastname Firstname Address City Nilsen Fred Kirkegt 56 Stavanger Rasmussen Nina Stien 12 Stavanger Delete the data of LastName "Rasmussen" in the Person table:
Delete from person where lastname = 'rasmussen'
The data in the "Person" table after executing the delete statement is:
Lastname Firstname Address City Nilsen Fred Kirkegt 56 Stavanger
Create Table
use:
Establish a new information table.
grammar:
CREATE TABLE TABLE_NAME (Column_name1 Data_Type, Column_name2 Data_Type, .......)
example:
Create a table called "Person", which has 4 fields "lastname", "firstname", "address", "age":
Create Table Person (Lastname Varchar, Firstname Varchar, Address Varchar, Age Int)
If you want to specify the maximum storage length of the field, you can do this:
Create Table Person (Lastname Varchar (30), FirstName Varchar (30), Address Varchar (120), AGE INT (3))
The following table lists some of the data types in SQL:
Data Type Description integer (size) int (size) smallint (size) tinyint (size) Hold integers only. The maximum number of digits are specified in parenthesis. Decimal (size, d) numeric (size, d) Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d". char (size) Holds a fixed length string (can contain letters, numbers, and special characters) . The fixed size is specified in parenthesis. varchar (size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. date (yyyymmdd) Holds a date
Alter Table
use:
Add the latter removal field in the existing table
grammar:
ALTER TABLE TABLE_NAME ADD Column_name DataType Alter Table Table_name Drop Column Column_name
Note: Some database management systems do not allow fields in the table
example:
Original data in the "Person" table:
LastName Firstname Address Pettersen Kari Storgt 20 Adds a field named City in the Person table:
ALTER TABLE PERSON ADD City VARCHAR (30)
Increase the data in the table below:
Lastname Firstname Address City Pettersen Kari Storgt 20
Remove the original Address field in the Person table:
ALTER TABLE PERSON DROP COLUMN Address
The data in the removal table is as follows:
Lastname Firstname City Pettersen Kari
Drop table
use:
Remove a data table definition in the database and all information, indexes, triggers, condition constraints, and permissions in the data table.
grammar:
DROP TABLE TABLE_NAME
Create Database
use:
Establish a new database.
grammar:
Create Database data_name
DROP DATABASE
use:
Remove the original database
grammar:
DROP DATABASE DATABASE_NAME
Aggregate function
count
use:
Remove the number of collected results in the selected result.
grammar:
SELECT Count (Column_name) from table_name
example:
The original data in the "Persons" table is as follows:
Name Age Hansen, OLA 34 Svendson, TOVE 45 Pettersen, Kari 19
Select the total number of records:
Select Count (Name) from Persons
Results of the:
3
Sum
use:
The sum of all values is transmitted in an expression, or only the Distinct value. SUM can only be used in numerical data lines. NULL values have been ignored.
grammar:
SELECT SUM (Column_name) from table_name
example:
The original data in the "Persons" table is as follows:
Name Age Hansen, OLA 34 Svendson, TOVE 45 Pettersen, Kari 19
Choose the age sum of everyone in the "Persons" table:
SELECT SUM (AGE) from Persons
Results of the:
98
Choose the age of more than 20 years old in the "Persons" table:
SELECT SUM (AGE) from Persons where agn> 20
Results of the:
79
AVG
use:
The average value of the selected result set value is transmitted. NULL values have been ignored.
grammar:
SELECT AVG (Column_name) from table_name
example:
The original data in the "Persons" table is as follows:
Name Age Hansen, OLA 34 Svendson, TOVE 45 Pettersen, Kari 19
Select the average age of everyone in the "Persons" table:
SELECT AVG (AGE) from Persons
Results of the:
32.67
The average age of people who have more than 20 years old in the "Persons" table:
SELECT AVG (AGE) from Persons Where Age> 20
Results of the:
39.5
Max
use:
Remove the maximum value of the selected result set value. NULL values have been ignored. grammar:
SELECT MAX (Column_name) from Table_name
example:
The original data in the "Persons" table is as follows:
Name Age Hansen, OLA 34 Svendson, TOVE 45 Pettersen, Kari 19
Select the maximum age in the "Persons" table:
SELECT MAX (AGE) from Persons
Results of the:
45
MIN
use:
Retrieve the minimum value of the selected result set value. NULL values have been ignored.
grammar:
SELECT MIN (Column_name) from table_name
example:
The original data in the "Persons" table is as follows:
Name Age Hansen, OLA 34 Svendson, TOVE 45 Pettersen, Kari 19
Select the minimum age in the "Persons" table:
SELECT MIN (AGE) from Persons
Results of the:
19
Arithmetic function
ABS
use:
Remove the absolute positive value of the specified numerical expression (Numeric Expression).
grammar:
ABS (Numeric_Expression)
example:
ABS (-1.0) ABS (0.0) ABS (1.0)
Results of the:
1.0 0.0 1.0
CEIL
use:
The transmission is greater than or equal to the minimum integer of a given numerical expression.
grammar:
CEIL (Numeric_Expression)
example:
CEIL (123.45) CEIL (-123.45)
Results of the:
124.00 -123.00
Floor
use:
The maximum integer of less than or equal to a given numerical expression.
grammar:
FLOOR (Numeric_Expression)
example:
FLOOR (123.45) Floor (-123.45)
Results of the:
123.00 -124.00
COS
use:
A mathematical function of the triangular cosine value of the specified angle (unit) in the specified expression.
grammar:
COS (numeric_expression)
example:
COS (14.78)
Results of the:
-0.599465
Cosh
use:
The transmitted angle value in an arc is transmitted, and the cosine is the specified FLOAT expression, also known as anti-rest.
grammar:
Cosh (numeric_expression)
example:
Cosh (-1)
Results of the:
3.14159
Sin
use:
The Trigonometric Sine is transmitted back to the trigonometric sine in the approximate value (FLOAT) expression.
grammar:
Sin (numeric_expression)
example:
Sin (45.175643)
Results of the:
0.929607
Sinh
use:
By the point of view of the degree, the sinusoid is the specified FLOAT expression (also known as an alkoid).
grammar:
SINH (Numeric_Expression)
example:
Sinh (-1.00)
Results of the:
-1.5708
Tan
use:
Remove the correct function of the input expression.
grammar:
Tan (numeric_expression)
example:
Tan (3.14159265358979 / 2) Execution:
1.6331778728383844e 16
Tanh
use:
The transmission is in an angle of the degree, which is orthirty as the specified FLOAT expression (also known as an interpretation).
grammar:
Tanh (numeric_expression)
example:
Tanh (-45.01)
Results of the:
-1.54858
EXP
use:
The exponential value of the given Float expression is passed.
grammar:
Exp (numeric_expression)
example:
EXP (378.615345498)
Results of the:
2.69498E 164
log
use:
The natural logar of the given Float expression.
grammar:
Log (numeric_expression)
example:
Log (5.175643)
Results of the:
1.64396
Power
use:
Transfer the value of the page for a given expression.
grammar:
Power (numeric_expression, v)
example:
Power (2, 6)
Results of the:
64
Sign
use:
The positive ( 1), zero (0) or negative (-1) numbers are passed back.
grammar:
Sign (numeric_expression)
example:
Sign (123) SIGN (0) SIGN (-456)
Results of the:
1 0 -1
SQRT
use:
The square of the given expression is passed.
grammar:
SQRT (numeric_expression)
example:
SQRT (10)
Results of the:
100
