mysql学习之索引、视图、触发器和常用函数(二)

一、索引

索引定义：索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度； 类似于图书的目录，方便快速定位，寻找指定的内容。

优点：提高查询数据的速度；
缺点：创建和维护索引的时间增加了。

索引的分类：

1.创建索引：创建表、在表上添加、alert添加三种方式的单列和多列索引

CREATE TABLE t_user1(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX (userName)
                 );

CREATE TABLE t_user2(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     UNIQUE INDEX index_userName(userName)
                 );

CREATE TABLE t_user3(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX index_userName_password(userName,PASSWORD)
                 );

CREATE  INDEX index_userName ON t_user4(userName);

CREATE  UNIQUE INDEX index_userName ON t_user4(userName);

CREATE  INDEX index_userName_password ON t_user4(userName,PASSWORD);

ALTER TABLE t_user5 ADD INDEX index_userName(userName);

ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);

2.删除索引

DROPINDEX`这里写代码片` 索引名 ON 表名 

DROP INDEX index_userName ON t_user5;

DROP INDEX index_userName_password ON t_user5;

二、视图

1.视图的引入

1，视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。
2，数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。
3，使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

2.视图作用

1，使操作简便化；
2，增加数据的安全性；
3，提高表的逻辑独立性；

3.创建视图和查看视图

CREATE VIEW v1 AS SELECT * FROM t_book;

CREATE VIEW v2 AS SELECT bookName,price FROM t_book;

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book;


SELECT * FROM v1;

SELECT * FROM v2;

SELECT * FROM v3;

CREATE VIEW v4 AS SELECT bookName,bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;

CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id;

SELECT * FROM v4;

SELECT * FROM v5;

4.查看视图

DESC v5;

SHOW TABLE STATUS LIKE 'v5';

SHOW TABLE STATUS LIKE 't_book';

SHOW CREATE VIEW v5;

5.修改视图

SELECT  * FROM v1;

CREATE OR REPLACE VIEW v1(bookName,price) AS SELECT bookName,price FROM t_book;

ALTER VIEW v1 AS SELECT * FROM t_book;

6.更新视图

INSERT INTO v1 VALUES(NULL,'java good',120,'feng',1);

UPDATE v1 SET bookName='java very good',price=200 WHERE id=5;

DELETE FROM v1 WHERE id=5;

7.删除视图

DROP VIEW IF EXISTS v4;

三、触发器

1.触发器引入
触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。 当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

2.创建触发器
创建一个执行语句的触发器

CREATE TRIGGER trig_book AFTER INSERT 
     ON t_book FOR EACH ROW
        UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;


INSERT INTO t_book VALUES(NULL,'java好',100,'ke',1);

创建多个执行语句的触发器

DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE 
    ON t_book FOR EACH ROW
    BEGIN
       UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
       INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');
       DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
    END 
|
DELIMITER ;

DELETE FROM t_book WHERE id=5;

查看触发器和删除触发器

SHOW TRIGGERS;

DROP TRIGGER trig_book2 ;

四、mysql常用函数

SELECT CURDATE(),CURTIME(),MONTH(birthday) AS m FROM t_t;

SELECT userName,CHAR_LENGTH(userName),UPPER(userName),LOWER(userName) FROM t_t;

SELECT num,ABS(num) FROM t_t;

SELECT SQRT(4),MOD(9,4) FROM t_t;

INSERT INTO t_t VALUES(NULL,'2013-1-1','a',1,PASSWORD('123456'));

INSERT INTO t_t VALUES(NULL,'2013-1-1','a',1,MD5('123456'));

INSERT INTO t_t VALUES(NULL,'2013-1-1','a',1,MD5('123456'),ENCODE('abcd','aa'));

SELECT DECODE(pp,'aa') FROM t_t WHERE id=5;