//建表

CREATE TABLE `cs_user2` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

  `category` varchar(20) COLLATE utf8_turkish_ci NOT NULL COMMENT '类别',

  `parentid` int(11) NOT NULL DEFAULT '0' COMMENT '上级',

  `createtime` int(11) NOT NULL COMMENT '创建时间',

  PRIMARY KEY (`id`),

  KEY `fl` (`createtime`)

) ENGINE=InnoDB AUTO_INCREMENT=46056 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;

 

//添加数据

<?php

set_time_limit(100);

$liarr=['耳机','蓝牙耳机','帽子','蓝牙音响','USB音响','键盘','MP3','MP4','鼠标','助听器'];

$k=rand(0,9);

$j=$liarr["$k"];

include './connect0.php';

$k=rand(0,9);$j=$liarr["$k"];

for($i=1;$i<=20;$i++){

$k=rand(0,9);$j=$liarr["$k"];

$sql1="INSERT INTO cs_user2 (category,parentid,createtime) VALUES('".$j."','$i',".$_SERVER['REQUEST_TIME'].") ;";

$sq=mysql_query($sql1);

if (!$sq) echo '<br>'.mysql_error();

}

?>

<?php

include 'connect0.php';

$sql0='select id from cs_user2';

$fetch=mysql_query($sql0);

while($id=mysql_fetch_array($fetch,MYSQL_NUM)){

$ids[]=$id[0];

}

for($i=0;$i<count($ids);$i++){

$price[$ids[$i]]=rand(5000,10000)/100;

}

for($j=1;$j<=100;$j++){

$cateid=array_rand($ids);

$sql1='insert into cs_user3(cateid,goodname,price,markamout,quantity,createtime)';

$sql1.='values("'.$cateid.'","商品名称","'.$pric=array_rand($price).'","'.$pric*1.2.'","'.rand(0,9999).'","'.rand(1485878400,1492509983).';")';

$query=mysql_query($sql1);

if(!$query)echo mysql_error().'<br>';

}

?>

 

//数据查询相关函数

DISTINCT() 过滤重复COUNT()    统计个数    SELECT COUNT(DISTINCT(cateid)) FROM cs_goods ORDER BY cateid    SELECT COUNT(*) FROM cs_goods    SUM()   求和       求price列总和       SELECT SUM(price) FROM cs_goods       求每个月总销售额       SELECT SUM(price),SUBSTRING(FROM_UNIXTIME(createtime),1,7) AS ymonth FROM cs_goods GROUP BY ymonth;       求每天总销售额       SELECT SUM(price),DATE(FROM_UNIXTIME(createtime)) AS ymonth FROM cs_goods GROUP BY ymonth ORDER BY ymonth DESC;       求每天销售额大于100的记录       SELECT SUM(price) AS total,DATE(FROM_UNIXTIME(createtime)) AS ymonth FROM cs_goods GROUP BY ymonth HAVING total>100 ORDER BY ymonth DESC;AVG()  求平均       求所有商品平均单价       SELECT AVG(price) FROM cs_goods;       求每个分类下商品平均单价       SELECT AVG(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;MAX()  求最大值       求每个分类下最高单价       SELECT MAX(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;MIN()  求最小值       求每个分类下最小单价       SELECT MIX(a.price),a.cateid,b.category FROM cs_goods a INNER JOIN cs_category b ON(a.cateid=b.id) GROUP BY cateid;