SQL Basic Syntax

A table that may be involved in an SQL statement:

Studentinfo:

PersonInfo:

Test_outjoin:

Test2_outjoin:

Two basic SQL syntax

if exists (SELECT * from dbo.sysobjects where name= ' Studentinfo ')--query The view that exists in the database if the table is present to delete
drop table Studentinfo
Go
CREATE TABLE Studentinfo
(
ID int identity (20140001,1),--Grow by step 1
Name nvarchar (20),
Chinese float,
Math float,
中文版 float,
Address nvarchar (20),
Tel nvarchar (20),
Entime datetime
)
---------------------inserting related data in a table
Insert Studentinfo values (' Zhang Sanfeng ', 50.5,69.3,90.4, ' Shandong ', ' 47356454 ', ' 2014-01-09 00:00:00 ')
Insert Studentinfo values (' Li Xiaofei ', 80.5,39.3,90.4, ' Hebei ', ' 47344454 ', ' 2014-01-09 00:00:00 ')
Insert Studentinfo values (' Ludi ', 6.5,39.3,90.4, ' Hubei ', ' 47344454 ', ' 2014-01-09 00:00:00 ')
Insert Studentinfo values (' King solution ', 80.5,79.3,70.4, ' Anhui ', ' 4777454 ', ' 2014-01-09 00:00:00 ')
--------------------querying data in a table
Select Name,math from Studentinfo
-------------------Creating temporary table variables
Declare @TempStudentInfo table (name nvarchar, Chinese float,math float,english float)--Declaration table variable
Insert INTO @TempStudentInfo select Name,chinese,math,english from studentinfo-----inserting data into a table variable
SELECT * FROM @TempStudentInfo
---------------Custom data types (both based on existing data types) Method 1 manual
------Programmability---types---user-defined data types in the database
---Way 2 is implemented by code
exec sp_addtype Newchar, ' char ', ' NOT NULL '
-------------Case Statement
SELECT *, Chinese =
Case
When chinese>60 and then ' qualified '
When chinese<60 and then ' unqualified '
End
From Studentinfo
Go
-------------Loop Statements
DECLARE @mysum int
DECLARE @i int
Set @i=1
Set @mysum =0
while (@i<101)
Begin
Set @[email protected]+@i
Set @i+=1
End
Print @mysum
Go
-------------------Temporary tables
--Local temporary table
CREATE TABLE #tempStu
(
ID int,
Name nvarchar (20)
)
Insert INTO #tempStu select Id,name from Studentinfo
SELECT * FROM #tempStu
Go
---global temp 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 the from Studentinfo---Inserts the ID and name into the staging table (with the temporary table created)
Calculation of-----------------------------column
Select Name as the name, Chinese as language, math as mathematics, Chinese as English, chinese+math+english as total score from Studentinfo ORDER by total desc--default to Ascending descending is desc
--------Interval Query select * FROM table ID (not) between 20
Use of-------in
SELECT * from Studentinfo where Math in (69.3,79.3)
-------------------------------------connecting two sheets (join)
SELECT * FROM Studentinfo
-----Sex Chart
CREATE TABLE PersonInfo
(
ID int,
Sex nvarchar (10)
)
INSERT into PersonInfo values (20140007, ' female ')
SELECT * FROM PersonInfo
---------Start connecting two sheets
Select A1.name as name, a2.sex as sex from Studentinfo A1,personinfo A2 where a1.id=a2.id
Go
Use of-------GROUP by
Select Address as region, Sum (math) as math score from Studentinfo A1 GROUP by a1.address
--------use of distance to select different data usage for related columns
Select DISTINCT sex from PersonInfo
---------use of Like
---like is another directive that will be used in the WHERE clause. Basically, like allows us to find the information we need based on a nested pattern.
--select "field name" from "Table name" WHERE "field name" like {nested};
--nested often include wild card a few examples:
/*
1 ' a_z ': All beginning with ' A ', another word of any value, and a string ending with ' Z '. Both ' ABZ ' and ' a2z ' conform to this pattern, and ' akkz ' does not conform (because there are two characters between A and Z, not a single word).
2 ' abc% ': all the strings beginning with ' ABC '. For example, ' ABCD ' and ' abcabc ' conform to this set of formula.
3 '%xyz ': all strings ending with ' XYZ '. For example, ' wxyz ' and ' zzxyz ' all fit this set.
4 '%an% ': All strings containing ' an ' this set. For example, ' LOS ANGELES ' and ' SAN FRANCISCO ' match this set.
*/
Select name from studentinfo where name like ' Zhang _ '
The use of--------having
/*
So how do we set the conditions for the values that the function produces? For example, we may only need to know who has a score of more than 60. In this case, we cannot use the where directive. What do we do then? Fortunately, SQL provides a having command, and we can use this command to achieve this goal. The HAVING clause is usually at the end of a SQL sentence. A SQL containing a HAVING clause does not necessarily include a GROUP by clause. Having the following syntax:
Select "Field 1", SUM ("Field 2")
From "Table name"
GROUP by "Field 1"
Having (function condition);
*/
Select name, sum (math) from Studentinfo GROUP by name have sum (math) >60
/* Internal links and External links: Left join (left join), also known as internal connection (inner join). In this case, the same value should be found in all two tables, and the information will be selected. What if we want to list each of the data in a table, regardless of whether its value appears in another table? At this point, we need to use the SQL OUTER join (external connection) instructions.
The syntax for an external connection differs depending on the database. For example, on Oracle, we would add a "(+)" to the table in the WHERE clause to select all the data, and we would say all the information in the table.
*/
--------------------------------------------------External links to SQL
--Re-building 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 one we will use external links 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
The--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 the-----------------------Union
/*
The purpose of the UNION directive is to merge two SQL statements. From this point of view, the Union is similar to join, because these two commands can have multiple tables to retrieve data. One limitation of the Union is that the columns generated by the two SQL statements need to be of the same type of data. In addition, when we use the Union command we will 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--two types of query area and set
--union All UNION All the purpose of this instruction is to merge the results of the two SQL statements together. The Union all and union differ in that union all lists each qualifying data, regardless of whether the data values are duplicates or not

---INTERSECT is the opposite of Union. He's kind of like and takes the intersection.
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 directive is used on two SQL statements. It first finds out the results of the first SQL statement and then looks at whether the results are in the result of a second SQL statement. If so, the information is removed, not in the final results. If the result of the second SQL statement does not exist in the result of the first SQL statement, the data is discarded.
*/
-----SQL Concatenate
/* Sometimes, we need to concatenate the data obtained from different fields. Each database provides a way to achieve this:

Mysql:concat ()
Oracle:concat (), | |
SQL Server: +
The syntax for CONCAT () is as follows:
CONCAT (String 1, String 2, String 3, ...)
Concatenate string 1, String 2, String 3, and so on. Please note that Oracle's CONCAT () only allows two parameters, in other words, only two strings can be concatenated at a time. However, in Oracle, we can concatenate multiple strings at once with ' | | '.

*/
Select Test2_outjoin. City_in_where+ ' +test2_outjoin. City as ' Test concatenate ' from Test2_outjoin

----SubString
The substring function in/*sql is used to grab a portion of a field data. The name of this function is not exactly the same in different repositories:

Mysql:substr (), SUBSTRING ()
Oracle:substr ()
SQL server:substring ()
The most commonly used methods are as follows (here we use SUBSTR () as an example):
SUBSTR (str, POS)
From <str>, select all characters starting with the <pos> position. Please note that this syntax does not apply to SQL Server.
SUBSTR (str, POS, len)
Starting with the <pos> position in <str>, select the next <len> characters.

*/
--------------------Trim
/*
The TRIM function in SQL is used to remove the head or end of a string. The most common use is to remove whitespace from the beginning or end of the word. This function has different names in different repositories:
Mysql:trim (), RTRIM (), LTRIM ()
Oracle:rtrim (), LTRIM ()
SQL Server:rtrim (), LTRIM ()
The syntax for the various trim functions is as follows:
TRIM ([[position] [string to remove] from] string: Possible values for [position] are leading (beginning), TRAILING (end), or BOTH (beginning and ending). This function removes the string [to be removed] from the beginning, end, or beginning and end of the string. If we don't list what the [string to remove] is, the blanks will be removed.
LTRIM (String): Removes all whitespace from the beginning of the string.
RTRIM (String): Removes all whitespace from the end of the string.
*/

---------------------views View
---Create a view with an existing table (Studentinfo)
CREATE VIEW V_student
As select Name,chinese,math,english from Studentinfo
SELECT * FROM V_student
-----Indexing in the Name field of the Studentinfo table
Create INDEX Index_name_studentinfo on Studentinfo (name)
------------------alter tabler ALTER TABLE structure
/*
The syntax of ALTER TABLE is as follows:
ALTER TABLE "table_name"
[Change mode];
? Add a field: Add "Field 1" "Field 1 data Type"
? by deleting a field: DROP "Field 1"
Change field Name: "Original field name" "New Field Name" "New Field name data type"
Change the field of information type: MODIFY "Field 1" "New data Type"

*/
---add a column
Alter table studentinfo add comment nvarchar (20)
-----Change the column name (SQL Server use the sp_rename stored procedure for detailed usage please check the information
exec sp_rename ' studentinfo.name ', ' name ', ' COLUMN '
------------------------Create a table primary key
/*
Each of the data in the primary key (Primary key) is a unique value in the table. In other words, it is a unique way to identify each row of data in a table. The primary key can be a field in the original data, or a man-made field (a field that has no relation to the original data). A primary key can contain one or more fields. When a primary key contains more than one field, it is called a key combination (Composite key).

The primary key can be set when a new table is built (using the CREATE table statement) or by changing the existing table schema (using ALTER TABLE).

Here are a few ways to set a primary key when you are building 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));

Here's how you can set the primary key by changing the existing table schema:
[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 adding the primary key with the ALTER TABLE statement, we need to make sure that the field used as the master key is set to "not NULL"; that is, the field must not have data.
*/
------------------------------------------Delete all of the data in the Table TRUNCATE TABLE directive
TRUNCATE TABLE PersonInfo
SELECT * FROM PersonInfo
Go
Inserting INSERT INTO----------------------------------------data
----Operation form one: INSERT into "table name" ("Field 1", "Field 2", ...) VALUES ("Value 1", "Value 2", ...);
----Operation form two: INSERT into "Table 1" ("Field 1", "Field 2", ...) Select "Field 3", "Field 4", ... From "Form 2";
-----------------------------------------modify the values in the table UPDATE
The-----syntax is: UPDATE "table name" SET "Field 1" = [New value]where "condition"; Also modify several fields: UPDATE "table" SET "Field 1" = [value 1], "field 2" = [value 2]where "condition";
----------------------------------------some of the data in the direct database
--delete from "table name" WHERE "condition";
SELECT * FROM Studentinfo
Delete from Studentinfo where math<60



SQL Basic Syntax