当前位置：文档之家 › 基本的SQL语句练习

基本的SQL语句练习

  • 格式：docx
  • 大小：27.38 KB
  • 文档页数：6

基本的SQL语句练习（学生课程表） 创建表 [c-sharp]view plaincopyprint? 1. DROP TABLE IF EXISTS student; 2. CREATE TABLE student( 3. sno int auto_increment primary key, 4. sname varchar(8), 5. ssex varchar(3), 6. sage int , 7. sclass varchar(6) 8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 9. INSERT INTO student(sname,ssex,sage,sclass) VALUES('李勇','男',20,'y01'); 10. INSERT INTO student(sname,ssex,sage,sclass) VALUES('刘晨','男',21,'y02'); 11. INSERT INTO student(sname,ssex,sage,sclass) VALUES('王敏','女',19,'y02'); 12. INSERT INTO student(sname,ssex,sage,sclass) VALUES('张力','男',25,'y05'); 13. DROP TABLE IF EXISTS course; 14. CREATE TABLE course( 15. cno int auto_increment primary key, 16. cname varchar(20), 17. ccredit int 18. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 19. INSERT INTO course(cname,ccredit) VALUES('C语言',5); 20. INSERT INTO course(cname,ccredit) VALUES('数据库',5); 21. INSERT INTO course(cname,ccredit) VALUES('开发模式_VB',5); 22. DROP TABLE IF EXISTS sc; 23. CREATE TABLE sc( 24. sno int references student(sno) on delete cascade, 25. cno int references course(cno) on delete cascade, 26. grade int 27. ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 28. INSERT INTO sc VALUES(1,1,90); 29. INSERT INTO sc VALUES(1,2,95); 30. INSERT INTO sc VALUES(2,1,55); 31. INSERT INTO sc VALUES(4,3,null);

DROP TABLE IF EXISTS student;CREATE TABLE student(sno int auto_increment primary key,sname varchar(8),ssex varchar(3),sage int ,sclass varchar(6)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO student(sname,ssex,sage,sclass) VALUES(INSERT INTO student(sname,ssex,sage,sclass) VALUES(INSERT INTO student(sname,ssex,sage,sclass) VALUES(INSERT INTO student(sname,ssex,sage,sclass) VALUES(DROP TABLE IF EXISTS course;CREATE TABLE course(cno int auto_increment primary key,cname varchar(20),ccredit int) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、查出全体同学的学号、姓名 select sno,sname from student; 2、查询全体同学的姓名学号班级（按顺序输出） select sname,sno,sclass from student; 3、查询全体同学的记录（显示所有行） select * from student; 4、查询全体同学的姓名及出生年份 select sname,(2010-sage) as '出生年份' from student ; 5、查询全体同学姓名出生年份班级（班级要用小写字母lower函数） select sname,2010-sage as '出生年份',lower(sclass) from student; 6、查询全体同学的姓名/出生年份/所在班级列为YearOfBirth select sname,2010-sage YearOfBirth,sclass from student; 7、查询选中学员的所在班级并且去掉重复行用distinct select distinct sclass from student; 8、查询Y02班全体同学名单 select * from student where sclass='y02'; 9、查询所有年龄在20岁一下的同学姓名及年龄 select sname,sage from student where sage<20; 10、查询考试不及格的同学的姓名及成绩 select s.sname,sc.grade from student s,sc where s.sno = sc.sno and sc.grade<60; 11、查询年龄在19－20岁（包括19、20）之间的同学姓名、班级、年龄 select sname,sclass,sage from student where sage in (19,20);（19、20 比较特殊选项少可以使用 in） select sname,sclass,sage from student where sage between 19 and 20; select sname,sclass,sage from student where sage>=19 and sage<=20; 12、查询年龄不在19－20岁之间的同学姓名、班级、年龄 select sname,sclass,sage from student where sage not in (19,20); select sname,sclass,sage from student where sage not between 19 and 20; select sname,sclass,sage from student where sage<19 or sage>20; 13、查询y02班级和y05班的同学的姓名、性别 select sname,sclass from student where sclass in ('y02','y05'); select sname,sclass from student where sclass='y02' or sclass='y05'; 14、查询不是y02或者y05班的同学的姓名、性别 select sname,sage from student where sclass not in('y02','y05'); select sname,sage from student where sclass!='y02' and sclass!='y05'; select sname,sage from student where not sclass='y02' and not sclass='y05'; 15、查所有姓刘的同学的姓名、学号、性别( " % "表示一个或者多个，" _ "表示只占一个字符) select sname,sno,ssex from student where sname like'刘%'; 16、查所有姓张且全名只有2个汉子的同学的所有信息 select * from student where sname like'张_'; 17、某些学生未考试查缺成绩的同学的学号和课程号 select sno,cno,grade from sc where grade is null; 18、查询所有成绩的同学的学号、课程号和成绩 select sno,cno,grade from sc where grade is not null; 19、查y02班年龄在20岁一下的姓名和年龄 select sname,sage from student where sclass='y02' and sage<20; 20、查选修1号课程的同学的学号和成绩，按成绩降序排序 select sno,grade from sc where cno=1 order by grade desc; 21、查全体同学信息查询结果按所在班级的班级名称按降序排列，同班同学按年龄升序排列 select * from student order by sclassdesc,sageasc; 22、查询学员的总人数 select count(sno) from student;

合集下载

SQL语句进阶练习(多表连接查询)-提供查询题目与解答

SQL语句进阶练习(多表连接查询)-提供查询题目与解答

SQL语句进阶练习（多表连接查询）-提供查询题目与解答
说明
第一眼看到表的结构可能心中会觉得题目比较简单，但是当我深入做题目的时候发现这题目可能有些变态，几乎费了我一天的时间才 把这些题目做出来（可能是因为我不怎么聪明的原因） 在我所做的题解中有一部分不是最优解，还有很多高效的查询方式，比如最后一题的解答，我有三种方式解答，但是最后的一种才是 最简单的行转列的方式。 在这些题目中使用最多的就是join多表连接查询
-方- 法二 SELECT st.s_id,st.s_name FROM ( SELECT *,COUNT(c_id) FROM score GROUP BY s_id ) one RIGHT JOIN student st ON one.s_id=st.s_id WHERE `COUNT(c_id)`<3 OR `COUNT(c_id)` IS NULL
--查5询. 两门以上不及格课程的同学的学号及其平均成绩 SELECT s_id,AVG(s_score) FROM score WHERE s_score<60 GROUP BY s_id HAVING COUNT(c_id)>=2
--查6询. 平均成绩大于85的所有学生的学号、姓名和平均成绩 SELECT st.s_id,s_name,`AVG(s_score)` avg_score FROM student st INNER JOIN ( SELECT s_id,AVG(s_score) FROM score GROUP BY s_id HAVING `AVG(s_score)`>85) sc ON st.s_id=sc.s_id
题目解答
稍微基础题目解答
--查1询. 平均成绩大于60分的学生的学号和平均成绩 -题- 目解析一 如果平均成绩为已选科目的平均分解答如下 SELECT s_id,AVG(s_score) FROM score GROUP BY s_id HAVING `AVG(s_score)`>60 -題- 目解析二 如果平均分是总科目分数除以总门数，没有考当0做分解答如下 SELECT s_id,(SUM(s_score)/3) avg_score FROM score GROUP BY s_id HAVING `avg_score`>60
SQL操作语句

SQL操作语句

SQL操作语句SQL语句与Mysql的语句⼤体上⽐较相似。

以下是sql server的⼀套练习题，是很好的数据库操作语句学习资料，学校的学习资料，在此整理了以下。

数据库exam：这是⼀个模拟电⼦商务，⽹上直销的数据，主要销售计算机配件。

该数据库包含了订单数据、经销商基本数据、产品基本数据以及供应商基本数据等。

该数据库共包含6个表，表名分别为：orders、distributors、suppliers、city、items和products。

各个表的内容与格式如下：表1 ⽹上直销订单表格，表名为：orders列名描述数据类型OrderNum订单号码IntOrderDate订购⽇期DatatimeDistriNum经销商号码IntPaidDate付款⽇期Datatime表2 经销商表格，表名为：distributors列名描述数据类型DistriNum经销商号码IntBossName经理名称CharCompany公司名称CharCityCode所属市区代码CharAddress公司地址CharPhone公司电话号码Char表3 供应商表格，表名为：suppliers列名描述数据类型SuppCode供应商代码CharSuppName供应商名称Char表4 市区表格，表名为：city列名描述数据类型CityCode市区代码CharCityName市区名称Char表5 订单项⽬表格，表名：items列名描述数据类型Item订单项⽬号码IntOrderNum订单号码IntProdNum产品号码IntSuppCode供应商代码CharQuantity单项定购数量IntTotalPrice单项总价Money表6 产品表格，表名：products列名描述数据类型ProdNum产品号码IntSuppCode供应商CharDescriptions产品名称CharUnitPrice产品单价Money⼀、数据表的简单操作⽤SQL语句选取表中所有的列：1、查询exam数据库中city表中所有的列的内容select*from city;2、查询exam数据库中orders表OrderDate列的内容select OrderDate from orders;选取惟⼀的数值：消除重复关键字：distinct （放在select后⾯）1、查询exam数据库中orders表中的订购⽇期OrderDate，并且相同的订购⽇期只显⽰⼀次（distinct）select distinct OrderDate from orders;2、对记录进⾏操作——选取特定的记录：查询exam数据库中供应商代码为HHT的供应商的实际名称select SuppName from city where SuppCode= "HHT";3、对记录进⾏操作——排除特定的记录：查询exam数据库中供应⼚代码不为HHT的所有供应商的实际名称select SuppName from city where SuppCode!= "HHT";3、对数据进⾏操作——选取介于某⼀范围的数据：查询exam数据库的products表中所有单价不超过20元的产品的名称及其对应的价格select Descriptions,UnitPrice from products where UnitPrice <=20;4、对数据进⾏操作——选取属于某⼀⼦集合的数据：in：在...⾥查询exam数据库中经销商号码为1006、1009、1013、1014、1017的经理名称以及其所在的公司名称select DistriNum,BossName,Company from distributors where DistriNum in (1006,1009,1013,1014,1017);5、对字符串进⾏操作——选取⼦字符串：查询exam数据库中所有经销商的电话区号select substring(Phone,1,3) from distritubors; //第⼀位和从这⼀位开始⼏位查询exam数据库中所有经销商的姓select substring(BossName,1,1) from distributors;6、对字符串进⾏操作——搜索⼦字符串：‘%’：表⽰N个任意字符查询exam数据库中所有经理姓“王”的公司的名称、电话以及公司地址select BossName,Company,Phone,Address from distributors where BossName like'王%';⼆、⼀般数据查询与处理1、同时执⾏多个SQL语句：实现在⼀次查询中同时查询exam数据库中orders表和distributors表所有列的内容select*from orders;goselect*from distributors;go2、⽤order by⼦句对数据进⾏排序：查询exam数据库products表格中所有的记录，查询结果根据单价由⼩到⼤的顺序(降序)排序select*from products order by UnitPrice desc;3、使⽤算术运算符：查询exam数据库products表中的所有记录，并且把每个产品单价显⽰成调⾼10%后的价格（只显⽰，数据库不变）select ProNum,SuppCode,description,UnitPrice * 1.1from products;3、查询exam数据库products表中的所有记录，并且把每个产品单价显⽰成8折后的价格select ProNum,SuppCode,description,UnitPrice *0.8from products;5、搜索单⼀长度的任意字符串：‘_’:表⽰⼀个任意字符查询exam数据库suppliers表中供应商代码的第1、3位分别为H和T的所有供应商的记录select*from suppliers where SuppCode like "H_T";6、使⽤常⽤的字符串函数：查询exam数据库distribuotrs表中经销商号码为1001的地址字符串的长度select len(Address) from distributors where DistriNum = "1001";7、查询字符串的长度select len(‘中华⼈民共和国’);8、将字符串‘I LOVE SQL SERVER‘的所有字母转换成⼩写字母select LOWER('I LOVE SQL SERVER');9、将字符串‘abcdefg’中的⼩写字母转换成⼤写字母select UPPER('abcdefg');10、将字符串‘ abcdefg’前的空格去掉select ltrim(' abcdefg);11、将字符串‘uvwxyz ’后的空格去掉select rtrim('uvwxyz ');12、将字符串‘我喜欢数据库’中的字符由左⾄右反转排列select REVERSE('我喜欢数据库');13、求出字符A的ASCII码select ASCII('A');14、判断数字5的正负号，如果正返回1，负返回-1select SIGN(5);15、使⽤常⽤的数学函数：计算出以下三个数的绝对值：-2.5，0.0，7.4select ABS(-2.5)，ABS(0.0)，ABS(7.4);16、返回分别⼤于或等于以下数字的最⼩整数：5.5，-7.2，0.0select floor(5.5)，floor(-7.2)，floor(0.0);17、返回π（PI）的值select PI();18、返回630.67的近似值，并将该数分别四舍五⼊到1、-2、3、0长度的⼩数select ROUND(630.67,1),ROUND(630.67,-2),ROUND(630.67,3),ROUND(630.67,0);19、返回以下3个数的平⽅值：4、-6，6.6select SQUARE(4),SQUARE(-6),SQUARE(6.6);20、返回以下3个数的算数平⽅根：4，0，50select SQRT(4)，SQRT(0)，SQRT(50);21、计算Cos（3.1415926）的值 sin(3.14159260) ctg(3.14) tg(3.1415926)select COS（3.1415926),SIN(3.14159260),COT(3.14),TAN(3.1415926);22、计算2的⾃然对数、指数值select LOG(2),EXP(2);23、计算1000的以10为底的对数select LOG10(1000);24、计算2的10次⽅select POWER(2,10);三、常⽤函数常⽤的集总函数的应⽤：1、查询exam数据库products表中共销售多少种产品select COUNT(*) from products;2、查询exam数据库items表中代码为HSC的供应商已订货的总⾦额select SuppCode,SUM(TotalPrice) from items where SuppCode ='HSC';3、查询exam数据库products表中所有供应商所供应的货品的平均单价select SuppCode,AVG(UnitPrice) from products group by SuppCode;4、查询exam数据库products表中每⼀个供应商产品的最⾼单价/最低单价select SuppCode,MAX(UnitPrice),MIN(UnitPrice) from products group by SuppCode;6、时间函数：（1）获取当前时间select getdate();（2）、在1991-07-10 2:30:00.000时间上增加21天select dateadd(d,21,’ 1991-07-102:30:00.000’);（3）、返回1991-07-10 2:30:00.000五天前的时间select DATEADD(D,-5,'1991-07-10 2:30:00.000');（4）、返回两个⽇期间隔的时间select DATEDIFF(D,'02/20/1999','02/28/1999');（5）、以字符串形式返回当天⼏年select DATENAME(Y,GETDATE());（6）、以整数形式返回当天⼏号select DATEPART(D,GETDATE());（7）、查询当前UTC时间（协调时间）select GETUTCDATE();四、⾼级数据查询与处理1、在⼀个select语句中使⽤多个表格：查询exam数据库中每⼀个经销商所有的订单数据，列出经销商号码、公司名称、电话号码以及订货⽇期select distributors.DistriNum,Company,Phone,OrderDate from distributors,orders where orders.DistriNum = distributors.DistriNum 2、在select语句中使⽤⼦查询：查询exam数据库中与经理名称为‘王为’的经销商同在⼀个市区的所有经销商的数据内容select*from distributors where CityCode=(select CityCode from distributors where BossName='王为');3、⽤union运算符实现从不同的表格抽取相同格式的数据：查询exam数据库中单价不⼤于25元或者是已订货项⽬中数量⼩于2的产品数据select ProdNum,SuppCode,Description,UnitPrice from products where UnitPrice <=25unionselect ProdNum,SuppCode,Quantity from items where Quantity <24、添加记录：在orders表中添加⼀笔记录，OrderNum为119，OrderDate为1905-06-8，DistriNum为1022，PaidDate为1905-06-9insert into orders(OrderNum,OrderDate,DistriNum,PaidDate) values(119,1905-06-8,1022,1905-06-9);5、删除记录：删除orders表中OrderNum为119的记录delete from orders where OrderNum =1196、更新记录：将orders表中订单号为118的订单的订购⽇期改为2003-07-11update orders set OrderDate = "2003-07-11" where OrderNum =1187、实现查询exam数据库中每⼀个经销商的订单号码（orders表中）与经销商公司所属市区的代码（ditributoirs表中）select orders.DistriNum,OrderNum,distributors.CityCode from orders,distributors where orders.DistriNum = distributors.DistriNum8、实现查询exam数据库中公司在宣武区的所有经销商的数据内容select*from distributors where CityCode= (select CityCode from city where CityName='宣武区')9、实现查询exam数据库中订单号码为100对应的经销商的情况select*from distributors where DistriNum= (select DistriNum from orders where OderNum =100)五、设计数据库⽤T-SQL语句实现默认值与列的结合：默认值创建：create default 关键字 as XX绑定默认值：sp_bindefault ‘默认值名’，’表名.[列名]’重命名默认值：exec sp_rename ‘旧名称’,’新名称’1.在exam数据库中新建名为moren的默认值，值为100，并把该默认值与orders表的OrderNum列结合(sp_bindefault)create default moren as100gosp_bindefault 'moren','orders.[OrderNum]'gosp_bindefault 将新默认值绑定到列(尽管最好使⽤ DEFAULT 约束)或者绑定到别名数据类型。

数据库sql课后练习题及答案解析

数据库sql课后练习题及答案解析

先创建下面三个表：（book表）（borrow表）（reader表）1) 找出姓李的读者姓名(NAME)和所在单位(COMPANY)。

2) 列出图书库中所有藏书的书名(BOOK_NAME)及出版单位(OUTPUT)。

3) 查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE)，结果按单价降序排序。

4) 查找价格介于10元和20元之间的图书种类(SORT)，结果按出版单位(OU TPUT)和单价(PRICE)升序排序。

5) 查找书名以”计算机”开头的所有图书和作者(WRITER)。

6) 检索同时借阅了总编号(BOOK_ID)为和两本书的借书证号(READER_ID)。

##7)* 查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。

8)* 找出李某所借所有图书的书名及借书日期(BORROW_DATE)。

9)* 无重复地查询2006年10月以后借书的读者借书证号(READER_ID)、姓名和单位。

##10)* 找出借阅了<FoxPro大全>一书的借书证号。

11) 找出与”赵正义”在同一天借书的读者姓名、所在单位及借书日期。

12) 查询2006年7月以后没有借书的读者借书证号、姓名及单位。

#13) 求”科学出版社”图书的最高单价、最低单价、平均单价。

##14)* 求”信息系”当前借阅图书的读者人次数。

#15) 求出各个出版社图书的最高价格、最低价格和总册数。

#16) 分别找出各单位当前借阅图书的读者人数及所在单位。

17)* 找出当前至少借阅了2本图书(大于等于2本)的读者姓名及其所在单位。

18) 分别找出借书人次数多于1人次的单位及人次数。

19) 找出藏书中各个出版单位的名称、每个出版社的书籍的总册数(每种可能有多册)、书的价值总额。

20) 查询经济系是否还清所有图书。

如果已经还清，显示该系所有读者的姓名、所在单位和职称。

附录：建表语句创建图书管理库的图书、读者和借阅三个基本表的表结构：创建BOOK:（图书表）CREATE TABLE BOOK (BOOK_ID int,SORT VARCHAR(10),BOOK_NAME VARCHAR(50),WRITER VARCHAR(10),OUTPUT VARCHAR(50),PRICE int);创建READER:（读者表）CREATE TABLE READER (READER_ID int,COMPANY VARCHAR(10),NAME VARCHAR(10),SEX VARCHAR(2),GRADE VARCHAR(10),ADDR VARCHAR(50));创建BORROW:（借阅表）CREATE TABLE BORROW (READER_ID int,BOOK_ID int,BORROW_DATE datetime)插入数据:BOOK表:insert into BOOK values(,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into BOOK values(,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into BOOK values(,'TP3/12','数据库导论','王强','科学出版社',17.90); insert into BOOK values(,'TP5/10','计算机基础','李伟','高等教育出版社',18.00);insert into BOOK values(,'TP3/12','FoxBASE','张三','电子工业出版社',23.60);insert into BOOK values(,'TS7/21','高等数学','刘明','高等教育出版社',20.00);insert into BOOK values(,'TR9/12','线性代数','孙业','北京大学出版社',20.80);insert into BOOK values(,'TR7/90','大学英语','胡玲','清华大学出版社',12.50);insert into BOOK values(,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into BOOK values(,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);insert into BOOK values(,'TP4/14','FoxPro大全','周虹','科学出版社',32.70);insert into BOOK values(,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);insert into BOOK values(,'TP4/15','计算机网络','黄力钧','高等教育出版社',2 1.80);READER表:insert into reader values(111,'信息系','王维利','女','教授','1号楼424'); insert into reader values(112,'财会系','李立','男','副教授','2号楼316 ');insert into reader values(113,'经济系','张三','男','讲师','3号楼105'); insert into reader values(114,'信息系','周华发','男','讲师','1号楼316'); insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224 ');insert into reader values(116,'信息系','李明','男','副教授','1号楼318 ');insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214 ');insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216 ');insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318 ');insert into reader values(120,'国际贸易','李雪','男','副教授','4号楼506');insert into reader values(121,'国际贸易','李爽','女','讲师','4号楼510 ');insert into reader values(122,'国际贸易','王纯','女','讲师','4号楼512 ');insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202'); insert into reader values(124,'财会系','朱海','男','讲师','2号楼210'); insert into reader values(125,'财会系','马英明','男','副教授','2号楼212 ');BORROW表:insert into borrow values(112,,'3-19-2006');insert into borrow values(125,,'2-12-2006');insert into borrow values(111,,'8-21-2006');insert into borrow values(112,,'3-14-2006');insert into borrow values(114,,'10-21-2006');insert into borrow values(120,,'11-2-2006');insert into borrow values(120,,'10-18-2006');insert into borrow values(119,,'11-12-2006');insert into borrow values(112,,'10-23-2006');insert into borrow values(115,,'8-21-2006');insert into borrow values(118,,'9-10-2006');现有关系数据库如下：数据库名：图书借阅管理系统读者表(读者编号 char(6)，姓名，性别，年龄，单位，身份证号，职称)图书表(图书编号char(6)，图书名称，出版社，作者)借阅表(读者编号，图书编号，借阅时间)用SQL语言实现下列功能的sql语句代码。

sql练习与答案3

sql练习与答案3

31. 分数表scores设计如下：courseID(课程编号)studentID(学生编号)score(分数)另有一个学生信息表student,包含studentID,sname(学生姓名)。

已知并非所有学生都参加了courseID为0001的考试，现在查询所有参加0001号课程考试及格学生的学生姓名，下面正确的是（）。

（选择一项）AA. select sname from student where studentID in (select studentID from scores where courseID = 0001 and score>=60)B. select sname from student where studentID = (select studentID from scores where courseID = 0001 and score>=60)C. select sname from student where studentID not in (select studentID from scores where courseID = 0001 and score<=60)D. select sname from student where studentID exists (select studentID from scores where courseID = 0001 and score>=60)32. 在（）的列上更适合创建索引。

（选择两项）ADA. 需要对数据进行排序B. 具有默认值C. 频繁更改D. 频繁搜索33. SQL Server数据库有2种登录认证方式。

其中在（）方式下，需要客户端应用程序连接时提供登录时需要用户标识和密码。

CA. Windows身份认证。

B. 以超级用户身份登录。

C. SQL Server身份认证。