MySQL(01) 基础查询

一、基础查询

1、建表语句

部门和员工关系表：

CREATE TABLE `tb_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `deptName` varchar(30) DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `empName` varchar(20) DEFAULT NULL COMMENT '员工名称',
  `deptId` int(11) DEFAULT '0' COMMENT '部门ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

插入几条测试数据

INSERT INTO tb_dept(id, deptName)VALUES(1, 'AA');
INSERT INTO tb_dept(id, deptName)VALUES(2, 'BB');
INSERT INTO tb_dept(id, deptName)VALUES(3, 'CC');
INSERT INTO tb_dept(id, deptName)VALUES(4, 'DD');
INSERT INTO tb_emp(id, empName, deptId)VALUES(1, '张三', 1);
INSERT INTO tb_emp(id, empName, deptId)VALUES(2, '李四', 1);
INSERT INTO tb_emp(id, empName, deptId)VALUES(3, '王五', 2);
INSERT INTO tb_emp(id, empName, deptId)VALUES(4, '赵六', 3);
INSERT INTO tb_emp(id, empName, deptId)VALUES(5, '飞机', 100);

结果：

2、七种连接查询

图解示意图

• 图1：左外连接

select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
left join tb_emp t2 on
	t1.id = t2.deptId;

• 图2：右外连接

select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
right join tb_emp t2 on
	t1.id = t2.deptId;

• 图3：内连接

select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
inner join tb_emp t2 on
	t1.id = t2.deptId;

• 图4：左连接

查询tb_dept表特有的地方。

select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
left join tb_emp t2 on
	t1.id = t2.deptId
where
	t2.deptId is null ;

• 图5：右连接

查询tb_emp表特有的地方。

select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
right join tb_emp t2 on
	t1.id = t2.deptId
where
	t1.Id is null ;

• 图6：全连接

    即左连接联合右连接

select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
left join tb_emp t2 on
	t1.id = t2.deptId
union 
select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
right join tb_emp t2 on
	t1.id = t2.deptId ;

• 图7：全不连接

   查询两张表互不关联到的数据。

select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
left join tb_emp t2 on
	t1.id = t2.deptId
	where t1.id is null 
union 
select
	t1.*,
	t2.empName,
	t2.deptId
from
	tb_dept t1
right join tb_emp t2 on
	t1.id = t2.deptId 
where t2.deptId is null;
	

二、时间日期查询

1、建表语句

CREATE TABLE `ms_consume` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `user_name` varchar(20) NOT NULL COMMENT '用户名',
  `consume_money` decimal(20,2) DEFAULT '0.00' COMMENT '消费金额',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='消费表';

    插入数据

INSERT INTO  ms_consume(id, user_id, user_name, consume_money, create_time)VALUES(1, 11, '张三', 100.02, '2023-07-09 09:55:03');
INSERT INTO  ms_consume(id, user_id, user_name, consume_money, create_time)VALUES(2, 22, '李四', 1111.02, '2022-06-08 09:55:03');
INSERT INTO  ms_consume(id, user_id, user_name, consume_money, create_time)VALUES(3, 33, '王五', 2222.00, '2023-01-07 09:55:03');
INSERT INTO  ms_consume(id, user_id, user_name, consume_money, create_time)VALUES(4, 44, '赵六', 112345.98, '2022-08-09 09:55:03');
INSERT INTO  ms_consume(id, user_id, user_name, consume_money, create_time)VALUES(5, 55, '铁子', 1.01, '2023-03-02 09:55:03');
INSERT INTO  ms_consume(id, user_id, user_name, consume_money, create_time)VALUES(6, 66, '老六', 100.02, '2023-08-09 09:55:03');
INSERT INTO  ms_consume(id, user_id, user_name, consume_money, create_time)VALUES(7, 22, '李四', 99999.02, '2023-08-06 09:55:03');
INSERT INTO  ms_consume(id, user_id, user_name, consume_money, create_time)VALUES(8, 11, '张三', 10880.02, '2023-07-19 09:55:03');

2、日期统计案例

• 日期范围内首条数据

场景：产品日常运营活动中，经常见到这样规则：活动时间内，首笔消费满多少，优惠多少。

select
	*
from
	(
	select
		*
	from
		ms_consume
	where
			create_time 
		between '2023-07-09 00:00:00' and '2023-08-09 23:59:59'
	order by
		create_time
	) t1
group by
	t1.user_id ;

ps：有可能会报这个错误

 报错原因：

ONLY_FULL_GROUP_BY的意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

解决办法：

查看sql_model参数命令：
SELECT @@SESSION.sql_mode;

在mysql中输入

mysql> set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 重启服务（貌似不重启也行哈，习惯了）&& 视图工具也可执行，不一定要到命令行

 查询结果

- 日期之间时差

场景：常用的倒计时场景

select
	t1.*,
	   timestampdiff(second,NOW(),t1.create_time) as second_diff秒为单位
from
	ms_consume t1
where
	t1.id = '3' ;

 查询今日数据

-- 方式一

SELECT * FROM ms_consume

WHERE DATE_FORMAT(NOW(),'%Y-%m-%d')=DATE_FORMAT(create_time,'%Y-%m-%d');

-- 方式二

SELECT * FROM ms_consume

WHERE TO_DAYS(now())=TO_DAYS(create_time) ;

结果

 ps: 方式一通过修改限制，可以控制查询范围

select * from ms_consume 
where date_format(now(),'%Y')=DATE_FORMAT(create_time,'%Y');

- 时间范围统计

场景：统计近七日内，消费次数大于0次的用户。

select
	user_id,
	user_name,
	create_time ,
	COUNT(user_id) as userIdSum
from
	ms_consume
where
	create_time > date_sub(NOW(), interval '7' day)
group by
	user_id
having
	userIdSum > 0;

- 日期范围内平均值

场景：指定日期范围内的平均消费，并排序。

select
	*
from
	(
	select
		user_id,
		user_name,
		consume_money,
		AVG(consume_money) as avg_money
	from
		ms_consume t
	where
		t.create_time 
			between '2023-07-09 00:00:00' and '2023-08-09 23:59:59'
	group by
		user_id
	) t1
order by
	t1.avg_money desc;

三、树形表查询

1、建表语句

CREATE TABLE ms_city_sort (
	`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	`city_name` VARCHAR (50) NOT NULL DEFAULT '' COMMENT '城市名称',
	`city_code` VARCHAR (50) NOT NULL DEFAULT '' COMMENT '城市编码',
	`parent_id` INT (11) NOT NULL DEFAULT '0' COMMENT '父级ID',
	`state` INT (11) NOT NULL DEFAULT '1' COMMENT '状态：1启用,2停用',
	`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
	PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '城市分类管理';

插入测试数据

INSERT INTO  ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(1, '武汉', 'WUHAN', 0, 1, '2023-08-09 11:10:31', '2023-08-09 11:10:31');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(2, '北京', 'BEIJING', 0, 1, '2023-08-09 11:11:42', '2023-08-09 11:11:42');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(3, '上海', 'SHANGHAI', 0, 1, '2023-08-09 11:11:42', '2023-08-09 11:11:42');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(4, '广州', 'GANGZHOU', 0, 1, '2023-08-09 11:11:42', '2023-08-09 11:11:42');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(5, '襄阳', 'XIANGYANG', 1, 1, '2023-08-09 11:11:42', '2023-08-09 11:11:42');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(6, '深圳', 'SENGZHENG', 4, 1, '2023-08-09 11:11:42', '2023-08-09 11:11:42');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(7, '香港', 'XIANGGANG', 4, 1, '2023-08-09 11:11:42', '2023-08-09 11:11:42');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(8, '西安', 'XIAN', 0, 1, '2023-08-09 11:11:52', '2023-08-09 11:11:52');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(9, '孝感', 'XIAOGAN', 1, 1, '2023-08-09 11:11:59', '2023-08-09 11:11:59');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(10, '十堰', 'SHIYAN', 1, 1, '2023-08-09 11:11:59', '2023-08-09 11:11:59');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(11, '北京1', 'BEIJING1', 2, 1, '2023-08-09 11:15:54', '2023-08-09 11:15:54');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(12, '北京2', 'BEIJING2', 3, 1, '2023-08-09 11:15:54', '2023-08-09 11:15:54');
INSERT INTO ms_city_sort(id, city_name, city_code, parent_id, state, create_time, update_time)VALUES(13, '上海1', 'SHANGHAI1', 3, 1, '2023-08-09 11:15:54', '2023-08-09 11:15:54');

2、直接SQL查询

SELECT t1.*, t2.parentName
FROM ms_city_sort t1
LEFT JOIN (
	SELECT
		m1.id,m2.city_name parentName
	FROM
		ms_city_sort m1,ms_city_sort m2
	WHERE m1.parent_id = m2.id
	AND m1.parent_id > 0
) t2 ON t1.id = t2.id;

              

 3、函数查询

- 查询父级名称

DROP FUNCTION IF EXISTS get_city_parent_name;
CREATE FUNCTION `get_city_parent_name`(pid INT) 
RETURNS varchar(50) CHARSET utf8
begin 
    declare parentName VARCHAR(50) DEFAULT NULL;
    SELECT city_name FROM ms_city_sort WHERE id=pid into parentName;
    return parentName;
end

SELECT t1.*,get_city_parent_name(t1.parent_id) parentName FROM ms_city_sort t1 ;

查询根节点子级

DROP FUNCTION IF EXISTS get_root_child;
CREATE FUNCTION `get_root_child`(rootId INT) 
    RETURNS VARCHAR(1000) CHARSET utf8
    BEGIN 
        DECLARE resultIds VARCHAR(500); 
        DECLARE nodeId VARCHAR(500);
        SET resultIds = '%'; 
		SET nodeId = cast(rootId as CHAR);
        WHILE nodeId IS NOT NULL DO 
			SET resultIds = concat(resultIds,',',nodeId);
            SELECT group_concat(id) INTO nodeId 
			FROM ms_city_sort WHERE FIND_IN_SET(parent_id,nodeId)>0;
        END WHILE; 
        RETURN resultIds; 
END  ;

SELECT * FROM ms_city_sort WHERE FIND_IN_SET(id,get_root_child(5)) ORDER BY id ;