mysql的使用17

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>
mysql> insert into xx select 'a','b';
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into xx select 'b',NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into xx select 'c','d';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into xx select 'c','d';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into xx select 'c','d';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into xx select 'c','c';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into xx select 'e','f';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> insert into yy select 'a','b';
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into yy select 'b',NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into yy select 'c','d';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into yy select 'c','c';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> #先用别的方法做
mysql> select distinct x.a, x.b from x
    -> left join y
    -> on x.a=y.a and x.b=y.b
    -> where y.a is NULL and y.b is NULL;
ERROR 1054 (42S22): Unknown column 'x.b' in 'field list'
mysql> select distinct xx.a, xx.b from xx left join yy on xx.a=yy.a and xx.b=yy.b where yy.a is NULL and yy.b is NULL;
+------+------+
| a    | b    |
+------+------+
| b    | NULL |
| e    | f    |
+------+------+
2 rows in set (0.00 sec)

mysql> select distinct * from x
    -> where not exists(
    -> select * from y where x.a = y.a);
+------+
| a    |
+------+
| c    |
+------+
1 row in set (0.00 sec)

mysql> select distinct * from xx where not exists( select * from yy where xx.a = yy.a and xx.b = yy.b);
+------+------+
| a    | b    |
+------+------+
| b    | NULL |
| e    | f    |
+------+------+
2 rows in set (0.00 sec)

mysql> #但是这两种方法是有问题的 NULL是不可知的
mysql>
mysql> select * from (select distinct 'x' as source,a,b from x union all
    -> select siftinct 'Y',a,b from yy)as A froup by a,b having count(*)=1 and A.source='X';
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 'froup by a,b having count(*)=1 and A.source='X'' at line 2
mysql>
mysql>
mysql> select * from (select distinct 'X' as source,a,b from xx union all  select distinct 'Y',a,b from yy)as A group by a,b having count(*)=1 and A.source='X';
+--------+------+------+
| source | a    | b    |
+--------+------+------+
| X      | e    | f    |
+--------+------+------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select * from xx;
+------+------+
| a    | b    |
+------+------+
| a    | b    |
| a    | b    |
| b    | NULL |
| c    | d    |
| c    | d    |
| c    | d    |
| c    | c    |
| e    | f    |
+------+------+
8 rows in set (0.00 sec)

mysql> select * from yy;
+------+------+
| a    | b    |
+------+------+
| a    | b    |
| b    | NULL |
| c    | d    |
| c    | c    |
+------+------+
4 rows in set (0.00 sec)

mysql> select M.a, M.b
    -> from (
    -> select a,b
    -> max(case when source='X' then cnt else 0 end) as xcnt,
    -> max(case when source='Y' then cnt else 0 end) as ycnt from
    -> (
    -> select distinct 'X' as source,a,b,count(*) as cnt from xx
    -> group by a,b
    -> union all
    -> select distinct 'Y' as source,a,b,count(*) from yy
    -> group by a,b
    -> ) as P
    -> group by a,b
    -> ) as M
    -> join nums
    -> where nums.a <= xcnt-ycnt;
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 'max(case when source='X' then cnt else 0 end) as xcnt,
max(case when source='Y' ' at line 4
mysql>
mysql>
mysql>
mysql> select M.a, M.b
    -> from (
    -> select a,b,
    -> max(case when source='X' then cnt else 0 end) as xcnt,
    -> max(case when source='Y' then cnt else 0 end) as ycnt from
    -> (
    -> select distinct 'X' as source,a,b,count(*) as cnt from xx
    -> group by a,b
    -> union all
    -> select distinct 'Y' as source,a,b,count(*) from yy
    -> group by a,b
    -> ) as P
    -> group by a,b
    -> ) as M
    -> join nums
    -> where nums.a <= xcnt-ycnt;
ERROR 1146 (42S02): Table 'test.nums' doesn't exist
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| MonthlyOrders  |
| Nums           |
| a              |
| animals        |
| charTest       |
| customers      |
| dept_manager   |
| emp            |
| employees      |
| g              |
| mintable       |
| new_emp        |
| orders         |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| ttt            |
| updatetime     |
| x              |
| xx             |
| y              |
| yeartest       |
| yy             |
| z              |
+----------------+
27 rows in set (0.00 sec)

mysql> desc Nums;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| a     | int(10) unsigned | NO   | PRI | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select M.a, M.b from ( select a,b, max(case when source='X' then cnt else 0 end) as xcnt, max(case when source='Y' then cnt else 0 end) as ycnt from ( select distinct 'X' as source,a,b,count(*) as cnt from xx group by a,b union all select distinct 'Y' as source,a,b,count(*) from yy  group by a,b ) as P group by a,b ) as M join nums where Nums.a <= xcnt-ycnt;
ERROR 1146 (42S02): Table 'test.nums' doesn't exist
mysql>
mysql> select M.a, M.b from ( select a,b, max(case when source='X' then cnt else 0 end) as xcnt, max(case when source='Y' then cnt else 0 end) as ycnt from ( select distinct 'X' as source,a,b,count(*) as cnt from xx group by a,b union all select distinct 'Y' as source,a,b,count(*) from yy  group by a,b ) as P group by a,b ) as M join Nums where Nums.a <= xcnt-ycnt;
+------+------+
| a    | b    |
+------+------+
| a    | b    |
| c    | d    |
| c    | d    |
| e    | f    |
+------+------+
4 rows in set (0.00 sec)

mysql> #ok 进行操作
mysql>
mysql> #聚合函数
mysql> drop table z;
Query OK, 0 rows affected (0.05 sec)

mysql> create table z(a int, b int);
Query OK, 0 rows affected (0.11 sec)

mysql> create into z select 1,200;
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 'into z select 1,200' at line 1
mysql> insert into z select 1,200;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into z select 1,100;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into z select 1,100;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into z select 2,400;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into z select 2,500;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into z select 3,NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from z;
+------+------+
| a    | b    |
+------+------+
|    1 |  200 |
|    1 |  100 |
|    1 |  100 |
|    2 |  400 |
|    2 |  500 |
|    3 | NULL |
+------+------+
6 rows in set (0.00 sec)

mysql> select a, group_concat(b) from z group by a;
+------+-----------------+
| a    | group_concat(b) |
+------+-----------------+
|    1 | 200,100,100     |
|    2 | 400,500         |
|    3 | NULL            |
+------+-----------------+
3 rows in set (0.00 sec)

mysql> select a, group_concat(distinct b order by b desc separator ':') from z group by a;
+------+--------------------------------------------------------+
| a    | group_concat(distinct b order by b desc separator ':') |
+------+--------------------------------------------------------+
|    1 | 200:100                                                |
|    2 | 500:400                                                |
|    3 | NULL                                                   |
+------+--------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> #聚合的算法
mysql> desc orders;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| order_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| customer_id | varchar(10) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select hex(12);
+---------+
| hex(12) |
+---------+
| C       |
+---------+
1 row in set (0.00 sec)

mysql> select hex(120);
+----------+
| hex(120) |
+----------+
| 78       |
+----------+
1 row in set (0.00 sec)

mysql> #主要的算法是附加属性的聚合
mysql> #连续聚合 积累聚合 滑动聚合
mysql> #聚合的一些方法就是这些  自己可以深入摸索下
mysql>
mysql>
mysql> exit