MySQL_05

表增加列，修改列，删除列

增加列：增加的列默认是在表的最后一列，可以用after来声明新列在哪列后面,如果要放在第一列，加first即可

alter table tb add name varchar(10) not null after id;  （列声明）

alter table tb add name varchar(10) not null first;

修改列

alter table tb change name newname varchar(20) not null;

删除列

alter table tb drop name;

 

视图

由查询结果形成的一张虚拟表

结构：create view 视图名 as select语句

create view gui as select goods_id,goods_name ,shop_price from goods where shop_price>3000;

select * from gui;

show tables;

desc gui;

 

视图的作用：

1，  简化查询。

2，  可以进行权限控制（把表的权限封闭，开放相应的视图权限，视图里只包含部分列部分数据）。

3，  大数据分表时可以用到（表的行数超过200万时，存取速度变慢，方法1：可以把表拆成多张表来存放；方法2： 把4张表形成一张视图）

create view new as select * from t1 union select * from t2....

 

删除视图：drop view 视图名

 

修改视图：alter view as select ...

 

视图与表的关系：

1，视图是表的查询结果，跟随表的数据改变而改变

2，视图增删改也会影响表，但视图并不总是能增删改（视图的数据与表的数据一一对应时，可以修改）（对于视图insert还应注意，视图必须包含所有表中没有默认值的列）

 

字符集

创建数据库或表时可以指定字符集 charset utf8;

create database/table name .. charset utf8;

查看表或数据库的默认编码show create table/database name;

1，设置客户端发送过去的字符编码：set character_set_client=gbk;

2，设置转换器将转换成的编码：set character_set_connection=gbk;

3，设置服务器返回的编码：set character_set_results=gbk;

1,2,3的步骤简写为：set names gbk;

 

乱码：

1，  client声明与事实不符

2，  results与客户端页面编码不符

 

丢失数据：

转换器和服务器的字符集比Client字符集小时，将丢失数据

 

校对集

概念：对字符集的排序规则

show collation;

show character set;

show collation like ‘utf8%’;

 

触发器trigger

触发器是一类特殊的事务 ,可以监视某种数据操作(insert/update/delete),

并触发相关操作(insert/update/delete)

 

触发器创建语法 之4要素: 监视地点(table)，监视事件insert/update/delete，触发时间after/before，触发事件insert/update/delete

 

创建触发器的语法

create trigger 触发器名称

after/befor (触发时间)

insert/update/delete (监视事件)

on  表名 (监视地址)

for each row

begin

sql1;

..

sqlN;

end;

（为防止MySQL遇到分号结束语句，需取消分号的结束权，使用delimiter + 新符号）

 

在触发器中引用行的值

对于insert，新增的行用new表示，行中的每一列的值，用 new.列名 表示

对于delete，被删除的行用old表示，行中的列用old.列名 引用

对于update，修改前的数据用old表示，修改后的数据用new来引用，用new/old.列名引用列

 

触发器里after和before的区别

after先完成数据的增删改，再触发，触发中的语句晚于增删改，无法影响前面的增删改

before先完成触发，再增删改，触发的语句先于监视的增删改发生，可以判断、修改即将发生的操作

 

查看所有触发器：show triggers;

 

删除触发器：drop trigger triggerName

 

例：

create table g (id int,name varchar(10),num int)charset utf8;

create table o (oid int,gid int,much int)charset utf8;

insert into g values(1,'猪',22),(2,'羊',19),(3,'狗',12),(4,'猫',8);

select * from g;

insert into o values (1,2,3);

select * from o;

update g set num=num-3 where id=2;

select * from g;

#以下这句取消分号作为结束符，而改用$

delimiter $

#监视地点o表

#监视操作insert

#触发操作update

#触发时间after

create trigger tg1

after insert on o

for each row

begin

update g set num=num-3 where id=2;

end$

delimiter ;

insert into o values (2,2,3);

select * from g;

show triggers;

drop trigger tg1;

delimiter $

#添加订单，库存减少

create trigger tg2

after insert on o

for each row

begin

update g set num=num-new.much where id = new.gid;

end$

delimiter ;

truncate o;

insert into o values (1,4,1);

select * from g;

 

#删除订单，库存增加

#监视地点 o表

#监视事件 delete

#触发事件 update

#触发时间 after

delimiter $

create trigger tg3

after delete on o

for each row

begin

update g set num=num+old.much where id = old.gid;

end$

delete from o where oid=1 $

select * from g $

 

#修改订单，库存更新

#监视地点 o表

#监视事件 update

#触发事件 update

#触发时间 after

delimiter $

create trigger tg4

after update on o

for each row

begin

update g set num = num + old.much – new.much where id=old.gid;

end$

insert into o values (1,1,3)$

update o set much=5 where oid=1$

select * from g$

 

#对于所下订单进行判断，如果订单的数量大于5，就认为是恶意订单，并强制把所订的商品数量改成5

#监视地点 o

#监视事件 insert

#触发事件 update

#触发时间 before

drop trigger tg2 $

create trigger tg5

before insert on o

for each row

begin

       if new.much > 5 then

              set new.much = 5;

       end if;

       update g set num=num-new.much where id = new.gid;

end$

 

存储引擎

数据库对数据的存储方式和管理方式

show engines;

事务

一组相互关联的sql操作

使用Innodb存储引擎

事务的ACID特性：

1.          原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事物。

数据库事务的不可再分的原则即为原子性。 组成事务的所有查询必须：

要么全部执行，要么全部取消。

2.          一致性(Consistency)：指数据的规则,在事务前/后应保持一致

3.          隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的.

4.          持久性(Durability)：当事务完成后，其影响应该保留下来，不能撤消

 

开启事务：start transaction

执行sql操作

提交/回滚：commit/rollback（注意某些隐式提交）

 

 

备份与恢复

系统运行中，分增量备份和整体备份（相互配合使用）

备份单独库下面的所有表的方法（不导出库）

mysqldump -uuname -ppasswd dbname > /dir/filename

例：mysqldump –uroot –p112599 php > d://a.sql

备份某一库下面的几个表的方法

mysqldump -uuname -ppasswd dbname table1 table2.. tableN  > /dir/filename

例：mysqldump -uroot -p112599 php user stu > d://a.sql

备份多个库的方法

mysqldump -uname -ppasswd -B db1 db2 > /dir/filename

备份所有库的方法

mysqldump -uname -ppasswd -A > /dir/filename

 

恢复：

1:在命令行操作

mysql -uuname -ppasswd [databaseName]< /dir/filename

2:登陆mysql后source操作

mysql> use dbname;

mysql> source  /dir/filename;

 

索引

给数据加的目录

索引的作用：加快了查询速度(select )。缺点：降低了增,删,改的速度(update/delete/insert)，增大了表的文件大小(索引文件甚至可能比数据文件还大)

索引的使用原则：不过度索引。索引条件列(where后面最频繁的条件比较适宜索引)，索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大

索引类型：普通索引  (index)，主键索引  (primary key)，唯一索引  (unique)，全文索引  (fulltext)

 

索引的创建语法

方法1：

建表时直接声明索引:

create table tableName (

列1 列类型 列属性,

....

列N 列类型 列属性,

#主键索引：不能重复，但唯一索引不一定是主键，一张表只能有一个主键（可能是组合主键），但是可以用一个或多个唯一索引

＃普通索引仅加快查询速度

＃唯一索引不能重复

#全文索引

primary key (列名),

index  [索引名] (列名),

unique [索引名] (列名),

fulltext [索引名] (列名)

)engine xxxxx  charset xxxx

方法2：

通过修改表建立索引

alter table add index (列名);

alter table add unique (列名);

alter table add primary key(列名);

alter table add fulltext (列名);

索引产生的文件以.MYI为后缀，数据文件以.MYD为后缀

 

索引的删除：

删除主键:

alter table 表名drop primary key

删除其他索引:

alter table 表名 drop index 索引名

注:索引名一般是列名,如果不是,可通过show index from tableName查看索引

 

全文索引

全文索引在mysql的默认情况下，对于中文意义不大

使用方法：match(全文索引名) against (‘keyword’)

普通查询（速度慢）

 

利用全文索引查询

 

查看匹配度

 

停止词：全文索引不针对非常频繁的词作索引