关于我的数据库——MySQL——第四篇
（叠甲：如有侵权请联系，内容都是自己学习的总结，一定不全面，仅当互相交流（轻点骂）我也只是站在巨人肩膀上的一个小卡拉米，已老实，求放过）。
函数
| 函数名称 | 描述 |
| current_date() | 当前日期 |
| current_time() | 当前时间 |
| current_timestamp() | 当前时间戳 |
| date(datetime) | 返回datetime 参数的日期部分 |
| date_add(date,interval d_value_type) | 在date中减去日期或时间 interval 后的数值单位可以是：year minute second day |
| date_sub(date, interval d_value_type) | 在date中减去日期或时间 interval 后的数值单位可以是：year minute second day |
| datediff(datel,date2) | 两天日期的差，单位是天 |
| now() | 当前日期时间 |
获取年月日
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-10-26 |
+----------------+
1 row in set (0.00 sec)获取时分秒
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 09:37:40 |
+----------------+
1 row in set (0.00 sec)
获取时间戳
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-10-26 09:38:03 |
+---------------------+
1 row in set (0.00 sec)
在日期的基础上加日期
mysql> select date_add('2024-10-26',interval 59 day);
+----------------------------------------+
| date_add('2024-10-26',interval 59 day) |
+----------------------------------------+
| 2024-12-24 |
+----------------------------------------+
1 row in set (0.00 sec)
在日期的基础上减日期
mysql> select date_sub('2024-10-26',interval 59 day)
);
+----------------------------------------+
| date_sub('2024-10-26',interval 59 day) |
+----------------------------------------+
| 2024-08-28 |
+----------------------------------------+
1 row in set (0.00 sec)
例子：创建一张生日表
mysql> create table birthday( id int primary key auto_increment, birthday date);
Query OK, 0 rows affected (0.02 sec)//添加当前日期
mysql> insert into birthday values( 1, current_date(());
Query OK, 1 row affected (0.01 sec)查询结果
mysql> select *from birthday;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2024-10-26 |
+----+------------+
1 row in set (0.00 sec)
案例2：创建一个留言表
//建表
mysql> create table msg( id int primary key auto_increment, content varchar(30)not null, sendtime datetime);
Query OK, 0 rows affected (0.02 sec)//插入数据
mysql> insert into msg(content,sendtime)values('hello1',now());
Query OK, 1 row affected (0.00 sec)mysql> insert into msg(content,sendtime)values('hello2',now());
Query OK, 1 row affected (0.00 sec)//查询数据
mysql> select *from msg;
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 1 | hello1 | 2024-10-26 09:52:32 |
| 2 | hello2 | 2024-10-26 09:52:40 |
+----+---------+---------------------+
2 rows in set (0.00 sec)//显示所有留言信息，发布日期只显示日期，不用显示时间
mysql> select content, date(sendtime)from msg;
+---------+----------------+
| content | date(sendtime) |
+---------+----------------+
| hello1 | 2024-10-26 |
| hello2 | 2024-10-26 |
+---------+----------------+
2 rows in set (0.00 sec)//显示所有信息，发布信息包含具体时间
mysql> select content, sendtime from msg;
+---------+---------------------+
| content | sendtime |
+---------+---------------------+
| hello1 | 2024-10-26 09:52:32 |
| hello2 | 2024-10-26 09:52:40 |
+---------+---------------------+
2 rows in set (0.00 sec)//查询2分钟内发布的贴子--插入的信息均超过2分钟
mysql> select * from msg where date_add(sendtime,interval 2 minute)>now();
Empty set (0.00 sec)//插入新数据
mysql> insert into msg(content,sendtime)values('hello3',now());
Query OK, 1 row affected (0.01 sec)//再次查询
mysql> select * from msg where date_add(sendtime,interval 2 minute)>now();
+----+---------+---------------------+
| id | content | sendtime |
+----+---------+---------------------+
| 3 | hello3 | 2024-10-26 09:55:19 |
+----+---------+---------------------+
1 row in set (0.00 sec)
字符串函数
| charset(str) | 返回字符串字符集 |
| concat(sting2 [,……]) | 连接字符串 |
| insert(sting,substing) | 返回substring在string中出现的位置，没有返回0 |
| ucase(string2) | 转换成大写 |
| lcase(string2) | 转换成小写 |
| left(string2,length) | 从string2中的左边起去length个字符 |
| length(string) | sting的长度 |
| replace(str,serch_str,replace_str) | 在str中用replace_str 替换search_str |
| strcmp(string1,string2) | 逐字比较两字符串大小 |
| substring(str,position [,length]) | 从str的potion开始,取出length个字符 |
| ltrim(string) | rtrim(string)trim(string) | 去除前空格或后空格 |
获取emp表中的ename列的字符集
mysql> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
+----------------+
14 rows in set (0.00 sec)
要求显示student表中的信息，显示格式：‘XXX的XX是XXX分，XXXXXX分’
mysql> select concat(name, '的语文是',chinese,'分,
数学是',math,'分')as'分数'from exam_result;
+---------------------------------------------+
| 分数 |
+---------------------------------------------+
| 唐三藏的语文是134分,数学是107分 |
| 猪八戒的语文是110分,数学是114分 |
| 曹孟德的语文是112分,数学是108分 |
| 刘玄德的语文是176分,数学是81分 |
| 诸葛亮的语文是196分,数学是97分 |
| 荀彧的语文是192分,数学是95分 |
| 马超的语文是176分,数学是98分 |
| 孙武的语文是176分,数学是98分 |
+---------------------------------------------+
求学生表中学生姓名占用的字节数
mysql> select length(name),name from exam_result;
+--------------+-----------+
| length(name) | name |
+--------------+-----------+
| 9 | 唐三藏 |
| 9 | 猪八戒 |
| 9 | 曹孟德 |
| 9 | 刘玄德 |
| 9 | 诸葛亮 |
| 6 | 荀彧 |
| 6 | 马超 |
| 6 | 孙武 |
+--------------+-----------+
将emp中所有名字有S的替换成‘上海’
mysql> select replace(ename,'S','上海'),ename from
emp;
+-----------------------------+--------+
| replace(ename,'S','上海') | ename |
+-----------------------------+--------+
| 上海MITH | SMITH |
| ALLEN | ALLEN |
| WARD | WARD |
| JONE上海 | JONES |
| MARTIN | MARTIN |
| BLAKE | BLAKE |
| CLARK | CLARK |
| 上海COTT | SCOTT |
| KING | KING |
| TURNER | TURNER |
| ADAM上海 | ADAMS |
| JAME上海 | JAMES |
| FORD | FORD |
| MILLER | MILLER |
+-----------------------------+--------+
14 rows in set (0.00 sec)
截取emp表中ename字段的第1个到第三个字符
mysql> select substring(ename,2,2),ename from emp;
+----------------------+--------+
| substring(ename,2,2) | ename |
+----------------------+--------+
| MI | SMITH |
| LL | ALLEN |
| AR | WARD |
| ON | JONES |
| AR | MARTIN |
| LA | BLAKE |
| LA | CLARK |
| CO | SCOTT |
| IN | KING |
| UR | TURNER |
| DA | ADAMS |
| AM | JAMES |
| OR | FORD |
| IL | MILLER |
+----------------------+--------+
14 rows in set (0.00 sec)
以首字母小写的方式显示所有员工的姓名
mysql> select concat(lcase(substring(ename,1,1)),susbstring(ename,2)) from emp;
+--------------------------------------------------------+
| concat(lcase(substring(ename,1,1)),substring(ename,2)) |
+--------------------------------------------------------+
| sMITH |
| aLLEN |
| wARD |
| jONES |
| mARTIN |
| bLAKE |
| cLARK |
| sCOTT |
| kING |
| tURNER |
| aDAMS |
| jAMES |
| fORD |
| mILLER |
+--------------------------------------------------------+
14 rows in set (0.00 sec)
数学函数
| 函数名称 | 描述 |
| abs(number) | 绝对值函数 |
| bin(decimal_number) | 十进制转换二进制 |
| hex(decimal_number) | 十进制转换十六进制 |
| conv(number,from_base,to_base) | 进制转换 |
| ciling(number) | 向上取整 |
| floor(number) | 向下取整 |
| format(number,decimal_places) | 格式化，保留小数位数 |
| hex(decimaNumber) | 转换成十六进制 |
| rand() | 返回随机浮点数，范围[0.0,1.0] |
| mod(number,denominator) | 取模，求余 |
绝对值
mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
| 100.2 |
+-------------+
1 row in set (0.00 sec)
向上取整
mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
| 24 |
+----------------+
1 row in set (0.00 sec)
向下取整
mysql> select floor(23.04);
+--------------+
| floor(23.04) |
+--------------+
| 23 |
+--------------+
1 row in set (0.00 sec)
保留2位小数位数（小数四舍五入）
mysql> select format(12.456,2);
+------------------+
| format(12.456,2) |
+------------------+
| 12.46 |
+------------------+
1 row in set (0.00 sec)
产生随机数
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.8124908859026534 |
+--------------------+
1 row in set (0.00 sec)
user()查询当前用户
mysql> select user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)
md5（str）对一个字符串进行md5摘要，摘要后得到一个32位字符串
mysql> select md5('admin');
+----------------------------------+
| md5('admin') |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set (0.00 sec)
database（）显示当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| scott |
+------------+
1 row in set (0.00 sec)
password（）函数，MySQL数据库使用该函数对用户加密
mysql> select password('root');
+-------------------------------------------+
| password('root') |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
ifnull（val1,val2）如果val1为null,返回val2，否则返回val1
mysql> select ifnull('abc','123');
+---------------------+
| ifnull('abc','123') |
+---------------------+
| abc |
+---------------------+
1 row in set (0.00 sec)mysql> select ifnull(null,'123');
+--------------------+
| ifnull(null,'123') |
+--------------------+
| 123 |
+--------------------+
1 row in set (0.00 sec)
复合查询（重点）
查询工资高于500或岗位为manager的雇员，同时还满足他们的名字首字母为大写的J
mysql> select *from emp where(sal>500 or job = 'manager') and ename like 'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
按照部门升序而工资降序排列
mysql> select *from emp order by deptno ,sal desc;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
使用年薪进行降序排列
mysql> select ename , sal*12+ifnull(comm,0)as'年薪'from emp order by 年薪 desc;
+--------+----------+
| ename | 年薪 |
+--------+----------+
| KING | 60000.00 |
| SCOTT | 36000.00 |
| FORD | 36000.00 |
| JONES | 35700.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| ALLEN | 19500.00 |
| TURNER | 18000.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| WARD | 15500.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| SMITH | 9600.00 |
+--------+----------+
14 rows in set (0.00 sec)
显示工资最高的员工的名字和工作岗位
mysql> select ename ,job from emp where sal = (select max(sal)from emp);
+-------+-----------+
| ename | job |
+-------+-----------+
| KING | PRESIDENT |
+-------+-----------+
1 row in set (0.00 sec)
显示工资高于平均工资的员工信息
mysql> select ename ,job from emp where sal = (select max(sal)from emp);
+-------+-----------+
| ename | job |
+-------+-----------+
| KING | PRESIDENT |
+-------+-----------+
1 row in set (0.00 sec)
显示每个部门的平均工资和最高工资
mysql> select deptno ,format(avg(sal),2),max(sal) forom emp group by deptno;
+--------+--------------------+----------+
| deptno | format(avg(sal),2) | max(sal) |
+--------+--------------------+----------+
| 10 | 2,916.67 | 5000.00 |
| 20 | 2,175.00 | 3000.00 |
| 30 | 1,566.67 | 2850.00 |
+--------+--------------------+----------+
3 rows in set (0.00 sec)
显示平均工资低于2000的部门号和它的平均工资（--having 和 group by配合使用，对group by 结果进行过滤）
mysql> select deptno ,avg(sal)as avg_sal from emp group by deptno having avg_sal<2000;
+--------+-------------+
| deptno | avg_sal |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)显示每种岗位的雇员总数，平均工资
mysql> select job ,count(*),format(avg(sal),2) from emp group by job;
+-----------+----------+--------------------+
| job | count(*) | format(avg(sal),2) |
+-----------+----------+--------------------+
| ANALYST | 2 | 3,000.00 |
| CLERK | 4 | 1,037.50 |
| MANAGER | 3 | 2,758.33 |
| PRESIDENT | 1 | 5,000.00 |
| SALESMAN | 4 | 1,400.00 |
+-----------+----------+--------------------+
5 rows in set (0.00 sec)
多表查询
实际开发中往往数据来自于不同的表，所以需要多表查询，以下例子根据emp、dept、salgrade 来演示如何进行多表查询。
//deptmysql> select *from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)//empmysql> select *from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)salgrademysql> select *from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
笛卡尔积：
从第一张表中选出第一条记录，和第二表的所有记录进行组合；然后从第一张表中取第二条记录，和第二张表中所有记录组合；不加过滤条件，得到的结果称为“笛卡尔积”
mysql> select *from emp,dept;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 10 | ACCOUNTING | NEW YORK |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 20 | RESEARCH | DALLAS |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 40 | OPERATIONS | BOSTON |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 10 | ACCOUNTING | NEW YORK |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 20 | RESEARCH | DALLAS |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 40 | OPERATIONS | BOSTON |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 10 | ACCOUNTING | NEW YORK |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 20 | RESEARCH | DALLAS |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 40 | OPERATIONS | BOSTON |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 30 | SALES | CHICAGO |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 30 | SALES | CHICAGO |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 20 | RESEARCH | DALLAS |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 40 | OPERATIONS | BOSTON |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 30 | SALES | CHICAGO |
| 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 40 | OPERATIONS | BOSTON |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
56 rows in set (0.00 sec)
其实我们只想要emp表中的deptno = dept表中的deptno字段的记录；
mysql> select emp.ename , emp.sal , dept.dname fromm emp,dept where emp.deptno = dept.deptno;
+--------+---------+------------+
| ename | sal | dname |
+--------+---------+------------+
| SMITH | 800.00 | RESEARCH |
| ALLEN | 1600.00 | SALES |
| WARD | 1250.00 | SALES |
| JONES | 2975.00 | RESEARCH |
| MARTIN | 1250.00 | SALES |
| BLAKE | 2850.00 | SALES |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES |
| ADAMS | 1100.00 | RESEARCH |
| JAMES | 950.00 | SALES |
| FORD | 3000.00 | RESEARCH |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.00 sec)
显示部门号为10的部门名，员工名和工资
mysql> select emp.ename , emp.sal , dept.dname from emp,dept where emp.deptno = dept.deptno and dept.deptno = 10;
+--------+---------+------------+
| ename | sal | dname |
+--------+---------+------------+
| CLARK | 2450.00 | ACCOUNTING |
| KING | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
3 rows in set (0.00 sec)
显示各个员工的姓名，工资，及工资级别
mysql> select ename ,sal,grade from emp ,salgrade where emp.sal between losal and hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)自连接
//使用子查询
mysql> select empno ,ename from emp where emp.empno = (select mgr from emp where emp.ename = 'FORD');
+--------+-------+
| empno | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)//使用自连接（多表查询）--from emp leader,emp worker, 给自己的表起别名，因为要做笛卡尔积，所以别名可以先识别
mysql> select leader.empno , leader.ename from emp
leader,emp worker where leader.empno = worker.mgr and worker.ename = 'FORD';
+--------+-------+
| empno | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)
子查询（其中的单行、多行、多列指的是条件的个数）
子查询是指嵌入其他sql语句中的select语句，也叫嵌套语句
单行子查询——返回一行记录的子查询
显示SMITH同一部门的员工
mysql> select *from emp where deptno = (select deptno from emp where ename = 'smith');
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
+--------+-------+---------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)
多行子查询——返回多行记录的子查询
1）in关键字；查询和10号部门的工作岗位相同雇员的名字，岗位，工资，部门号，但是不包含10自己的
mysql> select ename ,job ,sal,deptno from emp where job in (select job from emp where deptno = 10)anddeptno<>10;
+-------+---------+---------+--------+
| ename | job | sal | deptno |
+-------+---------+---------+--------+
| JONES | MANAGER | 2975.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| SMITH | CLERK | 800.00 | 20 |
| ADAMS | CLERK | 1100.00 | 20 |
| JAMES | CLERK | 950.00 | 30 |
+-------+---------+---------+--------+
5 rows in set (0.00 sec)
2）all关键字；显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号
mysql> select ename ,sal,deptno from emp where sal>all(select sal from emp where deptno = 30);
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| JONES | 2975.00 | 20 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| FORD | 3000.00 | 20 |
+-------+---------+--------+
4 rows in set (0.00 sec)
3）any关键字;显示工资比部门30的任意员工的工资高的员工的姓名，工资个部门号（包含自己部门的员工）
mysql> select ename ,sal,deptno from emp where sal>any(select sal from emp where deptno = 30);
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
12 rows in set (0.00 sec)
多列子查询
单行子查询是指子查询只返回单列，单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列的子查询语句。
例子:查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
mysql> select ename from emp where (deptno, job)=(seelect deptno, job from emp where ename='SMITH') and ename <> 'SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.00 sec)
在from子句中使用子查询
子查询语句出现在from子句中，这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。
显示每个高于自己部门平均工资的员工的名字、部门、工资、平均工资。
mysql> select ename, deptno, sal, format(asal,2) from emp, (select avg(sal) asal, deptno dt from emp guroup by deptno) tmp where emp.sal > tmp.asal and emPp.deptno=tmp.dt;
+-------+--------+---------+----------------+
| ename | deptno | sal | format(asal,2) |
+-------+--------+---------+----------------+
| KING | 10 | 5000.00 | 2,916.67 |
| JONES | 20 | 2975.00 | 2,175.00 |
| SCOTT | 20 | 3000.00 | 2,175.00 |
| FORD | 20 | 3000.00 | 2,175.00 |
| ALLEN | 30 | 1600.00 | 1,566.67 |
| BLAKE | 30 | 2850.00 | 1,566.67 |
+-------+--------+---------+----------------+
6 rows in set (0.00 sec)
查找每个部门工资最高人的姓名，工资、部门、最高工资
mysql> select emp.ename, emp.sal, emp.deptno, ms froom emp, (select max(sal) ms, deptno from emp groupby deptno) tmp where emp.deptno=tmp.deptno and emp.ssal=tmp.ms;
+-------+---------+--------+---------+
| ename | sal | deptno | ms |
+-------+---------+--------+---------+
| BLAKE | 2850.00 | 30 | 2850.00 |
| SCOTT | 3000.00 | 20 | 3000.00 |
| KING | 5000.00 | 10 | 5000.00 |
| FORD | 3000.00 | 20 | 3000.00 |
+-------+---------+--------+---------+
4 rows in set (0.00 sec)
显示每个部门的信息（部门名，编号，地址）和人员数量
mysql> select dept.dname, dept.deptno, dept.loc,counnt(*) '部门人数' from emp, dept where emp.deptno=deppt.deptno group by dept.deptno,dept.dname,dept.loc;+------------+--------+----------+--------------+
| dname | deptno | loc | 部门人数 |
+------------+--------+----------+--------------+
| ACCOUNTING | 10 | NEW YORK | 3 |
| RESEARCH | 20 | DALLAS | 5 |
| SALES | 30 | CHICAGO | 6 |
+------------+--------+----------+--------------+
3 rows in set (0.00 sec)
合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union ，union all
union
该操作符用于取的两个结果集的并集，当使用该操作符时，会自动去掉集中的重复行。
案例：将工资大于2500或职位是manager的人找出来
mysql> select ename, sal, job from emp where sal>25000 job='MANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+
8 rows in set (0.00 sec)
union all
该操作符用于取得两个结果集的并集，当使用该操作符时，不会去掉结果集中的重复行
mysql> select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where
job='MANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+
8 rows in set (0.00 sec)