建表、添加数据及数据查询

//建表

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;