数据库 = MySQL单表查询以及多表关系详解

进行sql语句进行查询，使用聚合函数，sql进行分组查询，添加主键，外键，唯一，非空约束

表之间的关系以及外键约束

 一.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,'马云',58,'男','杭州',56,90),
(2,'胖东来',56,'男','漯河',50,90),
(3,'平煤',32,'男','郑州',56,90),
(4,'董明珠',30,'男','株洲',66,79),
(5,'马景涛',48,'男','香港',76,67),
(6,'杨惠妍',34,'女','惠州',86,56),
(7,'马化腾',73,'女','深证',96,83),
(8,'刘得花',29,'男','香港',46,91),
(9,'马明哲',65,'男','深证',57,70),
(10,'猪八戒',26,'男','高老庄',58,80),
(11,'白骨精',38,'女','白虎岭',58,92),
(12,'唐僧',56,'男','长安',59,90),
(13,'沙僧',35,'男','流沙河',36,90),
(14,'孙悟空',78,'男','花果洞',96,90);

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 ='女'; 
# 分组
-- 按性别分组
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;

总结：

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实现

主键约束

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

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;

唯一约束

作用：限定某一列的值不能重复，可以出现多个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);

非空约束

作用：限定某一列的值不能为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);

默认值

作用：限定某一列的默认值，再没有指定的情况下所有列的默认值为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);

三.表与表之间的关系

1概述：

现实生活中，实体与实体之间肯定是有关系的，我们在设计表的时候，那这种关系就应该体现在表与表之间的这种关系。

简称：关系型数据库

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

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

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

2.实现

一对多：

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

-- 一对多

-- 班级表（主表）
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;

 

多对多：

-- 多对多

-- 课程表（主表）
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);

 

一对一：

-- 一对一

-- 公司表
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,'北京');

四.外键约束：

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

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 约束名;

其实在企业开发中，我们很少用外键约束，一般也就是传统的 项目中我们需要用外键约束，其他项目基本不用。

案例：

表结构：

--创建数据库travel
CREATE DATABASE travel;

--创建类别表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(32),
	CODE VARCHAR(16)
)

--创建线路表
CREATE TABLE lineroute(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(64),
	CODE VARCHAR(16),
	category_code INT
)
--给类别，线路表增加数据
INSERT INTO category VALUES(1,'中国',1);
INSERT INTO category VALUES(2,'美国',2);
INSERT INTO category VALUES(3,'英国',3);

INSERT INTO lineroute VALUES(1,'中国',1,1);
INSERT INTO lineroute VALUES(2,'美国1',2,2);
INSERT INTO lineroute VALUES(3,'美国2',3,2);
--查询线路表
SELECT * FROM lineroute;
--给线路表添加外键
ALTER TABLE lineroute ADD CONSTRAINT category_code_fk FOREIGN KEY(category_code) REFERENCES category(id);

--创建用户表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(32) NOT NULL,
	age VARCHAR(16) DEFAULT '0',
	sex VARCHAR(16) DEFAULT '男',
	phone VARCHAR(32) UNIQUE NOT NULL
)

--插入用户数据
INSERT INTO USER VALUES(1,'张三','25','女','18900126456');

SELECT * FROM USER;

INSERT INTO USER (NAME,age,sex ,phone) VALUES('李三','23','男','18935126456'),('三丰','55','女','18398764567'),('三胖','21','男','12345678909');



SELECT * FROM USER;

SELECT * FROM lineroute;

SELECT * FROM category;

//关于用户表与线路表多对多的关系，=创建中间表，收藏表
CREATE TABLE favorite(
	lineroute_id INT,
	user_id INT,
	PRIMARY KEY(lineroute_id,user_id)
)

--添加数据
INSERT INTO favorite VALUES(1,5);
INSERT INTO favorite VALUES(1,6);
INSERT INTO favorite VALUES(1,7);
INSERT INTO favorite VALUES(2,6);
INSERT INTO favorite VALUES(3,7);

SELECT * FROM favorite;

--添加外键约束
ALTER TABLE favorite ADD CONSTRAINT f_id_fk FOREIGN KEY(lineroute_id) REFERENCES lineroute(id);
ALTER TABLE favorite ADD CONSTRAINT u_id_fk FOREIGN KEY(user_id) REFERENCES USER(id);