数据库35题练习(08.08)

MySQL数据库

作者：孙慧敏

1.表、视图的区别

    1、视图是已经编译好的sql语句。而表不是   

    2、视图没有实际的物理记录。而表有。

    3、表是内容，视图是窗口

    4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，
表可以及时四对它进行修改，但视图只能有创建的语句来修改

    5、表是内模式，视图是外模式

    6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。

    7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。 

    8、视图的建立和删除只影响视图本身，不影响对应的基本表。

    9、不能对视图进行update或者insert into操作。

1.1函数和存储过程的区别：

    1、存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据。

    2、存储过程声明用procedure，函数用function

    3、存储过程不需要返回类型，函数必须要返回类型。

    4、存储过程可作为独立的pl-sql执行，函数不能作为独立的plsql执行，必须作为表达式的一部分。

    5、存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out外，还可以使用return返回值。

    6、sql语句（DML或SELECT)中不可用调用存储过程，而函数可以。

2.2mysql引擎

    InnoDB和MyISAM

3.描述一下MYSQL优化

① 为查询缓存优化查询
② EXPLAIN 我们的SELECT查询(可以查看执行的行数)
③ 当只要一行数据时使用LIMIT 1
④ 为搜索字段建立索引
⑤ 在Join表的时候使用相当类型的列，并将其索引
⑥ 千万不要 ORDER BY RAND  ()
⑦ 避免SELECT *
⑧ 永远为每张表设置一个ID
⑨ 可以使用ENUM 而不要VARCHAR
⑩ 尽可能的使用NOT NULL
固定长度的表会更快
垂直分割
拆分打的DELETE或INSERT语句
越小的列会越快
选择正确的存储引擎
小心 "永久链接"
https://www.cnblogs.com/xwgcxk/p/8855469.html

4.假设我有这么一张表，有一个创建时间字段，每增加一条记录的话，这个创建时间都会取当前时间来给他保存 需求一：查询出今天在这个表里生成了多少个记录。需求二：查出表里有多少条数据。

1. SELECT COUNT(1) FROM test4 WHERE DATE_FORMAT(NOW(),'%Y%m%d') = DATE_FORMAT(test4.time,'%Y%m%d');
2. SELECT COUNT(1) FROM test4

5. limit是mysql的分页关键词，假设我给的条件是查询出前五条数据，如何查出五条之外的数据？

/*使用两层嵌套*/
SELECT * FROM test4 WHERE id NOT in (SELECT a.id FROM(SELECT * FROM test4 LIMIT 0,5)as a)
/*https://www.xuebuyuan.com/2041089.html*/

6.mysql的索引

    在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。
    索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。
    索引类型：

    1.常规索引：也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。

    2.主键索引：主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

    3.唯一索引：唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

    4.全文索引：全文索引（Full Text），可以提高全文搜索的查询效率，一般使用Sphinx替代。但Sphinx不支持中文检索，Coreseek是支持中文的全文检索引擎，也称作具有中文分词功能的Sphinx。实际项目中，我们用到的是Coreseek。

    5.外键索引：外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。
        https://www.jb51.net/article/133626.htm

7. Oracle与MYSQL的区别

    这里只针对MySQL数据库和Oracle数据库的区别

(1) 对事务的提交
    MySQL默认是自动提交，而Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮

(2) 分页查询
    MySQL是直接在SQL语句中写"select... from ...where...limit  x, y",有limit就可以实现分页;而Oracle则是需要用到伪列ROWNUM和嵌套查询

(3) 事务隔离级别
      MySQL是read commited的隔离级别，而Oracle是repeatable read的隔离级别，同时二者都支持serializable串行化事务隔离级别，可以实现最高级别的
    读一致性。每个session提交后其他session才能看到提交的更改。Oracle通过在undo表空间中构造多版本数据块来实现读一致性，每个session
    查询时，如果对应的数据块发生变化，Oracle会在undo表空间中为这个session构造它查询时的旧的数据块
    MySQL没有类似Oracle的构造多版本数据块的机制，只支持read commited的隔离级别。一个session读取数据时，其他session不能更改数据，但可以在表最后插入数据。session更新数据时，要加上排它锁，其他session无法访问数据

(4) 对事务的支持
    MySQL在innodb存储引擎的行级锁的情况下才可支持事务，而Oracle则完全支持事务

(5) 保存数据的持久性
    MySQL是在数据库更新或者重启，则会丢失数据，Oracle把提交的sql操作线写入了在线联机日志文件中，保持到了磁盘上，可以随时恢复

(6) 并发性
    MySQL以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。
    虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁。
    Oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以Oracle对并
  发性的支持要好很多。

(7) 逻辑备份
    MySQL逻辑备份时要锁定数据，才能保证备份的数据是一致的，影响业务正常的dml使用,Oracle逻辑备份时不锁定数据，且备份的数据是一致

(8) 复制
    MySQL:复制服务器配置简单，但主库出问题时，丛库有可能丢失一定的数据。且需要手工切换丛库到主库。
    Oracle:既有推或拉式的传统数据复制，也有dataguard的双机或多机容灾机制，主库出现问题是，可以自动切换备库到主库，但配置管理较复杂。

(9) 性能诊断
    MySQL的诊断调优方法较少，主要有慢查询日志。
    Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等    

(10)权限与安全
    MySQL的用户与主机有关，感觉没有什么意义，另外更容易被仿冒主机及ip有可乘之机。
    Oracle的权限与安全概念比较传统，中规中矩。

(11)分区表和分区索引
    MySQL的分区表还不太成熟稳定。
    Oracle的分区表和分区索引功能很成熟，可以提高用户访问db的体验。

(12)管理工具
    MySQL管理工具较少，在linux下的管理工具的安装有时要安装额外的包（phpmyadmin， etc)，有一定复杂性。
    Oracle有多种成熟的命令行、图形界面、web管理工具，还有很多第三方的管理工具，管理极其方便高效。

(13)最重要的区别
    MySQL是轻量型数据库，并且免费，没有服务恢复数据。
Oracle是重量型数据库，收费，Oracle公司对Oracle数据库有任何服务。

8. mysql分页跟oracle分页的区别

8.1 MySql的Limit m,n语句

    Limit后的两个参数中，参数m是起始下标，它从0开始；参数n是返回的记录数。我们需要分页的话指定这两个值即可

8.2 Oracle数据库的rownum

    在Oracle数据库中,分页方式没有MySql这样简单,它需要依靠rownum来实现.

    Rownum表示一条记录的行号,值得注意的是它在获取每一行后才赋予.因此,想指定rownum的区间来取得分页数据在一层查询语句中是无法做到的,要分页还要进行一次查询.

    https://blog.csdn.net/hengliang_/article/details/88976963

9. MySQL分页，分组用什么关键字

    分页：limit
    分组：group by

10.left join 和inner join的区别

    left join 是左连接，两张表连接，以左边的表为基础进行连接，如果左表有数据就显示左表数据
    inner join 是全连接，两张表连接，两张表字段完全匹配才返回数据
###11. sql语句的编写，update语句
Update 表名 set 列名 = 新值[列名1 = 新值2…] from [where 条件]

12. 数据库怎么避免脏读，脏读是什么

    脏读就是指读到还没完全弄好的数据。比如，你要读取数据库中的字段A、字段B，你读取时恰巧有其他用户正在更新这2个字段，而且是先更新A、再更新B，此时就可能会发生脏读：
    如果都未更新你就读取了，或者都更新完了你才读取，这都不是脏读，因为你得到的是更新前的有效值，或完全更新后的值。
    如果那个用户更新一半你就读取了，也就是说更新了A，正打算要更新B但尚未更新时，你就读取了，此时你得到的就是脏数据。
    避免脏读的办法就是采取事务，使得他用户正在更新时锁定数据库，阻止你读取，直至全部完成才让你读取。

13. 复合索引（表ab是复合索引，如果单独查询a表，索引会失效吗？）

    用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引); 复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引; 设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效;
    会失效。

15.手写创建数据库表的sql语句

    CREATE table if not EXISTS `student` (id int PRIMARY KEY auto_increment,name VARCHAR(50) not null DEFAULT "张三",money DECIMAL(3,2)

16. 事务如何使用

    mysql数据库自动开启了事务提交，所以我们应该先关闭自动提交，改为手动提交
    SET AUTOCOMMIT  = 0;    # 关闭自动提交模式（0关闭1开启）
    START TRANSACTION #开启事务
    ....sql代码....
    COMMIT #提交事务  或  ROLLBACK #事务回滚
    SET AUTOCOMMIT = 1;#开启事务提交，以便接下来使用

17. 再写项目中某个功能的sql语句

/*列出既学过1号课程又学过2号课程的所有学生姓名*/
SELECT s.sname FROM s LEFT JOIN sc 
ON s.sno = sc.sno 
WHERE sc.cno = 1 AND s.sname in (SELECT s.sname FROM s LEFT JOIN sc 
ON s.sno = sc.sno  
WHERE sc.cno = 2)
select s.sname from sc
left join s on sc.sno=s.sno
where sc.cno=1 or sc.cno=2 
group by sc.sno 
having count(sc.sno)=2

18. Select* select 1 select id(是索引以及非索引的时候) 哪个效率高

    select 1效率高

19.数据库优化问题。 常用索引有哪些，主键索引在什么时候用到？

    主键索引、普通索引、唯一索引和全文索引，

20.我有一张个人简历表，这个个人简历表里有性别年龄字段，那么我在表里添加数据库我添加了两个索引，索引一：是姓名的普通索引 索引二：是姓名跟性别的组合索引，我现在有一个select 姓名的查询语句 我现在有一个条件 where 性别为男，这个时候执行这个查询，我刚刚说的那两个索引哪个会被用到？

       都用不到

21.数据库分组，排序。Having

    分组：Group by
    排序：Order by

22.事务管理用什么 开启新事务怎么做

SET AUTOCOMMIT  = 0;    # 关闭自动提交模式（0关闭1开启）
START TRANSACTION #开启事务
....sql代码....
COMMIT #提交事务  或  ROLLBACK #事务回滚
SET AUTOCOMMIT = 1;#开启事务提交，以便接下来使用
（任何一条DML语句(insert、update、delete)执行，标志事务的开启）

23. 一条sql以时间作条件算当天内积分之和

    Select count(表名.积分) from 表名 where DATE_FORMAT(NOW(),'%Y%m%d') = DATE_FORMAT(表名.时间,'%Y%m%d');

23.1SQL语句：select*from a,b执行结果集记录行为（ D ）

A、a表记录行                B、b表记录行  
B、a表与b表记录行之和       D、a表与b表行数的笛卡尔积

24.SQL语句的聚合函数，比如sum(),count(),average()一般都配些那个子句一起使用（ B ）

A、update   B、group by   C、having count   D、order by

25.数据库索引起到什么作用

    1. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    2.可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。

    3.可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    4.在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间

    5.通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能

26.union与union all的区别

1、对重复结果的处理：UNION在进行表链接后会去重，UNION All不会。

2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

UNION ALL要比UNION效率高，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

27.sql语句中，如何在insert语句结束后，不用select查询语句就直接返回给我们主键？

jdbc

28.sql的增删改查的语法？

增加insert:
    INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( '值1', '值2', '值3', …)
删除delete:   
    DELETE FROM 表名 [ WHERE 条件 ]; 
修改update:   
    UPDATE 表名 SET 列名 = 新值 [ , 列名1 = 新值2, …. ] [ WHERE 条件 ]; 
查询selet:    
    SELECT 列名1，列名2 FROM 表名 [ WHERE 条件 ]; 

29.sql的函数用过哪些？

1. 数学函数

2. 字符串函数

3. 日期和时间函数

4. 系统信息函数

30.事务的隔离级别4种

    数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。而且，在事务的并发操作中可能会出现脏读，不可重复读，幻读。

https://www.cnblogs.com/ubuntu1/p/8999403.html

31.给一个数据库查出id为5，10，15，20，25的name的值,为查出的值进行去重复

select distinct name from 表 where id in (5,10,15,20,25)

32.怎么把这样一个表

Year    mounth    amount
1991     1         1.1
1991     2         1.2
1991     3         1.3
1991     4         1.4
1992     1         2.1
1992     2         2.2
1992     3         2.3
1992     4         2.4
查成这样一个结果
Year  m1   m2   m3   m4
1991  1.1   1.2   1.3   1.4
1992  2.1   2.2   2.3   2.4

Select a.year,(select b.amount from xxx b where b.year = a.year and b.mounth = 1) ‘m1’ from xxx a group by a.year
M2,m3,m4同m1起别名作为列名，进行子查询

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

Name    kecheng    fenshu
张三     语文       81
张三     数学       75
李四     语文       76
李四     数学       90
王五     语文       81
王五     数学       100
王五     英语       90

/*33.用一条SQL语句查询出每门课都大于80分的学生姓名*/
SELECT DISTINCT NAME FROM result WHERE `Name` not in (SELECT NAME FROM result WHERE fenshu < 80)

34.编写数据库去重的SQL语句

Select distinct 字段名1，字段名2… from 表名 [where条件]

35.什么是 DDL DML DCL

36.客户订单信息如下：

订单表sales(id:产品编号，amt:金额，cust-id：客户号)
Id  amt   cust-id    date
101 100   9001     2013-01-01
102 500   9005     2013-01-01
101 150   9003     2013-01-01
102 200   9001     2013-01-02
103 300   9002     2013-01-02
101 500   9003     2013-01-02
103 250   9002     2013-01-02
102 400   9001     2013-01-03
客户信息表customer(cust-id:客户号，name:客户姓名，addr：住址)
Cust-id  name  addr
9001   张三   上海
9002   李四   广州
9003   王五   深圳
9005   麻六   北京
1、查询每种产品的总销售数量及总销售金额，并按产品号从小到大排序
2、查询总订购金额超过600的客户的客户号，客户名及其住址

/*1、查询每种产品的总销售数量及总销售金额，并按产品号从小到大排序*/
SELECT id '产品号',COUNT(id) '总销售数量',SUM(amt) '总销售金额' FROM sales  GROUP BY id ORDER BY id
/*2、查询总订购金额超过600的客户的客户号，客户名及其住址*/
SELECT b.custid,b.name,b.addr FROM customer b   WHERE b.custid in    
(SELECT a.custid    FROM(SELECT custid,SUM(amt) sa FROM sales  GROUP BY custid) a WHERE a.sa>600) 

14. sOL 面试题 20121

1. 学生表 t student(编号 td,学号sno,姓名 sam,课程编号 cno,课程名称 cname,分数score),参考记录如下:

1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
1 2005001 张三 0001 数学 69

问题:删除除了编号id 不同,其他都相同的学生冗余信息

/*学生表*/
CREATE TABLE IF NOT EXISTS t_student
 (id int,sno int,
sname VARCHAR(50),
cno BIGINT,
cname VARCHAR(50),
score FLOAT)
CREATE table if not EXISTS `student` (
    id int  PRIMARY KEY auto_increment,
    name VARCHAR(50) not null DEFAULT "张三",
  money DECIMAL(3,2)
)
/*删除除了编号id不同，其他都相同的学生信息*/
delete from t_student where id not in
(select min(id) from (select id,sno,sname,cno,cname,score from t_student) as t group by t.sname);

2. 学生表 s(学号 sno,姓名 snaIL),课程关系c(课程号 cno,课程名 cname,任课教
teacher),选课关系 se(学号 sno,课程号 cno,分数 score)
(1)找出没有选修过“李明”老师讲授课程的所有学生姓名
(2)列出既学过“1”号课程,又学过“2”号课程的所有学生姓名
(3)列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

/*找出没有选修过李明老师讲授课程的所有学生信息*/
SELECT s.sno,s.sname FROM s WHERE s.sname NOT in 
(SELECT s.sname FROM s 
LEFT JOIN sc 
ON s.sno = sc.sno 
LEFT JOIN c 
ON c.cno = sc.cno
WHERE sc.cno = (SELECT c.cno FROM c WHERE c.cteacher = '李明'))
/*列出既学过1号课程又学过2号课程的所有学生姓名*/
SELECT s.sname FROM s LEFT JOIN sc 
ON s.sno = sc.sno 
WHERE sc.cno = 1 AND s.sname in (SELECT s.sname FROM s LEFT JOIN sc 
ON s.sno = sc.sno  
WHERE sc.cno = 2)
select s.sname from sc
left join s on sc.sno=s.sno
where sc.cno=1 or sc.cno=2 
group by sc.sno 
having count(sc.sno)=2
/*列出1号课成绩比2号同学该门课成绩高的所有学生的学号*/
SELECT s1.sno FROM 
(SELECT s.sno,sc.score FROM s 
LEFT JOIN sc 
ON s.sno = sc.sno 
LEFT JOIN c 
ON c.cno = sc.cno 
WHERE sc.cno = 1) s1 LEFT JOIN 
(SELECT s.sno,sc.score FROM s 
LEFT JOIN sc 
ON s.sno = sc.sno 
LEFT JOIN c 
ON c.cno = sc.cno 
WHERE sc.cno = 2) s2 
ON s1.sno = s2.sno 
WHERE s1.score > s2.score
SELECT * FROM c,s,c