MySQL fundamentals (01): classic practical query cases, summary and collation

Source code of this article: GitHub click here | | GitEE click here |

1. Join query

Schematic diagram

1. Build a table sentence

Department and employee relations table:

CREATE TABLE `dept` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'key ID', `deptName` varchar (30) DEFAULT NULL COMMENT' department name', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;CREATE TABLE `tb_ emp` (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `empName` varchar (20) DEFAULT NULL COMMENT' employee name', `deptId` int (11) DEFAULT'0' COMMENT 'department ID', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 2. Seven kinds of connection query figure 1: left outer connection select t1.recording select t2.empNamedirection T2 2.deptId from tb_dept T1 LEFT JOIN tb_emp t2 on t1.iddescription t2.deptId; figure 2: right outer connection select t1.select T1 RIGHT JOIN tb_emp t2 on t1.idfort2.deptId; figure 3: inner connection select t1.empNamedirection t2.empNamedirection t2.deptId from tb_dept t1 inner join tb_emp t2 on t1.iddict2.deptId; figure 4: left connection

Query specific features of the tb_dept table.

Select t1.recording from tb_dept t2.empNameMagol t2.deptId from tb_dept T1 LEFT JOIN tb_emp T2 on t1.id=t2.deptIdWHERE t2.deptId IS NULL; figure 5: right connection

Query specific features of the tb_emp table.

Select t1.recording from tb_dept t2.deptId from tb_dept T1 RIGHT JOIN tb_emp T2 on t1.id=t2.deptIdWHERE t1.id IS NULL; figure 6: fully connected select t1.deptId from tb_dept T1 LEFT JOIN tb_emp T2 on t1.id=t2.deptId figure 7: not connected at all

Query data to which two tables are not associated with each other.

Select t1.recording on t1.id=t2.deptIdWHERE t1.id IS NULLUNIONselect t2.deptId from tb_dept T1 RIGHT JOIN tb_emp T2 on t1.id=t2.deptIdWHERE t1.id IS NULLUNIONselect T1 on t1.id=t2.deptIdWHERE t2.deptId IS NULL t2.deptId from tb_dept T1 LEFT JOIN tb_emp T2 on t1.id=t2.deptIdWHERE t2.deptId IS NULL 2, time and date query 1, table creation statement CREATE TABLE `deptId (`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `user_ id` int (11) NOT NULL COMMENT' user ID' `user_ name`varchar (20) NOT NULL COMMENT 'user name', `consume_ Money`decimal (20jue 2) DEFAULT '0.00' COMMENT' consumption amount', `create_ time`datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT=' consumption Table' 2. Date statistics the first piece of data within the date range of the case

Scene: in the daily operation of products, we often see such rules: during the activity time, how much is the first consumption and how much is the discount?

SELECT * FROM (SELECT * FROM ms_consume WHERE create_time BETWEEN '2019-12-10 00 AND' 2019-12-18 23 SELECT 59 SELECT) t1GROUP BY t1.user_id; time difference between dates

Scenarios: commonly used countdown scenarios

SELECT t1.query, timestampdiff (SECOND,NOW (), t1.create_time) second_diff FROM ms_consume T1 WHERE T1 WHERE t1.idwritten data 9'; query today's data-- method-SELECT * FROM ms_consume WHERE DATE_FORMAT (NOW (),'% Ymuri% mmi% d') = DATE_FORMAT (create_time,'%Y-%m-%d');-- method II select * FROM ms_consume WHERE TO_DAYS (now ()) = TO_DAYS (create_time) Time range statistics

Scene: statistics of users who spent more than twice in the past seven days.

SELECT user_id,user_name,COUNT (user_id) userIdSum FROM ms_consume WHERE create_time > date_sub (NOW (), interval'7' DAY) GROUP BY user_id HAVING userIdSum > 1; average within date range

Scenario: specify the average consumption within the date range and sort it.

SELECT * FROM (SELECT user_id,user_name, AVG (consume_money) avg_money FROM ms_consume t WHERE t.create_time BETWEEN '2019-12-10 00 SELECT 00' AND' 2019-12-18 23 SELECT user_id,user_name 59 °GROUP BY user_id) t1ORDER BY t1.avg_money DESC Tree table query 1. Table creation statement CREATE TABLE ms_city_sort (`id`INT (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `city_ name` VARCHAR (50) NOT NULL DEFAULT' 'COMMENT' city name', `city_ code`VARCHAR 'NOT NULL DEFAULT' 'COMMENT' city code', `parent_ id`INT (11) NOT NULL DEFAULT'0' COMMENT 'parent ID', `state`INT (11) NOT NULL DEFAULT' 1' COMMENT 'status: 1 enable 2 deactivate', 'create_ time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT' creation time', `update_ time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (id) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = 'city classification management' 2. Directly SQL query SELECT t1.characters, t2.parentNameFROM ms_city_sort t1LEFT JOIN (SELECT m1.idstatement m2.citywords name parentNameFROM ms_city_sort m1reel msgiving cityweights sort m2 WHERE m1.parent_id = m2.id AND m1.parent_id > 0) T2 ON t1.id = t2.idscape 3, function query parent name DROP FUNCTION IF EXISTS get_city_parent_name CREATE FUNCTION `get_root_ name` (pid INT) RETURNS varchar (50) CHARSET utf8begin declare parentName VARCHAR (50) DEFAULT NULL; SELECT city_name FROM ms_city_sort WHERE id=pid into parentName; return parentName;endSELECT T1. Query root node child DROP FUNCTION IF EXISTS get_root_child;CREATE FUNCTION `get_root_ child` (rootId INT) RETURNS VARCHAR (1000) CHARSET utf8 BEGIN DECLARE resultIds VARCHAR DECLARE nodeId VARCHAR; 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; IV, source code address GitHub address https://github.com/cicadasmile/mysql-data-baseGitEE address https://gitee.com/cicadasmile/mysql-data-base