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