mysql笔记

基础知识(了解)

mysql版本

常用的mysql版本 5.7

---

DBMS

DB 数据库

dbms 数据库管理系统

rdbms 关系型数据库

持久化

把数据（如内存中的对象）保存到可永久保存的存储设备中（如磁盘）

mysql主要的两个目录

data 文件夹 存放的是 数据库

my.ini 文件 mysql配置文件

sql分类

DDL:数据定义语言 create[创造][亏-A-特] \ alter[改变][奥-ter] \ DROP[放弃][zha-pe] \ rename[重命名] \ truncate[截断][穿-kei-特] 清空

DML:数据操作语言 insert 添加 \ delete 删除 \ update 修改 \ select 查询

DCL:数据控制语言 commit 提交 \ rollback 回滚 \ savepoint[sei-pa-in-ter] 保存点 \ grant[哥-ru|ang-te ] 赋予权限 \ revoke[锐-wou-克] 回收权限

USE

use 表名 //选中某张表 注:如果在cmd中操作需要先选中表名

导入数据

source 文件的全路径名

常用的sql标准

92 和 99

1.查询

1.基础

1. AS 别名

as:全称alias

给字段起别名的三种方式

select 字段1 别名1,字段2 as 别名2,字段3 “别名3” from 表名

给表名起别名

select * from 表1 别名,表2 别名 where 条件

给表取别名之后原名就不能用了 必须用别名

3. null值参与运算

null值参与运算 结果一定为null

ifnull(字段1,默认值)
如果字段1的值为null 则返回默认值

4. 着重号

’ ’

表名或字段名 起的如果是保留字则使用 着重号 区分

5. 显示表结构

describe 表名

显示 表中字段的详细信息

6. where过滤数据

select * from 表名 where 字段=值

where 一定要写在 from 后面

2.运算符

1.算数运算符

+ - * /或div %或mod

加 减 乘 除 取余

2.比较运算符

= <=> !=

等于 安全等于 不等于 大于 …

假 返回 0 (比如1=2 返回 0)

真 返回 1 (比如 2=2 返回 1)

字符串跟数字比较 字符串默认进行(隐式转换)转换成0 (比如0=‘a’ 返回 1)

字符串跟字符串比较 比较不会进行(隐式转换)转换

null比较 只要有null值参与比较结果 为 null (不包括安装等于)

安全等于 为null值而生: 两边都为null返回1 , 一边为null返回0

逻辑运算符

and 类似于 && (逻辑与)
or 类似于 || (逻辑或)
not 类似于 ! (逻辑非)

XOR 异 满足一个 但不能 满足另一个(一个满足,另一个不满足)

select  *  from  user where  city='北京' and  sex='男'; 	//与和或
select  *  from  user  where  not city='上海'		//非		查找不是上海的
select * from user where not city in ('上海','北京') //不是北京和上海的

3.单条件

= != > < <= >=

select * from 表名 where 字段=‘值’

查找多个字段用逻辑与 或 逻辑或 例:
where 字段1=‘值a’ and 字段2=‘值b’
在字段1里查找值a并且字段2里面也是值b

where表示筛选条件

in 和 not in

in 和 not in 查找多个 只要包含里面一个

在这两个列表之中(只要包含里面一个就行)

select * from 表名 where 字段1 in (‘值1’,‘值2’)
字段1里只要有值1或者值2就显示
包含这些值
字段1里包含in 后面括号里的值

not in
不包含这些值

between 和 not between

between 和 not between 查找区间 介于 值1 到 值2 之间 (值1 下界 值2 为上界 也就是值2不能比值1小)

在这两个数之间(包含这两个值(包含边界));
not between不包含边界

select * from 表名 where 字段1 between 数字值1 and 数字值2;

**like 和 not like **

like不区分到小写
like binary区分大小写

select * from 表名 where 字段1 like binary ‘%值_’

‘值1’ 字段1的值必须等于值1 //安全匹配,完全匹配

%:表示不确定个数的字符
‘天%’ 查找第一个为 天 的 //模糊匹配
‘%天%’ 查找只要里面有 天 字的
‘%天’ 查找最后一个为 天 的

_ 表示一个任意字符
‘李_’ 查找姓李,并且名是一位的
‘李__’ 两位

rlike和regexp

regexp和rlike 支持正则表达式 //模糊匹配

rlike不区分大小写
rlike binary 区分大小写
not rlike 取反

select * from 表名 where 字段1 rlike ‘值1’
字段1里面的值包含值1的
‘^值2’ 开头是值2的
‘值3$’ 结尾是值3的
值1可以换成正则表达式

is null 和 is not null

获取为null的数据
获取不为null的数据

用法: where 字段 is null //返回为null的数据

not 取反

where not 1=2 返回 1

least() 和 greatest()

least 返回最小的

greatest 返回最大的

用法:select least(字段1,字段2,…) from 表名

distinct 去重

distinct 去除重复数据

select distinct 字段1 from 表名

4.排序

select * from 表名 order by 字段1,字段2 desc
先按照字段1排序再按照字段2排序
order by 字段1 排序(从小到大,第一个是最小的)
desc 降序
多个字段都想要降序时,每个字段后面都需要加desc

列的别名只能在 order by中使用,不能再where中使用

查询语句底层执行顺序:

from -> where -> select -> order by 所以别名不能再where中使用

多级排序

先按照 字段1 排序,再按照 字段2 排序 再按照字段3

select * from 表名 order by 字段1,字段2 desc,字段3 ASC

5.分页

1.分页

limit
查询前3条数据
select * from user limit 0,3
0位置,3数量
从哪个位置开始取,取几个

pageSize 每页显示条数
pageNo 页数(第几页)

得出公式
limit (pageNo-1) * pageSize , pageSize

声明顺序
select * from 表名 where 条件 order by 字段1 desc,字段2 limit 0 , 10;

6.多表查询

1.内连接

等值连接/内连接inner 为null的数据不显示
sql92语法实现: select * from 表1,表2 where 表1.主键=表2.外键

sql99语法实现: select * from 表1 join 表2 on 表1.主键=表2.外键

sql99语法实现三张表: sql99语法实现: select * from 表1 join 表2 on 表1.主键=表2.外键 join 表3 on 表2.字段=表3.字段

内连接:结果中不包含不匹配的行

1. 非等值连接

区间连接

select * from 表1,表2 where 表1.字段 bewteen 表2.字段1 and 表2.字段2

表1.字段的值 需要在 表2.字段1和字段2 之间

2.自连接

自己连接自己 (一张表 当 两张表 使用)

select 别名1.字段,别名2.字段 from 表1 别名1,表1 别名2 where 别名1.字段=别名2.字段

select e1.employee_id,e1.last_name,e2.employee_id,e2.last_name 
from employees e1,employees e2 
where e1.employee_id=e2.manager_id

2.外连接

外连接:结果中包含不匹配的行

左右外连接 left right
select * from 表1 left join 表2 on 表1.主键=表2.外键

全连接
左连接 union 右连接

select * from emp e left join dept t on e.deptId=t.deptId
union
select * from emp e right join dept t on e.deptId=t.deptId

1.左外连接

返回左表中不匹配的行

select * from 表1 left join 表2 on 表1.主键=表2.外键

2.右外连接

返回右表中不匹配的行

select * from 表1 right join 表2 on 表1.主键=表2.外键

3.union

合并查询结果

union 和 union all的区别:
uniton去除重复数据
uniton不去除重复数据

4.七种join的实现

内连接 : select * from 表1 join 表2 on 表1.主键=表2.外键

左外连接 : select * from 表1 left join 表2 on 表1.主键=表2.外键

右外连接 : select * from 表1 right join 表2 on 表1.主键=表2.外键

只要表1中 和表2中不匹配的数据 : select * from 表1 left join 表2 on 表1.主键=表2.外键 where 表1.主键 is null

只要表2中 和表1中不匹配的数据 : select * from 表1 right join 表2 on 表1.主键=表2.外键 where 表2.主键 is null

满外连接 : 左外连接 nuion 右外连接 或者 左外连接 nuion all 只要表1中 和表2中不匹配的数据

只要两张表不匹配的数据 : 只要表1中 和表2中不匹配的数据 nuion all 只要表2中 和表1中不匹配的数据

7.函数

1.单行函数

abs(x) 求绝对值

sign(x) 判读正负数 负数返回-1,整数返回1

pi() π

ceil(x) 天花板 返回不小于x的最小整数值

floor(x) 地板数 返回不大于x的最大整数

mod(x,y) 求余 x除以y后的余数

rand() 求随机数 0-1

round(x) 四舍五入

round(x,y) 四舍五入 保留y位小数

truncate(x,y) 截取(舍去) 截取y位小数

sqrt(x) 开方 返回x的平方根

2.三角函数

看 [宋红康]mysql版 33级

3.字符串函数

mysql中下标从1开始

1.char_length() 字符串长度

length(字段)
一个中文占三个字节所有一个中文长度为3

char_length()
一个中文长度为1

2.concat() 字符串连接

concat(字段1,字段2)

concat_ws(‘-’,字段1,字段2,字段2) //中间用短横线连接

3.insert() 替换

insert(字段,下标,删除数量,‘替换的值’)
inset(name,2,0,‘-’)
在下标为2的位置,删除0个字符,替换成-

下标从1开始

4.replace() 替换

replace(字段,‘被替换的值’,‘替换成的值’)

replace(字段,a,b) 将a替换成b

5.转小写lower() 和 转大写upper()

lower(字段)

6. left()左截取 和 right()右截取

select *,left(字段,x) from 表名 //从左边取值,取x位

7.lpad()左填充 和 rpad()右填充

select *,left(字段,3,‘#’) from 表名
如果字段里的值不够3个则在左面补#(可以是其他字符)
select *,left(name,3,‘#’)

8.trim() 去掉两边的空格

select trim(’ aaa ')

ltrim 只去掉左边的空格

rtrim 只去掉右边的空格

trim(x from s) 去除字符串s两边的x字符

trim(leaning x from s) 去除字符串s左边的x字符

trim(trailing x from s) 去除字符串s右边的x字符

9.repeat() 重复

repeat(‘hello’,4) 输出hellohellohellohello

将结果重复n次

10.space() 返回空格

space(n) 返回n个空格

11.strcmp()

strcmp(x,y) 比较x和y的ascll x大返回1 y大返回-1

12.substring() 从中间截取

select *,substring(字段1,下标,取几位) from 表名
select *,substring(name,2,1) from user
从下标为2的位置开始截取,截取1位

13.查找字符

locate(‘字符1’,字段)
查找字符1首次出现的下标的位置 返回下标
没有返回0;

区间查找
locate(‘字符1’,字段,下标)
从指定下标位置开始查找

14.nullif

nullif(x,y) 比较x和y是否相等,相等返回null 不相等返回x

15.reverse

reverse(asd) 反转字符串

不懂 看 [宋红康]mysql版 34级

4.日期和时间函数

1.curdate() 获取当前日期

curdate()

2.curtime() 获取当前时间

curtime()

3.now() 获取当前日期加时间

now()

4.日期与时间戳 转换

unix_timestamp() 不加参数获得当前时间时间戳

unix_timestamp(date) 将date以时间戳的形式返回

from_unixtime(timestamp) 将时间戳转换成时间格式返回

5.获取月份 星期 星期数 天数 等函数

year(date) 返回 日期 中的 年份
month(date) 返回月份
day(date) 返回天数

hour(time) 小时
minute(time) 分钟
second(time) 秒

monthname(date) 返回这一天对应的月份

dayname(date) 返回星期几

weekday(date) 返回星期几 周一是0,周二是1,…

quarter(date) 返回季度

week(date) 返回一年中的第几周

dayofyear(date) 返回一年中的第几天

dayofmonth(date) 返回日期所在月份的第几天

dayofweek(date) 返回周几 周日是1,周一是2,…

6.日期的操作函数

extract(type from date) 返回指定日期中特定的部分，type指定返回的值

例:extract(second from new()) 返回当前秒数
extract(second from ‘2022-01-20’) 返回当前秒数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GQt1gIzW-1648707710487)(mysql%E5%9F%BA%E7%A1%80%E7%AF%87.assets/日期操作函数.png)]

7.计算日期和时间函数

date_add()

date_add(日期字段,interval n day) 将日期字段增加n天

date_add(now,interval ‘1_1’ minute_second) 将当前时间增加1分1秒

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tekG56FA-1648707710491)(mysql%E5%9F%BA%E7%A1%80%E7%AF%87.assets/image-20220120171612171.png)]

addtime(new(),date) 在当前时间上加上date时间

subtime(date1,date2) 将date1时间减去date2时间

datediff(date1,date2) 返回date1-date2的日期间隔天数

timediff(time1,time2) 返回time1-time2的时间间隔

last_day(date) 返回date所在月份的最后一天

8.日期的格式化和解析

格式化: 将 日期 转换成 字符串

解析: 将 字符串 转换成 日期

格式化:

date_format(date,fmt) 将日期格式化成fmt格式
例:date_format(now(),‘%Y-%M-%D’)

time_format(time,fmt) 将时间格式化成fmt格式

解析:

str_to_date(str,fmt) 将字符串str解析成日期格式, str格式需要和fmt格式一致

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UBDP5nLg-1648707710493)(mysql%E5%9F%BA%E7%A1%80%E7%AF%87.assets/image-20220120174728120.png)]

5.流程控制函数

逻辑if, ifnull, case

if()

if(expr1,expr2,expr3)
类似于三目运算
expr1为true返回expr2
expr1位flase返回expr3

expr1必须是条件

if(credit<300,'一般客户','优质客户')

ifnull() 判断是否为空

IFNULL(值1,值2,)
值1 如果不是null值 返回 值1
值1 是null则返回 值2

case

用法一(类似于if判断)
select (case when 条件1 then ‘值1’ when 条件2 then ‘值2’ else ‘值3’ end) 别名
如果条件1成立执行值1,都不成立执行值3

select userId,name,credit,
(case when credit>300 then '优质客户' when credit>200 then '中等客户' else '一般客户' end) 别名
from user

用法二(类似于)

select userId,name,credit,
(case city when '北京' then '首都' when '上海' then '魔都' when '深圳' then '特区'  else '省会' end) 别名
from user

city等于北京返回首都,等于上海返回魔都,都不等于返回省会;

6.其他不重要函数

1.加密与解密函数

2.mysql信息函数 //查看mysql版本,数据库编码等信息

8.聚合函数

1.常用的聚合函数

聚合函数只能用在having后,不能用在where后

select 后面要是有聚合函数时, 再写字段时字段必须是group by分组的字段

//只能用于 数值型 的字段
avg() 求平均值
sum() 求和

---

//可以用于 字符串 数值 日期 类型
max() 最大值
min() 最小值

---

count 返回有多少行数据(不计算null值) 返回数字
count(1) 返回表中有多少条数据

注意:聚合函数不能嵌套,单行

2.group by 分组

group by 字段

多列分组: group by 字段1,字段2

select 展示的字段必须是 分组的字段,或者是聚合函数

使用顺序 from -> where -> group by -> order by -> limit

3.having

9.sql底层执行原理

书写顺序:

select (要展示的字段,聚合函数)
from join on (表名,多表关联)
where (非聚合函数查询条件)
group by (分组字段)
having (分组后查询条件包含聚合函数)
order by (排序字段1,字段2)
limit (分页)

select 字段1,聚合函数() from 表1 join 表2 on 表1.外键=表2.外键 where 查询条件 group by 分组字段 having 查询条件 order by 排序字段 limit 0,10;

执行顺序:

from join on -> where -> group by -> having -> select -> order by -> limit

10.子查询

注意事项:

1. 子查询要包含在括号内

2. 单行操作符对应单行子查询,多行操作符对应多行子查询

3. 子查询如果当表使用必须加 别名

4. 子查询不能在 group by 和 limit 中使用,

单行子查询表示 返回一条数据

多行子查询表示 返回多条数据

1.单行子查询

单行子查询操作符 : = != > < 等等

2.多行子查询

多行子查询操作符:

in 等于列表中的任意一个

any

all

SELECT * from user where city=(select city from user where userId=101);

select * from (select * from ) 别名 where 条件
嵌套当表使用时必须 加别名

in

括号里的查询结果为多个值时用in

any

select  userId,name,credit,city 
from  user 
where  credit >any(select credit from user where city='上海')

比里面任意一个大 或 任意一个小 或 等于里面任意一个

all

select  * 
from  user 
where  credit > all (select credit from user where city='上海')

比里面 最大的大 或 最小的小

3.相关子查询

// 子表里面用外表的字段 (使用方法:外表.字段)

select * from 表1 表1别名 where (select 字段 from 子表 字表别名 where 表1.字段=条件)=条件;