上午：
MySQL存儲引擎
作為可插拔的組件提供
-mysql服務軟件自帶的功能程式，處理表的處理器
-不同的存儲引擎有不同的功能和數據存儲方式
默認的存儲引擎
MYSQL 5.0/5.1 —-MyISAM
MySQL 5.5/5.6 —-InnoDB
mysql-5.7.17社區開源板
mysql> show engines;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.00 sec)
查看表使用的存儲引擎
mysql> show create table t32;
+——-+——————————————————————————————–+
| Table | Create Table |
+——-+——————————————————————————————–+
| t32 | CREATE TABLE `t32` (
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——-+——————————————————————————————–+
1 row in set (0.00 sec)
建表時指定表使用的存儲引擎
mysql> create table t32(name char(10))engine=myisam;
Query OK, 0 rows affected (0.32 sec)
mysql> show create table t32;
+——-+——————————————————————————————–+
| Table | Create Table |
+——-+——————————————————————————————–+
| t32 | CREATE TABLE `t32` (
`name` char(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+——-+——————————————————————————————–+
1 row in set (0.00 sec)
修改表使用的存儲引擎
mysql> alter table t32 engine=InnoDB
-> ;
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t32;
+——-+——————————————————————————————–+
| Table | Create Table |
+——-+——————————————————————————————–+
| t32 | CREATE TABLE `t32` (
`name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+——-+——————————————————————————————–+
1 row in set (0.00 sec)
查看資料庫服務使用的存儲引擎
mysql> show engines;
建表時指定使用的存儲引擎
mysql> create table t32(name char(10))engine=myisam;
Query OK, 0 rows affected (0.32 sec)
修改資料庫服務軟件默認使用的存儲引擎
[root@localhost ~]# cat /etc/my.cnf
default-storage-engine=myisam
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
mysql> show engines;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.00 sec)
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table t34(age int);
Query OK, 0 rows affected (0.19 sec)
mysql> use db1;
Database changed
mysql> show create table t34;
+——-+——————————————————————————————+
| Table | Create Table |
+——-+——————————————————————————————+
| t34 | CREATE TABLE `t34` (
`age` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+——-+——————————————————————————————+
1 row in set (0.00 sec)
工作中怎麼選用存儲引擎
常用存儲引擎特點
myisam
不支持事務、事務回滾、外鍵
支持表級鎖
存儲方式：一個表對應3個存儲文件
表名.frm 表結構
表名.MYD 數據
表名.NYI 索引
事務：對資料庫服務的訪問過程(連接資料庫伺服器、操作數據、斷開連接)
支持事務的表 有對應的事務日志文件記錄執行過的SQL操作
事務特性：原子性(操作要麼成功要麼失敗)，一致性(操作沒有完成，表中的記錄是沒有變化的)，隔離性(操作隔離)，持久性(數據提交則數據永久改變)
ib_buffer_pool
ibdata1
ib_logfile0
事務回滾：在事務執行過程中，任何一步操作失敗，都會恢復之前的所有操作
mysql> create table t36(id int)engine=innodb;
Query OK, 0 rows affected (0.23 sec)
mysql> insert into t36 values(100);
Query OK, 1 row affected (0.07 sec)
mysql> select *from t36;
+——+
| id |
+——+
| 100 |
+——+
1 row in set (0.00 sec)
mysql> show variables like "autocommit";
+—————+——-+
| Variable_name | Value |
+—————+——-+
| autocommit | ON |
+—————+——-+
1 row in set (0.01 sec)
mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "autocommit";
+—————+——-+
| Variable_name | Value |
+—————+——-+
| autocommit | OFF |
+—————+——-+
1 row in set (0.00 sec)
mysql> delete form t36;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't36' at line 1
mysql> delete from t36;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t36;
Empty set (0.00 sec)
mysql> roolback
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'roolback' at line 1
mysql> rollback;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from t36;
+——+
| id |
+——+
| 100 |
+——+
1 row in set (0.00 sec)
mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t36;
+——+
| id |
+——+
| 100 |
+——+
1 row in set (0.00 sec)
mysql> set autocommit=ON;
Query OK, 0 rows affected (0.00 sec)
innodb
支持事務、事務回滾、行級鎖、外鍵
存儲方式：一個表對應2個存儲文件
表名.frm 表結構
表名.ibd 數據和索引
鎖粒度：
行級鎖 innodb 隻給當前被訪問的行加鎖，沒有被訪問的不加鎖i
表級鎖 myisam 隻要對表做訪問無論訪問1行還是10行都回把整張表鎖上
鎖：解決的是並發訪問沖突訪問。
根據客戶端的訪問類型，鎖又分為
讀鎖 共享鎖 select
寫鎖 排他鎖 互斥鎖 insert update delete
建表是根據存儲引擎的特點決定表使用那種存儲引擎
寫操作多的表適合使用innodb存儲引擎，此引擎支持行級鎖，這樣對表的並發訪問量大。
查詢操作多的表適合使用myisam存儲引擎，此引擎支持表級鎖，隻鎖一次這樣可以節省系統資源。
二。數據導入導出(批量操作數據)
2.1 數據導入的命令格式及數據導入時的註意事項
導入數據的命令格式：
數據導入：把系統文件的內容存儲到數據昆伺服器的表裡。
把系統已有用戶的信息保存到db3庫下的usertab表裡。
導入數據
1.把需要導入的數據放入到相應的文件夾中
mysql> show variables like "secure_file_priv";
+——————+———————–+
| Variable_name | Value |
+——————+———————–+
| secure_file_priv | /var/lib/mysql-files/ |
+——————+———————–+
1 row in set (0.00 sec)
[root@localhost lib]# cd mysql-files/
[root@localhost mysql-files]# ls
[root@localhost mysql-files]# cp /etc/passwd /var/lib/mysql-files/
2.導入數據
mysql> load data
-> infile "/var/lib/mysql-files/passwd"
-> into table user1
-> fields terminated by ":"
-> lines terminated by "\n";
Query OK, 41 rows affected (0.00 sec)
Records: 41 Deleted: 0 Skipped: 0 Warnings: 0
mysql> alter table user1
-> add
-> id int(2) primary key auto_increment first;
Query OK, 41 rows affected (0.06 sec)
Records: 41 Duplicates: 0 Warnings: 0
下午課程：
3.更改指定的導入的路莖
[root@localhost ~]# mkdir /mydata
[root@localhost ~]# chown mysql /mydata/
[root@localhost ~]# ls -ld /mydata/
drwxr-xr-x. 2 mysql root 6 6月 27 10:37 /mydata/
[root@localhost ~]# gentenforce
bash: gentenforce: 未找到命令…
[root@localhost ~]# getenforce
Permissive
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv="/mydata"
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
mysql> show variables like "secure_file_priv";
+——————+———-+
| Variable_name | Value |
+——————+———-+
| secure_file_priv | /mydata/ |
+——————+———-+
1 row in set (0.00 sec)
數據導出
mysql> select * from user1 into outfile
-> "/mydata/user.txt";
Query OK, 123 rows affected (0.00 sec)
mysql> select name from user1 into outfile "/mydata/user3.txt";
Query OK, 123 rows affected (0.00 sec)
[root@localhost mydata]# ls
passwd user2.txt user3.txt user.txt
在導出的數據的行和列加相應的間隔符號
mysql> select * from user1 into outfile "/mydata/user9\.txt" fields terminated by ":" lines terminated by "\n";
Query OK, 123 rows affected (0.00 sec)
管理表記錄
mysql> desc user1;
+———-+———–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———–+——+—–+———+——-+
| name | char(30) | YES | MUL | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | char(100) | YES | | NULL | |
| homefir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+———-+———–+——+—–+———+——-+
7 rows in set (0.01 sec)
mysql> load data infile "/mydata/passwd" into table user1 fields terminated by ":" lines terminated by "\n";
Query OK, 41 rows affected (0.00 sec)
Records: 41 Deleted: 0 Skipped: 0 Warnings: 0
mysql> alter table user1 add id int(2) primary key auto_increment first;
Query OK, 41 rows affected (0.33 sec)
Records: 41 Duplicates: 0 Warnings: 0
insert 插入的四種方式
註意事項
-字段值要與字段類型相匹配
-對應字符類型的字段，要用雙或單引號括起來
-依次給所有字段賦值，字段名可以省
隻給一部分字段賦值時，必須明確寫出對應的字段名稱
mysql> insert into user1 values(42,"bob","x",3000,3000,"student user","/home/bob","/bin/bash");
Query OK, 1 row affected (0.00 sec)
mysql> insert into user1 values(43,"bob","x",3000,3000,"student user","/home/bob","/bin/bash"),(44,"bbb","x",3000,3000,"student user","/home/bob","/bin/bash");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into user1(name,uid ,gid) values ("lili",29,30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user1(name,uid ,gid) values ("lili",29,30),("yy",29,32);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
select查詢的兩種方式
mysql> select * from user1;
mysql> select * from user1 where id =32;
update
修改全部
mysql> update user1 set password ="A";
Query OK, 47 rows affected (0.00 sec)
Rows matched: 47 Changed: 47 Warnings: 0
按條件修改
mysql> update user1 set password ="A" where name="root";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
delect
mysql> delete from user1 where id=43;
Query OK, 1 row affected (0.00 sec)
mysql> delete from user1
匹配條件
基本匹配條件：適用於select update delete
數值比較 > < != >= <= =
select * from user1 where id =10;
update user1 set password="B" where id<=10;
delete from user1 where uid >=3000;
字符比較 = !=
select * from user1 where name="root";
select name,shell from user1 where name!="root";
匹配空 is null
匹配非空 is not null
select name,uid from user1 where id is null;
update user1 set uid=250 where uid is null;
delete from user1 where uid is null;
邏輯比較
and
邏輯與 and
或 or
非 ! not 取反
范圍內匹配
in(值列表) 在…裡…
delete from user1 where name in ("zhangsan","mysql","rsync","apache");
not in(值列表) 不在..裡..
Between 數字1 and 數字2 在..之間
select * from user1 where id between 10 and 25;
DISTINCT 字段名 去重顯示
select distinct shell from user1 where uid>=100;
高級匹配條件：適用於select update delete
模糊匹配like
-一個字符
% 0個或者多個
insert into user1(id,name)values(67,null),(69,""); —null是代表什麼都沒有，不是0
select name from user1 where name like '_';
select name from user1 where name like '_%_';
select name from user1 where name like 'a_';
select name from user1 where name like '%';
正則匹配 regrexp '正則表達式'
^開頭
$結尾
[ ]
* 0個或者多個
.位數
select name from user1 where name regexp '^a'; 以a開頭的
select name from user1 where name regexp 'a'; 包含a
select name from user1 where name regexp '[0-9]'; 名字裡面有數據的
select name from user1 where name regexp '[0-9]$';
select name,uid from user1 where uid regexp '…';
select name,uid from user1 where uid regexp '^…$';
select name,uid from user1 where uid regexp 'r.*t';
select name,uid from user1 where name regexp '^r.*t$';
四則運算 適用於 select update
+
–
*
/
%
select name,uid from user1 where uid >=10 and uid <=20;
update user1 set uid=uid+1 where uid >=10 and uid <=20;
alter table user1 add age int(2) default 19 after name;
select name,age from user1 where name="root";
select name,2018-age s_year from user1 where name="root";
select name,uid,gid,(uid+gid)/2 pjz from user1;