数据库Day03

DQL

        DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据。

        语法：select 查询列表 from 表名；

        查询结果的处理

        单行函数：字符串处理函数、逻辑处理、数学函数、日期函数

        聚合/分组/统计函数：sum()总和 、avg()平均数、max()最大值、min()最小值、count()总个数

-- 基本查询语法  select 查询列表  from 表名；
-- select 列 from 表名  where 条件  order by（排序） group by（分组） having(分组后的查询) limit
-- 嵌套子查询  关联查询
-- 查询出来的结果是虚拟的表格（备份），不会改变的，不会影响到数据库中的数据
SELECT id,num,sname,SUBSTR (sname,1,2)FROM student;

-- 查询特定的列   实际中是建议的（会提高查询的效率）
SELECT num,sname,gender FROM student

-- *表示查询所有的列，在需要查询所有列时使用，一般不建议
SELECT * FROM student

-- 去除重复行（指的是所有列的值都相同）实际多用于在关联查询时，由于关联条件导致一些重复数据
SELECT DISTINCT sname,gender FROM student

-- 算数运算 + - * / 只能作算数运算
SELECT num,sname,height+0.5 ,height+num FROM student;
-- + 不能用作字符串连接
SELECT sname+gender FROM student;

-- 字符串连接函数  定义别名  as 别名 as还可以省略
SELECT CONCAT(sname,':',gender)AS sname FROM student;

-- 单行函数  此函数会对查询结果的每行进行处理

/*
  length()：获取参数值的字节个数 
  char_length()获取参数值的字符个数 
  concat(str1,str2,.....)：拼接字符串 
  upper()/lower()：将字符串变成大写/小写 
  substring(str,pos,length)：截取字符串 位置从1开始 
  instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0 
  trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串) 
  lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度 
  rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度 
  replace(str,old,new)：替换，替换所有的子串
  
  java中  对象.方法名()
*/
-- length(列名) 以字节为单位
SELECT num,sname,LENGTH(sname) FROM student;
-- CHAR_LENGTH（列名） 以字符为单位
SELECT num,sname,CHAR_LENGTH(sname) FROM student;
SELECT num,sname,UPPER(sname),LOWER(sname) FROM student;
-- SUBSTRING(列名，开始位置从1开始，截取的长度)
SELECT num,sname,SUBSTRING(sname,1,2)FROM student;


/*
  instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0 
  trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串) 
  lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度 
  rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度 
*/

SELECT INSTR(sname,'i'),INSTR("abcdcc",'c') FROM student;

SELECT CHAR_LENGTH(TRIM("  abc  ")) FROM student;

-- 去掉前后的指定字符串
SELECT TRIM('a' FROM "abca") FROM student;

-- 填充字符串
SELECT num,LPAD(sname,5,"a"),RPAD(sname,5,"b") FROM student;

-- 逻辑处理  数学函数 日期函数 分组函数（统计函数  聚合函数）
/*
case when 条件 then 结果1 else 结果2 end; 可以有多个when   一个用if 多个用when
ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回 原本的值 
if函数：if else的 效果 if(条件,结果1,结果2)
*/

SELECT num,
       sname,
       height,
       ( CASE WHEN height>=1.70 THEN "高个子" ELSE '非高个子' END)height
       FROM student

SELECT num,
       sname,
       height,
       ( CASE WHEN height>=1.80 THEN "大高个子" 
              WHEN height>=1.70 THEN "高个子" 
              ELSE '非高个子' END)height
       FROM student
       
 SELECT num,sname,IF(height>=1.70,"高个子","非高个")FROM student 

 SELECT num,sname,IFNULL(height,"身高信息未录入") FROM student


-- 数学函数  
/*
truncate(数值,保留小数的位数)：截断，小数点后截断到几位
mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负 
rand()：获取随机数，返回0-1之间的小数
*/
-- 截取小数部分
SELECT num,sname,TRUNCATE(height,1) FROM student
-- 取余数
SELECT num,sname,MOD(num,3) FROM student
-- 取0-1之间的小数
SELECT RAND()FROM student

-- 日期函数
/*
now()：返回当前系统日期+时间 
curdate()：返回当前系统日期，不包含时间 
curtime()：返回当前时间，不包含日期 
	   可以获取指定的部分，年、月、日、小时、分钟、秒 
	   YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期) SECOND(日期) 
str_to_date：将日期格式的字符转换成指定格式的日期 
date_format：将日期转换成字符串 
datediff(big,small)：返回两个日期相差的天数
*/
-- 获取系统时间
SELECT NOW(),CURDATE(),CURTIME() FROM student

SELECT num,sname,YEAR(birthday) FROM student

SELECT STR_TO_DATE('2022-1-7','%Y-%m-%d') FROM student

SELECT DATE_FORMAT(birthday,'%Y-%m-%d')FROM student
-- 计算两个日期之间相差的天数
SELECT num,sname,DATEDIFF(CURDATE(),'2022-1-1') FROM student


-- sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
-- sum，avg一般用于处理数值型max，min，count可以处理任何类型

SELECT SUM(height) ,gender FROM student GROUP BY gender -- 计算某列值的总和

SELECT AVG(height)  FROM student  -- 某列总和

SELECT COUNT(*) FROM student  -- 统计表中的总数

-- max(数值/字符串) 字符使用字符编码比较大小
SELECT MAX(height),MAX(sname),MIN(height),MIN(sname) FROM student

        条件查询、排序、限制数量、分组

-- 查询条件  排序 数量限制 分组

-- =, != 或<>, >, <, >=, <=
-- where 后面= 是比较是否相等
SELECT * FROM student WHERE gender = "男"
SELECT * FROM student WHERE gender != "男"
SELECT * FROM student WHERE gender <> "男" -- 不等于

-- and 与    or 或       not 非
SELECT * FROM student WHERE gender = "男" AND height>1.70 AND address = '西安'

SELECT * FROM student WHERE gender = "男" OR height>1.70

SELECT * FROM student WHERE num NOT IN(101,102,103) -- not in()  不满足in中的条件

SELECT * FROM student WHERE phone IS NULL;

SELECT * FROM student WHERE phone IS NOT NULL; -- 不是null的

SELECT * FROM student WHERE height >=1.70 AND height<=1.90
-- 在两者之间  包含两边的边界值
SELECT * FROM student WHERE height BETWEEN 1.70 AND 1.90

-- like 模糊查询  %匹配任意个字符   _匹配一个字符
SELECT * FROM student WHERE sname LIKE "张%"

SELECT * FROM student WHERE sname LIKE "%张%"

SELECT * FROM student WHERE sname LIKE "张_"

-- 排序  
SELECT * FROM student ORDER BY height  -- 默认升序  ace也是升序
SELECT * FROM student ORDER BY height DESC  -- desc降序

SELECT * FROM student WHERE id>=1 ORDER BY height DESC

-- 限制数量   索引从第0行开始  mysql分页查询的基础
SELECT * FROM student LIMIT 0,3
SELECT * FROM student LIMIT 3,3

SELECT * FROM student WHERE id>=1 ORDER BY register_time DESC LIMIT 0,3

-- 分组  group by 分组列（以那列为条件分组）  与分组函数联合使用
-- 把值相同的数据分到同一组
-- 统计男生和女生分别有多少人
SELECT COUNT(*),gender FROM student GROUP BY gender