2021年09月22日 SQL专项练习

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

考点1:数据库分类

MySQL是一种（ ）数据库管理系统。

  • A 层次型
  • B 联盟链
  • C 关系型
  • D 对象型
显示答案/隐藏答案正确答案: C

层次型数据库管理系统是紧随网状数据库而出现的。现实世界中很多事物是按层次组织起来的。层次数据模型的提出，首先是为了模拟这种按层次组织起来的事物。
最著名最典型的层次数据库系统是IBM公司的IMS（Information Management System）

MySQL就是一种普通的关系型数据库，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库中。

比较流行的数据库模型有三种，分别为层次式数据库、网状数据库和关系型数据库。而在当今的互联网中，最常见的数据库模型主要是两种，即SQL关系型数据库和NoSQL非关系型数据库。关系型数据库的代表包括Oracle, Sql Server, Mysql。

考点2:分组查询语句

SQL中属于分组查询的语句是？（）

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

区块链分类

区块链大致可以分为公有链（Public Blockchain）、私有链（Private Blockchain）以及联盟链（Consortium Blockchain）三大类。
因此，联盟链属于区块链的一种。

SQL各语句的作用

  • where筛选、
  • group by分组、
  • having与group by连用，用于筛选。

考点3:group by having

若要“查询选修了3门以上课程的学生的学号”，则正确的SQL语句是( )

  • A SELECT S# FROM SC GROUP BY S# WHERE COUNT(*)> 3
  • B SELECT S# FROM SC GROUP BY S# HAVING COUNT(*)> 3
  • C SELECT S# FROM SC ORDER BY S# WHERE COUNT(*)> 3
  • D SELECT S# FROM SC ORDER BY S# HAVING COUNT(*)> 3
显示答案/隐藏答案正确答案: B

count(*)计算了有多少行，由于group by学号，则count(*)计算了每个学号有多少行

order by排序

1.order by 从英文里理解就是行的排序方式，默认的为升序。 order by 后面必须列出排序的字段名，可以是多个字段名。

group by必须搭配聚合函数使用

2.group by 从英文里理解就是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。
注意：聚合函数是—sum()、count()、avg()等都是“聚合函数”

having子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
SQL HAVING语法如下：

1
2
3
4
5
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

执行顺序 where->group by->聚合->having,
where无论如何不能拿到聚合

where 聚合函数 having的执行顺序

where、聚合函数、having 在from后面的执行顺序：

1
where>聚合函数(sum,min,max,avg,count)>having

若须引入聚合函数来对group by结果进行过滤 则只能用having。

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

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

1、where 后不能跟聚合函数，因为where执行顺序先于聚合函数。
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
https://blog.csdn.net/qq_35269216/article/details/90812872

SQL HAVING 子句

https://www.w3cschool.cn/sql/bxfh1oza.html

HAVING​ 子句使你能够指定过滤条件，从而控制查询结果中哪些组可以出现在最终结果里面。

WHERE​子句对被选择的列施加条件，而 ​HAVING​子句则对 ​GROUP BY​ 子句所产生的组施加条件。

语法

下面可以看到 ​HAVING​ 子句在 ​SEL ECT ​查询中的位置：

1
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY;

在SELECT​查询中，​HAVING​子句必须紧随GROUP BY​子句，并出现在ORDER BY​子句（如果有的话）之前。

带有 ​HAVING​ 子句的 ​SELECT​ 语句的语法如下所示：

1
2
3
4
5
6
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2;

示例

考虑 ​CUSTOMERS​ 表，表中的记录如下所示：

1
2
3
4
5
6
7
8
9
10
11
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

下面是一个有关 ​HAVING​ 子句使用的实例，该实例将会筛选出出现次数大于或等于 2 的所有记录。

1
2
3
4
SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

其执行结果如下所示：

1
2
3
4
5
6
+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  |
+----+----------+-----+---------+---------+
|  2 | Khilan   |  25 | Delhi   | 1500.00 |
|  4 | Chaitali |  25 | Mumbai  | 6500.00 |
+----+----------+-----+---------+---------+;

考点4:多对多

某学院包含多个专业如计算机科学、信息管理、软件工程、网络工程。每个专业每年都招收一个班级的学生。在招生过程中就已明确规定，一个学生只能就读于该学院的一个班级，但是一个班级可以招收不超过60个学生。那么，学生和班级之间是________的关系。

  • A 一对多
  • B 多对多
  • C 一对一
  • D 多对一
显示答案/隐藏答案正确答案: D
  • 多个学生在一个班级，限定条件：一个学生只能在一个班级，所以多对一
  • 如果限定条件改变：一个学生可以读不同的班级，那么就是多对多

考点5:SQL Server datediff函数 getdate函数

请取出 BORROW表中日期(RDATE字段)为当天的所有记录？(RDATE字段为datetime型，包含日期与时间)。SQL语句实现正确的是：（ ）

  • A select * from BORROW where datediff(dd,RDATE,getdate())=0
  • B select * from BORROW where RDATE=getdate()
  • C select * from BORROW where RDATE-getdate()=0
  • D select * from BORROW where RDATE>getdate()
显示答案/隐藏答案正确答案: A

SQL Server DATEDIFF() 函数

https://www.w3cschool.cn/mysql/func-datediff.html

MySQL DATEDIFF() 函数

https://www.w3cschool.cn/mysql/func-datediff-mysql.html

定义和用法

DATEDIFF() 函数返回两个日期之间的天数。

语法

1
DATEDIFF(date1,date2);

date1 和 date2 参数是合法的日期或日期/时间表达式。

注释：只有值的日期部分参与计算。

实例

下面是 SELECT 语句：

1
SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate;

结果如下所示：

1
2
3
4
5
6
7
8
9
mysql> SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate;
+----------+
| DiffDate |
+----------+
|        1 |
+----------+
1 row in set (0.16 sec)

mysql>

SQL Server getdate函数

https://www.w3cschool.cn/mysql/func-getdate.html

getdate()得出的是当前日期+时间(精确到毫秒) RDATE也是datetime型，与getdate无法准确比较 故用datediff(dd, date1, date2)=0，datepart以日为单位，比较rdate和当前的日期，使之相隔0天即为当天。

SQL Server 和 MySQL 中的 Date 函数

https://www.w3cschool.cn/sql/zh6t9fpu.html

考点6:select case条件语句

积分result表中有A B C D四列，要求：
1）当A列值大于等于B列时，选择A列否则选择B列
2）当C列值大于等于D列时，选择C列否则选择D列
用SQL语句实现正确的是：（ ）

  • A select ( when A>=B then A else B ) MAX_AB, ( when C>=D then C else D ) MAX_CD from result
  • B select (case when A>=B then A else B ) MAX_AB, (case when C>=D then C else D ) MAX_CD from result
  • C select (case when A>=B then A else B end) MAX_AB, (case when C>=D then C else D end) MAX_CD from result
  • D select case when A>=B then A else B end MAX_AB, case when C>=D then C else D end MAX_CD from result
显示答案/隐藏答案正确答案: C

SQL 查询：SELECT CASE 条件赋值

https://www.cnblogs.com/richardzhu/p/3571670.html
https://zhuanlan.zhihu.com/p/63333847
https://segmentfault.com/a/1190000020877471
https://www.gairuo.com/p/sql-select-case-when-then

1
2
3
4
5
6
7
8
select c_1,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        WHEN conditionN THEN resultN
        ELSE result_else
        END as c_name
from tab_name;

MySQL CASE语句

https://www.yiibai.com/mysql/case-statement.html
https://www.yiibai.com/mysql/case-function.html
https://www.yiibai.com/plsql/plsql_searched_case.html

这里有一张图片

考点7:多变联查

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

  • A 

    1
    2
    3
    4
    5
    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
    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
    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

考点8:最可能导致sql注入的字符

下面哪些字符最可能会导致sql注入?

  • A '（单引号）
  • B /
  • C "(双引号)
  • D $
显示答案/隐藏答案正确答案: A

防止SQL注入，需要注意以下几个要点：

  • 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和**双”-“**进行转换等。
  • 永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
  • 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
  • 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
  • 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
  • sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

考点9:多表联查

雇员表EMP 结构如下

1
2
3
4
(  雇员编号 EMPNO ,   姓名 ENAME ,
工作岗位 JOB ,  管理员编号 MGR ,
受雇时间 HIREDATE ,  工资 SAL ,
奖金 COMM ,  部门编号 DEPTNO );

下列操作语句正确的是：（ ）

  • A 显示在10和30部门工作并且工资大于5500元的雇员的姓名和工资，列标题显示为Employee和Monthly Salary 语句：SELECT ENAME EMPLOYEE ,SAL “MONTHLY SALARY” FROM EMP WHERE DEPTNO IN(10,30)AND SAL>5500;
  • B 显示受雇时间在2010年1月1日和2012年12月31日之间的雇员的姓名、工资、及受雇时间，并以受雇时间升序排列。 语句：SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE BETWEEN ‘2010-01-01’ AND ‘2012-12-31’ ORDER BY HIREDATE;
  • C 显示奖金比工资多10％以上的雇员的姓名、工资及奖金。 语句：SELECT ENAME,SAL ,COMM FROM EMP WHERE COMM>SAL*1.1;
  • D 查询没有奖金且工资低于6500并工作岗位是经理、普通员工、销售员的所有员工信息。 语句：SELECT * FROM EMP WHERE SAL<6500 AND COMM IS NULL AND JOB IN (‘经理’,‘普通员工’,‘销售员’);
显示答案/隐藏答案正确答案: A

C选项 where子句中不能使用*

考点10:delete和truncate的区别

下面有关sql 语句中 delete truncate的说法正确的是？（）

  • A 论清理表数据的速度，truncate一般比delete更快
  • B truncate命令可以用来删除部分数据。
  • C truncate只删除表的数据不删除表的结构
  • D delete能够回收高水位
显示答案/隐藏答案正确答案: AC

1、处理效率：drop>trustcate>delete
2、drop删除整个表；trustcate删除全部记录，但不删除表；delete删除部分记录
3、delete不影响所用extent，高水线保持原位置不动；trustcate会将高水线复位。

truncate之所以快是因为，truncate不删除数据，只是将当前表重新映射一段存储空间，并把旧空间标记为可用，并未覆盖数据。

oracle数据库中高水位的概念，通俗的讲就是将存储空间想象成水库，插入数据水位线就会上升，但是删除操作并不会造成最高水位线下降。

执行truncate语句需要拥有表的drop权限，从逻辑上讲，truncate table类似于delete删除所有行的语句或drop table然后再create table语句的组合。为了实现高性能，它绕过了删除数据的DML方法，因此，它不能回滚。尽管truncate table与delete相似，但它被分类为DDL语句而不是DML语句

MySQL truncate table语句

https://www.yiibai.com/mysql/truncate-table.html

SQL Truncate Table

https://www.yiibai.com/sql/sql-truncate-table.html

  1. SQL TRUNCATE TABLE与DELETE逻辑上，TRUNCATE TABLE语句和不带WHERE子句的DELETE语句提供了从表中删除所有数据的相同效果。 但是，它们确实存在一些差别：

使用DELETE语句时，数据库系统会记录操作。 通过一些努力，可以回滚已删除的数据。 但是，当使用TRUNCATE TABLE语句时，除非在尚未提交的事务中使用它，否则无法回滚。
要从外键约束引用的表中删除数据，不能使用TRUNCATE TABLE语句。 在这种情况下，必须使用DELETE语句。
如果表具有与之关联的触发器，则TRUNCATE TABLE语句不会触发delete触发器。
执行TRUNCATE TABLE语句后，某些数据库系统会将自动增量列(或标识，序列等)的值重置为其起始值。 DELETE语句不是这种情况。带有WHERE子句的DELETE语句从表中删除部分数据，而TRUNCATE TABLE语句始终从表中删除所有数据。//原文出自【易百教程】，商业转载请联系作者获得授权，非商业请保留原文链接：https://www.yiibai.com/sql/sql-truncate-table.html

2. SQL TRUNCATE TABLE与DELETE

逻辑上，TRUNCATE TABLE语句和不带WHERE子句的DELETE语句提供了从表中删除所有数据的相同效果。 但是，它们确实存在一些差别：

  • 使用DELETE语句时，数据库系统会记录操作。 通过一些努力，可以回滚已删除的数据。 但是，当使用TRUNCATE TABLE语句时，除非在尚未提交的事务中使用它，否则无法回滚。
  • 要从外键约束引用的表中删除数据，不能使用TRUNCATE TABLE语句。 在这种情况下，必须使用DELETE语句。
  • 如果表具有与之关联的触发器，则TRUNCATE TABLE语句不会触发delete触发器。
  • 执行TRUNCATE TABLE语句后，某些数据库系统会将自动增量列(或标识，序列等)的值重置为其起始值。 DELETE语句不是这种情况。
  • 带有WHERE子句的DELETE语句从表中删除部分数据，而TRUNCATE TABLE语句始终从表中删除所有数据。

MYSQL中TRUNCATE和DELETE的区别

https://www.jianshu.com/p/ddc5b65e63af