MySQL表的增删改查(进阶)

这篇具有很好参考价值的文章主要介绍了MySQL表的增删改查(进阶)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

目录

数据库约束

约束的定义

约束类型

null约束

unique:唯一约束

default:默认值约束

primary key:主键约束(重要)

foreign key:外键约束(描述两个表之间的关联)

表的设计

一般思路

三大范式

一对一

一对多

​编辑

多对多

​编辑

新增

查询

聚合查询

聚合函数

group by字句

having

联合查询(也称多表查询)

内连接

外连接

自连接

子查询

合并查询


数据库约束

约束的定义

定义:创建表时,给这个表制定一些规则,后续插入/修改/删除都要保证数据能够遵守这些规则.

引入约束的规则,是为了更强的数据检查/校验.数据一定要保证正确性.(如果后续插入/修改的数据不符合要求,就会报错)

执行效率(代码在机器上跑的快不快)

开发效率(程序员写代码所花费的时间)

我们讲约束也是牺牲了执行效率,换来了开发效率.因为开发效率更重要:因为人力成本高于机器硬件成本.

约束类型

共分为not null, unique, default, primary key, check这几种

null约束

定义:指示某列不能存储null值

在创建表时,就是指定某列不为空,当指定列插入null会报错

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT NOT NULL ,
  sn INT ,
  name VARCHAR ( 20 ),
  qq_mail VARCHAR ( 20 )
);

unique:唯一约束

定义:保证某列的每行必须有唯一的值.

指定sn为唯一的,不重复的(即在这一列都不会出现):

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT NOT NULL ,
  sn INT UNIQUE,
  name VARCHAR ( 20 ),
  qq_mail VARCHAR ( 20 )
);

default:默认值约束

定义:规定没有给列赋值时赋默认值.(默认情况下的默认值设置为null)

指定插入数据时,name列为空,默认值为unknown

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT NOT NULL ,
  sn INT UNIQUE,
  name VARCHAR ( 20 ) DEFAULT 'unkown' ,
  qq_mail VARCHAR ( 20 )
);

primary key:主键约束(重要)

定义:not null和unique的结合.确保某列(或两个列,多个列的组合)有唯一标识(就是一个记录的身份标识),有助于更容易更快速地找到表中的一个特定记录.(通常用xxx id作为主键,通常一个表中只有一个主键).

指定id列为主键:

-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT NOT NULL PRIMARY KEY,
  sn INT UNIQUE,
  name VARCHAR ( 20 ) DEFAULT 'unkown' ,
  qq_mail VARCHAR ( 20 )
);
自增主键的介绍:auto_increment(对id(主键)的+1操作)
不需要用户自己指定(可以放个null),可交给数据库进行分配(1,2,3...)
其实,程序员可以自行设定id亦可
每次使用null方式插入自增主键时,都是数据库根据这一列的最大值,在这个基础上,继续递增,不会重复利用之前上述的自增主键,只能在当前单个数据库下生效
MySQL表的增删改查(进阶),mysql,数据库
如果数据库是由多个mysql服务器构成集群时,自增主键就无效了
在实际业务当中,引入的主键是不希望重复的.
id INT PRTMARY KEY auto_increment,

foreign key:外键约束(描述两个表之间的关联)

定义:保证一个表中的数据匹配另一个表中的值的参照完整性.
外键用于关联其他表的主键或者唯一键,语法:
foreign key (字段名) references 主表(列)
-- 字段名是指本表的哪个列
-- 主表是指被关联的表
-- 列就是被关联的表的哪个列
注:外键约束是写到最后,把前面的定义好之后,在最后通过foreign key创建外键约束.
案例:
创建班级表classes,id为主键:
-- 创建班级表,有使用 MySQL 关键字作为字段时,需要使用 `` 来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
        id INT PRIMARY KEY auto_increment,
        name VARCHAR ( 20 ),
        `desc` VARCHAR ( 100 )
);
设计要求:学生表中的class_id都在班级表中存在
创建学生表student,一个学生对应一个班级,一个班级对应多少学生.使用id为主键,classes_id为外键,关联班级表id
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id INT PRIMARY KEY auto_increment,
  sn INT UNIQUE,
  name VARCHAR ( 20 ) DEFAULT 'unkown' ,
  qq_mail VARCHAR ( 20 ),
classes_id int ,
FOREIGN KEY (classes_id) REFERENCES classes(id)
); 

1.把约束别的表的表称为"父表".

   把被约束的表,称为"子表".

2.外键约束,也是双向的,要想删除父表的记录,就必须先删除子表中的相关数据,以确保子表中没有数据.

3.使用外键约束时,操作子表,要查询父表;操作父表,也要查询子表.子表和父表中被引用的列都要带有"索引".表里有了主键之后,就会自动创建出索引,加快查询速度

4.扩展知识:对电脑上的文件进行删除,也只是逻辑上的删除,不是真的删除.把文件删除掉,其实也是在系统中,把硬盘对应的盘块数据标记为无效了(所以不是真正意义上的删除) 

表的设计

一般思路

 1.先根据需求,找到实体(一些关键性质的对象),梳理清需求,提取出关键字的名字,一般来说,每个实体,都得安排个表.

2.梳理实体间的关系(不同关系下,有不同设计表的方式),造句,向里面套,能套上哪个,就用哪种方式创建数据表(定式)

三大范式

一对一

MySQL表的增删改查(进阶),mysql,数据库

方案一:搞一个大表,把这些信息都放在一起(前提是这两个表都很简单(即列很少,可以考虑合并))

方案二:分两个表,使用id引用过来,建立联系(可用于表很复杂的情况) 

一对多

MySQL表的增删改查(进阶),mysql,数据库

MySQL表的增删改查(进阶),mysql,数据库

多对多

MySQL表的增删改查(进阶),mysql,数据库

创建课程表

-- 创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course (
  id INT PRIMARY KEY auto_increment,
  name VARCHAR ( 20 )
);

创建学生课程中间表,考试成绩表

-- 创建课程学生中间表:考试成绩表
DROP TABLE IF EXISTS score;
CREATE TABLE score (
  id INT PRIMARY KEY auto_increment,
  score DECIMAL ( 3 , 1 ),
  student_id int ,
  course_id int ,
  FOREIGN KEY (student_id) REFERENCES student(id),
  FOREIGN KEY (course_id) REFERENCES course(id)
);

新增

插入查询结果

语法(将插入语句和查询语句结合到一起了):

insert into table_name [(column [, column...])] select ... 

-- 查询结果的集合就代替了values

案例:创建一张用户表,设计有name姓名,email邮箱,sex性别,mobile手机号字段.需要把已有的学生数据复制进来,可以复制的字段有name, qq_mail.

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
  id INT primary key auto_increment,
  name VARCHAR ( 20 ) comment ' 姓名 ' ,
  age INT comment ' 年龄 ' ,
  email VARCHAR ( 20 ) comment ' 邮箱 ' ,
sex varchar ( 1 ) comment ' 性别 ' ,
mobile varchar ( 20 ) comment ' 手机号 '
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

查询

聚合查询

定义:就是"行和行"之间的运算,但此处行之间的运算具有一定限制,不像表达式查询(表达式查询是操作列).

聚合函数

使用聚合函数时,列和列之间,已经被"打散了",如果查询中包含聚合函数,和非聚合的列,各自是各自的.大部分情况,聚合的列和非聚合的列是不能混用的,一种情况除外(group by(后面讲))

函数 说明
count([distinct] expr) 返回查询到数据的数量
sum([distinct] expr) 返回查询到的数据的总和,不是数字则没有意义
avg([distinct] expr) 返回查询到数据的平均值,不是数字则没有意义
max([distinct] expr) 返回查询到数据的最大值,不是数字则没有意义
min([distinct] expr)

返回查询到数据的最小值,不是数字则没有意义

案例:

count:

-- 统计班级共有多少同学
SELECT COUNT (*) FROM student;
SELECT COUNT ( 0 ) FROM student;
-- 统计班级收集的 qq_mail 有多少个, qq_mail NULL 的数据不会计入结果
SELECT COUNT (qq_mail) FROM student;
注意:当使用count(*) 时,即使是全为null的行,也会被记录次数
        当使用count(列名)时,如果中间有为null的行,则不会被记录次数

sum:

-- 统计数学成绩总分
SELECT SUM (math) FROM exam_result;
-- 不及格 < 60 的总分,没有结果,返回 NULL
SELECT SUM (math) FROM exam_result WHERE math < 60 ;
注意:
1.sum函数会将null自动忽略
2.sql很多时候会将字符串当作数字进行算术运算,都会将字符串转为数字
(eg.允许"100"转换为100)
avg:
-- 统计平均总分
SELECT AVG (chinese + math + english) 平均总分 FROM exam_result;

max:

-- 返回英语最高分

SELECT MAX (english) FROM exam_result;

min:

-- 返回 > 70 分以上的数学最低分
SELECT MIN (math) FROM exam_result WHERE math > 70 ;

group by字句

在select中使用group by可以对指定列进行分组查询.需要满足:使用group by进行分组查询时,select指定的字段必须是"分组依据字段", 其它字段如想出现在select中,必须包含在聚合函数中.

实际效果就是把这个指定的列,值相同的记录划分到一组,针对这些组就可以分别聚合查询了,分组操作,往往是和"聚合"配合使用的.

select column1, sum(column2), .. from table group by column1,column3;

 -- column1是分组依据

案例:

准备测试表及数据:职员表,有id(主键), name(姓名), role(角色), salary(薪水)

create table emp(
id int primary key auto_increment,
name varchar ( 20 ) not null ,
role varchar ( 20 ) not null ,
salary numeric ( 11 , 2 )
);
insert into emp(name, role, salary) values
( ' 马云 ' , ' 服务员 ' , 1000.20 ),
( ' 马化腾 ' , ' 游戏陪玩 ' , 2000.99 ),
( ' 孙悟空 ' , ' 游戏角色 ' , 999.11 ),
( ' 猪无能 ' , ' 游戏角色 ' , 333.5 ),
( ' 沙和尚 ' , ' 游戏角色 ' , 700.33 ),
( ' 隔壁老王 ' , ' 董事长 ' , 12000.66 );

查询每个角色的最高工资,最低工资和平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role;

having

group字句进行分组之后,需要对分组结果再进行条件过滤时,不能使用where语句,而需要用having语句.(即分组之前的条件,使用where表示;分组之后的条件,使用having表示)

显示平均工资低于1500的角色和它的平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role
having avg(salary)< 1500 ;

联合查询(也称多表查询)

所谓多表联合查询,核心操作为笛卡尔积,然后指定一些条件之类的,来实现需求中的一些查询结果.

实际开发中往往数据来自不同的表,所以需要多表联合查询.多表查询是对多张表的数据取笛卡尔积:

MySQL表的增删改查(进阶),mysql,数据库

笛卡尔积列数为之前列数之和,行数为两个表的行数之积.

但实际上,笛卡尔积得到的结果,绝大部分是无效数据.

 注意:关联查询可以对关联表使用别名.

让我们来看一下下面的案例(可以尝试自己敲一下):

insert into classes(name, `desc`) values
( ' 计算机系 2019 1 ' , ' 学习了计算机原理、 C Java 语言、数据结构和算法 ' ),
( ' 中文系 2019 3 ' , ' 学习了中国传统文学 ' ),
( ' 自动化 2019 5 ' , ' 学习了机械自动化 ' );
insert into student(sn, name, qq_mail, classes_id) values
( '09982' , ' 黑旋风李逵 ' , 'xuanfeng@qq.com' , 1 ),
( '00835' , ' 菩提老祖 ' , null , 1 ),
( '00391' , ' 白素贞 ' , null , 1 ),
( '00031' , ' 许仙 ' , 'xuxian@qq.com' , 1 ),
( '00054' , ' 不想毕业 ' , null , 1 ),
( '51234' , ' 好好说话 ' , 'say@qq.com' , 2 ),
( '83223' , 'tellme' , null , 2 ),
( '09527' , ' 老外学中文 ' , 'foreigner@qq.com' , 2 );
);
insert into course(name) values
( 'Java' ),( ' 中国传统文化 ' ),( ' 计算机原理 ' ),( ' 语文 ' ),( ' 高阶数学 ' ),( ' 英文 ');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
( 70.5 , 1 , 1 ),( 98.5 , 1 , 3 ),( 33 , 1 , 5 ),( 98 , 1 , 6 ),
-- 菩提老祖
( 60 , 2 , 1 ),( 59.5 , 2 , 5 ),
-- 白素贞
( 33 , 3 , 1 ),( 68 , 3 , 3 ),( 99 , 3 , 5 ),
-- 许仙
( 67 , 4 , 1 ),( 23 , 4 , 3 ),( 56 , 4 , 5 ),( 72 , 4 , 6 ),
-- 不想毕业
( 81 , 5 , 1 ),( 37 , 5 , 5 ),
-- 好好说话
( 56 , 6 , 2 ),( 43 , 6 , 4 ),( 79 , 6 , 6 ),
-- tellme
( 80 , 7 , 2 ),( 92 , 7 , 6 );
注意:在进行多表查询时,始终贯穿着这四部曲(由繁入简),筛选出符合条件的数据:
1.笛卡尔积
2.连接条件
3.根据需求指定其他条件(筛选行)
4.针对列进行精简(筛选列)

内连接

定义:是内连接查询中一种特殊的等值连接,所谓的自连接就是指表与其自己当前表进行连接。自己和自己做连接。

语法:

select 字段 from 表1 别名1 [inner] join 表2  别名2 on  连接条件 and 其他条件;

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;(个人最推荐第二种,嘎嘎好用)

案例:

(1)查询许仙同学的成绩

select sco .score from student stu inner join score sco on stu .id =sco .student_id
and stu .name = ' 许仙 ' ;
-- 或者
select sco .score from student stu, score sco where stu .id =sco .student_id and
stu .name = ' 许仙 ' ;
(2)查询所有同学的总成绩,以及同学的个人信息
-- 成绩表对学生表是多对 1 关系,查询总成绩是根据成绩表的同学 id 来进行分组的
SELECT
stu .sn ,
stu .NAME ,
stu .qq_mail ,
sum( sco .score )
FROM
student stu
JOIN score sco ON stu .id = sco .student_id
GROUP BY
sco .student_id ;

(3)查询所有同学的成绩,以及同学的个人信息:

-- 查询出来的都是有成绩的同学, 老外学中文 同学 没有显示
select * from student stu join score sco on stu .id =sco .student_id ;
-- 学生表、成绩表、课程表 3 张表关联查询
SELECT
stu .id ,
stu .sn ,
stu .NAME ,
stu .qq_mail ,
sco .score ,
sco .course_id ,
cou .NAME
FROM
student stu
JOIN score sco ON stu .id = sco .student_id
JOIN course cou ON sco .course_id = cou .id
ORDER BY
        stu.id;

外连接

外连接分为左外连接和右外连接.如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接. 外连接也是多表查询的一种表现形式,一般使用比较少,属于特殊情况特殊处理. 

语法:

-- 左外连接,表一完全显示

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

-- 右外连接,表二完全显示

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

为了形象展示内连接,左外连接和右外连接的区别,下面我们来看这一组图:

MySQL表的增删改查(进阶),mysql,数据库

案例:查询所有学生的成绩,及同学的个人信息,如果该同学没有成绩,也需要显示

-- “ 老外学中文 同学 没有考试成绩,也显示出来了
select * from student stu left join score sco on stu .id =sco .student_id ;
-- 对应的右外连接为:
select * from score sco right join student stu on stu .id =sco .student_id ;
-- 学生表、成绩表、课程表 3 张表关联查询
SELECT
stu .id ,
stu .sn ,
stu .NAME ,
stu .qq_mail ,
sco .score ,
sco .course_id ,
cou .NAME
FROM
student stu
LEFT JOIN score sco ON stu .id = sco .student_id
LEFT JOIN course cou ON sco .course_id = cou .id
ORDER BY
stu .id ;

自连接

顾名思义,自连接就是指在同一张表中进行查询.

案例:

显示所有"计算机原理"成绩比"Java"成绩高的成绩信息(在这个例子中,要想完成不同科目的比较,就需要比较行之间的大小,因为sql是无法实现这个功能的,所以只能将行转为列)

-- 先查询 计算机原理 “Java” 课程的 id
select id,name from course where name= 'Java' or name= ' 计算机原理 ' ;
-- 让我们使用四部曲来写一下
(1)将所有内容进行笛卡尔积
select * from score as s1, score as s2;
(2)连接条件:两张表student_id相同
select * from score as s1, score as s2 where s1.student_id = s2.student_id;
(3)只筛选出要比较的课程id的数据
 select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1;
(4)找出结果
 select * 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;

子查询

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询(这个查询方法虽然很装逼但不建议用,所以了解这个即可,自己写的时候尽量不要这样写)

举个例子:查询与"不想毕业"同班的同学:

select * from student where classes_id=( select classes_id from student where
name= ' 不想毕业 ' );

合并查询

在实际应用中,为了合并多个select的执行结果,可以使用集合操作符union, union all.使用union和union all时,前后查询结果集中,字段需要一致.(合并查询其实用的很少,了解即可)

案例:查询id 小于3,或者名字为英文的课程文章来源地址https://www.toymoban.com/news/detail-754413.html

select * from course where id< 3
union
select * from course where name= ' 英文 ' ;
-- 或者使用 or 来实现
select * from course where id< 3 or name= ' 英文 ' ;

到了这里,关于MySQL表的增删改查(进阶)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • node+vue3+mysql前后分离开发范式——实现对数据库表的增删改查

    大家好,我是yma16,本文分享关于 node+vue3+mysql前后分离开发范式——实现对数据库表的增删改查。 技术选型 前端:vite+vue3+antd 后端:node koa 数据库:mysql koa是一个现代的Node.js框架,可以用来构建Web应用程序。 Vue.js 是一款用于构建用户界面的开源JavaScript框架。Vue.js 3 是 Vu

    2024年02月20日
    浏览(38)
  • MySQL--MySQL表的增删改查(进阶)

    check 聚合查找 count sum average max min 我们这里先构造出多张表 查询lisi同学的成绩 来自student和来自score c 增加名字这一条件 查询所有同学的总成绩以及个人信息 来自score和来自student 查询所有同学的各科成绩以及个人信息 来自student,course和score 简化后的结果如下: 除了上述的

    2024年02月09日
    浏览(39)
  • MySQL表的增删改查(进阶)

    目录 数据库约束 约束的定义 约束类型 null约束 unique:唯一约束 default:默认值约束 primary key:主键约束(重要) foreign key:外键约束(描述两个表之间的关联) 表的设计 一般思路 三大范式 一对一 一对多 ​编辑 多对多 ​编辑 新增 查询 聚合查询 聚合函数 group by字句 having 联合查询

    2024年02月05日
    浏览(44)
  • 4.MySQL表的增删改查(进阶)

    大家好,我是晓星航。今天为大家带来的是 MySQL表的增删改查(进阶) 相关的讲解!😀 NOT NULL - 指示某列不能存储 NULL 值。 UNIQUE - 保证某列的每行必须有 唯一 的值。 DEFAULT - 规定没有给列赋值时的默认值。 PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结

    2024年02月09日
    浏览(52)
  • 【MySql】数据库的增删改查

    本篇的主要目的:对于数据库如何去增加删除查询修改 主要细节在于选项问题,编码选项 说明: 大写的表示 [] 是可选项 CHARACTER SET: 指定数据库采用的字符集 COLLATE: 指定数据库字符集的校验规则 查看当前用户数据库的列表show databases; 创建数据库create database db_name; 当

    2024年02月12日
    浏览(46)
  • 【MYSQL数据库的增删改查操作-超市管理系统】

    MYSQL数据库的增删改查 创建数据库 选择数据库 删除数据库 创建数据表 删除数据表 插入数据 更新数据 查找数据 以超市管理系统为例,包含员工表,库存表,仓库表 数据库:db_supermarket 数据表:s_employee #员工表;s_stock #库存表;s_storehouse #仓库表 插入数据之后生成表如下:

    2024年02月09日
    浏览(49)
  • 【MySQL】数据库的增删改查、备份、还原等基本操作

    大写的表示 [] 是可选项 CHARACTER SET: 指定数据库采用的字符集 COLLATE: 指定数据库字符集的校验规则 创建数据库的时候,有两个编码集: 1.数据库编码集——数据库未来存储数据 2.数据库校验集——支持数据库进行字段比较使用的编码,本质也是一种读取数据库中数据采

    2024年02月08日
    浏览(74)
  • MySql学习2:SQL分类、数据库操作、表操作、数据的增删改查

    SQL分类: DDL:数据定义语言,用来定义数据库对象(数据库、表、字段) DML:数据操作语言,用来对数据库表中的数据进行增删改 DQL:数据库查询语言,用来查询数据库表中的记录 DCL:数据控制语言,用来创建数据库用户、控制数据库的访问权限 查询所有数据库 查询当前

    2024年02月11日
    浏览(51)
  • Django学习记录:使用ORM操作MySQL数据库并完成数据的增删改查

    数据库操作 MySQL数据库+pymysql Django开发操作数据库更简单,内部提供了ORM框架。 安装第三方模块 ORM可以做的事: 1、创建、修改、删除数据库中的表(不用写SQL语句)。【无法创建数据库】 2、操作表中的数据(不用写SQL语句)。 1、自己创建数据库 1)启动MySQL服务 2)自带

    2024年02月14日
    浏览(59)
  • 【MySQL】 MySQL表的增删改查(基础)

    本节目标: CRUD : Create, Retrieve,Update,Delete 新增数据 查询数据 修改数据 删除数据 注释:在SQL中可以使用“–空格+描述”来表示注释说明 CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写 语法格式: 创建案例如下: 代码如下: 结果如下: 代

    2024年02月07日
    浏览(45)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包