mysql使用05

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> #现在我们来讨论星期数的问题
mysql> set @a='2011-01-01';
Query OK, 0 rows affected (0.01 sec)

mysql> set weekday(@a),dayofweek(@a),dayname(@a);
ERROR 1193 (HY000): Unknown system variable 'weekday'
mysql> set dayofweek(@a),dayname(@a);
ERROR 1193 (HY000): Unknown system variable 'dayofweek'
mysql> select weekday(@a),dayofweek(@a),dayname(@a);
+-------------+---------------+-------------+
| weekday(@a) | dayofweek(@a) | dayname(@a) |
+-------------+---------------+-------------+
|           5 |             7 | Saturday    |
+-------------+---------------+-------------+
1 row in set (0.00 sec)

mysql> #表示出了 我们所要的日期
mysql>
mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Monday         |
+----------------+
1 row in set (0.00 sec)

mysql> set lc_time_names = 'zh_CN';
Query OK, 0 rows affected (0.00 sec)

mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| 星期一         |
+----------------+
1 row in set (0.00 sec)

mysql> #设置了显示的格式  至中国时区的表示法
mysql> select week('2011-01-01'),
    -> week('2011-01-02),
    '> week('2011-01-03');
    '> ;
    '> ;
    '> ';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2011-01-03');
;
;
'' at line 2
mysql> select week('2011-01-01'),
    -> week('2011-01-02'),
    -> week('2011-01-03');
+--------------------+--------------------+--------------------+
| week('2011-01-01') | week('2011-01-02') | week('2011-01-03') |
+--------------------+--------------------+--------------------+
|                  0 |                  1 |                  1 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> #外国人将星期日算为一周的开始 这就不符合中国人习惯
mysql>
mysql> create table sales
    -> (
    -> id int primary key auto_increment not null,
    -> date datetime not null,
    -> cost int unsigned not null
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> insert into sales(date, cost) values('2010-12-31',100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into sales(date, cost) values('2011-01-01',200);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales(date, cost) values('2011-01-02',100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into sales(date, cost) values('2011-01-06',100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into sales(date, cost) values('2011-01-10',100);
Query OK, 1 row affected (0.04 sec)

mysql> #现在我们建立了数据库  并且插入了一些数据
mysql> select * from sales;
+----+---------------------+------+
| id | date                | cost |
+----+---------------------+------+
|  1 | 2010-12-31 00:00:00 |  100 |
|  2 | 2011-01-01 00:00:00 |  200 |
|  3 | 2011-01-02 00:00:00 |  100 |
|  4 | 2011-01-06 00:00:00 |  100 |
|  5 | 2011-01-10 00:00:00 |  100 |
+----+---------------------+------+
5 rows in set (0.00 sec)

mysql> #现在看看按周分组的情况
mysql> select week(date), sum(cost)
    -> from sales
    -> group by week(date);
+------------+-----------+
| week(date) | sum(cost) |
+------------+-----------+
|          0 |       200 |
|          1 |       200 |
|          2 |       100 |
|         52 |       100 |
+------------+-----------+
4 rows in set (0.00 sec)

mysql> #我们可以看出一些问题  2010-12-31作为52周，但是在中国和2011-01-01是同一周
mysql>
mysql> select floor(datediff(date,'1900-01-01')/7) as a, sum(cost) from sales group by floor(datediff(date,'1900-01-01')/7);
+------+-----------+
| a    | sum(cost) |
+------+-----------+
| 5791 |       400 |
| 5792 |       100 |
| 5793 |       100 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> #其中 a表示1900-01-01到现在的周数
mysql> #现在进行修改
mysql>
mysql> select date_add('1900-01-01',
    -> interval floor(datediff(date,'1900-01-01')7)*7 day)
    -> as week_start,
    -> date_add('1900-01-01', interval floor(datediff(date,'1900-01-01')*7+6 day)
    -> as week_end,
    -> sum(cost)
    -> from sales;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7)*7 day)
as week_start,
date_add('1900-01-01', interval floor(datediff(date,'19' at line 2
mysql>
mysql> select
    -> date_add('1900-01-01',interval floor(datediff(date,'1900-01-01')7)*7 day) as week_start,
    -> date_add('1900-01-01',interval floor(datediff(date,'1900-01-01')*7+6 day) as week_end, sum(cost)
    -> from sales
    -> group by floor(datediff(date,'1900-01-01')/7);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7)*7 day) as week_start,
date_add('1900-01-01',interval floor(datediff(date,'190' at line 2
mysql> #shit,,,,,
mysql>
mysql> select
    -> date_add('1900-01-01',interval floor(datediff(date,'1900-01-01')/7)*7 day) as week_start,
    -> date_add('1900-01-01',interval floor(datediff(date,'1900-01-01')/7)*7 +6 day) as week_end, sum(cost)
    -> from sales
    -> group by floor(datediff(date,'1900-01-01')/7);
+------------+------------+-----------+
| week_start | week_end   | sum(cost) |
+------------+------------+-----------+
| 2010-12-27 | 2011-01-02 |       400 |
| 2011-01-03 | 2011-01-09 |       100 |
| 2011-01-10 | 2011-01-16 |       100 |
+------------+------------+-----------+
3 rows in set (0.00 sec)

mysql> #ok  
mysql> #可以看到日期包含了2010-12-31
mysql>