参考书籍《深入浅出MySql 数据库开发、优化与管理维护》
安装MySQL时系统自动创建
information_schema:主要存储了系统中的一些数据库对象信息
cluster:存储了系统的集群信息
mysql:存储了系统的用户权限信息
test:系统自动创建的测试数据库,任何用户都可以使用
SQL分类
DLL(Data Definition Languages)语句:数据定义语句
DML(Data Manipulation Language)语句:数据操纵语句
DCL(Data Control Language)语句:数据控制语句
DML
SELECT
查询全部 SELECT * FROM tablename [WHERE CONDITION]
查询不重复的记录SELECT DISTINCT(field1,field2...) FROM tablename
条件查询 SELECT field1,field2 FROM tablename [WHERE CONDITION]
排序和限制 SELECT * FROM tablename [WHERE CONDITION]
[ORDER BY field1 [DESC|ASC],field2[DESC|ASC],..,fieldn[DESC|ASC]]
[LIMIT offset_start,row_count]
聚合 SELECT [field1,field2,...,fieldn] fun_name
FROM tablename
[WHERE where_contition]
[GROUP BY field1,field2,...,fieldn]
[WITH ROLLUP]
[HAVING where_contition]
//fun_name:sum(求和),max(最大值),min(最小值)
count(*)(记录数):越常用的列,要放在靠前的位置。而cout(*)和count(列)是两个不等价的用法,
所以 无法比较哪个性能更好,
在实际的sql优化场景中要根据当时的业务场景再去考虑是使用count(*)还是count(列)
WITH ROLLUP可选语法 表明是否符分类聚合后的结果进行再汇总
HAVING 关键字表示对分类后的结果再进行条件的过滤
表连接 内连接:仅选出两张表中互相匹配的记录
SELECT * FROM table1,table2 WHERE table1.fieldname=table2.fieldname
外连接:选出其他不匹配的记录
左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
SELECT * FROM table1 left join table2 on table.field=table.field
右连接:包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
SELECT * FROM table1 right join table2 on table1.field=table2.field
子查询 SELECT * FROM tablename WHERE field in(SELECT field FROM tablename)
not in 、=、!=、exists、not exists等
[注]:表连接在很多情况下用于优化子查询
记录联合 将两个表的数据按照一定的查询条件查询出来后,将结果合并显示
SELECT * FROM t1
UNION\UNION ALL
。。。
SELECT * FROM tn
快速查阅帮助 ?content
日期时间类型
年月日 date
年月日时分秒 datetime
取值范围 1000-01-01 00:00:00 -9999-12-31 23;59:59
时分秒 time
需要经常插入或者更新日期为当前系统时间 timestamp
取值范围:19700101080001-2038年的某一天
年份 year
几种常见的存储引擎的适用环境:
MyISAM:默认的MySQL插件式存储引擎 应用是以读操作和插入操作为主
InnoDB:用于事务处理应用程序,支持外键 应用对事务的完整性有比较高的要求
MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据的环境下,可提供极快的访问
MERGE:用于将一系列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用它们
选择合适的数据类型:
char 与 varchar
char 固定长度的字符类型 varchar 可变长度的字符类型
MyISAM :建议使用固定长度的数据列代替可变长度的数据列
MEMORY:两张都是作为char类型处理
InnoDB:建议使用varchar类型
text与blob
blob 保存二进制数据 比如:照片 blob mediumblob longblob
text 保存字符数据 比如:日记 text mediumtext longtext
1.blob和text值会引起一些性能问题,特别是在执行了大量的删除操作时,删除操作会在数据表中留下很大的“空洞”
为了提高性能,建议定期使用OPTIMEIZE TABLE 功能对这类表进行碎片整理
2.可以使用合成(Synthetic)索引来提高大文本字段(BLOB或TEXT)的查询性能
简单来说,合成索引就是根据打文本字段的内容建立以个散列值
并把这个值存储在单独的数据列中,接下来就可以通过检索散列值找到数据行
3.在不必要的时候避免检索大型的BLOB或TEXT值
4.把BLOB或TEXT列分离到单独的表中
浮点数和定点数
浮点数一般用于表示含有小数部分的数值 MySQL中 float、double(或real)
定点数以字符串形式存放,所以定点数可以更精确地保存数据 MySQL decimal(或numberic)
几个原则:
1.浮点数存在误差
2.对货币等对精度敏感的数据,应该用定点数表示或存储
3.在编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较
4.要注意浮点数中一些特殊值的处理
日期类型
DATE TIME DATETIME TIMESTAMP
原则:
1.根据实际需要选择能够满足应用的最小存储的日期类型
2.如果要记录年月日时分秒,并且记录的年份比较久远,那么最好用DATETIME
3.如果记录的日期需要让不同时区的用户使用,那么最好使用TIMESTAMP,因为日期类型中只有它能够和实际时区相对应
字符集
UCS (UCS-4) ISO-10646 4字节(32bit)编码 1984
Uicode 16位编码 1991
BMP(BasicMulti-lingual Plane)基本多语言文字面 分为 辅助字面(supplymentary planes)和专用字面(private use planes)1991
UTF(UCS/Unicode Transformation Format)
UTF-8
UTF-16 UTF-16BE(Big Endian) UTF-16LE(Little Endian)
UTF-32 UTF-32BE(Big Endian) UTF-32LE(Littke Endian)
汉字编码
GB 2312-80 《信息交换用汉字编码字符集 基本集》1980
GB 13000 《信息技术 通用多八位编码字符集(UCS)第一部分:体系结构与基本多文种平面》
GBK 《汉字内部扩展规范》
GB 18030 《信息技术信息交换用汉字编码字符集、基本集的扩充》
常用字符集的特点:
字符集 | 是否定长 | 编码方式 | 其他说明 |
ACSII | 是 | 单字节7位编码 | 最早的奠基性字符集 |
ISO-8859-1/latin1 | 是 | 单字节8位编码 | 西欧字符集,用来转码 |
GB 2312-80 | 是 | 双字节编码 | 早期编码 |
GBK | 是 | 双字节编码 | 不是国标 |
GB18030 | 否 | 2字节或4字节 | 数据库支持少 |
UTF-32 | 是 | 4字节编码 | UCS-4原始编码 |
UCS-2 | 是 | 2字节编码 | Windows 2000 |
UTF-16 | 否 | 2字节或4字节 | Java和Windows XP/NT |
UTF-8 | 否 | 1~4字节 | Linux MySQL Go |
如何选择合适的字符集
发布到不同语言的国家或地区 Unicode字符集
字符集对已有数据的兼容性
只需支持一般中文,数据量大。性能要求高 GBK
主要处理英文字符,仅有少量汉字数据 UTF-8
数据库需要做大量的字符运算 定长字符集
避免字符集转换带来的性能开销和数据损失
MySQL字符集 字符集(CHARACTER) 校队规则(COLIATION)
四个默认级别 服务器级 数据库级 表级 字段级
服务器启动之前设置好字符集和校对规则
注:选择目标字符集的时候,要注意最好是源字符集的超集,或者确定比源字符集的字库更大
否则导入后,会丢失一部分数据
运行后发现不足,修改字符集
例:将latin1字符集的数据库修改成GBK字段
导出表结构 mysqldump -uroot -p --default-character-set=gbk -d databasename> createab.sql
手动修改createtab.sql
确保记录不再更新,导出所有记录
mysqldump -uroot -p --quick --no-creare-info --extended-insert --default -character-set=latin1 databasename> data.sql
--quick 用于转储大的表 强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行,并在输出前将它缓存到内存中
--extended-insert 使用包括几个VALUES列表的多行INSERT语法 这样使转储文件更小,重载文件时可以加速插入
--no-create-info 不导出每个转储表的CREATE TABLE语句
--default-character-set=latin1 按照原有的字符集导出所有数据,这样导出的文件中,所有中文都是可见,不会保存成乱码
打开data.sql,将SET NAMES latin1 修改成SET NAMES gbk
使用新的字符集创建新的数据库
create database databasename default charset gbk
创建表,执行createtab.sql
mysql -uroot -p databasename < createtab.sql
导入数据,执行data.sql
mysql -uroot -p databasename < data.sql