SQL Learning Content

Basic part:
SQL is divided into two parts: Data manipulation Language (DML) and data definition language (DDL) database management system and relational database management system
The query and update Directives form the DML portion of SQL:
SELECT-Get data from a database table
Update-updating data in a database table
Delete-Deletes data from the database table
INSERT into-inserts data into a database table

The most important DDL statement in SQL:
Create database-Creating new databases
ALTER DATABASE-Modify databases
CREATE table-Creates a new table
ALTER TABLE-Change (change) database table
drop table-Delete tables
Create index-Creating indexes (search key)
Drop INDEX-Delete indexes

Select syntax:
Select Column name from table name/select * FROM table name
Example: SELECT lastname,firstname from Persons
Remove duplicate values: SELECT DISTINCT column name from table name
Example: SELECT DISTINCT Company from Orders

WHERE clause syntax:
SELECT column name from table name WHERE column operator value
Text instance: SELECT * from Persons WHERE firstname= ' Bush '
Numeric instance: SELECT * from Persons WHERE year>1965
Summary: Text value with ' ', value does not add single quotation marks

And and OR operators:
SELECT * from Persons WHERE firstname= ' Thomas ' and lastname= ' Carter '
SELECT * from Persons WHERE firstname= ' Thomas ' OR lastname= ' Carter '
Combine and and OR operators:
SELECT * from Persons WHERE (firstname= ' Thomas ' OR firstname= ' William ') and lastname= ' Carter '

ORDER BY statement (ASC is ascending and desc is descending):
SELECT Company, OrderNumber from Orders ORDER by company
Displays the company name in alphabetical order and displays the order number (OrderNumber) in numerical order:
SELECT Company, OrderNumber from Orders ORDER by company, OrderNumber
Show company name in reverse alphabetical order:
SELECT Company, OrderNumber from Orders ORDER by company DESC
Displays the company name in reverse alphabetical order and displays the sequential number in numerical order:
SELECT Company, OrderNumber from Orders ORDER by company DESC, OrderNumber ASC
Results:
Companyordernumber
w3school2356
w3school6953
IBM3532
Apple4698

INSERT INTO statement:
Insert into table name values (value 1, value 2, value 3)
Specify columns for data insertion: INSERT INTO table_name (column 1, column 2, column 3) VALUES (value 1, value 2, value 3)
Instance:
INSERT into Persons VALUES (' Gates ', ' Bill ', ' xuanwumen ', ' Beijing ')
INSERT into Persons (LastName, Address) VALUES (' Wilson ', ' champs-elysees ')
UPDATE statement:
Update table name set column name = new value where column name = value
Instance:
Update a column in a row: Update person SET FirstName = ' Fred ' WHERE LastName = ' Wilson '
Update several columns in a row: Update person SET Address = ' Zhongshan ", city = ' nanjing ' WHERE LastName = ' Wilson '
Delete statement:
DELETE from table name where column name = value
Instance:
Delete a row: Delete from person Where lastname = ' Wilson '
Delete entire table contents: Delete * from person

Advanced section:
Top:
SQL server:select TOP number|percent column name/column_name (s) from table_name
Mysql:select column name from table_name LIMIT number
Oracle:select column name from table_name WHERE ROWNUM <= number
Instance:
Select the first two rows of records: SELECT TOP 2 * from Persons
Select 50% Records: Select TOP PERCENT * from Persons
Like:
Select Column name from table name where column name like expression ('%a% ')
Instance:
SELECT * from Persons WHERE city like '%lon% '
Not Like:select * from Persons WHERE city is not like '%lon% '
Wildcard: "%" instead of one or more characters, "_" replaces only one character, "[ABC]" in the character of any single character, "[^abc] or [!ABC]" is not a character columns any single character
Instance:
SELECT * from Persons WHERE FirstName like ' _eorge '
SELECT * from Persons WHERE city like ' [aln]% '
SELECT * from Persons WHERE city like ' [! aln]% '
The in:in operator allows us to specify multiple values in the WHERE clause
Select Column name from table name where column name in (value1,value2,...)
Instance:
SELECT * from Persons WHERE LastName in (' Adams ', ' Carter ')
Between: Select a range of data between two values
Select Column name from table name where column name between value1 and value2
Instance:
SELECT * from Persons WHERE LastName between ' Adams ' and ' Carter '
Not Between:select * from Persons WHERE LastName not between ' Adams ' and ' Carter '
Alias aliases:
Alias to table: Select Column name from table name as Alias
Alias column name: Select Column name as alias from table name
Instance:
SELECT LastName as Family, FirstName as Name from Persons
Join: Use to query data from these tables based on the relationship between the columns in two or more tables
Two table Queries And:select persons.lastname, Persons.firstname, Orders.orderno
From Persons, Orders
WHERE persons.id_p = orders.id_p
Inner Join:select persons.lastname, Persons.firstname, Orders.orderno
From Persons
INNER JOIN Orders
On persons.id_p = orders.id_p
ORDER by Persons.lastname
INNER JOIN notation: Select Column name from table 1 inner JOIN table 2 on table 1. Column name = Table 2. Column Name
Left JOIN notation: Select Column name from table 1 left JOIN table 2 on table 1. Column name = Table 2. Column Name
Right join notation: Select Column name from table 1 right Join table 2 on table 1. Column name = Table 2. Column Name
UNION: The result set used by the operator to merge two or more SELECT statements
Select Column name from table 1 UNION Select column name from Table 2
Union selects a different value, union ALL selects all values
Create datebase:
Create Database database_name
Instance:
CREATE DATABASE my_db
CREATE TABLE:
Create Table Table Name
(
Column name 1 data type [constraint type],
Column Name 2 data type [constraint type],
Column name 3 data type [constraint type],
...
)
Instance:
CREATE TABLE Persons
(
id_p int,
LastName varchar (255),
FirstName varchar (255),
Address varchar (255),
City varchar (255)
)
Constraint type:
NOT NULL: null value not Accepted
Unique: Single constraint
To add a unique constraint to an existing table: ALTER table Persons add unique (id_p)
To revoke a UNIQUE constraint:
DROP CONSTRAINT Uc_personid
PRIMARY KEY:
Add PRIMARY key:alter table Persons add PRIMARY KEY (id_p) for existing tables
Undo Primary Key:alter TABLE Persons DROP CONSTRAINT Pk_personid
FOREIGN key constraint:
CREATE TABLE Orders
(
id_o int not NULL PRIMARY KEY,
OrderNo int not NULL,
id_p int FOREIGN KEY REFERENCES Persons (id_p)
)
To add a foreign key constraint to an existing table:
ALTER TABLE Orders
ADD FOREIGN KEY (id_p)
REFERENCES Persons (id_p)
Revoke foreign key:alter TABLE Orders DROP CONSTRAINT fk_perorders

DROP:
Drop Index:drop Index Table_name.index_name
Drop TABLE:DROP Table table_name
Drop Database:drop Database database_name
Clear table data does not delete structure: Truncate table name
Alter:
Add Column: Alter table table_name Add column name data type
Delete Column: Alter table table_name Drop column name
Change column data type: ALTER TABLE table_name ALTER COLUMN name data type

View:create View:
CREATE VIEW View_name as
SELECT column_name (s)
From table_name
WHERE condition
Instance:
CREATE VIEW [Current Product List] as
SELECT Productid,productname
From Products
WHERE Discontinued=no
To update the view:
CREATE VIEW [Current Product List] as
SELECT productid,productname,category
From Products
WHERE Discontinued=no
Undo View:
DROP VIEW view_name
Null value: Method of judgment, is NULL, was NOT NULL
Instance:
SELECT lastname,firstname,address from Persons
WHERE Address is NULL

SELECT lastname,firstname,address from Persons
WHERE Address is not NULL

IsNull (): If a null value participates in the calculation of 0
SELECT productname,unitprice* (Unitsinstock+isnull (unitsonorder,0))
From Products
Subquery: In one query, the condition is another query, the instance
SELECT Customer from Orders
WHERE orderprice> (SELECT AVG (Orderprice) from Orders)

Find out the names of all students who have not been enrolled in the "Li Ming" teacher course
--Implementation code:
Select SNAME from S where not EXISTS (SELECT * from Sc,c Where SC. Cno=c.cno and Cname= ' Li Ming ' and SC. SNO=S.SNO)
Count: Calculate the number of orders for customer "Carter"
SELECT COUNT (Customer) as Customernilsen from Orders
WHERE customer= ' Carter '
To calculate the total number of rows:
SELECT COUNT (*) as numberoforders from Orders
Calculate the number of different customers:
SELECT COUNT (DISTINCT Customer) as Numberofcustomers from Orders
Now (): Returns the current date and time, SELECT ProductName, UnitPrice, today () as Perdate from products

Basic part:
SQL is divided into two parts: Data manipulation Language (DML) and data definition language (DDL) database management system and relational database management system
The query and update Directives form the DML portion of SQL:
SELECT-Get data from a database table
Update-updating data in a database table
Delete-Deletes data from the database table
INSERT into-inserts data into a database table

The most important DDL statement in SQL:
Create database-Creating new databases
ALTER DATABASE-Modify databases
CREATE table-Creates a new table
ALTER TABLE-Change (change) database table
drop table-Delete tables
Create index-Creating indexes (search key)
Drop INDEX-Delete indexes

Select syntax:
Select Column name from table name/select * FROM table name
Example: SELECT lastname,firstname from Persons
Remove duplicate values: SELECT DISTINCT column name from table name
Example: SELECT DISTINCT Company from Orders

WHERE clause syntax:
SELECT column name from table name WHERE column operator value
Text instance: SELECT * from Persons WHERE firstname= ' Bush '
Numeric instance: SELECT * from Persons WHERE year>1965
Summary: Text value with ' ', value does not add single quotation marks

And and OR operators:
SELECT * from Persons WHERE firstname= ' Thomas ' and lastname= ' Carter '
SELECT * from Persons WHERE firstname= ' Thomas ' OR lastname= ' Carter '
Combine and and OR operators:
SELECT * from Persons WHERE (firstname= ' Thomas ' OR firstname= ' William ') and lastname= ' Carter '

ORDER BY statement (ASC is ascending and desc is descending):
SELECT Company, OrderNumber from Orders ORDER by company
Displays the company name in alphabetical order and displays the order number (OrderNumber) in numerical order:
SELECT Company, OrderNumber from Orders ORDER by company, OrderNumber
Show company name in reverse alphabetical order:
SELECT Company, OrderNumber from Orders ORDER by company DESC
Displays the company name in reverse alphabetical order and displays the sequential number in numerical order:
SELECT Company, OrderNumber from Orders ORDER by company DESC, OrderNumber ASC
Results:
Companyordernumber
w3school2356
w3school6953
IBM3532
Apple4698

INSERT INTO statement:
Insert into table name values (value 1, value 2, value 3)
Specify columns for data insertion: INSERT INTO table_name (column 1, column 2, column 3) VALUES (value 1, value 2, value 3)
Instance:
INSERT into Persons VALUES (' Gates ', ' Bill ', ' xuanwumen ', ' Beijing ')
INSERT into Persons (LastName, Address) VALUES (' Wilson ', ' champs-elysees ')
UPDATE statement:
Update table name set column name = new value where column name = value
Instance:
Update a column in a row: Update person SET FirstName = ' Fred ' WHERE LastName = ' Wilson '
Update several columns in a row: Update person SET Address = ' Zhongshan ", city = ' nanjing ' WHERE LastName = ' Wilson '
Delete statement:
DELETE from table name where column name = value
Instance:
Delete a row: Delete from person Where lastname = ' Wilson '
Delete entire table contents: Delete * from person

Advanced section:
Top:
SQL server:select TOP number|percent column name/column_name (s) from table_name
Mysql:select column name from table_name LIMIT number
Oracle:select column name from table_name WHERE ROWNUM <= number
Instance:
Select the first two rows of records: SELECT TOP 2 * from Persons
Select 50% Records: Select TOP PERCENT * from Persons
Like:
Select Column name from table name where column name like expression ('%a% ')
Instance:
SELECT * from Persons WHERE city like '%lon% '
Not Like:select * from Persons WHERE city is not like '%lon% '
Wildcard: "%" instead of one or more characters, "_" replaces only one character, "[ABC]" in the character of any single character, "[^abc] or [!ABC]" is not a character columns any single character
Instance:
SELECT * from Persons WHERE FirstName like ' _eorge '
SELECT * from Persons WHERE city like ' [aln]% '
SELECT * from Persons WHERE city like ' [! aln]% '
The in:in operator allows us to specify multiple values in the WHERE clause
Select Column name from table name where column name in (value1,value2,...)
Instance:
SELECT * from Persons WHERE LastName in (' Adams ', ' Carter ')
Between: Select a range of data between two values
Select Column name from table name where column name between value1 and value2
Instance:
SELECT * from Persons WHERE LastName between ' Adams ' and ' Carter '
Not Between:select * from Persons WHERE LastName not between ' Adams ' and ' Carter '
Alias aliases:
Alias to table: Select Column name from table name as Alias
Alias column name: Select Column name as alias from table name
Instance:
SELECT LastName as Family, FirstName as Name from Persons
Join: Use to query data from these tables based on the relationship between the columns in two or more tables
Two table Queries And:select persons.lastname, Persons.firstname, Orders.orderno
From Persons, Orders
WHERE persons.id_p = orders.id_p
Inner Join:select persons.lastname, Persons.firstname, Orders.orderno
From Persons
INNER JOIN Orders
On persons.id_p = orders.id_p
ORDER by Persons.lastname
INNER JOIN notation: Select Column name from table 1 inner JOIN table 2 on table 1. Column name = Table 2. Column Name
Left JOIN notation: Select Column name from table 1 left JOIN table 2 on table 1. Column name = Table 2. Column Name
Right join notation: Select Column name from table 1 right Join table 2 on table 1. Column name = Table 2. Column Name
UNION: The result set used by the operator to merge two or more SELECT statements
Select Column name from table 1 UNION Select column name from Table 2
Union selects a different value, union ALL selects all values
Create datebase:
Create Database database_name
Instance:
CREATE DATABASE my_db
CREATE TABLE:
Create Table Table Name
(
Column name 1 data type [constraint type],
Column Name 2 data type [constraint type],
Column name 3 data type [constraint type],
...
)
Instance:
CREATE TABLE Persons
(
id_p int,
LastName varchar (255),
FirstName varchar (255),
Address varchar (255),
City varchar (255)
)
Constraint type:
NOT NULL: null value not Accepted
Unique: Single constraint
To add a unique constraint to an existing table: ALTER table Persons add unique (id_p)
To revoke a UNIQUE constraint:
DROP CONSTRAINT Uc_personid
PRIMARY KEY:
Add PRIMARY key:alter table Persons add PRIMARY KEY (id_p) for existing tables
Undo Primary Key:alter TABLE Persons DROP CONSTRAINT Pk_personid
FOREIGN key constraint:
CREATE TABLE Orders
(
id_o int not NULL PRIMARY KEY,
OrderNo int not NULL,
id_p int FOREIGN KEY REFERENCES Persons (id_p)
)
To add a foreign key constraint to an existing table:
ALTER TABLE Orders
ADD FOREIGN KEY (id_p)
REFERENCES Persons (id_p)
Revoke foreign key:alter TABLE Orders DROP CONSTRAINT fk_perorders

DROP:
Drop Index:drop Index Table_name.index_name
Drop TABLE:DROP Table table_name
Drop Database:drop Database database_name
Clear table data does not delete structure: Truncate table name
Alter:
Add Column: Alter table table_name Add column name data type
Delete Column: Alter table table_name Drop column name
Change column data type: ALTER TABLE table_name ALTER COLUMN name data type

View:create View:
CREATE VIEW View_name as
SELECT column_name (s)
From table_name
WHERE condition
Instance:
CREATE VIEW [Current Product List] as
SELECT Productid,productname
From Products
WHERE Discontinued=no
To update the view:
CREATE VIEW [Current Product List] as
SELECT productid,productname,category
From Products
WHERE Discontinued=no
Undo View:
DROP VIEW view_name
Null value: Method of judgment, is NULL, was NOT NULL
Instance:
SELECT lastname,firstname,address from Persons
WHERE Address is NULL

SELECT lastname,firstname,address from Persons
WHERE Address is not NULL

IsNull (): If a null value participates in the calculation of 0
SELECT productname,unitprice* (Unitsinstock+isnull (unitsonorder,0))
From Products
Subquery: In one query, the condition is another query, the instance
SELECT Customer from Orders
WHERE orderprice> (SELECT AVG (Orderprice) from Orders)

Find out the names of all students who have not been enrolled in the "Li Ming" teacher course
--Implementation code:
Select SNAME from S where not EXISTS (SELECT * from Sc,c Where SC. Cno=c.cno and Cname= ' Li Ming ' and SC. SNO=S.SNO)
Count: Calculate the number of orders for customer "Carter"
SELECT COUNT (Customer) as Customernilsen from Orders
WHERE customer= ' Carter '
To calculate the total number of rows:
SELECT COUNT (*) as numberoforders from Orders
Calculate the number of different customers:
SELECT COUNT (DISTINCT Customer) as Numberofcustomers from Orders
Now (): Returns the current date and time, SELECT ProductName, UnitPrice, today () as Perdate from products
Group by:select customer,sum (orderprice) from OrdersGROUP by Customer

SQL Learning Content