MySQL——增删改查进阶

阿国是也

已于 2022-08-05 20:41:27 修改

阅读量274

点赞数 1

文章标签： mysql 数据库 java

于 2022-08-02 18:00:21 首次发布

本文链接：https://blog.csdn.net/weixin_63056061/article/details/126106332

版权

四，查询（聚合函数：count,sum,max,min,avg）

7.联合查询（笛卡尔积——列数相加，行数相乘）

一，数据库的约束

1.not null——指定列非空

create table student (
id int not null,
sn int ,
name varchar(20),
qq_mail varchar(20)
);

2.unique——指定值唯一

create table student (
id int ,
sn int unique,
name varchar(20),
qq_mail varchar(20)
);

使用unique约束，数据库自动给对应列创建索引

3.default——默认值约束

create table student (
id int ,
sn int ,
name varchar(20) default ' 未命名',
qq_mail varchar(20)
);

4.primary key——主键约束

create table student (
id int primary key auto_increment,
sn int ,
name varchar(20) ,
qq_mail varchar(20)
);

1.相当于 unique+not null

2.一个表中只能有一个（分布式部署时不能保证不重复）

3.常与 auto_increment 搭配使用，可自动增加值，也支持手动分配.

手动分配后，按手动分配后新数据继续自增

生成公式=（时间戳+机房编号/主机编号+随机因子）==>计算哈希值

5.foreign key——外键约束

create table classes (
id int primary key auto_increment,
name varchar(20),
`desc` varchar(100)
);

create table students (
id int primary key auto_increment,
sn int unique,
name varchar(20),
qq_mail varchar(20),
classes_id int,
foreign key (classes_id) references classes(id)
);

父表为classes，子表为students

在子表里创建父表的伪id

1.子表存在，不能直接删父表

2.被约束的不能直接删除

拓展：逻辑删除

将数据标记为无效

6.check约束——了解

create table students (
id int primary key auto_increment,
sn int unique,
name varchar(20),
sex varchar(20),

check (sex='男' or sex='女')
);

使用时不报错且自动忽略

二，表的设计

1.主要思路

（1）.根据需求，找到“实体”

（2）.梳理实体间的关系

一对一

一对多

1.学校宿舍管理系统，要求包含宿舍信息，学生信息，每日的宿舍查房记录。

create table dormitory(

id int primary key,

  number varchar(20)

);

create table student(

id int primary key,

  name varchar(20),

  dormitory_id int,

  foreign key (dormitory_id) references dormitory(id)

);

多对多

需要额外一个关联表

三，新增

可以把查询的结果插到另一个表中

insert into 表1 select * from 表2 [where ];

四，查询（聚合函数：count,sum,max,min,avg）

对某某列的行进行操作

1.count——计算行数

select count(列/*)from 表

注意：

count(列)，若该列为null，则不计算；若该列为*，所有列为空也计算

2.sum——若干行之间进行加和

select sum(列名) from 表名；

空值不参加运算，直接跳过

3.avg——求若干行平均数

4.max——求若干行最大值

5.min——求若干行最小值

6.group by——分组查询

(1).分组

select 列名 from 表名 group by 列名;

(2). 在分组时进行操作

select 列, count(*) from 表 group by 列;

产生select 所需列，尽量不要使用*，否则分组产生的数据只显示第一个出现的行

count()

avg(),max(),min()和count类似

(3).分组之前筛选

select 列, avg(*) from 表 wher 条件 group by 列;

(4).分组之后筛选

select 列, avg(*) from 表 group by 列 having 条件;

HAVING是从分组结果中筛选数据的（行），而不是筛选列的

7.联合查询（笛卡尔积——列数相加，行数相乘）

联合查询/多表查询=笛卡尔积+连接条件+其他条件

（1）.未做数据约束

select * from 表1 ,表2;

（2）.排除错误数据

select * from 表1 ,表2 where 条件;

eg:

select * from students, classer where students.id=classes.id; () 表.列

（3）.jion on 写法

select * from 表1 join 表2 on 条件;

（4）.具体聚合步骤

1.先笛卡尔积

select * from 表1,表2 ;

2.再加上连接条件（一般是对id进行去重）

select * from 表1,表2 where 表1.id=表2.id ;

3.再加上其他限制条件，留下你需要的内容

select * from 表1,表2 where 表1.id=表2.id and 条件/group by ;

4.最后改变 select 后的 * 为所需要的列

select 列1,列2/sum(列),avg(列)... from 表1,表2 where 表1.id=表2.id and 条件/group by ;

（5）.内外链接

1.内外连接区别

如果两张表数据对应，内外连接基本相同，甚至没有区别

如下所示：

反之，情况不同，内外连接区别明显

2.内连接

select * from 表1，表2 where 条件;

select * from 表1 join 表2 on 条件;

select * from 表1 inner join 表2 on 条件;

3.外链接

外连接分为左外连接和右外连接。

左外连接：左侧的表完全显示

select * from 表名1 left join 表名2 on 连接条件;

表1完全显示，表2若无表1对应数据，填充为NULL

右外连接：右侧的表完全显示

select * from 表名1 right join 表名2 on 连接条件;

表2完全显示

（6）.自连接

自连接是指在同一张表连接自身进行查询，比较特殊，因条件查询一般为列操作，而自连接可行操作，即——将行转换为列

但因其可读性差，运行开销成本高，故实际中执行多个SQL来完成比较

8.子查询/嵌套查询

其本质就是将多个查询语句组合成一个查询语句

（1）.单行子查询

返回一行记录的子查询

select * from student where classes_id=(select classer_id from student where name=' xxx');

（2）.多行子查询

返回多行记录的子查询

[not] in

-- 使用in
select * from score where course_id in (select id from course where name='语文' or name='英文');

-- 使用 not in
select * from score where course_id not in (select id from course where name!='语文' and name!='英文');

[not] exists

-- 使用 exists
select * from score sco where exists (select sco.id from course cou
where (name='语文' or name='英文') and cou.id = sco.course_id);

-- 使用 not exists
select * from score sco where not exists (select sco.id from course cou
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

exists可能速度较慢，延迟较高，故一般使用 In ，但当数据特别大时，可以使用exists