[风一样的创作]sql语句配合mysql数据库常用语句

算是对sql基础和mysql函数的一些基本应用吧，对应刚入门的人来说会有一定的帮助。

#数据库初始表和数据
DROP TABLE IF EXISTS `phone`;
CREATE TABLE `phone`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `pid` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of phone
-- ----------------------------
INSERT INTO `phone` VALUES (1, '13755354957', 1);
INSERT INTO `phone` VALUES (2, '1875442838', 1);

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `sex` int(0) NOT NULL,
  `time` datetime(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (3, '', 0, '2022-01-18 14:22:52');
INSERT INTO `user` VALUES (4, NULL, 0, '2022-01-19 14:22:58');
INSERT INTO `user` VALUES (5, NULL, 0, '2022-01-20 14:23:01');
INSERT INTO `user` VALUES (6, NULL, 0, '2022-01-21 14:23:05');
INSERT INTO `user` VALUES (7, NULL, 0, '2022-01-22 17:37:18');
INSERT INTO `user` VALUES (9, NULL, 0, '2022-02-11 09:43:37');
INSERT INTO `user` VALUES (10, NULL, 0, '2022-02-11 09:44:23');
INSERT INTO `user` VALUES (11, NULL, 0, '2022-02-11 09:44:23');

1.单条数据添加
#user()里面是当前这条新增数据要添加内容的字段，values()里面是按顺序写上字段对应类型的内容
insert into user(name,sex,time) t1 values('小花',0,now())

2.单条数据删除
#不写条件就是删除整个表的数据，所以用的时候要注意是否会出现条件失效的情况导致删掉整张表
delete from user t1 where t1.id=1

3.单条数据编辑
#不写条件就是编辑整个表的指定字段，所以用的时候要注意是否会出现条件失效的情况
update user set name='小霞',sex=1 where id=2

4.单表查询
select t1.* from user t1

5.where / and / or 条件和逻辑关键字
#使用()和and和or注意优先级，从左到右or优先级最低，and就是把前后两个条件合并成一个条件参考java的&&，优先级参考12 + 20 * 10 -10
select t1.* from user t1 where t1.name='小霞' or t1.name='王五' and t1.sex=3 or t1.name='小二'

6.like 模糊查询关键字(可以配合函数concat('%','旦','%')使用)
#concat()作用是拼接任意字符串，%表示万能字符串
select t1.* from user t1 where t1.name like '%霞' or t1.name like '校%' or t1.name like '%飞%' or t1.name like concat('%','旦','%')

7.group by 分组关键字
#把字段字段作为条件进行分组，返回每一组的第一条结果，如果多个字段条件就是类似于and的意思，就是用这些条件加起来的结果来分组，比如name='小霞',sex=1 刚好有两条满足这个条件的就分组成一条，有一个不满足就是分组成多条
select t1.* from user t1 group by t1.name,t1.sex

8.order by 排序关键字
#指定字段进行排序，不加desc关键字默认就是正序，如果是多个条件就是依次排序上次的排序结果，比如用name排序完以后再用sex，这时候就类似于 select * from user t1 where t1.name = '小霞' order by t1.sex(如果就一条数据就不动，多条则根据新条件互换这些数据的位置，其他没查询到的数据还是按照原来上一次排序的结果位置)，多个条件就是递归一样，几个条件递归几次，不断排序上一次的排序结果
select t1.* from user t1 order by t1.name,t1.sex,t1.id

9.having 筛选关键字
#having作用和where类似，区别就是where筛选的是from指定的所有数据(表或子查询结果)，having筛选的是当前查询的结果，筛选的字段必须是查询结果中的字段
select t1.* from user t1 having t1.id=2 or t1.id=3 or t1.name='王五'

10.limit 分页关键字
#从指定位置(不包含开始位置那一条)开始分页，获取指定条数的数据，第一个参数是开始位置，第二个参数是往后获取多少条，结果其实就是从N1+1开始到N1+1+N2结束
select t1.* from user t1 limit 5,2

11.is null / is not null 空判断关键字
#判断指定字段是否为null，注意null和'' 是两个东西，''是空字符串，null是表示没内容
select t1.* from user t1 where t1.name is null
select t1.* from user t1 where t1.name is not null
select t1.* from user t1 where t1.name=''
select t1.* from user t1 where t1.name!=''
select t1.* from user t1 where t1.name='' or t1.name is null
select t1.* from user t1 where t1.name!='' or t1.name is not null

12.in / not in / exists / not exists 批量逻辑过滤关键字
#in表示当前行数据在子查询结果里面只要有一个相同的就返回true，not in就刚好相反
select t1.* from user t1 where t1.id in (1,2,3)
select t1.* from user t1 where t1.id not in (1,2,3)
#exists是用主表循环，然后把每行的数据代入子查询匹配自拟定的条件，如果子查询返回N条数据(就是查询不是空)则返回true保留这条数据，not exists则相反
select t1.* from user t1 where exists (select t2.* from user t2 where t2.id=t1.id and t2.id=1)
select t1.* from user t1 where not exists (select t2.* from user t2 where t2.id=t1.id and t2.id=1)

13.between 范围逻辑关键字
#注意between范围是包前包后的，比如你开始值是2022-01-17 14:22:45，那么完全等于这个时间的也会被查询出来，大概就是 >= and <= 的逻辑，一般用来判断数字和时间，如果是字符串默认以首字母排序
select t1.* from user t1 where (t1.name between '2022-01-17 14:22:45' and '2022-01-19 14:22:58') order by t1.time

14.union 合并数据关键字
#合并两个查询的结果，必须要满足的条件(两个查询的结果字段要一样多，并且要从左到右保持数据类型一致，另外union或默认过滤掉一整行所有字段数据相同的行，多行一样则显示一行)
select t1.id,t1.name from user t1 union select t2.id,t2.phone from phone t2

15.as 别名关键字
#用as标识后面写上新的名字，可以用来给字段，表，子查询结果(临时虚拟表)取别名，注意用一个空格也可以代替as
select t3.* from (select t1.id as ids,t1.name as names from user t1) t3

16.子查询
#子查询可以用来做返回值字段过滤，可以用来做条件查询数据，可以用来做临时虚拟表等等
select * from (select t1.*,(select t3.name from user t3 where t3.id=2) as tname from user t1 where t1.id in (select t2.id from phone t2)) t4

17.多表链表关联查询(inner join，left join，right join)
#inner join内关联查询两张表都是主表，条件的话on和where都可以用，也都可以不用(就会把所有可能的逻辑关系都生成数据)，效果上on和where基本一样，看喜欢
select t1.*,t2.* from user t1 inner join phone t2
select t1.*,t2.* from user t1 inner join phone t2 on t1.id=t2.id
select t1.*,t2.* from user t1 inner join phone t2 on t1.id=t2.id and t1.name='小霞' and t2.phone='13755354957' where t1.name='小霞' and t2.phone='13755354957'
#left join以左表为主，在on后面where前面写入的条件只会影响右表的返回结果，左表一般不会被影响，但是有一些逻辑下会，比如on条件里面左表的字段对应右表的字段，是一个一对多的或者多对多的情况，就会返回满足条件的所有情况数据，数据就可能会超过左表真实的数据，但是每一行数据肯定是会有至少一个字段是和任何一行都不相同的，而where后面一般只写左表的独立条件(也可以说就是过滤on之后的查询结果))，right join和left join逻辑基本一样，只不过是以右表为主
select t1.*,t2.* from user t1 left join phone t2 on t1.id=t2.pid
select t1.*,t2.* from user t1 left join phone t2 on t1.name='小霞'
select t1.*,t2.* from user t1 left join phone t2 on t2.id=1
select t1.*,t2.* from user t1 left join phone t2 on t1.id=t2.id and t1.name='小霞'
select t1.*,t2.* from user t1 left join phone t2 on t1.id=t2.id and t1.name='小霞' where t1.id=1
select t1.*,t2.* from user t1 left join phone t2 on t1.id=t2.id and t1.name='小霞' where t2.id=1

18.批量数据添加
#批量添加，格式就是和单个添加差不多，在values后面添加几条数据就加几个括号
insert into user(name,sex,time) values('小花',0,now()),('小张',1,now())

19.批量数据编辑
#批量编辑(这里指的批量编辑不是单纯指修改了多个数据，而是修改了符合N>1个条件的数据)，第一种是固定的修改字写法，第二种是根据条件匹配修改值和固定值混合使用，如果是在代码中使用就是循环when id=1 then '小A'，意思大概是 if(id=1){return '小A'}，循环几次就是几个if，所有if不匹配则返回null
update user set name='小霞',sex=1 where id in (1,2)
update user set name=case when id=1 then '小A' when id=2 then '小B' end,sex=1 where id in (1,2)

20.批量数据删除
#批量删除(单个删除是指用唯一标识进行的删除，这里指的批量删除不是单纯指删除了多个数据，而是删除了符合N>1个条件的多个数据)
delete from user t1 where t1.id in (1,2)

21.聚合函数查询
sum()：累加
count(0)：计次
max：最大值
min：最小值
avg：平均值
ceil：得到整数
#聚合函数，默认会把查询结果所有行数据合并成一行数据得到统计结果，数据为null的(空白不是null)不会被统计，如果统计的字段所有数据都是null则返回null，可以配合group by 分组使用，这样就会根据分组的组数再进行统计得到对应组数的统计结果
select sum(t1.id) from user t1
select count(t1.id) from user t1
select max(t1.id) from user t1
select min(t1.id) from user t1
select avg(t1.id) from user t1
#这个函数不会合并数据
select ceil(3.1314) from user t1
#带分组的用法
select avg(t1.id) from user t1 group by t1.id

22.判断函数(多作用于返回数据判断)
#判断函数，作用和java的if差不多意思，特别是case when ？ then ？ else ？其实就是if(){}else{}
select IF(t1.id=4,t1.id,-100) from user t1 
select case when t1.id=4 then t1.id ELSE 5 end from user t1
select IFNULL(t1.name,-100) from user t1 
select * from user t1 where t1.id=IF(t1.id>3,t1.id,-100)