《MySQL 深入浅出》 1-17章节阅读整理

2022-01-11 06:38:20

链接：http://blog.itpub.net/28602568/viewspace-1622174/

标题：《MySQL 深入浅出》 1-17章节阅读整理

** 以下提到oracle部分只是对比扩展，本文重点是《MySQL 深入浅出》书中1-17章节个人觉得需要提笔一记的知识点整理。

** 该书讲解 VERSION() ==>’5.0.18-nt‘ ，默认引擎 : MyISAM

1、获取建表语句

mysql --> show create table emp \G;

oracle --> SELECT DBMS_METADATA.GET_DDL ('TABLE', '表名',user ) FROM DUAL;

2、一条insert 多行value值

mysql --> insert into table(id,name) values (1,'a'),(2,'b'),(3,'c');

oracle --> insert into table(id,name) select 1,'a' from dual union all select 2,'b' from dual union all select 3,'c' from dual ; 或者写多条insert语句；

3、限制/范围行数

mysql --> limit [offset_start,row_count] offset_start 表示记录的起始偏移量{默认0}，row_count 表示显示的行数

oracle --> rownum = n Oracle rownum 分页引起的效率问题及优化思路

4、更改表字段和表名字：

改字段名：

oracle：alter table emp rename column age to age1;

mysql ：alter table emp change age age1 int (4) ;

oracle、mysql modify 都不能需要字段名称。

改表名字：oracle 和mysql 都是用rename emp to emp1或者 alter table emp rename to emp1 实现.

5、执行sql/存储有警告/报错时查看warning/error 详细信息

mysql --> show warnings;

oracle --> "SQL>show errors" 、 OS层$ oerr ora 00922

6、表结构设置 id int(5) zerofill 属性，再insert 1111111位数为 7 不会报错，因为zerofill后，宽度格式限制失效，且也不会有填充0部分.

7、字段雷系TIMESTAMP(tm )，增加时，系统会自动给第一个timestamp字段授予默认值 CURRENT_TIMESTAMP（系统日期），且可以直接写定制19700101080001数字形式 ....

8、now()函数代表当前日期

9、year 年份部分 00 "到"69" 范围的值被转换为 2000～2069 范围的 YEAR 值、"70" 到“99”范围的值被转换为 1970～1999 范围的 YEAR 值

10、mysql 3中注释符

1）#内容
2）/*内容*/;
3）-- 内容 (提示--后要有空格)

11、 mysql 通过命令直接执行语句

[root@lottery ~]# mysql -uroot -p11 -N -e "use diamond; show tables;"

+-------------+

| config_info |

+-------------+

[root@lottery ~]#

-e 后面跟上要执行的 SQL语句

-N 参数是不显示表头

25章有详细介绍。

--------------------------------------------------------------以上为小知识点，小笔记------------------------------------------------------------------------------------------------------

一、

MySQL 中的字符类型

字符串类型	描述及存储需求
CHAR （ M）	M 为 0 ～ 255 之间的整数
VARCHAR （ M）	M 为 0 ～ 65535 之间的整数，值的长度 +1 个字节
TINYBLOB	允许长度 0 ～255 字节，值的长度 +1 个字节
BLOB	允许长度 0 ～65535 字节，值的长度 +2 个字节
MEDIUMBLOB	允许长度 0 ～167772150 字节，值的长度 +3 个字节
LONGBLOB	允许长度 0 ～4294967295 字节，值的长度 +4 个字节
TINYTEXT	允许长度 0 ～255 字节，值的长度 +2 个字节
TEXT	允许长度 0 ～65535 字节，值的长度 +2 个字节
MEDIUMTEXT	允许长度 0 ～167772150 字节，值的长度 +3 个字节
LONGTEXT	允许长度 0 ～4294967295 字节，值的长度 +4 个字节
VARBINARY（M）	允许长度 0 ～M 个字节的变长字节字符串，值的长度 +1 个字节
BINARY （ M）	允许长度 0 ～M 个字节的定长字节字符串

char 和varchar 类型

char(2)

insert '1' oracle数据库 length=2，mysql数据库 length=1，

insert '1空格' oracle length=2，mysql=1

varchar(2)

insert '1' oracle length=1，mysql=1 ，

insert '1空格' oracle length=2，mysql=2

二、

第四章、MySQL 中的运算符

算术运算符
运算符	作用
+	加法
-	减法
*	乘法
/，DIV	除法，返回商
%，MOD	除法，返回余数
MySQL 支持的比较运算符
运算符	作用
=	等于
<>或!=	不等于
<=>	NULL 安全的等于(NULL-safe)
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	存在与指定范围
IN	存在于指定集合
IS NULL	为 NULL
IS NOT NULL	不为 NULL
LIKE	通配符匹配
REGEXP 或 RLIKE	正则表达式匹配
MySQL 中的逻辑运算符
运算符	作用
NOT 或！	逻辑非
AND 或&&	逻辑与
OR 或 \|\|	逻辑或
XOR	逻辑异或
MySQL 支持的位运算符
运算符	作用
&	位与（位 AND）
\|	位或（位 OR ）
^	位异或（位 XOR）
~	位取反
>>	位右移
<<	位左移
MySQL 中的运算符优先级
运算符	优先级顺序
:=	1
\|\|, OR, XOR	2
&&, AND	3
NOT	4
BETWEEN, CASE, WHEN, THEN, ELSE	5
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN	6
\|	7
&	8
<<, >>	9
-, +	10
*, /, DIV, %, MOD	11
^	12
- (一元减号), ~ (一元比特反转)	13
!	14

*** 实际上，我们都是用 “（）” 来将需要优先的操作括起来，既起到优先作用也方便看.

三、

第五章、MySQL常用函数

MySQL 中的常用字符串函数
函数	功能
CANCAT(S1,S2,…Sn)	连接 S1,S2,…Sn 为一个字符串
INSERT(str,x,y,instr)	将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr
LOWER(str)	将字符串 str 中所有字符变为小写
UPPER(str)	将字符串 str 中所有字符变为大写
LEFT(str ,x)	返回字符串 str 最左边的 x 个字符
RIGHT(str,x)	返回字符串 str 最右边的 x 个字符
LPAD(str,n ,pad)	用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度
RPAD(str,n,pad)	用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度
LTRIM(str)	去掉字符串 str 左侧的空格
RTRIM(str)	去掉字符串 str 行尾的空格
REPEAT(str,x)	返回 str 重复 x 次的结果
REPLACE(str,a,b)	用字符串 b 替换字符串 str 中所有出现的字符串 a
STRCMP(s1,s2)	比较字符串 s1 和 s2
TRIM(str)	去掉字符串行尾和行头的空格
SUBSTRING(str,x,y)	返回从字符串 str x 位置起 y 个字符长度的字串
MySQL 中的常用数值函数
函数	功能
ABS(x)	返回 x 的绝对值
CEIL(x)	返回大于x的最大整数值
FLOOR(x)	返回小于 x 的最大整数值
MOD(x，y)	返回 x/y 的模
RAND()	返回 0 到 1 内的随机值
ROUND(x,y)	返回参数 x 的四舍五入的有 y 位小数的值
TRUNCATE(x,y)	返回数字 x 截断为 y 位小数的结果
MySQL 中的常用日期时间函数
函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期 date 的 UNIX 时间戳
FROM_UNIXTIME	返回 UNIX 时间戳的日期值
WEEK(date)	返回日期 date 为一年中的第几周
YEAR(date)	返回日期 date 的年份
HOUR(time)	返回 time 的小时值
MINUTE(time)	返回 time 的分钟值
MONTHNAME(date)	返回 date 的月份名
DATE_FORMAT(date,fmt)	返回按字符串 fmt 格式化日期 date 值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间 expr 和结束时间 expr2 之间的天数
MySQL 中的日期时间格式
格式符	格式说明
%S,%s	两位数字形式的秒（00,01,...,59）
%i	两位数字形式的分（00,01,...,59）
%H	两位数字形式的小时，24 小时（00,01,...,23）
%h,%I	两位数字形式的小时，12 小时（01,02,...,12）
%k	数字形式的小时，24 小时（0,1,...,23）
%l	数字形式的小时，12 小时（1,2,...,12）
%T	24 小时的时间形式（hh:mm:ss）
%r	12 小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）
%p	AM 或 PM
%W	一周中每一天的名称（Sunday,Monday,...,Saturday）
%a	一周中每一天名称的缩写（Sun,Mon,...,Sat）
%d	两位数字表示月中的天数（00,01,...,31）
%e	数字形式表示月中的天数（1,2，...,31）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）
%w	以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）
%j	以3位数字表示年中的天数（001,002,...,366）
%U	周（0,1,52），其中 Sunday 为周中的第一天
%u	周（0,1,52），其中 Monday 为周中的第一天
%M	月名（January,February,...,December）
%b	缩写的月名（January,February,...,December）
%m	两位数字表示的月份（01,02,...,12）
%c	数字表示的月份（1,2,...,12）
%Y	4位数字表示的年份
%y	两位数字表示的年份
%%	直接值“%”
MySQL 中的日期间隔类型
表达式类型	描述	格式
HOUR	小时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
YEAR_MONTH	年和月	YY-MM
DAY_HOUR	日和小时	DD hh
DAY_MINUTE	日和分钟	DD hh:mm
DAY_ SECOND	日和秒	DD hh:mm:ss
HOUR_MINUTE	小时和分	hh:mm
HOUR_SECOND	小时和秒	hh:ss
MINUTE_SECOND	分钟和秒	mm:ss
MySQL 中的流程函数
函数	功能
IF(value,t f)	如果 value 是真，返回 t；否则返回 f
IFNULL(value1,value2)	如果 value1 不为空返回 value1，否则返回 value2
CASE WHEN [value1]	如果 value1 是真，返回 result1，否则返回 default
THEN[result1]…ELSE[default]END	如果 value1 是真，返回 result1，否则返回 default
CASE [expr] WHEN [value1]	如果 expr 等于 value1，返回 result1，否则返回 default
THEN[result1]…ELSE[default]END	如果 expr 等于 value1，返回 result1，否则返回 default
MySQL 中的其他常用函数
函数	功能
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(IP)	返回 IP 地址的数字表示
INET_NTOA(num)	返回数字代表的 IP 地址
PASSWORD(str)	返回字符串 str 的加密版本
MD5()	返回字符串 str 的 MD5 值

第17章正则表达式中的模式

** 和上文函数可一起使用特此一起记录
序列	序列说明
^	在字符串的开始处进行匹配
$	在字符串的末尾处进行匹配
.	匹配任意单个字符，包括换行符
[…]	匹配出括号内的任意字符
[^…]	匹配不出括号内的任意字符
a*	匹配零个或多个 a（包括空串）
a+	匹配 1 个或多个 a（不包括空串）
a？	匹配 1 个或零个 a
a1\|a2	匹配 a1 或 a2
a(m)	匹配 m 个 a
a(m,)	匹配 m 个或更多个 a
a(m,n)	匹配 m 到 n 个 a
a(,n)	匹配 0 到 n 个 a
(…..)	将模式元素组成单一元素

四、

第6章图形化工具的使用

提供的功能包括启动关闭数据库、连接管理、健康检查、备份管理、Catalogs 管理、用户管理、参数配置、数据库对象管理、备份恢复管理等

1）连接管理：查看当前活跃的数据库连接，与 SHOW PROCESSLIST 命令的执行结果相同

2）健康检查：数据库连接的变化情况、SQL 查询执行的数量、缓冲区的命中率等

3）备份管理
备份执行方法的选项进行说明。
? InnoDB Online Backup：为了确保 InnoDB 表备份结果的数据的一致性，会在备份开始的时候启动一个事务，推荐只在备份 InnoDB 类型的表时使用。
? Lock all tables：为了确保 MyISAM 表备份结果的数据的一致性，会在备份开始时对本次要备份的表执行 Lock 操作，防止新的数据写入。这样在备份期间会阻塞表的更新，对于更新密集型的应用要谨慎选择使用这个选项。
? Online with binlog pos：除了实现和 InnoDB Online Backup 相同的功能外，还记录了当前 Binlog 的位置，便于恢复时知道需要恢复的 Binlog 的起点。
? Normal backup：只在备份每个表的时候才锁定该表，这种情况下对应用的影响是最小的，但是相应的这种备份方法生成的备份结果中，表间的数据一致性是最没有保障的，选择这种备份方式前要考虑到这个问题，通过选择合适的时间执行备份操作来减小备份数据不一致的风险。
? Complete backup：选定数据库的全备份，而忽视前面选择的表的列表。这种方式对于数据库中的表定期增加的情况非常有用，省去每次修改备份列表的工作
注意：控制台的备份管理提供的选项比命令行少很多，例如不能指定导出字符集，不能指定导出记录的 WHERE 条件，也不能按照指定的分隔符和换行符导出数据成文本文件。所以如果用户需要更高级的数据备份功能，还是要熟练掌握 mysqldump 的各个命令行参数

4）Catalogs
控制台提供的管理表、索引、视图和过程的工具。可以用来查询和修改已有的数据库对象或者创建新的数据库对象，取决于连接的用户所拥有的权限。=> 类似SHOW TABLE STATUS 命令行显示， show table status 类似oracle的user_tables 里的(rows,data_length,max_data_length,comment等)
注意：对分区的支持还不是很好，新建/修改分区表，都没有可以指定分区特性的地方，所以如果你需要创建和维护分区表，那么暂时还只能通过命令行来创建。

5）MySQL Query Brower 提供的工具包括数据的查询、更新和 SQL 的优化分析，虽然对于 MySQL Query Brower 提供的大多数功能，用字符界面的 MySQL 客户端都可以实现，但是相比之下， Query Brower 返回的结果更直观，在查询的列非常多的时候不会因为内容换行而使结果看起来很让人困惑。此外，对于查询的返回结果，可以选择导出成 XML、HTML、EXCEL、CVS 等多种格式，相比命令行的方式更灵活和方便。

6）phpMyAdmin（简称 PMA）
一个用 PHP 编写的、可以通过 Web 控制和操作 MySQL 数据库的工具，功能非常全面，包括数据库管理、数据对象管理、用户管理、数据导入导出、数据库管理、数据管理等

五、

第7章表类型（存储引擎）的选择
1) MySQL 5.0 支持的存储引擎包括 MyISAM/InnoDB/BDB/MEMORY/MERGE/EXAMPLE/NDB/Cluster/ ARCHIVE/CSV/BLACKHOLE/FEDERATED 等，其中 InnoDB 和 BDB 提供事务安全表，其他存储引擎都是非事务安全表。
2) 查看mysql当前默认的存储引擎: show variables like '%storage_engine%';
3) 查询当前数据库支持的存储引擎的2种方式。 1. mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ \| Engine \| Support \| Comment \| Transactions \| XA \| Savepoints \| +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ \| PERFORMANCE_SCHEMA \| YES \| Performance Schema \| NO \| NO \| NO \| \| CSV \| YES \| CSV storage engine \| NO \| NO \| NO \| \| MRG_MYISAM \| YES \| Collection of identical MyISAM tables \| NO \| NO \| NO \| \| BLACKHOLE \| YES \| /dev/null storage engine (anything you write to it disappears) \| NO \| NO \| NO \| \| MyISAM \| YES \| MyISAM storage engine \| NO \| NO \| NO \| \| MEMORY \| YES \| Hash based, stored in memory, useful for temporary tables \| NO \| NO \| NO \| \| ARCHIVE \| YES \| Archive storage engine \| NO \| NO \| NO \| \| InnoDB \| DEFAULT \| Supports transactions, row-level locking, and foreign keys \| YES \| YES \| YES \| \| FEDERATED \| NO \| Federated MySQL storage engine \| NULL \| NULL \| NULL \| +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ mysql> 2. SHOW VARIABLES LIKE 'have%';
4) 更改表的存储引擎: alter table table_name engine = innodb;
5) 常用存储引擎的对比
特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B 树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持
mysql 非自动提交设置注意事项 (mysql默认是自动提交，根据业务去更改) 若表引擎不是innodb，即使设置=0 ，每执行也都会自动提交；若innodb引擎，set auto_commit =1就会自动提交，=0就要手动commit；

InnoDB 表的自动增长列（关键字autoincre_demo）

insert 自动增长列可以按照自动增长属性 insert数据每行+1，也可以手动写定值，但当定值为 null或者0时，会按照自动增加列的max(id)+1增长.定值部分还是会正常insert.;

例如：若max(id)=300, 执行insert .. values(103,'1'),(0,'2'),(null,'3'); 对应insert成的数据为： (103,'1'),(301,'2'),(302,'3');

通过ALTER TABLE *** AUTO_INCREMENT = n; 语句强制设置自动增长列的初识值，默认从 1 开始，

但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要在数据库启动以后重新设置。

可以使用select LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入了多条记录，那么返回的是第一条记录使用的自动增长值。下面的例子演示了使用 LAST_INSERT_ID() 的情况：

MEMORY 存储引擎

使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。给 MEMORY 表创建索引的时候，可以指定使用 HASH 索引还是 BTREE 索引：create index 索引名 USING HASH/BTREE on 表(列) ;

每个 MEMORY 表中可以放置的数据量的大小，受到 max_heap_table_size 系统变量的约束，这个系统变量的初始值是 16MB，可以按照需要加大。此外，在定义 MEMORY 表的时候，可以通过 MAX_ROWS 子句指定表的最大行数。

下面是常用存储引擎的适用环境。

** MyISAM ：默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。 MyISAM 是在 Web 、数据仓储和其他应用环境下最常使用的存储引擎之一。

** InnoDB ：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（ Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统， InnoDB 都是合适的选择。

** MEMORY ：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。 MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

** MERGE ：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。 MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善 MERGE表的访问效率。这对于诸如数据仓储等 VLDB 环境十分适合。

注意：以上只是我们按照实施经验提出的关于存储引擎选择的一些建议，但是不同应用的特点是千差万别的，选择使用哪种存储引擎才是最佳方案也不是绝对的，这需要根据用户各自的应用进行测试，从而得到最适合自己的结果。

六、

第8章选择合适的数据类型
CHAR 和 VARCHAR
CHAR 和 VARCHAR 类型类似，都用来存储字符串，但它们保存和检索的方式不同。CHAR 属于固定长度的字符类型，而 VARCHAR 属于可变长度的字符类型。
CHAR 和 VARCHAR 对比
值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	' '	4 个字节	''	1 个字节
'ab'	'ab '	4 个字节	'ab '	3 个字节
'abcd'	'abcd'	4 个字节	'abcd'	5 个字节
'abcdefgh'	'abcd'	4 个字节	'abcd'	5 个字节
简单概括不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同。 MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。 MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或 VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。 InnoDB 存储引擎：建议使用 VARCHAR 类型。对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于 CHAR 平均占用的空间多于 VARCHAR，因此使用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。
TEXT 与 BLOB
TEXT 或者 BLOB用来保存较大文本；二者间主要差别是： BLOB 能用来保存二进制数据，比如照片；而 TEXT 只能保存字符数据，比如一篇文章或者日记。 TEXT 和 BLOB 中有分别包括 TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型；它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足需求的最小存储类型。 BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。注释：OPTIMIZE TABLE会产生锁表, mysql 空洞类似oracle 高水位，oracle 通过move 等形式解决
浮点数与定点数
float、double（或 real）表示浮点数。
decimal（或 numberic）表示定点数
注意：在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则：浮点数存在误差问题；对货币等对精度敏感的数据，应该用定点数表示或存储；在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；要注意浮点数中一些特殊值的处理。

七、

第9章字符集

常用字符集比较

字符集	是否定长	编码方式	其他说明
ACSII	是	单字节 7 位编码	最早的奠基性字符集
ISO-8859-1/latin1	是	单字节 8 位编码	西欧字符集，经常被一些程序员用来转码
GB2312-80	是	双字节编码	早期标准，不推荐再使用
GBK	是	双字节编码	虽然不是国标，但支持的系统不少
GB18030	否	2 字节或 4 字节编码	开始有一些支持，但数据库支持的还少见
UTF-32	是	4 字节编码	UCS-4 原始编码，目前很少采用
UCS-2	是	2 字节编码	Windows 2000 内部用 UCS-2
UTF-16	否	2 字节或 4 字节编码	Java 和 Windows XP/NT 等内部使用 UTF-16
UTF-8	否	1 至 4 字节编码	互联网和UNIX/Linux广泛支持的Unicode字符集
UTF-8	否	1 至 4 字节编码	MySQLServer 也使用 UTF-8

查询当前服务器的字符集和校对规则：
show variables like 'character_set_server';

设置/更换字符集：
1/ my.cnf中设置：
[mysqld] default-character-set=gbk
2/ 启动选项中指定：
mysqld --default-character-set=gbk
3/ 在编译的时候指定：
./configure --with-charset=gbk

参数：character_set_client、 character_set_connection 和 character_set_results，分别代表客户端、连接和返回结果的字符集

八、

BTREE 索引与 HASH 索引

两种不同类型的索引各有其不同的适用范围。HASH 索引有一些重要的特征需要在使用的时候特别注意，如下所示。
只用于使用=或<=>操作符的等式比较。
优化器不能使用 HASH 索引来加速 ORDER BY 操作。
MySQL 不能确定在两个值之间大约有多少行。如果将一个 MyISAM 表改为 HASH 索引的 MEMORY 表，会影响一些查询的执行效率。
只能使用整个关键字来搜索一行。
而对于 BTREE 索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者 LIKE 'pattern'（其中'pattern'不以通配符开始）操作符时，都可以使用相关列上的索引。
例子：
下列范围查询适用于 BTREE 索引和 HASH 索引：
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
下列范围查询只适用于 BTREE 索引：
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa' AND 'simon';

view

使用 CREATE OR REPLACE 或者 ALTER 修改视图

显示用户下多少view ：show tables;

查看视图状态 : show table status like 'staff_list' ;

查看视图创建语句： show create view staff_list;

查看存储状态： show procedure status like 'film_in_stock';

查看存储创建语句： SHOW CREATE {PROCEDURE | FUNCTION} sp_name ;

通过查看 information_schema. Routines 了解存储过程和函数的详细信息：
select * from Routines where ROUTINE_NAME = 'film_in_stock';

*** 怎么写存储/函数一些语法等，语法太多，在此不做说明，若有需要请自己查找相关资料

九、

第14章事务控制和锁定语句

LOCK TABLE 和 UNLOCK TABLE
LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个 LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁

一个获得表锁和释放表锁的简单例子


session_1	session_2
获得表film_text的READ锁定
mysql> lock table film_text read;
Query OK, 0 rows affected (0.00 sec)
当前session可以查询该表记录 mysql> select film_id,title from film_text where film_id = 1001; +---------+------------------+ \| film_id \| title \| +---------+------------------+ \| 1001 \| ACADEMY DINOSAUR \| +---------+------------------+ 1 row in set (0.00 sec)	其他session也可以查询该表的记录 mysql> select film_id,title from film_text where film_id = 1001; +---------+------------------+ \| film_id \| title \| +---------+------------------+ \| 1001 \| ACADEMY DINOSAUR \| +---------+------------------+ 1 row in set (0.00 sec)
	其他 session 更新锁定表会等待获得锁： mysql> update film_text set title = 'Test' where film_id = 1001; 等待
释放锁 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)	等待
	Session 获得锁，更新操作完成： mysql> update film_text set title = 'Test' where film_id = 1001; Query OK, 1 row affected (1 min 0.71 sec) Rows matched: 1 Changed: 1 Warnings: 0

事物控制：
MySQL 通过 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等语句支持本地事务，具体语法如下。
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

MySQL 默认是自动提交（Autocommit）的，可用Commit 和 Rollback 来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和 Oracle 的事务管理明显不同的地方。如果应用是从 Oracle 数据库迁移到 MySQL 数据库，则需要确保应用中是否对事务进行了明确的管理。
START TRANSACTION 或 BEGIN 语句可以开始一项新的事务。
COMMIT 和 ROLLBACK 用来提交或者回滚事务。
CHAIN 和 RELEASE 子句分别用来定义在事务提交或者回滚之后的操作，CHAIN 会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接. ? SET AUTOCOMMIT 可以修改当前连接的提交方式，如果设置了 SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。
如果只是对某些语句需要进行事务控制，则使用 START TRANSACTION 语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改 AUTOCOMMIT 来控制事务比较方便，这样不用在每个事务开始的时候再执行 START TRANSACTION 语句。

***** 在此只摘取重要说明部分，较多例子在此未作声明，若想了解请自己查找相关资料

十、

SQL MODE

MySQL 5.0 上, SQL Mode（sql_mode 参数）默认为REAL_AS_FLOAT/PIPES_AS_CONCAT/ANSI_QUOTES/GNORE_SPACE 和 ANSI；
在这种模式下允许插入超过字段长度的值，只是在插入后， MySQL 会返回一个 warning 。通过修改 sql_mode 为 STRICT_TRANS_TABLES（严格模式）实现了数据的严格校验，使错误数据不能插入表中，从而保证了数据的准确性，具体实现如下。

查看默认 SQL Mode 的命令如下：

mysql> select @@sql_mode;

sql_mode 的一种修改方法，即 SET [SESSION|GLOBAL] sql_mode='modes'，其中 SESSION 选项表示只在本次连接中生效；而 GLOBAL 选项表示在本次连接中并不生效，而对于新的连接则生效，这种方法在 MySQL 4.1 开始有效。另外，也可以通过使用“--sql-mode="modes"”选项，在 MySQL 启动时设置 sql_mode。

详见博客 MySQL数据类型：SQL_MODE设置不容忽视

MySQL 中的 SQL Mode

sql_mode值	描述
ANSI	等同于 REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE 和 ANSI
ANSI	组合模式，这种模式使语法和行为更符合标准的 SQL
STRICT_TRANS_TABLES	STRICT_TRANS_TABLES 适用于事务表和非事务表，它是严格模式，不允许非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值给出错误而不是警告

TRADITIONAL	TRADITIONAL 模式等同于 STRICT_TRANS_TABLES、STRICT_ALL_TABLES、

MySQL 中的常用数据库 Mode
组合后的模式名称	组合模式中的各个sql_mode
DB2	PIPES_AS_CONCAT 、 ANSI_QUOTES 、 IGNORE_SPACE 、 NO_KEY_OPTIONS
DB2	NO_TABLE_OPTIONS、NO_FIELD_OPTIONS
MAXDB	PIPES_AS_CONCAT 、 ANSI_QUOTES 、 IGNORE_SPACE 、 NO_KEY_OPTIONS
MAXDB	NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER
MSSQL	PIPES_AS_CONCAT 、 ANSI_QUOTES 、 IGNORE_SPACE 、 NO_KEY_OPTIONS
MSSQL	NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS
ORACLE	PIPES_AS_CONCAT 、 ANSI_QUOTES 、 IGNORE_SPACE 、 NO_KEY_OPTIONS
ORACLE	NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER
POSTGRESQL	PIPES_AS_CONCAT 、 ANSI_QUOTES 、 IGNORE_SPACE 、 NO_KEY_OPTIONS
POSTGRESQL	NO_TABLE_OPTIONS、NO_FIELD_OPTIONS

在数据迁移过程中，可以设置 SQL Mode 为 NO_TABLE_OPTIONS 模式，这样将去掉 show create table 中的“engine”关键字，获得通用的建表脚本。

测试实例如下：
mysql > show create table emp \ G;
CREATE TABLE `emp `
(`ename ` varchar( 20 ) DEFAULT NULL )
ENGINE =InnoDB DEFAULT CHARSET =gbk ;
mysql> set session sql_mode ='NO_TABLE_OPTIONS' ;
mysql > show create table emp \G ;
CREATE TABLE `emp ` (`ename ` varchar( 20) DEFAULT NULL );

从第18章SQL 优化开始往后每章节都需要仔细阅读书籍和资料。由于知识点过多，所以在此先不做整理。

后续可能会对一些点做整理另发博客。

码农公寓

相关文章