Mysql数据库学习（三）：表的crud操作、完整性约束、select各种查询

野性酷女 2022-08-25 05:20 67阅读 0赞

**一、表的crud操作**

指增加(Create)、查询(Retrieve)（重新得到数据）、更新(Update)和删除(Delete)

// select 查询后面再讲

SQL Code

**二、完整性约束**

表完整性约束

> 主键(constraint)
> 
> 外键(constraint)
> 
> 用户自定义完整性约束 (check)
> 
>   
>

SQL Code

<table style="width:100%; font-family:'Consolas','Courier New'; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:rgb(239,239,239); width:100%; vertical-align:text-top"> 
 <div style="margin:7px"> 
 create&nbsp; 
 table&nbsp;t_emp(empno&nbsp; 
 int&nbsp; 
 not&nbsp; 
 null&nbsp; 
 primary&nbsp; 
 key,&nbsp;ename&nbsp; 
 varchar( 
 20),&nbsp;esex&nbsp; 
 char( 
 2));&nbsp; 
 /*&nbsp;创建时设置主键*/ 
 
 
 create&nbsp; 
 table&nbsp;t_emp(empno&nbsp; 
 int,&nbsp;ename&nbsp; 
 varchar( 
 20),&nbsp;esex&nbsp; 
 char( 
 2),&nbsp; 
 primary&nbsp; 
 key&nbsp;(empno)); 
 
 
 create&nbsp; 
 table&nbsp;t_emp(empno&nbsp; 
 int,&nbsp;ename&nbsp; 
 varchar( 
 20),&nbsp;esex&nbsp; 
 char( 
 2),&nbsp; 
 constraint&nbsp;PK_EMPNO&nbsp; 
 primary&nbsp; 
 key(empno));&nbsp; 
 /*&nbsp;设置主键*/ 
 
 
 create&nbsp; 
 table&nbsp;t_emp(empno&nbsp; 
 int,&nbsp;ename&nbsp; 
 varchar( 
 20),&nbsp;esex&nbsp; 
 char( 
 2)); 
 
 
 alter&nbsp; 
 table&nbsp;t_emp&nbsp; 
 add&nbsp; 
 constraint&nbsp;PK_EMPNO&nbsp; 
 primary&nbsp; 
 key(empno);&nbsp;&nbsp;&nbsp; 
 /*&nbsp;这种方式也可以设置主键&nbsp;*/ 
 
 
 insert&nbsp; 
 into&nbsp;t_emp&nbsp; 
 values( 
 1000,&nbsp; 
 'john',&nbsp; 
 'm'); 
 
 
 insert&nbsp; 
 into&nbsp;t_emp&nbsp; 
 values( 
 1000,&nbsp; 
 'lily',&nbsp; 
 'f');&nbsp;&nbsp;&nbsp;&nbsp; 
 /*&nbsp;error，empno不能相等&nbsp;*/ 
 
 
 insert&nbsp; 
 into&nbsp;t_emp&nbsp; 
 values( 
 null,&nbsp; 
 'lily',&nbsp; 
 'f');&nbsp;&nbsp;&nbsp;&nbsp; 
 /*&nbsp;error，主键不能为空&nbsp;*/ 
 
 
 
 
 create&nbsp; 
 table&nbsp;t_emp(empno&nbsp; 
 int,&nbsp;deptno&nbsp; 
 int,&nbsp;ename&nbsp; 
 varchar( 
 20),&nbsp;esex&nbsp; 
 char( 
 2)); 
 
 
 alter&nbsp; 
 table&nbsp;t_emp&nbsp; 
 add&nbsp; 
 constraint&nbsp;PK_EMPNO&nbsp; 
 primary&nbsp; 
 key(empno); 
 
 
 create&nbsp; 
 table&nbsp;t_dept(deptno&nbsp; 
 int,&nbsp;dname&nbsp; 
 varchar( 
 20)); 
 
 
 alter&nbsp; 
 table&nbsp;t_dept&nbsp; 
 add&nbsp; 
 constraint&nbsp;PK_DEPTNO&nbsp; 
 primary&nbsp; 
 key(deptno); 
 
 
 alter&nbsp; 
 table&nbsp;t_emp&nbsp; 
 add&nbsp; 
 constraint&nbsp;FK_DEPTNO&nbsp; 
 foreign&nbsp; 
 key(deptno)&nbsp; 
 references&nbsp;t_dept(deptno);&nbsp; 
 /*设置t_emp&nbsp;的外键为t_dept&nbsp;的主键&nbsp;*/ 
 
 
 set&nbsp; 
 names&nbsp;gbk; 
 
 insert&nbsp; 
 into&nbsp;t_dept&nbsp; 
 values( 
 2001,&nbsp; 
 '人事部'); 
 
 insert&nbsp; 
 into&nbsp;t_dept&nbsp; 
 values( 
 2002,&nbsp; 
 '技术部'); 
 
 
 insert&nbsp; 
 into&nbsp;t_emp&nbsp; 
 values( 
 1001,&nbsp; 
 2001,&nbsp; 
 'john',&nbsp; 
 'm'); 
 
 
 insert&nbsp; 
 into&nbsp;t_emp&nbsp; 
 values( 
 1003,&nbsp; 
 2003,&nbsp; 
 'john',&nbsp; 
 'm'); 
 
 
 create&nbsp; 
 table&nbsp;t_test1(id&nbsp; 
 int&nbsp;auto_increment&nbsp; 
 primary&nbsp; 
 key,&nbsp;name&nbsp; 
 varchar( 
 30),&nbsp;age&nbsp; 
 int&nbsp; 
 default&nbsp; 
 20); 
 
 
 insert&nbsp; 
 into&nbsp;t_test1&nbsp; 
 values( 
 null,&nbsp; 
 'aaa'); 
 
 
 insert&nbsp; 
 into&nbsp;t_test1&nbsp; 
 values( 
 null,&nbsp; 
 'aaa',&nbsp; 
 null); 
 
 
 insert&nbsp; 
 into&nbsp;t_test1&nbsp;(name)&nbsp; 
 values(&nbsp; 
 'bbb'); 
 
 
 create&nbsp; 
 table&nbsp;t_test2(id&nbsp; 
 int,&nbsp;name&nbsp; 
 varchar( 
 30),&nbsp;age&nbsp; 
 int); 
 
 alter&nbsp; 
 table&nbsp;t_test2&nbsp; 
 add&nbsp; 
 constraint&nbsp;CC_AGE&nbsp; 
 check&nbsp;(age&nbsp;&gt;= 
 18&nbsp; 
 and&nbsp;age&lt;= 
 60);&nbsp; 
 /*&nbsp;实际上现在mysql不支持check限制&nbsp;*/ 
 
 
 alter&nbsp; 
 table&nbsp;t_test2&nbsp; 
 add&nbsp; 
 constraint&nbsp;CC_AGE&nbsp; 
 check&nbsp;( 
 length(name)&gt; 
 2); 
 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

增加/删除unique key

ALTER TABLE Persons
    ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

ALTER TABLE Persons
    DROP INDEX uc_PersonID

此时insert ignore 如果插入的数据中有重复的primary key or unique 索引，则忽略不插入

mysql 中常用的四种插入数据的语句:

insert into 表示插入数据，数据库会检查主键，如果出现重复会报错；

replace into 表示插入替换数据，需求表中有Primary Key，或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和

insert into 一样；

insert ignore 表示，如果中已经存在完全相同的记录，或者primary key/ unique 索引冲突， 则忽略当前新数据，但不会出现错误

insert into ... ON DUPLICATE KEY UPDATE 如果插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE

MySQL MyIsAm 存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，索引键最大长度总和不能超过1000(注意：utf8 为 3bytes），innodb 引擎则不受限制。查询系统是否支持 innodb，可以 执行如下命令：  
SHOW variables like "have\_%"  
显示结果中会有如下3种可能的结果：  
have\_innodb YES  
have\_innodb NO  
have\_innodb DISABLED  
这 3 种结果分别对应：  
已经开启 InnoDB 引擎  
未安装 InnoDB 引擎  
未启用 InnoDB 引擎  
针对第二种未安装，只需要安装即可；针对第三种未启用，则打开mysql 配置文件，找到 skip-innodb 项，将其改成 \#skip-innodb，之后重启 mysql 服务即可。

**三、select 查询**

练习前先导入数据：

create database scott;

use scott;

source C:\\scott.sql scott.sql [ 点这下载][Link 1]

或者 mysql -uxxx -pxxx scott < scott.sql

1.select 单表查询：

SQL Code

<table style="width:100%; font-family:'Consolas','Courier New'; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:rgb(239,239,239); width:100%; vertical-align:text-top"> 
 <div style="margin:7px"> 
 select&nbsp;empno,ename,job&nbsp; 
 from&nbsp;emp; 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp; 
 
 
 SELECT&nbsp;empno&nbsp; 
 as&nbsp; 
 '工号',ename&nbsp; 
 '姓名'&nbsp; 
 FROM&nbsp;emp;&nbsp; 
 /*&nbsp;as&nbsp;后面是别名&nbsp;*/ 
 
 SELECT&nbsp;empno,&nbsp; 
 '暨南大学'&nbsp; 
 FROM&nbsp;emp;&nbsp; 
 /*&nbsp;常量列查询&nbsp;*/ 
 
 SELECT&nbsp;empno,&nbsp; 
 concat(ename, 
 '#')&nbsp; 
 FROM&nbsp;emp;&nbsp; 
 /*&nbsp;concat连接&nbsp;*/ 
 
 SELECT&nbsp;empno,&nbsp;ename|| 
 '#'&nbsp; 
 FROM&nbsp;emp;&nbsp; 
 /*&nbsp;oracle可以用||作为连接符&nbsp;*/ 
 
 
 SELECT&nbsp;empno,&nbsp;ename,&nbsp;job&nbsp; 
 FROM&nbsp;emp&nbsp; 
 WHERE&nbsp;ename&nbsp;=&nbsp; 
 'SMITH' 
 
 SELECT&nbsp;empno,&nbsp;ename,&nbsp;job&nbsp; 
 FROM&nbsp;emp&nbsp; 
 WHERE&nbsp;ename&nbsp;&lt;&gt;&nbsp; 
 'SMITH'&nbsp; 
 /*&nbsp;也可以使用!=&nbsp;*/ 
 
 SELECT&nbsp;empno,&nbsp;ename,&nbsp;sal&nbsp; 
 FROM&nbsp;emp&nbsp; 
 WHERE&nbsp;sal&gt;=&nbsp; 
 1500 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 WHERE&nbsp;deptno= 
 30&nbsp; 
 and&nbsp;sal&gt; 
 1500;&nbsp; 
 /*&nbsp;&nbsp;and&nbsp;*/ 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 WHERE&nbsp;job= 
 'MANAGER'&nbsp; 
 or&nbsp;job= 
 'SALESMAN'&nbsp; 
 /*&nbsp;or&nbsp;*/ 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 where&nbsp;sal&nbsp; 
 BETWEEN&nbsp; 
 800&nbsp; 
 and&nbsp; 
 1500; 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 where&nbsp;sal&nbsp;&gt;=&nbsp; 
 800&nbsp; 
 and&nbsp;sal&nbsp;&lt;=&nbsp; 
 1500; 
 
 SELECT&nbsp;empno,&nbsp;ename,&nbsp;sal,&nbsp;comm&nbsp; 
 FROM&nbsp;emp&nbsp; 
 WHERE&nbsp;comm&nbsp; 
 is&nbsp; 
 null&nbsp; 
 
 SELECT&nbsp;empno,&nbsp;ename,&nbsp;sal,&nbsp;comm&nbsp; 
 FROM&nbsp;emp&nbsp; 
 WHERE&nbsp;comm&nbsp; 
 is&nbsp; 
 not&nbsp; 
 null&nbsp; 
 /*&nbsp;not&nbsp;*/ 
 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 where&nbsp;sal&nbsp; 
 not&nbsp; 
 BETWEEN&nbsp; 
 800&nbsp; 
 and&nbsp; 
 1500;&nbsp; 
 /*&nbsp;between&nbsp;*/ 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 where&nbsp;ename&nbsp; 
 in&nbsp;( 
 'SMITH',&nbsp; 
 'KING');&nbsp; 
 /*&nbsp;in&nbsp;*/ 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 where&nbsp;ename&nbsp; 
 like&nbsp; 
 'S%';&nbsp; 
 /*&nbsp;模糊查询&nbsp;通配符:&nbsp;‘%’（0个多个字符）；&nbsp;通配符:&nbsp;‘_’&nbsp;（单个字符）&nbsp;*/ 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 where&nbsp;ename&nbsp; 
 like&nbsp; 
 'S_ITH'; 
 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 ORDER&nbsp; 
 BY&nbsp;ename&nbsp; 
 desc;&nbsp;&nbsp; 
 /*&nbsp;order&nbsp;by&nbsp;默认是升序&nbsp;asc&nbsp;*/ 
 
 SELECT&nbsp;empno,&nbsp;ename,&nbsp;job&nbsp; 
 FROM&nbsp;emp&nbsp; 
 ORDER&nbsp; 
 BY&nbsp; 
 2&nbsp; 
 desc; 
 
 SELECT&nbsp;*&nbsp; 
 FROM&nbsp;emp&nbsp; 
 ORDER&nbsp; 
 BY&nbsp;job&nbsp; 
 asc,&nbsp;sal&nbsp; 
 desc; 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

如果我就真的要查%或者\_，怎么办呢？使用escape，转义字符后面的%或\_就不作为通配符了，注意前面没有转义字符的%和\_仍然起通配符作用  
select username from gg\_user where username like '%xiao/\_%' escape '/';  
select username from gg\_user where username like '%xiao/%%' escape '/';

在like 里面为了查找 “\\”，必须指定它为 “\\\\\\\\” 。

SQL Code

2.select 多表查询：

多表查询

> 交叉连接
> 
> 内连接
> 
> 自身连接
> 
> 外连接

> > 左外连接
> 
> > 右外连接
> 
> > 全连接

> 自然连接

交叉连接是不带WHERE子句的多表查询，它返回被连接的两个表所有数据行的笛卡尔积。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

内连接（等值连接）：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

内连接（不等连接）：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>

内连接(自身连接)

外连接（左连接）： 返回包括左表中的所有记录和右表中联结字段相等的记录；即左外连接就是在等值连接的基础上加上主表中的未匹配数据（被连接

表字段为 NULL）。

外连接（右连接）：返回包括右表中的所有记录和左表中联结字段相等的记录；即右外连接是在等值连接的基础上加上被连接表的不匹配数据（连接表字段为 NULL）。

外连接（全连接）：全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上。mysql 不支持 full outer join。

自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

SQL Code

![20151201184043420][]

3.子查询/any/all./exists

子查询即一个查询语句嵌到另一个查询语句的子句中；可以出现在另一个查询的列中，where子句中，from子句中等。

<any，小于子查询中的某个值。等价于<max

>any，大于子查询中的某个值。等价于>min

>all，大于子查询中的所有值。等价于>max

<all，小于子查询中的所有值。等价于<min

exists 存在性条件判断: 若内层查询非空，则外层的where子句返回真值，否则返回假。not exists相反。

SQL Code

<table style="width:100%; font-family:'Consolas','Courier New'; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:rgb(239,239,239); width:100%; vertical-align:text-top"> 
 <div style="margin:7px"> 
 /*&nbsp;查询员工及其领导名称&nbsp;*/ 
 
 select&nbsp;A.ename&nbsp;员工,&nbsp;B.ename&nbsp;领导&nbsp;&nbsp; 
 from&nbsp;emp&nbsp;A,&nbsp;emp&nbsp;B&nbsp; 
 where&nbsp;A.mgr&nbsp;=&nbsp;B.empno; 
 
 
 /*&nbsp;子查询，同上&nbsp;*/ 
 
 select&nbsp;ename&nbsp;员工,&nbsp;( 
 select&nbsp;ename&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;empno&nbsp;=&nbsp;e.mgr)&nbsp;领导 
 
 from&nbsp;emp&nbsp;e; 
 
 
 /*&nbsp;列出所有“CLERK”（办事员）的姓名及其部门名称&nbsp;*/ 
 
 select&nbsp;ename,&nbsp;dname&nbsp; 
 from&nbsp;emp,dept&nbsp; 
 where&nbsp;job= 
 'CLERK'&nbsp; 
 and&nbsp;emp.deptno&nbsp;=&nbsp;dept.deptno; 
 
 
 /*&nbsp;子查询，同上&nbsp;*/ 
 
 select&nbsp;ename,&nbsp;( 
 select&nbsp;dname&nbsp; 
 from&nbsp;dept&nbsp; 
 where&nbsp;deptno=e.deptno)&nbsp;dname 
 
 from&nbsp;emp&nbsp;e&nbsp; 
 where&nbsp;job= 
 'CLERK'&nbsp; 
 and&nbsp;deptno&nbsp; 
 in&nbsp;( 
 select&nbsp;deptno&nbsp; 
 from&nbsp;dept); 
 
 
 /*&nbsp;子查询，同上&nbsp;*/ 
 
 select&nbsp;ename,&nbsp;dname 
 
 from 
 ( 
 select&nbsp;ename,&nbsp;( 
 select&nbsp;dname&nbsp; 
 from&nbsp;dept&nbsp; 
 where&nbsp;deptno=e.deptno)&nbsp;dname 
 
 from&nbsp;emp&nbsp;e&nbsp; 
 where&nbsp;job= 
 'CLERK')&nbsp;a 
 
 where&nbsp;dname&nbsp; 
 is&nbsp; 
 not&nbsp; 
 null; 
 
 
 /*&nbsp;子查询，多出deptno=90的行&nbsp;*/ 
 
 select&nbsp;ename,&nbsp;( 
 select&nbsp;dname&nbsp; 
 from&nbsp;dept&nbsp; 
 where&nbsp;deptno=e.deptno)&nbsp;dname 
 
 from&nbsp;emp&nbsp;e&nbsp; 
 where&nbsp;job= 
 'CLERK'; 
 
 /*&nbsp;同上&nbsp;*/ 
 
 select&nbsp;ename,&nbsp;dname&nbsp; 
 from&nbsp;emp&nbsp;&nbsp; 
 LEFT&nbsp; 
 JOIN&nbsp;dept&nbsp; 
 on&nbsp;emp.deptno&nbsp;=&nbsp;dept.deptno&nbsp; 
 where&nbsp;job= 
 'CLERK'; 
 
 
 
 
 /*&nbsp;列出薪金比'SMITH'高的员工*/ 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;sal&nbsp;&gt;&nbsp;( 
 select&nbsp;sal&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;ename= 
 'SMITH'); 
 
 /*&nbsp;列出受雇日期早于其直接上级的所有员工*/ 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp&nbsp;e&nbsp; 
 where&nbsp;hiredate&nbsp;&lt;&nbsp;( 
 select&nbsp;hiredate&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;empno=e.mgr); 
 
 
 
 /*&nbsp;查询薪金小于销售员某个员工的员工信息*/ 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp&nbsp; 
 WHERE 
 sal&nbsp;&lt;&nbsp; 
 any&nbsp;( 
 select&nbsp;sal&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;job= 
 'SALESMAN'); 
 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp&nbsp; 
 WHERE 
 sal&nbsp;&lt;&nbsp;( 
 select&nbsp; 
 max(sal)&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;job= 
 'SALESMAN'); 
 
 
 /*&nbsp;查询薪金大于所有销售员的员工信息&nbsp;*/ 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp&nbsp; 
 WHERE 
 sal&nbsp;&gt;&nbsp; 
 all&nbsp;( 
 select&nbsp;sal&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;job= 
 'SALESMAN'); 
 
 
 /*&nbsp;列出与“SCOTT”从事相同工作的所有员工&nbsp;*/ 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp&nbsp;e&nbsp; 
 where&nbsp; 
 EXISTS 
 ( 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;ename= 
 'SCOTT'&nbsp; 
 and&nbsp;e.job&nbsp;=&nbsp;job 
 ); 
 
 
 select&nbsp;*&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;job&nbsp;=( 
 select&nbsp;job&nbsp; 
 from&nbsp;emp&nbsp; 
 where&nbsp;ename= 
 'SCOTT'); 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

4. 聚合函数/group by/having/group by与子查询

聚合函数一般用于统计，常用如下:

> count(field) //记录数
> 
> avg(field) //平均值
> 
> min(field) //最小值
> 
> max(field) //最大值
> 
> sum(field) //总和

group by/having：分组查询通常用于统计，一般和聚合函数配合使用

select 分组字段或聚合函数  
from 表  
group by 分组字段 having 条件  
order by 字段

SQL Code

select url, get\_keys, post\_keys, count(distinct url, get\_keys, post\_keys) from sec\_tmp\_after\_task1\_step10 group by url, get\_keys, post\_keys

select url, get\_keys, post\_keys, wm\_concat(distinct url\_source, ",", "asc") as url\_source\_all from sec\_tmp\_luascan\_task\_step1 group by url, get\_keys, post\_keys

5.MySQL函数

> 控制流程函数
> 
> 字符串函数
> 
> 数值函数
> 
> 日期和时间函数

> > now
> 
> > date\_add/adddate
> 
> > datediff
> 
> > date\_format

>   
> 
> 
> 聚合函数

SQL Code

<table style="width:100%; font-family:'Consolas','Courier New'; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:rgb(239,239,239); width:100%; vertical-align:text-top"> 
 <div style="margin:7px"> 
 CASE&nbsp; 
 value&nbsp; 
 WHEN&nbsp;[compare- 
 value]&nbsp; 
 THEN&nbsp; 
 result&nbsp;[ 
 WHEN&nbsp;[compare- 
 value]&nbsp; 
 THEN&nbsp; 
 result&nbsp;...]&nbsp;[ 
 ELSE&nbsp; 
 result]&nbsp; 
 END 
 
 
 CASE&nbsp; 
 WHEN&nbsp;[condition]&nbsp; 
 THEN&nbsp; 
 result&nbsp;[ 
 WHEN&nbsp;[condition]&nbsp; 
 THEN&nbsp; 
 result&nbsp;...]&nbsp;[ 
 ELSE&nbsp; 
 result]&nbsp; 
 END&nbsp; 
 
 
 SELECT&nbsp; 
 CASE&nbsp; 
 1&nbsp; 
 WHEN&nbsp; 
 1&nbsp; 
 THEN&nbsp; 
 'one'&nbsp; 
 
 WHEN&nbsp; 
 2&nbsp; 
 THEN&nbsp; 
 'two'&nbsp; 
 ELSE&nbsp; 
 'more'&nbsp; 
 END; 
 
 
 SELECT&nbsp; 
 CASE&nbsp; 
 WHEN&nbsp; 
 1&gt; 
 0&nbsp; 
 THEN&nbsp; 
 'true'&nbsp; 
 ELSE&nbsp; 
 'false'&nbsp; 
 END; 
 
 select &nbsp;a.cgi from ( select case when port=443 then concat('https://', url) else concat('http://', url) as cgi from t_url) a; 
 </div> 
 <div style="margin:7px"> 
 
 /*&nbsp;查询员工的薪金等级 */ 
 
 select&nbsp;ename&nbsp;员工,&nbsp;sal&nbsp;薪金,&nbsp; 
 case&nbsp;grade&nbsp; 
 when&nbsp; 
 1&nbsp; 
 then&nbsp; 
 '一级' 
 
 when&nbsp; 
 2&nbsp; 
 then&nbsp; 
 '二级' 
 
 when&nbsp; 
 3&nbsp; 
 then&nbsp; 
 '三级' 
 
 when&nbsp; 
 4&nbsp; 
 then&nbsp; 
 '四级' 
 
 when&nbsp; 
 5&nbsp; 
 then&nbsp; 
 '五级' 
 
 end&nbsp;等级 
 
 from&nbsp;emp,&nbsp;salgrade 
 
 where&nbsp;sal&nbsp; 
 between&nbsp;losal&nbsp; 
 and&nbsp;hisal; 
 
 
 SELECT&nbsp; 
 ASCII( 
 '2a'); 
 
 SELECT&nbsp; 
 ASCII( 
 'a2'); 
 
 SELECT&nbsp; 
 BIN( 
 12); 
 
 SELECT&nbsp;BIT_LENGTH( 
 'text'); 
 
 
 SELECT&nbsp; 
 CHAR( 
 77, 
 121, 
 83, 
 81, 
 '76'); 
 
 SELECT&nbsp; 
 CHAR( 
 77, 
 121, 
 83, 
 81, 
 76); 
 
 
 SELECT&nbsp; 
 3+ 
 5; 
 
 SELECT&nbsp; 
 3/ 
 5; 
 
 
 SELECT&nbsp; 
 ABS(- 
 32); 
 
 
 select&nbsp;now(); 
 
 
 SELECT&nbsp; 
 DATE_ADD( 
 '1998-02-02',&nbsp; 
 INTERVAL&nbsp; 
 31&nbsp; 
 DAY); 
 
 SELECT&nbsp; 
 DATE_ADD( 
 '1998-02-02',&nbsp; 
 INTERVAL&nbsp; 
 28&nbsp; 
 DAY); 
 
 
 SELECT&nbsp; 
 adddate( 
 '1998-02-02',&nbsp; 
 INTERVAL&nbsp; 
 28&nbsp; 
 DAY); 
 
 
 SELECT&nbsp; 
 adddate( 
 '1998-02-02',&nbsp; 
 28); 
 
 
 select&nbsp; 
 DATEDIFF(now(), 
 '2014-02-01'); 
 
 select&nbsp; 
 DATEDIFF( 
 '2014-02-01', 
 '2014-03-01'); 
 
 
 
 select&nbsp; 
 DATE_FORMAT(now(),&nbsp; 
 '%H:%i:%s'); 
 
 
 select&nbsp; 
 DATE_FORMAT(now(),&nbsp; 
 '%Y%M%D'); 
 
 
 select&nbsp; 
 DATE_FORMAT(now(),&nbsp; 
 '%Y%m%d'); 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

工作中常用的时间函数还有 time\_to\_sec, date\_format, str\_to\_date, addtime, timestampdiff 等，注意 NULL 数据做什么运算结果都是NULL，不为真，为此可以用 ifnull(exp1, exp2) 指定默认值。

参考：  
《数据库系统概论》  
mysql 5.1 参考手册

[Link 1]: http://download.csdn.net/detail/simba888888/6933011
[20151201184043420]: /images/20220824/a928629a8f174c96b9880c6f92e964b5.png