数据库MySQL学习系列二
创新互联建站从2013年成立，先为万宁等服务建站，万宁等地企业，进行企业商务咨询服务。为万宁企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
一.MYSQL数据库对象与应用
2.1-MySQL数据类型
· ×××
· 浮点型
· INT
· SMALLINT
· MEDIUMINT
· BIGINT
type | Storage | Minumun Value | Maximum Value |
(Bytes) | (Signed/Unsigned) | (Signed/Unsigned) | |
TINYINT | 1 | -128 | 127 |
0 | 255 | ||
SMALLINT | 2 | -32768 | 32767 |
0 | 65535 | ||
MEDIUMINT | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
INT | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
0 | 18446744073709551615 |
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)
属性 | 存储空间 | 精度 | 精确性 |
Float | 4 bytes | 单精度 | 非精确 |
Double | 8 bytes | 双精度 | 比Float精度高 |
· 精度丢失
一个例子：
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
· CHAR和VARCHAR存储的单位都是字符
· CHAR存储定长，容易造成空间的浪费
· VARCHAR存储变长，节省存储空间
编码\输入字符串 | 网易 | netease |
gbk(双字节) | varchar(2)/4 bytes | varchar(7)/7 bytes |
utf8(三字节) | varchar(2)/6 bytes | varchar(7)/7 bytes |
utf8mb4(四字节) | varchar(2) ? | varchar(7)/7 bytes |
对于utf8mb4号称占用四字节但是并不绝对。如果在utf8可以覆盖到的范围则仍然占用3字节。
utf8mb4最有优势的应用场景是用于存储emoji表情
· MySQL版本 > 5.5.3
· JDBC驱动版本 > 5.1.13
· 库和表的编码设为utf8mb4
· 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都包含上述两者
· 存储范围的区别
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;
· 应用程序将时间转换为数字类型
· DataBase/Schema
· Table
· Index
· View/Trigger/Function/Procedure
· 一个DataBase对应一个Schema
· 一个Schema包含一个或多个表
· 一个表里面包含一个或多个字段
· 一个表里包含一条或多条记录
· 一个表包含一个或多个索引
· 业务隔离
· 资源隔离
· 索引
· 约束
· 视图、触发器、函数、存储过程
· 读书的时候如何快速定位某一章节
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 约束名称必须要唯一，即使不在一张表上
· 产品需求
o 假如有其他部门的同事想查询我们数据库里的数据，但是我们并不想暴露表结构，并且只提供给他们部分数据
· 视图将一组查询语句构成的结果集，是一种虚拟结构，并不是实际数据
· 视图能简化数据库的访问，能够将多个查询语句结构化为一个虚拟结构
· 视图可以隐藏数据库后端表结构，提高数据库安全性
· 视图也是一种权限管理，只对用户提供部分数据
· 创建已完成订单的视图
o create view order_view as select * from `order` where status=1;
· 产品需求
o 随着客户个人等级的提升， 系统需要自动更新用户的积分，其中一共有两张表，分别为：用户信息表和积分表
· Trigger俗称触发器，指可以在数据写入表A之前或者之后可以做一些其他动作
· 使用Trigger在每次更新用户表的时候出发更新积分表
· Function
· Procedure
· 网络要通畅
· 用户名和密码要正确
· 数据库需要加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
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] ...
· 允许被授予权利的人把这个权利授予其他的人
· MySQL权限信息是存在数据库表中
· MySQL账号对应的密码也加密存储在数据库表中
· 每一种权限类型在元数据里都是枚举类型，表明是否有该权限
· user
· db
· table_pirv
· columns_pirv
· host
查询时从user->db->table_pirv->columns_pirv依次验证，如果通过则执行查询。
· MySQL权限信息都是以数据记录的形式存储在数据库的表中。
· MySQL的权限验证相比网站登录多了白名单环节，并且粒度更细，可以精确到表和字段。
· 使用Binary二进制安装管理用户没有设置密码
· MySQL默认的test库不受权限控制，存在安全风险
· 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 线上密码要求随机
本课程涉及建表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;