MySQL学习笔记

作者: BeYanJin | 来源:发表于2016-12-09 10:48 被阅读0次

参考资料

慕课网——与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外键约束：都可以存在表级和列级约束。