MySQL学习笔记

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

MySQL 学习实践笔记（四）
MySQL 学习实践笔记（三）
MySQL 学习实践笔记（二）
MySQL 学习实践笔记（一）
MYSQL语句执行大体流程
MySQL学习笔记---基础概念和一些基础SQL语句（一）
MySQL学习笔记一之MySQL语法
mysql学习笔记(更新中).md
Mysql&Jdbc学习笔记
MySql高级部分笔记转载

参考资料

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

MySQL 学习实践笔记（四）
MySQL 学习实践系列 MySQL 学习实践笔记（一） MySQL 学习实践笔记（二） MySQL 学习实践笔记...
MySQL 学习实践笔记（三）
MySQL 学习实践系列 MySQL 学习实践笔记（一） MySQL 学习实践笔记（二） MySQL 学习实践笔记...
MySQL 学习实践笔记（二）
MySQL 学习实践系列 MySQL 学习实践笔记（一） MySQL 学习实践笔记（二） MySQL 学习实践笔记...
MySQL 学习实践笔记（一）
MySQL 学习实践系列 MySQL 学习实践笔记（一） MySQL 学习实践笔记（二） MySQL 学习实践笔记...
MYSQL语句执行大体流程
学习笔记是学习了极客时间 - 《MySQL实战45讲》整理的笔记。 MYSQL简单架构 MySQL 可以分为 ...
MySQL学习笔记---基础概念和一些基础SQL语句（一）
MySQL学习笔记---基础概念和一些基础SQL语句（一）作者：zhangjunqiang MySQL学习笔记，...
MySQL学习笔记一之MySQL语法
title: MySQL学习笔记一之MySQL语法tags: MySQL 数据库categories: MySQL...
mysql学习笔记(更新中).md
mysql初级学习笔记.md 标签（空格分隔）： mysql mysql初级 mysql介绍 DB：databas...
Mysql&Jdbc学习笔记
Mysql学习笔记（九） Mysql与JDBC Jdbc: Java database connectivity ...
MySql高级部分笔记转载
尚硅谷MySQL高级学习笔记