-- 大于: 查询年龄大于20
select * from tb_user where age > 20;
-- 大于等于: 查询年龄大于等于20
select * from tb_user where age >= 20;
-- 小于: 查询年龄小于20
select * from tb_user where age < 20;
-- 小于等于: 查询年龄小于等于20
select * from tb_user where age <= 20;
-- 等于: 查询年龄等于20
select * from tb_user where age = 20;
-- 不等于: 查询年龄不等于20
select * from tb_user where age != 20;
select * from tb_user where age <> 20;
重要：BETWEEN AND、IN、LIKE、IS NULL、AND、OR、NOT
-- 查询范围: 查询年龄在18到20间(包括最大值和最小值)
select * from tb_user where age between 18 and 20;
-- 多条件满足其一: 查询年龄为11、14、20
select * from tb_user where age in(11, 14, 20);
-- 模糊匹配(_匹配单个字符，%匹配多个字符)
-- 查询名字是两个字的
select * from tb_user where name like '__';
-- 查询名字后是雨结尾的
select * from tb_user where name like '%雨';
-- NULL: 地址为空
select * from tb_user where address is null;
-- AND:
select * from tb_user where age > 18 and age < 22;
select * from tb_user where age > 18 && age < 22;
-- OR:
select * from tb_user where age < 18 OR age > 22;
select * from tb_user where age < 18 || age > 22;
-- NOT:
select * from tb_user where address is not null;
4.3. 聚合函数
将一列数据作为一个整体，进行纵向计算
常见聚合函数
函数
功能
count
统计数量
max
最大值
min
最小值
avg
平均值
sum
求和
语法
SELECT 聚合函数(字段列表) FROM 表名;
注意：null值不参与所有聚合函数运算
4.4. 分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式
ASC ：升序（默认值）
DESC：降序
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
4.6. 分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录条数
分页查询时数据库的方言，不同的数据库有不同的实现
如果查询的时第一页数据，起始索引可以省略
5. DCL（数据控制语言）
Data Control Language 数据控制语言，用来创建数据库用户，控制数据库的访问权限
5.1. 管理用户
查询用户
USE mysql;
SELECT * FROM user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' INDENTIFIED WITH mysql_native_password BY '新密码';
删除用户
DROP USER '用户名'@'主机名';
主机名可以使用%通配
5.2. 权限管理
权限
说明
ALL, ALL PRIVILEGES
所有权限
SELECT
查询数据
INSERT
插入数据
UPDATE
修改数据
DELETE
删除数据
ALTER
修改表
DROP
删除数据库/表/视图
CREATE
创建数据库/表
查询权限
SHOW GRANT FOR '用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';
多个权限之间，使用,分隔
授权时，数据库名和表名可以使用*进行通配，代表所有。
6. MySQL内置函数
6.1. 字符串函数
函数
功能
concat(s1, s2, … sn)
字符串拼接，将s1，s2, …, sn拼接成一个字符串
lower(str)
将字符串str全部转为小写
upper(str)
将字符串str全部转为大写
lpad(str, n, pad)
左填充，用字符串pad对str的左边进行填充，达到n个字符长度
rpad(str, n, pad)
右填充，用字符串pad对str的右边进行填充，达到n个字符长度
trim(str)
去掉字符串头部和尾部的空格
substring(str, start, len)
返回从字符串str从start位置起的len个长度的字符串
6.2. 数值函数
函数
功能
ceil(x)
向上取整
floor(x)
向下取整
mod(x, y)
返回x%y的模
rand()
返回0~1内的随机数
round(x, y)
求参数x的四舍五入值，保留y位小数
6.3. 日期函数
函数
功能
curdate()
返回当前日期
curtime()
返回当前时间
now()
返回当前日期和时间
year(date)
获取指定date的年份
month(date)
获取指定date的月份
day(date)
获取指定date的日期
date_add(date, intreval expr type)
返回一个日期/时间值加上一个时间间隔exper后的时间值
datediff(date1, date2)
返回起始时间date1和结束时间date2之间的天数
6.4. 流程函数
函数
功能
if(value, t, f)
如果value为true，则返回t，否则返回f
ifnull(value1, value2)
如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] … else [default] end
如果val1为true，返回res1，… 否则返回default默认值
case [expr] when [val1] then [res1] … else [default] end
如果expr的值等于val1，返回res1，… 否则返回default默认值
select if(ture, 'ok', 'false');
--
select if(null, 'default');
--
select case when math >= 90 then '优秀' when math >= 60 then '良好' else '不及格' end as '等级';
--
select case math when 90 then '优秀' when 85 then '及格' else '不及格' end as '等级';
create table tb_student(
id smallint primary key auto_increment,
name varchar(5),
math smallint
);
insert into tb_student (name, math) values ('小明', 95), ('小红', 85), ('小王', 56);
select id, name, case when math >= 90 then '优秀' when math >= 60 then '良好' else '不及格' end as '等级' from tb_student;
select id, name, case math when 95 then '优秀' when 84 then '及格' else '不及格' end as '等级' from tb_student;
7. 约束
7.1. 概述
约束时作用于表中字段上的规则，用于限制存储在表中的数据
保证数据库中数据的正确、有效性和完整性
约束
描述
关键字
非空约束
限制该字段的数据不能为null
not null
唯一约束
保证该字段的所有数据都是唯一，不重复的
unique
主键约束
主键是一行数据的唯一标识，要求非空且唯一
primary key
默认约束
保存数据时，如果未指定该字段的值，则采用默认值
default
检查约束
保证字段值满足某一个条件
check
外键约束
用来让两张表的数据之间建立连接，保证数据的一致性和完整性
foreign key
约束时作用在表中字段上的，可以在创建表/修改表的时候添加约束
7.2. 演示
create table tb_person(
id int primary key auto_increment comment '主键',
name char(8) not null unique comment '姓名',
age smallint check ( age > 0 and age < 120 ) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
) comment '员工信息表';
alter table 表名 add constraint 外键名名 foreign key (外键字段) references 主表名(主表字段名) on update on cascade on delete cascade;
alter table tb_staff
add constraint fk_staff_position_position_id foreign key (position) references tb_position(id)
on update cascade on delete cascade ;
alter table tb_staff
add constraint fk_staff_position_position_id foreign key (position) references tb_position(id)
on update set null on delete set null ;
-- 隐式内连接
select 字段列表 from 表1, 表2 where 条件 ...;
-- 显示内连接
select 字段列表 from 表1 inner join 表2 on 连接条件 ...;
8.3. 外连接
-- 左外连接
-- 相当于查询表1的所有数据 包含表1和表2交集部分
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
-- 右外连接
-- 相当于查询表2的所有数据 包含表1和表2交集部分
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
-- 等价于
select 字段列表 from 表2 left [outer] join 表1 on 条件...;
8.4. 自连接
自连接查询，可以是内连接查询，也可以是外连接查询
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;
8.5. 联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
8.6. 子查询
标量子查询
子查询返回的结构是单个值（数字、字符串、日期等），最简单的形式
select id from tb_position where position = '研发部';
列子查询
操作符
描述
in
在指定的集合范围之内，多选一
not in
不在指定的集合范围之内
any
子查询返回列表中，有任意一个满足即可
some
与any等同
all
子查询返回列表的所有值都必须满足
子查询返回的结果是一列（可以是多行）
select * from tb_staff
where salary >
all (select salary from tb_staff where position =
(select id from tb_position where position = '研发部'));
-- 查询研发部的id值
-- 根据查出后的id值查询道研发部所有人的薪水
-- 查询比研发部所有人薪水都高的人的信息
行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
select * from emp where (salary, manageried) = (select salary, manageried from emp where name = '张三');
表子查询
子查询返回的结果是多行多列
-- 多行多列 in 满足其中一个条件
select * from emp where
(job,salary) in (select (job,salary) from emp where name = '张三' or name = '王五');
-- 根据查表后的新表再查询
select e.*, d.* from
(select * from emp where entrydata > '2000-01-09') e
left join dept d on e.dept_id = d.id;
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
