mysql总结(完善版)

mysql总结

一.数据定义语言:DDL(熟悉)

1.操作库:

1.创建库create

create database if not exists 数据库名称 charset 'utf8';

2.删库drop

drop database 数据库名称;

3.查询库show

show databases ;             #展示所有的库
show create database 数据库名;#查看数据库建库信息

4.查询正在使用的库

select current_database();

5.切换数据库

use 数据库名称;/*切换数据库*/

2.操作表

1.创建表

 create table if not exists 表名(
        字段名1 类型 [约束],
        字段名2 类型 [约束],
        字段名3 类型 [约束]
    )engine = 引擎名 default charset utf8;

2.查看表

1.查看有几个表
show tables;              # 展示所有的表
2.查看建表语句
show create table 表名;
3.查看表结构
desc 库名.表名 # 查看每个类的类型和约束
eg:
desc test_sql.book;

3.删除表

drop table 表名;
delete from 表名;
truncate 表名;

4.修改表

1.添加一列
alter table 表名 add 字段名 类型(长度) [first|after 其他字段名称];
eg:
    alter table table_user add information varchar(40);
    alter table table_user add first_name varchar(20) first ;
    alter table table_user add age int;#在末尾添加
    alter table table_user add gender int after age;# 在age元素后边
# 通过修改表.添加主键.
	alter table tb_game2 add primary key (id);

2.删除一列
alter table 表名 drop 列名;
eg:
#删除用户表中的年龄这一列；
alter table table_user drop age;

3.修改类型和约束
alter table 表名 modify 要修改的字段名 类型(长度) [约束];
eg:
#将用户名字段修改长度为16，且添加不为空约束。
alter table table_user modify user_name varchar(16) not null 
# 通过修改表.添加主键.
alter table tb_game2 add primary key (id);

4.修改列名
alter talbe 表名 change 旧列名 新列名 类型 约束;
eg:
#把用户表的信息字段列名修改为info，类型修改为varchar，长度为100。
alter table table_user change information info varchar(100);

5.修改表名
rename table 表名 to 新表名;

二.数据操作语言:DML(操作数据,对数据增删改)(会背)

1.增insert

1.插入指定列数据
insert into 表名[(字段名1,字段名2,字段名3,...)] values(值1,值2,值3,...);
2.全列值插入
insert into 表名 values(值1,值2,值3,...);
3.插入多条数据
(1) insert into 表名[(字段名1,字段名2,字段名3,...)] values (值1,值2,值3,...),(值1,值2,值3,...),...;
(2) insert into 表名 values(值1,值2,值3,...),(值1,值2,值3,...),...;

2.删delete

delete from 表名;
delete from 表名 where 条件;
eg:
delete from user2 where id=1;
delete from user2 where username='李四';

3.改update

update 表名 set 字段名1=值1, 字段名2=值2, 字段名3=值3,...;
update 表名 set 字段名1=值1, 字段名2=值2, 字段名3=值3,... where 条件;
eg: 
update user set gender='男';
update user set gender='女',age = 18;
update user set gender='男' where id = 2;
update user set age = 30 , address='湖北省武汉市' where id = 3;
update user set age = age + 1;

三.数据查询语言:DQL

1.查询所有

1.查询所有
select  *   from    user;       #查询user表中的所有数据
2.指定列查
select 列名,列名,列名.. from 表名
3.去重查询
select distinct 列名  表名;

2.条件查询

select * from 表名  where 条件;
常见条件: = > >= <= < !=

3.范围查询

1.连续范围: between 值  and  值
eg: select * from product where price between 200 and 1000;
2.非连续范围: in (值1,值1,..)
eg: 
select * from product where price in (200,800);
select * from stu where address in ('河南','河北')

4.逻辑查询

(1)and
eg:
#查询河南大于80分的学生
select * from stu where address = '河南'  and score > 80;
(2)or
eg:
#查询河南或者山东的学生
select * form stu where address = '河南' or address = '山东'
(3)not
eg:
#查询不是河南的学生
select * from stu where address not in('河南')

5.模糊查询

(1)like'匹配符号'
查询商品名称含有"香"字的所有商品信息
select * from product where pname like '%香%';
(2)%  多个任意字符
查询以"香"开头的所有商品信息
select * from product where pname like '香%';
(3)_  一个任意字符
eg:查询商品名称为三个字的商品信息
select * from product where pname like '___';
查询商品名称以"斯"结尾，并且是三个字的商品信息
select * from product where pname like '__斯';
查询以"香"开头，且是三个字的商品信息
select * from product where pname like '香__';

6.排序查询 order by[asc/desc]

select * from 表 where 条件 order by 列名 [asc/desc]
(1).升序:第一行最小,慢慢最大
按价格进行升序排序查询所有的商品信息
select * from product order by price;
select * from product order by price asc;# 默认就是升序 asc可以省略
按照价格升序排序查询名称中有"想"字的所有商品信息
select * from product where pname like '%想%' order by price;
(2).降序:第一行最大,慢慢变小
按价格进行降序排序查询所有的商品信息；
select * from product order by price desc;# 降序
(3).双字段排序
eg:
先按照年龄排序.年龄相同按照成绩排序
select * from stu  order by age , score
年龄降序,工龄降序
select * from employee order by eage desc,eworked_year desc ;

7.聚合查询

(1).5个纵向计算函数 
max(列) 
min(列) 
sum(列) 
avg(列) 
count(非空列(主键))--有几条数据(不统计null列)
(2).聚合函数不能和非分组列一起查
select max(mp_max) from archer;
select hp_max ,max(mp_max) from archer;--Expression not in GROUP BY key 'hp_max'

使用命令完成：
（1）查询商品的总条数；
select count(*) from product;# 效率低
select count(pid) from product;# 效率高
（2）查询商品价格的最大值；
select max(price) from product;
（3）查询商品价格的最小值；
select min(price) from product;
（4）加入where条件后，查询价格大于200的商品总条数；
select count(pid) from product where price > 200;
（5）查询分类c001中所有商品的总和；
select sum(price) from product where category_id = 'c001';
（6）查询分类为c002所有商品的平均价格。
select avg(price) from product where category_id = 'c002';

!!!select max(price),min(price) from product;# 多个函数可以同时出现在 select 后边.
#聚合函数的查询结果都是一个值.

8.分组查询 group by

分组查询不能用select * from 表名(*不行)
分组查询的查询结果只能是'分组字段'和'聚合函数'
eg:
/**
  group 练习:
  例如，使用命令完成：
（1）计算平均年龄值；
（2）计算总人物个数；
（3）请统计出不同性别的人平均年龄值；
（4）请统计出不同性别的人总个数。
 */
select avg(age) from person;
select count(id) from person;
select gender,avg(age) from person group by gender;# 分组字段可以显示.
select gender,count(id) from person group by gender;# 分组字段可以显示.
/**
  group by  +  having 条件
  having是做分组后数据的过滤.
  having不能脱离group by单独使用
 */
# 例如，使用命令完成：
# （1）请统计各个性别下人物的总个数；
select gender,count(id) from person group by gender;# 分组字段可以显示.
# （2）请统计各个性别中当性别为女时，人物的总个数；
select gender,count(id) from person where gender = '女';
select gender,count(id) from person group by gender having gender = '女';
select gender,count(id) from person where gender = '女' group by gender;
# （3）请统计各个性别下人物的总个数，且只显示总个数大于2的信息。
select gender,count(id) as total  from person group by gender having total > 2; -- 扩展 as 起别名
select gender,count(id) total  from person group by gender having total > 2; -- 扩展 as 可以省略
select gender,count(id) total  from person group by gender having count(id) > 2; -- total 是别名

9.分页查询

(1) 从XX开始查询
limit start  count
(2) limit m,n
        m:表示index索引位置(数据库中的数据位置)
        n:每次查询的个数 count
(3) start值计算公式:(当前页码-1)*每页展示条数,start从0开始

eg:
（1）从商品信息中开始处查询5条数据；
select * from product limit 0,5;
（2）从商品信息中索引值为5开始，共查询10条数据；
select * from product limit 5,10;
（3）获取当前商品中价格最低的2件商品；
select * from product order by price limit 0,2;
（4）思考：每页显示5条数据，要显示第3页的所有数据，该怎么做？
# 公式: (当前页面-1) * 每页显示条数
select * from product limit 10,5;

10.查询语句的位置顺序

1.执行where 条件(对原始表进行过滤) 
2.执行  group by 
3.执行 聚合函数(用于计算)
4. 执行 having 条件(进一步过滤)
5.执行 order by 排序
6.执行 limit 分页



select  
         列名,聚合函数 as 别名
from 
        表名
where
          条件 
group by  
          列名
having 
          条件 
order by
          列名
limit   m , n;

五.sql约束

1.主键primary key

MySQL建议所有表的主键字段都叫id，类型为int
eg:
create table game5(
    id int primary key auto_increment,
    first_name varchar(20),
    last_name varchar(20) unique ,
    skill varchar(20)
);

2.非空 not null

eg: select * from product where pname is not null;# pname是非空的列
select * from product where not pname is null;# 取反 pname是空

3.唯一 unique (加一个distinct)

eg:
--添加约束 -- 应该在建表的时候加入.
create table game5(
    id int primary key auto_increment,
    first_name varchar(20),
    last_name varchar(20) unique ,
    skill varchar(20)
);
eg:
--建表后修改列
alter table user_detail modify uid int unique ;
eg:
--成功去除重复的价格
select distinct price from product;
--无法去除重复的价格distict计算的是它后方的所有列.
select distinct price,pname from product;
--todo 总结:distinct后边只能跟要去重复的列.不可以跟无关列.distinct必须放在列的开头.

4.默认值 default

当不填写字段对应的值时，会使用默认值；如果填写时，则以填写为准。 

5.外键

涉及到多张表时才会使用。对表与表关系字段进行约束.约束两张表.
外键语法: foreign key (字段名) references 另外表名(主键列名)

六.创建多表关系（了解）

1.一对一

/**
  外键语法: foreign key (字段名) references 另外表名(主键列名)
  外键关联之 一对一关系:
    1.建表方式:在其中一张表中设立外键,但是要求外键唯一.
    2.建表方式:主键关联主键
 */    
create table user( -- 用户表
    id int primary key auto_increment,
    username varchar(20),
    password varchar(20)
);
create table user_detail( -- 用户详情表
    id int primary key auto_increment,
    name varchar(20),
    age int,
    gender varchar(10),
    mobile varchar(20),
    uid int unique ,-- 这是外键,用于连接user表的主键
    foreign key (uid) references user (id) -- 外键uid关联user表中的主键id.
#   foreign key (id) references user (id)-- 主键关联主键 [了解]
);    

2.一对多

在多的一方建立外键,关联一的一方的主键.

/**
   外键语法: foreign key (字段名) references 另外表名(主键列名)
   外键关联之 一对多关系:
   原则: 在多的一方,设立外键关联一的一方的主键.
 */
create table category(--分类表
    cid int primary key auto_increment,
    cname varchar(20)
);

create table product(--商品表
    pid int primary key auto_increment,
    pname varchar(20),
    price double,
    cno int ,-- 在多的一方准备一个列当做外键
    foreign key (cno) references category (cid)-- 设置外键列去关联主表的主键.
);

3.多对多

创建第三张表(中间表),至少有两个外键分别连接两张表的主键

/**
   外键语法: foreign key (字段名) references 另外表名(主键列名)
   外键关联之 多对多关系:
   原则: 准备第三张表,这个表至少有两个外键列,分别指向两个多表的主键.
 */
create table student(--学生表
    sid int primary key auto_increment,
    sname varchar(20)
);
create table course(--课程表
    cid int primary key auto_increment,
    cname varchar(20)
);
create table selection(--选课表
    sno int,#学生编号
    cno int,#课程编号
    foreign key (sno) references student(sid),#学生编号关联学生表主键
    foreign key (cno) references course (cid)#课程编号关联课程表主键
);

/**
  多对多场景练习:
  例如，使用命令完成：
（1）在数据库班级db_product3中，创建货物表、订单表；
（2）货物表的字段有编号、名称、品牌、价格，其中货物编号为整型且自动增长的主键；
（3）订单表的字段信息有编号、订单金额，其中订单编号为字符串型的主键；
（4）创建一个中间表，并关联货物表、订单表；
（5）当成功创建表后，观察效果。
 */
create table goods(
    gid int primary key auto_increment,
    gname varchar(20),
    brand varchar(20),
    price double
);
create table orders(
    oid varchar(20) primary key ,
    money double
);
create table order_item(
    id int primary key auto_increment,
    gno int,
    ono varchar(20),
    foreign key (gno) references goods (gid),
    foreign key (ono) references orders (oid)
);
insert into goods values (1,'usb网卡','绿联',60);
insert into goods values (2,'鼠标','雷神',140);
insert into goods values (3,'u盘','金士顿',200);

insert into orders values (1,200);
insert into orders values (2,340);

insert into order_item values (null,1,1);
insert into order_item values (null,2,1);

insert into order_item values (null,2,2);
insert into order_item values (null,3,2);

七.多表查询

多表查询数据准备

/**
  多表查询方式:准备数据
  例如，使用命令完成：
（1）商品类别表信息有分类编号cid、名称cname；
（2）商品表信息有商品编号pid、名称pname、价格price、是否上架flag、外键category_id（关联商品类别表的分类编号cid）；
（3）添加数据到两个数据表中。
 */
create database db_product4 charset 'utf8';
use db_product4;
create table category(
    cid varchar(20) primary key ,
    cname varchar(20)
);
create table product(
    pid varchar(20) primary key ,
    pname varchar(20),
    price double,
    flag  varchar(2), -- 是否上架标记为：1表示上架、0表示下架
    category_id varchar(20),
    foreign key (category_id) references category (cid)
);

-- 分类
insert into category(cid, cname) values('c001', '电器');
insert into category(cid, cname) values('c002', '服装');
insert into category(cid, cname) values('c003', '化妆品');

-- 商品
insert into product(pid, pname, price, flag, category_id) values('p001', '联想', 5000, '1', 'c001');
insert into product(pid, pname, price, flag, category_id) values('p002', '海尔', 3000, '1', 'c001');
insert into product(pid, pname, price, flag, category_id) values('p003', '雷神', 5000, '1', 'c001');

insert into product (pid, pname, price, flag, category_id) values('p004', 'JACK JONES', 800, '1', 'c002');
insert into product (pid, pname, price, flag, category_id) values('p005', '真维斯', 200, '1', 'c002');
insert into product (pid, pname, price, flag, category_id) values('p006', '花花公子', 440, '1', 'c002');
insert into product (pid, pname, price, flag, category_id) values('p007', '劲霸', 2000, '1', 'c002');

insert into product (pid, pname, price, flag, category_id) values('p008', '香奈儿', 800, '1', 'c003');
insert into product (pid, pname, price, flag, category_id) values('p009', '相宜本草', 200, '1', 'c003');
insert into product (pid, pname, price, flag, category_id) values('p010', '迪奥', 1600, '1', 'c003');
insert into product (pid, pname, price, flag, category_id) values('p011', '一叶子', 799, '1', 'c003');

1.笛卡尔积(查询结果两张表的乘积)

/**
  多表查询一: 交叉连接 [了解]
    语法1:select 字段名 from A表名 cross join B表名;
    语法2:select 字段名 from A表名 , B表名;
  例如，使用交叉查询命令完成：
（1）使用语法1查询类别表和商品表中的所有商品信息；
（2）使用语法2查询类别表和商品表中的所有商品信息。
 */
select * from category cross join product;
select cname,pname from category , product;

2.内连接查询(查询结果两张表的交集)

(1) 显示内连接
select * from 表A join 表B  on 关联条件;
select * from 表A inner join 表B  on 关联条件; --inner可以省略
(2) 隐示内连接
select * from 表A , 表B where 关联条件;



/**
  多表查询一: 内连接 [重点掌握]
    select 字段名 from A表名, B表名 where 条件;
    select 字段名 from A表名 别名 inner join B表名 别名 on 条件 [where 查询条件];
  例如，使用内连接查询命令完成：
（1）使用隐式内连接查询类别表和商品表的共有数据信息；
（2）使用显式内连接来查询类别表和商品表的公共商品信息；
（3）使用显式内连接来查询类别表中cid与商品表category_id相等的公共商品信息的类别编号信息，并去除重复值。
 */
# （1）使用隐式内连接查询类别表和商品表的共有数据信息；
select * from category c ,product p where c.cid = p.category_id; #隐式内连接
# （2）使用显式内连接来查询类别表和商品表的公共商品信息；
select * from product p inner join category c on p.category_id = c.cid;#显式内连接
select * from product p  join category c on p.category_id = c.cid;#显式内连接可以省略inner关键词.
# （3）使用显式内连接来查询类别表中cid与商品表category_id相等的公共商品信息的类别编号信息，并去除重复值。
select distinct c.cid from product p join category c on p.category_id = c.cid;

3.外连接查询(查询结果一张表的全部和两张表的交集)

(1) 左外(查询左表的全部和两张表的交集)
select * from 左表 left join 右表 on 关联条件;
(2) 右外(查询表的全部和两张表的交集)
select * from 左表 right join 右表 on 关联条件;


/*
 多表查询一: 外连接 [重点掌握]
 语法: select 字段名 from A表名 别名 left [outer] join B表名 别名 on 条件 [where 查询条件];
例如，使用左外连接查询命令完成：
（1）以左表为主，连接查询类别表和商品表中的所有商品数据信息。
*/
select * from category c left join product p on c.cid = p.category_id;
# select * from category c right join product p on c.cid = p.category_id;
select * from  product p left join category c on c.cid = p.category_id;

# 查询所有分类和分类下的产品名称.没有产品的分类也要出来.
select c.cid,c.cname,p.pname from category c left join product p on c.cid = p.category_id;

八.子查询==(常用)==

-- 一个select 语句中出现了另一个select 语句.

(1)子查询的结果是一个值(单行单列)使用关系和逻辑运算符进行比较
eg:
select * from 表名 where id = (select id form 表名  where 条件);
(2)子查询的结果是多个值(多行单列)使用in()或者not in()
eg:
select * from 表名 where adderss in (select address form 表名  where address like '%河%');
(3)子查询的结果是一个值(多行多列)当做临时表使用必须起别名
eg:
select * from 表名 a , (select *  form 表名  where 条件) b where id = a.id = b.aid;

九.自连接查询==(不常用)==

把一张表看成两张表进行连接查询,要求表中的其中一列与另一列有从属关系.
语法: select * from 表A  t1, 表A t2 where  t1.xxid = t2.xxid;

/**
  自连接查询: [了解]
    1.不是任何一个表都可以玩自连接查询.必须符合两个列用关系的表才能玩自连接.
    2.把表看成两个表 : select * from area a , area b where a.xx = b.xx;
  例如，使用命令完成：
    （1）创建一个区域表tb_area，信息有省编号pid为整型自增且主键、名称name为字符串、城市编号city_id为整型；
    （2）给区域表tb_area添加批量数据，以(1,'广东省',null)、(4,'深圳市',1)形式让城市和省份进行关联；
    （3）查询所有数据结果。
     (4)自连接查询省的名称为广东省的所有城市信息
 */
create table area(
    pid int primary key auto_increment,
    name varchar(20),
    city_id int
);

insert into area values (1,'广东省',null),(2,'江西省',null),(3,'广州市',1),(4,'深圳',1),
                        (5,'东莞',1),(6,'南昌',2),(7,'赣州',2),(8,'九江',2);

# （3）查询所有数据结果。
select a1.pid,a1.name,a2.name,a2.city_id from area a1,area a2 where a1.pid = a2.city_id;

#  (4)自连接查询省的名称为广东省的所有城市信息
select a1.pid,a1.name,a2.name,a2.city_id from area a1,area a2 where a1.pid = a2.city_id and a1.name = '广东省';

十.扩展

查询练习题

/**
  例如，使用命令完成：
（1）使用数据库班级db_product3下的商品表和分类表来操作；
（2）查询商品表、分类表的所有数据信息；
（3）查询分类为"服装"的所有商品信息；[分类id、商品]
（4）查询商品名称为"格力"的分类信息；               #todo 子查询查出的结果是单个值. >,<,=
（5）查询分类为"化妆品"和电器下的所有商品名称。       # todo 子查询查出的结果是多个值. in()
（6）查询分类编号大于2的分类下所有的商品信息和分类信息. # todo 子查询查出的结果是一个表. 当做临时表使用
 */
（2）查询商品表、分类表的所有数据信息；
select * from category c join product p on c.cid = p.category_id;

（3）查询分类为"服装"的所有商品信息；[分类id、商品]
    #3.1先根据服装这个分类名查询分类id
    select cid from category where cname = '服装';#todo 子查询查出的结果是单个值.
    #3.2根据分类id查询商品信息
    select * from product where category_id = 'c002';# c002是肉眼观察得到的.不科学.
    select * from product where category_id = (select cid from category where cname = '服装');

 （4）查询商品名称为"海尔"的分类信息；
select category_id from product where pname = '海尔';
select * from category where cid = (select category_id from product where pname = '海尔');

 （5）查询分类为"化妆品"和电器下的所有商品名称。
     #5.1先查询化妆品"和电器的cid编号
     #5.2 根据cid编号去商品表查询商品.
select cid from category where cname in('化妆品','电器');# todo 子查询查出的结果是多个值. in()
select * from product where category_id in (select cid from category where cname in('化妆品','电器'));

 （6）查询分类名称是服装和化妆品的分类下所有的商品信息和分类信息.
select * from category where cname = '化妆品' or cname = '服装' ;

select * from product p ,(select * from category where cname = '化妆品' or cname = '服装' ) c
where p.category_id = c.cid;

# 用内连接加上条件也可以实现.
select * from category c join product p on c.cid = p.category_id where cname = '化妆品' or cname = '服装';

# 求出最贵的商品信息.
select max(price) from product;#6000最贵
select * from product where price = (select max(price) from product);

字符串函数

/**
  字符串函数
    ①length(str)函数                               :字符串长度
    ②concat(str1,str2,...)函数                     :字符串连接
    ③upper(str)、lower(str)函数                     :转大写
    ④substr(str,start,len)函数                      :从指定位置截取指定个数的字符串
    ⑤instr(str,要查找的子串)函数                      :判断是否存在 返回 1 或者 0
    ⑥trim(str)函数                                  : 去除两端空格
    ⑦lpad(str,len,填充字符)、rpad(str,len,填充字符)函数:左右两边指定字符填充.然后获取指定长度.
    ⑧replace(str,旧串,新串)函数                : 字符串替换.
 */

select length('hello') ;# 查询传入字符串的的长度.
select length(pname) from product where pid = 'p001';#如果是中文.又是utf8编码一个汉字占用3个字节

# 连接商品名称和商拼价格中间,号分隔.
select concat('hello','123','world');#hello123world
select concat(pname,',',price)from product;# "联想,6000"

# 把pid内容变大写.pname变小写
select upper(pid),lower(pname) from product;#P001 ,jack jones

# 把t_book 表中的书名截取前6个字符
select substr('hello',0,3);# 开始位置必须从1开始
select substr(bname,1,6) from tb_book;# 包1包6 [1,6]

#判断商品名称是否包含 '花'
select instr(pname,'花') from product; # 存在返回1 ,不存在返回 0

#去掉空格.一般做数据清洗
select trim(pname) from product;

select lpad('aaa',4,'#');#: #aaa 左右两边指定字符填充.然后获取指定长度.
select rpad('aaa',4,'#');#: aaa#

select replace('hello','ll','xx');#hexxo

#把霸改成霸王
select replace(pname,'霸','霸王') from product where pid = 'p007';

数学函数

/*
数学函数:
①round(x,保留位数)函数: 四舍五入保留小数位.
②ceil(x)函数        :天花板函数--向上取整
③floor(x)函数       :地板函数 --向下取整
④truncate(x,D)函数  : 按照D的值截取小数部分.
⑤mod(被除数,除数)函数 : 求余数
⑥pow(x,D)函数       : x的D次方
*/
select round(3.1415926,2);# 3.14
select round(3.1415926,3);# 3.142 四舍五入保留小数位.

select ceil(3.14);#4
select floor(3.94);#3
select truncate(3.1415,3);
select truncate(3.1415,4);# 不考虑四舍五入
select mod(10,3);#1
select mod(9,3);#0
select pow(2,2);
select pow(2,3);

#把书的价格调整为整数销售.
select ceil(bprice) from tb_book;

日期格式

/**
    日期格式                            :
    now()函数                          :当前日期和时间
    curdate()函数                      :当前日期
    curtime()函数                      :当前时间
    获取日期和时间中的年、月、日、时、分、秒
    weekofyear()函数                   :一年的第几周
    quarter()函数                     :一年中的季度
    str_to_date()函数                 :字符串转时间类型
    date_format()函数                  :格式化时间字符串
    date_add(日期,interval num 时间)函数 :添加日期
    last_day()函数                      :月度最后一天
    datediff(end_date,start_date)函数   : 时间差 返回天
    timestampdiff(unit,start_date,end_date)函数计算两个时间返回的年/月/日数；
    unix_timestamp(date) : 把日期转毫秒值
    from_unixtime(int): 把毫秒值转 日期
 */

select now();#2024-03-27 16:09:49
select curdate();#2024-03-27
select curtime();#16:09:49
select year('24-11-11');#2024
select year(now());#2024# 从年月日时分秒中获取年.
select substr(now(),1,4);#2024 -- 探索函数实现原理
select substr(now(),6,2);#03
select month(now()) ;# 3

#查询今日是一年的第几周
select weekofyear(now());# 13周
#查询今日是一年的那个季度
select quarter(now());#1
select quarter('2024-06-09');#2

#字符串时间转日期类型
SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');# str_to_date要求格式比较固定
select str_to_date('04-06-2024','%m-%d-%Y');

# date_format()函数  :格式化时间字符串
select now();#2024-03-27 16:28:12
select date_format(now(),'%Y/%m/%d %H:%i:%s');#2024/03/27 16:34:02
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');#2024年03月27日 16时33分15秒

#查询明天的日期
select date_add(now(),interval 1 day );# ;2024-03-28 16:37:35
#查询明年的日期
select date_add(now(),interval 1 year );# ;2025-03-27 16:37:35
#查询去年的日期
select date_add(now(),interval -1 year );# ;2023-03-27 16:37:35

#计算本月最后一天
select last_day('2024-02-01');# 参数为date
select last_day('2024-12-01');

#  datediff(end_date,start_date)函数   : 时间差
# 计算到今年5月1还有几天
select datediff('2024-05-01',now());
select timestampdiff(day,now(),'2024-05-01');#34天
select timestampdiff(month ,now(),'2024-06-01');# 1
select timestampdiff(year ,now(),'2025-06-01');# 1

# unix_timestamp(date) : 把日期转毫秒值
select unix_timestamp(now());#1711529268
# from_unixtime(int): 把毫秒值转 日期
select from_unixtime(1711529268);#2024-03-27 16:47:48 日期格式是默认格式.
select from_unixtime(1711529268,'%Y年%m月%d日 %H时%i分%s秒');#2024年03月27日 16时47分48秒 日期格式是指定格式

区间范围的判断

/**
  ①if(expr,v1,v2)函数   : 判断数据给出返回值
  ②ifnull()函数         : 判断空给出返回值
  ③nullif(expr1,expr2)   : 相同返回null 不同保留表达式1的原值.
  ④case…when函数用法     : 多条件判断给出返回值.
    #区间范围的判断.
    # Case
    # When condition1 Then result1
    # When condition2 Then result2
    # ...
    # Else result_n
    # End
    #--------------------------------------
    # 固定值的判断.
    # case 列名
    # when 值 then 值
    # when 值 then 值
    # ....
    # else 值
    end
 */

# ①if(expr,v1,v2)函数   : 判断数据给出返回值
# 执行流程: 表示式成功,返回v1,表达式失败,返回v2.
select if(5 > 3,'成功','失败');#成功
select if(5 < 3,'成功','失败');#失败

#对商品表查询.如果价格超过1000显示奢侈品.否则显示平价商品.
select pname,price,if(price>1000,'奢侈品','平价商品') as type from product;

#如果分类id是null 那么显示默认分类.不是null直接显示.
select pname,price, if(category_id is null,'默认分类',category_id) from product;

# ②ifnull()函数         : 判断空给出返回值
select pname,price, ifnull(category_id,'默认分类') from product;

# ③nullif(expr1,expr2)   : 相同返回null 不同保留表达式1的原值.
select nullif('aaa','bbb');
select nullif('aaa','aaa');

# product表不是1号分类的商品正常显示名称.1号分类展示null
select pname,price,nullif(category_id,'c001') from product;

case … when的用法

/**
  ④case…when函数用法     : 多条件判断给出返回值.
    #区间范围的判断.
    Case
        When condition1 Then result1
        When condition2 Then result2
        ...
        Else result_n
    End
    --------------------------------------
    固定值的判断.
    case 列名
        when 值 then 值
        when 值 then 值
        ....
        else 值
    end
 */


select
case
 when 50>=90 then '优秀'
 when 50>= 70 then '良好'
 when 50= 60 then '及格'
 else '不及格'
end ;

# 使用case when来查询商品表.如果价格在1000以上是奢侈品.500-1000属于高端品.500以下就是平价商品.
select pname,price,
        case
        when price > 1000 then '奢侈品'
        when price >=500 and price <= 1000 then '高端品'
        when price < 500 then '平价商品'
        end as type
from product;


# 固定值的判断.
#     case 列名
#         when 值 then 值
#         when 值 then 值
#         ....
#         else 值
#     end
# 商品表.如果商品类别是c001显示电器,是c002显示服装,是c003显示化妆品.

select pname,price,
       case category_id
            when 'c001' then '电器'
            when 'c002' then '服装'
            when 'c003' then '化妆品'
            else '其它'
       end category_name
       from product;