一、MySQL数据库基础

89ou

　　1.1、数据库模型
　　数据库由一批数据构成有序集合，这些数据被存在结构化的数据表中。数据表之间互相关联，反应客观事物间的本质联系。数据库系统提供对数据的安全控制和完整性控制。数据库的发展大致划分为几个阶段：人工管理阶段、文件系统阶段、数据库系统阶段、高级数据库阶段。其种类大概有3种：层次式数据库、网络式数据库和关系式数据库。
　　文本数据库缺陷：数据冗余和不一致，数据访问困难，数据孤立，完整性问题，原子性问题并发访问问题，安全性问题。
　　数据库系统的特性：数据管理独立性；有效地完成数据存取；数据完整性和安全性；数据集中管理；并发存储与故障恢复；减少应用程序开发时间。
　　数据库系统由硬件部分和软件部分共同构成，硬件主要用于存储数据库中的数据，包括计算机、存储设备等。软件部分则主要包括DBMS、支持DBMS运行的操作系统，以及支持多种语言进行应用开发的访问技术等。
　　通用关系型数据库架构：

　　1.2、数据类型与约束
　　数据类型决定了数据在计算机中的存储格式，代表不同的信息类型。常用的数据类型有：
　　字符：CHAR(n)  VARCHAR(n)  BINARY(n)  VARBINARY(n)  TEXT(n)  BLOB(n)
　　数值：
　　整型：TINYINT  SMALLINT  MEDIUMINT  INT  BIGINT
　　修饰符：UNSIGNED  NOT NULL
　　浮点型：FLOAT  DOUBLE
　　日期时间：DATE  TIME  DATETIME STAMP
　　布尔
　　NULL
　　内置：ENUM， SET
　　字段修饰符：
　　NOT NULL    非空
　　NULL        空
　　UNSIGNED    无符号
　　DEFAULT     不适用于TEXT类型
　　PRIMARY KEY 主键
　　UNIQUE KEY  唯一键
　　AUTO_INCREMENT：自动增长类型的字段必须为主键或惟一键
　　域约束：数据类型约束
　　外键约束：引用完整性约束
　　主键约束：某字段能惟一标识此字段所属的实体，并且不允许为空，一张表中只能有一个主键。
　　惟一性约束：每一行的某字段都不允许出现相同值，可以为空一张表中可以有多个
　　检查性约束：用户自定义有效取值范围。
　　1.3、MySQL介绍
　　MySQL是一个小型关系数据库管理系统，与其他大型数据库管理系统(Oracle、DB2)相比，MySQL规模小、功能有限，但是它体积小、速度快、成本低，且它提供的功能对稍微复杂的应用来说已经够用。
　　MySQL的主要运行流程如下所示：

　　MySQL数据库管理系统提供了许多命令行工具，这些工具可以用要管理MySQL服务器、对数据库进行访问控制、管理MySQL用户以及数据库备份和恢复工具等。
　　服务器端工具程序：
　　mysqld：SQL后台程序。该程序运行后，客户端才能连接服务器来访问数据库。
　　mysqld_safe：服务器启动脚本。
　　mysqld_multi：服务器启动脚本，可以启动或停止系统上安装的多个服务器。
　　myisamchk：用来描述、检查、优化和维护MyISAM表的实用工具。
　　mysqlbug：MySQL缺陷报告脚本。它可以用来向MySQL邮件系统发送缺陷报告。
　　mysql_install_db：该脚本用默认权限创建MySQL授权表。通常只是在系统首次安装MySQL时执行一次。
　　客户端工具程序：
　　myisampack：压缩MyISAM表以产生更小的只读表的工具
　　mysql：交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具
　　mysqlaccess：检查访问主机名、用户名和数据库组合的权限的脚本
　　mysqladmin：执行管理操作的客户端程序，如创建或删除数据库，重载授权表，重新打开日志文件等，还可以用来检查版本、进程、以及服务器的状态信息
　　mysqlbinlog：从二进制日志读取语句的工具，二进制日志文件中包含执行过的语句，可以用来帮助系统从崩溃中恢复
　　mysqlcheck：检查、修复、分析以及优化表
　　mysqldump：将mysql数据库转储到一个文件
　　mysqlhotcopy：当服务器运行时，快速备份MyISAM表的工具
　　mysqlimport：使用LOAD DATA INFILE将文本文件导入相关表的库户程序
　　mysqlshow：显示数据库、表、列以及索引相关信息的客户程序
　　perror：显示系统或MySQL错误代码含义的工具
　　mysqladmin [options] command [arg] [command [arg]] ...
　　[options]：所有通行选项都可使用
　　command：
　　create DB_NAME  创建数据库drop DB_NAME删除数据库
　　debug: 打开调试日志并记录于error log中；
　　status：显示简要状态信息  (--sleep #: 间隔时长--count #: 显示的批次)
　　extended-status: 输出mysqld的各状态变量及其值，相当于执行mysql> SHOW GLOBAL STATUS
　　variables: 输出mysqld的各服务器变量
　　flush-hosts: 清空主机相关的缓存：DNS解析缓存，此前因为连接错误次数过多而被拒绝访问mysqld的主机列表
　　flush-logs: 日志滚动，二进制日志和中继日志
　　refresh: 相当于同时使用flush-logs和flush-hosts
　　flush-privileges、reload：刷新授权表
　　flush-status: 重置状态变量的值
　　flush-tables: 关闭当前打开的表文件句柄
　　flush-threads：清空线程缓存
　　password: 修改当前用户的密码；
　　ping: 测试服务器是否在线
　　processlist：显示mysql线程列表
　　shutdown: 关闭mysqld进程；
　　start-slave/stop-slave: 启动/关闭从服务器线程
　　kill:　杀死指定的线程，可以一次杀死多个，以逗号分隔，不能有多余空格
　　
　　1.4、源码安装MySQL5.5
　　安装cmake软件与依赖包:
[root@mylinux home]# yum install -y cmake ncurses-devel　　创建用户和组
[root@mylinux home]# groupadd mysql　　
[root@mylinux home]# useradd mysql -s /sbin/nologin -M -g mysql
　　解压编译MySQL
[root@mylinux home]#wget　　
[root@mylinux home]#tar xf mysql-5.5.56.tar.gz
　　
[root@mylinux home]#cd mysql-5.5.56
　　
[root@mylinux home]#cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.56 \
　　
-DMYSQL_DATADIR=/application/mysql-5.5.56/data \
　　
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.56/tmp/mysql.sock \
　　
-DDEFAULT_CHARSET=utf8 \
　　
-DDEFAULT_COLLATION=utf8_general_ci \
　　
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
　　
-DENABLED_LOCAL_INFILE=ON \
　　
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
　　
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
　　
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
　　
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
　　
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
　　
-DWITH_FAST_MUTEXES=1 \
　　
-DWITH_ZLIB=bundled \
　　
-DENABLED_LOCAL_INFILE=1 \
　　
-DWITH_READLINE=1 \
　　
-DWITH_EMBEDDED_SERVER=1 \
　　
-DWITH_DEBUG=0
　　
[root@mylinux home]#make
　　
[root@mylinux home]#make install
　　
[root@mylinux application]# cd /application/
　　
[root@mylinux application]# ln -s /application/mysql-5.5.56/ /application/mysql
　　
[root@mylinux application]# mkdir /application/mysql/data -p
　　
[root@mylinux application]# cd mysql
　　
[root@mylinux mysql]#chown  -R root:mysql  /application/mysql
　　
[root@mylinux mysql]#mdkir -p /application/mysql-5.5.56/tmp
　　
[root@mylinux mysql]#chmod -R 777 /application/mysql-5.5.56/tmp
　　选择配置文件与环境变量
[root@mylinux mysql]#cp support-files/my-large.cnf  /etc/my.cnf　　
[root@mylinux mysql]#vim /etc/profile.d/mysql.sh
　　
   export PATH=/application/mysql/bin:$PATH
　　初始化数据库
[root@mylinux mysql]# ./scripts/mysql_install_db  --user=mysql --data=/application/mysql/data/　　
Installing MySQL system tables...
　　
170711 22:09:40 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
　　
170711 22:09:40 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
　　
170711 22:09:40 [Note] ./bin/mysqld (mysqld 5.5.56-log) starting as process 28738 ...
　　
OK
　　
Filling help tables...
　　
170711 22:09:41 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
　　
170711 22:09:41 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
　　
170711 22:09:41 [Note] ./bin/mysqld (mysqld 5.5.56-log) starting as process 28745 ...
　　
OK
　　

　　
To start mysqld at boot time you have to copy
　　
support-files/mysql.server to the right place for your system
　　

　　
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
　　
To do so, start the server, then issue the following commands:
　　

　　
./bin/mysqladmin -u root password 'new-password'
　　
./bin/mysqladmin -u root -h mylinux password 'new-password'
　　

　　
Alternatively you can run:
　　
./bin/mysql_secure_installation
　　

　　
which will also give you the option of removing the test
　　
databases and anonymous user created by default.  This is
　　
strongly recommended for production servers.
　　

　　
See the manual for more instructions.
　　

　　
You can start the MySQL daemon with:
　　
cd . ; ./bin/mysqld_safe &
　　

　　
You can test the MySQL daemon with mysql-test-run.pl
　　
cd ./mysql-test ; perl mysql-test-run.pl
　　

　　
Please report any problems at http://bugs.mysql.com/
　　添加启动脚本与设置开机启动
[root@mylinux mysql]# cp support-files/mysql.server /etc/init.d/mysqld　　
[root@mylinux mysql]# chmod +x /etc/init.d/mysqld
　　
[root@mylinux mysql]# chkconfig --add mysqld
　　
[root@mylinux mysql]# chkconfig --level 2345 mysqld on
　　
[root@mylinux mysql]# service mysqld start
　　
Starting MySQL.. SUCCESS!
　　mysql的配置文件：
　　/etc/my.cnf或/etc/mysql/my.cnf-->$MYSQL_BASE/my.cnf -->--defaults-extra-file=my.cnf
　　-->~/ .my.cnf
　　使用配置文件的方式
　　1、它依次查找每个需要查找的文件，结果是所有文件并集；
　　2、如果某参数在多个文件中出现多次，后读取的最终生效；
　　1.5、MySQL变量与作用域
　　服务器变量：@@变量名
　　显示：SELECT @@变量名
　　设定：SET GLOBAL|SESSION 变量名='value'
　　设定服务器变量的值：（仅用于支持动态的变量）
　　支持修改的服务器变量：
　　动态变量：可以MySQL运行时修改，可即时修改
　　静态变量：于配置文件中修改其值，并重启后方能生效；
　　动态调整参数的生效方式：
　　全局变量：服务器级别，修改之后仅对新建立的会话有效；
　　会话变量：会话级别，仅对当前会话有效；会话建立时，从全局继承各变量；
　　查看服务器变量：
mysql> SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ''];　　
mysql> SELECT @@{GLOBAL|SESSION}.VARILABLE_NAME;
　　
mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='NAME';
　　
mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='NAME';
　　修改变量：默认仅管理员有权限修改全局变量
mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE';　　注意：无论是全局还是会话级别的动态变量修改，在重启mysqld后都会失效；想永久有效，可定义在配置文件中的相应段中[mysqld]。
　　1.6、MySQL中文数据乱码解决办法
　　1)、向表中插入数据之前先设置客户端的字符集与表保持一致
  SELECT CREATE TABLE tb_name \G   查看表的字符集　　
  set names 字符集;
　　2)、在SQL文件中指定字符集然后登陆MYSQL导入数据
  source tb_namq.sql;　　3)、在SQL文件中指定字符集，通过mysql命令导入数据
  mysql  -uroot  -p’password’  tb_name