2021年09月23日 SQL专项练习

发表于 分类于 ， ， Waline： 本文字数： 8.4k 阅读时长 ≈ 8 分钟

考点1:数据操纵语言 SQL语言分类

SQL语言共分为三大类（亦有说法分为四大类），那么不属于数据操纵语言的有（）

  • A update
  • B grant
  • C delete
  • D insert
显示答案/隐藏答案正确答案: B

数据操纵语言

  • 数据查询语言（DQL）：是由SELECT子句，FROM子句，WHERE子句组成的查询块
  • 数据操纵语言（DML）:SELECT(查询)INSERT(插入)UPDATE(更新)DELETE(删除）
  • 数据定义语言（DDL）：CREATE(创建数据库或表或索引）ALTER(修改表或者数据库）DROP(删除表或索引）
  • 数据控制语言（DCL）：GRANT(赋予用户权限）REVOKE(收回权限）DENY(禁止权限)
  • 事务控制语言（TCL）：SAVEPOINT (设置保存点）ROLLBACK (回滚)COMMIT(提交)

考点2:多表联查

设有图书管理数据库：
图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2))
读者(借书证号C(4),单位C(8),姓名C(6),性别C(2),职称C(6),地址C(20))
借阅(借书证号C(4),总编号C(6),借书日期D(8))
对于图书管理数据库，查询0001号借书证的读者姓名和所借图书的书名。

SQL语句正确的是______。
SELECT 姓名,书名 FROM 借阅,图书,读者 WHERE;
借阅.借书证号=”0001” AND;

  • A 
    1
    2
    图书.分类号=借阅.分类号 AND;
    读者.借书证号=借阅.借书证号;
  • B
    1
    2
    图书.总编号=借阅.总编号 AND;
    读者.借书证号=借阅.借书证号;
  • C 
    1
    2
    读者.总编号=借阅.总编号 AND;
    读者.借书证号=借阅.借书证号;
  • D 
    1
    2
    图书.总编号=借阅.总编号 AND;
    读者.书名=借阅.书名;
显示答案/隐藏答案正确答案: B

考点3:单表连接查询 双重否定

一张学生成绩表score，部分内容如下：

1
2
3
4
name       course     grade
张三        操作系统      67
张三        数据结构      86
李四        软件工程      89;

用一条SQL 语句查询出每门课都大于80 分的学生姓名，SQL语句实现正确的是：（ ）

  • A Select distinct name from score where name not in(Select name from score where grade <= 80);
  • B Select distinct name from score where name in(Select name from score where grade <= 80);
  • C Select name from score where name not in(Select name from score where grade <= 80);
  • D Select name from score where name in(Select name from score where grade <= 80);
显示答案/隐藏答案正确答案: A

不存在 小于八十分的课=所有的 课大于八十分
遇到都，所有，需要留意，看看能不能转换为双重否定。

每门课都大于80=不存在一门课 小于等于80

考点4:group by-having子句

SQL语句中与Having子句同时使用的语句是？（）

  • A Group By
  • B 联盟链
  • C left Join
  • D Where
显示答案/隐藏答案正确答案: A

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

考点5:select into语句

某打车公司将驾驶里程（drivedistanced）超过5000里的司机信息转移到一张称为seniordrivers 的表中,他们的详细情况被记录在表drivers 中，正确的sql为（）

  • A 
    1
    2
    insert into seniordrivers
    drivedistanced>=5000 from drivers where;
  • B 
    1
    insert seniordrivers (drivedistanced) values from drivers where drivedistanced>=5000;
  • C 
    1
    2
    insert into seniordrivers
    (drivedistanced)values>=5000 from drivers where;
  • D 
    1
    select * into seniordrivers from drivers where drivedistanced >=5000;
显示答案/隐藏答案正确答案: D

insert into 要求目标表必须存在，select into 如果不存最目标表的话会自动创建

SQL INSERT INTO SELECT 语句

https://www.runoob.com/sql/sql-insert-into-select.html

通过SQL，您可以从一个表复制信息到另一个表。

INSERT INTO SELECT语句从一个表复制数据，然后把数据插入到一个已存在的表中。

SQL INSERT INTO SELECT语句
INSERT INTO SELECT语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

SQL INSERT INTO SELECT语法

我们可以从一个表中复制所有的列插入到另一个已存在的表中：

1
2
INSERT INTO table2
SELECT * FROM table1;

或者我们可以只复制希望的列插入到另一个已存在的表中：

1
2
3
4
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

演示数据库
在本教程中，我们将使用 RUNOOB 样本数据库。

下面是选自 “Websites” 表的数据：

1
2
3
4
5
6
7
8
9
10
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 “apps” APP 的数据：

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)

SQL INSERT INTO SELECT 实例

复制 “apps” 中的数据插入到 “Websites” 中：

实例

1
2
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;

只复 QQ 的 APP 到 “Websites” 中：

实例

1
2
3
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;

SQL SELECT INTO 语句

https://www.runoob.com/sql/sql-select-into.html
通过SQL，您可以从一个表复制信息到另一个表。

SELECT INTO语句从一个表复制数据，然后把数据插入到另一个新表中。

注意：
MySQL 数据库不支持 SELECT … INTO 语句，但支持 INSERT INTO … SELECT 。
当然你可以使用以下语句来拷贝表结构及数据：

1
2
3
CREATE TABLE 新表
AS
SELECT * FROM 旧表 ;

SQL SELECT INTO 语法

我们可以复制所有的列插入到新表中：

1
2
3
SELECT *
INTO newtable [IN externaldb]
FROM table1;

或者只复制希望的列插入到新表中：

1
2
3
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

提示：新表将会使用 SELECT 语句中定义的列名称和类型进行创建。您可以使用 AS 子句来应用新名称。

SQL SELECT INTO 实例

创建 Websites 的备份复件：

1
2
3
SELECT *
INTO WebsitesBackup2016
FROM Websites;

只复制一些列插入到新表中：

1
2
3
SELECT name, url
INTO WebsitesBackup2016
FROM Websites;

只复制中国的网站插入到新表中：

1
2
3
4
SELECT *
INTO WebsitesBackup2016
FROM Websites
WHERE country='CN';

复制多个表中的数据插入到新表中：

1
2
3
4
5
SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;

提示：SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：

1
2
3
4
SELECT *
INTO newtable
FROM table1
WHERE 1=0;

区别

select into from 和 insert into select 都是用来复制表
两者的主要区别为：

  • select into from要求目标表不存在，因为在插入时会自动创建；
  • insert into select from要求目标表存在。
  • select into from ：将查询出来的数据整理到一张新表中保存，表结构与查询结构一致。
    1
    select *(查询出来的结果) into newtable(新的表名)from where (后续条件);
    即，查询出来结果—>复制一张同结构的空表—>将数据拷贝进去。
  • insert into select ：为已经存在的表批量添加新数据。
    1
    insert into  (准备好的表) select *(或者取用自己想要的结构)from 表名 where 各种条件;
    即，指定一张想要插入数据的表格—>对数据进行加工筛选—>填入一张准备好的表格。

create table复制表结构或数据

1. 复制表结构及其数据：

1
create table table_name_new as select * from table_name_old;

2. 只复制表结构：

1
create table table_name_new as select * from table_name_old where 1=2;

或者：

1
create table table_name_new like table_name_old;

insert into 复制表数据：

如果两个表结构一样：

1
insert into table_name_new select * from table_name_old;

如果两个表结构不一样：

1
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old;

考点6:多表联查 连接查询 量词

运动会比赛信息的数据库，有如下三个表：
运动员ATHLETE（运动员编号 Ano，姓名Aname，性别Asex，所属系名 Adep）， 项目 ITEM （项目编号Ino，名称Iname，比赛地点Ilocation）， 成绩SCORE （运动员编号Ano，项目编号Ino，积分Score）。
写出目前总积分最高的系名及其积分，SQL语句实现正确的是：（ ）

  • A 
    1
    2
    3
    SELECT Adep,SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep HAVING SUM(Score)
    >=ANY
    (SELECT SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)
  • B 
    1
    2
    3
    SELECT Adep,SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep HAVING SUM(Score)
    >=SOME
    (SELECT SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)
  • C 
    1
    2
    3
    SELECT Adep,SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep HAVING SUM(Score) 
    IN
    (SELECT SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)
  • D 
    1
    2
    3
    SELECT Adep,SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep HAVING SUM(Score)
    >=ALL
    (SELECT SUM(Score) FROM ATHLETE,SCORE WHERE ATHLETE.Ano=SCORE.Ano GROUP BY Adep)
显示答案/隐藏答案正确答案: D

All：对所有数据都满足条件，整个条件才成立；
Any：只要有一条数据满足条件，整个条件成立；
Some的作用和Any一样 .

1
2
3
运动员ATHLETE（运动员编号 Ano，姓名Aname，性别Asex，所属系名 Adep）， 
项目ITEM （项目编号Ino，名称Iname，比赛地点Ilocation）， 
成绩SCORE （运动员编号Ano，项目编号Ino，积分Score）
1
2
3
ATHLETE（Ano，Aname，Asex，Adep）， 
ITEM（Ino，Iname，Ilocation）， 
SCORE（Ano，Ino，Score）

写出目前总积分最高的系名及其积分，SQL语句实现正确的是

考点7:select into from和insert into select

某打车公司将驾驶里程（drivedistanced）超过5000里的司机信息转移到一张称为seniordrivers 的表中,他们的详细情况被记录在表drivers 中，正确的sql为（）

  • A 
    1
    insert into seniordrivers drivedistanced>=5000 from drivers where;
  • B 
    1
    insert seniordrivers (drivedistanced) values from drivers where drivedistanced>=5000;
  • C 
    1
    insert into seniordrivers (drivedistanced)values>=5000 from drivers where
  • D 
    1
    select * into seniordrivers from drivers where drivedistanced >=5000;
显示答案/隐藏答案正确答案: D

考点8:as取别名

在MySql中进行数据查询时，如果要对查询结果的列名重新命名，将sno列重新命名为学号，则下列语句正确的是（ ）

  • A select sno as 学号 from T
  • B select 学号= sno from T
  • C select sno 学号 from T
  • D select sno=学号 from T
显示答案/隐藏答案正确答案: AC

as只用于mysql语言中，as可以为列或者表起别名，as可以省略，但是要用空格代替

考点9:select语句

SQL语句中，可以查看到学生信息表中，学生姓名的SQL语句为？（）

  • A select 学生信息 from 学生信息
  • B select 学生信息 from 学生姓名
  • C select * from 学生信息
  • D select 学生姓名 from 学生信息
显示答案/隐藏答案正确答案: CD

SQL SELECT 语法

1
2
SELECT column_name,column_name
FROM table_name;

考点10:聚合函数不统计null值 count(*)和count(1)都是统计行数

表结构如下：

1
2
3
4
5
6
7
CREATE TABLE `score` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sno` int(11) NOT NULL,
    `cno` tinyint(4) NOT NULL,
    `score` tinyint(4) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ;

以下查询语句结果一定相等的是（）

1
2
3
4
5
6
A.SELECT sum(score) / count(*) FROM score WHERE cno = 2;
B.SELECT sum(score) / count(id) FROM score WHERE cno = 2;
C.SELECT sum(score) / count(sno) FROM score WHERE cno = 2;
D.SELECT sum(score) / count(score) FROM score WHERE cno = 2;
E.SELECT sum(score) / count(1) FROM score WHERE cno = 2;
F.SELECT avg(score) FROM score WHERE cno = 2;
  • A A,E,F
  • B A,D,F
  • C A,B,C,D
  • D D,F
  • E A,B,E,F
  • F A,B,C,E
显示答案/隐藏答案正确答案: DF

聚合函数(字段名)会忽略null值

count(字段名)：会忽略该列中所有的null值
avg(字段名)函数会忽略null值

count(*)和count(1)都是统计行数

count(*)：不会忽略null值，本质计算的是行数
count(1)：不会忽略null值，本质计算的是行数

由于id、sno都not null，因此count(*)、count(1)、count(id)、count(sno)本质都是计算行数，ABCE的分子一样，分母也相等，所以结果相等。

score字段default null，avg(score)计算时，忽略null值，sum(score)和count(score)也同样忽略null值。
所以计算得到的avg(score)和sum(score)/count(score)的值也是相等的。即DF结果一样。

Score是默认约束为null，
count(score)统计忽略空值行（对象），
avg(score)也会忽略空值行（对象）

所以都是求平均成绩：D和F都会忽略 空值，所以DF一样结果

id,sno是not null约束的，不会有空值，所以 count(id)和count(sno)都是统计行数，count(*)和count(1)也是统计行数，所以

ABCE都是计算的全部，所以是一样的结果

主要考察：
avg(column_name)时忽略空值，
count(column_name)时忽略空值

Count(1)和count(*)效果一样，统计全部个数（count(1)比count(*)高效）

默认约束 default 的默认值设置了null

1、count(*), count(1)是记录表格出现的行数，count(sno), count(id)因为都是非空元素，计数也等于表格总行数
2、count(score)是记录score非空的行数，ave()函数也只对非空元素作用