MySQL详解

属于变长编码，表示不同的字符，可以用1-4个字节表示，对于汉字来说一般是三个字节，是当今世界上最流行的编码方式，不仅能表示中文，也能表示世界上任何一种语言。MySQL的utf8，是不完全体，是个残体，有些字符不支持。但是MySQL提供了一个方案utf8mb4，这个是utf8的完全体，但这个是MySQL独有的。

create database+名字 charset utf8；

b，指定一个简单的条件

（如果不存在就创建，存在就啥也不做但会弹出警告）用于批量执行

create database if not exists +名字：

查看警告：show warnings；

c，字符约束collate：

描述了字符串之间的比较规则

（3）选中数据库：

use +数据库名；

代表切换成功

（4）删除数据库

drop database+数据库名；

删除数据库是一个非常危险的的操作，一定要慎重！！！一旦删除就恢复不了

1，控制权限，不是每个人都能操作，只有少数人才能操作（MySQL也有权限机制）

2，要对数据库进行及时的备份，拷贝出来，存储到别的地方（数据备份，一二三原则：一份数据，至少需要保存到两个机器上，至少有三个副本）

3，确实需要进行删库的操作的时候，最好拉上一个人，和你一起操作，你操作，他在旁边，他在旁边给你看着（僚机）

第二组：数据表的操作

常用数据类型（MySQL中）：

数字：

虽然TINYINT和SMALLINT更节省空间，但是更推荐使用INT,BIGINT，硬件设备成本越来越低，但相比之下，程序员的开发成本（时间）更加尖锐。如果TINYINT和SMALLINT用出bug，造成的损失远远不是几个硬盘能比的。

FLOUT（M,D）：M表示浮点数的长度为M，D小数点后的位数，后序对数据库进行插入，此时新的数据就得遵循上述规则，如果非法数据就会插入失败

MySQL这里也遵守IEEE754标准，表示浮点数。很多时候，小数是不能精准储存和表示的，这就导致在进行某些计算的时候，会出现误差，也严禁使用两个浮点数进行比较

在工程上对于误差的容忍度比较大，但又一些特殊情况，对容忍就特别小，甚至0容忍

DECIMAL和NUMERIC（这两个差别不大，一般使用DECIMAL）不再使用IEEE754,这一套了，而是自己设置了一套存储格式，自己设定的这个存储格式相当于“变长的”，付出了更多的空间，使存储的数据更加准确

java中提供了类BigDecimal来表示。

上述谈到的类型均为有符号类型。无符号数字相减，很容易出现溢出。

字符串类型：

最常使用的类型VARCHAR，可变长字符串。如果是定长字符串，设定小了，容易不够用，设定大了，容易浪费空间。

强调：varchar（size）的单位是字符，不是字节。括号里面的为最大字符串长度

TEXT；不需要指定最大长度，完全根据你存储的数据自适应

虽然VARCHAR和TEXT都是可变长，但实际使用的时候更倾向于VARCHAR。TEXT可能会很大，大小难以预估，varchar由于制定了最大长度，所以容易预估出表里的数据量有多少

BLOB:存储的是二进制的数据，前面几个存储的是文本数据。

文本：（r，w，a）

二进制：（rb，wb，ab）

计算机存储和表示数据都是二进制方式（内存，硬盘，CPU……）；

所谓文本是一种特殊的情况：文本数据里面的二进制数据，都是可以在码表上查询到对应的合法的字符，反之，这段二进制内容，无法在码表上查询到，或者查询到一些乱码之类的字符

使用数据库：

图片，视频，音频，可执行文件……都是二进制，可以使用BLOB进行存储

如果需要储存“hello”这样的字符串，使用varchar、text等类型

blob只能表示最大64kb，所以我们不建议直接用数据库直接存储图片/视频/音频/等内容，数据库SQL提供了很多丰富的功能，但这些功能只能针对数字/字符串/时间日期才有效。只存二进制数据，上述这些丰富的功能就没用了，一方面，数据库往往是一个系统中，执行效率比较低的环节，容易造成性能瓶颈。把二进制数据提取出来，不使用数据库保存，也能将的数据库的负担

时间类型：

操作之前都需要use选中数据库

（1）查看数据库中有哪些表

show tables;

（2）创建表

create table 表名 （列名类型，列名类型，列名类型……）；

在关系型数据库中，一个表有哪些列，每一个列叫什么名字，类型是什么，都是要提前确定好的，后续往表里存储的数据，都要严格最受上述类型的要求

注释：comment‘注释’，只能在建表中使用

更推荐：--或者#

在最初的时候就把表的结构设计好，但后期无可避免的是有修改的，这时该怎样做（数据量很大时）

1）申请一个新的机器，搭建好数据库环境

2）把旧的数据导入新的机器中（导入的过程中要注意，对于旧的数据库代码，切换成新的数据库代码）

3）当所有数据同步后，把应用程序中访问旧的数据库的代码更换成访问新的数据库的代码

（3）查看表结构

类似于查看的是“文件详情”，这样的内容也称为“元数据”

desc 表名；

此处的int（11）：11表示位宽，在控制台上表示的时候，最多表示11个字符的宽度

这两列都允许为空；default：默认值为空

（4）删除表

drop table 表名；

注意：删表，也是一个危险的操作！！！删表比删库的危害性更大！！！删库第一时间就能发现问题，对于删表操作就更隐蔽了，程序使用数据库的时候，绝大部分都是正常的，但是可能逻辑以及出问题了

第三组：数据表内容的操作

（1）增

insert into 表名 +values（……）；

insert into 表名（列名，列名……）values（……）；个数，类型，顺序和（）中的列名进行匹配

insert into 表名 values（值，值……），（值，值……），（值，值……），（值，值……）；

在“客户端-服务器”交互过程中，交互的次数越多，整体开销就越大，花的时间就越长

插入时间：

插入当前时间：

（2）删

危险操作！！

delete from 表名 where 条件/order by /limit；

（3）改

一定要确保update数据改对了，改出问题，很麻烦！！

update 表名 set 列名 =值，列名=值……where/order by/limit；

（4）查

基础：

a，全列查询

select * from student；

*称为通配符。

select *是一个很危险的操作，如果这种表比较小select *无所谓，但是如果一个表一旦非常的，就会产生大量的硬盘IO和网络IO

硬盘和网卡，读写速度都是存在上限的，一旦触发大规模的select *就意味着把你的的硬盘/网卡宽带给吃满了，其他客户端尝试访问数据库，访问操作就无法进行了

b，指定列查询

select 列名，列名……from 表名；

c，表达式查询

select 表达式 from 表名；

这样的结果只是数据库查询过程中生成的临时表，数据库本体没有进行任何修改

d，引入多个列进行计算

表达式查询只针对列和列只进行运算！！

e，别名

select 表达式 as 别名 from 表名；

（as可以省略）

f，去重查询

select distinct 列名 from 表名；

如果列名有多项，那么需要两项内容都相同，才会去重

g，排序查询

order by+排序依据；

asc可以显示表示升序，也可以省略

查询的时候可以按照指定的某个列进行查询，顺序或者倒序

数据库不会对于查询得到的结果做出任何承诺，除非SQL包含order by（如果不写结果是不可预期的）

降序排序：order by+排序依据 desc；（descen：降序）

如果数据相同，那么排序顺序是不可与预期的。

可以指定多个列来排序：

order by 列名，列名……；先按照第一个列来排序，如果数据相同，再按照第二个来排序……

h，条件查询

满足条件的记录就保留，不满足条件的就跳过

select 列名 from 表名 where 条件；

SQL中null是一个特殊的值；加减乘除都是null。null=null=>null，null相当于不成立

null<=>null =》true

顺序：

1）遍历表，取出每一行

2）把当前行带入where条件，根据条件决定真假

3）再根据select后面的列名/表达式，进行选择行/进行计算

4）order by进行排序

i，like模糊匹配：

不要求完全相等，只要满足一定的条件就可以了

%：匹配0个或任意个任意的字符

_：匹配一个特定的运算符

在SQL的like是不支持正则的，只能简单的使用%和_;

对于数据库来说，like是一个比较低效的操作，在实际开发中，使用要节制一些

j，NULL 的查询

k，分页查询

select*查询容易查询的数据过多，数据库容易挂，更稳妥的的方式是分页查询

select 列名 from 表名 limit N；

从下标为3开始，显示5个长度

进阶：

a，查询+插入

将一个表里的内容查询后直接放到另一个表中，这时要保证两个表中的列数/类型/顺序要相匹配，列名不需要相同

但是如果列的顺序不一致，也可以指定列插入

b，聚合查询

需要搭配集合函数进行查询

（1）count

先执行select*from，再根据上述结果计算count（）

如果是count（*）中有空值，那么空值的那一行也会被计数。但是如果count（某列），遇到空值则不会进行计数。

计数时去重:distinct

(2)sum

select sum(列名+列名.....)from grade;

对一列进行求和,但要确保这一列是数字

在遇到null时,会直接跳过,不参与运算

(3)avg

在遇到null时,会直接跳过,不参与运算

(4)max/min

在遇到null时,会直接跳过,不参与比较

(5)group by

使用group by,制定一个列,就会把列,就会把列的值相同的行,归到一组,分完组后,还可以针对每个组,分别进行集合查询

(6)分组前加条件:

select role,avg(salary)from emp where name!="张三" group by role;

这时,在统计平均薪资时,少了"张三的数据"

(7)分组后加条件:

select role,avg(salary) from emp group by role having avg(salary)<50000;

相结合:

select role,avg(salary) from emp where name!="张三" group by role having avg(salary)<50000;

c,联合查询

内连接

(1)明确信息来自那张表

(2)把学生表和分数表进行笛卡尔积

(3)按照链接条件进行筛选,删除无意义数据

(4)指定其他条件

(5)删除多余的列

1)许仙同学的成绩:

mysql> select sn,name,score from student,score where student.id=score.student_id and student.name="许仙";

mysql> select sn,name,score from student join score on student.id=score.student_id and student.name="许仙";

2)所有同学的总成绩,及个人信息:

mysql> select sn,name,student.qq_mail,sum(score) from student,score where student.id=score.student_id group by student.name;

3)学生课程信息:

mysql> select sn,student.name,course.name as course_name ,score from student,score,course where student.id=score.student_id and course.id=score.course_id;

mysql> select sn,student.name,course.name as course_name ,score from student join score on student.id=score.student_id join course on course.id=score.course_id;

用要慎重!!由于笛卡尔积会产生大量的"中间结果"此时就会对性能影很大,有时甚至会把数据库搞挂

外连接:

如果两个表的数据是一一对应的,那么内连接和外连接的结果是一样的,但是数据不在一一对应,那么内连接和外连接就会不同

内连接:只包含两张表中同时具备的数据

外连接不支持from这种写法,给join 前面加上left/right就表示外连接

左外连接:

左连接就是以左表为基准,能够确保为左表的数据都呈现在最终的结果里,如果左表中的数据在右表中没有对应的记录,此时就会把右表中的相关字段改为null

右外连接:

自连接:

自己和自己进行笛卡尔积;

sql中进行的条件查询,是针对两个列之间进行比较,不能两个行进行比较

自连接本质上就是把行关系转换成列关系(这时一个经典的数学思维,把未知问题转换为已知问题)

我们需要给这个表前后取不同的别名,指定连接条件

以比较学生的不同科目的成绩比较为例:

mysql> select s1.student_id,s1.score,s2.score from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1 and s1.score>s2.score;

子查询(原则上不推荐使用):

单行子查询:

查询不想毕业同学的同班同学

mysql> select classes_id from student where name="不想毕业";

mysql> select name from student where classes_id=1 and name!="不想毕业";

两行sql合并就构成了子查询:

mysql> select name from student where classes_id=(select classes_id from student where name="不想毕业") and name!="不想毕业";

多行子查询:

mysql> select id from course where name="语文";

mysql> select id from course where name="英文";

mysql> select *from score where course_id in(4,6);

三行sql合并就构成了子查询:

mysql> select *from score where course_id in( select id from course where name="语文"or name="英文");

exists,子查询本身就不推荐使用,exists非常消耗时间,背后会触发大量的硬盘IO操作,并且代码理解起来比较复杂

合并查询:

可以针对不同表

union 会去重,union all,不会去重

要求合并双方列的类型,个数,顺序匹配,列名不做要求

约束

——数据库这样的程序，对于保存的数据进行合法性检查这样的机制，一旦发现问题，数据库就能直接作出反馈

1）NOT NULL

——指示某列不能存储 NULL 值。

not null不仅插入的时候有效，修改的时候同样有效

2）UNIQUE

—— 保证某列的每行必须有唯一的值。

插入/修改之前，先进行查询。

3）DEFAULT

—— 规定没有给列赋值时的默认值。

4）PRIMARY KEY（主键）

—— NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

不允许一张表中，存在多个主键：

逐渐也会在插入/修改前进行查询。

MySQL提供了一个自动分配主键值方式，“自增主键”插入数据时，可以不指定id列的值，让数据库自动分配，就会按照1,2,3……

写的时候id为null，系统会自动分配……

在MySQL给每一个表中都记录的了一个“自增逐渐的最大值”，后续继续插入数据，无论之前的最大值是否存在，都会根据之前保存的最大值，继续往后分配

自增主键只针对int/bigint整数，但有可能会用字符串作为主键（数据量特别大的时候，不是一个MySQL数据库，而是多个MySQL构成的分布式“集群”，这样就引入了多个机器，每个机器上只储存一部分数据，但我们仍希望这些数据的id是唯一的，所以这时MySQL的自增主键就难以胜任力，因为他只能在一个机器上的MySQL生效，不能跨主机，此时，就会把id指定成一个字符串类型，然后通过应用程序里面的一系列算法，生成一个“唯一的字符串id”生成的时候有很多不同的方法，例如uuid/雪花算法……）