MySQL笔记--select基础

登录

方式一：通过mysql自带的客户端 只限于root用户
方式二：通过windiws自带的客户端
登录：mysql -h localhost -P 3306 -u root -proot
mysql【-h主机名 -P端口号 】-u用户名 -p密码

退出

exit 或 ctrl+C

语法规范

表达式分类:

条件表达式

条件运算符：>、 < 、>=、<=、<>、=、!=

逻辑表达式

逻辑运算符：
&& 、 || 、 !
and or not

模糊查询

like 、between and 、in 、is null
说明:

select * from A where  A字段 like '%%' or B字段 like '%%'；
select * from A where  A字段 like '%%' and B字段 like '%%'；
结果是否一样？
不一样 ，因为 like '%%' 会过滤null值

常用查询

-- 查看当前所有数据库
SHOW DATABASES; 
-- 使用/打开指定数据库
USE loan_xiaodai;
-- 查看当前数据库所有表
SHOW TABLES;
-- 查看指定数据库所有表
SHOW TABLES FROM loan_xiaodai;
-- 查看指定数据库表的表结构
DESC open_city;
-- 查看数据库版本	8.0.22 
SELECT VERSION(); 
-- 方式二：命令：mysql -V 或 mysql -version

常用关键字

distinct 去重

SELECT distinct id from emp; 

escape 转译

相当于""

select name from emp 
where name like '_\_%';
-- 等价于==>
Select name from emp 
Where name like '_$_%' escape '$';
-- 等价于==>
Select name from emp 
Where name like '_@_%' escape '@';

常用函数

常见函数分类：
单行函数：
eg：concat、length、ifnull等
分组函数：
功能：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

字符函数：

concat() 字符串拼接 CONCAT()

select concat('a', 'b','cd') AS 结果; -- 返回拼接结果： abcd

length() 获取参数值的字节数

select length('name'); -- 返回name的字节数： 4

upper() 变大写

select upper('Abdcvd') 变大; -- ABDCVD

lower() 变小写

select lower('Abdcvd') 变小; -- abdcvd

substr() 截取字符串 索引从 1 开始

-- 截取从指定索引处后面所有字符
select substr('小白很菜',4); -- 菜
-- 截取从指定索引处指定字符长度的字符 
select substr('小白很菜',1,2); -- 小白

substring() 截取字符串 索引从 1 开始

-- 截取从指定索引处后面所有字符
select substring('小白很菜',4); -- 菜
-- 截取从指定索引处指定字符长度的字符 
select substring('小白很菜',1,2); -- 小白

instr() 返回字串第一次出现的索引 ，没有返回0

select instr('小白很菜','小白'); -- 1
select instr('小白很菜','小白菜'); -- 0

trim() 去前后的空格（不去中间的）

select trim('        小白很菜      '); -- 小白很菜
select trim('        小白  很菜      '); -- 小白  很菜

lpad() 用指定的字符实现左填充指定长度

select lpad('小白很菜',10,'*'); -- ******小白很菜
select lpad('小白很菜',10,'**$$'); -- **$$**小白很菜
select lpad('小白很菜',2,'**$$'); -- 小白

rpad() 用指定的字符实现右填充指定长度

select rpad('小白很菜',10,'*');-- 小白很菜******
select rpad('小白很菜',10,'**$$'); -- 小白很菜**$$**
select rpad('小白很菜',2,'**$$'); -- 小白

replace() 替换

select replace('小白很菜','菜','6'); -- 小白很6

数学函数

round() 四舍五入

select round(1.345); -- 1
select round(-1.345); -- -1
select round(1.545); -- 2
select round(-1.545); -- -2
select round(1.345,2); -- 1.35

ceil() 向上取整（>=当前参数的最小整数）

select ceil(1.92); -- 2
select ceil(-1.92); -- -1

floor() 向下取整（<=当前参数的最大整数）

select floor(1.92); -- 1
select floor(-1.92); -- -2

truncate() 截断

select truncate(1.6999,1); -- 1.6
select truncate(1.6999,3); -- 1.699

mod() 取余

select mod(10,3); -- 1
select mod(-10,3); -- -1
select mod(10,-3); -- 1

日期函数

序号	格式符	功能
1	%Y	四位的年份 yyyy
2	%y	2位的年份
3	%m	月份(01,02,03…11,12)
4	%c	月份(1,2,3…11,12)
5	%d	日(01,02…)
6	%H	小时(24小时制)
7	%h	小时(12小时制)
8	%i	分钟(00,01…59)
9	%s	秒(00,01,…59)

now() 返回当前系统日期+时间

select now(); -- 2022-01-19 19:49:00

curdate() 返回当前系统日期

select curdate(); -- 2022-01-19

curtime() 返回当前系统时间

select curtime(); -- 19:49:22

获取指定部分的年月日时分秒

select year(now()) 年; -- 2022
select year('2062-09-08') 年; -- 2062
select month(now()) 月; -- 1
select month('2062-09-08') 月; -- 9
select monthName('2062-09-08') 月; -- September
select day('2062-09-08') 日; -- 8
select hour(now()) 时;
select minute(now()) 分;
select second(now()) 秒;

str_to_date() 将字符通过指定的格式转换成日期 “yyyy-mm-dd”

select str_to_date('2021-02-07','%Y-%m-%d'); -- 2021-02-07
select str_to_date('2021-2-7','%Y-%c-%d'); -- 2021-02-07
select str_to_date('2-7 2021','%c-%d %Y'); -- 2021-02-07

data_format() 将日期转换成字符

select date_format(now(),'%Y年%m月%d日'); -- 2022年01月19日
select date_format(now(),'%m月%d日 %Y年'); -- 01月19日 2022年
select date_format('2021-02-07','%Y年%m月%d日'); -- 2021年02月07日

其他函数

SELECT VERSION(); -- 当前版本号 8.0.22
SELECT DATABASE(); -- 当前数据库 XXX
SELECT USER();  -- 当前登录的用户 CCC

判断相关函数

IFNULL(某字段,默认值)
//eg:如果name值为null，默认为admin
IFNULL(name,'admin')
IF(某字段和另一个字段判断,TRUE为字段1,FALSE为字段2)
//eg:如果age值为21岁以上，显示Y，否则N
IF(age>21,Y,N)

流程控制函数

if

select if(5>3,'大','小');

case

-- case 表达式/字段 when 常量1 then 值1/语句1  when 常量2 then 值2/语句2 else 其他值/其他语句 end

-- case when 条件1 then 值1/语句1  when 条件2 then 值2/语句2 else 其他值/其他语句 end

select case 5>3 
when true then '大'
when false then '小'
end as result;

select case 
when 5>3  then '大'
when 5<3 then '小'
end as result;

分组函数

功能

用作统计使用、又称聚合函数或统计函数或组函数

分类

sum求和、avg 平均值 一般处理数值类型
max最大值、min最小值 、count 计算个数 可以处理所有类型

说明

处理的时候会忽略null值
eg： 表中10条数据，3条某字段为null， 只处理剩余的7条数据

demo

select sum(salary) from emp; -- 统计总工资
select avg(salary) from emp; -- 统计平均工资
select max(salary) from emp; -- 统计最大工资
select min(salary) from emp; -- 统计最小工资
select count(salary) from emp; -- 统计有工资的数量

count说明

一般常与distinct一起使用用于去重

	count(*)、count(1)、count(字段)
	MYISAM 存储引擎下，count(*)效率高；
	INNODB 存储引擎下，count(*)和count(1)差不多，但都比count(字段)效率高；

SQL标准分类

按年代分类：

Sql92标准：只支持内连接

等值连接：

select a.*,b.* from a,b where a.XxX=b.XxX

非等值连接：

A>0 and A <10 ; 
==>
A between 0 and 10

自连接：

select a.*,b.* from A a, A b where a.XxX=b.Xx

Sql99标准

语法：

		select查询列表
		from 表1 别名 〖连接类型〗
		join 表2 别 名 
		on 连接条件
		 〖where 筛选条件（查询表数据的筛选）〗
		 〖group by 分组〗
		 〖having 筛选条件（分组后的筛选条件）〗
		 〖order by 排序列表 〗 

按功能分类 ：

内连接： inner

	等值连接（交集）：

		select a.*,b.* from a 
		inner join b on a.XxX=b.XxX

	非等值连接

		select a.*,b.* from a
		inner join b on a.XxX>b.Xx and a.XxX<b.XxX

	自连接

		select a.*,b.* from A a,A b 
		inner join b on a.XxX=b.Xx

外连接 ：

	左外连接 left  〖OUTER〗

-- a表全数据，b表补null
select a.*,b.* 
from a 
left join b on a.XxX=b.XxX

	右外连接 right 〖OUTER〗

-- b表全数据，a表补null
select a.*,b.* from a 
right join b on a.XxX=b.XxX

	全外连接 full  〖OUTER〗 mysql 不支持

-- 并集
select a.*,b.* from a 
full join b on a.XxX=b.XxX

交叉连接(笛卡尔积)

cross join

select a.*,b.* from a 
cross join b on a.XxX=b.XxX

union关联查询

select a,b,c from A 
union 
select a,b,c from B 

union会自动去重，union all不去重

分页查询 limit

limit page,size （page是0开始）
eg:limit 10,2
从第10条开始，查两条数据
eg:limit 2
从第0条开始，查两条数据

子查询

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

分类：

按子查询出现的位置：

 select 后面： 仅仅支持标量子查询
 from   后面： 支持表子查询
 where/having 后面：标量子查询、列子查询、行子查询
 exists 后面： 表子查询（相关子查询）

说明：
标量子查询（单行）： 一行一列 搭配 >、 < 、>=、<=、<>、=使用
列子查询（多行）： 一列多行 搭配 in、any/some、all使用
行子查询（多行多列）： 一行多列/多行多列
表子查询（多行多列）： 多行多列