MySQL(一)：基本原理

一、数据库定义

数据库，简单来说是本身可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据运行新增、截取、更新、删除等操作。

数据库指的是以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。

数据库的组织模式：

层次型数据结构

网状型数据结构

关系型数据结构(MySQL)

非关系型数据结构(NoSQL)

关系型数据结构图示

上图描述了从用户层到物理层的工作结构。

二、数据库基础知识

SQL：structure query language；一种介于关系代数与关系演算之间的结构化查询语言，是一个通用的、功能极强的关系性数据库语言。

SQL语言组成部分：

数据定义语言(DDL:Data Definition Language)：CREATE, DROP, ALTER

数据操纵语言(DML:Data Manipulation Language)：SELECT, INSERT, UPDATE, DELETE

数据控制语言(DCL:Data Control Language)：GRANT, REVOKE

事务(transaction)：事务就是一组原子性的查询语句；也即将多个查询当作一个独立的工作单元。

ACID测试：能满足ACID测试就表示其支持事务；或兼容事务。

原子性（Atomic）:事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

一致性（Consistency）:事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。

隔离性（Isolation）:多个事务并发执行时，一个事务所做的操作在提交之前对其他事务是不可见的。

持久性（Durability）:已被提交的事务对数据库的修改应该永久保存在数据库中。

三、MySQL基础特性

键：key(字段)

主键：能够唯一标识表中每一个记录的字段或字段的组合。

候选键：能够作为唯一标识表中每一个记录的字段。

外键：依赖别的表当做属性以创建彼此的关系；而这个属性就是外键。

唯一键：可以确定一条记录的字段(或字段组合)，一张表可以有多个唯一键。

约束：constraint

主键约束：用于定义基本表的主键，起惟一标识作用；对一张表来说；主键只能有一个；不能为空；不能重复。

外键约束：定义了一个表中数据与另一个表中的数据的联系；对应其他表中的字段；在自己表中没有的不能填入。

唯一键约束：用于指明创建惟一约束的列上的取值必须惟一；可以为空；不能重复；可以有多个。

检查式约束：用户自定义有效取值范围；通常为布尔表达式。

非空约束：只用于定义列约束。

mysql中的比较方式、存储空间的取值范围以及参与的运算等都取决于mysql的数据类型；所以数据类型非常重要；具体的字段的数据类型：

字符型

char(#)

用于保存定长(size)字节的字符串数据。编码时不区分字符大小写。最大长度为255字节.

varchar(#)

用于保存变长的字符串数据。其中最大字节长度由(size)指定。每行长度可变，最大长度为65535字节。数据长度缺省为1 ；编码时区不分字符大小写。

binary(#)

固定长度的二进制字符串；每行定长(不足部分补为空格)；最大长度为255字节，缺省值为每行1字节。编码时区分字符大小写。

varbinary(#)

可变长度的长度的二进制字符串。每行长度可变，最大长度为65535字节。数据长度缺省为1。编码时区分字符大小写。

text

一个BLOB或TEXT列，最大长度为65535(2^16-1)个字符；编码时不区分字符大小写

blob

二进制的大对象；一个BLOB或TEXT列，最大长度为65535(2^16-1)个字符；编码时区分字符大小写。

数值型

精确数值型

tinyint

保存整型数据，范围为0到255。最大长度为1字节。

smallint

保存整型数据，范围为-32767到+32767最,大长度为2字节。

mediumint

保存整型数据，范围为-8388608至8388607或0至16777215。最大长度为3字节。

int

保存整型数据，范围为-2147483647到+2147483647。最大长度为4字节。

bigint

保存整型数据，范围为-9223372036854775808至9223372036854775807或0至18446744073709551615。最大长度为8字节

decimal

保存精确数值数据类型，范围为-10E-38到+10E-38,最大长度为2~17字节。

近似数值型

float

最小非零值：±1.175494351E – 38，同double一样适用于精度要求高的场合

double

最小非零值：±2.2250738585072014E - 308

日期时间型

date

1000-01-01~9999-12-31 3字节

time

-838:59:59~838:59:59 3字节

datetime

1000-01-01 00:00:00~9999-12-31 23:59:59 8字节

timestamp

19700101000000~指定时间的某个时刻 4字节

year

YYYY 格式表示的年份值

MySQL内置类型

enum

枚举类型(指定范围内中某一种)

set

集合型(指定范围内任意组合)

布尔型：tinyint{0|1}

NULL

字段或字段类型的修饰符

not null

非空

null

空

unsigned

无符号整型(只能用于数值)

default [value]

字符需要加引号；数据绝对不能加引号

auto_increment

自动增长类型的字段必须为主键或唯一键

primary key

定义主键

unique key

定义唯一键

Example:

<code>mysql> create table stu1 (</code><code>Id</code> <code>int</code> <code>unsigned auto_increment unique key,Name char(</code><code>30</code><code>) </code><code>not</code> <code>null,Age tinyint unsigned </code><code>not</code> <code>null,Gender enum(</code><code>'F'</code><code>,</code><code>'M'</code><code>) </code><code>not</code> <code>null default </code><code>'M'</code><code>);</code>

<code>Query OK, </code><code>0</code> <code>rows affected (</code><code>0.04</code> <code>sec)</code>

<code>mysql> desc stu1;</code>

<code>mysql></code>

<code>mysql> insert into stu1 (Name,Age,Gender) values (</code><code>'Tom'</code><code>,</code><code>20</code><code>,</code><code>'M'</code><code>),</code>

<code> </code><code>-</code><code>> (</code><code>'Pipi'</code><code>,</code><code>20</code><code>,</code><code>'M'</code><code>), </code><code>#这里插入数据都没有插入Id号；因为上述定义的是自增长</code>

<code> </code><code>-</code><code>> (</code><code>'Jerry'</code><code>,</code><code>18</code><code>,</code><code>'F'</code><code>),</code>

<code>Query OK, </code><code>4</code> <code>rows affected (</code><code>0.00</code> <code>sec)</code>

<code>Records: </code><code>4</code> <code>Duplicates: </code><code>0</code> <code>Warnings: </code><code>0</code>

<code>mysql> select </code><code>*</code> <code>from</code> <code>stu1; </code><code>#显示表字段信息</code>

SQL语句可以直接在mysql里面帮助获取：帮助信息显示的也很详细

<code>mysql> </code><code>help</code> <code>insert</code>

<code>#[]括号内的都是可省的；{}内是必选的；</code>

<code>Name: </code><code>'INSERT'</code>

<code>Description:</code>

<code>Syntax:</code>

<code>INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]</code>

<code> </code><code>{VALUES | VALUE} ({expr | DEFAULT},...),(...),...</code>

<code> </code><code>[ ON DUPLICATE KEY UPDATE</code>

<code>mysql> </code><code>help</code> <code>select</code>

<code>Name: </code><code>'SELECT'</code>

<code>SELECT</code>

<code> </code><code>[</code><code>ALL</code> <code>| DISTINCT | DISTINCTROW ]</code>

<code> </code><code>[HIGH_PRIORITY]</code>

<code> </code><code>[STRAIGHT_JOIN]</code>

<code> </code><code>[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]</code>

<code> </code><code>[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]</code>

<code> </code><code>select_expr [, select_expr ...]</code>

<code> </code><code>[FROM table_references</code>

<code> </code><code>[WHERE where_condition]</code>

<code> </code><code>[GROUP BY {col_name | expr | position}</code>

<code> </code><code>[ASC | DESC], ... [WITH ROLLUP]]</code>

<code> </code><code>[HAVING where_condition]</code>

<code> </code><code>[ORDER BY {col_name | expr | position}</code>

<code> </code><code>[LIMIT {[offset,] row_count | row_count OFFSET offset}]</code>

<code> </code><code>[PROCEDURE procedure_name(argument_list)]</code>

<code> </code><code>[INTO OUTFILE </code><code>'file_name'</code>

<code> </code><code>[CHARACTER </code><code>SET</code> <code>charset_name]</code>

<code> </code><code>export_options</code>

<code> </code><code>| INTO DUMPFILE </code><code>'file_name'</code>

<code> </code><code>[FOR UPDATE | LOCK IN SHARE MODE]]</code>

四、布尔条件表达式操作符

equal；等于

<=>

null sale equal to；与空值比较的安全方式

<> | !=

not equal to；不等于

less；小于

less than or equal to；小于等于

greater；大于

greater than or equal to；大于等于

is null

为空

is not null

不为空

通配；%(任意长度的任意字符), _(任意单个字符)

regexp | rlike

支持使用正则表达式

判断指定字段是否在给定的列表中

between... and...

位于指定的范围之间

组合条件测试

not | !

非

and | &&

与

or | ||

或

聚合函数

sum()

求和

avg()

求平均值

max()

最大值

min()

最小值

count()

计数

根据数据库操作语言(DML)列出几个简单的例子；后续作详细说明

Examples:

select：

<code>mysql> select </code><code>*</code> <code>from</code> <code>students;</code>

<code>mysql> select Name,Age </code><code>from</code> <code>students where Age > </code><code>20</code><code>;</code>

<code>#查找年龄大于20的</code>

<code>mysql> select Name,Gender </code><code>from</code> <code>students where Gender </code><code>=</code> <code>'F'</code><code>;</code>

<code>#查找性别为女的</code>

<code>| Name | Gender |</code>

<code>| Xue Baochai | F |</code>

<code>mysql> select Name,Age,Gender </code><code>from</code> <code>students where Age > </code><code>20</code> <code>and</code> <code>Gender </code><code>=</code> <code>'F'</code><code>;</code>

<code>| Name | Age | Gender |</code>

delete：

<code>mysql> delete </code><code>from</code> <code>students where Name like </code><code>'X%'</code><code>;</code>

<code>Query OK, </code><code>2</code> <code>rows affected (</code><code>0.00</code> <code>sec)</code>

update：

<code>mysql> update students </code><code>set</code> <code>Age </code><code>=</code> <code>40</code> <code>where Name </code><code>=</code> <code>'Wu yong'</code><code>;</code>

<code>Query OK, </code><code>1</code> <code>row affected (</code><code>0.01</code> <code>sec)</code>

<code>Rows matched: </code><code>1</code> <code>Changed: </code><code>1</code> <code>Warnings: </code><code>0</code>

几个简单show命令的用法

<code>mysql> show create table stu1\G;</code>

<code>#查看创建表所使用的命令。</code>

<code>#\G为客户端命令；可以使用\?来查看。</code>

<code> </code><code>Table: stu1</code>

<code>Create Table: CREATE TABLE `stu1` (</code>

<code> </code><code>`</code><code>Id</code><code>` </code><code>int</code><code>(</code><code>10</code><code>) unsigned NOT NULL AUTO_INCREMENT,</code>

<code> </code><code>`Age` tinyint(</code><code>3</code><code>) unsigned NOT NULL,</code>

<code> </code><code>`Gender` enum(</code><code>'F'</code><code>,</code><code>'M'</code><code>) NOT NULL DEFAULT </code><code>'M'</code><code>,</code>

<code> </code><code>UNIQUE KEY `</code><code>Id</code><code>` (`</code><code>Id</code><code>`)</code>

<code>) ENGINE</code><code>=</code><code>MyISAM AUTO_INCREMENT</code><code>=</code><code>9</code> <code>DEFAULT CHARSET</code><code>=</code><code>latin1</code>

<code>ERROR:</code>

<code>No query specified</code>

<code>mysql> show engines\G;</code>

<code>#查看当前数据库支持的存储引擎</code>

<code> </code><code>Engine: MRG_MYISAM</code>

<code> </code><code>Support: YES </code><code>#是否支持</code>

<code> </code><code>Comment: Collection of identical MyISAM tables</code>

<code>Transactions: NO </code><code>#是否支持事务</code>

<code> </code><code>XA: NO </code><code>#是否支持分布式事务</code>

<code> </code><code>Savepoints: NO </code><code>#是否支持保存点</code>

<code> </code><code>Engine: CSV</code>

<code> </code><code>Support: YES</code>

<code> </code><code>Comment: CSV storage engine</code>

<code>Transactions: NO</code>

<code> </code><code>Savepoints: NO</code>

<code> </code><code>Engine: MyISAM</code>

<code> </code><code>Support: DEFAULT</code>

<code> </code><code>Comment: Default engine as of MySQL </code><code>3.23</code> <code>with great performance</code>

<code> </code><code>Engine: InnoDB</code>

<code> </code><code>Comment: Supports transactions, row</code><code>-</code><code>level locking, </code><code>and</code> <code>foreign keys</code>

<code>Transactions: YES</code>

<code> </code><code>Savepoints: YES</code>

<code> </code><code>Engine: MEMORY</code>

<code> </code><code>Comment: </code><code>Hash</code> <code>based, stored </code><code>in</code> <code>memory, useful </code><code>for</code> <code>temporary tables</code>

<code>mysql> show character </code><code>set</code><code>;</code>

<code>#显示字符集</code>

<code>mysql> show collation;</code>

<code>#查看show的帮助都有详细说明</code>

<code>mysql> </code><code>help</code> <code>show</code>

<code>SHOW has many forms that provide information about databases, tables,</code>

<code>columns, </code><code>or</code> <code>status information about the server. This section describes</code>

<code>those following:</code>

<code>SHOW AUTHORS</code>

<code>SHOW {BINARY | MASTER} LOGS</code>

<code>SHOW BINLOG EVENTS [IN </code><code>'log_name'</code><code>] [FROM pos] [LIMIT [offset,] row_count]</code>

<code>SHOW CHARACTER </code><code>SET</code> <code>[like_or_where]</code>

<code>SHOW COLLATION [like_or_where]</code>

<code>SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]</code>

<code>SHOW CONTRIBUTORS</code>

<code>SHOW CREATE DATABASE db_name</code>

<code>SHOW CREATE EVENT event_name</code>

<code>SHOW CREATE FUNCTION func_name</code>

<code>SHOW CREATE PROCEDURE proc_name</code>

<code>SHOW CREATE TABLE tbl_name</code>

<code>SHOW CREATE TRIGGER trigger_name</code>

关系型数据库的基本用法到此，由于数据库理论等方面内容很多；无法总结的很全面；可能存在很多遗漏的地方。后续介绍详细用法。

本文转自Mr_陈 51CTO博客，原文链接：http://blog.51cto.com/chenpipi/1392761，如需转载请自行联系原作者

MySQL(一)：基本原理

继续阅读

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述