道者编程

Mysql基本操作、命令管理、索引、视图、触发器…

一:基本命令

mysql --help|grep 'my.cnf' #查看my.cnf位置

1:链接

mysql -u root -p 123 #本地进入mysql控制台
mysql -h 10.10.10.1 -u root -p 123 #远程进入mysql控制台

2:权限

(1):创建用户

create user '用户名'@'%' IDENTIFIED BY '密码'; 
 %:所有情况均能访问

(2):修改用户密码

update mysql.user set password=password('新密码') where user='用户名';

(3):删除用户

delete from mysql.user Where User='用户名';

(4):用户权限:语法模板

grant all privileges on 库名.表名 to '用户名'@'IP地址' identified by '密码' with grant option;

1):所有地方都能使用root访问,%:所有情况均能访问

grant all privileges on *.* to 'root'@'%' identified by  '密码' with grant option; 
 2):IP为192.168.1.8可以使用root远程访问

grant all privileges on *.* to 'root'@'192.168.1.8' identified by  '密码' with grant option;

(5):设置权限生效,相当于刷新

flush privilege
 修改后一定要做这一步,才会生效

3:数据库

show databases #显示所有数据库
use test2 #切换到 test2 数据库
select version() 查看mysql版本号
create database test2; #创建test2 数据库
create database `test2` default character set utf8 collate utf8_general_ci #修改test2编码字符集为utf-8


#上面两句可以并为一句,创建test2数据库,并指定编码字符集
create database `test2` default character set utf8 collate utf8_general_ci 

CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 

drop database test2 #删除test2数据库
show create database test2; #查看test2数据库的编码字符集

3:表

show create table goods #查看goods表字符集
show tables #查看当前数据库下所有表
rename table test2 to test3 #表test2重命名为test3
desc test2 #查看test2表结构
show create table test2 #也是查看表结构
alter table test2 engine=innodb; #修改test2表引擎为innodb

4:索引

(1):索引类型:主键索引(PRIMARY )、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT))、组合索引

 Mysql索引特点:
普通索引:最基本的索引,没有任何限制
唯一索引:与"普通索引"类似,不同之处:索引列的值必须唯一,但允许有空值。
主键索引:特殊的唯一索引,不允许有空值。 
全文索引:5.6以下版本只支持MyISAM 表,较大的数据,生成全文索引很耗时好空间。
组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

备注:关于组合索引:最左前缀,通俗的说就是必须从最左边一个作为起点,比如a,b,c组成1个组合索引。那么其实相当于生成了3个索引,即:a;ab;abc;而bc就不是,因为没了起点a,mysql无法判别从哪里开始。

(2):用法:

1):查看索引

#查看索引,以下两种方式查看表索引
show index from 表名 
show keys from 表名

2):添加索引

#1.添加PRIMARY KEY(主键索引) 
ALTER TABLE `表名` ADD PRIMARY KEY (`column`) ;

#2.添加UNIQUE(唯一索引) 
ALTER TABLE `表名` ADD UNIQUE (`column`);

#3.添加INDEX(普通索引) 
ALTER TABLE `表名` ADD INDEX(`column`);

#4.添加FULLTEXT(全文索引) 
ALTER TABLE `表名` ADD FULLTEXT (`column`);

添加组合索引 
ALTER TABLE `表名` ADD INDEX (`column1`, `column2`, `column3`);

备注:添加索引推荐用ALTER,create不通用,主键索引就不能用create创建

3)删除索引

alter table `表名` drop index 索引名
4)修改索引,先删除,然后再增加

备注:索引就好比一本书的目录,通过目录可以精确定位查找,节约搜索时间;但也并不是越多越好,索引越多,在添加、删除的时候,mysql会维护索引,维护成本较高。

5:mysql表复制

两步完成表的复制,第一步复制表结构;第二步复制表数据

create table test3 like test2 #复制test2表结构到新表test3
insert into test3 select * from test2 #复制表test2数据到test3

6:视图(view)

视图是一张虚拟的表,本身不保存数据,视图的数据根据条件从其他表里面取得。

1):新建视图:

语法:

CREATE VIEW 视图名(字段1,字段2...)
 AS SELECT (字段1,字段2...)
 FROM ...

举例1:

建立一个名称为v_goods的视图,通过该视图能够获取goods表id大于3的所有数据。

create view v_goods as select * from goods where id>3;
举例2:

建立一个名称为v_goods2的视图,通过该视图能够获取goods表id大于3的name字段数据。

create view v_goods2(`name`) as select (`name`) from goods where id>3
举例3:

通过视图获取多表联合查询数据,获取goods和user表数据

create view v_goods3(id,name,pid)
as
select goods.name,user.pid
from goods,user
where goods.id=user.id

2):查看视图

show tables #视图就是中间表,大部分命令和表差不多
3);删除视图

drop view 视图名
4):修改视图

修改视图和创建视图一样,多了两个参数:or replace

create or replace view 视图名…… #视图存在则覆盖,如不存在则创建

7:mysql触发器 trigger

触发器就是执行某个操作的时候,同时触发另外一个操作,比如对一个表进行添加的时候,同时添加另外一个表,Mysql自动执行,不需要也不能直接调用。

(1)触发器四个因素:

1. 监视地点(table)表

2. 监视事件(insert/update/delete)

3. 触发时间(after/before)之后/之前

4. 触发事件(insert/update/delete)

(2)语法格式:

create trigger trigger_name #触发器名称
after/before   insert/update/delete on tb_name #在tb_name表执行添加/修改/删除的之后/之前
for each row #行级触发器,每影响一条记录都要触发一次
begin #执行区间开始
sql 语句:(触发的语句一句或者多句)
end #执行区间结束

举例1:

t1表在插入数据后,再往t2表也插入数据,也就是t1表insert的之后触发t2表执行insert操作,这里要用after,触发器名称:t_ts

create trigger t_ts before insert on t1 for each row
begin
insert into t2(name) values(new.name); #如果是修改、删除,这里是old.字段
end


这样就实现了触发的功能。

注意:因为里面语句有分号,而Mysql看到分号的时候认为程序已经结束,所以需要加语句终结符,这里我们把语句终结符改为://

\d //:表示修改语句终结符为//,mysql默认是分号,\d是delimiter的缩写。

举例2:删除t1表的之后,触发删除t2表

create trigger t_ts before delete on t1 for each row
begin
delete from t2 where id = old.id
end

注意:格式和添加完全一样所不同的是语句是delete,执行语句要用old,因为这个字段是表里面有的,所以用old,而添加的时候是新的,所以上面添加是new;修改也是这样。

举例3:修改t1表的之后,触发修改t2表

create trigger t_ts before update on t1 for each row
begin
update t2 set id= new.id where id = old.id #注意这里的new和old,修改肯定是把字段修改成新的数据,所以set这里是new
end

特别注意:两个表ID自增不一样,可能ID不对应;解决办法,在对应的表加一个字段,添加的时候,触发器把添加的ID带过来到对应表的该字段即可。

(3):其他管理

show triggers #查看触发器
drop trigger t_t1 #删除 t_t1触发器

 8:truncate、delete与drop区别

(1):相同点:三者都可以删除mysql表内容,delete不带where条件,删除整个表内容

(2):不同点:

1):truncate和delete只删除数据内容,不删除数据结构,表还存在

2):drop删除整个表

3):delete是一行一行删除,在数据多的时候效率低下,且不会改变自增ID

4):truncate是整个删除,效率高,且会改变自增ID,truncate之后,新添加数据ID又从1开始


最新评论:
我要评论:

看不清楚