SQL查询语句

DQL语句--排序查询

# 格式: select * from 表名 order by 要排序的列1 [asc/desc], 要排序的列2 [asc/desc];
# 解释:
#   1. 无论SQL语句简单或者是复杂, order by语句一般都放最后, 注意: 如果有limit(分页), 则它(limit)在最后.
#   2. asc表示升序, desc表示降序, 其中, 默认是升序, 所以asc可以省略不写.
#   3. 如果排序的字段有多个, 则优先按照第1个字段规则排序, 第1个字段值一样才会按照第2个排序字段规则走, 第2个数据也一样......

例：

# 1. 切库.
use day02;
# 2. 查看表数据.
select * from product;# 需求1: 按照 商品价格进行排序.   注意: 如果没说升序还是降序, 一般默认都是升序.
select * from product order by price;       # 默认升序
select * from product order by price asc;   # 默认就是升序, asc表示升序, 所以它可以省略不写.
select * from product order by price desc;  # desc表示降序, 这个是必须写的.# 需求2: 按照价格进行降序排序, 价格一样, 按照pid降序排列.
select * from product order by price desc, pid desc;     # 价格降序, 价格一样, pid降序.
select * from product order by price desc, pid;          # 价格降序, 价格一样, pid升序.

DQL语句--聚合函数

聚合函数主要是操作某列值的, 对该列的值做聚合操作, 例如: 求最大值, 最小值, 平均值, 累加和, 次数等.

• 聚合函数有

  • sum() 求和

  • max() 求最大值

  • min() 求最小值

  • avg() 求平均值

  • count() 计数

• 例：

• # 聚合操作主要是对某列值做处理的, 常用的有: sum(), max(), min(), avg(), count()
  # 1. 查询表数据.
  select * from product;# 2. 需求: 求 商品价格的 总和.
  select sum(price) as sum_price from product;# 3. 需求: 求 商品价格的 最大值.
  select max(price) as max_price from product;# 4. 需求: 求 商品价格的 最小值.
  select min(price) as min_price from product;# 5. 需求: 求 商品价格的 平均值.
  # 扩展: round(小数, 要保留的位数) 可以实现四舍五入, 保留n位小数.
  select avg(price) as avg_price from product;# avg(price) 求商品价格的平均值
  # round()    四舍五入, 保留n位小数.
  select round(avg(price), 3) as avg_price from product;# 6. 需求: 求表数据的总条数.
  select count(*) as total_cnt from product;   # *代表整行 13条# 7. 扩展1, 可以在1个SQL语句中, 计算多项值.
  selectsum(price) as sum_price,round(avg(price), 3) as avg_price,max(price) as max_price,min(price) as min_price,count(*) as total_cnt
  from product;# 8. 扩展2, 关于count的面试题. 即: count(1), count(列), count(*)的区别是什么?
  select * from product;# 区别1: null值问题. count(1), count(*) 在统计的时候, 不会忽略null值,  而count(列)只统计该列的 非 null值.
  # 区别2: 效率问题, 效率从高到低分别是: count(主键) > count(1) > count(*) > count(普通列)
  select count(1) from product;               # 结果: 13条, 统计所有行, 不会忽略null值, 直接扫描行号, 不一定非得写数字1, 其它数字也行.
  select count(*) from product;               # 结果: 13条, 统计所有行, 不会忽略null值, * 是扫描该行所有的数据
  select count(category_id) from product;     # 结果: 12条, 只统计该列的数据总条数, 会忽略null值.# 扩展, 对数据进行去重查询.   distinct
  # 按照 price的值 进行去去重.
  select distinct price from product;# 按照 price的值 和 category_id的值 作为1个整体来去重的, 即: 800, 'c002' 和 800, 'c003' 不是同一条数据.
  select distinct price, category_id from product;

DQL语句--分组查询

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组

格式:

select 分组字段1, 分组字段2, 聚合函数 from 表名 where 组前筛选 group by 分组字段1, 字段2... having 组后筛选;

细节:

1. 查询列中不能出现非分组字段, 除了聚合函数.

2. 分组前的筛选可以通过where实现, 分组后的筛选可以通过having实现.

3. 分组查询一般都要结合 聚合函数 一起使用, 才有意义.

例：

# 格式: select 分组字段1, 分组字段2, 聚合函数 from 表名 where 组前筛选 group by 分组字段1, 字段2...  having 组后筛选;
# 解释: 分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组.
# 细节:
#   1. 查询列中不能出现非分组字段, 除了聚合函数, 即分组后, 查询列中只能出现, 分组字段, 聚合函数.
#   2. 分组前的筛选可以通过where实现, 分组后的筛选可以通过having实现.
#   3. 分组查询一般都要结合 聚合函数 一起使用, 才有意义.# 1. 查询表数据.
select * from product;# 2. 需求: 按照 商品类别分组, 统计每组商品的总价格.
select category_id, sum(price) total_price from product group by category_id;# 3. 需求: 将所有商品按组分类,获取每组的平均价格大于600的所有分组
select category_id, round(avg(price), 2) avg_price from product  group by category_id having avg_price > 600;# 4. 需求: 统计各个分类商品的个数,且只显示个数大于1的信息
select category_id, count(1) total_cnt from product group by category_id having total_cnt > 1;
# 上述的代码, 还可以变形为如下的写法, 效果相同.
select category_id, count(1) from product group by category_id having count(1) > 1;# 面试题: having 和 where的区别是什么?
# where: 组前筛选, 后边不能跟聚合函数.
# having: 组后筛选, 后边可以跟聚合函数.# 扩展: with rollup, 在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果
# 求每组的, 商品平均价格.
select category_id, round(avg(price), 2) avg_price from product  group by category_id with rollup ;# 求每组的, 商品总价格.
select category_id, sum(price) total_price from product  group by category_id with rollup ;

DQL语句--分页查询

所谓的分页查询指的是: 一次性从数据表中获取指定条数的数据, 即为: 分页查询.

假设:

商品表一共100条数据, 每页显示10条,

此时, 用户看第1页, 只要查1~10条数据即可,

用户看第2页, 只要查11~20即可, 以此类推,

一方面可以提供用户体验, 另一方面也可以减小服务器 和 浏览器的压力.

 

# 格式: select * from 表名 limit 起始索引, 每页的数据条数;
# 解释:
#   1. 数据表中每条数据都是有索引(也叫编号, 脚标, 下标, index)的, 且索引默认从0开始.
#   2. 要想把分页搞明白, 要搞懂4个值, 分别是: 每页的数据条数, 每页的起始索引, 数据总条数, 总页数.
#   3. 每页的数据条数:  产品经理, 项目经理定.
#   4. 数据总条数:     count(1), count(*)
#   5. 每页的起始索引:  (当前页数 - 1) * 每页的数据条数
#   6. 总页数:         (总数据条数 + 每页的数据条数 - 1) / 每页的数据条数    取整.

例：

# 1. 查询表数据.
select * from product;# 2. 每页3条, 查询第1页
select * from product limit 0, 3;# 3. 上述需求的简化写法, 如果起始索引是0, 则可以省略不写.
select * from product limit 3;# 4. 每页3条, 求第3页.        起始索引公式:  (当前页数 - 1 ) * 每页的数据条数 = (3 - 1) * 3 = 6
select * from product limit 6, 3;# 5. 每页5条, 求第3页.
select * from product limit 10, 5;# 总结, 一个完整的单表查询SQL语句格式为
# select [distinct] 列1, 列2 as 别名 from 表 where 组前筛选 group by 分组字段 having 组后筛选 order by 排序 [asc/desc] limit 起始索引, 每页的数据条数;

扩展--枚举类型

枚举指的是一些固定的值, 将来往列中填充数据的时候, 必须传入这些指定的值, 否则报错, 一般用于 某些自定义的规则.

# 枚举指的是一些固定的值, 将来往列中填充数据的时候, 必须传入这些指定的值, 否则报错, 一般用于 某些自定义的规则.

例：

# 1. 建表, 老师表, id, 姓名, 性别
drop table teacher;
create table teacher(id int primary key auto_increment,      # id, 主键, 自增name varchar(20),gender enum('男', '女')           # 全称单词, enumeration
);# 2. 往表中添加元素.
insert into teacher value(null, '张三', 'male'), (null, '李四', 'female');      # 报错, 必须传入枚举值.
insert into teacher value(null, '张三', '男'), (null, '李四', '女');             # 这样写是OK的, 因为传入的是枚举值.# 3. 查询表数据.
select * from teacher;

约束--外键约束

• 概述

  • 它属于约束的一种, 主要应用于多表的情况, 有外键(从键)的表被称之为: 外表(从表)

  • 主键所在的表被称之为: 主表.

  • 回顾, 数据类型和约束的作用: 保证数据的完整性和安全性.

• 格式

  • 方式1: 在创建表的时候直接指定约束.

    constraint 外键约束名 foreign key(外键列) references 主表名(主键列)

  • 方式2: 在建表后, 添加指定的约束.

    alter table 外表名 add constraint 外键约束名 foreign key(外键列) references 主表名(主键列);

• 特点(记忆)

  外表的外键列 不能出现 主表的主键列, 没有的数据.

• 示例代码

• # 关于主外键约束, 需要你记忆的内容:
  #   1. 无论是数据类型还是约束, 都是用于保证 数据的安全性和完整性的.
  #   2. 有外键列的表被称之为: 外表(从表), 有主键列的表被称之为: 主表.
  #   3. 外键约束特点: 外表的外键列 不能出现 主表的主键列没有的数据.
  #   4. 设置主外键约束, 是在 外表中添加的.# 1. 创建day03数据库.
  create database day03;
  # 2. 切库.
  use day03;
  show tables;# ---------------------------------------------------- 以下是 外键约束详解 ----------------------------------------------------
  # 案例: 部门表 department(id, name), 员工表(employee, id, name, salary, did),  一个部门有多个员工, 但是1个员工只能属于1个部门.
  # 3. 建表.
  # 3.1 创建部门表
  drop table dept;
  create table dept(id int primary key auto_increment,      # 部门idname varchar(20)                        # 部门名称
  );# 3.2 创建员工表
  drop table emp;
  create table emp(id int primary key auto_increment,      # 员工idname varchar(20),                       # 员工姓名salary double,                          # 员工工资did int                                # 员工所属的部门id#        外键约束名           外键列          主表名(主键列)# constraint fk01 foreign key (did) references dept(id)       # 添加外键约束的方式1: 建表时添加约束.
  );# 4. 添加表数据.
  # 4.1 添加数据到主表(部门表)
  insert into dept value (null, '人事部'), (null, '财务部'), (null, '研发部');# 4.2 添加数据到外表(员工表)
  insert into emp value(null, '乔峰', 1000, 2);     # 可以, 因为 部门id=2的数据, 在主表(部门表)中 有.
  insert into emp value(null, '虚竹', 3333, 6);     # 报错, 因为 部门id=6的数据, 在主表(部门表)中 没有, 保证数据的 安全性.# 5. 查看表数据.
  # 5.1 查询 主表 数据(部门表)
  select * from dept;
  # 5.2 查询 外表 数据(员工表)
  select * from emp;
  delete from emp where id = 2;# 6. 查看是否成功添加好 外键约束.
  desc dept;      # 主表, 部门表desc emp;       # 外表, 员工表.# 7. 查看外键约束是否生效.
  select @@foreign_key_checks;# 8. 查看SQL表被哪种执行引擎执行.
  show create table dept;
  show create table emp;# 9. 这个语句能执行成功吗?
  delete from dept where id = 3;  # 可以, 因为财务部"没员工"
  delete from dept where id = 2;  # 不能, 因为从表有数据在用它, 保证数据的 完整性.# 10. 扩展-外键约束的创建方式2: 建表后添加外键约束.
  alter table emp add constraint fk02 foreign key (did) references dept(id);  # 细节: 数据合法添加成功, 数据不合法添加失败.# 11. 扩展--删除外键约束.
  alter table emp drop foreign key fk02;      # 根据外键约束名, 删除指定的外键.
  

多表查询--交叉查询

准备数据源

# 总结: 多表查询的本质就是通过关联条件, 把多张表的数据, 整合到一张表中.# 1. 切库.
use day03;
show tables;# 2. 准备多表查询所需要的数据.
# 创建hero表
CREATE TABLE hero
(hid       INT PRIMARY KEY,hname     VARCHAR(255),kongfu_id INT
);# 创建kongfu表
CREATE TABLE kongfu
(kid   INT PRIMARY KEY,kname VARCHAR(255)
);# 插入hero数据
INSERT INTOhero
VALUES(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);# 插入kongfu数据
INSERT INTOkongfu
VALUES(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');# 3. 查询表数据
select * from hero;select * from kongfu;

具体的交叉查询

# 格式:      select * from 表A, 表B;
# 查询结果:   两张表的笛卡尔积, 即: 表A的总条数 * 表B的总条数, 这样会产生大量的脏数据, 所以这种方式不用.
select * from hero, kongfu;

多表查询--连接查询

内连接

例：

# 查询结果: 无论是显式内连接还是隐式内连接, 查询结果都是一样的, 都是表的: 交集.# 方式1: 显式内连接.
# 格式: select * from 表A inner join 表B on 关联条件;           # 细节: inner可以省略不写
select * from hero h inner join kongfu kf on h.kongfu_id = kf.kid;
select * from hero h join kongfu kf on h.kongfu_id = kf.kid;    # 细节: inner可以省略不写# 方式2: 隐式内连接.
# 格式:  select * from 表A, 表B where 条件;
select * from hero h, kongfu kf where h.kongfu_id = kf.kid;

外连接

例：

# 写法1: 左外连接
# 格式: select * from 表A left outer join 表B on 条件;
# 查询结果: 左表的全集 + 表的交集
select * from hero h left outer join kongfu kf on h.kongfu_id = kf.kid;
# outer可以省略
select * from hero h left join kongfu kf on h.kongfu_id = kf.kid;# 写法2: 右外连接
# 格式: select * from 表A right outer join 表B on 条件;
# 查询结果: 右表的全集 + 表的交集
select * from hero h right join kongfu kf on h.kongfu_id = kf.kid;# 这样的话, 效果等价于上边的 左外链接.
select * from kongfu kf right join  hero h on h.kongfu_id = kf.kid;# 小细节: 左外连接 和 右外连接掌握1个就够了, 推荐掌握左外链接, 因为给表的顺序换一下, 结果一样.