《深入浅出MySQL》读书拾遗-基础开发

一、    MySQL入门

1. DDL(Data Definition Languages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。
2. DML(Data Manipulation Languages)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据库完整性。常用的语句关键字主要包括insert、delete、update和select等。
3. DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。
4. change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不方便。但是change的优点是可以修改列名称，modify则不能。

二、    MySQL支持的数据类型

1. MySQL支持的数值类别有TINYINT, SMALLINT, MEDIUMINT, INT/INTEGER, BIGINT, FLOAT, DOUBLE, DEC(M,D)/DECIMAL(M,D), BIT(M)
2. 在5个整数类型中，如果超出类型范围的操作，会发生“Out of range”的错误提示。
3. 对于整型数据，MySQL还支持在类型名称后面的小括号内指定显示宽度，例如int(5)表示当数值小于5位的时候在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。一般配合zerofill使用，zerofill就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。
4. 一个表中最多只能有一个AUTO_INCREMENT列。对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL，并定义为PRIMARY KEY或定义为UNIQUE键。
5. 浮点数(Float/Double)和定点数(Decimal)都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一花显示M位数字(整数位+小数位)，其中D位位于小数点后面，M和D又称为精度和标度。
6. 浮点数如果不写精度和，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值DECIMAL(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。
7. BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()(显示为二进制格式)或者hex()(显示为十六进制格式)函数进行读取。
8. 数据插入BIT类别字段时，首先转换为二进制，如果位数允许，将成功插入；如果位数小于实际定义的数，则插入失败。如果长度过长则直接所有位填充1(自己测试的)。
9. MySQL中的日期和时间类型有DATE, DATETIME, TIMESTAMP, TIME, YEAR。每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下，系统会进行错误提示，并将以零值来进行存储。
10. MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值，因为MySQL规定TIMESTAMP类型字段只能有一列的默认值为CURRENT_TIMESTAMP。
11. TIMESTAMP还有一个重要特点，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。
12. TIMESTAMP的取值范围为19700101080001到2038年的某一天。插入的数值超出下限和上限都会出现警告，并插入0值。
13. DATETIME的格式允许不严格语法：任何标点符都可以用做日期部分或时间部分之间的间隔符。
14. MySQL中支持字符类型有，CHAR(M), VARCHAR(M), TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, VARBINARY(M), BINARY(M)。
15. CHAR列的长度固定为创建表时声明的长度，长度可以从0~255的任何值；而VARCHAR列中的值为可变长字符串，长度可以指定为0    ~255(5.0.3以前)或65535(5.0.3之后)之间的值。在检索的时候，CHAR列删除尾部的空格，而VARCHAR则保留这些空格。
16. BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。当保存BINARY值，在值的最后通过填充“0X00”(零字节)以达到指定的字段长度。

三、   常用函数

1. 字符串函数：
   • CONCAT(S1,S2,…,Sn)：把传入的参数连接成为一个字符串
   • INSERT(str, x, y, instr)：把字符串str从第x位置开始，y个字符长的子串替换为字符串instr
   • LOWER(str)和UPPER(STR)：把字符串的大小写转换
   • LEFT(str, x)和RIGHT(str, x)：返回字符串最左边的x个字符和最右边的x个字符。如果第二个参数为NULL，那么将不返回任何字符串
   • LPAD(str, x, pad)和RPAD(str, n, pad)：用字符串pad对str最左边和最右边进行填充，直到长度为n个字符长度
   • LTRIM(str)和RTRIM(str)：去年字符串str左侧和右侧空格
   • REPEAT(str, x)：返回str重复x次的結果
   • REPLACE(str, a, b)：用字符串b替换字符串str中所有出现的字符串a
   • STRCMP(s1, s2)：比较字符串s1和s2的ASCII码值的大小
   • TRIM(str)：去掉目标字符吕的开头和结尾的空格
   • SUBSTRING(str, x, y)：返回从字符串str中的第x位置起y个字符长度的字串
2. 数值函数：
   • ABS(x)：返回x的绝对值
   • CEIL(x)：返回大于x的最小整数
   • FLOOR(x)：返回小于x的最大整数
   • MOD(x)：返回x/y的模
   • RAND()：返回0～1内的随机值
   • ROUND(x, y)：返回参数x的四舍五入的有y位小数的值
   • TRUNCATE(x, y)：返回数字x截断为y位小数的結果
3. 日期和时间函数：
   • CURDATE()：返回当前日期，只包含年月日
   • CURTIME()：返回当前时间，只包含时分秒
   • NOW()：返回当前的日期和时间，年月日时分秒都包含
   • UNIX_TIMESTAMP(date)：返回日期date的UNIX时间戳
   • FROM_UNIXTIME(unixtime)：返回UNIXTIME时间戳的日期值，和UNIX_TIMESTAMP(date)互为逆操作
   • WEEK(DATE)和YEAR(DATE)：前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年
   • HOUR(time)和MINUTE(time)：前者返回所给时间的小时，后者返回所给时间的分钟
   • MONTHNAME(date)：返回date的英文月份名称
   • DATE_FORMAT(date, fmt)：按字符串fmt格式化日期date值
   • DATE_ADD(date, INTERVAL expr type)：返回与所给日期date相差INTERVAL时间段的日期
   • DATEDIFF(date1, date2)：用来计算两个日期之间相关的天数
4. 其他函数
   • DATABASE()：返回当前数据库名
   • VERSION()：返回当前数据库版本
   • USER()：返回当前登录用户名
   • INET_ATON(IP)：返回IP地址的网络字节序表示
   • INET_NTOA(num)：返回网络字节序代表的IP地址
   • PASSWORD(str)：返回字符串str的加密版本，一个41位长的字符串
   • MD5(str)：返回字符串str的MD5值，常用来对应中的数据进行加密

四、   存储引擎

1. MySQL 5.0支持的存储引擎包括 MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表。
2. 如果需要修改默认的存储引擎，则可以在参数文件中设置default-table-type。查看当前的默认存储引擎，可用命令”show variables like ‘table_type’”。
3. 查询当前数据库支持的存储引擎有两种方法。第一种为，”show engines”，第二种为，”show variables like ‘have%’”。
4. MyISAM
   • MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。
   • 每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是：.frm(存储表定义)、MYD(MYData，存储数据)、MYI(MYIndex，存储索引)。
   • MyISAM的表还支持3种不同的存储格式，分别是静态(固定长度)表、动态表和压缩表。
5. InnoDB
   • InnoDB表的自动增长列可以手工插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。
   • 可以通过”ALTER TABLE *** AUTO_INCREMENT=n;”語句强制设置自动增长列的初识值，默认从1开始，但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要在数据库启动以后重新设置。
   • 可以使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入了多条记录，那么返回的是第一条记录使用的自动增长值。
   • 自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引前几列排序后在同值的类里面递值的。
   • 使用共享表存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
   • 使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.idb中。
   • 多表空间的参数生效后，只对新建的表生效，其表的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的最大限制、扩展大小等参数。
6. MEMORY
   • 每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
   • 每个MEMORY表中可以放置的数据量的大小，受到max_heap_table_size系统变量的約束，这个系统变量的初始值是16MB，可以按照需要加大。
7. MERGE
   • MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的MyISAM表进行的。
   • MERGE表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm文件存储表定义，另一个.MRG文件包含组合表的信息，包括MERGE表由哪些表组成、插入新的数据时的依据。可以通过修改.MRG文件来修改MERGE表，但是修改后要通过FLUSH TABLES刷新。

五、  字符集

1. 查看所有可用的字符集的命令是”show character set”或者”desc information_schema.character_sets”。
2. 字符集是用来定义MySQL存储字符串的方式，校对规则是定义了比较字符串的方式。
3. 每个字符集至少对应一个校对规则。可以用”show collation like ‘utf-8/gbk/***’”命令或者查看information_schema.COLLATIONS。
4. 校对规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元，即比较是基于字符编码的值而与language无关)结束。
5. MySQL的字符集和校对规则有４个级别的默认设置：服务器级、数据库级、表级和字段级。
6. 服务器字符集和校对，在MySQL服务启动的时候确定。可以在my.cnf中设置：default-character-set=gbk。或者在启动选项中指定：mysqld –default-character-set=gbk。或者在编译的时候指定：./configure –with-charaset=gbk。
7. 如果没有特别的指定服务器字符集，默认使用latin1作为服务器字符集。可以通过”show variables like ‘character_set_server’”和”show variables like ‘collation_server’”查看当前服务器的字符集和校对规则。
8. 数据库的字符集和校对规则在创建数据库的时候指定，如果没有指定，则应用服务器的字符集和校对规则的配置。要显示当前数据库的字符集和校对规则，可以使用”show variables like ‘character_set_database’”和”show variables like ‘collation_database’”命令查看。
9. 设置了以上４个级别的字符集，对于实际的应用访问来说，还存在客户端和服务器之间交互的字符集和校对规则的设置。对于客户端和服务器的交互操作，MySQL提供了３个不同的参数：character_set_client、character_set_connection和character_set_results，分别代表客户端、连接和返回結果的字符集。通过情况下，这３个字符集应该是相同的，才可以确保用户写入的数据可以正确地读出。
10. 可以通过”SET NAMES ***”来设置连接的字符集和校对规则，这个命令可以同时修改这３个参数的值。使用这个方法修改连接的字符集和校对规则，需要应用每次连接数据库后都执行这个命令。
11. 可以在my.cnf中设置”default-character-set=gbk”，这样服务器启动后，所有连接默认就是使用GBK字符集进行连接的，而不需要在程序中再执行set names命令了。