MySQL(一)：基本原理

MySQL(一)：基本原理

2014-04-09 13:59:08

标签： mysql  DDL  DML  DCL

原创作品，允许转载，转载时请务必以超链接形式标明文章  原始出处 、作者信息和本声明。否则将追究法律责任。 http://chenpipi.blog.51cto.com/8563610/1392761

一、数据库定义

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

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

数据库的组织模式：

• 层次型数据结构

• 网状型数据结构

• 关系型数据结构(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基础特性

MySQL的安装这里就不做介绍；Linux下编译安装LAMP并分离为多台服务器有介绍。下面来介绍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:

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> create table stu1 ( Id  int  unsigned auto_increment unique key,Name char( 30 )  not  null,Age tinyint unsigned  not  null,Gender enum( 'F' , 'M' )  not  null default  'M' ); Query OK,  0  rows affected ( 0.04  sec) mysql> desc stu1; + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - + | Field  |  Type                 | Null | Key | Default | Extra          | + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - + |  Id      |  int ( 10 ) unsigned    | NO   | PRI | NULL    | auto_increment | | Name   | char( 30 )            | NO   |     | NULL    |                | | Age    | tinyint( 3 ) unsigned | NO   |     | NULL    |                | | Gender | enum( 'F' , 'M' )       | NO   |     | M       |                | + - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - - - - - - - + 4  rows  in  set  ( 0.00  sec) mysql>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> insert into stu1 (Name,Age,Gender) values ( 'Tom' , 20 , 'M' ),      - > ( 'Pipi' , 20 , 'M' ),     #这里插入数据都没有插入Id号；因为上述定义的是自增长      - > ( 'Jerry' , 18 , 'F' ),      - > ( 'Soul' , 24 , 'M' ); Query OK,  4  rows affected ( 0.00  sec) Records:  4   Duplicates:  0   Warnings:  0 mysql> select  *  from  stu1;     #显示表字段信息 + - - - - + - - - - - - - + - - - - - + - - - - - - - - + |  Id  | Name  | Age | Gender | + - - - - + - - - - - - - + - - - - - + - - - - - - - - + |   1  | Tom   |   20  | M      | |   2  | Pipi  |   20  | M      | |   3  | Jerry |   18  | F      | |   4  | Soul  |   24  | M      | + - - - - + - - - - - - - + - - - - - + - - - - - - - - +

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

mysql>  help  insert #[]括号内的都是可省的；{}内是必选的； Name:  'INSERT' Description: Syntax: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]      [INTO] tbl_name [(col_name,...)]      {VALUES | VALUE} ({expr | DEFAULT},...),(...),...      [ ON DUPLICATE KEY UPDATE        col_name = expr          [, col_name = expr] ... ] # # mysql>  help  select Name:  'SELECT' Description: Syntax: SELECT      [ ALL  | DISTINCT | DISTINCTROW ]        [HIGH_PRIORITY]        [STRAIGHT_JOIN]        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]      select_expr [, select_expr ...]      [FROM table_references      [WHERE where_condition]      [GROUP BY {col_name | expr | position}        [ASC | DESC], ... [WITH ROLLUP]]      [HAVING where_condition]      [ORDER BY {col_name | expr | position}        [ASC | DESC], ...]      [LIMIT {[offset,] row_count | row_count OFFSET offset}]      [PROCEDURE procedure_name(argument_list)]      [INTO OUTFILE  'file_name'          [CHARACTER  SET  charset_name]          export_options        | INTO DUMPFILE  'file_name'        | INTO var_name [, var_name]]      [FOR UPDATE | LOCK IN SHARE MODE]]

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

=	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	不为空
like	通配；%(任意长度的任意字符), _(任意单个字符)
regexp | rlike	支持使用正则表达式
in	判断指定字段是否在给定的列表中
between... and...	位于指定的范围之间

组合条件测试

not | !	非
and | &&	与
or | ||	或

聚合函数

sum()	求和
avg()	求平均值
max()	最大值
min()	最小值
count()	计数

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

Examples:

select：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

mysql> select  *  from  students; + - - - - - + - - - - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - + | SID | Name        | Age | Gender | Tutor       | ClassID | + - - - - - + - - - - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - + |    1  | Guo Jing    |   27  | M      | Song Jiang  |        2  | |    2  | Yang Guo    |   28  | M      | Hu Sanniang |        3  | |    3  | Guo Polu    |   21  | M      | Jia Baoyu   |        3  | |    4  | Xue Baochai |   19  | F      | Rong Momo   |        1  | |    5  | Xia Yuhe    |   37  | F      | Shi Qian    |        2  | |    6  | Wu Yong     |   51  | M      | Lin Daiyu   |        1  | |    7  | tom         |   11  | M      | jerry       |        1  | |    8  | tomy        |   13  | M      | NULL        |        4  | + - - - - - + - - - - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - + mysql> select Name,Age  from  students where Age >  20 ; #查找年龄大于20的 + - - - - - - - - - - + - - - - - + | Name     | Age | + - - - - - - - - - - + - - - - - + | Guo Jing |   27  | | Yang Guo |   28  | | Guo Polu |   21  | | Xia Yuhe |   37  | | Wu Yong  |   51  | + - - - - - - - - - - + - - - - - + mysql> select Name,Gender  from  students where Gender  =  'F' ; #查找性别为女的 + - - - - - - - - - - - - - + - - - - - - - - + | Name        | Gender | + - - - - - - - - - - - - - + - - - - - - - - + | Xue Baochai | F      | | Xia Yuhe    | F      | + - - - - - - - - - - - - - + - - - - - - - - + mysql> select Name,Age,Gender  from  students where Age >  20  and  Gender  =  'F' ; + - - - - - - - - - - + - - - - - + - - - - - - - - + | Name     | Age | Gender | + - - - - - - - - - - + - - - - - + - - - - - - - - + | Xia Yuhe |   37  | F      | + - - - - - - - - - - + - - - - - + - - - - - - - - +

delete：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> delete  from  students where Name like  'X%' ; Query OK,  2  rows affected ( 0.00  sec) mysql> select  *  from  students; + - - - - - + - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - + | SID | Name     | Age | Gender | Tutor       | ClassID | + - - - - - + - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - + |    1  | Guo Jing |   27  | M      | Song Jiang  |        2  | |    2  | Yang Guo |   28  | M      | Hu Sanniang |        3  | |    3  | Guo Polu |   21  | M      | Jia Baoyu   |        3  | |    6  | Wu Yong  |   51  | M      | Lin Daiyu   |        1  | |    7  | tom      |   11  | M      | jerry       |        1  | |    8  | tomy     |   13  | M      | NULL        |        4  | + - - - - - + - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - + 6  rows  in  set  ( 0.00  sec)

update：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> update students  set  Age  =  40  where Name  =  'Wu yong' ; Query OK,  1  row affected ( 0.01  sec) Rows matched:  1   Changed:  1   Warnings:  0 mysql> select  *  from  students; + - - - - - + - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - + | SID | Name     | Age | Gender | Tutor       | ClassID | + - - - - - + - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - + |    1  | Guo Jing |   27  | M      | Song Jiang  |        2  | |    2  | Yang Guo |   28  | M      | Hu Sanniang |        3  | |    3  | Guo Polu |   21  | M      | Jia Baoyu   |        3  | |    6  | Wu Yong  |   40  | M      | Lin Daiyu   |        1  | |    7  | tom      |   11  | M      | jerry       |        1  | |    8  | tomy     |   13  | M      | NULL        |        4  | |    9  | Tom      |   24  | M      | NULL        |    NULL | |   10  | Jerry    |   23  | F      | NULL        |    NULL | + - - - - - + - - - - - - - - - - + - - - - - + - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - - - +

几个简单show命令的用法

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> show create table stu1\G; #查看创建表所使用的命令。 #\G为客户端命令；可以使用\?来查看。 * * * * * * * * * * * * * * * * * * * * * * * * * * *  1.  row  * * * * * * * * * * * * * * * * * * * * * * * * * * *         Table: stu1 Create Table: CREATE TABLE `stu1` (    ` Id `  int ( 10 ) unsigned NOT NULL AUTO_INCREMENT,    `Name` char( 30 ) NOT NULL,    `Age` tinyint( 3 ) unsigned NOT NULL,    `Gender` enum( 'F' , 'M' ) NOT NULL DEFAULT  'M' ,    UNIQUE KEY ` Id ` (` Id `) ) ENGINE = MyISAM AUTO_INCREMENT = 9  DEFAULT CHARSET = latin1 1  row  in  set  ( 0.00  sec) ERROR: No query specified mysql>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

mysql> show engines\G; #查看当前数据库支持的存储引擎 * * * * * * * * * * * * * * * * * * * * * * * * * * *  1.  row  * * * * * * * * * * * * * * * * * * * * * * * * * * *        Engine: MRG_MYISAM       Support: YES         #是否支持       Comment: Collection of identical MyISAM tables Transactions: NO          #是否支持事务            XA: NO          #是否支持分布式事务    Savepoints: NO          #是否支持保存点 * * * * * * * * * * * * * * * * * * * * * * * * * * *  2.  row  * * * * * * * * * * * * * * * * * * * * * * * * * * *        Engine: CSV       Support: YES       Comment: CSV storage engine Transactions: NO            XA: NO    Savepoints: NO * * * * * * * * * * * * * * * * * * * * * * * * * * *  3.  row  * * * * * * * * * * * * * * * * * * * * * * * * * * *        Engine: MyISAM       Support: DEFAULT       Comment: Default engine as of MySQL  3.23  with great performance Transactions: NO            XA: NO    Savepoints: NO * * * * * * * * * * * * * * * * * * * * * * * * * * *  4.  row  * * * * * * * * * * * * * * * * * * * * * * * * * * *        Engine: InnoDB       Support: YES       Comment: Supports transactions, row - level locking,  and  foreign keys Transactions: YES            XA: YES    Savepoints: YES * * * * * * * * * * * * * * * * * * * * * * * * * * *  5.  row  * * * * * * * * * * * * * * * * * * * * * * * * * * *        Engine: MEMORY       Support: YES       Comment:  Hash  based, stored  in  memory, useful  for  temporary tables Transactions: NO            XA: NO    Savepoints: NO 5  rows  in  set  ( 0.00  sec) ERROR: No query specified mysql>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

mysql> show character  set ; #显示字符集 + - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - + | Charset  | Description                 | Default collation   | Maxlen | + - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - + | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |       2  | | dec8     | DEC West European           | dec8_swedish_ci     |       1  | | cp850    | DOS West European           | cp850_general_ci    |       1  | | hp8      | HP West European            | hp8_english_ci      |       1  | | koi8r    | KOI8 - R Relcom Russian       | koi8r_general_ci    |       1  | | latin1   | cp1252 West European        | latin1_swedish_ci   |       1  | | latin2   | ISO  8859 - 2  Central European | latin2_general_ci   |       1  | | swe7     |  7bit  Swedish                | swe7_swedish_ci     |       1  | | ascii    | US ASCII                    | ascii_general_ci    |       1  |                                                                                                                                                                                                                                                                                                                                                                                         mysql> show collation; #排序规则 + - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - + | Collation                | Charset  |  Id   | Default | Compiled | Sortlen | + - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - - - - + - - - - - - - - - + | big5_chinese_ci          | big5     |    1  | Yes     | Yes      |        1  | | big5_bin                 | big5     |   84  |         | Yes      |        1  | | dec8_swedish_ci          | dec8     |    3  | Yes     | Yes      |        1  | | dec8_bin                 | dec8     |   69  |         | Yes      |        1  | | cp850_general_ci         | cp850    |    4  | Yes     | Yes      |        1  | | cp850_bin                | cp850    |   80  |         | Yes      |        1  | | hp8_english_ci           | hp8      |    6  | Yes     | Yes      |        1  | | hp8_bin                  | hp8      |   72  |         | Yes      |        1  | | koi8r_general_ci         | koi8r    |    7  | Yes     | Yes      |        1  |                                                                                                                                                                                                                                                                                                                                                                                         #查看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 ....

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

如有错误；恳请纠正。

本文出自 “Soul” 博客，请务必保留此出处http://chenpipi.blog.51cto.com/8563610/1392761