Database sub-functions, etc.

CREATE TABLEyyy
(
Code int,
namevarchar(50),
Sex varchar(50),
Age int,
hight DecimaL (18,1),
Weight Decimal(18,1),
Idno bigint,
Address varchar(50)
)
Insert intoyyy Values(1,' Zhang San ',' man ',20,161,61,37030219,' Shandong ')
Insert into yyy Values(2,' Wang Baole four ',' man ', 21,162,62,37030219,' Zibo ')
Insert into yyy Values(6,' Li Ke ',' man ', 22,178,75,371521,' Zibo ')
Insert into yyy Values(3,' Harry ', ' Male ', 22,163,63,37030219,' Jinan ')
Insert into yyy Values(4,' Li Ke ',' man ', 23,164,64,37030219,' Weifang ')
Insert into yyy Values(5,' Li Ke ',' man ', 24,165,65,37030219,' Linyi ')
Select* from yyy

--top Keywords
Select Top3* from yyy --Displays the first 3 rows of the entire table
Select Top3* from yyy where Age>=22--Find age>=22 first and then show the first 3 lines of age>=22
Select Top3name from yyy--Displays the name column in the first 3 rows of this table

--distinct
SELECT DISTINCT name from yyy--Remove the same name

--order by Sort
Select* from yyy Order by Age ASC --age sort ASC ascending from small to large
Select* from yyy Order by Age desc --age from large to small sort desc descending
Select* from yyy where weight<70Order by Weight --Filter weight<70 first and then sort by weight from small to large
Select *from yyy Order by Age,Weight --Sort by age first and then by weight without changing the first order


--Group By column to which column to group, you can only display which column
Select Age from yyy Group by Age--Group the Name column to display only the information for the name column
Select age from yyy Group by Age

--Query Age plus 5 years old more than 27
Select * from yyy where age +5>27

--Arithmetic operator: +-*/%
--Comparison operator:> < >= <=! = <> (not equal to)!< greater than or equal to!> less than or equal
--logical operator and OR
--Modifier all some

--in within what parameter range
select * yyy where age in (22,23)   --equals age=22 or age=23
select * from yyy where age =22 or age=23 --displays both age=22 and age=23

--not is not within the parameters range.
Select * from yyy where Age Not inch (22,23) --does not show that age is 22 and 23

--Check the information of students who are not within the age range of 164
Select * from yyy wherehight=164
Select * from yyy where Age!=23
--subquery: Query a column of data using a query statement and then use it as a parameter in the query criteria of the other query
--Check the height range of people who are not 22 years of age
Select * from yyy where Age=22
Select * from yyy where hight not in(178,163)
--Simplified
Select * from yyy where hight not in(Select hight from yyy where Age=22)--The result of this column of data that first queries the weight of the owner of the age=22 as a parameter, and then uses this column parameter for the first function

Select * from yyy
--inquire about the age of the person named Li Ke who is 3 years older than Code=1 's Zhang San.
Select * from yyy  where Age-(select age from yyy where code=1 and name =' Zhang San ' ) =3 and name=' li ke '

--query for information about people aged 1 years or older who are named Li Ke
select * from name = ' li Ke ' and age -1 in (select age from yyy where name like ' king% ' )

--FOREIGN key: Constrained table called foreign key table, constrained data source called primary key table
-To add foreign keys, first you have to have a primary key table
-If you want to delete the primary key table data, you must first remove the foreign key table data
--The column of the data source for the foreign key must be a unique key (this column must be a primary key or unique)

CREATE TABLE Teacher
(
Tcode int PRIMARY KEY identity(+),
Tnamevarchar(50)
)
Insert into Teacher (tname) Values(' Zhang San ')
Select* from Teacher
CREATE TABLEStudent
(
SCODE int PRIMARY KEY identity(+),
sname varchar (50),
TNO int ReferencesTeacher (Tcode),The TNO entry for the--student table references the Tcode entry for the teacher table, and the Tcode entry must be a primary key item
CID varchar(20)Unique--Unique column, cannot be re- unique
)
Insert into StudentValues(' Student 1 ', NULL, ' 32134124 ')--tno entries can only enter a number of NULL or Zhang San
Insert into Student Values(' Student 2 ', NULL, ' 321434124 ')
Insert into Student Values(' Student 3 ',NULL, ' 32153124 ')
Insert into Student Values(' Student 4 ', 1, ' 3215g124 ')
Select *from Student

Database sub-functions, etc.