GotoKnow
  • เข้าระบบ
  • สมัครสมาชิก
  • แผงจัดการ
  • ออกจากระบบ
GotoKnow

MySQL : new key feature note

Feature หลักๆ ของ MySQL 5.0 ขอ note ไว้ก่อนดีกว่า...

Stored Procedures

CODE
CREATE PROCEDURE procedure1                /* name */
(IN parameter1 INTEGER)                    /* parameters */
BEGIN                                      /* start of block */
 DECLARE variable1 CHAR(10);                /* variables */
 IF parameter1 = 17 THEN                    /* start of IF */
   SET variable1 = 'birds';                   /* assignment */
 ELSE
   SET variable1 = 'beasts';                  /* assignment */
 END IF;                                   /* end of IF */
 INSERT INTO table1 VALUES (variable1);    /* statement */
END                                       /* end of block */

Chapter 17. Stored Procedures and Functions


Triggers
CODE
mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)


CODE
mysql> CREATE PROCEDURE p ()
   -> BEGIN
   ->   /* This procedure does nothing */
   -> END;//
Query OK, 0 rows affected (0.00 sec)


CODE
 ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
 DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
 LOCK OPTIMIZE REPAIR REPLACE REVOKE
 ROLLBACK SAVEPOINT 'SELECT FROM table'
 'SET system variable' 'SET TRANSACTION'
 SHOW 'START TRANSACTION' TRUNCATE UPDATE

Chapter 18. Triggers


Views
CODE
mysql> DROP VIEW v CASCADE;
Query OK, 0 rows affected (0.00 sec)


CODE
mysql> CREATE VIEW v AS
   -> SELECT column1 AS c /* view col name is c */
   -> FROM table1;
Query OK, 0 rows affected (0.01 sec)


CODE
CREATE VIEW v AS SELECT column1 FROM t;
mysql> INSERT INTO v VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM v;
+---------+
| column1 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Chapter 19. Views

Information Schema

CODE
mysql> CREATE TABLE table1 (column1 INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v AS
   -> SELECT column1 AS c /* view col name is c */
   -> FROM table1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT table_name, table_type, engine
   -> FROM INFORMATION_SCHEMA.tables
   -> WHERE table_schema = 'tp'
   -> ORDER BY table_type ASC, table_name DESC;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| t2         | BASE TABLE | MyISAM |
| t1         | BASE TABLE | InnoDB |
| v1         | VIEW       | NULL   |
+------------+------------+--------+


Chapter 20. The INFORMATION_SCHEMA Database


Archive Storage Engine

CODE
create table test_myisam engine=myisam as select * from client_transaction_hist;
Query OK, 112050 rows affected (1.06 sec)
Records: 112050  Duplicates: 0  Warnings: 0

mysql> create table test_innodb engine=innodb as select * from client_transaction_hist;
Query OK, 112050 rows affected (3.72 sec)
Records: 112050  Duplicates: 0  Warnings: 0

mysql> create table test_archive engine=archive as select * from client_transaction_hist;
Query OK, 112050 rows affected (1.92 sec)
Records: 112050  Duplicates: 0  Warnings: 0

mysql> SELECT  table_name table_name,
   ->         engine,
   ->         ROUND(data_length/1024/1024,2) total_size_mb,
   ->         table_rows
   -> FROM   information_schema.tables
   -> WHERE  table_schema = 'gim' and
   ->        table_name like 'test%'
   -> ORDER BY 3;
+--------------+---------+---------------+------------+
| table_name   | engine  | total_size_mb | table_rows |
+--------------+---------+---------------+------------+
| test_archive | ARCHIVE |          1.64 |     112050 |
| test_myisam  | MyISAM  |          6.46 |     112050 |
| test_innodb  | InnoDB  |          9.52 |     112050 |
+--------------+---------+---------------+------------+


14.8. The ARCHIVE Storage Engine

บันทึกนี้เขียนที่ GotoKnow โดย 

คำสำคัญ (keywords): database
หมายเลขบันทึก: 18911
เขียน:
แก้ไข:
อ่าน:
สัญญาอนุญาต: สงวนสิทธิ์ทุกประการ

ความเห็น (0)