投稿
  1. 首页
  2. mysql

庄周带你练习数据库语句复习常备之【JavaWeb阶段学习】

code • 2022-5-21 • mysql • 阅读 38

庄周带你练习数据库语句复习常备之【JavaWeb阶段学习】,第1张

🥂(❁´◡`❁)您的点赞👍➕评论📝➕收藏⭐是作者创作的最大动力🤞

💖📕🎉🔥 支持我：点赞👍+收藏⭐️+留言📝

目录

 1、DDL、DML、DCL、DQL

1.1、DDL（data definition language）数据库模式定义语言：

1.2、DML（data manipulation language） 数据库 *** 控语言：

1.3、DCL（Data Control Language）数据库控制语言：

1.4、DQL（Data Query Language SELECT ）数据查询语言：select语句。

2、Mysql_SQL语句

2.1、查询

2.2、新增

2.3、修改（更新）

2.4、删除

2.5、创建视图

2.6、创建存储过程

2.7、外键

3、案例讲解45道SQL练习[学完这些基本增删改查不成问题]

3.1、建表语句及数据

3.2、练习

4、案例讲解SQL练习16道[巩固练习]

 1、DDL、DML、DCL、DQL

DDL 数据库模式定义语言 create

DML 数据库 *** 控语言 insert update等等

DCL 数据库控制语言 grant remove等等

DQL 数据库查询语言 query 等等

1.1、DDL（data definition language）数据库模式定义语言：

数据定义语言，用于定义和管理 SQL 数据库中的所有对象的语言。

DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

1、CREATE - to create objects in the database 创建

2、ALTER - alters the structure of the database 修改

3、DROP - delete objects from the database 删除

4、TRUNCATE - 删除表中的所有记录，包括删除记录所分配的所有空间

eg:

 TRUNCATE TABLE [Table Name]

5、COMMENT - add comments to the data dictionary 注释

6、GRANT - gives user's access privileges to database 授权

7、REVOKE - withdraw access privileges given with the GRANT command 收回已经授予的权限

1.2、DML（data manipulation language） 数据库 *** 控语言：

数据 *** 作语言，SQL中处理数据等 *** 作统称为数据 *** 纵语言

它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行 *** 作的语言。

1、SELECT - retrieve data from the a database 查询

2、INSERT - insert data into a table 添加

3、UPDATE - updates existing data within a table 更新

4、DELETE - deletes all records from a table, the space for the records remain 删除

5、CALL - 调用PL/SQL或Java子程序

6、EXPLAIN PLAN - 解释数据访问路径

Oracle RDBMS执行每一条SQL语句，都必须经过Oracle优化器的评估。所以，了解优化器是如何选择(搜索)路径以及索引是如何被使用的，对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。

7、LOCK TABLE - control concurrency 锁，用于控制并发

1.3、DCL（Data Control Language）数据库控制语言：

数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库 *** 纵事务发生的时间及效果，对数据库实行监视等。

是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有 sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。

1、COMMIT - save work done 提交

2、SAVEPOINT - identify a point in a transaction to which you can later roll back 保存点

3、ROLLBACK - restore database to original since the last COMMIT 回滚

4、SET TRANSACTION - Change transaction options like what rollback segment to use 设置当前事务的特性，它对后面的事务没有影响

1.4、DQL（Data Query Language SELECT ）数据查询语言：select语句。
select具体用法 
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
数据查询语言DQL基本结构是由 SELECT 子句，FROM 子句，WHERE 子句组成的查询块： 
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>
2、Mysql_SQL语句

注：所有 *** 作都必须在英文状态下

建表 
-- ----------------------------
-- Table structure for wanjia
-- ----------------------------
CREATE TABLE `wanjia` (
  `w_id` varchar(50) NOT NULL,
  `w_name` varchar(100) default NULL,
  `w_sex` varchar(50) default NULL,
  `w_like` varchar(50) default NULL,
  PRIMARY KEY  (`w_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for yingxiong
-- ----------------------------
CREATE TABLE `yingxiong` (
  `p_id` varchar(50) NOT NULL,
  `P_name` varchar(100) default NULL,
  `p_sex` varchar(50) default NULL,
  `p_age` int(11) default NULL,
  `p_weizhi` varchar(100) default NULL,
  `p_leader` varchar(50) default NULL,
  `w_id` varchar(50) default NULL,
  PRIMARY KEY  (`p_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.1、查询

1、全查

语句： 
select * from yingXiong
理解： 
select表示查询
*表示默认缺省(全部字段)
from表示来自
yingXiong表示表名称
注意：select from在数据库查询中都为蓝色字体，表示当前为数据库关键字(保留字)
注意：每一个单词之间只用空格进行分离
结构： 
select 字段 from 表名称

2、按字段查询

语句： 
select p_id,p_name from yingXiong
理解： 
p_id/p_name表示要查询的字段
注意：以字段查询时可以单一字段也可以多个字段
注意：以多个字段内进行查询时，字段与字段之间用逗号(,)间隔

3、按条件查询

语句： 
select * from yingXiong where p_sex='女'
理解： 
where关键字表示条件
p_sex=’值’
注意：字段必须是当前表中存在的字段，值必须是当前字段存在的值，当值不存在时查询结果为空
注意：如果SQL语句中存在条件关键字(where)则必须给条件
注意：对于字符串来说，必须加单引号(‘’),对于数字来说可以不加，但推荐加单引号
4、运算符查询 
语句同条件查询  < > <= >= <>（！=）

5、命名别名

语句：

select p_age as p_age1 from yingXiong
理解： 
作用：将查询的字段p_age命名为p_age1
关键字as表示命名
注意：as可以省略

6、多条件查询

语句：（and） 
select * from yingXiong where p_age='23' and p_weizhi='辅助'
理解： 
作用：查询到同时满足p_age='23' 和 p_weizhi='辅助'条件的数据
关键字：and表示和(与)，两边同时成立
语句：（or） 
select * from yingXiong where p_age='23' or p_weizhi='辅助'
理解： 
作用：查询满足p_age='23' 或 p_weizhi='辅助'条件的数据
关键字：or表示或，两边只要有一个满足就可以

7、以集合方式查询

语句： 
select * from yingXiong where p_age in (21,34)
理解： 
作用：将p_age的值为21或者34的数据取出来
关键字：in表示集合，in后面跟小括号()，在括号中填入集合元素
注意：在小括号的元素中，每一个元素需要用逗号(,)隔开
相当于：select * from yingXiong where p_age='21' or p_age = '34'

8、以区间方式查询

语句： 
select * from yingXiong where p_age between ‘18’ and ‘40’
理解： 
作用：查询p_age值在18到40之间数据，包含18和40
关键字：between...and...表示在...和...之间，包含...
注意：and关键字前为小，后为大
相当于：select * from yingXiong where p_age>='18' and p_age<='40'
9、以主键做单一查询 
select * from yingXiong where p_id='p001'

10、模糊查询

语句： 
select * from yingXiong where p_name like '%庄周%'

 理解： 
作用：查询p_name值中含有啊的数据
关键字：like表示模糊，后面跟要模糊的值
注意：如果将要虎摸的值中去掉%，则当前like相当于=
注意：如果去掉要模糊的值前面的%，则结果为以“啊”开头的数据，如果去掉要模糊的值后面的%，则结果为以“啊”结尾的数据

11、分页查询

语句： 
select * from yingXiong limit 0,3
理解： 
作用：查询三条数据，从第一条开始
关键字：limit表示分页，后面跟数字，如果为两个数字，则用逗号隔开，第一个数字表示从第几条开始查，
          第二个数字表示要查询的条数，如果为一个数字，则表示从第一条开始查询，条数为指定数字
注意：当前语句中的数字不能加单引号
注意：分页查询时，第一条数据序号为0

12、排序查询

语句： 
select * from yingXiong order by p_age
理解： 
作用：将查询到的结果以p_age的值进行排序，默认为升序
关键字：order by 表示以什么（哪一个字段）进行排序，order by后跟字段
升序：asc（与默认方式相同）
降序：desc

13、子条件查询

语句： 
select * from yingXiong where p_id=(select p_leader from yingXiong where p_name='庄周')
理解： 
作用：查询庄周的大哥的全部信息
注意：小括号中包裹的为子语句，将子语句的结果作为主语句的条件值做查询
注意：当前主语句条件为=，则子语句查询到的结果必须为一个
如果子语句查询到的结果为多行，则主语句的条件应该为集合（in）

14、空值查询方式

1、没有的空：select * from yingxiong where p_sex=’’

2、值为null：select * from yingxiong where p_sex=’null’

3、默认为空(Null)：select * from yingxiong where p_age is null

--------------------聚合函数开始------------------------------------

15、求平均值

语句： 
select avg(p_age) avg_pAge from yingxiong
理解： 
avg关键字表示平均，后面跟小括号，里面为要平均的字段
注意：avg只能对数字进行求平均值，与类型无关
注意：在求平均值时，如果字段值含有不为数字的字符，则从遇到第一个不是数字的字符开始不记算

16、求和

语句： 
select sum(p_leader)  from yingxiong
理解： 
Sum关键字表示求和后面跟小括号，小括号中为要求和的字段

17、求最大值

语句： 
select max(p_leader)  from yingxiong
理解： 
max关键字表示求最大值，后面跟小括号，小括号中为要求最大值的字段

18、求最小值

语句： 
select min(p_leader)  from yingxiong
理解： 
min关键字表示求最小值，后面跟小括号，小括号中为要求和的字段

19、求总数

语句： 
select count(p_leader)  from yingxiong
理解： 
count关键字表示求总数，后面跟小括号，小括号中为要求总数的字段

-------------------聚合函数结束--------------------------

20、分组函数

语句： 
select p_sex from yingxiong group by p_sex
理解： 
关键字：group by表示通过什么来分组，后面跟要分组的字段

21、分组函数加条件

语句： 
select p_weizhi from yingxiong group by p_weizhi having p_weizhi != '辅助'
理解： 
注意：分组函数专用条件关键字having
注意：having和where的区别，having从查询的字段中进行筛选，where从表中进行筛选

22、多表联合查询

语句： 
select * from yingxiong yx,wanjia wj where yx.w_id=wj.w_id
理解： 
注意：当有多张表是。表与表之间用逗号间隔
注意：多表联合查询需要条件过滤，如果不进行过滤，则得到的数据为两表数据的乘积，此种方式称之为笛卡尔乘积；
条件过滤的方式：一表中的字段 条件 另一表中的字段

23、左连接查询

语句： 
select * from yingxiong yx left join wanjia wj on yx.w_id =wj.w_id
理解： 
关键字：left join... on ...,left join表示左连接，on表示条件
结果：以左表数据为准（数据全要），右表数据匹配，如有未匹配项，则以空填充

24、右连接查询

语句： 
select * from yingxiong yx right join wanjia wj on yx.w_id =wj.w_id;
理解： 
关键字：right join... on ...,right join表示右连接，on表示条件
结果：以右表数据为准（数据全要），左表数据匹配，如有未匹配项，则以空填充

25、内连接查询

语句： 
select * from yingxiong yx inner join wanjia wj on yx.w_id =wj.w_id;
理解： 
关键字：inner join... on ...,inner join表示内连接，on表示条件
结果：以两表数据为准，互相匹配，如有未匹配项，则摒弃
注意：on为两表联查的专用条件关键字
2.2、新增 语句： 
insert into wanjia (w_id,w_name,w_sex,w_like) values ('w008','ss273','男','辅助')
理解： 
效果：在wanjia表中插入一条新数据
框架：insert into 表名称 (字段1，字段2，...) values (‘字段1的值’,’字段2的值’...)
注意：字段和值的位置和数量必须一致
注意：不允许为空的字段新增时必须指定值
2.3、修改（更新） 语句： 
update wanjia set w_name='庄周1',w_sex='男' where w_id='w010'
理解： 
效果：将指定的一条数据修改为想要的结果
框架：update 表名称 set 字段1=’值’,字段2=’值’... where 主键=’值’
注意：修改语句必须使用条件，如果不使用条件则结果为整张表的数据被修改
2.4、删除 语句： 
delete from wanjia where w_id='w010'
理解： 
效果：删除指定数据
框架：delete from 表名称 where 主键=’值’
注意：删除语句必须使用条件，强烈建议使用主键进行删除
2.5、创建视图 语句： 
create view view3 as select * from yingXiong
理解： 
Create表示创建 	view表示视图	view3表示视图名称
2.6、创建存储过程 语句： 
DELIMITER ;; CREATE PROCEDURE pro1(out ss int)
 begin select count(*) into ss from yingXiong; end; DELIMITER ;
理解： 
DELIMITER定义结束标志
PROCEDURE 表示存储过程
pro1当前存储过程的名称
begin表示存储过程开始
end表示存储过程结束
out ss int		out表示输出，ss表示变量名称，int表示变量类型
 into ss 		into表示将当前值赋值给后面跟的变量
ss在参数位置定义的变量
存储过程的调用：
call pro1(@ss);
select @ss;
注意：存储过程传递变量时需要加上@
2.7、外键

set null当主表数据发生删除或者修改主键时，从表外键设置为默认空

Cascade级联，当删除主表数据时，从表外键值为当前数据的主键的数据一起删除，修改主表主键时，从表原来引用当前主键的数据全部一起修改为当前主表主键值

No action没有动作，主表数据改变不影响从表数据

Restrict 限制,约束,制约，当从表中有主表的主键引用，则主表数据不能删除或修改

3、案例讲解45道SQL练习[学完这些基本增删改查不成问题] 3.1、建表语句及数据 建表 
  SET FOREIGN_KEY_CHECKS=0;
  -- ----------------------------
  -- Table structure for coures
  -- ----------------------------
  CREATE TABLE `coures` (
    `c_id` varchar(50) NOT NULL,
    `c_name` varchar(100) default NULL,
    `t_id` varchar(100) default NULL,
    PRIMARY KEY  (`c_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  -- ----------------------------
  -- Table structure for score
  -- ----------------------------
  CREATE TABLE `score` (
    `s_no` varchar(50) NOT NULL,
    `s_id` varchar(30) default NULL,
    `c_id` varchar(50) default NULL,
    `degree` int(10) default NULL,
    PRIMARY KEY  (`s_no`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  -- ----------------------------
  -- Table structure for student
  -- ----------------------------
  CREATE TABLE `student` (
    `s_id` varchar(50) NOT NULL,
    `s_name` varchar(100) default NULL,
    `s_sex` varchar(50) default NULL,
    `s_birthday` datetime default NULL,
    `s_class` varchar(50) default NULL,
    PRIMARY KEY  (`s_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  -- ----------------------------
  -- Table structure for teacher
  -- ----------------------------
  CREATE TABLE `teacher` (
    `t_id` varchar(30) NOT NULL,
    `t_name` varchar(40) default NULL,
    `t_sex` varchar(20) default NULL,
    `t_birthday` datetime default NULL,
    `prof` varchar(60) default NULL,
    `depart` varchar(100) default NULL,
    PRIMARY KEY  (`t_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据 
  -- ----------------------------
  -- Records 
  -- ----------------------------
  INSERT INTO `coures` VALUES ('3-105', '计算机导论', '825');
  INSERT INTO `coures` VALUES ('3-245', ' *** 作系统', '804');
  INSERT INTO `coures` VALUES ('6-166', '数据电路', '856');
  INSERT INTO `coures` VALUES ('9-888', '高等数学', '100');
  INSERT INTO `score` VALUES ('201', '103', '3-245', '86');
  INSERT INTO `score` VALUES ('2010', '101', '6-166', '85');
  INSERT INTO `score` VALUES ('2011', '107', '6-106', '79');
  INSERT INTO `score` VALUES ('2012', '108', '6-166', '81');
  INSERT INTO `score` VALUES ('202', '105', '3-245', '75');
  INSERT INTO `score` VALUES ('203', '109', '3-245', '68');
  INSERT INTO `score` VALUES ('204', '103', '3-105', '92');
  INSERT INTO `score` VALUES ('205', '105', '3-105', '88');
  INSERT INTO `score` VALUES ('206', '109', '3-105', '76');
  INSERT INTO `score` VALUES ('207', '101', '3-105', '64');
  INSERT INTO `score` VALUES ('208', '107', '3-105', '91');
  INSERT INTO `score` VALUES ('209', '108', '3-105', '78');
  INSERT INTO `student` VALUES ('101', '李1', '男', '1976-02-20 00:00:00', '95033');
  INSERT INTO `student` VALUES ('103', '陆2', '男', '1974-06-03 00:00:00', '95031');
  INSERT INTO `student` VALUES ('105', '匡3', '男', '1975-10-02 00:00:00', '95031');
  INSERT INTO `student` VALUES ('107', '王4', '女', '1976-01-23 00:00:00', '95033');
  INSERT INTO `student` VALUES ('108', '曾5', '男', '1977-09-01 00:00:00', '95033');
  INSERT INTO `student` VALUES ('109', '王6', '女', '1975-02-10 00:00:00', '95031');
  INSERT INTO `teacher` VALUES ('804', '李7', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
  INSERT INTO `teacher` VALUES ('825', '王8', '女', '1972-05-05 00:00:00', '助教', '计算机系');
  INSERT INTO `teacher` VALUES ('831', '刘9', '女', '1977-08-14 00:00:00', '助教', '电子工程系');
  INSERT INTO `teacher` VALUES ('856', '张十', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');
3.2、练习 1、 查询student表中的所有记录的s_name s_sex和s_class列。 
select s_name,s_sex,s_class from student;
2、 查询教师所有的单位即不重复的depart列。 
select depart from teacher group by depart;
select distinct depart from teacher;  //( distinct 与 group by depart 有什么区别)

select depart from teacher group by depart having depart='计算机系';
select distinct(depart) from teacher where depart='计算机系';
3、 查询student表的所有记录。 
select * from student;
4、 查询score表中成绩在60到80之间的所有记录。 
select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree < 80;
5、 查询score表中成绩为85，86或88的记录。 
select * from score where degree in (85,86,88);
select * from score where degree = 85 or degree = 86 or degree = 88;
6、 查询student表中“95031”班或性别为“女”的同学记录。 
select * from student where s_sex = '女' or s_class = '95031';
7、 以s_class降序查询student表的所有记录。 
select * from student order by s_class desc;
8、 以c_id升序、degree降序查询score表的所有记录。 
select * from score order by c_id asc;

select * from score order by degree desc;
9、 查询“95031”班的学生人数。 
select count(*) from student where s_class = '95031';
select sum(s_class = '95031') from student;
10、查询score表中的最高分的学生学号和课程号。 
select s_id,c_id from score where degree = (select max(degree) from score);
11、查询‘3-105’号课程的平均分。 
select avg(degree) from score where c_id = '3-105';
12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。 
select avg(degree) from score where c_id like '3%' group by c_id having count(c_id) > 4;
select avg(degree) from score group by c_id having count(c_id)>=5 and c_id like '3%';
13、查询最低分大于70，最高分小于90的s_id列。 
select s_id from score where degree > 70 and degree < 90;
select s_id from score where degree between 70 and 90;

14、查询所有学生的s_name、c_id和degree列。

select s_name,c_id,degree from student stu,score sco where stu.s_id = sco.s_id;
15、查询所有学生的s_id、c_name和degree列。 
select s_id,c_name,degree from score sco,coures cou where sco.c_id = cou.c_id;
16、查询所有学生的s_name、c_name和degree列。 
select s_name,c_name,degree from student,coures,score where 
			student.s_id = score.s_id and score.c_id = coures.c_id;
select s_name,c_name,degree from student join score on student.s_id = score.s_id 
					join coures on score.c_id = coures.c_id;
17、查询“95033”班所选课程的平均分。 
select avg(degree) from score where s_id in (select s_id from student where s_class = '95033');
select avg(degree) from student,score  where student.s_id=score.s_id 
						group by s_class having s_class='95033'
18、假设使用如下命令建立了一个grade表： 
create table grade(low   int(3),
            upp   int(3),
            rank   varchar(50));
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(0,59,’E’);
commit;

现查询所有同学的s_id、c_id和rank列。

select s_id,c_id,rank from score,grade where score.degree between grade.low and grade.upp;
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 
select * from student,score where student.s_id = score.s_id and score.c_id = '3-105' 
	and degree > (select degree from score where score.s_id = '109' and score.c_id = '3-105');
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。 
select * from score where degree not in (select max(degree) from score group by s_id) 
		and s_id in (select s_id from score group by s_id having count(c_id)>1);
  	
select t1.s_id,t2.c_id,t2.degree from score as t2,(select s_id,max(degree) as maxdegree  
    from score group by s_id having count(s_id) > 1 ) as t1 where t1.s_id=t2.s_id 
    					and t2.degree < t1.maxdegree;
22、查询和学号为108的同学同年出生的所有学生的s_id、s_name和s_birthday列。 
mysql> select s_id,s_name,s_birthday from student where s_birthday like 
			(select s_birthday from student where s_id = '108');
  +------+--------+---------------------+
  | s_id | s_name | s_birthday          |
  +------+--------+---------------------+
  | 108  | 曾5    | 1977-09-01 00:00:00 |
  +------+--------+---------------------+
  
select s_id,s_name,s_birthday from student where s_birthday like '1977%';
23、查询"张旭"教师任课的学生成绩。 
select degree from score where c_id = (select c_id from coures where t_id = 
			(select t_id from teacher where t_name = '张十'));
  		
select degree from score,coures,teacher where score.c_id = coures.c_id and 
			coures.t_id = teacher.t_id and teacher.t_name = '张十';
24、查询选修某课程的同学人数多于5人的教师姓名。 
select t_name from teacher,coures,score where teacher.t_id=coures.t_id and 
	 coures.c_id=score.c_id group by score.c_id having count(score.c_id)>5;
   
select t_name from teacher where t_id in (select t_id from coures where c_id in 
		  (select c_id from score group by c_id having count(c_id) > 5));
25、查询95033班和95031班全体学生的记录。 
select * from student,score,coures,teacher where student.s_class in ('95033','95031') 
  and student.s_id = score.s_id and score.c_id = coures.c_id and coures.t_id = teacher.t_id;
26、查询存在有85分以上成绩的课程c_id. 
select c_id from score where degree > 85;
27、查询出“计算机系“教师所教课程的成绩表。 
select student.s_name,score.degree from coures,student,score,teacher 
	where  teacher.depart = '计算机系' and coures.c_id = score.c_id 
  and score.s_id = student.s_id and coures.t_id = teacher.t_id;

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

select t_name,prof from teacher where teacher.depart in ('计算机系','电子工程系')

select t_name,prof from teacher where (depart='计算机系' or depart='电子工程系') 
				and (prof in(select prof from teacher group by prof))
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的c_id、s_id和Degree,并按Degree从高到低次序排序。 
select c_id,s_id,degree from score where c_id = '3-105' and degree > 
        (select min(degree) from score where c_id = '3-245') order by degree desc;

select * from (select c_id,s_id,degree from score where c_id='3-105' and 
    degree > (select min(degree) from score where c_id='3-245') )as xin order by degree desc;
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的c_id、s_id和Degree. 
select c_id,s_id,degree from score where c_id = '3-105' and degree > 
			(select max(degree) from score where c_id = '3-245');
31、查询所有教师和同学的name、sex和birthday. 
select t_name,t_sex,t_birthday,s_name,s_sex,s_birthday from teacher,coures,score,student 
       where teacher.t_id=coures.t_id and coures.c_id=score.c_id and score.s_id=student.s_id;
32、查询所有“女”教师和“女”同学的name、sex和birthday. 
select t_name,t_sex,t_birthday from teacher where t_sex='女';
select s_name,s_sex,s_birthday from student where s_sex = '女';
33、查询成绩比该课程平均成绩低的同学的成绩表。 
select s_no,s_id,c_id,degree from score,(select avg(degree) as xdegree,c_id as xc_id 
	from score group by c_id ) as xin where score.c_id=xc_id and score.degree
34、查询所有任课教师的t_name和Depart. 
select t_name,depart from teacher where t_id in
 (select t_id from coures where c_id in 
    (select c_id from score));
35 查询所有未讲课的教师的t_name和Depart. 
select t_name,depart from teacher where t_id not in ( select t_id from coures 
					where c_id in (select c_id from score));
  				
select t_name,depart from teacher where t_name in (select t_name from teacher,coures,score 
		where teacher.t_id=coures.t_id and coures.c_id=score.c_id group by t_name);
36、查询至少有2名男生的班号。 
select s_class from student where s_sex = '男' group by s_class having count(*) >= 2;
select s_class from student group by s_class having count(s_sex='男') >= 2;
37、查询Student表中不姓“王”的同学记录。 
select * from student where s_name not like '王%';
  38、查询Student表中每个学生的姓名和年龄。 

select s_name,year(now()) - YEAR(s_birthday) as age from Student ;
select s_name,extract(year from now()) - (date_format(s_birthday,'%Y')) as age from student;

39、查询Student表中最大和最小的s_birthday日期值。 
select max(s_birthday)as minage,min(s_birthday) as maxage from student;

select (select s_birthday from student order by (date_format(s_birthday,'%Y')) asc,
  (date_format(s_birthday,'%m')) asc,(date_format(s_birthday,'%d')) asc limit 0,1 ) as 'maxage',
  (select s_birthday from student order by (date_format(s_birthday,'%Y')) desc,
  (date_format(s_birthday,'%m')) desc,(date_format(s_birthday,'%d')) desc limit 0,1 ); 
  as 'minage' from student limit 0,1
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。 
select  *  from student  order by s_class desc,s_birthday asc;

select * from student order by s_class desc,(date_format(s_birthday,'%Y'))asc,
            (date_format(s_birthday,'%m'))asc,(date_format(s_birthday,'%d'))asc;
41、查询“男”教师及其所上的课程。 
select t_name,c_name from teacher,coures where teacher.t_id =coures.t_id and teacher.t_sex = '男';
select t_name,c_name from (select * from teacher where t_sex = '男') as nan 
							left join coures on nan.t_id=coures.t_id;
select c_name,t_name from coures join teacher on coures.t_id = teacher.t_id where t_sex='男' ;
select c_name,t_name from coures where t_id in(select t_id from teacher where t_sex='男');
42、查询最高分同学的s_id、c_id和Degree列。 
select s_id,c_id,degree from score where degree = (select max(degree) from score);
43、查询和“李军”同性别的所有同学的s_name. 
select s_name from student where s_sex = (select s_sex from student where s_name = '李1');

select s_name from student where s_sex = (select s_sex from student where s_name = '李1') 
								and s_name <> '李1';
44、查询和“李军”同性别并同班的同学s_name. 
select s_name from student where s_sex = (select s_sex from student where s_name = '李1') 
    and s_class = (select s_class from student where s_name = '李1') and s_name <> '李1';

select s_name from student s1 where s_sex=(select s_sex from student s2 where s_name='李1' 
					and s1.s_class= s2.s_class) and s_name <> '李1';
45、查询所有选修“计算机导论”课程的“男”同学的成绩表 
select * from score where c_id = (select c_id from coures where c_name = '计算机导论') 
				and s_id in (select s_id from student where s_sex = '男');

select s_name,degree from coures,score,student where coures.c_id = score.c_id and 
	          score.s_id = student.s_id and c_name='计算机导论' and s_sex='男'
4、案例讲解SQL练习16道[巩固练习] 
/*
MySQL Data Transfer
Source Host: localhost
Source Database: sqltest
Target Host: localhost
Target Database: sqltest
Date: 2022/05/02 22:10:46
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for j
-- ----------------------------
CREATE TABLE `j` (
`jno` varchar(11) NOT NULL,
`jname` varchar(20) DEFAULT NULL,
`city` varchar(20) DEFAULT NULL,
PRIMARY KEY (`jno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for p
-- ----------------------------
CREATE TABLE `p` (
`pno` varchar(11) NOT NULL,
`pname` varchar(20) DEFAULT NULL,
`color` varchar(10) DEFAULT NULL,
`weight` varchar(10) DEFAULT NULL,
PRIMARY KEY (`pno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for s
-- ----------------------------
CREATE TABLE `s` (
`sno` varchar(11) NOT NULL,
`sname` varchar(20) DEFAULT NULL,
`status` varchar(5) DEFAULT NULL,
`city` varchar(50) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for spj
-- ----------------------------
CREATE TABLE `spj` (
`sno` varchar(11) DEFAULT NULL,
`pno` varchar(11) DEFAULT NULL,
`jno` varchar(11) DEFAULT NULL,
`qty` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `j` VALUES ('j1', '三建', '北京');
INSERT INTO `j` VALUES ('j2', '一汽', '长春');
INSERT INTO `j` VALUES ('j3', 'd簧厂', '天津');
INSERT INTO `j` VALUES ('j4', '造船厂', '天津');
INSERT INTO `j` VALUES ('j5', '机车厂', '唐山');
INSERT INTO `j` VALUES ('j6', '无线电厂', '常州');
INSERT INTO `j` VALUES ('j7', '半导体厂', '南京');
INSERT INTO `p` VALUES ('p1', '螺母', '红', '12');
INSERT INTO `p` VALUES ('p2', '螺栓', '绿', '17');
INSERT INTO `p` VALUES ('p3', '螺丝刀', '蓝', '14');
INSERT INTO `p` VALUES ('p4', '螺丝刀', '红', '14');
INSERT INTO `p` VALUES ('p5', '凸轮', '蓝', '40');
INSERT INTO `p` VALUES ('p6', '齿轮', '红', '30');
INSERT INTO `s` VALUES ('s1', '精益', '20', '天津');
INSERT INTO `s` VALUES ('s2', '盛锡', '10', '北京');
INSERT INTO `s` VALUES ('s3', '东方红', '30', '北京');
INSERT INTO `s` VALUES ('s4', '丰泰盛', '20', '天津');
INSERT INTO `s` VALUES ('s5', '为民', '30', '上海');
INSERT INTO `spj` VALUES ('s1', 'p1', 'j1', '200');
INSERT INTO `spj` VALUES ('s1', 'p1', 'j3', '100');
INSERT INTO `spj` VALUES ('s1', 'p1', 'j4', '700');
INSERT INTO `spj` VALUES ('s1', 'p2', 'j2', '100');
INSERT INTO `spj` VALUES ('s2', 'p3', 'j1', '400');
INSERT INTO `spj` VALUES ('s2', 'p3', 'j2', '200');
INSERT INTO `spj` VALUES ('s2', 'p3', 'j4', '500');
INSERT INTO `spj` VALUES ('s2', 'p3', 'j5', '400');
INSERT INTO `spj` VALUES ('s2', 'p5', 'j1', '400');
INSERT INTO `spj` VALUES ('s2', 'p5', 'j2', '100');
INSERT INTO `spj` VALUES ('s3', 'p1', 'j1', '200');
INSERT INTO `spj` VALUES ('s3', 'p3', 'j1', '200');
INSERT INTO `spj` VALUES ('s4', 'p5', 'j1', '100');
INSERT INTO `spj` VALUES ('s4', 'p6', 'j3', '300');
INSERT INTO `spj` VALUES ('s4', 'p6', 'j4', '200');
INSERT INTO `spj` VALUES ('s5', 'p2', 'j4', '100');
INSERT INTO `spj` VALUES ('s5', 'p3', 'j1', '200');
INSERT INTO `spj` VALUES ('s5', 'p6', 'j2', '200');
INSERT INTO `spj` VALUES ('s5', 'p6', 'j4', '500');

（1）求供应工程 J1 零件的供应商号码 SNO :

select distinct sno from spj where jno = 'j1'

（2）求供应工程 J1 零件 P1 的供应商号码 SNO

select distinct sno from spj where jno = 'j1' and pno = 'p1'

（3）求供应工程 J1 零件为红色的供应商号码 SNO :

select distinct sno from spj where jno = 'j1' and pno in (select pno from p where color = '红')

（4）求没有使用天津供应商生产的红色零件的工程号 JNO :

select distinct jno from spj where jno not in (select jno from spj,p,s where s.city = '天津' 
                                      and color = '红' and spj.sno = s.sno and p.pno = spj.pno)

（5）求至少用了供应商 S1 所供应的全部零件的工程号 JNO :

select distinct pno from spj where sno = 's1'
    
select jno from spj where pno = 'p1' and jno in (select jno from spj where pno = 'p2')

(1) 找出所有供应商的姓名和所在城市。

select sname,city from s

(2) 找出所有零件的名称、颜色、重量。

select pname,color,weight from p

(3) 找出使用供应商 S1 所供应零件的工程号码。

select distinct jno from spj where sno = 's1'

(4) 找出工程项目 J2 使用的各种零件的名称及其数量。

select pname,qty from p,spj where spj.jno = 'j2' and spj.pno = p.pno

(5) 找出上海厂商供应的所有零件号码。

select pno from spj where sno in (select sno from s where city = '上海')

(6) 找出使用上海产的零件的工程名称。

select jname from j where jno in 
(select jno from spj where sno in 
(select sno from s where city = '上海'))

select distinct jname from spj,s,j 
where s.sno = spj.sno 
and j.jno = spj.jno and s.city = '上海'

(7) 找出没有使用天津产的零件的工程号码。

select jno from spj where jno not in 
(select jno from spj where sno in
 (select sno from s where city = '天津'))

select jno from spj where jno not in 
(select jno from spj,s where s.sno = spj.sno and s.city ='天津')

(8) 把全部红色零件的颜色改成蓝色。

update p set color = '蓝' where color = '红'

(9) 由 S5 供给 J4 的零件 P6 改为由 S3 供应。

update spj set sno = 's3' where sno = 's5' and pno = 'p6' and jno = 'j4'

(10) 从供应商关系中删除供应商号是 S2 的记录,并从供应情况关系中删除相应的记录。

1>delete from s where sno = 's2'
2>delete from spj where sno = 's2'

(11) 请将 (S2 , J6 , P4 , 200) 插入供应情况关系。

insert into spj (sno,pno,jno,qty) values ('s2','j6','p4',200)

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/991056.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
code code 管理员组
0 0
上一篇 2022-05-21
下一篇 2022-05-21

发表评论

登录后才能评论

评论列表（0条）

保存