MySQL学习笔记

数据库基础

1、数据库（database）

保存有组织的数据容器（可以是很多的形式，可以使文件，文件夹等）

2、表（table）

某种特定类型数据的结构化清单
我们通常将数据分类存放在表中，每个表都具有自己的意义，自己独特的名称。在同一个数据库中，每一张表的名称都是独一无二的，但是在不同的数据表中可以存在相同名称的数据表；

3、列（column）

表中的一个字段，所有表都是由一个或多个列组成。
数据库中的表与我们平时生活中的数据表类似存在着许多列，每个列都有自己独特的含义以及对储存信息约束(数据结构)。

4、行（row）

表中的数据记录；

5、主键（primary key）

代表这一列中的数据，能够区分表中的每一行；
此列中的数据唯一且不为空。通常对于大多数数据表都会定义主键，这样可以便于检索，便于对数据表操作及管理。
关于主键使用的好习惯:
1、不更新主键中的值；
2、不重用主键列中的值；
3、不在主键中储存可能会更改的值，最好与存储内容无关

6、MySQL语言

SQL 是专门用来与数据库通信的语言；

SQL不是一种专利语言，而且存在一个标 准委员会，他们试图定义可供所有DBMS使用的SQL语法，但 事实上任意两个DBMS实现的SQL都不完全相同。本书讲授的 SQL是专门针对MySQL的，虽然书中所讲授的多数语法也适 用于其他DBMS，但不要认为这些SQL语法是完全可移植的。
摘自《关于MySQL必知必会》

(1、创建数据库

使用关键字CREATE DATABASE
格式：CREATE DATABASE database_name;

(2、选择数据库

使用关键字**USE **
格式：USE database_name;

(3、关于SHOW关键字

SHOW DATABASES 查看所有数据库
SHOW TABLES 查看本数据库中的所有数据表
** SHOW COLUMNS FROM table_name**查看一个数据表中所有列的信息。

DESRIBE语句 MySQL中支持使用DESCRIBE作为SHOW COLUMNS的一种快捷方式，所以换句话说，SHOW COLUMNS语句可以使用DESCRIBE语句快捷使用

关于SHOW的其他语句

SHOW STATUS，用于显示广泛的服务器状态信息； 
SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创 建特定数据库或表的MySQL语句；
SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安 全权限； 
SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。

更多可以通过输入HELP SHOW来获得

（4、数据检索

使用SELECT语句 ，
基本格式有：

SELECT [DISTINCT] column_name,…
FROM table_neme
LIMIT number
OFFSET number

DISTINCT 对重复信息的过滤。
DISTINCT关键字应用于所有列而 不仅是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非指定的两个列都不同，否则所有行都将被 检索出来。

LIMIT对输出结果行数的限
OFFSET 对输出结果开始索引进行限定，默认零

注 ：当SELECT 后使用 * 通配符时输出结果为整个数据表。

(5、排序数据检索

在我们使用SELECT 语句查询数据时返回的结果是一种随机顺序，这顺序随着查询字段不同而不同，但是有时候我们需要对数据进行排序，这样更有利于我们对数据的分析。
这时候我们需要用到 ORDER BY关键字，使用格式为：

SELECT columns
FROM table_name
ORDER BY columns；

语句返回的结果会按照ORDER BY后字段进行排序，进而返回一组有顺序的数据；
我们可以在ORDER BY后写上一个以上的字段，主要格式为 ：

SELECT *
FROM table_name
ORDER BY column_1 ,column_2;

这时候只有当数据中column_1列的数据完全相同时才使用column_2列的数据进行排序。
默认情况下输出的为升序排序(ACS) ,使用DESC关键字可以使结果降序输出。
使用格式:

ORDER BY column_1 DESC;

DESC关键字只对前面那个字段有效，所以在对多个字段都要使他们降序排序时，就得在每个字段后都加上DESC。

（6、数据过滤

关键：WHERE子句 ，WHERE语句位于FROM语句之后，数据将根据WHERE中的条件进行输出；

注： 在同时使用ORDER BY和WHERE子句时，应 该让ORDER BY位于WHERE之后，否则将会产生错误

关于WHERE语句中的条件操作符，与其他编程语言大致一致（<> 为不等于）
下面对几种特殊的介绍:
AND 且
OR 或
NOT 非 mysql语句中允许对IN、BETWEEN和 EXISTS子句取反
between 与AND连用，between a AND b，a到b范围中的数据(a<b);
in 代表包括，in(a,b,d)与a OR b OR c语句意义相同 ;
IS NULL 代表这列中为null的数据，为null是没有储存数据与0和""不同；
同时可以对他取反代表不为空的数据 NOT IS NULL;

使用通配符过滤

使用LIKE子句 ，就是像什么的意思，就是数据不一定完全相同，只要数据的部分字段相同就会被输出
两个常用的通配符：
% ：代表一个或一个以上的字符 ，’ % ing’ 代表ing为后缀的数据 ，‘un %’代表un为前缀的数据，‘%k%’ 代表含有k的数据，与位置无关；
_ : 代表一个字符，用法与%差不多

使用正则表达式过滤

使用正则表达式过滤的关键字为REGEXP 代表使用正则表达式;

仅为正则表达式语言的一个子集 如果你熟悉正则表达式，需 要注意：MySQL仅支持多数正则表达式实现的一个很小的子 集

关于正则表达式的使用与LIKE通配符的用法差不多，正则表达式的工功能更加强大。

匹配不区分大小写 MySQL中的正则表达式匹配（自版本 3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大 小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY ‘JetPack .000’

（1、正则表达式中的or ‘|’ ，‘a|b’代表含有a或含有b的数据,也可以用[1,2,3]表示包含关系
当满足1或2 或3 就满足关系
（2、范围筛选，使用符号[] ,[0-9] 代表含有0到9的数据，*使用^符号可以对[]中的内容进行取反操作 [^0-9]代表不含0-9的数据
（3、对于特殊字符的筛选，通常在特殊字符前加上\,如’\.'代表含有点的数据

\或\\? 多数正则表达式实现使用单个反斜杠转义特殊字符， 以便能使用这些字符本身。但MySQL要求两个反斜杠（MySQL 自己解释一个，正则表达式库解释另一个）。

（4、正则表达式中有一些字符集代表我们一些常用的数据，如所有的数字，所有的字母等，下面是一些字符集的表格

字符集	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\t]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:]	任意大写字母（同[A-Z]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

（5、定位符
对筛选位置的选定

符号	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

(6、重复字符

	说明
*	0个或多个匹配 + 1个或多个匹配（等于{1,}）
?	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

(7、计算字段拼接

在使用数据库的时候，我们需要的数据往往不是原始的数据，而是进行加工后的数据。在MySQL中支持在列的基础上对列数据进行算术运算（加减乘除）;
Concat() : MySQL 中使用Concat()对字段进行拼接，如Concat(column_1,’_n’) 得到结果为“ column_1_n”;

MySQL的不同之处 多数DBMS使用+或||来实现拼接， MySQL则使用Concat()函数来实现。当把SQL语句转换成 MySQL语句时一定要把这个区别铭记在心

AS : 对字段起别名 ，如“column_1 AS name” 就可以在接下的操作中用name代替column_1;

别名的其他用途 别名还有其他用途。常见的用途包括在实际 的表列名包含不符合规定的字符（如空格）时重新命名它，在 原来的名字含混或容易误解时扩充它，等等

(8、MySQL中的函数

下面是一些MySQL中支持的函数。
关于文本处理

函数名	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

关于Soundex()函数，SOUNDEX是一个将任何文 本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似 的发音字符和音节，使得能对串进行发音比较而不是字母比较。

关于日期处理

函数名	说明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

应该总是使用4位数字的年份 MySQL支持2位数字的年份，MySQL 处理00-69为2000-2069，处理70-99为1970-1999。虽然它们可 能是打算要的年份，但使用完整的4位数字年份更可靠，因为 MySQL不必做出任何假定。

关于数值处理函数

函数名	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

（9、聚集函数

聚集函数（aggregate function）: 运行在行组上，计算和返回单 个值的函数

为什么使用聚集函数:
确定表中行数（或者满足某个条件或包含某个特定值的行数）。
获得表中行组的和。
找出表列（或所有行或某些特定的行）的最大值、最小值和平均值。

MySQL中的聚集函数

函数名	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

特殊说明，在使用COUNT(*)时不会忽略NULL值，但是在COUNT()括号中放入列时便会忽略NULL值，其余聚集函数计算都会忽略NULL值

还可以在聚集函数括号中放入DISTINCT 来过滤重复数据；
注意 ： 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT 不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT）， 否则会产生错误。类似地，DISTINCT必须使用列名，不能用 于计算或表达式。

(10、分组数据

GROUP BY ： 对后面的字段进行分组，与筛选类似，但是会计算没一个重复的数据的数量，比如一个班的成绩表中每个分数的人数是多少，类似的分组。
在具体使用GROUP BY子句前，需要知道一些重要的规定。 
GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制。 
如果在GROUP BY子句中嵌套了分组，数据将在后规定的分组上 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算 （所以不能从个别的列取回数据）。 
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。 
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子 句中给出。 
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列 中有多行NULL值，它们将分为一组。 
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前 。

过滤分组

使用HAVING关键字，位置在GROUP BY之后，用法与WHERE相同。

HAVING和WHERE的差别 这里有另一种理解方法，WHERE在数据 分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重 要的区别，WHERE排除的行不包括在分组中。这可能会改变计 算值，从而影响HAVING子句中基于这些值过滤掉的分组

SLECT 子句的顺序:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

(11、联结

将有关系的两张表联结起来查询数据，是生活中更加常用的方法，因为我们往往无法把所有信息都储存在一张表中（多张数据表更有效率，也更方便处理），所以我们有时候需要对照两张或者更多的表去寻找数据。这种道理在数据库中同样通行。

两张表中往往需要我们去定义一个或多个相关的信息，才可以有效的联结起来。这时候我们可以使用一个约束：
外键（foreign key） 外键为某个表中的一列，它包含另一个表 的主键值，定义了两个表之间的关系；

在MySQL中创建联结的方式有两种
（2、使用JOIN子句，JOIN子句是标准的SQL语句联结语句，使用方法，表名 + JOIN+表名+ON+条件
这只是个简单对联结的介绍。

(12、储存过程

储存过程是将一段可以实现某种功能的代码保存在一个集合中，用户可以在有需要的时候去调用;
创建一个储存过程：

	CREATE PROCEDURE procedurename(参数列表
		OUT  变量名 变量类型,
		IN   变量名 变量类型,
		-- OUT代表输出，IN代表输入;
		)
	BEGIN 
			代码过程（实现具体功能）
	END;

注：

mysql命令行客户机的分隔符 如果你使用的是mysql命令行 实用程序，应该仔细阅读此说明。 默认的MySQL语句分隔符为;（正如你已经在迄今为止所使用 的MySQL语句中所看到的那样）。mysql命令行实用程序也使 用;作为语句分隔符。如果命令行实用程序要解释存储过程自 身内的;字符，则它们最终不会成为存储过程的成分，这会使 存储过程中的SQL出现句法错误。
解决方法如下:

DELIMITER //
CREATE PROCEDURE   procedurename()
	BEGIN 
			代码过程（实现具体功能）
	END  //
	DELIMITER ;

其中，DELIMITER //告诉命令行实用程序使用//作为新的语 句结束分隔符，可以看到标志存储过程结束的END定义为END //而不是END;。这样，存储过程体内的;仍然保持不动，并且 正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER ;。 除\符号外，任何字符都可以用作语句分隔符。 如果你使用的是mysql命令行实用程序，在阅读本章时请记住 这里的内容。

执行储存过程:

CALL procedurename (@price,
					price)

删除储存过程:

DROP procedurname;

当对一个储存过程修改时必须对这个储存过程进行删除再重建

MySQL中变量都必须以@开头
查看变量使用SELECT语句
SELECT @price;
IF语句结构

	IF  条件   THEN(如果为真) 
	  执行语句

REPEAT 循环结构

   REPEAT
        语句
    UNTIL 条件 END REPEAT;

使用游标

在储存对一个SELECT 语句返回的结果集进行操作时我们有时需要对其中的每一个值进行操作时可以使用游标，游标类似指针，可以对SELECT的结果进行逐个操作。
定义一个游标

	DECLARE ordernumbers CURSOR
	FOR 
	SELECT order_num FROM orders; 

定义一个游标后我们需要使用OPEN和CLOSE语句去开启和关闭游标；