MySQL 基本语法与使用
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函数
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子查询
语法结构：
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子查询
语法结构：
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函数
语法结构：
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;
7 结束语
数据库语言在过去的工作中经常用到，本次也算是温故知新了，同时在复习过程中发现了一个宝藏网站W3school，里面包含系统的学习教程，界面简洁，知识点集中且总结到位，是非常适合自学者学习和练习的网站，网址附在文末，关于编程语言的学习还是要多多练习，多多运用才能避免荒废呀！
W3scool：https://www.w3school.com.cn/index.html
祝大家学习工作生活都能越来越好，慢慢变成自己希望成为的人！