2022-8-16 第一小组 (≥▽≤) 学习笔记

目录

• 1.DQL(数据库查询语言)
  • 构建数据库
  • 1.1 单表查询
    • 基本查询
      • 基本语法
      • 列运算
      • 别名
      • 条件查询
      • 排序
    • 聚合函数
      • count
      • max
      • min
      • sum
      • avg
    • 分组查询
      • 分组查询前
      • 分组查询后
      • 面试题：
    • 分页查询
  • 1.2 多表查询
    • 笛卡尔积
    • 多表连接的方式
    • SQL92语法
    • SQL99语法
      • 内连接
      • 外连接(常用)
      • 全连接
      • 子查询

1.DQL(数据库查询语言)

重点：DQL是我们接触编写最多也是最难的SQL，该语言用来查询记录，不会修改数据库和表的结构

构建数据库

-- 构建一张student表
DROP TABLE IF EXISTS `student`;

CREATE TABLE `student`(
	`id` INT(10) PRIMARY KEY ,
	`name` VARCHAR(10) ,
	`age` INT(10) NOT NULL,
	`gender` VARCHAR(2)
)

-- 构建一张course表
DROP TABLE if EXISTS `course`

CREATE TABLE `course`(
	`id` INT(10) PRIMARY key,
	`name` VARCHAR(10) ,
	`t_id` INT(10)
)

-- 构建一张teacher表
DROP TABLE if EXISTS `teacher`

CREATE TABLE `teacher`(
	`id` INT(10) PRIMARY KEY,
	`name` VARCHAR(10)
)

-- 构建一张score表
DROP TABLE if EXISTS `score`;

CREATE TABLE `score`(
	`s_id` INT(10),
	`score` INT(10),
	`c_id` INT(10),
	PRIMARY KEY(`s_id`,`c_id`)
)

1.1 单表查询

基本查询

基本语法

查询所有记录:

-- select * from 表名 ;

查询指定的列:

-- select 字段名1,字段名2...字段名n  from  表名

补充--在开发中,严禁使用select*

如果表中有完全重复的记录,在查询的列之前加上distinct可以去重.

-- SELECT DISTINCT  字段名1,字段名2...字段名n  FROM  表名
-- 去重

列运算

-- select 字段名1,字段名2*10   from  表名 

注意:

• 我们写的所有查询语句,最终的执行结果,生成的都是一张虚拟表

• null值和任何值做计算都为null,需要用到函数ifnull()函数.

• 将字符串做加减乘除运算,会把字符串当0处理.

别名

• 我们可以给列起别名,我们在查询过程中,列名很可能重复,可能名字不够简洁,或者列名不能满足要求

• --  SELECT DISTINCT  字段名1 as  新字段名,字段名n  FROM  表名
  

条件查询

• 条件查询----在后面添加where指定条件

• -- select * from 表名 WHERE 条件 ;
  

• 模糊查询----查询所有姓张的

• -- select * from 表名 WHERE 条件 LIKE '张%';
  -- 查询姓张的,名字为2个字的
  -- select * from 表名 WHERE 条件 LIKE '张_';
  

  通配符：_下划线代表一个字符，%百分号代表任意个字符。

排序

• 升序

• -- select * from 表名 ORDER BY 字段名 ASC;
  

• 降序

• -- select * from 表名 ORDER BY 字段名 DESC;
  

• 使用多列作为排序条件：当第一个排序条件相同时，根据下一列排序条件进行排序

• -- select * from 表名 ORDER BY 字段名1 ASC，字段名2 DESC;
  

• 举例：

  创建一张用户表，id，username，password

  几乎所有的表都会有两个字段，create_time，update_time

  几乎所有的程序都会按照update_time降序排列

聚合函数

count

• 查询满足条件的记录行数，后边可以根where条件。

• 如果满足条件的列的值为空，不会进行统计

• 如果我们要统计真实有效的记录数，最好不要用可以为空的列

  • count（*）
  • count（主键）（推荐）
  • count（1）（不推荐）

• -- select count(字段名（主键）/1/*) from 表名 WHERE 条件 ;
  

max

• 查询满足条件的记录中的最大值，后面可以根据where条件

• -- select max（字段名） from 表名 WHERE 条件 ;
  

min

• 查询满足条件的记录中的最小值，后面可以根据where条件

• -- select min（字段名） from 表名 WHERE 条件 ;
  

sum

• 查询满足条件的记录的和，后面可以根据where条件

• -- select sum（字段名） from 表名 WHERE 条件 ;
  

avg

• 查询满足条件的记录的平均数，后面可以根据where条件

• -- select avg（字段名） from 表名 WHERE 条件 ;
  

分组查询

• 顾名思义：就是将原有数据分组统计

• 举类：

  将班级的同学按照性别分组，统计男生和女生的平价年龄

  -- select 分组列名，聚合函数1，聚合函数2...from 表名 group by 该分组列名；
  

• 分组要使用关键字group by，后面可以是一列，也可以是多列，分组后查询的列只能说分组的列，或者是使用了聚合函数的其他的列，剩余的列不能单独使用

分组查询前

• 可以通过关键字【where】先把满足条件的记录分出来，再分组。

• -- select 分组列名，聚合函数1，聚合函数2...from 表名 where 条件 group by 该分组列名；
  

分组查询后

• 可以通过关键字【having】把组信息中满足条件的组再细分出来。

• -- select 分组列名，聚合函数1，聚合函数2...from 表名 where 条件 group by 该分组列名 having 聚合函数或列名（条件）；
  

面试题：

where和having的区别？

• where是写在group by之前的筛选，再分组前筛选；having是写再group by之后，分组后再筛选
• where只能使用分组的列作为筛选条件；having可以使用分组的列，也可以使用聚合函数作为筛选条件。

分页查询

• limit字句，用来限定查询结果的起始行，以及总行数。

• limit是MySQL独有的语法

• -- select * from 表名 WHERE 条件  limit 4,3;
  -- select * from 表名 WHERE 条件  limit 4;
  

  • 如果只有一个参数，说明从起始位置查找4条记录。
  • 如果有两个参数，说明从第4行的下一行向后查找3条记录

• 面试题：常见数据库的分页关键字

  MySQL：limit

  SQL server：top

  Oracle：rownum

• 分析：

  如果10条件数据，如果每页显示4条数据，分3页。（向上取整）【(int)(Math.ceil(10/4))】

• 判断一个数据是否存在

  -- select count（数据） from 表名 WHERE 数据  limit 1；
  

1.2 多表查询

笛卡尔积

• 如果两个表之间没有任何关系，直接查询这两个表会生成的虚拟表是这两张表的数据之间的乘积
• 开发中，一定要避免出现笛卡尔积

多表连接的方式

SQL92语法

-- select * from 表名1，表名2  WHERE 条件【表名1.字段=表名2.字段】  
-- select * from 表名1，表名2，表名3  WHERE 条件【表名1.字段=表名2.字段】AND条件【表名3.字段=表名2.字段】  

SQL92语法，多表查询，如果有数据为空，会自动过滤掉

--   select * from 表名1，表名2，表名3  WHERE 条件【表名1.字段=表名2.字段】AND 条件【表名3.字段=表名2.字段】  

SQL99语法

内连接

• 在我们刚才的SQL，使用逗号分隔表，MySQL进行了优化默认就等效于内连接。
• 使用【join】关键字，使用【on】来确定连接条件。【where只做筛选条件】

-- SELECT 字段名 FROM 表名1 JOIN 表名2  ON 连接条件 WHERE 筛选条件

外连接(常用)

内连接与外连接的区别：

• 对于【内连接】的两个表，如果【驱动表】在【被驱动表】找不到与之匹配的记录，则最终记录不会出现在结果集中。
• 对于【外连接】的两个表，如果【驱动表】在【被驱动表】找不到与之匹配的记录，也要将该记录加入到最后的结果集中，针对不同的【驱动表】的位置，又分【左外连接】与【右外连接】

• 对于左外连接，左边的表为主表，左边的表的记录会完整的出现在结果集里。
• 对于右外连接，右边的表为主表，右边的表的记录会完整的出现在结果集里。

• 外连接的关键字【outter join】,也可以省略outter，连接条件同样使用【on】关键字。

• 左连接

  • -- SELECT  字段名 FROM 表名1 LEFT JOIN 表名2 ON 连接条件
    

• 右连接

  • -- SELECT  字段名 FROM 表名1 RIGHT JOIN 表名2 ON 连接条件
    

全连接

• mysql不支持全连接，Oracle支持

• -- SELECT  字段名 FROM 表名1 FULL JOIN 表名2 ON 连接条件
  

• 我们可以通过一些手段来实现全连接的一些效果【UNION】关键字

• -- SELECT  字段名 FROM 表名1 RIGHT JOIN 表名2 ON 连接条件 UNION  SELECT  字段名 FROM 表名1 LEFT JOIN 表名2 ON 连接条件
  

子查询