mysql数据库 笔试题

Mysql1. 事务的基本要素2. 事务隔离级别（必考）3. 如何解决事务的并发问题(脏读，幻读)（必考）4. MVCC多版本并发控制（必考）5. binlog,redolog,undolog都是什么，起什么作用6. InnoDB的行锁/表锁7. myisam和innodb的区别，什么时候选择myisam8. 为什么选择B+树作为索引结构（必考）9. 索引B+树的叶子节点都可以存哪些东西（必考）10. 查询在什么时候不走（预期中的）索引（必考）11. sql如何优化12. explain是如何解析sql的13. order by原理

数据库常用知识

1. and TradeDate between 20090101 and 20091231
   表示select的数据在2009年里
   order by TradeDate asc
   升序排序
   Desc
   降序排序
   select Did from Deposit where Cid=88888888 and TradeDate between 20090101 and 20091231 order by TradeDate asc;
   查找客户号“88888888”的2009全年的存款流水，按日期升序排列

2. 针对上面的查询要求，为Deposit表创建一个索引idx_Deposit
   Create index idx_Deposit on Deposit (Cid, TradeDate)

3.Group by根据一个或多个列对结果集进行分组，例如聚合函数 (比如 SUM) 常常需要添加 Group by语句用于分组。

Group by语法可以根据给定数据列的每个成员对查询结果进行分组，这里的分组就是将一个“数据集合”划分成若干个“小块”，然后对这些“小块”进行数据处理。最终得到按一个分组汇总的结果表。

SELECT子句后面的字段一般是聚合函数或者是Group by 后面的。

Group by 一般和sum、max、avg等聚合函数一起使用。

我们查找平均工资大于7000的部门的最高工资：
执行SQL：
select dept,max(salary)
from person
group by dept
having avg(salary)>7000;

4.
输出整个银行2009年全年各网点名称及对应的存款总额。

Select dname sum(amount) from deposit,dept where deposit.deptid=dept.deptid and deposit.tradedate between 20090101 and 20191231 group by dname

5.聚合函数，
例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上

6.列出曾经有多笔（2笔或者大于2笔）存款的客户号。
Select Cid from Deposit group by Cid having count(*) > 1

统计数据，比如说count，Sum 一定要先group by进行分组，这样的话才能统计组里的各种数据，可以是通过id分组，也可以通过班级号分组 ，当用 having 的时候，前面更是要用 group by 分组

7.列出客户信息表中没有存款交易的客户，输出客户号和客户姓名。

Select cid,cname from customer where not exist(select 1 from deposit where customer.cid = deposit.cid)

,只要exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where …”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。
SELECT ID,NAME FROM A WHERE EXIST (SELECT * FROM B WHERE A.ID=B.AID)
–执行结果为
1 A1
2 A2

–原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
—> SELECT * FROM B WHERE B.AID=１有值，返回真，所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
—> SELECT * FROM B WHERE B.AID=２有值，返回真，所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
—> SELECT * FROM B WHERE B.AID=３无值，返回假，所以没有数据

**

EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。 一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

**

8.多表查询时，前面的字段名也要加上表名

9.增加员工“王五”2014年5月份工资为3000的记录，GMT_CREATE时间取数据库系统时间

INSERT INTO T_SALARY (EMPLOYEE_ID, SALARY, MONTH, GMT_CREATE) VALUES ((SELECT ID FROM T_EMPLOYEE WHERE NAME=‘王五’), 3000, 201403, CURDATE())

curdate() 是获取当前系统时间

删除员工姓名为“李四”的员工信息和工资信息

delete from T_EMPLOYEE E,T_SALARY S
where E.ID = S.EMPLOYEE_ID
and E.NAME=‘李四’;

1.找出未持有账户的客户信息列表（输出姓名、性别以及所属分行）；

select * from clientinfo where custid not in(select cusitid from acctibalance)

这个查询涉及两张表，客户信息表和账户余额表，两张表重合的字段为custid，要先查询出账户余额表中的客户id，再在客户信息表中用where custid not in来查询出所有没有账户的客户信息

2. 找出在2013年有交易发生的账户列表（输出帐号、币种、客户姓名、性别）；

select accountno,ccycode,custname,custsex
from acctbalance ac,clientinfo cl
where ac.custid=cl.custid and accountno in(
select accountno from funddetail
where trandsdate between
‘2013-01-01 00:00:00’ and ‘2013-12-31 23:59:59’
)
如果要查询的是有的，那就要用in

5) 将账户余额超过1000000且属于分行‘300000’的客户存款利率涨15%；

update acctbalance a set interestrate = interestrate * 1.15 where currentbalance > 1000000 and custid in (select custid from clientinfo where branchid = ‘300000’）；

更新语句：update 表名 set 字段=’ ’ where 条件语句

1.查询所有学过 “数据库”的学生姓名，并且按姓名由大到小排序。

注：由于补考、重修，一个学生可能有多个考试成绩，查询结果只需要输出一个。
SELECT distinct Sname

    FROM S,C,SC

    WHERE S.Sno=SC.Sno AND C.Cno=SC.Cno

    AND C.Cname=’数据库’

    ORDER BY Sname DESC;

输出一个用distinct

3) 给SC表增加代课教师字段Teacher，数据类型为字符串，长度20，非空
ALTER TABLE CS ADD(Teacher,char(20) not null)

4) 创建每个学生学习总分的视图，字段有学号、总分，视图名SCSum
CREATE VIEW SCSum

AS SELECT Sno,SUM(Grade) Grade

FROM SC

GROUP BY Sno

group by和having的用法

having是分组（group by）后的筛选条件，分组后的数据组内再筛选
where则是在分组前筛选

通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。
当你指定 GROUP BY region 时， 属于同一个region（地区）的一组数据将只能返回一行值．
也就是说，表中所有除region（地区）外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值．

SQL实例：

一、显示每个地区的总人口数和总面积．
SELECT region, SUM(population), SUM(area)
FROM bbc# & e4 k' X* n1 v% ?+ | GROUP BY region 先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中的不同字段（一或多条记录）作运算。# B* i' z, }* S, E5 i

二、 显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。
SELECT region, SUM(population), SUM(area) 7 ]; Z& I! t% i
FROM bbc 8 F4 w2 v( P- f
GROUP BY region
HAVING SUM(area)>1000000 # y" P z. O7 D9 `# X
在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。
相反，HAVING子句可以让我们筛选成组后的各组数据

group包含多个条件分组

group by departments.dept_no,title
中间不需要括号