SQL实现Excel的10个常用功能的示例详解

createtablesale_guang
SELECT*fromsalewherecity="广州";

SELECT*fromsalea
innerJOIN
(SELECTordernum,profitfromsale_guang)b
ona.ordernum=b.ordernum

SELECT*fromsalea
WHEREa.ordernumnotin
(SELECTb.ordernumfromsale_guangb);

SELECT*FROMsale
wheresalesnumnotin
(SELECTsalesnumfromsale
GROUPBYsalesman
HAVINGCOUNT(salesnum)>1)

--用0填充：
updatesalesetcity=0wherecity=NULL
--删除有缺失值的行：
deletefromsalewherecity=NULL;

SELECT*fromsale
wheresalesman="张爱"
andcity="北京"
andorderaccount>=6000;

SELECT*fromsale
whereinventorynamelike"%三星%"
or存货名称like"%索尼%";

SELECTcity,sum(`profit`)
fromsale
WHEREcity="北京"
GROUPBY`city`;

--有多少个？
SELECTCOUNT(*)fromsale
whereinventorynamelike"%三星%"
and`tax`>1000;

--这些订单的利润总和和平均利润是多少？
SELECT`ordernum`,SUM(profit),AVG(`profit`)
fromsale
whereinventorynamelike"%三星%"
and`tax`>1000
GROUPBY`ordernum`;

SELECTtrim(inventoryname)fromsale;

SELECTcity,ordernum,
(Nontaxamount-profit)ascost
fromsale
orderbycostDESC;

selectsname,ssex,classfromstudent;

select*fromscorebetween60and80;

selectclass,avg(degree)fromScorea
joinstudentb
ona.sno=b.sno
GROUPBYCLASS；

createtableStudent_new
(snovarchar(20)PRIMARYKEY,
snamevarchar(10),ssexchar(2),
sageint,sdeptvarchar(25))；

select*fromstudent
wheresdept="计算机"
orderbysno;

selecta.sno,a.sname,a.ssexfromstudenta
join(Courseb,SCc)
ona.sno=c.snoandb.cno=c.cno
whereCcredit=5andGrade>60；

SELECTa.cus_idfrom`表a`asa
INNERJOIN`表b`asb
ona.cus_id=b.cus_id；

SELECT*from`表a`
UNION
SELECT*from`表b`；

SELECT*from`表a`
wherecus_idnotin(SELECT*from`表b`)
UNioN
SELECT*from`表b`
wherecus_idnotin(SELECT*from`表a`)；

SELECT*from`表a`
WHEREcus_idnotin(SELECTcus_idfrom`表b`)；