数据库那些题

2022-09-30 13:35:17

1.用两种方式根据部门号从高到低，工资从低到高列出每个员工说完信息　

表信息：employee：eid，ename，salary，deptid

select * from employee order by deptid desc,salary（asc）; //默认是由低到高的

考点：order by：排序

2.列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序

表信息：看最后一题的表

SELECT e.* ,eb.avgSalary ,count(*) AS total 
FROM employees1 e,(
    SELECT departmentid as avgId,avg(salary) as avgSalary 
    FROM employees1 GROUP BY departmentid
) eb 
WHERE e.departmentid = eb.avgId
AND e.salary > eb.avgSalary 
GROUP BY e.departmentid

3.分页语句

select * from t order by id limit 30,10

考点： limit A,B ,其中A代表起点，不包括A点，B代表行数

4.用一条sql语句查询每门课都大于80分的学生姓名

(1)

select name from student group by name having min(score>80);

　　考点：having 用于筛选满足条件的组：

        HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 和 SELECT 的交互方式类似。WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。

(2)

select distinct name from student where name not in (select distinct name from student where score <=80);

　　考点：distinct 去重

5.一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.

select a.name, b.name from team a, team b where a.name < b.name

//select a.name, b.name from team a, team b 是对表Team进行自连接，即（a,a）,（a,b）,（a,c）......(d,d)，就相当于数学中的排列；
//条件where a.name < b.name，是将上面的上述操作得到的排列中的重复元素（比如（a,b）和(b,a)）删除掉

6.请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目(即每月的发生额都比101科目多的科目)。请注意：TestDB中有很多科目，都有1－12月份的发生额。

表信息：AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。

select a.*
from TestDB a ,(
    select Occmonth,max(DebitOccur) Debit101ccur 
    from TestDB where AccID=‘101‘ group by Occmonth
) b
where a.Occmonth=b.Occmonth 
and a.DebitOccur>b.Debit101ccur

 7.统计每年每月的信息

表信息：

数据库那些题

 

要求输出结果：

数据库那些题

 

select sales.year ,

(select t.amount from sales t where t.month=‘1‘ and t.year= sales.year) ‘m1‘,

(select t.amount from sales t where t.month=‘2‘ and t.year= sales.year) ‘m2‘,

(select t.amount from sales t where t.month=‘3‘ and t.year= sales.year) ‘m3‘,

(select t.amount from sales t where t.month=‘4‘ and t.year= sales.year) as ‘m4‘

from sales group by year;

8.显示文章标题，发帖人、最后回复时间（这个题有问题，是要求显示一个人的还是显示所有人的最后回复时间？而且parentid是什么一直没搞懂）

数据库那些题

 

 查询所有人的：

select a.title,a.postuser,(select max(postdate) 
from articles where postuser = a.postuser ) lastTime 
from articles as a GROUP BY a.postuser

查询一个人的：

select a.title,a.postuser,(select max(postdate) 
from articles where postuser = a.postuser ) lastTime 
from articles as a WHERE a.postuser = "李四" AND a.postdate = (select max(postdate) 
from articles where postuser = a.postuser)

9.删除除了id号不同,其他都相同的学生冗余信息

数据库那些题

错误：

delete from student2 where id not in(select id from student2 as t group by t.name)

错误提示：1093 - You can‘t specify target table ‘student2‘ for update in FROM clause

原因：mysql中，不能通过嵌套子查询来直接删除或者修改记录，需要通过别名来指定嵌套子查询作为一个临时表。

 

正确：delete from student2 where id not in(SELECT a.id FROM (select id from student2 as t group by t.name)a)

10.航空网的几个航班查询题

  数据库那些题               数据库那些题

 

（1）查询起飞城市是北京的所有航班，按到达城市的名字排序

SELECT a.*,b.cityname as ‘起飞城市‘ ,c.cityName AS ‘终点城市‘ 
from flight a,city b ,city c 
WHERE a.StartCityID = b.cityID 
and a.endCityID = c.cityID 
and a.StartCityID = 1 
ORDER BY a.endCityID

（2）查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）

SELECT a.*,b.cityname as ‘起飞城市‘ ,c.cityName AS ‘终点城市‘ 
from flight a,city b ,city c 
WHERE a.StartCityID = b.cityID 
and a.endCityID = c.cityID 
and a.StartCityID = 1 
AND a.endCityID = 2 
ORDER BY a.endCityID

（3）查询具体某一天（2005-5-8）的北京到上海的的航班次数

SELECT count(*) as ‘航班数‘
from flight a,city b ,city c  
WHERE a.StartCityID = b.cityID 
and a.endCityID = c.cityID 
and a.StartCityID = 1 
AND a.endCityID = 2 
AND a.StartTime > ‘2005-05-08 00:00:00‘ 
AND a.StartTime < ‘2005-05-08 23:59:59‘ 
ORDER BY a.endCityID

10.查出比经理薪水还高的员工信息：（没搞明白员工和经理之间的联系字段 ）

数据库那些题

select y.* 
from employees y,employees j 
where y.managerid = j.id 
and y.salary>j.salary;

11.求出小于45岁的各个老师所带的大于12岁的学生人数

学生表：                                               老师表：                                                           学生老师关联表：

数据库那些题                   数据库那些题                   数据库那些题

SELECT count(*) total 
from teacher t ,student s ,tea_stu ts 
WHERE s.stuID = ts.stuID 
AND t.teaID = ts.teaID 
AND t.age < 45 
AND s.age > 12

12.求出发帖最多的人

数据库那些题

select postuser , count(*) c 
from articles group by postuser 
having count(*) = (
    select max(t.total) from (
        select postuser,count(*) total 
        from articles group by postuser) t)

13.一个用户表中有一个积分字段，假如数据库中有100多万个用户，若要在每年第一天凌晨将积分清零，你将考虑什么，你将想什么办法解决?

解决方案一，update user set score=0;

解决方案二，假设上面的代码要执行好长时间，超出我们的容忍范围，那我就alter table userdrop column score;alter table user add column score int。

知识拓展：

       数据库那些题

 

　　SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。 

1. 数据查询语言DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE 子句组成的查询块：

SELECT <字段名表> FROM <表或视图名> WHERE <查询条件> 

2 .数据操纵语言DML

数据操纵语言DML主要有三种形式：

1) 插入：INSERT

2) 更新：UPDATE

3) 删除：DELETE 

3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、 索引、同义词、聚簇等。如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER | | | | | 表 视图 索引 同义词 簇 

DDL操作是隐性提交的！不能rollback 

4. 数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

1) GRANT：授权。   

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。

    ROLLBACK：回滚，回滚命令使数据库状态回到上次最后提交的状态。其格式为： SQL>ROLLBACK;   

3) COMMIT [WORK]：提交。

在数据库的插入、删除和修改操作时，只有当事务在提交到数据 库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看 到所做的事情，别人只有在最后提交完成后才可以看到。

提交数据有三种类型：显式提交、隐式提交及自动提交。下面分 别说明这三种类型。   

(1) 显式提交 用COMMIT命令直接完成的提交为显式提交。其格式为： SQL>COMMIT；   

(2) 隐式提交 用SQL命令间接完成的提交为隐式提交。这些命令是： ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP， EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。   

(3) 自动提交  若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：  SQL>SET AUTOCOMMIT ON。

来源：知乎某大神

14.一个用户具有多个角色，请查询出该表中具有该用户的所有角色的其他用户（比如 a 用户）。

数据库那些题

SELECT * FROM emp e,
(SELECT role as cRole,number as cNumber FROM emp WHERE number = 1002) c 
WHERE e.role = c.cRole 
AND e.number != c.cNumber 
GROUP BY e.number 
having count(*)=(select count(number) from emp where number=1002 )

15.xxx公司的sql面试

数据库那些题   数据库那些题

 （1）基于上述EMPLOYEES表写出查询：写出雇用日期在今年的，或者工资在[1000,2000]之间的，或者员工姓名（last_name）以’Obama’打头的所有员工，列出这些员工的全部个人信息。

WHERE e.departmentid = d.departmentid 
AND YEAR(e.hireDate) = YEAR(CURDATE())
OR (e.salary > 1000 AND e.salary < 2000) 
OR e.last_name = ‘Obama‘ 
GROUP BY e.employees_id

（2）基于上述EMPLOYEES表写出查询：查出部门平均工资大于1800元的部门的所有员工，列出这些员工的全部个人信息。





select a.* ,AVG(e.salary) as avgsalary from employees1 a,employees1 e
where a.departmentid=e.departmentid 
GROUP BY a.employees_id 
HAVING AVG(e.salary) > 1800 
ORDER BY departmentid








select a.*, b.avgsal from employees1 a,
(select departmentid,avg(salary) as avgsal 
    from employees1 group by departmentid) as b
where a.departmentid=b.departmentid
and b.avgsal > 1800
ORDER BY departmentid

//分组！分组！分组！




（3）基于上述EMPLOYEES表写出查询：查出个人工资高于其所在部门平均工资的员工，列出这些员工的全部个人信息及该员工工资高出部门平均工资百分比。




select a.*, b.avgsal, (a.salary-b.avgsal)/b.avgsal*100 AS percent from employees1 a,
(select departmentid,avg(salary) as avgsal 
    from employees1 group by departmentid) as b
where a.departmentid=b.departmentid
and a.salary > b.avgsal 




16.存储过程和触发器




　　（1）触发器：触发器是数据库中的表/视图发生某些特定事件时自动执行的过程（代码段）。触发器主要用于维护数据库中的完整性。触发器还用于实施业务规则，审核数据库中的更改以及复制数据。最常见的触发器是在操作数据时触发数据操作语言（DML）触发器。


　　另一种说法便于理解，触发器是一种特殊类型的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如Update、 Insert、 Delete 这些操作时，SQL Server就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。




 




　　（2）存储过程：


① 存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集。经编译后存储在数据库中。


② 存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（可以有参数，也可以没有）来执行它。


③ 存储过程是由 流控制 和 SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中。


④ 存储过程 可由应用程序通过一个调用来执行，而且允许用户声明变量。


⑤ 同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。




 




　　（3）区别：


a.存储过程是一组已创建并存储在数据库中的SQL语句。所以我们可以一遍又一遍地重用代码。而触发器是一种特殊的不是由用户直接调用存储过程，创建触发器时，会定义在针对特定表或列进行特定类型的数据修改时触发。


b.用户可以使用Execute或Exec语句来直接调用或执行存储过程，而无法直接调用或执行触发器。触发相关事件时，只会自动执行触发器。


c.存储过程可以采用输入参数，而触发器中不能将参数作为输入我们不能将参数作为输入传递给触发器。


d.存储过程可以返回零或n值，触发器无法返回值。


e.我们可以在存储过程中使用事务，触发器内不允许进行事务处理


f.存储过程通常用于执行用户指定的任务，触发器通常用于审计工作




 


17.数据库三范式


第一范式（1NF）无重复的列




 




所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项。比如说：我的数据库表中有‘地址’这个字段，我可以直接将‘地址’作为一个字段。但是如果要访问地址里面的省份、城市等更为详细的信息，我需将省份、城市列为新的字段，不能在地址那一列下再分割成小字段。




数据库那些题


 


第二范式（2NF）




 




第二范式在第一范式的基础之上更进一层，第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分，需要确保数据库表中的每一列都和主键相关。即，第二范式（2NF）就是非主属性完全依赖于主关键字，即不能出现部分依赖。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。




 比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。数据库那些题


 这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关，属于部分依赖关系。所以并不符合第二范式。我们可以将这个表进行拆分。


数据库那些题


 


 第三范式（3NF）






第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。




比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。


数据库那些题


 


18.优化数据库的经验




（1）多用 LIMIT


（2）尽量不用ORDER BY RAND()（从表中随机提取一条记录），建议使用max(id) * rand()。


（3）避免 SELECT *，建议查询需要的字段就行。


（4）尽量不使用NOT IN和like语句操作。NOT IN和like“%aaa%”操作都不会使用索引，将进行全表扫描。可取的方法是NOT IN可以NOT EXISTS代替 
（注意的是like “aaa%”是可以使用索引的）


（5）UNION-ALL代替UNION。因为UNION-ALL不会过滤重复数据，所执行效率要快于UNION,并且UNION可以自动排序，而UNION-ALL不会，这些细节往往都影响了数据库的性能。




 


19.union和union all有什么区别




定义：union(或称为联合)的作用是将多个结果合并在一起显示出来。


区别：


Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序； 




SELECT * FROM student2 WHERE id < 4 
UNION 
SELECT * FROM student2 WHERE id > 2 AND id < 6 

结果：

数据库那些题



 


Union All：对两个结果集进行并集操作，包括重复行，不进行排序； 




SELECT * FROM student2 WHERE id < 4 
UNION ALL
SELECT * FROM student2 WHERE id > 2 AND id < 6 

结果：

数据库那些题





 


SELECT * FROM student2 WHERE id < 4 UNION SELECT * FROM student2 WHERE id > 2 AND id < 6 


数据库那些题
上一篇：Ubuntu下安装Apache2, php5 mysql

下一篇：win7+SQL2008无法打开物理文件 操作系统错误 5:拒绝访问 SQL Sever