表
创建表
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-197069-2016),四位数字(19012155) |
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 sqlstate '02000' 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;
- 不具有分隔符的语言(如中文)不能恰当的返回全文搜索结果;
事务
commit,rollback
事务回滚可用来回退insert,update,delete语句,不能回滚create和drop操作
在事务代码块中可以使用create和drop语句,但事务回滚之后,这些操作不会被撤销
start transaction;
insert...
update...
delete...
commit;//rollback;
保留点
- 如果发生错误,只回退到某个特定的位置即可,不用全部回退;
- 在mysql中可以设置任意个保留点;
- 事务处理完成后自动释放保留点;
savepoint delete1;
...
rollback to delete1;
更改自动提交
- 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');
日志
错误日志
包含错误信息,位于data目录中的hostname.err文件
可通过–log-error命令更改名称
查询日志
记录了mysql的所有活动,日志文件通常非常大。位于data目录中hostname.log文件中
可以通过–log命令更改
二进制日志
记录了更新过数据的所有语句,位于data目录中的hostname-bin文件,可以用–log-bin命令修改
此日志在mysql5及之后的版本中存在
缓慢查询日志
记录执行缓慢的任何查询,名称为hostname-slow.log,可以通过–log-slow-queries命令更改
刷新日志记录
flush logs;
MySQL性能提升
- 定期调整内存分配、缓冲区大小等(show variables;show status;查看当前设置);
- 使用kill命令终结缓慢进程(show processlist用于显示所有进程);
- explain语句,用于解释MySQL如何执行select语句;
- 存储过程比循环执行SQL语句更快;
- 使用正确的数据类型;
- 减少使用select * ···;
- 导入数据时,关闭自动提交,删除索引;
- 找出select语句中重复的where和group by语句,如果必要,建立索引;
- 复杂的or条件通过使用多条语句,用union替代;
- 索引能提升select性能,但对插入、删除、更新性能有损;
- 使用fulltext代替like;
---创建索引
create index indexName on tableName (columnName [asc|desc],...)
---drop
drop databases|index|procedure|table|trigger|user|view itemName;