SQL知识点.txt
一、查询
SELECT prod_name, prod_id
FROM Products;
二、排序
SELECT prod_id, prod_name
FROM Products
ORDER BY prod_id, prod_name DESC;
三、过滤
SELECT prod_id, prod_name
FROM Products
WHERE prod_price BETWEEN 5 AND 10
ORDER BY prod_id DESC, prod_name;
四、高级过滤
1 SELECT prod_id, prod_name
FROM Products
WHERE vend_id=‘DLL01’ AND prod_price <= 4;
2 SELECT prod_id, prod_name
FROM Products
WHERE vend_id=‘DLL01’ OR prod_price <= 4;
3 AND和OR一起使用时，优先使用AND
4 SELECT prod_id, prod_name
FROM Products
WHERE vend_id IN (‘DLL01’, ‘BRS01’)
ORDER BY prod_name;
5 SELECT prod_id, prod_name
FROM Products
WHERE vend_id NOT ‘DLL01’
ORDER BY prod_name;
五、用通配符进行过滤
SELECT prod_id, prod_name
FROM Products
WHERE vend_name LIKE ‘Fish%’
SELECT prod_id, prod_name
FROM Products
WHERE prod_desc LIKE '__ inch teddy bear';
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '[JM]%'
六、创建计算字段
计算字段并不实际存在数据库表中，而是运行时在SELECT语句内创建。
1 拼接字段,使用 + 或 || 表示(使用RTRIM()函数来消除空格)
SELECT RTRIM(vend_name) + ’ (’ + RTRIM(vend_country) + ‘)’
FROM Vendors
ORDER BY vend_name’;
2 使用别名AS
SELECT RTRIM(vend_name) + ’ (’ + RTRIM(vend_country) + ‘)’ AS vend_title
FROM Vendors
ORDER BY vend_name;
3 执行算数计算
SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_number = 2008;
七、使用数据处理函数
1 文本处理函数UPPER()#转化为大写
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
2 日期和时间处理函数
SELECT order_number
FROM Orders
WHERE DATEPART(yy, order_date) = 2004;
3 数值处理函数
ABS() EXP() PI() SQRT() SIN() COS() TAN()
八、汇总数据
1 聚集函数
AVG() COUNT() MAX() MIN() SUM()
SELECT AVG(prod_price) AS avg_price
FROM Products;
2 聚集不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = ‘DLL01’;
#只计算不同值的平均值
3 组合聚集函数，将多个函数一起应用
九、分组数据
1 创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
2 过滤分组
SELECT cust_id, COUNT() AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT() >= 2;
3 分组和排序
GROUP BY 和 ORDER BY
十、使用子查询:嵌套在其他查询中的查询
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = ‘RGAN01’);
十二、联结表
SELECT Products.vend_id, vend_name, prod_price
FROM Products, Vendors
WHERE Products.vend_id = Vendors.vend_id;
#等同于(等值联结，也称为内部联结)
SELECT Products.vend_id, vend_name, prod_price
FROM Products INNER JOIN Vendors
ON Products.vend_id = Vendors.vend_id;
十三、高级联结
#FROM同一个表，使用表别名(自联结)
SELECT C1.cust_id, C1.cust_name
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_name = C2.cust_name
AND C1.cust_contact = ‘Jim Jones’;
自然联结
SELECT *
FROM Products INNER JOIN Vendors
ON Vendors.vend_id = Products.vend_id;
外部联结
SELECT Customers.cust_id, cust_name, order_num
FROM Customers LEFT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;
十四、组合查询
#将多个结果显示在一起
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_id IN (1000000005, 1000000003, 1000000001)
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = ‘Fun4All’
ORDER BY cust_name;
十五、插入数据
INSERT INTO Customers
VALUES(‘1000000006’,
‘Zed’,
‘2 street’,
‘shanghai’,
‘SH’,
‘65656’,
‘China’,
‘Jim’,
NULL
);
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000007', 'Zero', '3 street', 'BJ', 89478, 'China', 'Kite');
#INSERT SELECT 语句，可以一次插入多行
#SELECT INTO 复制一个新表
十六、更新和和删除数据
UPDATA Customers
SET cust_name = ‘Jan’
WHERE cust_id = 1000000007;
DELETE FROM Customers
WHERE cust_id = 1000000007;
十七、创建和操作表
#创建表
CREATE TABLE ProdCopy
(
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 DEFAULT 8.88,
prod_desc VARCHAR(1000) ,
);
#删除表
DROP TABLE ProdCopy;
#更新表
ALTER TABLE ProdCopy
ADD prod_phone CHAR(10);
#重命名表
十八、视图(VIEW,虚拟的表)
CREATE VIEW ProductCustomers
AS
SELECT cust_name, cust_contact, prod_id
FROM Orders, Customers, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;
#从视图中检索数据
SELECT * FROM ProductCustomers;
十九、存储过程(PROCEDURE)
#创建存储过程
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
CREATE PROCEDURE NewOrder @cust_id INTEGER
AS
DECLARE @order_num INTEGER
SELECT @order_num = MAX(order_num)
FROM Orders
SELECT @order_num = @order_num + 1
INSERT INTO Orders(cust_id, order_num, order_date)
VALUES(@cust_id, @order_num, GETDATE());
RETURN @order_num;
#使用标识字段
CREATE PROCEDURE NewOrder2 @cust_id CHAR(10)
AS
INSERT INTO Orders(cust_id, order_date)
VALUES(@cust_id, GETDATE())
SELECT order_num = @@IDENTITY;
#执行存储过程
EXECUTE NewOrder 20005
二十、事务处理(TRANSACTION)
事务：一组SQL语句
回退：撤销指定SQl语句
提交：将未储存的SQL语句结果写入数据库
保留点：事务处理中设置的临时占位符，可以对他进行回退。
(可以回退INSERT, UPDATE, DELETE语句。CREATE, DROP不可回退)。
BEGIN TRANSACTION
DELETE
FROM Orders
WHERE cust_id=1000000006
SAVE TRANSACTION delete1
DELETE
FROM Orders
WHERE cust_id=1000000007
COMMIT TRANSACTION;
#回退
ROLLBACK TRANSACTION delete1
二十一、游标(CURSOR)
@创建游标
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL
OPEN CustCursor
DECLARE @cust_id CHAR(10),
@cust_name CHAR(50),
@cust_address CHAR(50),
@cust_city CHAR(50),
@cust_state CHAR(5),
@cust_zip CHAR(10),
@cust_country CHAR(50),
@cust_contact CHAR(50),
@cust_email CHAR(255),
FETCH NEXT FROM CustCursor
INTO @cust_id, @cust_name, @cust_address, @cust_city, @cust_state, @cust_zip, @cust_country, @cust_contact, @cust_email
CLOSE CustCursor
DEALLOCATE CustCursor
二十二、高级特性
<1>约束
主键
CREATE TABLE Test
(
test_id CHAR(10) NOT NULL PRIMARY KEY,
test_name CHAR(1) NOT NULL,
test_address CHAR(10) NOT NULL,
);
外键
CREATE TABLE test2
(
order_num INTEGER NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
);
#另一种外键方法
ALTER TABLE Customers
ADD CONSTRAINT
FOREIGN KEY(cust_id) REFERENCES Customers(cust_id)
唯一约束
CREATE TABLE NewTable
(
name INTEGER NOT NULL,
id CHAR(10) NOT NULL,
quantity CHAR(10) NOT NULL CHECK(quantity>0),
);
索引
CREATE INDEX prod_name_index
ON Products (prod_name);
触发器
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id
数据库安全
#GRANT和REVOKE语句
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 提示词工程——AI应用必不可少的技术