MySQL之复杂查询的实现

select * from blog order by balance desc;

select * from blog order by balance asc;

update blog set age = 25 where age < 25;

select * from blog order by age asc, balance desc;

CREATE TABLE `employee` (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL DEFAULT '',
  `identity` varchar(20) NOT NULL DEFAULT '',
  `signin` tinyint NOT NULL DEFAULT '0' COMMENT '打卡次数',
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `employee` VALUES 
('1', '小明', 'firemen', 3, '2022-02-21'), 
('2', '小王', 'doctor', 3, '2022-02-21'), 
('3', '小丽', 'police', 3, '2022-02-21'), 
('4', '小王', 'doctor', 2, '2022-02-22'), 
('5', '小明', 'firemen', 1, '2022-02-22'), 
('6', '小丽', 'police', 3, '2022-02-22'), 
('7', '小明', 'firemen', 2, '2022-02-23'),
('8', '小王', 'doctor', 2, '2022-02-23'),
('9', '小红', 'nurse', 3, '2022-02-23');

SELECT name, COUNT(*) FROM employee GROUP BY name;

SELECT name, SUM(signin) as signin_count FROM employee GROUP BY name WITH ROLLUP;

SELECT name, SUM(signin) as signin_count FROM employee GROUP BY name WITH ROLLUP;

select author from blog 
UNION ALL 
select identity from employee;

alter table blog modify `author` varchar(40) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '';

select author from blog 
UNION
select identity from employee;

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

select b.author, b.age, b.title, e.name, e.signin from blog b 
INNER JOIN employee e on b.author = e.identity;

select b.author, b.age, b.title, e.name, e.signin from blog b,employee e 
where b.author = e.identity;

select b.author, b.age, b.title, e.name, e.signin from blog b 
LEFT JOIN employee e on b.author = e.identity;

select b.author, b.age, b.title, e.name, e.signin from blog b 
RIGHT JOIN employee e on b.author = e.identity;