mysql-day03
一、mysql存储引擎
1.1存储引擎介绍: 是mysql数据库软件自带的功能程序，
每种存储引擎的功能和数据存储方式也不同
存储引擎就处理表的处理器

1.2查看数据库服务支持的存储引擎有那些？
mysql> showengines;
InnoDB DEFAULT

1.3查看已有的表使用的存储引擎
show createtable 表名；

1.4修改数据库服务默认使用的存储引擎
]#vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
:wq
]# systemctlrestart mysqld

1.5修改表使用的存储引擎，或 建表时指定表使用的存储引擎
altertable表 engine=存储引擎名；
createtable表（字段列表）engine=存储引擎名；

1.6 常用存储引擎的特点
innodb特点：
支持事务、 事务回滚 、行级锁、外键
存储方式： 一个表对应2个存储文件
表名.frm表结构
表名.ibd 数据和索引

myisam特点
不支持事务、 事务回滚、外键
支持表级锁
存储方式： 一个表对应3个存储文件
表名.frm 表结构
表名.MYD 数据
表名.MYI 索引

事务：对数据库服务的访问过程（连接数据库服务器操作数据 断开连接）

事务回滚 ： 在事务执行过程中，任何一步操作失败，都会恢复之前的所有操作。
支持事务的表有对应的事务日志文件记录

插卡 （与数据库服务器建立连接）
转账：对方卡号 888888
金额50000
ok
提示转账成功 -50000+50000
提示转账失败 +50000
退卡

mysql数据库服务使用事务日志文件记录，对innodb存储引擎表执行的sql操作。
cd/var/lib/mysql/
ib_logfile0 -|
|------> 记录SQL命令
ib_logfile1 -|
insert intot1values(8888);
ibdata1 ----> 数据源（sql命令执行后产生的数据信息）

锁粒度：
表级锁（myisam）给整张表加锁(不管你访问一行还是几行 都会把整张表进行加锁)
行级锁 (innodb)只给表中当前被操作行加锁

锁的作用：解决对表的并发访问冲突问题。
select*fromt1where id <=20;
insert
delete fromt1;
updatet1setname="bob"wherename="lucy";
updatet1setname="tom"wherename="jerry";
根据客户端的访问类型 锁又分为读锁和写锁
锁类型
读锁（共享锁）select
写锁（互斥锁/排他锁）insert update delete

事务特性 (ACID)
• Atomic :原子性
– 事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败。
• Consistency : 一致性 例如 银行转账
– 事务操作的前后,表中的记录没有变化。
• Isolation :隔离性
– 事务操作是相互隔离不受影响的。
• Durability :持久性
– 数据一旦提交,不可改变,永久改变表数据

1.7 建表时如何决定表使用那种存储引擎
执行写操作多的表适合使用innodb存储引擎，此引擎支持行级锁,这样对表的并发访问量大。
执行查操作多的表适合使用myisam存储引擎,可以节省系统资源,此引擎支持表级锁,
++++++++++++++++++++++++++++++++++++++
二、数据导入导出（批量操作数据）
2.1数据导入的命令格式及数据导入时的注意事项
导入数据的命令格式：

数据导入：把系统文件的内容存储到数据库服务器的表里。
把系统已有用户的信息保存到db3库下的usertab表里

创建存储数据表
createdatabasedb3;
createtabledb3.usertab(
usernamechar(50),
password char(1),
uid int(2),
gidint(2),
comment char(100),
homedir char(100),
shell char(50),
index(username)
);
descdb3.usertab;
select * fromdb3.usertab;

导入数据
]# cp /etc/passwd/var/lib/mysql-files/

mysql>
loaddatainfile "/var/lib/mysql-files/passwd"
intotabledb3.usertab
fieldsterminated by ":"
linesterminatedby "\n";

mysql> altertabledb3.usertab
add
idint(2)primary keyauto_incrementfirst;

mysql> select from db3.usertab;
mysql> select from db3.usertab whereid=20;

loaddatainfile "/mysqldata/passwd"
intotabledb3.usertab
fieldsterminated by ":"
linesterminatedby "\n";

2.2数据导出的命令格式及数据导出时的注意事项

mysql>select username,uidfromdb3.usertab intooutfile "/mysqldata/user1.txt";

mysql>select*fromdb3.usertabintooutfile "/mysqldata/user2.txt";

mysql>selectusername,uidfromdb3.usertabintooutfile "/mysqldata/user3.txt" fieldsterminatedby"###";

]# cat/mysqldata/user1.txt
]# cat/mysqldata/user2.txt
]# cat/mysqldata/user3.txt

三、管理表记录(db3.usertab)
插入记录

mysql> insert intousertab
values
(43,"yaya","x",1001,1001,"","/home/yaya","/bin/bash");

mysql> insert intousertab
values (50,"yaya2","x",1002,1002,"","/home/yaya2","/sbin/nologin"),(51,"7yaya","x",1003,1003,"","/home/7yaya","/sbin/nologin");

insert into usertab(username,homedir,shell)
values
("lucy","/home/lucy","/bin/bash");

insert into usertab(username,homedir,shell)
values
("lu8cy","/home/lu8cy","/bin/bash"),("tom","/home/tom","/bin/bash"),("lilei","/home/lilei","/bin/bash");

+++++++++查看记录
select*from db3.usertab;

select *fromusertabwhereid = 1;

selectid,username,passwordfrom db3.usertab;

selectusername,uid,shell fromusertabwhereid = 1;

------修改记录
update db3.usertabset password="A";
update db3.usertabset password="x" where id=1;
select * fromdb3.usertab;

-----删除记录
deletefrom db3.usertabwhere id=3;

四、匹配条件（查看selcet修改update删除delete记录时可以加条件）

4.1数值比较 字段名 符号数字
= !=<<= > >=

selectusernamefromusertabwhereuid=10;
selectid,username,uidfromusertabwhereuid=1001;
select*fromusertabwhereid<=10;

4.2字符比较 字段名 符号“字符串”
= !=
selectusernamefromusertabwhereusername="apache";
selectusername,shellfromusertabwhereshell="/bin/bash";
selectusername,shell fromusertabwhereshell!="/bin/bash";

4.3 范围内比较
字段名between数字1and数字2 在...之间...
字段名 in(值列表)在...里
字段名 notin (值列表) 不在...里
selectusernamefromusertabwhere uidbetween 100 and 150;

selectusername,uidfromusertabwhere uidin (10,20,30,50);

selectusername,uidfromusertabwhere usernamein("root","rsync","mysql");

selectusernamefromusertabwhere username not in ("root","bin");

4.4 逻辑比较（就是有个查询条件）
逻辑与 and多个条件同时成立才匹配
逻辑或 or 多个条件,某一个条件成立就匹配
逻辑非！ 或 not取反

selectusername,uidfrom usertabwhereusername="root" anduid=0 andshell="/bin/bash";

selectusername,uidfrom usertabwhereusername="root"oruid=1orshell="/bin/bash";

selectusername,uidfrom usertabwhereusername="root"or username="apache" or username="bob";

4.5匹配空 字段名isnull
匹配空 字段名isnotnull

selectusername,uid,gidfromusertab
where
uid is nullandgidisnull;

mysql> update usertabset uid=3000,gid=3000 where username="lucy";

selectidfromusertabwherename="yaya"anduid is not null;

updateusertabsetusername=nullwhere id=2;

4.6 模糊匹配
字段名like '表达式';
% 表示零个或多个字符
_表任意一个字符

selectusername fromusertab whereusername like' ';
selectusername fromusertab whereusername like'a_ _t';

insertinto usertab(username)values("a");

selectusername fromusertab whereusername like'a%';
selectusername fromusertab whereusername like'%';

4.7 正则匹配
字段名regexp '正则表达式';
^ $.* []
selectusernamefrom usertab whereusernameregexp '[0-9]';
selectusernamefrom usertab whereusernameregexp '^[0-9]';
selectusernamefrom usertab whereusernameregexp '[0-9]$';

selectusernamefrom usertab whereusernameregexp 'a.*t';

selectusernamefrom usertab whereusernameregexp '^a.*t$';

selectusername,uidfrom usertab whereuidregexp '..';
selectusername,uidfrom usertab whereuidregexp '^..$';

4.7 四则运算(select 和 update 操作是可以做数学计算)
字段类型必须数值类型（整型 或浮点型）

selectid,username,uidfrom usertab whereid <=10;
updateusertabsetuid=uid+1whereid <=10;

select username ,uid,gidfrom usertab where usernane="mysql";
select username ,uid,gid, uid+gidaszhfrom usertab where username="mysql";

select username ,uid,gid, uid+gidaszh,(uid+gid)/2aspjz from usertab where username="mysql";

altertableusertab addagetinyint(2)unsigneddefault 21 after username;

mysql> selectusername,age from usertab;
selectusername , age ,2018-age s_year from usertab where username="root";

4.9聚集函数（对字段的值做统计，字段的类型要求是数值类型）
count(字段名）统计字段值的个数
sum(字段名）求和
max(字段名）输出字段值的最大值
min(字段名）输出字段值的最小值
avg(字段名）输出字段值的平均值
selectmax(uid)from usertab;
selectsum(uid)from usertab;
selectmin(uid)from usertab;
selectavg(uid)from usertab;
selectcount(id)from usertab;
selectcount(username) from usertab where shell="/bin/bash";

4.10查询不显示字段重复值distinct 字段名

selectdistinct shellfromusertab;

selectdistinct shellfromusertab whereuid >10and uid<=100;

4.11查询分组
sql查询 group by字段名；

select shellfromusertab whereuid >10and uid<=100
groupbyshell;

4.12 查询排序 (按照数值类型的字段排队)
sql查询orderby字段名asc|desc;

selectusername,uid fromusertab whereuid >10and uid<=100orderbyuid;

selectusername,uid fromusertab whereuid >10and uid<=100orderbyuiddesc;

查询结果过滤
基本用法
– SQL 查询 having条件表达式;
– SQL 查询 where 条件 HAVING 条件表达式;
– SQL 查询 group by 字段名 HAVING 条件表达式;

4.13 限制查询显示行数(默认显示所有查询的记录)
sql查询limit数字； 显示查询结果的前几行
sql查询limit数字1，数字2；显示查询结果指定范围的行

selectusername,uid fromusertab whereuid >10and uid<=100
orderbyuiddesclimit1;

selectusername,uid fromusertab whereuid >10and uid<=100
orderbyuiddesclimit 2,3;

##########################################################################################
一、多表查询
1.1复制表
作用？ 备份表 和 快速建表
命令格式？ create table 库.表 sql查询命令；

例子？
createtabledb3.user2 select *fromdb3.usertab;

createtabledb3.user3 select username,uid,shellfromdb3.usertablimit 5;

createdatabase db4;
create tabledb4.t1 select *fromdb3.usertabwhere 1 =2;

create tabledb4.t2 selectid,username,uid,homedirfromdb3.usertabwhere 1 =2;

1.2 where嵌套查询
select username,uidfrom db3.usertab where uid < (selectavg(uid)fromdb3.usertab）

;

mysql> selectusername,uidfrom db3.usertab where uid > (selectavg(uid) from

db3.usertab);

selectusername fromdb3.usertab
where usernamein
(select user frommysql.user where host="localhost");

1.3多表查询
mysql> create table db4.t3
-> selectusername,uid,shell,homedir from db3.usertab
-> limit3;

mysql> createtabledb4.t4
-> select username,uid,gidfrom db3.usertab limit 5;

 3* 5 =15 

select *from t3,t4;迪卡尔集

mysql> selectt3.username,t4.username fromt3,t4
-> where
-> t3.username =t4.username;

mysql> selectt3.*,t4.username fromt3,t4 where t3.username =t4.username;

select *from t3,t4
where
t3.uid = t4.uid ;

select t3.* , t4.gidfrom t3,t4
where
t3.uid = t4.uid ;

select t3.username , t4.usernamefrom t3,t4
where
t3.uid = t4.uid ;

select t3.username,t4.usernamefrom t3,t4
where
t3.uid = t4.uid
andt3.username is not null
andt4.username is not null;

1.4连接查询
mysql> createtabledb4.t5
select username,uid,gid,shellfrom db3.usertab
where uid>=100and uid<=500;

mysql> createtabledb4.t6
select username,uid,gid,shellfrom db3.usertab
where uid>=100and uid<=500limit3;

select *from t6 rightjoint5on
t6.uid =t5.uid;

select *from t6 leftjoint5on t6.uid =t5.uid;

select t5.username,t6.username from t6 rightjoint5on
t6.uid =t5.uid;

selectt5.username,t6.usernamefrom t6 leftjoint5on t6.uid =t5.uid;

2.2在数据库服务器上安装图形管理工具phpmyadmin
准备软件的运行环境lamp/lnmp
]# rpm -q httpdphpphp-mysql
]# yum-y install httpd phpphp-mysql
]# systemctlstatus httpd
]#systemctlrestart httpd
]#systemctlenable httpd

测试运行环境
[root@mysql51 mysql]# vim/var/www/html/test.php
<?php
$x=mysql_connect("localhost","root","123456");
if($x){ echo "ok";}else{echo "no";};
?>
[root@mysql51 mysql]#
[root@mysql51 mysql]# yum-y installelinks

]#elinks --dump http://localhost/test.php
ok

安装软件phpMyAdmin-2.11.11-all-languages.tar.gz

]#tar -zxf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/
]# cd /var/www/html/
]#mv phpMyAdmin-2.11.11-all-languagesphpmyadmin

修改软件的配置文件定义管理的数据库服务器
]#cdphpmyadmin
]#cp config.sample.inc.php config.inc.php
]#vim config.inc.php
17 $cfg['blowfish_secret'] = 'plj123';
31 $cfg['Servers'][$i]['host'] = 'localhost';
:wq

在客户端访问软件 管理数据库服务器
打开浏览器输入url地址 访问软件
http://192.168.4.51/phpmyadmin
用户名root
密 码123456

三、用户授权与权限撤销
3.0管理员密码管理
恢复数据库管理员本机登录密码
]#systemctlstop mysqld
]# vim /etc/my.cnf
[mysqld]
skip-grant-tables
#validate_password_policy=0
#validate_password_length=6
:wq
]# systemctlstart mysqld
]#mysql

mysql> selecthost,user,authentication_stringfrom mysql.user;
mysql>
updatemysql.user
setauthentication_string=password("abc123")
where
host="localhost"anduser="root";

mysql> flushprivileges;
mysql>quit

]# vim /etc/my.cnf
[mysqld]
#skip-grant-tables
validate_password_policy=0
validate_password_length=6
:wq

]# systemctlrestart mysqld
]#mysql -uroot-pabc123
mysql>

操作系统管理员 修改数据库管理员root本机登录的密码
[root@mysql51 ~]# mysqladmin-hlocalhost-uroot-ppassword"654321"
Enter password: 当前登录密码

3.1 什么是用户授权： 在数据库服务器上添加连接用户，添加时可以设置用户的访问权限和连接的密

码。默认只允许数据库管理员root用户在本机登录。默认只有数据库管理员root用户在本机登录才有

授权权限。

3.2 用户授权命令的语法格式
mysql>grant 权限列表on数库名 to用户名@"客户端地址"
identified by"密码" [withgrantoption];

例子1： 允许客户端254主机可以使用root连接，连接密码是123456，连接后的访问权限是多所有库

所有表有完全访问权限 ，且有授权权限。
192.168.4.51mysql>
db3.
grantall on .* to root@"192.168.4.254"
-> identified by "123456"
-> withgrant option;

3.3在客户端使用授权用户连接数据库服务器
]# ping-c 2 192.168.4.51
]# whichmysql
]#yum-yinstallmariadb
]#mysql -h数据库服务器ip地址-u用户名 -p密码

192.168.4.254]#mysql-h292.168.4.51-uroot-p123456
mysql>

grantselect,update(name) on studb.t8 to yaya3@"%" identified by "123456";

grantselect,insert,update onstudb.dogperson toyaya@"localhost" identified by

"123456";

grantallon studb.* toadmin@"%" identified by "123456";

3.4 数据库服务器使用授权库存储授权信息
mysql库
userdescmysql.user; select* frommysql.user
db
tables_priv
clomoun_priv

3.3 撤销用户权限命令的语法格式
mysql> revoke权限列表on数库名from 用户名@"客户端地址" ;

例子1 ： 撤销254主机 使用root用户连接时，授权权限。
mysql> revoke grant option on . from 'root'@'192.168.4.254';

例子2 ： 通过修改表记录的方式撤销用户的访问权限
mysql> updatemysql.user
set Select_priv="N"
whereuser= 'root'and host='192.168.4.254';

mysql> flushprivileges;

例子3：撤销254主机 使用root用户连接时 所有权限
mysql> revokeallon . from 'root'@'192.168.4.254';

例子4删除授权用户 'root'@'192.168.4.254';
dropuser 'root'@'192.168.4.254';

3.5 工作中如何授权
管理者 给完全权限且有授权权限
使用者只给对存储数据的库有select和insert的权限