一、数据类型
MySQL数据类型分为三大类:数值型、日期/时间、字符串。
整数类型:
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期/时间类型:
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型:
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
二、高级用法
MySQL中含有很多针对复杂情况设计的一些方便程序员使用的关键字和函数,对于特殊的一些情况使用特殊关键字可以极大的节省时间成本,带来便利。
1、where子句
- where用来对数据进行初次筛选过滤,一般在查询语句、删除语句、修改语句中使用。
- where一般放在SQL语句的后面,但不一定是最后面,还有很多关键字使用在where之后。
- where语句相当于if语句。
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
举例:查询student表中年龄小于20岁的男生。
select * from `student` where `age` < 20 , `gender` = '男';
2、like操作符
- 有时候我们需要获取某字段含有特定字符的所有记录,这时我们就需要在 WHERE 子句中使用 LIKE操作符 。
- %表示所有字符
- LIKE 可以充当 = 使用
举例:查询姓名中带有张的所有学生的信息。
Select * from `student` where `name` like '%张%';
3、通配符
通配符一般与LIKE一起使用。
通配符 | 描述 |
---|---|
% | 替代一个或多个字符 |
_ | 仅替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist] / [!charlist] | 不在字符列中的任何单一字 |
4、Order By 排序
如果我们需要对读取的数据进行排序,我们就可以使用 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序,并返回排序后的结果。
- 可以使用任何字段来作为排序的条件。
- 可以设定多个字段来排序。
- 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认按升序排列。
举例: 查询所有学生,并以年龄升序排序
select * from `student` order by `age` asc;
5、IN操作符
- IN 操作符允许您在 WHERE 子句中规定多个值。
- IN有是的意思。
举例:查询名字是张三的学生的年龄
SELECT`age` FROM `student` WHERE`name` IN '张三';
6、子查询
子查询是嵌套在另一个查询中的查询。放在括号内的查询称为子查询,它也称为内部查询或内部选择。
包含子查询的查询称为外部查询或外部选择。
- 子查询必须用括号括起来。
- 子查询在 SELECT 子句中只能有一个列,除非在主查询中有多列,与子查询的所选列进行比较。
- ORDER BY 不能用在子查询中,虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY,功能与 ORDER BY 相同。
- 子查询返回多于一行,只能与多值运算符一起使用,如 IN 运算符。
- BETWEEN 运算符不能与子查询一起使用,但是,BETWEEN 可在子查询内使用。
举例:查询一名年龄大于15岁的学生;
select * FROM `student` where `age` = (
select 'age' from `student` where `age` > 15
);
7、between
BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
语法: between a and b ,在a和b之间
8、join连接
以上都是单个表的操作,如果我们从多个数据表中读取数据的时候,我们就需要进行表的连接。
JOIN 按照功能大致分为如下三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
9、union操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
条件:UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。
- UNION 操作符用来选取不同的值。若允许重复的值,使用 UNION ALL。
- UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
select age from student
union
select age from teacher
order by age;
10、group by分组
GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。
- 可以用来进行合并表之后以group by后的标签进行分组排序
SELECT s.id, s.name t.name FROM teacher t
LEFT JOIN student s
ON s_id=t.id
GROUP BY s.id;
11、limit子句
limit常用于order by之后,用来返回结果中的一部分。
- 要检索查询返回的行的一部分,请使用LIMIT和OFFSET子句,LIMIT 限制数量 OFFSET 偏移量。
- limit适用于有限的数据库系统中。
SELECT column_name(s)
FROM table_name
order by column_name
LIMIT `number`;
12、null值
NULL 值代表遗漏的未知数据。表的列默认可以存放 NULL 值;如果声明为not null, 则不能有NULL值。
-
无法使用比较运算符来测试 NULL 值,比如 =、< 或 <>。我们必须使用 IS NULL 和 IS NOT NULL 操作符。
-
IS NULL : where column_name is null 选定某列中为null的记录
-
IS NOT NULL : where column_name is not null 选定某列中不为null的记录
-
13、having子句
用 WHERE 关键字无法与聚合函数一起使用,所以出现了HAVING 子句来实现该操作。
HAVING 子句可以让我们筛选分组后的各组数据。
SELECT s.id,s.s_name,t.name FROM teacher t
LEFT JOIN student s
ON s.id = t.id
GROUP BY t.id
HAVING t.id <=3;
14、DISTINCT运算符
从结果中删除重复项
SELECT DISTINCT
column1, column2, ...
FROM
table1;
15、SQL函数
MySQL提供了许多聚合函数,让我们在进行数据库操作中减少计算式的成本,把更多精力放到逻辑本身。
函数分类:
- 聚合函数:返回计算结果。
- 转换函数:数据类型转换。
- 日期函数:处理日期和时间。
- 数学函数:执行算术运算。
- 字符串函数:对字符串、二进制数据或表达式执行操作。
- 系统函数:从数据库返回在SQLSERVER中的值、对象或设置的特殊信息。
- 文本图像函数:有关文本和图像数据的操作。
聚合函数:
它对其应用的每个行集返回一个值。
- AVG(表达式) --返回表达式中所有的平均值,忽略NULL值。
- COUNT(表达式) -- 返回表达式中非NULL值的数量。
- COUNT(*) -- 返回表中的行数,包括NULL值。
- MAX(表达式) -- 返回表达式中的最大值,忽略NULL值。
- MIN(表达式) -- 返回表达式中的最小值,忽略NULL值。
- SUM(表达式) -- 返回表达式中所有的总和,忽略NULL值。
转换函数:
用于数据类型的转换
- CONVERT(Type ,ex): 将ex数据类型转换成Type数据类型
- CAST(ex AS Type): 将ex数据类型转换成Type数据类型
日期函数:
用于管理时间的函数
GETDATE()
:当前的系统日期
DATEADD(日期字段,number,date):
返回带有指定数字(number)的日期(date)
DATEDIFF(
日期字段
,date1,date2)
:返回两个日期中指定的日期部分之间的差值
DATENAME(
日期字段
,date)
:返回日期中日期部分的字符串形式
。 varchar
DATEPART(
日期字段
,date)
:返回日期中指定的日期部分的整数形式
。int
YEAR(date)
:返回指定日期的年份
数值
MONTH(date):
返回指定日期的月份
数值
DAY(date):
返回指定日期的天数
值
数字函数:
计算代数运算
- ABS(num_expr) 返回数值表达式的绝对值。
- ACOS(float_expr) 返回角(以弧度表示),它的余弦值近似于指定的浮点表达式。
- ASIN(float_expr) 返回角(以弧度表示),它的正弦值近似于指定的浮点表达式。
- ATAN(float_expr) 返回角(以弧度表示),它的正切值近似于指定的浮点表达式。
- ATN2(float_expr1, float_expr2) 返回角(以弧度表示),它的正切值在两个近似的浮点表达式之间。
- CEILING(num_expr) 返回大于或等于数值表达式的最小整数。
- COS(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的余弦三角函数的值。
- COT(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的余切三角函数的值。
- DEGREES(num_expr)返回数值表达式表示的弧度值对应的度值。
- EXP(float_expr) 根据指定的近似浮点表达式,返回指数值。
- FLOOR(num_expr) 返回小于或等于数值表达式的最大整数
- LOG(float_expr) 根据指定的近似浮点表达式,返回自然对数值。
- LOG10(float_expr) 根据指定的近似浮点表达式,返回以为底的对数。
- PI() 返回常量值.141592653589793
- POWER(num_expr,y) 返回幂为y的数值表达式的值。
- RADIANS(num_expr) 返回数值表达式表示的度值对应的弧度值。
- RAND([seed]) 随机返回的到之间的近似浮点值,可以对seed指定为整数表达式(可选)。
- ROUND(num_expr,length) 对数值表达式截取指定的整数长度,返回四舍五入后的值。
- SIGN(num_expr) 对正数执行+1操作,对负数和零执行-1操作。
- SIN(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正弦三角函数的值。
- SQUARE(float_expr) 返回浮点表达式的平均值。
- SQRT(float_expr) 返回指定的近似浮点表达式的平方根。
- TAN(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正切三角函数的值。
————————————————
原段链接:https://blog.csdn.net/sundna/article/details/89926586
字符串函数:
主要用于char和varchar数据类型。也可用于binary 和varbinary数据类型的字段
- xpr1+expr2 返回两个表达式的组合形式的字符串。
- ASCII(char_expr) 返回表达式最左边字符的ASCⅡ代码值。
- CHAR(int_expr) 返回到之间的整数表达式的ASCⅡ字符值。如果输入的值不在有效范围内,则返回NULL。
- CHARINDEX('pattern',char_expr) 返回字符表达式中指定模式的起始位置。
- DIFFERENCE(char_expr1,char_expr2) 根据比较两个字符表达式的相似度,返回到之间的值。表示匹配度最佳。
- LEN(char_expr) 返回字符表达式的长度。
- LOWER(char_expr) 将字符表达式全部转换为小写。
- LTRIM(char_expr) 返回删除掉前面空格的字符表达式。
- PATINDEX('%pattern%',expr) 返回表达式中模式第一次出现的起始位置。返回表示不存在模式形式。
- REPLICATE(char_expr,int_expr) 返回重复指定次数的字符表达式产生的字符串。
- REVERSE(char_expr) 反转字符表达式。
- RIGHT(char_expr,int_expr) 返回从字符表达式最右端起根据指定的字符个数得到的字符。
- RTRIM(char_expr) 返回删除掉其后空格的字符表达式。
- SOUNDEX(char_expr) 评估两个字符串的相似度后得到的位代码。
- SPACE(int_expr) 返回包含指定空格数的字符串。
- STR(float_expr[,length[,decimal]]) 返回浮点表达式的字符串表示法。
- STUFF(char_expr1,start,length,char_expr2) 使用字符表达式替换字符表达式的一部分字符,从指定的位置开始替换指定的长度。
- SUBSTRING(char_expr,start,length) 返回从字符表达式的指定位置开始,截取指定长度得到的字符集。
- UPPER(char_expr) 将字符表达式全部转换为大写。
----------------------------------------------------------------------------------------
原段链接:https://blog.csdn.net/sundna/article/details/89926586
系统函数:
用于返回元数据或配置设置。
- COALESCE(expr1,expr2, xprN) 返回第一个非NULL表达式。
- COL_LENGTH('table_name','column_name') 返回列的长度。
- COL_NAME(table_id,column_id) 返回指定的表中的列名。
- DATALENGTH('expr') 返回任何数据类型的实际长度。
- DB_ID([‘database_name']) 返回数据库的标识号。
- DB_NAME([database_id]) 返回数据库的名称。
- GETANSINULL([‘database_name']) 返回数据库的默认空性(Nullability)。
- HOST_ID() 返回工作站的标识号。
- HOST_NAME() 返回工作站的名称。
- IDENT_INCR('table_or_view') 有新的记录添加入到表中时计数加。
- IDENT_SEED('table_or_view') 返回标识列的起始编号。
- INDEX_COL('table_name',index_id,key_id) 返回索引的列名。
- ISNULL(expr,value) 使用指定的值替换的NULL表达式。
- NULLIF(expr1,expr2) Expr1与Expr2相等时,返回Null。
- OBJECT_ID('obj_name') 返回数据库对象标识号。
- OBJECT_NAME('object_id') 返回数据库对象名。
- STATS_DATE(table_id,index_id) 返回上次更新指定索引的统计的日期。
- SUSER_SID([‘login_name']) 返回用户的登录标识号。
- SUSER_ID([‘login_name']) 返回用户的登录标识号。这个函数类似于SUSER_SID()函数,并且保留了向后的兼容性。
- SUSER_SNAME([server_user_id]) 返回用户的登录标识号。
- SUSER_NAME([server_user_id]) 返回用户的登录标识号。这个函数类似于SUSER_SNAME()函数,并且保留了向后的兼容性。
- USER_ID('user_name') 返回用户的数据库标识号。
- USER_NAME(['user_id']) 返回用户的数据库名称。
————————————————
原文链接:https://blog.csdn.net/sundna/article/details/89926586
文本图像函数:
返回有关文本和图像数据所需的信息
TEXTPTR(col_name)
返回varbinary格式的文本指针值。对文本指针进行检查以确保它指向第一个文本页。
TEXTVALID('table_name.col_name',text_ptr)
检查给定的文本指针是否有效。返回表示有效,返回表示指针无效。