MySQL学习总结

一、初始MySQL

1、MySQL常用用命令：

SELECT VERSION(); //显示当前服务器版本

SELECT NOW();//显示当前日期时间

SELECT USER();//显示当前用户

2、MySQL语句的规范：

关键字与函数名称全部大写

数据库名称、表名称、字段名称全部小写

SQL语句必须以分号结尾

3、操作数据库：

3.1、  创建数据库CREATE DATABASE database-name

3.2、  删除数据库  DROP DATABASE dbname

3.3、  修改数据库  ALTER DATABASE dbname

二、MySQL中的数据类型：

1、Mysql中的整数类型：

类型名称	有符号	无符号	存储要求(字节)
TINYINT	-128~127	0~255	1
SMALLINT	-32768~32767	0~65535	2
MEDIUMINT	-8388608~8388607	0~16777215	3
INT(INTEGER)	-2147483648~2147483647	0~4294967295	4
BIGINT	-9233372036854775808~ 9223372036854775807	0~ 18446744073709551615	8

 

2、  MySQL中的浮点数据类型

类型名称	说明	存储需求（字节）
FLOAT	单精度浮点数	4
DOUBLE	双精度浮点数	8
DECIMAL(M,D),DEC	压缩的严格定点数	M+2

 

3、  MySQL中的日期与时间类型

类型名称	日期格式	日期范围	存储需求（字节）
YEAR	YYYY	1901~2155	1
TIME	HH:MM:SS	-838:59:59~838:59:59	3
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~ 9999-12-31 23:59:59	8
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 utc~ 2038-01-19 03:14:07 utc	4

 

4、  MySQL字符型类型

MySQL数据类型	含义
CHAR(N)	固定长度，最多255个字符
VARCHAR(N)	可变长度，最多65535个字符
TINYTEXT	可变长度，最多255个字符
TEXT	可变长度，最多65535个字符
MEDIUMTEXT	可变长度，最多2的24次方-1个字符
LONGTEXT	可变长度，最多2的32次方-1个字符
ENUM	1或2个字节，取决于枚举值的数目
SET	1,2,3,4或8个字节，取决于集合成员的数量

 

三、数据表的基本操作：

1、创建基本表

          CREATE TABLE <表名> (<列名><数据类型>[列级完整性约束条件]

                                                    [,<列名><数据类型>[列级完整性约束条件]

                                                    .........

                                                    [,<表级完整性约束条件>]);

Eg：CREATE TABLE links (name VARCHAR(255) NOT NULL DEFAULT , address VARCHAR(255)NOT NULL );

2、修改基本表

           ALERT  TABLE  <表名>

                                 [ RENAME[TO]<新表名>]

                                 [ADD <新列名> <数据类型> [完整性约束][first|after]]

                                 [DROP <字段名>]

                                 [CHANGE <旧字段名><新字段名><数据类型>];

                                 [MODIFY <字段名><数据类型>];

          ADD: 用于向表中增加新列及新列相关的完整性约束条件，新的列增加成功后不带任何数据

          DROP:用于删除指定的完整性约束条件

          MODIFY:用于修改原来的列定义，包括修改列名和数据类型，列数据类型修改后可能会丢失原有的数据

3、删除基本表

           DROP TABLE<表名>

4、  数据的查找

                    SELECT  [DISTINCT |DISTINCTROW | ALL]

       列名[，列名][，....]

       [INTO {OUTFILE | DUMPFILE} '文件名' EXPORT_OPTIONS]

       [FROM 表名]

       [WHERE 查询条件]

       [GROUP BY 列名[ASC|DESC, ....]]

       [HAVING 条件定义]

       [ORDER BY {无符号整数|列名|公式}[ASC | DESC],...]

       [LIMIT [偏移量，] 行数]

①AS作为别名，此别名用作表达式的列名，可以用于GROUP BY、ORDER BY或HAVING子句。

②where 查询条件。对记录进行过滤，如果没有指定where子句，则显示所有记录。在where表达式中，可以使用MySQL支持的函数或运算符。

③HAVING必须引用GROUP BY子句中的列或用于总计函数中的列或者聚合函数如max。

④使用GROUP BY，则输出行根据GROUP BY列进行分类，如同您对相同的列进行了ORDER BY。

⑤LIMIT子句可以被用于限制被SELECT语句返回的行数。LIMIT取一个或两个数字自变量，自变量必须是非负的整数常数（当使用已预备的语句时除外）。使用两个自变量时，第一个自变量指定返回的第一行的偏移量，第二个自变量指定返回的行数的最大值。

5、数据的插入

     第一种方式：

                       INSERT[LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

                                       [INTO] tbl_name [(col_name,...)]

                                        VALUES ({expr | DEFAULT},...),(...),...

                                        [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

     Eg：INSERT INTOlinks(name,address) VALUES('jerichen','gdsz');

第二种方式：INSERT插入多条语句

     假如我们想一次性的往数据库里插入多条数据咋办？一定要写多条语句吗？肯定是不会的，因为MySQL设计的还是很人性的。其提供insert语句的一种非标准格式，即，                       values(字段值1,字段值2,字段值3),(另一个字段1的值,另一个字段2的值,另一个字段3的值);

第三种方式：INSERT使用UPDATA语句中的SET插入语句

        INSERT INTOlinks SET name='jerichen',address='gdsz';

第四种方式：可以使用INSERT结合SELECT

        INSERT [INTO] table_name [(col_name, …)] SELECT …

          可以将查找的结果输入到表格中去。

5、  数据的修改

UPDATE[LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]…

[WHEREwhere_condition]

 其中table_reference代表多表间的参考关系，语法：

{[INNER|CROSS] JOIN |{LEFT|RIGHT}[OUTER] JOIN}

表的连接分为三种：内连接、左外连接、右外连接。

6、  数据的删除

DELETE FROM<表名> where <条件表达式>

7、数据约束：

A：非空约束（NOT NULL）

B：主键约束（PRIMARY KEY）

C: 唯一约束（UNIQUE KEY）

D: 默认约束（DEFAULT）

E: 外键约束（FOREIGN KEY）

外键约束的语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

        REFERENCES tbl_name (index_col_name,...)

        [ON DELETE {RESTRICT | CASCADE | SETNULL | NO ACTION}]

            [ON UPDATE {RESTRICT | CASCADE |SET NULL | NO ACTION}]

外键约束的参照操作：

·  CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。

·  SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。。

·   NO ACTION: 在ANSI SQL-92标准中，NO ACTION意味这不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主要键值的企图不被允许进行（Gruber, 掌握SQL, 2000:181）。 InnoDB拒绝对父表的删除或更新操作。

·   RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样，删除ON DELETE或ON UPDATE子句。（一些数据库系统有延期检查，并且NO ACTION是一个延期检查。在MySQL中，外键约束是被立即检查的，所以NO ACTION和RESTRICT是同样的）。

·   SET DEFAULT: 这个动作被解析程序识别，但InnoDB拒绝包含ON DELETE SETDEFAULT或ON UPDATE SET DEFAULT子句的表定义。

四、子查询与连接

4.1 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者几个表。子查询常用操作符有ANY（SOME）、ALL、IN、EXISTS。

Eg：SElECT * FROM t1 WHERE col1=（SELECT col2 FROM t2）；

子查询分为：

第一：使用比较运算符的子查询：=、>、<、>=、<=、<>、!=、<=>

语法结构： operand comparison_operator [ANY|ALL|SOME] subquery

第二：使用EXISTS关键字的子查询

 EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询，如果只查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

第三：使用IN关键字的子查询

Operand comparison_operation[NOT] IN (subquery) =ANY 运算符与IN等效。

!=ALL或<>ALL运算符与NOT IN等效。

IN关键字进行子查询，内存查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

4.2 多表更新

UPDATE[LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]…

[WHEREwhere_condition]

 其中table_reference代表多表间的参考关系。

4.3 连接

表的连接分为三种：内连接（INNER JOIN）、左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）

语法：{[INNER|CROSS] JOIN |{LEFT|RIGHT}[OUTER] JOIN}

使用ON关键字来设定连接条件，也可以使用WHERE来代替。

内连接：显示两个表格中都符合条件中的记录

左外连接：显示左表的全部和右表中符合条件的记录。

右外连接：显示右表的全部和左表中符合条件的记录。

4.4 多表删除

DELETE table_name[.*][,tbl_name[.*]]…

FROM table_references

[WHEREwhere_condition]

五、运算符和函数

5.1 字符串函数：

1.字符串长度函数CHAR_LENGTH(str),LENGTH(str)

CHAR_LENGTH()返回值为字符串str的长度，长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集，LENGTH() 返回值为 10, 而 CHAR_LENGTH() 的返回值为 5

2.拼接函数 CONCAT(str1 ,str2 ,...):

返回结果为连接参数产生的字符串。如有任何一个参数为 NULL ，则返回值为NULL。

CONCAT_WS(separator ,str1 ,str2,...):

CONCAT_WS() 代表 CONCAT With Separator ，是 CONCAT() 的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL ，则结果为 NULL 。函数会忽略任何分隔符参数后的 NULL 值。

3.重复函数 REPEAT(str ,count):

函数使用说明：返回一个由重复的字符串str 组成的字符串，字符串str 的数目等于count 。 若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL ，则返回 NULL 。

4.查找位置函数 FIND_IN_SET(str, strlist)

假如字符串 str 在由 N 子链组成的字符串列表 strlist 中，则返

回值的范围在 1 到 N 之间 。一个字符串列表就是一个由一些被‘,’ 符号分开的自链组成的字符串。如果第一个参数是一个常数字符串，而第二个是 type SET 列，则FIND_IN_SET() 函数被优化，使用比特计算。如果 str 不在 strlist 或 strlist 为空字符串，则返回值为 0 。如任意一个参数为 NULL ，则返回值为 NULL。

LOCATE(substr ,str ) ,LOCATE(substr ,str ,pos ):

第一个语法返回字符串 str 中子字符串substr 的第一个出现位置。第二个语法返回字符串 str 中子字符串substr 的第一个出现位置, 起始位置在pos 。如若substr 不在str 中，则返回值为0 。

5.获取指定长度字符串的函数

LEFT(str,len):

返回从字符串str 开始的len 最左字符

RIGHT(str ,len ):

从字符串str 开始，返回最右len 字符。

 

6、获取子串的函数：SUBSTRING(str ,pos ) , SUBSTRING(str FROM pos ) SUBSTRING(str ,pos,len ) , SUBSTRING(str FROM pos FOR len ):

不带有len 参数的格式从字符串str 返回一个子字符串，起始于位置 pos 。带有len 参数的格式从字符串str 返回一个长度同len 字符相同的子字符串，起始于位置 pos 。使用 FROM 的格式为标准 SQL 语法。也可能对pos 使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。

注：SUBSTRING() 等价于SUBSTR()

7.大小写转换函数

LOWER(str)转化为小写，UPPER(str)转化为大写

8.字符串替换函数

REPLACE(str ,from_str ,to_str)

返回字符串str 以及所有被字符串to_str 替代的字符串from_str

9.反转函数REVERSE(str)

10.去空格函数

select LTRIM('   abc'); --abc

select RTRIM('abc   '); --abc

TRIM([{BOTH | LEADING |TRAILING} [remstr ] FROM] str ):

返回字符串 str ，其中所有remstr 前缀和/ 或后缀都已被删除。若分类符BOTH 、LEADIN 或TRAILING 中没有一个是给定的, 则假设为BOTH

select TRIM(BOTH FROM'  a b  '); --a  b

11空格字符串

SPACE(N)：返回一个由N个空格组成的字符串

12. 替换字符串的函数INSERT（s1,x,len,s2）

返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度，则返回值为原始字符串。

5.2 数值运算符和函数

所有的数学函数在一个出错的情况下返回NULL。

ABS(X)

返回X的绝对值。

SIGN(X)

返回参数的符号，为-1、0或1，取决于X是否是负数、零或正数。

MOD(N,M)

%模 (类似C中的%操作符)。返回N被M除的余数。

FLOOR(X)

对X向下取整。

CEILING(X)

对X向上取整。

ROUND(X)

返回参数X的四舍五入的一个整数。

ROUND(X,D)

返回参数X的四舍五入的有D位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

RAND()

RAND(N)

返回在范围0到1.0内的随机浮点值。如果一个整数参数N被指定，它被用作种子值。

你不能在一个ORDER BY子句用RAND()值使用列，因为ORDER BY将重复计算列多次。然而在MySQL3.23中，你可以做： SELECT *FROMtable_name ORDER BY RAND()，这是有利于得到一个来自SELECT* FROM table1,table2 WHERE a=b ANDc的集合的随机样本。注意在一个WHERE子句里的一个RAND()将在每次WHERE被执行时重新评估。

LEAST(X,Y,...)

有2和2个以上的参数，返回最小(最小值)的参数。参数使用下列规则进行比较：

如果返回值被使用在一个INT上下文，或所有的参数都是整数值，他们作为整数比较。

如果返回值被使用在一个FLOAT上下文，或所有的参数是实数值，他们作为实数比较。

如果任何参数是一个大小敏感的字符串，参数作为大小写敏感的字符串被比较。

在其他的情况下，参数作为大小写无关的字符串被比较。

在MySQL 3.22.5以前的版本，你可以使用MIN()而不是LEAST。

GREATEST(X,Y,...)

返回最大(最大值)的参数。参数使用与LEAST一样的规则进行比较。

在MySQL在 3.22.5以前的版本, 你能使用MAX()而不是GREATEST.

5.3 比较运算符和函数

IS NULL 是否为空

IS NOT NULL 是否不为空

expr BETWEEN min AND max

如果expr对大于或等于min且expr是小于或等于max，BETWEEN返回1，否则它返回0。

expr IN (value,...)

如果expr是在IN表中的任何值，返回1，否则返回0。如果所有的值是常数，那么所有的值根据expr类型被计算和排序，然后项目的搜索是用二进制的搜索完成。这意味着如果IN值表全部由常数组成，IN是很快的。如果expr是一个大小写敏感的字符串表达式，字符串比较以大小写敏感方式执行。

expr NOT IN(value,...)

与NOT (expr IN(value,...))相同。

ISNULL(expr)

如果expr是NULL，ISNULL()返回1，否则它返回0。

5.4 日期时间函数

NOW()

SYSDATE()

CURRENT_TIMESTAMP

以'YYYY-MM-DDHH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间，取决于函数是在一个字符串还是在数字的上下文被使用。

CURDATE()

CURRENT_DATE

以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。

CURTIME()

CURRENT_TIME

以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。

DATE_ADD(data,INTERVAL exprtype)和DATA_SUB(data,INTERVALexpr type)

其中，data是一个DATETIME或DATE值，用来指定起始时间。Expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值。

5.5 信息函数

CONNECTION_ID()返回数据库的连接次数

VERSION()返回数据库版本号

DATABASE()、SCHEMA()返回当前数据库名

USER()、SYSTEM_USER()、SESSION_USER()返回当前用户

CURRENT_USER()、CURRENT_USER返回当前用户

CHARSET(str)返回字符串str的字符集

COLLATION(str)返回字符串str的字符排列方式

LAST_INSERT_ID()返回最后生成的AUTO_INCREMENT值

5.6 聚合函数

AVG（） 求平均值

COUNT（） 计数

MAX（） 最大值

MIN（） 最小值

SUM（） 求和

5.7 加密函数

MD5（） 信息摘要算法

PASSWORD（） 密码算法

六、  存储过程

1、存储过程的创建

MySQL中，创建存储过程的基本形式如下：

CREATE PROCEDURE sp_name([proc_parameter[,...]]) 

        [characteristic ...] routine_body

其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表；characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下：

[ IN | OUT | INOUT ] param_nametype

其中，IN表示输入参数；OUT表示输出参数； INOUT表示既可以是输入，也可以是输出； param_name参数是存储过程的参数名称；type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。

characteristic参数有多个取值。其取值说明如下：

COMMENT 'string'：注释信息。

技巧：创建存储过程时，系统默认指定CONTAINS SQL，表示存储过程中使用了SQL语句。但是，如果存储过程中没有使用SQL语句，最好设置为NO SQL。而且，存储过程中最好在COMMENT部分对存储过程进行简单的注释，以便以后在阅读存储过程的代码时更加方便。

2、存储过程的修改的基本形式如下：

ALTERPROCEDURE sp_name [characteristic…] COMMENT‘string’

|{ CONTAINSSQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

|SQLSECURITY { DEFINER | INVOKER }

{ CONTAINS SQL | NO SQL | READSSQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIESSQL DATA表示子程序中包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。

SQLSECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。默认情况下，系统指定的权限是DEFINER。

注意：不能修改过程体。

3、删除存储过程：

DROP PROCEDURE [IF EXISTS] sp_name

七、  MySQL中的存储引擎

MySQL中有多个存储引擎，MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的MySQL存储引擎，除非你配置MySQL默认使用另外一个引擎。

MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。

特点	Myisam	BDB	Memory	InnoDB	Archive
存储限制	没有	没有	有	64TB	没有
事务安全		支持		支持
锁机制	表锁	页锁	表锁	行锁	行锁
B树索引	支持	支持	支持	支持
哈希索引			支持	支持
全文索引	支持
集群索引				支持
数据缓存			支持	支持
索引缓存	支持		支持	支持
数据可压缩	支持			支持	支持
空间使用	低	低	N/A	高	非常低
内存使用	低	低	中等	高	低
批量插入的速度	高	高	高	低	非常高
支持外键				支持