SQl Server数据库学习总结

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)