MySQL学习笔记
1. 建立数据库服务器
MySQL –h localhost –u root –p
set names gbk;
2. 在mysql中使用tee把执行过程记录下来
tee 文件名(Eg：d:\\tee test.sql)
3. 分节符
delimiter $
4. 建立一个数据库
create database DBname[字符集声明，整理声明];
create database dbstudy charset utf8;
5. 查看当前服务器下面有那些数据库.
show database;
6. 删除数据库
drop database dbname;
7. 修改数据库
只能修改字符集和整理集，不能修改数据库的名字。
8. 选择数据库
在进行表行的相关操作的时候，要先选择数据库 .
use DBName; //选择相应的数据库
9. 建立表
Create table 表名(
列名称 列类型 [列属性] [默认值],à列声明
列名称 列类型 [列属性] [默认值],
列名称 列类型 [列属性] [默认值]
)charset utf8/gbk/….;
Create table tableName(列名,默认值,…..)charset utf8;
(1) 所谓表就是声明列的过程，数据是以文件的形式存放在硬盘或者内存里面的。
(2) 列：不同的类型占用的空间不一样。
(3) 选列的原则：够用不乱费原则。
(4) 列可以声明默认值，而且推荐声明默认值,Not null default 0.
(5) 建表实例
create table t_info(
id int primary key auto_increment,
name char(4) not null default '',
age tinyint unsigned not null default 0,
tel char(11) not null default '',
salary decimal(7,2) not null default '2000',
riqi date not null default '2013-04-20'
)charset utf8;
10. 如何查看一下数据库下面的所有表
show tables;
11. 清空一张表
truncate tableName；
12. 查看数据库表的创建语句
show create table 表名;
show create table member;
13. 如何删除一张表
drop table tableName;
14. 如何修改表名
rename table oldName to newName;
15. 查看表结构
desc tableName;
16. 添加列
alter table 表名 add 字段名 字段类型unsigned zerofill;
alter table t_class add age3 tinyint(5) unsigned zerofill Not NULL default 0;
17. 解决字符集问题
默认建表一般用utf-8,而我们在Windows下窗口是GBK.因此需要声明字符集.
set names gbk;
注意：如果发现语句打错了,应该退出本语句,再继续打新语句,也可以\c退出语句.
18. 排序
select * from msg order by id asc;
其中：asc即升级, desc即降序
19. 往表中插入数据
insert into tableName(列1,列2…) values(值1,值2,…);
insert into tableName values(值1,值2,…..);
20. 删除数据
delete from tableName where expression；
21. 更新数据
update 表名 set 列1=新值1,列2=新值2,where expression;
22. mysql三大列类型
(1) 数值型
l 整型tinyint, smallint, mediumint, int, bigint
注意：tinyint(M) unsigned zerofill
M:代表宽度（在zerofill ‘0填充的’时候才有意义）
Unsigned：无符号类型（非负）,影响存储范围
Zerofill:零填充（如果某列有zerofill，默认就是unsigned）
l 小数型
float(D,M)
decimal(D,M)
注意：
M：精度（总位数，不包含点）
D： 标度（小数位）
Float ：浮点型
Decimal:定点型
Decimal更精确
create table t_goods(
name varchar(10) not null default '',
price float(6,2) not null default 0.00
)charset utf8;
insert into t_goods(name,price) values('跑步机',688,896);
select concat(name,'!'),concat(waihao,'!') from table;
concat字符连接
(2) 字符型
l char(M)：定长,char(M),M代表宽度,即可容纳的字符数,在0<=M<=255之间.如果实际存储内容不足M个字符,则后面加空格补齐,在取数据的时候,在将后面的空格去掉,所以内容后面有空格,将会被清除.
l varchar(M): 变长,varchar(M),M代表宽度,即可容纳的字数,0<=M<=65535(以ASCII字符为例,utf8算的话在22000左右).
l char与varchar型的选择原则：
² 空间利用率,四字成语表,char(4),个人简介,微博(140字),选用varchar(140).
² 速度:用户名char.
l text:文本类型,可以存储比较大的文本段,搜索速度稍慢,因此,如果不是特别大的内容,建议用char,varchar来代替.text不用加默认值,加了也没有作用.
(3) 日期/时间型
l Date 日期类型
² 典型格式 1992-08-12
² 日期类型：’1000-01-01’à’9999-12-31’
l Time 时间
² 典型格式：hh:mm:ss
² 时间类型：”-838:59:59”à”+838:59:59”
l Datetime 类型
² 典型格式:’1989-05-06 14:32:00’
² 时间日期范围：1000-01-01 00：00：00à9999-12-31 23:59:59
注意：在开发中，很少用到日期时间类型来表示一个需要的精确到秒的列。
原因：虽然日期时间类型能精确到秒,而且方便查看.
时间戳：是1970-01-01 00：00：00到当前的秒数.
一般存储注册时间,商品发布时间等，并不是用datetime存储,而是用时间戳, 因为datetime虽然直观,但计算不方便.而用int型存储时间戳,方便计算,对 于显示来说,也可以方便的格式化.
l Year 类型
² 1个字节,表示1901-2155,[0000表示错误时选择].
² 如果输入2位, ”00-69”,则表示2000-2069年;
² 如果输入2位, ”70-99”,则表示1970-1999年.
² 如果记起来比较麻烦,输入的时候直接输入4位的年即可.
23.mysql整数类型的存储和范围
|
类型 |
字节 |
最小值 |
最大值 |
|
|
|
(带符号的/无符号的) |
(带符号的/无符号的) |
|
TINYINT |
1 |
-128 |
127 |
|
|
|
0 |
255 |
|
SMALLINT |
2 |
-32768 |
32767 |
|
|
|
0 |
65535 |
|
MEDIUMINT |
3 |
-8388608 |
8388607 |
|
|
|
0 |
16777215 |
|
INT |
4 |
-2147483648 |
2147483647 |
|
|
|
0 |
4294967295 |
|
BIGINT |
8 |
-9223372036854775808 |
9223372036854775807 |
|
|
|
0 |
18446744073709551615 |
24. 枚举类型创建表
create table t_student(
name varchar(10) not null default ' ',
gender enum('男','女')
)charset utf8;
注意：在实际的设计性别的时候不用枚举类型，其原因是不符合关系型数据库的规范.一般可以用数字1和0来分别区分是男是女。
1. Select 5种子句介绍
(1) Where条件查询
² 比较运算符
|
运算符 |
说明 |
|
< |
小于 |
|
<= |
小于等于 |
|
= |
等于 |
|
!=或<> |
不等于 |
|
>= |
大于等于 |
|
> |
大于 |
|
in |
在某集合内 |
|
between |
在某范围内 |
注意：in(值1,值2….值n),等于值1-->n任意一个都行；
between 值1 and 值2,表示在值1和值2之间.
² 逻辑运算符
|
运算符 |
说明 |
|
NOT或！ |
逻辑非 |
|
OR或|| |
逻辑或 |
|
AND或&& |
逻辑与 |
² 模糊查询
like->像
‘%’à 通配任意字符
‘_’à 单个字符
(2) group by分组
² 作用：把行按字段分组
² 语法：group by col1,col2,…..colN;
² 运用场合
常见于统计场合,如按栏目计算帖子数,统计每个人的凭据成绩等.
² group 函数
|
函数 |
说明 |
|
Max |
求最大 |
|
Min |
求最小 |
|
Sum |
求总和 |
|
Avg |
求平均 |
|
Count |
求总行数 |
例子：select avg(age) from t_info;
(3) having 筛选
l having和where异同点
² having与where类似,可筛选数据,where后的表达式怎么写,having就怎么写.
² where针对表中的列发挥作用,查询数据.
² having针对查询结果中的列发挥作用,筛选数据
l 例子
select goods_id,goods_name,market_price-shop_peice as sheng from goods having sheng>200;
(4) order by 排序
l 按一个或多个字段对查询结果进行排序.
l 可用字段名DESC来声明按降序排列,显示声明升序排列,可用ASC来声明.
l order by可以按多字段排序,order by 列1 [desc/asc],列2 [desc/asc]
(5) limit 限制结果条数
l limit在语句的最后,起到限制条目的作用.
l limit[offset,N]
² Offset:偏移量
² N：取出条目
² Offset如果不写,则相当于 limit 0,N
注意：
² 5个子句是有顺序要求的：whereàgroupàhavingàorder byàlimit.
² where 表达式,把表达式放在行中,看表达式是否为真.
² 列理解成变量,可以运算.
² 查询结果可以了解成一张临时表.
2. 子查询
(1) where型子查询
l 例子：查询出最新的一条记录
² select goods_id,goods_name from goods order by goods_id desc limit 0,1;
² select goods_id,goods_name from goods where goods_id=33;(33是最新插入).
² select goods_id,goods_name from goods where goods_id=(select max(goods_id)from goods);
² 把内层查询的结果作为外层查询的比较条件.
(2) from型子查询
l 把内层的查询结果当成临时表,加 ’as’临时表名,供外层sql再次查询.
l Select goods_id,cat_id,goods,name from goods where goods_id in(select max(goods_id) from goods group by cat_id;
l Select * from (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as temp group by cat_id;
(3) exist型子查询
l 把外层的查询结果,拿到内存,看内层的查询是否成立.
3. union的用法
(1) 实例
create table ta(
id char(1),
num int
);
insert into ta values('a',5),('b',10),('c',15),('d',10);
create table tb(
id char(1),
num int
);
insert into tb values('b',5),('c',10),('d',20),('e',99);
想要得到的结果是：
a,5 b,15 c,25 d,30 e,99
select id,sum(num) from (select * from ta union select * from tb) as temp group by id;
(2) Unionà联合.
(3) 作用à把2次或多次查询结果合并起来.
(4) 要求à两次查询的列数要一致.推荐à查询的每一列,相对应的列类型也一样.
(5) 多次sql语句取出的列名可以不一致,此时以第一个sql的列名为准.
(6) 如果不同的语句中取出的行,有完全相同(每个列的值都相同)行,那么相同的行将会合并(去合并).
(7) 如果不去重复,可以加all来指定.
update tb set num=15;
select id,sum(num) from (select * from ta union all select * from tb) as temp group by id;
(8) 如果子句中有order by，limit，需要加小括号()，推荐放到所有子句之后,即对最终合并后的结果来排序.
l 例子1：想取第四栏目的商品,价格降序排列,还想取第五栏目的商品,价格也降序排列,union完成.
(select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=5) order by shop_price desc;
l 例子2:取第3栏目价格前3高的商品和第4栏目几个钱2高的商品，用union来实现.
(select goods_id,cat_id,goods_name,shop_price from goods where cat_id=3 order by shop_price desc limit 3) union (select goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 order by shop_price desc limit 2);
(9) 在子句中,order by配合limit使用才有意义,如果order by不配合limit使用,会被语法分析器优化分析时去除.
4. 集合
(1) 集合set
(2) 集合的特性：无序性,唯一性
(3) 一张表就是一个集合,一行数据是集合的一个元素。
(4) 理论上讲：不可能存在完全相同的两个行,但是表中可以存在完全相同的两行,因为表内部有一个rowid.
(5) 集合相乘,笛卡尔基.其实就是两个集合的完全组合.
(6) 问：设集合A有M个元素,M个元素各不相同;设集合B,有N个元素,N个元素各不相同.A*B得到的积,有M*N个元素,不可能重复.
例如：表A有9行数据,表B有10行数据,两表相乘有9*10=90行数据.
(7) Select * from ta,tb;
5. 左连接(left join)
(1) Select 列1,列2,…..列N from tableA left join tableB on tableA.列=tableB.列
注意：此表连接成一张大表,完全可以当成普通表看,后面where,group by,having继续写.
(2) select goods_id,goods.cat_id,cat_name,goods_name,shop_price from goods left join category on goods.cat_id=category.cat_id;
(3) 实例
create table boy(
name varchar(10),
flower varchar(8)
)charset utf8;
insert into boy values('林书','玫瑰'),('刘翔','桃花'), ('周杰伦','茉莉花'),('犀利哥','荷花'),('刘德华','狗尾巴花');
create table girl(
name varchar(10),
flower varchar(6)
)charset utf8;
insert into girl values('艾薇儿','玫瑰'), ('居里夫','桃花'), ('芙蓉姐','茉莉花'), ('凤姐','茉莉花'), ('林志玲','荷花');
6. 右连接(right join)
(1) Select 列1,列2,…..列N from tableA right join tableB on tableA.列=tableB.列
注意：此表连接成一张大表,完全可以当成普通表看,后面where,group by,having继续写.
(2) select goods_id,goods.cat_id,cat_name,goods_name,shop_price from goods right join category on goods.cat_id=category.cat_id;
7. 内连接(inner)
(1) Select 列1,列2,…..列N from tableA right join tableB on tableA.列=tableB.列
注意：此表连接成一张大表,完全可以当成普通表看,后面where,group by,having继续写.
(2) select goods_id,goods.cat_id,cat_name,goods_name,shop_price from goods inner join category on goods.cat_id=category.cat_id;
8. 左右连接总结
(1) 以左表为准,去右表找匹配数据,找不到匹配数据,则用NULL补齐.
(2) 左右连接可以相互转化.
(3) 可以把右连接转换为左连接来使用(并推荐使用左连接代替右连接à兼容性好一些).
A站在B的左边=àB站在A的右边.
A left join B =àB right join A.
9. 内连接总结
(1) 内连接：查询左右表都有的数据.即:不要左右连接中NULL的那一部分.
(2) 内连接是左右连接的交集.
(3) 目前的MySQL不支持外连接(outer join),但是可以用union来达到目的.
10. 左连接实例
create table t_match(
matchID int primary key auto_increment,
hostTeamID int,
guestTeamID int,
matchResult varchar(10),
matchTime date
)charset utf8;
insert into t_match(hostTeamID,guestTeamID,matchResult,matchTime) values
(1,2,'2:0','2006-05-21'),(2,3,'1:2','2006-06-21'),
(3,1,'2:5','2006-06-25'),(2,1,'3:2','2006-07-21');
create table t_team(
teamID int,
teamName varchar(10)
)charset utf8;
insert into t_team values
(1,'国安'),(2,'申花'),(3,'传智联队');
select hostTeamID,t1.teamName as hostName,matchResult,guestTeamID,t2.teamName as gname,matchTime from
t_match left join t_team as t1 on t_match.hostTeamID=t1.teamID
left join t_team as t2 on t_match.guestTeamID=t2.teamID
where matchTime between '2006-06-01' and '2006-07-01';
1. 表增加列,修改列,与删除列
(1) 增加列
l Alter table 表名 add 列声明.
l 增加的列默认是在表的最后一列.
l 可以用after来声明新增加的列在那一列后面.
l Alter table 表名 add 列声明 after 列名.
Alter table boy add age int after name;
l 如果新增加的列放在最前面,怎么做？
Alter table 表名 add 列声明 first
Alter table girl add id int primary key auto_increment first;
(2) 修改列
Alter table 表名 change 被改变的列名 列声明
Alter table boy change name name varchar(15) not null default ‘lisi’;
(3) 删除列
Alter table 表名 drop 列名;
Alter table boy drop id;
2. 视图(view)
(1) 视图的定义
视图是由查询结果形成的一张虚拟表
(2) 视图的创建语法
Create view 视图名 as select 语句
create [algorithm=merge|temptable|undefined] view viewName as select ....;
Create view v_view as select * from t_test;
(3) 视图的目的
l 可以简化查询
l 可以进行权限控制
把表的权限封闭,但是开放相应的视图权限，视图里只开放部分数据.
l 大数据分表时可以用到.
比如表(news)的行数超过200万行时,就会变慢,可以把一张表的数据拆成四张表来存放.
Newsid (1,2,3,…)
News 1,news2,news3,news4表
Id%4+1=[1,2,3,4]
比如 $_GET[‘id’]=17,
17%4+1=2,$tableName=’news’.’2’
Select * from news2 where id=17;
还可以用视图,把4张表形成一张视图.
Create view news as select * from news1 union select * from news2 union select * from news3;
(4) 删除视图
drop view 视图名
(5) 视图修改
Alter view 视图名 as select 语句
Alter view gui as select name,age,salary from t_test;
注意：此处的视图名是已经存在的.
(6) 视图与表的关系
视图是表的查询结果,自然表的数据改变了,影响视图的结果
(7) 视图改变了呢？
l 视图增删改也会影响表,但是视图并不是总是能增删改的.
l 视图的数据与表的数据一一对应时,可以修改.对于视图insert 还应该注意,视图必须包含表中没有默认值的列.
(8) 视图的algorithm
l Algorithm=merge/temptable/undefined
l Merge:当引用视图时,引用视图的语句与定义视图的语句合并.
l Temptable:当引用视图时,根据视图的创建语句建立一个临时表.
l Undefined:未定义,自动让系统帮你选择.
l Merge意味着视图只是一个语句规则,当查询视图时,把查询视图的语句(比如where)与创建时的语句where子句等合并,分析,形成一条select语句.
² 例:创建视图的语句：
create view g2 as select goods_is,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;
² 查询视图的语句：
select * from g2 group by cat_id;
² 最终执行的语句:
select goods_is,cat_id,goods_name,shop_price from goods group by cat_id order by cat_id asc,9 shop_price desc;
l 而temptable是根据创建语句瞬间创建一张临时表,然后查询视图的语句从该临时表查数据
create algorithm=temptable view g2 as select goods_is,cat_id,goods_name,shop_price from goods order by cat_id asc,shop_price desc;
查询视图的语句：
Select * from g2 group by cat_id;
最终执行的2句话:取数据并放在临时表,然后去查询临时表.
3. 字符集
(1) create table 表名(列声明,…)charset utf8;
(2) MySQL的字符集设置非常的灵活可以设置以下几种字符集
l 服务器默认字符集
l 数据库默认字符集
l 表默认字符集
l 列字符集
注意：如果某一个级别没有指定字符集,则继承上一级别.
(3) 以表声明utf8为例,存储的数据在表中,最终是utf8.
l 告诉服务器,我给你发送的数据是什么编码的?character_set_client.
例：set character_set_client=utf8;
l 告诉转换器,转换成什么编码?character_set_connection.
例：set character_set_connection=utf8;
l 查询的结果用什么编码?character_set_results.
例：set character_set_results=utf8;
l 如果以上3者都为字符集N,则可以简写为 set names N.
l 实例
(4) 推论:什么时候将会出现乱码
l client声明与事实不符.
l results与客户端页面不符的时候.
(5) 什么时候将会丢失数据
Connection和服务器的字符集比client范围小的时候,将会丢失数据.
4. 校对集
(1) 校对集：指字符集的排序规则
(2) 一种字符集可以有一个或者多个排序规则
(3) 以utf8为例,我们默认使用的是utf8_general_ci规则,也可以按二进制来排序,utf8_bin
(4) 怎么声明校对集
Create table tableName(列声明,….)charset utf8 collate utf8_general_ci;
Create table test(name varchar(10))charset utf8 collate utf8_bin;
注意：声明的校对集必须是字符集合法的校对集.
5. 触发器(trigger)
(1) 作用：监视某种情况并触发某种操作.
能监视：增加,删除,修改
触发操作：增加,删除,修改
(2) 触发器应用场合
l 当向一张表中添加或删除记录时,需要在相关表中进行同步操作.
比如：当一个订单产生时,订单所购的商品的库存量相应减少.
l 当表上某列数据的值与其它表中的数据有联系时.
比如：当某客户进行欠款消费,可以在生成订单时通过设计触发器判断该客户的累计欠款是否超出了最大限度.
l 当需要对某张表进行跟踪时。
比如：当有新订单产生时,需要及时通知相关人员进行处理,此时可以在订单表上设计添加触发器加以实现.
(3) 触发器创建语法之四要素
l 监事地点(table)
l 监视事件(insert/update/delete)
l 触发时间(after/before)
l 触发事件(insert/update/delete)
(4) 创建触发器的语法
Create trigger triggerName
after/before insert/update/delete on 表名
For each row #这句话是固定不变的
Begin
Sql语句；#一句或者多句,insert/update/delete范围内
End;
(5) 删除触发器的语法
drop trigger 触发器名;
(6) 查看触发器
show triggers;
(7) 如何在触发器中引用行的值
l 对应insert 而言,新增的行用new来表示,行中的每一列的值,用new.列名来表示.
例子1：客户下一个(添加)订单,库存相应减少.
delimiter $
create table goods(id int,name varchar(10),num int)charset utf8$
create table orders(oid int, gid int,much int)charset utf8$
insert into goods values(1,'猪',22),(2,'羊',19),(3,'狗',12),(4,'猫',8)$
insert into orders values(1,2,3)$
#监视地点 order表
#监视事件 insert
#触发事件 update
#触发时间after
create trigger tg1
after insert on order
for each row
begin
update goods set num=num-new.much where id=new.gid;
end $
注意：分节符delimiter $
l 对于delete来说,原本有一行,后来被删除,想引用被删除的这一行,用old来表示,old.列名就可以引用被删行中的值.
例子2：客户删除一个订单,库存相应增加.
#监视地点 order表
#监视事件 delete
#触发事件 update
#触发时间after
create trigger tg3
after delete on order
for each row
begin
update goods set num=num+old.much where id =old.gid;
end$
l 对于update来说,被修改的行,修改前的数据,用old来表示,old.列名引用被修改之前行中的值.
例子3：修改订单的数据时,库存相应改变
#监视地点 order表
#监视事件 update
#触发事件 update
#触发时间after
create trigger tg4
after update on order
for each row
begin
update goods set num=num+old.much-new.much where id=old.gid;
end$
(1) 触发器里after和before的区别
l after是先完成数据的增加,删除,修改在触发
l 触发的语句晚于监视的增加,删除,修改,无法影响前面的增加,删除,修改动作.
l Before 是先完成触发,再增加,删除,修改.
l 触发的语句先于监视的增加,删除,修改发生,我们有机会判断,修改即将发生的操作.
l 典型案例：
对于所下订单进行判断,如果订单的数量>5,就认为是恶意订单,强制把所订的商品数据改成5.
#监视地点 order表
#监视事件 insert
#触发事件 update
#触发时间 before
#目的：触发事件先于监视事件发生,并判断监视事件的数据
create trigger tg5
before insert on order
for each row
begin
if new.much>5 then
set new.much=5;
end if;
update goods set num=num-new.much where id=new.gid;
end $
1. 存储引擎
(1) 一部电影有MP4,WMV,AVI,RMVB,FLV等格式,同样的一部电影在硬盘上有不同的存储格式,所占空间与清晰程度也不一样.
(2) 表里的数据存储在硬盘上,存储的方式方法也有多种.对于用户来说,同样一张表的数据,无论用什么引擎来存储,用户看到的数据是一样的,但是对于服务器来说,是有区别的.
(3) 常用表的引擎
l Myisam:批量插入速度快,不支持事务,锁表.
l Innodb:批量插入相对较慢,支持事务,锁行.
l 全文索引：目前MySQL 5.5版本,Myisam,innodb都已经支持.
2. 事务的ACID特性
(1) 原子性(Atomicity)
通俗的说事务指的是一组操作,要么都成功执行,要么都不执行.
(2) 一致性(Consistency)
事务发生前和发生后,数据的总额依然匹配.
(3) 隔离性(Isolation)
在所有的操作没有执行完毕之前,其它的会话不能够看到中间改变的过程.
(4) 持久性(Durability)
事务产生的影响不能够撤销.
(5) 如果出了错误,事务也不允许撤销,只能通过 ” 补偿性事务”.
(6) 例子
转账：
李四：-----支出500,李四 -500
张三：-----收到500,张三 +500
(7) 关于事务的引擎
l 选用innodb
create table account(
id int,
name varchar(10),
money int
)engine=innodb charset=utf8;
insert into account values(1,'张三',3000);
insert into account values(2,'李四',3000);
l 开启事务
start transaction
l 提交事务
commit
l 回滚事务
rollback
注意：当一个事务commit或者rollback以后就结束了.但是有一些语句会造成事务的隐式的提交,比如又开启一个事务(start transaction)
(8) 不用事务的图解
(9) 用事务的图解
3. 数据库的备份
(1) 系统运行中,增量备份,与整体备份.
例如：每周日整体备份一次数据库,周一到周六备份当天的数据库.
(2) 如果周五数据库出了问题,可以用周日的整体备份+周一,二,三,四的数据库备份来恢复.
(3) 备份的工具
l 有第三方的收费备份工具.
l 目前我们所学的是系统自带的备份功能:mysqldump
(4) mysqldump可以导出库和表
l 例1：导出 db_study库下面的account表 goods表.
mysqldump -u用户名 -p密码 库名 表1 表2 表3>地址/备份文件名称
导出的是建表语句及insert语句.
mysqldump -uroot -pxuwei123 db_study account goods > D:\\accountTable.sql
l 例2：如何导出一个库下面的所有表
mysqldump -u用户名 -p密码 库名>地址/备份文件名称
mysqldump -uroot -pxuwei123 db_study >D:\\allTable.sql
l 例3：如何以库为单位导出
mysqldump -u用户名 -p密码 -B 库1 库2 库3>地址/备份文件名称
mysqldump -uroot -pxuwei123 -B db_study test practice mysql > D:\\ku.sql
l 例4：如何导出所有库
mysqldump -u用户名 -p密码 -A >地址/备份文件名称
mysqldump -uroot -pxuwei123 -A >D:\\allKu.sql
4. 数据库的恢复
(1) 对于库级的备份文件
l 登录到mysql命令行
l mysql>source 备份文件地址
(2) 对于表级的备份文件
l 登录到mysql命令行
l mysql> use 库名
l mysql> source 备份文件地址
(3) 针对库级的备份文件
l 不登录到mysql命令行
l mysql -u 用户名 -p密码 <备份文件地址
例: mysql –uroot –pxuwei123 <D:/ku.sql
(4) 针对表级的备份文件
l 不登录到mysql命令行
l mysql -u用户名 -p密码 库名<表级备份文件地址
mysql –uroot –pxuwei123 db_study <D:/ku.sql
5. 索引
(1) 索引的定义
索引是针对数据所建立的目录.
索引是一种特殊的数据库结构可以用来快速查询数据库表中的特定记录。
索引是提高数据库性能的重要方式.
(2) 索引与优化之索引算法
设有N条随机记录,不用索引,平均查找N/2次,那么用了索引之后呢
二叉树索引(btree)：log2’n
哈希索引(hash)：1
(3) 索引的作用(好处)
加快了查询速度(select)
(4) 索引的负面影响(坏处)
降低了增加,删除和修改的速度(insert/delete/update)
增加了表的文件大小(索引文件甚至可能比数据文件还大)
(5) 案例
设有新闻表15列,10列上有索引,共500万行数据,如何快速导入数据?
l 把空表的索引全部删除.
l 导入500W行数据.
l 数据导入完毕后,集中建立索引.
(6) 索引的创建原则
l 不要过度使用索引.
l 在where条件最频繁的列上加索引.
l 尽量索引散列值,过于集中的值加索引意义不大.
(7) 索引与优化之索引类型
l 普通索引(index)
仅仅是加快查询速度
l 唯一索引(unique)
行上的值不能重复
l 主键索引(primary key)
不能重复,主键必唯一,但是唯一索引不一定是主键,一张表上只能有一个主键,但是可以用一个或多个唯一索引.
l 全文索引(fulltext)
(8) 查看一张表上所有索引
show index from 表名
(9) 建立索引
alter table 表名 add index/unique/fulltext [索引名] 列名;
alter table 表名 add primary key[列名]; //不要加索引名字,因为主键只有一个
create table member(
id int,
email varchar(30),
tel char(11),
intro text
)engine myisam charset utf8;
l 查看表上的索引
show index from member \G;
l 给tel加一个普通索引
alter table member add index tel (tel);
show index from member \G;
l 给email加上唯一索引
alter table member add unique (email);
show index from member \G;
l 给intro加全文索引
alter table member add fulltext(intro);
l 给id加上主键索引
alter table member add primary key (id);
(10) 删除索引
l 删除非主键索引
alter table 表名 drop index 索引名;
alter table member drop index email;
l 删除主键索引
alter table 表名 drop primary key;
alter table member drop primary key；
6. 全文索引
(1) alter table member add fulltext(intro);
(2) insert into member values(1,’13925851862qq.com’,’15696205206’,’I come from China,welcome to itcast’);
(3) select * from member where intro like ’%itcast%’;
(4) 查询匹配度
select id,email,tel,match(intro) against (‘China’) from member;
(5) 关于全文索引的用法
match(全文索引名) against (‘keyword’);
(6) 关于全文索引的停止词
全文索引不针对非常频繁的词做索引
如：this, is, you, my等等.
(7) 全文索引在mysql默认情况下,对于中文意义不大.
因为英文有空格,标点符号来拆成单词,进而对单词进行索引;而对于中文,没有空格来隔开单词,mysql无法识别每个中文词.
7. 存储过程(procedure)
(1) 存储过程的概念
l 类似于函数,就是把一段代码封装起来.
l 当要执行一段代码的时候,可以通过调用该存储过程来实现.
l 在封装的语句体里面,可以用if/else, case, while等控制结构.
l 可以进行sql编程.
(2) 查看现有的存储过程
show procedure status;
(3) 删除存储过程
drop procedure 存储过程的名字
(4) 第一个存储过程,体会”封装sql”
l 创建存储过程p1()
delimiter $
create procedure p1()
begin
select * from account;
end$
l 调用存储过程
call p1();
(5) 第二个存储过程,体会”参数”
l 创建存储过程p2
create procedure p2(n int)
begin
select * from goods where num>n;
end$
l 调用存储过程p2
call p2(10);
(6) 第三个存储过程,体会 ”控制结构”
l 创建存储过程p3
create procedure p3(n int, j char(1))
begin
if j='h' then
select * from goods where num>n;
else
select * from goods where num<n;
end if;
end$
l 调用存储过程p3
call p3(10,’h’);
(7) 第四个存储过程,体会 ”循环”
l 创建存储过程p4, 计算1->n的和
create procedure p4(n smallint)
begin
declare i int;
declare sum int;
set i=1;
set sum=0;
while i<=n do
set sum=sum+i;
set i=i+1;
end while;
select sum;
end$
l 调用存储过程p4
call p4(100);
(8) 在mysql中,存储过程和函数的区别
l 一个是名称不同
l 二个是存储过程没有返回值
