一.数据的操作
插入数据记录
-
1.插入完整数据记录
INSERT INTO table_name(属性1,属性2,...) VALUES(value1,value2,...); # 或者 INSERT INTO table_name VALUES(value1,value2,...); # 注意使用此方法时添加的数据数量必须与字段数相同 -
2.插入数据记录一部分
INSERT INTO table_name(field1,field2) VALUES(value1,value2);该方法可指定插入数据的字段
-
插入多条数据记录
- 插入多条完整数据记录
INSERT INTO table_name(field1,field2,...) VALUES(value1,value2,...), (value1,value2,...), (value1,value2,...), ... (value1,value2,...);- 插入多条部分数据记录
INSERT INTO table_name(指定字段1,指定字段2,...) VALUES(value1,value2,...), (value1,value2,...), ... (value1,value2,...); -
插入查询结果
INSERT INTO table_name1(field11,field12,field13,...field1n) SELECT(field21,field22,field23,...field2n) FROM table_name2 WHERE CONDITION;
更新数据记录
-
更新特定数据记录
UPDATE table_name SET field1=value1, field2=value2, field3=value3, WHERE CONDITION; -
更新所有数据记录
UPDATE table_name SET field1=value1, field2=value2, ... WHERE CONDITION; # 将条件设置为大范围，例如deptno<6
删除数据记录
-
删除特定数据记录
DELETE FROM table_name WHERE CONDITION; -
删除全部数据记录
DELETE FROM table_name;
二.单表查询数据记录
简单数据记录查询
-
语法格式
SELECT field1,field2,...,fieldn FROM table_name; # 可以调换字段位置来改变字段显示位置 -
“*”符号的使用->查询所有字段数据
SELECT * FROM table_name; -
查询指定字段数据
SELECT empno,ename,sal FROM t_employee; -
避免重复数据查询——DISTINCT
SELECT DISTINCT field1,field2,fieldn FROM table_name; -
实现数学四则运算数据查询
运算符 描述 + 加法 - 减法 * 乘法 /(DIV) 除法 %(MOD) 求余 - 例子
SELECT ename,sal*12 FROM t_employee;- 可以用别名来使显示数据更加美观
SELECT 字段1 [AS] 别名1,字段2 [AS] 别名2... FROM table_name; -
设置显示格式数据查询
使用CONCAT()函数来连接字符串，从而实现设置显示数据的格式
SELECT CONCAT(ename,'雇员的年薪为：',sal*12) yearsalary FROM t_employee;
条件数据记录查询
- 分类
- 带有关系运算符和逻辑运算符的条件数据查询
- 带BETWEEN AND关键字的条件数据查询
- 带IS NULL关键字的条件数据查询
- 带IN关键字的条件数据查询
- 带LIKE关键字的条件数据查询
- 1.带有关系运算符和逻辑运算符的条件数据查询
| 比较运算符 | 描述 |
|---|---|
| > | 大于 |
| < | 小于 |
| = | 等于 |
| !=(<>) | 不等于 |
| >= | 大于等于 |
| <= | 小于等于 |
| 逻辑运算符 | 描述 |
|---|---|
| AND（&&） | 逻辑与 |
| OR（||） | 逻辑或 |
| XOR | 逻辑异或 |
| NOT（！） | 逻辑非 |
-
-
1.单条件数据查询
SELECT field FROM table_name WHERE CONDITION; -
2.多条件数据查询
SELECT field FROM table_name WHERE CONDITION1 AND|&& CONDITION2;
-
-
2.带BETWEEN AND关键字的条件数据查询
# 符合范围的数据记录查询 # 字段数据需要满足处于value1~value2之间的条件 SELECT field1,field2,...fieldn FROM table_name WHERE field BETWEEN value1 AND value2; # 不符合范围的数据记录查询 # 字段数据需要满足不处于value1~value2之间的条件 SELECT field1,field2,...fieldn FROM table_name WHERE field NOT BETWEEN value1 AND value2; -
3.带IS NULL关键字的条件数据查询
# 判断字段数值是否为空，为空则能被查询到 SELECT field1,field2,...fieldn FROM table_name WHERE field IS NULL; # 判断字段数值是否为空，不为空则能被查询到 # 形式1 SELECT field1,field2,...fieldn FROM table_name WHERE field IS NOT NULL; # 形式2 SELECT field1,field2,...fieldn FROM table_name WHERE NOT field IS NULL; -
4.带IN关键字的条件数据查询
# 判断字段的数值是否在指定集合中，如果在其中，则能被查询到 SELECT field1,field2,... FROM table_name WHERE field IN (value1,value2,value3,...,valuen); # 判断字段的数值是否在指定集合中，如果不在其中，则能被查询到 SELECT field1,field2,... FROM table_name WHERE field NOT IN (value1,value2,value3,...,valuen); -
5.带LIKE关键字的条件数据查询
-
(1)带有"%"通配符的查询
# 查询ename中以字母A开头的数据记录 SELECT ename FROM t_employee WHERE ename LIKE 'A%'; # 查询ename中不是以A开头的数据记录 SELECT ename FROM t_employee WHERE NOT|! ename LIKE 'A%'; # 查询ename中包含字母M的数据记录 SELECT ename FROM t_employee WHERE ename LIKE '%M%'; # 查询ename中以字母S为结尾的数据记录 SELECT ename FROM t_employee WHERE ename LIKE 'S%'; -
(2)带有“_”通配符的查询
# 查询t_employee所有名字中第二个字母为字符A的人 SELECT ename FROM t_employee WHERE ename LIKE '_A%'; # # 查询t_employee所有名字中第二个字母不是字符A的人 SELECT ename FROM t_employee WHERE ename NOT LIKE '_A%'; -
(3)带LIKE关键字的模糊查询注意点
- 对于LIKE关键字，如果匹配“%%”则表示查询所有数据记录
-
排序数据记录查询
-
语法格式
SELECT field1,field2,...,fieldn FROM table_name WHERE CONDITION ORDER BY fieldm1 [ASC|DESC][,fieldm2[ASC|DESC]]- ASC表示升序排序
- DESC表示降序排序
- 可以按照单字段排序，也可以按照多字段排序
限制数据记录查询数量
通过关键字LIMIT来限制查询结果的数量
-
语法格式
SELECT field1,field2,...fieldn FROM table_name WHERE CONDITION LIMIT OFFSET_START,ROW_COUNT;- OFFSET_START：表示数据记录的起始偏移量
- ROW_COUNT：表示显示的行数
- 分类
- 不指定初始位置方式
- 指定初始位置方式
-
1.不指定初始位置
LIMIT row_count;如果ROW_COUNT的值小于查询结果的总数量，将会从第一条数据记录开始，显示ROW_COUNT条数据记录；如果ROW_COUNT的值大于查询结果的总数量，则会显示所有查询结果
-
2.指定初始位置
# 从第一条记录开始，显示五条记录 SELECT * FROM table_name WHERE CONDITION ORDER BY field LEMIT 0,5; # 从第五条记录开始，显示五条记录 SELECT * FROM table_name WHERE CONDITION ORDER BY field LEMIT 5,5;
统计函数和分组数据记录查询
统计函数
- COUNT()函数：该统计函数实现统计表中记录的条数
- AVG()函数：该统计函数实现计算字段值的平均值
- SUM()函数：该统计函数实现计算字段值的总和
- MAX()函数：实现查询字段值的最大值
- MIN()函数：实现查询字段值的最小值
MySQL支持的统计函数
- 1.统计数据记录条数
- COUNT(*)使用方式：该种方式可以实现对表中记录进行统计，不管表字段中包含的是NULL值还是非NULL值
- COUNT(field)使用方式：该种方式可以实现对指定字段的记录进行统计，在具体统计时将忽略NULL值，不忽略数值为0的数据
# 统计雇员表中的人数
SELECT COUNT(*) number
FROM t_employee;
# 统计雇员表中领奖金的人数
# 该种方法不忽略数值为0的人
SELECT COUNT(comm) number
FROM t_employee;
# 忽略数值为0的人
SELECT COUNT(comm) number
FROM t_employee
WHERE NOT comm=0;
-
2.统计计算平均值
- AVG(field)：实现对指定字段的平均值进行计算，在计算时忽略NULL值
# 该种方式没有忽略值为0的数据记录 SELECT AVG(comm) average FROM t_employee; # 该种方式忽略值为0的数据记录 SELECT AVG(comm) average FROM t_employee WHERE NOT comm=0; -
3.统计计算求和
- SUM(field)：实现计算指定字段值之和，统计时将忽略NULL值
# 获取雇员领取工资的总和 SELECT SUM(sal) sumvalue FROM t_employee; # 获取雇员领取奖金的总和 SELECT SUM(comm) sumvalue FROM t_employee WHERE NOT comm=0; # 该行可以省略，不影响计算结果 -
4.统计计算最大值和最小值
- MAX(field)：实现计算字段值最大值，统计时忽略NULL值
- MIN(field)：实现计算字段值最小值，统计时忽略NULL值
SELECT MAX(sal) maxval,MIN(sal) minval FROM t_employee WHERE NOT sal=0; -
统计函数注意点
- 如果操作的表中没有数据记录，则COUNT()函数会返回数据0，而其他函数会返回NULL
分组数据查询
通过SQL语句GROUP BY来实现，进行分组查询时，分组所依据的字段上的值一定要有重复值，否则将没有任何实际意义
-
注意：在 SQL 查询中，如果使用了
GROUP BY子句，那么SELECT中的列必须满足两个条件：- 列要么是聚合函数的参数（如
SUM()、COUNT()等），或者是聚合函数本身。 - 列在
GROUP BY子句中列出。
- 列要么是聚合函数的参数（如
-
简单分组查询
SELECT function() FROM table_name WHERE CONDITION GROUP BY field; -
实现多个字段分组查询
SELECT GROUP_CONCAT(field),function(field) FROM table_name WHERE CONDITION GROUP BY field1,field2,...fieldn;-
首先按照field分组，然后按照字段field2分组，以此类推
select deptno FROM t_employee GROUP BY deptno; select deptno,t_employee.Hiredate from t_employee group by deptno, t_employee.Hiredate ORDER BY deptno ASC, t_employee.Hiredate DESC; SELECT deptno,hiredate,GROUP_CONCAT(ename) enames,COUNT(ename) FROM t_employee GROUP BY deptno,hiredate;
-
-
实现HAVING子句限定分组查询
SELECT function(field) FROM table_name WHERE CONDITION GROUP BY field1,field2,...,fieldn HAVING CONDITION;关键字HAVING用于指定分组后的条件
# 例子 select deptno,AVG(sal) average,GROUP_CONCAT(ename) enames,COUNT(ename) number from t_employee GROUP BY deptno HAVING AVG(sal)>2000;
三.多表数据记录查询
本章的讲解需要用到两张表：雇员表(t_employee)、部门表(t_dept)，表格创建语句如下
create table t_dept(
deptno int(11),
dname varchar(20),
loc varchar(40)
);
create table t_employee(
empno int(11),
ename varchar(20),
job varchar(40),
MGR int(11),
Hiredate date,
sal double(10,2),
comm double(10,2),
deptno int(11)
);
insert into t_employee (empno, ename, job, MGR, Hiredate, sal, comm, deptno)
values (7369,'SMITH','CLERK',7902,'1981-03-12',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1982-03-12',1600.00,300.00,30),
(7521,'MARD','SALESMAN',7698,'1983-03-12',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1981-03-12',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-03-12',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1981-03-12',1850.00,null,30),
(7782,'CLARK','MANAGER',7839,'1985-03-12',2450.00,null,10),
(7788,'SCOTT','ANALYST',7566,'1981-03-12',3000.00,null,20),
(7839,'KING','PRESIDENT',null,'1981-03-12',5000.00,null,10),
(7844,'TURNER','SALESMAN',7698,'1989-03-12',1500.00,0.00,30),
(7876,'ADAMS','CLERK',7788,'1998-03-12',1100.00,null,20),
(7900,'JAMES','CLERK',7698,'1997-03-12',950.00,null,30),
(7902,'FORD','ANALYST',7566,null,3000.00,null,20),
(7934,'MILLER','CLERK',7782,'1981-03-12',1300.00,null,10);
insert into t_dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
关系数据操作
-
并(UNION)
指把具有相同字段数目和字段类型的表合并到一起
-
笛卡尔积(CARTESIAN PRODUCT)
指没有连接条件表关系返回的结果
-
内连接(INNER JOIN)
-
连接：指在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件进行选择生成一个新的关系。连接分为内连接(INNER JOIN)、外连接(OUTER JOIN)、交叉连接(CROSS JOIN)
-
内连接：指在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按照匹配条件分为自然连接、等值连接、不等连接
-
1.自然连接(NATURAL JOIN)
- 定义：自然连接操作就是表关系的笛卡儿积中，首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段
-
2.等值连接
- 定义：表关系的笛卡尔积中，选择所匹配字段值相等(=符号)的数据记录，新关系中不会去掉重复的字段
-
3.不等连接
- 定义：表关系的笛卡尔积中，选择所匹配字段值不相等(！=)的数据记录，在新关系中不会去掉重复的字段
-
-
外连接(OUTER JOIN)
- 定义：在表关系的笛卡儿积数据记录中，不仅保留关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录
- 分类：按照不匹配条件数据记录分为三类，左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)、全外连接(FULL OUTER JOIN)
- 1.左外连接：除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录
- 2.右外连接：除了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录
- 3.全外连接：除了选择相匹配的数据记录，还包含关联左右两个表中不皮皮额的数据记录
内连接查询
内连接方式有两种，一种采用ANSI连接语法格式(显示)，另一种采用(select…from…where)形式(隐式)
-
通过SQL语句“INNER JOIN…ON"来实现内连接数据查询的语法格式
SELECT field1,field2,...,fieldn FROM join_tablename1 INNER JOIN join_tablename2 【INNER JOIN join_tablenamen】 ON join_condition;- join_condition：表示进行匹配的条件
- 根据匹配情况，可以将内连接查询分为两类
- 等值连接
- 不等连接
-
1.自连接
-
定义：一种特殊的等值连接，指表与其本身进行连接
-
实例
# 形式1 select e.ename employeename,e.job,l.ename loadername from t_employee e INNER JOIN t_employee l on e.MGR=l.empno; # 形式2 select e.ename employeename,e.job,l.ename loadername from t_employee e,t_employee l where e.mgr=l.empno;
-
-
2.等值连接
-
定义：在关键字ON后的匹配条件中通过等于关系运算符(=)来实现等值条件
-
实例
# 方式1 select e.empno,e.ename,e.job,d.dname,d.loc from t_employee e INNER JOIN t_dept d ON e.deptno=d.deptno; # 方式2 select e.empno,e.ename,e.job,d.dname,d.loc from t_employee e,t_dept d where e.deptno=d.deptno; select e.empno,e.ename employeename,e.sal,e.job,l.ename loadername from t_employee e INNER JOIN t_employee l ON e.mgr=l.empno; select e.empno,e.ename employeename,e.sal,e.job,l.ename loadername,d.dname,d.loc from t_employee e INNER JOIN t_employee l ON e.mgr=l.empno INNER JOIN t_dept d on l.deptno=d.deptno; select e.empno,e.ename employeename,e.sal,e.job,l.ename loadername,d.dname,d.loc from t_employee e,t_employee l,t_dept d where e.mgr=l.empno and l.deptno=d.deptno;
-
-
3.不等连接
-
定义：在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件
-
实例
#方式1 select e.ename employeename,e.job,l.ename loadername from t_employee e inner join t_employee l on e.mgr=l.empno and e.empno>l.empno; #方式2 select e.ename employeename,e.job,l.ename loadername from t_employee e,t_employee l where e.mgr=l.empno and e.empno>l.empno;
-
外连接查询
-
语法形式
SELECT field1,field2,...,fieldn FROM join_tablename1 LEFT|RIGHT|FULL [OUTER] JOIN join_tablename2 ON join_condition; -
分类：按照连接关键字，外连接查询可以分为三类，分别是左外连接、右外连接、全外连接
-
1.左外连接
-
定义：以关键字LEFT JOIN左边的表为参考表
-
实例
select e.ename employeename,e.job,l.ename loadername from t_employee e LEFT JOIN t_employee l on e.mgr=l.empno;
-
-
2.右外连接
-
定义：以关键字RIGHT JOIN右边的表为参考表
-
实例
select e.empno,e.ename employeename,e.job,d.dname,d.loc from t_employee e RIGHT JOIN t_dept d on e.deptno=d.deptno;
-
合并查询数据记录
本章讲解将用到两张表，计算机系学生表(t_cstudent)和音乐系学生表(t_msudent),创建语句如下
-
语法格式
SELECT field1,field2,...fieldn FROM table_name1 UNION|UNION ALL SELECT field1,field2,...fieldn FROM table_name2 ... -
1.带有关键字UNION的合并操作
会去掉重复的数据记录
SELECT * FROM t_cstudent UNION SELECT * FROM t_mstudent; -
2.带有关键字UNION ALL的合并操作
不会去掉重复的数据记录
SELECT * FROM t_cstudent UNION SELECT * FROM t_mstudent;
子查询
-
子查询理论上来说可以出现在语句的任何位置，但在实际开发中，子查询经常出现在WHERE和FROM中
-
WHERE中的子查询：该位置的子查询一般返回单行单列、多行单列、单行多列数据记录
-
FROM子句中的子查询：该位置的子查询一般返回多行多列数据记录，可以当作一张临时表
-
-
本章讲解使用t_dept表和t_employee表
返回结果为单行单列和单行多列的子查询
-
1.单行单列
SELECT sal FROM t_employee WHERE sal>(SELECT sal FROM t_employee WHERE name='SMITH'); -
2.单行多列
SELECT sal,job FROM t_employee WHERE (sal,job)=(SELECT sal,job FROM t_employee WHERE ename=;SMITH)
返回结果为多行单列的子查询
-
1.带有关键字IN的子查询
SELECT * FROM t_employee WHERE deptno IN(SELECT deptno FROM t_dept); SELECT * FROM t_employee WHERE deptno NOT IN( SELECT deptno FROM t_dept ); -
2.带有关键字ANY的子查询
关键字ANY用来表示主查询的条件为满足子查询返回查询结果中任意一条数据记录，该关键字有三种匹配方式，分别是：
-
=ANY：其功能于IN一样 -
>ANY(>=ANY)：比子查询中返回的数据记录中最小的还要大于(大于等于)的数据记录 -
<ANY(<=ANY)：比子查询中返回的数据记录中最大的还要小于(小于等于)的数据记录 -
实例
SELECT ename,sal FROM t_employee WHERE sal>ANY( SELECT sal FROM t_employee WHERE job='MANAGER' ); SELECT ename,sal FROM t_employee WHERE sal>=ANY( SELECT sal FROM t_employee WHERE job='MANAGER' );
-
-
3.带有关键字ALL的子查询
关键字ALL用来表示主查询的条件为满足子查询返回查询结果中所有数据记录，该关键字有两种匹配方式，分别为：
-
>ALL(>=ALL)：比子查询中返回数据记录最大的还要大于(大于等于)的数据记录 -
<ALL(<=ALL)：比子查询中返回数据记录最大的还要小于(小于等于)的数据记录 -
实例
SELECT ename,sal FROM t_employee WHERE sal>ALL( SELECT sal FROM t_employee WHERE job='MANAGER' ); SELECT ename,sal FROM t_employee WHERE sal>=ALL( SELECT sal FROM t_employee WHERE job='MANAGER' );
-
返回结果为多行多列的子查询
一般子查询语句会出现在FROM中
# 实例
SELECT ddeptno,d.dname,d.loc,COUNT(e.empno) number,AVG(e.sal) average
FROM t_employee e INNER JOIN t_dept d
ON e.deptno=d.deptno
GROUP BY d,deptno DESC,d.dname,d.loc;
SELECT d.deptno,d.dname,d.loc,number,average
FROM t_dept d INNER JOIN(
SELECT deptno dno,COUNT(empno) number,AVG(sal) average
FROM t_employee
GOURP BY deptno DESC
) employee
ON d.deptno=employee.dno;
四.使用MySQL运算符
使用算术运算符
MySQL软件所支持的算术运算符
| 运算符 | 描述 | 表达式形式 |
|---|---|---|
| + | 加法 | x1+x2+x3+…+xn |
| - | 减法 | x1-x2-x3-…-n |
| * | 乘法 | x1x2…*xn |
| /(DIV) | 除法 | x1/x2/x3/…/xn or x1 DIV x2 DIV x3 DIV … VID xn |
| %(MOD) | 求余 | x1%x2%x3%…%xn or x1 MOD x2 MOD x3 MOD … MOD xn |
-
使用演示
SELECT 6+4 加法操作, 6-4 减法操作, 6*4 乘法操作, 6/4 除法操作, 6 DIV 4 除法操作, 6%4 求模操作, 6 MOD 4 求模操作;
使用比较运算符
表达式结果为真则返回1，为假则返回0
MySQL软件支持的比较运算符
| 运算符 | 描述 | 表达式形式 |
|---|---|---|
| > | 大于 | x1>x2 |
| < | 小于 | x1<x2 |
| = 或 <=> | 等于 | x1=x2 或 x1<=>x2 |
| !=(<>) | 不等于 | x1!=x2 或 x1<>x2 |
| >= | 大于等于 | x1>=x2 |
| <= | 小于等于 | x1<=x2 |
| BETWEEN AND | 存在于指定范围 | x1 BETWEEN m AND n |
| IS NULL | 为空 | x1 IS NULL |
| IN | 存在于指定集合 | x1 IN(value1,value2,value3,…,valuen) |
| LIKE | 通配符匹配 | x1 LIKE expression |
| REGEXP | 正则表达式匹配 | x1 REGEXP regularexpression |
-
常见比较运算符的使用
SELECT 1=1 数值比较, 'cjgong'='cjgong' 字符串比较, 1+2=3+3 表达式比较, 1<=>1 数值比较, 'cjgong'<=>'cjgong' 字符串比较, 1+2<=>3+3 表达式比较;SELECT 1<>1,1!=1, 'cjgong'<>'cjgong','cjgong'!='cjgong', 1+2<>3+3,1+2!=3+3; -
REGEXP(正则表达式)比较运算符的使用
- 正则表达式：指通过模式去匹配一类字符串
- 结果相符则返回1，不符则返回0
MySQL支持的模式字符
模式字符 含义 ^ 匹配字符串的开始部分 $ 匹配字符串的结束部分 . 匹配字符串中的任意一个字符 [字符集合] 匹配字符集合中的任意一个字符 [^字符集合] 匹配字符集合外的任意一个字符 str1|str2|str3 匹配str1、str2、str3中的任意一个字符串 * 匹配字符，包含0个和1个 + 匹配字符，包含一个 字符串{N} 字符串出现N次 字符串(M,N) 字符串至少出现M次，最多出现N次 -
使用示例
SELECT 'cjgong' REGEXP '^c' 特定字符开头, 'cjgongcjgong' REGEXP '^cjgong' 特定字符串开头; SELECT 'cjgong' REGEXP 'g$' 特定字符结尾, 'cjgongcjgong' REGEXP 'cjgong$' 特定字符串结尾; SELECT 'cjgong' REGEXP '^c....g$' 匹配任意四个字符; SELECT 'cjgong' REGEXP '[abc]' 指定字符中字符, 'cjgong' REGEXP '[a-zA-Z]' 指定字符中的集合区间, 'cjgong' REGEXP '[^abc]' 指定字符外字符, 'cjgong' REGEXP '[^a-zA-Z0-9]' 指定字符外区间; SELECT 'cjgong' REGEXP 'a*g' 指定是否包含零个或多个指定字符, 'cjgong' REGEXP 'a+g' 指定是否包含至少一个指定字符; SELECT 'cjgong' REGEXP 'cjg' 指定单个字符串, 'cjgong' REGEXP 'cjc|cjgc' 指定多个字符串; SELECT 'cccjgong' REGEXP 'c{3}' 匹配3个c, 'cccjgongg' REGEXP 'g{2}' 匹配2个g, 'cgong' REGEXP 'cj{1,2}' 至少1个最多2个;
使用逻辑运算符
MySQL软件支持的逻辑运算符
| 运算符 | 描述 | 表达式形式 |
|---|---|---|
| AND(&&) | 与 | x1 AND x2 或 x1 && x2 |
| OR(||) | 或 | x1 OR x2 或 x1 || x2 |
| NOT(!) | 非 | NOT x1 或 !x1 |
| XOR | 异或 | x1 XOR x2 |
-
使用示例
SELECT 3 AND 4, 0 AND 4; 0 AND NULL, 3 AND NULL, 3 && 4, 0 && NULL; # 所有操作数部位0且不为NULL时，结果返回1 # 存在任何一个操作数为0时，结果返回0 # 存在任意一个操作数为NULL且操作数没有0时，结果返回NULLSELECT 3 OR 4, 0 OR 4, 0 OR NULL, 3 OR NULL, 3 || 4, 0 || 4, 0 || 0, 0 || NULL, 3 || NULL; # 所有操作数存在任何一个操作数不为0时，结果返回1 # 所有操作数中不包含非0的数字，但是包含NULL，结果返回NULL # 所有操作数都为0时，结果返回0SELECT NOT 3, NOT 0, NOT NULL, !3, !0, !NULL; # 如果操作数为非0数字，结果返回0 # 如果操作数为0，结果返回1 # 如果操作数为NULL，结果返回NULLSELECT 3 XOR 4, 0 XOR 0, NULL XOR NULL, 0 XOR 4, 0 XOR NULL, 3 XOR NULL; # 如果操作数中包含NULL，结果返回NULL # 如果操作数为同为0数字或者同为非零数字，结果返回0 # 如果操作数一个为0，一个为非零数字，结果返回1
使用位运算符
MySQL软件支持的逻辑运算符
| 运算符 | 描述 | 表达式形式 |
|---|---|---|
| & | 按位与 | x1 & x2 |
| | | 按位或 | x1 | x2 |
| ~ | 按位取反 | ~ x1 |
| ^ | 按位异或 | x1 ^ x2 |
| << | 按位左移 | x1 << x2 |
| >> | 按位右移 | x1 >> x2 |
-
使用示例
# 按位与 # 先把操作数由十进制转换为二进制数，然后按位进行与操作，即1与1为1，其他的为0，最后将与后的结果转换为十进制数，当存在多个操作数时，按照从左到右的顺序进行计算 SELECT 5&6,BIN(5&6) 二进制数, # 4,100 4&5&6,BIN(4&5&6) 二进制数; # 4,100 # 按位或 # 先把操作数由十进制转换为二进制数，然后按位进行或操作，即1与任何数或运算的结果为1，0与0运算的结果为0，最后将与后的结果转换为十进制数，当存在多个操作数时，按照从左到右的顺序进行计算 SELECT 5|6,BIN(5|6) 二进制数, # 7,111 4|5|6.BIN(4|5|6) 二进制数; # 7,111 # 按位取反 # 先把操作数由十进制转换为二进制数，然后进行按位取反操作，即1取反的运算结果为0，0取反的运算结果为1，最后将与后的结果转换为十进制 SELECT ~4,BIN(4) 二进制数; # 按位左移，按位右移 # 先把操作数由十进制转换为二进制数，如果向左移则右边补0，如果向右移动则左边补0，最后将移动后的结果转换为十进制数
五.使用MySQL常用函数
本章介绍分为四部分：
- 字符串函数
- 数值函数
- 日期函数
- 系统信息函数
使用字符串函数
字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(str1,str2,…,strn) | 连接n个字符串成为一个完整的字符串 |
| INSERT(str1,y,instr) | 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr |
| LOWER(str) | 将字符串str中所有字母变为小写 |
| UPPER(str) | 将字符串str中所有字母变为大写 |
| LEFT(str,x) | 返回字符串str中最左边的x个字符 |
| RIGHT(str,x) | 返回字符串str中最右边的x个字符 |
| LPAD(str,n,pad) | 使用字符串pad对字符串str最左边进行填充，直到长度为n个字符长度 |
| RPAD(str,n,pad) | 使用字符串pad对字符串str最右边进行填充，直到长度为n个字符长度 |
| LTRIM(str) | 去掉字符串str左边的空格 |
| RTRIM(str) | 去掉字符串str右边的空格 |
| REPEAT(str,x) | 返回字符串str重复n次的结果 |
| REPLACE(str,a,b) | 使用字符串b替换字符串str中所有出现的字符串a |
| STRCMP(str1,str2) | 比较字符串str1和str2 |
| TRIM(str) | 去掉字符串str左右两边的空格 |
| SUBSTRING(str,x,y) | 返回字符串从x位置开始起y个字符长度的字符串 |
-
1.合并字符串函数CONCAT()和CONCAT_WS()
- CONCAT()
- 定义：
CONCAT(s1,s2,...,sn); - 作用：函数会将传入的参数连接起来返回合并的字符串类型数据，参数的数据类型不做要求，如果其中一个参数为NULL，则返回值为NULL
- 定义：
- CONCAT_WS()
- 定义：
CONCAT_WS(SEP,s1,s2,...,sn); - 作用：比起CONCAT()多了一个表示分隔符的SEP参数，即不仅将传入的参数连接起来，而且还会通过分隔符将各个字符串分隔开。分隔符可以是一个字符串，也可以是其他参数，如果分隔符为NULL，则返回结果为NULL。函数会忽略任何分隔符参数后的NULL值
- 定义：
- CONCAT()
-
2.比较字符串大小函数STRCMP()
- 定义：
STRCMP(s1,s2); - 作用：比较字符串s1和字符串s2，如果s1大于s2，则返回1,如果s2大于1，则返回-1,如果两个字符串相等，则返回0
- 定义：
-
3.获取字符串长度函数LENGTH()和字符数函数CHAR_LENGTH()
- LENGTH()
- 定义：
LENGTH(str) - 作用：获取传入参数的长度(字节数)
- 定义：
- CHAR_LENGTH(str)
- 定义：
CHAR_LENGTH(str) - 作用：获取传入参数的字符数
- 定义：
- LENGTH()
-
4.字母大小写转换函数UPPER()和LOWER()
- UPPER()或UCASE()：将传入参数中的小写字母转为大写字母
- LOWER()或LCASE()：将传入参数中的大写字母转为小写字母
-
5.查找字符串
-
1.返回字符串位置的函数FIND_IN_SET()
- 定义：
FIND_IN_SET(str1,str2) - 作用：返回在str2中与str1相匹配的字符串的位置，参数str2字符串中将包含若干个用逗号隔开的字符串
- 定义：
-
2.返回指定字符串位置的函数FIELD()
- 定义：
FILED(str,str1,str2,...,strn); - 作用：返回第一个与字符串str相匹配的字符串的位置
- 定义：
-
3.返回子字符串相匹配的开始位置
-
有三种函数可以实现该功能，分别是：LOCATE()、POSITON()、INSTR()
-
定义:
LOCATE(str1,str); POSITON(str1 IN str); INSTR(str,str1);
-
-
4.返回指定位置的字符串的函数ELT()
- 定义：
ELT(n,str1,str2,...); - 作用：返回第n个字符串
- 定义：
-
5.选择字符串的函数MAKE_SET()
- 定义：
MAKE_SET(num,str1,str2,...,strn); - 作用：先将num转为二进制数字，然后按照二进制数字从参数str1,str2,…,strn中选取相应字符串。在通过二进制数来选择字符串时，会按从右到左的顺序读取改指，如果值为1，选择该字符串，如果不为1，不选择该字符串
- 定义：
-
6.从现有字符串中截取子字符串
-
定义：
# 从左边或右边截取字符串 LEFT(str,num); RIGHT(str,num); # 截取指定位置和长度的字符串 SUBSTRING(str,num,len); MID(str,num,len);
-
-
7.去除字符串的首尾空格
- 去除字符串开始处空格：
LTRIM(str); - 取出字符串结尾处空格：
RTRIM(str); - 去除字符串首尾空格：
TRIM(str);
- 去除字符串开始处空格：
-
8.替换字符串
-
(1)使用INSERT()函数：
INSERT(str,pos,len,newstr);作用：将字符串str中pos位置开始长度为len的字符串用字符串newstr来替换，任何一个参数为NULL则返回值为NULL
-
(2)使用REPLACE()函数：
REPLACE(str,substr,newstr);作用：将字符串str中的子字符串substr用字符串newstr来替换
-
-
-
使用示例
# 合并字符串 SELECT CONCAT('My','S','QL') 合并后字符串; # 返回MySQL SELECT CONCAT('My','S',NULL); # 返回NULL SELECT CONCAT_WS('-','029','88461234'); # 029-88461234 SELECT CONCAT_WS('NULL','029','88461234'); # NULL # 比较字符串大小函数 SELECT STRCMP('abc','abd'); # -1 SELECT STRCMP('abc','abb'); # 1 SELECT STRCMP('abc','abc'); # 0 # 获取字符串长度函数 LENGTH('MySQL'); # 5 LENGTH('张三'); # 6 CHAR_LENGTH('MySQL'); # 5 CHAR_LENGTH('张三'); # 3 # 字母大小写转换函数 SELECT UPPER('mysql'),UCASE('mysql'), # MYSQL LOWER('MySQL'),LCASE('MySQL'); # mysql # 查找字符串 SELECT FIND_IN_SET('MySQL','orcle,SQL server,MySQL'); # 3 SELECT FIELD('MySQL','orcle','SQL server','MySQL'); # 3 # 返回子字符串的匹配开始位置 SELECT LOCATE('SQL','MySQL'), # 3 POSITION('SQL' IN 'MySQL'), # 3 INSTR('MySQL','SQL'); # 3 # 选择字符串的函数 SELECT BIN(5),MAKE_SET(5,'MySQL','Oracle','SQL server','PostgreSQL'); # MySQL,SQL server # 截取字符串的函数 SELECT 'MySQL',LEFT('MySQL',2); # My SELECT 'MySQL',RIGHT('MySQL',2); # QL SELECT SUBSTRING('oraclemysql',7,5); # mysql SELECT MID('oraclemysql',7,5); # mysql
使用数值函数
常用数值函数
| 函数 | 功能 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| CEIL(x) | 返回大于x的最大整数 |
| FLOOR(x) | 返回小于x的最大整数 |
| MOD(x,y) | 返回x模y的值 |
| RAND() | 返回0-1中的随机数 |
| ROUND(x,y) | 返回数值x的四舍五入后有y位小数的数值 |
| TRUNCATE(x,y) | 返回数值x截断y位小数的数值 |
-
随机数函数
SELECT RAND(),RAND(), RAND(3),RAND() # 如果在RAND()括号内指定数字，返回结果数值将不变 -
获取整数的函数
SELECT CEIL(4.3),CEIL(-2.5), # 5.-2 CEILING(4.3),CEILING(-2.5); #5,-2 SELECT FLOOR(4.3),FLOOR(-2.5); # 4，-3 -
截断数值函数
SELECT TRUNCATE(903.53567,2),TRUNCATE(903.53367,-1); # 903.53,900 -
四舍五入函数
SELECT ROUND(903.53567),ROUND(-903.53567); # 904,-904
使用日期和时间函数
常用日期和时间函数
| 函数 | 功能 |
|---|---|
| CURDATE() | CURRENT_DATE() | 获取当前日期 |
| CURTIME() | CURRENT_TIME() | 获取当前时间 |
| NOW() | TIMESTAMP() | LOCALTIME() | SYSTEMDATE() | 获取当前的日期和时间 |
| UNIX_TIMESTAMP() | 获取日期的UNIX时间戳 |
| FROM_UNIXTIME() | 获取UNIX时间戳的时间值 |
| WEEK(date) | 返回日期为一年中的第几周 |
| YEAR(date) | 返回日期的年份 |
| HOUR(time) | 返回时间的小时 |
| MINUTE(time) | 返回时间的分钟 |
| MONTHNAME(date) | 返回时间的月份 |
| 函数 | 功能 |
|---|---|
| FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())) | 将UNIX格式的时间戳转为普通格式 |
| UTC_DATE() | 显示UTC格式日期 |
| UTC_TIME() | 显示UTC格式时间 |
| QUARTER(time) | 显示当前季度 |
| DAYOFMONTH(time) | 显示当前为该月的第几天 |
| SECOND(time) | 显示当前秒数 |
| MONTHNAME(time) | 显示英文月数 |
| WEEKOFYEAR(time) | 年中第几个星期 |
| DAYNAME(time) | 英文星期名 |
| DAYOFWEEK(time) | 星期数值 |
| WEEKDAY(time) | 星期数值 |
| DAYOFYEAR(time) | 当前年的第几天 |
| DAYOFMONTH(time) | 当前月的第几天 |
- 获取指定值的函数EXTRACT()
- 定义：
EXTRACT(type FROM date) - 功能：函数会从时间和日期参数date中获取指定类型参数type的值。type参数的取值可以是year、month、day、hour、minute、second
- 定义：
- 计算日期和时间的函数
- 1.默认日期和时间操作
- TO_DAYS(date)：该函数计算日期参数date与默认日期和时间(0000年1月1日)之间的间隔天数
- FROM_DAyS(number)：该函数计算从默认日期和时间开始经历number天后的日期和时间
- DATEDIFF(date1,date2)：该函数计算两日期之间相隔天
- 2.指定日期和时间操作
- ADDDATE(date,n)：该函数计算日期参数date加上n天后的日期
- SUBDATE(date,n)：该函数计算日期参数date减去n天后的日期
- ADDTIME(time,n)：该函数计算时间参数time加上n秒后的时间
- SUBTIME(time,n)：该函数计算时间参数time减去n秒后的时间
- 1.默认日期和时间操作
使用系统信息函数
常用系统信息函数
| 函数 | 功能 |
|---|---|
| VERSION() | 返回数据库版本号 |
| DATABASE() | 返回当前数据库名 |
| USER() | 返回当前用户 |
| LAST_INSERT_ID() | 返回最近生成的AUTO_INCREMENT()值 |
六.存储过程和函数的操作
创建存储过程和函数
-
语法形式
CREATE PROCEDURE procedure_name([procedure_paramter[....]]) [characteristic...] routine_body;- procedure_paramter：存储过程的参数
- characteristic：存储过程的特性
- routine_body：存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束
-
procedure_paramter中每个参数的语法形式：
[IN|OUT|INOUT] parameter_name type- IN输入类型、OUT输出类型、INOUT输入输出类型
- type表示参数类型，可以是任意一个数据类型
-
characteristic参数的取值:
LANGUAGE SQL |[NOT] DETERMINISTIC |{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA} |SQL SECURITY {DEFINER|INVOKER} |COMMENT 'string'-
LANGUAGE SQL：表示存储过程的routine_body部分由SQL语言的语句组成，为MySQL软件默认的语句
-
[NOT] DETERMINISTIC：表示存储过程的执行过程是否确定，如果是确定的，则输入相同的参数将得到相同的输出；如果不确定，输入相同的参数可能得到不同的输出结果，默认确定
-
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}：表示使用SQL语句的限制
-
CONTAINS SQL(默认值)：可以包含SQL语句，但是不包含读或写数据的语句
-
NO SQL：不包含SQL语句
-
READS SQL DATA：包含读数据的语句
-
MODIFIEDS SQL DATA：包含读数据的语句
-
-
-
创建函数的语法形式
CREATE FUNCTION function_name([function_paramter[,...]]) [characteristic...] routine_body return value; -
创建简单的存储过程和函数
DELIMITER $$ CREATE PROCEDURE proce_employee_sal() COMMENT '查询所有雇员的工资' BEGIN SELECT sal FROM t_employee; END $$ DELIMITER ;
关于存储过程和函数的表达式
-
1.操作变量
-
声明变量
DECLARE var_name[,...] type [DEFAULT value]- DEFAULT value：用于设置变量的默认值，默认为NULL
- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内
-
赋值变量
SET var_name=expr[,...] # 将查询结果赋值给变量 SELECT field_name[,...] INTO var_name[,...] FROM table_name WHERE condition;- expr：关于变量的赋值表达式
- field_name：表示查询的字段名
- var_name：表示变量名
-
-
2.操作条件
为了提高语言的安全性，提供了异常处理机制，即为条件
-
定义条件
DECLARE condition_name CONDITION FOR condition_value condition_value; SQLSTATE[VALUE] sqlstate_value |mysql_error_code- condition_name：定义的条件名称
- condition_value：设置条件的类型
- sqlstate_value、mysql_error_code：设置条件的错误
-
定义处理程序
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement handler_type: CONTINUE |EXIT |UNDO condition_value: SQLSTATE[VALUE] sqlstate_value |condition_name |SQLWARNING |NOT FOUND |SQLEXCEPTION |mysql_error_code
-
-
3.使用光标
MySQL软件提供了光标(游标)来实现遍历记录结果的功能
-
声明光标
DECLARE cursor_name CURSOR FOR select_statement- select_statement：查询语句
-
打开光标
OPEN cursor_name -
使用光标
FETCH cursor_name INTO var_name[,var_name]...- 将cursor_name中SELECT语句执行结果保存到变量参数var_name中，变量参数要在光标使用前定义
-
关闭光标
CLOSE cursor_name -
使用示例
DECLARE cursor_emplayee CURSOR FOR SELECT sal FROM t_employee; OPEN cursor_emplayee; FETCH cursor_emplayee INTO emplayee_sal; CLOSE cursor_emplayee; -
注意：光标必须在处理程序之前、在变量和条件之后声明，并且最后一定要关闭光标
-
-
4.使用流程控制
流程控制语句主要用于实现控制语句的执行条件，例如顺序、条件和循环，MySQL软件中通过关键字IF和CASE来实现条件控制，关键字LOOP、WHILE和REPEAT实现循环
-
条件控制语句：IF
IF search_condition THEN statement_list [ELSELF search_condition THEN statement_list]... [ELSE search_condition] END IF- search_condition：条件的判断
- statement_list：表示不同条件的执行语句
-
条件控制语句：CASE
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] END CASE- case_value：条件判断的变量
- when_value：条件判断变量的值
- statement_list：表示不同条件的执行语句
-
循环控制语句：LOOP
[begin_label:] LOOP statement_list END LOOP [end_label]- begin_label、end_label：开始和结束的标志，这两个标志必须相同，且可以省略
- 如果想要退出正在执行的循环体，可以通过关键字LEAVE来实现，其语法形式如下：
LEAVE label
-
循环控制语句：WHILE
满足条件才执行语句
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]- search_condition：循环执行条件
-
循环控制语句：REPEAT
满足条件就跳出循环体
[begin_label:] REPEAT search_condition DO statement_list END REPEAT [end_label]
-
查看储存过程和函数
-
1.通过SHOW PROCEDURE STATUS语句查看储存过程状态信息
SHOW PROCEDURE STATUS [LIKE 'pattern'];- pattern：要查询的存储过程名
-
2.通过SHOW FUNCTION STATUS语句查看函数状态信息
SHOW FUNCTION STATUS [LIKE 'pattern']; -
3.通过系统表information_schema.routines实现查看存储过程和函数的信息
USE information_schema; SELECT * FROM routines WHERE SPECIFIC_NAME='procedure_name'; -
4.通过SHOW CREATE PROCEDURE语句查看储存过程定义信息
SHOW CREATE PROCEDURE proce_name; -
5.通过SHOW CREATE FUNCTION语句查看函数定义信息
SHOW CREATE FUNCTION func_name;
修改存储过程和函数
-
修改存储过程
ALTER PROCEDURE proce_name [characteristic...]-
此时characteristic参数取值只能为下值：
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|{SQL SECURITY{DEFINER|INVOKER}|COMMENT 'string' -
注意：所修改存储过程必须在数据库中已经存在
-
-
修改函数
ALTER FUNCTION function_name [characteristic...]- 注意：所修改函数必须在数据库中已经存在
- 对characteristic参数的要求和存储过程的修改相同
删除存储过程和函数
-
1.通过DROP语句删除存储过程
DROP PROCEDURE proce_name;USE information_schema; SELECT * FROM routines WHERE PROCEDURE_NAME='proce_name'; # 通过系统表检查存储过程对象是否存在 -
通过DROP FUNCTION语句删除函数
DROP FUNCTION func_name;
6814
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
