SQL操作三

文章目录

1. 1. Day03
   1. 1.1. 查询null
   2. 1.2. 别名
   3. 1.3. 去除重复的值(distinct)
   4. 1.4. where
   5. 1.5. and 和 or
   6. 1.6. like
      1. 1.6.1. not like (不包含)
   7. 1.7. between … and (在..之间)
   8. 1.8. in (查询的值为多个)
   9. 1.9. order by
   10. 1.10. 分页 limit 子句
   11. 1.11. concat() 函数
   12. 1.12. 数值运算
   13. 1.13. 日期相关函数
   14. 1.14. DATE_FORMATE() 函数 日期格式化
   15. 1.15. str_to_date 把字符串转成日期格式
   16. 1.16. IFNULL() 函数
   17. 1.17. 聚合函数
       1. 1.17.1. 字符串的函数
       2. 1.17.2. 数学相关函数

Day03

查询null

• 查询列值为null (is null)
  • select * from emp where mgr is null; 查询上级领导为空的员工
• 查询列值不为null (is not null)
  • select * from emp where mgr is not null and comm>0;

别名

如果表中的字段名称太长或者不是很容易直接看懂，那么我们可以使用别名，使用的方式有三种：

• select ename "姓名" from emp;
• select ename as "姓名" from emp;
• select ename 姓名 from emp;

去除重复的值(distinct)

select distinct job from emp;

where

条件语句支持的运算符： > < = != >= <= !=(<>)

and 和 or

and 并且 多个条件属于与的关系 or 或者

• select * from user where id=1 or id=2; 查询id=1的数据或者id=2的数据 ,如果这两个都存在，那么将会全部返回

like

• _ 代表单个未知字符
  • 第二个字母为a : _a%
  • 倒数第三个字母为a ：%a__
• % 代表多个未知字符
  • 以a开头的 : a%
  • 以a结尾的 ：%a
  • 包含a :%a%
• select * from user where name like '_加%';
• select * from user where name like '_加_'; 此时匹配的名字是三个字符，比如 陈加兵

not like (不包含)

select * from user where name not like '_加%';

between … and (在..之间)

在两个数之间 select * from t_item where price between 10 and 100; 查询价格在10 到 100之间的数据

in (查询的值为多个)

查询某个字段的值为多个值的时候使用in select * from t_item where price in(100,200,233);

order by

• 升序（默认 asc） -select price from t_item order by price
  • order by 写在后面，如果有where条件，那么要写在where条件的后面
  • select price from t_item where price<100 order by price;
• 降序（desc）
  • select price from t_item order by price desc
• 如果需要多个字段进行排序，则在by的后面写多个字段
  • select category_id,price from t_item order by category_id,price desc; 按照分类id升序，价格降序
• 查询带燃字的商品，按照价格降序排列
  • select title,price from t_item where title like "%燃%" order by price;

分页 limit 子句

• limit n,m ： n表示跳过的条数，m表示每页显示的条数
• 写在排序（order by 字句）的后面，如果没有排序写在where后面
  • limit 0,5 查询第一页，每页显示5条
  • limit 10,5 查询第三页，每页显示5条
  • limit 12,3 查询第五页 每页3条
• select price from t_item order by price limit 10,10; 按照价格升序排列，显示第二页，每页10条
• select price from t_item where price <100 limit 0,10; 查询价格小于100的记录，显示第一页，每页10条

concat() 函数

concat()函数可以实现多个字符串的拼接

• 在终端直接输入 select concat('a','b');
• select concat(price,"元") from t_item limit 0,3; 查询商品，并且将查询到的价格和元这个单位拼接。相当于显示的是价格只是每个价格后面添加了单位 比如： 23元
• 将标题和单价拼到一起进行展现
  • select concat(price,"元",title) from t_item limit 0,5;

数值运算

支持加减乘除，取余(%)等效mod(n,m)

• 查询商品并在结果中显示商品的总价值
  • select price,num ,price*num '总价' from t_item;
  • 直接做运算即可，可以使用别名解释字段的含义

日期相关函数

• 获取当前时间+时间
  • now()
  • 在终端输入select now();
• 获取当前日期
  • curdate()
• 获取当前时间
  • curtime()
• 测试
  • select now(),curdate(),curtime();
• 从日期和时间中获取日期
  • date(now())
• 从日期和时间中获取时间
  • time(now())
• extract() 提取年月日时分秒的函数
  • select extract(year from now());
  • select extract(month from now());
  • select extract(day from now());
  • select extract(hour from now());
  • select extract(minute from now());
  • select extract(second from now());

DATE_FORMATE() 函数 日期格式化

• format
  • %Y 4位年 2018
  • %y 2位 18
  • %m 月 05
  • %c 月 5
  • %d 日
  • %H 24小时制
  • %i 分
  • %s 秒
• 测试
  • select date_format(now(),'%Y年%m月%d日 %h时%i分%s秒'); 输出 2018年03月23日 03时44分51秒
• 查询商品 并显示商品上传日期
  • select title,date_format(created_time,'%Y年%m月%d日 %h时%i分%s秒') from t_item;

str_to_date 把字符串转成日期格式

• 将’2018年10月22日’ 转换成日期
  • select str_to_date("2018年10月23日",'%Y年%m月%d日');
  • 第一个参数是即将要转换的字符串日期，第二个参数是这个字符串日期的格式，用来解析这个字符串。
  • 输出： 2018-10-23

IFNULL() 函数

• age=ifnull(a,b) 如果a是null，age=b，如果不是null，age=a;
• 把奖金是null 设置成0
  • update emp set comm=ifnull(comm,0); 如果奖金comm是null，那么comm=0，如果不是空 comm=comm，还是原来的值

聚合函数

• 对多行数据进行合并统计
  • sum() 求和
    • select sum(num) from t_item where price<100;
  • avg() ： 求平均值
    • select avg(price) from t_item;
  • count() 计算数量
    • select count(*) from t_item where price<100;
  • max() 最大值
    • select max(price) from t_item;
  • min() 最小值
    • select min(price) from t_item;
• 测试
  • 查询DELL的平均单价
    • select avg(price) "平均单价" from t_item where title like "%DELL%";

字符串的函数

• char_length(str)
• instr(str,substr)
• locate(substr,str)
• insert(str,start,end,newStr)
• lower(str)
• upper(str)
• left(str,count)
• right(str,count)
• trim(str)
• substring(str,index)
• substring(str,index,length);
• repeat(str,count)
• replace(str,old,new)
• reverse()

数学相关函数

• floor(num)
• round(num)
• round(num,m)
• truncate(num,m)