MySQL基本语句模板

DDL数据定义语言

建表语句

DROP TABLE fileinform;
CREATE TABLE `fileinform`(
  `fid` int(8) NOT NULL AUTO_INCREMENT COMMENT '文件id',
  `fname` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '文件名称',
  `ftype` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '文件类型',
  `fpath` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NOt NULL DEFAULT '' COMMENT '文件路径',
  `fMD5` varchar(300) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NUll DEFAULT '' COMMENT '文件MD5(hash)',
  `fsize` int(8) NOT NULL DEFAULT 0 COMMENT '文件大小(字节)',
  `upsize` int(8) NOT NULL DEFAULT 0 COMMENT '已经上传大小(字节)',
  `state` tinyint(1) NOT NULL DEFAULT 0 COMMENT '状态标记位（0代表未上传完成，1代表上传成功）',
  `uploadTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
  `notice` longtext COMMENT '注意事项',
  PRIMARY KEY (`fid`) USING BTREE,
  UNIQUE KEY `id_find_file` (`ftype`,`fsize`,`fname`) USING BTREE,
  KEY `fMD5` (`fMD5`) USING BTREE,
)ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '文件信息表' ROW_FORMAT = Dynamic;

其中COLLATE代表排序规则，详情参照这里

ALTER

-- 修改表名
ALTER  TABLE  旧表名  RENAME  AS  新表名
-- 增加表的字段
ALTER  TABLE 表名  ADD  字段名  列属性
-- 增加外键
ALTER  TABLE 表名  ADD  [CONSTRAINT  `约束名(多以FK_开头)` ] FOREIGN  KEY  (`字段名`)  REFERENCES `引用表`(`字段名`)

-- 修改表的字段
-- 修改约束
ALTER  TABLE  表名  MODIFY 字段名  列属性 [ ]
-- 字段重命名
ALTER  TABLE  表名  CHANGE  旧名字  新名字  列属性 [ ]

-- 删除表的字段
ALTER  TABLE  表名  DROP  字段名

DROP

 删除表
DROP  TABLE  [ IF EXISTS ]  表名

DML数据管理语言

Insert

-- 插入语句
insert  into  表名（字段名1，字段名2，……）values（‘值1’，‘值2’，……）[，( )，( )，……]

Update

-- 修改语句
update  `表名`  set  `修改的字段名`  =  修改的值  where  条件
-- 当没有条件时，默认修改该字段所有的值

Delete

-- 删除数据（全部）
delete  from `表名`
-- 删除数据（指定数据）
delete  from `表名`  where  条件
-- 清空表
truncate  `表名`
-- truncate 和 delete的区别
delete                不会影响自增
truncate              自增会归零

Delete删除的问题，重启数据库后现象【引擎分析】
InnoDB 自增列会重1开始 (存在内存当中的，断电即失)
MylSAM 继续从上一个自增量开始 (存在文件中的，不会丢失)

DQL数据查询语言

查询模板

select  `字段名`
from  表名
[ left | right | inner ] join 表2  on 联合键
where  条件
group  by ……     -- 分组【在使用分组查询时，select后面的列必须是 ：统计函数处理的列 或 分组条件列】
having  ……       --  分组后的次要条件
order  by  ……
limit  ……

单表查询

-- 查询全部
select  *  from  表名
-- 查询指定字段
select  `字段名`  from  表名
-- 查询指定字段别名显示
select  `字段名`  as  别名  from  表名
-- 函数    例：Concat(a，b)
select  Concat(`其他或字段名`，`字段名`)  as  别名  from  表名
-- 查询结果【去重】
select  distinct  `字段名`  from  表名
-- 查询结果【条件】
select  `字段名`  from  表名  where  条件
条件
-- 子查询
select  `字段名`  from  表名  where  `字段名` = （查询语句）
其他条件
-- 排序
select  `字段名`  from  表名  where  条件  order  by  `字段名`  [ASC (升序) /DESC (降序) ]
-- 分页
select  `字段名`  from  表名  where  条件  order  by  `字段名`  [ASC (升序) /DESC (降序) ]   limit  起始值，显示数量

分页公式
第N页 limit (n-1) * pageSize，pageSize
【pageSize：显示数量】
【(n-1) * pageSize：起始值】
【n：当前页】
【数据总数 / 显示数量 = 总页数】

连接查询

内连接
-- INNER JOIN
select  `字段名`  from  表1  inner  join  表2  on  表1.字段  =  表2.字段
外连接
-- RIGHT JOIN
select  `字段名`  from  表1  right  join  表2  on  表1.字段  =  表2.字段
-- LEFT  JOIN
select  `字段名`  from  表1  left  join  表2  on  表1.字段  =  表2.字段

联合查询

作用：将多个select语句的查询结果，显示在一个结果集中。

-- 代码实现
select  `字段名`  from  表1
union [all]
select  `字段名`  from  表2

【注意点】

1. 参与联合查询的列的数量必须相同.
2. 最好对应列的类型、长度一致

UNION ALL 与 UNION的区别

1. union all会显示所有数据，包括重复的
2. union对重复的数据只显示一次

其他语句

按年月日时分秒统计

方案一

MySQL中提供了date_format函数进行格式化该字段显示成期望形式。
DATE_FORMAT(date,format) 函数用于以不同的格式显示日期/时间数据。

我们常用的

• 按年统计就可以 group by date_format(date字段名, “%Y”)
• 按月统计就可以 group by date_format(date字段名, “%Y-%m”)
• 按天统计就可以 group by date_format(date字段名, “%Y-%m-%d”)
• 按季度统计可以 group by concat(date_format(date字段名, ‘%Y-’),quarter(date字段名) ）

使用 quarter() 函数，直接计算当天时间为哪个季度季度 （1，2，3，4）
concat拼接 这样每个日期会被处理 成 2021-1 这种形式（1是第几季度）

DATE_FORMAT可使用格式参照这里

方案二

可以使用MySQL的函数【YEAR、MONTH、WEEK】进行筛选

SELECT `需要排序的单位函数` FROM `表名` 
WHERE YEAR(`用于筛选的日期字段`) = 2021
GROUP BY MONTH(`用于筛选的日期字段`)