MySql 数据库的查询操作

1、 基础语法

show databases #查看当前所有的数据库
use  数据库名   # 打开指定的数据库
show tables    # 显示该数据库中的列表
show tables from 库名; # 查看其它库所有表
create table 表名(
		列名 列表类型，
		列名 列表类型，
		···
		)
desc 表名;  #查看表结构

2、基础查询

# 查询列表，列表可以为表中的字段、常量值、表达式、函数
select 列表
# 为字段起别名，如果别名中含有字符（空格）需要加上双引号，as 也可以省略
select 列表 as "别名"
# 去重
select distinct 字段 from 库名
/* 
	连接，使用concat(str1,str2,···), +号只能做运算符
		字符+数值 如果转换成功，则相加，否则，字符串则为0
		null+数值 全为null
*/
select concat('a','b','c')    # abc
# 判断某字段或表达式是否为NULL，如果是返回指定的值
ifnull(commissipn_pct, 0)

3、条件查询

select 查询列表(最后查3) from 表名(先查1) where 筛选条件(再查2)

按筛选条件可以分为三类：

# 条件表达式  >  <  =  <>（不等于）  >=  <=
# 逻辑运算符  and(&&)  or(||)  not(!)
# 模糊查询
1)	like '%a%'  #包含a的字段，一般与通配符通用
		% 任意多个字符
		- 任意单个字符
		\ 转义字符
		escape # 将字符变成转义字符，示例：like '_$_%' escape '$'
2)	between A and B		# 在A和B之间，包含边界值，A<=B 提高了代码的简洁度
3)	in(str1,str2,···)	# 判断某字段的值是否属于in列表中的某一项,列表中不能出现通配符，必须与列表中的字段一致
4)	is null				# 用于判断null值

4、排序查询

# 语法 asc 表升序/desc 表降序，如果不写默认升序
select 查询列表 from 表名 [where 筛选条件] order by  排序列表 (asc/desc)

# 排序列表可以为，单个字段、函数名、表达式、别名、多个字段排序

# 四大排序函数：
# https://blog.youkuaiyun.com/shaiguchun9503/article/details/82349050
# 排序函数row_number、rank、dense_rank的区别
# 口诀：
row_number 不存在并列
dense_rank 和rank存在并列，但rank很跳。
# 口诀的意思是：
row_number：不考虑并列的情况，哪怕分数相同，排名都是一溜下来的自然数。
dense_rank和rank 考虑并列的情况，区别在于rank很跳，并列排名的个数会影响接下来的排名，表现为数字的中断。而dense_rank 不管有几个并列的第5名，接下来都是从6开始排。

5、常见函数

(1) 单行函数之字符串函数：

# 将两个或多个字符串连接为一个字符串
concat(str1,str2,···)
# 如果字符串为NULL，则返回指定的值
ifnull(str1,)
# 计算字符串的字节长度
length(str)
# 将字符串转化为大写形式
upper(str)
# 将字符串转化为小写形式
lower(str)
# 字符串从1开始，截取字符串从指定索引处所有字符
substr(str1,start,end)
# 查找str2在str1的位置，如果有则返回子串出现第一次的索引，没有返回0
instr(str1,str2)
# 剔除字符串中左右的空格,或者剔除字符串左右的ch字符
trim(str[ch from str1])
# 用指定的ch字符，左填充len长度
lpad(str,len,ch)
# 用指定的ch字符，右填充len长度
rpad(str,len,ch)
# 将字符串中from_str替换为to_str
replace(str,from_str,to_str)

(2) 单行函数之数学函数：

# 四舍五入,可以保留n位小数
round(num,n)
# 向上取整 ，大于等于该值的最大整数
ceil(num)
# 向下取整 ，小于等于该值最大整数
floor(num)
# 截断,n后所有数值都舍去
truncate(num,n)
# 取模:a-a/b*b
mod(num1,num2)

(3) 单行函数之日期函数:

# 当前日期+时间
now()
# 当前日期，不包含时间
curdate()
# 当前时间
curtime()
# 获取指定年、月、日、时、分、秒
year()
month()
day()
# 返回日期的月份名称
monthname()
# 将字符串通过指定格式转换成日期
str_to_date(date_str,'%Y-%C-%d')
# 将日期转化为字符
date_format(date,'%Y年%C月%d日')

(4) 单行函数之其他函数

# 查看版本号
version()
# 查看当前库名
database()

(5) 单行函数之流程控制函数

if(条件,条件正确的执行内容,条件错误时执行内容)
case   # 值后面不加;
	1:类似于switch···case···
		case 要判断的字段或者表达式
		when 常量1 then 要显示的值或者语句
		when 常量2 then 要显示的值或者语句
		···
		else 要显示的值n或语句n
		end
	2:类似于 if···elif···else
		case
		when 条件1 then 要显示的值或者语句
		when 条件2 then 要显示的值或者语句
		··
		else 要显示的值n或语句n
		end

(6) 分组函数（统计函数、聚类函数、组函数）

# 最大值
max()  # 忽略null值
# 最小值
min()  # 忽略null值
# 计算个数
count()
	使用count('*') 统计行数效率较高，也可使用count(1)/count(const)
# 计算平均值
avg() # 忽略null值，处理数值型
# 求和
sum() # 忽略null值，处理数值型

# 可以使用distinct搭配使用
示例：select sum(distinct salary) from employees

# 和分组函数一同查询的字段有限制，要求为group by 后的字段

6、分组查询

# group by 语法
语法：
	select 		column,group_function(column)
	from   		table
	[where 		condition]
	[group by	group_by_expression]
	[order by	column]

# 分组前筛选(原有数据有) 		group by 前   where关键字
# 分组后筛选(原始数据没有) 	group by后	having关键字

7、连接查询

含义：多表查询，查询的字段来自多个不同的表

笛卡尔乘积：表1 m行， 表2 n行 结果；m*n行

发生原因：没有有效的连接条件

# 按有效条件分类
	sql192标准：仅仅支持内连接
	sql199标准【建议】：支持内连接和外连接（左，右）交叉连接 
# 按功能分类
/*	
	内连接：
		等值连接
		非等值连接
		自连接
*/
/*		
	外连接：应用场即将能够：一个表中有，一个表中没有
		左外连接：左边的表为主表，将主表所有记录显示出来，如果主表在从表中有匹配的，正常匹配；如果没有匹配的则显示NULL
		
		右外连接：右边的表为主表，右边的表为主表，将主表所有记录显示出来，如果主表在从表中有匹配的，正常匹配；如果没有匹配的则显示NULL
		
		全外连接：内连接部分+表1中有表2没有的+表2中有表1没有的
*/
/*		
	交叉连接：笛卡尔乘积
*/
sql192标准：
    1.等值连接
        # 如果起了别名，则原来的名字就不能使用了
        # 多表等值连接的结果为多表的交集部分
        # n个表需要n-1个等值条件
        # 多表的顺序没有要求
        # 可以与排序等一块使用，
        SELECT 字段1,字段2
        FROM 表1,表2
        WHERE 表1.字段1=表2.字段2

    2.非等值连接
        SELECT 字段1,字段2
        FROM 表1,表2
        WHERE 字段1 between 表2.字段2.1 and 表2.字段2.2 

    3.自连接
        # 将一个表看成多个表进行连接
        
sql199标准【建议】：支持内连接和外连接（左，右）交叉连接
语法：
        select 查询列表
        from 表1 别名 
        [连接类型] join 表2 别名 
        on 连接条件
/* 
        连接类型：
        内连接：inner 
        左外连接：left[outer] 
        右外连接：left[outer]	
        全连接：full[outer]
        交叉连接：cross
*/

8、子查询

(1) 定义：出现在其他语句中的select语句

外部的查询语句成为主查询或外查询

(2) 分类：

• 按子查询出现的位置：

  • select 后面
    • 仅仅支持表量子查询
  • from 后面
    • 仅仅支持表子查询
  • where 或 having 后面
    • 表量子查询
    • 列子查询
    • 行子查询
  • exists 后面(相关子查询)
    • 表子查询

• 按结果集的行列数的不同：

  • 标量子查询（结果集只有一行一列）
  • 列子查询（结果集只有一列多行）
  • 行子查询（结果集只有一行多列）
  • 表子查询（结果集一般为多列多行）

（3）where 或 having 后面

特点:

• 子查询放在小括号内
• 子查询一般放在条件的右侧
• 标量子查询，一般搭配着单行操作符使用

< 	 >  	=  	<= 	>= 	<>

• 列子查询，一 般搭配着多行操作符使用

in/not in：前面不加等于号，等于或不等于列表中in(str1,str2,···)任意一个

any/some：前面加等于号，和子查询返回的某一个值比较 a>any<==>min  

all：和子查询返回的所有值进行比较

9、分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

关键词：limit  offset ,size
offset:要显示的起始索引
size：要显示的条目数

# limit 放在所有语句的后面，也是最后执行的语句

10、联合查询

应用场景：将语句特别长的拆分成多个语句；更加适合从多个无关的表，获取相同列数的信息；自动去重，如果想全部显示，需加关键字（all）

关键字：union