MySQL-Step2

熟练掌握使用MySQL

前面的话

本人使用的MySQL 5.1版本，图形化界面用的SQLyog，本文主要介绍有如下内容：

插入、更新和删除数据
索引
视图

插入、更新和删除数据

一、插入数据

1）给表的所有字段插入数据

insert into 表名 values(值1, 值2, ..., 值n);

2）给表的指定字段插入数据

insert into 表名(属性1,属性2,...,属性n) values(值1, 值2, ..., 值n);

3）同时插入多条记录

insert into 表名[(属性列表)]
    values(取值列表1),(取值列表2),...,(取值列表n);

二、更新数据

update 表名
set 属性名1=取值1,
    属性名2=取值2,
    ...
    属性名n=取值n
where 条件表达式;

三、删除数据

delete from 表名 [where 条件表达式]

索引

索引的引入

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

索引的优缺点

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

索引的分类

1）普通索引

普通索引可以创建在任何数据类型中。

2）唯一性索引

使用UNIQUE参数可以设置，在创建唯一性索引时，限制该索引的值必须是唯一的。

3）全文索引

使用FULLTEXT参数可以设置，全文索引只能创建在char，varchar，text 类型的字段上。主要作用就是提高查询较大字符串类型的速度，只有MyISAM引擎支持该索引，MySQL默认引擎不支持。

4）单列索引

在表中可以给单个字段创建索引，单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。

5）多列索引

多列索引是在表的多个字段上创建一个索引。

6）空间索引

使用SPATIAL参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率，只有MyISAM引擎支持该索引，MySQL默认引擎不支持。

创建索引

在创建表的同时创建索引：

create table 表名(
    属性名 数据类型 [完整性条件约束],
    属性名 数据类型 [完整性条件约束],
    ...
    属性名 数据类型 [完整性条件约束],
    [unique|fulltext|spatial] index|key [别名](属性名[长度][asc|desc]
);

1）创建普通索引

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

2）创建唯一性索引

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

3）创建全文索引

4）创建单列索引

5）创建多列索引

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

6）创建空间索引

在已经存在的表上创建索引

create [unique|fulltext|spatial] index 索引名
    on 表名(属性名[长度][asc|desc]);

CREATE INDEX index_userName ON t_user4(userName);

用alter table语句来创建索引

alter table 表名 add [unique|fulltext|spatial] index 索引名
    on 表名(属性名[长度][asc|desc]);

ALTER TABLE t_user4 
    ADD UNIQUE INDEX index_userName(userName);

删除索引

drop index 索引名 on 表名;

视图

视图的引入

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

视图的作用

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

创建视图

create [algorithm={undefiend|merge|temptable}]
    view 视图名 [(属性清单)]
    as select 语句
    [with[cascade|local] check option]

algorithm 是可选参数，表示视图选择的算法；

algorithm 包括3个选项undefined、merge和temptable。

其中undefined表示MySQL将自动选择所要使用的算法；

merge表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分；

temptable选项表示将视图的结果存入临时表，然后使用临时表执行语句；

cascade是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；

local表示更新视图时，要满足该视图本身的定义条件即可；

在单表上创建视图

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;

在多表上创建视图

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

查看视图

1） describe/desc 语句查看视图基本信息

DESC v4;

2） show table status 语句查看视图基本信息

/* 视图 */
SHOW TABLE STATUS LIKE 'v4';
/* 基本表 */
SHOW TABLE STATUS LIKE 't_book';

3） show create view 语句查看视图详细信息

SHOW CREATE VIEW v4;

4）在views表中查看视图详细信息

在系统数据information_schema的系统表VIEWS中可以查看所有的视图信息

修改视图

1） create or replace view 语句修改视图

create or replace [algorithm={undefiend|merge|temptable}]
    view 视图名 [(属性清单)]
    as select 语句
    [with[cascade|local] check option]

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

2） alter 语句修改视图

alter [algorithm={undefiend|merge|temptable}]
    view 视图名 [(属性清单)]
    as select 语句
    [with[cascade|local] check option]

ALTER VIEW v1 AS
    SELECT bookName FROM t_book;

更新视图

更新视图是指通过视图来插入（insert）、更新（update）和删除（delete）表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

1） insert

insert into v1 values('BlueSky');

2） update

UPDATE v3 SET p=p+1;

如果在定义视图时使用了别名，在属性设置时要使用别名设置。

3） delete

DELETE FROM v3
WHERE p=200;

删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。

drop view [if exists] 视图名列表 [restrict|cascade]

结束语

索引为了更好地查询，提高查询效率。

视图是虚表，一切对视图的操作，最终都是作用于实际表。

表的增删改是经常要做的操作，需要熟练掌握。