[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’
⛅个人推荐：
c语言从初阶到进阶
JavaEE详解
数据结构
⚡学好数据结构，刷题刻不容缓：点击一起刷题
🌙心灵鸡汤：总有人要赢，为什么不能是我呢

💻💻💻数据库约束

🔭🔭🔭约束类型

• not null: 指示某列不能存储 NULL 值
• unique: 保证某列的每行必须有唯一的值
• default: 规定没有给列赋值时的默认值.
• primary key: 确保某列(或两个列多个列的结合) 有唯一标识,有助于更容易查找表的特定的记录.
• foreign key: 保证一个表中的数据匹配另一个表中的参照的完整性.
• check: 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略
  CHECK子句.

🔭🔭🔭NULL 约束

创建表时,可以指定某个列不为空:

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

🔭🔭🔭UNIQUE: 唯一约束

指定某一列是唯一的,不重复的:

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

🔭🔭🔭DEFALUT: 默认值约束

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

-- 重新设置学生表结构
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: 主键约束

指定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来使用。插入数据对应字段不给值时，使用最大
值+1。

🔭🔭🔭FOREIGN KEY:

外键用于关联其他表的主键或唯一键:

foreign key (字段名) references 主表(列)

案例:

创建班级表classes，id为主键

-- 创建班级表，有使用MySQL关键字作为字段时，需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
 id INT PRIMARY KEY auto_increment,
 name VARCHAR(20),
 `desc` VARCHAR(100)
);

创建学生表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)
);

🔭🔭🔭CHECK约束（了解）

MySQL使用时不报错，但忽略该约束：

drop table if exists test_user;
create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

💻💻💻表的设计

对于表的设计我们大体上可以分为三类:

• 一对一
  

• 一对多
  

• 多对多

创建课程表:

create table course(
  id  int primary key 
  name varchar(20)); 

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

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);

💻💻💻新增:

插入查询结果

案例：创建一张用户表，设计有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;

💻💻💻查询

聚合查询
常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

案例:

• 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 的总分，没有结果，返回 NULL
SELECT 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;

🔭🔭🔭GROUP BY 子句

使用场景:
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查
询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函
数中。

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;

🔭🔭🔭HAVING

在我们使用 GROUP BY 对数据进行分类之后,如果还想对数据进行过滤,就不能直接使用 WHERE 处理数据
此时我们就可以使用 HAVING 对数据进行进一步的过滤.

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

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