一、单表sql常用功能
字段去重查询
select DISTINCT user_id from Student;
检索前5行数据
SELECT TOP 5 user_id FROM Students;
第五行起的4条数据
select user_id from Students LIMIT 5 OFFSET 4;
多个字段指定排序
select name, user_id, address from Students ORDER BY name DESC, user_id;
#圆括号具有比 AND 或 OR 操作符更高的求值顺序
SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;
百分号(
%
)通配符
SELECT user_id, name from Students where name LIKE 'JEMM%'; --检索JEMM开头的名字
下划线(
_
)通配符--仅匹配单个字符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '_ inch teddy bear';
方括号
[ ]
通配符
#匹配以J 或者M开头的名字
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
两列拼接 +
select vend_name +'(' + vend_country + ')' from Vendors order by vend_name;
#RTRIM()去掉字符串右边的所有空格
#LTRIM()去掉字符串左边的空格
#TRIM()去掉字符串左右两边的空格
select vend_name +'(' + RTRIM(vend_country) + ')' from Vendors order by vend_name;
as 重命名字段
select pro_id,
quantity,
item_price,
quantitiy*item_price AS expanded_price
from OrderItems WHERE order_num = 20008;
二、函数功能
1.文本函数
# UPPER() 函数 -大写文本所有值
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
from Vendors ORDER BY vend_name;
2.时间函数
YEAR() 从日期中提取年份
SELECT order_num from Orders WHERE YEAR(order_date) = 2023;
3.聚集函数
AVG() 获取列的平均值
# 获取一列的平均价格
SELECT AVG(prod_price) AS avg_price FROM Products;
# 获取特定列的平均值
SELECT AVG(prod_price) AS avg_price FROM Products
WHERE vend_id = '01';
#获取去重后的平均值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = '01';
COUNT() 对列进行计数统计
# COUNT(*) 对行数目进行统计,不管列中包含的是否为null或者空值
select COUNT(*) AS num_cust
From Customers;
# COUNT(column) 对特定列中有值的进行计数,忽略null
select COUNT(cust_email) AS num_cust
From Customers;
三、分组数据
1.group by语句
分组使用group by,过滤条件用having替代
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
Having COUNT(*) >= 2;
#having 和 where 同时存在
SELECT cust_id, COUNT(*) AS orders
FROM Orders
WHERE prod_price >=4
GROUP BY cust_id
Having COUNT(*) >= 2;
2.子查询
#统计Customers 表中每个用户在Orders表里面有多少个订单
SELECT cust_name, cust_state,
( SELECT COUNT(*) FROM Orders
WHERE Orders.cust_id =Customers.cust_id)
as orders FROM Customers ORDER BY cust_name;
3.Where 联结语句
#一个表的联结
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
#两个表的联结
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
#三个表的联结
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';
SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
#右链接
SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;
#带函数的右链接
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
4.UNION语句
UNION :将两个或者两个以上的sql组合到一个查询结果中,每个sql查询的列必须相同, 会去重
UNION ALL:几个sql组合的查询,不会去重
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name ='FUN4ALL';
四、插入数据
1.插入整表数据
INSERT INTO table1 select FROM table2;
INSERT INTO Customers(cust_id,
cust_contact,cust_email)
SELECT cust_id, cust_contact, cust_email
FROM CustNew;
#从一个表复制到另一个表
SELECT * INTO CustCopy
FROM Customers;