《SQL必知必会》知识点汇总
程序员文章站
2022-12-25 13:47:38
通配符的使用 拼接字段 文本处理函数的使用RTRIM和LTRIM 排序ORDER BY并以列号指代某一列 NOT操作符，检索除某一项之外的所有数据 IN操作符，检索在某个条件范围内的数据 文本处理函数UPPER和LOWER的使用，将列值转换为大写或者转换成小写 时间处理函数DATEPART的使用，取 ......
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_';--_指代任意单个字符
拼接字段
1 select CustomerNo+'('+CustomerState+')'
2 AS CustomerTitle
3 from dbo.Customers
4 ORDER BY CustomerNo DESC;--将两列合并成一列并取别名为CustomerTitle，按照CustomerNo降序排列
文本处理函数的使用RTRIM和LTRIM
1 SELECT RTRIM(CustomerNo)+'('+RTRIM(CustomerState)+')'
2 AS CustomerTitle
3 FROM Customers
4 ORDER BY CustomerNo DESC;--在上一条语句的基础上去掉字符串右边的空格
排序ORDER BY并以列号指代某一列
1 SELECT CustomerNo,CustomerShortName,CustomerState,Class from dbo.Customers 2 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='126000' 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('2018062501',
'AMY',
'北仑红联渡口路29号',
'宁波',
'OENW',
'28193',
'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='2018062501';--排除掉已插入的行，不然会提示主键重复，语句结束 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='2018062503'; 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='2018062502'; SELECT * FROM CustCopy;
删除表的某一行
DELETE FROM CustCopy WHERE cust_id='2018062504'
创建表
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='0001'; 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='2018062501' DELETE FROM Customers WHERE cust_id='2018062501' COMMIT TRANSACTION--这两行语句要不一起提交，要不全部不提交，不存在部分执行完成。
添加保留点SAVE TRANSACTION和回滚至保留点ROLLBACK TRANSACTION
BEGIN TRANSACTION SELECT *FROM Orders; DELETE FROM Orders; INSERT INTO Orders VALUES( '2819','2018-06-25 00:00:00.000','2018062501'); SAVE TRANSACTION insert1; INSERT INTO Orders VALUES( '2817','2018-06-25 00:00:00.000','2018062502'); SAVE TRANSACTION insert2; INSERT INTO Orders VALUES( '2818','2018-06-25 00:00:00.000','2018062503'); SAVE TRANSACTION insert3; ROLLBACK TRANSACTION insert2;--回滚至保留点insert2
游标的定义和使用
1 DECLARE CustCursor SCROLL CURSOR 2 FOR 3 SELECT cust_id FROM Customers 4 WHERE cust_contact IS NULL;--定义游标CustCursor 5 OPEN CustCursor;--打开游标
如何使用一个已打开的游标
DECLARE @cursor CHAR(10);--定义参数cursor FETCH FIRST FROM CustCursor INTO @cursor;--将游标的值传入该参数 SELECT @cursor AS Cursortest;--将参数输出并取别名为Cursortest
删除游标
1 CLOSE CustCursor; 2 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( '2018062506', 'Lily', '世纪大道333号', '北京', 'ienwoho', '783428@qq.com' ); SELECT *FROM Customers;