Mysql基础3 - 实践

一、MySQL

• MySQL 中日期指的是年月日、时间指的是时分秒
  • MySQL 中年月日格式:'xx-xx-xx'、时间格式:'yy:yy:yy'、日期时间格式:'xx-xx-xx yy:yy:yy'

1、内置函数

1-1、日期函数

• current_date():当前日期-获取当前年-月-日
• current_time():当前时间-获取当前时:分:秒
• current_timestamp():时间戳-以年-月-日 时:分:秒形式展示
• now():日期时间-获取当前年-月-日 时:分:秒
• date(datetime):返回 datetime 参数的日期部分
• date_add(date,interval d_value_type):在 date 添加天数/时间
• date_sub(date,interval d_value_type):在 date 减去天数/时间
• datediff(date1,date2):两个日期相差天数,前者减后者

1-2、字符串函数

• charset(column):查看表中字段数据的类型
• concat(string1,string2,...):将字符串拼接函数
• instr(string,substring):判断 substring 是否在 string 中，存在则返回子串在主串的下标位置
• ucase(string):将内部小写的字符串转成大写的字符串的函数
• lcase(string):将内部大写的字符串转成小写的字符串的函数
• length(string)求字符串字所占节数大小函数
• replace(str,search_str,replace_str):从 str 找到 search_str 子串，并把这个子串用 replace_str 替换
• substring(str,position [,length]):从 str 的 position 开始，取 length 个字符
• ltrim(string):去掉 string 内部开头的空格
• rtrim(string):去掉 string 内部结尾的空格
• trim(string):去掉 string 内部开头和结尾的空格

1-3、数学函数

• abs(number):绝对值函数
• bin(decimal_number):十进制转换成二进制函数
• hex(number):转换成 16 进制
• conv(number,a,b):将 number 由 a 进制转换成 b 进制
• ceiling(number):向上取整
• floor(number):向下取整
• format(number,a):保留 number 小数的 a 位精度
• mod(a,b):取模运算 a%b
• random():生成 0~1 的随机浮点数

1-4、其他函数

• user():查看当前用户函数
• md5(a):MySQL 用户密码存储并非直接保存的，而是通过加密后进行保存的(保存的是加密后的密码)，此函数就是哈希加密用的
• password(a):MySQL 专门加密密码的函数

2、复合查询–进阶重要

• 所有多表都要先转化成单表进行查询

2-1、子查询

• where/from 子句内部可以写 select+其他过滤语句作为 where 子句的一部分
  • where 子查询
  • from 子查询
• 先执行子查询语句

2-1-1、单行子查询-where 子查询

• 子查询返回的结果是单行单列的
  • where [column=(select ....)]

2-1-2、多行子查询-where 子查询

• 子查询返回的结果是多行单列的
  • where [column in (select ....)]:column 字段数据只需要在 select…表中的数据即可即可
  • where [column [>/</=] all (select ....)]:column 字段数据 [>/</=] 所有 select…表中的数据即可
  • where [column [>/</=] any (select ....)]:column 字段数据 [>/</=] 任意一个 select…表中的数据即可

2-2-3、多列子查询-where 子查询

• 子查询返回的结果是多列的
  • where (colum1,colun2,...) = (select column1,column2,... from ...)

2-2-4、from 中使用子查询

• from 中的结果当作临时表
  • from (select column1,column2,... from ...)

2-2、多表查询

• 多表查询本质:将两张表进行笛卡尔基放到内存中
  • selecct * from [表1],[表2],...[, 表n]
• 这个笛卡尔基后的表字段会有重复的，我们需通过[表].[字段]来进行操作

2-3、自连接

• 同一张表进行笛卡尔基就是自连接，直接进行笛卡尔基是无法连接的，需要对两张表的表名在 from 子句里重命名成不一样的名字

2-4、合并查询–用不多

• 将多个 select 执行的结果合并成一个表
  • union:获取两个结果的并集，将多余的行去重
  • union all:获取两个结果的合并，不将多余的行去重

3、内外连接

• 内连接本质:利用where子句对两种表形成的笛卡尔积进行筛选

3-1、内连接

• 语法:select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件
  • 先执行from 表1 inner join 表2 on 连接条件再执行and 其他条件其次执行select 字段然后再执行group by进行分组后，紧接着必须使用聚合函数构成新表后，再交给select [列投影] from投影出列，然后使用having子句行筛选，之后order by [排序条件]排个序，最后limit [分页条件]行筛选出最终结果交给用户
• 笛卡尔积就是内连接

3-2、外连接

• 非笛卡尔式拼接，单纯的把左右两表对应字段数据匹配，将全部匹配正确的数据所在行进行直接拼接，拼成一个新表

3-2-1、左外连接

• 连接后把left关键字左侧的表完全显示，且不过滤其表中数据
• 语法:select 字段名 from 表1 left join 表2 on 连接条件

3-2-2、右外连接

• 连接后把right关键字右侧的表完全显示，且不过滤其表中数据
• 语法:select 字段名 from 表1 right join 表2 on 连接条件