一、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服务器端同客户端通信方式图示

455442.png

1、mysql安装方式:

二进制格式:rpm、通用二进制包。

源码格式:make(早期),5.7以后使用cmake编译。

1.1安装cmake(编译)

    ./bootstrap

    make

    make install

1.2编译安装mysql-5.5.33

[root@dt0b4007c ~]# cmake -DCMAKE_INNOBASE_STORAGE_ENGINE=1 -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/mydata/data -DSYSCONFDIR=/etc -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

[root@dt0b4007c ~]# make

[root@dt0b4007c ~]# make install


1.3创建mysql用户,并给数据目录赋予mysql权限。

1.4初始化mysql数据库

[root@10 mysql]# cd /usr/local/dtedu/mysql/

[root@10 mysql]# ll scripts/

总用量 16

-rwxr-xr-x. 1 mysql mysql 14816 8月  26 2012 mysql_install_db

[root@10 mysql]# script/mysql_install_db --user=mysql --datadir=/mydata/data/  —basedir=/use/local/mysql —default-file=/etc/my.cnf


1.5将启动脚本加入到init.d目录中

[root@10 support-files]# cd /usr/local/dtedu/mysql/support-files/

[root@10 support-files]# cp mysql.server /etc/rc.d/init.d/mysqld

[root@10 support-files]# chkconfig --add mysqld

[root@10 support-files]# chkconfig --level 2345 mysqld on

[root@10 support-files]# chkconfig --list mysqld

mysqld         0:关闭1:关闭2:启用3:启用4:启用5:启用6:关闭


cmake编译安装mysql常用参数:

183131.png

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 <init.sql

1、选择数据库,要查看数据库内容需要先选择指定的数据库。

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

| Alter_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       |       |

| Alter_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';