MySQL基础
查询
基础查询
-
select 字段 from 表
- 着重符(`)
- 用于字段为关键字时
select `关键字字段` from 表
- 去重
- 关键字:distinct
select distinct 字段 from 表
- 起别名
- 使用空格 (常用)
select person_name n from 表
- 使用as
select person_name as n from 表
- 若别名为关键字,用双(单)引号即可
select person_name "OUT PUT" from 表
- 拼接字符-concat
- 用于两个或多个字符(字段)的拼接
select concat(last_name,first_name) 姓名 from 表
条件查询
-
select 字段 from 表 where 条件
-
简单条件运算符
> < = != <> >= <=
-
模糊查询关键字
like | between and | in | is null
-
逻辑运算符
&& || ! and or not --(建议使用)
模糊查询
- like
- 一般和通配符搭配使用
- 通配符:
- %:任意多个字符,包含0个字符
- _ :任意单个字符
- 案例1:查询person_name中包含字符a的人员信息
select * from person where person_name like '%a%'
- 案例2:查询person_name中第三个字符为b,第五个字符为a的人员信息
select * from person where person_name like "__b_a%"
- 案例3:查询person_name中第二个字符为_的人员信息**(使用转义字符)**
select * from person where person_name like "_\_%"
- between and
- 案例:查询person_id在100到200的人员信息
select * from person where person_id between 100 and 200
- in
- 案例1:查询person_salary是3000,5000,6000的人员信息
select * from person where person_salary in(3000,5000,6000)
- 案例2:查询person_name中包含字符a或字符z的人员信息
select * from person where person_name in ('%a%','%z%')
- is null(is not null)
- 案例1:查询没有工资的人员信息
select * from person where person_salary is null
- 案例2:查询有工资的人员信息
select * from person where person_salary is not null
- 安全等于<=>
- 可读性差,较少用。
- 案例1:查询没有工资的人员信息
select * from person where person_salary <=> null
- 案例2:查询有工资的人员信息
select * from person where person_salary is not null
排序查询
- order by
- 语法
select 字段 from 表 order by 排序字段 --[asc|desc]
-
asc代表升序,desc代表降序;默认为升序
-
案例1:查询人员信息,要求person_salary从高到低排序
select * from person order by person_salary desc
- 案例2:查询人员信息,要求person_salary从低到高排序
select * from person order by person_salary --[asc]
分组查询
- group by
- 语法
select 字段 from 表 group by 分组的列表
- having
- 用于分组后筛选
- 案例:查询哪个部门的员工个数>2
select count(*) ,department_id from employees group by department_id having count(*) >2
分页查询
- limit
- 语法
select 字段 from 表 limit offset,size; #offset:要显示条目的起始索引(起始索引从0开始) #size:要显示的条目个数
联合查询
- union
- 将多条查询语句的结果合并成一个结果
常见函数
单行函数
字符函数(10个)
- 字符索引从1开始,并非0
- length()
- 获取参数值的字节个数
- concat()
- 拼接字符
- upper()
- 字符转为大写
- lower()
- 字符转为小写
- substr()
- 截取字符;substr()等价于substring()
- 截取从指定索引处后面所有字符
select substr('一二三四五六七',3) out_put; --'三四五六七'
- 截取从指定索引处指定字符长度的字符
select substr('一二三四五六七',3,2) out_put; --'三四'
- instr()
- 返回子串第一次出现的索引,如果找不到返回0
select instr('一二三四五六七','三四') out_put; --'3'
- trim()
- 去除字符中的空格
select trim(' 一二三四五六七 ') out_put; --'一二三四五六七'
- 去除字符中的指定字符
select trim('a' from 'abcabcabca') out_put; --'bcabcabca'
- lpad()
- 用指定的字符实现左填充指定长度
select lpad('一二三四五',7,'*') out_put; --'**一二三四五'
select lpad('一二三四五',3,'*') out_put; --'一二三'
- rpad()
- 用指定的字符实现右填充指定长度
select lpad('一二三四五',7,'*') out_put; --'一二三四五**'
select lpad('一二三四五',3,'*') out_put; --'三四五'
- replace()
- 替换指定字符
select replace('一二三四五六七','三四','八九') out_put; --'一二八九五六七'
数学函数
- round()
- 四舍五入
select round(1.456) out_put; --'1'
select round(1.456,1) out_put; --'1.5'
- ceil()
- 向上取整
- floor()
- 向下取整
- truncate()
- 截断,保留小数点后位数
select truncate(1.6999,2) out_put; --'1.69'
- mod()
- 取余
日期函数
- now()
- 返回当前系统日期+时间
- curdate()
- 返回当前系统日期,不包含时间
- curtime()
- 返回当前系统时间,不包含日期
- 获取指定部分
- 年、月、日、时、分、秒
year(),month(),day(),hour(),minute(),second()
- 年、月、日、时、分、秒
- str_to_date
- 将日期格式的字符转换为指定格式的日期
select str_to_date('2021-4-16','%Y-%c-%d') out_put; --'2021-04-16'
格式符 | 功能 |
---|---|
%Y | 4位的年份 |
%y | 2位的年份 |
%m | 月份(01,02…11,12) |
%c | 月份(1,2…11,12) |
%d | 日(01,02…) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟(00,01…59) |
%s | 秒(00,01…59) |
- date_format()
- 将日期转换成字符
select date_format('2021-4-16','%Y年%m月%d日') out_put; --'2021年04月16日'
流程控制函数
- if()
- 语法类似三元运算符
select if(10>5,'大于','小于') out_put; --'大于'
- case()
- 语法
case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 ... else 要显示的值n或语句n end
其他函数
- version()
- 查看当前版本号
- database()
- 查看当前数据库
- user()
- 查看当前用户
分组函数
-
sum()
- 求和
-
avg()
- 求平均值
-
max()
- 求最大值
-
min()
- 求最小值
-
count()
- 求个数;一般使用count统计行数
-
特点
- sum、avg一般用于处理数值型;
max、min、count可以处理任何类型 - 以上分组函数都忽略null值
- 可以和distinct搭配实现去重的运算
案例:查询person_sex的种类个数
select count(distinct person_sex) from person
- 和分组函数一同查询的字段要求是group by后的字段
- sum、avg一般用于处理数值型;
插入
insert
- 语法一 (常用)
优点:insert into 表名 [(列名1...)] values(值1...);
- 支持插入多行
- 支持子查询
- 语法二
insert into 表名 set 列名1=值1, 列名2=值2...;
更新
update
- 修改单表的记录
update 表名 set 列名1=新值1, 列名2=新值2... where 筛选条件;
- 修改多表的记录
update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列名1= 新值1, ... where 筛选条件;
删除
delete
- 单表的删除 (常用)
delete from 表名 where 筛选条件;
- 多表的删除
delete 表1的别名, 表2的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件;
truncate
- 语法
truncate table 表名;
- 多使用于清空表,不支持事务回滚。
注意
- delete可以加where条件,truncate不能加
- truncate删除效率较高
- 假如要删除的表中有自增列,用delete删除后,再插入数据,自增长列的值从断点开始;而truncate删除后,再插入数据,自增长列的值从1开始。
库的管理
库的创建
- 语法
create database [if not exists] 库名;
库的修改
- 语法
rename database 库名 to 新库名;
- 更改库的字符集
alter database 库名 character set 编码格式;
库的删除
- 语法
drop database [if exists] 库名;
表的管理
表的创建
- 语法
create table [if not exists] 表名( 列名1 列的类型 [(长度) 约束], 列名2 列的类型 [(长度) 约束], 列名3 列的类型 [(长度) 约束], ... )
表的修改
- 语法
alter table 表名 add|drop|modify|change column 列名 [列类型 约束];
- 修改列名
alter table 表名 change column 旧列名 新列名 列的类型;
- 修改列的类型或约束
alter table 表名 modify column 列名 类型;
- 添加新列
alter table 表名 add column 新列名 类型;
- 删除列
alter table 表名 drop column 列名;
- 修改表名
alter table 旧表名 rename to 新表名;
表的删除
- 语法
drop table [if exists] 表名;
表的复制
- 仅仅复制表的结构
create table 新表 like 被复制的表;
- 复制表的结构和数据
create table 新表 select * from 被复制的表;
- 只负责部分数据
create table 新表 select 字段1,... from 被复制的表 where 筛选条件;
常见约束
- 六大约束
- not null:非空
- default:默认
- primary key:主键
- unique:唯一
- check:检查约束【mysql中不支持】
- foreign key:外键
存储引擎
事务 | 锁粒度 | 并发性 | 适用场景 | |
---|---|---|---|---|
InnoDB | 支持 | 行锁 | 相对较高 | |
MyISAM | 不支持 | 表锁 | 低 | ①读为主 ②数据一致性要求低 |
索引
分类
- 普通索引
- 唯一索引
- 单列索引
- 组合索引
- 全文索引
- 空间索引
- 前缀索引
数据结构
-
Hash
-
B Tree
- 底层为多叉树
- 每个节点中存储 key-value
- 不适于范围查找
-
B+ Tree
- 底层为多叉树
- 非叶子节点仅存储key,叶子节点中存储key-value
- 子节点采用双向链表结构,因此适用于范围查找
-
聚簇索引与非聚簇索引
- 仅是叶子节点中存储的数据不同,而进行区别
- 聚簇索引:叶子节点中存储的value为行数据。
- 非聚簇索引:叶子节点中存储的value为主键。因此,在使用到非聚簇索引的场景中,通常会先查询到主键,然后再通过主键二次查表,此操作也称之为 回表。