mysql基础知识介绍

mysql简史

早期，应用程序自己管理自己的数据，每个应用程序自己写自己的读写数据代码，这样做效率低容出错，同时每个应用程序访问数据的接口不相同，数据难以复用，所以数据库的出现大大改善了这些问题，数据库作为一种专门管理数据的软件，写读写数据后提供应用程序接口。这样数据的读写功能就大大改善了。

数据模型

数据库按照数据结构来组、存储和管理数据，实际上，数据库一共有三种模型：层次模型、网状模型、关系模型
层次模型：即上下级层次关系来组织数据的一种方式，层次模型的数据结构看起来像一个树

网状模型：把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网：

关系模型：把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表：

数据类型

int 整型-----4字节整数类型
bigint 长整型-----8字节整数类型
real 浮点型 ------4字节浮点数
double 浮点型-----8字节浮点数
declmal 高精度小数-----由用户指定精度的小数，通常用于财务计算
char 定长字符串-----存储指定长度字符串
varchar（N）变长字符串-----存储可变长度的字符串
bollean 布尔类型-----存储True或Flase
date 日期类型------存储日期
time 时间类型----存储时间
datetime 时期和时间类型----存储时期+时间
关系模型-介绍
表的每一行称为：记录（record），记录是一个逻辑意义上的数据
表的每一列称为：字段（cloumn），同一个表的每一行记录都拥有相同的若干字段
字段定义了数据类型（整型、浮点型、字符串、日期、是否允许未Null）（null表示字段不存在）
（一个整型字段若为null不表示它的值为0同样一个字符串字段为null也不表示它的值为空串’ ')
注意：通常情况下，字段应该避免允许null，不允许null可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL
和Excel表有所不同，关系数据库的表和表之间需要建立一对多、多对一、一对一，这样才能够按照应用程序逻辑来组织存储数据

主键

（主键是关系表重记录的唯一标识。主键选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键你也不允许null）
在关系数据库中，一张表的每一条数据被称为一条记录，一条记录由多个字段组成，例如：student表中的两行记录：

每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义
对于关系表，有个重要约束就是任意两条记录不能重复，不能重复不是指两条记录不完全相同，而是指能够通过字段唯一区分不同的记录，这个字段被称为主键
对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。
由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。
所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。
作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：
自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增 整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用担心自己预先生成组件
全局唯一guid类型：使用一种全局唯一的字符作为主键，类似
8f55d96b-8acc-4636-8cb8-76bf8abc2f57,GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。
对于大部分应用来说，通常自增类型的组件就能满足需求，我们在学生表重定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型
如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。
联合主键
关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。
对于联合主键，允许一列有重复，只要不是所有主键列都重复即可：

如果我们把上述表的id_num和id_type这两列作为联合主键，那么上面的3条记录都是允许的，因为没有两列主键组合起来是相同的。
没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。
外键
（关系型数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证）
在一张表中，能够通过一个字段可以把数据和另一张表关联起来，这种列被称为外键，
ALTER TABLE STUDENT
当我们用主键唯一标识记录时，我们就可以在students表中确定任意一个学生的记录：

我们还可以在classes表中确定任意一个班级记录：

但是我们如何确定students表的一条记录，例如，id=1的小明，属于哪个班级呢？
由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。
为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应：

这样，我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。
例如：
○ 小明的class_id是1，因此，对应的classes表的记录是id=1的一班；
○ 小红的class_id是1，因此，对应的classes表的记录是id=1的一班；
○ 小白的class_id是2，因此，对应的classes表的记录是id=2的二班。
○ 在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。
■ 外键不通过列名实现，而是通过定义外键约束实现的;例如：
ALTER TABLE student
ADD CONSTRAINT FK_class_id
FOREIGN KEY (class_id)
refrences classer(id);
其中，外键约束的名称 fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。
通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。
由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。
要删除一个外键约束，也是通过ALTER TABLE实现的：
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
（注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN …实现的。）
多对多
通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。
多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：

中间表teacher_class 关联两个一对多的关系

通过中间表teacher_class可知teachers到classes的关系：
● id=1的张老师对应id=1,2的一班和二班；
● id=2的王老师对应id=1,2的一班和二班；
● id=3的李老师对应id=1的一班；
● id=4的赵老师对应id=2的二班。
同理可知classes到teachers的关系：
● id=1的一班对应id=1,2,3的张老师、王老师和李老师；
● id=2的二班对应id=1,2,4的张老师、王老师和赵老师；
一对一
一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。
例如，students表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表contacts，我们就可以得到一个“一对一”关系：

如果业务允许，完全可以把两个表合为一个表。但是，有些时候，如果某个学生没有手机号，那么，contacts表就不存在对应的记录。实际上，一对一关系准确地说，是contacts表一对一对应students表。
还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

索引：
在关系型数据库重，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。
索引是关系型数据库中国对某一列或多个列的值进行与预排序的数据结构，通过使用索引，可以让数据库系统不必扫描整个表。而是直接定位到符合条件的记录，这样大大加快了查询速度
例如：

如果要经常根据score列进行查询，就可以对score列创建索引：
ALTER TABLE students
ADD INDEX idx_score (score);
使用ADD INDEX idx_score(score)就创建了一个名为idx_score,使用列score的索引，索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：
ALTER TABLE students
ADD INDEX idx_name_score (name, score);
索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。
可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。
唯一索引:
（通过对数据库表创建索引，可以提高查询速度，通过创建唯一索引，可以保证某一列的值具有唯一性，数据库索引对于用户和应用程序来说都是透明的）
在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。
但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);
通过unique关键字添加一个唯一索引
也可以只对某一列添加唯一约束而不是不创建唯一索引：
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);
这种情况下，name列没有索引，但仍然具有唯一性保证。
无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。这里的意思是说，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

查询数据
在关系数据库中，最常见的操作就是查询
准备数据
为了便于讲解和练习，我们先准备好一个student表和class表

基本查询：
（使用SELECT查询的基本语句select * from <表名>可以查询到一张表的所有行和列的数据，select查询的结果是一个二维表），
添加表数据语句 insert into： insert into calss （id，name）
value（‘1’,‘一班’),(‘2’,‘二班’),('‘3’,‘三班’)
去重使用distinct：select dinstinct name from student
查询全表：select * from student
查询不要求一定要有from子句，例如：select 100+200
条件查询：
(通过where条件查询，可以筛选出符合指定条件的记录，而不是整个表的所有记录）
使用select *from <表名>可以查询到一张表的所有记录，但是很多时候，我们并不希望获得所有记录，而是根据条件选择性的获取指定条件记录例如
查询分数在80分以上的学生记录：select * from students where score>80
【注意：当使用navicat进行查询时遇到报错：
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘select s.StudentNo,s.StudentName from Student_kb03 s where s.StudentNo in (sel’ at line 14
原因：（1）sql语句后面没有写“；”
（2）字段名称和表名称使用的中文的单引号而不是英文的，也不是反引号】
■ 两个条件同时满足，使用AND
■ 两个条件满足其中一个，使用 OR
■ 不符合该条件查询，使用NOT
○ 组合三个或者更多条件，需要用小括号（）表示如何进行条件运算，例如：
select * from student where （score<80 OR score>90)AND gender =‘M’
如果不加括号，则条件运算按照NOT、AND 、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR（not>and>or)。加上括号可以改变优先级
查询分数在60分（含）~90分（含）之间的学生可以使用where语句
where score>=60 OR score <=90
where score>=60 AND score <=90
where score in(60,90)
where score between 60 and 90
where 60<=score <90

投影查询：

(使用select * 表示查询表中所有列，使用select列1，列2可以返回指定列，这种操作称为投影，select 语句可以对结果集的列进行重命名）
使用select * from <表名>where <条件>可以选出表中若干条记录，我们注意到返回的二维表结构和原表相同，即结果集的所有列与原表的所有列都一一对应
若需要返回表中的列1，列2，列3，让结果集仅包含指定列，这种操作成为投影查询
例如：从Student中返回id，score----》select id，score from students where gender=‘M’;

排序：

（使用Order by 可以对结果集进行排序，可以对多列进行升序、倒叙排序）
使用select查询时，通常是按照id进行排序的，也就是根据主键排序，这也是大部分数据库的做法，如果我们要根据其他条件排序怎么办，可以加上order by子句，按照成绩从低到高进行排序（默认ASC升序，可省略）
例如：按照score从低到高进行排序：
select id ,name,gender,score from student order by score;
倒叙排列，只需要在后面加上DESC即可
当score列有相同的数据，要进一步进行排序，可以添加列名，使用 order by score DESC，gener表示按score列倒叙后，若有相同分数，再按gender列排序
select id，name，gender，score from student order by score DESC，gender；
注意：当有where子句，则order by子句放在where 子句后面，例如：查询一班的学生成绩，并按照倒叙升序
select id from student where id=1 order by score DESC----结果集包含符合where条件的记录，并按照order by 的设定排序

分页查询：

使用select查询时，如果结果集数据很大，比如几万行数据，放在一个页面显示数据量太大，不如分页显示，每次显示100条
要实现分页功能，实际上就是从结果集中显示第1_{100条作为第一页，101}200作为第二页，这个查询可以通过limit offset子句实现（M指最多m条记录即pageSize，N为：pageSize * （pageIndex -1）
例如：select id ，name，gender from student order by score DESC limit 3 offset 0；（结果分集每页3页记录，获取第一页的记录（结果集从0开始记录）当我们要查询第二页的记录，我们只需要跳过头3条记录，也就是对结果集从3号记录开始查询，吧offser设定为3
select id ，name,gender,score from student order by score DESC limit 3 offset 3;
同理，在查询第三页的时候，offset应该设定为6
注意：（1）offset超过了查询的最大数量并不会报错，而是得到一个空的结果集
（2）offset 是可选的，如果写limit 15 相当于limit 15 offset 0
（3）limit 15 offset 30还可以简写成 limit 30，15
（4）使用分页查询时，N越大，查询效率也会越来越低

聚合查询：

计算一张表的数据量，统计总数、平均数，sql提供了专门的聚合函数，能够快速的获得结果
查询表中一共有多少条数据，使用内置count（）函数查询
select count（*）from student—count（）表示查询所有列的函数，注意聚合函数的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表一行一列，并且列名是count（）

count（*）和count（id）是一样的效果，同时聚合函数同样可以使用where条件，因此我们可以方便的统计出多少男生、多少女生、多少80分以上的学生

除了count（）函数外，sql还提供了如下聚合函数：
SUM 计算某一列的合计值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX计算某一列的最大值
MIN计算某一列的最小值

sql中的取整函数：
floor（value，precision）按精度（precision）截取某个数字，不进行舍入操作
round（value,pricision）根据给定的精度（pricision）输入数值
ceil（value）产生大于或等于指定值value的最小整数
trunc（value）与ceil（）相反，产生小于或等于指定值（value）的最小整数
sign（value）与绝对值函数ABS（）相反，ABS()给出的是值的量而不是其符号，sign(value)则给出值的符号而不是量
例如：返回大于或等于x的最大整数

注意：如果集合查询的where条件没有匹配到任何行，count（）会返回0，而SUM（），AVG()、MAX()会返回NULL;
每页3条记录，如果通过聚合函数查询获得总页数
select count（）/3 from student
select floor（count（）/3）from student
select ceil（count（*）/3） from student

分组：

如果我们需要统计一班的学生数量，我们知道，可以用select count() num from student where class_id=1;,但如果要统计2班，3班，还需要一个一个修改吗？
当然不是，对于这种情况，我们可以使用聚合函数查询，sql提供了分组聚合。
例如：select count （）num student group by class_id;
group by 子句指定按照calss_id分组，因此执行该select语句时，会把class_id相同是列分组，再分别计算，因此，得到了3行结果
为了能更好的查看清楚3行结果分别是哪3个班级的，我们把class_id也放在结果集中
select class_id, count(*)num from students group by class_id;

多表查询(又称为笛卡尔查询）

：
（使用多表查询可以获取M*N行记录，所以多表查询的结果集可能非常巨大）
select查询不但从一张表查询数据，还可以从多张表中查询数据，查询多张表的语法：
select * from <表1> < 表2>

在上述查询结果中可能工具以及自动将相同id做了区分，但我们在不使用工具进行查询时，可以通过投影查询来解决字段名相同的问题，设置列的别名：
select
student.id
student.class_id
student.name,
student.gender
student.score
class.cid
class.name_class
from student ,class;

这个查询的结果每行记录都满足条件s.gender ='M’和c.id =1。添加 where 条件后结果集的数量大大减少

连接查询：

（1.join连接查询时先确定主表，然后把另外一个表上的数据附加到结果集
2.inner join是最常见的一种join查询，语法：select …from<表1> inner join<表2> on <条件…>
3.join查询仍然是where 和order by排序）
连接查询是另一种类型的多表查询，连接查询对多个表进行join运算，简单的说就是先确定一个主表作为结果集，然后把其他表的行有选择性的连接在主表结果集上，例如我们想要选出student表中所有学生信息，可以用一条简单的select语句完成：

注意！！！：连接查询语法：
1.inner join 查询的写法是：先确定主表，使用from<表1>的语法；
2.再确定需要连接的表，使用 inner join <表2>的语法
3.然后确定连接条件，使用 on<条件…>，这里的条件是s.class_id =c.id,表示student表的class_id列和calss表的id列相同连接
4.可选，加上where 子句、order by子句，使用别名不是必须的，但可以更好简化查询语句
join查询语句还有:left outer join/right outer join/ full outer join 详情如下

修改数据：

Creat、Retrieve、Update、Delete
INSET插入新数据：inser into <表名>(字段1，字段2，…)values (值1，值2，…)
【注意，插入时常见错误：
MySQL 主从切换后，新的master上写入数据报主键冲突.错误如下：
ERROR 1062 (23000): Duplicate entry ‘5’ for key ‘PRIMARY’
原因：主从切换后只有一张表有问题，其它的表都正常。
由于使用了replace into 方式插入导致新插入数据，导致slave上表的AUTO_INCREMENT小于Master。
在slave切换为master后，新插入的数据导致主键冲突】
update更新数据表记录：update <表名>set 字段1=值1，字段2=值2，… where …;
update 语句的where条件 和select语句的where 条件相同

注意：（1）当where条件没有查询到任何记录时，update语句不会报错，也不会有任何记录被更新
（2）同时，update也可以没有where条件
（3）我们在使用真正数据库时，update语句会返回更新的函数以及where条件匹配的函数

delete
删除数据库表中的记录：delete
语法：delete from <表名> where…
注意：（1）delete 和update一样，当没有匹配到where任何记录时，delete语句也不会报错，也不会有任何别删除的记录
（2）当delete不带条件是，会删除整个表的数据
（3）delete会返回删除的行数以及where条件匹配的函数
插入和替换（insert和replace）
replace语法:replace into 表（字段名1，字段2…)values(1,2…)
插入和更新（insert和update）
当我们插入一新纪录insert，但记录已经存在，就更新记录，可以使用语法
insert into … on douplincate key update …
插入或忽略(insert和ignore）
若我们希望插入一新纪录insert但记录已经存在马，可以进行忽略，可以使用语法
insert ignore into …
快照（ create table 和select）
若想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合
create table 和select，同时新创建的表结构和select使用的表结构完全一致

写入查询结果集（insert和select）

如果查询结果集需要写入到表中，，可以结合insert和select，将selec语句的结果集直接插入到指定表中：

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：

强制使用指定索引（force index）

（注意：指定索引的前提是索引idx_class_id必须存在）
在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id =1 ORDER BY id DESC;

事务
Read Uncommitted
隔离级别最低的一种事务级别，在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读
Read Committed
在readCommitted隔离级别下，一个事务可能会遇到不可重读的问题
不可重读是指：在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据可能不一致
repeatable Read
在Repeatable Read隔离级别下，一个事务可能会遇到幻读的问题，幻读是指在一个事务中，第一次查询到某条记录，发现没有，但是，当试图更新这条不存在的记录时，既然能成功，并且，再次读取同一条记录，他就神奇的出现了

Serializable
serializable是最严格的隔离级别，在serializable隔离级别下，所有事务按照次序依次执行，因此，胀读、不可重复读、幻读都不会出现
虽然serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序性能会极具降低，如果没有特别重要的情景，一般不会使用Serializable隔离级别
如果没有指定隔离级别，数据库就会使用默认隔离级别。在mysql中，如果使用innodb，默认隔离级别是 repeattable Read