数据库MYSQL学习系列二

                        数据库MySQL学习系列二

创新互联建站从2013年成立，先为万宁等服务建站，万宁等地企业，进行企业商务咨询服务。为万宁企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

一.MYSQL数据库对象与应用

2.1-MySQL数据类型

Number不止一种

· ×××

· 浮点型

×××

· INT

· SMALLINT

· MEDIUMINT

· BIGINT

老生常谈的问题

int(11) VS int(21) 存储空间，还是存储范围有区别？

答案是：两者完全一样，只是在显示的时候补全0的位数不一样。

可以通过下面的例子来验证：

create table t(aint(11) zerofill, bint(21) zerofill);insert into tvalues (1,1);select * from t;

MySQL默认是不带0补全的。

只是在一些特殊情况下两者显示有区别，其本质完全一样。

浮点型

· FLOAT(M, D)

· DOUBLE(M, D)

精度丢失问题

· 精度丢失

一个例子：

create table t(aint(11), b float(7,4));insert into tvalues (2,123.12345);select * from t;

定点数-更精确的数字类型

· DECIMAL

o 高精度的数据类型，常用来存储交易相关的数据

o DECIMAL(M,N).M代表总精度，N代表小数点右侧的位数（标度）

o 1 < M < 254, 0 < N < 60;

o 存储空间变长

性别、省份信息

一般使用tinyint、char(1)、enum类型。

经验之谈

· 存储性别、省份、类型等分类信息时选择TINYINT或者ENUM

· BIGINT存储空间更大，INT和BIGINT之间通常选择BIGINT

· 交易等高精度数据选择使用DECIMAL

存储用户名的属性

· CHAR

· VARCHAR

· TEXT

CAHR与VARCHAR

· CHAR和VARCHAR存储的单位都是字符

· CHAR存储定长，容易造成空间的浪费

· VARCHAR存储变长，节省存储空间

字符与字节的区别

对于utf8mb4号称占用四字节但是并不绝对。如果在utf8可以覆盖到的范围则仍然占用3字节。

utf8mb4最有优势的应用场景是用于存储emoji表情

emoji表情

· MySQL版本 > 5.5.3

· JDBC驱动版本 > 5.1.13

· 库和表的编码设为utf8mb4

TEXT与CHAR和VARCHAR的区别

· CHAR和VARCHAR存储单位为字符

· TEXT存储单位为字节，总大小为65535字节，约为64KB

· CHAR数据类型最大为255字符

· VARCHAR数据类型为变长存储，可以超过255个字符

· TEXT在MySQL内部大多存储格式为溢出页，效率不如CHAR

一个例子：

create table t (achar(256));create table t (avarchar(256));

存储头像

· BLOB

· BINARY

性能太差，不推荐

经验之谈

· CHAR与VARCHAR定义的长度是字符长度不是字节长度

· 存储字符串推荐使用VARCHAR(N),N尽量小

· 虽然数据库可以存储二进制数据，但是性能低下，不要使用数据库存储文件音频等二进制数据

存储生日信息

· DATE

· TIME

· DATETIME

· TIMESTAMP

· BIGINT

时间类型的区别在哪里

· 

存储空间上的区别

· 

o DATE三字节，如：2015-05-01

o TIME三字节，如：11:12:00

o TIMESTAMP，如：2015-05-01 11::12:00

o DATETIME八字节，如：2015-05-01 11::12:00

· 

存储精度的区别

· 

o DATE精确到年月日

o TIME精确到小时分钟和秒

o TIMESTAMP、DATETIME都包含上述两者

TIMESTAMP VS DATETIME

· 存储范围的区别

o TIMESTAMP存储范围：1970-01-01 00::00:01 to 2038-01-19 03:14:07

o DATETIME的存储范围：1000-01-01 00:00:00 to 9999-12-31 23:59:59

MySQL在5.6.4版本之后，TimeStamp和DateTime支持到微妙

· 字段类型与市区的关联关系

o TIMESTAMP会根据系统时区进行转换，DATETIME则不会

字段类型和时区的关系

· 国际化的系统

一个例子：

create table test (a datetime, btimestamp);select now();insert into testvalues (now(), now());select * from test;set time_zone= '+00:00';select * from test;

BIGINT如何存储时间类型

· 应用程序将时间转换为数字类型

2.2-MySQL数据对象

MySQL常见的数据对象有哪些

· DataBase/Schema

· Table

· Index

· View/Trigger/Function/Procedure

库、表、行层级关系

· 一个DataBase对应一个Schema

· 一个Schema包含一个或多个表

· 一个表里面包含一个或多个字段

· 一个表里包含一条或多条记录

· 一个表包含一个或多个索引

多DataBase用途

· 业务隔离

· 资源隔离

表上有哪些常用的数据对象

· 索引

· 约束

· 视图、触发器、函数、存储过程

什么是数据库索引

· 读书的时候如何快速定位某一章节

o 查找书籍目录

o 在自己喜欢的章节加书签，直接定位

· 索引就是数据库中的数据的目录（索引和数据是分开存储的）

o 索引和数据是两个对象

o 索引主要是用来提高数据库的查询效率

o 数据库中数据变更同样需要同步索引数据的变更

如何创建索引（一）

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

  [index_type]

  ON tbl_name (index_col_name,...)

  [index_option]

  [algorithm_option | lock_option] ...

index_col_name:

  col_name [(length)] [ASC |DESC]

index_type:

  USING {BTREE | HASH}

如何创建索引（二）

ALTER [IGNORE] TABLE tbl_name

  [alter_specification [, alter_specification] ...]

  [partition_options]

alter_specification:

    table_options

  | ADD [COLUMN] col_name column_definition

        [FIRST | AFTER col_name]

    ADD [COLUMN] (col_name column_definition,...)

    ADD {INDEX|KEY} [index_name]

        [index_type] (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]]PRIMARY KEY

        [index_type] (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]]

        UNIQUE [INDEX|KEY] [index_name]

约束

· 生活中的约束有哪些

o 每个人的指纹信息必须唯一

o 每个人的×××要求唯一

o 网上购物需要先登录才能下单

· 唯一约束

o 对一张表的某个字段或者某几个字段设置唯一键约束，保证在这个表里对应的数据必须唯一，如：用户ID、手机号、×××等。

创建唯一约束

· 唯一约束是一种特殊的索引

· 唯一约束可以是一个或者多个字段

· 唯一约束可以在创建表的时候建好，也可以后面再补上

· 主键也是一种唯一约束

唯一约束

以如下这张表为例

CREATE TABLE `order` (

  `id` int(10) unsignedNOT NULL AUTO_INCREMENT,

  `orderid` int(10) unsignedNOT NULL,

  `bookid` int(10) unsignedNOT NULL DEFAULT'0',

  `userid` int(10) unsignedNOT NULL DEFAULT'0',

  `number` tinyint(3) unsignedNOT NULL DEFAULT'0',

  `address` varchar(128)NOT NULL DEFAULT'',

  `postcode` varchar(128)NOT NULL DEFAULT'',

  `orderdate` datetimeNOT NULL DEFAULT'0000-00-00 00:00:00',

  `status` tinyint(3) unsigned zerofill DEFAULT'000',

  PRIMARY KEY (`id`),

  UNIQUE KEY`idx_orderid` (`orderid`),

  UNIQUE KEY`idx_uid_orderid` (`userid`,`orderid`),

  KEY`bookid` (`bookid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

· 索引有哪些

o 主键索引 ID

o 单键索引 orderid

o 单键索引 bookid

o 组合索引 (userid + orderid)

· 唯一约束有哪些

o 主键约束 (ID)

o 单键唯一索引 (orderid)

o 组合唯一索引 (userid + orderid)

添加唯一约束

· 添加主键

o alter table `order` add primary key (id);

· 添加唯一索引

o alter table `order` add unique key idx_uk_orderid (orderid);

外键约束

· 外键指两张表的数据通过某种条件关联起来

创建外键约束

· 将用户表和订单表通过外键关联起来

o alter table `order` add CONSTRAINT constraint_uid FOREIGN KEY (userid) REFERENCES user(userid);

· 使用外键的注意事项

o 必须是INNODB表，Myisam和其他引擎不支持外键

o 相互约束的字段类型必须要求一样

o 主表的约束字段要求有索引

o 约束名称必须要唯一，即使不在一张表上

View

· 产品需求

o 假如有其他部门的同事想查询我们数据库里的数据，但是我们并不想暴露表结构，并且只提供给他们部分数据

View的作用

· 视图将一组查询语句构成的结果集，是一种虚拟结构，并不是实际数据

· 视图能简化数据库的访问，能够将多个查询语句结构化为一个虚拟结构

· 视图可以隐藏数据库后端表结构，提高数据库安全性

· 视图也是一种权限管理，只对用户提供部分数据

创建View

· 创建已完成订单的视图

o create view order_view as select * from `order` where status=1;

Trigger

· 产品需求

o 随着客户个人等级的提升， 系统需要自动更新用户的积分，其中一共有两张表，分别为：用户信息表和积分表

· Trigger俗称触发器，指可以在数据写入表A之前或者之后可以做一些其他动作

· 使用Trigger在每次更新用户表的时候出发更新积分表

除此之外还有哪些

· Function

· Procedure

2.3-MySQL权限管理

连接MySQL的必要条件

· 网络要通畅

· 用户名和密码要正确

· 数据库需要加IP白名单

· 更细粒度的验证（库、表、列权限类型等等）

数据有哪些权限

show privileges命令可以查看全部权限

权限粒度

· Data Privileges

o DATA: SELECT, INSERT, UPDATE, DELETE

· Definition Privileges

o DataBase: CREATE, ALTER, DROP

o Table: CREATE, ALTER, DROP

o VIEW/FUNCTION/TRIGGER/PROCEDURE: CREATE, ALTER, DROP

· Administrator Privileges

o Shutdown DataBase

o Replication Slave

o Replication Client

o File Privilege

MySQL赋权操作

GRANT

  priv_type [(column_list)]

    [, priv_type [column_list]] ...

  ON [object_type] priv_level

  TO user_specification [, user_specification] ...

  [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]

  [WITH with_option ...]GRANT PROXYON user_specification

  TO user_specification [, user_specification] ...

  [WITHGRANT OPTION]

如何新建一个用户并赋权

· 使用MySQL自带的命令

o CREATE USER 'netease'@'localhost' IDENTIFIED BY 'netease163';

o GRANT SELECT ON *.* TO 'netease'@'localhost' WITH GRANT OPTION;

其他方法

· 更改数据库记录

o 首先向User表里面插入一条记录，根据自己的需要选择是否向db和table_pirv表插入记录

o 执行flush privileges命令，让权限信息生效

更简单的办法

· GRANT语句会判断是否存在该用户，如果不存在则新建

o GRANT SELECT ON *.* TO 'NETEASE'@'localhost' IDENTIFIED BY 'netease163' WITH GRANT OPTION;

查看用户的权限信息

· 查看当前用户的权限

o show grants;

· 查看其它用户的权限

o show grants for netease@'localhost';

如何更改用户的权限

· 回收不需要的权限

o revoke select on *.* from netease@'localhost';

· 重新赋权

o grant insert on *.* to netease@'localhost';

如何更改用户密码

· 用新密码，grant语句重新授权

· 更改数据库记录，Update User表的Password字段

o 注意：用这种办法，更改完需要flush privileges刷新权限信息，不推荐

删除用户

DROP USER user [, user] ...

With Grant Option

· 允许被授予权利的人把这个权利授予其他的人

MySQL权限信息存储结构

· MySQL权限信息是存在数据库表中

· MySQL账号对应的密码也加密存储在数据库表中

· 每一种权限类型在元数据里都是枚举类型，表明是否有该权限

有哪些权限相关的表

· user

· db

· table_pirv

· columns_pirv

· host

权限验证流程

查询时从user->db->table_pirv->columns_pirv依次验证，如果通过则执行查询。

小结

· MySQL权限信息都是以数据记录的形式存储在数据库的表中。

· MySQL的权限验证相比网站登录多了白名单环节，并且粒度更细，可以精确到表和字段。

MySQL权限上有哪些问题

· 使用Binary二进制安装管理用户没有设置密码

· MySQL默认的test库不受权限控制，存在安全风险

mysql_secure_installation

· You can set a Password for root accounts.

· You can remove root accounts that are accessible from outside the localhost.

· You can remove anonymous-user accounts.

· You can remove the test database.

小结

· 权限相关的操作不要直接操作表，统一使用MySQL命令。

· 使用二进制安装MySQL安装后，需要重置管理用户(root)的密码。

· 线上数据库不要留test库

实践课：数据库对象

何为表结构设计

· 表结构设计需要在正式进行开发之前完成

· 根据产品需求将复杂的业务模型抽象出来

设计表的时候需要注意哪些

· 理解各个表的依赖关系

· 理解各个表的功能特点

o 字段之间的约束、索引

o 字段类型、字段长度

收集表属性

· 昵称

· 生日

· 性别

· 手机号码

· 住宅号码

· 邮编

· 住宅地址

· 注册地址

· 登录IP

· 上一次登录时间

· 邮件地址

理解表的功能特点——数据用途

create table tb_account(

  account_idint not null auto_incrementprimary key,

  nick_namevarchar(20),

  true_namevarchar(20),

  sexchar(1),

  mail_addressvarchar(50),

  phone1varchar(20)not null,

  phone2varchar(20),

  passwordvarchar(30)not null,

  create_time datetime,

  account_state tinyint,

  last_login_time datetime,

  last_login_ipvarchar(20)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

create table tb_goods(

  good_idbigint not null auto_incrementprimary key,

  goods_namevarchar(100)not null,

  pic_urlvarchar(500)not null,

  store_quantityint not null,

  goods_notevarchar(4096),

  producervarchar(500),

  category_idint not null

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

create table tb_goods_category(

  category_idint not null auto_incrementprimary key,

  category_levelsmallint not null,

  category_namevarchar(500),

  upper_category_idint not null

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

create table tb_order(

  order_idbigint not null auto_incrementprimary key,

  account_idint not null,

  create_time datetime,

  order_amountdecimal(12,2),

  order_state tinyint,

  update_time datetime,

  order_ipvarchar(20),

  pay_methodvarchar(20),

  user_notesvarchar(500)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

create table tb_order_item(

  order_item_idbigint not null auto_incrementprimary key,

  order_idbigint not null,

  goods_idbigint not null,

  goods_quantityint not null,

  goods_amountdecimal(12,2),

  uique key uk_order_goods(order_id, goods_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

数据类型——命名规范

· 所有表名，字段名全部使用小写字母

· 不同业务，表名使用不同前缀区分。

· 生成环境表名字段名要有实际意义

· 单个字段尽量使用字段全名；多个字段之间用下划线分隔

字段设计规范

· 字段类型选择，尽量选择能满足应用要求的最小数据类型

· 尽量使用×××代替字符型。×××在字段长度、索引大小等方面开销小效率更高，如邮编字段，手机号码等

· 注释，每个字段必须以comment语句给出字段的作用

· 经常访问的大字段需要单独放到一张表中，避免降低sql效率，图片、电影等大文件数据禁止存数据库

· 新业务统一建议使用utf8mb4字符集

用户赋权

· 理解用户到底需要什么权限

o 普通用户只有数据读写权限

o 系统管理员具有super权限

· 权限粒度要做到尽可能的细

o 普通用户不要设置with grant option属性

o 权限粒度：系统层面>库层面>表层面>字段层面

· 禁止简单密码

o 线上密码要求随机

2.4-SQL语言进阶

本课程涉及建表SQL

-- ------------------------------ Table structure for `play_fav`-- ----------------------------DROP TABLE IF EXISTS`play_fav`;CREATE TABLE `play_fav` (

  `userid` bigint(20)NOT NULL COMMENT'收藏用户id',

  `play_id` bigint(20)NOT NULL COMMENT'歌单id',

  `createtime` bigint(20)NOT NULL COMMENT'收藏时间',

  `status` int(11) DEFAULT'0' COMMENT'状态，是否删除',

  PRIMARY KEY (`play_id`,`userid`),

  KEY`IDX_USERID` (`userid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌单收藏表';

-- ------------------------------ Records of play_fav-- ----------------------------INSERT INTO play_favVALUES ('2','0','0','0');INSERT INTO play_favVALUES ('116','1','1430223383','0');INSERT INTO play_favVALUES ('143','1','0','0');INSERT INTO play_favVALUES ('165','2','0','0');INSERT INTO play_favVALUES ('170','3','0','0');INSERT INTO play_favVALUES ('185','3','0','0');INSERT INTO play_favVALUES ('170','4','0','0');INSERT INTO play_favVALUES ('170','5','0','0');

-- ------------------------------ Table structure for `play_list`-- ----------------------------DROP TABLE IF EXISTS`play_list`;CREATE TABLE `play_list` (

  `id` bigint(20)NOT NULL COMMENT'主键',

  `play_name` varchar(255) DEFAULTNULL COMMENT'歌单名字',

  `userid` bigint(20)NOT NULL COMMENT'歌单作者账号id',

  `createtime` bigint(20) DEFAULT'0' COMMENT'歌单创建时间',

  `updatetime` bigint(20) DEFAULT'0' COMMENT'歌单更新时间',

  `bookedcount` bigint(20) DEFAULT'0' COMMENT'歌单订阅人数',

  `trackcount` int(11) DEFAULT'0' COMMENT'歌曲的数量',

  `status` int(11) DEFAULT'0' COMMENT'状态,是否删除',

  PRIMARY KEY (`id`),

  KEY`IDX_CreateTime` (`createtime`),

  KEY`IDX_UID_CTIME` (`userid`,`createtime`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌单';

-- ------------------------------ Records of play_list-- ----------------------------INSERT INTO play_listVALUES ('1','老男孩','1','1430223383','1430223383','5','6','0');INSERT INTO play_listVALUES ('2','情歌王子','3','1430223384','1430223384','7','3','0');INSERT INTO play_listVALUES ('3','每日歌曲推荐','5','1430223385','1430223385','2','4','0');INSERT INTO play_listVALUES ('4','山河水','2','1430223386','1430223386','5',null,'0');INSERT INTO play_listVALUES ('5','李荣浩','1','1430223387','1430223387','1','10','0');INSERT INTO play_listVALUES ('6','情深深','5','1430223388','1430223389','0','0','1');

-- ------------------------------ Table structure for `song_list`-- ----------------------------DROP TABLE IF EXISTS`song_list`;CREATE TABLE `song_list` (

  `id` bigint(20)NOT NULL COMMENT'主键',

  `song_name` varchar(255)NOT NULL COMMENT'歌曲名',

  `artist` varchar(255)NOT NULL COMMENT'艺术节',

  `createtime` bigint(20) DEFAULT'0' COMMENT'歌曲创建时间',

  `updatetime` bigint(20) DEFAULT'0' COMMENT'歌曲更新时间',

  `album` varchar(255) DEFAULTNULL COMMENT'专辑',

  `playcount` int(11) DEFAULT'0' COMMENT'点播次数',

  `status` int(11) DEFAULT'0' COMMENT'状态,是否删除',

  PRIMARY KEY (`id`),

  KEY`IDX_artist` (`artist`),

  KEY`IDX_album` (`album`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌曲列表';

-- ------------------------------ Records of song_list-- ----------------------------INSERT INTO song_listVALUES ('1','Good Lovin\' Gone Bad','Bad Company','0','0','Straight Shooter','453','0');INSERT INTO song_listVALUES ('2','Weep No More','Bad Company','0','0','Straight Shooter','280','0');INSERT INTO song_listVALUES ('3','Shooting Star','Bad Company','0','0','Straight Shooter','530','0');INSERT INTO song_listVALUES ('4','大象','李志','0','0','1701','560','0');INSERT INTO song_listVALUES ('5','定西','李志','0','0','1701','1023','0');INSERT INTO song_listVALUES ('6','红雪莲','洪启','0','0','红雪莲','220','0');INSERT INTO song_listVALUES ('7','风柜来的人','李宗盛','0','0','作品李宗盛','566','0');

-- ------------------------------ Table structure for `stu`-- ----------------------------DROP TABLE IF EXISTS`stu`;CREATE TABLE `stu` (

  `id` int(10)NOT NULL DEFAULT'0',

  `name` varchar(20) DEFAULTNULL,

  `age` int(10) DEFAULTNULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ------------------------------ Records of stu-- ----------------------------

-- ------------------------------ Table structure for `tbl_proc_test`-- ----------------------------DROP TABLE IF EXISTS`tbl_proc_test`;CREATE TABLE `tbl_proc_test` (

  `id` int(11)NOT NULL AUTO_INCREMENT,

  `num` int(11) DEFAULTNULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;