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

文档中心

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

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

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 intout表示输出，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<xdegree;

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 TransferSource Host: localhostSource Database: sqltestTarget Host: localhostTarget Database: sqltestDate: 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', '弹簧厂', '天津');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)

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

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道[巩固练习]

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签

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

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道[巩固练习]

相关问题

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签

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

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