mysql查询详细知识点

末蓝、 2022-04-23 21:00 271阅读 0赞

**目录**

MySql查询和约束

一、==DQL==

1. 条件查询

2. 排序查询

3. 聚合函数

4. 分组查询

5. 分页查询

6. 综合查询

二、备份和恢复

1. SQLyog备份与恢复

2. dos窗口备份与恢复

三、约束

1. 主键约束

2. 唯一性约束

3. 非空约束

4. 默认值约束

5. 外键约束

四、表关系

1. 一对一（了解）

2. 一对多

3. 多对多

内容回顾

--------------------

## 一、==DQL== ##

### 1. 条件查询 ###

1.1 单条件查询

*  运算符有：`>, <, >=, <=, =, <>`。`is null` 和 `is not null`

-- 1. 单条件查询
    -- 1.1 查询所有的男同学
    SELECT * FROM student3 WHERE sex = '男';
    -- 1.2 查询年龄在25岁以上的同学
    SELECT * FROM student3 WHERE age > 25;
    -- 1.3 查询地址不是香港的同学
    SELECT * FROM student3 WHERE address <> '香港';
    -- 1.4 查询英语成绩为空的同学
    SELECT * FROM student3 WHERE english IS NULL;
    SELECT * FROM student3 WHERE english IS NOT NULL;

1.2 多条件

*  连接符有：`and, or, not`

-- 2. 多条件查询
    -- 2.1 查询年龄大于25岁的男同学
    SELECT * FROM student3 WHERE age > 25 AND sex = '男';
    -- 2.2 查询年龄小于25岁的同学，或者英语成绩大于80的同学
    SELECT * FROM student3 WHERE age < 25 OR english > 80;
    -- 2.3 查询年龄不大于25岁的同学
    SELECT * FROM student3 WHERE age <= 25;
    SELECT * FROM student3 WHERE NOT(age > 25);

1.3 范围条件

*  `between 开始值 and 结束值`，包含开始值和结束值
 *  `in(值1, 值2, ....)`

-- 3. 范围查询
    -- 3.1 查询年龄在20到50之间（包含20和50）
    SELECT * FROM student3 WHERE age >= 20 AND age <= 50;
    SELECT * FROM student3 WHERE age BETWEEN 20 AND 50;
    -- 3.2 查询地址是深圳和南京的同学
    SELECT * FROM student3 WHERE address = '深圳' OR address = '南京';
    SELECT * FROM student3 WHERE address IN ('深圳', '南京');

1.4 模糊查询

*  语法：`like '表达式'`。在表达式里可以写：
    
     *  `_`：一个任意字符
     *  `%`：任意个任意字符

-- 4. 模糊查询
    -- 4.1 查询所有姓马的同学
    SELECT * FROM student3 WHERE NAME LIKE '马%';
    -- 4.2 查询姓马的、名称为两个字的同学
    SELECT * FROM student3 WHERE NAME LIKE '马_';
    -- 4.3 查询姓名里含有“德”字的同学
    SELECT * FROM student3 WHERE NAME LIKE '%德%';
    -- 4.4 查询姓名为两个字的同学
    SELECT * FROM student3 WHERE NAME LIKE '__';
    SELECT * FROM student3 WHERE CHAR_LENGTH(NAME) = 3;
    -- 4.5 查询姓名里不包含“德”字的同学
    SELECT * FROM student3 WHERE NAME NOT LIKE '%德%';

### 2. 排序查询 ###

*  语法：`order by 排序字段 排序规则, 排序字段2 排序规则2, ...`。排序规则有：
    
     *  `ASC`：升序，从小到大，默认的
     *  `DESC`：降序，从大到小

-- 5. 排序查询
    -- 5.1 查询所有的同学，按照年龄从小到大排列显示
    SELECT * FROM student3 ORDER BY age ASC
    -- 5.2 查询所有的同学，按照年龄从小到大排列显示；如果年龄相同，按照数学成绩从高到低排序
    SELECT * FROM student3 ORDER BY age ASC, math DESC
    -- 5.3 查询所有的男同学，按照年龄从小到大排列显示；如果年龄相同，按照数学成绩从高到低排序
    SELECT * FROM student3 WHERE sex = '男' ORDER BY age ASC, math DESC

### 3. 聚合函数 ###

*  聚合函数：用于统计的。常用的聚合函数有：
    
     *  `count()`：统计数量
     *  `sum()`：求和
     *  `avg()`：求平均值
     *  `max()`：求最大值
     *  `min()`：求最小值

> 注意：所有聚合函数会忽略null值

-- 6. 聚合函数
    -- 6.1 查询学生表里的数量
    SELECT COUNT(*) FROM student3;
    -- 6.2 查询所有学生的年龄总和
    SELECT SUM(age) FROM student3;
    -- 6.3 查询所有学生的平均年龄
    SELECT SUM(age)/COUNT(*) FROM student3;
    SELECT AVG(age) FROM student3;
    -- 6.4 查询最大年龄
    SELECT MAX(age) FROM student3;
    -- 6.5 查询最小年龄
    SELECT MIN(age) FROM student3;
    
    -- 6.6 所有聚合函数都会忽略null值
    SELECT COUNT(english) FROM student3;
    SELECT AVG(english) FROM student3;
    
    SELECT SUM(english)/COUNT(*) FROM student3;

### 4. 分组查询 ###

*  语法：`group by 分组字段 having 分组后的过滤条件`
 *  注意：
    
     *  select后边，只能跟分组的字段，或者聚合函数
 *  where和having的区别：
    
     *  where在分组前执行；having在分组后执行
     *  where过滤原始表的数据；having过滤分组后的数据
     *  where里不要写聚合函数；having里可以写聚合函数

-- 7. 分组查询
    -- 7.1 分组统计男生和女生的数量
    SELECT sex,COUNT(*) FROM student3 GROUP BY sex;
    -- 7.2 分组统计每个地方的学生数量
    SELECT address, COUNT(*) FROM student3 GROUP BY address;
    -- 7.3 分组统计每个地方的学生数量，只要数量大于1的
    SELECT address, COUNT(*) FROM student3 GROUP BY address HAVING COUNT(*) > 1;
    -- 7.4 分组统计每个地方的男生数量
    SELECT address, COUNT(*) FROM student3 WHERE sex = '男' GROUP BY address;
    -- 7.5 分组统计每个地方的男生数量，只要数量大于1的
    SELECT address, COUNT(*) FROM student3 WHERE sex = '男' GROUP BY address HAVING COUNT(*) > 1;

### 5. 分页查询 ###

*  语法：`limit 起始索引，查询数量`

-- 8. 分页查询：每页3条
    -- 8.1 查询第1页的数据  起始索引：0， 查询数量：3
    SELECT * FROM student3 LIMIT 0, 3;
    -- 8.2 查询第2页的数据  起始索引：3， 查询数量：3
    SELECT * FROM student3 LIMIT 3, 3;

### 6. 综合查询 ###

-- 数据准备
    create table `student3` (
        `id` int (11),
        `name` varchar (60),
        `age` int (11),
        `sex` varchar (15),
        `address` varchar (300),
        `math` int (11),
        `english` int (11)
    ); 
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('1','马云','55','男','杭州','66','78');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('2','马化腾','45','女','深圳','98','87');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('3','马景涛','55','男','香港','56','77');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('4','柳岩','20','女','湖南','76','65');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('5','柳青','20','男','湖南','86',NULL);
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('6','刘德华','57','男','香港','99','99');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('7','马德华','22','女','香港','99','99');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('8','德玛西亚','18','男','南京','56','65');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('9','李彦宏','48','男','湖南','100','100');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('10','丁磊','45','男','湖南','100','99');
    insert into `student3` (`id`, `name`, `age`, `sex`, `address`, `math`, `english`) values('11','东哥','49','男','杭州','95','90');

-- 9. 综合查询
    -- 9.1 分组统计每个地方的男生数量，只要数量大于1的。查询结果按照数量升序显示。 每页2条，显示第1页
    SELECT address, COUNT(*) FROM student3 WHERE sex = '男' GROUP BY address HAVING COUNT(*) > 1 ORDER BY COUNT(*) ASC LIMIT 0,2;

## 二、备份和恢复 ##

### 1. SQLyog备份与恢复 ###

### 2. dos窗口备份与恢复 ###

*  备份（dos命令）：`mysqldump -u用户名 -p密码 数据库名称 > E:\heima63.sql`
 *  恢复（sql语句）：
    
     *  登录MySql
     *  如果库不存在，就创建库；
     *  使用库
     *  执行SQL：`source E:\heima63.sql`

## 三、约束 ##

### 1. 主键约束 ###

*  主键：一张表的唯一标识。原则上：每张表必须有主键，且只能有一个主键
 *  主键约束：
    
     *  特点：被主键约束的字段，值必须是非空、唯一
     *  语法：`primary key`
    
        -- 1. 创建一张商品表product（商品id 主键， 商品名称pname， 商品价格price）
        CREATE TABLE product(
            id INT PRIMARY KEY,
            pname VARCHAR(100),
            price DOUBLE
        );
        
        -- 2. 验证主键的效果：值必须唯一、非空
        INSERT INTO product (id,pname,price) VALUES (1,'Thinkpad T490', 8999);  -- 正常插入
        INSERT INTO product (id,pname,price) VALUES (1,'macbook pro', 15999);   -- 不能插入，主键值重复了
        INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);      -- 不能插入，主键值为空了
 *  主键自增策略：
    
     *  要求：主键字段必须是整数类型，MySql会自动生成主键值：`1,2,3, ... +1`
     *  语法：`primary key auto_increment`
    
        -- 1. 创建一张商品表product（商品id 主键 自增， 商品名称pname， 商品价格price）
        CREATE TABLE product(
            id INT PRIMARY KEY AUTO_INCREMENT,
            pname VARCHAR(100),
            price DOUBLE
        );
        
        -- 2. 验证主键自增的效果：主键不设置值，或者设置为null，MySql会自动生成主键值
        INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999);  -- 正常插入
        INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999);   -- 正常插入
        INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 正常插入

### 2. 唯一性约束 ###

*  效果：唯一性约束的字段，值不能重复，但是可以为null
 *  语法：`字段名 类型 unique`

-- 1. 创建一张商品表product（商品id 主键 自增， 商品名称pname 唯一， 商品价格price）
    CREATE TABLE product(
        id INT PRIMARY KEY AUTO_INCREMENT,
        pname VARCHAR(100) UNIQUE,
        price DOUBLE
    );
    
    -- 2. 验证唯一性约束的效果：值不能重复
    INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999);  -- 正常插入
    INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999);   -- 正常插入
    INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 正常插入
    INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 不能插入，因为pname值不唯一
    INSERT INTO product (id,pname,price) VALUES (NULL,NULL, 5999);             -- 正常插入

### 3. 非空约束 ###

*  效果：非空约束的字段，值不能为null
 *  语法：`字段名 类型 not null`

-- 1. 创建一张商品表product（商品id 主键 自增， 商品名称pname 唯一、非空， 商品价格price）
    CREATE TABLE product(
        id INT PRIMARY KEY AUTO_INCREMENT,
        pname VARCHAR(100) UNIQUE NOT NULL,
        price DOUBLE
    );
    
    -- 2. 验证非空约束的效果：值不能为空
    INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999);  -- 正常插入
    INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999);   -- 正常插入
    INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 正常插入
    INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 不能插入，因为pname值不唯一
    INSERT INTO product (id,pname,price) VALUES (NULL,NULL, 5999);             -- 不能插入，因为pname值为空了

### 4. 默认值约束 ###

*  效果：默认值约束的字段，如果**不指定**值，取默认值（如果指定null值，值就是null，不取默认值）
 *  语法：`字段名 类型 default 默认值`

-- 1. 创建一张商品表product（商品id 主键 自增， 商品名称pname 唯一、非空， 商品价格price 默认值0）
    CREATE TABLE product(
        id INT PRIMARY KEY AUTO_INCREMENT,
        pname VARCHAR(100) UNIQUE NOT NULL,
        price DOUBLE DEFAULT 0
    );
    
    -- 2. 验证默认值的效果：不设置值的时候，取默认值
    INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', NULL);  -- 插入null值，不取默认值
    INSERT INTO product (id,pname) VALUES (NULL,'macbook pro');   -- 正常插入

### 5. 外键约束 ###

*  效果：外键约束的字段，必须从指定表的主键中取值。
 *  目的：保证数据的一致性和完整性，避免脏数据
 *  语法：
    
     *  创建表时设置外键：`[constraint 约束名称] foreign key(外键字段) references 主表(主键)`
     *  已有表里设置外键：`alter table 表名称 add [constraint 约束名称] foreign key(外键字段) references 主表(主键)`

-- 1. 创建一张商品分类表category（主键id， 分类名称cname）
    CREATE TABLE category(
        id INT PRIMARY KEY AUTO_INCREMENT,
        cname VARCHAR(50) NOT NULL UNIQUE
    );
    -- 2. 准备一些商品分类信息
    INSERT INTO category (id,cname) VALUES (NULL, '家用电器'); -- id值是1
    INSERT INTO category (id,cname) VALUES (NULL, '手机数码'); -- id值是2
    INSERT INTO category (id,cname) VALUES (NULL, '电脑办公'); -- id值是3
    
    -- 3. 创建一张商品表product（主键id，商品名称pname，商品价格price, 所属分类cid 外键 从category的id里取值）
    CREATE TABLE product(
        id INT PRIMARY KEY AUTO_INCREMENT,
        pname VARCHAR(100) NOT NULL UNIQUE,
        price DOUBLE DEFAULT 0,
        cid INT NOT NULL,
        CONSTRAINT product_category_fk FOREIGN KEY(cid) REFERENCES category(id)
    );
    
    -- 4. 准备一些商品数据
    INSERT INTO product (id,pname,price,cid) VALUES (NULL, '小米电视4A 65', 2999, 1); -- 正常插入，cid的值是从category表的id里取的
    INSERT INTO product (id,pname,price,cid) VALUES (NULL, '吉普JEEP牛仔裤男士青年商务休闲长裤子', 133.2, 4);-- 不能插入，但是插入的是脏数据，没有对应的分类
    INSERT INTO product (id,pname,price,cid) VALUES (NULL, '吉普JEEP牛仔裤男士青年商务休闲长裤子', 133.2, NULL); -- 不能插入（因为外键字段上有约束not null）

## 四、表关系 ##

### 1. 一对一（了解） ###

*  不常用，因为一对一的两张表，通常可以合并成一张表。但是以下情况，建议拆分表，例如：
    
     *  出于效率的考虑：常用字段放在一张表，不常用字段放在另外一张。两张表一对一
     *  出于业务划分的考虑：用户信息表，和帐户信息表。

### 2. 一对多 ###

*  例如：分类和商品，用户和订单
 *  建表原则：在从表上增加一个字段，作为外键指向主表的主键。
    
     *  从表：多的一方
     *  主表：一的一方

### 3. 多对多 ###

*  例如：老师和学生，订单和商品，学生和课程
 *  建表原则：建立一张中间关系表，表里要有两个字段，是外键分别指向两张表的主键

-- 多对多 表设计: 学生表，课程表
    -- 1. 创建学生表
    CREATE TABLE student(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(10)
    );
    -- 2. 创建课程表
    CREATE TABLE course(
        id INT PRIMARY KEY AUTO_INCREMENT,
        sname VARCHAR(20)
    );
    -- 3. 创建中间关系表
    CREATE TABLE student_course(
        id INT PRIMARY KEY AUTO_INCREMENT,
        sid INT,
        cid INT,
        FOREIGN KEY(sid) REFERENCES student(id),
        FOREIGN KEY(cid) REFERENCES course(id)
    );
    -- 4. 准备数据
    INSERT INTO student(id, NAME) VALUES (NULL, '刘德华');
    INSERT INTO student(id, NAME) VALUES (NULL, '刘邦');
    INSERT INTO student(id, NAME) VALUES (NULL, '刘文健');
    
    INSERT INTO course(id,sname) VALUES (NULL, '英语');
    INSERT INTO course(id,sname) VALUES (NULL, 'Java');
    INSERT INTO course(id,sname) VALUES (NULL, '体育');
    
    -- 刘文健选择课程：英语和Java
    INSERT INTO student_course(id,sid,cid) VALUES (NULL, 3, 1);
    INSERT INTO student_course(id,sid,cid) VALUES (NULL, 3, 2);
    -- 刘德华选择课程：英语和体育
    INSERT INTO student_course(id,sid,cid) VALUES (NULL, 1, 1);
    INSERT INTO student_course(id,sid,cid) VALUES (NULL, 1, 3);

# 内容回顾 #

1.  ==DQL查询==
    
    1.  简单查询
    2.  条件查询
    3.  排序查询
    4.  聚合函数
    5.  分组查询
    6.  分页查询
    7.  综合查询：
    
        select 字段|聚合函数 from 表名 where 条件 group by 分组字段 having 分组后过滤条件 order by 排序字段 排序规则 limit 起始索引, 查询数量
    
     
2.  备份和恢复（了解）
3.  约束
    
    1.  主键约束：主键字段值非空唯一；一张表要有只能有一个主键
    2.  唯一性约束：字段值不能重复，但是可以为null
    3.  非空约束：字段值不能为null
    4.  默认值约束：如果不设置字段值，就取默认值。
    5.  外键约束：外键字段的值，必须从主表的主键中取值。维护数据的一致性和完整性，避免脏数据
4.  表关系：
    
    1.  一对一（了解）：
        
        1.  建表原则：任意一张表增加外键，外键字段指向另外一张表的主键，并且外键字段要加唯一性约束
    2.  一对多:
        
        1.  建表原则：在从表上增加外键字段，指向主表的主键字段
        2.  主表：一的一方
        3.  从表：多的一方
    3.  多对多：
        
        1.  建表原则：建立一张中间关系表，表里有两个外键字段，分别指向两张表的主键