【MySQL 查询语句测试】

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

　　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   

查看更多关于【MySQL 查询语句测试】的详细内容...