MySQL——进阶操作

对数据的操作

在建表时可以使用如下关键字对表的结构进行更改

NULL

通过NULL关键字可以指定某一列不能填NULL

create table 表名(列名 类型 not null);

unique

通过unique关键字可以指定某一列的值不能重复

create table 表名(列名 类型 unique);

default

通过default关键字可以指定某一列的默认值

create table 表名(列名 类型 default 默认值);

primary key

这个单词的意思是主键，如同我们的身份证号一样，被primary key修饰的列相当于一个表中的独有的身份证明，其属性是，不能为空，不能重复，是表中每一行所独有的

create table 表名(列名 类型 primary key);

由于一个个指定主键过于麻烦，我们可以用auto_increment来让编译器自己指定，其原理是，我们可以插入时指定为null，编译器会自己寻找指定过的主键的最大值，然后加一进行赋值。也就是说，当我们一直指定null时，我们的主键就是1，2，3…

foreign key

我们可以指定本表中的列名，关联别的表中的主键

create table 表名(列名 类型, foreign key(列名) references 外界表名(外界列名));

check

我们可以通过改关键字，使得在插入数据时对数据的有效性进行检查

create table 表名(列名 类型, check (列名 = 'XXX' or 列名 = 'XXX'));

插入查询的结果

我们可以吧查询到的结果插入到一个表中

insert into 表名(列名 类型) select 类型 from 表名; 

查询

聚合查询

关键字	解释
count(表达式)	查询满足表达式的数据的数量
sum(表达式)	查询满足表达式的数据的总和
avg(表达式)	查询满足表达式的数据的平均值
max(表达式)	查询满足表达式的数据的最大值
min(表达式)	查询满足表达式的数据的最小值

例如：

select sum(english + math) from scores where math > 60;

分组查询

我们可以针对某一列的值进行分组查询，分在一组的成员都是这一列的值相同的

select 查询内容 from 表名 group by 列名;

我们分组的条件用where来筛选，分组后如果我们还需要对分组后的内容进行筛选，那么我们就需要用到having关键字

select 查询内容 from 表名 where 分组条件 group by 列明 having 筛选条件;

联合查询

当我们需要将多个表的内容进行整合的时候，那么就需要进行联合查询，其底层原理是笛卡尔积，简单来讲就是将两个表的数据进行排列组合

select 查询内容 from 表1, 表2... where 条件;

由于我们是简单的排列组合，因此并不是所有的数据都是有效数据，比如说学生表和成绩表。学生表中有张三，1号，还有李四，2号，然后成绩表中有一号，语文70，还有，一号，英语80，还有，二号，语文90，还有，二号，英语99,那么在笛卡尔积的时候就会有2 * 4 = 8种结果，张三1号一号语文70，张三1号一号英语80，张三1号二号语文90，张三2号二号英语99…
不难看出，其中有的数据是把学生表中的张三和成绩表中的李四的学号进行了混合，那么这种数据就是无效数据。因此我们在笛卡尔积的时候就应该加上筛选条件

select * from student, score where student.id = score.id;

可以看出上述代码中我们用到了.操作符，这个操作符和我们结构体访问成员变量的逻辑是一样的，就是访问该表下的数据。

此外，我们的联合查询还有一种方式

select 查询内容 from 表1 join 表2 on 条件;

那么这两种查询方法有什么区别呢？
join on 的查询可以进行外链接，而第一种方法和上述的join on代码是内链接

外链接

左外链接

select 查询内容 from 表1 left join 表2 on 条件

右外链接

select 查询内容 from 表1 right join 表2 on 条件

左外链接就是，假如我们有学生表和成绩表，学生表left join成绩表，如果有的学生学号下没有成绩，那么这位同学也会显示信息，其成绩为null

右外链接就是，假如我们有学生表和成绩表，学生表right join成绩表，如果有的成绩的学号下没有该学生，那么这个成绩的学号也会显示信息，其学生名字为null

自链接

当我们需要按照行来查找表的信息时，就用到自链接，也就是将自己和自己进行笛卡尔积

select 查询内容 from 表1 别名1 join 表2 别名2 on 链接条件;

例如我们有一个成绩表，其中我们要查询语文比英语好的信息

select * from score sco1 join score sco2 on sco1.id = sco2.id and sco1.chinese < sco2.english;

自查询

我们可以把select的查询结果当作条件，再查询其他信息。就像函数的嵌套一样

select * form 表名 where ID = (select ID from 表名 where 条件);

IN

和上一篇blog中的in的用法相同

select * from 表名 where ID in (select ID from 表名 where 条件);

exists

select * from 表名 where ID eixits (select ID from 表名 where 条件);

合并查询

union

用于获得两条查询语句的并集，也就是任意一个查询语句中的内容满足即可，会自动的去掉两个语句中的重复的内容

select 查询内容 from 表名 where 条件
union
select 查询内容 from 表名 where 条件

其实和我们直接在查询条件中加or的效果是相同的

union all

和union一样，不过不会去除重复项