Sql语法笔记

一、单表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;