1.数据更新
update 表格 set 列 = 更改值 where 筛选条件
2.内连接
select Products.*,vend_name,vend_phone from Products inner join Vendors on Products.vend_id = Vendors.vend_id
3.多次连接
distinct 可去除重复数据
SELECT DISTINCT st.SNO,st.SName,st.BirthYear,st.Ssex,st.College,st.Major,st.WeiXin
FROM category c
LEFT JOIN goods g ON c.CategoryNO = g.CategoryNO
LEFT JOIN salebill s ON s.GoodsNO = g.GoodsNO
LEFT JOIN student st ON st.SNO = s.SNO
4.Count
1.COUNT(*) 能对整张表的内容(行)进行计数,不管内容(行)是有值还是空值。
SELECT COUNT(*)
FROM 表名
2.COUNT(字段) 能对某一字段的内容(行)进行计数,但是会忽略 NULL 值。
SELECT COUNT(字段名)
FROM 表名
5.派生表
select SupplierName 供应商, soo 存量 from Supplier left join
(select SupplierNO,count(Number) soo from Goods group by SupplierNO)snno
on Supplier.SupplierNO = snno.SupplierNO
6.SUM
图示:
SELECT SUM(cost) as total
FROM Customers
6.1.SUM()
select sum(prod_price * quantity) as amount from Products
7. 查找特定日期
1、使用Like关键字和'%'来达到对日期的查询
select
count(distinct device_id) as did_cnt,
count(question_id) as question_cnt
from question_practice_detail
where date like "2021-08-%"
2、使用year和month函数
select
count(distinct device_id) as did_cnt,
count(question_id) as question_cnt
from question_practice_detail
where year(date)="2021" and month(date)="08"
8.SQL语句执行顺序
Sql语句执行顺序为:
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
9.获取日期
使用 datename 函数,获取日期的月份。
例: 表 table 中的 rq 字段
SQL语句如下:
select date(mm,rq) from table
相关日期、时间的提取方法还有:
年份 datename(yy,rq)
月份 datename(mm,rq)
日 datename(dd,rq)
小时 datename(hh,rq)
分钟 datename(n,rq) //不是 m,也不是 mm