Basic SQL syntax and basic SQL statements

Basic SQL syntax and basic SQL statements

A table that may be involved in an SQL statement:

StudentInfo:

PersonInfo:

Test_outjoin:

Test2_outjoin:

Ii. Basic SQL syntax

If exists (select * from dbo. sysobjects where name = 'studentinfo') -- Query existing views in the database. if this table exists, delete it.
Drop table StudentInfo
Go
Create table StudentInfo
(
Id int identity (20140001,1), -- increases by step size of 1
Name nvarchar (20 ),
Chinese float,
Math float,
English float,
Address nvarchar (20 ),
Tel nvarchar (20 ),
EnTime datetime
)
--------------------- Insert related data in the table
Insert StudentInfo values ('zhang Sanfeng ', 50.5, 69.3, 90.4, 'shandong', '000000', '2017-01-09 00:00:00 ')
Insert StudentInfo values ('Li Xiaofei ', 80.5, 39.3, 90.4, 'hebei', '000000', '2017-01-09 00:00:00 ')
Insert StudentInfo values ('loud', 6.5, 39.3, 90.4, 'hubei ', '000000', '2017-01-09 00:00:00 ')
Insert StudentInfo values ('wang Jie ', 80.5, 79.3, 70.4, 'anhui', '000000', '2017-01-09 00:00:00 ')
-------------------- Query table data
Select name, Math from StudentInfo
------------------- Create a temporary table variable
Declare @ TempStudentInfo table (name nvarchar (20), Chinese float, Math float, English float) -- declare table Variables
Insert into @ TempStudentInfo select name, Chinese, Math, English from StudentInfo ----- insert data into Table Variables
Select * from @ TempStudentInfo
--------------- Custom Data Types (all based on existing data types) method 1 manual
-- In the database ---- programmable --- type --- user-defined data type
--- Method 2 through code implementation
Exec sp_addtype newChar, 'Char (80) ', 'not null'
------------- Case statement
Select *, language =
Case
When Chinese> 60 then 'qualifying'
When Chinese <60 then 'unqualified'
End
From StudentInfo
Go
------------- Cyclic statement
Declare @ mysum int
Declare @ I int
Set @ I = 1
Set @ mysum = 0
While (@ I <101)
Begin
Set @ mysum = @ mysum + @ I
Set @ I + = 1
End
Print @ mysum
Go
------------------- Temporary table
-- Local temporary table
Create table # tempStu
(
Id int,
Name nvarchar (20)
)
Insert into # tempStu select id, name from StudentInfo
Select * from # tempStu
Go
--- Global temporary table
Drop table # tempStu
Create table # tempStu
(
Id int,
Name nvarchar (20)
)
Insert into # tempStu select id, name from StudentInfo
Select * from # tempStu

Select id, name into # temptable from StudentInfo --- insert id and name into the temporary table (a temporary table is created at the same time)
----------------------------- Column Calculation
Select name as name, Chinese as language, Math as mathematics, English as English, Chinese + Math + English as total score from StudentInfo order by total score desc -- default value: ascending descending order, desc
-------- Range Query select * from table id (not) between 17 and 20
------- Use of in
Select * from StudentInfo where Math in (69.3, 79.3)
------------------------------------- Join two tables)
Select * from StudentInfo
----- Gender Table
Create table PersonInfo
(
Id int,
Sex nvarchar (10)
)
Insert into PersonInfo values (20140007, 'female ')
Select * from PersonInfo
--------- Start to connect two tables
Select A1.name as name, A2.sex as gender from StudentInfo A1, PersonInfo A2 where A1.id = A2.id
Go
------- Use of group
Select address as region, Sum (Math) as mathematical score from StudentInfo A1 group by A1.address
-------- How to use distance to select different data usage methods of related columns
Select distinct sex from PersonInfo
--------- Like usage
--- LIKE is another instruction used in the WHERE clause. Basically, LIKE allows us to find the information we need based on a pattern.
-- SELECT "column name" FROM "table name" WHERE "column name" LIKE {format };
-- The overlay model usually includes the following examples:
/*
1 'A _ Z': All strings starting with 'a and ending with 'Z. Both 'abz' and 'a2z' comply with this mode, while 'akkz' does not (because there are two original fonts between A and Z, rather than one original ).
2 'abc % ': All strings starting with 'abc. For example, both 'abc' and 'abcabc' comply with this rule.
3' % xyz': All strings ending with 'xyz. For example, both 'wxyz' and 'zzxyz' comply with this rule.
4' % AN % ': All strings containing the 'A' format. For example, both 'Los Angeles' and 'san FRANCISCO 'comply with this rule.
*/
Select name from StudentInfo where name like 'zhang _'
-------- Use of Having
/*
How can we set conditions for the values produced by functions? For example, we only need to know who scored more than 60. In this case, we cannot use the WHERE command. What should we do? Fortunately, SQL provides a HAVING command, which we can use to achieve this goal. HAVING clauses are usually at the end of an SQL sentence. An SQL statement containing the HAVING clause does not necessarily contain the GROUP BY clause. The syntax of HAVING is as follows:
SELECT "column 1", SUM ("column 2 ")
FROM "table name"
Group by "column 1"
HAVING (function condition );
*/
Select name, sum (Math) from StudentInfo group by name having sum (Math)> 60
/* Internal link and external link: left join, also known as inner join ). In this case, the data will be selected only when both tables have the same value. What should we do if we want to list every piece of information in a table, whether its value is in another table or not? At this time, we need to use the SQL OUTER JOIN command.
The external connection syntax varies with the database. For example, in Oracle, a "(+)" is added to the table in which all data is to be selected in the WHERE clause to indicate that all data in this table is required.
*/
-------------------------------------------------- External link of SQL
-- Recreate two tables
Go
Create table Test_outjoin
(
City nvarchar (30 ),
StoreNummber int,
Infodatetime datetime
)
Create table Test2_outjoin
(
City_in_Where nvarchar (10 ),
City nvarchar (10)
)
Select * from Test_outjoin
Select * from Test2_outjoin
------- Hebei and Beijing are not in Table 1. We will use external links to two tables.
Select A1.City, SUM (A2.StoreNummber) from Test2_outjoin A1 left join Test_outjoin A2 on A1.City = A2.city group by A1.City
-- Orcle is written as select A1.City, SUM (A2.StoreNummber) from Test2_outjoin A1, Test_outjoin A2 where A1.City = A2.city (+) group by A1.City (right connection: A1.City (+) = A2.city)

----------------------- Use of UNION
/*
The purpose of the UNION command is to combine two SQL statements. From this perspective, union is similar to join because both commands can retrieve data from multiple tables. One Limit of union is that the columns generated by two SQL statements must be of the same data type. In addition, when we use the union command, we only see different data values (similar to select distinct)
*/
Go

Select Test_outjoin.city from Test_outjoin union select Test2_outjoin.City from Test2_outjoin -- union of two query zones
-- Union all union all command is to combine the results of two SQL statements. The difference between union all and UNION is that union all lists each qualified data, regardless of whether the data value is repeated.

--- INTERSECT and union are the opposite. They are a bit like and.
Select Test_outjoin.city from Test_outjoin intersect select Test2_outjoin.City from Test2_outjoin

--- MINUS (MSSQL does not exist) different values are listed only once
/* The MINUS command is used in two SQL statements. It first finds out the results produced by the first SQL statement, and then checks whether the results are in the results of the second SQL statement. If so, the information will be removed and will not appear in the final result. If the results produced by the second SQL statement are not included in the results generated by the first SQL statement, the information will be discarded.
*/
----- SQL Concatenate
/* Sometimes, we need to link the data obtained from different columns. Each database provides methods to achieve this purpose:

MySQL: CONCAT ()
Oracle: CONCAT (), |
SQL Server: +
The CONCAT () syntax is as follows:
CONCAT (string 1, string 2, string 3 ,...)
Concatenates string 1, string 2, and string 3. Note that Oracle CONCAT () only allows two parameters. In other words, only two strings can be connected at a time. However, in Oracle, we can use '|' to concatenate multiple strings at a time.

*/
Select Test2_outjoin.City_in_Where + ''+ Test2_outjoin.City as 'test Concatenate' from Test2_outjoin

---- SubString
/* The substring function in SQL is used to capture part of a column. The function names are not exactly the same 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)
Select All characters starting from position <pos> in <str>. Note that this syntax is not applicable to SQL Server.
SUBSTR (str, pos, len)
Start from the <pos> position in <str> and select the <len> character.

*/
-------------------- Trim
/*
The TRIM function in SQL is used to remove the character header or end of a string. The most common purpose is to remove the leading or trailing blank spaces. This function has different names in different databases:
MySQL: TRIM (), RTRIM (), LTRIM ()
Oracle: RTRIM (), LTRIM ()
SQL Server: RTRIM (), LTRIM ()
Syntax of various trim functions:
TRIM ([[location] [string to be removed] FROM] string): the possible value of [location] is LEADING (start), TRAILING (end ), or BOTH (start and end ). This function removes the [string to be removed] from the start, end, or end of the string. If we do not list what the [string to be removed] is, the blank space will be removed.
LTRIM (string): removes white spaces from the start of all strings.
RTRIM (string): removes the white space at the end of all strings.
*/

--------------------- View
--- Create a view from an existing table (StudentInfo)
Create view V_Student
As select name, Chinese, Math, English from StudentInfo
Select * from V_Student
----- Create an index in the name field of the StudentInfo table
Create index index_name_Studentinfo on Studentinfo (name)
------------------ AlTER TABLER changes the table structure
/*
The alter table syntax is as follows:
Alter table "table_name"
[Change method];
• ADD a column: ADD "column 1" "column 1 data type"
• Delete a field: DROP "Field 1"
• CHANGE the column name: CHANGE "original column name" "new column name" "new column name data type"
• Change the category of the column: MODIFY "column 1" "New category"

*/
--- Add a column
Alter table StudentInfo add remarks nvarchar (20)
----- Change the column name (for SQL server, use sp_rename for detailed usage instructions
Exec sp_rename 'studentinfo. name', 'name', 'column'
------------------------ Create a table primary key
/*
Each item in the Primary Key is a unique value in the table. In other words, it is used to uniquely identify each row of data in a table. A primary key can be a column in the original data, or a column created by a person (a column irrelevant to the original data ). A primary key can contain one or more columns. When a primary Key contains multiple columns, it is called a Composite Key ).

The primary key can be set when creating a new TABLE (using the create table statement), or by changing the existing TABLE Schema (using alter table ).

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 the alter table statement to add a primary key, we need to confirm that the field used as the primary key is set to "not null"; that is, the column must not have no information.
*/
------------------------------------------ Delete all data in the TABLE. truncate table command
Truncate table PersonInfo
Select * from PersonInfo
Go
---------------------------------------- Insert
---- Operation form 1: insert into "table name" ("column 1", "column 2 ",...) VALUES ("value 1", "value 2 ",...);
---- Operation form 2: insert into "table 1" ("column 1", "column 2 ",...) SELECT "column 3", "column 4 ",... FROM "Table 2 ";
----------------------------------------- Modify the value UPDATE in the table
----- Syntax: UPDATE "table name" SET "column 1" = [new value] WHERE "condition"; modify several columns simultaneously: UPDATE "table" SET "column 1" = [value 1], "column 2" = [value 2] WHERE "condition ";
---------------------------------------- Some data in the direct database
-- Delete from "table name" WHERE "condition ";
Select * from StudentInfo
Delete from StudentInfo where Math <60

Zookeeper