SELECT * FROM t WHERE score = 0
1.范式:
第一范式1NF:满足原子性就是第一范式,所有关系型数据库都满足;
第二范式2NF:一范式的基础上满足非主键部分不能部分依赖主键;
第三范式3NF:满足二范式前提下不能有依赖传递
2.游标:
就是一个缓存,用来保存用户sql的执行结果;
3.mysql中 in 和 exists 区别
in exists:子查询大的用exists 小的用in
not in和not exists:前者不用索引,两个表全表扫描,not exists:子查询依旧可以用索引
4.char和varchar
前者长度不可变,空白部分用空格补缺,用空间换时间,效率快
后者长度可变,时间换空间,效率慢
5.drop、delete、truncate的区别:
6.事务
1)四大特性
原子性:要么一起成功,要么一起失败
一致性:执行事务前后,数据库一致
隔离性:事务直接互不影响,一般通过锁来实现。
持久性:事务一旦提交,其结果就是永久的。即便发生系统故障,也能恢复。
2)脏读、不可重复读、幻读
脏读:T1修改数据,T2读取改数据,然后T1撤回修改,导致T2读取无效数据
不可重复读:对相同事务查询返回了两条不同结果;(Update和delete)
幻读:T1操作某个表,然后T2在表内插入一条数据,但是T1没有执行这一事务却发现表内多了一条数据。(insert)
3)事务隔离级别
未提交读:允许脏读,别的事务修改但是没提交数据后本事务中可以读取修改后的数据;
提交读:只能读取到已经提交的数据;o'racle数据库的默认级别
可重复度:无论别的事务是否修改提交数据,本事务的数据都不会被影响;mysql的默认级别
串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞。
7.索引
1)什么是索引:索引是建立在一列或者多列上的辅助对象;是一种排序的数据结构;作用是帮忙提高查询效率;索引的数据结构是:B树或者B+树
2)索引的优点:
使用索引可以帮忙提高查询效率;创建唯一索引可以保证数据库数据的唯一性;减少分组排序的时间;
3)索引的缺点:也需要实际物理空间;创建维护都需要花费时间,并且随着数据增多,索引也增多,所花费时间也就越多;表进行更新操作时,索引需要被重建,降低了数据的维护速度。
4)索引的使用场景
- 经常需要搜索的列上
- 作为主键的类上
- 经常用在链接的类上,这些连接一般都是外键
- 经常需要根据范围进行搜索的列上(例如分数。根据分数范围搜索)
- 经常需要进行排序的列上
5)索引的类型有哪些?5种
普通索引,数据可以重复,没有任何限制。
唯一索引,要求索引列的值必须唯一,但允许有空值;如果是组合索引,那么列值的组合必须唯一。
主键索引,是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值,一般是在创建表的同时创建主键索引。
组合索引 ,在多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。
全文索引,是对于大表的文本域:char,varchar,text列才能创建全文索引,主要用于查找文本中的关键字,并不是直接与索引中的值进行比较。fulltext更像是一个搜索引擎,配合match against操作使用,而不是一般的where语句加like。
6)如何建立一个索引
alter table 表名 add index(字段名);
7)索引的原理:
把无序的数据结构变有序
先排序,生成倒排表拼接地址链,查询先查倒排表,再拿地址,取出数据
8)创建索引三种方式:
第一种方式:在执行CREATE TABLE时创建索引
第二种方式:使用ALTER TABLE命令去增加索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
第三种方式:使用CREATE INDEX命令创建
CREATE INDEX index_name ON table_name (column_list);
MySQL百万级数据添加索引
- 导出原表数据
- 创建新表与原表结构保持一致,在该表上执行alter语句添加索引;
- 将表数据导入创建的新表;
- 修改新建的表名为原表名。
9).百万级x别或以上的数据如何删除
先删索引,再删无用的东西,然后创建索引,再删数据;
10)聚簇索引非聚簇索引
前者会把索引和值放在一起都存起来;后者只存索引,索引结构的叶子节点指向了数据的对应行
11)
8.sql优化
sql执行顺序:
语法:
执行顺序:
FROM
<表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...>
<join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>
这是不走索引的情况:
1)模糊查询尽量不要 %??% 而是加在最后 ??% ,会导致数据库引擎放弃索引进行全表扫描。
2)in/not in 尽量不要用,会导致数据库引擎放弃索引进行全表扫描。
如果是连续的两个数用 between 2 and 3
子查询用exists
3)尽量少用or,会导致数据库引擎放弃索引进行全表扫描。 用union
4)尽量不用null做判断条件,会导致数据库引擎放弃索引进行全表扫描。
SELECT * FROM t WHERE score IS NULL
优化:可以给字段添加默认值0,对0值进行判断。如下:
SELECT * FROM t WHERE score = 0
5)尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。
可以将表达式、函数操作移动到等号右侧。如下
-
-- 全表扫描
-
SELECT * FROM T WHERE score/10 = 9
-
-- 走索引
-
SELECT * FROM T WHERE score = 10*9
6) 当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。如下:
SELECT username, age, sex FROM T WHERE 1=1
优化方式:用代码拼装sql时进行判断,没 where 条件就去掉 where,有where条件就加 and。
7)查询条件不能用 <> 或者 !=
使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。
8)隐式类型转换造成不使用索引
select col1 from table where col_varchar=123;
9) where条件仅包含复合索引非前置列
就是索引顺序如果a没有就会成为断点;
eg:select col1 from table where key_part2=1 and key_part3=2
这里索引包含key_part1,key_part2,key_part3三列,然后1就成为断点了 就不走索引
10) order by 条件要与where中条件一致,否则order by不会利用索引进行排序
-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;
当order by 中的字段出现在where条件中时,才会利用索引而不再二次排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。
这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。
SELECT语句其他优化
1)避免使用select *
2)避免出现不确定结果的函数
3)多表联查 小表在前大表在后
4)使用表别名
5)用where代替having
6)调整Where字句中的连接顺序
MySQL采用从左往右,自上而下的顺序解析where子句。根据这个原理,应将过滤数据多的条件往前放,最快速度缩小结果集。
sql查询优化
1.复杂查询加一个临时表
2.优化group by(如果查询包括 GROUP BY 但你并不想对分组的值进行排序,你可以指定 ORDER BY NULL禁止排序。)
3.优化join语句
4.优化union查询
如果不是非要去重用union all
5.拆分复杂SQL为多个小SQL,避免大事务
9.聚合函数
1. avg 求平均数
select AVG(Amount) from Details;
2. min求最小值
select MIN(Amount) from Details;
3. max求最大值
select MAX(Amount) from Details;
4. sum求总和
select SUM(Amount) from Details;
5. count求数据的条数
select COUNT(Amount) from Details;
基于Mycat实现数据库的读写分离、分表分库;
准备两台虚拟机;安装JDK,下载压缩包、解压、进入解压后的文件夹、去config、修改server.xml文件 在<user>标签里添加数据库信息 这里的user相当于mysql数据库,
name:相当于给数据库起了个名字,这个schmas要改成接下来你在schema.xml文件里添加的数据库;
schema.xml中
name:对应数据库名称,写三个
datenode:对应数据库
具体,<dataHost>标签有个 心跳语句就是去查询数据库是否正常
重点::::balance:这个参数改成1才开启
配置主从:主是<writeHost> 从是在这个标签内部 写一个<readhost>
分片规则“:rule对应的是conf中的rule.xml文件 ,具体去里边写;
这些结束后 去mycat的bin目录里输入 ./macat start 就好了
最后查看一下:就去navicat去连接用户名就用我们起的那个名字,密码就是设置的密码,端口改成mycat默认端口:8066,然后我们项目中数据库连接就和这个mycat数据库连接就好了
MySQL行转列:
if:
select s_name,
max(if(course='语文',score,0)) as 语文,
max(if(course='数学',score,0)) as 数学,
max(if(course='英语',score,0)) as 英语,
from tb_student
group by s_name;
case:
select s_name,
max(case course when '语文' then score else 0 end) as 语文,
max(case course when '数学' then score else 0 end) as 数学,
max(case course when '英语' then score else 0 end) as 英语,
from tb_student
group by s_name;
列转行
select * from
(select s_name,'语文' as course,Chinese as score from table
union all
select s_name,'语文' as course,Chinese as score from table;)
as t order by t.score desc;