数据库SQL习题练习Day2

1.需查看stu表中索引的索引信息时，应使用的存储过程是（）
A. sp_helpindex
B. sp_help
C. sp_helpdb
D. sp_helptext

B选项，报告有关数据库对象的信息，语法及应用错误；
C选项，返回指定数据库或者全部数据库的信息；
D选项，显示规则、默认、存储过程、触发器、视图等对象的未加密的文本定义信息。

2.在成绩表中查询绩点在3.7-4.0之间，并且挂科数为0的学生的所有信息。下列SQL语句不正确的是（）
A. SELECT * FROM 学生 WHERE 绩点>=3.7 and 绩点<=4.0 and 挂科数=0
B. SELECT * FROM 学生 WHERE 绩点 BETWEEN 3.7 and 4.0 and 挂科数=0
C. SELECT * FROM 学生 WHERE 绩点>=3.7 and (绩点<=4.0 and 挂科数=0)
D. SELECT * FROM 学生 WHERE 挂科数=0 and (绩点>=3.7 OR 绩点<=4.0)

首先，注意审题，题目要求的是不正确的
其次，题目说的是在3.7-4.0之间的，不应该使用 OR

3.已知某期刊信息库中有作家信息表author（作者编号aid，作者姓名aname，作者笔名ausername，作者邮箱aemail，备注remarks），稿件表manuscript（稿件编号mid，标题mtitle，作者编号aid，交稿时间mtime）现需要设置外键作者编号，下列语句正确的是（）
A. ALTER TABLE manuscript
ADD CONSTRAINT FK_aid
FOREIGN KEY (aid) REFERENCES author (aid)

B. ALTER TABLE manuscript
ADD CONSTRAINT FK_aid
FOREIGN KEY (manuscript.aid) REFERENCES author (aid)

C. ALTER TABLE manuscript
ADD FOREIGN KEY (manuscript.aid)
REFERENCES author (aid)

D. ALTER TABLE manuscript
ADD FOREIGN KEY (aid)
REFERENCES author (aid)

添加外键格式为：
alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

4.语句GRANT、REVOKE实现了结构化查询语言的哪类功能（）
A. DDL
B. DML
C. DCL
D. 数据库事务

DDL主要的命令有CREATE、ALTER、DROP等，大多在建立表时使用；
DML包括SELECT、UPDATE、DELETE等，对数据库里的数据进行操作；
数据控制语言DCL，主要用于对用户权限的授权GRANT和回收REVOKE；
数据库事务包括COMMIT、ROLLBACK等，主要用于对事务的提交、回收和设置保存点。

5.有一张employees表，信息如下：

另有一张dept_manager表：

从employees表和dept_manager表中选取出所有的county值（包含重复值）并按county的升序排列得到如下结果：

下面查询语句正确的是：
A. SELECT country FROM employees UNION SELECT country FROM dept_manager ORDER BY country;

B. SELECT country FROM employees UNION SELECT country FROM dept_manager ORDER BY country DESC;

C. SELECT country FROM employees UNION ALL SELECT country FROM dept_manager ORDER BY country;

D. SELECT country FROM employees UNION ALL SELECT country FROM dept_manager ORDER BY country DESC;

在SQL中，UNION操作符可以合并多个SELECT语句的结果集，但是只使用UNION操作符的时候，他只会列出不重复的值；
如果想要获取重复值，就要用UNION ALL操作符才行。两者的语法如下：

SELECT col_name FROM tab_name UNION SELECT col_name FROM tab_name ORDER BY col_name；
SELECT col_name FROM tab_name UNION ALL SELECT col_name FROM tab_name ORDER BY col_name；

6.写一段SQL，已知衬衫表SHIRTABLE，请你实现通过窗口函数实现，根据不同的衬衫种类shirt_type，按照销售单价shirt_price从低到高的顺序创建排序表（）

正确答案: A 你的答案: A (正确)
A. SELECT shirt_name, shirt_type, shirt_price，
RANK() OVER (PARTITION BY shirt _type ORDER BY shirt_price) AS ranking
FROM SHIRTABLE

B. SELECT shirt _name, shirt_type, shirt _price，
PARTITION BY shirt _type ORDER BY shirt _price AS ranking
FROM SHIRTABLE

C.SELECT shirt _name, shirt_type, shirt _price，
RANK (PARTITION BY shirt _type ORDER BY shirt _price) AS ranking
FROM SHIRTABLE

D. SELECT shirt _name, shirt_type, shirt _price，
RANK() OVER (PARTITION BY shirt_type) AS ranking
FROM SHIRTABLE

partition by可理解为group by 分组。
over(partition by 列名)搭配分析函数时，分析函数按照每一组每一组的数据进行计算的。
rows between 开始位置 and 结束位置
是指定窗口范围，比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时，分析函数按照这个范围进行计算的。
常与over()一起使用的分析函数：
聚合类
avg()、sum()、max()、min()
排名类
rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位（如：1、3、3、6、9）
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（如：1、2、2、3、3、4）
row_number() 按照值排序时产生一个自增编号，不会重复（如：1、2、3、4、5、6）

7.关于返回受上一个SQL语句影响的行数，下列SQL语句正确的是（）
A. SELECT @@ERROR
B. SELECT @@IDENTITY
C. SELECT @@ROWCOUNT
D. SELECT @@MAX_CONNECTIONS

@@ERROR：是返回最后一个T_SQL错误的错误号
@@IDENTITY：返回最后一个插入的标识值
@@ROWCOUNT：返回受上一个SQL语句影响的行数
@@MAX_CONNECTIONS：显示可以创建的同时链接的最大数目

8.下列关于索引的描述不正确的是（）
A. 可通过索引快速查找数据，减少查询执行时间
B. 数据库索引采用B+树是因为B+树在提高了磁盘IO性能的同时解决了元素遍历效率低下的问题
C. 如果WHERE子句中使用了索引，那么ORDER BY子句中不会使用索引
D. 索引提高了查询速度，也会提高更新表的速度

索引会提高查询速度 但不会提高更新表的速度
索引滥用会降低更新表的速度，更新表时，MySQL不仅要更新数据，保存数据，还要更新索引，保存索引，索引会占用磁盘空间。

9.SELECT COALESCE（NULL，fat，2）AS name1，COALESCE（pig，test，test）AS name2 FROM NAMETABLE；以上SQL执行后name1和name2分别为多少
A. NULL，pig
B. 2，test
C. fat，pig
D.null，test

使用coalesce函数，返回第一个非空值，如果都是空，返回空值

10.下面哪个不是sql的合法标识符
A. 22d
B. sd1
C. #id
D. sut@

SQL合法标识符 第一个字必须是 字母 、下划线 、@和#开头
不能将数字作为标识的开头

11.Mysql中表student_table(id,name,birth,sex)，插入如下记录：
(‘1001’ , ‘’ , ‘2000-01-01’ , ‘男’);
(‘1002’ , null , ‘2000-12-21’ , ‘男’);
(‘1003’ , NULL , ‘2000-05-20’ , ‘男’);
(‘1004’ , ‘张三’ , ‘2000-08-06’ , ‘男’);
(‘1005’ , ‘李四’ , ‘2001-12-01’ , ‘女’);
查询name除’张三’之外的记录，正确的SQL是（）？
A. select * from student_table where name <> ‘张三’ ;
B. select * from student_table where name <> ‘张三’ or name is null ;
C. select * from student_table where name <> ‘张三’ or length(name) >= 0;
D. select * from student_table where name <> ‘张三’ and length(name) >= 0;

在MySQL中，对于等于这类操作，没有数学运算符，只有以下三种操作
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

12.Mysql中表student_table(id,name,birth,sex)，插入如下记录：
(‘1001’ , ‘’ , ‘2000-01-01’ , ‘男’);
(‘1002’ , null , ‘2000-12-21’ , ‘男’);
(‘1003’ , NULL , ‘2000-05-20’ , ‘男’);
(‘1004’ , ‘张三’ , ‘2000-08-06’ , ‘男’);
(‘1005’ , ‘李四’ , ‘2001-12-01’ , ‘女’);
执行 select * from student_table where name <> ‘张三’ 的结果行数是（）？
A. 1
B. 2
C. 3
D. 4

题目要求选择姓名不等于张三的是
1002和1003条记录处为null，null不会和任何值相等，也就不能用来做相等比较
故而只有1001和1005两条记录了

13.学生、书店和图书三个实体集之间的联系属于（）
A. 二元联系
B. 多元联系
C. 自反联系
D. 一对一联系

二元联系指只有两个实体集参与的联系；
参与联系的实体集个数大于2个时，为多元联系；
自反联系描述了同一实体集内两部分实体之间的联系，是一种特殊的二元联系；
一对一联系是二元联系下的一种细分。

14.假设创建新用户nkw，现在想对于任何IP的连接，仅拥有user数据库里面的select和insert权限，则列表语句中能够实现这一要求的语句是（）
A. grant select ,insert on . to ‘nkw’@‘%’
B. grant select ,insert on user. to ‘nkw’@‘%’*
C. grant all privileges on . to ‘nkw’@‘%’
D. grant all privileges on user.* to ‘nkw’@‘%’

数据库授权命令：
GRANT<权限> on 表名(或列名) to 用户 
数据库回收权限
REVOKE <权限> on 表名(或列名) FROM 用户 

15.有一张订单表orders，包含如下数据：

现要查找order_num不为NULL的所有数据，下面语句正确的是：
A. SELECT * FROM orders WHERE order_num != NULL;
B. SELECT * FROM orders WHERE order_num <> NULL;
C. SELECT * FROM orders WHERE order_num IS NOT NULL;
D. SELECT * FROM orders WHERE order_num NOT IS NULL;

在MySQL中不能使用 = NULL 或 != NULL 等比较运算符在列中查找 NULL 值 。
要用IS NULL 或 IS NOT NULL才会进行NULL值或非NULL值得查找。

16.将学生信息表的班级列默认值设为“暂未输入”，下列语句正确的是（）
A. ALTER TABLE student MODIFY class CHAR(10) VALUES (‘暂未输入’)；
B. INSERT INTO student (class) VALUES (‘暂未输入’)；
C. ALTER TABLE student MODIFY class CHAR(10) DEFAULT ‘暂未输入’；
D. INSERT INTO student class CHAR(10) DEFAULT ‘暂未输入’；

alter的用法：
添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
修改列名
alter table 表名 change column 旧列名 新列名 类型;
删除列
alter table 表名 drop column 列名;
修改表名
alter table 表名 rename 【to】 新表名;
表已经存在
alter table 表名 modify 字段名 default 默认值;

17.Mysql中表student_table(id,name,birth,sex)，score_table(stu_id,subject_name,score)，查询没有英语分数的学生明细记录，如下SQL正确的是（）？
A. select * from student_table where id in (
select stu_id from score_table where subject_name <> ‘英语’);

B. select * from student_table where id not in (
select stu_id from score_table where subject_name = ‘英语’);

C. select * from student_table where id not in (
select stu_id from score_table where subject_name <> ‘英语’);

D. select * from student_table where id in (
select stu_id from score_table where subject_name = ‘英语’);

AC查询的不是英语课，错误
题目要求的是查询没有英语成绩的学生，D查询的是有英语分数的学生

18.Mysql中表student_table(id,name,birth,sex)，插入如下记录：
(‘1003’ , NULL , ‘2002-05-20’ , ‘男’);
(‘1004’ , ‘张三’ , ‘2000-09-06’ , ‘男’);
(‘1005’ , ‘李四’ , ‘2001-12-01’ , ‘女’);
(‘1006’ , NULL , ‘2001-12-02’ , ‘女’);
修改name字段为NULL的记录，是男生时设置name=‘男生姓名’，是女生时设置name=‘女生姓名’，如下SQL正确的是（）？
A. update student_table set name = ( case when sex = ‘男’ then ‘男生姓名’ when sex = ‘女’ then ‘女生姓名’ end) if name is null ;

B. update student_table when name is null set name = ( case when sex = ‘男’ then ‘男生姓名’ when sex = ‘女’ then ‘女生姓名’)

C. update student_table set name = ( when sex = ‘男’ then ‘男生姓名’ when sex = ‘女’ then ‘女生姓名’) where name is null ;

D. update student_table set name = ( case when sex = ‘男’ then ‘男生姓名’ when sex = ‘女’ then ‘女生姓名’ end) where name is null ;

【修改name字段为NULL的记录】需要把where条件写在最后；
case when的正确语法是:
case when ... then ... when ... then ... else ... end，case和end不能缺少！

19.快件信息表waybillinfo(id, waybillno, zonecode, optype, update_time)中存储了快件的所有操作信息，请找出在’中山公园’网点，异常派送（optype=‘异常派件’）次数超过3次的快件(waybillno)，正确的sql为（）
A. select waybillno, count(*) from waybillinfo where zonecode=‘中山公园’ and optype=‘异常派件’
and count(waybillno) >3

B. select waybillno, count() from waybillinfo where zonecode=‘中山公园’ and optype=‘异常派件’
order by waybillno having count() > 3

C. select waybillno, count() from waybillinfo where zonecode=‘中山公园’ and optype=‘异常派件’
having count() > 3

D. select waybillno from waybillinfo where zonecode=‘中山公园’ and optype=‘异常派件’
group by waybillno having count(*) > 3

having只用来在group by之后，对group by的结果进行筛选，不能单独用
分组结果统计需要用group by，每个快件整体作为一组

20.已知grade表如下，要求找出分数重复的数据，下列选项中SQL语句正确的是（）

sno	mark
1	85
2	95
3	85
4	78

A. SELECT mark FROM (
SELECT mark,COUNT(mark) AS num
FROM grade
GROUP BY mark
) AS STATISTIC
WHERE num>1

B. SELECT mark,COUNT(mark)>1
FROM grade
GROUP BY mark

C. SELECT mark FROM (
SELECT mark,COUNT(mark) AS num
FROM grade
GROUP BY mark
)
WHERE num>1

D. SELECT mark
FROM grade
GROUP BY mark
WHERE COUNT(mark)>1

B选项，只是将所有数据出现的次数显示出来，不符题意；
更正：在GROUP BY 子句后跟HAVING 子句  COUNT (mark)>1
C选项，创建临时表的步骤中缺少“AS 临时表”这一部分；
D选项，where子句不能与聚合函数一起使用
更正：改WHERE为 HAVING 

21.有一张Person表包含如下信息：

Id	Name	Address	Career
1	Bob	China Town	Chef
2	Carter	Oxford Street	Teacher
3	Anna	Fourteen Avenue	Dancer

现要选取居住地址Address不以’C’或’O’开头的人员信息，下列MySQL查询语句正确的是：
A. SELECT * FROM Person Address REGEXP ‘^[CO]’;
B. SELECT * FROM Person Address LIKE ‘[!CO]%’;
C. SELECT * FROM Person Address LIKE ‘[^CO]%’;
D. SELECT * FROM Person Address REGEXP ‘^[CO]’;

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
[^charlist] 通配符表示 不在字符列中的任一单一字符。
MySQL中选取不以charlist中任一字符开头的语法为：
SELECT * FROM tab_name WHERE col_name REGEXP '^[charlist]';

同理，不以某、或另一某字符开头的语法为：
SELECT * FROM tab_name WHERE col_name REGEXP '^[^charlist]';
SELECT * FROM tab_name WHERE col_name NOT REGEXP '^[charlist]';

22.下面有关sql 语句中 delete、truncate的说法正确的是？（）
A. 论清理表数据的速度，truncate一般比delete更快
B. truncate命令可以用来删除部分数据。
C. truncate只删除表的数据不删除表的结构
D. delete能够回收高水位(自增ID值)

处理效率：drop>trustcate>delete
删除范围：drop删除整个表（结构和数据一起删除）；trustcate删除全部记录，但不删除表结构；delete只删除数据
高水位线：delete不影响自增ID值，高水线保持原位置不动；trustcate会将高水线复位，自增ID变为1。