MySQL
1.SQL&NoSQL
MySQL是一个开源、免费的关系型数据库
关系型数据库特点：
- 数据是按照结构存储在二维表中
- 支持事务的A(原子性)C(隔离性)I(持久性)D(特性)
- 支持SQL语言对数据库中的数据操作
- 适用场景：
- 数据之间存在一定的关系，比如订单和商品之间，一个订单有多个商品
- 需要事务支持的业务场景，比如操作金额，转账
- 需要使用SQL语言灵活操作数据
非关系型数据库：
- 存储结构灵活，没有固定的结构
- 对事务的支持弱，但对并发处理性能高
- 大多不使用SQL语言操作
- 适用场景：
- 数据结构不固定，比如商品属性，每个商品包含的属性不一致
- 对事务要求不高，但读写并发大的场景
- 对数据处理比较简单
2.数据库设计
宽表模式：对象的所有属性全部存储在一个表中
存在的问题：
- 造成数据冗余，相同的数据在一个表中存在了多次
- 数据修改异常：修改一行中某列的值，会同时修改多行数据；如图，虽然可以添加一个主标题条件，但是会出现信息不一致问题
- 数据插入异常：部分数据由于主键确实无法加入表中，比如：我们只想添加一个方向，这个方向下的课程还没有呢，那这个时候就添加不进去
- 数据删除异常：删除某一数据时不得不删除其他数据：比如我们只想删除方向，但是它会将课程也直接删除
2.1 数据库设计范式
- 第一范式：表中的字段都是不可再分的
- 第二范式：表中必须存在业务主键，并且非主键全部依赖于主键
- 第三范式：表中的非主键列之间不能相互依赖
2.2 存储引擎
InnoDB：事务型存储引擎
- 支持ACID
- 数据按主键存储
- 支持行锁：操作时只锁一行，对其他行没有影响，适合高并发操作
- 支持Btree和自适应Hash索引
- 支持全文和空间索引
使用InnoDB优化数据库表：给每个表添加一个主键ID
主键的目的在于索引
InnoDB引擎使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。
1. 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：
这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
- 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间。此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
2.3 物理设计
整数类型
- 取值范围=-2的位次幂 - 2的位次幂-1 比如tinyint的取值范围= -2的8次 - 2的8次-1
- int(2)并不会限制只能存储长度为2位的数据，可以用tinyint
- SIGN ED是符号位(默认)，UNSIGNED是无符号位，不允许有负数
- MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。
- 默认是255，因为InnoDB存储引擎的表索引的前缀长度最长是767字节(bytes)，你如果需要建索引，就不能超过 767 bytes；utf8编码时 255*3=765bytes ,恰恰是能建索引情况下的最大值。
浮点类型
- float和double不是精确的，所以进行运算可能会出现偏差
- decimal是精确的，但是存储空间要大，
最多可以存储65个数字，如果不声明，默认为(10,0) - 对于计算金额等业务，使用decimal，对于其他的使用float和double即可
CREATE TABLE IF NOT EXISTS t(
d DOUBLE,
d1 DECIMAL(10,2)
);
INSERT INTO t(d,d1) VALUES(112.13,112.13);
INSERT INTO t(d,d1) VALUES(10.13,110.13);
INSERT INTO t(d,d1) VALUES(9,9);
INSERT INTO t(d,d1) VALUES(1.14,1.14);
INSERT INTO t(d,d1) VALUES(100.1,100.1);
SELECT SUM(d),SUM(d1) FROM t;
# result
232.49999999999997 , 332.5
时间类型
- TIME之所以是3-6个字节，因为秒的小数位可以有6位
- DATATIME与时区无关，TIMESTAMP与时区有关
字符串类型
- char是固定长度，无论存储多少个字符，都会占用固定的存储空间
- varchar是可变长度，只会占据存储字符长度的存储空间，比如：varchar(10)，那么存储的字节不可以超过30个字节，utf8：3x10，utf8mb4：4x10；varchar(n)中的n代表字符数而非字节数。，char也一样
- char(20)，varchar(20)最多只能存储20个字符，超出了会提示 Data too long for column 'xxx' at row 1
- 为什么varchar不直接使用255，因为将大量Varchar字段用于不需要么长的数据，可能会使临时表非常大，占用磁盘空间
2.3.1 选择合适的存储类型
- 优先选择符合存储数据的最小数据类型，节省存储空间
- 比如31351253251，可以用bigint表示，但是是8个字节，也可以用unsigned int表示，只有4个字节
- 比如255.255.255.255保存ip，可以用字符串，但是是15个字节，如果用unsigned int表示，只有4个字节
- 但是需要将255.255.255.255转为int，使用INET_ATON('255.255.255.255')=25325235131
- 将int转为255.255.255.255，使用INET_NTOA(242342352)='255.255.255.255'
- 谨慎使用ENUM、TEXT字符串类型
- 比如对于说明、备注我们常常喜欢直接定义为TEXT类型，但是TEXT有64k字节，最多可以存储2万多个汉字，使用varchar足够了
- Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。导致查询效率缓慢
- 同财务相关的数值类型，必须使用decimal精确类型
举例：
- 课程ID为主键自增，有索引，提高查询效率，因为id不会有负数，所以数据类型设置为int unsigned无符号位
- 主标题最多不超过20个字符，并且设置唯一索引
- 课程方向、分类是其他表的主键id，因为这几个表id不会太大，设置weismallint unsigned 6万多个够用了
- 上线时间，可以用datatime也可以timestamp，timestamp最大为2038年
- 学习人数，这个会很多，也不可能负数，所以设置为int unsigned，为43亿。。。
- 课程时长，多少小时多少分钟，所以用TIME
- 内容评分这些用float也可以，因为并不是财务数据
- 课程ID的数据类型需要和课程表的id一致，
为了方便关联索引，查询更加高效，而且如果这边设置小了的话也会出现错误 - 章节编号：tinyint(2)并不会限制存储的长度，而是保证当内容不足两位时，ZEROFILL用来左侧填满0补到两位
- 密码用char(32)固定是因为密码用md5加密生成的就是固定32个字符
- 性别用char(2)应为就 男、女、未知三种情况
2.3.2 为表和列选择合适的名字
- 名称必须使用小写，单词之间用下划线分割
- 禁止使用mysql保留字
- 临时表必须以tmp为前缀并以日期为后缀；方便清理
- 备份表必须以bak为前缀并以日期为后缀
- 不同表相同列名称保持一致
访问数据库
#-u:用户 -p:密码 -h:ip地址
mysql -uroot -p123456 -h192.168.169.140
DDL
对于数据和表的管理语句
建立数据库账号
#用户名为mc_test
#可以访问的ip为192.168.3.*
#密码为123456
create user zhangsan@'192.168.3.%' IDENTIFIED BY '123456';
给账号授权
#赋予zhangsan权限
#权限为对mysql.user表进行select
grant select on mysql.user to zhangsan@'192.168.3.%';
回收账号权限
#收回zhangsan的权限
#权限为对mysql.user表的delete,update,insert
revoke delete,update,insert on mysql.user from zhangsan@'192.168.3.%';
创建表
create table [if not exists] table_name(
column_name 数据类型 [约束] [COMMENT 注释],
column_name 数据类型 [约束] [COMMENT 注释],
column_name 数据类型 [约束] [COMMENT 注释]
)
修改表
alter table table_name
#修改表名
alter table table_name rename to new_table_name;
#修改表的字符集
alter table table_name character set 字符集名称;
#添加列
alter table table_name add column_name 数据类型;
#删除列
alter table table_name drop column_name;
删除表(会删除表中和表中的数据，删除前进行备份 )
drop table [if exists] table_name
创建索引
#unique：唯一
#index：索引
#给imc_class表中的class_name字段添加名为uqx_class_name的唯一索引
create unique index uqx_class_name on imc_class(class_name);
DML
对于表中的数据增删改查的语句
添加数据
#对全部属性添加
insert into table_name VALUES(v1,v2,v3);
#对指定属性添加
insert into table_name(c1,c2,c4) VALUES(v1,v2,v4);
#批量添加多个
insert into table_name VALUES(v1,v2,v3),(v1,v2,v3),(v1,v2,v3);
#当添加数据是唯一索引触发，数据重复了那就修改column_name的值为123
insert into table_name VALUES(v1,v2,v3) on duplicate key update column_name = '123';
查询数据
#查常量值
SELECT 100;
#查函数
SELECT 100%99;
#查函数
SELECT version();
#起别名 as as可以省略不写
SELECT 字段 AS '别名' FROM 表名;
#去重 distinct
SELECT DISTINCT 字段 FROM 表名;
#+号的使用
只充当运算符，不充当连接符号
SELECT 100+90; 结果为190
SELECT '123'+20; 结果为143，会将字符转为数值运算
SELECT '哈哈'+20; 转换失败，结果为0
SELECT null+20; 一方为null，结果为null
#连接关键字 concat,如果有一个字段为null,结果为null
SELECT CONCAT(字段1，字段2) FROM 表名
#如果字段为NULL ifnull(ex1,ex2)ex1是可能为NULL的字段，ex2是替代值
SELECT IFNULL(可能为NULL字段,替代值) FROM 表名字
比较运算符
| 比较运算符 | 说明 |
|---|---|
| = > < >= <= <> != | <>和!=都表示不等于 |
| BETWEEN min AND max | min到max之间的数据，包含min和max |
| IS NULL、IS NOT NULL | 判断列的值是否为NULL |
| LIKE、NOT LIKE | 模糊查询、%代表任意数量字符 ，_代表一个字符 |
| IN、NOT IN | 判断列的值是否在指定范围内 |
逻辑运算符
| 逻辑运算符 | 说明 |
|---|---|
| AND、&& | 两边表达式都为真，才为真 |
| OR、|| | 一边表达式为真，就为真 |
| XOR | 只有一边为真，才为真 |
任何表达式和NULL值运算都为NULL
多表查询
- 表和表之间一定要存在一定的关联关系
- 你要查询哪些数据，看看这些数据都属于哪些表，然后连起来查就行了
- 通过
JOIN关键字进行关联
内链接查询INNER JOIN
内连接是指两个表之间有符合的条件
#假设现在两个表t1和t2，t2中的一个字段为t1的id叫做ti1，查询数据，如果t1中有一个数据的id是4，而t2中的tid没有4，那就查询不出来t1处id为4的数据
SELECT *
FROM table t1
INNER JOIN table t2
WHERE t1.id = t2.tid;
外连接查询LEFT JOIN和RIGHT JOIN
- 外连接分为主表和从表，主表显示全部的数据，从表如果有匹配的就显示值，没有就显示NULL
- 左外连接：left 左边的是主表
- 右外连接：right右边的是主表
SELECT *
FROM table t1
LEFT JOIN table t2
WHERE t1.id = t2.tid;
SELECT *
FROM table t1
RIGHT JOIN table t2
WHERE t1.id = t2.tid;
分组
GROUP BY ... HAVING关键字进行分组
- 只支持对结果集中的字段或者聚合函数分组
- 可以根据多个字段分组
- Having是对分组后进行条件筛选，可以跟聚合函数
#根据title分组，算出每个 title的数量，并且筛选出总数》15的
SELECT title,count(*)
FROM 表名
GROUP BY co.title
HAVING count(*) > 15
聚合函数
可以单独使用，也可以和GROUP BY 一起使用
| 聚合函数 | 说明 |
|---|---|
| COUNT | 计算符合条件的数据行数 |
| SUM(column_name) | 计算一列的数值总和 |
| AVG(column_name) | 计算一列的数值平均值 |
| MAX(column_name） | 计算一列的最大值 |
| MIN(column_name） | 计算一列的最小值 |
排序
ORDER BY .. ASC/DESC；对查询的结果进行排序
- INNODB默认是按照主键排序；MySAIM默认是按照写入的时间排序
- ASC为省序，DESC为降序
- ORDER BY 也可以根据select中未出现的字段排序
SELECT * FROM 表名 ORDER BY column_name DESC
分页
LIMIT offset,size；offset代表起始索引，size代表显示的数量
#从索引0开始查询10条数据
SELECT * FROM 表名 LIMIT 0,10
视图
将查询的结果创建为一个视图保存起来
创建视图
CREATE VIEW view_name AS 查询语句
查询视图
SELECT * FROM view_name
删除数据
删除整个表的数据
TRUNCATE 表名
DELETE FROM 表名
删除符合条件的数据
DELETE FROM 表名 WHERE 条件
删除指定数量数据
DELETE FROM 表名 LIMIT 数量
多表关联删除
DELETE t1
FROM 表1 t1
LEFT JOIN 表2 t2
ON t1.id = t2.tid
WHERE 条件
删除重复的数据，只保留最小的一个数据
DELETE it
FROM imc_type it
INNER JOIN(
SELECT type_name,MIN(type_id) as min_type_id,COUNT(*)
FROM imc_type
GROUP BY type_name
HAVING COUNT(*)>1
) it2
ON it.type_name = it2.type_name
WHERE it.type_id > min_type_id;
更新数据
UPDATE 表名 SET 列名 = 新值 WHERE 条件
时间函数
时间单元：YEAR年、DAY天、MONTH月、HOUR小时、MINUTE分钟
| 函数名 | 说明 |
|---|---|
| NOW()，CURDATE()，CURTIME() | 当前日期+时间，当前日期，当前时间 |
| DATE_FORMAT(date,format) | 按照format格式对日期格式化:%Y,%m.%d,%h,%i,%s |
| RAND() | 返回一个0-1的随机数 |
| SEC_TO_TIME(seconds)/TIME_TO_SEC(time) | 秒转为时间/时间转为秒 |
| DATE_DIFF(date1,date2) | 计算两个日期相差天数 |
| DATE_ADD(date, INTERVAL exr unit) | 对日期进行增加或者减少时间单元，DAY，YEAR，HOUR |
| EXTRACT(unit from date) | 对date日期进行提取时间单元 |
字符串函数
| 函数名 | 说明 |
|---|---|
| CONCAT(str1,str2,...) | 将str1,str2等 字符串进行拼接 |
| CONCAT(seq,str1,str2,...) | 将str1,str2等 字符串进行拼接，并以seq进行分割 |
| CHAR_LENGTH(str) | 返回str字符串的字符个数 |
| LENGTH(str) | 返回str字符串的字节个数，一个英文一个字节，一个中文三个字节 |
| LEFT(str,length)/RIGHT(str,length) | 从左边/右边起截取length个字符 |
| SUBSTRING(str,pos,[length]) | 截取str字符串，从pos位置起，截取length个长度， 如果length不写，就截取到尾部，pos从1开始 |
| SUBSTRING_INDEX(str,delim,count) | 返回str字符串按delim分割后的前count个字符串 |
其他函数
| 函数名 | 说明 |
|---|---|
| ROUNG(X,D) | 对数值X四舍五入，保存D位小数S |
| CASE WHEN 条件 THEN 结果 END | 类似于java中的switch，数据控制 |
| MD5(str) | 返回strMD5加密后的值 |
SELECT username
,CASE WHEN sex = 0 THEN '男'
WHEN sex = 1 THEN '女'
ELSE '未知'
END AS '性别'
FROM user;
注意事项
- 不要使用COUNT(*)
判断是否存在符合条件的数据，使用COUNT效率低，每次都会扫描所有符合条件的数据- 使用SELECT .. LIMIT 1，找到一条符合条件的数据就会返回
执行顺序
SELECT 查询列表 7
FROM 表1 1
连接类型 JOIN 表2 2
ON 连接条件 3
WHERE 筛选条件 4
GROUP BY 分组列表 5
HAVING 筛选条件 6
ORDER BY 排序列表 8
LIMIT 偏移，条目数量 9
SQL优化
问题
ERROR 1130: Host '192.168.1.3' is not allowed to connect to this MySQL server
可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%"
use mysql;
update user set host = '%' where user = 'root';
select host, user from user;
flush privileges; 这句一定要加上！！！
浙公网安备 33010602011771号