一、索引
索引定义:索引是由数据库表中一列或者多列组合而成,其作用是提高对表中数据的查询速度; 类似于图书的目录,方便快速定位,寻找指定的内容。
优点:提高查询数据的速度;
缺点:创建和维护索引的时间增加了。
索引的分类:
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;