數(shù)據(jù)庫(kù)的基本操作2

mysql-day03
一、mysql存儲(chǔ)引擎
1.1 存儲(chǔ)引擎介紹: 是mysql數(shù)據(jù)庫(kù)軟件自帶的功能程序，
每種存儲(chǔ)引擎的功能和數(shù)據(jù)存儲(chǔ)方式也不同
存儲(chǔ)引擎就處理表的處理器

1.2 查看數(shù)據(jù)庫(kù)服務(wù)支持的存儲(chǔ)引擎有那些？
mysql> show engines;
InnoDB DEFAULT

1.3 查看已有的表使用的存儲(chǔ)引擎
show create table 表名；

1.4 修改數(shù)據(jù)庫(kù)服務(wù)默認(rèn)使用的存儲(chǔ)引擎
]#vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
:wq
]# systemctl restart mysqld

1.5 修改表使用的存儲(chǔ)引擎，或 建表時(shí)指定表使用的存儲(chǔ)引擎
alter table 表 engine=存儲(chǔ)引擎名；
create table 表（字段列表）engine=存儲(chǔ)引擎名；

1.6 常用存儲(chǔ)引擎的特點(diǎn)
innodb特點(diǎn)：
支持事務(wù) 、 事務(wù)回滾 、行級(jí)鎖 、外鍵
存儲(chǔ)方式： 一個(gè)表對(duì)應(yīng)2個(gè)存儲(chǔ)文件
表名.frm 表結(jié)構(gòu)
表名.ibd 數(shù)據(jù)和索引

myisam特點(diǎn)
不支持事務(wù) 、 事務(wù)回滾、外鍵
支持表級(jí)鎖
存儲(chǔ)方式： 一個(gè)表對(duì)應(yīng)3個(gè)存儲(chǔ)文件
表名.frm 表結(jié)構(gòu)
表名.MYD 數(shù)據(jù)
表名.MYI 索引

事務(wù)：對(duì)數(shù)據(jù)庫(kù)服務(wù)的訪問(wèn)過(guò)程（連接數(shù)據(jù)庫(kù)服務(wù)器 操作數(shù)據(jù) 斷開(kāi)連接）

事務(wù)回滾 ： 在事務(wù)執(zhí)行過(guò)程中，任何一步操作失敗，都會(huì)恢復(fù)之前的所有操作。
支持事務(wù)的表有對(duì)應(yīng)的事務(wù)日志文件記錄

插卡 （與數(shù)據(jù)庫(kù)服務(wù)器建立連接）
轉(zhuǎn)賬： 對(duì)方卡號(hào) 888888
金額 50000
ok
提示轉(zhuǎn)賬成功 -50000 +50000
提示轉(zhuǎn)賬失敗 +50000
退卡

mysql數(shù)據(jù)庫(kù)服務(wù)使用事務(wù)日志文件記錄，對(duì)innodb存儲(chǔ)引擎表執(zhí)行的sql操作。
cd /var/lib/mysql/
ib_logfile0 -|
|------> 記錄SQL命令
ib_logfile1 -|
insert into t1 values(8888);
ibdata1 ----> 數(shù)據(jù)源（sql命令執(zhí)行后產(chǎn)生的數(shù)據(jù)信息）

鎖粒度：
表級(jí)鎖（myisam）給整張表加鎖 (不管你訪問(wèn)一行還是幾行 都會(huì)把整張表進(jìn)行加鎖)
行級(jí)鎖 (innodb) 只給表中當(dāng)前被操作行加鎖

鎖的作用：解決對(duì)表的并發(fā)訪問(wèn)沖突問(wèn)題。
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";
根據(jù)客戶端的訪問(wèn)類型 鎖又分為讀鎖和寫鎖
鎖類型
讀鎖 （共享鎖） select
寫鎖 （互斥鎖/排他鎖） insert update delete

事務(wù)特性 (ACID)
? Atomic :原子性
– 事務(wù)的整個(gè)操作是一個(gè)整體,不可分割,要么全部成功,要么全部失敗。
? Consistency : 一致性 例如 銀行轉(zhuǎn)賬
– 事務(wù)操作的前后,表中的記錄沒(méi)有變化。
? Isolation :隔離性
– 事務(wù)操作是相互隔離不受影響的。
? Durability :持久性
– 數(shù)據(jù)一旦提交,不可改變,永久改變表數(shù)據(jù)

1.7 建表時(shí)如何決定表使用那種存儲(chǔ)引擎
執(zhí)行寫操作多的表適合使用innodb存儲(chǔ)引擎，此引擎支持行級(jí)鎖,這樣對(duì)表的并發(fā)訪問(wèn)量大。
執(zhí)行查操作多的表適合使用myisam存儲(chǔ)引擎,可以節(jié)省系統(tǒng)資源,此引擎支持表級(jí)鎖,
++++++++++++++++++++++++++++++++++++++
二、數(shù)據(jù)導(dǎo)入導(dǎo)出（批量操作數(shù)據(jù)）
2.1 數(shù)據(jù)導(dǎo)入的命令格式及數(shù)據(jù)導(dǎo)入時(shí)的注意事項(xiàng)
導(dǎo)入數(shù)據(jù)的命令格式：

數(shù)據(jù)導(dǎo)入：把系統(tǒng)文件的內(nèi)容存儲(chǔ)到數(shù)據(jù)庫(kù)服務(wù)器的表里。
把系統(tǒng)已有用戶的信息保存到db3庫(kù)下的usertab表里

創(chuàng)建存儲(chǔ)數(shù)據(jù)表
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;

導(dǎo)入數(shù)據(jù)
]# 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 數(shù)據(jù)導(dǎo)出的命令格式及數(shù)據(jù)導(dǎo)出時(shí)的注意事項(xiàng)

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記錄時(shí)可以加條件）

4.1 數(shù)值比較 字段名 符號(hào) 數(shù)字
= != < <= > >=

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

4.2 字符比較 字段名 符號(hào) “字符串”
= !=
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 范圍內(nèi)比較
字段名 between 數(shù)字1 and 數(shù)字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 邏輯比較（就是有個(gè)查詢條件）
邏輯與 and 多個(gè)條件同時(shí)成立 才匹配
邏輯或 or 多個(gè)條件,某一個(gè)條件成立 就匹配
邏輯非 ！ 或 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 '表達(dá)式';
% 表示零個(gè)或多個(gè)字符
_ 表任意一個(gè)字符

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 '正則表達(dá)式';
^ $ . * [ ]
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 四則運(yùn)算(select 和 update 操作是可以做數(shù)學(xué)計(jì)算)
字段類型必須數(shù)值類型（整型 或浮點(diǎn)型）

• • • / %

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聚集函數(shù)（對(duì)字段的值做統(tǒng)計(jì)，字段的類型要求是數(shù)值類型）
count(字段名）統(tǒng)計(jì)字段值的個(gè)數(shù)
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 查詢不顯示字段重復(fù)值 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 查詢排序 (按照數(shù)值類型的字段排隊(duì))
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;

查詢結(jié)果過(guò)濾
基本用法
– SQL 查詢 having 條件表達(dá)式;
– SQL 查詢 where 條件 HAVING 條件表達(dá)式;
– SQL 查詢 group by 字段名 HAVING 條件表達(dá)式;

4.13 限制查詢顯示行數(shù)(默認(rèn)顯示所有查詢的記錄)
sql查詢 limit 數(shù)字； 顯示查詢結(jié)果的前幾行
sql查詢 limit 數(shù)字1，數(shù)字2； 顯示查詢結(jié)果指定范圍的行

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 復(fù)制表
作用？ 備份表 和 快速建表
命令格式？ create table 庫(kù).表 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 在數(shù)據(jù)庫(kù)服務(wù)器上安裝圖形管理工具phpmyadmin
準(zhǔn)備軟件的運(yùn)行環(huán)境 lamp/lnmp
]# rpm -q httpd php php-mysql
]# yum -y install httpd php php-mysql
]# systemctl status httpd
]#systemctl restart httpd
]#systemctl enable httpd

測(cè)試運(yùn)行環(huán)境
[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 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

修改軟件的配置文件定義管理的數(shù)據(jù)庫(kù)服務(wù)器
]#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

在客戶端訪問(wèn)軟件 管理數(shù)據(jù)庫(kù)服務(wù)器
打開(kāi)瀏覽器輸入url地址 訪問(wèn)軟件
http://192.168.4.51/phpmyadmin
用戶名 root
密 碼 123456

三、用戶授權(quán)與權(quán)限撤銷
3.0 管理員密碼管理
恢復(fù)數(shù)據(jù)庫(kù)管理員本機(jī)登錄密碼
]#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>

操作系統(tǒng)管理員 修改數(shù)據(jù)庫(kù)管理員root本機(jī)登錄的密碼
[root@mysql51 ~]# mysqladmin -hlocalhost -uroot -p password "654321"
Enter password: 當(dāng)前登錄密碼

3.1 什么是用戶授權(quán)： 在數(shù)據(jù)庫(kù)服務(wù)器上添加連接用戶，添加時(shí)可以設(shè)置用戶的訪問(wèn)權(quán)限和連接的密

碼。默認(rèn)只允許數(shù)據(jù)庫(kù)管理員root用戶在本機(jī)登錄。默認(rèn)只有數(shù)據(jù)庫(kù)管理員root用戶在本機(jī)登錄才有

授權(quán)權(quán)限。

3.2 用戶授權(quán)命令的語(yǔ)法格式
mysql> grant 權(quán)限列表 on 數(shù)庫(kù)名 to 用戶名@"客戶端地址"
identified by "密碼" [with grant option];

例子1： 允許客戶端254主機(jī)可以使用root連接，連接密碼是123456，連接后的訪問(wèn)權(quán)限是多所有庫(kù)

所有表有完全訪問(wèn)權(quán)限 ，且有授權(quán)權(quán)限。
192.168.4.51mysql>
db3.
grant all on .* to root@"192.168.4.254"
-> identified by "123456"
-> with grant option;

3.3 在客戶端使用授權(quán)用戶連接數(shù)據(jù)庫(kù)服務(wù)器
]# ping -c 2 192.168.4.51
]# which mysql
]# yum -y install mariadb
]# mysql -h數(shù)據(jù)庫(kù)服務(wù)器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 數(shù)據(jù)庫(kù)服務(wù)器使用授權(quán)庫(kù)存儲(chǔ)授權(quán)信息
mysql庫(kù)
user desc mysql.user; select * from mysql.user
db
tables_priv
clomoun_priv

3.3 撤銷用戶權(quán)限命令的語(yǔ)法格式
mysql> revoke 權(quán)限列表 on 數(shù)庫(kù)名 from 用戶名@"客戶端地址" ;

例子1 ： 撤銷254主機(jī) 使用root用戶連接時(shí)，授權(quán)權(quán)限。
mysql> revoke grant option on . from 'root'@'192.168.4.254';

例子2 ： 通過(guò)修改表記錄的方式撤銷用戶的訪問(wèn)權(quán)限
mysql> update mysql.user
set Select_priv="N"
where user= 'root' and host='192.168.4.254';

mysql> flush privileges;

例子3： 撤銷254主機(jī) 使用root用戶連接時(shí) 所有權(quán)限
mysql> revoke all on . from 'root'@'192.168.4.254';

例子4 刪除授權(quán)用戶 'root'@'192.168.4.254';
drop user 'root'@'192.168.4.254';

3.5 工作中如何授權(quán)
管理者 給完全權(quán)限且有授權(quán)權(quán)限
使用者 只給對(duì)存儲(chǔ)數(shù)據(jù)的庫(kù)有select和insert的權(quán)限