查询

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.去重

这里有两点需要注意：

1. DISTINCT 需要放到所有列名的前面，如果写成:
   SELECT username , DISTINCT id FROM employees 会报错。
2. 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)SJLJfrom(SELECT * from 各楼所有冷水累计_今日union allselect * from 各楼总热水累计_今日)aWHERE 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) tempWHERE @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) tempWHERE @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 usernamewhen '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,casewhen id>2 then '我大于2'when id>1 then '我大于1'else '一般用户'end isGOOD
from acl_user;

结果:

2. 分组函数:

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

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

特点:

1.参数支持的类型

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

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

1. 所有的分组函数:都会 忽略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(*):
select count(*) from acl_user;

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

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

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

#count:计算id个数
select count(id) 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.函数嵌套使用

selectsum(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_roleon acl_role.id=acl_user_role.role_id;

自连接,自己找自己两次

#自连接,自己找自己两次
select aur.id, aur.role_id, au.is_deleted
from acl_user_role aurjoin (select is_deleted from acl_user_role where id = 3) auon 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, all3.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');

结果