mysql从入门都精通（3）-日期函数，时间分量，日期格式化，聚合函数，字符串函数

###上次总结：
####数据库相关SQL
1. 查询所有 show databases;
2. 创建 create database db1 character set utf8/gbk;
3. 查看详情 show create database db1;
4. 删除 drop database db1;
5. 使用 use db1;
####表相关SQL
1. 查询所有 show tables;
2. 创建表  create table t1(name varchar(10),age int);
3. 查看详情 show create table t1;
4. 字段信息 desc t1;
5. 删除表  drop table t1;
6. 删除并创建新的  truncate table t1;
7. 修改表名 rename table t1 to t2;
8. 改引擎和字符集  alter table t1 engine=myisam/innodb charset=utf8/gbk;
9. 添加字段 alter table t1 add age int first/after xxx;
10. 删除字段 alter table t1 drop age;
11. 修改名称和类型 alter table t1 change age newAge int;
12. 修改类型和位置 alter table t1 modify age int first/after xxx;
####数据相关
1. 插入数据 insert into t1 (name,age) values(值1,值2),(值1,值2);
2. 查询数据 select name,age from t1 where age<30;
3. 修改数据 update t1 set age=18, name='aaa' where id=3;
4. 删除数据 delete from t1 where id=10;
####day02
1. 主键约束+自增 唯一且非空 primary key auto_increment
2. 注释： comment 
3. '和`
4. 冗余：重复数据  通过拆分表解决
5. 事务：数据库中执行sql语句的最小工作单元，保证里面的多条SQL语句全部成功或全部失败 show variables like '%autocommit%';  set autocommit=0/1;  提交commit  回滚rollback   回滚点 savepoint s1; rollback to s1;
6. SQL分类
- DDL 数据定义语言 包括：create alter drop truncate 不支持事务
- DML 数据操作语言 包括：insert delete update select(DQL) 支持事务
- DQL 数据查询语言 包括：select 
- TCL 事务控制语言 包括：commit rollback savepoint 。。。
- DCL 数据控制欲要 分配用户权限相关SQL 
7. 数据类型
- 整数： int(m) 和bigint   m代表显示长度 需要结合zerofill使用
- 浮点数：double(m,d) m代表总长度 d代表小数长度  decimal超高精度浮点数
- 字符串：char固定长度 执行效率高 最大255 varchar可变长度 节省空间 最大65535超高255建议使用text 
- 日期： date保存年月日  time保存时分秒  datetime：9999-12-31 默认为null   timestamp：最大2038-1-19 默认值为当前时间

 

###is null 和 is not null
    CREATE TABLE `emp` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `salary` int(11) DEFAULT NULL,
      `dept` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert into emp values(1,'刘备',25,4000,'三国部'),
                            (2,'关羽',24,8000,'三国部'),
                            (3,'张飞',22,3000,'三国部'),
                            (4,'悟空',250,5000,'取经部'),
                            (5,'八戒',350,6000,'取经部'),
                            (6,'沙僧',450,7000,'取经部'),
                            (7,'唐僧',50,8000,'取经部');
                            
1. 查询emp表中id为空的，姓名name，薪水salary ,工资salary

    select name,salary from emp where id is null;
3. 查询所有id不为空的所有员工信息
    select * from emp where id is not null;
###别名
1. 将查询到的员工姓名ename改成‘姓名’
    select name from emp;
    select name as '姓名' from emp; 
    select name '姓名' from emp;
    select name '姓名',salary '工资' from emp;

###去重 distinct
1. 查询emp表中出现的所有部门dept
    select distinct dept  from emp;

###比较运算符 >,<,>=,<=,=,!=和<>
1. 查询工资小于等于1600的所有员工的姓名和工资
    select name,salary from emp where salary<=1600;
2. 查询部门编号是取经部的所有员工姓名、职位和部门编号deptno
    select ename,job,deptno from emp where deptno='取经部';
3. 查询部门不是取经部门的所有员工信息(两种写法)
    select * from emp where dept!='取经部';
    select * from emp where dept<>'取经部';

###and 和 or
- and等效java中的&&
- or等效java中的||
- 查询年龄大于50岁并且在取经部的员工信息    
    select * from emp where age>50 and dept='取经部';
2. 查询年龄大于24，或者在取经部的
    select * from emp where age>24 or dept='取经部';

###in 
1. 查询emp表中年龄在22,25,50的员工信息
    select * from emp where age =22 or age=25 or age=50;
    select * from emp where age in(22,25,50);
    
###between x and y  包含x和y
1. 查询年龄在22到50之间的员工姓名和工资    
    select * from emp where age>=22 and age<=50;
    select * from emp where age between 22 and 50;
    
###模糊查询 like 
- _:代表单个未知字符
- %：代表0或多个未知字符
- 举例：
1. 以a开头的字符串    a%
2. 以m结尾   %m
3. 包含x    %x%    
4. 第二个字符是a    _a%
5. 倒数第三个字母是m  %m__
6. 以a开头并且倒数第二个字母是b    a%b_
- 案例：
    update emp set name='liubei' where name='刘备';
    update emp set name='zhangfei' where name='张飞';
    update emp set name='guanyu' where name='关羽';
1. 查询名字中包含a的所有员
    select * from emp where name like '%a%';
2. 查询名字中倒数第三个字母是f的员工信息
    select * from emp where name like '%f__';

###排序 order by 
- order by 关键字， by后面写排序的字段名称 默认是升序(asc)， 
- asc升序 desc降序
1. 查询所有员工的姓名和工资按照年龄升序/降序排序
    select * from emp order by age asc;
    select * from emp order by age desc;

###分页查询 limit 
- limit 跳过的条数,请求的数量（每页的数量）
1. 查询员工表中年龄降序的前5条数据
    select * from emp order by age desc limit 0,2;
2. 查询员工表中年龄降序的第3页的4条数据
    select * from emp order by age desc limit 1,4;
3. 查询商品表中年龄升序的前10条数据
    select * from emp order by age limit 0,10;
4. 查询员工中年龄大于22岁的第2页的3条数据
    select * from t_item where price<100 limit 3,3;

###concat()函数
- 把concat内部的参数拼接到一起  
1. 查询员工姓名和年龄，要求年龄单位是岁   
    select name, concat(age,'岁')from emp;
###数值计算 + - * / %
    mod(7,2)等效 7%2  取余；
    select mod(7,2); //1;

###日期相关函数
1. 获取当前的 年/月/日/时/分/秒
    select now();
2. 获取当前的日期 年/月/日
    select curdate();
3. 获取当前的时间 时/分/秒
    select curtime();    
4. 从年月日时分秒中提取年月日
    select date(now());
5. 从年月日时分秒提取时分秒
    select time(now());
    
6. 从年月日时分秒中提取时间分量 年  月  日  时 分 秒
- extract(year from now())
- extract(month from now())
- extract(day from now())
- extract(hour from now())
- extract(minute from now())
- extract(second from now())
select extract(year from now());

- 查询员工表中的所有员工姓名和入职的年份 
    例如：select ename,extract(year from hiredate) from emp;
    
7. 日期格式化 date_format(时间,格式)
- %Y:四位年 2018
- %y:两位年 18
- %m:两位月 05
- %c:一位月 5
- %d:日   
- %H: 24小时
- %h: 12小时
- %i: 分
- %s: 秒
    select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');

    
- 把非标准的日期字符串转成标准的时间格式 str_to_date(时间字符串,格式)
    26.12.2018 17:19:10 
    select str_to_date('26.12.2018 17:19:10','%d.%m.%Y %H:%i:%s');  //结果为 2018-12-26 17:19:10
    select str_to_date('26-12-2018 00:28:17','%d-%m-%Y %s:%i:%H'); //结果为  2018-12-26 17:28:00

    select str_to_date('2018年12月28号','%Y年%m月%d号'); //2018-12-28
    注：格式要一致它才能识别 
    
###ifnull(x,y)函数
- age=ifnull(x,18) 如果x的值为null 则age=18 如果不为null则 age=x
1. 修改员工表中奖金为null的值为0
    update emp set comm=ifnull(comm,0);
    
###聚合函数
- 用于对多条数据进行统计 
1. 求和 sum(字段名)
    - 查询emp中10号部门的工资总和
        select sum(age) from emp where dept='取经部';
2. 平均值 avg(字段名)
    - 查询emp表中所有员工的工资平均值
        select avg(age) from emp;
3. 最大值 max(字段名)
    - 查询30号部门的员工的最高奖金
        select max(age) from emp where dept='取经部';
4. 最小值 min(字段名)
    - 查询商品表中价格最便宜的商品的单价
        select min(age) from emp;
5. 统计数量 count(字段名) 一般使用count(*)
    - 统计30号部门有多少人
        select count(*) from emp where dept is not null;
        

###和字符串相关函数
1. 获取字符串的长度 char_length(str);
    获取所有员工的姓名和姓名的字符长度 
    select name,char_length(name) from emp;
2. 获取字符串在另外一个字符串中出现的位置 instr(str,substr)
    select instr('abcdefg','d');
3. 插入字符串 insert(str,start,length,newStr)
    select insert('abcdefg',3,2,'m');  //abmefg
4. 转大写 转小写
    select upper('abc'),lower('NBA');
5. 左边截取和右边截取
    select left('abcdefg',2) , right('abcdefg',2);
6. 去两端空白 
    select trim(' a b ');
7. 截取字符串
    select substring('abcdefg',3,2);
8. 重复 repeat(str,count)
    select repeat('ab',2); 
9. 替换 replace(str,old,new)
    select replace('This is mysql','my','your');
10. 反转 reverse(str)
    select reverse('abc');

###总结：
1. is null 和 is not null
2. 别名 
3. 去重 distinct
4. and 和 or 
5. 比较运算符  >  <  >=  <= =  !=和<>
6. in
7. between x and y 
8. like    _单个未知  %0或多个未知
9. order by age asc/desc,sal
10. limit 12,3
11. concat(abc,mm)
12. 数值运算  + - * / %和 mod()
13. 日期  now()  curdate()  curtime() date(now())  time(now())  extract(year/month/day/hour/minute/second from now())   date_format(now(),'格式')   Y y m c d H h i s  , str_to_date(时间字符串，格式)
14. age=ifnull(x,y)  如果x=null age=y  x！=null age=x
15. 聚合函数  sum() avg() max() min() count(*)
16. 字符串  char_length   instr    insert   upper lower  left right substring   replace  repeat  reverse trim