SELECT SUM(amount) AS total_sales FROM sales;

SELECT MONTH(date), SUM(amount) AS monthly_sales 
FROM sales 
GROUP BY MONTH(date);

SELECT SUM(amount) AS total_sales FROM sales WHERE salesman_id = 101;

SELECT 
    IFNULL(MONTH(date), 'Total') AS Month, 
    SUM(amount) AS monthly_sales 
FROM 
    sales 
GROUP BY 
    MONTH(date) WITH ROLLUP;

SELECT 
    customers.name, 
    SUM(orders.amount) AS total_order_amount 
FROM 
    customers 
JOIN 
    orders ON customers.id = orders.customer_id 
GROUP BY 
    customers.name;

select * from Info --查所有數(shù)據(jù)
select Code,Name from Info --查指定列的數(shù)據(jù)
select Code as '代號(hào)',Name as '姓名' from Info --給列指定別名

select * from Info where Code='p001'
select * from Info where Sex='true' and Nation='n001' --多條件并的關(guān)系
select * from Info where Sex='true' or Nation='n001' --多條件或的關(guān)系

select * from Car where Price>40 and Price<50
select * from Car where Price between 40 and 50

select * from Car where Code in ('c001','c005','c010','c015')
select * from Car where Code not in ('c001','c005','c010','c015')

select * from Car where Name like '%寶馬%' --查包含寶馬的
select * from Car where Name like '寶馬%' --查以寶馬開頭的
select * from Car where Name like '%寶馬' --查以寶馬結(jié)尾的
select * from Car where Name like '寶馬' --查等于寶馬的
select * from Car where Name like '__E%' --查第三個(gè)字符是E的

select * from Car order by Price asc --以價(jià)格升序排列
select * from Car order by Price desc --以價(jià)格降序排列
select * from Car order by Oil desc,Price asc --以兩個(gè)字段排序，前面的是主條件后面的是次要條件

select top 5 * from Car
select top 5 * from Car where Code not in (select top 5 Code from Car)

select top row * from Car where Code not in (select top (page-1)*row Code from Car)

select distinct Brand from Car

select Brand from Car group by Brand having count(*)>2

select count(*) from Car --查詢所有數(shù)據(jù)條數(shù)
select count(Code) from Car --查詢所有數(shù)據(jù)條數(shù)
select sum(Price) from Car --求和
select avg(Price) from Car --求平均
select max(Price) from Car --求最大值
select min(Price) from Car --求最小值

select * from Info,Nation --形成笛卡爾積
select * from Info,Nation where Info.Nation = Nation.Code
select Info.Code,Info.Name,Sex,Nation.Name,Birthday from Info,Nation where Info.Nation = Nation.Code
select * from Info join Nation on Info.Nation = Nation.Code --join on 的形式

select Code,Name from Info
union
select Code,Name from Nation

--查詢民族為漢族的所有人員信息
select * from Info where Nation = (select Code from Nation where Name = '漢族')

--查詢系列是寶馬5系的所有汽車信息
select * from Car where Brand =(select Brand_Code from Brand where Brand_Name = '寶馬5系')

--查找油耗低于該系列平均油耗的汽車
select * from Car where Oil
select avg(Oil) from Car where Brand = (該系列)
select * from Car a where Oil