T-SQL
Removing Duplicates
Select ALL ╳
Select Distinct √
SELECT distinct COLOR
from PRODUCT
Sorting Results
ASC/DESC
Limiting sorted results
Top [n]/TOP[n]Percent、order by
Paging sorted results
offset-fetch offer a mechinism for paging through results &&Specify number of rows to skip or to retrieve
select ISNULL(COLOR,'NULL') as color1
from PRODUCT
ORDER BY COLOR desc
OFFSET 10 ROWS FETCH NEXT 10 ROWS only;
Filtering and using predicates
IN Between Like And Or Not
ISNULL
使用指定的替换值替换 NULL。
语法
ISNULL ( check_expression , replacement_value )
自动添加主键ID
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
以逆字母顺序显示公司名称,并以数字顺序显示顺序号:
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
query with Table
join
combine rows from multi tables;based on primary key-foreign key ;
Select ...
from Tble1 join Tble2
on <on_predicate>
Inner Joins
return only rows found in both tables;Match rows based on attr supplied in predicate;=?Equi-join
select orderNo ,goodsId
from class
Inner join persons
on class.id_p=persons.p_id;
Outer Joins
return all row from one and any matched from second
one table is preserved
SELECT Persons.LastName, Persons.FirstName, class.OrderNo
FROM Persons
LEFT JOIN class
ON Persons.P_id=class.id_p
ORDER BY Persons.LastName
SELECT p.LastName, p.FirstName, class.OrderNo
FROM Persons as p
LEFT JOIN class
ON P.P_id=class.id_p
ORDER BY P.LastName
//注意如果替换了persons,必须全部替换成P,否则报错
Cross Joins
Self Joins
compare rows in same table to each other
select e.employee,m.Employee as managerName
from Employee as e
left join employee as m
on e.managerId=m.employeeid
order by e.managerid
对新建表进行查询编辑时 出现红色下划线,提示‘对象名无效’列名无效’
方法二: 将所有test所有表设置为dbo就OK了
执行语句:exec sp_msforeachtable “sp_changeobjectowner ‘?’,’dbo’”
最后采用执行语句“exec sp_msforeachtable “sp_changeobjectowner ‘?’,’dbo’””后重新启动服务器后解决
Using set Operators
Union
returns a set of distinct rows ;remove dulplicates ;Union All retains dulplicates
SELECT E_Name FROM Employees_China
UNION/Intersect/Except
SELECT E_Name FROM Employees_USA
intersect
only distinct row in both result sets
except
only distinct row in 1st but no in 2nd
Built-In Functions
Scalar
Operate on a single row ;return a single value
logical
Aggregatd
Window
Rowset
Using Subqueries and Apply
Select
from
where
(select from)