MySQL数据库基本操作表视图存储过程全文检索用户管理日志等

约 2954 字大约 10 分钟

[TOC]

表

创建表

create table customers(
    cust_id int not null auto_increment,
    cust_anme char(50) not null,
    cust_address char(50) null,
    cust_city char(50),
    cust_state char(1) not null default 0,
    cust_zip char(50) null,
    cust_contry char(50) null,
    cust_contact char(50) null,
    cust_email char(255) null,
    primary key(cust_id)
) engine=InnoDB;

可以创建组合主键；
```
primary key(cust_id,cust_name)
```
主键不能使用允许为null值得列；
每个表只允许一列为auto_increment，而且必须被索引；
覆盖auto_increment，需要在insert语句中将对应的字段设置为要改动的值，后续的增量将从这个覆盖后的值开始；
使用last_insert_id()函数，获得auto_increment的当前值，可以用于后续的SQL语句中；
MySQL不允许使用函数作为默认值，default后只能接常亮；
设置存储引擎，InnoDB（可靠地事务处理引擎），MEMORY（功能上等同于MyISAM，但存储在内存），MyISAM（高性能引擎，支持全文本搜索，但不支持事务）；
引擎类型可以混用；
外键不能跨引擎使用

更新表

添加列

alter table vendors add vend_phone char(20);

删除列

alter table vendors drop column vend_phone;

添加外键

alter table products add constraint fk_products_vendors foreign key(vend_id) references vendors(vend_id);

使用alter table需要注意，最好提前备份数据防止数据误删除

删除表

drop table customers2;

重命名表

rename table customers2 to customers;

rename table backup_customers to customers,backup_vendors to vendors,backup_products to products;

视图

有些视图不允许手动更新，如果视图中定义有如下操作，则不能进行更新：

分组：使用group by和having
联结；
子查询；
并；
聚合函数；
distinct；
导出列；

存储过程

创建存储过程

create procedure productpricing()
begin
	select avg(prod_price) as priceaverage from products;
end;

调用存储过程

call productpricing();

使用命令行创建存储过程

delimiter //

create procedure productpricing()
begin
select avg(prod_price) as priceaverage form products;
end //

delimiter;

其中，delimiter告诉程序，使用//作为新的语句结束符，这样在程序执行过程中，“；”不再是结束符。使用结束后通过delimiter语句恢复原来的结束符。

删除存储过程

drop procedure productpricing;

带参数的存储过程

create procedure productpricing(
		out pl decimal(8,2),
    out ph decimal(8,2),
    out pa decimal(8,2)
)
begin
	select min(prod_price) into pl from products;
	select max(prod_price) into ph from products;
	select avg(prod_price) into pa from products;
end;

create procedure ordertotal(
    in onumber int,
    out ototal decimal(8,2)
)
begin
	select sum(item_price*quantity) from orderitems where order_num=onumber into ototal;
end;

in——入参，out——返回值

两个存储过程的调用

call productpricing(@pricelow,@pricehigh,@priceaverage);
select @pricehign,@pricelow,@priceaverage;

call ordertotal(20005,@total);
select @total;

完整的存储过程

-- 注释
-- comment关键字：不是必须的，如果有comment，这个存储过程可以再show procedure status结果中显示出来
create procedure ordertotal(
    in onumber int,
    in taxable boolean,
    out ototal decimal(8,2)
) comment 'Obtain order total, optionally add tax'
begin
	-- 定义局部变量
	declare total decimal(8,2);
	declare taxrate int default 6;
	
	-- 查询结果
	select sum(item_price*quantity) from orderitems where order_num=onumber into total;
	
	-- 判断boolean
	if taxable then 
		-- yes
		select total+(total/100*taxrate) into total;
	end if;
	
	-- 赋值
	select total into ototal;
end;

mysql数据类型

char	1~255个字符
enum	64K个字符串组成的一个预定义集合的字符串
longtext	最大4G
mediumtext	最大16K
set	64个字符串组成的集合
test	最大64K
tinytext	最大255字节
varchar	最大255字节
bit	点位
bigint	整数，
boolean	布尔0，1
decimal	精度可变浮点数
double	双精度浮点数
float	单精度浮点数
int	整数，-2147486348~2147483647
mediumint	整数，-8388608~8388607
real	4字节浮点数
smallint	整数，-32768~32767
tinyint	整数，-128~127
date	1000-01-01~9999-12-31之间,格式YYYY-MM-DD
datetime	date+time
time	格式HH:mm:ss
timestamp
year	年，两位数字（70-1970_{69-2016），四位数字（1901}2155）
blob	二进制，最大长度64K
mediumblob	二进制，最大长度16M
longblob	二进制，最大长度4G
tinyblob	二进制最大长度255字节

检查存储过程

show create procedure ordertotal;

--获取存储过程信息
show procedure status like 'ordertotal';

游标

create procedure processorders()
begin
	declare done boolean default 0;
	declare o int;
	declare t decimal(8,2);
	--创建游标
	declare ordernumbers cursor for
	select order_num from orders;
	--定义继续执行的条件
	declare continue handler for not found set done=1;
	--创建表用于存储结果
	create table if not exists ordertotals(order_num int,total decimal(8,2));
	--打开游标
	open ordernumbers;
	--使用游标数据
	repeat
		fetch ordernumbers into o;
		call ordertotal(0,1,t);
		insert into ordertotals(order_num,total) values(o,t);
	until done end repeat;
	--关闭游标
	close ordernumbers;
end;

触发器

创建名称为“newproduct”的触发器，after insert表示在insert操作之后执行，for each row表示对每个插入行执行

create trigger newproduct after insert on products for each row select 'Product Added';

删除触发器

drop trigger newproduct;

触发器只能作用于表，不能作用于视图；
触发器不能更新或覆盖，如需修改，需要先删除，在新增；

全文搜索

MyISAM引擎支持全文本搜索；
使用全文本搜索，必须索引被搜索的列，随着数据的修改，需要不断重新索引；
创建表时使用FULLTEXT()语句指出被索引列的 “,” 分隔列表；
或者在所有相关数据已建立索引后指定FULLTEXT；

建表语句

create table productnotes(
    note_id int not null auto_increment,
    prod_id char(10) not null,
    not_date datetime not null,
    not_text text null,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
) ENGINE=MyISAM;

进行全文本搜索（使用Match()和Against()）

Match()指定被搜索的列，Against()指定使用的表达式

select note_text from productnotes where Match(note_text) Against('rabbit');

传递给Match（）的值必须与FULLTEXT（）定义相同，指定多个列，必须列出，且次序相同；
除非使用BINARY方式，否则搜索不区分大小写
搜索结果以文本匹配的良好程度排序，匹配程度由高到低

查看该字段全文搜索匹配程度

select note_text,Match(note_text) Against('rabbit') as rank from productnotes;

其中，rank值由行中词的数目、唯一词的数目、整个索引中词的总数、包含该词的行的数目计算出来，不包含‘rabbit’的行等级为0，搜索结果由rank值从大到小排序；
如果包含多个搜索项，则包含多数词的行比包含较少词的行匹配度高；

全文本搜索速度明显高于like关键字

select note_text from productnotes where note_text like '%rabbit%';

查询扩展

用于查找所有包含‘anvils’的行和与其相关的行，即使不包含‘anvils’关键字

select note_text from productnotes where Match(note_text) Against('anvils' with query expansion);

MySQL通过对数据和索引进行两次扫描来完成查询扩展，步骤：

进行一次基本的全文本搜索，查找所有匹配行；
MySQL检查这些匹配航，并选择所有有用的词^[1]；
MySQL再次进行全文本搜索，包含 1 中的查询条件和 2 中的所有有用的词；

布尔文本搜索

没有使用fulltext索引也可以使用，主要功能包括：

要匹配的词；
要排斥的词（即使包含要匹配的词）；
排列提示（某些词优先级更高）；
表达式分组；
其他……

select note_text from productnotes where Match(note_text) Against('heavy -rope*' in boolean mode);

-rope*表示：排除包含rope*的行（任何以rope开始的词，包括ropes）
所有布尔操作符：

操作符	说明
+	包含，词必须存在
-	排除，词不能出现
>	包含，增加优先级
<	包含，减少优先级
( )	把词组成表达式（允许这些表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾通配符
" "	定义一个短语（匹配整个短语，可用于包含或排除）

查找包含rabbit和bait的行

select note_text from productnotes where Match(note_text) Against('+rabbit +bait' in boolean mode);

包含rabbit和bait至少一个词的行

select note_text from productnotes where Match(note_text) Against('rabbit bait' in boolean mode);

匹配短语rabbit bait，而不匹配两个单独的词rabbit和bait

select note_text from productnotes where Match(note_text) Against('"rabbit bait"' in boolean mode);

匹配rabbit和carrot，增加rabbit的等级，降低carrot的等级

select note_text from productnotes where Match(note_text) Against('>rabbit <carrot' in boolean mode);

注意

MySQL存在一个stopword表，表中的词在索引全文本数据时被忽略，如果需要可以覆盖这个表；
50%规则——如果一个词出现在50%以上的行中，则将它当成一个非用词忽略，此规则不适用与IN BOOLEAN MODE中；
如果表中的行数少于3，全文本搜索不返回结果（因为结果或者不出现，或者至少出现在50%的行中）
忽略单引号，don't=dont；
不具有分隔符的语言（如中文）不能恰当的返回全文搜索结果；

更改自动提交

mysql默认自动提交所有更新，通过参数autocommit设置是否自动更新，’0‘——关闭；
autocommit只针对每个连接，不是针对整个服务；

set autocommit=0;

管理用户

创建用户

create user ben identified by 'p@$$w0rd';

重命名账号

rename user ben to bforta;

删除账号

drop user bforta;

查看账号

user mysql;
select user from user;

查看账号权限

show grants for bforta;

授予用户权限

grant语句，至少包含1.要授予的权限，2.被授予访问权限的数据库或表，3，用户名

--允许bforta用户在crashcourse库的所有表使用select
grant select on crashcourse.* to bforta;

grant的反操作（撤销特定的权限）

--撤销bforta用户在crashcourse库所有表的select权限
revoke select on crashcourse.* from bforta;

grant和revoke可以控制权限的层次：

整个服务器，grant all和revoke all；
整个数据库，on database.*；
特定的表，on database.table；
特定的列；
特定的存储过程。

可以授予或撤销的权限

权限	说明
all	除grant option外的所有权限
alter	使用alter table
alter routine	使用alter procedure和drop procedure
create	使用create table
create routine	使用create procedure
create temporary tables	使用create temporary table
create user	使用create user，drop user，rename user和revoke all privileges
create view	使用create view
delete	使用delete
drop	使用drop table
execute	使用call和存储过程
file	使用select into outfile和load data infile
grant option	使用grant和revoke
index	使用create index和drop index
insert	使用insert
lock tables	使用locak tables
process	使用show full processlist
reload	使用flush
replication client	服务器位置的访问
replication slave	由复制从属使用
select	使用select
show databases	使用show databases
show view	使用show create view
shutdown	使用mysqladmin shutdown（用来关闭mysql）
super	使用change master，kill，logs，purge，master和set global。允许mysqladmin调试登录
update	使用update
usage	无访问权限

同时进行多权限授权

grant select,insert on crashcourse.* to bforta;

更改密码

set password for bforta=password('n2w p@$$w0rd');
--更改自己的密码
set password=password('new password');
--mysql8+版本修改密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';