Kooxda wadarta MySQL isticmaal ahaan? Sharaxaada tafatiran ee koox ahaan tiro ahaan ee xogta MySQL

MySQL Guud ahaan koox ahaan isticmaal ahaan?database MySQLSharaxaada faahfaahsan ee koox ahaan

GROUP MySQL BY bayaanka

GROUP BY warbixinta waxay kooxeeyaan natiijada la dejiyay iyadoo lagu salaynayo hal ama dhowr tiir.

Tiirka la isku daray waxaan isticmaali karnaa COUNT, SUM, AVG, iwm.

GROUP BY syntax

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Tusaalaha muujinta

Tusaalaha cutubkani waxa uu isticmaalayaa shaxda soo socota qaab dhismeedka iyo xogta, ka hor inta aan la isticmaalin, waxaan soo gelin karnaa xogta soo socota database.

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Kadib soo dejintu guulaysato, fuli bayaanka SQL ee soo socda:

mysql> set names utf8;
mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)

Marka xigta waxaan isticmaalnaa GROUP BY bayaanka si aan ugu ururino shaxda xogta magac ahaan oo aan tirino inta diiwaan ee qof kastaa haysto:

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

U adeegso duubis

ROLLUP waxay hirgelin kartaa isla tirokoobyada (SUM, AVG, COUNT...) iyadoo lagu salaynayo tirokoob kooxaysan.

Tusaale ahaan, waxaanu ku ururinay shaxda xogta kor ku xusan magac ahaan, ka dibna waxaanu tirinnaa tirada jeer ee qof kastaa soo galay:

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

Rikoorka NULL wuxuu u taagan yahay tirada gelitaanka qof kasta.

Waxaan u isticmaali karnaa coalesce si aan u dejino magac bedeli kara NUll, syntax coalesce:

select coalesce(a,b,c);

Sharaxaadda cabbirka: haddii a== null, dooro b; haddi b==null, dooro c; haddi a!=null, dooro a; haddi abc uu buray, soo noqo waxba (macnola'aan).

Tusaalaha soo socda haddii magacu madhan yahay waxaan isticmaalnaa wadarta guud halkii:

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)