当前位置:Gxlcms > 数据库问题 > 【MySQL 查询语句测试】

【MySQL 查询语句测试】

时间:2021-07-01 10:21:17 帮助过:13人阅读

  具体随机生成测试数据可以参考下面链接:

  https://www.cnblogs.com/hill1126/p/11334523.html

 

2.注意事项

  如果你的MySQL版本是8以上的话可能会遇到一个小问题:

  用CALL  add_t_user_memory (1000000)来创建100w的数据时会报错如下:

  mysql 1449 : The USER specified AS a DEFINER (‘root‘@‘%‘) does NOT exist

  如果数据库版本低于8.0执行下面两句即可  

grant all privileges on *.* to root@"%" identified by ".";
flush privileges;

 

https://www.cnblogs.com/godtrue/p/6751814.html

如果数据库版本高于8.0报错,说上面语句存在语法错误,执行下面语句即可:

#先创建用户
CREATE USER ‘root‘@‘%‘ IDENTIFIED BY ‘.‘;
#再给用户授权
grant all privileges on *.* to ‘root‘@‘%‘ ;

 

3.建表

customers表字段类型如下:

CREATE TABLE `customers_memory` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(22) NOT NULL DEFAULT ‘‘,
`sex` CHAR(2) DEFAULT NULL,
`city_id` INT(11) NOT NULL,
`phone` VARCHAR(11) ,
`create_time` DATETIME NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

 

技术图片

 

salary表字段如下:

技术图片

 

4.SQL语句测试

 ? 若求女性客户数量和平均月薪:

求女性客户数量:

SELECT COUNT(*)
FROM customers
WHERE sex = "1";

总耗时      : 0.018 sec

求女客户平均月薪:

需要关联两张表

SELECT    AVG(b.monthsalary) FROM customers a,salary b  WHERE a.id=b.id  AND a.sex = "1";

总耗时      : 0.011 sec

当然写成下面这样也可以,耗时是一样的,因为现在都默认是内连接了,inner join作为内连接,对应outer join(外连接),二者主要区别 就是取交集的时候是否显示未查找到的数据(null);

SELECT AVG(b.monthsalary) FROM customers a INNER JOIN salary b  ON a.id = b.id WHERE a.sex = "1";

 

 ? 求不同城市的客户数量和平均月薪:

求客户数量:

SELECT city_id,COUNT(*)
FROM customers
GROUP BY city_id
ORDER BY city_id

 

技术图片

为了直观加了排序ORDER BY ,不加排序耗时0.006s左右,加了大概是0.007s

 

 ? 列出icity_id小于50,或者年奖金不足1k的客户姓名
SELECT NAME 
FROM customers a INNER JOIN salary b ON a.id = b.id WHERE a.city_id<50 OR b.yearbonus <1000

总耗时      : 0.007 sec

 

 ? 不同年龄段(0 到 100 岁之间,每 10 岁为一个年龄段)的客户数据量和平均年收入
  SELECT ELT(
 INTERVAL(
   TIMESTAMPDIFF(YEAR, create_time, CURDATE()),0,10,20,30,40,50,60,70,80,90,100),
   "0-10","10-20","20-30","30-40","40-50","50-60","60-70","70-80","80-90","90-100",">100"
) AS age ,monthsalary*12+yearbonus AS income
FROM customers a INNER JOIN salary b ON a.id = b.id  GROUP BY age ORDER BY NULL

查询结果:

技术图片

分组查询一般需要elt和interval两个函数组合完成,

 ELT(N,str1,str2,str3,...)  如果N= 1,返回str1,如果N= 2,返回str2,等等。如果N小于1或大于参数个数,返回NULL;

  INTERVAL(N,N1,N2,N3,...)  (N小于后面的某个参数,就返回这个参数的前一个位置数字);

比如 SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); (23小于30,30的位置是4,于是返回3)

而TIMESTAMPDIFF函数可以计算时间差值(年龄),

YEAR, create_time, CURDATE()三个参数,YEAR表示以年为单位计算差值,返回第二个参数与 第三个参数CURDATE()之差,第三个参数即为当前时间
总耗时      : 0.030 sec     后续考虑添加索引                    

【MySQL 查询语句测试】

标签:define   arc   报错   连接   问题:   需要   div   rgb   one   

人气教程排行