数据库SQL练习(一)

最新推荐文章于 2024-08-22 01:19:55 发布
最新推荐文章于 2024-08-22 01:19:55 发布
阅读量2.1k 收藏 34
点赞数 7
分类专栏： SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42873760/article/details/110127810
版权

好久没有复习SQL语句了，今天简单温习一下。

SQL语句执行顺序

语法顺序：select–from–where–group by–having–order by
执行顺序：from–where–group by–having–select–order by
(select的顺序与语法顺序稍微不一样)

from: 从哪个数据表检索数据
where: 过滤表中数据的条件
group by: 如何将上面过滤出的数据分组
having: 对上面group by已经分组的数据进行过滤的条件
select: 查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据

以下是祖传的数据练习样例，想学习的可以尝试写一写

简单的数据库使用情景

数据库对象

  1. 用SQL命令创建三张表：Sailors、Boats、Reserves

具体信息如下：
Sailors(sid char(10),sname char(20),rating int,age int),
其中sid是主关键字，sid表示水手的编号，sname表示水手的姓名，rating表示水手的级别，age表示水手的年龄。

CREATE TABLE `sailors` (
  `sid` char(10) NOT NULL,
  `sname` char(20) NOT NULL,
  `rating` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`sid`)
)

Boats(bid char(10),bname char(20),color char(10)),其中bid表示船的编号是主关键字，bname是船的名字，color是船的颜色

CREATE TABLE `boats` (
  `bid` char(10) NOT NULL,
  `bname` char(20) NOT NULL,
  `color` char(10) NOT NULL,
  PRIMARY KEY (`bid`)
)

Reserves(sid char(10),bid char(10),rdate date),
Reserves中记录水手在哪天定了那只船，其中sid是指向Sailors的外关键字，bid是指向Boats的外关键字,(sid,bid,rdate)合起来构成Reserves的主关键字。当sailors或Boats中的相关记录被删除时要求Reservers中的记录也被联带删除。

CREATE TABLE `reserves` (
  `sid` char(10) NOT NULL,
  `rdate` date NOT NULL,
  `bid` char(20) NOT NULL,
  PRIMARY KEY (`sid`,`rdate`,`bid`),
  KEY `bid_idx` (`bid`),
  CONSTRAINT `bid` FOREIGN KEY (`bid`) REFERENCES `boats` (`bid`) ON DELETE CASCADE,
  CONSTRAINT `sid` FOREIGN KEY (`sid`) REFERENCES `sailors` (`sid`) ON DELETE CASCADE
)
/*constraint 外键名 foreign key 外键字段 references 主表名(关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
RESTRICT（限制外表中的外键改动，默认值）
CASCADE（跟随外键改动,同删/同更新）
SET NULL（设空值）
SET DEFAULT（设默认值）
NO ACTION（无动作，默认的）
*/
  1. 在以上三表中插入数据，这里给出我的插入：

INSERT INTO 语句
INSERT INTO 语句用于向表格中插入新的行。

语法
INSERT INTO 表名称 VALUES (值1, 值2,…)

指定所要插入数据的列：
INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

//sailors 表
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("22","dustin",7,45);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("58","rusty",10,35);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("29","brustus",1,33);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("64","horatio",7,35);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("31","lubber",8,56);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("71","zorba",10,35);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("32","andy",8,26);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("74","horatio",9,35);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("85","art",3,26);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("96","frodo",3,26);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("86","john",1,17);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("98","tom",3,17);
INSERT INTO Sailors(sid ,sname,rating,age)
VALUES("95","bob",3,64);
//Boats 表
INSERT INTO Boats(bid,bname,color)
VALUES("101","A","red");
INSERT INTO Boats(bid,bname,color)
VALUES("105","E","red");
INSERT INTO Boats(bid,bname,color)
VALUES("102","B","green");
INSERT INTO Boats(bid,bname,color)
VALUES("106","F","blue");
INSERT INTO Boats(bid,bname,color)
VALUES("103","C","blue");
INSERT INTO Boats(bid,bname,color)
VALUES("107","G","green");
INSERT INTO Boats(bid,bname,color)
VALUES("104","D","white");
//Reserves 表
INSERT INTO Reserves(sid,bid,rdate)
VALUES("22","101","2010-01-08");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("22","101","2010-04-07");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("22","102","2010-01-09");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("22","105","2010-05-01");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("29","103","2010-01-09");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("22","106","2010-06-18");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("31","102","2010-02-11");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("22","107","2010-07-09");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("22","104","2010-03-08");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("31","106","2010-08-06");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("22","103","2010-03-10");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("32","105","2010-08-06");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("32","105","2010-03-11");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("29","104","2010-08-07");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("32","106","2010-03-18");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("64","103","2010-09-05");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("32","102","2010-03-19");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("58","102","2010-09-09");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("58","104","2010-03-20");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("64","104","2010-11-03");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("64","105","2010-03-20");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("64","105","2010-11-04");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("95","101","2010-04-02");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("31","106","2010-12-06");
INSERT INTO Reserves(sid,bid,rdate)
VALUES("85","102","2010-04-05");*/

一些简单CURD操作(Create Update Retrieve Delete）

  1. 查找定了103号船的水手
SELECT s.*,r.bid
FROM reserves r,sailors s
WHERE r.bid = 103 AND s.sid = r.sid
//简单使用WHERE过滤即可

在这里插入图片描述

  1. 查找定了红色船水手的姓名
SELECT DISTINCT S.sname 
FROM sailors S ,reserves R,boats B
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color="red" 
//注：distinct 意思是将两条完全相同的纪录变为一条

在这里插入图片描述

  1. 将年龄小于30的水手级别增加1.
    更新操作：
    UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

    注：where用来限制更新哪些行，set后面用,分隔可以更新多列
    varchar等字符串的拼接更新不用+，而用concat
    CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串

// update 
UPDATE SAILORS S
SET S.rating = S.rating + 1
WHERE S.age < 30 ;

/*
若遇到以下错误：
Error Code: 1175 You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column”
Workbench的安全设置不允许我们在没有修改主键的情况下修改table，
我们通过设置就可以进行处理。具体操作是在workbench的菜单栏点击“edit-->Preferences”,
然后会弹出对话框“workbench preferences“，
在“workbench”左边栏点击“SQL Editor”，
会看到最后一行是“Safe Updates……”是打钩的，
我们只要缺消打钩应用即可
*/
  1. 删除名字叫lubber的水手的定船信息.
    删除语法：
    DELETE FROM 表名称 WHERE 列名称 = 值
delete
from reserves
where sid = (select sid from sailors where sname ="lubber")
/*
from 后面接一个table
初学者可以使用delete时会类比select的写法，可能写成以下形式
delete
from reserves r,sailor r
where r.sid = s.sid and s.name = "lubbe";
这样是一个语法错误！
*/

一些较复杂CURD操作

  1. 查找定过船而没有定过红色船的水手信息

简单的思路：
先找到订过红色船的水手,然后在所有订了船的水手中取not除掉前者，剩余的即为所求。
（最外面的select是为了获取水手的信息，其实sid在第二个select已经都拿到了）

select *
from sailors S
where S.sid in( select R.sid
				from reserves R
				where R.sid not in(select R.sid
								from reserves R,boats B
								where R.bid = B.bid and B.color="red")  )

这里涉及到了in操作：
其过程为，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。由于其放弃了索引，改为笛卡尔积+扫描，在内、外表特别大的时候不推荐使用。
select * 打印多余信息，工程上尽量少用

在这里插入图片描述

  1. 查找定过所有船的水手姓名和编号

    exists：指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样，匹配上就将结果放入结果集中，其每次查询外表值是否在内表存在，会尝试使用内表的索引。
    相较于in,in会把B表放在内存里遍历比较，exists会查询数据库索引。通常来说exists性能更优，但是表小的时候in可能效果更佳，具体还是要看应用情况。

select S.sname,S.sid
from sailors S
where not exists(select *  /*不存在有船没被sid定*/
				 from boats B
                 where not exists(select distinct R.bid /*某个水手其订的所有船*/
				                  from reserves R
				                  where R.sid = S.sid and R.bid = B.bid) )

在这里插入图片描述

  1. 查找年龄最大的水手姓名和年龄
    MAX() 函数
    MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
select S.sname,S.age
from sailors S
where S.age = (select max(S.age)
              from sailors S)

在这里插入图片描述

  1. 统计水手表中每个级别组的平均年龄和级别组

    AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

select avg(S.age) as 平均年龄,S.rating as 级别组
from sailors S
group by S.rating
//avg这个对group以后的分组数据取平均值，as的别名依据需求设置

在这里插入图片描述

  1. 统计水手表中每个人数不少于2人的级别组中年满18岁水手的平均年龄和级别组

    分析：对组有要求用having 注意语句执行顺序，先where再group
    count() 函数返回匹配指定条件的行数。
    count()(column_name) 函数返回指定列的值的数目（NULL 不计入）：
    count(*)—包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，记录NULL的记录。
    count(DISTINCT column_name) 函数返回指定列的不同值的数目

select avg(S.age) as 平均年龄,S.rating as 级别组
from sailors S
where S.sid in(select S1.sid
              from sailors S1
              where S1.age > 18)
group by S.rating
having count(1) >= 2

在这里插入图片描述

  1. 统计水手表中人数最少的级别组及人数
    首先得找到分组的数据表用count()，然后用min()取人数最少
    from 中用temp做临时表
select temp.ranks,temp.people
from (select S.rating as ranks,count(*) as people
      from sailors S
      group by S.rating) as temp
	  where temp.people = (select min(temp.people)
						 from (select S.rating as ranks,count(*) as people
			                     from sailors S
								 group by S.rating) as temp);

在这里插入图片描述

补充一点：
当我们需要从数据源上 直接判断数据显示代表的含义的时候 ,就可以在SQL语句中使用 Case When这个函数;

MySQL 的 case when 的语法：
简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

搜索函数
CASE WHEN [expr] THEN [result1]…ELSE [default] END

简单例子：
查询男生、女生的人数；

select 
count(case when student.gender = "男" then 1 end) as "男生人数" ,
count(case when student.gender = "女" then 1 end) as "女生人数" 
from student

按各科平均成绩从低到高和及格率的百分数从高到低顺序；

select score.Course_id as 课程id , avg(score.number) as 平均成绩,
100 * sum(case when score.number >= 60 then 1 else 0 end) / count(*)  as 及格率
from score
group by 课程id
order by 平均成绩 asc,
及格率 desc;
Simplezk
关注
专栏目录
Oracle数据库SQL练习
08-14
"HR_POPUL.SQL"可能是用来填充HR数据库的脚本，包含了大量的INSERT语句，用户可以通过运行此脚本来快速生成一个具有样本数据的数据库，以供练习和测试SQL查询。"HR_CRE.SQL"则可能包含了创建HR数据库的DDL（数据定义...
oracle数据库sql练习题（含答案）
10-19
oracle数据库sql练习题（含答案）用于练习增删改查操作。
sqlite3的简单使用
ADC0809608的专栏
03-27 5445
实验代码 #include #include #include #include //sqlite3的回调函数 sqlite 每查到一条记录，就调用一次这个回调 // 传递的私有参数 一行有多少字段 字段值 字段名 int LoadMyInfo( void * para, int n_column, cha
数据库SQL查询作业二--水手船
Stay Hungry, Stay Foolish.
04-02 4830
一、创建三张表 ①Sailors(sid char(10),sname char(20),rating int,age int), 其中sid是主关键字，sid表示水手的编号，sname表示水手的姓名，rating表示水手的级别，age表示水手的年龄。 ②Boats(bid char(10),bname char(20),color char(10)), 其中bid表示船的编号是主关键字，bnam...
sql语句练习(自用)
最新发布
qq_30166465的博客
08-22 1289
2.使用子查询的第二个和第三个查询可能会较慢，尤其是当 student 表和 score 表很大时，因为对于每个学生记录，数据库都需要执行两次独立的子查询。3.EXISTS 和 NOT EXISTS 通常比 IN 和 NOT IN 更高效，因为它们在找到第一个匹配的行后就会停止搜索。左外连接：左表全部都有，左右两表能不能匹配是看 on 条件的左右两边 ，而不是看表在sql语句中的前后顺序。end as 列别名。1.使用 JOIN 的第一个查询通常在处理大量数据时更有效，因为数据库可以更高效地执行连接操作。
SQL练习 （水手 船 订购信息）
bijingrui的博客
03-22 5103
sailors 水手信息表boats 船信息表reserves 水手订船信息表 CREATE TABLE sailors( sid VARCHAR(20) PRIMARY KEY, sname VARCHAR(20), rating INT, age REAL ); CREATE TABLE boats( bid VARCHAR(20) PRIMARY KEY, bname V...
如何在MySQL workbench中建表查询建索引等一系列操作
lzy2001515的博客
10-14 2935
首先呢，我们的第一步就是要建立一个数据库，我给他取名为srb（createdatabasesrb）。建立好了之后我们就开始了表的创建（create table...）我这里呢是建了三个表（sailors，boats，reserves，这也是我们小白常取的名字了hhh） ...
sql语句
weixin_30550081的博客
01-10 250
1、查找只有一个水手预定的船（查找只有一个学生选修的课程）（Reserves表中字段为sid，bid，date） SELECT bid FROM Reserves R1 WHERE bid NOT IN (SELECT bid 　　　　　　　　　　FROM Reserves R2 　　　　　　　　　　WHERE R1.sid != R2.sid ) 基本逻辑是，从Res...
教学管理数据库SQL练习.doc
05-05
教学管理数据库 SQL 练习是数据库管理系统中一个重要的实践项目，旨在通过各种 SQL 语句的实践来加深对数据库管理系统的理解和掌握。该练习涵盖了数据库设计、数据查询、数据修改、数据删除等多方面的内容，为学习者...
数据库SQL练习1-CREATE/DROP/ALTER
01-21
首先，提到创建基本表，CREATE语句是基础的SQL命令之一，用于在数据库中创建新的表。创建表时，我们需要定义表名、各列的名称以及数据类型，并可为列指定约束条件，如主键约束、唯一约束、非空约束等，来保证数据的...
水手预定船的sql语句答案
12-15
【boat-sailor】 saliors(sid,sname,rating,age)，其中sid表示水手号，sname表示水手姓名，rating表示水手级别，age水 手年龄； boats(bid,bname,color) 其中bid表示船号，bname表示船名，color表示船的颜色； reserves(sid,bid,day) 其中sid表示水手号，bid表示船号，day表示水手预定船的日期 1. 找出预订了103船的水手的名字 2. 找出预订了红色船的所有水手的名字 3. 找出Lubber预定的船的所有颜色 4. 找出至少预订了一艘船的水手的名字 5. 找出预订了一艘红色船或者绿色船的水手名 6. 找出同时预订了红色船和绿色船的水手名 7. 找出预定了所有船的水手们的名字 8. 找出预定了所有名为Interlake船的水手的名字 9. 找出最老的水手的名字和年龄 10. 找出比等级为10的最老的水手的年龄还要大的水手的名字 11. 对于每个等级级别找出最年轻的水手的年龄 12. 对于至少有两个水手的等级级别，求出水手们的平均年龄 13. 对于至少有两个水手的等级，找出到了投票年龄（>18岁）的所有水手的平均年龄 14. 找出在所有的等级中水手的平均年龄是最低的那些等级
水手预定船的SQL实验答案
10-29
用Transact－SQL完成下列操作： 1. 找出预订了103船的水手的名字 2. 找出预订了红色船的所有水手的名字 3. 找出Lubber预定的船的所有颜色 4. 找出至少预订了一艘船的水手的名字 5. 找出预订了一艘红色船或者绿色船的水手名 6. 找出同时预订了红色船和绿色船的水手名 7. 找出预定了所有船的水手们的名字 8. 找出预定了所有名为Interlake船的水手的名字 9. 找出最老的水手的名字和年龄 10. 找出比等级为10的最老的水手的年龄还要大的水手的名字 11. 对于每个等级级别找出最年轻的水手的年龄 12. 对于至少有两个水手的等级级别，求出水手们的平均年龄 13. 对于至少有两个水手的等级，找出到了投票年龄（至少有18岁）的所有水手的平均年龄 14. 找出在所有的等级中水手的平均年龄是最低的那些等级 15. 插入95号水手，于04-03-24 预定103号船的信息 16. 将101号船颜色改为‘黄色’ 17. 从水手表中删除95号水手 答案TXT文件里
mysql 查询练习题
12-18
create table sailors( sid char(10) primary key, sname char(20), rating int, age int); create table boats( bid char(10) primary key, bname char(20), color char(10)); create table reserves( sid char(10) , bid char(10) , rdate date, primary key(sid,bid,rdate), foreign key (sid) references sailors(sid) on delete cascade, foreign key (bid) references boats(bid) on delete cascade); insert into sailors(sid,sname,rating,age) values("22","dustin",7,45) ("29","brustus",1,33), ("31","lubber",8,56), ("32","andy",8,26), ("58","rusty",10,35), ("64","horatio",7,35), ("71","zorba",10,35), ("74","horatio",9,35), ("85","art",3,26), ("86","john",1,17), ("95","bob",3,64), ("96","frodo",3,26), ("98","tom",3,17); insert into boats(bid,bname,color) values("101","A","red"), ("102","B","green"), ("103","C","blue"), ("104","D","white") ("105","E","red"), ("106","F","blue"), ("107","G","green"); insert into reserves(sid,bid,rdata) values("22","101","2010-01-08"), ("22","102","2010-01-09"), ("29","103","2010-01-09"), ("31","102","2010-02-11"), ("22","104","2010-03-08"), ("22","103","2010-03-10"), ("32","105","2010-03-11"), ("32","106","2010-03-18"), ("32","102","2010-03-19"), ("58","104","2010-03-20"), ("64","105","2010-03-20"), ("95","101","2010-04-02"), ("85","102","2010-04-05"), ("22","101","2010-04-07"), ("22","105","2010-05-01"), ("22","106","2010-06-18"), ("22","107","2010-07-09"), ("31","106","2010-08-06"), ("32","105","2010-08-06"), ("29","104","2010-08-07"), ("64","103","2010-09-05"), ("58","102","2010-09-09"), ("64","104","2010-11-03"), ("64","105","2010-11-04"), ("31","106","2010-12-0
mysql练习题
03-02
对于初学mysql，可以练习上面的题目，有助于增加mysql的理解
Mysql-SQL查询(水手预订船案例)
happyyouli's blog
04-14 3173
学习视频：东南大学-徐立臻-数据库原理 drop database if exists navigator; # 创建数据库 create schema navigator; use navigator; # 水手表 create table saliors ( sid int PRIMARY KEY, sname VARCHAR ( 20 ), rating int, age fl...
【数据库】二、SQLite3数据库连接查询（c++篇）
幸福之家的博客
06-28 4148
详细介绍了SQLite的数据库连接查询操作，并附带部分常用函数详解；
数据库笔记： SQL
qq_40206371的博客
07-01 904
1 数据库语言 DBMS提供操作命令和语言，使用户能够对数据库进行各式各样的操作，例如查询、增、删、改数据，定义、修改数据模式等 ——— >这就构成了用户和数据库的接口。 DBMS所提供的语言一般局限于对数据库的操作，有别于计算完备的程序设计语言，称为数据库语言（database language）。 1.1 数据库语言分类 数据库语言一般分为这几种 1.1.1 形式化查询语言（Query Language ） 本章的重点SQL就是这一类语言，它有严格的语法和文法 1.1.2表格查询.
SQL
weixin_33816946的博客
08-25 315
###basic sql query select (distinct,用于去重) target-list from ralation-list where qulification target-list 目标 ralation-list 表 qualifica 条件 参考关系验算部分 复制代码先排序后去重 表连接： 1.做笛卡尔乘积 2.筛选不符...
oracle数据库sql练习
06-02
Oracle数据库SQL练习可以通过以下步骤进行： 1. 熟悉Oracle SQL语法：首先需要掌握Oracle SQL语法，包括SELECT、INSERT、UPDATE、DELETE、JOIN、GROUP BY、ORDER BY等语句。 2. 学习Oracle数据库基本操作：对...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值