数据库的基本操作2

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

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

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

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

1.5 修改表使用的存储引擎，或 建表时指定表使用的存储引擎
alter table 表 engine=存储引擎名；
create table 表（字段列表）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 into t1 values(8888);
ibdata1 ----> 数据源（sql命令执行后产生的数据信息）

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

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

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

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

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

创建存储数据表
create database db3;
create table db3.usertab(
username char(50),
password char(1),
uid int(2),
gid int(2),
comment char(100),
homedir char(100),
shell char(50),
index(username)
);
desc db3.usertab;
select * from db3.usertab;

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

mysql>
load data infile "/var/lib/mysql-files/passwd"
into table db3.usertab
fields terminated by ":"
lines terminated by "\n";

mysql> alter table db3.usertab
add
id int(2) primary key auto_increment first;

mysql> select from db3.usertab;
mysql> select from db3.usertab where id=20;

load data infile "/mysqldata/passwd"
into table db3.usertab
fields terminated by ":"
lines terminated by "\n";

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

mysql>select username,uid from db3.usertab into outfile "/mysqldata/user1.txt";

mysql>select * from db3.usertab into outfile "/mysqldata/user2.txt";

mysql>select username,uid from db3.usertab into outfile "/mysqldata/user3.txt" fields terminated by "###";

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

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

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

mysql> insert into usertab
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 * from usertab where id = 1;

select id,username,password from db3.usertab;

select username,uid,shell from usertab where id = 1;

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

-----删除记录
delete from db3.usertab where id=3;

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

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

select username from usertab where uid=10;
select id,username,uid from usertab where uid=1001;
select * from usertab where id<=10;

4.2 字符比较 字段名 符号 "字符串"
= !=
select username from usertab where username="apache";
select username,shell from usertab where shell="/bin/bash";
select username,shell from usertab where shell!="/bin/bash";

4.3 范围内比较
字段名 between 数字1 and 数字2 在...之间...
字段名 in (值列表) 在...里
字段名 not in (值列表) 不在...里
select username from usertab where uid between 100 and 150;

select username,uid from usertab where uid in (10,20,30,50);

select username,uid from usertab where username in ("root","rsync","mysql");

select username from usertab where username not in ("root","bin");

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

select username,uid from usertab where username="root" and uid=0 and shell="/bin/bash";

select username,uid from usertab where username="root" or uid=1 or shell="/bin/bash";

select username,uid from usertab where username="root" or username="apache" or username="bob";

4.5 匹配空 字段名 is null
匹配空 字段名 is not null

select username,uid,gid from usertab
where
uid is null and gid is null;

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

select id from usertab where name="yaya" and uid is not null;

update usertab set username=null where id=2;

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

select username from usertab where username like ' ';
select username from usertab where username like 'a_ _t';

insert into usertab(username)values("a");

select username from usertab where username like 'a%';
select username from usertab where username like '%';

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

select username from usertab where username regexp 'a.*t';

select username from usertab where username regexp '^a.*t$';

select username,uid from usertab where uid regexp '..';
select username,uid from usertab where uid regexp '^..$';

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

• / %

select id,username,uid from usertab where id <=10;
update usertab set uid=uid+1 where id <=10;

select username ,uid,gid from usertab where usernane="mysql";
select username ,uid,gid, uid+gid as zh from usertab where username="mysql";

select username ,uid,gid, uid+gid as zh , (uid+gid)/2 as pjz from usertab where username="mysql";

alter table usertab add age tinyint(2) unsigned default 21 after username;

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

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

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

select distinct shell from usertab;

select distinct shell from usertab where uid >10 and uid<=100;

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

select shell from usertab where uid >10 and uid<=100
group by shell;

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

select username,uid from usertab where uid >10 and uid<=100 order by uid;

select username,uid from usertab where uid >10 and uid<=100 order by uid desc;

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

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

select username,uid from usertab where uid >10 and uid<=100
order by uid desc limit 1;

select username,uid from usertab where uid >10 and uid<=100
order by uid desc limit 2,3;

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

例子？
create table db3.user2 select * from db3.usertab;

create table db3.user3 select username,uid,shell from db3.usertab limit 5;

create database db4;
create table db4.t1 select * from db3.usertab where 1 =2;

create table db4.t2 select id,username,uid,homedir from db3.usertab where 1 =2;

1.2 where嵌套查询
select username,uid from db3.usertab where uid < (select avg(uid) from db3.usertab）

;

mysql> select username,uid from db3.usertab where uid > (select avg(uid) from

db3.usertab);

select username from db3.usertab
where username in
(select user from mysql.user where host="localhost");

1.3多表查询
mysql> create table db4.t3
-> select username,uid,shell,homedir from db3.usertab
-> limit 3;

mysql> create table db4.t4
-> select username,uid,gid from db3.usertab limit 5;

                     3  * 5 =  15 

select * from t3,t4; 迪卡尔集

mysql> select t3.username,t4.username from t3,t4
-> where
-> t3.username = t4.username;

mysql> select t3.*,t4.username from t3,t4 where t3.username = t4.username;

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

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

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

select t3.username,t4.username from t3,t4
where
t3.uid = t4.uid
and t3.username is not null
and t4.username is not null;

1.4 连接查询
mysql> create table db4.t5
select username,uid,gid,shell from db3.usertab
where uid>=100 and uid<=500;

mysql> create table db4.t6
select username,uid,gid,shell from db3.usertab
where uid>=100 and uid<=500 limit 3;

select * from t6 right join t5 on
t6.uid = t5.uid;

select * from t6 left join t5 on t6.uid = t5.uid;

select t5.username,t6.username from t6 right join t5 on
t6.uid = t5.uid;

select t5.username,t6.username from t6 left join t5 on t6.uid = t5.uid;

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

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

]# 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-languages phpmyadmin

修改软件的配置文件定义管理的数据库服务器
]#cd phpmyadmin
]#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 管理员密码管理
恢复数据库管理员本机登录密码
]#systemctl stop mysqld
]# vim /etc/my.cnf
[mysqld]
skip-grant-tables
#validate_password_policy=0
#validate_password_length=6
:wq
]# systemctl start mysqld
]#mysql

mysql> select host,user,authentication_string from mysql.user;
mysql>
update mysql.user
set authentication_string=password("abc123")
where
host="localhost" and user="root";

mysql> flush privileges;
mysql>quit

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

]# systemctl restart mysqld
]# mysql -uroot -pabc123
mysql>

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

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

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

授权权限。

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

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

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

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

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

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

grant select,insert,update on studb.dogperson to yaya@"localhost" identified by

"123456";

grant all on studb.* to admin@"%" identified by "123456";

3.4 数据库服务器使用授权库存储授权信息
mysql库
user desc mysql.user; select * from mysql.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> update mysql.user
set Select_priv="N"
where user= 'root' and host='192.168.4.254';

mysql> flush privileges;

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

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

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