mysql 学习---->表优化、合成索引、浮点数与定点数

1. 1.表优化  
2. mysql> use test1;  
3. Reading table information for completion of table and column names  
4. You can turn off this feature to get a quicker startup with -A  
5. Database changed  
6.   
7. mysql> alter table t rename t_old;  
8.   
9. mysql> create table t ( id varchar(100),content text);  
10.   
11. mysql> insert into t values  
12.     -> (1,repeat('haha',100)),  
13.     -> (2,repeat('test',100)),  
14.     -> (3,repeat('java',100));  
15.   
16. mysql> insert into t select * from t;  
17.   
18. mysql> alter table t rename t_optimize;  
19. 
    
20. mysql> select table_name,data_length from information_schema.tables where table_name = "t_optimize";  
21. +------------+-------------+  
22. | table_name | data_length |  
23. +------------+-------------+  
24. | t_optimize |       16384 |  
25. +------------+-------------+  
26.   
27. mysql> optimize table t_optimize;  
28.   
29. +------------------+----------+----------+-------------------------------------------------------------------+  
30. | Table            | Op       | Msg_type | Msg_text                                                          |  
31. +------------------+----------+----------+-------------------------------------------------------------------+  
32. | test1.t_optimize | optimize | note     | Table does not support optimize, doing recreate + analyze instead |  
33. | test1.t_optimize | optimize | status   | OK                                                                |  
34. +------------------+----------+----------+-------------------------------------------------------------------+  
35.   
36. mysql> select table_name,data_length from information_schema.tables where table_name = "t_optimize";  
37. +------------+-------------+  
38. | table_name | data_length |  
39. +------------+-------------+  
40. | t_optimize |       16384 |  
41. +------------+-------------+  
42.  
43. 2.合成索引  
44. mysql> create table t (id varchar(100),context blob,hash_value varchar(40));  
45.   
46. mysql> insert into t values(1,repeat('zhejiang',2),md5(context));  
47.   
48. mysql> insert into t values(1,repeat('jiangsu',2),md5(context));  
49.   
50. mysql> insert into t values(1,repeat('fujian 2008',2),md5(context));  
51.   
52. mysql> select * from t;  
53. +------+------------------------+----------------------------------+  
54. | id   | context                | hash_value                       |  
55. +------+------------------------+----------------------------------+  
56. | 1    | zhejiangzhejiang       | 8f36854efa45585f35e6ca54b7d58f31 |  
57. | 1    | jiangsujiangsu         | e2817db97256ac13805ee1ccfc1048e7 |  
58. | 1    | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |  
59. +------+------------------------+----------------------------------+  
60.   
61. mysql> select * from t ;  
62. +------+------------------------+----------------------------------+  
63. | id   | context                | hash_value                       |  
64. +------+------------------------+----------------------------------+  
65. | 1    | zhejiangzhejiang       | 8f36854efa45585f35e6ca54b7d58f31 |  
66. | 1    | jiangsujiangsu         | e2817db97256ac13805ee1ccfc1048e7 |  
67. | 1    | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |  
68. +------+------------------------+----------------------------------+  
69.   
70. mysql> select * from t  
71.     ->  where hash_value=md5(repeat('fujian 2008',2));  
72. +------+------------------------+----------------------------------+  
73. | id   | context                | hash_value                       |  
74. +------+------------------------+----------------------------------+  
75. | 1    | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |  
76. +------+------------------------+----------------------------------+  
77.   
78. mysql> desc t;  
79. +------------+--------------+------+-----+---------+-------+  
80. | Field      | Type         | Null | Key | Default | Extra |  
81. +------------+--------------+------+-----+---------+-------+  
82. | id         | varchar(100) | YES  |     | NULL    |       |  
83. | context    | blob         | YES  |     | NULL    |       |  
84. | hash_value | varchar(40)  | YES  |     | NULL    |       |  
85. +------------+--------------+------+-----+---------+-------+  
86.   
87. mysql>  create index inx_blob on t(context(100));   
88.   
89. mysql> select * from t;  
90. +------+------------------------+----------------------------------+  
91. | id   | context                | hash_value                       |  
92. +------+------------------------+----------------------------------+  
93. | 1    | zhejiangzhejiang       | 8f36854efa45585f35e6ca54b7d58f31 |  
94. | 1    | jiangsujiangsu         | e2817db97256ac13805ee1ccfc1048e7 |  
95. | 1    | fujian 2008fujian 2008 | 1efb8d993ee2cae7532407ff30b5778c |  
96. +------+------------------------+----------------------------------+  
97.   
98. mysql> desc select * from t where context like 'fujian%' \G;  
99. *************************** 1. row ***************************  
100.            id: 1  
101.   select_type: SIMPLE  
102.         table: t  
103.          type: range  
104. possible_keys: inx_blob  
105.           key: inx_blob  
106.       key_len: 103  
107.           ref: NULL  
108.          rows: 1  
109.         Extra: Using where   
110.   
111. ERROR:   
112. No query specified  
113.   
114. mysql> desc select * from t where context like '%fujian' \G;  
115. *************************** 1. row ***************************  
116.            id: 1  
117.   select_type: SIMPLE  
118.         table: t  
119.          type: ALL  
120. possible_keys: NULL  
121.           key: NULL  
122.       key_len: NULL  
123.           ref: NULL  
124.          rows: 3  
125.         Extra: Using where  
126. 1 row in set (0.00 sec)  
127.   
128. ERROR:   
129. No query specified  
130.   
131. 3.浮点数、定点数  
132. mysql> create table t(f float(8,1));   
133.   
134. mysql> desc t;  
135. +-------+------------+------+-----+---------+-------+  
136. | Field | Type       | Null | Key | Default | Extra |  
137. +-------+------------+------+-----+---------+-------+  
138. | f     | float(8,1) | YES  |     | NULL    |       |  
139. +-------+------------+------+-----+---------+-------+  
140.   
141. mysql> insert into t values(1.23456);  
142.   
143. mysql> select * from t;  
144. +------+  
145. | f    |  
146. +------+  
147. |  1.2 |  
148. +------+  
149.   
150. mysql> insert into t values(1.25456);  
151.   
152. mysql> select * from t;  
153. +------+  
154. | f    |  
155. +------+  
156. |  1.2 |  
157. |  1.3 |  
158. +------+ 
159.   
160. mysql> create table test(c1 float(10,2),c2 decimal(10,2));  
161.   
162. mysql> insert into test values(131072.32,131072.32);  
163.   
164. mysql> select * from test;  
165. +-----------+-----------+  
166. | c1        | c2        |  
167. +-----------+-----------+  
168. | 131072.31 | 131072.32 |  
169. +-----------+-----------+  
170.