MySQL入门门门🚪[第二篇]

MongieLee
209 阅读10分钟

第一篇的传送门：
MySQL入门门门🚪[第一篇]（附Docker使用教程）

连接查询

  • 从一张表单独查询成为单表查询
  • 连接查询是指 联合两张及以上表进行查询成为连接查询

SQL语法，以出现年份命名

  • SQL92：1992年出现的语法
  • SQL99：1999年出现的语法 着重使用SLQ99编写，SQL92了解即可
笛卡尔积现象

是指多表查询时没有设置条件限制，就会出现查询结果为表条数的乘积

A表100条数据，B表100条数据，当对两张表连接查询并且不设置条件则查询结果为10000条

插播两个知识点

快速复制一张表

create table [表明] as select * from t_user; 根据查询结果创建了一张新表，表的字段同步，但会丢失字段约束等

image.png

这里快速复制了一张t_user表并命名为t_user_cp，再进行表查询可以发现表创建成功，并显示了3行数据受影响（因为被复制的表只有3行）

为了演示，再进行一次不带条件的连接查询

image.png

这里使用了SQL92语法，在多张表之间用逗号隔开，可以查出总共9条数据（a表3条*b表3条），这个现象就称之为笛卡尔积现象

将查询结果当输入插入到目标表中

insert into [表明] from select xxx 了解即可

内连接之等值连接 (SQL99语法）

为了后面的演示，再上一篇的铺垫数据后，再新建一张表并插入对应的数据（部门表）

-- 建表
create table t_department(
    id int primary key auto_increment,
    d_name varchar(20) unique,
    level int
);
-- 插入数据
insert into t_department (d_name,level) values('XFB',1),('STB',2),('CWB',3);

-- 在原有的t_user表进行增加字段操作
alter table t_user add column dept_id int;

-- 用户表与部门表关联（一对多）
update t_user set dept_id = 1 where id <= 2;
update t_user set dept_id = 2 where id > 2;

老规矩：要么一段一段执行，要么批量导入，参考上一篇

连表条件是等值关系，所以称为等值连接
select [...查询字段] from [表名] inner join [表名] on 条件
，其中的inner join可以缩写成join，字段名应该给表起别名并且使用表明[字段名]进行访问，比起SQL92在语法上更明朗，且不会占用where关键字，SQL92的条件是写在where关键字后面

image.png

通过join连表，on条件筛选，别名[属性名]进行访问，查询出每个用户的对应所在部门的名字

内连接之非等值连接

条件不是等量关系，称为非等值连接

image.png

连表查询找出用户id>1的用户名和部门名，但这份数据并没有实用性，所以加上用户的部门id=部门的部门id才是目标预期要查询的数据

image.png

内连接之自连接

要点在于一张表看成两张表，其他用法一致

外连接

内连接的问题在于只会查询出符合查询条件的数据，而外连接则保证了主表的数据完整性

  • left join 左外连接，将左边的表当成主表，顺序关联查询右边的表（主表的数据行数必定齐全）
  • right join 右外连接，将右边的表当成主表，顺序关联查询左边的表 left和right是可以替换使用的，作用都一致，就看选择用那个关键字，完整写法是left outer join，outer和内连接的inner一样可以忽略

这个示例先将用户id为1的部门id设为空，然后再进行外连接，可以看到t_user被当成主表，即使id为的1用户并不符合on的条件也会被查出来，只是字段值为null

image.png

多表连接

select * from 表a left join 表b on 条件 left join 表c...以此类推
可以看成先把表a和表b进行关联查询，再将表a和表c进行关联查询
多表查询可以内连接外连接混用

需要注意，不管内连接还是外连接，都不会减少匹配次数，该匹配100次还是100次，只是避免了笛卡尔积这种结果现象
所以尽量降低连接表的次数，可以提高查询效率，且尽量给表设置别名，通过别名.字段名访问，否则会去连接的表中各自寻找字段

子查询

也称为嵌套查询 子查询分别可以用在select，from，where关键字后面

where子句中的子查询

select * from t_user where age > (select avg(age) from t_user);

查询出当前用户表中所有年龄大于当前用户表平均年龄的数据，(select avg(age) from t_user)假设查询结果为20，那么就等价于select * from t_user where age > 20

from子句中的子查询

可以将查询结果当作为一张临时表去看待 select a.*,b.c_name class_name from (select id,s_name student_name,c_id class_id from t_student) a join t_class b on a.class_id = b.id;

示例纯粹是为了演示用from的子查询用法：

(select id,s_name student_name,c_id class_id from t_student)会查出学生id，学生名，班级id，再把这份查询结果当成临时表去内连接班级表，查询出学生表中的班级id等于班级表id的数据，并显示班级名称

image.png

select子句中的子查询

用的不太多，了解下语法即可，select子查询的结果只能返回一个字段，大于一条就会报错

select a.s_name student_name,(select b.c_name class_name from t_class b where b.id = a.c_id) class_name from t_student a;

示例的查询结果和from子查询作用相同

image.png

union 合并结果集

union会将两个及以上的结果集合并操作，并且要求结果集的字段一致，字段位置一致，列数一致 拥有重复数据时，默认会去重，如果不想去重可以使用union all，union进行结果合并使用的是加法

select s_name,c_id from t_student where c_id = 1 union select s_name,c_id from t_student where c_id = 2;

先查出班级id为1的结果再去合并查询结果是班级id为2的结果

image.png

limit 数据切割

作用为将查询结果的一部分取出，最常见的应为就是数据分页，比如百度搜索时，是按照当前页码多少，每页多少条，切换页码刷新数据的交互，这部分就是通过limit去实现的数据获取

写法格式为

  1. limit startIndex offset length 开始的下标从0开始，即第一条的下标为0
  2. limit startIndex,length 不写offset则可以用逗号替代，第一种写法等价
  3. limit length 当startIndex被忽略时，相当于默认写了0,length 关于页数也页码的计算方式，一般来说前端传给后端的数据格式会类似这样：pageNo=1?pageSize=10 当拿到后需要进行SQL的拼接此时，limit的计算方式如下
    limit （页码-1）* 页数 offset 页数 传入pageNo=1?pageSize=10时就是limit （1-1) * 10 offset 10得到的就是前十条，其他传参以此类推

实际业务处理时，还可以在接收参数时，去判断前端是否传了参数，或者传递参数符不符合要求，比如pageNo传了小于1的值，可以进行默认值和参数修正的操作

select * from [表名] limit ... 上一篇中说到了select，from，where，group by having ...，order by的执行顺序，limit操作顺序为最后

image.png

根据id倒序排序后，从第二条数据截取，截取数为两条

约束 Constraint

可以给表的字段加上约束，保证数据的完整性，有效性，添加约束有两种方式

  • 添加在字段列后面的叫列级约束
  • 添加在全部列声明后面的叫表级约束
非空约束 not null

被约束的字段值不能为空：create table t_teat(name varchar(10) not null);
name字段的值不能为null（需要注意null和空字符串不一样，null表示什么都没有）,在尝试插入空值时，会抛出字段不能为null的异常信息

image.png

唯一性约束 unique

被约束的字段值不能有相同重复的：create table t_teat(name varchar(10) unique);
虽然不能重复，但允许为null，重复的数据无法被成功存储，会抛出字段值重复异常信息

image.png

上面的示例为字段约束，使用表级约束则可以添加联合约束
create table t_test(name varchar(10),email varchar(10),unique(name,email));
示例中有俩字段，unique(name,email)表示这张表中name字段和email字段的值加起来不能重复

主键约束 primary key

被添加为主键约束的字段称为主键字段，对应的值为主键值。
主键类似于人的身份证号码，可能某俩人的出生年月，性别，姓名都一样，但是身份证是绝对的唯一，用主键来保证数据的唯一性，被添加primary key后的字段不能为null，也不能重复，每张表都应该有主键，没有主键的表无效。

由于主键拥有不为空且不重复的特性，所以某个字段同时添加了not null和unique约束，该字段会自动称为主键，且每张表只能存在一个主键字段

主键又分为业务主键和自然主键

  1. 自然主键是一个自然数，或者说唯一标识，和业务本身没有关系
  2. 业务主键和业务紧密关联 实际开发推荐使用自然主键，业务主键的缺点在于如果业务发生变动，则会影响主键值，自然主键除了保证数据不重复以外没有其他意义，mysql中可以用通过auto_increment关键字让mysql自动维护主键值，表示自增从1开始以1递增

create table t_test(id int primary key auto_increment,name varchar(10) unique);

主键亦可使用表级约束来实现联合约束，这种主键称为复合主键，不推荐使用，主键尽量使用列级单约束，主键类型推荐为定长的数据类型（ing，bingint，char）,下方示例为列级约束联合约束复合主键
create table t_test(id int auto_increment,name varchar(10) unique, primary key(id,name));

外键约束 foreign key

被添加为外键约束的字段称为外键字段，对应的值为外键值。
假设有个需求需要存储学生和班级的信息数据，如果数据都存在一张表中，就会造成数据冗余且不好维护，这种情况下应该分表，并通过外键进行表字段关联，如学生表只存储学生基本信息加一个班级id的字段，为了保证这个字段被随便插入数据，需要为字段添加外键约束，此时该字段的值只能是引用表字段已有的值，如外键为班级表的id。

在进行表操作时

  • 如果是创建，则应该先创建父表（被关联的表）再创建子表（需要主动引用其他表的表）
  • 如果是删除，则应该先删除子表（需要主动引用其他表的表）再删除父表（被关联的表）
  • 子表对外键引用的字段要求不一定是主键，但至少是拥有unique约束不能重复的值，否则外键就失去了意义，外键值可以为null
-- 先创建父表班级表
create table t_class(
    id int primary key auto_increment,
    c_name varchar(30) unique
);
-- 插入班级数据
insert into t_class(c_name) values('one class'),('two class'),('three class'),('four class');

-- 创建子表班级表
create table t_student(
    id int primary key auto_increment,
    s_name varchar(5) unique,
    c_id int,
    foreign key(c_id) references t_class(id)
);
-- 插入学生数据
insert into t_student(s_name,c_id) values('jack',1),('mike',2),('ben',3),('susan',4);

当尝试插入不存在的班级id时（示例为不存在的班级id 100），会抛出错误

image.png

即使用图形化工具操作时，外键的值也只能从关联表的字段值中取

image.png

第二篇先🚪到这 ： )

avatar
文章
阅读
粉丝