站長資訊網linux學習第九周總結
本周的內容重中之重，數據庫MySQL，
一、簡介
MySQL是一個關系型數據庫管理系統，由瑞典MySQL AB 公司開發，目前屬于 Oracle 旗下產品。MySQL 是最流行的關系型數據庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關系數據庫管理系統) 應用軟件。
MySQL是一種關系數據庫管理系統，關系數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度并提高了靈活性。
MySQL所使用的 SQL 語言是用于訪問數據庫的最常用標準化語言。MySQL 軟件采用了雙授權政策，分為社區版和商業版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站數據庫。
由于其社區版的性能卓越，搭配 PHP 和 Apache 可組成良好的開發環境。
應用環境
與其他的大型數據庫例如 Oracle、DB2、SQL Server等相比，MySQL [1] 自有它的不足之處，但是這絲毫也沒有減少它受歡迎的程度。對于一般的個人使用者和中小型企業來說，MySQL提供的功能已經綽綽有余，而且由于 MySQL是開放源碼軟件，因此可以大大降低總體擁有成本
系統特性編輯 2.二進制安裝 3.編譯安裝 關系型數據庫的常見組件 數據庫操作 表 創建表：CREATE TABLE 字段信息 查看所有的引擎：SHOW ENGINES 數據長什么樣 數值類型 日期/時間類型 字符串(字符)類型 https://dev.mysql.com/doc/refman/5.5/en/data-types.html 2、浮點型(float和double)，近似值 4、字符串(char,varchar,_text) 5.二進制數據：BLOB 6.日期時間類型 示例 表操作 修改表示例 多表查詢 交叉連接：笛卡爾乘積 用于FROM子句中的子查詢 1.函數：系統函數和自定義函數 2.存儲過程 創建含參存儲過程：只有一個IN參數 示例 創建含參存儲過程:包含IN參數和OUT參數 說明:創建存儲過程deleteById,包含一個IN參數和一個OUT參數.調用時,傳入刪除的ID和保存被修改的行數值的用戶變量@Line,select @Line;輸出被影響行數 3.流程控制 說明： 觸發器示例 示例：創建觸發器，在向學生表INSERT數據時，學生數增加，DELETE學生時，學生數減少 查看觸發器 創建用戶：CREATE USER MyISAM引擎特點 InnoDB數據庫文件 其它存儲引擎 MySQL中的系統數據庫 mysqld選項，服務器系統變量和服務器狀態變量 服務器配置 查詢緩存 哪些查詢可能不會被緩存 查詢緩存相關的服務器變量 SELECT語句的緩存控制 命中率和內存使用率估算 InnoDB存儲引擎 索引 索引類型： B+TREE索引 B+Tree索引的限制： 索引優化策略： 索引優化建議 SQL語句性能優化 管理索引 刪除索引： 查看索引： 優化表空間： 查看索引的使用 并發控制 事務 啟動事務： 事務隔離級別 并發控制 還有日志管理 ，備份還原，mysql集群在下周總結 二、安裝方式
1.RPM安裝 RPM包安裝 CentOS 7：安裝光盤直接提供 mariadb-server 服務器包 mariadb 客戶端工具包 CentOS 6 提高安全性 mysql_secure_installation 設置數據庫管理員root口令 禁止root遠程登錄 刪除anonymous用戶帳號 刪除test數據庫 客戶端程序： mysql: 交互式的CLI工具 mysqldump：備份工具，基于mysql協議向mysqld發起查詢請求，并將查得的所有數據轉換成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.%.% _ 匹配任意單個字符 服務器監聽的兩種socket地址： ip socket: 監聽在tcp的3306端口，支持遠程通信 unix sock: 監聽在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修改
二進制格式安裝過程 (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)創建數據庫文件 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
安裝包 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 提示：如果出錯，執行rm -f CMakeCache.txt 準備環境變量 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
三、管理數據庫和表
四、數據類型：
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 查看
五、函數，存儲過程和觸發器
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 ;
delimiter // CREATE PROCEDURE showTime() BEGIN SELECT now(); END// delimiter ; CALL showTime
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;
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;
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body
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);
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;
六、mysql用戶和權限管理
七、存儲引擎
所有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架構
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;
OPTIMIZE TABLE tb_name;
鎖策略：在鎖粒度及數據安全性尋求的平衡機制 顯式使用鎖 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原子性；整個事務中的所有操作要么全部成功執行，要么全部失敗后回滾 C：consistency一致性；數據庫總是從一個一致性狀態轉換為另一個一致性狀態 I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離級別，實現并發 D：durability持久性；一旦事務提交，其所做的修改會永久保存于數據庫中
