《SQL必知必会》知识点汇总

select CustomerNo from dbo.Customers;

通配符的使用

select *from dbo.Customers;

select CustomerNo from dbo.Customers

where CustomerNo LIKE'[AQ]%';--[]表示中括号中内容任取其一，%指代任意多个字符

SELECT CustomerNo FROM Customers

WHERE CustomerNo LIKE '_TM00_';--_指代任意单个字符

拼接字段

 select CustomerNo+'('+CustomerState+')'

 AS CustomerTitle

 from dbo.Customers

 ORDER BY CustomerNo DESC;--将两列合并成一列并取别名为CustomerTitle，按照CustomerNo降序排列

文本处理函数的使用RTRIM和LTRIM

 SELECT RTRIM(CustomerNo)+'('+RTRIM(CustomerState)+')'

 AS CustomerTitle

 FROM Customers

 ORDER BY CustomerNo DESC;--在上一条语句的基础上去掉字符串右边的空格

排序ORDER BY并以列号指代某一列

 SELECT CustomerNo,CustomerShortName,CustomerState,Class from dbo.Customers

 ORDER BY 4 DESC,2;

NOT操作符，检索除某一项之外的所有数据

SELECT CustomerNo FROM dbo.Customers

WHERE NOT CustomerNo LIKE'[QC]%'

IN操作符，检索在某个条件范围内的数据

SELECT CustomerNo,Class FROM dbo.Customers

WHERE CustomerNo IN ('ATM002','QTM104','ATM004')

ORDER BY 2 DESC;

文本处理函数UPPER和LOWER的使用，将列值转换为大写或者转换成小写

SELECT UPPER(CustomerNo) AS CUS,Class FROM dbo.Customers

WHERE CustomerNo LIKE 'Cus%';

SELECT LOWER(CustomerNo) AS cus,Class FROM dbo.Customers

WHERE CustomerNo LIKE '_TM%'

时间处理函数DATEPART的使用，取时间值中的某一项数据

SELECT Created from dbo.Inspections

WHERE DATEPART(YY,Created)=2018;

聚集函数AVG,COUNT,MAX,MIN,SUMD的使用

SELECT AVG(TotalAmount) AS AvgAmount FROM dbo.Inspections;

SELECT COUNT(*) AS CountNum FROM dbo.Customers

WHERE CustomerState='销售机会';

SELECT CustomerNo FROM dbo.Customers

WHERE CustomerState='销售机会';

SELECT MAX(TotalAmount) AS MaxAmount FROM dbo.Inspections;

SELECT * FROM dbo.Inspections WHERE TotalAmount=''

SELECT * FROM dbo.Inspections;

SELECT SUM(TotalVolumn) AS SumV FROM dbo.Inspections;

SELECT TotalAmount+TotalGrossWeight AS SumV FROM dbo.Inspections;

SELECT COUNT(*) AS countNo,

MIN(TotalVolumn) AS minTV,

MAX(TotalVolumn) AS maxTV,

AVG(TotalVolumn) AS avgTV

FROM dbo.Inspections

分组函数GROUP BY

SELECT COUNT(*) AS countNO

FROM dbo.Customers

WHERE CustomerNo LIKE '_TM%'

GROUP BY CustomerState;

对组处理条件函数HAVING,对已分组的组进行进一步筛选

SELECT CustomerState,COUNT(*) AS countNo

FROM dbo.Customers

WHERE CustomerNo LIKE '_TM%'

GROUP BY CustomerState

HAVING CustomerState='产品认可';

内联结，联结多个表的查询语句

SELECT * FROM dbo.Quotations;

SELECT QuotationNo,QuotationStatus,Class
FROM Quotations,Customers

WHERE Quotations.CustomerNo=Customers.CustomerNo;

SELECT QuotationNo,QuotationStatus,Class
FROM Quotations INNER JOIN Customers

ON Quotations.CustomerNo=Customers.CustomerNo;

将多表联结转化为子查询语句

SELECT QuotationNo,QuotationStatus FROM Quotations,Customers

where Customers.CustomerState='销售机会'

AND Customers.CustomerNo=Quotations.CustomerNo;

SELECT QuotationNo,QuotationStatus FROM Quotations

WHERE Quotations.CustomerNo IN(

SELECT CustomerNo FROM Customers

WHERE Customers.CustomerState='销售机会');--要求查询结果的所有列都在同一张表中才能与多表联结互相转化

表别名

SELECT Q.QuotationNo,Q.QuotationStatus,C.Class
FROM Quotations Q,Customers C

WHERE C.CustomerState='销售机会'

AND Q.CustomerNo=C.CustomerNo;

SELECT QuotationNo,QuotationStatus,Class

FROM Quotations Q,Customers C

WHERE Q.QuotationStatus='处理中'

AND Q.CustomerNo=C.CustomerNo

AND C.CustomerState='销售机会';

自联结

SELECT C1.CustomerNo,C1.Class,C1.CustomerState

FROM Customers C1,Customers C2

WHERE C2.CustomerNo='CTM002'

AND C1.Class=C2.Class;--列出所有和CTM002的Class一样的客户

将上面的自联结语句转换成子查询语句

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE Class=(

SELECT Class FROM Customers

WHERE CustomerNo='CTM002'

);

组合查询UNION和UNION ALL

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE Class='C'

UNION

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE CustomerState='销售机会'---重复记录不显示

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE Class='C'

UNION ALL

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE CustomerState='销售机会'

ORDER BY 1,2;--重复记录显示

检索在select1中存在而在select2中不存在的行

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE Class='C'

EXCEPT

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE CustomerState='销售机会'

ORDER BY 1,2;

检索在两个select语句中都存在的行

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE Class='C'

INTERSECT

SELECT CustomerNo,Class,CustomerState

FROM Customers

WHERE CustomerState='销售机会'

ORDER BY 1,2;

插入一条数据INSERT INTO

INSERT INTO Customers(cust_id,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country,

cust_contact,

cust_email

)

VALUES('',

'AMY',

'北仑红联渡口路29号',

'宁波',

'OENW',

'',

'China',

NULL,

'277816@qq.com'

);

SELECT * FROM Customers;

INSERT INTO 的新用法：插入检索出的数据

INSERT INTO CustNew(

cust_id,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country,

cust_contact,

cust_email

)

SELECT* FROM Customers;

INSERT INTO CustNew(

cust_id,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country,

cust_contact,

cust_email

)

SELECT* FROM Customers;

使用INSERT SELECT一次插入多行

INSERT INTO CustNew(

cust_id,

cust_name,

cust_address,

cust_city,

cust_state,

cust_zip,

cust_country,

cust_contact,

cust_email

)

SELECT* FROM Customers--不管SELECT出多少行都会被插入

WHERE NOT cust_id='';--排除掉已插入的行，不然会提示主键重复，语句结束

SELECT* FROM CustNew;

自动创建一个新表并完全复制另一个表的数据

SELECT* INTO CustCopy

FROM Customers;--这条语句自动创建CustCopy表并将Customers表中的数据完全复制并填充过去

更新表中数据

UPDATE CustCopy

SET cust_email='chenlili@intersky.com.cn',

cust_city='广西',

cust_name='陈莉莉'

WHERE cust_id='';

SELECT *FROM CustCopy;

用表1的数据更新表2的数据

ALTER TABLE CustNew ADD te VARCHAR(20);--在CustNew表中新建te列

UPDATE CustNew SET CustNew.te=CustCopy.cust_name

FROM CustCopy,CustNew

WHERE CustNew.cust_id=CustCopy.cust_id;--用CustCopy表中的cust_name列值更新CustNew表中的te列，条件是两表cust_id相等

删除某个列的值可以将它设置为NULL

UPDATE CustCopy

SET cust_address=NULL

WHERE cust_id='';

SELECT * FROM CustCopy;

删除表的某一行

DELETE FROM CustCopy

WHERE cust_id=''

TRUNCATE TABLE删除表

使用TRUNCATE TABLE可以快速删除表中的所有记录，而且无日志记录。与DELETE相比，速度更快，使用的系统资源和事务日志资源更少。使用DELETE语句，系统一次一行地处理要删除的表中的记录，并在事务处理日志中记录相关删除操作。使用TRUNCATE TABLE语句一次性完成删除与表有关的所有数据页的操作，且不更新事务处理日志，无法用ROLLBACK回滚。

TRUNCATE TABLE a;--删除表a中的所有数据

创建表

CREATE TABLE Products(

prod_id CHAR(10) NOT NULL,

vend_id CHAR(10) NOT NULL,

prod_name CHAR(254) NOT NULL,

prod_price DECIMAL(8,2) NOT NULL,

prod_desc VARCHAR(1000) NULL

);

CREATE TABLE Orders(

order_num INTEGER NOT NULL,

order_date DATETIME NOT NULL,

cust_id CHAR(10) NOT NULL

);

CREATE TABLE Vendors(

vend_id CHAR(10) NOT NULL,

vend_name CHAR(50) NOT NULL,

vend_address CHAR(50),

vend_city CHAR(50),

vend_state CHAR(5),

vend_zip CHAR(10),

vend_country CHAR(50)

);--不填写NULL和NOT NULL时默认为NULL

新建表并指定默认值DEFAULT

CREATE TABLE OrderItems(

order_num INTEGER NOT NULL,

order_item INTEGER NOT NULL,

prod_id CHAR(10) NOT NULL,

quantity INTEGER NOT NULL DEFAULT 1,

item_price DECIMAL(8,2) NOT NULL

);

更新表ALTER TABLE

ALTER TABLE Vendors

ADD vend_phone CHAR(20);

SELECT * FROM Vendors;

ALTER TABLE Vendors

DROP COLUMN vend_phone;

ALTER TABLE Vendors

ADD PRIMARY KEY(vend_id);--给vend_id设置主键

删除整张表及其表结构

DROP TABLE CustNew;

--重命名表

EXEC sp_rename 'Orders','testi21';

EXEC sp_rename 'testi21','Orders';

SELECT *FROM Orders;

GO;

创建视图CREATE VIEW view_name AS...

CREATE VIEW ProductCustomers AS

SELECT cust_name,cust_contact,prod_id

FROM Customers,Orders,OrderItems

WHERE Customers.cust_id=Orders.cust_id

AND Orders.order_num=OrderItems.order_num;

GO;

SELECT * FROM ProductCustomers;

删除视图

SELECT * FROM ProductCustomers

WHERE prod_id='';

DROP VIEW ProductCustomers;

存储过程（类似自定义函数）

SELECT *FROM Customers;

--输出Customers表中所有cust_contact为空的用户个数,利用存储过程结果应该为2;

GO;

CREATE PROCEDURE CusCount

@custc INTEGER OUT--带输出参数的存储过程

AS

SELECT @custc=COUNT(*) FROM Customers

WHERE cust_contact IS NULL;

RETURN @custc;

GO;

新建无参数的存储过程

CREATE PROCEDURE MailingListCount AS

DECLARE @cnt INTEGER

SELECT @cnt=COUNT(*) FROM Customers

WHERE NOT cust_contact IS NULL;

RETURN @cnt;

GO;

调用带输出参数的存储过程

DECLARE @tt INTEGER

EXEC CusCount @tt OUTPUT;

SELECT @tt AS Ccount;

调用无参数的存储过程

DECLARE @ReturnValue INT

EXEC @ReturnValue=MailingListCount;

SELECT @ReturnValue AS Ccount;

GO

事务处理TRANSACTION

BEGIN TRANSACTION

DELETE FROM Orders;

ROLLBACK TRANSACTION;

GO;

/*

SELECT *FROM Orders;

INSERT INTO Orders VALUES(

'2819','2018-06-25 00:00:00.000','2018062501');

INSERT INTO Orders VALUES(

'2817','2018-06-25 00:00:00.000','2018062502');

INSERT INTO Orders VALUES(

'2818','2018-06-25 00:00:00.000','2018062503');*/

事务处理，COMMIT显式提交

BEGIN TRANSACTION

DELETE FROM Orders WHERE cust_id=''

DELETE FROM Customers WHERE cust_id=''

COMMIT TRANSACTION--这两行语句要不一起提交，要不全部不提交，不存在部分执行完成。

添加保留点SAVE TRANSACTION和回滚至保留点ROLLBACK TRANSACTION

BEGIN TRANSACTION

SELECT *FROM Orders;

DELETE FROM Orders;

INSERT INTO Orders VALUES(

'','2018-06-25 00:00:00.000','');

SAVE TRANSACTION insert1;

INSERT INTO Orders VALUES(

'','2018-06-25 00:00:00.000','');

SAVE TRANSACTION insert2;

INSERT INTO Orders VALUES(

'','2018-06-25 00:00:00.000','');

SAVE TRANSACTION insert3;

ROLLBACK TRANSACTION insert2;--回滚至保留点insert2

游标的定义和使用

 DECLARE CustCursor SCROLL CURSOR

 FOR

 SELECT cust_id FROM Customers

 WHERE cust_contact IS NULL;--定义游标CustCursor

 OPEN CustCursor;--打开游标

如何使用一个已打开的游标

DECLARE @cursor CHAR(10);--定义参数cursor

FETCH FIRST FROM CustCursor INTO @cursor;--将游标的值传入该参数

SELECT @cursor AS Cursortest;--将参数输出并取别名为Cursortest

删除游标

 CLOSE CustCursor;

 DEALLOCATE CustCursor;--释放游标占用的资源

游标的滚动实例

DECLARE CustCursor SCROLL CURSOR

FOR

SELECT cust_id FROM Customers

WHERE cust_contact IS NULL;

OPEN CustCursor;

DECLARE @cursor1 CHAR(10);

FETCH NEXT FROM CustCursor INTO @cursor1

WHILE (@@FETCH_STATUS=0)--游标的循环

BEGIN

FETCH NEXT FROM CustCursor INTO @cursor1--将游标指向下一个值

END

SELECT @cursor1 AS TT;

关闭游标并释放资源

CLOSE CustCursor;

DEALLOCATE CustCursor;

主键与外键

ALTER TABLE Products

ADD PRIMARY KEY(prod_id);--用ALTER语句添加主键

用ALTER设置外键

ALTER TABLE Orders

ALTER COLUMN cust_id NCHAR(10);--两个表数据类型不同设置外键会报错，因此先更改外键字段的数据类型

ALTER TABLE Orders

ADD CONSTRAINT FK_ord--将Orders.cust_id用CONSTRAINT关键字设置唯一约束

FOREIGN KEY(cust_id)

REFERENCES Customers(cust_id)

GO

用ALTER语句设置唯一约束

ALTER TABLE Vendors

ADD UNIQUE(Vend_id);

用ALTER语句设置检查约束

ALTER TABLE Products

ADD CHECK(prod_price>0);

添加索引,系统根据索引快速查询数据

CREATE INDEX prod_name_int

ON Products(prod_name);

SELECT *FROM Products WHERE prod_name='莫匹罗星软膏';

GO;

创建触发器

CREATE TRIGGER cust_state

ON Customers

FOR INSERT,UPDATE

AS

UPDATE Customers

SET cust_state=UPPER(cust_state)

WHERE Customers.cust_id=(SELECT cust_id from inserted);--当Customers.cust_state发生INSERT,UPDATE操作时，触发器将自动把cust_state字段内容从小写变成大写

触发器测试

INSERT INTO Customers(

cust_id,

cust_name,

cust_address,

cust_city,

cust_state,

cust_email

)VALUES(

'',

'Lily',

'世纪大道333号',

'北京',

'ienwoho',

'783428@qq.com'

);

SELECT *FROM Customers;

巴特西

《SQL必知必会》知识点汇总

最新文章

热门文章