索引
MySQL的索引包括普通索引、唯一性索引(unique index)、全文索引(fulltext index)、单列索引、多列索引和空间索引等。
1.索引的创建
·创建表的时候创建索引
SQL语法:index|key [索引名] (属性名[(长度)] [asc|desc])
create table newTable(
id int not null primary key,
name varchar(20),
age int,
index name_index(name(10))
);
·在已经存在的表上创建索引
SQL语法:create index 索引名 on 表名(属性名[(长度)] [asc|desc]);
create index age_index on newTable(age(10));
·使用alter table来创建索引
SQL语法:alter table table_name add index|key 索引名[(长度)] [asc|desc]);
alter table newTable add index name_index(name(5) desc);
2.查看索引
SQL语法:
show index from table_name[from db_name];
show index from mydb.mytable;
show index from newTable;#查看索引信息
3.删除索引
SQL语法:
drop index index_name on table_name;
alter table table_name drop index index_name;
alter table table_name drop primary key;
drop index name_index on newTable;
alter table newTable drop primary key;
视图
视图是一个虚拟表,其内容由查询定义。
1.视图的创建
SQL语法:create view 视图名[(视图列表)] as 查询语句;
create view student_view2(name,cname,grade)
as select sname,cname,grade
from student s,course c,sc
where s.sno=sc.sno and c.cno=sc.cno;
2.查看视图
describe语句:describe 视图名称;
show table status语句:show table status like '视图名';
show create view语句:show create view '视图名';
查询db1数据库下的student表:
select * from db1 a.view where student='student_view'\G
3.修改视图
create or replace view 视图名[{属性清单}] as select 语句;
create or replace view
student_view(姓名,选修课,成绩)
as select sname,cname,grade
from student s,course c,sc
where s.sno=sc.sno and c.cno=sc.cno;
4.删除视图
SQL语法:drop view [if exists] view_name[,view_name2];
drop view if exists student_view;
5.更新视图数据
update student_view set sno='001',sname='张三’,ssex='男';