Mysql的基本知识
为数据列挑选类型时应该思考的问题:
- 这个数据列将容纳什么样的数据?数值?字符串?日期?坐标值?
- 数据是否都在某个特定的区间内。是不是整数,是不是总是非负(可以考虑unsigned)?是不是字符串,是不是来自特定的字符串(可以考虑ENUM或SET)
- 在性能和效率方面有没有需要考虑的要素?
使用那种数字类型,可遵循以下原则:
- 选择最小的可用类型,如果值永远不会超过127,则使用TINYINT比INT强
- 对于完全是数字的,可以选择整数类型
- 浮点类型用于可能具有小数部分的数,如货物单价
- 使用简单的数据类型, 如 int比varchar好
使用int来储存日期时间,利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数经行转换
需要注意的是,用int来储存日期时,int的最大值是2147483647,也就是说,日期只能储存到2038-01-09 03:14:07(UTC)。如果超出这个范围,日期将会回到1970年1月1日9時0分0秒。 - 使用bigint来储存IP地址,利用INET_ATON(),INET_NTOA()两个函数进行转换
- 尽可能的使用not null 定义字段
- 尽量少使用text类型,非用不可时最好考虑分表
- 普通的文本字符串类型,即CHAR和VARCHAR类型,CHAR列的长度被固定为创建表所声明的长度,取值在1~255之间;VARCHAR列的值是变长的字符串,取值和CHAR一样
- 可变类型(TEXT和BLOB)。它们的大小可以改变,TEXT类型适合存储长文本,而BLOB类型适合存储二进制数据,支持任何数据,如文本、声音和图像等。
使用字符串类型时可以遵循以下原则:
- 从速度方面考虑,要选择固定的列,可以使用CHAR类型
- 要节省空间,使用动态的列,可以使用VARCHAR类型
- 要将列中的内容限制在一种选择,可以使用ENUM类型
- 允许在一个列中有多于一个的条目,可以使用SET类型
- 如果要搜索的内容不区分大小写,可以使用TEXT类型
- 如果要搜索的内容区分大小写,可以使用BLOB类型
比较运算符
在运用运算符“=”判断两个字符是否相同时,数据库系统都是根据字符的ASCII码进行判断的。如果ASCII码相等,则表示这两个字符相同。如果ASCII码不相等,则表示两个字符不同。切记空值(NULL)不能使用“=”来判断。
IS NULL用来判断操作数是否为空值(NULL)。操作数为NULL时,结果返回1;否则,返回0。
如果要判断一个值是否为空值,可以使用“<=>”、IS NULL和IS NOT NULL来判断
REGEXP同样用于匹配字符串,但其使用的是正则表达式进行匹配,其表达式如下。
x1 REGEXP '匹配方式'
使用运算符REGEXP匹配字符串,其使用方法非常简单。REGEXP运算符经常与“^”“$”和“.”一起使用。“^”用来匹配字符串的开始部分;“$”用来匹配字符串的结尾部分;“.”用来代表字符串中的一个字符。
聚合函数查询
- COUNT()函数,对于除“*”以外的任何参数,返回所选择集合中非NULL值的行的数目;对于参数“*”,返回选择集合中所有行的数目,包含NULL值的行
- SUM()函数可以求出表中某个字段取值的总和。
- AVG()函数可以求出表中某个字段取值的平均值。
- MAX()函数可以求出表中某个字段取值的最大值。
- MIN()函数可以求出表中某个字段取值的最小值。
EXISTS关键字
使用关键字EXISTS时,内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录,就返回一个真值(true),否则将返回一个假值(false)。当返回的值为true时,外层查询语句将进行查询;当返回的值为false时,外层查询语句不进行查询或者查询不出任何记录。
NOT EXISTS关键字
与关键字EXISTS刚好相反,使用关键字NOT EXISTS时,当返回的值是true时,外层查询语句不执行查询;当返回值是false时,外层查询语句将执行查询。
ANY关键字
只要满足内层查询语句返回的结果中的任何一个,就可以通过该条件来执行外层查询语句
ALL关键字
需要满足内层查询语句返回的所有结果,才可以执行外层查询语句
常用函数
- INSERT(s1,x,len,s2)函数将字符串s1中x位置开始长度为len的字符串用字符串s2替换。
- UPPER(s)函数和UCASE(s)函数将字符串s的所有字母变成大写字母。
- LEFT(s,n)函数返回字符串s的前n个字符。
- RTRIM(s)函数将去掉字符串s结尾处的空格
- SUBSTRING(s,n,len)函数从字符串s的第n个位置开始获取长度为len的字符串
- REVERSE(s)函数将字符串s的顺序反过来
- FIELD(s,s1,s2,…)函数返回第一个与字符串s匹配的字符串的位置
- LOCATE(s1,s):表示子字符串s1和在字符串s中的开始位置
- POSITION(s1 IN s):表示子字符串s1在字符串s中的开始位置
- INSTR(s,s1):表示子字符串s1在字符串s中的开始位置
- CURDATE()函数和CURRENT_DATE()函数用于获取当前日期
- CURTIME()函数和CURRENT_TIME()函数用于获取当前时间
- NOW()函数获取当前日期和时间。还有URRENT_TIMESTAMP()函数、LOCALTIME()函数、SYSDATE()函数和LOCALTIMESTAMP()函数也同样可以获取当前日期和时间
- DATEDIFF(d1,d2)用于计算日期d1与d2之间相隔的天数
- ADDDATE(d,n)用于返回起始日期d加上n天的日期
- ADDDATE(d,INTERVAL expr type)函数返回起始日期d加上一个时间段后的日期
- SUBDATE(d,n)函数返回起始日期d减去n天的日期
VERSION()函数返回数据库的版本号
CONNECTION_ID()函数返回服务器的连接数,也就是到现在为止MySQL服务的连接次数;DATABASE()函数和SCHEMA()函数返回当前数据库名
PASSWORD(str)函数可以对字符串str进行加密。一般情况下,PASSWORD(str)函数主要是用来给用户的密码加密的
MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密
FORMAT(x,n)函数可以将数字x进行格式化,将x保留到小数点后n位。这个过程需要进行四舍五入
CONVERT(s USING cs)函数将字符串s的字符集变成cs
CAST(x AS type)和CONVERT(x,type)这两个函数将x变成type类型