SQL _ Basics

No matter whether you are a newbie to SQL, you should be a beginner in the data warehousing industry who needs to review SQL. This SQL textbook website lists commonly used SQL commands. This website contains the following parts:
-SQL commands: How SQL is used to store, read, and process data in the database.
-Table processing: How SQL is used to process tables in a database.
-SQL Syntax: This page lists all the SQL syntaxes mentioned in this tutorial.
For every directive in the explain command, we will first list and explain the syntax of this directive, and then we will use an example to explain to readers how this directive is used. After reading all the teaching materials on this website, you will have a general understanding of SQL syntax. In addition, you will be able to correctly use SQL to obtain information from the database. The author's own experience is that although a thorough understanding of SQL can be completed overnight, it is difficult to have a basic understanding of SQL. I hope you will have the same idea after reading this website.

SQL select

SELECT store_name FROM Store_Information 

SQL distinct

SELECT DISTINCT store_name FROM Store_Information

SQL where

SELECT store_name  FROM Store_Information  WHERE Sales > 1000  

SQL and or

SELECT store_name  FROM Store_Information  WHERE Sales > 1000  OR (Sales < 500 AND Sales > 275) 

SQL in

SELECT *  FROM Store_Information  WHERE store_name IN ('Los Angeles', 'San Diego')  

SQL

SELECT *  FROM Store_Information  WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999' 

SQL like

SELECT *  FROM Store_Information  WHERE store_name LIKE '%AN%' 

SQL order

SELECT store_name, Sales, Date  FROM Store_Information  ORDER BY Sales DESC 

You can use the column name to specify the basis for the sort order. Besides the column name
In addition, we can also use the column Order (based on the order in the SQL statement ). After Select
The first column is 1, the second column is 2, and so on. In the above example,
The following SQL statement can achieve the same effect:

SELECT store_name, Sales, Date  FROM Store_Information  ORDER BY 2 DESC  

SQL Functions

Since there are many types of data in the database, it is very important to be able to perform some operations on these numbers, such as combining them, the warning is to find out their average values. SQL provides some such functions. They are:
AVG (average)
Count)
Max (maximum)
Min (minimum)
Sum (SUM)
The method of using the function is:
Select "function name" ("column name ")
From "table name"

SELECT SUM(Sales) FROMStore_Information  

SQL count

SELECT COUNT(store_name)  FROM Store_Information  WHERE store_name is not NULL

Count and distinct are often used together in order to find out the data in the table that contains the same number of records (which is really important to explain ). For example, if we want to find out how many different store_names exist in our table, we will enter,

SELECT COUNT(DISTINCT store_name)  FROM Store_Information  

SQL group

SELECT store_name, SUM(Sales)  FROM Store_Information  GROUP BY store_name  

When we choose to use a column with at least one column containing the function, we need to use the group by command. In this case, we need to determine that we have all columns of group. In other words, we need to place all the columns including functions in the group by clause.

SQL having

How can we set conditions for the values produced by functions? For example, we may need to know which stores have a turnover of more than $1,500. In this case, we can use the where command. What should we do? Very good, SQL provides a having command, and we can use this command to achieve this goal. Having clauses are usually at the end of an SQL sentence. An SQL statement containing the having clause must contain the group by clause. The methods of having are as follows:
Select "column 1", sum ("column 2 ")
From "table name"
Group by "column 1"
Having (function condition)
Note: The group by clause limit is required.

SELECT store_name, SUM(sales)  FROM Store_Information  GROUP BY store_nameHAVING SUM(sales) > 1500  

SQL alias

SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"  FROM Store_Information A1GROUP BY A1.store_name  

SQL connection

○ Inner join: only the matched rows are connected.
Left Outer Join: contains all rows in the left table (no matter whether the table on the right has rows matching them) and all matched rows in the right table
Right outer join: contains all rows in the right table (no matter whether the left table has rows matching them) and all matched rows in the left table
Full outer join: contains all rows in the left and right tables, regardless of whether the tables on the other side have rows matching them.
Inner join: only the matched rows are connected.
Cross join generates Cartesian Product-instead of using any matching or selection conditions, it directly matches each row in a data source with each row in another data source.

For example.
Table
ID name
1
2 Li
3 Wang

Table B
ID address a_id
1 Beijing 1
2 Shanghai 3
3 Nanjing 10
○/******************************** Left join ** ***************************************/
Inclusive: Table A is inclusive of Table B, and left join are complete. (left join or left Outer Join)
The SQL statement is as follows:

SELECT A.name, B.addressFROM ALEFT JOIN B ON A.id = B.A_id

The query result is:
Name address
Zhang beijing.com
Li null
Wang Shanghai
○/******************************** Right join ** ***************************************/
Inclusive: Table B is inclusive of Table A, and the right join to the right table is full. (right join or right Outer Join)
The SQL statement is as follows:

SELECT A.name, B.addressFROM ARIGHT JOIN B ON A.id = B.A_id

The query result is:
Name address
Zhang beijing.com
Wang Shanghai
Null Nanjing
○/********************************* Full join ** ***************************************/
Note: All data in the two data sets involved in the connection is returned, regardless of whether they have rows matching them. It is equivalent

Perform left Outer Join and right Outer Join for the two data sets respectively, and then use the result set to remove duplicate rows and combine the above two results into a result set.

. (Full join or full outer join)
The SQL statement is as follows:

select * from Afull join B

The query result is:
ID name ID address a_id
1 Sheet 1 Beijing 1
2 Li 1 Beijing 1
3 wang 1 Beijing 1
1 Sheet 2 Shanghai 3
2 Li 2 Shanghai 3
3 Wang 2 Shanghai 3
1 piece 3 Nanjing 10
2 Li 3 Nanjing 10
3 Wang 3 Nanjing 10
○/********************************** Inner join connections ** ***************************************/
Exclusive: rows are returned only when at least one matching clause exists in Table A and table B. Intersection of two tables
The SQL statement is as follows:

select A.name,B.address from Ainner join Bon A.id = B.A_id

The query result is:
Name address
Zhang beijing.com
Wang Shanghai
Inner join is equivalent to the following SQL statement:

SELECT A.name, B.addressFROM A, BWHERE A.id = B.A_id

○/******************************** Cross join (without conditions) where ...) ***********************************/
Note: 3*3 = 9 records are returned, that is, Cartesian product.
The SQL statement is as follows:

SELECT * FROM ACROSS JOIN B

The query result is:
ID name ID address a_id
1 Sheet 1 Beijing 1
2 Li 1 Beijing 1
3 wang 1 Beijing 1
1 Sheet 2 Shanghai 3
2 Li 2 Shanghai 3
3 Wang 2 Shanghai 3
1 piece 3 Nanjing 10
2 Li 3 Nanjing 10
3 Wang 3 Nanjing 10
Cross join is equivalent:

select * from A,B

Note:
1. On a. ID = B. ID is equivalent to using (ID) // field names must be the same here
2. When MySQL retrieves information from a table, you can prompt which index it chooses.
This feature is useful if the explain command shows that MySQL uses an index that may be incorrect in the index list.
By specifying the use index (key_list), you can tell MySQL to use the most appropriate index to find record rows in the table.
The optional syntax ignore index (key_list) can be used to tell MySQL not to use a specific index.

Efficiency:
1. Inner join is faster than left join
Note: inner join is equivalent to the following SQL statement: select a. Name, B. address from a, B where a. ID = B. A _ id
Therefore, you can use a normal connection.
2. Create an index by connecting Fields

SQL subquery

SELECT SUM(Sales) FROM Store_InformationWHERE Store_name IN(SELECT store_name FROM Geography  WHERE region_name = 'West')  

SQL Union

The purpose of the Union command is to combine the results of two SQL statement statements. When we use the Union command
See different data values (similar to select distinct ).

SELECT Date FROM Store_InformationUNIONSELECT Date FROM Internet_Sales  

SQL Union all

The purpose of the Union all command is to combine the results of two SQL statement clauses.
Union all and union are different from each other. Union all records that meet the conditions.
Are listed, regardless of whether the data value is repeated.

SELECT Date FROM Store_InformationUNION ALLSELECT Date FROM Internet_Sales 

SQL intersect

Union is the Union, while intersect is the intersection.

SELECT Date FROM Store_InformationINTERSECTSELECT Date FROM Internet_Sales  

SQL minus

Union is the Union, Intersect is the intersection, and minus is the complement.

SELECT Date FROM Store_InformationMINUSSELECT Date FROM Internet_Sales  

SQL concatenate

Sometimes, we need to link the data obtained from different columns. Each type of resource
Pipeline provides methods to achieve this goal:
MySQL: Concat ()
ORACLE: Concat (), |
SQL Server: +
Concat (string 1, string 2, string 3,...): concatenates string 1, string 2, string 3, and other words. Note that the Concat () delimiter of Oracle allows two parameters. In other words, the Concat () delimiter can concatenate two strings at a time. In Oracle, we can use '|' to concatenate character strings at a time.

MySQL/Oracle:

SELECT CONCAT(region_name,store_name) FROM Geography  WHERE store_name = 'Boston';  

SQL Server:

SELECT region_name + ' ' + store_name FROM Geography  WHERE store_name = 'Boston';  

SQL substring

The substring function in SQL is used to capture part of a column.
The name of this function is different in different databases:
MySQL: substr (), substring ()
ORACLE: substr ()
SQL Server: substring ()

The most common method is as follows (Here we use substr () as an example ):
Substr (STR, POS): from <STR>, select all words starting from position <POS>.
RMB. Note that this logon method is applicable to dynamic SQL Server.
Substr (STR, POs, Len): Starting from the <POS> position in <STR>, select next
<Len> characters. Suppose we have the following table:

Example 1:

SELECT SUBSTR(store_name, 3)  FROM Geography  WHERE store_name = 'Los Angeles';  

Result:
'S Angeles'
Example 2:

SELECT SUBSTR(store_name,2,4)  FROM Geography  WHERE store_name = 'San Diego';  

Result:
'An d'

SQL trim

The TRIM function in SQL is used to remove the character headers and ending rules from a string. Most common
The purpose is to remove the white space at the beginning and end of the ring. This function is different in different databases.
Name:
MySQL: trim (), rtrim (), ltrim ()
ORACLE: rtrim (), ltrim ()
SQL Server: rtrim (), ltrim ()

Example 1:

SELECT TRIM('   Sample   ');  

Result:
'Sample'
Example 2:

SELECT LTRIM('   Sample   ');  

Result:
'Sample'
Example 3:

SELECT RTRIM('   Sample   ');  

Result:
'Sample'

SQL CREATE TABLE

CREATE TABLE customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date date)  

SQL create View

The following describes how to create a visual table:
Create View "view_name" as "SQL statement"

CREATE VIEW V_CustomerAS SELECT First_Name, Last_Name, CountryFROM Customer 

CREATE VIEW V_REGION_SALESAS SELECT A1.region_name REGION, SUM(A2.Sales) SALESFROM Geography A1, Store_Information A2WHERE A1.store_name = A2.store_nameGROUP BY A1.region_name 

Select * From v_region_sales
Result:
Region Sales
East: $700
West $2050

SQL create Index

The indexing method is as follows:

CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME) 

CREATE INDEX IDX_CUSTOMER_LAST_NAMEon CUSTOMER (Last_Name) 

CREATE INDEX IDX_CUSTOMER_LOCATIONon CUSTOMER (City, Country) 

SQL ALTER TABLE

Alter table customer add gender char (1) // Add a column alter table customer change address ADDR char (50) // modify the column name alter table customer modify ADDR char (30) // modify the field type alter table customer drop gender // delete a field

SQL primary key

The following describes how to set the primary key when creating a new table:
MySQL:

CREATE TABLE Customer  (SID integer,  Last_Name varchar(30),  First_Name varchar(30),  PRIMARY KEY (SID));  

ORACLE:

CREATE TABLE Customer  (SID integer PRIMARY KEY,  Last_Name varchar(30),  First_Name varchar(30));  

SQL Server:

CREATE TABLE Customer  (SID integer PRIMARY KEY,  Last_Name varchar(30),  First_Name varchar(30));  

The following describes how to change the existing table schema to set the primary key:
MySQL:

ALTER TABLE Customer ADD PRIMARY KEY (SID);  

ORACLE:

ALTER TABLE Customer ADD PRIMARY KEY (SID);  

SQL Server:

ALTER TABLE Customer ADD PRIMARY KEY (SID);  

Note that before using alter table statement to add a primary key pair, we need to ensure that the primary key is used
The column used as the primary key is set to "not null"; that is to say, that column is
No information is available.

SQL foreign key

The following lists several ways to specify Foreign keys when creating an orders table:
MySQL:

CREATE TABLE ORDERS  (Order_ID integer,  Order_Date date,  Customer_SID integer,  Amount double,  Primary Key (Order_ID),  Foreign Key (Customer_SID) references CUSTOMER(SID));  

ORACLE:

CREATE TABLE ORDERS  (Order_ID integer primary key,  Order_Date date,  Customer_SID integer references CUSTOMER(SID),  Amount double);  

SQL Server:

CREATE TABLE ORDERS  (Order_ID integer primary key,  Order_Date datetime,  Customer_SID integer references CUSTOMER(SID),  Amount double);  

In the following example, the foreign key is specified by changing the table schema. Assume that orders
The table has been created, but the foreign key has not been specified:
MySQL:

ALTER TABLE ORDERS  ADD FOREIGN KEY (customer_sid) REFERENCESCUSTOMER(sid);  

ORACLE:

ALTER TABLE ORDERS  ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid)REFERENCES CUSTOMER(sid);  

SQL Server:

ALTER TABLE ORDERS  ADD FOREIGN KEY (customer_sid) REFERENCESCUSTOMER(sid);  

SQL drop table

DROP TABLE customer.  

SQL truncate table

TRUNCATE TABLE customer. 

 

SQL insert

INSERT INTO Store_Information (store_name, Sales, Date)SELECT store_name, Sales, DateFROM Sales_InformationWHERE Year(Date) = 1998  

SQL update

UPDATE Store_InformationSET Sales = 500WHERE store_name = "Los Angeles"  AND Date = "Jan-08-1999"  

SQL Delete from

DELETE FROM Store_InformationWHERE store_name = "Los Angeles"  

SQL concise syntax reference

Select
Select "column" from "table name"
Distinct
Select distinct "column"
From "table name"
Where
Select "column"
From "table name"
Where "condition"
And/or
Select "column"
From "table name"
Where "simple condition"
{[And | or] "simple condition"} +
In
Select "column"
From "table name"
Where "column" in ('value 1', 'value 2 ',...)

Between
Select "column"
From "table name"
Where "column" between' value: 1' and 'value: 2'
Like
Select "column"
From "table name"
Where "column" like {mode}
Order
Select "column"
From "table name"
[Where "condition"]
Order by "column" [ASC, DESC]

Count
Select count ("column ")
From "table name"
Group
Select "column 1", sum ("column 2 ")
From "table name"
Group by "column 1"
Having
Select "column 1", sum ("column 2 ")
From "table name"
Group by "column 1"
Having (column)
Create Table
Create Table "table name"
("Column 1" "column 1 Data Type ",
"Column 2" "column 2 data types ",
...)
Drop table
Drop table "table name"
Truncate table
Truncate table "table name"
Insert
Insert into "table name" ("column 1", "column 2 ",...)
Values ("value 1", "value 2 ",...)
Update
Update "table name"
Set "column 1" = [new value]
Where {condition}
Delete from
Delete from "table name"
Where {condition}