MySQL数据库--知识文档(全)

于 2024-04-22 08:30:00 发布
阅读量1.5k 收藏 27
点赞数 11
文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_65265621/article/details/137611298
版权

MySQL数据库

一、数据库的本质

1、数据库就是存储在硬盘上的格式化文件，通过一些手段可以很方便的这些文件进行增删改查

二、数据库的分类

1. 关系型数据库

① 特点

1、所有的数据都是由一张张表组成，这些表之间存在着关联关系

② 常见的数据库

MySQL、Oracle、SqlServer、DB2、PostgreSQL、SQLite

2. 非关系型数据库

① 特点

数据是以键值对的形式存放（name:'张三'） (age:18)

② 常见的数据库

Redis、HBase、MangDB、Neo4j ....

三、 MySQL的介绍

1. 特点

 1、MySQL是关系型数据库
 2、MySQL一张表数据可以达到千万行数据量
 3、MySQL支持标准SQL语言

2. 版本

  社区版:免费
  企业版:收费
  集群版：免费/收费
  
  #基础课程使用的是MySQL8.0社区版

四、SQL介绍

1. 概述

SQL(Structured Query Language)结构化查询语言，专门用于操作数据库的语言

2. DDL语言

操作数据库和表整体：
1）对数据整体进行增删改 （不涉及内容）
2）对表整体进行增删改（不涉及内容）

3. DML语言

对数据表的内容进行增删改： insert、update、delete

4. DQL语言

对数据表内容进行查询

5. DCL语言

用于权限控制，了解即可

6. 语法要求

1、SQL语句可以单行或多行书写，以分号结尾。
	select * from students;
	
2、可使用空格和缩进来增强语句的可读性
	
3、MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
例如：SELECT * FROM userSelect * from user; 

4、可以使用/**/的方式完成注释  --  # 
/**/：多行注释，在注释区域内可以随意换行
-- # ：单行注释，写在语句开头，换行后注释截止。
注释快捷键：ctrl+/

五、进入MySQL终端

#进入：
 mysql -uroot -p123456
#退出：
 exit
#查询
select * from student;

在这里插入图片描述

六、DDL操作

1. 操作数据库

  ###############一、数据库操作#######################
  -- 1、创建数据库
  create database bigdata_db;   #第一种写法，如果数据库存在则报错
  create database if not exists  bigdata_db;#第二种写法，如果数据库存在则不报错
  
  -- 2、查看所有的数据库
  show databases;
  
  -- 3、删除数据库
  drop database bigdata_db; #第一种写法
  drop database if exists bigdata_db; #第二种写法，如果存在则删除
  
  -- 4、选择要操作哪个数据库
  use bigdata_db;
  
  -- 5、查看当前正在使用哪个数据库
  select database();

2. 操作表

  ###############二、表操作#######################
  
  -- 1、创建表
  /*
   tinyint: - 128 到  127
   smallint: 0 到  65535
   mediumint：0 到  16777215
   int      : -2147483648 到  -2147483647
   bigint   :-9223372036854775808 到 2^63-1 (9223372036854775807)
   */
  use bigdata_db;
  create table if not exists student(
      id int,
      name varchar(20),  -- '李涛、张三丰 、皇甫艳艳,澹台玄仲、古力那扎尔·拜合提亚尔'
      sex  varchar(2),
      age  tinyint
  );
  
  
  create table category(
      cid varchar(20) primary key ,
      cname varchar(100)
  );
  
  -- 2、查看所有的表
  show tables ;
  
  -- 3、查看表结构（查看该表有几个列，每列叫啥名字，每列啥类型）
  desc student;
  
  -- 4、删除表
  drop table  if exists  student;
  drop table  if exists  category;
  
  
  -- 5、修改表结构(了解)
  -- 5.1 给student表添加address列
  alter table student add address  varchar(64);
  #如果你添加的列名和关键字冲突，则可以给列名上边加上一个飘号(反引号)
  alter table student add `address`  varchar(64);
  
  -- 5.2 将student的address列名修改为addr
  
  alter table  student change address addr varchar(64);
  
  -- 5.3 将student的addr列删除
  alter table  student drop addr;
  
  
  -- 5.4 将student表名修改为stu
  rename table student to stu;

七、DML操作

###############DML-三、表数据操作#######################
-- 1、给student表添加数据
-- insert into 表 (字段1,字段2,字段3...) values(值1,值2,值3...);

insert into student(id,name,sex,age) values (1,'刘备','男',43);
insert into student(id,name) values (2,'关羽');
insert into student values (3,'张飞','男',38);
insert into student values
                        (4,'小乔','女',18),
                        (5,'大乔','女',19),
                        (6,'赵云','男',21);

-- 2、修改student表数据
-- update 表名 set 字段名=值,字段名=值,...;
-- 将张飞的年龄修改为88
update student set age = 88;
update student set age = 88 where name = '张飞';
update student set age = 88 where name = '张飞';

-- 将曹操的性别修改为女，将年龄修改为99
update  student set sex = '女' , age = 99 where id = 7;

-- 将每个人的年龄加上10岁
update student set age = age + 10;

-- 将曹操的id修改为77
update student set id = 77 where id = 7;

-- 将大乔和小巧的年龄减去50
update student set age = age - 50 where name = '小乔' or name = '大乔';
update student set sex = '男' where id >= 3 ;


-- 3、删除student表数据
-- delete from 表名 [where 条件];
delete from student;

-- 删除关羽
delete from student where name = '关羽';


-- 删除大乔，小巧，赵云
delete from student where name = '大乔' or  name = '小乔' or name = '赵云';
delete from student where name in('大乔','小乔','赵云');

八、约束操作

1. 建表时添加约束

① 主键约束

###############DML-四、表的约束#######################
-- --------------------1、主键约束------------------
/*
  1、主键约束：primary key
  2、一个表只能有一个主键（一个主键可以包含一列，也可以包含多列）
  3、主键列特点：非空 + 唯一
     空值唯一的定义：null
 */

-- 1、创建person表,指定主键
create table if not exists  person(
    id int primary key ,  #指定该列是主键列
    name varchar(20),
    address varchar(64),
    city varchar(20)
);

desc person;

insert into person values (1,'林冲','中国开封','开封市');
insert into person values (1,'智深','中国忻州','五台山');  #不能添加主键重复值
insert into person values (null,'智深','中国忻州','五台山');  #不能给主键列添加空值

-- 如果主键列是字符串类型，则注意空值问题
create table if not exists  person2(
    id varchar(20) primary key ,  #指定该列是主键列
    name varchar(20),
    address varchar(64),
    city varchar(20)
);

insert into person2 values ('','智深','中国忻州','五台山');    #''不是null，可以添加
insert into person2 values ('','智深','中国忻州','五台山');    #不可以添加
insert into person2 values (null,'智深','中国忻州','五台山');  #不能给主键列添加空值

-- 在定义表之后单独指定主键列
create table if not exists  person3(
    id varchar(20) ,
    name varchar(20),
    address varchar(64),
    city varchar(20),
    primary key (id)  -- 在这里指定id为主键列
);


-- 联合主键：多列组成一个主键,经纬度
/*
  1、添加数据时，联合主键的列不能一模一样，只要有一列不同即可
  1、添加数据时，联合主键的列，每一列都不能为空，否则报错
 */
create table if not exists  person4(
    id varchar(20)  ,
    name varchar(20) ,
    address varchar(64),
    city varchar(20),
    primary key (id,name)
);


insert into person4 values ('1','智深','中国忻州','五台山');
insert into person4 values ('1','林冲','中国开封','开封市');
insert into person4 values ('2','智深','中国开封','开封市');

insert into person4 values (null,'智深','中国忻州','五台山'); #id列不能为空


-- --------------------1、主键约束-自动增长------------------
/*
   1、自动增长关键字auto_increment只能和主键一起使用
   2、自动增长的列一般是数字列，不能用于联合主键
   3、自动增长默认是从1开始
   4、如果你使用delete删除数据之后，下次主键依然从最大的值开始加1，不会再从从1开始
   5、如果你想要主键从1开始重新增加，则可以使用truncate table 表名 来实现，
      该命令内部是先删除表，再创建新表
 */
create table if not exists  person5(
    id int  primary key auto_increment ,  #指定该列是主键列
    name varchar(20),
    address varchar(64),
    city varchar(20)
);

insert into person5(name,address,city) values ('林冲','中国开封','开封市');
insert into person5 values (null,'林冲','中国开封','开封市'); #设置为null，自己增加
insert into person5 values (5,'林冲','中国开封','开封市'); #手动设置id值
insert into person5 values (null,'林冲','中国开封','开封市'); #下次从设置的id值+1开始

delete from person5;

-- 删除数据之后，可以通过以下该命令来修正自动增长的初始值
alter table person5 AUTO_INCREMENT 3;



insert into person5 values (null,'林冲','中国开封','开封市');

-- 如果你想让表的主键重新从1开始编号，你应该删除该表，再建新表，也就是使用命令：truncate table person5;
truncate table person5;
insert into person5 values (null,'林冲','中国开封','开封市');


set auto_increment_offset  = 88;

insert into person5 values (null,'林冲','中国开封','开封市');  #从最高的加1

desc person5;

② 非空约束

-- --------------------2、非空约束------------------
/*
   1、非空约束关键字：not null
   2、如果某一列加了非空约束,该列的值不能为空
   3、一张表可以多个列都添加非空约束
 */

create table if not exists  person6(
    id int  primary key auto_increment ,  #指定该列是主键列
    name varchar(20) not null,
    address varchar(64),
    city varchar(20)
);

insert into person6 values (null,'林冲','中国开封','开封市');  #可以
insert into person6 values (null,'','中国开封','开封市');     #可以
insert into person6 values (null,'null','中国开封','开封市');     #可以
insert into person6 values (null,null,'中国开封','开封市');     #不可以
insert into person6(address,city) values ('中国开封','开封市');     #不可以

desc  person6;

③ 唯一约束

-- --------------------3、唯一约束------------------
/*
 1、唯一约束添加之后，该列的值必须唯一
 2、唯一约束关键字：unique
 3、唯一约束的列可以添加多个null值，null非常特殊，null和任何值都不相同，和自己也不相同
 */
drop table person7;
create table if not exists  person7(
    id int  primary key auto_increment ,  #指定该列是主键列
    name varchar(20) not null,
    address varchar(64) unique ,
    city varchar(20)
);
insert into person7 values (null,'林冲','中国开封','开封市');  #可以
insert into person7 values (null,'林冲','中国开封','开封市');  #重复
insert into person7 values (null,'林冲',null,'开封市');  #可以
insert into person7 values (null,'林冲',null,'开封市');  #可以，null和任何值都不相同，甚至和自己都不相同
insert into person7 values (null,'林冲',null,'开封市');  #重复

select  * from person7;
select  * from person7 where 1=1;
select  * from person7 where null=null;  #没有任何结果

desc person7;


-- 联合唯一，多个字段只要有一列不同即可
create table if not exists  person77(
    id int  primary key auto_increment ,  #指定该列是主键列
    name varchar(20) not null,
    address varchar(64)  ,
    city varchar(20),
    unique (address,city)
);

insert into person77 values (null,'林冲','中国','开封市');
insert into person77 values (null,'林冲','中国','洛阳市');
insert into person77 values (null,'林冲','河南','开封市');

④ 默认值约束

-- --------------------4、默认值约束------------------
drop table person8;
create table if not exists  person8(
    id int  primary key auto_increment ,  #指定该列是主键列
    name varchar(20) not null,
    address varchar(64) unique ,
    city varchar(20) default '北京'
);

insert into person8 values (null,'林冲','中国开封',null);  # city是null
insert into person8(id,name,address) values (null,'林冲','中国洛阳'); #city默认是北京
insert into person8 values (null,'林冲','中国杭州','杭州市'); #city指定为洛阳
insert into person8 values (null,'林冲','中国南京','南京市'); #city指定为洛阳
insert into person8 values (null,'林冲','中国武汉','武汉市'); #city指定为洛阳

⑤ 约束叠加

-- --------------------5、约束叠加------------------
drop table if exists person9;
create table if not exists  person9(
    id int  primary key auto_increment ,  #指定该列是主键列
    name varchar(20) not null,
    address varchar(64) unique ,
    city varchar(20) not null  default '北京'
);

insert into person9 values (null,'林冲','中国武汉','武汉市');
insert into person9(id,name,address) values (null,'林冲','中国武汉1');
insert into person9(id,name,address) values (null,'林冲','中国武汉2');


insert into person9 values (null,'林冲','中国武汉',null);


desc person9;


desc person2;

2. 建表后添加约束(了解)

-- -----------------主键约束----------------------
-- 1、添加主键约束
-- ALTER TABLE 表名 ADD PRIMARY KEY(字段)
drop table student2;
CREATE TABLE student2(
    sno int ,
    sname VARCHAR(10),
    sex VARCHAR(5),
    age INT
);
alter  table student2 add primary key (sno);

-- 1、给主键添加自动增长
# ALTER TABLE 表名 MODIFY 字段名 数据类型 AUTO_INCREMENT

alter table student2 modify sno  int primary key auto_increment;


-- 2、删除删除主键约束
-- ALTER TABLE 表名 DROP PRIMARY KEY  #因为主键是唯一的，不需要指明主键名
alter table student2 modify sno  int  ;  #删除自动增长
alter table student2 drop primary key ;  #删除主键
alter table student2 modify sno  int null ;  #删除非空



-- -----------------非空约束----------------------
-- 1、添加非空约束
-- ALTER TABLE student2 MODIFY sex VARCHAR(10) NOT NULL;
alter table student2 modify  sex  VARCHAR(10) not null;

-- 2、删除非空约束
alter table student2 MODIFY sex VARCHAR(10) null;


-- -----------------唯一约束----------------------
-- 1、添加唯一约束-方式1
-- age_uni是唯一约束的名字
alter table student2 add  unique (sex);  #如果没有给唯一约束起名字，则约束名字就是该列的列名
alter table student2 add constraint  age_uni unique (age);

-- 1、添加唯一约束-方式2
alter table student2 modify sname varchar(10) unique ;

-- 2、删除唯一约束
drop index age_uni on student2;
ALTER TABLE student2 DROP INDEX sex;


-- -----------------默认约束----------------------
-- 1、添加默认约束
alter table student2 modify age int default  18;

-- 2、删除默认约束
alter table student2 modify age int;

九、DQL操作

简单查询

###############DQL-五、表的查询#######################

-- 1、准备查询的数据
# 创建商品表
drop table if exists  bigdata_db.product;

CREATE TABLE bigdata_db.product
(
    pid         INT PRIMARY KEY,
    pname       VARCHAR(20),
    price       DOUBLE,
    category_id VARCHAR(32)
);

INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(8,'海澜之家',1,'c002');

INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(9,'香奈儿',800,'c003');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(10,'相宜本草',200,'c003');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(11,'面霸',5,'c003');

INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(12,'好想你枣',56,'c004');
INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(13,'香飘飘奶茶',1,'c005');


-- 1、select基本查询
/*
格式：select [distinct]*| 列名,列名 from 表 [where 条件]
 */

-- 1.1 查询所有商品
select pid,pname,price,category_id  from product;
select *  from product;

-- 1.2 查询商品名和价格
select pname,price from product;

-- 1.2 查询所有商品加10元之后的价格
-- 给列起别名:  列表达式 as 别名   （as可以省略）
select pname, price+10 as new_price from product;
select pname, price+10 new_price from product;

条件查询

在这里插入图片描述

###############DQL-五、表的查询#######################


-- 1、select基本查询
/*
格式：select [distinct]*| 列名,列名 from 表 [where 条件]
 */

-- 1.1 查询所有商品
select pid,pname,price,category_id  from product;
select *  from product;

-- 1.2 查询商品名和价格
select pname,price from product;

-- 1.2 查询所有商品加10元之后的价格
-- 给列起别名:  列表达式 as 别名   （as可以省略）
select pname, price+10 as new_price from product;
select pname, price+10 new_price from product;


-- 2、条件查询
# 2.1 查询商品名称为“花花公子”的商品所有信息：
select * from product where pname = '花花公子';

# 2.2 查询价格为800商品
select * from product where price = 800;

# 2.3 查询价格不是800的所有商品
select * from product where price != 800;
select * from product where price <> 800;
select * from product where not price = 800;
select * from product where not price in (800) ;

# 2.4 查询价格是200 或者 800的商品信息
select * from product where  price in (200,800) ;
select * from product where  price = 200 or price = 800;

# 2.5 查询商品价格大于60元的所有商品信息
select * from product where price > 60;

# 2.6 查询商品价格小于等于800元的所有商品信息
select * from product where price <= 800;

# 2.7 查询商品价格在200到1000之间所有商品

select * from product where price between 200 and 1000;
select * from product where price >= 200  and price <= 1000;

# 2.8 查询以'香'字开头的商品
select * from product where pname like '香%';

# 2.8 查询第二个字为'想'的商品
-- 这里的下划线_ 用来匹配任意一个字符
select * from product where pname like '_想%';

# 2.9 查询四个字的商品信息
select * from product where pname like '____';


# 2.10 查询category_id 不为null的商品
select * from product where  category_id is not null;
select * from product where not category_id is  null;

# 2.10 查询category_id 为null的商品
select * from product where category_id is  null;


select  * from product where category_id = '';
select  * from product where length(category_id) = 0;
select  * from product where length(trim(category_id)) = 0;


select * from product   where pname like '香%' and price > 200;
select * from product  where substring(pname,5,1)= '茶';

排序查询

-- 3、排序查询
/*
 格式：SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
 ASC 升序 (默认)    ascending
 DESC 降序         descending
 */

-- 3.1 按照价格升序排序(默认是升序)
select * from product order by price asc;
select * from product order by price ;

-- 3.2 按照价格降序排序
select * from product order by price desc;

-- 3.3 使用字段表达式排序
select pid,pname,price+10,category_id from product order by price+10 desc ;


-- 3.4 排序使用多个字段
-- 先按照price进行升序排序，如果price相同，则按照pid升序排序
-- 前者是排序的主要条件，后者是排序的次要条件，只有前者相同，或者才能生效
/*
  order by 颜值,能力,;
 */
select * from product order by price,pid;

select * from product order by price desc ,pid asc;

聚合查询

-- 4. 聚合查询
/*
   count(* 或者 1或者 字段名):count不会统计null值
   sum(字段名) ： 对某一列求和
   avg(字段名) : 对某一列求平均值,如果某列的值是null，则将这一行剔除出去，求剩余数值的平局值
   max(字段名) : 对某一列求最大值
   min(字段名) : 对某一列求最小值
 */

# 4.1、查询商品的总条数
select count(pid)  from product;  #统计pid列有13行不为null的值
select count(category_id)  from product;  #统计category_id列有12行不为null的值
select count(1)  from product;  #统计该表的行数 第一代写法
select count(*)  from product;  #统计该表的行数 第二代写法

# 4.2、查询价格大于200商品的总条数
select * from product where price > 200 ;
select count(*)  from product where price > 200 ;

# 4.3、查询分类为'c001'的所有商品的价格总和
select  sum(price) from product ;
select  sum(price) from product where category_id = 'c001'

# 4.4、查询分类为'c002'所有商品的平均价格
select avg(price) from product;
select avg(price) from product where category_id = 'c002';

# 4.5、查询商品的最大价格和最小价格
select max(price) from product;
select min(price) from product;
select * from product where price = (select max(price) from product);

select max(price) max_price ,min(price) min_price from product;
select max(category_id) max_price ,min(category_id) min_price from product

分组查询

在这里插入图片描述
在这里插入图片描述

-- 5. 分组查询
/*
  1、分组时，group by后边如果跟的是A字段，则select后边只能跟A字段和聚合函数
  2、分组之后，如果还想进行条件筛选，必须用having，不能用where，where用在分组之间的条件筛选
 */
-- 5.1 查询每一种分类商品的个数

select category_id,count(*) from product group by category_id;

-- 5.2 查询每一种分类商品的个数,并筛选出商品个数大于3的信息

select category_id,count(*) from product group by category_id having count(*) > 3;
select category_id,count(*) as cnt from product group by category_id having cnt > 3;

-- ctrl + alt + l
select
    category_id,
    count(*) as cnt
from product
group by category_id
having cnt > 3;


-- 分组之后跟多个字段
create table test2(
    id int,
    name varchar(20),
    province varchar(10),
    city varchar(20),
    sex varchar(2)
)

select province,count(*) from test2 group by province;
select city,count(*) from test2 group by city;

-- group by后边可以跟多个字段，只有province和city都一样才能分到同一组，字段顺序无所谓
select province,city,count(*) from test2 group by province, city;
select province,sex,count(*) from test2 group by province, sex;

分页查询

# 6、分页查询
/*
    格式：
    SELECT 字段1，字段2... FROM 表名 LIMIT M,N
        M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
        N: 整数，表示查询多少条数据
    SELECT 字段1，字段2... FROM 表明 LIMIT 0,5
    SELECT 字段1，字段2... FROM 表明 LIMIT 5,5

   limit关键字：
   limit 3:显示表的前三条
   limit 5,5
   
 */

select * from product limit 5;
select * from product limit 0,5;  #从第0行（第1行）开始显示，显示5行
select * from product limit 5,5;  #从第5行（第6行）开始显示，显示5行
select * from product limit 10,5;  #从第10行（第11行）开始显示，显示5行
select * from product limit 15,5;  #从第15行（第16行）开始显示，显示5行

select * from product limit 10,3;  #显示11,12,13行

-- 第一行
select * from product limit 0,120;  #显示11,12,13行
select * from product limit 120,120;  #显示11,12,13行
select * from product limit 240,120;  #显示11,12,13行

去重问题

  • 介绍

    在MySQL中去重有两种方式：1、distinct关键字 2、使用group by关键字

  • 操作

    create table if not exists students(studentNo int primary key auto_increment,
                                    name varchar(20),
                                    sex varchar(2),
                                    hometown varchar(20),
                                    age int,
                                    class_id int,
                                    card varchar(20));

insert into students(name, sex, hometown, age, class_id, card)
values ('王昭君', '女', '北京', 20, 1, '340322199001247654'),
       ('诸葛亮', '男', '上海', 18, 2, '340322199002242354'),
       ('张飞', '男', '南京', 24, 3, '340322199003247654'),
       ('白起', '男', '安徽', 22, 4, '340322199005247654'),
       ('大乔', '女', '天津', 19, 3, '340322199004247654'),
       ('孙尚香', '女', '河北', 18, 1, '340322199006247654'),
       ('百里玄策', '男', '山西', 20, 2, '340322199007247654'),
       ('小乔', '女', '河南', 15, 3, null),
       ('百里守约', '男', '湖南', 21, 1, ''),
       ('妲己', '女', '广东', 26, 2, '340322199607247654'),
       ('李白', '男', '北京', 30, 4, '340322199005267754'),
       ('孙膑', '男', '新疆', 26, 3, '340322199000297655');


select * from students order by  sex;

select class_id, sex, count(*)
from students
group by class_id, sex


select * from students;


drop table if exists student;
CREATE TABLE if not exists student(
	id INT,
	NAME VARCHAR(20),
	chinese INT,
	english INT,
	math INT
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王五',75,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李来财',75,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);

select * from student;

-- 对某一列去重,以下两种方式等价
select distinct chinese from student;
select  chinese from student group by chinese;

-- 对某一列去重,以下两种方式等价
select distinct province from test2;
select  province from test2 group by province;


--  对整行去重，以下几种方式等价
select distinct * from student;
select * from student group by id,NAME,chinese,english,math;
select * from student group by id,NAME,chinese,english,math;
select id,NAME,chinese,english,math from student group by id,NAME,chinese,english,math;

-- count去重------>先去重再统计
select count(distinct province) from test2;

十、多表查询

表与表之间的关系

概念

  • 一对一关系
  1、A表的每一行对应B表的每一行，同理B表的每一行也对应A表的每一行
  2、由于该模型下两张表可以合成一张表，所以不需要过多关注

在这里插入图片描述

  • 一对多关系
    在这里插入图片描述
  1、A表的每一行对应B表的多行，反之不可以，一对多一般描述的是包含关系
  2、一对多关系是数据分析遇到最多的关系之一
  • 多对多关系
    在这里插入图片描述
    在这里插入图片描述
1、A表的每一行对应B表的多行，反之B表的每一行都对应A的多行
2、多对多关系是数据分析最复杂的关系，这种关系表必须有中间表


部门表和员工表:
演员表 和 角色表：

在这里插入图片描述
在这里插入图片描述

表关系实现

  • 一对多关系
    在这里插入图片描述
  ######################表关系-一对多关系##############
  # 1、创建分类表 -主表
  drop table if exists category;
  CREATE TABLE if not exists category(
      cid   VARCHAR(32) PRIMARY KEY,
      cname VARCHAR(100)
  );
  
  # 2、创建商品表 -从表
  
  -- 创建外键约束-方式1-在建表时直接指定
  drop  table if exists product;
  CREATE TABLE if not exists product
  (
      pid         INT PRIMARY KEY,
      pname       VARCHAR(20),
      price       DOUBLE,
      category_id VARCHAR(32),
      constraint fk_tb_product2 foreign key (category_id) references category(cid)
  );
  
  # 创建外键约束-方式2-在建表之后指定
  -- 大小写转换 ： Ctrl + Shift  + u
  /*
   alter table 从表 add constraint 外键的名字
      foreign key(从表外键列)  references 主表(主表主键列);
   */
  alter table product add constraint fk_tb_product
      foreign key(category_id)  references category(cid);
  
  -- 验证有约束的数据操作
  
  -- 添加数据
  /*
    1、先要添加主表数据，才能添加从表数据
   */
  insert into category values ('c001','电器');
  insert into category values ('c002','服装');
  
  INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
  INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
  INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
  
  INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
  INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
  INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
  INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
  INSERT INTO bigdata_db.product(pid,pname,price,category_id) VALUES(8,'海澜之家',1,'c002');
  
  
  -- 删除数据
  /*
    1、必须先删除从表数据，才能删除主表数据
   */
  
  delete from product where pname = '联想';
  delete from product where pname = '雷神';
  delete from product where pname = '海尔';
  
  
  delete from product where category_id = 'c001';
  
  delete  from category where cid = 'c001';
  
  
  -- 删除外键约束
  -- alter  table 从表 drop foreign key  外键约束名;
  alter  table product drop foreign key  fk_tb_product2;
  
  -- 结论
  /*
  对于外键约束而言，只是来约束你插入数据和删除数据的行为，对查询不会造成任何影响
   */

在这里插入图片描述

  • 多对多关系
    在这里插入图片描述
   ######################表关系-多对多关系##############
   -- 1、创建左侧主表
   drop table if exists actor;
   create table actor(
       actor_id int primary key ,
       actor_name varchar(20)
   );
   -- 2、创建右侧主表
   drop table if exists role;
   create table role(
       role_id int primary key ,
       role_name varchar(20)
   );
   -- 3、创建中间从表
   create table actor_role(
       actor_id int,
       role_id int
   );
   
   -- 4、创建左侧外键约束1
   alter table actor_role add constraint fk_1 foreign key(actor_id)
   references actor(actor_id);
   -- 5、创建右侧外键约束2
   
   alter table actor_role add constraint fk_2 foreign key(role_id)
   references role(role_id);
   
   
   
   -- 6、验证-数据的添加
   /*
      1、左侧主表和右侧主表的数据可以随便添加
    */
   
   -- 添加左侧主表数据
   insert into actor values
                         (1,'王宝强'),
                         (2,'杨幂'),
                         (3,'贾乃亮'),
                         (4,'迪丽热巴'),
                         (5,'胡歌');
   
   -- 添加右侧主表数据
   insert into role values
                         (1,'皇帝'),
                         (2,'太监'),
                         (3,'宫女'),
                         (4,'大臣'),
                         (5,'皇后');
   
   -- 添加中间表
   insert into actor_role values
                         (1,2),
                         (1,4),
                         (2,5),
                         (4,3),
                         (5,4);

多表join查询

查询方式

  1、内连接查询
    1.1 内连接求两张表的交集

  2、外连接查询
    2.1 左外连接
    2.2 右外连接

内连接查询

在这里插入图片描述

#方式1
seelct * from A,B where 条件;

#方式2
seelct * from A inner jon B on 条件;
seelct * from A  jon B on 条件;   #  推荐

在这里插入图片描述

/*
  1、内连接查询
    1.1 内连接求两张表的交集

  2、外连接查询
    2.1 左外连接
    2.2 右外连接
 */


-- 2.1 内连接查询
/*
 #方式1
seelct * from A,B where 条件;

#方式2
seelct * from A inner jon B on 条件;
seelct * from A  jon B on 条件;
 */
-- 方式1
alter  table product drop foreign key  fk_tb_product2;
select * from category,product;  #笛卡尔集，将两张表进行相乘(表的行数=A表行数 * B表行数)
select * from category,product where  cid = category_id;

-- 如果多张表中有同名的字段，则字段名前边必须加表名 : 表名.列名
select *
from actor,actor_role,role
where actor.actor_id = actor_role.actor_id  and actor_role.role_id = role.role_id;

-- 可以给表起别名
select *
from actor as a,actor_role as ar,role as r
where a.actor_id = ar.actor_id  and ar.role_id = r.role_id;

-- 方式2

select * from category inner join product;  #笛卡尔集，将两张表进行相乘(表的行数=A表行数 * B表行数)

select * from category join product on  cid = category_id;

-- 如果多张表中有同名的字段，则字段名前边必须加表名 : 表名.列名
select *
from actor,actor_role,role
where actor.actor_id = actor_role.actor_id  and actor_role.role_id = role.role_id;



-- 可以给表起别名
select *
from actor as a,actor_role as ar,role as r
where a.actor_id = ar.actor_id  and ar.role_id = r.role_id;


select
    *
from actor a
     join actor_role ar on  a.actor_id = ar.actor_id
     join  role r  on ar.role_id = r.role_id;


-- 手拉手join
# select
#     *
# from A1
#     join A2 on A1和A2关联条件
#     join A3 on A2和A3关联条件
#     join A4 on A3和A4关联条件
#     join A5 on A4和A5关联条件;
#
#
# -- 小弟和大哥join
# select
#     *
# from A1
#     join A2 on A1和A2关联条件
#     join A3 on A1和A3关联条件
#     join A4 on A1和A4关联条件
#     join A5 on A1和A5关联条件



-- 省表
create table province(
    pid int primary key ,
    name varchar(20)
);
-- 市表
create table city(
    cid int primary key ,
    name varchar(20),
    pid int
);

-- 区表
create table county(
    id int primary key ,
    name varchar(20),
    cid int
);


select
*
from province p
    join  city c on p.pid = c.pid
    join  county c2 on c.cid = c2.cid;

在这里插入图片描述

外连接查询
  • 介绍
  1、外连接查询
  左外连接： left join
     -- 以左表为主，一定会把左表的数据全部输出，右表有交集的数据就输出，没有交集的数据就输出null
  右外连接： right join
     -- 以右表为主，一定会把右表的数据全部输出，左表有交集的数据就输出，没有交集的数据就输出null
  • 操作
  
  -- 2.2  外连接查询
  /*
   1、外连接查询
  左外连接： left join
     -- 以左表为主，一定会把左表的数据全部输出，右表有交集的数据就输出，没有交集的数据就输出null
  右外连接： right join
   */
  
  -- 左外连接
  select
  *
  from category
      left join product p on category.cid = p.category_id;
  
  /*
    select
      *
    from  A1
      left join A2 on 条件1
      left join A3 on 条件2
      left join A4 on 条件3
      left join A5 on 条件3
   */
  
  -- 右外连接
  
  select
  *
  from category
      right join product p on category.cid = p.category_id;
      
      
  -- 统计每种分类对应商品的个数
  /*
     电器  3
     服装  5
     化妆品 0
   */
  select
   cname,
   count(pname) as total
  from category c
      left join  product p  on c.cid = p.category_id
  group by cname
  order by  total desc;

在这里插入图片描述
在这里插入图片描述

自关联查询

  • 介绍
  1、有时，我们需要将一行表当成多张表来用，来实现该表的自关联，也就是表自己和自己关联
  2、应用场景：
     员工的上下级关系
     行政区域的所属关系
     商品分类的所属关系
  • 操作
  -- 3、自关联查询
  use bigdata_db;
  
  drop table employee;
  create table employee(
      eid int primary key comment '员工id',  --  员工id
      ename varchar(20),      -- 员工名
      mgr_id int              -- 员工上级领导id
  );
  
  insert into  employee values (1,'刘备',13);
  insert into  employee values (2,'关羽',1);
  insert into  employee values (3,'张飞',1);
  insert into  employee values (4,'赵云',1);
  
  insert into  employee values (5,'曹操',13);
  insert into  employee values (6,'典韦',5);
  insert into  employee values (7,'徐晃',5);
  insert into  employee values (8,'郭嘉',5);
  
  insert into  employee values (9,'孙权',13);
  insert into  employee values (10,'周瑜',9);
  insert into  employee values (11,'鲁肃',9);
  insert into  employee values (12,'黄盖',9);
  
  insert into  employee values (13,'刘协',null);
  
  
  -- 1、查询每个员工及其领导
  /*
     关羽  刘备
     张飞  刘备
     赵云  刘备
     ....
     典韦  曹操
   */
  
  select * from employee e join employee  m ;
  select * from employee e join employee m  on e.mgr_id = m.eid;
  select e.ename,m.ename from employee e join employee m  on e.mgr_id = m.eid;
  
  -- 通过以上sql发现，刘协由于是最大的boss，没有领导，如果要显示 : 刘协  null，则应该使用left join
  
  /*
     关羽  刘备
     张飞  刘备
     赵云  刘备
     ....
     典韦  曹操
  
     刘协   null
   */
  select e.ename,m.ename from employee e  left join employee m  on e.mgr_id = m.eid;
  
  
  /*
     关羽  刘备   刘协
     张飞  刘备   刘协
     赵云  刘备   刘协
     刘备  刘协   null
     ....
     典韦  曹操   刘协
  
     刘协   null null
   */
  
   select e.ename,m1.ename,m2.ename
   from employee e   --  员工
          left join employee m1  on e.mgr_id = m1.eid   -- 小领导
          left join employee m2  on m1.mgr_id = m2.eid; -- 大领导
  
  
  
  select
   t1.name,
   t2.name,
   t3.name
  from t_district t1  -- 县
      left join  t_district t2 on t1.pid = t2.id -- 市
      left join  t_district t3 on t2.pid = t3.id; -- 省
  
  
  select count(*) from t_district;
  
  
  
  -- 查看焦作市的下属行政区
  select
   t1.name,
   t2.name,
   t3.name
  from t_district t1  -- 县
      left join  t_district t2 on t1.pid = t2.id -- 市
      left join  t_district t3 on t2.pid = t3.id -- 省
  where t2.name = '焦作市';
  
  
  select
   t1.name,
   t2.name,
   t3.name
  from t_district t1  -- 县
      left join  t_district t2 on t1.pid = t2.id -- 市
      left join  t_district t3 on t2.pid = t3.id -- 省
  where t2.name = '深圳市';
  
  
  -- 查询每个省级及自治区的所属行政区数量
  
  select
  t2.name,
  count(*) as total
  from t_district t1
      right join
      (
          select
              *
          from t_district
          where name like '%省' or name like '%自治区'
      ) t2
      on t1.pid = t2.id
  group by t2.name
  order by  total desc;
  

  #自关联约束
  一张表的一个列去约束另外一个列
  
  alter table employee add constraint fk_e foreign key(mgr_id)
  references employee(eid) ;

在这里插入图片描述

十一、子查询

  • 介绍
  如果一个条sql中出现了select嵌套，则查询就属于子查询
  • 操作
  ######################子查询##############
  
  -- 1、查询价格最高的商品详情信息
  select max(price) from product;
  select * from product where price = 5000;
  -- 将查询的结果当做一个值
  select * from product where price = (select max(price) from product);
  
  
  -- 2、查询价格最高的商品详情信息
  -- 将查询的结果有多个值
  -- 查询电器和服装的商品信息详情
  
  -- 方式1- 多表查询
  select
      *
  from category  c
      left join product p on c.cid = p.category_id
  where  cname in ('电器','服装');
  
  
  -- 方式2- 子查询
  select cid from category where cname in ('电器','服装');
  
  select * from product where category_id in('c001','c002');
  
  -- 子查询的结果当做多个值
  select * from product where category_id in(select cid from category where cname in ('电器','服装'));
  
  
  -- 子查询的结果当做一张表
  select
  *
  from
      (select  * from category where cname in ('电器','服装') ) t1
       join
      (select * from product) t2
      on t1.cid = t2.category_id;
  
  
  /*
  
   select
   *
   from (select * from A1 where 条件)
          left join (select * from A2 where 条件)  on 条件
          left join (select * from A3 where 条件)  on 条件
          left join (select * from A4 where 条件)  on 条件;
  
   */
  
  -- 查询价格>1000的服装商品详情信息
  -- 挑选服装分类信息
  select * from category where cname = '服装';
  -- 挑选价格>1000的分类信息
  select  * from product where price >= 1000;
  
  
  select
  *
  from (select * from category where cname = '服装') t1
      join
      (select  * from product where price >= 1000) t2
      on t1.cid = t2.category_id;
  
  
  -- 查找比服装商品平均价格高的其他商品详情
  
  -- 1、先求服装商品的平均价格
  select avg(price) from category join product p on category.cid = p.category_id where cname = '服装';
  
  
  -- 2、判断其他商品的价格是否大于服装商品的平均价格
  select * from product where price > (服装商品的平均价格);
  
  -- 获取其他商品
  select
      *
  from product
  where category_id != (select cid from category  where cname = '服装')
        and price > (select avg(price) from category join product p on category.cid = p.category_id where cname = '服装'
  );
  
  
  -- 查询商品价格是在最低和最高之间
  select * from product
           where price > (select min(price) from product) and price < (select max(price) from product);

十二、with语句

  • 介绍
  #----------原始写法------------
  select
  *
  from (select * from A where 条件1) t1
       join
      (select * from B where 条件2) t2
       on 条件3;
  
  #---------------简化写法-------------------
  with t1 as(
      select * from A where 条件1
  ),
  t2 as (
      select * from B where 条件2
  )
  select * from t1 join t2 on 条件3；
  
  
  
  
  
  ######################嵌套包含简化#########################
  #----------原始写法------------
  
  select 
  *
  from
   (select * from (select * from A)tt)t;
   
  #---------------简化写法-------------------
  with tt as (
   select * from A
  ),
  t as (
   select * from tt
  )
  select * from t;
  • 操作
   -- 学生表 Student
   create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
   insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
   insert into Student values('02' , '钱电' , '1990-12-21' , '男');
   insert into Student values('03' , '孙风' , '1990-05-20' , '男');
   insert into Student values('04' , '李云' , '1990-08-06' , '男');
   insert into Student values('05' , '周梅' , '1991-12-01' , '女');
   insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
   insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
   insert into Student values('09' , '张三' , '2017-12-20' , '女');
   insert into Student values('10' , '李四' , '2017-12-25' , '女');
   insert into Student values('11' , '李四' , '2017-12-30' , '女');
   insert into Student values('12' , '赵六' , '2017-01-01' , '女');
   insert into Student values('13' , '孙七' , '2018-01-01' , '女');
   
   -- 科目表 Course
   create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))
   insert into Course values('01' , '语文' , '02');
   insert into Course values('02' , '数学' , '01');
   insert into Course values('03' , '英语' , '03');
   
   -- 教师表 Teacher
   create table Teacher(TId varchar(10),Tname varchar(10))
   insert into Teacher values('01' , '张三');
   insert into Teacher values('02' , '李四');
   insert into Teacher values('03' , '王五');
   -- 成绩表 SC
   create table SC(SId varchar(10),CId varchar(10),score decimal(18,1))
   insert into SC values('01' , '01' , 80);
   insert into SC values('01' , '02' , 90);
   insert into SC values('01' , '03' , 99);
   insert into SC values('02' , '01' , 70);
   insert into SC values('02' , '02' , 60);
   insert into SC values('02' , '03' , 80);
   insert into SC values('03' , '01' , 80);
   insert into SC values('03' , '02' , 80);
   insert into SC values('03' , '03' , 80);
   insert into SC values('04' , '01' , 50);
   insert into SC values('04' , '02' , 30);
   insert into SC values('04' , '03' , 20);
   insert into SC values('05' , '01' , 76);
   insert into SC values('05' , '02' , 87);
   insert into SC values('06' , '01' , 31);
   insert into SC values('06' , '03' , 34);
   insert into SC values('07' , '02' , 89);
   insert into SC values('07' , '03' , 98);
   
   
   -- 1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
   
   -- 左表留下01成绩
   select * from Student s join SC c  on s.SId = c.SId and CId = '01';
   -- 左表留下02成绩
   select * from Student s join SC c  on s.SId = c.SId and CId = '02';
   
   
   -- 左右两个表关联
   select
   *
   from (select s.SId,c.CId,c.score from Student s join SC c  on s.SId = c.SId and CId = '01') t1
        join
       (select s.SId,c.CId,c.score from Student s join SC c  on s.SId = c.SId and CId = '02') t2
       on t1.SId = t2.SId
   where  t1.score > t2.score;
   
   
   -- 简化写法
   with t1 as(
       select s.SId,c.CId,c.score from Student s join SC c  on s.SId = c.SId and CId = '01'
   ),
   t2 as (
      select s.SId,c.CId,c.score from Student s join SC c  on s.SId = c.SId and CId = '02'
   )
   select
    t1.SId ,
    s.Sname,
    t1.score as t1_score,
    t2.score as t2_score
   from  t1
       join t2 on t1.SId = t2.SId
       join Student s on s.SId = t2.SId
   where  t1.score > t2.score;
   
   
   -- 2.查询学过「张三」老师授课的同学的信息
   -- 3.查询没有学全所有课程的同学的信息
   select
       s.SId,
       s.Sname
   from Student as s
       left join SC as sc on s.SId = sc.SId
       left join Course as c on sc.CId = c.CId
   group by s.SId,s.Sname
   having count(s.SId) < (select count(*) from Course);
   
   -- 4.查询各科成绩最高分、最低分和平均分，及格率：
   -- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率
   
   
   select
     c.CId,
     Cname,
     max(score) as max_score,
     min(score) as min_score,
     avg(score) as avg_score,
     concat(round(sum(if(score >= 60,1,0)) / count(*) * 100,2),'%') as pass_rate
   from Course as c
       join SC as sc on c.CId = sc.CId
   group by c.CId,Cname;

十三、MySQL的函数

字符串函数

##################MySQL的函数########################
-- 数据准备
drop table emp;
create table emp(
    emp_id int primary key auto_increment comment '编号',
    emp_name char(20) not null default '' comment '姓名',
    salary decimal(10,2) not null default 0 comment '工资',
    department char(20) not null default '' comment '部门'
);

insert into emp(emp_name,salary,department)
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');



-- 1、求绝对值:abs()函数
select abs(-5);
select abs(5);

-- 计算 张晶晶和王飞飞 薪资差多少
-- 查找张晶晶薪资
select salary from emp where emp_name = '张晶晶';

-- 查找王飞飞薪资
select salary from emp where emp_name = '王飞飞';

-- 方式1
select
    abs(
                (select salary from emp where emp_name = '张晶晶') - (select salary from emp where emp_name = '王飞飞')
    );

-- 方式2
-- select abs(张晶晶薪资-王飞飞薪资) from  张晶晶薪资表 join 王飞飞薪资表
select abs(t1.salary-t2.salary)  as diff_salary
from  (select salary from emp where emp_name = '张晶晶')t1
         join (select salary from emp where emp_name = '王飞飞')t2;


-- 2、ceil  和  floor
-- ceil 向上取整
-- floor 向下取整
select ceil(12.11); #向上找一个最近的整数
select ceil(12.00); #12
select floor(12.99); #向下找一个最近的整数，把零头抹掉

-- 3、greatest 返回列表最大值,可以求一行多列的最大值
select greatest(12,123,99,34,45);
/*
1,张小明,89,78,90
2,李进,67,53,95
3,王五,75,78,77
 */
select name,greatest(chinese,english,math) as max_score from  student;


-- 4、least 返回列表最小值,可以求一行多列的最小值
select least(12,123,99,34,45);
/*
1,张小明,89,78,90
2,李进,67,53,95
3,王五,75,78,77
 */
select name,greatest(chinese,english,math) as min_score from  student;


-- 5、pow 求几次方
select pow(2,3);   #求2的3次方
select pow(100,100);   #求100的100次方

-- 6、rand 产生随机数
-- rand产生0-1之间的随机数，包含0，不包含1
select  rand();

-- rand产生1-100之间的随机数
-- [0 - 99]  + 1  ---> [1,100]
select  floor(rand() * 100 + 1); # 1 - 100
select  ceil(rand() * 100);      # 0 - 100

-- [0 - 99]  + 1  ---> [500,1000]
select  floor(rand() * 100 + 1);
select  floor(rand() * 501 + 500);

--  求n到m之间的随机公式
-- floor(rand() * (m - n + 1) + n)

select floor(rand() * (1000 - 500 + 1) + 500);
select floor(rand() * 501 + 500);

select floor(rand() * (100 - 1 + 1) + 1);
select floor(rand() * 100 + 1);



-- 7、round 四舍五入
select round(3.499);  #4舍
select round(3.511);  #5入

#将一个数+0.5，向下取整就可以实现四舍五入
select floor(4875.5001 + 0.5);
select floor(4875.4001 + 0.5);


-- 四舍五入的同时保留n位小数
select round(3.499,2);
select round(3.484,2);
select round(3.485,2);


-- 8、字符串拼接:concat,concat_ws
select concat('hello','world');

select concat(emp_name,'_',department) from emp;
select concat('河北省','保定市','竞秀区','中山路38号');

-- concat_ws 在拼接式可以指定固定的分隔符
-- 2020-12-23
select concat_ws('-','2020','12','23');

-- 2020-12-23 11:34:56
select concat(concat_ws('-','2020','12','23'),' ',concat_ws(':','11','34','56'));


-- 9、去除空格
-- ltrim 去除左边空格
-- rtrim 去除右边空格
-- trim 去除两边空格
select '            hello              ';
select ltrim('            hello              ') as x;
select rtrim('            hello              ') as x;
select trim('            hello              ') as x;

select concat('llll',('            hello              '),'rrrrr')as x;
select concat('llll',ltrim('            hello              '),'rrrrr')as x;
select concat('llll',rtrim('            hello              '),'rrrrr')as x;
select concat('llll',trim('            hello              '),'rrrrr')as x;



-- 10、替换
select replace('你是个sb!','sb','**');
-- 将字符串中的sb全部替换为**
select replace('你是个sb,他也是个sb!','sb','**'); #全部替换


-- 11、字符串翻转
select reverse('13812345678');


-- 12、大小写转换
-- upper 将小写转大写
-- lower 将大写转小写

select upper('KDJKeieieeeKJJ');
select lower('KDJKeieieeeKJJ');


-- 13、字符串截取  substring ,substr
--  substring ,substr 等价
select substring('helloworld',2); -- 从第二个字符开始截取到最后
select substr('helloworld',2); -- 从第二个字符开始截取到最后

select substring('helloworld',1); -- 从第1个字符开始截取到最后
select substring('helloworld',2,4); -- 从第2个字符开始截取,截取4个长度
select substring('2020-12-23 11:34:56',6,5); -- 从第6个字符开始截取,截取5个长度
select substring('2020-12-23 11:34:56',-5,2); -- 从倒数第5个开始,截取2个长度

-- 统计每一个姓氏人数-方式1
with t as (
    select substring(name,1,1) as first_name from student
)
select first_name,count(*) as cnt from t group by first_name order by cnt desc;

-- 统计每一个姓氏人数-方式2

 select
     substring(name,1,1) as first_name,
     count(*) as cnt
 from student
 group by substring(name,1,1)
order by cnt desc;


with  t as (
 select
    length(name) / 3 as info,
    concat(count(*),'人') as cnt
 from student
 group by length(name) / 3
)
select concat(floor(info),'个字人数') as info,cnt from t;

数学函数

-- 1、求绝对值:abs()函数
select abs(-5);
select abs(5);

-- 计算 张晶晶和王飞飞 薪资差多少
-- 查找张晶晶薪资
select salary from emp where emp_name = '张晶晶';

-- 查找王飞飞薪资
select salary from emp where emp_name = '王飞飞';

-- 方式1
select
    abs(
                (select salary from emp where emp_name = '张晶晶') - (select salary from emp where emp_name = '王飞飞')
    );

-- 方式2
-- select abs(张晶晶薪资-王飞飞薪资) from  张晶晶薪资表 join 王飞飞薪资表
select abs(t1.salary-t2.salary)  as diff_salary
from  (select salary from emp where emp_name = '张晶晶')t1
         join (select salary from emp where emp_name = '王飞飞')t2;


-- 2、ceil  和  floor
-- ceil 向上取整
-- floor 向下取整
select ceil(12.11); #向上找一个最近的整数
select ceil(12.00); #12
select floor(12.99); #向下找一个最近的整数，把零头抹掉

-- 3、greatest 返回列表最大值,可以求一行多列的最大值
select greatest(12,123,99,34,45);
/*
1,张小明,89,78,90
2,李进,67,53,95
3,王五,75,78,77
 */
select name,greatest(chinese,english,math) as max_score from  student;


-- 4、least 返回列表最小值,可以求一行多列的最小值
select least(12,123,99,34,45);
/*
1,张小明,89,78,90
2,李进,67,53,95
3,王五,75,78,77
 */
select name,greatest(chinese,english,math) as min_score from  student;


-- 5、pow 求几次方
select pow(2,3);   #求2的3次方
select pow(100,100);   #求100的100次方

-- 6、rand 产生随机数
-- rand产生0-1之间的随机数，包含0，不包含1
select  rand();

-- rand产生1-100之间的随机数
-- [0 - 99]  + 1  ---> [1,100]
select  floor(rand() * 100 + 1); # 1 - 100
select  ceil(rand() * 100);      # 0 - 100

-- [0 - 99]  + 1  ---> [500,1000]
select  floor(rand() * 100 + 1);
select  floor(rand() * 501 + 500);

--  求n到m之间的随机公式
-- floor(rand() * (m - n + 1) + n)

select floor(rand() * (1000 - 500 + 1) + 500);
select floor(rand() * 501 + 500);

select floor(rand() * (100 - 1 + 1) + 1);
select floor(rand() * 100 + 1);



-- 7、round 四舍五入
select round(3.499);  #4舍
select round(3.511);  #5入

#将一个数+0.5，向下取整就可以实现四舍五入
select floor(4875.5001 + 0.5);
select floor(4875.4001 + 0.5);


-- 四舍五入的同时保留n位小数
select round(3.499,2);
select round(3.484,2);
select round(3.485,2);

日期函数

# -----------------------日期函数----------------------------
-- 1、获取从1970年到此时此刻的秒值
select unix_timestamp();

-- 2、将指定的时间字符串转为秒值
select unix_timestamp('2023-12-27 14:42:56');

-- 3、统计你来到这个世界多少天
select (unix_timestamp() - unix_timestamp('2001-11-11 11:58:32')) / 3600 / 24;


-- 4、将秒值转为指定的格式
-- (1598079966,'%Y-%m-%d %H:%i:%s'); -> 2020-08-22 15-06-06
SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s');

-- 5、将2023-12-27 14:42:56 转为 202年12月27日 14时42分56秒

-- 2023-12-27 14:42:56    ----> 2023年12月27日 14时42分56秒
-- 2023-12-27 14:42:56    ----> 2023/12/27 14:42:56

SELECT FROM_UNIXTIME(unix_timestamp('2023-12-27 14:42:56'),'%Y年%m月%d日 %H时%i分%s秒');

-- 6、获取当前的年月日
select  current_date();  -- 获取年月日： 2023-12-27
select  current_time();  -- 获取时分秒：14:55:03

select current_timestamp(); -- 获取年月日时分秒： 2023-12-27 14:55:31
select now();  -- 获取年月日时分秒： 2023-12-27 14:55:31

-- 7、从日期字符串中提取年月日
select date('2023-12-27 14:42:56'); -- 2023-12-27   得到的是日期类型
select substring('2023-12-27 14:42:56',1,10); -- 2023-12-27  得到的是字符串

-- 8、计算日期之间相差的天数
select abs(datediff('2008-08-08',current_date()));

-- 最近7入购物的统计，最近30天，最近60天
select   abs(datediff('2023-12-07',current_date())) < 30;


-- 9、计算小时之间的差值
select timediff('14:42:56','15:15:23');  -- 00:32:27


-- 10、日期格式化
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r');
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y/%m/%d %H:%i:%s');

-- 2011-1-1 1:1:1 转为  2011-01-01 01:01:01
SELECT DATE_FORMAT('2011-1-1 1:1:1','%Y-%m-%d %H:%i:%s');

-- 2020-4-12 21:20  --->  2020-04-12 21:20:00
SELECT DATE_FORMAT('2020-4-12 21:20','%Y-%m-%d %H:%i:%s');

SELECT STR_TO_DATE('August 10 2017', '%M %d %Y');

-- 11 、将日期向前后推移
-- 时间向前推2天
select date_sub('2023-12-27 14:42:56',interval 2 day) ;
select date_add('2023-12-27 14:42:56',interval -2 day);

-- 时间向后推2天
select date_sub('2023-12-27 14:42:56',interval -2 day) ;
select date_add('2023-12-27 14:42:56',interval 2 day) ;

-- 时间向后推3年
select date_sub('2023-12-27 14:42:56',interval -3 year) ;
select date_add('2023-12-27 14:42:56',interval 3 year) ;

-- 时间向后推1周
select date_sub('2023-12-27 14:42:56',interval -1 week) ;
select date_add('2023-12-27 14:42:56',interval 1 week) ;

-- 统计任何一个年份的2月有几天,将时间定为该年的3月1日，然后时间向前推一天，就是2月的最后一天
select date_sub('2023-03-01',interval 1 day) ;

-- 12、从日期中提取指定的值
-- 从日期中提取分钟
select extract(minute  from '2023-12-27 14:42:56');

-- 从日期中提取小时
select extract(hour  from '2023-12-27 14:42:56');

-- 从日期中提取年
select extract(year  from '2023-12-27 14:42:56');


-- 从日期中提取季度
select extract(quarter  from '2023-12-27 14:42:56');

-- 从日期中提取周(今年的第几周)
select extract(week  from '2023-12-27 14:42:56');

-- 从日期中提取周几
select YEAR('2023-12-27 14:42:56');
select MONTH('2023-12-27 14:42:56');
select DAY('2023-12-27 14:42:56');
select HOUR('2023-12-27 14:42:56');
select MINUTE('2023-12-27 14:42:56');
select SECOND('2023-12-27 14:42:56');

-- 1:周日 2：周一 3：周二 4:周三 5：周四 6：周五 7：周六
select DAYOFWEEK('2023-12-27 14:42:56');

-- 获取季度
select QUARTER('2023-12-27 14:42:56');


select min(date_code),max(date_code) from t_date;


-- 13、获取今天是周几
-- 1:周日 2：周一 3：周二 4:周三 5：周四 6：周五 7：周六
select dayofweek(current_date());

逻辑处理函数

# -----------------------逻辑处理函数----------------------------

-- 1、if语句
select if(2 > 1 , '大于','小于');  -- 条件成立，则取第一个值，否则取第二值
select if(2 < 1 , '大于','小于');

select *,'及格' as status from SC;
select *,'不及格' as status from SC;

-- 给表添加一列，标记是及格还是不及格
select *,if(score >= 60,'及格','不及格') as status from SC;

-- 统计及格和不及格人数
select sum(if(score >= 60,1,0)), sum(if(score >= 60,0,1)) from SC;


-- 2、ifnull
-- select ifnull(值1，值2) ：如果值1为null，则返回值2，否则还是返回值1
-- 该函数用来处理null值问题
select ifnull(1,2);

select *  from emp;
select 800 * 12 + if();

select ename, 12 * sal + ifnull(comm,0) as year_sal from emp;


-- 3、case  when 语句

-- 格式1
/*
  case
    when 条件1  then  '值1'
    when 条件2  then  '值2'
    when 条件3  then  '值3'
    else
      '值'     -- else可以没有
 end
 */
select
*,
  case
    when score >= 90  then  '优秀'
    when score >= 80  then  '良好'
    when score >= 60  then  '及格'
    else
        '不及格'
 end as status
from SC;

select
*,
  case
    when orders.pay_type = 1  then  '微信支付'
    when orders.pay_type = 2  then  '支付宝支付'
    when orders.pay_type = 3  then  '银行卡支付'
    else
        '其他'
 end as status
from orders;




-- 格式2
/*
  case 字段
    when 值1  then  '信息1'
    when 值2  then  '信息2'
    when 值3  then  '信息3'
    else
      '信息4'     -- else可以没有
 end
 */

create table orders(
 oid int primary key, -- 订单id
 price double, -- 订单价格
 pay_type int -- 支付类型(1:微信支付 2:支付宝支付 3:银行卡支付 4：其他)
);
insert into orders values(1,1200,1);
insert into orders values(2,1000,2);
insert into orders values(3,200,3);
insert into orders values(4,3000,1);
insert into orders values(5,1500,2);


select
*,
  case orders.pay_type
    when  1  then  '微信支付'
    when  2  then  '支付宝支付'
    when  3  then  '银行卡支付'
    else
        '其他'
 end as status
from orders;
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值