MySQL_05
来源:互联网 发布:淘宝天音软件 编辑:程序博客网 时间:2024/05/23 17:36
表增加列,修改列,删除列
增加列:增加的列默认是在表的最后一列,可以用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’)
普通查询(速度慢)
利用全文索引查询
查看匹配度
停止词:全文索引不针对非常频繁的词作索引