數據庫的基本操作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
<?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的權限