本篇为基础篇,在另一篇博客里,笔者总结了一些Mysql的高级特性,如索引优化、sql执行流程、MVCC等,链接如下Mysql高级篇,需要的同学可以点击查看,希望大伙一起加油,共同进步!
1、查看登录mysql版本,报ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)
解决方法:使用mysql -uroot -p
切换到root用户登录;
2、说说你对DB、DBMS、SQL的理解?
- DB:database,看作是数据文件(.doc .txt .mp3…)
- DBMS:数据库管理系统(类似于word工具、wps工具、记事本工具…)
- MYSQL数据库服务器中安装了MySQL DBMS,使用MySQL DBMS来管理和操作DB,使用的是SQL语言。
3、SQL大小写规范(建议遵守)
Mysql在Windows环境下不区分大小写,而在Linux环境下大小写敏感,建议采取统一的书写规范:
- 数据库名、表名、表别名、字段名、字段别名等都小写;
- SQL关键字、函数名、绑定变量都大写;
4、列的别名
列的别名可以使用一对 " "引起来,不要使用 ’ ',不会报错但不专业!
注:SQL有自己统一的语言,但不同的数据库又有各自的小特点,这些不同的地方称之为方言,比如查询数据取前十条,MySQL和Oracle的SQL语句就有所不同;
5、空值参与运算
- 空值 null :不等同与 0 , ’ ’ , ‘null’ ,参与运算时,结果也一定为空。
- 解决方案:引入IFNULL
6、mysql使用limit实现数据的分页显示
注:LIMIT子句必须放在整个SELECT语句的最后,在MySQL中取前几条数据用LIMIT;
6.1 分页公式:
LIMIT (pageNo-1)*pageSize,pageSize;
注:pageNo是第几页,pageSize是每页显示多少条。
eg: SELECT * FROM student WHERE age>18 ORDER BY age LIMIT 1,10; //分页后第二页的10条数据
6.2 WHERE . . .ORDER BY . . .LIMIT 声明顺序
LIMIT的格式:严格来说:LIMIT 位置偏移量,条目数
结构“LIMIT 0,条目数” 等价于 “LIMIT 条目数”
eg: SELECT * FROM student WHERE age>18 ORDER BY age LIMIT 10; //取年龄大于18的前10条数据
6.3 一般我们使用PageHelper来实现分页,方式如下:
SpringBoot集成PageHelper实现分页
7、SQL的执行过程(面试常问)
SQL完整的写法: SELECT …FROM…JOIN…ON…WHERE…GROUP…BY…HAVING…ORDER BY…LIMIT
在我们执行中则是
FROM …JOIN…ON 先找出表,并关联出表之间的关系
WHERE… 过滤条件(非聚合)
GROUP…BY… HAVING… 分组…条件过滤
SELECT…将过滤好的虚表进行一个筛选
ORDER…BY…LIMIT…对筛选后的数据进行排序或分页
数据库主要由下面三部分组成:解析器、优化器、执行引擎
在实际的sql底层执行时:会将我们的sql语句通过解析器进行解析,判断是否有sql语法错误,语法无误则将其转换为sql查询树,通过优化器去判断是否走索引,走哪些索引比较快,最后通过我们具体的执行引擎(innodb或mysalam等)进行执行。
注:优化器是mysql底层做查询优化时,会有自己的一套执行逻辑,它会根据数据量大小、查询是否回表等因素,判断走索引还是走全表。这里有一个误区:走索引一定比全表扫描快?举个例子,我们的联合索引有最左匹配原则 ,当联合索引的第一个元素出现范围查询时,索引会失效,但这一定会失效吗?不一定!当数据量为数十万以上时,mysql认为走联合索引更快。而出现这种不走索引是因为,mysql认为如果要走索引。会进行多次的回表操作,执行效率更低。
8、WHERE 与HAVING的对比
- 从使用范围上讲,HAVING的适用范围更广;
- 如果过滤条件没有聚合函数:这种情况下,WHERE的执行效率要高于HAVING (因为如果需要通过连接从关联表中获取所需的数据,WHERE是先筛选后连接,而HAVING是先连接后筛选);
9、Mysql与Oracle的区别 - Mysql的聚合函数不能嵌套使用,Oracle的可以,如(MIN(AVG(salary))
- mysql 从前往后过滤条件,oracle先后往前
比如:北京朝阳的快递 mysql: where are1 = 北京 And aren2 = 朝阳
10、建表:基于现有的表,同时导入数据
CREATE TABLE myempt2 AS SELECT employee_id,last_name,salary FROM employee e,departments d WHERE e.departments_id=d.departments_id
- 说明1:查询语句中字段的别名,可以作为新创建的表的字段的名称
- 说明2:此时的查询语句结构可以结构比较丰富,使用各种SELECT
11、DDL(数据定义语言)和DML(数据操作语言)的说明
TRUNCATE TABLE 和DELETE FROM都可以清空表,但前者属于DDL的操作一旦执行,不可以回滚。而后者属于DML(默认自动提交,不可回滚)操作,如果在执行前执行指令 set autocommit=false则可以回滚。
注:COMMIT相当于将文件永久写入数据库,所以不可以ROLLBACK,且ROLLBACK只能回滚到最近一次COMMIT。
DDL、DCL、DML分别是啥
12、mysql去除字段内空格
#eg:找出书名中字数最多的一本书,不含空格
SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(NAME,' ','')) DESC LIMIT 1;
13、mysql数据类型及选择建议(阿里规范)
- 如果确认是整数,就用INT;
- 如果是小数,一定用定点数类型 DECIMAL(M,D)—(共M位,保留D位小数),禁止使用FLOAT和DOUBLE(会精度丢失);
- 任何字段为非负数,必须是UNSIGNED,如:出生人口;
- 若存储的字符串长度几乎相等,用CHAR定长字符串类型,如:uuid;
- VARCHAR是可变长字符串,不预先分配存储空间,长度不要超过5000,如果大于此值,定义字段类型为TEXT,独立一张表,用主键来对应,避免影响其他字段索引效率。
1、时间类:时间戳(毫秒)存入的,对应数据库为bigint类型,一般除以1000变成秒存入,对应INT类型
2、int * int 结果为int类型(可能会溢出),计算机乘法是根据加法器进行计算的
3、利用构造方法,将一个对象转换成另一个对象
14、约束(Constraint)
- 可以向声明unique的字段添加null值,且可以添加多个null值;
- 开发中,一旦主键作用的字段上声明了auto_increament,我们在添加数据的时候就不要给主键对应的字段上赋值了;
- Mysql5.7不支持CHECK约束(检查某个字段是否符合XX要求,一般指值范围)
CHECK (salary>14000)
- 阿里规范:不得使用外键(从表设置外键,指向主表)与级联,一切外键概念必须在应用层(java程序)解决。
外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响插入速度。