sql高级操作

校对集：可以通过数据库，和数据表两种方式（库选项和表选项）

注意：如果想要使得表中的校对集生效，那么必须在建表之初就设置好对应的校对集（后面的修改无效）

bin是区分大小写

 

修改表结构：修改校对集不会影响表原来的校对原理

 

乱码：三码合一

1.      保证PHP在操作数据库的时候，使用set names的时候，将字符集设置成与PHP文件字符集相同的字符集即可（保证PHP与数据库不会产生乱码）

2.      保证PHP最终输出结果给浏览器的时候，浏览器能够按照PHP脚本（文件）的字符集进行数据的显示（header：最终结果就是变成html里的meta）

 

数据类型：数值型，字符串型，时间日期型

         数值型：整型和小数型（浮点型和定点型）

         有符号和无符号：默认是有符号，可以设置成无符号：数据类型 unsigned

         显示宽度：不能改变类型所能表示的数据范围，需要配合0填充zerofill

        

         小数型

                   浮点型：float单精度，double双精度，当数据量很大而且对精度要求不高的数据

                   定点型：decimal(M,D)，M表示总长度，D表示小数部分的长度

 

         字符串型：char，varchar，text，blob，enum，set

                   char：定长字符串，分配之后就固定长度，不管有没有数据，最大值是255

                   varchar：变长字符串，长度由实际存储数据来定，需要在数据长度之外，额外的开辟1到2个字节来保存数据的长度信息。最大值是65535（latin1：欧洲编码规则，默认一个字符只占一个字节）

                   text/blob：存储很大的字符串或者二进制数据，不占据数据记录的长度（自己本身要占用10个字节）

                   enum：枚举，可以从设定的多个元素里，只能选择一个存储，保存的数据本身是枚举对应的数值，单选框

                   set：集合，每个元素占用一个位，8个字节最多能存放64个数组，组合出来的值有2 ^ 64

         时间日期型：datetime，timestamp，date，time，year（1901-2155）

 

字段属性（约束）

在字段类型之后，用于对当前字段进行一系列的约束的内容（限制内容的情况）

字段属性：null/not null，primary key，auto_increment，unique key，comment，default

null

表示字段的值可以为空（在进行数据插入的时候，该字段可以不给数据），not null表示不能为空，必须要给定值（不能是null）

default

默认值，系统给出的最多的值是defaultnull。

尽量不要让字段允许为空，因为数据往往需要有值才有意义。

 

使用default关键字来显示的使用系统的默认值

 

primary key

主键，主要的键，用主键修饰的字段，该字段具有唯一性（不能重复）

1.      一张表最多只能有一个主键

2.      主键字段不能为空

 

如何给表添加主键呢？

1.      在创建表的时候，直接在某个字段后添加主键信息

2.      在表后面增加primarykey语句，来包含对应的主键字段

使用该方式，可以使用复合主键（可以使用多个字段共同组成主键）

复合主键

查看复合主键

3.      可以通过修改表结构增加主键

语法：alter table 表名 add primary key(字段列表)

 

修改主键：主键不能被修改。

修改：先删除后增加

删除主键：alter table 表名 drop primary key

 

增加主键

 

auto_increment

自动增长

1.      字段必须是整型

2.      必须保证使用自动增长的字段首先满足的一个条件：是一个索引字段

3.      一张表只能有一个自动增长

4.      自动增长通常是配合逻辑主键（idint primary key auto_increment）

 

自动增长在使用后会出现在表的选项里，而且其值为下一条要自增的字段的值

 

修改自动增长的值

不能修改为已经存在的值

不存在的值可以修改

 

修改不生效

 

注意：auto_increment的值的修改，被修改的新值，必须要比当前表中已经存在的自增长的值要大。

 

可以通过修改自增长配置来实现自增长的控制

 

unique key

唯一键，字段的值不能重复

1.      一张表唯一键可以有多个

2.      唯一键不对null进行处理

 

如何添加唯一键？

跟主键的添加方式基本相同

1.      在字段之后直接跟uniquekey

2.      在表的所有字段之后，使用uniquekey(字段列表)

3.      在修改表结构的时候增加唯一键：altertable 表名 add unique key(字段列表)

 

注意：当唯一键字段不能为null之后，会自动转换成主键（主键只是表现上是主键，实质还是唯一键）

 

唯一键转主键：

1.      该唯一键不能为空

2.      该唯一键是第一个不为空的唯一键

 

唯一键不会去区分null字段

 

删除唯一键

唯一键与主键不同，主键一张表只有一个，而唯一键可能有多个，所谓唯一键的删除，需要额外的指定要删除的唯一键的名字

语法：alter table 表名 drop index 唯一键名字(字段名)

 

comment

注释，是一种字段说明，会永远的保存在表的创建语句中

注释：表示当前代码是注释，不用执行，在语句执行之后，会被忽略掉

1.   两个中划线后跟一个空格，行注释

2.   #也是一种行注释

3.   块注释：/**/

 

 

关系

自然界中实体与实体之间的联系如何在数据库中进行体现。

 

多对一/一对多

1.      需求：有一个学生表一张班级表

一个学生只属于一个教室，一个教室却包含多个学生。这种关系就称之为多对一或者一对多的关系

 

多对多

2.      需求：有一个教师表和一个学生表

一个学生被多个老师教过，一个老师教过多个学生，这个关系就称之为多对多的关系

 

一对一

3.      需求：一个学生信息，有10种信息，其中只有5个信息是非常常用的，其他5个信息基本上用不上（婚姻状况，籍贯，国籍）。

将一个表中常用的数据和不常用的数据分离开来，分别保存，但是使用同样的id进行管理。

 

外键约束

维护一个学生与班级的关系

 

外键定义：外键就是指的是一个表中某个字段的值指向另外一个表的主键。

 

增加外键

在一张表中创建一个字段，该字段指向另外一张表的主键。通过一定的约束语句来指明其联系。

 

外键规则：

1.      外键的字段类型与被指向的表的主键的字段类型一致

2.      如果想要实现on deleteset null，外键字段必须可以为空

 

1.      可以通过在创建表的时候，指定对应的外键

语法：foreign key(当前表中的外键字段) references 外键指向的表(外键指向表的主键);

 

查看表结构

 

查看表创建语句

 

2.      修改表的结构增加外键（指定外键名字）

alter table 表名 add constraint 外键名 foreign key(字段) references 父表(主键)

前提：子表中没有父表中不存在的值

 

外键的作用

父表：外键所指向的那张表称之为父表

子表：外键所在的表就是子表

 

外键的作用主要是用来约束外键所在表的数据的操作以及外键所指向的表的操作

1.      一旦一张表有一个外键字段，那么该表外键字段值，就不能是外键所指向的表中的主键的值之外的值

2.      约束外键所指向的表的操作（父表不能更新和删除）

实际约束

 

修改外键：外键不能被修改，只能先删除后增加

删除外键

一张表中可能存在多个外键，所以在删除外键的时候，必须指定外键名称。

语法：alter table 表名 drop foreign key 外键名字

 

修改外键作用

修改外键的约束作用，修改外键对父表的操作约束。

默认的，对父表的约束操作是不允许删除和更新。

 

修改外键对父表的约束

         置空：set null，当父表进行更新或者删除的时候，对应的子表里的数据应该被设置成null

         级联：cascade，当父表进行更新或者删除的时候，对应的子表里的数据应该被删除或者被更新

 

删除：父表删除有约束记录的时候子表怎么办？

         将子表的外键值设置为null（保证子表的外键字段是允许为空）

更新：父表更新有约束记录的时候子表怎么办？

         将子表的外键值，级联更新

 

在增加外键的时候指定约束方式。

on delete set null on update cascade：当父表删除记录时，子表置空，当父表更新记录时子表跟着更新

约束效果

 

外键的优缺点

优点：从数据库管理者出发

1.      能够保证数据的一致性（父表修改之后，子表跟着修改）

2.      保证数据的安全性（不允许在子表中增加父表中不存在的值）

3.      将表与表之间的关心进行了关联（体现了关系型数据库）

 

从PHP开发者出发

4.      增加了维护的难度

 

实际开发中，一般会使用外键字段，但是不使用外键约束。通过程序来实现表与表之间的关系维护。

外键：更新班级id，对应的学生的班级id也改变

不使用外键：更新班级ID，得到新的班级id和旧的班级id，通过旧的班级id去修改学生的班级id为新的。

 

 

数据高级操作

数据的操作无外乎就是对数据的增删改查

增加数据

基本方式：insert into 表名 [(字段列表)] values (值列表)；

 

主键冲突：在插入数据的时候，主键值已经存在了，但是要求是必须使用该主键字段

 

实现目标：如果该主键不存在那么就增加记录，如果存在，就修改部分字段的值

1.      使用主键冲突方式

语法：insert into 表名 values(值列表) onduplicate key update 字段 = 值

2.      使用替换插入

replace into 表名 (字段列表) values(值列表)

replace其实先删除存在的行，再进行插入。

 

注意：大部分情况下，因为是使用逻辑主键，一般会自增长，所以使用null即可（insert），但是如果是使用业务主键，那么很有可能会存在冲突（replace）。

 

蠕虫复制

蠕虫能够一分为2,2分为4，在成倍增长

语法：insert into 表名 (字段列表) select 字段列表 from 表名;

蠕虫复制能够快速使用表中的数据量增加，没有办法改变数据

蠕虫复制也可以从其他的表中去获取数据。

 

更新数据

基本语法：update 表名 set 字段 = 值 [where条件]

高级语法：update 表名 set 字段 = 值 [where条件]  [limit]

 

删除数据

基本语法：delete from 表名 [where条件]

高级语法：delete from 表名 [where条件] [limit]

 

删除数据并不能修改一张表的索引和自增长。如果想删除表的自增长重新来过

先删除表（drop），再新建表（create）

mysql提供了简单方式实现两个步骤：truncate

 

查询数据

基础语法：select 字段列表 from 表名 [where条件]

高级语法：select [select选项] 表达式 [from子句] [where子句] [group by 子句] [having 子句] [order by子句] [limit 子句]

 

select选项：在查询得到数据是否需要进行合并，all表示获取所有数据（默认的），distinct将完全一致的记录进行合并

 

去重