MySQL进阶知识

青旅半醒 2023-10-10 18:46 31阅读 0赞

> ### 数据库约束 ###
> 
> #### NULL约束 ####
> 
> ![5eededa4f751411ea23f0d8a83360659.png][]
> 
> create table student (id int not null,
> 
> sn int,
> 
> name varchar(20),
> 
> qq\_mail varchar(20));
> 
> 指定了这个id参数不为空
> 
> #### 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：默认值约束 ####
> 
> 指定插入数据时，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：主键约束 ####
> 
> 主键是啥一会你就知道了
> 
>     drop table if exists student;
>     create table student (id int not null primary key,
>     sn int unique,
>     name varchar(20),
>     qq_mail varchar(20));
> 
> 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
> 
> 对于整数类型的主键，常配搭自增长auto\_increment来使用。
> 
>     id INT PRIMARY KEY auto_increment,
> 
> 插入数据对应字段不给值时，使用最大 值+1。就是 默认来讲 他就会1 2 3 4 5....
> 
> #### FOREIGN KEY：外键约束 ####
> 
> 先创建一个class表 以id为自增主键
> 
>     
>     drop table if exists class;
>     create table class (id int primary key auto_increment,
>     name varchar(20),
>     `desc` varchar(100));
>     
>     
> 
> 再创建一个 student 表 一个class对应多个student
> 
>     drop table if exists student;
>     create table student (id int not null primary key auto_increment,
>     sn int unique,
>     name varchar(20),
>     qq_mail varchar(20),
>     classes_id int,
>     foreign key (classes_id) references class(id));
> 
> 所以主键和外键是搭配使用的
> 
> 你可能会想:那我直接在后面写一个class:一班多好 我还整这么复杂干啥 就是这个classes\_id
> 
> 必须和class(id)一 一对应 假如说我的班级表里面 有一班 二班 那我的学生表里面如果输入错了 写了张三是三班 那就会报错 所以叫 约束

> ### 表的设计 ###
> 
> 1.一对一
> 
> ![7298e6d407d340c0b0e43ec9d2e87c4a.png][]
> 
> 2.一对多
> 
> ![e84f061cd6d542259b82b8d903f7ddbc.png][]
> 
> 3.多对多
> 
> ![9cb8e4835d6e45e4ad391a6eb35fdd36.png][]
> 
> 前面俩其实都很好理解对吧
> 
> 但是多对多是啥玩意呢
> 
> 这里举个例子
> 
>     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));
> 
> 第一个表是课程表 一个课程对应一个表
> 
> 第二个表是成绩表
> 
> 没个学生有一张成绩表 然后成绩表又和课程表对应
> 
> 成绩表作为中间表 把学生和课程联系起来

> #### 复制操作 ####
> 
> 众所周知 我们可以把select和where联合使用 进行条件查询
> 
> 我们还可以把insert和where联合使用 批量插入(复制)
> 
>     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 '手机号'
>     
>     );
> 
> 我们创建一个表 然后把之前student里面的值复制过来
> 
>     insert test_user(name,email) select name,qq_mail from student;

> #### 聚合查询 ####
> 
> 通过聚合函数的方式查询
> 
> ![50c6079146504a3e8e809b46af77e22a.png][]
> 
> 查询有多少个学生信息
> 
> select count(\*) from student;
> 
> (这个\* 就是说所有的列)

> #### GROUP BY子句 ####
> 
> SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查 询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
> 
>     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 语句，而需要用
> 
> 显示平均工资低于1500的角色和它的平均工资
> 
>     select role,max(salary),min(salary),avg(salary) from emp group by role 
>     
>     having avg(salary)<1500;

### 连接 ###

首先我们先准备一下

创建表

班级表

DROP TABLE IF EXISTS classes;
    
    CREATE TABLE classes (
     id INT PRIMARY KEY auto_increment,
     name VARCHAR(20),
     `desc` VARCHAR(100)
    );

学生表

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

课程表

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

这个表的创建和数据的准备都要按顺序来 因为这些主键外键都是有依赖关系的

#### 内连接 ####

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

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

这有两种写法

假如说 我们想查询许仙同学的成绩的话

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='许仙';

来来来 我们来理解一下

需要两个参数

1.成绩在score表中

2.姓名在student表中

所以 我们要联合score表和student表

然后呢 我要查的人 是 许仙stu.name='许仙' 然后再通过主键外键约束拿到他的成绩stu.id=sco.student\_id

select sco.score这个sco就是 我们后面 join score sco 给的别名

![2ed0c1ed0892445b811cb6ab22dd9e46.png][]

[5eededa4f751411ea23f0d8a83360659.png]: https://img-blog.csdnimg.cn/5eededa4f751411ea23f0d8a83360659.png
[7298e6d407d340c0b0e43ec9d2e87c4a.png]: https://img-blog.csdnimg.cn/7298e6d407d340c0b0e43ec9d2e87c4a.png
[e84f061cd6d542259b82b8d903f7ddbc.png]: https://img-blog.csdnimg.cn/e84f061cd6d542259b82b8d903f7ddbc.png
[9cb8e4835d6e45e4ad391a6eb35fdd36.png]: https://img-blog.csdnimg.cn/9cb8e4835d6e45e4ad391a6eb35fdd36.png
[50c6079146504a3e8e809b46af77e22a.png]: https://img-blog.csdnimg.cn/50c6079146504a3e8e809b46af77e22a.png
[2ed0c1ed0892445b811cb6ab22dd9e46.png]: https://img-blog.csdnimg.cn/2ed0c1ed0892445b811cb6ab22dd9e46.png