Learning SQL3

CASE 
   WHEN  employee.title='Head Teller'
     THEN 'Head Teller'
   WHEN employee.title='Teller'
     AND YEAR(employee.start_date)>2007
     THEN 'Teller TRAINEE'
   WHEN employee.title='Teller'
     AND YEAR(employee.start_date)<2006
     THEN 'Experienced Teller'
   WHEN employee.title='Teller'
      THEN ‘Teller’
 ELSE 'Non-Teller'
END IF

Learning SQL3
条件查询
case 表达式

IF  ELSE
IF<>...
END IF
ELSE ...
END IF 　


索引
1.创建索引  ADD INDEX

mysql> ALTER TABLE student_list
    -> ADD INDEX student_id(student_id);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW INDEX FROM student_list;
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student_list |          1 | student_id |            1 | student_id  | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


2.唯一索引ADD UNIQUE
3.多列索引 ADD INDEX index_name(lname,fname)


视图---一种简单的数据查询机制
创建视图
mysql>  CREATE VIEW student_list_vw
    ->  (student_id,fname,lname,tel)
    ->  AS
    -> SELECT   concat('ends in',substr(tel,8,4)) tel ,
    ->  student_id,fname,lname
    ->  FROM student_list;
Query OK, 0 rows affected (0.12 sec)


mysql> SELECT student_id,fname,lname,tel
    -> FROM student_list_vw;
+-------------+------------+-------+---------+
| student_id  | fname      | lname | tel     |
+-------------+------------+-------+---------+
| ends in8754 | 6100410004 | chen  | xueping |
| ends in3411 | 6100410007 | Gao   | Wei     |
| ends in0424 | 6100410014 | Li    | Xi      |
| ends in7983 | 6100410019 | Luo   | Haitao  |
+-------------+------------+-------+---------+
4 rows in set (0.02 sec)
为什么使用视图？？
数据安全、数据聚合、隐藏复杂性、连接分区数据