数据库 Mysql及其优化

数据库.
(1) 关系型数据库 (RDBMS). 基于标准的SQL.
MySQL. Oracle. SQL Server.
SQLite(开发Android应用程序的轻量级关系型数据库)
关系数据库 逻辑性强而物理性弱. 数据库中各条记录 前后顺序可以任意颠倒, 不会影响库中的 数据关系. 数据操纵语言(DMl) 负责表中的增删改查.

(2) 非关系型数据库.
基于分布式, 列数据库. hadoop. HBase
基于键值对(Key-Value). Redis. Memcached.
基于文档型. mongodb.

(3) 数据库的三大范式.
保证每列具有原子性, 每个列都不可以再拆分.
保证表中每一列 都和主键相关.
每一列都和 主键直接相关, 而不是间接相关.

1 数据库有关操作. 字符串一般用单引号,也可以使用双引号, 且不区分大小写.
(1) 创建数据库:
create database 数据库名;
create database if not exists 数据库名 default charset utf8mb4;
如果没有该数据库就创建,默认字符集是utf8
(2) 查看都有哪些数据库:
show databases;
(3) 使用数据库:
use 数据库名;
(4) 删除数据库:
drop database if exists 数据库名;

2 数据库表的有关操作.
(1) 创建表:
create table 表名(字段1 类型1,字段2 类型2, …)
可以使用 comment 在字段类型后面加上说明信息.
create table student(age int, name varchar(20) comment '年龄 ') default charset=utf8(字符集如果需要的话再设置) ;
整型->int
浮点型->decimal(m,d)
字符串->varchar(size)
日期类型->timestamp.
(2) 查看表结构 (字段名称, 字段类型, 索引类型).
desc 表名;
(3) 删除表
drop table if exists 表名;

(4) 增加一个字段.
alter table 表名 add (字段1 类型1, 字段2 类型2);
(5) 修改某一字段.
alter table 表名 change 旧字段 新字段 新类型.
(6) 删除某一字段.
alter table 表名 drop 字段;
(7) 分配权限: grant 可为用户zhangsan分配 数据库userdb 表userinfo 的查询和插入数据权限.
grant select,insert on userdb.userinfo(表) to ‘zhangsan’;

3 数据库表的增删改查操作.
(1) 插入操作:
全列插入: insert into 表名 values (字段必须和定义表的 字段类型 字段顺序 字段数量一样).
INSERT INTO student VALUES (100, 10000, ‘唐三藏’, NULL);
指定列插入: insert into 表名 (指定字段列) values (对应值)
此时如果一个字段 没有加入到指定字段 就会被默认设置为null. (如果一个字段建表时被设置为 not null, 或者没有默认值时, 就必须加入指定字段, 否则会报错)
INSERT INTO student (id, sn, name) VALUES (102, 20001, ‘曹孟德’);
插入操作还可以 把查询结果中的数据插入到表中
insert into student2 select id, name from student1;
concat (‘M’, ‘Y’, ‘S’). concat 可以连接括号里的多个字符串.

(2) 查询操作 (重点): 工作中 80% 的场景都是查询.
select 出来的结果顺序是 原先在数据库中的 物理顺序. 可以加上order by 指定具体的顺序.
全列查询: select * from 表名; 一般不建议全列查询, 数量太大,可能让数据库卡死
指定列查询: select 字段1, 字段2, …from 表名 可以写查询条件.
指定了哪个字段, 查询结果就会显示哪个字段, 没有指定的并不会显示. 指定字段的顺序不需按照定义的列的顺序来, 可以任意指定.

(3) 查询 字段为 表达式:
select 字段1+字段2+字段3, 字段4+10 from 表名; 当字段为整型时才能使用这种方式. 但如果不是整型的字段也不会报错.
查询结果中的列的名称为查询中使用的名称即 字段1+字段2+字段3 和 字段4+10.

(4) 为 查询结果 中的 列 或 表本身 指定别名 (使用较多).
select 字段1+字段2+字段3 as 指定的名字 from 表名; as可以省略.
select 字段 from 表1 表别名1;
注意: 当查询 多张表时, 要用 表的别名(如果没有指定别名就用本身) . 字段来查询.

(5) 去重查询 distinct.
select distinct math from exam_result;
select distinct 列1,列2… from 表名; 当去重多列时, 这些 列中元素全部一样时 才会去重.

(6) 排序查询:order by + 指定字段 + asc / desc.
不写默认为 asc. null数据视为比任何值都小, 升序在最上面, 降序在最下面.
select 列1,列2,… from 表名 order by 列1 asc; 升序(从小到大)
select 列1,列2,… from 表名 order by 列1 desc; 降序(从大到小)
select 字段1+字段2+字段3 别名1, 字段4+10 from 表名 order by 别名1 desc;
可以使用 表达式或别名 排序.
select class,english from 表名 order by class desc, english;
(注意) 多个字段排序,在前面的优先级高, 先按照class降序方式执行. 然后在第一个字段 有重复 的情况下再按english升序排 (比如先按班级排降序, 然后 同一个班级的 再按english排升序).

(7) 条件查询: where < , <=, >, >=, =, is null, is not null, != (或者<>).
where 当指定的条件时, 无论条件是查询列的具体条件, 还是没有在查询列中出现但是表中存在的列的条件, 多列查询结果 都会受到 此条件的约束.
select class,english from 表名 where english<60;
select class,english,chinese from 表名 where english< 60 or chinese> 80;
select class,english,chinese,math from 表名 where english< 60 or chinese> 80 and math<60;
and 优先级高于 or. 要想先计算or, 可以加括号来解决.
where 如果有多个条件可以使用 and 来连接.

IN 给定具体的值.
select * from user where user_id in (600,601,602);** 或的关系, 满足其中一个就行.
select * from user where (user_id,type) in ((568,6), (569,6), (600,8)); 多个字段同时使用.
LIKE 模糊匹配.
select name from 表名 where name like ‘sun%’ sunyang
select name from 表名 where name like ‘孙%’ ; 孙权 孙悟空 以孙开头的
select name from 表名 where name like '孙_ ’ ; 孙权**
% 作为通配符, 表示 任意字符出现 任意次(0次或以上).
_ 作为通配符, 表示 任意字符出现 一次.
select name from 表名 where name like ‘%s%’ ; 查询所有姓名包含s的名字
查询第三位字母是s的. name like '__s%'
模糊匹配效率不高,工作中使用较少.
NULL
select * from 表名 where math is null;

(8) 分页查询: LIMIT 数量 offset / , 起始位置.
select * from 表名 limit 3; 不写offset 默认是 从0开始筛选. 这里是选 3个结果.
select * from 表名 limit 3 offset 3; 从查询结果的 第3个开始选3个.
select * from 表名 limit 3 offset 6;
查询了三次,每一次有3个结果.总共是9个数据.
如果查询结果 没有足够的行, 将只返回 它能返回的那么多行.
常搭配 order by 使用, 也可省略 offset.
select emp_no, salary from salaries order by salary desc limit 1, 1;

(3) 修改操作: Update … set …
update 表名 set 要修改的字段 = 修改后的值;
updata 表名 set math=math+30 order by chinese+math+english limit 3.
总成绩 倒数前三的同学数学成绩加上30.
update user set username=‘dangkun’,nickname=‘小飞侠’ where id=1;
修改 表user里面 id=1列的 username 和 nickname字段的值.

(4) 删除操作: delete from 表名 where 删除的条件.
delete from 表名 where chinese between 80 and 90; 满足条件的所有信息都会删除 (删除一整行).
delete 根据条件删除表中满足删除条件的数据, 如果不指定where字句, 就删除整张表的记录;
truncate 会删除表中 所有记录, 并且重置所有索引;
drop 会删除表中所有的记录和结构, 将表所占用的空间全部释放.
truncate 和 drop 都不能回滚.

4 数据库约束 (create table建表时指定的 字段约束): 对数据库的表内容进行一定程度的 限制和校验.
NOT NULL: 指示某个字段 不能存储NULL值.
create table student (age int, name varchar(10) not null); 插入数据时 必须把该字段加入到指定字段中, 且不能赋值为null.
UNIQUE (唯一约束): 保证某个字段 每行必须有唯一值, 不能重复.
create table student (age int unique, name varchar(5)); 如果插入两个相同的 age 就会报错.
DEFAULT: 没有给列赋值时的 默认项.
create table student (age int, name varchar(10) default ‘张三’); 如果不写 default 默认为null.
PRIMARY KEY(主键约束): NOT NULL 和 UNIQUE 的结合 , 保证某列(或两个列多个列的结合)有唯一不重复不为null 的标识, 有助于更快速的找到表中的一个特定的记录.常搭配 auto_increment 使用(自增主键, 该列不用手动指定, 插入数据时数据库自动根据上一条结果来递增增加该列的值).
FOREIGN KEY(外键约束): 保证 一个表中的数据匹配 另一个表中的值的参照完整性 (多个表之间的关联关系) 只是约束, 校验的作用, 可以没有.

5 PRIMARY KEY (主键): 在后续的删改查的时候可能 更加快速, 确保操作数据范围安全.
CREATE TABLE student ( id INT PRIMARY KEY, sn INT UNIQUE); 把id设为主键.

FOREIGN KEY (外键): 两张表 的字段约束
create table class( id int,name varchar(20));
insert into class values(1, ‘张三’);

create table student( id int, class_id int, foreign key (class_id) references class(id));

如果插入 student 表中的 class_id 字段的值在 class 表中不存在, 就会插入失败(class 表中 有相关联字段的值 才能插入成功).
insert into student values(2, 1); 插入成功.
insert into student values(3, 2); 插入失败. class_id=2, class表 id 没有为2的值.

6 主键 是能确定一条记录的唯一标识, 不允许为空, 主键只能有一个, 但可以设置 多个字段 为主键, 即 联合主键.

外键 用于与另一张表关联, 表的外键 是另一张表的主键, 外键可以有多个, 可以有重复, 可以是空值.

7 聚合查询:
(1) count. 计算一共有多少符合条件的行, 返回一个整数.

() 里面写的是你要 查询的条件(可以是整数, 表中字段 或 表达式, 也可以是 * ).
查询结果的列名即为 count 的名字, 也可以指定别名.
整数: select count(1) from student; 表有多少行记录 就返回多少(一个整数).
字段: select count(math) t(别名) from 表名 where math>80; 比如有 4 条结果就返回 4.
表达式: select title, count(distinct emp_no) from titles. (count) 里面是表达式.
count 里面 如果写字段, 表中必须要有该字段才行, 并且结果为 null 的值不会被计入结果.
select count(name) from student; name为 null 的不会被计入结果.

(2) max, min, avg 也是聚合函数.
select role, max(salary), min(salary), avg(salary) from emp.

8 分组查询: group by + 字段.
对查询结果进行分组, 找到 该字段 的每一种不同的值, 相同的值只会出现一次. (distinct 多列的值一样时才会去重, 这个是根据 group by 的字段去重).

(1) 搭配 max, min, avg 函数来使用.
select role,max(salary), min(salary),avg(salary) from emp group by role;
role有不同的值, 按 role 相同的值 进行查询 (找 每一种 不同的role 的最大,最小,和平均salary).
指定条件时 如果条件在 group by后面用 HAVING, 在前面使用 WHERE .
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

(2) 搭配 count 使用.
select count(0) from student group by student_graduate_year;
查询每一种毕业年份有多少人.

(3) 如果没有在前面或后面指定条件, 那么查询结果 只会显示每种字段的 第一个结果.
select * from student group by dorm_id;
显示 每种宿舍id的 第一个查询结果.

9 联合查询 / 多表查询(难点) 原理: 笛卡尔积 M * N, 在 sql 中查询结果 数量很大,效率更低.
进行多表查询前建好多张表.
四张表, 三组对应关系:
1 学生表 和成绩表是一对多的关系. 条件: 学生表 id = student_id.
2 课程表 和成绩表是一对多的关系. 条件: 课程表里面的 id = 成绩表里面的 course_id.
3 班级表 和学生表是一对多的关系. 条件: classes 里面的id = student 里面的 classes_id.

(1) 内连接: join on. (去掉悬浮元组的结果)

join 两张表的情况. select 要查询的表的字段 from 表1 别名1 (inner) join 表2 别名2 on 条件 and 其他条件;
查询名字为"许仙"的所有科目成绩: (score 表没有学生姓名但是有学生id, 根据 学生id 把两张表结合起来).
select sco.score from student stu (inner) join score sco on stu.id = sco.student_id and stu.name = ‘许仙’;

join 多张表的情况. 它们之间 可以互相关联.
select 要查询的表的字段 from 表1 join 表2 on 条件 join 表3 on 条件 and 其他条件.
select e.last_name, e.first_name,d.dept_name
from employees e left join dept_emp de
on e.emp_no = de.emp_no
left join departments d
on de.dept_no=d.dept_no;
内连接也可以写成这样 (不使用 join on的方式):
select sco.score from student stu, score sco where stu.id = sco.student_id and student.name=‘许仙’;

外连接.
建两张表 r 和 s.

1 左外连接: 左侧的表完全显示, 即左表保留悬浮元组, 右表保留结果集用null代替. (悬浮元组, 如s表中的 (b5, 2) ).

select 字段名 from 表名1 left join 表名2 on 连接条件;
select * from r left join s on r.r_b=s.s_b;

2 右外连接: 右侧的表完全显示, 即右表保留悬浮元组, 左表保留结果集用null代替.

select 字段 from 表名1 right join 表名2 on 连接条件;
select * from r right join s on r.r_b=s.s_b

相比于 内连接,会舍弃悬浮元组.

select * from r (inner) join s on r.r_b=s.s_b;

3 全连接: 先以左表进行左外连接, 再以右表进行右外连接, 是多个 select 查询结果的 并集.
select colum1,colum2…from tableA union select colum1,colum2…from tableB;
注意: 通过union连接的SQL它们 分别单独取出的列数 必须相同.

union. 用于取得 两个结果集的 并集. 查询结果会 自动去除结果集中的 重复行.
select * from course where id < 3 union select * from course where name=‘英文’;
查询 id小于3 或者 名字为英文 的课程.

union all. 和上面一样, 但不会自动去除结果集中的 重复行.

4 自连接 (self join). 同一张表, 连接自身进行查询. 可以使用 join on 语句来进行自连接查询.

(2) 子查询(嵌套查询): where 跟着的查询条件是 当前表 或者 另一个表的 sql查询结果.

单行子查询: select … from 表1 where 字段1 = (select … from …);
查询 ‘张三’ 同学的同班同学名字:
select name from student where class_id = (select classes_id from student where name=‘张三’);

多行子查询: select … from 表1 where 字段1 (not) in (select … from …);
查询语文或者英文的成绩信息:
select score from score where course_id in( select id from course where name=‘语文’ or name=‘英文’);
select … from 表1 where (not) exists (select … from …);
临时表 tmp…

10 SQL查询中 各个关键字的执行先后顺序: from > on> join > where > group by > with > having >select > distinct > order by > limit.

11 sql注入.
通过在Web表单中 恶意输入SQL语句, 得到一个 存在安全漏洞的网站上的 数据库.

防止 sql注入.
(1) 使用预编译语句.
(2) mybatis 框架中 mapper 方式的 # 也能很大程度上防止sql注入.

12 dense_rank() 排名查询.
select id, number, dense_rank() over(order by number desc)
from passing_number
order by number desc, id;
按照 刷题通过的次数 排名.

13 Mysql 性能优化.
(1) 对查询进行优化, 尽量避免全表扫描, 在 where涉及的列上 建立索引 . 查询的条件 尽量使用索引字段. 如某一个表有多个条件, 就尽量使用 复合索引 查询.

(2) 多表关联尽量用 join . 表的关联字段如果 能用主键就用主键.(创建主键时会自动创建索引字段)

(3) 使用 limit 进行分页批量查询, 不要一次全部获取. 当 查询时知道只会得到一行数据时使用 limit 1.

(4) 绝对避免 select * 的使用, 要尽量明确 select具体需要的字段, 减少不必要字段的查询.

(5) 避免 使用 is (not) null.

(6) 用 (not) exists代替 (not) in, 效率会更好.

(7) 避免 使用不等于 (!=), 因为它不会发挥已经建立好的索引的作用.

(8) 选择 正确的存储引擎.
InnoDB: 支持外键, 事务, 必须有主键(用户没有指定的话会自己找或生产一个主键) 支持表, 行级锁(默认).
MyISAM: 不支持事务和外键, 可以没有主键, 只支持表锁.

14 大表怎么优化？某个表有 近千万数据, CRUD比较慢, 如何优化?
分库分表了是怎么做的？分表分库了有什么问题?

(1) 限定数据的范围: 务必 禁止不带任何限制数据范围条件 的查询语句.

(2) 主从同步: 经典的数据库拆分方案, 主库负责写, 从库负责读.

(3) 使用缓存: 使用 MySQL的缓存.

(4) 分库分表: 主要有垂直分表和水平分表.