一,基础函数
–1,概述
MySQL提供了很多函数,可以快速操作数据.
–2,各种函数的使用
–基本函数:lower\upper\length\substr\concat\replace\ifnull\uuid
–小数的函数:round\ceil \floor
–日期的函数:now\year\ month \ day\hour\minute \second\转义字符
–练习
#查看表结构
desc dept;
select * from dept;
#MySQL的函数们
select * from dept; #查询所有字段的值-低效
select dname from dept; #只查询dname字段的值-高效
select dname,deptno from dept; #只查询dname,deptno字段的值-高效
select dname,lower(dname) from dept;#lower(参数)-把参数变小写
select dname,upper(dname) from dept;#upper(参数)-把参数变大写
select dname,upper(dname),lower(dname) from dept;#查3列
#length(参数)-获取参数的长度
select dname,length(dname),loc,length(loc) from dept;
#substr(1,2)-1是要截取谁2是从哪儿开始(从1开始)
select dname,substr(dname,3) from dept;
#substr(1,2,3)-1是要截取谁2是从哪儿开始(从1开始)3是截取几个
select dname,substr(dname,2,3) from dept;
#concat()-拼接字符串
select dname,concat(dname,‘hello’) from dept;
select dname,concat(dname,“world”) from dept;
select dname,concat(dname,“world”,“123”) from dept;
#replace(1,2,3)替换-1是要替换的字段是谁2是要被替换的字符是啥3是新字符
#Mysql里整数可以简写,字符必须加’'或者""
select dname,replace(dname,‘c’,6) from dept;
select * from emp;
#ifnull(1,2)-1是字段名2是要被替换成的值
#ifnull如果是null可以替换成新值
select comm,ifnull(comm,1) from emp;
#对小数的处理
#round(四舍五入) & ceil(向上取整) & floor(全舍)
select comm,ceil(comm),floor(comm),round(comm) from emp;
#对日期数据的操作
select now() ; #获取年月日时分秒
select CURDATE(); #获取年月日
select CURTIME(); #获取时分秒
#year() month() day()
select now(),year(now()),month(now()),day(now())
#hour() minute() second()
select now(),hour(now()),minute(now()),second(now())
select uuid() #得到32位的字符
select 'xian'
select 'xi\'an'#'是SQL里的特殊符号,想作为普通字符使用需要转义\
--3,条件查询
--1,distinct/where/like/order by/limit
--2,练习
select 'xian'
select 'xi\'an'#'是SQL里的特殊符号,想作为普通字符使用需要转义\
select distinct loc from dept; #给字段的值去重
select * from emp;#查了所有
#给查询结果 加一个过滤条件
select * from emp where 字段名=字段值;#查id=100的数据
select * from emp where id=100;#查id是100的数据
select * from emp where ename='jack' ;#查ename是jack的数据
select id,job,sal from emp where ename='jack'#查ename是jack的id,job,sal数据
select ename,job from emp where job='副总';#副总的名字
#查询id>200的所有数据
select * from emp where id>200
#查询id>200的所有数据的名字和工资总和
select sal,comm,ename,sal+ifnull(comm,0) from emp where id>200
select * from emp where true
#SQL执行的顺序:from > where > select
select * from emp where 1=1
#多个条件间的关系: 并且and 或者or
#查询id是100,ename是jack的记录
select * from emp where id=100 or ename='jack'
select * from emp where job='副总' and sal>10000
select * from emp where job='副总' or sal>10000
#查询sal是8000,10000,20000的记录
select * from emp where sal=8000 or sal=10000 or sal=20000
select * from emp where sal in(8000,10000,20000)#等于8000或者等于10000或者等于20000
select * from emp where sal not in(8000,10000,20000)#不等于8000或者等于10000或者等于20000
#like 模糊查询
select * from emp where ename like 'a';#条件相当于ename='a'
select * from emp where ename like '%a%';#中间包含着a
select * from emp where ename like 't%';#以t开始后面有几个都行
select * from emp where ename like '%y';#以y结束前面有几个都行
#按照null过滤数据
select * from emp where comm is null #过滤出来是null的记录
select * from emp where comm is not null#过滤出来不是null的记录
#between and
select * from emp where sal>8000 and sal<20000
#过滤工资在[8000,20000]的
select * from emp where sal between 8000 and 20000#[8000,20000]
#limit 分页技术
select * from emp limit 3 #只取前三条
select * from emp limit 1,3 #从第二条开始取,取总共3条
select * from emp limit 2,2 #从第三条开始取,取总共2条
#order by 排序
select * from emp order by sal #默认升序,按数字的从小到大
select * from emp order by comm #默认升序,null放最上面
select * from emp order by ename #默认升序,按自然顺序
select * from emp order by hiredate #默认升序,按自然顺序
select * from emp ORDER BY sal desc #默认是升序,desc降序
#取工资最高的前3个人
select * from emp ORDER BY sal desc limit 3;#desc降序排序
#SQL的执行顺序? from > ORDER BY > limit
select * from emp ORDER BY sal desc limit 0,3;#从第一条开始取,取3条
#查询岗位是副总的 工资最高的员工信息
#SQL的执行顺序? from > where > order by > limit
select * from emp where job='副总' order by sal desc limit 1
#2017年以前入职的员工信息
select * from emp where year(hiredate) < 2017
select * from emp where hiredate < '2017-1-1'
#2017-1-1到2021-5-1入职的员工信息
select * from emp where hiredate <= '2021-5-1'
and hiredate >= '2017-1-1'
#公司福利不错13薪,统计员工的年薪
select ename,job,sal,comm,(sal+ifnull(comm,0))*13 from emp
--4,聚合函数
--对一列的结果进行运算
--count \ sum \avg \max \min
--练习
#count \sum \avg \max \min
select * from emp
select count(*) from emp #统计emp的记录总数-低效
select count(1) from emp #高效
select count(id) from emp #高效
select sal from emp
select max(sal) from emp #获取sal的最大值
select min(sal) from emp #获取sal的最小值
select avg(sal) from emp #获取sal的平均值
select sum(sal) from emp #获取sal的求和
#获取每个部门的,最高薪
select deptno,min(sal),max(sal) from emp group by deptno
#分组--聚合函数以外的列都需要分组
扩展:
–1,常见错误:
–表里有3列,赋值时只给两列赋值
mysql> insert into dept2 value(‘研发部’,‘北京’); #错误
ERROR 1136 (21S01): Column count doesn’t match value count at row 1
mysql> insert into dept2 value(null,‘研发部’,‘北京’);#正确
--中文乱码,本来应该是正确的数据,但是说长度太大
mysql> insert into dept2 value(null,'研发部','北京');
ERROR 1406 (22001): Data too long for column 'dname' at row 1
mysql> set names gbk; #防止中文乱码,后面插入的就不乱码了...
--未知的列
[SQL]select dname,concat(dname,hello) from dept;
[Err] 1054 - Unknown column 'hello' in 'field list'