SQL Server Getting Started learning

--distinct (remove duplicate data)
Select distinct time from highttable
--between
SELECT * from Highttable where ID between 1 and 2
--or
SELECT * from highttable where id=1 or id=2
--and and OR combination
SELECT * from highttable where (id=1 or ID =2) and id=1
--order by and sort (descending)
SELECT * from highttable ORDER BY ID Desc
--top All
Select Top 2 * from highttable
--top Field
Select top 2 ID from highttable
--like left Blur (matches the character to the left of the coordinate)
SELECT * from highttable where type like ' N% '
--like right blur (matches the character to the right of the coordinate)
SELECT * from highttable where type like '%N '
--like full match
SELECT * from highttable where type like '%N% '
--_ Match (_ character followed by a match of a character)
SELECT * from highttable where type like ' _n '
--_ multiple matches
SELECT * from highttable where type like ' N_n_n '
--[] Wildcard character (left pass with ' []% ', right pass with '%[] ', full pass, '%[]% ', anti-pass match '%[! n]% ')
SELECT * from highttable where type like '%[n]% '
--in
SELECT * from Highttable where ID in (.)
--Table Association condition query
Select A.id,b.id,b.type from testtime a,highttable b where a.id=10 and b.id=1
--inner joins inside joins
SELECT * from highttable h inner join testtime t on h.id = t.id where h.id<> null
--left Join left Connection
SELECT * from Highttable H left joins Testtime t on h.id = t.id where h.id = 1
--right Join Right Join
SELECT * from highttable h right joins testtime t on h.id = t.id where h.id=1
--full join returns a row as long as there is a match in one of the tables
SELECT * from highttable h full join testtime t on h.id = t.id where h.id=1
--union a result set that merges two or more SELECT statements (there are no duplicate values)
SELECT * FROM highttable Union SELECT * FROM Highttable where id=1
--union All (can be repeated)
SELECT * FROM highttable UNION ALL SELECT * from highttable where id=1
--select into (for copying tables and data, copying some fields)
Select Type,time to Highttabless from highttable
--select into (for copying tables and data, copying entire tables)
SELECT * Into Highttables from highttable
--connect other tables to innovate new tables and data
Select Type,time into Highttablet from highttable right join testtime on highttable.id = testtime.id

--Create a database
Create DATABASE Backbase
--Delete Database
Drop Database EFDemo
--Create a table unique--constraint uniquely identifies each record in a database table, PRIMARY key is self-increasing and cannot be used in conjunction with unique
CREATE TABLE Table_back (
ID int not NULL UNIQUE,
LastName varchar (100),
CityName varchar (100)
)
--Delete Table
--drop Table Highttable
--foreign key FOREIGN KEY constraint
CREATE TABLE Table_back1 (
ID int not NULL UNIQUE,
LastName varchar (100),
CityName varchar (100),
id_p int FOREIGN KEY REFERENCES table_back1 (ID)
)
--Delete foreign keys
ALTER TABLE Orders
DROP CONSTRAINT Fk_perorders
--check Limit Value Range
CREATE TABLE Table_back2 (
ID int not NULL UNIQUE,
LastName varchar (100),
CityName varchar (100),
id_p int FOREIGN KEY REFERENCES table_back1 (ID),
Check (id>0)--or (check id>0 and lastname= ' Xiaomi '), table already exists create check constraint (Add check (id>0))
)
--Delete Check constraint
ALTER TABLE Table_back2
Drop CONSTRAINT CheckName
--default inserting a default value into a table
--orderdate Date Default GETDATE (), City varchar (255) Default ' Sandnes '
--Delete default value settings
--alter TABLE Table_back2
--alter COLUMN City DROP DEFAULT
--create Index statement to create indexes in a table (can be multi-column index)
Create INDEX index_name on highttable (ID)
--Unique index
Create unique index index_name1 on highttable (ID)
Drop INDEX index_name on highttable
--truncate table deletes tabular data without deleting the structure of the table itself
TRUNCATE TABLE highttable
--alter table
ALTER TABLE highttable add Colunm_name int
--drop column Delete columns in a table
ALTER TABLE highttable drop column Colunm_name
--alter column to modify columns in a table
ALTER TABLE highttable ALTER COLUMN colunm_name varchar (30)
--auto-increment will generate a unique number when the new record is inserted into the table
--create View Creating views
CREATE VIEW [view_name] As
SELECT Id,value
From highttable
--now returns the current date and time
--curdate () returns the current date
--curtime () returns the current time
--date () Extract date part of date or date/time expression
--extract () returns a separate part of the date/time Press
--date_add () Adds a specified time interval to a date
--date_sub () Subtracts a specified time interval from a date
--datediff () returns the number of days between two dates
--date_format () Displays the date/time in a different format
--getdate () returns the current date and time
--datepart () returns a separate part of the date/time
--dateadd () Adds or subtracts a specified time interval from a date
--datediff () returns a time between two dates
--convert () Displays the date/time in a different format
--date-Format YYYY-MM-DD
--datetime-format: Yyyy-mm-dd HH:MM:SS
--smalldatetime-format: Yyyy-mm-dd HH:MM:SS
--timestamp-Format: Unique number
--is NULL
SELECT * from highttable where Value is null
--is NOT NULL
SELECT * from highttable where Value was not null
--function function
--avg returns the average of a column
SELECT AVG (column_name) from table_name
--count (column_name) returns the number of rows in a column (excluding NULL values)
Select COUNT (column_name) from highttable
--count (*) returns the number of rows selected
Select COUNT (*) from highttable
--first returns the value of the first record in a specified field
--last returns the value of the last record in the specified field
--max (column) returns the highest value of a column
Select MAX (value) from highttable
--min (column) returns the lowest value of a column
Select MIN (value) from highttable
--sum returns the sum of a column
Select SUM (value) from highttable
--var (column) returns the estimated value of the population sample variance
--varp (column returns the estimated value of the population variance
--ucase (c) Convert a field to uppercase
Select UCASE (type) from highttable
--lcase (C) Convert a field to lowercase
Select LCase (Type) highttable
--mid (C,start[,end]) extracting characters from a text field
--len (c) Returns the length of a text field
Select LEN (value) from highttable
--INSTR (C,char) returns the numeric position of the specified character in a text field
Select InStr (2, ' DASD ') from highttable
--left (C,number_of_char) returns the left part of a requested text field
Select Left (' Dasdasdas ', 3)
--right (C,number_of_char) returns the right part of a requested text field
--round (c,decimals) Rounding of a numeric field by a specified number of decimal digits
Select ROUND (2.122,2)
--mod (x, y) returns the remainder of the division operation
Select mod (3,6)
--now () returns the current system date
Select Now () from highttable
--format (C,format) returns how a field is displayed (convert can be substituted)
Select Format (value,format) from highttable
--datediff (D,date1,date2) for performing date calculations
Select DATEDIFF (MONTH, ' 2015-01-12 ', GETDATE ())
--group by grouping result sets based on one or more columns
Select Id,sum (Value) from highttable Group by ID
--having aggregate function, cannot be used with where, must have group by
Select ID from highttable GROUP by ID have SUM (sumvalue) >10 ORDER BY id DESC
--Sort (descending)
SELECT * from highttable ORDER BY ID Desc
--Sort (ascending)
SELECT * from Highttable ORDER by ID ASC

Hope to be useful to everyone!

SQL Server Getting Started learning