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 Column 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: When combined with two Boolean expressions, only two expressions are True when two 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 Persons
Where firstname = '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 Persons
Where firstname = '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 Persons Where
(Firstname = '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 colorn_name
Between Value1 and Value2
example:
Original data in the "Persons" table
Lastname
Firstname
Address
CITY
Hansen
OLA
TIMOTEIVN 10
Sandnes
Nordmann
Anna
NESET 18
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
Svendson
TOVE
Borgvn 23
Sandnes
Returns LastName to "Hansen" to "Pettersen" data:
SELECT * from Persons Where Lastname
Between 'Hansen' and 'Pettersen'
Return the result:
Lastname
Firstname
Address
CITY
Hansen
OLA
TIMOTEIVN 10
Sandnes
Nordmann
Anna
NESET 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
NOT BETWEEN '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 COMPANY field to find the total AMOUT of each Company:
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.
Syntax: 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 ORDERS
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 "Orders" table to find relevant data:
Select Employees.name, Orders.Product
From Employees
Left Join Orders
ON Employees.id = Orders.ID
Return the result:
Name
PRODUCT
Hansen, OLA
PRINTER
Svendson, TOVE
Svendson, Stephen
TABLE
Svendson, Stephenchair
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 ORDERS
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:
LastnameFirstname
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 Column_name = New_VALUE
Where colorn_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 the 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) Smallint (size) Tinyint (size)
Hold Integers ONLY. The Maximum Number of Digits Are Specified in Parenthes.
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 isot "d".
Char (size)
Holds a fixed length string (can contact size, numbers). The Fixed Size IS Specified in Parenthesis.
VARCHAR (SIZE)
Holds a Variable Length String (CAN Contain Letters, NumBers, and Special Characters). The maximum size isot.
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
Add a field called 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, OLA34
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)
Results of the:
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
