1、SQL 语法
1、库
-- 进入数据库
mysql -u root -proot
-- 查看存储位置
SHOW VARIABLES LIKE 'datadir';
-- 创建数据库
create database [if not exists] 数据库名;
-- 查询数据库
show databases;
-- 删除数据库
drop database [if exists] 数据库名;
-- 使用数据库
use 数据库名;
select database();
-- 查看表名大小写区分(0: 区分, 1: 不区分)
show variables like 'lower_case_table_names';
-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的新密码';
2、表
-- 创建数据表
create table 表名(
字段名1 字段类型1(字段长度),
字段名2 字段类型2(字段长度),
....
字段名n 字段类型n(字段长度)
);
-- 查询数据表
show tables;
desc 数据表名;
show create table 数据表名;
-- 修改数据表, 放在指定位置 first, after...
alter table 表名 add 字段名 列类型;
alter table 表名 modify 列名 列类型;
alter table 表名 change 旧列名 新列名 列类型;
alter table 表名 drop 列名;
rename table 旧表名 to 新表名;
-- 删除数据表
drop table 表名;
-- 先把整张表删除, 再创建相同结构的新表
truncate 表名;
-- 其它
create table t_student2 AS select * from t_student1; -- t_student2 和 t_student1 的表结构和数据都一样
create table t_student3 AS select * from t_student1 where 1 = 2; -- t_student3 和 t_student1 的表结构一样
create table t_student4 AS select sno, sname, age from t_student where sno = 2; -- t_student4 有 t_student1 的部分表结构和数据
2.1、列级约束
/*
约束:
建立一张用来存储学生信息的表
字段包含学号、姓名、性别、年龄、入学日期、班级、email 等信息
【1】学号是主键 = 不能为空 + 唯一, 主键的作用: 可以通过主键查到唯一的一条记录
【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email 唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在 18-50 之间
*/
-- 创建数据库表:
create table t_student(
sno int(6) primary key auto_increment, -- 非空、唯一、自增
sname varchar(5) not null, -- 非空
sex char(1) default '男' check(sex = '男' or sex = '女'), -- 默认值、检查
age int(3) check(age >= 18 and age <= 50), -- 检查
enterdate date,
classname varchar(10),
email varchar(15) unique -- 唯一
);
-- 主键没有设定值、null、default: 都可以完成主键自增
-- 如果 sql 语句执行报错, 主键（非空 + 唯一）就浪费了, 主键不连续了
2.2、表级约束
-- 自增、非空、默认: 只能为列级约束
-- 创建数据库表:
create table t_student(
sno int(6) auto_increment, -- 自增
sname varchar(5) not null, -- 非空
sex char(1) default '男', -- 默认值
age int(3),
enterdate date,
classname varchar(10),
email varchar(15),
constraint pk_stu primary key (sno), -- pk_stu 主键约束的名字
constraint ck_stu_sex check(sex = '男' or sex = '女'),
constraint ck_stu_age check(age >= 18 and age <= 50),
constraint eq_stu_email unique (email)
);
2.3、在创建表以后添加约束
drop table t_student;
-- 创建数据库表:
create table t_student(
sno int(6),
sname varchar(5) not null,
sex char(1) default '男',
age int(3),
enterdate date,
classname varchar(10),
email varchar(15)
);
-- 把 auto_increment 去掉
-- 在创建表以后添加约束
alter table t_student add constraint pk_stu_sno primary key (sno); -- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 自增约束(通过 modify)
alter table t_student add constraint ck_stu_sex check(sex = '男' or sex = '女');
alter table t_student add constraint ck_stu_age check(age >= 18 and age <= 50);
alter table t_student add constraint eq_stu_email unique (email);
3、增删改
-- 插入数据 [on duplicate key update]
insert into 表名 values (值1, 值2, ...);
insert into 表名 (列名1, 列名2, ...) values (值1, 值2, ...);
insert into 表名 (列名1, 列名2, ...) values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ...;
-- 修改数据
update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where 条件];
-- 删除数据
delete from 表名 [where 条件] [order by 列名] [limit 行数];
truncate 表名; -- 先把整张表删除, 再创建相同结构的新表
insert into imc_class(class_name)
values ('MYSQL'), ('Redis'), ('MongoDB'), ('Oracle');
create unique index uqx_classname on imc_class(class_name);
insert into imc_class(class_name)
values ('MYSQL'), ('Redis'), ('MongoDB'), ('Oracle')
on duplicate key update
add_time = current_time;
-- 册除课程方向表中重复的课程方向
-- 保留方向 ID 最小的一条, 并在方向名称上增加唯一索引
delete a
from imc_type a
join (
select type_name, min (type_id) as min_type_id, count(*)
from imc_type
group by type_name having count(*) > 1
) b
on a.type_name = b.type_name and a.type_id > min_type_id;
create unique index uqx_typename on imc_type(type_name);
-- 利用课程评价表中的评分, 更新课程表中课程的评分
update imc_course a
join (
select course_id,
avg(content_score) as avg_cotent,
avg(level_score) as avg_level,
avg(logic_score) as avg_logic,
avg(score) as avg_score
from imc_classvalue
group by course_id
) b on a.course_id = b.course_id
set a.content_score = b.avg_cotent,
a.level_score = b.avg_level,
a.logic_score = b.avg_logic,
a.score = b.avg_score;
4、单表查询
4.1、简单查询
-- 查询所有列
select * from 表名;
-- 查询部分列
select 列名1, 列名2, ... from 表名;
-- 去重
select distinct 列名 from 表名;
-- 查询之后进行简单运算
select 列名(直接进行四则运算) from 表名;
-- 空值处理
-- null 跟任何数字做运算, 结果都是 null
-- ifnull(列, 默认值) 当指定的列的值为 null 的时候, 使用后面的默认值进行替换
select name, math + chinese + english from student;
select name, math + chinese + ifnull(english, 0) from student;
-- 别名
select 列名 [as] 别名 from 表名; -- as 可以省略
-- 集合
union -- 并集, 去重, 效率低
union all -- 并集, 不去重, 效率高
-- 同一张表可通过 UNION 添加新的分类字段：先通过分类查询并添加新的分类字段，再 UNION 合并为带分类字段的新表
SELECT
order_id,
order_date,
'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT
order_id,
order_date,
'Archived' AS status -- Archived 归档
FROM orders
WHERE order_date < '2019-01-01';
-- 不同表通过 UNION 合并的情况：将一张 18 年的订单表和 19 年的订单表纵向合并起来在一张表里展示
SELECT first_name AS name_of_all -- 新列名由排在 UNION 前面的决定
FROM customers
UNION
SELECT name
FROM products
4.2、条件查询
-- 默认不区分大小写, 加 binary 来区分大小写
select * from emp where binary job = 'clerk';
-- 关系运算符
-- >, >=, <, <=, =, != 或 <>
-- 逻辑运算符
-- and, or, not
-- xor(查询 "左边为真右边为假" 和 "左边为假右边为真")
-- 范围关键字
-- in (1, 2, 3) 在某个集合中
-- not in (1, 3, 5) 不在某个集合中
-- between A and B in [A, B]
-- null 关键字
-- null 不能使用关系运算符参与比较
-- is null 表示判断某列的值为空( = null)
-- is not null 表示判断某列的值不为空 (!= null)
-- 模糊匹配 like, not like
-- % 表示模糊, 代表 0 个或者多个字符
-- _ 表示模糊, 代表的是 1 个字符
select * from 表名 where 列名 like '%值%';
4.3、高级查询
-- 排序, null 值在参与排序的时候, 会正序排在上面
select * from 表名 order by 列名1 [asc 正序(默认) | desc 倒序], 列名2 [asc 正序(默认) | desc 倒序]
-- 结果截取
select * from 表名 limit 开始索引(从 0 开始计数), 截取多少条;
select * from emp limit (页码数 - 1) * 页大小, 页大小;
-- 聚合函数
-- count 计数
-- sum 总数
-- avg 平均数
-- max 最大值
-- min 最小者
select 集合函数(列名) from 表名;
-- 分组: 把结果集按某些列分成不同的组, 并对分组后的数据进行聚合操作
-- 分组一般是跟聚合函数一起出现
-- 字段和聚合函数不可同时使用, 除非这个字段属于分组, 先根据字段分组, 后查询每组的聚合函数
-- where 条件是一定要在 group by 之前出现的
select 列名1, 聚合函数(列名2) from 表名 group by 列名1;
-- 过滤
-- 过滤一般是跟在分组之后
select 列名1, 聚合函数(列名2) from 表名 group by 列名1 having 过滤条件;
-- 高级查询的顺序
select 聚合函数() from 表 [where 条件] [group by 分组] [having 过滤] [order by 排序] [limit 截取];
5、多表查询
-- 内连接
select * from 左表 [inner] join 右表 on 连接条件;
-- 左外连接
select * from 左表 left [outer] join 右表 on 连接条件;
-- 补集, 左表存在, 右表不存在
select * from 左表 left [outer] join 右表 on 连接条件 where 右边.key is null;
-- 连接条件一般是, 从表的外键 = 主表的主键
-- 自连接（员工的上级也是员工）
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
-- 自连接（老板没有上级，即 reports_to 字段为空，如果用 JOIN 会被筛掉，用 LEFT JOIN 才能保留）
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m -- 包含所有雇员（包括没有 report_to 的老板本人）
ON e.reports_to = m.employee_id
-- using 简化连接条件
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_Id AND oi.product_id = oin.product_id
/ USING (order_id, product_id)
-- 交叉连接
-- 左表中得数据依次去匹配右表中的数据, 然后将所有的匹配结果呈现出来
-- 查询员工表和部门表中所有信息
select * from 左边, 右表;
select * from emp, dept;
-- 内连接
-- 左表中的数据依次去匹配右表中的数据, 只显示同时满足两张表的数据
select * from 左表, 右表 on 连接条件 where 条件;
-- 隐式内连接
select * from emp e, dept d where e.dept_id = d.id;
-- 显示内连接(推荐)
select * from emp e inner join dept d where e.dept_id = d.id;
-- 练习, 查询员工猪八戒的性别、工资、所在部门名称
select e.gender, e.salary, d.name
from emp e
inner join dept d on e.dept_id = d.id
where e.name = '猪八戒';
-- 左外连接
-- 左表中得数据依次去匹配右表中的数据, 右表能匹配上的直接显示匹配结果, 右表匹配不上的, 使用 null 补齐
-- 左边显示全部, 右边显示的是匹配中的或者 null
select * from 左表 left outer join 右表 on 连接条件;
select *
from emp e
left outer join dept d
on e.dept_id = d.id;
6、子查询
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
SELECT *
FROM products
WHERE product_id NOT IN (
SELECT DISTINCT product_id
FROM order_items
)
SELECT *
FROM invoices
WHERE invoice_total > ALL (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
SELECT *
FROM invoices
WHERE invoice_total > ANY (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
-- 查询工资最高的员工是谁
select * from emp
where salary = (select max(salary) from emp);
-- 查询工资小于平均工资的员工有哪些
select * from emp
where salary < (select avg(salary) from emp);
-- 查询工资大于 5000 的员工所在部门的名字
select * from dept
where id in (select dept_id from emp where salary > 5000);
-- 查询 2011 年以后入职的员工信息, 包括部门信息
select * from
(select * from emp where join_date >= '2011-01-01') as a
left outer join dept as d
on a.dept_id = d.id;
7、SQL 执行顺序
from join -> where -> group by -> having -> select -> distinct -> order by -> limit
select [distinct] 列 聚合函数() from 左表 left [outer] join 右表 on 连接条件 [where 查询条件] [group by 分组] [having 过滤] [order by 排序] [limit 截取];
8、事务
8.1、事务特性
* 原子性: atomicity 一个事务操作是不可分割的整体, 一个事务中的操作一定是要么全部成功, 要么全部失败
* 一致性: consistency 一个事务执行前后, 数据库的状态是一致的
* 隔离性: isolation 当多个事务同时执行的时候, 互相是不会产生影响的
* 持久性: durability 当事务执行完毕之后, 其对数据库产生的影响一定会持久的保存在磁盘上
8.2、事务原理
-- 当事务开启后, 一部分 sql 执行成功, 添加一个回滚点, 后续操作报错了, 直接到回滚点, 保证之前的操作可以成功提交
-- 开启事务
begin;
-- 提交事务
commit;
-- 回滚事务
rollback;
-- 设置保存点
savepoint 保存点;
-- 回滚保存点
rollback to 保存点;
-- MySQL 默认是自动提交事务, 设置为手动提交
show variables like 'autocommit';
set @@autocommit=on; -- 设置为自动提交
set @@autocommit=off; -- 设置为手动提交
8.3、事务的隔离性
在并发事务没有进行隔离的情况下，会发生如下问题
- 脏读：一个事务读取到了另外一个事务没有提交的数据（没提交）
- 不可重复读：一个事务读取到了另外一个事务修改的数据（修改）
- 幻读：一个事务读取到了另外一个事务新增的数据（新增）
| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
|---|---|---|---|---|---|---|
| 1 | 读未提交 | read uncommitted | ✓ | ✓ | ✓ | |
| 2 | 读已提交 | read committed | × | ✓ | ✓ | Oracle 和 SQL Server |
| 3 | 可重复读 | repeatable read | × | × | ✓ | MySQL |
| 4 | 序列化 | serializable | × | × | × |
9、数据类型
| 大分类 | 类型 | 描述 |
|---|---|---|
| 数字类型 | int | 整型 |
| float | 单精度浮点数类型 | |
| double | 双精度浮点数类型 | |
| decimal | 浮点数类型，decimal 类型的字段没有精度丢失的情况，和钱有关的数据 | |
| 字符类型 | varchar | 可变长度字符串类型，它的长度可以由我们自己指定，最大值是 65535，使用效率低，省磁盘存储空间 |
| char | 固定长度字符串类型，如果没有指定长度，默认长度是 255，查询效率高，浪费磁盘存储空间 | |
| 日期类型 | date | 日期，格式：yyyy-MM-dd |
| datetime | 日期时间，格式：yyyy-MM-dd HH:mm:ss，占用 8 字节的存储空间 | |
| timestamp | 日期时间，格式：yyyy-MM-dd HH:mm:ss，取值范围 1970-2038，占用 4 字节存储，性能要优于datetime |
9.1、整数
9.2、浮点数
9.3、时间
9.4、字符串
10、主键和外键
- 主键就是能唯一标识表中每条记录的字段（唯一 + 非空）
- 外键约束的特点
- 对于主表来说，不能删除被从表引用的数据
- 对于从表来说，不能添加主表中不存在的数据
- 添加数据的时候，要先添加主表数据
- 删除数据的时候，要先删除从表数据
-- 添加外键约束
create table 表名(
列名 列类型,
[constraint] [约束名(外键列_fk)] foreign key (列名) references 主表(主键)
);
alter table 表名 add [constraint] [约束名] foreign key (列名) references 主表(主键);
-- 删除外键约束
alter table 表名 drop foreign key 约束名;
10.1、一对多
-- class
create table class(
id int primary key,
name varchar(20)
);
-- student
create table student(
id int primary key,
name varchar(20),
class_id int
);
-- 添加外键约束
alter table student add constraint class_id_fk foreign key (class_id) references class(id);
10.2、多对多
-- student
create table student(
id int primary key,
name varchar(20)
);
-- cource
create table cource(
id int primary key,
name varchar(20)
);
-- sc
create table sc(
student_id int,
cource_id int,
constraint student_id_fk foreign key (student_id) references student(id), -- 外键约束, student
constraint cource_id_fk foreign key (cource_id) references cource(id) -- 外键约束, cource
);
-- 插入数据
insert into student values(1, '旭哥'), (2, '苗哥');
insert into cource values(1, 'JavaEE'), (2, 'UI'), (3, '挖掘机');
insert into sc values(1, 1), (1, 2), (1, 3), (2, 1);
10.3、一对一
-- husband
create table husband(
id int primary key,
name varchar(20)
);
-- wife
create table wife(
id int primary key,
name varchar(20),
husband_id int unique, -- 唯一
foreign key (husband_id) references husband(id) -- 外键, 默认生成的约束名
);
10.4、外键策略
-- 先创建父表: 班级表
create table t_class(
cno int(4) primary key auto_increment,
cname varchar(10) not null,
room char(4)
);
insert into t_class values
(null, 'java001', 'r803'),
(null, 'java002', 'r416'),
(null, '大数据001', 'r103');
-- 创建子表, 学生表
create table t_student(
sno int(6) primary key auto_increment,
sname varchar(5) not null,
classno int(4),
constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
insert into t_student values
(null, '张三', 1),
(null, '李四', 1),
(null, '王五', 2),
(null, '朱六', 3);
-- 删除班级 2: 直接删除不行, 因为有外键约束, 需要外键策略
-- 策略一: no action 不允许操作
-- 先把班级为 2 的学生的班级改了, 再删除班级 2
update t_student set classno = null where classno = 2;
delete from t_class where cno = 2;
-- 策略二: cascade 级联操作（操作主表的时候影响从表的外键信息）
-- 先删除之前的外键约束, 重新添加外键约束
alter table t_student drop foreign key fk_stu_classno;
alter table t_student
add constraint fk_stu_classno
foreign key (classno) references t_class (cno)
on update cascade
on delete cascade; -- 注意
update t_class set cno = 5 where cno = 3;
delete from t_class where cno = 5;
-- 策略三: set null 置空操作
-- 先删除之前的外键约束, 重新添加外键约束
alter table t_student drop foreign key fk_stu_classno;
alter table t_student
add constraint fk_stu_classno
foreign key (classno) references t_class (cno)
on update set null
on delete set null;
update t_class set cno = 8 where cno = 1;
-- 级联操作和置空操作可以混着使用
alter table t_student
add constraint fk_stu_classno
foreign key (classno) references t_class (cno)
on update cascade
on delete set null;
-- 应用场合
-- 1、朋友圈点赞留言都删除：级联操作
-- 2、解散班级, 对应的学生不删除, 只是班级设置为 null：置空操作
11、三大范式
- 第一范式：列原子性（地址表：数据库表每一列都是不可分割基本数据项，同一列中不能有多个值）
- 第二范式：消除部分依赖，数据和联合主键完全相关性（选课表：确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关）
如果表的业务主键只有一个列，那它天生就支持第二范式
如果表的业务主键有多个列，那就要区别对待 - 第三范式：消除间接依赖，数据和主键直接相关性（班级表：确保数据表中的每一列数据都和主键直接相关，而不能间接相关，属性不依赖于其他非主属性）
列原子性：数据库表每一列都是不可分割基本数据项，同一列中不能有多个值
确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关
确保数据表中的每一列数据都和主键直接相关，而不能间接相关，属性不依赖于其他非主属性
12、索引
explain select class, score, id from test where class = 3; -- 执行计划
12.1、介绍
索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度
- 普通索引：仅加速查询
- 唯一索引：加速查询 + 列值唯一（可以有 null），唯一约束
- 主键索引：加速查询 + 列值唯一（不可以有 null）+ 表中只有一个，主键约束
- 组合（联合）索引：使用多个列联合起来作为一个索引
索引的优缺点
- 优点：可以大幅度提高查询的速度
- 缺点：对于增删改来说，需要对索引进行维护，降低效率；会占用一定磁盘空间
索引的创建原则
- 将经常需要查询的字段设置为索引，便于查询
- 将经常需要连接的字段（主键、外键）设置为索引，便于连接
- 将经常需要排序的字段设置为索引，便于使用索引排序
常见索引失效情况
- 使用 like 关键字时，模糊匹配使用 % 开头
- 使用 or 连接条件时，如果条件中存在没有索引的
- 任何操作（计算、函数、自动 / 手动类型转换）
- 使用 !=、not in、is null、is not null 时
12.2、命令
-- 直接创建索引
create table student (
id int,
username varchar(30),
telephone varchar(30),
sex char(2)
);
-- 为 username 创建普通索引
create index username_idx on student(username);
-- 为 telephone 创建唯一索引
create unique index telephone_idx on student(telephone);
-- 为 username telephone sex 设置唯一联合索引
create unique index uts_idx on student(username, telephone, sex);
-- 修改表时指定索引
-- 为 id 设置主键索引
alter table student add primary key (id);
-- 为 telephone 设置唯一索引
alter table student add unique (id);
-- 为 username 设置普通索引
alter table student add index (username);
-- 创建表时指定索引【掌握】，使用此种方式建立的索引名字的规则：主键 PRIMARY，其它使用列名
create table student(
id int,
username varchar(30),
telephone varchar(30),
sex char(2)
primary key (id), -- 为 id 设置主键索引
unique (telephone), -- 为 telephone 设置唯一索引
index (username) -- 为 username 设置普通索引
);
-- 删除索引
-- 1、直接删除：drop index 索引名 on 表名;
-- 2、修改表时删除：alter table 表名 drop index 索引名;
-- 查看索引
SHOW INDEX FROM 表名;
12.3、前缀索引
当索引的列是字符串时（包括 CHAR、VARCHAR、TEXT、BLOG），尤其是当字符串较长时
我们通常不会使用整个字符串而是只是用字符串的前面几个字符来建立索引，这被称作 Prefix Indexes 前缀索引
这样可以减少索引的大小使其更容易在内存中操作，毕竟在内存中操作数据比在硬盘中快很多
-- 为 customers 表的 last_name 建立索引并且只使用其前 20 个字符
-- 这个字符数的设定对于 CHAR 和 VARCHAR 是可选的，但对于 TEXT 和 BLOG 是必须的
CREATE INDEX idx_lastname ON customers (last_name(20));
最佳字符数如何确定：可以利用 COUNT、DISTINCT、LEFT 关键词和函数
测试不同数目的前缀字符得到的独特值个数，目标是用尽可能少的前缀字符得到尽可能多的独特值个数
-- 结果是 '25'、'966'、'996'
SELECT
COUNT(DISTINCT LEFT(last_name, 1)),
COUNT(DISTINCT LEFT(last_name, 5)),
COUNT(DISTINCT LEFT(last_name, 10))
FROM customers
从前 1 个到前 5 个字符，效果提升是很显著的
从前 5 个到前 10 个字符，所用的字符数增加了一倍，但识别效果只增加了一点点
5 个字符已经能识别出 966 个独特值，与 1010 的记录总数相去不远了，所以可以认为用前 5 个字符来创建前缀索引是最优的
13、函数
13.1、字符串
1. concat(s1, s2 ... sn)
描述: 把字符串 s1, s2 等多个字符串合并为一个字符串
实例: select concat("传", "智", "播", "客");
2. char_length(str)
描述: 返回字符串 str 的字符数
实例: select char_length("传智播客");
3. length(str)
描述: 返回字符串 s 的字节数
实例: select length("传智播客");
4. upper(s)
描述: 将字符串转换为大写
实例: select upper("itcast");
5. lower(s)
描述: 将字符串转换为小写
实例: select lower("ITCAST");
6. locate(s1, s)
描述: 从字符串 s 中获取 s1 的开始位置, 位置从 1 开始
实例: select locate('he', 'itheima');
7. trim(str) | ltrim(str) | rtrim(str)
描述: 字符串去空格
实例: select trim(" 传智 人 ");
8. replace(s, s1, s2)
描述: 字符串 s, 用 s2 替代 s1
实例: select replace('abc', 'a', '*');
9. substr(s, start, length) | substring(s, start, length)
描述: 从字符串 s 的 start(从 1 开始计数) 位置截取长度为 length 的子字符串
实例: select substr("itcast", 2, 3);
10. strcmp(str1, str2)
描述: 比较字符串大小, 左大于右时返回 1, 左等于右时返回 0, 左小于于右时返回 -1
实例: select strcmp("a", "b");
11. concat_ws(sep, s1, s2 ... sn)
描述: 用指定的分隔符 sep 把字符串 s1, s2 等多个字符串合并为一个字符串
实例: select concat_ws("-", "传", "智", "播", "客");
12. format(x, d[,locale])
描述: 将数字 N 格式化为格式, 如 "#,###,###.##", 并舍位到 D 位小数
实例: select format(123456.789, 4); -- 123,456.7890
13. left(str, len) | right(str, len)
描述: 从字符串的左 / 右边起, 返回 len 长度的子字符串
实例: select left("itcast", 2), right("itcast", 2);
14. substring_index(str, delim, count)
描述: 返回字符串 str 按 delim 分割的前 count 个子字符串
实例: select substr("192.168.0.100", ".", -2); -- 0.100
15. trim([remstr from] str)
描述: 从字符串 str 两端删除不需要的字符 remstr
实例: select trim("x" from "xxxximoocxxxx");
16. md5(str)
描述: 返回 str 的 md5 值
13.2、日期函数
1. now() | curdate() | curtime()
描述: 获取系统当前日期时间、日期、时间
实例: select now();
2. year(DATE) | month(DATE) | day(DATE)
描述: 从日期中选择出年、月、日
实例: select year(now());
3. last_day(DATE)
描述: 返回月份的最后一天
实例: select last_day(now());
4. adddate(DATE, n) | subdate(DATE, n)
描述: 计算起始日期 DATE 加(减) n 天的日期
实例: select adddate(now(), 10);
5. quarter(DATE)
描述: 返回日期 DATE 是第几季节(季度), 返回 1 到 4
实例: select quarter(now());
6. datediff(d1, d2)
描述: 计算日期 d1->d2 之间相隔的天数
实例: select datediff('2019-08-01', '2019-07-01');
7. date_format(d, f)
描述: 按表达式 f 的要求显示日期 d
实例: select date_format(now(), '%Y-%m-%d');
8. sec_to_time(seconds)
描述: 把秒数转换为(小时:分:秒)
实例: select sec_to_time(60);
9. time_to_sec(time)
描述: 把(小时:分:秒)转换为秒数
实例: select time_to_sec('1:00:00');
10. date_add(DATE, INTERVAL expr unit)
描述: 对给定的日期 DATE 加(减) 指定的时间单元
时间单元: year 年、day 天、hour 小时、minutes 分钟、second 秒
实例: select date_add(now(), INTERVAL 1 DAY);
11. extract(unit from date)
描述: 返回日期 date 的指定部分
实例: select extract(year from now());
12. unix_timestamp()
描述: 返回 unix 时间戳
实例:
13. from_unixtime()
描述: 把 unix 时间戳转换为日期函数
实例:
14. timestampdiff(unit, d1, d2)
描述: 返回 d1 与 d2 的时间差
实例: timestampdiff(hour, concat(m.date, " ", m.start), concat(m.date, " ", m.end)) as hour
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值 |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（01-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
| %H | 小时（00-23） |
| %h | 小时（01-12） |
| %I | 小时（01-12） |
| %i | 分钟，数值（00-59） |
| %j | 年的天（001-366） |
| %k | 小时（0-23） |
| %l | 小时（1-12） |
| %M | 月名 |
| %m | 月，数值（01-12） |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒（00-59） |
| %s | 秒（00-59） |
| %T | 时间，24-小时 (hh:mm:ss） |
| %U | 周（00-53）星期日是一周的第一天 |
| %u | 周（00-5） 星期一是一周的第一天 |
| %V | 周（01-53）星期日是一周的第一天，与 %X 使用 |
| %v | 周（01-53）星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天 （0=星期日，6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
13.3、数字函数
1. abs(x)
描述: 返回 x 的绝对值
实例: select abs(-1);
2. ceil(x) | floor(x)
描述: 向上(下)取整
实例: select ceil(1.5);
3. mod(x, y)
描述: 返回 x mod y 的结果, 取余
实例: select mod(5, 4);
4. rand()
描述: 返回 0 到 1 的随机数
实例: select rand();
6. round(x)
描述: 四舍五入
实例: select round(1.23456);
7. truncate(x, y)
描述: 返回数值 x 保留到小数点后 y 位的值
实例: select truncate(1.23456, 3);
8. round(x, d)
描述: 对数字 x 进行四舍五入保留 d 位小数
实例: select round(1.23456, 3);
13.4、判断函数
select if(布尔表达式, 为真时的值, 为假时的值) from 表名;
select ename, salary + ifnull(bonus, 0), if(salary + ifnull(bonus, 0) > 20000, "家有娇妻", "单身狗") from emp;
select
case [字段]
when 判断条件 1 then 希望的到的值 1
when 判断条件 2 then 希望的到的值 2
when 判断条件 n then 希望的到的值 n
else 前面条件都没有满足情况下得到的值
end
from 表名;
select ename, job_id,
case job_id
when 1 then '糊口度日'
when 2 then '小康生活'
when 3 then '人生巅峰'
else '土豪'
end
as 'status'
from emp;
本文来自博客园，作者：lidongdongdong~，转载请注明原文链接：https://www.cnblogs.com/lidong422339/p/17708805.html
