MySQL的基础学习（一）

一、数据库

1. 历史

数据管理：是数据库的核心任务，内容包括对数据的分类、组织、编码、储存、检索和维护。
从数据管理的角度看，数据库技术到目前共经历了以下几个阶段：

• 人工管理阶段：数据不保存、没有对数据进行管理的软件系统、没有文件的概念、组数据对应于一个程序，数据是面向应用的
• 文件系统阶段：数据需要长期保存在外存上供反复使用、程序之间有了一定的独立性、文件的形式已经多样化、数据的存取基本上以记录为单位
• 数据库系统阶段：采用复杂的结构化的数据模型、较高的数据独立性、最低的冗余度、数据控制功能

2. 特点

• 实现数据共享
• 减少数据的冗余度
• 数据的独立性
• 数据实现集中控制
• 数据一致性和可维护性，以确保数据的安全性和可靠性
  • 安全性控制
  • 完整性控制
  • 并发控制
  • 故障的发现和恢复
• 故障恢复

3. 概述

• 数据库的相关概述:：
  • 数据库(Database),简称DB
    是长期存储在计算机内、有组织、可共享的数据的集合，换言之存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。
  • 数据库管理系统(Database Management System),简称DBMS
    是位于用户与操作系统之间的一层数据管理软件,对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。
  • 结构化查询语言(Structured Query Language), 简称SQL
• 数据库与数据库管理系统的关系：
  数据库管理系统(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

二、对象

数据库对象是数据库的组成部分，常常用CREATE命令进行创建，可以使用ALTER命令修改，用DROP执行删除操作。常见的数据库对象有:

对象	描述
表(TABLE)	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束(CONSTRAINT)	执行数据校验的规则，用于保证数据完整性的规则
视图(IEW)	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引(INDEX)	用于提高查询性能，相当于书的目录
存储过程(PROCEDURE)	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数(FUNCTION)	用于完成一次特定的计算，具有一 个返回值
触发器(TRIGGER)	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

三、操作

1. SQL

• SQL可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进，每条命令以;或\g或\G结束关键字，不能被缩写也不能分行。
• SQL用来与数据库通信的语言，SQL语言属于第四代编程语言:
  • 第一代编程语言，机器语言，是面向机器的，通过二进制代码对其计算机操作
  • 第二代编程语言，汇编语言，使用指令对应的符号，来代替二进制代码
  • 第三代编程语言，高级开发语言，例如C、C++ Java等， 语言更加简单，操作更方便
  • 第四代编程语言，只告诉计算机需要做什么，不需要告诉计算机怎么做，更加接近自然语言
• sql的分类：
  (1)DDL(Data Definition Language)数据定义语言
  用来定义数据库对象:数据库，表，列等。关键字: CREATE, ALTER, DROP, RENAME, TRUNCATE等
  (2)DML(Data Manipulation Language)数据操作语言
  用来对数据库中表的数据进行增删改。关键字: insert, delete, update等
  (3)DQL(Data Query Language)数据查询语言
  用来查询数据库中表的记录(数据)。关键字: select, where等
  (4)DCL(Data Control Language)数据控制语言(了解)
  用来定义数据库的访问权限和安全级别，及创建用户。关键字: GRANT，REVOKE 等
• 标识符
  • 字母，数字，下划线，@, #, $符合组成
  • 首字母不能是数字和$
  • 不允许是Mysql的关键字以及保留字
  • .不允许出现空格和特殊字符
  • 长度小于128位
  • 必须保证所有的()、单引号、双引号是成对结束的必须使用英文状态下的半角输入方式
  • 字符串型和日期时间类型的数据可以使用单引号(')表示
  • 列的别名，尽量使用双引号(")，而且不建议省略as
• SQL注意事项
  • MySQL在Windows环境下是大小写不敏感的;
  • MySQL在Linux环境下是大小写敏感的:
    • 数据库名、表名、表的别名、变量名是严格区分大小写的
    • 关键字、函数名、列名(或字段名)、列的别名(字段的别名)是忽略大小写的。
• 注解.
  • 单行注释：#注释文字( MySQL特有的方式)
  • 单行注释：-- 注释文字(–后面必须包含一个空格。)
  • 多行注释：/* 注释文字 */

2. 操作命令

• 显示所有数据库：
  show databases;
• 创建数据库：
  []方括号中的内容表示可选
  create database [if not exists] bd01 [character set utf8];
• 选择数据库：
  use bd01;
• 查看当前在那个数据库下：
  只有在使用了use进入具体数据库才能查询到内容
  select database();
• 修改数据库的编码格式：
  alter database bd01 character set gbk;
• 显示数据库的创建过程：
  show create database bd01;
• 删除数据库：
  drop database bd01;
• 查看系统变量：
  show variables;
• 查看以auto开头的所有变量：
  show variables like 'auto%';

%代表0到任意字符
_代表一个字符的占位

• 临时设置变量：
  set character_set_server=gbk
• 显示系统状态，可以获得数据库运行信息：
  show status;

Aborted_clients	由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects	尝试已经失败的MySQL服务器的连接的次数。
Connections	试图连接MySQL服务器的次数。
Created_ tmp_tables	当执行语句时，已经被创造了的隐含临时表的数量。
Key_read_requests	请求从缓存读入一个键值的次数。
Key_reads	从磁盘物理读入-个键值的次数。
Not_ flushed_delayed. rows	在INSERT DELAY队列中等待写入的行的数量。
Open_tables	打开表的数量。
Open_files	打开文件的数量。
Open_streams	打开流的数量(主要用于日志记载)
Opened_tables	已经打开的表的数量。
Questions	发往服务器的查询的数量。
Slow_queries	要花超过long query _time时间的查询数量。
Threads_connected	当前打开的连接的数量。
Threads_running	不在睡眠的线程数量。
Uptime	服务器工作了多少秒。

• 显示哪个线程正在运行：
  show [fu1l] processlist

注意: 你也能使用mysqladminprocesslist命令得到这个信息。如果你有process权限，你能看见所有的线程，否则，你仅能看见你自己的线程。如果你不使用FULL选项，那么每个查询只有头100字符被显示出来。

• 列出对一 个用户必须发出以重复授权的授权命令：
  show grants for root;
• 执行sq|文件：
  source /home/master/briup_create.sql
source /home/master/briup_populate.sql
• 在数据库下， 显示其下的所有表：
  show tables [like 'br%'];
• 查看表的构建语句：
  show create table briup.s_emp;

注意: briup数据库如果当前位置在briup数据库下，briup可以省略不在briup数据库下，任意针对该数据库库表的操作都必须加数据库的名字。

• 查看表结构：
  describe s_emp;或desc s_emp;
• 查看s_ emp表中基于la开头列的信息：
  show columns from s_emp like 'la%';
• 显示某个表的索引：
  show index from s_emp [from briup];
• 查询数据库支持的引擎：
  show engines;
  • Archive
    为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。
  • Mylsam
    Mylsam存储引擎独立于操作系统，也就是可以在windows.上使用，也可以比较简单的将数据转移到linux操作系统上去。这种存储引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。
    • 特点:
      (1)不支持事务，但是并不代表着有事务操作的项目不能用Mylsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
      (2)不支持外键。
      (3)查询速度很快。如果数据库insert和update的操作比较多的话采用表锁效率低(建议使用innodb)。
      (4)对表进行加锁。
  • InnoDB
    InnoDB是一个事务型的存储引擎，有行级锁定和外键约束，适用于以下的场合:更新多的表，适合处理多重并发的更新请求。
    • 特点:
      (1)支持事务。
      (2)可以从灾难中恢复(通过bin-log日志等)。
      (3)外键约束。只有他支持外键。
      (4)支持自动增加列属性auto_ increment。
  • Memory
    Memory采用的逻辑介质是内存，响应速度应该是很快的，但是当mysqld守护进程崩溃的时候数据会丢失，另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类 型的数据不可用(长度不固定的)。
    • 使用Memory存储引擎情况:
      (1)目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控 制Memory表的大小，限制Memory表的最大的大小。
      (2)如果数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
      (3)存储在Memory表中的数据如果突然间丢失的话也没有太大的关系
    • 注意:
      Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。
  • Cluster/NDB
    MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

功能	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes----B树索引	是	是	是	否	否
Backup/point-in-time recovery (note 1)----备份、按照时间点恢复数据	是	是	是	是	是
Cluster database support—支持集群	否	否	否	否	是
Clustered indexes—聚集索引	否	否	是	否	否
Compressed data—数据压缩	是(note 2)	否	是	是	否
Data caches—数据缓存	否	N/A	是	否	是
Encrypted data—数据加密	是(note 3)	是(note 3)	是(note 4)	是(note 3)	是(note 3)
Foreign key support—外键支持	否	否	是	否	是(note 5)
Full-text search indexes—全文检索	是	否	是(note 6)	否	否
Geospatial data type support—地理空间数据支持	是	否	是	是	是
Geospatial indexing support—地理空间索引支持	是	否	是(note 7)	否	否
Hash indexes—哈希索引	否	是	No (note 8)	No	是
Index caches—索引缓存	是	N/A	是	No	是
Locking granularity—锁粒度	Table	Table	Row	Row	Row
MVCC—多版本并发控制	否	否	是	否	否
Replication support （note 1）—支持复制	是	Limited (note 9)	是	是	是
Storage limits—存储限制	256TB	RAM	64TB	None	384EB
T-tree indexes—T树索引	否	否	否	否	是
Transactions—事务	否	否	是	否	是
Update statistics for data dictionary—更新数据字典的统计信息	是	是	是	是	是