👨💻作者简介:👨🏻🎓告别,今天
📔高质量专栏 :☕java趣味之旅
📔(零基础)专栏:MSQL数据库
欢迎🙏点赞🗣️评论📥收藏💓关注
💖衷心的希望我的📙作品能给大家带来收获。
🎊前言:
让我们如大自然般🌱悠然自在😊地生活一天吧,
别因为有坚果外壳或者蚊子翅膀落在铁轨上🛤️而翻了车🚆。让我们该起床时🛏️就赶紧起床🛏️,
该休息🛏️时就安心休息🛏️,
保持安宁而没有烦扰的心态;身边的人要来就让他来,要去就让他去,
让钟声🕰️回荡,让孩子👼哭喊—
下定决心好好地过一天。
🙏希望大家都可以过好每一天。也🙏希望我的博客,能给大家带来💯收获。
1. 数据库约束
1.1 约束类型
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- DEFAULT - 规定没有给列赋值时的默认值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句。
1.2 NULL约束
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT,
name VARCHAR(20),
qq_mail VARCHAR(20)
);
当引用NULL约束后,插入操作不能插入空值。
当引用NULL约束后,修改操作不能修改空值。
1.3 UNIQUE:唯一约束
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20),
qq_mail VARCHAR(20)
);
当我们多次插入同一个数据时,数据库报错。
加上UNIQUE之后
每次插入/修改 都会需要先查询
如果发现重复(已经存在)就会插入/修改失败
注意:加上unique之后,执行效率会降低。对数据的检验更严格了,有助于写代码减少出错的概率。(提高了开发的效率)
1.4 DEFAULT:默认值约束
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);
1.5 PRIMARY KEY:主键约束
-- 重新设置学生表结构
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)
);
注意:
在mysql中,主键,还有两个具体要求:
1.不能为null
2.不能重复
一个表只能有一个主键
对于整数类型的主键,常配搭自增长auto_increment来使用。插入数据对应字段不给值时,使用最大值+1。
-- 主键是 NOT NULL 和 UNIQUE 的结合,可以不用 NOT NULLid INT PRIMARY KEY auto_increment,
当然这种情况下也可以手动指定id,不一定非要依赖自增主键;
接下来见证奇迹的时候
当我们插入id为100的数后,默认插入自增为100+1
当我们删除id为100,101的数后,再次默认插入会呈现什么结果?
注意: 自增主键,本质上是 mysql 服务器存储了当前表中的最大id,再进一步进行累加的。
上述插入最大值为101
1.6 FOREIGN KEY:外键约束
foreign key ( 字段名 ) references 主表 ( 列 )
- 创建班级表classes,id为主键:
--创建学生表student,一个学生对应一个班级,一个班级对应多个学生。使用id为主键,
classes_id为外键,关联班级表id
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
create table student (id int primary key auto_increment,name varchar(20),classId int,foreign key (classId) references class(classId));
- 创建学生表student,一个学生对应一个班级,一个班级对应多个学生。使用id为主键,classes_id为外键,关联班级表id
-- 主键是 NOT NULL 和 UNIQUE 的结合,可以不用 NOT NULL
id INT PRIMARY KEY auto_increment,
foreign key (字段名) references 主表(列)
-- 创建班级表,有使用MySQL关键字作为字段时,需要使用``来标识
DROP TABLE IF EXISTS classes;
create table class (classId int primary key auto_increment,className varchar(20));
原则上来说,插入的学生表的数据中的classId必须要在class表中存在,这样插入的学生才有意义(从属于某个班级的)。但在实际情况中,并不会报错,所以我们要引用外键约束
此时,student 的 classid 就和 class 表的 classid 建立了联系。student classid 中的值,必须要在class 表的 classid 中存在。
class 表就对 student 表产生了制约。
此时,就把 class 表,制约别人的表,称为"父表"(parent table)
把student 表,被制约的表,称为"子表"(child table)
受外键约束影响,插入失败
同理,修改操作也一样。
外键约束和unique类似的效果,都是在插入/修改之前,进行查询
当我们删除没有被引用的父表时,不会造成影响,当我们删除被引用了时,MySQL表发生报错
虽然是父亲约束儿子,但实际上儿子也要反向约束父亲,双向奔赴。
外键约束,准确来说是两个表的列产生关联关系。当两个标产生关联关系后,直接删除父表会报错。这是正确的删除方式,是先删子表,再删父表
1.7 CHECK约束(了解)
drop table if exists test_user;create table test_user (id int ,name varchar ( 20 ),sex varchar ( 1 ),check (sex = ' 男 ' or sex= ' 女 ' ));
2. 新增
INSERT INTO table_name [( column [, column ...])] SELECT ...
-- 创建用户表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;
3. 查询
3.1 聚合查询
3.1.1 聚合函数
- COUNT
-- 统计班级共有多少同学SELECT COUNT (*) FROM student;SELECT COUNT ( 0 ) FROM student;-- 统计班级收集的 qq_mail 有多少个, qq_mail 为 NULL 的数据不会计入结果SELECT COUNT (qq_mail) FROM student;
- SUM
-- 统计数学成绩总分SELECT SUM (math) FROM exam_result;-- 不及格 < 60 的总分,没有结果,返回 NULLSELECT SUM (math) FROM exam_result WHERE math < 60 ;
- 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 ;
3.1.2 GROUP BY子句
select column1, sum(column2), .. from table group by column1,column3
- 准备测试表及数据:职员表,有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;
3.1.3 HAVING
- 显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by rolehaving avg(salary)< 1500 ;