SQl Server数据库学习总结

T-SQL

Removing Duplicates

Select ALL ╳
Select Distinct √

SELECT distinct COLORfrom 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 color1from PRODUCTORDER BY COLOR descOFFSET 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 Tble2on <on_predicate>

Inner Joins

return only rows found in both tables;Match rows based on attr supplied in predicate;=?Equi-join

select orderNo ,goodsIdfrom classInner join personson 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.OrderNoFROM PersonsLEFT JOIN classON Persons.P_id=class.id_pORDER BY Persons.LastName

SELECT p.LastName, p.FirstName, class.OrderNoFROM Persons as pLEFT JOIN classON P.P_id=class.id_pORDER BY P.LastName//注意如果替换了persons，必须全部替换成P，否则报错

Cross Joins

Self Joins

compare rows in same table to each other

select e.employee,m.Employee as managerNamefrom Employee as eleft join employee as mon e.managerId=m.employeeidorder 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_ChinaUNION/Intersect/ExceptSELECT 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)