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是开放源码软件，因此可以大大降低总体拥有成本

系统特性编辑编程语言提供了 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发起查询请求，并将查得的所有数据转换成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修改

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)创建数据库文件     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 提示：如果出错，执行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

字段信息

查看所有的引擎：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子句中的子查询

五、函数，存储过程和触发器

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

创建含参存储过程：只有一个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;

创建含参存储过程:包含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;

说明:创建存储过程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

说明：

触发器示例

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);

示例：创建触发器，在向学生表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;

查看触发器

六、mysql用户和权限管理

创建用户：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选项，服务器系统变量和服务器状态变量

服务器配置

八、mysql架构

查询缓存

哪些查询可能不会被缓存

查询缓存相关的服务器变量

SELECT语句的缓存控制

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

InnoDB存储引擎

索引

索引类型：

B+TREE索引

B+Tree索引的限制：

索引优化策略：

索引优化建议

SQL语句性能优化就不用，尽量写全字段名

管理索引

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持久性；一旦事务提交，其所做的修改会永久保存于数据库中

启动事务：

事务隔离级别