MySQL基础-摩杜云开发者社区

[TOC]

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

在本文中，我记录了自己学习的 MySQL 基础知识，以及自己实际应用的过程。

1. 概述

1.1 什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。

关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

1.2 RDBMS 术语

数据库: 数据库是一些关联表的集合，一个关系型数据库由一个或数个表格组成
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
**行：**一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
**外键：**外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
**索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
表头(header): 每一列的名称;
列(col): 具有相同数据类型的数据的集合;
行(row): 每一行用来描述某条记录的具体信息;
值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
键(key): 键的值在当前列中具有唯一性。

2.MySQL终端运行

2.1 MySQL连接

在下载好MySQL后，我们就可以开始我们SQL语句的学习了，首先我们先连接MySQL，win + r 输入cmd，进入Windows命令行界面，输入 mysql -u root -p ，敲击回车，输入密码，进入MySQL终端，如下：

mysql -u root -p // -u为用户user，后接用户名，root为管理员权限，-p为密码password

之后你就可以在此界面输入任何SQL语句了。

2.2SQL语句

新学习SQL语句的小伙伴建议多在终端敲一敲SQL语句，熟悉之后，为了高效，推荐使用[Navicat](Navicat GUI | DB Admin Tool for MySQL, Redis, PostgreSQL, MongoDB, MariaDB, SQL Server, Oracle & SQLite client)等图形化界面软件，就不需要敲一些简单的SQL语句了。

显示数据库

show databases;

information_schema 信息图式，存储服务器管理数据库的信息
performance_schema 性能图式
sys 系统文件

数据库相关操作

//创建数据库
create database name;	
create database `name`; // 推荐使用这种，更加规范，加上反引号
create database if not exists `name`; // 不知道数据库是否存在的情况下使用
// 删除数据库
drop database name;	
drop database if exists name; // 如果存在，则删除数据库
// 查看创建数据库的SQL
show create database name;	// 查看数据库的创建内容
// 字符编码
create database if not exists `name` charset=UTF8; // 创建数据库时指定字符集编码
alter database name charset=gbk; // 修改字符编码
// 选择数据库
use name; // 后面接数据库名就行了，后续的操作都会在该数据库中执行

数据类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

表的相关操作

创建MySQL数据表需要的信息：表名、表字段名、定义每个表字段

CREATE TABLE table_name (column_name column_type);

show tables; //查看该数据库中的表
show create table name;	// 显示出表的sql语句
desc name; // 查看表的结构
// 删除表
drop table if exists name;			// 删除单表
drop table if exists name1, name;	 // 删除多表
// 修改表
alter table name add 字段名 字段类型;	// 添加一个新的字段
alter table name add 字段名 字段类型 after 字段名;	// 在某个字段后添加一个新的字段
alter table name add 字段名 字段类型 first;		// 在第一行添加
alter table name drop 字段; // 删除字段
alter table name change 字段名 新字段名 字段类型; // 修改字段名和类型
alter table name modify 字段名 新字段类型;		// 修改字段类型
alter table name rename to name1;	// 修改表名

示例：

mysql> create table student(
    -> id int,
    -> name varchar(30),
    -> age int,
    -> );
Query OK, 0 rows affected (0.04 sec)

标准化：

mysql> create table if not exists teacher(
    -> id int auto_increment primary key comment'主键id',
    -> name varchar(30) not null comment '老师的名字',
    -> phone varchar(20) comment '电话号码',
    -> address varchar(100) default '暂时未知' comment '住址'
    -> )engine=innodb;
Query OK, 0 rows affected (0.03 sec)

auto_increment : 对字段进行自动增长
primary key : 主键，是关系型数据库连接桥梁，必须填写不能空着
comment : 注释
not null : 不能为空，必须填写
default : 如果为空那么表中这个数据默认为’ '中的内容，默认值
engine = innodb : 表示数据库引擎

数据的相关操作

插入数据

insert into 表名(可以省略) values (不可省略的内容);

// 插入数据
insert into student (id, name, phone, address) values(1,'Lowell', '2345235', 'Ganzhou'); // 插入一条数据
insert into teacher values(NULL, 'TOM_1', NULL, default),(NULL, 'Jerry', NULL, default); // 插入多条数据

可以插入NULL值和default值（也就是在定义表时给出的默认值）

删除数据

delete from 表名 where 选定的字段名称 = 你要删除的字段相应信息
delete from student where id = 2;	// 删除student表中 id = 2 的数据
delete from student;	// 整表删除
truncate table student;	// 清空表

delete 和 truncate 区别

delete 遍历整个表然后一个一个删除，低效率的方法

truncate 销毁一个表，然后再克隆个完全一样的空表，它不需要遍历删除，这样就比较高效

与自增的问题有关，当你使用delete清空表的时候，自增不会再次从1开始，而是你上次删掉的最后一个数据开始，而使用truncate就不会出现这样一个情况，它是属于报废了原来的表，新克隆了个一样的表，可以看作是原来的空表

修改数据

update 表名 set 要修改的字段名 = 要修改成的数据 where 定位字段名 = 相应数据	// 单条修改
update student set name='Lowell' where id=1;
update name set field1=new-value1, field2=new-value2 [where Clause]	// 多条更新，使用逗号`,`分割
update teacher set name='Frank' where id=1 or id=2;

where可以是任何一个字段的数据，删除数据时用where要慎重考虑选择定位字段，选择唯一确定的字段，消除不必要的麻烦

查询数据

select column_name,column_name from table_name [where Clause] [limit N][offset M]

查询语句中你可以使用一个或者多个表，表之间使用逗号 , 分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
可以使用 WHERE 语句来包含任何条件。
可以使用 LIMIT 属性来设定返回的记录数。
可以通过 OFFSET 指定 SELECT 语句开始查询的数据偏移量。默认情况下偏移量为0。

SQL语句区分

DDL（data definition language 数据库定义语言）：create、alter 、drop 、show
DML（data manipiation language 数据操纵语言）： insert 、update 、delete 、select
DCL (data control language 数据控制语言 )：grant 、revoke

字符编码问题

//  查询字符集编码
mysql> show variables like 'character_set_%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value
                    |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk
                    |
| character_set_connection | gbk
                    |
| character_set_database   | gbk
                    |
| character_set_filesystem | binary
                    |
| character_set_results    | gbk
                    |
| character_set_server     | latin1
                    |
| character_set_system     | utf8
                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

// 修改字符集编码
mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)

3.列属性问题

列属性要根据业务的要求来对数据的一些控制，例如是否能为空，是否是唯一的，等各种操作，就是我们每次desc表的时候的表头上的内容就是与类属性有关的东西：Type Null Key Dafult

3.1Primary key主键作用

主键用来区分数据，用来联系各表，不能为空且在表中的数据是唯一的。

不能为空保证了数据的完整性，在表中唯一使得在数据查询中变得更加方便，加快了表的查询速度。

主键的选择很重要，选择有把握且绝对不会更改的数据，最好是数字，万一所给你的数据没有这种类型的数据的时候，我们需要自己创建‘id’

在非自增字段的主键，Default必须赋值，不能为NULL

3.2 主键相关操作

create table t_8(
    id int(20) primary key,	// 定义主键
    name varchar(30)
    );

alter table tablename add primary key (字段名);   // 添加主键
alter table tablename drop primary key；		//删除主键

可以添加组合键（复合主键），但扩展性不行，一张表里可以有多个组合键，但是可能在非特殊的情况下实际的意义不大,添加复合主键要先把原主键删除

alter table tablename add primary key （字段，字段...）;	// 添加复合主键

注意：组合键并不是一个表中有多个主键，而是这些字段组合成复合主键，所以主键在表中只能有一个

3.3 唯一键 `uinique`

唯一键限定范围是在一张表中，它不会用作来关联其他的数据，它也可以为空，在这张表中一定是唯一的，用来保证在这个表中这个数据不重复。

// 添加唯一键，两种方法，
create table t(
    id int primary key,
    phone varchar(20) unique	// 唯一键
    );
    
alter table tablename add unique (字段，字段...);

alter table 表名 drop index 字段名; // 删除唯一键，字段名不使用括号

3.4 主键和唯一键区别

主键 `primary key`	唯一键 `unique`
可以用来连接各个表	不使用来链接各个表
不可以为空	可以为空
如果没有设置唯一键则自增字段必须是主键	自增字段不为主键的时候要将这个字段设置成唯一键
一张表只能有一个或者一个组合	可以有多个且不需要组合

3.5 SQL注释

普通注释：命令单行注释与多行注释

mysql> create table t_12 (
    -> id int(20)  primary key,	# this is primary key!
    -> phone varchar(20) 	-- this is phone
    -> /*
   /*> abcdefg
   /*> hijklmn
   /*> */
    -> );
Query OK, 0 rows affected (0.03 sec)

SQL内注释

mysql> create table staff(
    -> id int auto_increment primary key comment'主键id',
    -> name varchar(30) not null,
    -> age int comment'年龄',
    ->  )engine=innodb;

3.6 外键

外键创建前提：所参照的字段（外键字段）必须为参照表的主键，外键字段不能为该表的主键；

// 创建外键，两种方法
mysql> create table eatery(
    -> id int primary key,
    -> money decimal(10,4),
    -> stuId int(4),
    -> foreign key (stuId) references stu(stuId)); 

alter table tablename add foreign key (数据) references 连接的表名(数据);

如下：

mysql> create table stu(
    -> stuId int (4) primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table eatery(
    -> id int(20) primary key,
    -> money decimal(10,4),
    -> stuId int(4),
    -> foreign key(stuId) references stu(stuId) on delete set null on update cascade
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table eatery;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                   |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| eatery | CREATE TABLE `eatery` (
  `id` int(20) NOT NULL,
  `money` decimal(10,4) DEFAULT NULL,
  `stuId` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stuId` (`stuId`),
  CONSTRAINT `eatery_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `stu` (`stuId`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.7 外键的三种操作

严格性操作，也就是增删改查表的规范
置空操作：链接外键的表中，原表删除的数据会成为NULL
级联操作：链接外键的表中，原表删除的数据会全部删除

注意：留给外键进行删除数据的时候使用置空，更新使用级联

示例

创建eatery表，stuId为外键，设置级联和置空操作

foreign key(数据) references 表名(数据) on delete set null on update cascade

mysql> create table stu(
    -> stuId int (4) primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table eatery(
    -> id int(20) primary key,
    -> money decimal(10,4),
    -> stuId int(4),
    -> foreign key(stuId) references stu(stuId) on delete set null on update cascade
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table eatery;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table



                                   |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| eatery | CREATE TABLE `eatery` (
  `id` int(20) NOT NULL,
  `money` decimal(10,4) DEFAULT NULL,
  `stuId` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stuId` (`stuId`),
  CONSTRAINT `eatery_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `stu` (`stuId`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

插入几条数据：

mysql> insert into stu values(1,'frank');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu values(2,'jerry');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu;
+-------+-------+
| stuId | name  |
+-------+-------+
|     1 | frank |
|     2 | jerry |
+-------+-------+
2 rows in set (0.00 sec)
mysql> insert into eatery values(1, 20.5, 2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from eatery;
+----+---------+-------+
| id | money   | stuId |
+----+---------+-------+
|  1 | 20.5000 |     2 |
+----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into eatery values(2, 78.6, 1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into eatery values(3, 99.9, 2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into eatery values(4, 748.4, 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into eatery values(5, 748.4, 2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |     2 |
|  2 |  78.6000 |     1 |
|  3 |  99.9000 |     2 |
|  4 | 748.4000 |     1 |
|  5 | 748.4000 |     2 |
+----+----------+-------+
5 rows in set (0.00 sec)

更新stu表中stuId时，eatery表外键的数据会相应更改，这里是级联操作

mysql> update stu set stuId='4' where name = 'frank';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |     2 |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |     2 |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |     2 |
+----+----------+-------+
5 rows in set (0.00 sec)

删除stu表中stuId时，eatery表外键的数据会变为NULL，这里是置空操作

mysql> delete from stu where stuId='2';
Query OK, 1 row affected (0.01 sec)

mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
+----+----------+-------+
5 rows in set (0.00 sec)

4.单表查询

4.1`select`

可以用作查询，也可以做计算，别名操作使用as 或者省略

MySQL基础_主键

4.2 `from`

操作的内容来自哪张表,返回的为笛卡尔积

mysql> select * from stu, eatery;
+-------+--------+------+----------+-------+
| stuId | name   | id   | money    | stuId |
+-------+--------+------+----------+-------+
|     1 | Lowell |    1 | 100.0000 |     1 |
|     2 | Jerry  |    1 | 100.0000 |     1 |
|     1 | Lowell |    2 | 130.0000 |     2 |
|     2 | Jerry  |    2 | 130.0000 |     2 |
|     1 | Lowell |    2 |  10.7800 |     3 |
|     2 | Jerry  |    2 |  10.7800 |     3 |
+-------+--------+------+----------+-------+

4.3 `dual`

dual 默认的伪表，你可以在没有表的情况下指定一个虚拟的表名

select 2*8 as ret from dual;

4.4 `where`

从 MySQL 表中使用 SQL SELECT 语句来读取数据。如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中，用于筛选数据

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

select * from stu where id = 1;
select * from stu where id = 1 or phone = '123456';
select * from teacher where address in ('ShangHai');	# 用in代替等号的一种
select * from teacher where address not in ('ShangHai'); # 用not in 代替不等号

4.5`between ... and`

与 > and < 不同，between and表示在什么之间，会取等于两个数字之间的值

mysql>  select * from teacher where id >1 and id < 4;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
+----+------+--------+----------+

4.6 `is null`

查询数据是否为空

mysql> select * from teacher where address is null;
+----+------+-------+---------+
| id | name | phone | address |
+----+------+-------+---------+
|  4 | Tom  | NULL  | NULL    |
+----+------+-------+---------+
mysql> select * from teacher where address is not null;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |

4.7聚合函数

MySQL函数有很多，列举一些常用的函数：

sum(expression) 返回指定字段的总和
avg(expression) 返回一个表达式的平均值，expression 是一个字段
count(expression) 返回查询的记录总数，expression 参数是一个字段或者 * 号

count(1)计算一共有多少符合条件的行，1并不是表示第一个字段，而是表示一个固定值
count(*)（是针对全表）将返回表格中所有存在的行的总数包括值为null的行
count(列名)（是针对某一列）将返回表格中某一列除去null以外的所有行的总数

# 计算 OrderDetails 表中字段 Quantity 的总和
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
# 返回 Products 表中Price 字段的平均值
SELECT AVG(Price) AS AveragePrice FROM Products;
# 返回 Products 表中 products 字段总共有多少条记录
SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

4.8`like` 模糊查询

% 表示任意0个或多个字符，_ 表示任意单个字符

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

mysql> SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

4.9`order by` 排序查询

desc表示降序，asc表示升序

mysql> select * from score;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      99 |      99 |   99 |
|    2 |      77 |      94 |   96 |
+------+---------+---------+------+
mysql> select * from score order by chinese desc;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      99 |      99 |   99 |
|    2 |      77 |      94 |   96 |
+------+---------+---------+------+
mysql> select * from score order by chinese asc;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    2 |      77 |      94 |   96 |
|    1 |      99 |      99 |   99 |
+------+---------+---------+------+

4.10 `group by` 分组查询

根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数，查询的字段必须是分组字段和聚合函数

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | signin |
+----+--------+---------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
# 将数据表按名字进行分组，并统计每个人有多少条记录：
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+
3 rows in set (0.01 sec)

with rollup 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | signin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果anull,则选择b；如果bnull,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | signin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+

4.11 `group_concat`

concat()函数将多个字符串连接成一个字符串

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
+----+----------+-------+
5 rows in set (0.00 sec)
mysql> select stuId ,group_concat(money) from eatery group by stuId;
+-------+--------------------------+
| stuId | group_concat(money)      |
+-------+--------------------------+
|  NULL | 20.5000,99.9000,748.4000 |
|     4 | 78.6000,748.4000         |
+-------+--------------------------+

# 使用order by子句对结果中的值进行排序
mysql> select stuId ,group_concat(distinct money order by money desc) from eatery group by stuId;
+-------+--------------------------------------------------+
| stuId | group_concat(distinct money order by money desc) |
+-------+--------------------------------------------------+
|  NULL | 748.4000,99.9000,20.5000                         |
|     4 | 748.4000,78.6000                                 |
+-------+--------------------------------------------------+

4.12 `having`

在 SQL 中增加having子句原因是where关键字无法与合计函数一起使用，having子句可以让我们筛选分组后的各组数据。

mysql> select avg(money) as '$' ,stuId as 'stuId' from eatery group by stuId having $>400;
+--------------+-------+
| $            | stuId |
+--------------+-------+
| 413.50000000 |     4 |
+--------------+-------+

4.13 `limit`

limit 子句被用于强制 select 语句返回指定的记录数，初始记录行的偏移量是0而不是1

mysql> select * from eatery limit 0,2;
+----+---------+-------+
| id | money   | stuId |
+----+---------+-------+
|  1 | 20.5000 |  NULL |
|  2 | 78.6000 |     4 |
+----+---------+-------+
mysql> select * from eatery limit 3;
+----+---------+-------+
| id | money   | stuId |
+----+---------+-------+
|  1 | 20.5000 |  NULL |
|  2 | 78.6000 |     4 |
|  3 | 99.9000 |  NULL |
+----+---------+-------+

4.14 `distinct`

去除查询结果中的重复值

mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
+----+----------+-------+
5 rows in set (0.00 sec)

mysql> select distinct money from eatery;
+----------+
| money    |
+----------+
|  20.5000 |
|  78.6000 |
|  99.9000 |
| 748.4000 |
+----------+
4 rows in set (0.00 sec)

5. 多表查询

5.1`union`

UNION关键字用于连接两个以上的SELECT语句的结果组合到一个结果集合中

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

mysql> select stuId from eatery union all select stuId from stu;
+-------+
| stuId |
+-------+
|     1 |
|     2 |
|     2 |
|     3 |
|     3 |
|     1 |
|     2 |
+-------+
mysql> select stuId from eatery union select stuId from stu;
+-------+
| stuId |
+-------+
|     1 |
|     2 |
|     3 |
+-------+

5.2 `join`

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
**LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

mysql> select * from stu;
+-------+--------+
| stuId | name   |
+-------+--------+
|     1 | Lowell |
|     2 | Jerry  |
+-------+--------+
mysql> select * from eatery;
+------+----------+-------+
| id   | money    | stuId |
+------+----------+-------+
|    1 | 100.0000 |     1 |
|    2 | 130.0000 |     2 |
|    2 |  10.7800 |     3 |
|    2 | 233.4000 |     2 |
|    3 | 100.0000 |     3 |
+------+----------+-------+
# 内连接
mysql> select stu.stuId from stu left join eatery on stu.stuId = eatery.stuId;
+-------+
| stuId |
+-------+
|     1 |
|     2 |
|     2 |
+-------+
# 左连接，以左边表stu为基准
mysql> select stu.stuId from stu left join eatery on stu.stuId = eatery.stuId;
+-------+
| stuId |
+-------+
|     1 |
|     2 |
|     2 |
+-------+
# 右连接，以右边表eatery为基准
mysql> select stu.stuId from stu right join eatery on stu.stuId = eatery.stuId;
+-------+
| stuId |
+-------+
|     1 |
|     2 |
|     2 |
|  NULL |
|  NULL |
+-------+

cross join 交叉连接 :返回笛卡尔积，使用时须谨慎，可能产生非常大的表

mysql> select * from stu cross join eatery;
+-------+--------+------+----------+-------+
| stuId | name   | id   | money    | stuId |
+-------+--------+------+----------+-------+
|     1 | Lowell |    1 | 100.0000 |     1 |
|     2 | Jerry  |    1 | 100.0000 |     1 |
|     1 | Lowell |    2 | 130.0000 |     2 |
|     2 | Jerry  |    2 | 130.0000 |     2 |
|     1 | Lowell |    2 |  10.7800 |     3 |
|     2 | Jerry  |    2 |  10.7800 |     3 |
|     1 | Lowell |    2 | 233.4000 |     2 |
|     2 | Jerry  |    2 | 233.4000 |     2 |
|     1 | Lowell |    3 | 100.0000 |     3 |
|     2 | Jerry  |    3 | 100.0000 |     3 |
+-------+--------+------+----------+-------+

n**atural join 自然连接** ：在两张表中寻找那些数据类型和列名都相同的字段，自动地将他们连接起来，并返回所有符合条件按的结果

mysql> select * from stu natural join eatery;
+-------+--------+------+----------+
| stuId | name   | id   | money    |
+-------+--------+------+----------+
|     1 | Lowell |    1 | 100.0000 |
|     2 | Jerry  |    2 | 130.0000 |
|     2 | Jerry  |    2 | 233.4000 |
+-------+--------+------+----------+

5.3 `using`

using相当于 join 操作中的 on

根据id字段关联，以下命令是等价的，注意使用using时前面不要加on

on eatery.stuId = stu.stuId
using(stuId)

6.MySQL进阶

6.1 `view` 视图

视图主要负责筛选，有意隐藏敏感数据与结构

创建视图

create view view_name as select 查询语句

create view view_1 as select * from stu;

MySQL基础_数据_02

显示视图

本质上视图还是一张表，所以使用 show tables

mysql> show tables;
+----------------+
| Tables_in_name |
+----------------+
| eatery         |
| stu            |
| view_1         |
+----------------+

mysql> desc view_1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| money | decimal(10,4) | YES  |     | NULL    |       |
| stuId | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+

mysql> show create view view_2;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View                                                                                                                                                   | character_set_client | collation_connection |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_2 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_2` AS select `stu`.`stuId` AS `stuId`,`stu`.`name` AS `name` from `stu` | gbk                  | gbk_chinese_ci       |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

# 显示数据库所有视图，\G表示全部
mysql> show table status where comment ='view' \G;
*************************** 1. row ***************************
           Name: view_1
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
*************************** 2. row ***************************
           Name: view_2
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW

更新视图

alter view vw_name as select name from stu;

mysql> alter view view_1 as select * from stu;

mysql> desc view_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| stuId | int(11)     | NO   |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

删除视图

drop view vw_name;

mysql> drop view view_1;
Query OK, 0 rows affected (0.00 sec)
mysql> desc view_1;
ERROR 1146 (42S02): Table 'name.view_1' doesn't exist

视图算法

视图算法有合并算法 merge、临时表算法 temptable、未定义 undefined

MySQL基础_数据_03

6.2 事务

概述

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

**原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
**一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
**隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
**持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

开始、提交事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    636.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> update wallet set balance=balance-50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update wallet set balance=balance+50 where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;	# 提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    686.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

一旦commit就不能rollback了，先rollback再commit，查询结果未提交，则可以回溯

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update wallet set balance=balance+50 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> rollback;	# 回滚，数据未改变
Query OK, 0 rows affected (0.01 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    450.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

先commit再rollback，查询结果已经提交，无法回溯

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update wallet set balance=balance+50 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from wallet;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> commit;	# 提交事务
Query OK, 0 rows affected (0.01 sec)

mysql> rollback;	
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet;	# 无法回滚
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    500.87 |
|  2 |    736.57 |
|  3 | 888888.00 |
+----+-----------+
3 rows in set (0.00 sec)

设置回滚点

相当于快照功能，设置回滚点 savepoint，回到回滚点rollback to point

mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
+----+-----------+
4 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values(5,1000);
Query OK, 1 row affected (0.01 sec)

mysql> savepoint five;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into wallet values(6,5000);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint six;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
|  5 |   1000.00 |
|  6 |   5000.00 |
+----+-----------+
6 rows in set (0.00 sec)

mysql> rollback to five;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from wallet order by id ;
+----+-----------+
| id | balance   |
+----+-----------+
|  1 |    550.87 |
|  2 |    786.57 |
|  3 | 888938.00 |
|  4 |   1050.00 |
|  5 |   1000.00 |
+----+-----------+
5 rows in set (0.00 sec)

6.3 索引

概述

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。索引可以大大提高 MySQL 的检索速度。

MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引：

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引，即一个索引包含多个列。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引虽然能够提高查询性能，但也需要注意以下几点：

索引需要占用额外的存储空间。
对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

创建索引

create index index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

CREATE INDEX idx ON students (name);

修改表结构(添加索引)

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

创建表的时候直接指定

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_age (age)
);

删除索引

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

6.4 存储过程

创建存储过程

delimiter用于设定SQL语句分隔符，可以设置以双斜线为结尾

mysql> delimiter //
mysql> create procedure proc()
    -> begin
    -> update wallet set balance=balance+50;
    -> update teacher set name='Frank';
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc();
Query OK, 4 rows affected (0.01 sec)

mysql> select * from wallet natural join  teacher;
+----+-----------+-------+--------+----------+
| id | balance   | name  | phone  | address  |
+----+-----------+-------+--------+----------+
|  1 |    550.87 | Frank | NULL   | 暂时未知 |
|  2 |    786.57 | Frank | NULL   | 暂时未知 |
|  3 | 888938.00 | Frank | 123456 | ShangHai |
|  4 |   1050.00 | Frank | NULL   | NULL     |
+----+-----------+-------+--------+----------+
4 rows in set (0.00 sec)

显示存储过程

mysql> delimiter ;
mysql> show create procedure proc;
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                       | Create Procedure
                                               | character_set_client | collation_connection | Database Collation |
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| proc      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`()
begin
update wallet set balance=balance+50;
update teacher set name='Frank';
end | gbk                  | gbk_chinese_ci       | gbk_chinese_ci     |
+-----------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
# 显示所有存储过程
mysql> show procedure status \G;

删除存储过程

mysql> drop procedure proc;
Query OK, 0 rows affected (0.01 sec)

7.数据库设计

7.1 实体和实体之间的关系

数据对象彼此之间相互连接的方式称为联系(Relationship)，也称为关系，联系可分为以下 3 种类型

一对一联系 (1 ∶ 1) 例如，部门与经理的联系是一对一的一对多联系 (1 ∶ N) 例如，某校教师与教的课程之间存在一对多的多对多联系 (M ∶ N) 例如，学生与课程间的联系是多对多的

7.2数据库设计的步骤

第一步：标识表的设计
第二步：表字段的设计
第三布：表与表之间的设计

7.3 标识表的注意事项

表分为实体表\以及业务表\，但我们通常在标识表时会先标识实体表，再标识业务表。因为业务表一般是用于标识实体表与另一个实体的多对多的关系的。

实体表：记录中描述一个对象的表，就是名词，例如用户、商品、订单等业务表：就是需求中一个行为，关注、收藏等（大部分是中间表）

7.4 标识字段要符合数据库三大范式

第一范式：确保每列的原子性(强调的是列的原子性，即列不能够再分成其他几列)。实际上，第一范式是所有关系型数据库的最基本要求，不能表中套表，要保证数据的原子性。
第二范式：是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF），确保表中每列与主键相关，而不能只与主键的某部分相关（主要针对联合主键），主键列与非主键列遵循完全函数依赖关系，也就是完全依赖，任意一个字段都只依赖表中的同一个字段。
第三范式：属性不依赖于其他非主属性，确保主键列之间没有传递函数依赖关系，也就是消除传递依赖，一张表最多只存2层同类型信息。

7.5 企业规范

库表字段约束规范

字段名

is_vip unsigned tinyint 长度为1
mysql命名是不区分大小写的，字段名必须是小写字母
字段分割用下划线隔开，且一旦定义就不能改字段了

表名

mysql仓库名一般和应用名保持一致，关键词不能用作表名，且表名不能出现复数索引名：
主键一般是以pk_xxx开头，唯一键以uk_xxx，idx_xxx等开头小数类型禁止使用float和double，防止丢失精度，数据很小用char(定长)，不使用varchar(变长)，很长直接用text 表定义：
表里必须定义(强制要求)的字段 id ，create_time ，update_time
id必须为主键，必须为无符号自增bigint类型，不能定义为int，除非是分布式id
create_time，update_time 必须为 datatime 类型
单表行数不超过500W行，一般要分表

索引规范

索引约束

一般情况下，有唯一特性的字段要设置唯一索引
在实际开发当中不允许多于两个表的查询
多表查询的时候，关联的字段也要有索引
大多数情况下有约束规范就足够了

SQL开发约束规范

开发约束

不能用where name = null 来判断是否为空，需要用where name is null
并发项目中不要使用并发和级联，一切外键的问题在应用层解决
不能使用存储过程
子查询中避免in操作
编码格式统一utf-8