SQL Server comprehensive tutorial (Reading Notes)

Chapter 4: Transact-SQL

P80
Cast and convert
Directly input in the query Analyzer
Select cast (12345 as char), convert (INT, 3.14), convert (bit, 12.33)
Select cast (100 + 88 as char), convert (varchar (10), getdate ())
Select getdate (),
Convert (char (12), getdate (), 1), -- several time formats
Convert (char (24), getdate (), 100 ),
Convert (char (12), getdate (), 112)

 

 

Connection Query
1. equijoin
From T1, T2 where t1.id = t2.id
From T1 join T2 on t1.id = t2.id
2. Non-equivalent join
From T1, T2 where t1.id <t2.id
3. Self-connection
Select a. ID, B. ID from T1 A, t1 B
Where a. uid = B. ID
4. External Connection
Inner join is used to select data that meets the connection conditions, and tables that participate in the connection are equal.
External join, outer join, and external join. tables that participate in the join operation can be divided into master and slave tables.
Match the data columns of the slave table with each row of data in the master table,
Fill with null. If it is bit type, use 0
5. Left join and right join at the location of the master table.
6. nested Query
Select * from table where ID in
(Select ID from Table1)
7. predicate exists
Select * From Table1 where exists
(Select ID from orders where ID> 222)
8. Having
Select * From Table1 having id <11
* ************ Understanding of group
Select ID, AVG (UID) from ttest group by ID
Here, only one aggregate function can be followed after select, such as AVG (UID ),
Or the fields to be grouped, such as ID.
************* Having Comprehension
Having must be followed by an aggregate function, such as AVG (ID)
P232 merge Query

081205

Select id1 as number, name1 as name
From Table1 where lever = 2
Union
Select Id2, name2
From Table2
Order by 1 or order by number
Note that the types of fields to be queried in multiple tables must be consistent.

P234
1. Store query results
Use pangu
Exec sp_dboption 'pangu ', 'select into', true
Select ID, name
Into Table1
From Table2, table3
Where id = uid
Select * From talbe1
// Set select into to true.
2. Store the query result to the variable
Declare @ name varchar (50)
Declare @ account varchar (30)
Use pangu
Select @ name = fname, @ account = num
From Firms
Where id = '000000'
Select @ name as fname, @ account as num -- can it be displayed like this?
P235 full-text retrieval

P245 Chapter 1 data update
1. Insert data using Stored Procedures
Use pangu
Insert into table (ID, name)
Execute ('
Select ID, sum (Num) from Table1
')
2. The deletion statement of the tape Query
Delete from Table1
Where id = (
Select ID from Table2 where ..)
3. Delete the data of the current cursor row
Delete from T1
Where current of d_cursor
4. truncate table T1
5. updated statement of the tape Query
Update T1
Set wage = wage + 100
Where id = (
Select ID from T2 where lever = 3)
6. Update Data Using connection information
Update T1
Set wage = wage + 50
From T1, T2
Where t1.id = t2.id
P257 transaction ended

P265 stored procedure and trigger
1. Two types: system stored procedures and user-defined processes.
System procedures are stored in the master database with the prefix SP.
P270