MySQL查询&约束&多表.png

回顾

1. 数据库介绍
    MySQL软件-->多个仓库-->多张表-->多条记录
    实体和表关系
        一个实体（Student）对应一张表
        一个对象（new Student）对应一条记录
        
2. mysql安装和使用
    软件有一个核心配置文件：my.ini
    dos窗口操作
        登录
            mysql -u用户名 -p密码
        退出
            exit
    sqlyog操作
    
3. SQL
    结构化查询语言，通过它提供的sql语句，可以实现对记录的增删改查【CRUD】
    比如：MySQL特有的注释 # （方言）
    
    DDL：操作库和表
    DML：操作数据的增删改
    DQL：操作数据的查询
    DCL：操作用户和权限
    TCL：操作事务相关
    
4. 基本练习
    DDL
        操作库
            create database 数据库名;
            drop database 数据库名;
            use 数据库名;
            select database();
        操作表
            create table 表名(
               列名 数据类型,
               列名 数据类型,
               列名 数据类型
            );
                varchar(长度)
                decimal(m,n)
            show tables;
            desc 表名;
            drop table 表名;
            
   DML
        insert into 表名(字段1,字段2..)values(值1,值2....);
        insert into 表名values(值1,值2....);    
        update 表名 set 字段=值,字段=值 where 条件;       
        delete from 表名 where 条件;    
   DQL
        select * from 表名;
        关系
        逻辑
        in关键字
        between关键字
        空值关键字

MySQL查询&约束&多表

今日目标

1. DQL高级查询
    
2. 数据库约束

3. 表关系【重点】

一 DQL高级查询

准备数据

-- DQL语句 单表查询
create database day19;

use day19;

-- 创建表
CREATE TABLE student (
  id int,
  name varchar(20),
  age int,
  sex varchar(5),
  address varchar(100),
  math int,
  english int
);
-- 插入记录
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65),
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);

1.1 排序

1. 语法：
        select ... from 表名 order by 排序列 [asc|desc],排序列 [asc|dex]
            asc：升序 （默认值）
            desc：降序
            
2. 注意：
        多字段排序，后面的排序结果是在前面排序的基础之上

# 排序
-- 查询所有数据,使用年龄降序排序
SELECT * FROM student ORDER BY age DESC;
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM student ORDER BY age DESC,math DESC;

1.2 聚合函数

作用：对一列数据进行计算，返回一个结果，忽略null值

* 语法：
        count(列名)：统计一列个数
        max(列名)：求出一列的最大值
        min(列名)：求出一列的最小值
        sum(列名)：对一列求和
        avg(列名)：求出一列的平均值

# 聚合函数
-- 查询学生总数（null值处理）
SELECT COUNT(id) FROM student;
SELECT COUNT(english) FROM student;
SELECT COUNT(*) FROM student;
-- 查询年龄大于40的总数
-- 1.1 查询年龄大于40
SELECT * FROM student WHERE age >40;
-- 1.2 总数
SELECT COUNT(*) FROM student WHERE age >40;

-- 查询数学成绩总分
SELECT SUM(math) FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student;
-- 查询数学成绩最高分
SELECT MAX(math) FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student;

1.3 分组

作用：对一列数据进行分组，相同的内容分为一组，通常与聚合函数一起使用，完成统计工作

1. 语法：
        select 分组列 from 表名 group by 分组列 having 分组后的过滤条件;
        
2. where和having区别
        where在分组前进行条件过滤，不支持聚合函数
        having在分组后今天条件过滤，支持聚合函数

-- 统计男生和女生各有多少人
-- select count(*) from student where sex ='女'; 我想一条sql语句实现
# 分组
-- 按性别分组
SELECT sex FROM student GROUP BY sex;
-- 查询男女各多少人
SELECT sex,COUNT(*) FROM student GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
-- 1.1 查询年龄大于25岁的人
SELECT * FROM student WHERE age >25;
-- 1.2 按性别分组
SELECT sex FROM student WHERE age >25 GROUP BY sex;
-- 1.3 统计每组的人数
SELECT sex,COUNT(*) FROM student WHERE age >25 GROUP BY sex;

-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex,COUNT(*) FROM student WHERE age >25 AND COUNT(*)  >2 GROUP BY sex; -- 错误

SELECT sex,COUNT(*) FROM student WHERE age >25 GROUP BY sex HAVING COUNT(*)>2; -- 正确

1.4 分页

1. 语法：
        select ... from 表名 limit 开始索引,每页显示个数;
        
2. 索引特点：
        所以是从0开始，0也是默认值，可以省略
        
3. 分页索引公式：
        索引 = (当前页-1) × 每页个数

# 分页
-- 查询学生表中数据，显示前6条
SELECT * FROM student LIMIT 0,6;
SELECT * FROM student LIMIT 6;
-- 查询学生表中数据，从第三条开始显示，显示6条
SELECT * FROM student LIMIT 2,6;
-- 模拟百度分页，一页显示5条
-- 第一页
SELECT * FROM student LIMIT 0,5;
-- 第二页
SELECT * FROM student LIMIT 5,5;
-- 第三页
SELECT * FROM student LIMIT 10,5;

1.5 知识小结

sql语句执行顺序问题

select * from 表名 where 条件 group by 分组 having 分组后条件 order by 排序 limit 分页;

二 数据库约束

2.1 概述

作用

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类

1. primary key:主键约束【掌握】  要求表中有一个字段 唯一 且 非空，通常我们使用id作为主键

2. unique：唯一约束

3. not null：非空约束

4. default：默认值

5. foreign key：外键约束

2.2 实现

2.2.1 主键约束

作用：限定某一列的值非空且唯一， 主键就是表中记录的唯一标识。

1. 设置主键约束
    1）创建表
        create table 表名(
          id int primary key,
          ...
          ...
        );
    2）已有表
        alter tabe 表名 add primary key(id);
        
2. 特点：
        一张表只能有一个主键约束，但是我们可以设置联合主键（多个字段）
        
3. 自增器
    1）创建表【掌握】
        create table 表名(
            id int priamry key auto_increment,
            ...
            ...
        );
    2）特点：自增器起始值为1，可以手动指定
        alter table 表名 auto_increment=起始值;      
        
4. 删除主键约束【忘掉....】
    语法：
        alter table 表名 drop primary key;
        
    1）先移出自增器
        alter table stu3 modify id int;
    2）才能删除主键约束
        alter table stu3 drop primary key;
        
    解释：因为只有主键约束才有意义设置自增器...（保证唯一性....）

-- 主键约束
-- 给student表添加主键约束
ALTER TABLE student ADD PRIMARY KEY(id);

-- 创建表时指定主键约束
CREATE TABLE stu1(
 id INT PRIMARY KEY,
 `name` VARCHAR(32)
);
-- 插入数据测试
INSERT INTO stu1 VALUES(1,'jack');
-- Duplicate entry '1' for key 'PRIMARY' 错误：主键不能重复
INSERT INTO stu1 VALUES(1,'lucy');
-- Column 'id' cannot be null 错误：主键不能为空
INSERT INTO stu1 VALUES(NULL,'lucy');

-- 我想让name字段，也作为主键使用...
-- Multiple primary key defined -- 错误：主键被重复定义了
ALTER TABLE stu1 ADD PRIMARY KEY(`name`);



-- 联合主键（主键字段完全相同，在进行约束的限定）
CREATE TABLE stu2(
 id INT ,
 `name` VARCHAR(32),
 PRIMARY KEY(id,`name`)
);
-- 插入数据测试
INSERT INTO stu2 VALUES(1,'jack');
INSERT INTO stu2 VALUES(1,'lucy');
-- Duplicate entry '1-lucy' for key 'PRIMARY' 错误
INSERT INTO stu2 VALUES(1,'lucy');


-- 自增器
CREATE TABLE stu3(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32)
);
-- 插入数据测试
INSERT INTO stu3 VALUES(1,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
INSERT INTO stu3 VALUES(3,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');
INSERT INTO stu3 VALUES(10,'jack');
INSERT INTO stu3 VALUES(NULL,'jack');

-- 设置自增器起始值
ALTER TABLE stu3 AUTO_INCREMENT=1000;
INSERT INTO stu3 VALUES(NULL,'jack');

-- delete（橡皮擦） 和 truncat（撕纸） 区别
DELETE FROM stu3;
INSERT INTO stu3 VALUES(NULL,'jack');


TRUNCATE TABLE stu3;
INSERT INTO stu3 VALUES(NULL,'jack');


-- 1）先移出自增器
    ALTER TABLE stu3 MODIFY id INT;
-- 2）才能删除主键约束
    ALTER TABLE stu3 DROP PRIMARY KEY;

2.2.2 唯一约束

作用：限定某一列的值不能重复，可以出现多个null

1. 创建表时设置唯一约束
        create table 表名(
            列名 数据类型 unique,
            ...
            ...
        );

-- 唯一约束
CREATE TABLE stu4(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32) UNIQUE 
);

INSERT INTO stu4 VALUES(1,'jack');
-- Duplicate entry 'jack' for key 'name' 错误：名称重复了
INSERT INTO stu4 VALUES(2,'jack');
INSERT INTO stu4 VALUES(3,NULL);
INSERT INTO stu4 VALUES(4,NULL);

2.2.3 非空约束

作用：限定某一列的值不能为null

1. 创建表时设置非空约束
        create table 表名(
            列名 数据类型 not null,-- 非空约束
            列名 数据类型 unique not null,-- （唯一+非空）
        
        );

疑问：唯一 + 非空 = 主键？？

回答：不等于，主键约束一张表只能有一个，唯一+非空 设置多个

-- 唯一+非空
CREATE TABLE stu5(
 id INT PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(32) UNIQUE NOT NULL
);

INSERT INTO stu5 VALUES(1,'jack');
-- Column 'name' cannot be null 错误：名称不能为空
INSERT INTO stu5 VALUES(2,NULL);

2.2.4 默认值

作用：限定某一列的默认值，再没有指定的情况下所有列的默认值为null

1. 创建表设置默认值
        create table 表名(
          列名 数据类型 default 默认值,
          ...
          ...
        );

-- 默认值
CREATE TABLE stu6(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32),
  sex VARCHAR(5) DEFAULT '男'
);
INSERT INTO stu6(id,`name`) VALUES(1,'小张');
INSERT INTO stu6(id,`name`,sex) VALUES(2,'小刘','女');
-- 因为我们指定了默认值为男，你再插入null，会把默认值覆盖...
INSERT INTO stu6 VALUES(3,'小王',NULL);

三 表关系【重中之重】

3.1 概述

现实生活中，（班级）实体与（学生）实体之间肯定是有关系的，那么我们在设计表的时候，就应该体现出（班级）表与（学生）表之间的这种关系！

简称：关系型数据库（Relation DBMS）

1. 一对多
    应用场景：
        班级和学生、部门和员工
    解释：
        一个班级下面有多名同学，多名同学属于某一个班级

2. 多对多
    应用场景：
        老师和学生、学生和课程
    解释：
        一名老师可以教导多名学生，一名学生可以被多个老师教导

3. 一对一
    应用场景：
        公民和身份证号、公司和注册地
    解释：
        一个公民只能有一个身份证号，一个身份证号对应一个公民

3.2 实现

3.2.1 一对多

* 举例：班级和学生

1588132385848.png

-- 创建新库
CREATE DATABASE day19_pro;
USE day19_pro;

-- 一对多

-- 班级表（主表）
CREATE TABLE class(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32)
);
INSERT INTO class VALUES(1,'java一班');
INSERT INTO class VALUES(2,'java二班');

-- 学生表（从表）
CREATE TABLE student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32),
  class_id INT -- 外键字段
  
);
INSERT INTO student VALUES(1,'流川枫',1);
INSERT INTO student VALUES(2,'樱木花道',1);
INSERT INTO student VALUES(3,'大猩猩',2);
INSERT INTO student VALUES(4,'赤木晴子',2);

-- 通过班级找学生
SELECT * FROM student WHERE class_id =1;

-- 通过学生找班级
SELECT * FROM class WHERE id = 2;


-- 给学生表添加外键约束
ALTER TABLE student ADD CONSTRAINT class_id_fk FOREIGN KEY(class_id) REFERENCES class(id);

-- 删除学生表的外键约束
ALTER TABLE student DROP FOREIGN KEY class_id_fk;

3.2.2 多对多

* 举例：学生和课程

1588142266257.png

-- 多对多

-- 课程表（主表）
CREATE TABLE course(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(32)
);
INSERT INTO course VALUES(1,'java');
INSERT INTO course VALUES(2,'ui');
INSERT INTO course VALUES(3,'美容美发');
INSERT INTO course VALUES(4,'挖掘机');
-- 中间表（从表）
CREATE TABLE sc(
  s_id INT,
  c_id INT,
  PRIMARY KEY(s_id,c_id)
);
INSERT INTO sc VALUES(1,1);
INSERT INTO sc VALUES(1,2);
INSERT INTO sc VALUES(2,1);
INSERT INTO sc VALUES(2,3);

-- 联合主键，可以帮我们校验重复选修问题
INSERT INTO sc VALUES(1,1);


-- 给中间表增加外键约束
ALTER TABLE sc ADD CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id);
ALTER TABLE sc ADD CONSTRAINT c_id_fk FOREIGN KEY(c_id) REFERENCES course(id);

-- 流川枫不能选修，不存在的课程
INSERT INTO sc VALUES(1,6);

3.2.3 一对一

• 一对一关系在实际开发中用的并不多，因为可以办关联字段设计在同一张表...

* 公司和注册地

1588143207597.png

-- 一对一

-- 公司表
CREATE TABLE company(
 id INT PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(32)
);
INSERT INTO company VALUES(1,'拼多多');
INSERT INTO company VALUES(2,'传智播客');
-- 地址表
CREATE TABLE address(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 同时也作为外键
 `name` VARCHAR(32),
 CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES company(id)
);
INSERT INTO address VALUES(1,'上海');
INSERT INTO address VALUES(2,'江苏沭阳');

3.3 外键约束

作用：限定二张表有关系的数据，保证数据的正确性、有效性和完整性

1. 在从表中添加外键约束
    1）创建表
        create table 表名(
            列名 数据类型,
            [constraint] [约束名] foreign key(外键列) references 主表(主键)
        );      
    2）已有表
        alter table 表名 add [constraint] [约束名] foreign key(外键列) references 主表(主键);
        
2. 外键约束特点
        1）主表不能删除从表已引用的数据
        2）从表不能添加主表未拥有的数据
        3）先添加主表数据再添加从表数据
        4）先删除从表数据再删除主表数据
        5）外键约束允许为空但不能是错的
        
3. 删除外键约束
        alter table 表名 drop foreign key 约束名;

在企业开发过程中：传统的项目中我们需要外键约束，互联网项目绝对不用......（性能问题）

3.4 讲义中有课下作业

javaWeb综合案例黑马旅游网一部分的数据库设计，放在今天去玩一玩

• 分类和线路是一对多关系
• 用户和线路是多对多的关系（favorite 收藏表【中间表】）