MySQL学习
参考:尚硅谷教学
尚硅谷MySQL学习资源通过网盘分享的文件:https://pan.baidu.com/s/1NIDVOCxmllovLt8h0KwiHw 提取码: 2v9n
参考:菜鸟网站
最近正在学习有关数据库的知识,决定做一下笔记,供自己更好的学习,也供大家一起快速学习。本笔记只记录干货,只记录有用必记的知识,便于学习与复习。
目录
1. 数据库概述篇
使用数据库就是将数据存储在掉电设备中,以实现数据长久化存储和使用。
DB:数据库(Database)即存储数据的“仓库”,其本质是一个文件系统。它保存了一系列有组织的数据。
DBMS:数据库管理系统(Database Management System)
是一种操纵和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。
SQL:结构化查询语言(Structured Query Language)专门用来与数据库通信的语言。
数据库管理系统(DBMS)可以管理多个数据库,一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据,一般会在数据库创建多个表,以保存程序中实体用户的数据。
数据库管理系统、数据库和表的关系如图所示:
数据库分为关系型数据库(RDBMS)和非关系型数据库(RDBMS)两者区别与联系。
E-R(entity-relationship,实体-联系)模型中有三个主要概念是:实体集、属性、联系集。一个实体集(class)对应于数据库中的一个表(table),一个实体(instance)则对应于数据库表中的一行(row),也称为一条记录(record)。一个属性(attribute)对应于数据库表中的一列(column),也称为一个字段(field)。
ORM思想 (Object Relational Mapping)体现:
数据库中的一个表 <—> Java或Python中的一个类
表中的一条数据 <—> 类中的一个对象(或实体)
表中的一个列 <----> 类中的一个字段、属性(field)
表与表之间的联系:一对一关联、一对多关联、多对多关联、自我引用
MySQL安装与配置(8.0.26):MySQL完整安装和配置(保姆级教程)
SQLyog\Navicat安装与使用(MySQL5.5、SQLyog12.0.9、):MySQL的安装及客户端SQLyog和Navicat的安装
所有学习均基于:MySQL8.0.26(如用5.7.34会说明)、Windows10、SQLyog12.0.8.0
现在企业中MySQL8.0与5.7版本使用较多,建议大家安装这两个,上面两个链接分别对应MySQL8.0和5.5
2. SQL之SELECT使用篇
2.1 SQL基础
2.1.1 分类
- DDL(Data Definition Languages、数据定义语言),这些语句定义了不同的数据库、表、视图、索引等数据库对象,还可以用来创建、删除、修改数据库和数据表的结构。
- 主要的语句关键字包括CREATE 、DROP 、ALTER 等。
- DML(Data Manipulation Language、数据操作语言),用于添加、删除、更新和查询数据库记录,并检查数据完整性。
- 主要的语句关键字包括INSERT 、DELETE 、UPDATE 、SELECT 等。(SELECT最重要!!)
- DCL(Data Control Language、数据控制语言),用于定义数据库、表、字段、用户的访问权限和安全级别。
- 主要的语句关键字包括GRANT 、REVOKE 、COMMIT 、ROLLBACK 、SAVEPOINT 等。
2.1.2 规则
- SQL 可以写在一行或者多行。为了提高可读性,各子句分行写,必要时使用缩进。
- 关键字不能被缩写也不能分行。
- 每条命令以 ; (英文分号)结束。
- 必须保证所有的()、单引号、双引号是成对结束的。
- 字符串型和日期时间类型的数据使用单引号(’ ')表示。
- 列的别名,使用双引号(" "),而且不建议省略as。
2.1.3 规范
- MySQL 在 Windows 环境下是大小写不敏感的,在 Linux 环境下是大小写敏感的(数据库名、表名、表的别名、变量名是严格区分大小写的;关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。)建议严格按照Linux标准来,养成良好的编写习惯。
- 推荐采用统一的书写规范:数据库名、表名、表别名、字段名、字段别名等都小写(从创建到使用);SQL 关键字、函数名、绑定变量等都大写
2.1.4 注释
单行注释:#注释文字(MySQL特有的方式,对于数据库操作系统来说)
单行注释:-- 注释文字(–后面必须包含一个空格。)
多行注释:/* 注释文字 */
#这是一行单行注释
-- 这是一行单行注释
/*这是多行
注释
这些都不会被执行
*/
2.1.5 命名规则
- 数据库、表名不得超过30个字符,变量名限制为29个
- 必须只能包含 A–Z, a–z, 0–9, _共63个字符
- 数据库名、表名、字段名等对象名中间不要包含空格
- 同一个MySQL软件中,数据库不能同名;同一个库中,表不能重名;同一个表中,字段不能重名
- 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用,请在SQL语句中使
用`(着重号–>电脑键盘上面1的左边键)引起来 - 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据
类型在一个表里是整数,那在另一个表里可就别变成字符型了
#以下两句是一样的,不区分大小写
SHOW DATABASES;
SHOW DATABASES;
#创建表格
#create table student info(...); #表名错误,因为表名有空格
CREATE TABLE student_info(...);
#其中order使用``(着重号)括起来,因为order和系统关键字或系统函数名等预定义标识符重名了
CREATE TABLE `order`(id INT,NAME VARCHAR(15));
SELECT id AS "编号", `name` AS "姓名" FROM `order`; #起别名时,as都可以省略
SELECT id AS 编号, `name` AS 姓名 FROM `order`; #如果字段别名中没有空格,那么可以省略""
SELECT id AS 编 号, `name` AS 姓 名 FROM `order`; #错误,如果字段别名中有空格,那么不能省略""
!!! 以下所有学习都是先给出语法,然后是例题与对应sql实现,最后是运行结果
所有学习路线是跟随康师傅所学的,由于SQL中主要分为上面提到的DDL、DML、DCL,而三者中DML最重要,使用最多,DML中SELECT又是最重要、使用最多的,其他地方的学习也需要SELECT来查看结果,故先学习SELECT!!
2.2 基本的SELECT语句
2.2.1 SELECT…
SELECT… 就是最简单的查询,要想执行SQL语言最少得有SELECT用于执行
SELECT 5,2*3,5,9/2;
2.2.2 SELECT… FROM…
SELECT 列名
FROM 表名
如果不需要表名,只是执行简单的SELECT语句,可以用DUAL(伪表,个人理解就是一个不存在的,虚的表)
SELECT 4,5*7,7/4,8%3
FROM DUAL;
- 选择employees表中全部列
SELECT *
FROM employees;
上面语句中*表示所有的字段
一般情况下,除非需要使用表中所有的字段数据,最好不要使用通配符‘*’。使用通配符虽然可以节
省输入查询语句的时间,但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通
配符的优势是,当不知道所需要的列的名称时,可以通过它获取它们。
在生产环境下,不推荐你直接使用SELECT * 进行查询。
- 选择employees表中的last_name,first_name,salary列
SELECT last_name,first_name,salary
FROM employees;
2.2.3 列的别名
顾名思义,给列起一个别名(类似我们人的小名、外号、绰号hhh)
作用:重命名一个列,便于计算
紧跟列名,也可以在列名和别名之间加入关键字AS,别名使用双引号,以便在别名中包含空格或特
殊的字符并区分大小写。
AS 可以省略
建议别名简短,见名知意
- 选择employees表中的last_name列(别名为name),salary列(别名为工资)和12*salary列(别名为基本年工资)
SELECT last_name AS "name",salary AS "工资",12*salary "基本年工资"
FROM employees;
SELECT last_name "name",salary AS 工资,12*salary 基本年工资
FROM employees;
2.2.4 去除重复行(DISTINCT)
默认情况下,查询会返回全部行,包括重复行。在SELECT语句中使用关键字DISTINCT去除重复行,DISTINCT写在查询的列名前。
- 查询所有的部门号,每个部门号一次就够
SELECT DISTINCT department_id
FROM employees;
现在有如下语句也能执行,但是要注意:DISTINCT 其实是对后面所有列名的组合进行去重,你能看到最后的结果是 74 条,因为这 74 个部门id和 salary的组合是不同的。
SELECT DISTINCT department_id,salary
FROM employees;
豆包解释:当 DISTINCT 直接跟在 SELECT 关键字之后时,它会对 SELECT 后面指定的所有列的组合进行去重操作。在 SELECT DISTINCT department_id, salary FROM employees; 中,DISTINCT 作用于 department_id 和 salary 这两列的组合,数据库会检查查询结果中每一行的 department_id 和 salary 组合,只返回那些不重复的组合行,这种用法符合 DISTINCT 关键字的语法规则,所以该语句是正确的。
SELECT department_id,DISTINCT salary
FROM employees;
上面语句报错:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DISTINCT salary FROM employees LIMIT 0, 1000’ at line 1
豆包解释:DISTINCT 关键字的作用是消除查询结果中的重复行,它必须直接跟在 SELECT 关键字之后,用来修饰整个查询结果集,而不能单独应用于某一个列。在 SELECT department_id, DISTINCT salary FROM employees; 中,DISTINCT 错误地放在了 salary 列之前,数据库解析器无法理解这种语法,会将其识别为错误的 SQL 语句,所以该语句是不对的。
2.2.5 空值(NULL)参与运算
所有运算符或列值遇到null值,运算的结果都为null。在 MySQL 里面, 空值不等于空字符串。一个空字符串的长度是 0,而一个空值的长度是空。而且,在 MySQL 里面,空值是占用空间的。有更多NULL用法与运算后面到NULL与具体语法结合时再详细展示。
SELECT 1+NULL,NULL+NULL,'a'*NULL;
SELECT employee_id,salary,commission_pct,12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;
因为commission_pct字段有的为NULL,所以后面12 * salary * (1 + commission_pct)表达式与commission_pct有关,commission_pct不为NULL则正常计算,commission_pct为NULL,后面也为NULL。
2.2.6 着重号(`)
需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同,请在
SQL语句中使用一对``(着重号)引起来。
2.1.5中展示
2.2.7 显示表结构(DESCRIBE 或 DESC)
- 显示employees表的结构
DESCRIBE employees;
或
DESC employees;
- Field:表示字段名称。
- Type:表示字段类型,这里 barcode、goodsname 是文本型的,price 是整数类型的。
- Null:表示该列是否可以存储NULL值。
- Key:表示该列是否已编制索引。PRI表示该列是表主键的一部分;UNI表示该列是UNIQUE索引的一
部分;MUL表示在列中某个给定值允许出现多次。 - Default:表示该列是否有默认值,如果有,那么值是多少。
- Extra:表示可以获取的与给定列有关的附加信息,例如AUTO_INCREMENT等。
2.2.8 条件查询(WHERE)
使用WHERE 子句,将不满足条件的行过滤掉,WHERE子句紧随 FROM子句
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
- 查询部门号为90的员工的employee_id, last_name, job_id, department_id
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
WHERE必须紧跟在FROM后
WHERE不能使用列的别名,ORDER BY(后面要学的排序功能 )可以使用列的别名
解释:对于如下语句执行时,会按照前面的数字从小到大执,也就是先FROM确定表,再用WHERE确定条件筛选出符合条件的数据,再执行SELECT看需要哪些列,给列起什么别名,最后才是ORDER BY排序。这样在WHERE前还没有列的别名,所以不能用3 SELECT last_name,salary "工资" 1 FROM employees 2 WHERE department_id>50 4 ORDER BY "工资";
豆包给出的执行顺序:
- FROM:指定要查询的表或视图。
- WHERE:对 FROM 子句中筛选出的数据进行条件过滤。
- GROUP BY:对过滤后的数据进行分组。
- HAVING:对分组后的数据进行条件过滤。
- SELECT:选择要返回的列,并为列指定别名。
- ORDER BY:对最终结果集进行排序。
2.3 运算符
2.3.1 算术运算符
算术运算符主要用于数学运算,其可以连接运算符前后的两个数值或表达式,对数值或表达式进行加(+)、减(-)、乘(*)、除(/)和取模(%)运算。
例1
SELECT 1+1,1+10.0,2-1,10-2.0,2*3,2*3.0,4/2,4.0/3,4 DIV 3,5%3,5.0 MOD 3,8%3.0
FROM DUAL;
例2
SELECT 1+'20','20'+'20','a'+1,'a'+'b','a'+'1'
FROM DUAL;
例3
SELECT 1+NULL,2.0-NULL,NULL-3,NULL*8,NULL/9.0,9/NULL,NULL%3,5%NULL
FROM DUAL;
例4
SELECT 10*1,10*0.1,10/1,10.0/1,10/1.0,10/0
FROM DUAL;
- 一个整数类型的值对整数进行加法和减法操作,结果还是一个整数;例1
- 一个整数类型的值对浮点数进行加法和减法操作,结果是一个浮点数;例1
- 加法和减法的优先级相同,进行先加后减操作与进行先减后加操作的结果是一样的;例1
- 不管除数被除数什么类型,/都会得到浮点型,DIV是整除;例1
- 在Java中,+的左右两边如果有字符串,那么表示字符串的拼接。但是在MySQL中+只表示数
值相加。如果遇到非数值类型,先尝试转成数值,如果转失败,就按0计算。(补充:MySQL
中字符串拼接要使用字符串函数CONCAT()实现);例2 - ==加减乘除中只要有NULL参与运算都是NULL;==例3
- 一个数乘以整数1和除以整数1后仍得原数;例4
- 一个数乘以浮点数1和除以浮点数1后变成浮点数,数值与原数相等;例4
- 一个数除以整数后,不管是否能除尽,结果都为一个浮点数;例4
- 一个数除以另一个数,除不尽时,结果为一个浮点数,并保留到小数点后4位;例1
- 乘法和除法的优先级相同,进行先乘后除操作与先除后乘操作,得出的结果相同。
在数学运算中,0不能用作除数,在MySQL中,一个数除以0为NULL。例4
2.3.2 比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真则返回1,比较的结果
为假则返回0,其他情况则返回NULL。
- 等号运算符
等号运算符(=)判断等号两边的值、字符串或表达式是否相等,如果相等则返回1,不相等则返回
0。
在使用等号运算符时,遵循如下规则:
- 如果等号两边的值、字符串或表达式都为字符串,则MySQL会按照字符串进行比较,其比较的
是每个字符串中字符的ANSI编码是否相等。 - 如果等号两边的值都是整数,则MySQL会按照整数来比较两个值的大小。
- 如果等号两边的值一个是整数,另一个是字符串,则MySQL会将字符串转化为数字进行比较。
- 如果等号两边的值、字符串或表达式中有一个为NULL,则比较结果为NULL。
- 对比:SQL中赋值符号使用 :=
SELECT 1=1,1='1',1='a',97='a',1=0,'a'='a',(5+3)=(6+2),
''=NULL,0=NULL,NULL=NULL,'a'='ab','ab'='ab'
FROM DUAL;
SELECT 0='abc',1='abc',0='a',1+'a'
FROM DUAL;
豆包解释:
在大多数 SQL 数据库里,‘a’ 与 97 并不相等。
数据类型差异:在 SQL 里,‘a’ 代表的是字符类型(如 CHAR 或者 VARCHAR)的数据,它表示单个字符;而 97 属于数值类型(如 INT)的数据,代表一个整数。由于数据类型不同,在默认情况下它们不会被判定为相等。
特殊情况:不过,在某些数据库的比较操作里,会进行隐式类型转换。 比如 MySQL 在进行比较时,如果一个操作数是字符串,另一个是数字,会尝试把字符串转换为数字再进行比较。转换规则是从字符串的开头开始,将连续的数字字符转换为数字(能转多少算多少,如下面例子),如果开头不是数字字符,则转换结果为 0。 但字符 ‘a’ 无法合理地转换为数字,所以最终还是会判定为不相等。
如果你想判断字符 ‘a’ 的 ASCII 码是否为 97,可以使用数据库提供的函数来获取字符的 ASCII 码值再进行比较。在 MySQL 中可以这样操作:SELECT ASCII('a') = 97,1+'12aa' FROM DUAL;
执行上述代码,结果会显示 1,因为 ASCII(‘a’) 函数会返回字符 ‘a’ 的 ASCII 码值 97。 不同数据库中获取 ASCII 码的函数可能不同,例如在 SQL Server 中使用 ASCII() 函数,在 Oracle 中使用 ASCII() 函数功能相同。
安全等于符合(<=>),与=完全一样,除了其支持NULL运算。
为NULL而生
SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6),
'' <=> NULL,NULL <=> NULL ,1<=>NULL
FROM DUAL;
使用安全等于运算符时,两边的操作数的值都为NULL时,返回的结果为1而不是NULL;一边为NULL,一边不为NULL时为0,其他,返回结果与等于运算符相同。
不等于运算符(<>和!=)用于判断两边的数字、字符串或者表达式的值是否不相等,如果不相等则返回1,相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL,或两边都为NULL,则结果为NULL。
SELECT 1<>1,1!=1,1!=2,'a'<>'a','a'!='ab','a'!=0,'a'!=97,'a'!=NULL,10<>NULL
FROM DUAL;
其余运算符用法类似=,不在赘述
空运算符(IS NULL或者ISNULL)判断一个值是否为NULL,如果为NULL则返回1,否则返回
0。用法:IS NULL 表达式 / ISNULL(表达式) 感觉ISNULL更像一个函数,将要判断的传入后面的括号中。
SELECT NULL IS NULL,'a' IS NULL,1 IS NULL,' ' IS NULL,'' IS NULL,ISNULL(NULL),ISNULL(1),ISNULL('a')
FROM DUAL;
非空运算符(IS NOT NULL)判断一个值是否不为NULL,如果不为NULL则返回1,否则返
回0。
SELECT NULL IS NOT NULL,'a' IS NOT NULL,1 IS NOT NULL,' ' IS NOT NULL,'' IS NOT NULL
FROM DUAL;
最小值运算符 语法格式为:LEAST(值1,值2,…,值n)。其中,“值n”表示参数列表中有n个值。在有两个或多个参数的情况下,返回最小值。当比较值列表中有NULL时,不能判断大小,返回值为NULL。
最大值运算符 语法格式为:GREATEST(值1,值2,…,值n)。其中,n表示参数列表中有n个值。当有两个或多个参数时,返回值为最大值。假如任意一个自变量为NULL,则GREATEST()的返回值为NULL。
SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2),LEAST(1,0.58,2,3.55),
LEAST(0.26,1,'a'),LEAST('a',100,98,97,96)
FROM DUAL;
SELECT GREATEST(1,0,2),GREATEST(1.0,2,4,5.88),GREATEST('b','a','c'),
GREATEST(1,NULL,2),GREATEST(2.5,60,87,'a')
FROM DUAL;
感觉MySQL的LEAST()与GREATEST在同时有数字,字母进行比较时有问题
原因查询一番还未知,如有解释请赐教
SELECT LEAST('a',100,98,60,20),LEAST(100,98,60,20,'a'),GREATEST('a',100,98,60,20),GREATEST(100,98,60,20,'a')
FROM DUAL;
BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B,此时,当C大于或等于A,并且C小于或等于B时,结果为1,否则结果为0。
SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c'
FROM DUAL;
SELECT last_name,job_id,salary
FROM employees
WHERE salary BETWEEN 6000 AND 14000;
IN运算符用于判断给定的值是否是IN列表中的一个值,如果是则返回1,否则返回0。如果给定的值为NULL,或者IN列表中存在NULL(且列表中无指定值),则结果为NULL,若IN列表中有NULL,但是也有指定值,就返回1,不返回NULL。
SELECT 'a' IN ('a','b','c'), 1 IN (2,3),NULL IN (NULL,'a','bsb',15),
NULL IN ('a','b'), 'a' IN ('a', NULL),'a' IN (1,2,'b'),'a' IN ('b',NULL)
FROM DUAL;
-- 查询employees表中部门在(100,110,120)中的员工名字和工资
SELECT last_name,salary,department_id
FROM employees
WHERE department_id IN(100,110,120);
NOT IN运算符用于判断给定的值是否不是IN列表中的一个值,如果不是IN列表中的一个值,则返回1,否则返回0。
SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3)
FROM DUAL;
LIKE运算符主要用来匹配字符串,通常用于模糊匹配,如果满足条件则返回1,否则返回0。如果给定的值或者匹配条件为NULL,则返回结果为NULL。
LIKE运算符通常使用如下通配符:
“%”:匹配0个或多个字符。
“_”:只能匹配一个字符。
SELECT NULL LIKE 'abc', 'abc' LIKE NULL,'abc' LIKE '%a%','abc' LIKE 'a%'
FROM DUAL;
SELECT last_name,salary
FROM employees
WHERE last_name LIKE '%a%';-- 名字中含有a即可,因为前后都有%
SELECT last_name,salary
FROM employees
WHERE last_name LIKE '_a%';-- 名字第二个字母为a
回避特殊符号的:使用转义符。比如现在就想查询第一个字符为啊,第二个字符为_的名字,则为a\_。ESCAPE可以将后面的字符转成转移字符的作用。
SELECT last_name,salary
FROM employees
WHERE last_name LIKE '&_a%' ESCAPE '&';
REGEXP运算符用来匹配字符串,语法格式为:expr REGEXP 匹配条件 。如果expr满足匹配条件,返回1,否则返回0。
正则表达式在较多语言或者地方都有使用这里只是简单介绍
(1)‘^’匹配以该字符后面的字符开头的字符串。
(2)‘$’匹配以该字符前面的字符结尾的字符串。
(3)‘.’匹配任何一个单字符。
(4)“[…]”匹配在方括号内的任何字符。例如,“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围,使用一
个‘-’。“[a-z]”匹配任何字母,而“[0-9]”匹配任何数字。
(5)‘’匹配零个或多个在它前面的字符。例如,“x”匹配任何数量的‘x’字符,“[0-9]”匹配任何数量的数字,而“”匹配任何数量的任何字符。
详细学习:正则表达式全解析+常用示例
SELECT 'Aurora' REGEXP '^a', 'Aurora' REGEXP 'a$', 'Aurora' REGEXP 'ur',
'atguigu' REGEXP 'gu.gu','atguigu' REGEXP 'gu..gu', 'atguigu' REGEXP '[ab]'
FROM DUAL;
2.3.3 逻辑运算符
逻辑运算符主要用来判断表达式的真假,在MySQL中,逻辑运算符的返回结果为1、0或者NULL。
- 逻辑非(NOT或!)运算符表示当给定的值为0时返回1;当给定的值为非0值时返回0;当给定的值为NULL时,返回NULL。
- 逻辑与(AND或&&)运算符是当给定的所有值均为非0值,并且都不为NULL时,返回1;当给定的一个值或者多个值为0时则返回0;否则返回NULL。
- 逻辑或(OR或||)运算符是当给定的值都不为NULL,并且任何一个值为非0值时,则返回1,否则返回0;当一个值为NULL,并且另一个值为非0值时,返回1,否则返回NULL;当两个值都为NULL时,返回NULL。
- 逻辑异或(XOR)运算符是当给定的值中任意一个值为NULL时,则返回NULL;如果两个非NULL的值都是0或者都不等于0时,则返回0;如果一个值为0,另一个值不为0时,则返回1。
SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;
SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL,NULL AND 0;
SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;
SELECT 1 XOR -1, 0 XOR 1, 0 XOR NULL, 1 XOR NULL,NULL XOR 0;
OR可以和AND一起使用,但是在使用时要注意两者的优先级,由于AND的优先级高于OR,因此先对AND两边的操作数进行操作,再与OR中的操作数结合。如果自己写就建议加好小括号,看别人代码按直觉差不多,不明白借助资料
SELECT 1 AND 0 OR 1 AND 1;
SELECT (1 AND 0) OR (1 AND 1);
2.3.4 位运算符
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数,然后进行位运算,最后将计算结果从二进制变回十进制数。使用不多,一般源码或者算法中多,常用于优化,很多语言中都有
SELECT 1 & 10, 20 & 30,1 | 10,20 | 30,1 ^ 10,20 ^ 30,~1,~5;
SELECT 1<<2,4<<1,2>>1,4>>1;
2.2.5 运算符的优先级
数字编号越大,优先级越高,优先级高的运算符先进行计算。
!!! 如果自己写就建议加好小括号,看别人代码按直觉差不多,不明白借助资料
2.4 排序与分页
2.4.1 数据排序(ORDER BY)
使用 ORDER BY 子句排序。
ASC(ascend): 升序(不写排序方式,MySQL默认为升序)
DESC(descend):降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary;
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC;
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary DESC;
也可以进行多列排序,即按多个列进行排序
形式:ORDER BY 列1 (排序方式),ORDER BY 列2 (排序方式),…
可以使用不在SELECT列表中的列排序。
在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序。如果第一列数据中所有值都是唯一的,将不再对第二列进行排序。.
再次强调,ORDER BY可以用列的别名,WHERE不行
- 查询employees表中员工所有信息,按工资降序,入职时间升序
SELECT *
FROM employees
ORDER BY salary DESC,hire_date;
2.4.2 分页(LIMIT)
分页原理:所谓分页显示,就是将数据库中的结果集,按需要的条件一段一段显示出来。
MySQL中使用 LIMIT 实现分页
格式:LIMIT [位置偏移量,] 行数。不写位置偏移量时默认从0开始。
(位置偏移量即相对于第一条数据的偏移,第一条数据偏移量为0,第n条数据偏移量为n-1)
- 选择employees表中前10条数据
SELECT *
FROM employees
LIMIT 0,10;
SELECT *
FROM employees
LIMIT 10;
- 显示第11条到第20条数据
SELECT *
FROM employees
LIMIT 10,10;
分页显式公式:(当前页数-1)*每页条数,每页条数
SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;
1.LIMIT 子句必须放在整个SELECT语句的最后!
2.使用 LIMIT 的好处:
约束返回结果的数量可以减少数据表的网络传输量,也可以1 条,就可以使用提升查询效率。如果我们知道返回结果只有LIMIT 1,告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是SELECT 不需要扫描完整的表,只需要检索到一条符合条件的记录即可返回。
3.MySQL8.0新特性:
可以使用“LIMIT 3 OFFSET 4”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT 4,3;”返回的结果相同。
4.在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字,而且需要放到 SELECT 语句的最后面。
如果是 SQL Server 和 Access,需要使用 TOP 关键字,比如:SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC
如果是 DB2,使用FETCH FIRST 5 ROWS ONLY 这样的关键字,比如:
SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
如果是 Oracle,需要基于 ROWNUM 来统计行数,比如:
SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC
说明:这条语句是先取出来前 5 条数据行,然后再按照 hp_max 从高到低的顺序进行排序。但这样产生的结果和上述方法的并不一样。Oracle默认有一个rownum列。
SELECT *
FROM employees
LIMIT 4,3;
SELECT *
FROM employees
LIMIT 3 OFFSET 4;
2.5多表查询
为什么需要多表查询?很多字段一起放一张表不就可以吗?为什么将其分开设计成多张表的形式?
多表有利于:
1.获取完整信息:在实际的业务场景中,数据通常分散存储在多个表中。
2.分析关联数据:多表查询能够帮助分析不同表之间数据的关联关系。以社交媒体平台为例,用户表记录了用户的基本信息,关系表记录了用户之间的关注、好友关系等。通过多表查询,可以分析出某个用户的好友列表、共同兴趣爱好等,挖掘出用户之间的潜在关系,为推荐系统、社交网络分析等提供支持。
3.多表有利于更好的开发,实现事物、锁定等问题 。满足复杂业务场景:现代应用系统的业务逻辑往往非常复杂,涉及到多个实体和它们之间的各种关系。
5.提供多样化查询视角:多表查询可以从不同的角度和维度对数据进行查询和分析。例如在一个图书管理系统中,可以通过多表查询实现按作者查询其所有著作的借阅情况,按出版社查询出版图书的库存情况等多种查询需求,为用户提供了多样化的查询视角,方便用户根据不同的业务需求获取所需的数据。
一张表弊端:
1.设计一张表形式会有过多的冗余数据
2.查询效率降低(内存一样的情况下,字段多了可查询存储的条数就少了,查相同条数的数据就可能需要更多的查询次数)
笛卡尔积:是一个数学运算。假设两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能
组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素
个数的乘积数。
集合A(1,2,3) 集合B(4,5,6) 则集合A与集合B的笛卡尔积为((1,4),(1,5),(1,6),(2,4),(2,5),(2,6),(3,4),(4,4),(4,6))
SQL92中,笛卡尔积也称为交叉连接,英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交
叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关。
使用以下方式进行多表查询是错误的,因为查到的是笛卡尔积(除非数据本就是笛卡尔积的形式,即所有表中的所有行互相连接):
#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
笛卡尔积的错误会在下面条件下产生:
- 省略多个表的连接条件(或关联条件)
- 连接条件(或关联条件)无效
- 所有表中的所有行互相连接
为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件。
加入连接条件后,查询语法:
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件
- 查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
查询得到每个员工及其部门信息,但是只有106行信息,因为有一个员工King没有部门,该字段为NULL,进行条件判断时为NULL,就是假,查不到。
多表查询分为三类,以下详细介绍。
2.5.1 等值连接 vs 非等值连接
等值查询:WHERE中的连接条件是等式形式
非等值查询:WHERE中的连接条件是非等式形式
#等值连接
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
#非等值连接
#查询员工的工资等级
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
- 在表中有相同列时,在列名之前加上表名前缀。 如上面的department_id在两张表中都有,必须指定来自哪个表。==阿里开发规范:只要涉及多个表,都需要在列名前加表的别名(或
表名)进行限定。==建议大家遵守,养成良好的开发习惯!- 若有多个连接条件使用:与 (&&)AND 操作符
- 可以给表的起别名:使用别名可以简化查询,列名前使用表名前缀可以提高查询效率。==!!!但是一但给表起别名后,其他地方若用到表名,则必须使用别名。==否则会报错,因为sql语句先执行FROM语句,这时表的原名已被覆盖,再执行WHERE、SELECT等语句,现在只有别名了。
- 可以连接多个表,2及2以上。 连接 n个表,至少需要n-1个连接条件。比如,连接三个表,至少需要两个连接条件。 作为自己检查代码的依据。
#多个连接条件 + 连接多个表
#查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;
#给表起别名
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id
2.5.2 自连接 vs 非自连接
自连接:多表查询时连接的另一个表也是本表
非自连接:多表查询时连接的另一个表不是本表,而是其他表(以上案例都是)。即 当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。 然后两个表再进行内连接,外连接等查询。
#非自连接
#查询员工的姓名与部门名称
SELECT e.last_name,e.department_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;
#自连接
#查询员工的姓名、id及其管理者的姓名、id
SELECT emp.last_name,emp.employee_id,mgr.last_name,mgr.employee_id
FROM employees emp,employees mgr
WHERE emp.`manager_id`=mgr.`employee_id`;
- 自连接时给同一张表起不同的名字,此时若想用列,必须指明表名,因为本就是同一张表,里面肯定有这个字段
2.5.3 内连接 vs 外连接
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。即只匹配连接字段所有表都有的的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行 ,这种连接称为左(或右) 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
如果是左外连接,则连接条件中左边的表也称为主表,右边的表称为从表。
如果是右外连接,则连接条件中右边的表也称为主表,左边的表称为从表。
SQL92:使用(+)创建连接,在 SQL92 中采用(+)代表从表所在的位置。即左或右外连接中,(+) 表示哪个是从表。+号在那边即往那边补数据,另一边的为主表。
Oracle 对 SQL92 支持较好,而 MySQL 则不支持 SQL92 的外连接。而且在 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;
2.5.4 SQL99语法实现多表查询
如上一小节最后中的介绍那样,SQL92的多表查询MySQL不支持,而且没有全外连接。开发中多用SQL99实现多表查询,下面进行介绍。
SQL99中使用JOIN…ON子句创建连接的语法结构,每一个JOIN后都跟一个ON连接条件,这个条件是与其他条件分开的,关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的,都表示内连接。
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
内连接的实现(INNER JOIN、JOIN、CROSS JOIN)
语法:
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
- 查询员工的姓名及其部门名称
SELECT e.last_name,d.department_name
FROM employees e INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
SELECT e.last_name,d.department_name
FROM employees e CROSS JOIN departments d
ON e.`department_id`=d.`department_id`;
SELECT e.last_name,d.department_name
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`;
一般内连接省略INNER或者CROSS,直接用JOIN
外连接的实现(LEFT OUTER JOIN、RIGTH OUTER JION)
- 左外连接
语法:
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
- 查询所有员工的姓名及其部门名称(107条记录都要有,King没有部门也要查出来)
SELECT e.last_name,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;
SELECT e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`;
- 右外连接
语法:
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
- 查询所有部门的名称及其部门内员工的姓名
SELECT e.last_name,d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;
SELECT e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`;
左右外连接一般省略OUTER,直接用LEFT JOIN 和RIGHT JOIN
因为有LEFT和RIGHT就是外连接,外连接必须要有LEFT和RIGHT,则只有一个JOIN一定是内连接。
- 满外连接
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT JOIN代替。
设计集合的一些的运算,因为可以将各种的查询结果看作是结合的一些交并补运算。
2.5.5 7种集合查询实现
设两个数据集为集合A、B,红色部分为查询部分(查询到的数据是两表连接后的)
1图:A与B的交集->内连接、A∩B
2图:A中的全部数据->左外连接、A
3图:B中的全部数据->右外连接、B
4图:A中数据减去交集->A-A∩B
5图:B中数据减去交集->B-A∩B
6图:A与B中的全部数据(只含一次)->满外连接、2图+5图或者3图+4图、A∪B
7图:A中独有的数据加B中独有的数据、A与B中只含一次的全部数据减去交集->A∪B-A∩B、4图+5图
要想实现以上交操作,需要合并查到的两部分数据,则需要使用UNION或者UNION ALL关键字。
UNION 操作符返回两个查询的结果集的并集,去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
注意:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效率。
#4图
#实现A - A∩B
#因为是查的数据新增的是A中独有的,现在查询出所有的数据后,A中独有的数据对应的
#B中字段是没有的,即为NULL,所以条件为从表关联字段为NULL,比如有的人没有部门,
#现在左连接查出来了,他的部门id就是NULL
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null
and 等其他子句;
#5图
#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null
and 等其他子句;
#6图
#实现查询结果是A∪B
#用左外的A,union 右外的B
#2图+3图,因为有重复部分,所以用UNION
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;
#两部分没有重复数据,所以用UNION ALL
#2图+5图
select 字段列表
from A表 left join B表
on 关联条件
UNION ALL
#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null
and 等其他子句;
#两部分没有重复数据,所以用UNION ALL
#4图+3图
select 字段列表
from A表 right join B表
on 关联条件
UNION ALL
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null
and 等其他子句;
#7图
#实现A∪B - A∩B 或 (A - A∩B) ∪ (B - A∩B)
#使用左外的 (A - A∩B) union 右外的(B - A∩B)
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null
and 等其他子句
union all
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null
and 等其他子句
如果查到的数据没有重复的,尽量用UNION ALL,效率更高
- 假设A表为employees表,B表为departments表,实现以上7种情形
#内连接
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;
SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`;
#左外
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`;
#右外
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`;
#左偏
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
#where d.`department_id`<=>NULL;
WHERE d.department_id IS NULL;
#右偏
SELECT last_name,department_name
FROM employees RIGHT JOIN departments
ON employees.department_id=departments.department_id
WHERE employees.`department_id` IS NULL;
#满外连接
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL;
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
UNION ALL
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_id` IS NULL;
#去中心
SELECT 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 last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`department_id` IS NULL;
2.2.6 SQL99语法新特性
自然连接: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;
USING连接:SQL99还支持使用 USING 指定数据表里的合JOIN一起使用。与自然连接 NATURAL JOIN 不同的是,USING 指定了具体的相同的字段名称,你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化JOIN ON 的等值连接。
USING后可以加多个字段,表示需要多个连接字段相等,如USING (A,B),此时两个表中都得含有A,B两个字段。
- 以下代码等价:
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
常用的SQL标准:SQL92 和 SQL99,还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011 和 SQL:2016 等其他的标准。
要控制连接表的数量。多表连接就相当于嵌套 for 循环一样,非常消耗资源,会让 SQL 查询性能下降得很严重,因此不要连接不必要的表。在许多 DBMS 中,也都会有最大连接表的限制。
2.6 单行函数
DBMS 之间的差异性很大,远大于同一个语言不同版本之间的差异。
大部分 DBMS 会有自己特定的函数,这就意味着采用 SQL 函数的代码可移植性是很差的, 因此在使用函数的时候需要特别注意。
MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里将这些丰富的内置函数再分为两类:单行函数、聚合函数(或分组函数)。
单行函数特点:操作数据对象、接受参数返回一个结果、只对一行进行变换、每行返回一个结果、可以嵌套、参数可以是一列或一个值
通俗来说,输入是一行数据,操作后的结果也是一行
单行函数功能一般都十分清晰,以下主要介绍常用的单行函数,并进行测试
2.6.1 数值函数
- 基本函数
SELECT ABS(10),ABS(-5),ABS(5.23),ABS(-5.31),SIGN(10.05),SIGN(-8),SIGN(0),PI()
FROM DUAL;
SELECT CEIL(5),CEIL(5.001),CEIL(5.999),CEIL(-5),CEIL(-5.001),
CEIL(-5.999),CEILING(5.001),FLOOR(5),FLOOR(5.001),FLOOR(5.999),
FLOOR(-5),FLOOR(-5.001),FLOOR(-5.999)
FROM DUAL;
SELECT LEAST(10,3.33,-19,33),GREATEST(10,3.33,-19,33),MOD(12,5),12%5,12 MOD 5
FROM DUAL;
SELECT RAND(),RAND(10),RAND(10)
FROM DUAL;
rand(seed),提供一个随机种子来生成随机数,固定随机种子后,不论执行多少次都是一样的,因为本质上还是拿这个随机种子来经过一些列算法生成随机值
SELECT ROUND(12.5),ROUND(12.55),ROUND(12.99),
ROUND(12.49),ROUND(-12.5),ROUND(-12.889),ROUND(12.5,1),
ROUND(12.894,2),ROUND(-12.894,2),ROUND(12.894,-1)
FROM DUAL;
round(x,y):对x保留y位小数进行四舍五入,也可以位负数,就是从整数位也按照四舍五入形式,比如实例中ronud(12.894,-1),-1表示个位数的地方,2小于5,则为10.
TRUNCATE(x,y)函数必须得有两个参数,表示对x保留y位小数后进行截断,截断的含义就是不管后一位数为多少都直接将后面的数舍去,也可以传入负数参数,同 ROUND(x,y),但保留准则是截断。
单行函数可以嵌套使用,即将一个函数的结果作为另一个函数的参数传进去,如实例TRUNCATE(ROUND(12.5999,2),1),先对12.5999按四舍五入的准则保留两位小数为12.60,然后截断保留1位小数为12.6
SELECT TRUNCATE(12.5,0),TRUNCATE(12.99999,2),TRUNCATE(-12.889,2),
TRUNCATE(12.568,-2),TRUNCATE(12.568,-1),SQRT(16),
SQRT(2),TRUNCATE(ROUND(12.5999,2),1)
FROM DUAL;
- 三角有关的函数
一个圆的一圈为360度,也是2π
RADIANS(X):传入的参数为角度
DEGREES(X):传入的参数为弧度
SELECT RADIANS(90),RADIANS(360),DEGREES(PI()),DEGREES(1)
FROM DUAL;
SELECT SIN(PI()/6),SIN(PI()/2),ASIN(0.5),COS(PI()/3),ACOS(COS(PI()/3)),
TAN(PI()/4),ATAN(TAN(PI()/4))
FROM DUAL;
ATAN2(M,N)函数返回两个参数的反正切值。 与ATAN(X)函数相比,ATAN2(M,N)需要两个参数,例如有两个点point(x1,y1)和point(x2,y2),使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1)),使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出,当x2-x1等于0时,ATAN(X)函数会报错,而ATAN2(M,N)函数则仍然可以计算。
SELECT ATAN2(1,1),DEGREES(ATAN2(1,1)),ATAN2(1,2),DEGREES(ATAN2(1,2))
FROM DUAL;
- 指数、对数函数
SELECT POW(2,3),POWER(2,3),EXP(1),LN(EXP(1)),LOG(EXP(2)),LOG10(100),LOG2(4)
FROM DUAL;
- 进制转换函数
这些函数都是以10进制为基础
SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,2),CONV(10,2,10)
FROM DUAL;
2.6.2 字符串函数
MySQL中,字符串的位置是从1开始的。
SELECT ASCII('A'),ASCII('a'),ASCII(1),CHAR_LENGTH("你好"),
CHAR_LENGTH("abc"),LENGTH("你好"),LENGTH("abc")
FROM DUAL;
返回字符数和字节数还是有区别的,比如MySQL中utf8用三个字节表示一个汉字,故一个汉字字符数为1,字节数为3
SELECT CONCAT("hello","world"),CONCAT(" hello ","--world--"),
CONCAT_WS("+","hello","world"),CONCAT_WS("+"," hello ","--world--")
FROM DUAL;
#替换函数
SELECT INSERT("helloworld",2,3,"zzzzz"),REPLACE("helloworld","o","zzz")
FROM DUAL;
SELECT UPPER("hElloWorlD"),UCASE("hElloWorlD"),LOWER("hElloWorlD"),LCASE("hElloWorlD")
FROM DUAL;
SELECT LEFT("helloworld",3),RIGHT("helloworld",6),LPAD(" helloworld ",20,"+"),RPAD(" helloworld ",20,"+")
FROM DUAL;
SELECT LTRIM(" helloworld "),LTRIM("+ helloworld +"),LTRIM(" +helloworld+ "),
RTRIM(" helloworld "),RTRIM("+ helloworld +"),RTRIM(" +helloworld+ ")
FROM DUAL;
SELECT TRIM(" helloworld "),TRIM("+ helloworld +"),TRIM(" +helloworld+ "),
TRIM("+" FROM "++helloworld++"),TRIM(LEADING "+" FROM "++helloworld++"),TRIM(TRAILING "+" FROM "++helloworld++")
FROM DUAL;
SELECT REPEAT("helo",3),CONCAT("hello",SPACE(5),"world"),LENGTH(SPACE(4)),STRCMP("a","a"),STRCMP("aa","aa"),STRCMP("ab","aa")
FROM DUAL;
SELECT SUBSTR("helloworld",3,5),SUBSTRING("helloworld",3,5),LOCATE("llo","helloworld"),
LOCATE("lol","helloworld"),ELT(2,"hello","hi","ok"),
FIELD("hi","hello","hi","ok","hi")
FROM DUAL;
SELECT FIND_IN_SET("hi","ho,hk,hi,hl,hi,hu"),REVERSE("helloworld"),
NULLIF("aa","aa"),NULLIF("aa","ab")
FROM DUAL;
2.6.3 时间和日期函数
- 获取时间
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(),NOW(),
SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP(),
UTC_DATE(),UTC_TIME()
FROM DUAL;
记住几个自己常用即可,CURDATE(),CURTIME(),NOW()
- 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),
UNIX_TIMESTAMP(CURDATE()),UNIX_TIMESTAMP(CURTIME()),
UNIX_TIMESTAMP('2011-11-11 11:11:11'),
FROM_UNIXTIME(1740574652)
FROM DUAL;
- 获取月份、星期、星期数、天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME('2025-2-26'),DAYNAME('2025-2-26'),WEEKDAY('2025-2-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;
- 时间和秒钟转换的函数
SELECT TIME_TO_SEC(NOW()),SEC_TO_TIME(75988)
FROM DUAL;
- 计算日期和时间的函数
记住加法的用法即可,减法可以写成(-时间间隔),即加上一个负数
#4种测试分别为加1秒、加1分钟1秒、加(-1)年即减一年、加1年1月
SELECT
ADDDATE('2025-2-26 23:32:12',INTERVAL 1 SECOND) AS col,
DATE_ADD('2025-2-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS co2,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS co3, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS co4 #需要单引号
FROM DUAL;
感觉DATEDIFF(d1,2)使用较多,计算相隔天数时,MAKEDATE()构造时间
SELECT
ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2025-01-01'),
TIMEDIFF(NOW(),'2025-1-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
10)
FROM DUAL;
- 日期的格式化与解析
上述非GET_FORMAT 函数中fmt参数常用的格式符:
GET_FORMAT函数中date_type和format_type参数取值如下:
这个函数的功能就是获取想要的date在某个地区的表示形式,usa美国,eur欧洲等等
SELECT DATE_FORMAT(NOW(), '%H:%i:%s'),
STR_TO_DATE('09/01/2009','%m/%d/%Y'),
STR_TO_DATE('20140422154706','%Y%m%d%H%i%s'),
STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;
SELECT GET_FORMAT(DATE, 'USA'),
DATE_FORMAT(NOW(),
GET_FORMAT(DATE,'USA')),
STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d')
FROM DUAL;
2.6.4 流程处理函数
流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
SELECT IF(1,10,20),IF(TRUE,10,20),IF(1=0,10,20),IF(FALSE,10,20),IF(NULL,10,20)
FROM DUAL;
#就检测第一个参数是不是NULL,只要不是NULL都会返回第一个参数,ture映射为1,false为1,字符串正常返回
SELECT IFNULL(10,20),IFNULL(TRUE,10),IFNULL("helloworld",10),
IFNULL(FALSE,10),IFNULL(0,10),IFNULL(NULL,20),IFNULL(NULL,TRUE)
FROM DUAL;
SELECT CASE WHEN 1>0 THEN "1>0"
WHEN 2>0 THEN "2>0"
ELSE "3>0" END
FROM DUAL;
#按工资给员工评级,>20000为A,10000-20000为B,其余为C
SELECT employee_id,last_name,salary,CASE WHEN salary>20000 THEN "A"
WHEN salary>10000 THEN "B"
ELSE "C" END "工资评级"
FROM employees;
CASE WHEN 表达式1 THEN 结果1 WHEN 表达式2 THEN 结果2 ELSE 结果3 END类似于其他编程语言中的if(){} else if(){} else if(){} else{}
SELECT CASE 2 WHEN 1 THEN "写的1"
WHEN 2 THEN "写的2"
ELSE "写的其他" END
FROM DUAL;
#查询员工表中若部门为50,标记为“好部门”,部门为60标记为”一般部门”,其他为“继续努力部门”。
SELECT employee_id,department_id,CASE department_id WHEN 50 THEN "好部门"
WHEN 60 THEN "一般部门"
ELSE "继续努力部门" END
"部门评价"
FROM employees;
2.6.5 加密与解密函数
对于信息加密与解密有时候是非常必要的,加密与解密了解:
百度百科:加密与解密学习
PASSWORD()函数在8.0版本中不可用,5.7中还可以,以下是在5.7中演示
SELECT PASSWORD('mysql'), PASSWORD(NULL)
FROM DUAL;
MD5()函数与SHA()函数在8.0与5.7中均可用
SELECT MD5("mysql"),SHA("mysql")
FROM DUAL;
ENCODE()、DECODE()函数在8.0中不可用,5.7中可用
SELECT ENCODE('mysql', 123),DECODE(ENCODE('mysql', 123),123),DECODE("mysql",123)
FROM DUAL;
一定注意加密解密函数本质也是一种算法,对于相同的输入每次执行输出都会给出一样的内容。DECODE()解密时,要解密的内容一定是已经加密后的密文。
2.6.6 MySQL信息函数
SELECT DATABASE(),VERSION(), USER(), CURRENT_USER(), SYSTEM_USER(),
SESSION_USER(),CHARSET('ABC'), COLLATION('ABC')
FROM DUAL;
SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2), CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2),INET_ATON('192.168.1.100'),INET_NTOA(3232235876) , BENCHMARK(1, MD5('mysql')), CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'))
FROM DUAL;
2.7 聚合函数
2.7.1 常用聚合函数
聚合(或聚集、分组)函数是对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个值。
常用的聚合函数:AVG()求平均值、SUM()求总和、MAX()求最大值、MIN()求最小值、COUNT()统计数据总条数
语法:
SELECT [COLUNM],GROUP FUNCTION(COLUNM)
FROM TABLE
[WHERE CONDITION]
[GROUP BY COLUMN]
[ORDER BY COLUNM]
[LIMIT start,num];
- AVG和SUM函数
可以对数值型数据使用AVG 和 SUM 函数。
SELECT AVG(salary),SUM(salary)
FROM employees;
- MIN和MAX函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
SELECT MAX(salary),MIN(salary),MAX(hire_date),MIN(last_name)
FROM employees;
- COUNT函数
COUNT(*)返回表中记录总数,适用于任意数据类型。
COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(*),COUNT(employee_id),COUNT(commission_pct)
FROM employees;
问题:从上面实例可以看出,用count(),count(1),count(列名)都可以统计数据条数,用谁好呢?
其实,对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*),count(1)直接读行数,复杂度是O(n),因为innodb真的要去数一遍。但好于具体的count(列名)。
不要使用 count(列名)来替代 count() ,count() 是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。
说明:count(==)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。==
>AVG(),SUM()也都只会统计字段不为NULL的条数,为NULL则不统计在内
SELECT AVG(commission_pct),SUM(commission_pct),COUNT(*),
COUNT(commission_pct),SUM(commission_pct)/107,
SUM(commission_pct)/COUNT(commission_pct)
FROM employees;
2.7.2 GROUP BY分组
可以使用GROUP BY子句将表中的数据分成若干组,跟在WHERE后,WHERE一定放在FROM后面
GROUP BY 可以理解为先对数据按类别分组为一块一块的,然后对一块一块的数据做操作
1.在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中,因为这个字段需要是分组的依据,比如要查询每个部门的平均工资,SELECT后应该是department_id和avg(salary),因为要用department_id分组,其他列如last_namr就不能再写在SELECT中了,这样部门分组后前面有个名字是不合理的。
2.包含在 GROUP BY 子句中的列不必包含在SELECT 列表中,用某个列分组后可以不查询显示这个列,比如上面要查询每个部门的平均工资,GROUP BY用department_id分组,SELECT后可以只有avg(salary)。
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;
SELECT last_name,AVG(salary)
FROM employees
GROUP BY department_id;
也可以使用多个列分组,即先按某列分成一组一组后再按某列进行细分,只需要在GROUP BY后添加列名即可,用逗号隔开。
- 按部门和工种统计平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
还可使用WITH ROLLUP 关键字在GROUP BY之后,作用是在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量。当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的。(可以理解为分组后已经排序又统计总条数没意义,但是MySQL8.0是可以用的)
SELECT department_id,COUNT(*)
FROM employees
GROUP BY department_id WITH ROLLUP;
2.7.3 HAVING 条件筛选
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;
非法使用聚合函数 : 不能在 WHERE 子句中使用聚合函数。这与接下来要讲的 SQL语言执行顺序有关,WHERE紧跟在FROM后执行,此时是一条一条的数据,分组没有用
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
- WHERE和HAVING的对比
1.WHERE 可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;HAVING 必须要与 GROUP BY 配合使用,可以把分组计算的函数和分组字段作为筛选条件。在需要对数据进行分组统计的时候,HAVING 可以完成 WHERE 不能完成的任务。这是因为,在查询语法结构中,WHERE 在 GROUP BY 之前,所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后,可以使用分组字段和分组中的计算函数,对分组的结果集进行筛选,这个功能是 WHERE 无法完成的。另外,WHERE排除的记录不再包括在分组中。
2.如果需要通过连接从关联表中获取需要的数据,WHERE 是先筛选后分组,而 HAVING 是先分组后筛选。 这一点,就决定了在关联查询中,WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选,用一个筛选后的较小数据集进行分组,这样占用的资源比较少,执行效率也比较高。HAVING 则需要先把结果集准备好,也就是用未被筛选的数据集进行分组,然后对这个大的数据集进行筛选,这样占用的资源就比较多,执行效率也较低。- 开发建议:WHERE 和 HAVING 也不是互相排斥的,可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING,普通条件用 WHERE。这样,我们就既利用了 WHERE 条件的高效快速,又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候,运行效率会有很大的差别。
2.7.4 SQL语句执行过程
已学的查询结构:
1.SQL92
SELECT ...,...,...
FROM ...,...,
WHERE 多表连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ...ASC/DESC
LIMIT ...,...
2.SQL99
SELECT ...,...,...
FROM ... JOIN ...
ON 连接条件 JOIN ...
ON 连接条件
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
SQL语言关键字顺序:
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…
SELECT 语句的执行顺序(在 MySQL 和 Oracle 中,SELECT 执行顺序基本相同):
FROM -> ON->LEFT/RIGHT JOIN->WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
执行顺序:
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7
在 SELECT 语句执行这些步骤的时候,每个步骤都会产生一个虚拟表,然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是,这些步骤隐含在 SQL 的执行过程中,对于我们来说是不可见的。
SELECT 是先执行 FROM 这一步的。在这个阶段,如果是多张表联查,还会经历下面的几个步骤:
- 首先先通过 CROSS JOIN 求笛卡尔积,相当于得到虚拟表 vt(virtual table)1-1;
- 通过 ON 进行筛选,在虚拟表 vt1-1 的基础上进行筛选,得到虚拟表 vt1-2;
- 添加外部行。如果我们使用的是左连接、右链接或者全连接,就会涉及到外部行,也就是在虚拟表 vt1-2 的基础上增加外部行,得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表,还会重复上面的步骤,直到所有表都被处理完为止。这个过程得到是我们的原始数据。然后进入WHERE按条件筛选得到vt2。
然后进入第三步和第四步,也就是 GROUP 和 HAVING 阶段 。在这个阶段中,实际上是在虚拟表 vt2 的基础上进行分组和分组过滤,得到中间的虚拟表 vt3 和 vt4 。
当我们完成了条件筛选部分之后,就可以筛选表中提取的字段,也就是进入到SELECT 和 DISTINCT阶段。
首先在 SELECT 阶段会提取想要的字段,然后在 DISTINCT 阶段过滤掉重复的行,分别得到中间的虚拟表vt5-1 和 vt5-2 。
当我们提取了想要的字段数据之后,就可以按照指定的字段进行排序,也就是ORDER BY 阶段,得到虚拟表 vt6。
最后在 vt6 的基础上,取出指定行的记录,也就是 LIMIT 阶段 ,LIMIT 阶段 ,得到最终的结果,对应的是虚拟表vt7。
当然我们在写 SELECT 语句的时候,不一定存在所有的关键字,相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言,所以我们在写 SELECT 语句的时候,还要注意相应的关键字顺序,所谓底层运行的原理,就是我们刚才讲到的执行顺序。
末尾
未完待续…