MySql基础笔记

MySql

之前接触最多的是Oracle，而对于mysql却没有系统的学习过只停留在会用的基础上；接下来将花费一段时间将MySQL系统的学习一边，以待后续项目中使用；

基础

存储引擎

• InnoDB存储引擎，事务性数据库首选引擎，支持ACID，支持行锁和外键
  
  • 提供了具有提交、回滚、崩溃恢复能力的事务安全存储引擎
  • InnoDB为处理巨大数据量的最大性能设计的
  • 在主内存中缓存数据和索引，维持自己的缓冲池
  • InnoDB支持外键完整性约束
• MyISAM存储引擎，具有较高的插入、查询速度，但不支持事物
  
  • 支持大文件
  • 每个MyISAM表的最大索引数是64，每个索引最大的列是16个
  • 默认最大键长度是1000B
  • BLOB TEXT列可被索引
  • NULL值允许出现在索引列中
  • 所有数字键值优先高位存储以允许一个更高的索引压缩
  • 可以把数据文件和索引文件放在不同的目录
  • 每个字符列可以有不同的字符集
  • 有VARCHAR的表可以固定或动态的记录长度
  • VARCHAR和CHAR列可以多达64KB
• Memory存储引擎，将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问
  
  • 每个表可以拥有多达32个索引，每个索引16列，以及500B的最大键长度
  • 执行HASH和BTREE索引
  • 在一个表中可有非唯一键
  • 使用一个固定的记录长度格式
  • 不支持BLOB和TEXT列
  • 支持AUTO_INCREMENT列和可对NULL值列索引
  • 表在所有客户端之间共享
  • 表内容存储在内存中
  • 不在使用表内容时，删除表
• 其他引起，如 ARCHIVE CSV BLACKHOLE
• 修改表的引擎：alter table tbl_name engines=MyISAM，有外键约束的两张表的存储引擎需要一致

基本操作

• 创建database: create database DB_NAME
• 查看表结构： show create <TB_NAME> \G
• 修改表名：ALTER TABLE OLD_DB_NAME RENAME TO NEW_DB_NAME
• 修改字段的数据类型：ALTER TABLE <TB_NAME> MODIFY <FIELD_NAME> <DATA_TYPE>
• 修改字段名：ALTER TABLE <TB_NAME> CHANGE <OLD_FILED_NAME> <NEW_FIELD_NAME> <DATA_TYPE>
• 添加字段：ALTER TABLE <TB_NAME> ADD <FIELD_NAME> <DATA_TYPE> [NOT NULL | FIRST | AFTER <FIELD_NAME>]
• 删除字段：ALTER TABLE <TB_NAME> DROP <FIELD_NAME>
• 修改表字段位置：ALTER TABLE <TB_NAME> MODIFY <FIELD_NAME> <DATA_TYPE> [FIRST | AFTER <FIELD_NAME>]
• 删除表的外键约束：ALTER TABLE <TB_NAME> DROP FOREIGN KEY <FK_NAME>

数据类型

MySql支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型

• 整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT,该类型的字段可增加AUTO_INCREMENT;year int(4),4指的是显示宽度，即使存入的字段超过此值也可存入与查询
• 小数一般用 浮点数类型和定点类型 表示，浮点类型有FLOAT DOUBLE，定点类型有DECIMAL，FLOAT(M,N) M为精度表示总位数，N为标度表示小数的位数
  
  • 单精度浮点占用4个字节存储空间，双精度浮点占用8个字节的存储空间
  • DECIMAL(M,N)占用M+2个字节，以字符串形式存储
• 日期和时间类型：DATETIME DATE TIMESTAMP TIME YEAR
  
  • YEAR: 以4位字符串或者数字格式表示的year，范围为1901~2155;以2位字符串表示的YEAR，范围为00~99,00~69表示2000~2069,70~99表示1970~1999;以2位数字表示的YEAR范围为1~99，0会被转换为0000
  • TIME：用于只需要时间的值，用3节点存储,格式HH:MM:SS,D HH:MM:SS中D表示日，插入数据库为D*24+HH:MM:SS;HHMMSS无冒号时最右侧为秒；
  • DATE: 仅需要日期值不许要时间值，用3字节存储，格式为YYYY-MM-DD
  • DATATIME:存储时需要8个字节，格式YYYY-MM-DD HH:MM:SS
  • TIMESTAMP:存储时需要4个字节，取值范围小于DATATIME，为1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC,存储世界标准时间

• 文本字符串类型：CHAR VARCHAR TEXT ENUM SET

  • CHAR:在存储时右侧会填充空格以达到指定长度，当检索时尾部的空格将被删除
  • VARCHAR(M):长度可变字符串，值保存和检索时尾部空格仍保留
  • TEXT类型，保存非2进制字符串，查询或存储时不删除尾部空格
  • ENUM，枚举类型 <FIELD_NAME> ENUM('v1', 'v2', 'v3', ..., 'vn')，此列总有一个默认值，为默认值列表的第一个值
  • SET，字符串对象可以有0个或多个值，<FILED_NAME> SET ('v1', 'v2', 'v3', ... 'vn'),插入时 <insert into tb_name> values('v1'),('v1,v2,v3')

• 二进制字符串类型：BIT BINARY VARBINARY TINYBLOB BLOB MEDIUMBLOB LONGBLOB

  • BIT:位字段类型，存储时左补0
  • BINARY/VARBINARY:前者为不可变二进制字符串，位数不足时右边填充\0，后者为可变二进制字符串
  • BLOB存储的是大对象二进制字符串

• JSON:5.7.7后InnoDB引擎支持json

  • 存储类似text，可以存储非常大的数据
  • json有效性检查：插入的数据必须是json类型的字符串
    
    • 查询性能提升，不需要遍历整个字符串：jsn_extract(col, '$.name') 即可获取col列中的部分内容
  • 支持索引：通过虚拟列的功能可以对json部分的数据进行索引
    

    
    # 虚拟列
    
    alter table tb_name add col_name data_type generated always as (jsn_extract(json_col_name, '$.pro')) virtual;

TYPE	DESC	STORED
TINYINT	很小的整数	1个字节，-128~127, 0~255
SMALLINT	小整数	2个字节,-32768~32767,0~65535
MEDIUMINT	中等大小整数	3个字节，-2^24/2~2^24-1,2^24-1,
INT	普通大小的整数	4个字节，2^32
BIGINT	大整数	8个字节，2^64
CHAR(M)	固定长度	M字节,1<=M<=255
VARCHAR(M)	变长	L+1字节，1<=L<=M<=65535
TINYTEXT	非2进制字符串	L+1字节，L<2^8
TEXT	小字符串	L+2字节，L<2^16
MEDIUMTEXT	中等大小字符串	L+3字节，L<2^24
LONGTEXT	大的字符串	L+4字节，L<2^32
ENUM	枚举类型	1或2字节，取决于枚举值得数目(最大65535)
SET	字符串对象可以有0个或多个成员	1,2,3,4,8字节，取决于集合成员的数量(对多64个成员)
BIT(M)	位字段类型	(M+7)/8个字节
BINARY(M)	固定长度二进制字符串	M个字节
VARBINARY(M)	可变长度二进制字符串	M+1个字节
TINYBLOB	非常小的BLOB	L+1字节，L<2^8
BLOB	blob	L+2字节，L<2^16
MEDIUMBLOB	中等BLOB	L+3字节，L<2^24
LONGBLOB	非常大的BLOB	L+4字节，L<2^32

- 如何选择数据类型
+ 在任何情况下均应使用最精确的类型
+ 浮点数和定点数：如果进行数值标胶与运算，则选择DECIMAL，浮点数比定点的优势在于，长度一定情况下浮点数表示更大的范围
+ 日期与时间类型：TIMESTAMP日期大小相比于DATETIME而言较小，但TIMESTAMP比DATETIME更方便有效
+ CAHR与VARCHAR，固定长度的处理速度较快，缺点是浪费空间
* 对于MyISAM引擎而言，最好使用固定长度的数据列代替可变长度的数据列，可使整个表静态化，加快检索速度，以空间换时间
* 对于InnoDB引擎，使用VARCHAR比较节省空间

运算符

• 算术运算符：+ - * / %
• 比较运算符：> < = >= <=>（null <=> null 结果为1） <= != <> 以及IN BETWEEN AND IS NULL GREATEST(返回最大值) LEAST（返回最小值） LIKE REGEXP
• 逻辑运算符: NOT ! AND && OR || XOR
• 位操作运算符: & | ~ ^ << >>

函数

数学函数

• `ABD(X) PI() SQRT(x) MOD(x,y) CEIL(x) CEILING(x) FLOOR(x) RAND() RAND(X) ROUND(x) ROUND(x,y) TRUNCATE(x,y) SIGN(x) POW(x,y) POWER(x,y) EXP(x) LOG(x) LOG10(x) RADIANS(x)

字符串函数

• CAHR_LENGTH 字符串字符个数，LENGTH 字符串长度
• CONTACT(s1, s2, ...)连接字符串，如果有一个参数为NULL则结果为NULL， CONTACT_WS(x, s1, s2, ...)第一个参数为分隔符
• INSERT(s1, x, len, s2) 对于s1串从第X位开始，使用s2字符串替换len个长度，如果len大于s2的长度，则返回s1[1,x]+s2字符串
• lower(str) lcase(str) upper(str) ucase(str) 大小写转换
• left(s,n)从s的左边开始长度为n的字符串，right(s,n)从右侧开始长度为n的字符串
• lpad(s1,len,s2) 返回字符串s1,其左边由s2填充**至**len字符长度，如果s1长度大于len，则s1被缩短至len长度；rpad(s1,len,s2)从右填充； select lpad('ss', 5, 'q')->qqqss
• LTRIM(s) RTRIM(s) TRIM(s) 删除左侧、右侧、两侧的空格
• trim(s1 from s2) 删除指定字符串s1的两端中的s2
• repeat(s, n) 返回将s重复n次的字符串
• space(n)返回n个空格组成的字符串
• replace(s, s1, s2) 对于字符串s使用s2替换s1
• strcmp(s1,s2)比较s1和s2的大小，相等返回0；
• substring(s, n[, len]) mid(s, n[, len])从字符串s的第n位开始，截取len个长度; mid和substring结果一致
• locate(s, s1) position(s, s1) instr(s, s1) 返回s1在s中的开始位置
• reverse(s)字符串逆序函数，返回s的反顺序字符串
• elt(n, s1, s2, s3, ... , sm) 若n=1则返回s1,若n=3则返回s3，若n<1或n>m则返回null
• FIELD(s,s1,s2,...,sn) 返回s在列表s1…sn第一次出现的位置
• find_in_set(s1, s2)返回s1在列表s2中出现的位置，s2是由逗号分隔开的字符串列表，若任意一个参数为Null则返回null
• make_set(x, s1, s2, ... , sn) 返回由x的二进制数指定的相应位的字符串组成的字符串

日期和时间函数

• curdate() current_date()两者相同，返回YYYY-MM-DD, CURRENT_DATE()+0将日期类型转换为数值类型
• curtime() current_time()两者相同，返回HH:MM:SS
• CURRENT_TIMESTAMP() LOCALTIME() NOW() SYSDATE()作用相同，返回当前时间YYYY-MM-DD HH:MM:SS
• UNIX_TIMESTAMP([date]) 返回unix时间戳作为无符号整数，FROM_UNIXTIME(date)把unix时间戳转换为普通格式时间，与unix_timestamp互为反函数
• utc_date()返回世界标准时间日期值，其格式为YYYY-MM-DD,utc_time()返回当前时间值,格式为HH:MM:SS
• month(date)返回指定日期的月份[0-12]，monthname(date)返回对应月份的英文全名
• dayname(d)返回d对应的工作日全名[sunday..],dayofweek(d)返回d对应的一周的索引，星期日为1；weekday(d)返回d对应的一周的索引，周一为0
• week(d[,0|1..|7])日期d是一年中的第几周,weekofyear(d)相当于week(d,3)
• dayofyear(d)指定d是一年中的第几天[0~366]， dayofmonth(d)返回指定日期在一个月中的位置[0~31]
• year(d)指定日期对应的年份，quarter(d)指定日期对应的季度[1~4],minute(t)返回time对应的分钟数,second(t)返回time对应的秒数
• extract(type of d) 从指定日期d中提取type信息
• time_to_sec(t)将t转换为秒数， sec_to_time(sec)将秒转换为时分秒
• date_add() adddate() date_sub() subdate()参数为(date, INTERVAL expr type),日期增减，如date_add('2017-01-22 14:26:09', INTERVAL 1 SECOND)
• addtime(date, expr) subtime(time, expr) 对指定date的时间部分进行增减
• datediff(d1, d2)计算两个日期之间间隔的天数
• date_format(date, format), time_format(time, format)将日期或者时间进行格式化;get_format(val_type,format_type)返回字符串显示格式[‘EUR’,’ISO’, ‘JIS’,’USA’,’INTERVAL’]

条件判断含糊

• if(expr, v1, v2) 若expr<>0 或者expr<>null,则返回v1，否则返回v2
• ifnull(v1,v2)如v1不为null则返回v2
• case expr when v1 then r1 [when v2 then r2] [else rn] end

系统信息函数

• version()返回当前mysql版本号
• connection_id()返回mysql当前服务器的连接次数，每个连接的id唯一
• show processlist; show full processlist 输出有哪些线程中在运行，root查看所有，其他用户查看自己占用的连接
• user() current_user system_user() session_user()返回 当前用户名@主机名组合
• charset(str)返回str的字符集，convert(str using CAHRSET)将str转换为指定字符集
• collation(str)返回字符串排列方式
• last_insert_id()返回最后一个insert货update为auto_increment列设置的第一个值（一个sql语句多次插入时只返回第一个id），此值与表无关；

加密函数

• password(s) 单向加密，用于mysql服务权限鉴定，不建议用在个人应用程序；
• md5(str) 计算str的md5值，以32位的16进制字符串返回
• encode(str, pwd_str)使用pwd_str作为密码加密str;decode(cypt_str, pwd_str)使用pwd_str解密cypt_str

其他函数

• format(x, n) 将数字x格式，并以四舍五入保留后n位
• conv(n, from_base, to_base()将n由from_base转换为to_base进制
• inet_aton(ip_str) inet_ntoa(n) ip与数字互换
• get_lock(str, timeout)得到名称为str的锁，持续时间为timeout秒；release_lock(str)释放锁；is_free_lock(str)锁是否空闲;is_used_lock(str)锁是否被占用
• benchmark(count, expr)重复指定expr count次
• convert(str using charset)改变字符串字符集
• convert(x, type) cast(x as type) 将一个类型值转换为另一个类型的值，type有：binary char date time datetime decimal signed unsigned

查询

基本查询与一般sql无异

• 分页查询： select * from tbl limit n,m; 返回第n行之后的m行；

插入、更新、删除

基本操作与一般sql无异

• 多值插入： insert into tab (colume_list) values(value_list),(value_list)...

索引

索引是一个单独的、存储在磁盘上的数据库结构，包含了对数据表中所有记录的引用指针；

mysql中索引存储类型有两种BTREE与HASH,MyISAM 与InnoDB存储引擎只支持BTREE索引，MEMORY 与 HEAP支持BTREE与HASH索引

• 索引优点：

  • 通过创建唯一索引，保证数据表中每一行唯一性
  • 加快查询速度
  • 加快表与表之间的连接
  • 使用分组和排序子句进行查询时，显著降低分组和排序时间

• 索引的缺点

  • 创建索引和维护索引需要耗费时间
  • 索引需要占用磁盘空间
  • 降低数据的维护速度

• 索引的分类

  • 普通索引和唯一索引：普通索引允许索引列插入重复值和空值；唯一索引 索引列的值必须唯一，但允许有空值；主键索引不允许有空值
  • 单列索引和组合索引:组合索引只有在查询条件中是用来左边的字段时，索引才会被使用；使用组合索引时遵循最左前缀集合；
  • 全文索引:支持全文查找，允许空值和重复值，可以再char varchar text上建立索引，只有MyISAM引擎支持
  • 空间索引：对空间类型的字段建立索引，空间数据类型GEOMETRY POINT LINESTRING POLYGON 只有MyISAM引擎支持；

• 索引设计原则

  • 索引并非越多越好；
  • 避免对经常更新的表进行过多的索引；
  • 数据量小的表最好不要用索引；
  • 经常用到的重复读较低的列建立索引；
  • 当列符合唯一性时建立唯一索引；
  • 在频繁排序和分组的列上建立索引；

• 基于建表语句创建索引：

  • 创建普通索引：create table tb_name (fname dtype index(fname))
  • 创建唯一索引：create table tb_name (fname dtype unique index inx_name(fname))
  • 创建组合索引：create table tb_name (fname dtype not null, f2 dtype not null, f3 dtype, index inx_name(fname, f2, f3(10)))
  • 创建全文索引：create table tb_name (fname text fulltext index inx_name(fname)) engines=MyISAM

• 基于已有表创建索引

  • alter table tb_name add [unique|fulltext|spatial] [index | key] [index_name] (cols[(len), ...]) [asc | desc]
  • create [unique|fulltext|spatial] index [index_name] on tb_name (cols[(len), ...]) [asc | desc]

• 删除索引

  • alter table tb_name drop index idx_name
  • drop index idx_name on tb_name

视图

视图的优点：简单化、安全性、逻辑数据独立性

• 创建视图 create [or replace] [algorithm={undefined | merge | temptable}] view db_name.view_name [(col_list)] as select_sta [with [cascade | local] check option]

• 查看视图

  • show table status like 'view_name' \G;
  • show create view 'view_name' \G
  • select * from information_schema.views

• 删除视图 drop view [if exists] view_name [restrict | cascade]

参考资料《mysql5.7 从入门到精通》