庄周带你练习数据库语句复习常备之【JavaWeb阶段学习】
时间:2023-10-21 15:37:01
??(′`)收藏是作者创作的最大动力
??? 支持我:赞:赞 收藏 留言??
目录
?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)。让我们选择最好的查询方法来达到最大的优化效果。
-
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=utf;
– 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’,‘’,‘男’,‘辅助’)
-
理解:
效果:在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 INTOcoures
VALUES (‘3-245’, ‘操作系统’, ‘804’);
INSERT INTOcoures
VALUES (‘6-166’, ‘数据电路’, ‘856’);
INSERT INTOcoures
VALUES (‘9-888’, ‘高等数学’, ‘100’);
INSERT INTOscore
VALUES (‘201’, ‘103’, ‘3-245’, ‘86’);
INSERT INTOscore
VALUES (‘2010’, ‘101’, ‘6-166’, ‘85’);
INSERT INTOscore
VALUES (‘2011’, ‘107’, ‘6-106’, ‘79’);
INSERT INTOscore
VALUES (‘2012’, ‘108’, ‘6-166’, ‘81’);
INSERT INTOscore
VALUES (‘202’, ‘105’, ‘3-245’, ‘75’);
INSERT INTOscore
VALUES (‘203’, ‘109’, ‘3-245’, ‘68’);
INSERT INTOscore
VALUES (‘204’, ‘103’, ‘3-105’, ‘92’);
INSERT INTOscore
VALUES (‘205’, ‘105’, ‘3-105’, ‘88’);
INSERT INTOscore
VALUES (‘206’, ‘109’, ‘3-105’, ‘76’);
INSERT INTOscore
VALUES (‘207’, ‘101’, ‘3-105’, ‘64’);
INSERT INTOscore
VALUES (‘208’, ‘107’, ‘3-105’, ‘91’);
INSERT INTOscore
VALUES (‘209’, ‘108’, ‘3-105’, ‘78’);
INSERT INTOstudent
VALUES (‘101’, ‘李1’, ‘男’, ‘1976-02-20 00:00:00’, ‘95033’);
INSERT INTOstudent
VALUES (‘103’, ‘陆2’, ‘男’, ‘1974-06-03 00:00:00’, ‘95031’);
INSERT INTOstudent
VALUES (‘105’, ‘匡3’, ‘男’, ‘1975-10-02 00:00:00’, ‘95031’);
INSERT INTOstudent
VALUES (‘107’, ‘王4’, ‘女’, ‘1976-01-23 00:00:00’, ‘95033’);
INSERT INTOstudent
VALUES (‘108’, ‘曾5’, ‘男’, ‘1977-09-01 00:00:00’, ‘95033’);
INSERT INTOstudent
VALUES (‘109’, ‘王6’, ‘女’, ‘1975-02-10 00:00:00’, ‘95031’);
INSERT INTOteacher
VALUES (‘804’, ‘李7’, ‘男’, ‘1958-12-02 00:00:00’, ‘副教授’, ‘计算机系’);
INSERT INTOteacher
VALUES (‘825’, ‘王8’, ‘女’, ‘1972-05-05 00:00:00’, ‘助教’, ‘计算机系’);
INSERT INTOteacher
VALUES (‘831’, ‘刘9’, ‘女’, ‘1977-08-14 00:00:00’, ‘助教’, ‘电子工程系’);
INSERT INTOteacher
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', '弹簧厂', '天津'); 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)