中国农业大学计算中心资源下载MySQL数据库表数据查询

选择数据库/自己创建数据库,比如uses4（会显示changes4）

创建student表，如下开始练习

Createtablestudent...(表示创建好字段)

CREATETABLESTUDENT

(SNOVARCHAR(3)NOTNULL,

SNAMEVARCHAR(4)NOTNULL,

SSEXVARCHAR(2)NOTNULL,

SBIRTHDAYDATETIME,

CLASSVARCHAR(5))

CREATETABLECOURSE

(CNOVARCHAR(5)NOTNULL,

CNAMEVARCHAR(10)NOTNULL,

TNOVARCHAR(10)NOTNULL)

CREATETABLESCORE

CNOVARCHAR(5)NOTNULL,

DEGREENUMERIC(10,1)NOTNULL)

CREATETABLETEACHER

(TNOVARCHAR(3)NOTNULL,

TNAMEVARCHAR(4)NOTNULL,TSEXVARCHAR(2)NOTNULL,

TBIRTHDAYDATETIMENOTNULL,PROFVARCHAR(6),

DEPARTVARCHAR(10)NOTNULL)

创建好数据见下图：

2.2.基础练习一及参考sql语句

1、查询Student表中的所有记录的Sname、Ssex和Class列。

selectsname,ssex,classfromstudent;

2、查询教师所有的单位即不重复的Depart列。

Selectdistinctdepartfromteacher;

3、查询Student表的所有记录。

Select*fromstudent;

4、查询Score表中成绩在60到80之间的所有记录。

Selectdegreefromscorewheredegreebetween60and80;

5、查询Score表中成绩为85，86或88的记录。

Selectdegreefromscorewheredegreein(85,86,88);或者Select*fromscorewheredegreein(85,86,88);

6、查询Student表中“95031”班或性别为“女”的同学记录。

7、以Class降序查询Student表的所有记录。

Selectclassfromstudentwhereclassorderbyclassdesc;

8、以Cno升序、Degree降序查询Score表的所有记录。

Selectcnofromscorewherecnoorderbycno;

Selectdegreefromscorewheredegreeorderbydegreedesc;

9、查询“95031”班的学生人数。

selectcount(class)fromstudentwhereclass=95033;

10、查询Score表中的最高分的学生学号和课程号。

selectsno,degreefromscorewheredegree=(selectmax(degree)fromscore);

11、查询‘3-105’号课程的平均分。

selectavg(cno)fromscorewherecno=‘3-105’;

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

13、查询最低分大于70，最高分小于90的Sno列。

selectsnofromscorewheredegreebetween70and90;

14、查询所有学生的Sname、Cno和Degree列。（两张表查询）

SelectA.sname,B.cno,B.degreefromstudentasAjoinscoreasBonA.sno=B.sno;

Left/right/inner在实际用时可以不加，as也可以省略，如下

SelectA.sname,B.cno,B.degreefromstudent(as)A(left/right/inner)joinscore(as)BonA.sno=B.sno;

(NTOE:要选出不同表中的数据，需要将多个表进行并联，sname在studetn，cno在course和score，degree在score，所以sno为student和degree共有的，则在这两张表上进行并联，首先从student中选出sname，其次将A加入到score表，将B加入到A表，同时A表的sno和B表的sno相等)

15、查询所有学生的Sno、Cname和Degree列。

SelectA.cname,B.sno,B.degreefromcourseasAjoinscoreasBonA.cno=B.cno;

16、查询所有学生的Sname、Cname和Degree列。(三张表查询)

selectA.sname,B.cname,c.degreefromstudentasAjoin(courseB,scoreC)onA.sno=C.snoandB.cno=C.cno;

17、查询“95033”班所选课程的平均分。

18、假设使用如下命令建立了一个grade表：

createtablegrade(number(3,0),number(3),char(1));

insertintogradevalues(90,100,’A’);

insertintogradevalues(80,89,’B’);

insertintogradevalues(70,79,’C’);

insertintogradevalues(60,69,’D’);

insertintogradevalues(0,59,’E’);

commit;

commit

现查询所有同学的Sno、Cno和rank列。（两表查询）

selectsno,cno,levelfromscore,gradewherescore.degreebetweengrade.lowandgrade.upporderbylevel;

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

Step1非最高分：degreenotin(selectmax(degree)fromscore)

select*fromscorewheresnonotin(selects.snofromscoresjoin(selectcno,max(degree)degreefromscoregroupbycno)scon(sc.cno=s.cnoandsc.degree=s.degree));

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

23、查询“张旭“教师任课的学生成绩。

selectA.tno,B.cno,C.degreefromteacherasAjoin(courseB,scoreC)onA.tno=B.tnoandB.cno=C.cnowhereA.tname=‘张旭’;

24、查询选修某课程的同学人数多于5人的教师姓名。

这样写（报错）：X.tno,Y.cno只能取其中一个

selecttnamefromteacherwheretnoin(selectX.tno,Y.cnofromcourseX,scoreYwhereX.cno=y.cnogroupbyx.tnohavingcount(x.tno)>5);

ERROR1241(21000):Operandshouldcontain1column(s)

正确写法：

selecttnamefromteacherwheretnoin(selectX.tnofromcourseX,scoreYwhereX.cno=y.cnogroupbyx.tnohavingcount(x.tno)>5);

25、查询95033班和95031班全体学生的记录。

26、查询存在有85分以上成绩的课程Cno.

selectdistinctcnofromscorewheredegree>85;

27、查询出“计算机系“教师所教课程的成绩表。(三表相联)

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

31、查询所有教师和同学的name、sex和birthday.

selectA.snameasname,A.ssexassex,sbirthdayasbirthdayfromstudentAunionselectB.tnameasname,B.tsexassex,B.tbirthdayasbirthdayfromteacherB;

32、查询所有“女”教师和“女”同学的name、sex和birthday.

33、查询成绩比该课程平均成绩低的同学的成绩表。

selectA.*fromscoreAwheredegree<(selectavg(degree)fromScoreBwhereB.CNO=A.CNO);

34、查询所有任课教师的Tname和Depart.

选项两个参数以上，防止备选项出现重复，比如出现两个“电子工程系”

selecttname,departfromteacher;

selectA.tname,A.departfromteacherAjoinCourseBonB.Tno=A.Tno;

selecttname,departfromteacherwheretnoin(selecttnofromcourse);

35查询所有未讲课的教师的Tname和Depart.

selecttname,departfromteacherwheretnonotin(selecttnofromcourse);

selecttname,departfromteacherwherenotexists(select*fromcoursewhereteacher.tno=course.tno);

36、查询至少有2名男生的班号。

37、查询Student表中不姓“王”的同学记录。

38、查询Student表中每个学生的姓名和年龄。

selectsnameasname,(year(now())-year(sbirthday))asagefromstudent;

39、查询Student表中最大和最小的Sbirthday日期值。

selectsname,sbirthdayasmaxbirthdayfromstudentwheresbirthday=(selectmin(sbirthday)fromstudent)unionselectsname,sbirthdayasminbirthdayfromstudentwheresbirthday=(selectmax(sbirthday)fromstudent);

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

selectclass,(year(now())-year(sbirthday))asagefromstudentorderbyclassdesc,agedesc;

41、查询“男”教师及其所上的课程。

42、查询最高分同学的Sno、Cno和Degree列。

Selectsno,cno,degreefromscorewheredegree=(selectmax(degree)fromscore);

43、查询和“李军”同性别的所有同学的Sname.

44、查询和“李军”同性别并同班的同学Sname.

45、查询所有选修“计算机导论”课程的“男”同学的成绩表（三表关联）

2.3.基础练习二及参考sql语句

数据库中有三张表，分别为student,course,SC（即学生表，课程表，选课表）

//三张表截图如下：

创建表:

CREATETABLESTUDENT(

SNOVARCHAR(8)NOTNULL,

SNAMEVARCHAR(5)NOTNULL,

SAGEVARCHAR(3)NOTNULL,

SDEPTVARCHAR(7)NOTNULL);

INSERTINTOSTUDENT(SNO,SNAME,SSEX,SAGE,SDEPT)VALUES(9512101,’李勇’,’男’,19,’计算机系’);

CREATETABLECOURSE(

CNOVARCHAR(4)NOTNULL,

CNAMEVARCHAR(7)NOTNULL,

HOURSVARCHAR(3)NOTNULL);

CREATETABLESC(

GRADEVARCHAR(6)NOTNULL)

1、分别查询学生表和学生修课表中的全部数据。

Select*fromsc;

2、查询成绩在70到80分之间的学生的学号、课程号和成绩。

selectsno,cno,gradefromscwheregradebetween70and80;

3、查询C01号课程成绩最高的分数。

4、查询学生都选修了哪些课程,要求列出课程号。

selectcnoas课程号,cnameas课程名称fromcoursewherecnoin(selectcnofromsc);

5、查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。

6、统计每个系的学生人数。

Selectcount(学生id)from表groupby系字段；

selectsdept,count(sno)fromstudentgroupbysdept;

7、统计每门课程的修课人数和考试最高分。

selectcname,count(*)as修课人数,max(grade)fromcourse,scwherecourse.cno=sc.cnogroupbycname;

8、统计每个学生的选课门数,并按选课门数的递增顺序显示结果。

selectsname,count(sc.sno)as选课人数fromsc,studentwherestudent.sno=sc.snogroupbysnameorderbycount(sc.sno)asc;

9、统计选修课的学生总数和考试的平均成绩。

selectcount(distinctsno)as学生总数,avg(grade)as平均成绩fromsc;

10、查询选课门数超过2门的学生的平均成绩和选课门数。

Groupby一般最后用作求数或者排名用

selectsname,avg(sc.grade),count(sc.cno)fromstudent,scwheresc.sno=student.snogroupbysnamehavingcount(sc.sno)>2;

11、列出总成绩超过200分的学生,要求列出学号、总成绩。

Selectsnoas学号,sum(grade)as总成绩fromscgroupbysnohavingsum(grade)>200;

12、查询选修了c02号课程的学生的姓名和所在系。

13、查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。

selectA.sname,B.sno,B.gradefromstudentA,scBwhereA.sno=B.snoandB.grade>80orderbygradedesc;

使用using匹配相同字段必须加()

selectA.sname,B.sno,B.gradefromstudentAjoinscBusing(sno)whereB.grade>80orderbygradedesc;

14、查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。

15、查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

selectA.snameas年龄相同的学生姓名,A.sageas年龄fromstudentAjoinstudentBonA.sage=B.sagegroupbyA.sname,A.sageorderbyA.sage;

这种做法看似正确，其实多过滤出来三组数据；

selectA.snameas年龄相同的学生姓名,A.sageas年龄fromstudentAjoinstudentBonA.sagein(selectsagefromstudentwhereA.sage=B.sageandA.sname!=B.sname)groupbyA.sname,A.sageorderbyA.sage;

16、查询哪些课程没有人选,要求列出课程号和课程名。

Selectcnoas课程号,cnameas课程名fromcoursewherecnonotin(selectcnofromsc);

17、查询有考试成绩的所有学生的姓名、修课名称及考试成绩要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。

Selectstudent.sname,course.cname,sc.gradefromstudent,course,scwherestudent.sno=sc.sno=course.cno=sc.cnoandsc.gradeisnotnullorderbysname;

18、分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,并要求将这两个查询结果合并成一个结果集,并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。(Union)

Selectstudent.ssame,student.ssex,course.cname,sc.gradefromstudent,course,scwherestudent.sdept=’计算机系’andstudent.sno=sc.snoandcourse.cno=sc.cno;

19、用子查询实现如下查询:

查询选修了C01号课程的学生的姓名和所在系。

Selectdistinctstudent.sname,student.sdeptfromstudent,scwherestudent.snoin(selectsc.snofromscwheresc.cno=’c01’);

查询数学系成绩80分以上的学生的学号、姓名。

Selectsno,snamefromstudentwheresnoin(selectsnofromscwheresc.grade>80)andsnoin(selectsnofromstudentwheresdept=’数学系’);

查询计算机系学生所选的课程名.

Selectcourse.cnamefromcoursewherecnoin(selectcnofromscsc.snoin(selectsnofromstudentwheresdept=’计算机系’));

--创建数据库

CREATEDATABASEjing_dong;

--切换数据库

USEjing_dong;

--创建用户表

CREATETABLEusers

(

uidINTPRIMARYKEYAUTO_INCREMENT,

usernameVARCHAR(30),

passwordVARCHAR(30)

);

--查询表数据

SELECT*

FROMusers;

/*

概述:

实际开发中,如果SQL中的部分内容是要求用户键盘录入的,当用户录入一些非法字符的时候,就改变了SQL语句的结构,

从而引发一些列的安全问题,这些问题,就是SQL注入攻击问题.

例如:登陆账号的校验SQL如下

听话的用户:

录入的账号:sdkfsd

录入的密码:weor

小屌丝:

录入的账号:slkkjlsd

老屌丝:

*/

--校验登陆

FROMusers

DROPDATABASEjing_dong;

--创建数据库,报表库

CREATEDATABASErecord_form;

USErecord_form;

--查询所有的数据表

SHOWTABLES;

FROMcustomers;

--导入表数据

--直接运行报表项目数据.sql文件即可,我的在:..教案-报表项目数据.sql

--以下是各种需求,通过的代码实现即可.

--需求1:选中employees表的所有数据

FROMemployees;

--需求2:查询每个客户的ID,companyname,contactname,contacttitle,city,和country.并按照国家名字排序

SELECTcompany_name,

contact_name,

contact_title,

city,

country

FROMcustomers

ORDERBYcountry;

--需求3:查询每一个商品的product_name，category_name，quantity_per_unit，unit_price，units_in_stock并且通过unit_price字段排序

SELECTproduct_name,category_name,quantity_per_unit,unit_price,units_in_stock

FROMcategoriesc,

productsp

WHEREc.category_id=p.category_id

ORDERBYunit_price;

--需求4:列出所有提供了4种以上不同商品的供应商列表所需字段：supplier_id,company_name,andproducts_count(提供的商品种类数量).

SELECTp.supplier_id,

company_name,

COUNT(*)ASproducts_count

FROMproductsp,

supplierss

WHEREp.supplier_id=s.supplier_id

GROUPBYp.supplier_id,company_name

HAVINGproducts_count>4;

--需求5:提取订单编号为10250的订单详情，显示如下信息：

--product_name,quantity,unit_price（order_items表),discount，order_date按商品名字排序

SELECTo.order_id,--订单编号

product_name,--商品名字

quantity,--商品数量

oi.unit_price,--商品单价

discount,--折扣

order_itemsoi,

orderso

WHEREp.product_id=oi.product_id

ANDoi.order_id=o.order_id

ANDo.order_id=10250

ORDERBYproduct_name;

SELECTc.customer_id,--顾客id

c.company_name,--顾客公司名字

e.employee_id,--员工id

e.first_name,--员工名

e.last_name,--员工姓

o.ship_country--收件人国家

FROMemployeese,

orderso,

customersc

WHEREe.employee_id=o.employee_id

ANDo.customer_id=c.customer_id

SELECToi.order_id,

p.product_name,

oi.unit_price,

oi.quantity,

s.supplier_id,

s.company_nameASsupplier_name

FROMorder_itemsoi,

productsp,

WHEREoi.product_id=p.product_id

ANDp.supplier_id=s.supplier_id

ANDorder_id=10248;

--需求8:提取每件商品的详细信息，包括商品名称（product_name）,供应商的公司名称(company_name，在suppliers表中),

--类别名称category_name,商品单价unit_price,和每单位商品数量quantityperunit

SELECTproduct_name,--商品名字

s.company_nameASsupplier_name,--供应商的公司名称

c.category_name,--类别名称

unit_price,--商品单价

quantity_per_unit--每单位商品数量,例如,一箱20瓶

ANDp.supplier_id=s.supplier_id;

SELECTCOUNT(*)

FROMorders

--需求10:统计2013年入职的员工数量，统计字段起别名number_of_employees

SELECTCOUNT(*)number_of_employees

FROMemployees

--需求11:统计每个供应商供应的商品种类数量,结果返回供应商IDsupplier_id

--，公司名字company_name，商品种类数量（起别名products_count)使用products和suppliers表.

SELECTp.supplier_id,s.company_name,COUNT(*)ASproducts_count

GROUPBYp.supplier_id,s.company_name;

--需求12:我们要查找ID为10250的订单的总价（折扣前），SUM(unit_price*quantity)

SELECTorder_id,SUM(unit_price*quantity)

FROMorder_items

WHEREorder_id=10250;

--需求13:统计每个员工处理的订单总数,结果包含员工IDemployee_id，姓名first_name和last_name，处理的订单总数(别名orders_count)

SELECTe.employee_id,e.first_name,e.last_name,COUNT(*)ASorders_count

FROMemployeese

INNERJOINordersoONe.employee_id=o.employee_id

GROUPBYe.employee_id,e.first_name,e.last_name;

--需求14:统计每个类别中的库存产品值多少钱？显示三列：category_id,category_name,和category_total_value,如何计算库存商品总价：SUM(unit_price*units_in_stock)。

SELECTc.category_id,

category_name,

SUM(unit_price*units_in_stock)AScategory_total_value--库存商品总和

categoriesc

GROUPBYc.category_id,category_name;

--需求15:计算每个员工的订单数量

--需求16:计算每个客户的下订单数结果包含：用户id、用户公司名称、订单数量（customer_id,company_name,orders_count）

SELECTc.customer_id,c.company_name,COUNT(*)orders_count

FROMorderso,

WHEREo.customer_id=c.customer_id

GROUPBYc.customer_id,c.company_name;

--需求17:统计2016年6月到2016年7月用户的总下单金额并按金额从高到低排序

--结果包含：顾客公司名称company_name和总下单金额（折后实付金额）total_paid

--提示：

--计算实际总付款金额：SUM(unit_pricequantity(1-discount))

SUM(unit_price*quantity*(1-discount))total_paid--实际总付款金额

customersc,

order_itemsoi

WHEREo.order_id=oi.order_id

GROUPBYcompany_name

ORDERBYtotal_paidDESC;

--需求18:统计客户总数和带有传真号码的客户数量

--需要字段：all_customers_count和customers_with_fax_count

SELECTCOUNT(*)all_customers_count,--客户总数

COUNT(fax)customers_with_fax_count--带有传真号码的客户数量

--case.when简介:类似于Python中的if.elseif,做分支的.

--case.when语法:casewhen条件1then值1when条件2then值2...else值nend

--需求19:我们要在报表中显示每种产品的库存量，但我们不想简单地将“units_in_stock”列放在报表中。报表中只需要一个总体级别，例如低，高：

--库存大于100的可用性为高(high)

--50到100的可用性为中等(moderate)

--小于50的为低(low)

--零库存为(none)

SELECTproduct_id,

product_name,

units_in_stock,

CASE

FROMproducts;

--需求20:创建一个报表，统计员工的经验水平

--显示字段：first_name,last_name,hire_date,和experience

--经验字段（experience）：

SELECTfirst_name,

last_name,

hire_date,

ENDexperience

--需求21:我们的商店要针对北美地区的用户做促销活动：任何运送到北美地区（美国，加拿大)的包裹免运费。创建报表，查询订单编号为10720~10730活动后的运费价格

SELECTorder_id,

customer_id,

ship_country,

ELSEfreight

--需求22:需求：创建客户基本信息报表,包含字段：客户idcustomer_id,公司名字company_name

--所在国家country,使用语言language,使用语言language的取值按如下规则

SELECTcustomer_id,

country,

ENDlanguage

--需求23:需求：创建报表将所有产品划分为素食和非素食两类

--报表中包含如下字段：产品名字product_name,类别名称category_name

--膳食类型diet_type:

--素食

SELECTproduct_name,

WHEREc.category_id=p.category_id;

--需求24:在引入北美地区免运费的促销策略时，我们也想知道运送到北美地区和其它国家地区的订单数量

--促销策略,参见需求21的代码.

SELECTCASE

COUNT(*)ASorder_count--订单总数

GROUPBYCASE

--简化写法

GROUPBYcountry;

--需求25:创建报表统计供应商来自那个大洲,报表中包含两个字段：供应商来自哪个大洲（supplier_continent）和供应产品种类数量（product_count）

--供应商来自哪个大洲（supplier_continent）包含如下取值：

COUNT(*)

GROUPBYnewCountry;

--需求26:需求：创建一个简单的报表来统计员工的年龄情况

--报表中包含如下字段

--员工数量（employee_count）

,COUNT(*)

GROUPBYage;

--查询结果有两个字段：represented_by_owner和not_represented_by_owner

GROUPBYtitle;

--case.when的另一种写法:case字段when值1then结果1when值2then结果2...else结果nend

SELECTCASEcontact_title

--需求28:Washington(WA)是Northwind的主要运营地区，统计有多少订单是由华盛顿地区的员工处理的，

--多少订单是有其它地区的员工处理的

--结果字段：orders_wa_employees和orders_not_wa_employees

employeese

WHEREe.employee_id=o.employee_id;

--需求29:创建报表，统计不同类别产品的库存量，将库存量分成两类>30和<=30两档分别统计数量

--报表包含三个字段,类别名称category_name,库存充足high_availability,库存紧张low_availability

c.category_name,

COUNT(CASEWHENunits_in_stock>30THENproduct_idEND)high_availability,

COUNT(CASEWHENunits_in_stock<=30THENproduct_idEND)low_availability

GROUPBYc.category_id,c.category_name;

--需求30:创建报表统计运输到法国的的订单中，打折和未打折订单的总数量

--结果包含两个字段：full_price（原价）和discounted_price（打折）

--需求31:输出报表，统计不同供应商供应商品的总库存量，以及高价值商品的库存量（单价超过40定义为高价值）

--结果显示四列：

--供应商IDsupplier_id

--供应商公司名company_name

--由该供应商提供的总库存all_units

--由该供应商提供的高价值商品库存expensive_units

SELECTs.supplier_id,

s.company_name,

FROMsupplierss,

GROUPBYs.supplier_id,s.company_name;

--需求32:创建报表来为每种商品添加价格标签，贵、中等、便宜

--结果包含如下字段：product_id,product_name,unit_price,和price_level

--价格等级price_level的取值说明：

unit_price,

ENDASprice_level

--需求33:制作报表统计所有订单的总价（不计任何折扣）对它们进行分类。

--包含以下字段：

--order_id

--total_price（折扣前）

--price_group

--字段price_group取值说明：

SELECTo.order_id,

SUM(unit_price*quantity)total_price,

GROUPBYo.order_id;

--需求34:统计所有订单的运费，将运费高低分为三档

--报表中包含三个字段

--low_freightfreight值小于“40.0”的订单数

--avg_freightfreight值大于或等于“40.0”但小于“80.0”的订单数

--high_freightfreight值大于或等于“80.0”的订单数

SELECTCOUNT(CASEWHENfreight>=80.0THENorder_idEND)high_freight,

COUNT(CASEWHENfreight>=40.0ANDfreight<80.0THENorder_idEND)avg_freight,