4/29day44_MySql查询&约束&多表

2020-05-15 本文已影响0人蹦蹦跶跶的起床啊

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 收藏表【中间表】）

4/29day44_MySql查询&约束&多表

回顾

MySQL查询&约束&多表

一 DQL高级查询

1.1 排序

1.2 聚合函数

1.3 分组

1.4 分页

1.5 知识小结

二数据库约束

2.1 概述

2.2 实现

2.2.1 主键约束

2.2.2 唯一约束

2.2.3 非空约束

2.2.4 默认值

三表关系【重中之重】

3.1 概述

3.2 实现

3.2.1 一对多

3.2.2 多对多

3.2.3 一对一

3.3 外键约束

3.4 讲义中有课下作业

猜你喜欢

热点阅读

4/29day44_MySql查询&约束&多表

回顾

MySQL查询&约束&多表

一 DQL高级查询

1.1 排序

1.2 聚合函数

1.3 分组

1.4 分页

1.5 知识小结

二 数据库约束

2.1 概述

2.2 实现

2.2.1 主键约束

2.2.2 唯一约束

2.2.3 非空约束

2.2.4 默认值

三 表关系【重中之重】

3.1 概述

3.2 实现

3.2.1 一对多

3.2.2 多对多

3.2.3 一对一

3.3 外键约束

3.4 讲义中有课下作业

猜你喜欢

热点阅读

二数据库约束

三表关系【重中之重】