参考资料

慕课网——与MySQL的零距离接触

前言

本人菜鸟，入IT只为当鼓励师。本编文章主要对 MySQL的一些基础知识进行总结。

一、常用服务指令

1. 启动和停止MySQL服务

• 启动：net start [mysql服务名]
• 停止：net stop [mysql服务名]
  
  net start / net stop
  
  
  Windows服务

2. MySQL的登录与退出

• 登录：mysql -uroot -p -P3306 -h127.0.0.1（端口：3306，IP：127.0.0.1）
  
  登录与退出

3. 创建新用户

脚本：CREATE USER 'username'@'host' [IDENTIFIED BY 'PASSWORD']; 其中密码是可选项。
例子：CREATE USER 'john'@'192.168.189.71' IDENTIFIED BY "123456";这样就创建了一个用户（用户名：john，密码：123456，该用户只能在IP地址为192.168.189.71的内网机子上访问操作数据库。若host为localhost，则该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将localhost改为%，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录）。

4. 修改MySQL提示符

用prompt指令修改提示符

可选提示符参数

• 示例：（账号：root ，密码：root ）
  C:\Users\JIN>mysql -uroot -proot --prompt \h
  localhostprompt \h>
  PROMPT set to ‘\h>’
  localhost>prompt \u#\h \d \D >
  PROMPT set to ‘\u#\h \d \D > ‘
  root#localhost (none) Thu Dec 08 21:15:08 2016 >

5. 用SELECT显示当前信息

• 语句规范：
  **1. **关键字与函数名称全部大写。
  **2. **数据库名称、表名称、字段名称全部小写。
  **3. **SQL语句必须以分号结尾。

• SELECT指令：
  SELECT VERSION(); ：显示当前服务器版本。
  SELECT NOW(); ：显示当前日期时间。
  SELECT USER(); ：显示当前用户。
  

  SELECT指令示例

二、数据库

1. 数据库的操作

1-1. 创建数据库

CREATE {DATABASE| SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name(编码方式)

• 基本指令CREATE DATABASE db_name;：
  显示Query OK后，可用SHOW DATABASES;指令显示已成功创建出名为test的数据库：
  
  CREATE DATABASE test;
• 可选参数IF NOT EXISTS：
  若再次尝试创建已存在的数据库，系统会报错(error)。若加了可选参数IF NOT EXISTS，系统不会报错，取而代之的是把原本的错误放到警告(warning)中，可用SHOW WARNINGS;查看所有的警告：
  
  IF NOT EXISTS
• 可选指令CHARACTER SET [=] charset_name(编码方式)：
  显示Query OK后，可用SHOW CREATE DATABASE db_name;查看某个库使用的编码方式：
  
  CHARACTER SET gbk;

1-2. 打开数据库

USE db_name(数据库名称);

• 显示Database changed即成功打开对应数据库，可用SELECT DATABASE();查看当前打开的数据库：
  
  USE test;

1-3. 修改数据库

ALTER {DATABASE | SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name;

• 可用该指令修改数据库的编码方式：

  
  
  ALTER DATABASE test2 CHARACTER SET utf8;

1-4. 删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

• Query OK即为删除成功，可用SHOW DATABASES;再次查看数据库，发现里面没有test2（IF EXISTS作用与创建数据库指令中的IF NOT EXISTS相同）：
  
  DROP DATABASE IF EXISTS test2;

2. 查看数据库的相关信息

2-1. 查看数据库列表

SHOW {DATABASES | SCHEMAS};

• 注：下面显示已存在的6个数据库是软件自带的：
  
  SHOW DATABASES; / SHOW SCHEMAS;

2-2. 查看某个库使用的编码方式

SHOW CREATE DATABASE db_name;

SHOW CREATE DATABASE test;

2-3. 查看当前打开的数据库

SELECT DATABASE();

• 默认情况下，打开的数据库为空(NULL)：

  
  
  SELECT DATABASE(); （默认为NULL）

2-4. 查看数据库引擎

SHOW ENGINES;

SHOW ENGINES;

3. 查看警告信息

SHOW WARNINGS;

SHOW WARNINGS;

三、数据表

• 数据表(或称表)是数据库最重要的组成部分之一。数据库只是一个框架，数据表才是其实质内容。
• 一个（数据）表由行（记录）和列（字段、域）构成，组成一个二维关系表，其中列名也称为字段名。而一个真正的数据库由几个（或更多）表、视图及相关的文件等组成一个统一的、相关联的系统。
• 列的属性：
  1. 列名：表的列名在同一个表中具有惟一性。
  2. 数据类型：指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。同一列的数据属于同一种数据类型。
  3. NULL、NOT NULL、AUTO_INCREMENT、约束：NULL属性：允许在插入数据时省略该列的值； NOT NULL属性：不允许在没有指定列缺省值的情况下插入省略该列值的数据行；AUTO_INCREMENT：每插入一个新行时，被设值该属性的对应列的值（相对于上一行）递增加1。只有设置了主键约束的列才能使用该属性，一般用于id等序号。详细见第四点； 约束：约束一共有五种，分别拥有不同的目的和功能，详细见第四点。

1. 数据类型

CREATE TABLE [IF NOT EXISTS] table_name(
column_name type_name,
...
);

• type_name即为数据类型。
• 数据类型：指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。
  
  整型
  
  
  浮点型
  
  
  日期时间型
  
  
  字符型

2. 数据表的操作

2-1. 创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(
column_name type_name,
...
);

• 该命令用来创建数据表的列：

  
  
  CREATE TABLE tb( column_name type_name,... );

• 若当前打开的数据库为空(NULL)，创建数据表会报错：

  
  
  ERROR：no database selected

2-2. 修改数据表

2-2-1.添加列（字段）

2-3. 插入行（记录）

 INSERT [INTO] tbl_name[(col_name, ...)] VALUES(val, ...);

• 全部列：

  
  
  INSERT tb VALUES();

• 可选列：

  
  
  INSERT tb() VALUES();

3. 查看数据表的相关信息

3-1. 查看数据表列表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

• 若不加FROM db_name，要确保已经有数据库被打开，否则报错：
  
  ERROR：no database selected
• 正确使用指令：
  打开test数据库(USE test;)后再查看。
  
  SHOW TABLES;
  
  或直接加FROM db_name，此时与当前打开的数据库无关。
  
  SHOW TABLES FROM test;

3-2. 查看数据表的索引

SHOW INDEXES FROM tbl_name [\G]

主键约束自带的索引

外键约束的索引

• 以表格的形式显示：

  
  
  SHOW INDEXES FROM provinces;

• 以列表的形式显示

  
  
  SHOW INDEXES FROM provinces \G;
  
  
  SHOW INDEXES FROM users \G;

3-3. 查看列（字段）的结构和属性

SHOW COLUMNS FROM db_name;

• 使用该命令之前要确保已经有数据库被打开，否则报错：

  
  
  ERROR：no database selected

• 正确使用该命令：

  
  
  SHOW COLUMNS FROM tb

3-4. 查看列（字段）的代码与引擎

SHOW CREATE TABLE tbl_name;

SHOW CREATE TABLE t_group;

3-5. 查找行（记录）

SELECT expr,... FROM tbl_name;

SELECT * FROM tb;

4. 列的其他属性

表的列名在同一个表中具有惟一性，同一列的数据属于同一种数据类型。除了用列名和数据类型来指定列的属性外，还可以定义其它属性：

4-1. 自动编号

CREATE TABLE [IF NOT EXISTS] table_name(
column_name type_name AUTO_INCREMENT PRIMARY KEY,
...
);

• AUTO_INCREMENT，把字段设置为自动编号。
• 必须与主键(PRIMARY KEY)组合使用：
  
  非主键用AUTO_INCREMENT会报错

• 默认情况下，起始值为1，每次增量为1：

  
  
  起始值为1，增量为1
• 不管INSERT指令是否成功，被设置成AUTO_INCREMENT的列(id)都会自增：
  
  id=2时，INSERT失败而被跳过

4-2. 具体的五种约束

• 约束保证数据的完整性和一致性。
• 约束分为表级约束和列级约束。
• 约束类型包括：
  NOT NULL（非空约束）
  PRIMARY KEY（主键约束）
  UNIQUE KEY（唯一约束）
  DEFAULT (value)（默认约束）
  FOREIGN KEY（外键约束）

4-2-1. 空值和非空约束

CREATE TABLE [IF NOT EXISTS] table_name(
column_name type_name NULL(默认),
column_name type_name NOT NULL,
...
);

• NULL，字段值可以为空（默认）。
  NOT NULL，字段值禁止为空。
• 若该字段时属性设置为NOT NULL，则插入行（记录）时该字段不能省略，否则报错：
  
  NULL / NOT NULL

4-2-2. 主键约束

CREATE TABLE [IF NOT EXISTS] table_name(
column_name type_name PRIMARY KEY,
...
);

• PRIMARY KEY，给字段设置主键约束。

• 每张数据表只能存在一个主键：

  
  
  Error：Multiple primary key defined

• 主键保证记录的唯一性：

  
  
  ERROR：Duplicate entry 'xxx' for key 'xxx'
• 主键自动被设置为NOT NULL：
  
  ERROR：Field 'xxx' doesn't have a default value

• 主键是默认自带索引的：

  
  
  CREATE TABLE provinces
  
  
  SHOW INDEXES FROM provinces

4-2-3. 唯一约束

CREATE TABLE [IF NOT EXISTS] table_name(
column_name type_name UNIQUE KEY,
...
);

• UNIQUE KEY，给字段设置唯一约束：
  
  UNIQUE KEY

• 唯一约束保证记录的唯一性：

  
  
  ERROR：Duplicate entry 'xxx' for key 'xxx'

• 每张数据表可以存在多个唯一约束：

  
  
  Multiple UNIQUE KEY
• 唯一约束默认被设置为NULL：
  
  唯一约束默认为NULL

4-2-4. 默认约束

CREATE TABLE [IF NOT EXISTS] table_name(
column_name type_name DEFAULT value,
...
);

• DEFAULT (value)，默认值。

• 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值：

  
  
  DEFAULT 'unknown'

4-2-5. 外键约束

CREATE TABLE [IF NOT EXISTS] table_name(
...,
FOREIGN KEY (column_name) REFERENCES tbl_name(column_name) on [DELETE / UPDATE] [CASCADE / SET NULL / ON ACTION],
...
);

• FOREIGN KEY

目的：

• 保持数据一致性和完整性。
• 实现一对一或一对多的关系。

要求：

• 父表和子表必须使用相同的存储引擎，且禁止使用临时表。
• 数据表的存储引擎只能为InnoDB：
  可以查看或编辑数据表的默认存储引擎（编辑后要重启MySQL服务）——打开MySQL安装目录下的MySQL Server 5.7文件夹并用编辑器打开my.ini文件，找到下面这段代码：
  
  default-storage-engine=INNODB
• 外键列和参照列必须具有相似的数据类型。其中数字长度或是否有符号位必须相同；而字符的长度则可以不同：
  
  ERROR：Cannot add foreign key constraint
• **1. **外键列是不可以以一个没有索引的列作为参照列的，故参照列必须创建索引（该示例为主键(PRIMARY KEY)的索引）：
  
  SHOW INDEXES FROM provinces \G;
  
  **2. **外键列不存在索引的话，MySQL将自动创建索引：
  
  SHOW INDEXES FROM users \G;
  
  
  SHOW CREATE TABLE users

参照操作：

• CASCADE：从表中删除或更新且自动删除或更新子表中匹配的行。
• SET NULL：从父表中删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。
• RESTRICT：拒绝对父表的删除或更新操作。
• NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。

-- 父表
create table t_group (   
id int not null,  --参照列
name varchar(30),   
primary key (id)   
);  
insert into t_group values (1, 'Group1');   
insert into t_group values (2, 'Group2');  

****************************** 级联(cascade)方式 ******************************
--子表
create table t_user (   
id int not null,   
name varchar(30),   
groupid int,  --外键列
primary key (id),   
foreign key (groupid) references t_group(id) on delete cascade on update cascade  
);  
--参照完整性测试
insert into t_user values (1, 'qianxin', 1); --可以插入
insert into t_user values (2, 'yiyu', 2);    --可以插入
insert into t_user values (3, 'dai', 3);     --错误，用户组3不存在，与参照完整性约束不符
--约束方式测试
insert into t_user values (1, 'qianxin', 1);   
insert into t_user values (2, 'yiyu', 2);   
insert into t_user values (3, 'dai', 2);   
delete from t_group where id=2;      --导致t_user中的2、3记录级联删除   
update t_group set id=2 where id=1;  --导致t_user中的1记录的groupid级联修改为2  

****************************** 置空(set null)方式 ******************************
create table t_user (   
id int not null,   
name varchar(30),   
groupid int,   
primary key (id),   
foreign key (groupid) references t_group(id) on delete set null on update set null  
);   
--参照完整性测试   
insert into t_user values (1, 'qianxin', 1); --可以插入   
insert into t_user values (2, 'yiyu', 2);    --可以插入   
insert into t_user values (3, 'dai', 3);     --错误，用户组3不存在，与参照完整性约束不符   
--约束方式测试   
insert into t_user values (1, 'qianxin', 1);   
insert into t_user values (2, 'yiyu', 2);   
insert into t_user values (3, 'dai', 2);   
delete from t_group where id=2;      --导致t_user中的2、3记录的groupid被设置为NULL   
update t_group set id=2 where id=1;  --导致t_user中的1记录的groupid被设置为NULL  


*********************** 禁止(no action / restrict)方式  ***********************
create table t_user (   
id int not null,   
name varchar(30),   
groupid int,   
primary key (id),   
foreign key (groupid) references t_group(id) on delete no action on update no action  
);     
--参照完整性测试   
insert into t_user values (1, 'qianxin', 1); --可以插入   
insert into t_user values (2, 'yiyu', 2);    --可以插入   
insert into t_user values (3, 'dai', 3);     --错误，用户组3不存在，与参照完整性约束不符   
--约束方式测试   
insert into t_user values (1, 'qianxin', 1);   
insert into t_user values (2, 'yiyu', 2);   
insert into t_user values (3, 'dai', 2);   
delete from t_group where id=2;      --错误，从表中有相关引用，因此主表中无法删除   
update t_group set id=2 where id=1;  --错误，从表中有相关引用，因此主表中无法修改  

实战经验：

• 在实际开发中其实很少用到物理的外键约束（即如上设置外键列），很多使用的是逻辑的外键约束，物理的外键约束只有InnoDB这种引擎才能支持，所以一般不定义物理外键。
• 逻辑外键：定义两张表结构的时候，按照存在着某种结构的方式去定义，但是不使用FOREIGN KEY 这个关键词。

4-2. 表级约束与列级约束

• 列级约束：对一个数据列建立的约束。
  表级约束：对多个数据列建立的约束。
• 列级约束 即可在列定义时声明，也可在列定义后声明。
  表级约束 只能在列定义后声明。
• 在实际开发中，列级约束用得比较多，表级约束很少用。
• 在所有的约束中，并不是说每种约束都存在着表级或列级约束。其中：
  NOT NULL 非空约束，DEFAULT约束：只有列级约束而不存在表级约束。
  PRIMARY KEY主键约束，UNIQUE KEY唯一约束，FOREIGN KEY外键约束：都可以存在表级和列级约束。