mysql的使用13

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| Nums           |
| a              |
| animals        |
| charTest       |
| customers      |
| dept_manager   |
| employees      |
| new_emp        |
| orders         |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| ttt            |
| updatetime     |
| yeartest       |
| z              |
+----------------+
19 rows in set (0.00 sec)

mysql> #现在 我们进分区的讲解
mysql> #分区是通过在集合内部进行的分组操作，再对集合添加分区列来实现的
mysql> #在子查询的解决方案中， 通过子查询内部添加相关性 并匹配内部表和外部表的分区来实现分区
mysql> #现在对dept_manager进行分区操作  按dept_no分区 并按照emp_no升序进行分区统计
mysql>
mysql> desc dept_manager;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| dept_no    | varchar(10) | NO   |     | NULL    |       |
| emp_no     | int(11)     | NO   |     | NULL    |       |
| birth_date | date        | YES  |     | NULL    |       |
| worktime   | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> select dept_no, emp_no,(select count(*) from dept_manager as s2
    -> where s1.dept_no = s2.dept_no and s2.emp_no <=s1.emp_no) as rownum
    -> from dept_manager as s1
    -> order by dept_no, emp_no;
+---------+--------+--------+
| dept_no | emp_no | rownum |
+---------+--------+--------+
| d001    | 110022 |      1 |
| d001    | 110039 |      2 |
| d002    | 110085 |      1 |
| d002    | 110114 |      2 |
| d003    | 110183 |      1 |
| d003    | 110228 |      2 |
| d004    | 110303 |      1 |
| d004    | 110344 |      2 |
| d004    | 110386 |      3 |
| d004    | 110420 |      4 |
| d005    | 110511 |      1 |
| d005    | 110567 |      2 |
| d006    | 110725 |      1 |
| d006    | 110765 |      2 |
| d006    | 110800 |      3 |
| d006    | 110854 |      4 |
| d007    | 111035 |      1 |
| d007    | 111133 |      2 |
| d008    | 111400 |      1 |
| d008    | 111534 |      2 |
| d009    | 111692 |      1 |
| d009    | 111784 |      2 |
| d009    | 111877 |      3 |
| d009    | 111939 |      4 |
+---------+--------+--------+
24 rows in set (0.00 sec)

mysql> #可以看到 所有的都进行分区了
mysql> select * from dept_manager order by dept_no;
+---------+--------+------------+------------+
| dept_no | emp_no | birth_date | worktime   |
+---------+--------+------------+------------+
| d001    | 110022 | 1985-01-01 | 1991-10-01 |
| d001    | 110039 | 1991-10-01 | 9999-01-01 |
| d002    | 110085 | 1985-01-01 | 1989-12-17 |
| d002    | 110114 | 1989-12-17 | 9999-01-01 |
| d003    | 110183 | 1985-01-01 | 1992-03-21 |
| d003    | 110228 | 1992-03-21 | 9999-01-01 |
| d004    | 110420 | 1996-08-30 | 9999-01-01 |
| d004    | 110386 | 1992-08-02 | 1996-08-30 |
| d004    | 110344 | 1988-09-09 | 1992-08-02 |
| d004    | 110303 | 1985-01-01 | 1988-09-09 |
| d005    | 110511 | 1985-01-01 | 1992-04-25 |
| d005    | 110567 | 1992-04-25 | 9999-01-01 |
| d006    | 110854 | 1994-06-28 | 9999-01-01 |
| d006    | 110800 | 1991-09-12 | 1994-06-28 |
| d006    | 110765 | 1989-05-06 | 1991-09-12 |
| d006    | 110725 | 1985-01-01 | 1989-05-06 |
| d007    | 111035 | 1985-01-01 | 1991-03-07 |
| d007    | 111133 | 1991-03-07 | 9999-01-01 |
| d008    | 111534 | 1991-04-08 | 9999-01-01 |
| d008    | 111400 | 1985-01-01 | 1991-04-08 |
| d009    | 111692 | 1985-01-01 | 1988-10-17 |
| d009    | 111784 | 1988-10-17 | 1992-09-08 |
| d009    | 111877 | 1992-09-08 | 1996-01-03 |
| d009    | 111939 | 1996-01-03 | 9999-01-01 |
+---------+--------+------------+------------+
24 rows in set (0.00 sec)

mysql> #第二个应用 最小值缺失问题
mysql> create table x(a int unsigned primary key,b char(1) not null)engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert int x values (3,'a'),(4,'b'),(6,'c'),(7,'d');
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 'int x values (3,'a'),(4,'b'),(6,'c'),(7,'d')' at line 1
mysql> insert int x values (3,'a');
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 'int x values (3,'a')' at line 1
mysql> insert into x values (3,'a'),(4,'b'),(6,'c'),(7,'d');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from x;
+---+---+
| a | b |
+---+---+
| 3 | a |
| 4 | b |
| 6 | c |
| 7 | d |
+---+---+
4 rows in set (0.00 sec)

mysql> #现在索引应该从一开始  但是没1
mysql> select min(a)+1 as missing
    -> from x as a
    -> where not exists (select * from x as b where a.a+1=b.a);
+---------+
| missing |
+---------+
|       5 |
+---------+
1 row in set (0.01 sec)

mysql> #not exists弄成连续的了
mysql> select
    -> case
    -> when not exists (select a from x where a=1) then 1
    -> else
    -> (select min(a)+1 as missing from x as a where not exists(
    -> select * from x as b where a.a+1=b.a))
    -> end as missing
    -> ;
+---------+
| missing |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> #ok  利用语句判断下即可
mysql> insert into x values (1,'x'),(2,'y');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select  case when not exists (select a from x where a=1) then 1 else (select min(a)+1 as missing from x as a where not exists( select * from x as b where a.a+1=b.a)) end as missing;
+---------+
| missing |
+---------+
|       5 |
+---------+
1 row in set (0.00 sec)

mysql> #现在对最小值进行补缺的操作  例如5 将数据补为y
mysql> insert into x select  case when not exists (select a from x where a=1) then 1 else (select min(a)+1 as missing from x as a where not exists( select * from x as b where a.a+1=b.a)) end as missing 'y';
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 ''y'' at line 1
mysql> insert into x select  case when not exists (select a from x where a=1) then 1 else (select min(a)+1 as missing from x as a where not exists( select * from x as b where a.a+1=b.a)) end as missing,'y';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from x;
+---+---+
| a | b |
+---+---+
| 1 | x |
| 2 | y |
| 3 | a |
| 4 | b |
| 5 | y |
| 6 | c |
| 7 | d |
+---+---+
7 rows in set (0.00 sec)

mysql> #第三个应用  进行缺失范围和连续范围
mysql>
mysql> create table g(a int);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into g values (1),(2),(3),(100),(101),(103),(104),(105),(106);
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from g;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|  100 |
|  101 |
|  103 |
|  104 |
|  105 |
|  106 |
+------+
9 rows in set (0.00 sec)

mysql> #现在找出缺失的连续范围缺失的终点
mysql> select a from g as A
    -> where not exists (select * from g as B where A.a+1=B.a);
+------+
| a    |
+------+
|    3 |
|  101 |
|  106 |
+------+
3 rows in set (0.00 sec)

mysql> #此处利用的判断是终点前一位的判断
mysql>
mysql> select a+1 as start_range, (select min(a)-1 from g as C where C.a>A.a) as end_range from g as A where not exists
    -> (select * from g as B where A.a+1=B.a) and a<(select max(a) from g);
+-------------+-----------+
| start_range | end_range |
+-------------+-----------+
|           4 |        99 |
|         102 |       102 |
+-------------+-----------+
2 rows in set (0.00 sec)

mysql> #即可求出中间空格的多少
mysql>
mysql>
mysql> #现在求出每个分组的最大值
mysql>
mysql> select a,(select min(a) from g as A where not exists
    -> (select * from g as B
    -> where A.a+1 = B.a) and A.a >= C.a) as max from g as C;
+------+------+
| a    | max  |
+------+------+
|    1 |    3 |
|    2 |    3 |
|    3 |    3 |
|  100 |  101 |
|  101 |  101 |
|  103 |  106 |
|  104 |  106 |
|  105 |  106 |
|  106 |  106 |
+------+------+
9 rows in set (0.01 sec)

mysql> #即可求出每个分组的最大值
mysql>
mysql> exit