SQL Server knowledge

1. Insert multiple data entries into the table using the Union keyword.
Insert into users ([name], password, nickname)
Select 'aaa', 'bbbb ', 'cccc' Union
Select 'dfdf ', 'dfdf', 'dfdsf' Union
Select 'edtrer', 'dfgg ', 'dfgfg'
When Union all is used, the following rows with the same Union data will be overwritten.

2. Differences between Delete truncate drop
When the delete statement is used, all data in the table will be deleted without any write conditions. Deleting a record to the log file will slow down the table's row count.
Truncate table clears the table, which is fast.
If the table is not deleted by drop, an error will be reported.
When a delete table is deleted without this table, the number of affected rows is 0.

3. Date Functions
Getdate () Get the current date
Dateadd (yyyy, 2, '1970-11-10 ') adds the specified date to 2 years. You can also add month mm, day DD, etc.
Datediff (yyyy, '1970-10-5 ', '1970-10-5') returns the year, month, or day between two dates.
Datename (DW, '2017-11-4 ') returns the day of the week (W is also available) and the result is Tuesday
Datepart (day, '2017-12-21 ') obtains the specified integer form of the date.
Similar to datepart
Day (), month (), Year () Functions

4. Some common sqlserver functions
Len ('df substantially ') obtain the actual length of 5
datalength ('df substantially ') get 8 bytes
charindex ('mn ', 'mmmmn') Get the starting position of the first string in the second string 4
lower () returns the string in lower case.
upper () returns the string in upper case.
ltrim () removes spaces on the left of the string.
rtrim () clear spaces on the right of the string
right ('sdfs', 2) extract a specified number of characters from the right of the string
left ('dfds ', 2) truncates a specified number of characters from the left of the string
Replace ('dfdsfs', 'D', 'A ') replace character d with character a
stuff ('addfgdg', 2, 3, 'aaaaa ') truncates three characters from the second position of the string and uses AAAAA to replace
numeric ()
decimal (). The two functions are the same, and the precision is 5, that is, the total number of digits is 5, and the number of decimal places is 3
cast (123 as varchar (5) conversion function, which converts an integer to a string
convert (INT, '123 ') converts a string to an integer
convert (varchar (10), getdate (), 121). The format is used to obtain the year, month, and day of the date without the time, minute, and second.
isnull (classess, 'Non-shift offset') replace all classess columns with no-shift replace

5. Some common stored procedures
Sp_help users list table Structures
Sp_tables: Find all tables in the current database
Xp_mongoshell 'mkdir D: \ BB' create a folder
Exec sp_addlogin 'adminmaster', 'themaster' -- create an sqlserver Logon account
-- Under the master database
Exec sp_grantdbaccess 'adminmaster', 'adminmaster' -- create a database user
Use bbsdb
-- Grant the user the permission to view and delete the posts in the bbstopic table and the bbsreply table.
Grant select, delete on bbstopic to adminmaster
Grant select, delete on bbstopic to adminmaster
-- You have the modification permission on the user table bbsusers.
Grant update on bbsusers to adminmaster

Scope_identity () is used to obtain the real id value of the last inserted data, because when multiple users insert data, others may also insert data.
@ Identity: Obtain the id value of the last inserted data
Sp_who queries several users connected to the database.
Kill the value of the spid column to delete the resource-consuming user

Print @ identity -- when multiple users insert data, it is possible that the actual id value is not obtained.

Print @ max_connections -- maximum number of simultaneous connections that can be created

Print @ rowcount -- number of rows affected by the previous SQL statement

Print @ servername -- Name of the local server

Print @ timeticks -- number of microseconds per scale of the current computer

Print @ trancount -- number of opened items

Print @ version -- SQL Server version
UPS uninterruptible power supply

6. create databases, tables, and add Constraints
Under the master database
If exists (select * From sysdatabases where name = 'mytest ')
Drop database mytest
Create Database mytest
On
(
Name = 'mytest ',
Filename = 'e: \ test. MDF'
)
Under mytest
If exists (select * From sysobjects where name = 'stu ')
Drop table Stu
Create Table Stu
(
Id int identity,
Name varchar (50 ),
Calsses varchar (50)
)
Constraint
Alter table stuinfo
Add constraint ck_stuno check (stuno like 's253 _ ') and check Constraints
Alter table Stu
Add constraint df_name default ('aaa') for [name] add default Constraint
Alter table Stu
Add constraint uq_name (name) and unique constraint
Alter table Stu
Add constraint pk_id primary key plus primary key constraint
Alter table Stu
Add constraint fk_id foreign key (foreign key column) References primary key table (primary key column) plus foreign key constraints

7. Small knowledge
Set nocount on -- the number of rows affected by SQL statements is not displayed.
The displayed value must be inserted into the ID column.
Set identity_insert table name on
Set identity_insert table name off disable insert display value
Modify the Data Type of a column in a table
Alter table table name
Alter column name type (size)
Insert a column into the table
Alter table table name
Add column name type (size)

8. Table Variables
Declare @ mytable table
(
Id int identity,
Orderid varchar (20 ),
Customerid varchar (20)
)

9. The execution content is a string SQL statement
Declare @ strsql varchar (800)
Set @ strsql = 'select * from orders'
Exec (@ strsql)
Case when then usage:
Case when writtenexam is null then 'nottest'
Else cast (labexam as varchar)
End

10. Transaction
begin transaction
declare @ userid int, @ sumerror int
set @ sumerror = 0
select @ userid = uid from bbsusers where uname = 'bucket'
-- post with cocaine
insert into bbstopic (tsid, tuid, tface, ttopic, tcontents, tTime, tclickcount, tstate, tlastreply)
values (2, @ userid, 4 ,'. net configuration question ', 'my', getdate (), 2009, 0, '2017-1-8 ')
set @ sumerror = @ sumerror + @ error
-- post. add 1 to the Net Forum table
If (@ sumerror = 0) Update bbssection set stopiccount = stopiccount + 1 where sname like '%. net % '
set @ sumerror = @ sumerror + @ error
-- determines whether the error is 0, not posting fails
If (@ sumerror <> 0)
begin
Print 'your post is not successful! '
rollback transaction
end
-- if the error code is not 0, the post is successful.
else
begin
Print 'Post successful'
commit transaction
end
the transaction locks the table, exclusive to this table. operation can be performed on this table only after execution
11. index
If exists (select * From sysobjects where name = 'myindex ')
drop index myindex
Create nonclustered myindex -- Non-clustered index
On Table Name (column name)

12. View
If exists (select * From sysobjects where name = 'myview ')
Drop view myview
Go
Create view myview
As
(
Select * from orders
)
A view does not store data. It only involves ing, that is, referencing the data in the original table.
Execute the update Statement on the view. The data in the original table changes, but the virtual table does not.

13. triggers
If exists (select * From sysobjects where name = 'trig _ Bank ')
Drop trigger trig_bank
Go
Create trigger trig_bank
On Bank
For insert
As
......
When inserting data into the bank table, execute the begin to end of the trigger.

14. Stored Procedure
F exists (select * From sysobjects where name = 'proc _ stu ')
Drop procedure proc_stu
Go
Create procedure proc_stu
@ Notpassnum int output, -- output parameter, number of failed users
@ Writtenpass Int = 60, -- input parameter written test pass
@ Labpass Int = 60 -- enter the parameter and try again
As
......

15. Create a UDF
Create Function fun_myfun
(
@ Num int -- Parameter
)
Returns int -- return type
As -- begin and end cannot be saved
Begin
Set @ num = @ num + 1
Return @ num -- Return Value
End
Select DBO. fun_myfun (5) -- call the Function

16. Two Ways to insert data into a table
<1> select a. column, column A. column into B -- table B does not exist beforehand.
From
<2> insert into B (column, column)
Select column, the column from a -- B must exist in advance, and the data type and number must match

17. Differences between char and varchar
CHAR: If a character is defined as 10 characters, if only one character is written as 1, there will be 9 spaces after 1
Varchar: If a character is defined as 10 characters and only one character is written as 1, it occupies only one character.
Therefore, varchar can save disk space.
However, if you want to change the character 1 to 1111, you must move it back to free up space, so the efficiency is low.
Generally, char is used for less than 50 characters.
Use varchar for more than 50 characters
Update table name set column name = NULL -- set the data in this column to null

18. Three Methods for changing column names in the result set
<1> select codeid as 'student number' -- no quotation marks
<2> select codeid 'student number' -- because as can be omitted
<3> select 'student number' = codeid

the query is extracted by percentage using the keyword percent
select top 20 percent * from student
it should be <1> where <2> group by <3> having use order
where and having are filter conditions
the difference is that when conditions have Aggregate functions, you can only use having; otherwise, use where