使用DQL命令查询数据（二）

TuSi_Cat

于 2024-08-29 17:24:47 发布

阅读量561

点赞数 9

文章标签： mysql 数据库开发语言

本文链接：https://blog.csdn.net/TuSi_Cat/article/details/141683924

版权

使用DQL命令查询数据二

SELECT语法

    SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [,table.field2 [as  alias2]][,…]] }
FROM  table_name  [ as  table_ alias  ]
    [left|out|inner  join  table_name2]    #联合查询
    [ WHERE  … ]    #指定结果需满足的条件
    [ GROUP BY …]   #指定结果按照哪几个字段来分组
    [ HAVING …] #过滤分组的记录必须满足的次要条件
    [ ORDER BY… ]   #指定查询记录按一个或者多个条件排序
    [ LIMIT  {   [ offset,] row_count   |   row_count OFFSET offset   }] ;  #指定查询的记录从哪条至哪条

ORDER BY排序

ORDER BY排序查询

对SELECT语句查询得到的结果，按某些字段进行排序

与DESC或ASC搭配使用，默认为ASC

（desc降序 asc升序）

MySQL的LIMIT

LIMIT [m,]n 或 LIMIT n OFFSET m

限制SELECT返回结果的行数

m 制定第一个返回记录行的偏移量

n 制定返回记录行的最大数目

注意：m不指定则偏移量为0，从第一条开始返回前n条记录LIMIT 常用于分页显示

SELECT * FROM `result` LIMIT 5       #返回前5条记录
SELECT * FROM `result` LIMIT 5,10   #返回6-15条记录

子查询

在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句

注意：嵌套查询可由多个子查询组成，求解的方式是由里及外子查询返回的结果一般都是集合，故而建议使用 IN 关键字

MySQL函数

数学函数

1、ABS(x) 返回x的绝对值

SELECT ABS(-1);

2、AVG(expression) 返回一个表达式的平均值，expression 是一个字段

SELECT AVG(score) FROM sc;

3、CEIL(x)/CEILING(x) 返回大于或等于 x 的最小整数

SELECT CEIL(1.5);
SELECT CEILING(1.5);

4、FLOOR(x) 返回小于或等于 x 的最大整数

SELECT FLOOR(1.5);

5、EXP(x) 返回 e 的 x 次方

SELECT EXP(3);

6、GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值

SELECT GREATEST(3, 12, 34, 8, 25);

7、LEAST(expr1, expr2, expr3, …) 返回列表中的最小值

SELECT LEAST(3, 12, 34, 8, 25);

8、LN 返回数字的自然对数

SELECT LN(2);

9、LOG(x) 返回自然对数(以 e 为底的对数)

SELECT LOG(20.085536923188);

10、MAX(expression)返回字段 expression 中的最大值

SELECT MAX(score) FROM sc;

11、MIN(expression)返回字段 expression 中的最小值

SELECT MIN(score) FROM sc;

12、POW(x,y)/POWER(x,y)返回 x 的 y 次方

SELECT POW(2,3);
SELECT POWER(2,3);

13、RAND()返回 0 到 1 的随机数

SELECT RAND();

14、ROUND(x)返回离 x 最近的整数

SELECT ROUND(1.23456);

15、SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1

SELECT SIGN(-10);

16、SQRT(x)返回x的平方根

SELECT SQRT(25);

17、SUM(expression)返回指定字段的总和

SELECT SUM(score) FROM sc;

18、TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

SELECT TRUNCATE(1.23456,3);

字符串函数

1、返回字符串 s 的第一个字符的 ASCII 码

 SELECT ASCII('AB');

2、LENGTH/CHAR_LENGTH(s)/CHARACTER_LENGTH(s)返回字符串 s 的字符数

SELECT LENGTH('1234');

3、CONCAT(s1,s2…sn)字符串 s1,s2 等多个字符串合并为一个字符串

SELECT CONCAT('hel','llo');

4、FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置

SELECT FIND_IN_SET("c", "a,b,c,d,e");

5、FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入

SELECT FORMAT(250500.5634, 2);

6、INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

SELECT INSERT("google.com", 1, 6, "runnob");

7、LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置

SELECT LOCATE('st','myteststring');

8、LCASE(s)/LOWER(s)将字符串 s 的所有字母变成小写字母

SELECT LOWER('RUNOOB');

9、UCASE(s)/UPPER(s)将字符串 s 的所有字母变成大写字母

SELECT UCASE('runoob');

10、TRIM(s)去掉字符串 s 开始和结尾处的空格

SELECT TRIM('    RUNOOB    ');

11、LTRIM(s)去掉字符串 s 开始处的空格

SELECT LTRIM('    RUNOOB    ');

12、RTRIM(s)去掉字符串 s 结尾处的空格

SELECT RTRIM('    RUNOOB    ');

13、SUBSTR/SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串

SELECT SUBSTR("RUNOOB", 2, 3);
SELECT SUBSTRING("RUNOOB", 2, 3);

14、POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置

SELECT POSITION('b' in 'abc');

15、REPEAT(s,n)将字符串 s 重复 n 次

SELECT REPEAT('runoob',3);

16、REVERSE(s)将字符串s的顺序反过来

SELECT REVERSE('abc');

17、STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

SELECT STRCMP("runoob", "runoob");

日期和时间函数

1、CURDATE()/CURRENT_DATE()返回当前日期

SELECT CURDATE();
SELECT CURRENT_DATE();

2、CURRENT_TIME()/CURTIME()返回当前时间

SELECT CURRENT_TIME();

3、CURRENT_TIMESTAMP()返回当前日期和时间

SELECT CURRENT_TIMESTAMP();

4、ADDDATE(d,n)计算起始日期 d 加上 n 天的日期

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);

5、ADDTIME(t,n)时间 t 加上 n 秒的时间

SELECT ADDTIME('2011-11-11 11:11:11', 5);

6、DATE()从日期或日期时间表达式中提取日期值

SELECT DATE("2017-06-15 11:11:16");

7、DAY(d)返回日期值 d 的日期部分

SELECT DAY("2017-06-15");

8、DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数

SELECT DATEDIFF('2001-01-01','2001-02-02');

9、DATE_FORMAT按表达式 f的要求显示日期 d

SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');

10、DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday

SELECT DAYNAME('2011-11-11 11:11:11');

11、DAYOFMONTH(d)计算日期 d 是本月的第几天

SELECT DAYOFMONTH('2011-11-11 11:11:11');

12、DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推

SELECT DAYOFWEEK('2011-11-11 11:11:11');

13、DAYOFYEAR(d)计算日期 d 是本年的第几天

SELECT DAYOFYEAR('2011-11-11 11:11:11');

14、EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值

SELECT EXTRACT(MINUTE FROM '2011-12-13 14:15:16');

15、UNIX_TIMESTAMP()得到时间戳

SELECT UNIX_TIMESTAMP('2019-2-19');

系统信息函数

①version()函数

查看MySQL系统版本信息号

②connection_id()函数

查看当前登入用户的连接次数数

直接调用CONNECTION_ID()函数--不需任何参数--就可以看到当下连接MySQL服务器的连接次数，不同时间段该函数返回值可能是不一样的

③processlist

查看用户的连接信息

Id列：登录MySQL的用户标识，是系统自动分配的CONNECTION ID；

User列：显示当前的“用户名”；

Host列：显示执行这个语句的IP，用来追踪出现问题语句的用户；

db列：显示这个进程目前连接的是哪个数据库；

Command列：显示当前连接执行的命令，一般是休眠（Sleep）、查询（Query）、连接（Connect）；

Time列：显示这个状态持续的时间，单位是秒；

State列：显示使用当前连接的SQL语句的状态，包含有：Copying to tmptable、Sorting result、Sending data等状态；

Info列：显示当前SQL的内容，如果语句过长可能无法显示完全。

④database(),schema()函数

查看当前使用的数据库

⑤user(),current_user(),system_user()函数

⑥charset()函数

使用CHARSET()函数返回字符串使用的字符集

获取当前用户

⑦collation()函数

使用COLLATION()函数返回字符串排列方式

MySQL的统计函数

函数名称	描述
COUNT( )	返回满足SELECT条件的记录总和数，如 SELECT COUNT(*)…
SUM( )	返回数字字段或表达式列作统计，返回一列的总和
AVG( )	通常为数值字段或表达列作统计，返回一列的平均值
MAX( )	可以为数值字段、字符字段或表达式列作统计，返回最大的值
MIN( )	可以为数值字段、字符字段或表达式列作统计，返回最小的值

GROUP BY分组

使用GROUP BY关键字对查询结果分组

对所有的数据进行分组统计

分组的依据字段可以有多个，并依次分组

与HAVING结合使用，进行分组后的数据筛选

SQL中CASE WHEN THEN ELSE END的用法详解

1. CASE WHEN THEN ELSE END的基本语法

SELECT
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
  END
FROM table;
# 例子
SELECT
  employee_name,
  salary,
  CASE
    WHEN salary > 50000 THEN '高薪水'
    WHEN salary > 30000 THEN '中薪水'
    ELSE '低薪水'
  END AS salary_evaluation
FROM employees;

condition1, condition2, …：条件表达式，当满足某个条件时执行对应的 result。
result1, result2, …：满足相应条件时返回的结果。
ELSE default_result：可选项，当所有条件都不满足时返回的默认结果。

2.多条件判断

SELECT
  employee_name,
  salary,
  CASE
    WHEN salary > 50000 AND location = '北京' THEN '高薪高地'
    WHEN salary > 50000 AND location = '上海' THEN '高薪高地'
    WHEN salary > 30000 THEN '中薪水'
    ELSE '低薪水'
  END AS salary_location_evaluation
FROM employees;

通过增加 AND location = '北京' 和 AND location = '上海' 的条件，实现了对薪水水平和工作地点的联合评价

3.使用CASE进行计算

SELECT
  employee_name,
  salary,
  CASE
    WHEN performance_rating = '优秀' THEN salary * 0.2
    WHEN performance_rating = '良好' THEN salary * 0.1
    ELSE salary * 0.05
  END AS performance_bonus
FROM employees;

4.CASE与聚合函数的结合

SELECT
  CASE
    WHEN salary > 50000 THEN '高薪水'
    WHEN salary > 30000 THEN '中薪水'
    ELSE '低薪水'
  END AS salary_level,
  COUNT(*) AS employee_count
FROM employees
GROUP BY salary_level;

使用 CASE WHEN THEN ELSE END 将员工分为不同的薪水水平，并通过 COUNT(*) 统计每个水平的员工数量，实现了对员工薪水水平的汇总统计。

5.嵌套CASE语句

SELECT
  employee_name,
  salary,
  CASE
    WHEN salary

 > 50000 THEN
      CASE
        WHEN performance_rating = '优秀' THEN '高薪优秀'
        WHEN performance_rating = '良好' THEN '高薪良好'
        ELSE '高薪一般'
      END
    WHEN salary > 30000 THEN
      CASE
        WHEN performance_rating = '优秀' THEN '中薪优秀'
        WHEN performance_rating = '良好' THEN '中薪良好'
        ELSE '中薪一般'
      END
    ELSE
      CASE
        WHEN performance_rating = '优秀' THEN '低薪优秀'
        WHEN performance_rating = '良好' THEN '低薪良好'
        ELSE '低薪一般'
      END
  END AS salary_performance_evaluation
FROM employees;

我们想根据员工的薪水和绩效评级分别进行评价

6.CASE WHEN的注意事项

CASE WHEN THEN ELSE END 可以用于任何SQL语句中，包括 SELECT、WHERE、ORDER BY 等。
当条件判断较为简单时，可以使用 CASE WHEN 的缩写形式 CASE column_name WHEN value THEN result END。