我们一起写博客之Mysql基础

打王者荣耀连跪，心情甚是不好~，咋办昵，思来想去写篇博客缓解一下吧，我靠，好像是处女作哦，大神请绕行~，长话短说，从今天开始养成写博客的习惯，一来跟大家分享一下一些浅显的知识，二来整理一下自己的记忆碎片，下面首先讲的是mysql数据库相关的简单操作。

正如大家所知，常用的关系型数据库有Oracle、Mysql、Sql Server，DB2等，非关系型数据库（又被称为NoSQL（Not Only SQL )，意为不仅仅是SQ）有Redis、MongoDB、Memcache等，那么到底什么是关系型数据库呢？官方的解释是关系数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。其实说白了就是一个二维表和其他与之有关系的二维表组成的一个数据组织。好了，不扯淡了，接下来直接讲一下数据库字段类型和简单的sql操作。
常用的五大数据类型如下：

整数类型：TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR、VARCHAR、TEXT

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET等。
1.整型

取值范围如果加了unsigned，表示无符号，此时最大值翻倍，如tinyint unsigned的取值范围为(0~256) 。
2、浮点型(float和double)

设一个字段定义为float(5,3)，如果插入一个数123.456,实际数据库里存的是123.47，会四舍五入。
3、定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
4、字符串(char,varchar,_text)

char(n)是固定长度的，如果存储的数值长度小于n，在字符后面会用空格进行补充，所以存储的数据必须是后面不带空格的；varchar可以指定长度n也可以不指定，无论指定与不指定长度n，占用的空间都是以存储的字符长度为准，当然，如果指定了长度n，则存储的数据长度是肯定不能超过n的；如果存储的字符长度很大，这时就需要用text等进行定义表的列数据类型了。
6.日期时间类型

数据类型的属性

数据类型属性一般是创建表的时候对属性列以及表的一些约束条件，如下面的建表语句：
create table tb_test(
id int primary key auto_increment comment ‘表id’,
name varchar(20) not null comment ‘姓名’,
·score· varchar(10) default ‘0’ comment ‘分数’
);
（1）表属性的增删改：
1.1//添加新列
alter table ‘tb_test’ add column age int default 0 comment ‘年龄’;
1.2//修改列名称或者类型
alter table ‘tb_test’ change age newage varchar(10) comment ‘新年龄’;
1.3//修改列类型
alter table ‘tb_test’ modify age char comment ‘新年龄’;
1.4//删除列
alter table ‘tb_test’ drop column age;
1.5//表名重命名
alter table ‘tb_test’ rename ‘tb_test_new’;
上面这些简单的表操作可以说是‘鸡肋’，但是我偶尔某个操作具体写法就记不清，毕竟表创建完了之后对于表属性这些操作是很不常用的，希望看到的人能够和我一样永远记住这几个简单的语句，免得用的时候再查浪费time~
（2）查询语句：
2.1//查询所有数据
select * from tb_test;
select id,name,score from tb_test;
上面两种查询语句都是查询表中所有数据，但是在日常开发中用第二种，因为第一种用代替后，mysql会去解析代表的属性，这样就降低了查询效率；
2.2//常用聚合函数
MAX最大值，MIN最小值，COUNT条数，SUM求和，AVG平均值
如：select MAX(score) from tb_test;
2.3//group by分组
分组一般会和聚合函数一起使用或者和group_concat一起使用，单独使用毫无意义
//having
2.4筛选group by完成后的数据
select name，MAX(score) from tb_test group by name having MAX(score)>60;
2.5模糊查询：%代表多个字符 代表单个字符
Select * from tb_test where name like ‘张’; 比如张三满足； 但是张三丰，张后面是两个字符 所以不满足；
2.6子查询in,not in,exist,not exist
//查询分数不是60或70的学生
Select name from tb_test where score not in (‘60’,’70’);
或者
Select name from tb_test where not exist(select * from tb_test where score in(‘60’,’70’));
2.7 any满足任意条件，all满足所有条件
Select * from tb_test where score >= any(select score from tb_test);表示查询的结果大于等于任意一个分数都可以，所以结果是全部数据
Select * from tb_test where score >= all(select score from tb_test);分数需要大于所有的分数，所以结果是最大分数的记录；
2.8union合并查询结果，会去除重复记录，union all合并记录，不会去重，需要注意的是要合并的记录必须是属性完全对应的上的
Select name,score from tb_test where score=’60’ union select name,score from tb_test where score=’70’;
2.9left join 左连接,right join右连接,inner join内连接查询
Left join会将左表中数据全部查出来，右表中能匹配上的左表属性会显示出来，不能匹配上的显示null；右连接相反；内连接将匹配到的数据显示出来，未匹配到的不显示；
Select * from table1 left join table2 on table.id=table2.id;
注意：一般使用left join的时候会将小表（即数据量行数小的表）放到左面，大表放到右面，这样查询效率会高。为什么会这样呢，比如table1有a条数据，table2有b条数据，查询次数应该是ab次啊，这么说的话小表和大表在左面查询次数应该是一样的，那效率应该相同才对啊？？！
其实当on后面使用的连接条件没有索引的时候，查询次数确实是ab，这样效率都是一样的，但是有索引的时候，由于left join使用是连接方式是nested loop，这时时间复杂度是O(alogb)，所以左面a越小时间复杂度越低了，自然速度就快了。
对于inner join时间复杂度是O(ab)。如果没有索引的话，会选择hash join或者sort merge join，比如select a.name,b.score from tb1 inner join tb2 on a.id=b.id and a.age=’10’ and b.age = ‘11’;一般比较小的表会选择hash join，首先会将tb1进行遍历，然后把age=‘10’的放入hash表，id作为key，name作为value，然后将tb2遍历，找到满足ahe=‘11’的数据，然后根据b.id去tb1的hash表寻找a.id，如果一致，会将数据组合到一起，直到查出所有结果，此时时间复杂度是O(m+n)； 如果选择merge join连接方式，会将tb1（id，name）和tb2（id，score）复制，然后排序，分别移动指针寻找id相同的数据，时间复杂度是O(nlog（n）+mlog（m）)；
（3）新增数据
Insert into tb1（id,name） values(‘2’,’李四’);如果新增数据中包括所有属性值，则（id,name）这些属性列可以不用列出，Insert into tb1 values(‘2’,’李四’);
（4）删除数据
Delete from tb1；删除所有数据
Delete from tb1 where id=‘1’； 删除id为1的数据
（5）修改数据
Update tb1 set id=‘10’，name=‘wangwu’ where id=‘1’；
（6）视图：当表中有一部分属性不想被其他人看到时，可以通过视图展示只想被别人看到的数据，视图是一个虚拟表，里面没有真实数据，都是通过查询表中数据来展示的。如果表中数据发生变化，则视图中数据也会变化，如果视图中数据发生变化，此时若视图是根据单表来展示的，则会影响单表中数据，如果是根据多表关联展示的，则只能去影响其中一个表，而且删除视图中某条数据不能将多表相应数据都删除，只能删除其中某个表相应数据，具体是哪个表就要看删除语句的写法，这里不再赘述，因为视图确实很少很少用到；
（7）索引：通过给表添加索引，如果表数据比较多可以提高查询效率，但是如果表数据很少不建议创建索引，因为每当新增数据都会去维护索引，影响性能；
7.1创建索引，可以新建表时创建，也可以单独创建索引
单独创建：alter table tb1 add index ind_name( ‘name’);这是给name列创建索引；
Create index ‘ind_name’ on tb1’(name`);
7.2删除索引
Alter table tb1 drop index ‘ind_name’;
7.3修改索引:可以先删除再新增
7.4索引类型：主键索引primary key（不能为空），唯一索引unique（此列的值全表列唯一），联合索引（基于多个列创建的索引，如（‘id’，‘name’）），全文索引FullText（普通索引只会根据索引最前面几个字符去查，对于like ‘%aaa%’这种就不起作用，此时就要用到FullText来创建索引，起初时全文索引只有MyISAM引擎支持，但是在mysql5.6.24版本之后Innodb引擎也支持全文索引，查看mysql版本用select version();即可）
7.4使用索引的优点：
主键索引和唯一索引可以保证数据的唯一性；
提高查询效率，减少表的检索行数；
7.5使用索引的缺点：
索引会占用物理空间；
Update，insert，delete都会维护索引，会消耗性能，尤其是同一个表创建很多索引的时候，随着数据量增大，索引文件会越来越大，维护起来更加耗时；所以当select少于Update，insert，delete的情况下不建议使用索引
7.6使用索引的注意事项
1.在经常需要排序(order by),分组(group by)和distinct 列上加索引 可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)；
2.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引；
3.索引无法存储null值，所以会把null赋值为0或者“”
4. 不适合键值较少的列（重复数据较多的列）
　　假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。
　　再加上访问索引块，一共要访问大于200个的数据块。
　　如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块
少一些，肯定就不会利用索引了。
5.前导模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)
6. .MySQL主要提供2种方式的索引：B-Tree索引，Hash索引
　　B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。
　　哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
　　显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。
7.索引时效的情况
7.1.1最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。
前提条件：表中已添加复合索引（username,password,age）
7.1.2不在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描
7.1.3存储引擎不能使用索引中范围条件右边的列，范围之后索引失效。（< ,> between and） select * from tb1 where name=’zhansan’ and age>’10’ and score=’60’;如果name age score都有索引，name和age会走索引 但是score在>查询条件之后，所以不会走索引
7.1.4 mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效
7.1.5 mysql中使用is not null 或者 is null会导致无法使用索引
7.1.6 mysql中like查询是以%开头，索引会失效变成全表扫描，覆盖索引
7.1.7 mysql中，字符串不加单引号索引会失效。错误写法：select * from tb1 where name= zhangsan;
7.1.8 mysql中，如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
7.1.9如果mysql使用全表扫描要比使用索引快,则不会使用到索引

终于写完了~，mysql基础的东西就是上面那些了，日常开发中基本够用了