一、navicat 操作快捷键
一般都用naviact来操作数据库,故总结一下相关的快捷键:
CTRL+L 历史日志
CTRL+TAB 或 SHIFT+CTRL+TAB 下一个窗口或选项卡
CTRL+Q 新建查询
CTRL+F 查找字段
F3 查找下一个字段
F5 刷新
ESC 全选
CTRL+D 设计表
SHIFT+CTRL+R 运行当前语句
二、相关sql语句总结:
1)对库的操作(不常用)
--创建库
create database 库名;
--创建库时判断库是否存在,不存在则创建
create database if not exists 库名;
--查看所有数据库
show databases;
--使用指定数据库
use 库名;
--查看当前指定数据库包含的数据表
show tables;
--查看数据库的结构定义信息
show create database 库名;
--删除数据库
drop database 库名;
--修改数据库的字符集为utf8
alter database 库名 character set utf8;
2) 对表的操作:创建表、删除表、在表中增加字段、在表中减少字段,修改表字段名等
对表的操作:
--删除表
drop table 表名;
--删除表时判断表是否存在,若存在则删除
drop table if exists test111 ;
--创建表
create table test111 (
test_id bigint not null comment '主键id',
test_name varchar(50) default null comment '测试名称',
test_age int default null comment '测试年龄',
test_data date default null comment '测试日期',
primary key (`test_id`)
) comment '测试表'
-查看表结构
desc 表名;
--查看创建表的SQL语句
show create table 表名;
--修改表名
alter table 表名 rename to 新的表名;
对字段的操作:
--添加一个新的字段
alter table 表名 add 字段; 字段类型;
--修改字段名
alter table 表名 rename column 字段名 to 新的字段名;
--修改字段类型(注意类型修改前后数据是否兼容)
alter table 表名 modify column 字段名 新的字段类型;
--删除一个字段
alter table 表名 drop 字段名;
--添加一个字段
alter table test222 add test_year varchar(20) not null default '2022' after test_name
3)对数据的操作(增删改)
1、插入数据
--有多少个字段,就要写多少个值,且是一一对应的
insert into 表名 values(值1,值2,值3...值n);
--此方法要写出要插入的字段,并一一对应插入值
insert into 表名(字段1,字段2...字段n) values(值1,值2...值n);
2、删除数据(delete、truncate)
--删除表中所有数据
delete from 表名;
--删除表中指定的数据
delete from 表名 where 字段 = 值;
--删除表中所有数据(先删除整张表,然后创建一张一样的空表,此方法更高效)
truncate table 表名;
3、修改数据(update)
--无限制条件的修改,会修改整张表
update 表名 set 字段 = 值;
--有限制条件的修改,只修改特定记录
update 表名 set 字段 = 值 where 条件(字段 = 值);
4)关于查询的操作
查询规则:
-
每条命令以 ; 或 \g 或 \G 结束
-
关键字不能被缩写也不能分行
-
关于标点符号
-
必须保证所有的()、单引号、双引号是成对结束的
-
必须使用英文状态下的半角输入方式
-
字符串型和日期时间类型的数据可以使用单引号(' ')表示
-
列的别名,尽量使用双引号(" "),而且不建议省略as
-
字段用双引号,条件用单引号
-
-
MySQL 在 Windows 环境下是大小写不敏感的
-
MySQL 在 Linux 环境下是大小写敏感的
-
数据库名、表名、表的别名、变量名是严格区分大小写的
-
关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
-
-
推荐采用统一的书写规范:
-
数据库名、表名、表别名、字段名、字段别名等都小写
-
SQL 关键字、函数名、绑定变量等都大写
-
-
数据库、表名不得超过30个字符,变量名限制为29个
-
必须只能包含 A–Z, a–z, 0–9, _共63个字符
-
数据库名、表名、字段名等对象名中间不要包含空格 同一个MySQL软件中,数据库不能同名;同一个库中,表不能重名;
-
同一个表中,字段不能重名 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用,请在SQL语句中使 用`(着重号)引起来
-
保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据 类型在一个表里是整数,那在另一个表里可就别变成字符型了
1.普通条件查询
--查询表中所有数据
select *from 表名;
-- 查询在...到...之间(between and / && / and)
--查询users表中年龄在18~25岁之间的记录
--方式1 between..and..
select *from users where age between 18 and 25;
--方式2 &&
select *from users where age>=18 && age<=25;
--方式3 and
select *from users where age>=18 and age<=25;
--单个条件(or / in)
--查询users表中年龄为18,20,25岁的记录
--方式1 or
select *from users where age=18 or age=20 or age=25;
--方式2 in
select *from users where age in(18,20,25);
--多个条件(and)
--查询users表中年龄为23,性别为女,名字为小楠的记录
select *from users where age=23 and gender='女' and name='小楠';
--查询不为NULL值(is not null),为NULL值(is null)
--查询users表中序号不为空的记录
select *from users where id is not null;
--查询user表中序号为空的记录
select *from users where id is null;
--模糊查询(like)
--查询users表中所在城市不相同的记录
--select distinct 字段 from 表名;
select distinct city from users;
--去除重复记录查询(distinct)
--查询users表中记录,并以年龄升序排序
select *from users order by age; --默认升序
--排序查询(order by)
--查询users表中记录,并以年龄降序排序
select *from users order by age desc;--desc降序
--查询users表中记录,并体育成绩降序,年龄降序
select *from users order by PE desc,age desc;
--列的别名
* 重命名一个列 便于计算
* 紧跟列名,也可以在列名和别名之间加入关键字AS,别名使用双引号,以便在别名中包含空格或特 殊的字符并区分大小写。
* AS 可以省略
* 建议别名简短,见名知意
SELECT last_name AS name, commission_pct comm
FROM employees;
--空值参与运算
* 空值:null ( 不等同于0, ’ ‘, ’null‘ )
* 实际问题的解决方案:引入IFNULL
* 这里你一定要注意,在 MySQL 里面, 空值不等于空字符串。一个空字符串的长度是 0,而一个空值的长 度是空。而且,在 MySQL 里面,空值是占用空间的。
SELECT employee_id, salary "月工资", salary * (1 + IFNULL(commission_pct, 0)) * 12 "年工资" FROM employees;
-- 着重号 ``
* 必须保证你的字段没有和保留字、数据库系统或常见方法冲突。
* 如果坚持使用,在SQL语句中使用 \` \` 引起来。
* 就是说有保留字、关键字,直接保存会失败,如果一定要用,可以使用单引号括起来
SELECT * FROM `order`;
-- 查询常数
* 可以查询不存在的字段 设置为常数,每个常数会匹配到每一条数据上
SELECT '小张科技' as "公司名", employee_id, last_name FROM employees;
2.使用聚合函数查询
①计算和(sum)
select sum(字段) (as sumvalue) from 表名;
②计算最大值(max)
select max(字段) (as maxvalue) from 表名;
③计算最小值(min)
select min(字段) (as minvalue) from 表名;
④计算平均值(avg)
select avg(字段) (as avgvalue) from 表名;
⑤计算个数(count)
select count(字段) (as totalcount) from 表名;
3.分组、分页查询
分组查询(group by)
--查询users表中的记录,按照性别分组,查询男,女的体育成绩平均分
select gender,avg(PE) from users group by gender;
--查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数
select gender, avg(PE),count(id) from users group by gender;
--查询users表中的记录, 按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人,不参与分组
select gender, avg(PE),count(id) from users where PE > 60 group by gender;
--查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人,不参与分组,分组之后,人数要大于2个人
select gender,avg(PE),count(id) from users where PE > 60 group by gender having count(id)>2;
分页查询(limit)
注意:第一条记录的索引是0
--查询users表中的前10行条记录
select *from users limit 10;
--查询users表中第2~11条记录 (从第2条记录开始累加10条记录)
select *from users limit 1,10;
--查询users表中第5~17条记录 (从第5条记录开始累加13条记录)
4.内连接、外连接、子查询
内连接查询
--语法1 (隐式内连接)
select 字段1,字段2...
from 表1,表2...
where 过滤条件;
--语法2 (显式内连接)
select 字段1,字段2...
from 表1 inner join 表2 ...
外连接查询
外连接查询分为左外连接查询和右外连接查询
--左外连接
select 字段1,字段2..
from 表1 left (outer) join 表2 on 过滤条件;
--右外连接
select 字段1,字段2..
from 表1 right (outer) join 表2 on 过滤条件;
区别如下:
左外连接:是表1和表2的交集再并上表1的其他数据
右外连接:是表1和表2的交集再并上表2的其他数据
子查询就是将查询语句作为条件:
select * from user where user_name in ( select user_name from user where ....)
5.运算符
1) 算数运算符
SELECT 100 + 0, 100 + 50 * 30, 100 - 35.5 FROM DUAL;
# 一个整数类型的值对整数进行加法和减法操作,结果还是一个整数;
# 一个整数类型的值对浮点数进行加法和减法操作,结果是一个浮点数;
# 在Java中, + 的左右两边如果有字符串,那么表示字符串的拼接。但是在MySQL中 + 只表示数值相加。如果遇到非数值类型,先尝试转成数值,如果转失败,就按0计算。(注:MySQL 中字符串拼接要使用字符串函数CONCAT()实现)
# 在数学运算中,0不能用作除数,在MySQL中,一个数除以0为NULL。
2)比较运算符
# 比较运算符用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真则返回1,比较的结果 为假则返回0,其他情况则返回NULL。
# 比较运算符经常被用来作为SELECT查询语句的条件来使用,返回符合条件的结果记录。
# 如果等号两边的值、字符串或表达式中有一个为NULL,则比较结果为NULL。
SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL , NULL = NULL;
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 = 1 | 1 = '1' | 1 = 0 | 'a' = 'a' | (5 + 3) = (2 + 6) | '' = NULL | NULL = NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
| 1 | 1 | 0 | 1 | 1 | NULL | NULL |
+-------+---------+-------+-----------+-------------------+-----------+-------------+
SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM DUAL;
+-------+-----------+-----------+
| 1 = 2 | 0 = 'abc' | 1 = 'abc' |
+-------+-----------+-----------+
| 0 | 1 | 0 |
+-------+-----------+-----------+
+ 如果等号两边的值、字符串或表达式都为字符串,则MySQL会按照字符串进行比较,其比较的 是每个字符串中字符的ANSI编码是否相等。
+ 如果等号两边的值都是整数,则MySQL会按照整数来比较两个值的大小。
+ 如果等号两边的值一个是整数,另一个是字符串,则MySQL会将字符串转化为数字进行比较。
+ 如果等号两边的值、字符串或表达式中有一个为NULL,则比较结果为NULL。
SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual;
+-----------+---------+-------------+---------------------+-------------+---------------+
| 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=> NULL |
+-----------+---------+-------------+---------------------+-------------+---------------+
| 1 | 0 | 1 | 1 | 0 | 1 |
+-----------+---------+-------------+---------------------+-------------+---------------+
# 可以看到,使用安全等于运算符时,两边的操作数的值都为NULL时,返回的结果为1而不是NULL,其他返回结果与等于运算符相同。
3)空运算符 、 非空运算符
# 空运算符 (IS NULL 或者 ISNULL) 判断一个值是否为NULL,如果为NULL则返回1,否则返回0。
# 非空运算符(IS NOT NULL)判断一个值是否不为NULL,如果不为NULL则返回1,否则返回0。
SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
| 1 | 1 | 0 | 0 |
+--------------+--------------+-------------+-----------+
4)最小值运算符 、 最大值运算符
### 最小值运算符
# 语法格式为:LEAST(值1,值2,...,值n)。其中,“值n”表示参数列表中有n个值。在有 两个或多个参数的情况下,返回最小值。
SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
| 0 | a | NULL |
+---------------+--------------------+-----------------+
# 由结果可以看到,当参数是整数或者浮点数时,LEAST将返回其中最小的值;当参数为字符串时,返回字 母表中顺序最靠前的字符;当比较值列表中有NULL时,不能判断大小,返回值为NULL。
### 最大值运算符
# 语法格式为:GREATEST(值1,值2,...,值n)。其中,n表示参数列表中有n个值。当有 两个或多个参数时,返回值为最大值。假如任意一个自变量为NULL,则GREATEST()的返回值为NULL。
SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
| 2 | c | NULL |
+-----------------+-----------------------+--------------------+
# 由结果可以看到,当参数中是整数或者浮点数时,GREATEST将返回其中最大的值;当参数为字符串时, 返回字母表中顺序最靠后的字符;当比较值列表中有NULL时,不能判断大小,返回值为NULL。
5)BETWEEN AND运算符
# BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B,
此时,当C大于或等于A,并且C小于或等于B时,结果为1,否则结果为0。
6) IN运算符 、NOT IN运算符
### IN运算符
### IN运算符用于判断给定的值是否是IN列表中的一个值,如果是则返回1,否则返回0。如果给 定的值为NULL,或者IN列表中存在NULL,则结果为NULL。
mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
| 1 | 0 | NULL | 1 |
+----------------------+------------+-------------------+--------------------+
### NOT IN运算符
# NOT IN运算符用于判断给定的值是否不是IN列表中的一个值,如果不是IN列表中的一 个值,则返回1,否则返回0。
7) LIKE运算符
### LIKE运算符主要用来匹配字符串,通常用于模糊匹配,如果满足条件则返回1,否则返回 0。如果给定的值或者匹配条件为NULL,则返回结果为NULL。
### “%”:匹配0个或多个字符。
### “_”:只能匹配一个字符。
8) ESCAPE
### 回避特殊符号的:使用转义符。例如:将[%]转为[$%]、[]转为[$],然后再加上[ESCAPE‘$’]即可。
SELECT job_id FROM jobs WHERE job_id LIKE ‘IT\_%‘;
### 如果使用\表示转义,要省略ESCAPE。如果不是\,则要加上ESCAPE。
SELECT job_id FROM jobs WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;
9) REGEXP运算符
### REGEXP运算符用来匹配字符串,语法格式为: expr REGEXP 匹配条件 。
#(1)‘^’匹配以该字符后面的字符开头的字符串。
#(2)‘$’匹配以该字符前面的字符结尾的字符串。
#(3)‘.’匹配任何一个单字符。
#(4)“[...]”匹配在方括号内的任何字符。例如,“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围,使用一 个‘-’。“[a-z]”匹配任何字母,而“[0-9]”匹配任何数字。
#(5)‘*’匹配零个或多个在它前面的字符。例如,“x*”匹配任何数量的‘x’字符,“[0-9]*”匹配任何数量的数字, 而“*”匹配任何数量的任何字符。
6.逻辑运算符
### 位运算符是在二进制数上进行计算的运算符。
位运算符会先将操作数变成二进制数,然后进行位运算, 最后将计算结果从二进制变回十进制数。
### OR ||, AND &&, NOT !, XOR
SELECT last_name, salary, department_id FROM employees WHERE department_id = 10 OR department_id = 20;
SELECT last_name, salary, department_id FROM employees WHERE department_id = 10 AND department_id = 20;
SELECT last_name, salary, department_id FROM employees WHERE department_id = 50 AND salary > 6000;
SELECT employee_id, last_name, salary FROM employees WHERE NOT salary BETWEEN 6000 AND 8000;
SELECT employee_id, last_name, salary FROM employees WHERE !(salary BETWEEN 6000 AND 8000);
#2个条件只满足其1而另一个不满足
SELECT last_name, salary, department_id FROM employees WHERE department_id = 50 XOR salary > 6000;
#AND优先级高于OR
7.位运算
## 位运算
# 位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数,然后进行位运算, 最后将计算结果从二进制变回十进制数。
### &, |, ^, ~, >>, <<
SELECT 1 & 1, 12 | 5, 12 ^ 5 FROM DUAL;
SELECT 4 >> 1, 4 << 1, 5 >> 1 FROM DUAL
SELECT 10 & ~1 FROM DUAL;
8.运算符的优先级
### 数字编号越大,优先级越高,优先级高的运算符先进行计算。
扩展:使用正则表达式查询:
正则表达式通常被用来检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如,从一个文本文件中提取电话号码,查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等,这些地方都可以使用正则表达式。正则表达式强大而且灵活,可以应用于非常复杂的查询。
MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配列表
1. 查询以特定字符或字符串开头的记录 字符‘^’匹配以特定字符或者字符串开头的文本。
在fruits表中,查询f_name字段以字母‘b’开头的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP '^b';
2. 查询以特定字符或字符串结尾的记录 字符‘$’匹配以特定字符或者字符串结尾的文本。
在fruits表中,查询f_name字段以字母‘y’结尾的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'y$';
3. 用符号"."来替代字符串中的任意一个字符 字符‘.’匹配任意一个字符。 在fruits表中,查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
4. 使用"*"和"+"来匹配多个字符 星号‘*’匹配前面的字符任意多次,包括0次。加号‘+’匹配前面的字符至
少一次。
在fruits表中,查询f_name字段值以字母‘b’开头且‘b’后面出现字母‘a’至少一次的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
5. 匹配指定字符串 正则表达式可以匹配指定字符串,只要这个字符串在查询文本中即可,如要匹配多个
字符串,多个字符串之间使用分隔符‘|’隔开。
在fruits表中,查询f_name字段值包含字符串“on”的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'on';
在fruits表中,查询f_name字段值包含字符串“on”或者“ap”的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
之前介绍过,LIKE运算符也可以匹配指定的字符串,但与REGEXP不同,LIKE匹配的字符串如果在文本中
间出现,则找不到它,相应的行也不会返回。REGEXP在文本内进行匹配,如果被匹配的字符串在文本中
出现,REGEXP将会找到它,相应的行也会被返回。对比结果如下所示。
在fruits表中,使用LIKE运算符查询f_name字段值为“on”的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name like 'on';
6. 匹配指定字符中的任意一个 方括号“[]”指定一个字符集合,只匹配其中任何一个字符,即为所查找的
文本。
在fruits表中,查找f_name字段中包含字母‘o’或者‘t’的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
在fruits表中,查询s_id字段中包含4、5或者6的记录,SQL语句如下:
SELECT * FROM fruits WHERE s_id REGEXP '[456]';
7. 匹配指定字符以外的字符 “[^字符集合]” 匹配不在指定集合中的任何字符。
在fruits表中,查询f_id字段中包含字母a~e和数字1~2以外字符的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
8. 使用{n,}或者{n,m}来指定字符串连续出现的次数 “字符串{n,}”表示至少匹配n次前面的字符;“字符串
{n,m}”表示匹配前面的字符串不少于n次,不多于m次。例如,a{2,}表示字母a连续出现至少2次,也可以
大于2次;a{2,4}表示字母a连续出现最少2次,最多不能超过4次。
在fruits表中,查询f_name字段值出现字母‘x’至少2次的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
在fruits表中,查询f_name字段值出现字符串“ba”最少1次、最多3次的记录,SQL语句如下:
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
9.排序与分页
1) order by 排序
## 排序规则
## 使用 ORDER BY 子句排序
* ASC(ascend): 升序
* DESC(descend):降序
* ORDER BY 子句在SELECT语句的结尾。
### 单列排序 默认就是升序 ASC ,如果要切换降序要在后面加上 DESC
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;
### 多列排序
* 可以使用不在SELECT列表中的列排序。
* 在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序。如果第 一列数据中所有值都是唯一的,将不再对第二列进行排序。
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
2) limit 分页
* 格式:
LIMIT [位置偏移量,] 行数
* 举例:
--前10条记录:
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录:
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录:
SELECT * FROM 表名 LIMIT 20,10;
* MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT 4,3;”返回的结果相同。
* 分页显式公式:(当前页数-1)* 每页条数,每页条数
SELECT * FROM table
LIMIT(PageNo - 1) * PageSize, PageSize;
* 注意:LIMIT 子句必须放在整个SELECT语句的最后!
* 使用LIMIT的好处
约束返回结果的数量可以 减少数据表的网络传输量 ,也可以 提升查询效率 。
如果我们知道返回结果只有 1 条,就可以使用 LIMIT 1 ,告诉 SELECT 语句只需要返回一条记录即可。
这样的好处就是 SELECT 不需 要扫描完整的表,只需要检索到一条符合条件的记录即可返回。
10.多表查询
1)笛卡尔积
笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
- 笛卡尔积的错误会在下面条件下产生:
- 省略多个表的连接条件(或关联条件)
- 连接条件(或关联条件)无效
- 所有表中的所有行互相连接 为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件。 加入连接条件后,查询语法:
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; #连接条件
- 在 WHERE子句中写入连接条件。 正确写法:
#案例:查询员工的姓名及其部门名称 SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;
- 在表中有相同列时,在列名之前加上表名前缀。
2)自链接 、内连接
* 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
**SQL92语法**
SELECT emp.employee_id, dep.department_name
FROM employee emp, department dep
WHERE emp.`department_id` = dep.`department_id`;
**SQL99语法**
SELECT emp.employee_id, dep.department_name
FROM employee emp JOIN department dep
ON emp.`department_id` = dep.`department_id`;
* 内连接(INNER JOIN)的实现
* 语法:
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
* 示例:
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e INNER JOIN departments d
ON (e.department_id = d.department_id);
3)外链接、左外连接、右外连接
* 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
* 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的
行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
* 如果是左外连接,则连接条件中左边的表也称为 主表 ,右边的表称为 从表 。
* 如果是右外连接,则连接条件中右边的表也称为 主表 ,左边的表称为 从表 。
SQL92:使用(+)创建连接
* 在 SQL92 中采用(+)代表从表所在的位置。即左或右外连接中,(+) 表示哪个是从表。
O* racle 对 SQL92 支持较好,而 MySQL 则不支持 SQL92 的外连接。
#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;
而且在 SQL92 中,只有左外连接和右外连接,没有满(或全)外连接。
SQL99语法实现多表查询
* 3.1 基本语法
* 使用JOIN...ON子句创建连接的语法结构:
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强,即使再多的表进行连接也都清晰
可见。如果你采用 SQL92,可读性就会大打折扣。
语法说明:
* 可以使用 ON 子句指定额外的连接条件。
* 这个连接条件是与其它条件分开的。
* ON 子句使语句具有更高的易读性。
* 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的,都表示内连接
# 左外连接(LEFT OUTER JOIN)
* 语法:
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
* 示例:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER(可省略) JOIN departments d
ON (e.department_id = d.department_id) ;
# 右外连接(RIGHT OUTER JOIN)
* 语法
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
* 示例:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
需要注意的是,LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中,在 SQL92 中不存在,
只能用 (+) 表示。
# 满外连接(FULL OUTER JOIN)
* 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
* SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
* 需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT join代替。
11.UNION的使用
# 合并查询结果 利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。
# 合并时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
* 语法:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
* UNION 操作符返回两个查询的结果集的并集,去除重复记录。
* UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
* 注意:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效
率。
* 举例:
* 方式1
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
* 方式2
SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';
12.7种SQL JOINS的实现
sql实现:
#第1个 左上图:左外连接 (A中有的,B中有关联的,B中没关联的不查)
SELECT employee_id,last_name,department_name
FROM employees a LEFT JOIN departments b
ON a.`department_id` = b.`department_id`;
#第2个 右上图:右外连接 (B中有的,A中有关联的,A中没有的不查)
SELECT employee_id,last_name,department_name
FROM employees a RIGHT JOIN departments b
ON a.`department_id` = b.`department_id`;
#第3个 中间图:内连接(A、B中都有的)
SELECT employee_id,last_name,department_name
FROM employees a JOIN departments b
ON a.`department_id` = b.`department_id`;
#第4个 左中图:左外链接,但A中的关联条件不存在的(A中有的且B中没有关联的)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
#第5个 右中图:右外连接,但B中关联条件不存在的(B中有的且A中没有关联的)
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
#第6个 左下图:左外链接+右外连接+UNION
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作,效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#第7个 右下图:左外链接+右外连接+UNION(但连接条件时互相不存在的)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
12.SQL99语法新特性
1)自然连接
# SQL99 在 SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN 用来表示自然连接。我们可以把
自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ,然后进行 等值
连接 。
* 在SQL92标准中:
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
* 在 SQL99 中你可以写成:
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
2)USING连接
# 当我们进行连接的时候,SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。
但是只能配合JOIN一起使用。比如:
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
# 你能看出与自然连接 NATURAL JOIN 不同的是,USING 指定了具体的相同的字段名称,你需要在 USING
的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下
面的 SQL 查询结果是相同的:
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
# 表连接的约束条件可以有三种方式:WHERE, ON, USING
*8WHERE:适用于所有关联查询
*ON :只能和JOIN一起使用,只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写,但分开写可读性更好。
*USING:只能和JOIN一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字段值相等
#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#把关联条件写在on后面,只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
#把关联字段写在using()中,只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同,而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);
#n张表关联,需要n-1个关联条件
#查询员工姓名,基本工资,部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
# 注意:
* 我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样,非常消耗资源,会让 SQL 查询性能下降得很严重,因此不要连接不必要的表。在许多 DBMS 中,也都会有最大连接表的限制。
* 【强制】超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致;多表关联查询时, 保
证被关联的字段需要有索引。
说明:即使双表 join 也要注意表索引、SQL 性能。
- 在正式开始讲连接表的种类时,我们首先需要知道 SQL 存在不同版本的标准规范,因为不同规范下的表 连接操作是有区别的。
- SQL 有两个主要的标准,分别是 SQL92 和 SQL99 。92 和 99 代表了标准提出的时间,SQL92 就是 92 年 提出的标准规范。当然除了 SQL92 和 SQL99 以外,还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011 和 SQL:2016 等其他的标准。
- 这么多标准,到底该学习哪个呢?实际上最重要的 SQL 标准就是 SQL92 和 SQL99。一般来说 SQL92 的 形式更简单,但是写的 SQL 语句会比较长,可读性较差。而 SQL99 相比于 SQL92 来说,语法更加复杂, 但可读性更强。我们从这两个标准发布的页数也能看出,SQL92 的标准有 500 页,而 SQL99 标准超过了 1000 页。实际上从 SQL99 之后,很少有人能掌握所有内容,因为确实太多了。就好比我们使用 Windows、Linux 和 Office 的时候,很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能,满 足日常工作的需求即可。
- SQL92 和 SQL99 是经典的 SQL 标准,也分别叫做 SQL-2 和 SQL-3 标准。也正是在这两个标准发布之 后,SQL 影响力越来越大,甚至超越了数据库领域。现如今 SQL 已经不仅仅是数据库领域的主流语言, 还是信息领域中信息处理的主流语言。在图形检索、图像检索以及语音检索中都能看到 SQL 语言的使用。
三、单行函数
我们在使用 SQL 语言的时候,不是直接和这门语言打交道,而是通过它使用不同的数据库软件,即 DBMS。DBMS 之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只有很少的函数是 被 DBMS 同时支持的。比如,大多数 DBMS 使用(||)或者(+)来做拼接符,而在 MySQL 中的字符串拼 接函数为concat()。大部分 DBMS 会有自己特定的函数,这就意味着采用 SQL 函数的代码可移植性是很 差的,因此在使用函数的时候需要特别注意。
MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制 函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里,我将这些丰富的内置函数再分为两类: 单行函数 、 聚合函数(或分组函数) 。
单行函数:
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
1) 数值函数
基本函数:
ABS(x)
|
返回
x
的绝对值
|
SIGN(X)
|
返回
X
的符号。正数返回
1
,负数返回
-1
,
0
返回
0
|
PI()
|
返回圆周率的值
|
CEIL(x)
,
CEILING(x)
|
返回大于或等于某个值的最小整数
|
FLOOR(x)
|
返回小于或等于某个值的最大整数
|
LEAST(e1,e2,e3…)
|
返回列表中的最小值
|
GREATEST(e1,e2,e3…)
|
返回列表中的最大值
|
MOD(x,y)
|
返回
X
除以
Y
后的余数
|
RAND()
|
返回
0~1
的随机值
|
RAND(x)
|
返回
0~1
的随机值,其中
x
的值用作种子值,相同的
X
值会产生相同的随机
数
|
ROUND(x)
|
返回一个对
x
的值进行四舍五入后,最接近于
X
的整数
|
ROUND(x,y)
|
返回一个对
x
的值进行四舍五入后最接近
X
的值,并保留到小数点后面
Y
位
|
TRUNCATE(x,y)
|
返回数字
x
截断为
y
位小数的结果
|
SQRT(x)
|
返回
x
的平方根。当
X
的值为负数时,返回
NULL
|
SELECT
ABS(-123),ABS(32), # 返回绝对值
SIGN(-23),SIGN(43), # 返回数值的符号,正数返回1 负数返回-1,0返回0
PI(), # 返回圆周率
CEIL(32.32),CEILING(-43.23), #向上取整
FLOOR(32.32),FLOOR(-43.23), #向下取整
MOD(12,5) # x除于y的余数
FROM DUAL;
SELECT RAND(),RAND(),
RAND(10),RAND(10),
RAND(-1),RAND(-1) # 返回随机数,括号内数字相同,则返回相同
FROM DUAL;
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1), # 四舍五入
TRUNCATE(12.66,1),TRUNCATE(12.66,-1) # 返回数字x截断为y位小数的结果
FROM DUAL;
SELECT TRUNCATE(ROUND(12.55), 1) #单行函数可以嵌套
FROM DUAL;
角度与弧度互换:
RADIANS(x)
|
将角度转化为弧度,其中,参数
x
为角度值
|
DEGREES(x)
|
将弧度转化为角度,其中,参数
x
为弧度值
|
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;
三角函数:
SIN(x)
|
返回
x
的正弦值,其中,参数
x
为弧度值
|
ASIN(x)
|
返回
x
的反正弦值,即获取正弦为
x
的值。如果
x
的值不在
-1
到
1
之间,则返回
NULL
|
COS(x)
|
返回
x
的余弦值,其中,参数
x
为弧度值
|
ACOS(x)
|
返回
x
的反余弦值,即获取余弦为
x
的值。如果
x
的值不在
-1
到
1
之间,则返回
NULL
|
TAN(x)
|
返回
x
的正切值,其中,参数
x
为弧度值
|
ATAN(x)
|
返回
x
的反正切值,即返回正切值为
x
的值
|
ATAN2(m,n)
|
返回两个参数的反正切值
|
COT(x)
|
返回
x
的余切值,其中,
X
为弧度值
|
SELECT
SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1)
)
FROM DUAL;
指数和对数:
POW(x,y)
POWER(X,Y)
|
返回
x
的
y
次方
|
EXP(X)
|
返回
e
的
X
次方,其中
e
是一个常数,
2.718281828459045
|
LN(X)
,
LOG(X)
|
返回以
e
为底的
X
的对数,当
X <= 0
时,返回的结果为
NULL
|
LOG10(X)
|
返回以
10
为底的
X
的对数,当
X <= 0
时,返回的结果为
NULL
|
LOG2(X)
|
返回以
2
为底的
X
的对数,当
X <= 0
时,返回
NULL
|
SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4) FROM DUAL;
BIN(x)
|
返回
x
的二进制编码
|
HEX(x)
|
返回
x
的十六进制编码
|
OCT(x)
|
返回
x
的八进制编码
|
CONV(x,f1,f2)
|
返回
f1
进制数变成
f2
进制数
|
#进制转换
SELECT BIN(10),HEX(10),OCT(10),
#等同于上面
CONV(10, 10, 2),CONV(10, 10, 16),CONV(10, 10, 8)
FROM DUAL;
字符串函数:
ASCII(S)
|
返回字符串
S
中的第一个字符的
ASCII
码值
|
CHAR_LENGTH(s)
|
返回字符串
s
的字符数。作用与
CHARACTER_LENGTH(s)
相同
|
LENGTH(s)
|
返回字符串
s
的字节数,和字符集有关
|
CONCAT(s1,s2,......,sn)
|
连接
s1,s2,......,sn
为一个字符串
|
CONCAT_WS(x,s1,s2,......,sn)
|
同
CONCAT(s1,s2,...)
函数,但是每个字符串之间要加上
x
|
INSERT(str,idx,len,replacestr)
|
将字符串
str
从第
idx
位置开始,
len
个字符长的子串替换为字符串
replacestr
|
REPLACE(str, a, b)
|
用字符串
b
替换字符串
str
中所有出现的字符串
a
|
UPPER(s)
或
UCASE(s)
|
将字符串
s
的所有字母转成大写字母
|
LOWER(s)
或
LCASE(s)
|
将字符串
s
的所有字母转成小写字母
|
LEFT(str,n)
|
返回字符串
str
最左边的
n
个字符
|
RIGHT(str,n)
|
返回字符串
str
最右边的
n
个字符
|
LPAD(str, len, pad)
|
用字符串
pad
对
str
最左边进行填充,直到
str
的长度为
len
个字符
|
RPAD(str ,len, pad)
|
用字符串
pad
对
str
最右边进行填充,直到
str
的长度为
len
个字符
|
LTRIM(s)
|
去掉字符串
s
左侧的空格
|
RTRIM(s)
|
去掉字符串
s
右侧的空格
|
TRIM(s)
|
去掉字符串
s
开始与结尾的空格
|
TRIM(s1 FROM s)
|
去掉字符串
s
开始与结尾的
s1
|
TRIM(LEADING s1 FROM s)
|
去掉字符串
s
开始处的
s1
|
TRIM(TRAILING s1 FROM s)
|
去掉字符串
s
结尾处的
s1
|
REPEAT(str, n)
|
返回
str
重复
n
次的结果
|
SPACE(n)
|
返回
n
个空格
|
STRCMP(s1,s2)
|
比较字符串
s1,s2
的
ASCII
码值的大小
|
SUBSTR(s,index,len)
|
返回从字符串
s
的
index
位置其
len
个字符,作用与
SUBSTRING(s,n,len)、
MID(s,n,len)相同
|
LOCATE(substr,str)
|
返回字符串
substr
在字符串
str
中首次出现的位置,作用于
POSITION(substr IN str)、
INSTR(str,substr)
相同。未找到,返回
0
|
ELT(m,s1,s2,…,sn)
|
返回指定位置的字符串,如果
m=1
,则返回
s1
,如果
m=2
,则返回
s2
,如果m=n
,则返回
sn
|
FIELD(s,s1,s2,…,sn)
|
返回字符串
s
在字符串列表中第一次出现的位置
|
FIND_IN_SET(s1,s2)
|
返回字符串
s1
在字符串
s2
中出现的位置。其中,字符串
s2
是一个以逗号分隔的字符串
|
REVERSE(s)
|
返回
s
反转后的字符串
|
NULLIF(value1,value2)
|
比较两个字符串,如果
value1
与
value2
相等,则返回
NULL
,否则返回value1
|
# 注意:MySQL中,字符串的位置是从1开始的。
#字符串连接
SELECT CONCAT(emp.last_name, ' worked for ', mgr.last_name) detail
FROM employees emp JOIN employees mgr
WHERE emp.manager_id = mgr.employee_id;
#用特定符号连接
SELECT CONCAT_WS('-', 'h', 'e', 'l')
FROM DUAL;
#在这里字符串索引从1开始,字符替换
SELECT INSERT('hello world', 2, 3, 'aaaaa'),
REPLACE('hello', 'llo', 'mmm')
FROM DUAL;
SELECT UPPER('HelLo'), LOWER('HelLo')
FROM DUAL;
SELECT LEFT('HelLo', 2),#取左边两个
RIGHT('HelLo', 3)
FROM DUAL;
SELECT employee_id, last_name, LPAD(salary, 10, '*')#实现右对齐,RPAD左对齐
FROM employees;
日期和时间函数:
获取日期、时间: | |
CURDATE()
,
CURRENT_DATE()
|
返回当前日期,只包含年、月、日
|
CURTIME()
,
CURRENT_TIME()
|
返回当前时间,只包含时、分、秒
|
NOW()
/ SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()
|
返回当前系统日期和时间
|
UTC_DATE()
|
返回
UTC
(世界标准时间)日期
|
UTC_TIME()
|
返回
UTC
(世界标准时间)时间
|
日期与时间戳的转换:
| |
UNIX_TIMESTAMP()
|
以
UNIX
时间戳的形式返回当时间
SELECT UNIX_TIMESTAMP() ->1634348884
|
UNIX_TIMESTAMP(date)
|
将时间
date
以
UNIX
时间戳的形式返回。
|
FROM_UNIXTIME(timestamp)
|
将
UNIX
时间戳的时间转换为普通格式的时间
|
获取月份、星期、星期数、天数等函数:
| |
YEAR(date) / MONTH(date) / DAY(date)
|
返回具体的日期值
|
HOUR(time) / MINUTE(time) / SECOND(time)
|
返回具体的时间值
|
MONTHNAME(date)
|
返回月份:
January
,
...
|
DAYNAME(date)
|
返回星期几:
MONDAY
,
TUESDAY.....SUNDAY
|
WEEKDAY(date)
|
返回周几,注意,周
1
是
0
,周
2
是
1
,。。。周日是
6
|
QUARTER(date)
|
返回日期对应的季度,范围为
1
~
4
|
WEEK(date)
,
WEEKOFYEAR(date)
|
返回一年中的第几周
|
DAYOFYEAR(date)
|
返回日期是一年中的第几天
|
DAYOFMONTH(date)
|
返回日期位于所在月份的第几天
|
DAYOFWEEK(date)
|
返回周几,注意:周日是
1
,周一是
2
,。。。周六是7
|
EXTRACT(type FROM date)
|
返回指定日期中特定的部分,
type
指定返回的值
|
时间和秒钟转换的函数:
| |
TIME_TO_SEC(time)
|
将
time
转化为秒并返回结果值。转化的公式为:
小时
*3600+
分钟 *60+秒
|
SEC_TO_TIME(seconds)
|
将
seconds
描述转化为包含小时、分钟和秒的时间
|
计算日期和时间的函数 :
| |
DATE_ADD(datetime, INTERVAL expr type),
ADDDATE(date,INTERVAL expr type)
|
返回与给定日期时间相差
INTERVAL
时间段的日期时间
|
DATE_SUB(date,INTERVAL expr type),
SUBDATE(date,INTERVAL expr type)
|
返回与
date
相差
INTERVAL
时间间隔的
日期
|
上述函数中
type
的取值:
| |
ADDTIME(time1,time2)
|
返回
time1
加上
time2
的时间。当
time2
为一个数字时,代表的是
秒
,可以为负数
|
SUBTIME(time1,time2)
|
返回
time1
减去
time2
后的时间。当
time2
为一个数字时,代表的是
秒
,可以为负数
|
DATEDIFF(date1,date2)
|
返回
date1 - date2
的日期间隔天数
|
TIMEDIFF(time1, time2)
|
返回
time1 - time2
的时间间隔
|
FROM_DAYS(N)
|
返回从
0000
年
1
月
1
日起,
N
天以后的日期
|
TO_DAYS(date)
|
返回日期
date
距离
0000
年
1
月
1
日的天数
|
LAST_DAY(date)
|
返回
date
所在月份的最后一天的日期
|
MAKEDATE(year,n)
|
针对给定年份与所在年份中的天数返回一个日期
|
MAKETIME(hour,minute,second)
|
将给定的小时、分钟和秒组合成时间并返回
|
PERIOD_ADD(time,n)
|
返回
time
加上
n
后的时间
|
日期的格式化与解析 :
| |
DATE_FORMAT(date,fmt)
|
按照字符串
fmt
格式化日期
date
值
|
TIME_FORMAT(time,fmt)
|
按照字符串
fmt
格式化时间
time
值
|
GET_FORMAT(date_type,format_type)
|
返回日期字符串的显示格式
|
STR_TO_DATE(str, fmt)
|
按照字符串
fmt
对
str
进行解析,解析为一个日期
|
上述
非
GET_FORMAT
函数中
fmt
参数常用的格式符:
| |
GET_FORMAT
函数中
date_type
和
format_type
参数取值如下:
| |
流程控制函数:
IF(value,value1,value2)
|
如果
value
的值为
TRUE
,返回
value1,
否则返回value2
|
IFNULL(value1, value2)
|
如果
value1
不为
NULL
,返回
value1
,否则返回value2
|
CASE
WHEN 条件 1 THEN 结果 1 WHEN 条件 2 THEN 结果 2
.... [ELSE resultn] END
|
相当于
Java
的
if...else if...else...
|
CASE expr
WHEN
常量值
1 THEN
值
1
WHEN 常量值 1 THEN
值
1 .... [ELSE
值
n] END
|
相当于
Java
的
switch...case...
|