mysql参考手册笔记.docx

第3章 教程1、 mysql注释符有三种：(1)、#.(2)、- .（要求第2个破折号后面至少跟一个空格符(例如空格、tab、换行符等等)。）(3)、/*.*（允许跨多行）2、 用MySQL，可以使用/*! */语法把MySQL特定的关键词加到语句中。在/*/中的代码将被其它大多数SQL服务器视为注释(并被忽略)。3、 一个命令通常由SQL语句组成，随后跟着一个分号。（有一些例外不需要分号。早先提到的QUIT是一个例子。） 当发出一个命令时，mysql将它发送给服务器并显示执行结果，然后显示另一个mysql显示它准备好接受其它命令。 mysql显示返回了多少行，以及查询花了多长时间，它给你提供服务器性能的一个大致概念。因为他们表示时钟时间(不是 CPU 或机器时间)，并且因为他们受到诸如服务器负载和网络延时的影响，因此这些值是不精确的。4、 你可以在一行上输入多条语句，只需要以一个分号间隔开各语句：mysql SELECT VERSION(); SELECT NOW();5、 不必全在一个行内给出一个命令，较长命令可以输入到多个行中。mysql通过寻找终止分号而不是输入行的结束来决定语句在哪儿结束。（换句话说，mysql接受自由格式的输入：它收集输入行但直到看见分号才执行。）这里是一个简单的多行语句的例子：mysql SELECT - USER() - , - CURRENT_DATE;6、 如果你决定不想执行正在输入过程中的一个命令，输入c取消它：mysql SELECT - USER() - cmysql7、 下表显示出可以看见的各个提示符并简述它们所表示的mysql的状态：8、 在MySQL中，可以写由或字符括起来的字符串 (例如，hello或goodbye)。9、 USE，类似QUIT，不需要一个分号。（如果你喜欢，你可以用一个分号终止这样的语句；这无碍）USE语句在使用上也有另外一个特殊的地方：它必须在一个单行上给出。10、 AND和OR可以混用，但AND比OR具有更高的优先级。11、 load data方法： 创建一个文本文件，每个条目一行，用tab分开，各个列与create table中的列次序相对应，不知道的值为NULL用N代替。LOAD DATA LOCAL INFILE /path/filename.txt INTO TABLE table_name;注意windows中rn做为行的结束符因此应该使用LOAD DATA LOCAL INFILE /path/filename.txt INTO TABLE table_name LINES TERMINATED BY rn;如果想一次增加一条新的记录使用INSERT语句12、 在字符类型列上Order by，排序时没有区分大小写：对于某一列，可以使用BINARY强制执行区分大小写的分类功能，如：ORDER BY BINARY col_name。（注意和前一个以“j和J”开头的数据）13、 计算生日，可以计算当前日期的年和出生日期之间的差。如果当前日期的日历年比出生日期早，则减去一年：mysql SELECT name, birth, CURDATE(), - (YEAR(CURDATE()-YEAR(birth) - - (RIGHT(CURDATE(),5) AS age - FROM pet;+-+-+-+-+| name | birth | CURDATE() | age |+-+-+-+-+| Fluffy | 1993-02-04 | 2003-08-19 | 10 | Claws | 1994-03-17 | 2003-08-19 | 9 | Buffy | 1989-05-13 | 2003-08-19 | 14 | Fang | 1990-08-27 | 2003-08-19 | 12 | Bowser | 1989-08-31 | 2003-08-19 | 13 | Chirpy | 1998-09-11 | 2003-08-19 | 4 | Whistler | 1997-12-09 | 2003-08-19 | 5 | Slim | 1996-04-29 | 2003-08-19 | 7 | Puffball | 1999-03-30 | 2003-08-19 | 4 |+-+-+-+-+ 此处，YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符。比较MM-DD值的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1。14、 使用扩展正则表达式，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“”或在模式的结尾用“$”。15、 为了找出当前选择了哪个数据库，使用DATABASE( )函数：mysql SELECT DATABASE();+-+| DATABASE() |+-+| menagerie |+-+如果你还没选择任何数据库，结果是NULL。16、 如果表有索引，SHOW INDEX FROM tbl_name生成有关索引的信息。17、 根据天计算访问量：下面的例子显示了如何使用位组函数来计算每个月中用户访问网页的天数。CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23);示例表中含有代表用户访问网页的年月日值。可以使用以下查询来确定每个月的访问天数：SELECT year,month,BIT_COUNT(BIT_OR(1day) AS days FROM t1 GROUP BY year,month;将返回：+-+-+-+| year | month | days |+-+-+-+| 2000 | 01 | 3 | 2000 | 02 | 2 |+-+-+-+该查询计算了在表中按年/月组合的不同天数，可以自动去除重复的询问。解释：1、BIT_COUNT( expr )：返回 expr 的二进制表达式中”1“的个数。 例如：29 = 11101 则：BIT_COUNT（29）= 4；2、BIT_OR( expr )：返回 expr 中所有比特的bitwise OR。计算执行的精确度为64比特(BIGINT) 。 例如：上面例子中，2000年02月中有一条2号的记录两条23号的记录，所以1 mysql ptest shell mysql -p test第一个命令让mysql使用密码test，但没有指定默认数据库。第二个命令让mysql提示输入 密码并使用test作为默认数据库。21、 对mysql偶尔有用的另一个选项是-e或-execute选项，可用来将SQL语句传递给服务器。该语句必须用引号引起来(单引号或双引号)。(然而，如果想要在查询中将值引起来，则对于查询应使用双引号，查询中引用的值应使用单引号）。当使用该选项时，语句被执行，然后mysql立即退出命令外壳。（注：语句最后的“mysql”为数据库名）。可以按这种方式传递多个SQL语句，用分号隔开：22、 使用选项文件。MySQL程序可以从选项文件(有时也称为配置文件)读取启动选项。选项文件提供了一种很方便的方式来指定常用的选项，因此不需要每次运行程序时从命令行输入。在Windows中，MySQL程序从以下文件读取启动选项：文件名目的WINDIRmy.ini全局选项C:f全局选项INSTALLDIRmy.ini全局选项defaults-extra-file用-defaults-extra-file=path指定的文件，如果有WINDIR表示Windows目录的位置。通常为C:WINDOWS或C:WINNT。你可以使用下面的命令通过环境变量WINDIR的值确定其确切位置： C: echo %WINDIR%INSTALLDIR表示MySQL的安装目录。一般为C:PROGRAMDIRMySQLMySQL 5.1 Server，其中PROGRAMDIR表示程序目录(通常为Windows英文版的Program Files)，第7章 优化23、 EXPLAIN语法：EXPLAIN tbl_name或：EXPLAIN EXTENDED SELECT select_optionsEXPLAIN语句可以用作DESCRIBE的一个同义词，或获得关于MySQL如何执行SELECT语句的信息：EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接（single-sweep multi-join）的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。 当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。24、 为了看清一张表有什么索引，使用SHOW INDEX FROM tbl_name。25、 看了一段，不想看了。第9章 语言结构26、 字符串：字符串指用单引号()或双引号()引起来的字符序列。如果SQL服务器模式启用了NSI_QUOTES，可以只用单引号引用字符串。用双引号引用的字符串被解释为一个识别符。27、 在字符串中，某些序列具有特殊含义。这些序列均用反斜线()开始，即所谓的转义字符。在其它转义序列中，反斜线被忽略。也就是说，转义字符解释为仿佛没有转义。28、 有几种方式可以在字符串中包括引号： 在字符串内用引用的可以写成。 在字符串内用引用的可以写成。 可以在引号前加转义字符()。 在字符串内用引用的不需要特殊处理，不需要用双字符或转义。同样，在字符串内用引用的也不需要特殊处理。29、 MySQL支持十六进制值。在数字上下文中，十六进制数如同整数(64位精度)。在字符串上下文，如同二进制字符串，每对十六进制数字被转换为一个字符。十六进制值的默认类型是字符串。如果想要确保该值作为数字处理，可以使用CAST(.AS UNSIGNED)：可以用HEX()函数将一个字符串或数字转换为十六进制格式的字符串：30、 布尔值：常量TRUE等于1，常量FALSE等于0。常量名可以写成大写或小写。31、 用户变量： 可以先在用户变量中保存值然后在以后引用它；这样可以将值从一个语句传递到另一个语句。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。用户变量的形式为var_name，用户变量名对大小写不敏感。 设置用户变量的一个途径是执行SET语句： SET var_name = expr , var_name = expr . 对于SET，可以使用=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符：注释：在SELECT语句中，表达式发送到客户端后才进行计算。这说明在HAVING、GROUP BY或者ORDER BY子句中，不能使用包含SELECT列表中所设的变量的表达式。32、 系统变量： MySQL可以访问许多系统和连接变量。当服务器运行时许多变量可以动态更改。这样通常允许你修改服务器操作而不需要停止并重启服务器。 mysqld服务器维护两种变量。全局变量影响服务器整体操作。会话变量影响具体客户端连接的操作。 当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。服务器启动后，通过连接服务器并执行 SET GLOBAL var_name语句，可以动态更改这些全局变量。要想更改全局变量，必须具有SUPER权限。 服务器还为每个连接的客户端维护一系列会话变量。在连接时使用相应全局变量的当前值对客户端的会话变量进行初始化。对于动态会话变量，客户端可以通过 SET SESSION var_name语句更改它们。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。 对于全局变量的更改可以被访问该全局变量的任何客户端看见。然而，它只影响更改后连接的客户的从该全局变量初始化的相应会话变量。不影响目前已经连接的客户端的会话变量(即使客户端执行SET GLOBAL语句也不影响)。33、 要想设置一个GLOBAL变量的值，使用下面的语法（例子使用了sort_buffer_sizeas作为示例变量名）：要想设置一个SESSION变量的值，使用下面的语法：LOCAL是SESSION的同义词。如果设置变量时不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION。要想检索一个GLOBAL变量的值，使用下面的语法：要想检索一个SESSION变量的值，使用下面的语法：第11章 列类型34、 NATIONAL CHAR(M) BINARY| ASCII | UNICODE固定长度字符串，当保存时在右侧填充空格以达到指定的长度。M表示列长度。M的范围是0到255个字符。当检索CHAR值时尾部空格被删除。35、 NATIONAL VARCHAR(M) BINARY变长字符串。M 表示最大列长度。M的范围是0到65,535。36、 blob被视为二进制字符串,Text被视为非二进制字符串。在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。37、 ENUM(value1,value2,.) 枚举类型。只能有一个值的字符串，从值列value1，value2，.，NULL中或特殊 错误值中选出。ENUM列最多可以有65,535个截然不同的值。ENUM值在内部用整数表示。38、 SET(value1,value2,.) （集合）一个设置。字符串对象可以有零个或多个值，每个值必须来自列值value1，value2，.SET列最多可以有64个成员。SET值在内部用整数表示。39、 DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值，例如货币数据。DECIMAL(精度,标度)精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。在MySQL 5.1中以二进制格式保存DECIMAL和NUMERIC值。插入的整数部分的值超过了其表示范围后就直接忽略了小数部分的值，并以最大值填充。当整数部分合法，小数部分多余的位数，直接截断。在标准SQL中，语法DECIMAL(M)等价于DECIMAL(M,0)。同样，语法DECIMAL等价于DECIMAL(M,0)，可以通过计算确定M的值。在MySQL 5.1中支持DECIMAL和NUMERIC数据类型的变量形式。M默认值是10。40、 BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1到64。 要指定位值，可以使用bvalue符。value是一个用0和1编写的二进制值。例如，b111和b100000000分别表示7和128。 如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。例如，为BIT(6)列分配一个值b101，其效果与分配b000101相同。41、 TIMESTAMP列类型提供一种类型，你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列，只有第一个自动更新。 Timestamp与时区相关，Datetime与时区无关；Timestamp四字节，Datetime八字节；Timestamp能自动触发，Datetime必须传入值。42、 BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。 BINARY和VARBINARY允许的最大长度一样，如同CHAR和VARCHAR，不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度。第12章 函数和操作符43、 流程控制语句：（1）、case： CASE value WHEN compare-value THEN result WHEN compare-value THENresult . ELSE result END返回结果中， value=compare-value。CASE WHEN condition THEN result WHEN condition THEN result . ELSE result END （2）、IF(expr1,expr2,expr3) 如果 expr1 是TRUE (expr1 0 and expr1 NULL)，则 IF()的返回值为expr2; 否则 返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。 （3）、IFNULL(expr1,expr2) 假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。 （4）、NULLIF(expr1,expr2) 如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。44、 RAND() RAND(N)返回一个随机浮点值 v ，范围在 0 到1 之间 (即, 其范围为 0 v 1.0)。若已指定一个整数参数 N ，则它被用作种子值，用来产生重复序列。 若要在i R j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j i + 1)。例如， 若要在7 到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句：SELECT FLOOR(7 + (RAND() * 6);在ORDER BY语句中，不能使用一个带有RAND()值的列，原因是 ORDER BY 会计算列的多重时间。然而，可按照如下的随机顺序检索数据行：mysql SELECT * FROM tbl_name ORDER BY RAND();ORDER BY RAND()同 LIMIT 的结合从一组列中选择随机样本很有用：mysql SELECT * FROM table1, table2 WHERE a=b AND c ORDER BY RAND() LIMIT 1000;注意，在WHERE语句中，WHERE每执行一次， RAND()就会被再计算一次。45、 返回各自当前日期或时间的函数在每次询问执行开始时计算一次。这意味着在一个单一询问中，对诸如NOW() 的函数多次访问总是会得到同样的结果(未达到我们的目的，单一询问也包括对存储程序或触发器和被该程序/触发器调用的所有子程序的调用 )。这项原则也适用于 CURDATE()、 CURTIME()、 UTC_DATE()、 UTC_TIME()、UTC_TIMESTAMP()，以及所有和它们意义相同的函数。46、 CURDATE()：将当前日期按照YYYY-MM-DD 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。CURRENT_DATE和CURRENT_DATE()是的同义词.（和CURDATE()一样）47、 CURTIME()：将当前时间以HH:MM:SS或 HHMMSS 的格式返回， 具体格式根据函数用在字符串或是数字语境中而定。CURRENT_TIME 和CURRENT_TIME() 是CURTIME()的同义词。48、 DATE(expr)：提取日期或时间日期表达式expr中的日期部分。49、 NOW()：返回当前日期和时间值，其格式为 YYYY-MM-DD HH:MM:SS 或YYYYMMDDHHMMSS ， 具体格式取决于该函数是否用在字符串中或数字语境中。 在一个存储程序或触发器内, NOW() 返回一个常数时间，该常数指示了该程序或触发语句开始执行的时间。这同SYSDATE()的运行有所不同。50、 SYSDATE():返回当前日期和时间值，格式为YYYY-MM-DD HH:MM:SS 或YYYYMMDDHHMMSS， 具体格式根据函数是否用在字符串或数字语境而定。在一个存储程序或触发器中, SYSDATE()返回其执行的时间, 而非存储成都或触发语句开始执行的时间。这个NOW()的运作有所不同。51、 BINARY操作符将后面的字符串转换成一个一个二进制字符串。BINARY str 是CAST(str AS BINARY)的缩略形式。52、 BENCHMARK(count,expr)：BENCHMARK() 函数重复count 次执行表达式 expr 。 它可以被用于计算 MySQL 处理表达式的速度。结果值通常为 0。另一种用处来自 mysql客户端内部,能够报告问询执行的次数。53、 CHARSET(str)：返回字符串自变量的字符集。54、 CONVERT(expr USING transcoding_name)：带有USING的CONVERT() 被用来在不同的字符集之间转化数据。55、 CURRENT_USER, CURRENT_USER()： 返回当前话路被验证的用户名和主机名组合。这个值符合确定你的存取权限的MySQL 账户。在被指定SQL SECURITY DEFINER特征的存储程序内， CURRENT_USER() 返回程序的创建者。CURRENT_USER()的值可以和USER()的值有所不同。56、 DATABASE()：返回使用 utf8 字符集的默认(当前)数据库名。在存储程序里，默认数据库是同该程序向关联的数据库，但并不一定与调用语境的默认数据库相同。若没有默认数据库, DATABASE()返回 NULL。57、 FOUND_ROWS()：SELECT语句可能包括一个 LIMIT 子句，用来限制服务器返回客户端的行数。在有些情况下，需要不用再次运行该语句而得知在没有LIMIT 时到底该语句返回了多少行。为了知道这个行数, 包括在SELECT 语句中选择 SQL_CALC_FOUND_ROWS ，随后调用 FOUND_ROWS()：mysql SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name - WHERE id 100 LIMIT 10;mysql SELECT FOUND_ROWS(); 第二个 SELECT返回一个数字，指示了在没有LIMIT子句的情况下，第一个SELECT返回了多少行 (若上述的 SELECT语句不包括 SQL_CALC_FOUND_ROWS 选项,则使用LIMIT 和不使用时，FOUND_ROWS() 可能会返回不同的结果）。通过 FOUND_ROWS()的有效行数是瞬时的。58、 LAST_INSERT_ID() LAST_INSERT_ID(expr)： 自动返回最后一个INSERT或 UPDATE 问询为 AUTO_INCREMENT列设置的第一个 发生的值。 产生的ID 每次连接后保存在服务器中。这意味着函数向一个给定客户端返回的值是该客户端产生对影响AUTO_INCREMENT列的最新语句第一个 AUTO_INCREMENT值的。这个值不能被其它客户端影响，即使它们产生它们自己的 AUTO_INCREMENT值。这个行为保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁或处理。 假如你使用一个非“magic”值(一个不是 NULL也不是 0的值)来更新某一行的AUTO_INCREMENT 列，则LAST_INSERT_ID() 的值不会变化。重点: 假如你使用单INSERT语句插入多个行， LAST_INSERT_ID() 只返回插入的第一行产生的值。其原因是这使依靠其它服务器复制同样的 INSERT语句变得简单。 若给出作为到LAST_INSERT_ID()的参数expr ，则参数的值被函数返回，并作为被LAST_INSERT_ID()返回的下一个值而被记忆。这可用于模拟序列：o 创建一个表，用来控制顺序计数器并使其初始化： mysql CREATE TABLE sequence (id INT NOT NULL); mysql INSERT INTO sequence VALUES (0);o 使用该表产生这样的序列数 ： mysql UPDATE sequence SET id=LAST_INSERT_ID(id+1); mysql SELECT LAST_INSERT_ID();59、 ROW_COUNT()：ROW_COUNT()返回被前面语句升级的、插入的或删除的行数。 这个行数和 mysql 客户端显示的行数及 mysql_affected_rows() C API 函数返回的值相同。60、 SCHEMA()：这个函数和 DATABASE()具有相同的意义。61、 USER()：返回当前 MySQL用户名和机主名：这个值指示了你指定的连接服务器时的用户名,及你所连接的客户主机。这个值可以和CURRENT_USER() 的值不同。SESSION_USER()：SESSION_USER()和 USER()具有相同的意义。SYSTEM_USER()：SYSTEM_USER() 和 USER()具有相同的意义。USER()返回当前MySQL连接的用户名和主机名，表示用于建立连接的用户名和主机名，而CURRENT_USER表示mysql.user表里实际上使用的用户名，这将确定你在数据库中的权限。62、 VERSION()：返回指示 MySQL 服务器版本的字符串。这个字符串使用 utf8 字符集。63、 DEFAULT(col_name)：返回一个表的某一列的默认值。若该列没有默认值则会产生错误。64、 FORMAT(X,D)：将数字X 的格式写为#,#,#.#,以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。65、 UUID()：返回一个通用唯一标识符(UUID)。 UUID被设计成一个在时间和空间上都独一无二的数字。2个对UUID() 的调用应产生2个不同的值，即使这些调用的执行是在两个互不相连的单独电脑上进行。UUID 是一个由5位十六进制数的字符串表示的128比特数字 ，其格式为 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee ：o 前3个数字从一个时间戳产生。 o 第4 个数字保持暂时唯一性，以防时间戳值失去单一性 (例如, 由于经济时)。o 第5个数字是一个 IEEE 802 节点号，它提供空间唯一性。若后者不可用，则用一个随机数字替换。 (例如, 由于主机没有以太网卡，或我们不知道怎样在你的操作系统上找到界面的机器地址 )。假若这样，空间唯一性就不能得到保证。尽管如此，一个冲突的发生机率还是非常低的。66、 COUNT(DISTINCT expr,expr.)：返回不同的非NULL值数目。67、 GROUP_CONCAT(expr)：该函数返回带有来自一个组的连接的非NULL值的字符串结果。68、 GROUP BY修改程序：GR