Mysql语句

yzhSWJ

已于 2024-03-01 13:52:34 修改

阅读量1.6k

点赞数

分类专栏： sql 文章标签： sql 数据库 database

于 2020-06-11 23:57:21 首次发布

本文链接：https://blog.csdn.net/yuzheh521/article/details/106699775

版权

sql 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

查询

select 查询显示的列表 from 表名
select类似于： system.out.print(查询显示的列表),
特点:
1.查询列表可以是: 表中的字段,常量值,函数,表达式
2.查询出的表格是虚拟的表格(临时表格)

一基础查询

1.查询表中单个字段

date

#1.查询表中单个字段
select username from acl_user;

查询表中多个个字段

#查询表中多个个字段
select username,password from acl_user;

查询全部

#查询全部
select * from acl_user;

2.查询常量值

#2.查询常量值
#字符串
select 'admin111111111111';
#整数
select 100;

3.查询表达式

#3.查询表达式
select 100*2;

4.查询函数

#4.查询函数
select version();

5.起别名

方式一使用as

#方式一 使用as
select 100*2 as jieguo;

方式二用空格

#方式二 用 空格
select 100*2 jieguo;

如果别名中有特殊符号加上""

# 如果别名中有特殊符号（空格，#等） 加上""
select 100*2 as "out put";

6.去重

这里有两点需要注意：

DISTINCT 需要放到所有列名的前面，如果写成:
SELECT username , DISTINCT id FROM employees 会报错。
DISTINCT 其实是对后面所有列名的组合进行去重，

#6.去重
select distinct username from acl_user;

7.concat把多个字段链接内容合并成,一个新字段显示,

like concat('%', #{configName}, '%')

#7.把两个字段链接内容合并成,一个新字段显示,
  #例如 username 和password 内容合并 显示在 lianjie 字段
select concat(username,password) as lianjie from acl_user;

在这里插入图片描述
在这里在补充一下：
在java中想到的拼接字符串的一般会用到+：
java中的+号：
1.运算符，两个操作数都为数值型
2.连接符，只要有一个操作数为字符串，就可以把两个数据拼接在一起
mysql中的+号：仅仅只有一个功能，运算符
1select 100+90 ：两个操作数都为数值型，则做加法运算
2select ‘100’+90:其中一个为数值型，尝试将字符型数值转换为数值型。转换成功，则继续做加法运算，如果转换失败，则字符型数值转换成0
3select null+0；只要其中一项为null，结果就是null

8.union和union all （合并结果集）

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的。但列名则不一定需要相同，

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

数据求和 必须为UNION ALL后的数据加 表别名alisa
<select id="getGL_Jr" resultType="map">
		select a.YjName,sum(a.SJLJ)SJLJ
		from
		(SELECT * from 各楼所有冷水累计_今日
		union all
		select * from 各楼总热水累计_今日)a
		WHERE YjName not in ('泵房','洗衣房冷凝水','锅炉房冷凝水')
		group by a.YjName
	</select>

测试：

#UNION 会去除重复内容
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

在这里插入图片描述

9.查询结果自动增长虚拟列，sql语句

select (@i:=@i+1) as i,view_zan.id from view_zan,(select @i:=0) as it

在这里插入图片描述

二.条件查询

#条件查询
#  语法:
      #  select 查询列表    第三执行select
      #  from 表名          第一先执行
      #  where 筛选条件     第二执行where

  筛选条件分类
    一.按照条件表达式筛选,
         条件运算符: > , < , = , != , <> , >= , <=
    二.按照逻辑运算符筛选
         逻辑运算符: and , or , not
           作用:主要用 来连接 条件表达式的
    三.模糊查询
       模糊查询: like ,
                 between and, 相当于>= ,<=
                 in,
                 is null

1.条件表达式

条件运算符: > , < , = , != , <> , >= , <=

#条件表达式
select * from acl_user where id>2

2.逻辑运算符

逻辑运算符: and , or , not

#逻辑运算符
select *
from acl_user where username='yzh' and password='123';
#not 使用
select *
from acl_user where not (username='yzh' and password='123');

3.模糊查询

模糊查询: like ,
between and, 相当于>= ,<=
in,
is null

#.模糊查询
select * from acl_user where username like '%a%'

查询 username 第二个是d 第四个是i 的用户信息

#查询 username 第二个是d 第四个是i 的 用户信息
select *
from acl_user where username like '_d_i%';

3.1使用转义字符

查询 username 第二个是_ 用户信息,

方式一使用转义字符

# 查询 username 第二个是_ 用户信息,
# 方式一 使用转义字符
select *
from acl_user where username like '_\_%';

方式二指定转义字符 ,随意指定

# 方式二 指定转义字符 ,随意指定
select *
from acl_user where username like '_$_%' escape '$';

3.2.between and, 相当于>= ,<

#between and, 相当于>= ,<=
select * from acl_user where id between 1 and 2;
select * from acl_user where id >= 1 and id <= 2;

3.3.is null is not null 只能用来判断字段是否是null

# is null  is not null  用来判断字段是否是null
select * from acl_user where username is null ;
select * from acl_user where username is not null ;

三.排序查询 ORDER BY

1.查询女学生的学号、姓名和年龄，结果按年龄进行升序排序（从小到大）

SELECT sno,  sname, sage FROM student WHERE sex = '女' ORDER BY sage ASC

1.1其中，ASC可以省略，因为默认即为升序排序

SELECT sno, sname, sage FROM student WHERE ssex = '女' ORDER BY sage

2.查询全部学生信息，结果按年龄从大到小进行排序（降序）DESC

SELECT *  FROM student ORDER BY sage DESC

3.其含义为，查询结果首先按学生的年龄进行排序(降序)，如果年龄相同的话，再按学号从小到大进行排序(升序)

SELECT sno, sname, sage FROM student  
  WHERE sno LIKE '2012%' ORDER BY sage DESC, sno ASC

四.函数

概念:类似于java中的方法,将一组逻辑语句,封装在方法体中中,对外暴露方法名
好处:隐藏了实现细节,提高代码重用性
调用: select 函数名(实参列表) from 表

分类

1. 单行函数:

  如:concat,length,isnull等

1.1.字符函数

1.length:获取参数值得字节个数,一个汉字占三个字节

select length('hello');

2.concat:拼接字符串username,password 在一个字段中间用 _ (下划线) 隔开

select concat(username,'_',password)from acl_user;

在这里插入图片描述

3.upper 转大写,lower 转小写

select upper('a');
select lower('A');

实例:,用户名大写,密码小写

select upper(username),lower(password) from acl_user;

实例,用户名大写,密码小写,然后拼接

select concat(upper(username),lower(password)) as up from acl_user;

4.substr,substring,两者是一样的,用来截取字符串

#substr,substring,两者是一样的,用来截取字符串
#截取指定索引后面全部的字符
#sql中索引都是从1开始(字符)
select substr('截取字符', 3);
#截取指定索引处,指定字符的长度
# 索引2,往后查四个字符,结果 :取字符哈
select substring('截取字符哈哈哈', 2, 4);

5.instr,返回子串第一次出现的索引(结果是3),如果找不到返回0

#instr,返回子串第一次出现的索引(结果是3),如果找不到返回0
select instr('小于小孙','小孙') as start;

6.trim:去除前后的空格

#trim:去除前后的空格
select trim('   xiaoyuxiaosun   ');
#去除前后的a
select trim('a'from 'aaaaa小于小孙aaa');

7.lpad:用指定的字符,实现左填充指定的长度,指定长度为6,会用*号填充字符长度到6(**小于小孙)

#lpad:用指定的字符,实现 左填充 指定的长度,指定长度为6,会用*号填充字符长度到6(**小于小孙)
select lpad('小于小孙', 6, '*');

8.rpad:用指定的字符,实现右填充(小于小孙lovelove)

#rpad:用指定的字符,实现 右填充(小于小孙lovelove)
select rpad('小于小孙', 12, 'love');

9.replace:替换(小于小孙)

#replace:替换(小于小孙)
select replace('xiaoyu小孙', 'xiaoyu', '小于');

1.2.数学函数

1.round:四舍五入(1)

#round:四舍五入(1)
select round(1.26);

2.保留小数点后两位(1.57)

#保留小数点后两位(1.57)
select round(1.566, 2);

3.ceil:相上取整(2),返回>=该参数的最小整数


#ceil:相上取整(2),返回>=该参数的最小整数
select ceil(1.02);

4.floor:向下取整(1),返回<=该参数的最大整数

#floor:向下取整(1),返回<=该参数的最大整数
select floor(1.56);

5.truncate:截断(1),保留小数点后一位

#truncate:截断(1.9),保留小数点后一位
select truncate(1.9999, 1);

6.mod:取余 a-a/b*b

#mod:取余 a-a/b*b
#结果:被除数如果是正就是正,如果是负数就是负数
select mod(10, 3);

1.3.日期函数

1.now:返回当前系统日期加时间

#now:返回当前系统日期加时间
select now();

2.curdate / CURRENT_DATE:返回当前系统日期,不包含时间

#curdate:返回当前系统日期,不包含时间
select curdate();
#返回当前日期
 select CURRENT_DATE  from 表名

3.curtime: 返回当前系统时间,不包含日期

#curtime: 返回当前系统时间,不包含日期
select curtime();

4.可以获取,指定的时间,年,月,日期,时,分,秒

# 可以获取,指定的时间,年,月,日期,时,分,秒
#例如 获取年 ,结果(当前年)(1994)
select year(now());
select year('1994-11-20');
select month  ('1994-11-20');

在这里插入图片描述

5.str_to_date:将字符串通过指定的格式,转换成日期%c月-%d日期 %Y年

#str_to_date:将字符串通过指定的格式,转换成日期%c月-%d日期 %Y年
select * from acl_role where gmt_create = str_to_date('11-11 2019','%c-%d %Y')

6.date_format:将日期转换成指定的字符串

#date_format:将日期转换成指定的字符串
select date_format(gmt_create,'%m月/%d日 %y年') from acl_role;

在这里插入图片描述
date_format()函数其他格式符如下：

%a — 工作日的缩写名称（Sun~Sat)
%b — 月份的缩写名称（Jan…Dec)
%c — 月份，数字形式（0~12)
%D — 带有英语后缀的该月日期(0th, 2st, 3nd,…）
%d — 该月日期，数字形式（00~31)
%e — 该月日期，数字形式（(0~31)
%f — 微秒（000000 …999999)
%H — 以 2 位数表示 24 小时（00~23)
%h, %I — 以 2 位数表示 12 小时（01~12)
%i — 分钟，数字形式(00~59)
%j — —年中的天数（001~366)
%k — 以 24 小时（0~23)表示
%l — 以12小时（1~12)表示
%M — 月份名称（January~December)
%m — 月份，数字形式（00~12)
%p — 上午（AM) 或下午（PM)
%r — 时间，12小时制（小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM)
%S, %s — 以 2 位数形式表示秒（00~59)
%T — 时间，24 小时制（小时 (hh): 分钟 (mm): 秒数 (ss))
%U — 周（00~53)，其中周日为每周的第一天
%u — 周（00~53)，其中周一为每周的第一天
%V — 周（01~53)，其中周日为每周的第一天，和％X同时使用
%v — 周（01~53)，其中周一为每周的第一天，和%x同时使用
%W — 星期标识（周日、周一、周二…周六）
%w — —周中的每日（0= 周日…6= 周六）
%X — 该周的年份，其中周日为每周的第一天，数字形式，4 位数，和％V同时使用
%x — 该周的年份，其中周一为每周的第一天，数字形式，4位数，和%v同时使用
%Y — 4 位数形式表示年份
%y — 2 位数形式表示年份
%% — %一个文字字符

 DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%S')

‘2020-11-30 10:00:00’

7.datediff:前面的日期减后面的日期

select datediff('2019-11-6','2019-11-4')

在这里插入图片描述

7.1TimeStampDiff:计算两个时间之间的时间差

可以使用TimeStampDiff()函数计算两个时间之间的时间差，函数声明如下：

select TimeStampDiff(HOUR,'2019-11-06 06:20','2019-11-06 10:10')

在这里插入图片描述
其中，unit是时间的单位，枚举值有：

SECOND：秒
MINUTE：分钟
HOUR：小时
DAY：天
WEEK：周
MONTH：月份
QUARTER：季度
YEAR：年
注意，相减的时候是：datetime_expr2减去datetime_expr1，如果写反了会得到相反的结果

8.DATE_ADD() 函数向日期添加指定的时间间隔。（正数/负数）

#该字段加2天
DATE_ADD(可以是字段,INTERVAL 2 DAY)

// 得到当前时间增加1个小时的结果
select date_add(now(),interval 1 hour)

// 得到当前时间增加1天的结果
select date_add(now(),interval 1 day)

// 得到当前时间减少72个小时的结果
select date_add(now(),interval -72 hour)
// 得到昨天日期时间
select date_add(now(),interval -1 day)

9.DATE_SUB() 函数从日期减去指定的时间间隔

DATE_SUB(可以是字段,INTERVAL 2 DAY)

// 得到当前时间减去1个小时的结果
select DATE_SUB(now(),interval 1 hour)

10.date（）查询2020-06-11这天有多少人注册

1.时间查询

# 查询2020-06-11这天有多少人注册
#  date(gmt_create) 获取日期时间格式里面的日期部分
#2020-06-11 06:53:01 会获取2020-06-11
select count(*)
from ucenter_member
where date(gmt_create) = '2020-06-11';

11.MySql查询过去7天每一天的时间

方式一：无索引（不算当天时间一共七天）

SELECT  DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s :=@s + 1 DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := 0) temp
WHERE @s < 7
ORDER BY `date` desc;

方式二：有索引（算当天时间一共7天）

SELECT @s :=@s + 1 AS `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := -1) temp
WHERE @s < 6
ORDER BY `date`;

在这里插入图片描述

12.MySql查询过去1年每月的时间

方式一（不带索引，不算当前月一共12个月）

SELECT 

        date_format( DATE_SUB(now(), INTERVAL @s :=@s + 1  MONTH), '%Y-%m' )AS `YM`
        FROM mysql.help_topic,(SELECT @s := 0) temp
        WHERE @s < 12 order by ym

在这里插入图片描述

方式二（带索引，算当前月一共12个月）

SELECT  @s:=@s + 1 AS `index`,
        date_format( DATE_SUB(now(), INTERVAL @s MONTH), '%Y-%m' )AS `YM`
        FROM mysql.help_topic,(SELECT @s := -1) temp
        WHERE @s < 11

在这里插入图片描述

13.to_days函数

to_days函数：返回从0000年（公元1年）至当前日期的总天数。注意如下举例中的几个字段名一般为YMD或Y-M-D格式，非时间戳
1、利用to_days函数查询今天的数据：

select * from 表名 where to_days(时间字段名) = to_days(now());

1.4.其他函数

#version():当前版本
select version();
#database():当前用的数据库
select database();
#user():当前用户
select user();

1.5流程控制函数 IFNULL（判断）

0.1 ifnull(字段名，’转换后的值’)//字段名不加引号，转换后的值是单引号

SELECT IFNULL(username,'改变后的值') as username FROM 表;

select ifnull(likeNumber,'-') as likeNumber from view_zan where id=11;

在这里插入图片描述

1.if :相当于三元运算符(结果对)

# if :相当于三元运算符(结果对)
select if(10 > 5, '对', '错');
# 判断有没有密码
select username, password, if(password is null, '哈哈没有密码', '有密码') ispassword
from acl_user
where id = 1;

在这里插入图片描述

2.Case When的用法

2.1第一种语法:
case: 要判断的字段或者表达式
when: 常量1 then:要显示的值1 或者语句1
when: 常量2 then:要显示的值2 或者语句2
else :要显示的值n 或语句 n

select username,
       case username
           when 'admin' then '好用户'
           when 'test' then '坏用户'
           else '一般用户'
           end isGOOD
from acl_user;

在这里插入图片描述
结果

2.2第二种语法:

case
when: 条件1 then:要显示的值1 或者语句1
when: 条件2 then:要显示的值2 或者语句2
else :要显示的值n 或语句 n

#注意条件先后顺序
select username,
       case
           when id>2 then '我大于2'
           when id>1 then '我大于1'
           else '一般用户'
           end isGOOD
from acl_user;

在这里插入图片描述
结果:

2. 分组函数:

 功能:做统计使用,又称统计函数,聚合函数,组函数

总结:传入一组值,经过统计处理变成一个值
分类 : sum:求和 , avg:平均值 , max:最大值 , min:最小值 , count:计算个数.

特点:

1.参数支持的类型

sum, avg: 支持数值型
max, min , count: 任何类型都支持,

2.是否忽略null, 任何数值和null相加都等于null

所有的分组函数:都会 忽略null

3,所有分组函数可以和 distinct 搭配使用

案例:先去重在计算

select sum(distinct id) from acl_user;

常用:去重之后在计算个数

select count(distinct id) from acl_user;

4.和分组函数一同查询的字段要求是group by 后的字段

使用:

1.sum :所有id相加之和

#sum :所有id相加之和
select sum(id) from acl_user;

2.avg:平均值

#avg:平均值
select avg(id) from acl_user;

3.max:最大值

#max:最大值
select max(id) from acl_user;

4.min:最小值

#min:最小值
select min(id) from acl_user;

5.count:计算id个数(非空的)

count:详解:

1.count(*): 统计所有字段,只要有一个字段不是null的,就+1,不可能一行全是null,所以一般用来统计表,有多少行数据,(常用效率)

#count(*):相当于行数,包含所有的列,不会忽略null
select count(*) from acl_user;

2.count(1):想当于加了一列临时字段,每个字段内容都是1,统计该字段,功能效果和count(*) 一样,也可以count(2),count(3)…等效果一样

#count(1):
select count(1) from acl_user;

3.count(name):统计某一字段

#count:计算name个数,指定列会忽略所有的null值,假如有10条数据,如果其中有两条数据为null,那么返回值为8
select count(name) from acl_user;

6.多个函数一起

select 
  sum(id) as sum,
  avg(id) as avg,
  max(id) as max, 
  min(id) as min, 
  count(id) 
  from acl_user;

结果:
在这里插入图片描述

7.函数嵌套使用

select
  sum(id) as sum,
  round(avg(id),1) as avg保留1位小数
  from acl_user;

结果:
在这里插入图片描述

五.分组查询 (group by后面支持前面使用的别名)

总结: 把一张表(一组数据),拆分成几个小组
语法:

 select 分组函数, 列(要求出现在group by 后面)
    from 表名
    where 筛选条件
    group by 分组列
    order by(排序)

注意:查询字段比较特殊,要求是, 分组函数和group by 后出现的字段

1.分组前筛选

# 1. 按照,条件id>=1, is_deleted 分组 ,统计该组最大 **token**
select max(token),is_deleted from acl_user where id>=1  group by is_deleted;

表:
在这里插入图片描述
结果:

2.按照 is_deleted 统计每组 is_deleted 有几条数据

#统计is_deleted 有几条数据
select count(*),is_deleted from acl_user group by is_deleted;

结果:
在这里插入图片描述

3.分组后的筛选having

#按照 is_deleted 字段分组(相同的会是一组),统计每组个数,条件获取个数大于2的组
select count(*), is_deleted from acl_user group by is_deleted having count(*)>2;

在这里插入图片描述
结果:

分组总结:

分组条件分两种: 分组前筛选 和 分组后筛选

1.分组前筛选:筛选的是原始表 , 写在where 后面 group by前
在这里插入图片描述
2.分组后筛选:筛选的是分组后的结果集写在group by后面 having 后面

六.连接查询

在这里插入图片描述

一.sql 92 标准,

1.等值连接

#一.sql 92 标准,
#1.等值连接
select * from acl_role ,acl_user_role where acl_role.id = acl_user_role.role_id;

2.非等值连接

#2.非等值连接
select *
from acl_role,
     acl_user_role
where acl_role.id between acl_user_role.role_id and acl_user_role.user_id ;

3.自连接,自己找自己两次

#3.自连接,自己找自己两次
select *
from acl_user_role aur,(select user_id from acl_user_role where user_id=2) au
where aur.id=au.user_id ;

在这里插入图片描述
结果

二.sql 99标准,

连接类型,分类

连接类型,分类:
内连接 inner
外连接: 外连接的查询结果为主表中的所有记录
左外:left
右外:right
全外:full
交叉连接:cross

外连接特点: 外连接的查询结果为主表中的所有记录 ,
如果从表中有和它匹配的,则显示匹配数据
如果从表中没有和它匹配的,则显示null
外连接查询的结果=内连接查询的结果+主表中有而从表中没有的的记录

语法:

select 字段 from 表名1 别名
    连接类型 join 表名2 别名
        on 连接条件
where 筛选条件

连接类型,分类:
   内连接 inner
   外连接: 外连接的查询结果为主表中的所有记录
         
     左外:left
     右外:right
     全外:full
  交叉连接:cross

左连接案例

select * from acl_role 
 left join acl_user_role
    on acl_role.id=acl_user_role.role_id;

自连接,自己找自己两次

#自连接,自己找自己两次
select aur.id, aur.role_id, au.is_deleted
from acl_user_role aur
         join (select is_deleted from acl_user_role where id = 3) au
              on aur.id = au.is_deleted;

七.子查询(都要放在()内),

特点:子查询执行比主查询优先执行,因为主查询要用到子查询后的结果

含义:出现在其他语句中的 select 语句,称为子查询,或内查询
外部的查询语句称为外查询,或主查询

1.分类:

1.1按照子查询出现的位置:

子查询一般放在条件的右侧

1.select后面 
   1.1仅仅支持标量子查询
2.from后面
  2.1支持表子查询
3.where或having后面 
 3.1 标量子查询(结果集一行一列) 
     搭配:<,>,<=,>=, <>,
 3.2 列子查询(结果集一列多行)
    搭配 in,any/some, all
  3.3行子查询(结果集一行多列)(用的较少)
  
4.exists后面
  4.1 表子查询(结果集多列多行)

2.按结果集的行列数不同:

标量子查询(结果集一行一列)
列子查询(结果集一列多行)
行子查询(结果集一行多列)
表子查询(结果集多列多行)

2.where或having后面,标量子查询(结果集一行一列)

非法使用 ,标量子查询 ,子查询的结果不是一行一列(理解成,一个单元格)
在这里插入图片描述

#查询谁的id大于admin
select *
from acl_user
where id > (select id from acl_user where username = 'admin');

在这里插入图片描述
结果

yzhSWJ

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
1
评论
Mysql语句

函数# 查询2020-06-11这天有多少人注册# date(gmt_create) 获取日期时间格式里面的日期部分#2020-06-11 06:53:01 会获取2020-06-11select count(*)from ucenter_memberwhere date(gmt_create) = '2020-06-11';
复制链接

扫一扫