新哥傳奇

2015年2月28日星期六

[SQL] View

View

如果在資料庫的應用中，出現很常執行的查詢敘述時，你可以在MySQL資料庫中建立一種「View」元件，
View元件用來保存一段你指定的查詢敘述：
建立好需要的View元件以後，除了有一些限制外，
它使用起來就像是一個表格也有很多人稱「View」元件是一種「虛擬表格」，
因為它不是一個真正儲存紀錄資料的表格，
可是它又跟表格的用法類似。
所以如果有需要的話，你也可以使用View元件回傳的紀錄資料，
執行統計、分組與其它需要的處理：

下列是MySQL關於View元件的規定與限制：

在同一個資料庫中，View的名稱不可以重複，也不可以跟表格名稱一樣 View不可以跟Triggers建立聯結儲存在View中的查詢敘述也有下列的規定：查詢敘述中只能使用到已存在的表格或View 「FROM」子句中不可以使用子查詢不可以使用「TEMPORARY」表格不可以使用自行定義的變數、Procedure與Prepared statement參數註：「TEMPORARY」表格在「表格與索引、建立表格、建立暫存表格」中討論。「Triggers」、定義變數、「Procedure」與「Prepared statement」在後面都會有章節詳細的討論。

參考來源

新增view

mysql> create view testtable2View as select* from testtable2 order by car;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from testtable2View ;
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  4 | jim   | sw   | 7     |   6 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
+----+-------+------+-------+-----+-------+
4 rows in set (0.00 sec)

修改view

mysql> alter view testtable2View as select* from testtable2 order by price;
Query OK, 0 rows affected (0.00 sec)

mysql> select* from testtable2View ;
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
|  4 | jim   | sw   | 7     |   6 | NULL  |
+----+-------+------+-------+-----+-------+
4 rows in set (0.00 sec)

刪除view

mysql> drop view if exists testtable2View;
Query OK, 0 rows affected (0.00 sec)
mysql> select* from testtable2View ;
ERROR 1146 (42S02): Table 'testdb.testtable2View' doesn't exist

檢查view的狀態

check table testtable2View;
注意:不是: check view testtable2View;

select* from information_schema.views;

[SQL] subquery | 子查詢

select, where , from, 等語句都可以內嵌子查詢的語法.
括號裡面的回傳欄位只能是一個

mysql> select* from testtable2 where price  > (select price from testtable2 where name = 'leo' ) ;
+----+------+------+-------+-----+-------+
| id | name | job  | price | car | title |
+----+------+------+-------+-----+-------+
|  4 | jim  | sw   | 7     |   6 | NULL  |
+----+------+------+-------+-----+-------+
1 row in set (0.00 sec)


mysql> select* from testtable2 where name in ('lewis','leo' ) ;                     
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
+----+-------+------+-------+-----+-------+
3 rows in set (0.00 sec)

[SQL] Alter table | 修改資料表

修改table 屬性:

mysql> alter table testtable2 CHARSET = utf8;
看修改後的結果:
mysql> show create table testtable2;

| testtable2 | CREATE TABLE `testtable2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(35) CHARACTER SET latin1 NOT NULL,
  `price` char(35) CHARACTER SET latin1 NOT NULL,
  `car` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

查看索引資訊:

mysql> show index from testtable2;

增加一個欄位

mysql> alter table testtable2 add job varchar(20) default 'sw'after name;
mysql> select* from testtable2;                             
+----+-------+------+-------+-----+
| id | name  | job  | price | car |
+----+-------+------+-------+-----+
|  1 | lewis | sw   | 2     |   2 |
|  2 | lewis | sw   | 3     | 111 |
|  3 | leo   | sw   | 4     | 123 |
|  4 | jim   | sw   | 7     |   6 |
+----+-------+------+-------+-----+
4 rows in set (0.00 sec)

增加多個欄位

mysql> alter table testtable2 add (job2 varchar(20) , year varchar(20) );
mysql> select* from testtable2;                             
+----+-------+------+-------+-----+------+------+
| id | name  | job  | price | car | job2 | year |
+----+-------+------+-------+-----+------+------+
|  1 | lewis | sw   | 2     |   2 | NULL | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL | NULL |
+----+-------+------+-------+-----+------+------+
4 rows in set (0.00 sec)

修改欄位:
1.CHANGE 可以修改欄位的名稱與定義.
2.MODIFY 只能修改欄位的定義，不能修改欄位名稱.
一般來說會覺得change涵蓋兩種功能所以我們用change就好，
MODIFY可以防止不小心改到欄位名的狀況.

CHANGE

mysql> alter table testtable2 change job2 title varchar(30);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;                             
+----+-------+------+-------+-----+-------+------+
| id | name  | job  | price | car | title | year |
+----+-------+------+-------+-----+-------+------+
|  1 | lewis | sw   | 2     |   2 | NULL  | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL  | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL  | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL  | NULL |
+----+-------+------+-------+-----+-------+------+
4 rows in set (0.00 sec)

MODIFY

mysql> alter table testtable2 modify year int(10) after title;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;                            
+----+-------+------+-------+-----+-------+------+
| id | name  | job  | price | car | title | year |
+----+-------+------+-------+-----+-------+------+
|  1 | lewis | sw   | 2     |   2 | NULL  | NULL |
|  2 | lewis | sw   | 3     | 111 | NULL  | NULL |
|  3 | leo   | sw   | 4     | 123 | NULL  | NULL |
|  4 | jim   | sw   | 7     |   6 | NULL  | NULL |
+----+-------+------+-------+-----+-------+------+
4 rows in set (0.00 sec)

刪除其中一個欄位

mysql> alter table testtable2 drop year;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select* from testtable2;
+----+-------+------+-------+-----+-------+
| id | name  | job  | price | car | title |
+----+-------+------+-------+-----+-------+
|  1 | lewis | sw   | 2     |   2 | NULL  |
|  2 | lewis | sw   | 3     | 111 | NULL  |
|  3 | leo   | sw   | 4     | 123 | NULL  |
|  4 | jim   | sw   | 7     |   6 | NULL  |
+----+-------+------+-------+-----+-------+
4 rows in set (0.01 sec)

[Java] Use recursive way to print complex data structure type until primitive data type.

As title , the ALHM(hash map in array list ) or HMAL(arraylist in hash map)
is my favorite data structure ,
but sometime if there are many content in that,
you may not see clearly from your print,
So i provide a simple way so print data clearly.

is also support ALHM(HMAL(ALHM)), nest data structure .

your data may like this:

[
  {
    key1:[item1,item2,.....],
    key2:[item1,item2,.....],
    key3:[item1,item2,.....],
    key4:[item1,item2,.....],
    key5:[item1,item2,.....],
    key6:[item1,item2,.....],
    key7:[item1,item2,.....]
  },
  
  {
    key1:[item1,item2,.....],
    key2:[item1,item2,.....],
    key3:[item1,item2,.....],
    key4:[item1,item2,.....],
    key5:[item1,item2,.....],
    key6:[item1,item2,.....],
    key7:[item1,item2,.....]
  },
  
  {
    key1:[item1,item2,.....],
    key2:[item1,item2,.....],
    key3:[item1,item2,.....],
    key4:[item1,item2,.....],
    key5:[item1,item2,.....],
    key6:[item1,item2,.....],
    key7:[item1,item2,.....]
  },
  
]

here is code.

ViewDat.java

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.acer.util.DataUtil;


public class ViewDat {

 public boolean viewDat(String fileName) {

  File file = new File(fileName);
  if (file.exists()) {
   Object object = DataUtil.DeSerialization(fileName);
   if (object instanceof Map) {
    System.out.println(fileName + " is Map");
    getMapContent((Map)object);
   } else if (object instanceof List) {
    System.out.println(fileName + " is List");
    getListContent((List)object);
   } else {
    System.out.println(fileName + " only support map and list");
   }
  } else {
   System.out.println(fileName + " is not exist");
  }

  return true;
 }
 
 public void getMapContent(Map map ){
  
  Iterator keyIt = map.keySet().iterator();
     
  while (keyIt.hasNext()) {

   String key = keyIt.next();
   
   Object object = map.get(key);

   if (null!= object && ! DataUtil.isPrimitiveType(object.getClass())) {
    System.out.println("==="+key+"===");
   }
   
   recursive2Primitive(object,key);
  }
 }
 
 public void getListContent(List list){
  
  for(int i = 0 ; i < list.size() ; i ++  ){

   Object object = list.get(i);
   System.out.println("===================");
   recursive2Primitive(object,null);
   
   
  }
  
 }
 
 public void recursive2Primitive(Object object, String key){
  
  if (null != object && object instanceof Map) {

   getMapContent((Map) object);

  } else if (null != object && object instanceof List) {

   getListContent((List) object);

  } else if (null != object && DataUtil.isPrimitiveType(object.getClass())) {

   System.out.println(key+" : "+object.toString());

  } else if(null != object){

   System.out.println("not support this type, content is "
     + object.toString());

  }else{
   System.out.println("=============================== ");
   System.out.println("content is null, can't print ");
   System.out.println("=============================== ");
  }
  
 }

}

2015年2月26日星期四

[java] Check primitive data type

you can modify this method to what the type you need. at line 45~55

package test_fb_api;

import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

public class testCheckPrimitive {

 public static void main(String[] args) {

  java.util.regex.Pattern pattern = java.util.regex.Pattern.compile(".*[^0-9].*");
  
  int a =0 ;
  double id = 0.0;
  String s = "111";
  Double d = 1.0;
  Map map = new HashMap();
  
  asignVar(a);
  asignVar(id);
  asignVar(s);
  asignVar(d);
  asignVar(map);
  
 }

 public static void asignVar(Object obj){
  
  Object o = obj;
  System.out.println(o.getClass());
  System.out.println(isWrapperType(o.getClass()));
  System.out.println("=========================");
  
 }
 private static final Set <Class<?>> WRAPPER_TYPES = getWrapperTypes();
 
  public static boolean isWrapperType(Class<?> clazz)
     {
         return WRAPPER_TYPES.contains(clazz);
     }
  
  private static Set<Class<?>> getWrapperTypes()
     {
         Set<Class<?>> ret = new HashSet<Class<?>>();
         ret.add(Boolean.class);
         ret.add(Character.class);
         ret.add(Byte.class);
         ret.add(Short.class);
         ret.add(Integer.class);
         ret.add(Long.class);
         ret.add(Float.class);
         ret.add(Double.class);
         ret.add(Void.class);
         ret.add(String.class);
         return ret;
     }
  
}