站長(zhǎng)資訊網(wǎng)linux學(xué)習(xí)第九周總結(jié)
本周的內(nèi)容重中之重，數(shù)據(jù)庫(kù)MySQL，
一、簡(jiǎn)介
MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)) 應(yīng)用軟件。
MySQL是一種關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)，關(guān)系數(shù)據(jù)庫(kù)將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉(cāng)庫(kù)內(nèi)，這樣就增加了速度并提高了靈活性。
MySQL所使用的 SQL 語(yǔ)言是用于訪問數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語(yǔ)言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。
由于其社區(qū)版的性能卓越，搭配 PHP 和 Apache 可組成良好的開發(fā)環(huán)境。
應(yīng)用環(huán)境
與其他的大型數(shù)據(jù)庫(kù)例如 Oracle、DB2、SQL Server等相比，MySQL [1] 自有它的不足之處，但是這絲毫也沒有減少它受歡迎的程度。對(duì)于一般的個(gè)人使用者和中小型企業(yè)來說，MySQL提供的功能已經(jīng)綽綽有余，而且由于 MySQL是開放源碼軟件，因此可以大大降低總體擁有成本
系統(tǒng)特性編輯 2.二進(jìn)制安裝 3.編譯安裝 關(guān)系型數(shù)據(jù)庫(kù)的常見組件 數(shù)據(jù)庫(kù)操作 表 創(chuàng)建表：CREATE TABLE 字段信息 查看所有的引擎：SHOW ENGINES 數(shù)據(jù)長(zhǎng)什么樣 數(shù)值類型 日期/時(shí)間類型 字符串(字符)類型 https://dev.mysql.com/doc/refman/5.5/en/data-types.html 2、浮點(diǎn)型(float和double)，近似值 4、字符串(char,varchar,_text) 5.二進(jìn)制數(shù)據(jù)：BLOB 6.日期時(shí)間類型 示例 表操作 修改表示例 多表查詢 交叉連接：笛卡爾乘積 用于FROM子句中的子查詢 1.函數(shù)：系統(tǒng)函數(shù)和自定義函數(shù) 2.存儲(chǔ)過程 創(chuàng)建含參存儲(chǔ)過程：只有一個(gè)IN參數(shù) 示例 創(chuàng)建含參存儲(chǔ)過程:包含IN參數(shù)和OUT參數(shù) 說明:創(chuàng)建存儲(chǔ)過程deleteById,包含一個(gè)IN參數(shù)和一個(gè)OUT參數(shù).調(diào)用時(shí),傳入刪除的ID和保存被修改的行數(shù)值的用戶變量@Line,select @Line;輸出被影響行數(shù) 3.流程控制 說明： 觸發(fā)器示例 示例：創(chuàng)建觸發(fā)器，在向?qū)W生表INSERT數(shù)據(jù)時(shí)，學(xué)生數(shù)增加，DELETE學(xué)生時(shí)，學(xué)生數(shù)減少 查看觸發(fā)器 創(chuàng)建用戶：CREATE USER MyISAM引擎特點(diǎn) InnoDB數(shù)據(jù)庫(kù)文件 其它存儲(chǔ)引擎 MySQL中的系統(tǒng)數(shù)據(jù)庫(kù) mysqld選項(xiàng)，服務(wù)器系統(tǒng)變量和服務(wù)器狀態(tài)變量 服務(wù)器配置 查詢緩存 哪些查詢可能不會(huì)被緩存 查詢緩存相關(guān)的服務(wù)器變量 SELECT語(yǔ)句的緩存控制 命中率和內(nèi)存使用率估算 InnoDB存儲(chǔ)引擎 索引 索引類型： B+TREE索引 B+Tree索引的限制： 索引優(yōu)化策略： 索引優(yōu)化建議 SQL語(yǔ)句性能優(yōu)化 管理索引 刪除索引： 查看索引： 優(yōu)化表空間： 查看索引的使用 并發(fā)控制 事務(wù) 啟動(dòng)事務(wù)： 事務(wù)隔離級(jí)別 并發(fā)控制 還有日志管理 ，備份還原，mysql集群在下周總結(jié) 二、安裝方式
1.RPM安裝 RPM包安裝 CentOS 7：安裝光盤直接提供 mariadb-server 服務(wù)器包 mariadb 客戶端工具包 CentOS 6 提高安全性 mysql_secure_installation 設(shè)置數(shù)據(jù)庫(kù)管理員root口令 禁止root遠(yuǎn)程登錄 刪除anonymous用戶帳號(hào) 刪除test數(shù)據(jù)庫(kù) 客戶端程序： mysql: 交互式的CLI工具 mysqldump：備份工具，基于mysql協(xié)議向mysqld發(fā)起查詢請(qǐng)求，并將查得的所有數(shù)據(jù)轉(zhuǎn)換成insert等寫操作語(yǔ)句保存文本文件中 mysqladmin：基于mysql協(xié)議管理mysqld mysqlimport：數(shù)據(jù)導(dǎo)入工具 MyISAM存儲(chǔ)引擎的管理工具： myisamchk：檢查MyISAM庫(kù) myisampack：打包MyISAM表，只讀 服務(wù)器端程序 mysqld_safe mysqld mysqld_multi 多實(shí)例 ，示例：mysqld_multi --example mysql用戶賬號(hào)由兩部分組成： 'USERNAME'@'HOST‘ 說明： HOST限制此用戶可通過哪些遠(yuǎn)程主機(jī)連接mysql服務(wù)器 支持使用通配符： % 匹配任意長(zhǎng)度的任意字符 172.16.0.0/255.255.0.0 或 172.16.%.% _ 匹配任意單個(gè)字符 服務(wù)器監(jiān)聽的兩種socket地址： ip socket: 監(jiān)聽在tcp的3306端口，支持遠(yuǎn)程通信 unix sock: 監(jiān)聽在sock文件上，僅支持本機(jī)通信 如：/var/lib/mysql/mysql.sock) 說明：host為localhost,127.0.0.1時(shí)自動(dòng)使用unix sock 偵聽3306/tcp端口可以在綁定有一個(gè)或全部接口IP上 vim /etc/my.cnf [mysqld] skip-networking=1 關(guān)閉網(wǎng)絡(luò)連接，只偵聽本地客戶端， 所有和服務(wù)器的交互都通過一個(gè)socket實(shí)現(xiàn)，socket的配置存放在/var/lib/mysql/mysql.sock） 可在/etc/my.cnf修改
二進(jìn)制格式安裝過程 (1) 準(zhǔn)備用戶 groupadd -r -g 306 mysql useradd -r -g 306 -u 306 Cd /data/mysql mysql (2) 準(zhǔn)備數(shù)據(jù)目錄，建議使用邏輯卷 mkdir /data/mysql chown mysql:mysql /data/mysql (3) 準(zhǔn)備二進(jìn)制程序 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)建數(shù)據(jù)庫(kù)文件 cd /usr/local/mysql/ ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql (6)準(zhǔn)備服務(wù)腳本，并啟動(dòng)服務(wù) 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 做準(zhǔn)備用戶和數(shù)據(jù)目錄 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的重要特性之一是其獨(dú)立于源碼(out-of-source)的編譯功能，即編譯工作可以在另一個(gè)指定的目錄中而非源碼目錄中進(jìn)行，這可以保證源碼目錄不受任何一次編譯的影響，因此在同一個(gè)源碼樹上可以進(jìn)行多次不同的編譯，如針對(duì)于不同平臺(tái)編譯 編譯選項(xiàng):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 提示：如果出錯(cuò)，執(zhí)行rm -f CMakeCache.txt 準(zhǔn)備環(huán)境變量 echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh . /etc/profile.d/mysql.sh 生成數(shù)據(jù)庫(kù)文件 cd /app/mysql/ scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql 準(zhǔn)備配置文件 cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf 準(zhǔn)備啟動(dòng)腳本 cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld 啟動(dòng)服務(wù) chkconfig --add mysqld ;service mysqld start
三、管理數(shù)據(jù)庫(kù)和表
四、數(shù)據(jù)類型：
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（默認(rèn)值）, change（字段名）, modify（字段屬性） 索引: 添加索引：add index 刪除索引：drop index 表選項(xiàng) 修改: 查看表上的索引：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 查看
五、函數(shù)，存儲(chǔ)過程和觸發(fā)器
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用戶和權(quán)限管理
七、存儲(chǔ)引擎
所有InnoDB表的數(shù)據(jù)和索引放置于同一個(gè)表空間中 表空間文件：datadir定義的目錄下 數(shù)據(jù)文件：ibddata1, ibddata2, ... 每個(gè)表單獨(dú)使用一個(gè)表空間存儲(chǔ)表的數(shù)據(jù)和索引 啟用：innodb_file_per_table=ON 參看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table ON (>= MariaDB 5.5) 兩類文件放在數(shù)據(jù)庫(kù)獨(dú)立目錄中 數(shù)據(jù)文件(存儲(chǔ)數(shù)據(jù)和索引)：tb_name.ibd 表格式定義：tb_name.frm
八、mysql架構(gò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;
OPTIMIZE TABLE tb_name;
鎖策略：在鎖粒度及數(shù)據(jù)安全性尋求的平衡機(jī)制 顯式使用鎖 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] 關(guān)閉正在打開的表（清除查詢緩存），通常在備份前加全局讀鎖 SELECT clause [FOR UPDATE | LOCK IN SHARE MODE] 查詢時(shí)加寫或讀鎖
事務(wù)Transactions：一組原子性的SQL語(yǔ)句，或一個(gè)獨(dú)立工作單元 事務(wù)日志：記錄事務(wù)信息，實(shí)現(xiàn)undo,redo等故障恢復(fù)功能 ACID特性： A：atomicity原子性；整個(gè)事務(wù)中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾 C：consistency一致性；數(shù)據(jù)庫(kù)總是從一個(gè)一致性狀態(tài)轉(zhuǎn)換為另一個(gè)一致性狀態(tài) I：Isolation隔離性；一個(gè)事務(wù)所做出的操作在提交之前，是不能為其它事務(wù)所見；隔離有多種隔離級(jí)別，實(shí)現(xiàn)并發(fā) D：durability持久性；一旦事務(wù)提交，其所做的修改會(huì)永久保存于數(shù)據(jù)庫(kù)中
