linux學習第九周總結

本周的內容重中之重，數據庫MySQL，

一、簡介

MySQL是一個關系型數據庫管理系統，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產品。MySQL 是最流行的關系型數據庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關系數據庫管理系統) 應用軟件。

MySQL是一種關系數據庫管理系統，關系數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度并提高了靈活性。

MySQL所使用的 SQL 語言是用于訪問數據庫的最常用標準化語言。MySQL 軟件采用了雙授權政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發(fā)都選擇 MySQL 作為網站數據庫。

由于其社區(qū)版的性能卓越，搭配 PHP 和 Apache 可組成良好的開發(fā)環(huán)境。

應用環(huán)境

與其他的大型數據庫例如 Oracle、DB2、SQL Server等相比，MySQL [1] 自有它的不足之處，但是這絲毫也沒有減少它受歡迎的程度。對于一般的個人使用者和中小型企業(yè)來說，MySQL提供的功能已經綽綽有余，而且由于 MySQL是開放源碼軟件，因此可以大大降低總體擁有成本

系統特性編輯 編程語言提供了 API。這些編程語言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby,.NET和 Tcl 等。

二、安裝方式

1.RPM安裝     RPM包安裝     CentOS 7：安裝光盤直接提供         mariadb-server   服務器包         mariadb          客戶端工具包 CentOS 6 提高安全性     mysql_secure_installation         設置數據庫管理員root口令         禁止root遠程登錄         刪除anonymous用戶帳號         刪除test數據庫 客戶端程序：     mysql: 交互式的CLI工具     mysqldump：備份工具，基于mysql協議向mysqld發(fā)起查詢請求，并將查得的所有數據轉換成insert等寫操作語句保存文本文件中     mysqladmin：基于mysql協議管理mysqld     mysqlimport：數據導入工具 MyISAM存儲引擎的管理工具：     myisamchk：檢查MyISAM庫     myisampack：打包MyISAM表，只讀 服務器端程序     mysqld_safe     mysqld     mysqld_multi 多實例 ，示例：mysqld_multi --example mysql用戶賬號由兩部分組成：     'USERNAME'@'HOST‘ 說明：     HOST限制此用戶可通過哪些遠程主機連接mysql服務器         支持使用通配符：             % 匹配任意長度的任意字符             172.16.0.0/255.255.0.0 或 172.16.%.%             _ 匹配任意單個字符 服務器監(jiān)聽的兩種socket地址：     ip socket: 監(jiān)聽在tcp的3306端口，支持遠程通信     unix sock: 監(jiān)聽在sock文件上，僅支持本機通信         如：/var/lib/mysql/mysql.sock)     說明：host為localhost,127.0.0.1時自動使用unix sock 偵聽3306/tcp端口可以在綁定有一個或全部接口IP上 vim /etc/my.cnf     [mysqld]     skip-networking=1 關閉網絡連接，只偵聽本地客戶端， 所有和服務器的交互都通過一個socket實現，socket的配置存放在/var/lib/mysql/mysql.sock） 可在/etc/my.cnf修改

2.二進制安裝

二進制格式安裝過程 (1) 準備用戶     groupadd -r -g 306 mysql     useradd -r -g 306 -u 306 Cd /data/mysql mysql (2) 準備數據目錄，建議使用邏輯卷     mkdir /data/mysql     chown mysql:mysql /data/mysql (3) 準備二進制程序     tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local     cd /usr/local     ln -sv mariadb-VERSION mysql     chown -R root:mysql /usr/local/mysql/ (5)創(chuàng)建數據庫文件     cd /usr/local/mysql/     ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql (6)準備服務腳本，并啟動服務     cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld     chkconfig --add mysqld     service mysqld start (7)PATH路徑     echo ‘PATH=/user/local/mysql/bin:$PATH’ > /etc/profile.d/mysql (8)安全初始化     /user/local/mysql/bin/mysql_secure_installation

3.編譯安裝

安裝包 yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel 做準備用戶和數據目錄     useradd -r -s /sbin/nologin -d /data/mysql/ mysql     mkdir /data/mysql     chown mysql.mysql /data/mysql     tar xvf mariadb-10.2.18.tar.gz cmake 編譯安裝 cmake的重要特性之一是其獨立于源碼(out-of-source)的編譯功能，即編譯工作可以在另一個指定的目錄中而非源碼目錄中進行，這可以保證源碼目錄不受任何一次編譯的影響，因此在同一個源碼樹上可以進行多次不同的編譯，如針對于不同平臺編譯 編譯選項:https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html  cd mariadb-10.2.18/ cmake .  -DCMAKE_INSTALL_PREFIX=/app/mysql  -DMYSQL_DATADIR=/data/mysql/  -DSYSCONFDIR=/etc/mysql  -DMYSQL_USER=mysql  -DWITH_INNOBASE_STORAGE_ENGINE=1  -DWITH_ARCHIVE_STORAGE_ENGINE=1  -DWITH_BLACKHOLE_STORAGE_ENGINE=1  -DWITH_PARTITION_STORAGE_ENGINE=1  -DWITHOUT_MROONGA_STORAGE_ENGINE=1  -DWITH_DEBUG=0  -DWITH_READLINE=1  -DWITH_SSL=system  -DWITH_ZLIB=system  -DWITH_LIBWRAP=0  -DENABLED_LOCAL_INFILE=1  -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock  -DDEFAULT_CHARSET=utf8  -DDEFAULT_COLLATION=utf8_general_ci make && make install 提示：如果出錯，執(zhí)行rm -f CMakeCache.txt 準備環(huán)境變量     echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh     . /etc/profile.d/mysql.sh 生成數據庫文件     cd /app/mysql/     scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql 準備配置文件     cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf 準備啟動腳本     cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld 啟動服務     chkconfig --add mysqld ;service mysqld start

三、管理數據庫和表

關系型數據庫的常見組件

數據庫操作

表

創(chuàng)建表：CREATE TABLE

字段信息

查看所有的引擎：SHOW ENGINES

四、數據類型：

數據長什么樣

數值類型 日期/時間類型 字符串(字符)類型 https://dev.mysql.com/doc/refman/5.5/en/data-types.html 1、整型

2、浮點型(float和double)，近似值

4、字符串(char,varchar,_text)

5.二進制數據：BLOB

6.日期時間類型

示例

CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED); DESC students; CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

表操作

DROP TABLE [IF EXISTS] 'tbl_name'; ALTER TABLE 'tbl_name' 字段：     添加字段：add         ADD col1 data_type [FIRST|AFTER col_name]     刪除字段：drop     修改字段：         alter（默認值）, change（字段名）, modify（字段屬性） 索引:     添加索引：add index     刪除索引：drop index 表選項 修改: 查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name; 查看幫助：Help ALTER TABLE

修改表示例

ALTER TABLE students RENAME s1; ALTER TABLE s1 ADD phone varchar(11) AFTER name; ALTER TABLE s1 MODIFY phone int; ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); ALTER TABLE s1 DROP COLUMN mobile; ALTER TABLE s1 character set utf8; ALTER TABLE s1 change name name varchar(20) character set utf8; Help ALTER TABLE 查看

多表查詢

交叉連接：笛卡爾乘積

用于FROM子句中的子查詢

五、函數，存儲過程和觸發(fā)器

1.函數：系統函數和自定義函數

DELIMITER // CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) BEGIN DELETE FROM students WHERE stuid = uid; RETURN (SELECT COUNT(stuid) FROM students); END// DELIMITER ;

2.存儲過程

delimiter // CREATE PROCEDURE showTime() BEGIN SELECT now(); END// delimiter ; CALL showTime

創(chuàng)建含參存儲過程：只有一個IN參數

delimiter // CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED) BEGIN SELECT * FROM students WHERE stuid = uid; END// delimiter ; call selectById(2);

示例

delimiter // CREATE PROCEDURE dorepeat(n INT) BEGIN SET @i = 0; SET @sum = 0; REPEAT SET @sum = @sum+@i; SET @i = @i + 1; UNTIL @i > n END REPEAT; END// delimiter ; CALL dorepeat(100); SELECT @sum;

創(chuàng)建含參存儲過程:包含IN參數和OUT參數

delimiter // CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED) BEGIN DELETE FROM students WHERE stuid >= uid; SELECT row_count() into num; END// delimiter ; call deleteById(2,@Line); SELECT @Line;

說明:創(chuàng)建存儲過程deleteById,包含一個IN參數和一個OUT參數.調用時,傳入刪除的ID和保存被修改的行數值的用戶變量@Line,select @Line;輸出被影響行數

3.流程控制

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body

說明：

觸發(fā)器示例

CREATE TABLE student_info ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) DEFAULT NULL, PRIMARY KEY (stu_id) ); CREATE TABLE student_count ( student_count INT(11) DEFAULT 0 ); INSERT INTO student_count VALUES(0);

示例：創(chuàng)建觸發(fā)器，在向學生表INSERT數據時，學生數增加，DELETE學生時，學生數減少

CREATE TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count+1; CREATE TRIGGER trigger_student_count_delete AFTER DELETE ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count-1;

查看觸發(fā)器

六、mysql用戶和權限管理

創(chuàng)建用戶：CREATE USER

七、存儲引擎

MyISAM引擎特點

InnoDB數據庫文件

所有InnoDB表的數據和索引放置于同一個表空間中 表空間文件：datadir定義的目錄下 數據文件：ibddata1, ibddata2, ... 每個表單獨使用一個表空間存儲表的數據和索引 啟用：innodb_file_per_table=ON 參看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table ON (>= MariaDB 5.5) 兩類文件放在數據庫獨立目錄中 數據文件(存儲數據和索引)：tb_name.ibd 表格式定義：tb_name.frm

其它存儲引擎

MySQL中的系統數據庫 sql數據庫

mysqld選項，服務器系統變量和服務器狀態(tài)變量

服務器配置

八、mysql架構

查詢緩存

哪些查詢可能不會被緩存

查詢緩存相關的服務器變量

SELECT語句的緩存控制

命中率和內存使用率估算 100% 100%

InnoDB存儲引擎

索引

索引類型：

B+TREE索引

B+Tree索引的限制：

索引優(yōu)化策略：

索引優(yōu)化建議

SQL語句性能優(yōu)化 就不用，盡量寫全字段名

管理索引

CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...); ALTER TABLE tbl_name ADD INDEX index_name(index_col_name); help CREATE INDEX;

刪除索引：

DROP INDEX index_name ON tbl_name; ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

優(yōu)化表空間：

OPTIMIZE TABLE tb_name;

查看索引的使用

并發(fā)控制

鎖策略：在鎖粒度及數據安全性尋求的平衡機制 顯式使用鎖     LOCK TABLES 加鎖         tbl_name [[AS] alias] lock_type         [, tbl_name [[AS] alias] lock_type] ...         lock_type: READ ， WRITE         UNLOCK TABLES 解鎖 FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]     關閉正在打開的表（清除查詢緩存），通常在備份前加全局讀鎖 SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]     查詢時加寫或讀鎖

事務

事務Transactions：一組原子性的SQL語句，或一個獨立工作單元 事務日志：記錄事務信息，實現undo,redo等故障恢復功能 ACID特性：     A：atomicity原子性；整個事務中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾     C：consistency一致性；數據庫總是從一個一致性狀態(tài)轉換為另一個一致性狀態(tài)     I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離級別，實現并發(fā)     D：durability持久性；一旦事務提交，其所做的修改會永久保存于數據庫中

啟動事務：

事務隔離級別

并發(fā)控制

還有日志管理 ，備份還原，mysql集群在下周總結