mysql 基本语句笔记

mysql笔记

五，数据处理函数
                数据处理函数/单行处理函数
            
                    Lower                转换小写
                    upper                转换大写
                    substr                取子串（substr(被截取的字符串,起始下标,截取的长度)）
                    length                取长度
                    trim                去空格
                    str_to_date            将字符串转换成日期
                    date_format            格式化日期
                    format                设置千分位
                    round                四舍五入
                    rand()                生成随机数
                    Ifnull                 可以将null转换成一个具体值
                    
            1,lower
            查询员工将员工的名字转换为小写
            select lower(ename) from emp;
            2,upper
            select *from emp where job=upper('salesman');
            3,substr
            select * from emp where substr(ename, 1, 1)=upper('m');
            4,length
            查询员工姓名长度为5的员工
            select length(ename),ename from emp where length(ename)=5;
            5,trim 
            会去除首位空格，不会去除中间的空格
            岗位为salesman 的所有员工
            select *from emp where job=trim(upper('salesman '));
            5,str_to_date
            将字符串转换为日期
            查询1981-02-20入职的员工
            select *from emp where hiredate='1981-02-20';(系统默认格式)
            将字符串改为日期格式
            select *from emp where hiredate=str_to_date('1981-02-20','%Y-%m-%d');
            select *from emp where hiredate=str_to_date('20-02-1981','%d-%m-%Y');
            6,date_format
            select empno,ename,date_format(hiredate,'%Y-%m-%d %H:%i:%s') as hiredate from emp;
            7,now() 查询当前时期
            select date_format(now(),'%Y-%m-%d %H:%i:%s');
            
            日期格式的说明
            %Y：代表4位的年份
            %y：代表2位的年份
            %m：代表月, 格式为(01……12)  
            %c：代表月, 格式为(1……12)
            %H：代表小时,格式为(00……23)  
            %h： 代表小时,格式为(01……12)  
            %i： 代表分钟, 格式为(00……59) 
            %r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)  
            %T：代表 时间,格式为24 小时(hh:mm:ss) 
            %S：代表 秒,格式为(00……59)  
            %s：代表 秒,格式为(00……59) 
            8,format 
            查询员工的薪水加入千分位
            select empno,ename,format(sal,0) from emp;
            保留小数点的两位
            select empno,ename,format(sal,2) from emp;
            9，round(四舍五入）
            select round(123.45);
            10,rand()(生成随机数)
            select rand();
            生成一到一百的随机数
            select round(rand()*100+1);
            11，case … when … then …..else …end
            如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%
            select empno, ename, job, sal, case job when 'MANAGER' then sal*1.1 when 'SALESMAN' 
            then sal*1.5 end as newsal from emp;
            12，ifnull
            select ifnull(comm,0) from emp;
            如果comm为null 就替换为 0
            在SQL语句当中若有NULL值参与数学运算，计算结果一定是NULL
            为了防止计算结果出现NULL，建议先使用ifnull空值处理函数预先处理。
            以下SQL是计算年薪的：
            select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;
            
            六，分组函数/聚合函数/多行处理函数
            count          取得记录数
            sum            求和
            avg            取平均
            max            取最大的数
            min            取最小的数
    
            注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。
            select count(*) from emp where xxx;            符合条件的所有记录总数。
            select count(comm) from emp;        comm这个字段中不为空的元素总数。

            注意：分组函数不能直接使用在where关键字后面。
            mysql> select ename,sal from emp where sal > avg(sal);
        
            1,count
            取得所有员工数
            select count(*) from emp;
            取得津贴不为null的员工
            select count(comm) from emp;
            2,sum 
            sum可以取得某一列的和，null会被忽略
            取得薪水的合计
            select sum(sal) from emp;
            取得津贴的合计
            select sum(comm) from emp;
            取得薪水的合计
            select sum(ifnull(comm,0)+sal) from emp;
            3,avg
            取得某一列的平均值
            select avg(sal) from emp;
            4,max
            取得某一列的最大值
            select max(sal) from emp;
            取得最晚入职的员工
            select ename,max(str_to_date(hiredate,'%Y-%m-%d')) from emp;
            5,min 取得某一列的最小值
            select min(sal) from emp;
            取得最早入职的员工
            select ename,min(str_to_date(hiredate,'%Y-%m-%d')) from emp;
            6,组合聚合函数
            select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
            
            六,分组查询
            取得每个工作岗位的工资合计，要求显示岗位名称和工资合计；
            select job,sum(sal) from emp group by job;
            select job,deptno,sum(sal) from emp group by job;
            
            七，having
            如果想对分组数据再进行过滤需要使用having子句
            取得每个岗位的平均工资大于2000的
            select job,avg(sal) from emp group by job having avg(sal)>2000;
            
            select 语句总结
            select 字段
            from    表名
            where    …….
            group by ……..
            having …….   (就是为了过滤分组后的数据而存在的—不可以单独的出现)
            order by ……..
            以上语句的执行顺序
            1.首先执行where语句过滤原始数据
            2.执行group by进行分组
            3.执行having对分组数据进行操作
            4.执行select选出数据
            5.执行order by排序
            原则：能在where中过滤的数据，尽量在where中过滤，
            效率较高。having的过滤是专门对分组之后的数据进行过滤的。