mysql - 刷题记录

程序员文章站 2024-03-12 15:33:26

...

某IT公司人事管理采用专门的人事管理系统来实现。后台数据库名为LF。新来的人事部张经理新官上任，第一件事是要对公司的员工做全面的了解。可是他在访问员工信息表EMPL里的工资和奖金字段的时被拒绝，只能查看该表其他字段。作为LF的开发者你将如何解决这一问题：（ D ）
A. 废除张经理的数据库用户帐户对表EMPL里的工资列和奖金列的SELECT权限
B. 添加张经理到db_datareader角色
C. 添加张经理到db_accessadmin角色
D. 授予张经理的数据库用户帐户对表EMPL里的工资列和奖金列的SELECT权限。

解析：

db_accessadmin	可以添加、删除用户的用户
db_datareader	可以查看所有数据库中用户表内数据的用户

2. 有一张学生成绩表sc（sno 学号，class 课程，score 成绩），请查询出每个学生的英语、数学的成绩（行转列，一个学生只有一行记录）。

select sno,
sum(if(class=‘english’,score,0)) as english,
sum( if(class=‘math’,score,0) ) as math
from sc
where class in(‘english’,‘math’)
group by sno

解析：

mysql执行顺序是先找from表，然后再where，然后Group by这些，最后Select后的语句。
这道题先找到这个表，再看where后，只把class为英语数学的筛选出来，然后通过学号分组，每个学生的成绩放到一条记录里，英语数学成绩这两个字段便是查找完每个学生的记录后得到的，需要用到sum

一张学生成绩表score，部分内容如下：
name course grade
张三操作系统 67
张三数据结构 86
李四软件工程 89
用一条SQL 语句查询出每门课都大于80 分的学生姓名，SQL语句实现正确的是：Select distinct name from score where name not in(Select name from score where grade <= 80);

解析：
选择分数不在80分及以下的名字，小王数据结构81，小王操作系统99…就会选出多个小王。
在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。

关键词 DISTINCT 用于返回唯一不同的值。

雇员表EMP 结构如下
( 雇员编号 EMPNO , 姓名 ENAME ,
工作岗位 JOB , 管理员编号 MGR ,
受雇时间 HIREDATE , 工资 SAL ,
奖金 COMM , 部门编号 DEPTNO );
下列操作语句正确的是：（ A ）

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;

between…and后加日期，短日期默认为00:00:00，所以查不到12月31日的信息

C. 显示奖金比工资多10％以上的雇员的姓名、工资及奖金。语句：SELECT ENAME,SAL ,COMM FROM EMP WHERE COMM>SAL*1.1;

C 难道是因为没有考虑奖金为null 或者工资为 null？
C的错误是where后面跟的是表达式 where expression operator value;
这样说的话 C大于等于之后还有表达式，错误.

D. 查询没有奖金且工资低于6500并工作岗位是经理、普通员工、销售员的所有员工信息。语句：SELECT * FROM EMP WHERE SAL<6500 AND COMM IS NULL AND JOB IN (‘经理’,‘普通员工’,‘销售员’);

忽略了 COMM = 0

解析：
mysql - 刷题记录

表结构如下：

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`)
 ) ;

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;

解析：
所有的统计函数都会忽略空值(null)。
A :统计所有学生的平均分，就算成绩为空的学生，最后计算count()时也作为分母基数，计算得到所有学生的平均分。
B :与A一样，因为id主键非空，count(id)所得分母基数是所有学生。
C : 与B一样，非空属性sno。
D :由于score字段的值可能是空，空值在统计时忽略，所以count(score)和sum(score)统计的只是score不为空的学生，计算得到的平均分也只是有成绩的学生的平均分，无法计算所有学生的平均分。
E: count(1)与count()一样。
F:avg(score)会忽略空值,故计算结果为有成绩的学生的平均分。

1、count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL。

2、count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL。

3、count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空。

SQL语言共分为三大类（亦有说法分为四大类）
数据查询语言（DQL）：是由SELECT子句，FROM子句，WHERE子句组成的查询块
数据操纵语言（DML）: SELECT(查询) INSERT(插入) UPDATE(更新) DELETE(删除）
数据定义语言（DDL）：CREATE(创建数据库或表或索引）ALTER(修改表或者数据库）DROP(删除表或索引）
数据控制语言（DCL）：GRANT(赋予用户权限） REVOKE(收回权限） DENY(禁止权限)
事务控制语言（TCL）：SAVEPOINT (设置保存点）ROLLBACK (回滚) **COMMIT(**提交)
All：对所有数据都满足条件，整个条件才成立；
Any：只要有一条数据满足条件，整个条件成立；
Some的作用和Any一样 .
在SQL中语法规范中，having子句的使用下面描述正确的是：（ A、C ）
A. having子句即可包含聚合函数作用的字段也可包括普通的标量字段
B. 使用having的同时不能使用where子句
C. having子句必须于group by 子句同时使用，不能单独使用
用having就一定要和group by连用，且是先group by XXX 再having XXX，用group by不一有having（它只是一个筛选条件用的）
D. 使用having子句的作用是限定分组条件
Group by才是用来分组的，group by的作用是限定分组条件，而having则是对group by中分出来的组进行条件筛选。

E. Having子句和where子句是等同的
（where子句 = 指定行所对应的条件
having子句 = 指定组所对应的条件）
where在数据分组之前使用，having在数据分组之后使用，可以同时使用。
F. 如果select语句中没有聚合函数的使用，就不能使用having子句

链接：https://www.nowcoder.com/questionTerminal/45fa358b24364928bcb6f99c7d19ea0f
来源：牛客网

有两张表，如下图所示
表A(仅列出部分数据作参考)
Order_id User_id Add_time
11701245001 10000 1498882474
11701245002 10001 1498882475
表B：(仅列出部分数据作参考)
id Order_id goods_id price
1 11701245001 1001 10
2 11701245001 1002 20
3 11701245002 1001 10
问：用SQL查询购买过goods_id 为1001的用户user_id（）

select user_id from A where order_id in (select order_id from B where goods_id = ‘1001’)
在子表元素大于1时，不能用= 要用in in（子表）
Select A.user_id from A left join B on A.order_id=B.order_id where B.goods_id=‘1001’
左连接，返回左边表中所有记录和右边表中链接字段相等的记录右连接，等值连接，两边连接字段相等的行 A left join B on A. ＝B.

mysql - 刷题记录