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基础特性

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:

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>

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里面帮助获取：帮助信息显示的也很详细

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：

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：

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：

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命令的用法

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>

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>

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