一、创建和查看索引
1、创建和查看普通索引
普通索引:创建索引时,不附加任何限制条件(唯一、非空等限制)。该类型的索引可以创建在任何数据类型的字段上。
(1)创建表时创建普通索引
【语法】
create table table_name(
属性名1 数据类型,
属性名2 数据类型,
属性名3 数据类型,
index|key 索引名(属性名1 【(长度)】 【ASC|DESC】)
);
【实例】
#选择数据库
mysql> use test;
#创建索引
mysql> create table t_deptest(
-> deptno int,
-> dname varchar(20),
-> index index_deptno(deptno)
-> );
#校验索引是否创建成功
mysql> show create table t_deptest \G
#校验索引是否被使用
mysql> explain select * from t_deptest where deptno=1\G
注:\G后边不跟分号,否则会有no query specified错误
(2)在已经存在的表上创建普通索引
【语法】
create index 索引名 on 表名(属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> create index index_deptno on t_dept(deptno);
(3)通过SQL语句ALTER TABLE 创建普通索引
【语法】
alter table table_name add index 索引名(属性名 【(长度)】 【ASC|DESC】)
【实例】
mysql> alter table t_dept add index index_deptno(deptno);
2、创建和查看唯一索引
所谓唯一索引,就是在创建索引时,限制索引的值必须是唯一的,通过该类型的索引可以更快速的查询某条记录。根据创建索引方式,可以分为自动索引和手动索引两种。
- 自动索引:在数据库中设置完整性约束时,该表会被自动创建索引;
- 手动索引:手动在表上创建索引;
(1)创建表时创建唯一索引
【语法】
create table table_name(
属性名 数据类型,
属性名 数据类型,
unique index 索引名(属性名 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> create table t_dept1(
-> deptno int unique,
-> dname varchar(20),
-> unique index index_deptno(deptno)
-> );
(2)在已经存在的表上创建唯一索引
【语法】
create unique index 索引名 on 表名(属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> create unique index index_deptno on t_dept1 (deptno) ;
(3)通过SQL语句ALTER TABLE 创建唯一索引
【语法】
alter table table_name
add unique index|key 索引名(属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> alter table t_dept add unique index index_deptno(deptno);
3、创建和查看全文索引
全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上,以便能够快速查询数据量较大的字符串类型的字段。只能在存储引擎为MyISAM的数据库表上创建全文索引。
(1)创建表时创建全文索引
【语法】
create table table_name(
属性名1 数据类型,
属性名2 数据类型,
属性名3 数据类型,
fulltext index|key 索引名(属性名 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> create table t_dept3(
-> deptno int,
-> dname varchar(20),
-> fulltext index index_dname(dname)
-> ) ENGINE=MyISAM;
(2)在已经存在的表上创建全文索引
【语法】
create fulltext index 索引名 on 表名 (属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> create fulltext index index_name on t_dept3(dname);
(3)通过SQL语句ALTER TABLE 创建全文索引
【语法】
alter table table_name
add fulltext index 索引名(属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> alter table t_dept3 add fulltext index index_doname(dname);
4、创建和查看多列索引
所谓多列索引,是指在创建索引时,所关联的字段不是一个字段,而是多个字段。虽然可以通过所关联的字段查询,但是只有查询条件中使用了所关联字段中的第一个字段,多列索引才会被使用。
(1)创建表时创建多列索引
【语法】
create table table_name(
属性名 数据类型,
属性名 数据类型,
index|key 索引名 (属性名1 【(长度)】 【ASC|DESC】,
…………
属性名m 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> create table t_dept4(
-> deptno int,
-> dname varchar(20),
-> loc varchar(20),
-> key index_dname_loc(dname,loc)
-> );
(2)在已经存在的表上创建多列索引
【语法】
create index 索引名 on 表名(属性名1 【(长度)】 【ASC|DESC】,
…………
属性名m 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> create index index_dname_loc on t_dept4(dname,loc);
(3)通过SQL语句ALTER TABLE 创建全文索引
【语法】
alter table table_name add index|key 索引名(属性名1 【(长度)】 【ASC|DESC】,
…………
属性名m 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> alter table t_dept4 add index index_dname_loc(dname,loc);
5、删除索引
【语法】
drop index index_name on table_name
【实例】
mysql> drop index index_dname_loc on t_dept;
二、视图的操作
1、创建视图
【语法】
create view view_name as 查询语句
【实例】
mysql> create view view_employee select empno,ename from t_employee;
2、创建各种视图
(1)封装实现查询常量语句的视图,即所谓的常量视图
mysql> create view view_test1 as select 3.14;
(2)封装使用聚合函数(SUM、MIN、MAX、COUNT等)查询语句的视图;
mysql> create view v_student as select count(name) from t_student;
(3)封装了实现排序功能(ORDER BY)查询语句的视图
mysql> create view view_student as select name from t_student order by id desc;
(4)封装了实现表内连接查询语句的视图
mysql> create view v_test2
-> as
-> select s.name from t_student as s,t_group as g
-> where s.group_id=g.id and g.id=2;
(5)封装了实现表外连接(left join和right join)查询语句的视图
mysql> create view v_test3
-> as
-> select s.name
-> from t_student as s left join t_group as g on s.group_id=g.id
-> where g.id=2;
(6)封装了实现子查询相关查询语句的视图
mysql> create view v_test5
-> as
-> select s.name
-> from t_student as s where s.group_id in (select id from t_group);
(7)封装了实现记录联合(union和union all)查询语句的视图
mysql> create view v_test6
-> as
-> select id,name from t_student
-> union all
-> select id,name from t_group;
3、查看视图
- show tables语句查看视图
mysql> show tables;
- show tables status查看视图详细信息
mysql> show table status from 数据库名\G
- show tables status返回的字段含义
- 设置like参数查看某一个具体表或者视图的详细信息
mysql> show table status from test like "表名|视图名"\G
- show create view语句查看视图定义信息
mysql> show create view 视图名 \G
- describe|desc 语句查看视图设计信息
mysql> describe|desc 视图名;
- 通过系统表查看视图信息
#选择数据库information_schema
mysql> use information_schema;
#查看表views里的数据信息
mysql> select * from views where table_name="字段table_name值"\G
4、删除视图
【语法】
drop view view_name 【,view_name1】……
【实例】
mysql> drop view v_test2,v_test3;
5、修改视图
(1)create or replace view语句修改视图
【语法】
create or replace view view_name as 查询语句
【实例】
mysql> create or replace view v_test4
-> as
-> select job from t_employee;
(2)alter语句修改视图
【语法】
alter view view_name as 查询语句
【实例】
mysql> alter view v_test4
-> as
-> select ename from t_employee;
6、利用视图操作基本表
(1)检索(查询)数据
mysql> select * from 视图名;
(2)利用视图操作基本表数据
- 添加数据操作
mysql> insert into view_employee (empno,ename) values(2,3);
- 删除数据操作
mysql> delete from view_employee where ename='3';
- 更新数据操作
mysql> update view_employee set ename=4 where empno=3;
注意:
对视图数据进行添加和删除操作直接影响基本表
视图来自多个表时,不允许删除和添加数据
三、触发器的操作
触发器是由事件来触发某个操作,这些事件包括insert语句、update语句和delete语句。当数据库系统执行这些事件时,会激活触发器执行相应操作。MySQL从5.0.2开始支持触发器。使用触发器可以保证某些操作之间的一致性。
1、创建触发器
(1)创建有一条执行语句的触发器
【语法】
create trigger 触发器名 before|after 触发事件 on 表名 for each row 执行语句;
- before和after参数指定触发器执行的时间;
- 触发事件包括insert、update和delete;
- for each row表示任何一条记录上的操作满足触发条件都会触发触发器;
- 执行语句指触发器被触发后执行的程序。
【实例】创建触发器tri_diarytime,当向部门表t_dept中插入任意一条数据时,就会在插入操作之前向表t_diary中插入当前的事件记录。
- 创建t_dept和t_diary表
Database changed
mysql> create table t_dept(
-> deptno int,
-> dname varchar(20),
-> loc varchar(40)
-> );
mysql> desc t_dept;
mysql> create table t_diary(
-> diaryno int primary key auto_increment,
-> tablename varchar(20),
-> diarytime datetime
-> );
mysql> desc t_diary;
- 创建触发器tri_diarytime
mysql> create trigger tri_diarytime
-> before insert
-> on t_dept for each row
-> insert into t_diary values(null,'t_dept',now());
- 插入记录校验触发器
mysql> insert into t_dept values(1,'test','shangxi');
mysql> select * from t_diary;
(2)创建包含多条执行语句的触发器
【语法】
create trigger 触发器名 before|after 触发事件 on 表名 for each row begin 执行语句列表 end;
【实例】创建触发器tri_diarytime2,当向部门表t_dept中插入任意一条数据时,就会在插入操作之后向表t_diary中插入两条记录。
注意:
- 一般情况下,MySQL默认以’;‘结束执行语句。在创建触发器过程中需要用到该符号。在执行语句列表也用’;'分隔执行语句,因此可使用delimiter修改结束符号“;”。
- MySQL中,一个表在相同触发时间的相同触发事件只能创建一个触发器。如触发事件insert,触发时间为after的触发器只有一个。
- MySQL中,触发器执行的顺序是before触发器、表操作(insert、update和delete)和after触发器。
mysql> delimiter $$
mysql> create trigger tir_diarytime2
-> after insert on t_dept for each row
-> begin
-> insert into t_diary values(null,'t_dept',now());
-> insert into t_diary values(null,'t_dept',now());
-> end
-> $$
mysql> insert into t_dept values(2,'test','shangxi');
mysql> select * from t_diary;
mysql> delimiter ;;
2、查看触发器
(1)通过show triggers语句查询
mysql> show triggers \G
(2)通过查看系统表triggers查看触发器
#选择数据库
mysql> use information_schema;
#查看所有触发器
mysql> select * from triggers \G
#查看指定的触发器
mysql> select * from triggers where trigger_name='tri_diarytime' \G
3、删除触发器
语法:drop trigger 触发器名;
学习书籍:MySQL数据库应用从入门到精通 第2版完整版(第六、七、八章)