mysql数据库入门、进阶和提升

tyxiayu

　　一、SQL概述
　　什么是数据库？
　　数据库就是以有组织的方式存储的数据集合，可以简单的理解为一个大储物柜。而我们常常使用的mysql，sql_server都是DBMS,就是管理数据库的软件。
　　什么是表?
　　用来存储数据的结构化文件，一个数据库中不能有重复的表名。
　　什么是列？
　　构成表的基本单位，每一个列均有自己的数据类型。
　　什么是行？
　　数据库中的数据是按照行存储的，一行称为一条记录。
　　什么是主键？
　　能够唯一的区别（标识）表中的每一行的列属性称为主键
　　什么是sql
　　结构化查询语言，作为一种同数据库通信的语言，制定了一个规范，各个厂家可以语句sql语句规范定义自己的查询语句，比如微软的sql-server使用的是T-SQL,甲骨文公司oracle使用的是p1/sql，mysql数据库使用的是sql语言。
　　SQL语句对用户提供类似shell的交互式查询功能，也可以是非交互式的，类似编程。提供非交互式的查询功能可以将语句嵌套到其他编程语言中。这样就需要SQL提供一个专用的API接口，就是一个客户端程序，并且不同公司的数据库使用的客户端也是不能通用的。
　　用户编写的程序要和mysql数据库通信，需要知道通信协议的编码，这个工作如果交给用户来完成就会变得非常复杂，所以不同的数据库都提供了一个自己的驱动。其中通用驱动就是ODBC,就像php要和mysql通信需要使用一个php-myql的驱动一样。
　　mysql是C/S类型的数据库，客户端工具主要有三种。
　　mysql命令行工具，mysql administrator交互式工具，mysql query browser图形工具。
　　1、SQL语句类型：

　　DML:crud （insert,update,detele,select）
　　DDL:create,drop,alter
　　DCL:grant,revoke

　　2、事务的特性：

　　原子性：一组DML要么执行，要么不执行
　　一致性：
　　隔离性：
　　级别一，读未提交
　　级别二，读提交
　　级别三，可重读
　　级别四，可串行化
　　持久性：为了保证持久性，事务提交，立即写入磁盘。这样会造成写入速度变慢，所有出现了事物日志，既可以提高写入速度（先放到缓冲区，再写入存储中），又防止了断电丢失问题。

　　3、MYSQL日志：

　　错误日志：
　　查询日志：
　　慢查询日志：
　　事务日志：
　　二进制日志：可以用来恢复丢失的数据，操作不可靠。
　　中继日志：

　　4、客户端和服务器端通信
　　mysql数据库的服务器端对外提供被动连接，通常通过tcp/ip协议加端口号的方式（套接字）对外提供服务，并且有两种方式，本地间通信和远程通信。
　　本地通信：mysql.sock
　　客户端和服务器端要求在同一个设备上，在linux上是通过mysql.sock来提供通信的。在windows主机上是通过PIPE或者MEMORY来通信的。
　　5、索引和记录
　　变长纪录要解决的问题有哪些？
　　a、如何描述一条纪录，以实现快速纪录获取。
　　b、如何存储变长记录，以实现快速存储。
　　纪录按照是否有序可以有哪些分类?
　　无序文件组织（堆文件），顺序文件组织，散列文件组织。
　　索引的类型有哪些？
　　主索引，辅助索引
　　按照索引数据结构，有哪些索引类型？
　　树状索引，散列索引
　　mysql存储引擎有哪些，如何存储数据的。
　　MYISAM
　　表：
　　数据文件：表名.MYD
　　索引文件：表名.MYI
　　表定义：表名.FRM
　　INNODB，数据和索引并没有分开存放。
　　表：
　　表空间：多张表可以放在同一个表空间，并表现为一个文件。表空间的多个数据库可以共享数据。mysql支持每个表使用独立的表空间文件（需要设置）。
　　表定义文件：每张表定义文件在数据库目录中。
　　数据字典是做什么的？
　　数据字典又叫系统目录，用来保存数据库服务器上的元数据。
　　MySQL默认数据库作用：
　　mysql：也称为数据字典。
　　information_schema：用来将写入内存中的mysql数据库内部结构映射成文件，类似linux的proc目录。比如常用的show命令，查看的内容就是information_schema。
　　performance_schema：用来存储统计类数据。
　　例如：每个关系的属性的个数
　　每个关系行的个数
　　每个关系的存储方法
　　innoDB文件类型讲解：http://www.cnblogs.com/benshan/archive/2013/01/08/2851714.html
　　注意：
　　缓存置换，只能够通过mysql的innoDB引擎进行调优。
　　二、Mysql常见安装方式及编译安装参数
　　mysql服务器端同客户端通信方式图示

　　mysql优化可以使用percona，percona又依据innoDB引擎开发出来了自己的xtraDB，功能更高于InnoDB引擎。
　　编译安装xtraDB的方法是通过编译安装xtraDB并覆盖到mysql安装目录的innoDB,编译完后名称仍然叫InnoDB。并提供免费的Xtrabackup的热备份工具。
　　2、修改用户密码：
　　方法一：
　　[root@dt0b4007c ~]# mysqladmin -uroot -p -hlocalhost password 'root'
　　方法二：
　　mysql>use mysql
　　mysql> set password for root@localhost=password('dbroot@dtedu');
　　Query OK, 0 rows affected (0.00 sec)
　　方法三：
　　mysql> use mysql;
　　Database changed
　　mysql> UPDATE user SET password=PASSWORD('123123') WHERE user='root';
　　Query OK, 0 rows affected (0.00 sec
　　Rows matched: 0  Changed: 0  Warnings: 0
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.00 sec)
　　3、mysql的使用模式
　　交互式模式：
　　批处理模式：
　　-h --host=
　　-u --user=
　　-p --password=
　　-D --database=
　　mysql客户端命令：
　　\q
　　\G
　　\g:语句结束符
　　4、数据目录讲解
　　[root@10 support-files]# ll /data/mysql/
　　总用量 796736
　　drwx------  2 mysql mysql      4096 5月  30 15:33 ec_school用户数据库
　　-rw-rw----. 1 mysql mysql 136314880 7月  20 08:51 ibdata1使用innoDB引擎时的表空间（存数据库数据）
　　-rw-rw----. 1 mysql mysql   5242880 7月  20 08:51 ib_logfile0日志文件，固定大小，轮转使用
　　-rw-rw----. 1 mysql mysql   5242880 7月  20 08:51 ib_logfile1日志文件
　　drwx------. 2 mysql root       4096 8月  26 2012 mysqlmysql默认创建的数据库
　　-rw-rw----. 1 mysql mysql     27338 8月  26 2012 mysql-bin.000001二进制日志
　　-rw-rw----. 1 mysql mysql   1035873 8月  26 2012 mysql-bin.000002二进制日志
　　5、mysql的模糊查找
　　关键字：like
　　通配符：%：表示任意长度的任意字符
　　_：任意单个字符
　　6、本地通信mysql.sock设置注意哪些？
　　本地通信需要使用mysql.sock文件，这个文件是随着mysql的启动而启动的。需要注意的是这个文件的存贮位置必须是client和server端都是相同的。并且注意权限问题。
　　[client]
　　#password       = your_password
　　port            = 3306
　　socket          = /tmp/mysql.sock
　　default-character-set=utf8
　　# Here follows entries for some specific programs
　　# The MySQL server
　　[mysqld]
　　basedir=/usr/local/dtedu/mysql
　　datadir=/data/mysql
　　user=mysql
　　symbolic-links=0
　　port            = 3306
　　socket          = /tmp/mysql.sock
　　7、mysql常用命令
　　7.1显示mysql全局状态
　　mysql> show global variables;
　　+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
　　| Variable_name                                     | Value                                                                                                                  |
　　+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
　　| auto_increment_increment                          | 1                                                                                                                      |
　　| auto_increment_offset                             | 1                                                                                                                      |
　　7.2查看全局变量表中指定的字段信息
　　mysql> show global variables where variable_name='wait_timeout';
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| wait_timeout  | 28800 |
　　+---------------+-------+
　　1 row in set (0.00 sec)
　　7.3更改设置全局变量中字段的数值
　　mysql> set global wait_timeout=29000
　　-> ;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> show global variables where variable_name='wait_timeout';
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| wait_timeout  | 29000 |
　　+---------------+-------+
　　1 row in set (0.00 sec)
　　mysql的变量分为全局变量和当前会话变量两种，修改全局变量不会立即生效，并且需要管理员账户，二会话变量仅对当前会话有效。
　　8、mysql常用命令
　　8.1常用命令放在那里了？
　　通常放在安装mysql文件的bin目录下。
　　8.2程序类型有哪些，区别是什么？
　　服务器端程序：mysqld、mysqld_safe，mysqld_multi
　　客户端程序（需要连接到服务器端才能使用）：mysql，mysqladmin，mysqldump
　　非客户端程序（管理mysql数据文件）：
　　9、mysql读取配置文件通常从哪里读取，优先级是怎样的。
　　可以使用mysql —help —verbose来进行查看读取位置和优先级。
　　一般情况下是 /etc/mysql/my.cnf —>/etc/my.cnf——>default-extra-file=/path/to/some_conf_file——>~/.my.cnf。当有重复定义的情况，后检查的优先级高于先检查的。
　　Default options are read from the following files in the given order:
　　/etc/my.cnf /etc/mysql/my.cnf /usr/local/dtedu/mysql/etc/my.cnf ~/.my.cnf
　　The following groups are read: mysql client
　　The following options may be given as the first argument:
　　--print-defaults        Print the program argument list and exit.
　　--no-defaults           Don't read default options from any option file.
　　--defaults-file=#       Only read default options from the given file #.
　　--defaults-extra-file=# Read this file after the global files are read.
　　10、查看mysql变量内容的方法有一下几种？
　　10.1set {global|session} variable_name=‘value’;
　　10.2show {global|session} variable {like|where};
　　10.3select @@{global|session}.variable_name;
　　二、使用MySQL数据库
　　快速执行sql语句，比如执行extmail.sql这样一个sql脚本文件。
　　[root@mail docs]# mysql  use mysql
　　Database changed
　　2、了解数据库和表，如果不知道当前DBMS管理着那些数据库，可以使用show。
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| ec_school          |
　　| mysql              |
　　| performance_schema |
　　| test               |
　　+--------------------+
　　5 rows in set (0.02 sec)
　　3、了解针对具体数据库包含了那些表信息，需要先使用数据库use，在查看。
　　mysql> use mysql
　　mysql> show tables;
　　+---------------------------+
　　| Tables_in_mysql           |
　　+---------------------------+
　　| columns_priv              |
　　| db                        |
　　| event                     |
　　| func                      |
　　| general_log               |
　　| help_category             |
　　| help_keyword              |
　　| help_relation             |
　　| help_topic                |
　　| host                      |
　　| ndb_binlog_index          |
　　| plugin                    |
　　| proc                      |
　　| procs_priv                |
　　| proxies_priv              |
　　| servers                   |
　　| slow_log                  |
　　| tables_priv               |
　　| time_zone                 |
　　| time_zone_leap_second     |
　　| time_zone_name            |
　　| time_zone_transition      |
　　| time_zone_transition_type |
　　| user                      |
　　+---------------------------+
　　24 rows in set (0.00 sec)
　　4、了解一个表中每个列的属性。快捷方式：desc table_name
　　mysql> show columns from db;
　　+-----------------------+---------------+------+-----+---------+-------+
　　| Field                 | Type          | Null | Key | Default | Extra |
　　+-----------------------+---------------+------+-----+---------+-------+
　　| Host                  | char(60)      | NO   | PRI |         |       |
　　| Db                    | char(64)      | NO   | PRI |         |       |
　　| User                  | char(16)      | NO   | PRI |         |       |
　　| Select_priv           | enum('N','Y') | NO   |     | N       |       |
　　| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
　　| Update_priv           | enum('N','Y') | NO   |     | N       |       |
　　| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
　　| Create_priv           | enum('N','Y') | NO   |     | N       |       |
　　| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
　　| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
　　| References_priv       | enum('N','Y') | NO   |     | N       |       |
　　| Index_priv            | enum('N','Y') | NO   |     | N       |       |

　　|>　　| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
　　| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
　　| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
　　| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
　　| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |

　　|>　　| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
　　| Event_priv            | enum('N','Y') | NO   |     | N       |       |
　　| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
　　+-----------------------+---------------+------+-----+---------+-------+
　　22 rows in set (0.00 sec)
　　5、查看show命令的详细使用说明
　　mysql> help show;
　　Name: 'SHOW'
　　Description:
　　SHOW has many forms that provide information about databases, tables,
　　columns, or status information about the server. This section describes
　　those following:
　　SHOW AUTHORS
　　SHOW {BINARY | MASTER} LOGS
　　SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
　　SHOW CHARACTER SET [like_or_where]
　　SHOW COLLATION [like_or_where]
　　SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
　　SHOW CONTRIBUTORS
　　SHOW CREATE DATABASE db_name
　　SHOW CREATE EVENT event_name
　　SHOW CREATE FUNCTION func_name
　　SHOW CREATE PROCEDURE proc_name
　　SHOW CREATE TABLE tbl_name
　　SHOW CREATE TRIGGER trigger_name
　　SHOW CREATE VIEW view_name
　　SHOW DATABASES [like_or_where]
　　SHOW ENGINE engine_name {STATUS | MUTEX}
　　SHOW [STORAGE] ENGINES
　　SHOW ERRORS [LIMIT [offset,] row_count]
　　SHOW EVENTS
　　SHOW FUNCTION CODE func_name
　　SHOW FUNCTION STATUS [like_or_where]
　　SHOW GRANTS FOR user
　　SHOW INDEX FROM tbl_name [FROM db_name]
　　SHOW MASTER STATUS
　　SHOW OPEN TABLES [FROM db_name] [like_or_where]
　　SHOW PLUGINS
　　SHOW PROCEDURE CODE proc_name
　　SHOW PROCEDURE STATUS [like_or_where]
　　SHOW PRIVILEGES
　　SHOW [FULL] PROCESSLIST
　　SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
　　SHOW PROFILES
　　SHOW SLAVE HOSTS
　　SHOW SLAVE STATUS
　　SHOW [GLOBAL | SESSION] STATUS [like_or_where]
　　SHOW TABLE STATUS [FROM db_name] [like_or_where]
　　SHOW [FULL] TABLES [FROM db_name] [like_or_where]
　　SHOW TRIGGERS [FROM db_name] [like_or_where]
　　SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
　　SHOW WARNINGS [LIMIT [offset,] row_count]
　　查看表之间的主键和外键关联
　　mysql> select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
　　-> REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where
　　-> REFERENCED_TABLE_NAME = 'user';