mysql必知必会

2018年7月13日笔记

1.数据库的介绍

1.1 什么是数据库

数据库是按照数据结构来组织、存储和管理数据的仓库，它产生于距今。
六十多年前，随着信息技术和市场的发展，特别是二十世纪九十年代以后，数据管理不再仅仅是存储和管理数据，而转变为用户所需要的各种数据管理的方式。
数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各方面得到了广泛的应用。
在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分，是进行科学决策和决策管理的重要技术手段。

1.2 数据库的发展

1951年，雷明顿兰德公司（Remington Rand Inc.）的一种叫做Univac I 的计算机推出了一种一秒
钟可以输入数百条记录的磁带驱动器。
1956年，IBM生产出第一个磁盘驱动器—— the Model 305 RAMAC，可以储存5MB的数据。
1961年，通用电气公司的Charles Bachman 开发出世界上第一个网状DBMS也是第一个数据库管
理系统—— 集成数据存储（Integrated DataStore IDS）。
1968年，Childs已经提出了面向集合的模型
1970年，IBM的研究员E.F.Codd博士发表了一篇论文，提出了关系模型的概念，奠定了关系模型
的理论基础。这篇论文被普遍认为是数据库系统历史上具有划时代意义的里程碑。
1974 年，IBM的Ray Boyce和Don Chamberlin将Codd关系数据库的12条准则的数学定义以简单
的关键字语法表现出来，里程碑式地提出了SQL(Structured Query Language)语言。

2.MySQL的介绍

2.1 mysql的诞生

1979年，有一个人叫Monty Widenius, 为一个叫TcX的小公司打工，并用BASIC设计了
一个报表工具，可以在4M主频和16KB内在的计算机上运行。过了不久，又将此工具，
使用C语言重写，移植到Unix平台，当时，它只是一个很底层的面向报表的存储引擎。
这个工具叫做Unireg。
1990年，TcX的customer 中开始有人要求要为它的API提供SQL支持，当时，有人想到
了直接使用商用数据库算了，但是Monty觉得商用数据库的速度难令人满意。于是，
他直接借助于mSQL的代码，将它集成到自己的存储引擎中。但不巧的是，效果并不太
好。于是, Monty雄心大起，决心自己重写一个SQL支持。
1996年，MySQL 1.0发布,只面向部分人，相当于内部发布。
1996年10月，MySQL 3.11.1发布。最开始，只提供了Solaris下的二进制版本。
1996年11月，发布Linux版本。
1999-2000年，有一家公司在瑞典成立了，叫MySQLAB，公司名中的“AB”
是瑞典语，将其翻译为“MySQL有限公司”。雇了几个人,与Sleepycat合作，
开发出了 Berkeley DB引擎, 因为BDB支持事务处理，所以，MySQL从此开始
支持事务处理了。
2000年4月，MySQL对旧的存储引擎进行了整理，命名为MyISAM。
2001年，Heikiki Tuuri向MySQL提出建议，希望能集成他们的存储引擎
InnoDB，这个引擎同样支持事务处理，还支持行级锁。
2003年12月，到了MySQL5.0，开始有View, 存储过程。
2008年1月16号 MySQL被Sun公司收购。MySQL的创始人Michael “Monty”
Widenius也弄出MariaDB来对抗MySQL。

2.2 为什么学习mysql

1、MySQL是开源的数据库
everyone都可以获取数据库的源代码，everyone可以修正MySQL的缺陷
2、MySQL跨平台
不仅可在windows系统中运行，也可在Linux、UNIX和Mac OS系统中运行
3、价格优势
自由软件，可以从MySQL官网直接下载免费使用。
4、功能强大且使用方便
MySQL是一个真正的多用户、多线程SQL数据库服务器。Mysqld和客户程序组成，
可快速，有效和安全的处理大量数据，使用非常简单，相对于Oracle而言。

2.3 mysql简介

2.3.1 定义

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发， 目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系 统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。 MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同 的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并 提高了灵活性。 MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。 MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积 小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小 型网站的开发都选择 MySQL 作为网站数据库。 由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环 境。

2.3.2 应用环境

与其他的大型数据库相比,MySql有它的不足之处，但是这丝 毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业 来说，MySQL提供的功能已经绰绰有余，而且由于 MySQL是开放源 码软件，因此可以大大降低总体拥有成本。 Linux作为操作系统，Apache 或Nginx作为 Web 服务器， MySQL 作为数据库，PHP/Perl/Python作为服务器端脚本解释器。由 于这四个软件都是免费或开放源码软件（FLOSS)，因此使用这种方式 不用花一分钱（除开人工成本）就可以建立起一个稳定、免费的网站 系统，被业界称为“LAMP“或“LNMP”组合3 。

2.3.3 事务

一般是指要做的或所做的事情。 在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。 事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起， 并用形如begin transaction和end transaction语句（或函数调用）来界定。 事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。
概念 例如：在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。
特性 事务应该具有4个属性：原子性、一致性、隔离性、持久性。 原子性:一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性:事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性:一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务 是隔离的，并发执行的各个事务之间不能互相干扰。
持久性:持久性也称永久性指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来 的其他操作或故障不应该对其有任何影响。

2.4 关系型和非关系型的数据库

2.4.1 优缺点对比

Nosql和关系型数据库比较？ 优点： 1）成本：Nosql数据库简单易部署，基本都是开源软件，不需要像使用oracle那样花费大量成 本购买使用，相比关系型数据库价格便宜。 2）查询速度：Nosql数据库将数据存储于缓存之中，关系型数据库将数据存储在硬盘中，自 然查询速度远不及Nosql数据库。 3）存储数据的格式：Nosql的存储格式是key,value形式、文档形式、图片形式等等，所以可 以存储基础类型以及对象或者是集合等各种格式，而数据库则只支持基础类型。 4）扩展性：关系型数据库有类似join这样的多表查询机制的限制导致扩展很艰难。 缺点： 1）维护的工具和资料有限，因为Nosql是属于新的技术，不能和关系型数据库10几年的技术同 日而语。 2）将产生一定用户的学习和使用成本。 3）不提供关系型数据库对事物的处理。

2.4.2 关系数据库的不足

不擅长的处理:

1. 大量数据的写入处理
2. 为有数据更新的表做索引或表结构变更
3. 字段不固定时应用
4. 对简单查询需要快速返回结果的处理
   --大量数据的写入处理 读写集中在一个数据库上让数据库不堪重负，大部分网站已使用主从复制技术实现读写分离， 以提高读写性能和读库的可扩展性。 所以在进行大量数据操作时，会使用数据库主从模式。数据的写入由主数据库负责，数据的 读入由从数据库负责，可以比较简单地通过增加从数据库来实现规模化，但是数据的写入却 完全没有简单的方法来解决规模化问题。
   第一，要想将数据的写入规模化，可以考虑把主数据库从一台增加到两台，作为互相关联复 制的二元主数据库使用，确实这样可以把每台主数据库的负荷减少一半，但是更新处理会发 生冲突，可能会造成数据的不一致，为了避免这样的问题，需要把对每个表的请求分别分配 给合适的主数据库来处理。
   第二，可以考虑把数据库分割开来，分别放在不同的数据库服务器上，比如将不同的表放在 不同的数据库服务器上，数据库分割可以减少每台数据库服务器上的数据量，以便减少硬盘I O的输入、输出处理，实现内存上的高速处理。但是由于分别存储字不同服务器上的表之间无 法进行Join处理，数据库分割的时候就需要预先考虑这些问题，数据库分割之后，如果一定要 进行Join处理，就必须要在程序中进行关联，这是非常困难的。
   --为有数据更新的表做索引或表结构变更
   在使用关系型数据库时，为了加快查询速度需要创建索引，为了增加必要的字段就一定要改 变表结构，为了进行这些处理，需要对表进行共享锁定，这期间数据变更、更新、插入、删 除等都是无法进行的。如果需要进行一些耗时操作，例如为数据量比较大的表创建索引或是 变更其表结构，就需要特别注意，长时间内数据可能无法进行更新
   --字段不固定时的应用 如果字段不固定，利用关系型数据库也是比较困难的，有人会说，需要的时候加个字段就可 以了，这样的方法也不是不可以，但在实际运用中每次都进行反复的表结构变更是非常痛苦 的。你也可以预先设定大量的预备字段，但这样的话，时间一长很容易弄不清除字段和数据 的对应状态，即哪个字段保存有哪些数据。
   --对简单查询需要快速返回结果的处理 （这里的“简单”指的是没有复杂的查询条件） 这一点称不上是缺点，但不管怎样，关系型数据库并不擅长对简单的查询快速返回结果，因 为关系型数据库是使用专门的sql语言进行数据读取的，它需要对sql语句进行解析，同时还有 对表的锁定和解锁等这样的额外开销，这里并不是说关系型数据库的速度太慢，而只是想告 诉大家若希望对简单查询进行高速处理，则没有必要使用关系型数据库。

2.4.3 非关系型数据库

NoSQL数据库只应用在特定领域，基本上不进行复杂的处理，但它恰恰弥补了之前所列举的 关系型数据库的不足之处。 优点： 易于数据的分散 各个数据之间存在关联是关系型数据库得名的主要原因，为了进行join处理，关系型数据库不 得不把数据存储在同一个服务器内，这不利于数据的分散，这也是关系型数据库并不擅长大 数据量的写入处理的原因。相反NoSQL数据库原本就不支持Join处理，各个数据都是独立设计 的，很容易把数据分散在多个服务器上，故减少了每个服务器上的数据量，即使要处理大量 数据的写入，也变得更加容易，数据的读入操作当然也同样容易。

一、典型的NoSQL数据库
临时性键值存储（memcached、Redis）、永久性键值存储（ROMA、Redis）、面向文档的数 据库（MongoDB、CouchDB）、面向列的数据库（HBase）
键值存储：它的数据是以键值的形式存储的，虽然它的速度非常快，但基本上只能通过键的完全一致查 询获取数据，根据数据的保存方式可以分为临时性、永久性和两者兼具 三种。
（1）临时性 所谓临时性就是数据有可能丢失，memcached把所有数据都保存在内存中，这样保存和读 取的速度非常快，但是当memcached停止时，数据就不存在了。由于数据保存在内存中，所 以无法操作超出内存容量的数据，旧数据会丢失。总结来说： 。在内存中保存数据 。可以进行非常快速的保存和读取处理 。数据有可能丢失
（2）永久性 所谓永久性就是数据不会丢失，这里的键值存储是把数据保存在硬盘上，与临时性比起来， 由于必然要发生对硬盘的IO操作，所以性能上还是有差距的，但数据不会丢失是它最大的优 势。总结来说： 。在硬盘上保存数据 。可以进行非常快速的保存和读取处理（但无法与memcached相比） 。数据不会丢失
（3） 两者兼备 Redis属于这种类型。Redis有些特殊，临时性和永久性兼具。Redis首先把数据保存在内存中，在满足特定条件（默认是 15分钟一次以上，5分钟内10个以上，1分钟内10000个以上的键发生变更）的时候将数据写入到硬盘中，这样既确保了内存中数据的处理速度，又可以通过写入硬盘来保证数据的永久性，这种类型的数据库特别适合处理数组类型的数据。
总结来说：
同时在内存和硬盘上保存数据
可以进行非常快速的保存和读取处理
保存在硬盘上的数据不会消失（可以恢复）
适合于处理数组类型的数据

二、面向文档的数据库 MongoDB、CouchDB属于这种类型，它们属于NoSQL数据库，但与键值存储相异。
（1）不定义表结构即使不定义表结构，也可以像定义了表结构一样使用，还省去了变更表结构的麻烦。 （2）可以使用复杂的查询条件 跟键值存储不同的是，面向文档的数据库可以通过复杂的查询条件来获取数据，虽然不 具备事务处理和Join这些关系型数据库所具有的处理能力，但初次以外的其他处理基本上都能实现。
总结：关系型数据库与NoSQL数据库并非对立而是互补的关系，即通常情况下使用关系型数据库，
在适合使用NoSQL的时候使用NoSQL数据库，让NoSQL数据库对关系型数据库的不足 进行弥补。

3.SQL语言基本语法

3.1 SQL语言

SQL一共分3种：
数据定义语言（Data Definition Language,DDL），数据库管理系统提供了数据定义语言定义数据库涉及各种对象，定义数据的完整性约束、保密限制等约束。
数据操作语言（Data manipulation Language, DML），数据库管理系统提供了数据操作语言实现对数据的操作。基本的数据操作的两类：检索（查询select）和更新（插入insert,删除insert和更新update）。
数据控制语言（Data Control Language,DCL）数据库管理系统提供了数据控制语言实现对数据库的控制，包括数据完整性控制、数据安全性控制和数据库的恢复。

3.2 myql中的约束

数据库的完整新，是值数据库的准确性和一致性。、
4种约束：主键约束、外键约束、唯一约束、自增长约束
约束的作用：
1.提高查询速度
2.确保数据的唯一性
3.可以加快表与表之间的连接，实现表与表之间的参照完整性
4.使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
5.全文检索字段进行搜索优化

3.2.1 主键

某一个属性能唯一标识一条记录，如学生表（学号、姓名、班级、性别），学号是唯一标识的，可作为主键。
特点：最常见的索引类型、确保数据记录的唯一性、确定特定数据记录在数据库的位置

3.2.2 外键

特点：1.让数据库自己通过外键来保证数据的完整性和一致性
2.能够增加ER图的可读性
外键约束：子表的取值范围守父表约束

3.2.3 唯一索引

作用：避免同一表中某数据列中的值重复
主键索引的区别：主键索引只能有一个、唯一索引可有多个

3.2.4 自增长

作用：该列的每一行的值都是递增的，而且是自动的
限制条件：可以使用自动增长的列必须是主键、主键的类型必须是int类型、自动增长的值如果被删除是不可恢复的

3.3 常用mysql语句

1.查看数据库 show databases
2.建立数据库 create database {}，大括号替换为数据库名
3.删除数据库 drop database {}, 大括号替换为数据库名
4.打开数据库 use {}，大括号替换为数据库名
5.查看数据表 show tables
6.查看数据表结构 desc {}，大括号替换为数据表名
7.删除数据表 drop table {}，大括号替换为数据表名

练习

图片.png-45kB

use myschool;
create table student(
stu_no int primary key,
stu_pwd varchar(20) not null default "123456",
stu_name varchar(50) not null,
stu_sex char(2) not null,
stu_grand_id int not null,
stu_phone varchar(255) not null,
stu_address varchar(255),
stu_borndate date,
stu_email varchar(50)
);

图片.png-29.9kB

use myschool;
create table grand(
g_id int primary key comment "年级编号",
g_name varchar(50) not null comment "年级名"
);
create table subject(
sub_id int primary key comment "科目编号",
sub_name varchar(50) not null comment "科目名",
sub_hour int not null comment "学时",
sub_grand_id int not null comment "所属年级"
);

图片.png-19.9kB

use myschool;
create table result(
r_id int primary key comment "标识符",
r_student_no int not null comment "学号",
r_subject_id int not null comment "所考科目",
r_result int comment "分数",
r_examdate datetime not null comment "考试日期"
);