MySql学习笔记一

MySql学习笔记一

1.SQL 分类

1. DDL(Data Definition Language)数据定义语言
2. DML(Data Manipulation Language)数据操作语言
3. DQL(Data Query Language)数据查询语言
4. DCL(Data Control Language)数据控制语言
5. TCL(Transaction Control Language)事务控制语言

2.常见指令

1. 查看当前所有数据库: show databases

2. 打开指定的库：use 库名

3. 查看当前库的所有表：show tables

4. 查看其它库的所有表：show tables from 库名

5. 创建表：create table 表名(

   ​ 列名 列类型,

   ​ 列名 列类型,

   ​ ......

   );

6. 查看表结构：DESC 表名

7. 查看服务器版本：select version() || mysql --V

8. 查看字符集：Show variables like '%char%'

9. 查看当前用户：Select USER();

3.DQL（数据查询语言）

3.1基础查询

select 查询列表 from 表名

查询列表可以是：表中字段、常量、表达式、函数

查询的结果是一个虚拟机的表格

3.1.1.常用关键字

3.1.1.1.起别名

as 或者 空格 例如 select 字段名 as a from 表名 as b

对字段的别名最好加上双引号，可以在别名里加空格

3.1.1.2.去重

利用关键字DISTINCT

select distinct 字段名 from 表名

3.1.1.3.'+'只有计算功能

1 + 1 = 2

'1' + 1 = 2

'a' + 1 = 1

null + 1 = null

3.1.1.4.CONCAT

select 字段名 CONCAT 字段名 （AS 别名）from 表名

CONCAT相当于java中的 '+'

---

3.2.条件查询

select 查询列表 from 表名 where 筛选条件

3.2.1.按条件表达式筛选

条件运算符：>, < , =, !=, >=, <= ; != MySql建议写为 <>

3.2.2.按逻辑表达式筛选

and or not

3.2.3.模糊查询

• like

  ​ %任意多个字符

  ​ _任意单个字符

  ​ 如果查询含有转移符号的数据，要用转义如\%和\_

  ​ 也可以用ESCAPE 如 like '_(%' ESCAPLE ')'

• between...and... 两边都包含

• in

  ​ in列表中的值类型必须一致或者兼容

  ​ 不支持通配符

• is null

  ​ null值判断不能用 = null 要用 is null, is not null

3.2.4.安全等于

​ <=> 可以判断Null值，也可以判断普通数值

​ 但是可读性较差

---

3.3.排序查询

示例： select 查询列表 from 表名 order by 排序列表 ASC(DESC)

ASC是升序排列，DESC是降序排列， 默认是ASC

排序列表可以是 字段名，表达式，别名，函数

支持按多个字段排序， 例 order by 排序列表1 ASC(DESC), 排序列表2 ASC(DESC)....

先按按排序列表1进行排序，然后排序列表1相同的按排序列表进行排序，以此类推。

---

3.4.常见函数

函数类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处：1.隐藏了实现细节 2.提高代码重用性

3.4.1.单行函数

如count, length, IFNULL等

3.4.1.1.字符函数

length

​ 统计参数值的字节个数，utf-8中文占三个字节, gbk中文占两个字节

CONCAT

​ 拼接字符串 例CONCAT(str1,str2,str3.....)

​ 和null拼接会编程null

upper,lower

​ upper变大写，lower变小写

substr, subString

​ 截取字符串

​ substr(str, index) 截取从index开始后面所有的字符

​ substr(str, pos, len) 截取从pos到len的字符

​ sql中索引是从1开始的

instr

​ instr(str1, str2)

​ 判断str2在str1中第一次出现的索引，如果找不到返回0

trim

​ trim(str)

​ 去除str中的前后空格

​ trim(str1 from str2)

​ 去除str2中开头结尾重复出现的str1

LPAD、RPAD

​ LPAD(STR1, LEN, STR2)

​ 在STR1的前面重复填充STR2直到长度为LEN，如果LEN < STR1的LENGTH， 会截断STR1

​ RPAD同理

Replace

​ Replace(STR1, STR2, STR3)

​ 将STR1中的所有的STR2都替换为STR3

3.4.1.2.数学函数

round

​ round(double) 四舍五入为整数

​ round(double, num) 四舍五入保留num位小数

ceil

​ ceil(double) 返回>=num的最小整数

floor

​ floor(double) 返回<=num的最大整数

truncate

​ truncate(double, num) 小数点num位以后截断舍去

mod

​ mod(num1, num2) 取余 相当于%

rand

​ 获取0-1之间的随机数

3.4.1.3.日期函数

now

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

CURDATE

​ CURDATE() 返回当前系统日期，不包含时间

CURTIME

​ CURTIME() 返回当前系统时间，不包含日期

获取日期指定部分

​ YEAR(date) 获取年

​ MONTH(date) 获取月

​ MONTHNAME(date) 获取月名

​ DAY, HOUR, MINUTE, SECOND同上

字符串与日期的相互转换

​ STR_TO_DATE('STRDATE', 'Format') 例STR_TO_DATE('9-13-2019', '%m-%d-%Y') m是月份，必须用m

​ 将字符串按照format格式转换成日期

​ date_format(date, format) 例 date_format(now(), '%m月%d日%Y年')

​ 将日期按照format格式转换成字符串

​

DateDiff

DateDiff(date1, date2)

返回的是date1日期减去date2日期的天数差

3.4.1.4 流程控制函数

if函数

​ 实现类似于if else 的效果

​ 例如 if(experssion, trueResult, falseResult)

case函数

使用1 实现类似switch case的效果

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
when 常量3 then 要显示的值3或语句2;
...
else 要显示的值或语句
end

使用2 实现类似于if ,else if...的效果

case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
when 条件3 then 要显示的值3或语句3;
...
else 要显示的值或语句
end

3.4.2.分组函数

做统计使用，又称为统计函数，聚合函数，组函数

• sum(): 求和

• avg(): 平均值

• max(): 最大值

• min(): 最小值

• count(): 计算个数

  count(字段名) 统计字段名非空的行数

  count(*) 统计总行数 等价于count(常量) 如count(1), count('a')等

  效率：MYISAM引擎下：count(*)效率最高

  ​ INNODB引擎下：count(*)和count(1)效率差不多,比cout('str')高

分组函数参数支持类型：

	字符型	日期型	数值型	忽略null值	与DISTINCT搭配
sum()	X	X	√	√	√
avg()	X	X	√	√	√
max()	√	√	√	√	√
min()	√	√	√	√	√
count()	√	√	√	√	√

与分组函数一同查询的字段有限制，一般都是group by之后的字段

---

3.5.分组查询

Select 分组函数, 字段(要求该字段出现在group by的后面)
from 表
(where 分组前的筛选条件)
group by 分组的字段1 (分组的字段2.....)
(having 分组后的筛选条件)
(order by 排序)

where筛选的是分组前的原来的表中的数据

having筛选的是分组后的数据

分组函数的条件一定是放在having子句中

为了性能，能用分组前筛选的条件尽量放在where子句中

当group by之后有多个条件时，条件的先后顺序不影响查询结果，条目分组时必须满足每一个条件都相等