开发笔记 -- mysql常见问题

1.数据库事务的四个特性及含义

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。

持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

 

2、一个6亿的表a，一个3亿的表b，通过外键tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

解析：此题可以的关键是一个limit。LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

答：

1、如果A表TID是自增长,并且是连续的,B表的ID为索引

答：select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。

答：select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

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

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

建表语句：

CREATE TABLE chengji(

name varchar(20),

kecheng varchar(20),

fenshu int

);

INSERT INTO chengji VALUES('张三','语文',81),('张三','数学',75),('李四','语文',76),('李四','数学',90),('王五','语文',81),('王五','数学',100),('王五','英语',90);

SELECT DISTINCT NAME FROM chengji WHERE NAME NOT IN (SELECT DISTINCT NAME FROM chengji WHERE fenshu <=80);

 

 

 

4、根据以下三张表处理给出的题目：

CARD： 借书卡(表)	CNO 卡号，NAME 姓名，CLASS班级
BOOKS：图书(表)	BNO  书号，BNAME 书名， AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW：借书记录(表)	CNO 借书卡号，BNO 书号，RDATE 还书日期

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

4.1、找出借书超过5本的读者,输出借书卡号及所借图书册数。 ·

SELECT CNO,借图书册数=COUNT(*) FROM BORROW GROUP BY CNO HAVING COUNT(*)>5 ;

4.2、查询借阅了"水浒"一书的读者，输出姓名及班级。

SELECT * FROM CARD c WHERE EXISTS (SELECT * FROM BORROW a,BOOKS b  WHERE a.BNO=b.BNO AND b.BNAME=N'水浒'  AND a.CNO=c.CNO);

4.3、查询书名包括"网络"关键词的图书，输出书号、书名、作者。

SELECT BNO,BNAME,AUTHOR FROM BOOKS WHERE BNAME LIKE N'%网络%';

4.4、从BOOKS表中删除当前无人借阅的图书记录。

DELETE A FROM BOOKS a WHERE NOT EXISTS(   SELECT * FROM BORROW   WHERE BNO=a.BNO)

4.5、将"C01"班同学所借图书的还期都延长一周。

UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE) FROM CARD a,BORROW b WHERE a.CNO=b.CNO   AND a.CLASS=N'C01';

4.6、从BOOKS表中删除当前无人借阅的图书记录。

DELETE A FROM BOOKS a WHERE NOT EXISTS(   SELECT * FROM BORROW   WHERE BNO=a.BNO)    注：DELETE中不允许使用别名，所以别名要在FROM中设置。

 

5、Mysql的技术特点是什么？

答：Mysql数据库软件是一个客户端或服务器系统，其中包括：支持各种客户端程序和库的多线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理 工具。

 

6、无法登录MySQL会有哪写原因导致？

答：

1、服务未启用

2、远程连接权限被关或一直为开放。

3、登录mysql时的用户没有登录权限

4、客户端与服务端的端口不一致

5、网络原因导致的通信故障，或服务端防火墙未开放数据库端口。

 

7、CHAR和VARCHAR的区别？

答： CHAR和VARCHAR类型在存储和检索方面有所不同 • CHAR列长度固定为创建表时声明的长度，长度值范围是1到255当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。VARCHAR和CHAR的使用方面在于设计表之初的设计，两者各有千秋，VARCHAR比CHAR更省空间，但效率上略微的低于CHAR。

 

8、drop,delete与truncate的区别

答：drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。

（1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

 

（2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

 

（3） 一般而言，drop > truncate > delete

 

（4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

 

（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

 

9、公司的数据库现不支持emoji表情，如何解决？

答：入股是utf8字符集的话，需要升级至utf8_mb4即可。

 

10、描述下过往工作经验中mysql数据库的开发规范。

答：(网上很多)

命名规范

库名、表名、字段名必须使用小写字母，并采用下划线分割

MySQL有配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较。

如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱。

字段名显示区分大小写，但实际使⽤时不区分，即不可以建立两个名字一样但大小写不一样的字段。

为了统一规范， 库名、表名、字段名使用小写字母。

库名以 d 开头，表名以 t 开头，字段名以 f_ 开头

比如表 t_crm_relation，中间的 crm 代表业务模块名

视图以view_开头，事件以event_开头，触发器以trig_开头，存储过程以proc_开头，函数以func_开头

普通索引以idx_col1_col2命名，唯一索引以uk_col1_col2命名（可去掉f_公共部分）。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)

库名、表名、字段名禁止超过32个字符，需见名知意

库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符

临时库、表名须以tmp加日期为后缀

如 t_crm_relation_tmp0425。备份表也类似，形如 _bak20160425 。

按日期时间分表须符合_YYYY[MM][DD]格式

这也是为将来有可能分表做准备的，比如t_crm_ec_record_201403，但像 t_crm_contact_at201506就打破了这种规范。

不具有时间特性的，直接以 t_tbname_001 这样的方式命名。

11、如何通过Unix shell 登录 MySQL？

答：/bin/mysql -h hostname | host_ip -u {userName} -p port

 

12、LIKE声明中的%和_是什么意思？

答：％对应于0个或更多字符，_只是LIKE语句中的一个字符。

 

13、如何在Unix和Mysql时间戳之间进行转换？

答： UNIX_TIMESTAMP是从Mysql时间戳转换为Unix时间戳的命令

FROM_UNIXTIME是从Unix时间戳转换为Mysql时间戳的命令

14、列对比运算符是什么？

 

答：在SELECT语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR或LIKE运算符。

 

15、MySQL查询语句中是否区分大小写？

答：要根据操作系统而定。windows中的MySQL查询语句无需区分大小写。但在Linux系统中需要区分大小写。

在Linxu系统中的规则如下：

1、数据库名与表名是严格区分大小写的；

2、表的别名是严格区分大小写的；

3、列名与列的别名在所有的情况下均是忽略大小写的；

4、变量名也是严格区分大小写的；

16、什么样的对象可以使用CREATE语句创建？

答：

DATABASE

EVENT

FUNCTION

INDEX

PROCEDURE

TABLE

TRIGGER

USER

VIEW

17、MYSQL支持事务吗？

答：

在缺省模式下，MYSQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。 但是如果你的MYSQL表类型是使用InnoDB Tables 或 BDB tables的话，你的MYSQL就可以使用事务处理,使用SET AUTOCOMMIT=0就可以使MYSQL 允许在非autocommit模式，在非autocommit模式下，你必须使用COMMIT来提交你的更改，或者用ROLLBACK来回滚你的更改。

18、MYSQL数据表在什么情况下容易损坏？

答：

服务器突然断电导致数据文件损坏。 强制关机，没有先关闭mysql 服务等。

19、超键、候选键、主键、外键

答：

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选 键和主键。 候选键：是最小超键，即没有冗余元素的超键。 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值 （Null）。 外键：在一个表中存在的另一个表的主键称此表的外键。

20、什么是事务？什么是锁？

答：

事务：就是被绑定在一起作为一个逻辑工作单元的SQL 语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态， 或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID 测试，即原子性，一致性，隔离 性和持久性。 锁：在所以的 DBMS 中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不 能使用某些数据或数据结构。当然锁还分级别的。

21、出现Lock wait timeout exceeded; try restarting transaction是什么原因？

答：

是锁等待超时。是当前事务在等待其它事务释放锁资源造成的。通过select * from information_schema.innodb_trx;可以查看到有哪些事务未释放资源。

通过kill+线程号可以解决。

例如kill+79干掉对应的线程。

22、Nonrepeatable Read是什么意思？与数据库、MySQL有什么关系？

答：

Nonrepeatable Read --  不可重复读，是一种事务线程安全的问题。

在MySQL中仅是一种隔离级别，MySQL的默认隔离级别是 -- 可重复读 -- Repeatable read

23、mysql复杂更新（关联更新）

update basic_user a

join auth_provider b

on a.id = b.auth_user_id

and b.remark = '小匠'

and b.user_type_id = '313347464344109056'

set a.nickname = b.auth_name;