数据库的查询操作(DQL的知识点总结)

DQL（Data Query Language）

数据查询语言查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据**

基本查询语法: select 查询列表 from 表名
特点：
查询列表可以是：表中的字段、常量、表达式、函数 查询的结果是一个虚拟的表格

查询结果处理： 查询常量值 SELECT 100;

查询表达式：select 100*98;

查询函数：select 函数;

例如version()

特定列查询:select column1,column2 from table

全部列查询: select * from table

排除重复行: select distinct column1,column2 from table

查询结果处理：

●

函数

类似于java中的方法，将一组逻辑语句事先在数据库中定义好,可以直接调用

好处： 隐藏了实现细节 提高代码的重用性 调用：select 函数名(实参列表) [from 表];

● 分类：

单行函数：如concat、length、ifnull等

分组函数：做统计使用，又称为统计函数、聚合函数、组函数

单行函数中的字符串函数

length()：获取参数值的字节个数

SELECT id,LENGTH(NAME)FROM t_student

char_length()获取参数值的字符个数concat(str1,str2,…),concat(str1,str2,…)负责将前后字符串进行拼接：

SELECT id,CHAR_LENGTH(NAME) FROM t_student

SELECT CONCAT(NAME,sex)NAME FROM t_student

拼接字符串upper()/lower()：将字符串变成大写/小写substring(str,pos,length)：pos代表从第几位开始截取,length代表截取几个长度的字节(这次字符串是开始从1开始计数的哦)

SELECT UPPER(NAME)FROM t_student
SELECT LOWER(NAME)FROM t_student

截取字符串 位置从1开始instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0 trim(str)：

SELECT INSTR (NAME,'i')FROM t_student

去掉字符串前后的空格或子串,trim(指定子串 from 字符串)lpad(str,length,填充字符)：

SELECT id,CHAR_LENGTH(TRIM(NAME))FROM t_student
SELECT id,TRIM('b'FROM NAME)FROM t_student

– 用指定字符进行左/右填充

SELECT LPAD(NAME,5,'c')FROM t_student
SELECT RPAD(NAME,5,'a')FROM t_student

用指定的字符实现右填充将str填充为指定长度replace(str,old,new)：替换，替换所有的子串

SELECT REPLACE(NAME,'jim','tom')FROM t_student

逻辑处理:

case when 条件 then 结果1 else 结果2 end;

SELECT
  id,
  NAME ,(
    CASE
      WHEN height >= 160
      THEN '正常身高'
      WHEN height < 160
      AND height > 120
      THEN '偏低'
      ELSE '太低'
    END
  )
FROM
  t_student

可以有多个when if null(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值

if函数：if else的效果 if(条件,结果1,结果2)

SELECT IF(height>175,'高个','低个')FROM t_student

数学函数

round(数值)：四舍五入ceil(数值)：向上取整，返回>=该参数的最小整数

SELECT ROUND(10.2222) -- 返回11

floor(数值)：向下取整，返回<=该参数的最大整数

SELECT FLOOR(2.1)  -- 返回2

truncate(数值,保留小数的位数)：截断，小数点后截断到几位

SELECT TRUNCATE(1.111,2)   --返回1.11

mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负

rand()：获取随机数，返回0-1之间的小数

日期函数now()：

SELECT id,NAME,NOW()FROM t_student

返回当前系统日期+时间curdate()：

SELECT id,NAME,CURDATE()FROM t_student

返回当前系统日期，不包含时间curtime()：

SELECT id,NAME,CURTIME()FROM t_student

返回当前时间，不包含日期 可以获取指定的部分，年、月、日、小时、分钟、秒 YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期) SECOND(日期

SELECT id,NAME,YEAR(reg_time)FROM t_student
SELECT id,NAME,MONTH(reg_time)FROM t_student
SELECT id,NAME,DAY(reg_time)FROM t_student

str_to_date：将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('2021-7-6','%Y-%m-%d')

date_format：将日期转换成字符串

SELECT COUNT(*),DATE_FORMAT(birthday,'%Y-%m-%d')birth FROM t_student GROUP BY birth

datediff(big,small)：返回两个日期相差的天数

SELECT DATEDIFF(DATE_FORMAT(NOW(),'2021-7-10'),birthday)FROM t_student

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）

SELECT SUM(height)FROM t_student

SELECT SUM(height),sex FROM t_student GROUP BY sex

SELECT AVG(height)FROM t_student
SELECT MAX(height),sex FROM t_student GROUP BY sex
SELECT COUNT(height)FROM t_student

1.sum，avg一般用于处理数值型max，min，count可以处理任何类型

2.以上分组函数都忽略null值

3.count函数的一般使用count（*）用作统计行数

4.和分组函数一同查询的字段要求是group by后的字段

条件查询

使用Where子句进行条件过滤

select <结果> from <表名> where <条件>

逻辑运算符: and(与), or(或), not(非)

SELECT *FROM t_student WHERE sex='男'  -- 查询只为男性的学生

SELECT*FROM t_student WHERE sex<>'男'  -- 查询不为男性的学生

SELECT*FROM t_student WHERE sex='男'AND height>=160 -- 查询为男性的身高大于160的学生

SELECT *FROM t_student WHERE sex='男'OR height>160 -- 查询为男性的或身高大于160的学生

模糊查询:

LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值或数值型

通配符： % 任意多个字符，包含0个字符 _ 任意单个字符

SELECT*FROM t_student WHERE NAME LIKE 'j%' -- 查询以j开头的人名

SELECT*FROM t_student WHERE NAME LIKE'%i%' -- 查询名字中有i的人名

between and 两者之间,包含临界值；

SELECT*FROM t_student WHERE height BETWEEN 160 AND 170

in 判断某字段的值是否属于in列表中的某一项

SELECT*FROM t_student WHERE height IN(165,160) -- 查询身高在160和165的学生

IS NULL（为空的）或 IS NOT NULL（不为空的）

SELECT *FROM t_student WHERE NAME IS NOT NULL

UNION 的语法如下：
[SQL 语句 1]
UNION
[SQL 语句 2]

SELECT  id,NAME FROM t_student WHERE sex='男'
UNION
SELECT id,NAME FROM t_student WHERE height>170

NION ALL 的语法如下：
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，
mysql 会把所有的记录返回，且效率高于union

排序:

查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC

asc代表的是升序，desc代表的是降序，如果不写，默认是升序

SELECT *FROM t_student ORDER BY id ASC -- 在学生表中查询以学生id进行升序排序

order by子句中可以支持单个字段、多个字段、表达式、函数、别名

数量限制:

limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)

SELECT *FROM t_student WHERE height>160 LIMIT 0,2  -- 查询身高160以上的前两个(检索从0开始累加2条信息)

分组查询的语法:

select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

-- 查询男生和女生中人数最多的是哪一个
SELECT
  sex,
  COUNT(*)c -- 进行计数
FROM
  t_student
WHERE height > 160 -- 设置条件过滤
GROUP BY sex -- 进行分组
HAVING COUNT(*)> 2 -- 再上一步的前提上进行在过滤
ORDER BY COUNT(*)DESC -- 进行降序排列
LIMIT 0,1 -- 筛选

分组查询中的筛选条件分为两类:

分组查询

语法：select 分组函数，列（要求出现在group by的后面）from 表[where 筛选条件]group by 分组的列表[having 分组后的筛选][order by 子句]

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

SELECT COUNT(*),DATE_FORMAT(birthday,'%Y-%m-%d')birth FROM t_student GROUP BY birth