MySQL基本语法与使用

2022-02-27

1. MySQL的数据类型

1.1 整型

类型	字节大小	无符号范围	有符号范围
TINYINT	1	0 ~ 255(2的8次方减1)	-128 ~ 127(2的4次方减1)
SMALLINT	2	0 ~ 65536(2的16次方减1)	-32768 ~ 32767(2的8次方减1)
MEDIUMINT	3	0 ~ 2的24次方减1	-(2的12次方) ~ (2的12次方减1)
INT/INTEGER	4	0 ~ 2的32次方减1	-(2的16次方) ~ (2的16次方减1)
BIGINT	8	0 ~ (2的64次方减1)	-(2的32次方) ~ (2的32次方减1)

1.2 浮点型

类型	字节大小	取值范围
FLOAT	4
DOUBLE	8
DECIMAL(M,D)	如果M>D，为M+2，否则为D+2

1.3 字符串

类型	字节大小	示例
CHAR	0-255	name char(5)
VARCHAR	0-65535	name varcher(5)
TEXT	0-65535	大文本

1.4 日期类型

类型	字节大小	示例
DATE	4	‘2020-11-01’
TIME	3	‘23:29:59’
DATETIME	8	‘2020-11-01 23:29:59’
YEAR	1	‘2020’
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UCT

1.5 其他类型

更全面的数据类型情参考MySQL 8.0参考手册。

2. 查询语句

使用select语句执行数据库检索，基本语法为select 列名 from 表名;

2.1 检索单列/多列

-- 检索单列
select eno from employee;
-- 检索多列
select eno, ename from employee;
-- 检索所有列
select * from employee;
-- 检索前3行数据
select * from employee limit 3;
-- 去重检索
select distinct job from employee;

2.2 限定检索条件

基本结构为select 列名 from 表名 where 条件;

条件类型包括：

数值判断
- 大于：>
- 小于：<
- 等于：=
- 不等于：<>
- 大于等于：>=
- 小于等于：<=
- 范围：between and
逻辑判断
- 与集合：and
- 或集合：or
- 非集合：not
- 范围集合：in
模糊判断
- 模糊判断：like

select * from
employee
where deptno in (20,30)
and hiredate between "2013-01-01" and "2015-12-31"
and sal > 5000;

2.3 检索值计算

检索字段可进行加减乘除计算

新字段可以as关键字进行重命名

select 
字段1 + 字段2 as '合计'
字段1 - 字段2 as '相差'
字段1 * 字段2 as '乘积'
字段1 / 字段2 as '求商'
from 表名
where 条件;

拼接字段使用concat函数

1
2
3

select concat(字段1 , 字段2) as '新字段名'
from 表名
where 条件;

2.4 表连接检索

一张表中有一部分信息，另一张表有其他相关信息，当我们想要取出全部信息时，就需要把两张表做关联。

2.4.1 纵向连接

union用于把来自许多select查询语句的结果组合到一个结果集中，也叫联合查询。

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

应用举例：

-- union
select * from employee where sal > 10000
union
select * from employee_new where deptno = 30;

-- 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
-- union all
select * from employee where sal > 10000
union all
select * from employee_new where deptno = 30;

2.4.2 横向连接

2.4.2.1 内连接

join和inner join一样，表1 join 表2返回表1和表2共同的行

应用举例：

-- inner可加可不加
select * from employee join department 
on employee.deptno = department.deptno
where department.deptno is not null;

-- 以上写法等同于引用表写法
select * 
from employee, 
		 department 
where employee.deptno = department.deptno
and department.deptno is not null;

2.4.2.2 左连接

表1 left join 表2，以表1为基础，匹配表2的相关数据

2.4.2.3 右连接

表1 right join 表2，以表2为基础，匹配表1的相关数据

2.4.2.4 全连接

表1 right full join 表2，只要其中一个表中存在匹配，就返回行

2.5 子查询检索

当一个查询是另一个查询的一部分时，我们把内层的查询称为子查询(内查询)，外层查询称为父查询(主查询)。

通过子查询可以实现多表查询，查询语句可能包括in、any、all和exists等关键字，也可能包含比较运算符。

2.5.1 子查询分类

2.5.2 where子查询

语法结构：

1	select 列名 from 表名 where 条件如 col > (select 列名 from 表名);

应用举例：

-- 查询工资比王亮高的人员信息
select * from employee 
where sal > (select sal from employee where ename = "王亮");

-- in关键字
select * from employee 
where deptno in (select deptno from department);

-- any关键字
select ename, sal
from employee
where sal > any(select sal from employee where job = "经理");

-- all关键字
select ename, sal
from employee
where sal > all(select sal from employee where job = "经理");

2.5.3 from子查询

语法结构：

1	select t1.* from (select 字段 from 表名) t1 where 条件;

应用举例：

-- 查询各部门部门号、部门名称、部门地址、员工人数、平均工资信息
-- 方式一：子查询方式
select d.deptno, d.dname, d.location, e.num, e.avgsal
from (select deptno,count(*) num, avg(sal) avgsal 
      from employee 
      group by deptno) e
			inner join department d
			on e.deptno = d.deptno;

-- 方式二：表连接方式
select d.deptno, 
			 d.dname, 
			 d.location, 
			 count(e.eno) number, 
			 avg(e.sal) average
from employee e 
		 inner join department d
		 on e.deptno = d.deptno
group by d.deptno;

-- 方式三：引用两个表
select d.deptno, 
			 d.dname, 
			 d.location, 
			 count(e.eno) number, 
			 avg(e.sal) average
from employee e,
		 department d
where e.deptno = d.deptno
group by d.deptno;

3 常用函数

3.1 数值型函数

函数	描述
sum(列名)	返回某列的总和
avg(列名)	返回某列的平均值
min(列名)	返回某列的最小值
max(列名)	返回某列的最大值
count(列名)	返回某列的行数(不包括null)
count(*)	返回被选中的行数
count(distinct 列名)	返回相异结果的行数

以上函数也被称为聚合函数

3.2 时间日期型函数

时间格式：

DATETIME格式：YYYY-MM-DD HH:MM:SS
DATE格式：YYYY-MM-DD
YEAR格式：YYYY

常用日期型函数：

函数	描述
now()	返回当前日期和时间
curdate()	返回当前日期
curtime()	返回当前时间
date()	提取日期表达式的日期部分
weekday()	提取日期表达式的周几
weekofyear()	提取日期表达的当年周数
quarter()	提取日期表达式的季度
extract()	返回日期/时间的单独部分
date_add()	给日期添加指定的时间间隔
date_sub()	从日期减去指定的时间间隔
datediff()	返回两个日期之间的天数
date_format()	用不同的格式显示日期/时间

3.3 文本型函数

函数	描述
left(列名)	返回左边的字符
right(列名)	返回右边的字符
length(列名)	返回某字段的长度
replace(列名)	替换某列中的字符
substring(列名)	提取某列中的字符

4 数据分组

4.1 数据分组

语法结构：

select 字段, 计算字段
from 表名
where 条件
group by 字段;

4.2 数据过滤

语法结构：

select 字段, 计算字段
from 表名
where 条件
group by 字段
having 条件;

应用举例：

-- 奖金之和大于1000元的岗位
select job,sum(sal),sum(bonus)
from employee
where eage < 50
group by job
having sum(bonus) > 1000;

5 结果排序

语法结构：

select 字段, 计算字段
from 表名
where 条件
group by 字段
having 条件
order by 字段 desc;

升序：asc(默认)

降序：desc

6 控制语句

6.1 if函数

语法结构：

1	if(条件,true,false)

应用举例：

-- 判断是高收入工资还是低收入工资
select 
ename,
sal,
if(sal>10000,"高收入","低收入") as sal_res
from employee;

6.2 case when函数

语法结构：

case when 条件1 then '结果1'
		 when 条件2 then '结果2'
		 when 条件3 then '结果3'
		 ...
		 else '默认值'
end as '新字段名';

应用举例：

-- 根据入职日期判断员工属于元老级员工、老员工、还是新员工
select *,
case when hiredate < '2011-01-01' then '元老'
		 when hiredate between '2011-01-01' and '2016-12-31' then '老员工'
		 else '新员工'
end as type
from employee;

6.3 数据透视表

case when与group by联合使用，完成数据透视表功能

应用举例：

-- 根据入职日期判断元老级员工、老员工、新员工各有多少人
select job,
			 count(case when hiredate < '2011-01-01' then '元老' end) as "元老级员工"
			 count(case when hiredate between '2011-01-01' and '2016-12-31' then '老员工' end) as "老员工"
			 count(case when hiredate > '2017-01-01' then '新员工' end) as "新员工"
from employee
group by job;