目录
BETWEEN min AND max：在两者之间，即:>=min AND <=max
DISTINCT：用于去除指定列重复值的行，并且DISTINCT关键字必须紧跟SELECT
ORDER BY：根据指定的字段排序查询结果集。该子句只能放在查询语句的最后一个子句上
聚合函数：聚合函数(也称多函数)是用来多条记录统计为一条记录
子查询：嵌套在其他SQL语句中的查询语句被称为叫做"子查询"
外连接：如果需要在结果集中列出不满足连接条件的记录时，需要使用外连接
DQL语句数据查询语句
基本语法：SELECT 字段名1[,字段名2,... 或 *] FROM 表名
1、查看emp表中每条记录的所有字段值
SELECT * FROM emp;
2、查看每个员工的名字，职位，入职时间
SELECT name,job,hiredate FROM emp;
WHERE：添加过滤条件，可以仅将满足条件的记录查询出来
比较运算符：=，>，>=，<，<=，<>
1、查看工资大于1000的员工的名字，职位，工资
SELECT name,job,sal
FROM emp
WHERE sal>10002、查看职位除'人事'之外的所有员工的名字，工资和职位?
SELECT name,sal,job
FROM emp
WHERE job <> '人事'# !=操作不是所有数据库都支持
SELECT name,sal,job
FROM emp
WHERE job != '人事';# 查看部门编号为2的员工的名字，工资，职位，部门编号(dept_id)
SELECT name,sal,job,dept_idFROM emp
WHERE dept_id=2
AND，OR：来连接多个条件
- AND：都为真是才为真
- OR：都为假是才为假
1、查看2号部门工资高于1000的员工的名字，工资，职位，部门编号?
SELECT name,sal,job,dept_id
FROM emp
WHERE dept_id=2 AND sal>10002、查看职位是人事和销售的所有员工的名字，工资，职位，部门编号?
SELECT name,sal,job,dept_id
FROM emp
WHERE job='人事' OR job='销售'3、查看人事部的所有员工和工资高于1000的销售部员工的名字，工资，职位?
SELECT name,sal,job
FROM emp
WHERE job='人事' OR sal>1000 AND job='销售';4、查看人事部和销售部工资高于1000的员工的名字，工资，职位?
SELECT name,sal,job
FROM emp
WHERE (job='人事' OR job='销售') AND sal>1000;
# AND优先级高于OR，因此可以通过()来提高OR的优先级
IN(列表)：值在列表中(等于列表中的其中之一)
1、查看职位是人事和销售的所有员工的名字，工资，职位，部门编号?
SELECT name,sal,job,dept_id
FROM emp
WHERE job IN ('人事','销售');2、查看职位是人事和销售以外的所有员工的名字，工资，职位，部门编号?
SELECT name,sal,job,dept_id
FROM emp
WHERE job<>'人事' AND job<>'销售';
#不在列表中
SELECT name,sal,job,dept_id
FROM emp
WHERE job NOT IN ('人事','销售');3、查看工资在2000到3000之间的员工的名字，工资，职位?
SELECT name,sal,job
FROM emp
WHERE sal>=2000 AND sal<=3000;
BETWEEN min AND max：在两者之间，即:>=min AND <=max
查看工资在2000到3000之间的员工的名字，工资，职位?
SELECT name,sal,job
FROM emp
WHERE sal BETWEEN 2000 AND 3000;
DISTINCT：用于去除指定列重复值的行，并且DISTINCT关键字必须紧跟SELECT
1、查看公司有多少种职位?
SELECT DISTINCT jobFROM emp;
#多列去重时，就是去除指定这些列的值的组合有重复的行
2、去除职位与部门编号重复的行
SELECT DISTINCT job,dept_idFROM emp
练习:
1. 查询2号部门工资高于1000的员工信息
SELECT *
FROM emp
WHERE dept_id=2 AND sal>=1000;
2. 查询3号部门或工资等于5000的员工信息
SELECT *
FROM emp
WHERE dept_id=3 OR sal=5000;
3. 查询工资在1000到2000之间的员工姓名和工资
SELECT name,sal
FROM emp
WHERE sal BETWEEN 1000 AND 2000;
4. 查询工资不等于3000和5000的员工信息
SELECT *
FROM emp
WHERE sal NOT IN(3000,5000);
5. 查询1号部门有哪几种不同的工作
SELECT DISTINCT job
FROM emp
WHERE dept_id=1
LIKE：模糊查询
LIKE中两个通配符：_和%：
- _(下划线)：表示任意的一个字符
- %：表示任意个字符(0-多个)
总结：
%X%：字符串中包含X
%X：字符串以X结尾
X%：字符串以X开头
_X%：字符串第二个字符是X
%X_：倒数第二个字符是X
X%Y：字符串以X开头Y结尾
X_Y：字符串只有三个字，第一个是X，第三个是Y
1、查看姓孙的员工信息?(名字第一个字符是孙的员工)
SELECT name,sal,job
FROM emp
WHERE name LIKE '孙%';2、查看名字里含"悟"的员工?
SELECT name,sal,job
FROM emp
WHERE name LIKE '%悟%';3、查看名字第三个字是'精';
SELECT name,sal,job
FROM emp
WHERE name LIKE '__精';4、查看第二个字是'骨'的员工?
SELECT name,sal,job
FROM emp
WHERE name LIKE '_骨%';
练习:
1. 查询名字姓猪的员工姓名
SELECT name
FROM emp
WHERE name LIKE '猪%';
2. 查询名字中包含僧的员工信息
SELECT *
FROM emp
WHERE name LIKE '%僧%'
3. 查询名字以精结尾的员工姓名
SELECT name
FROM emp
WHERE name LIKE '%精';
4. 查询工作中包含销售并且工资大于1500的员工信息
SELECT *
FROM emp
WHERE job LIKE '%销售%'
AND sal>1500;
5. 查询工作中第二个字是售的员工姓名和工作
SELECT name,job
FROM emp
WHERE job LIKE '_售%';
6. 查询1号和2号部门中工作以市开头的员工信息
SELECT *
FROM emp
WHERE dept_id IN (1,2)
AND job LIKE '市%'
ORDER BY：根据指定的字段排序查询结果集。该子句只能放在查询语句的最后一个子句上
1、查看公司所有员工的工资，且工资从低到高排序
SELECT name,sal
FROM emp
ORDER BY sal;2、按照时间排序时，越早的时间越小，越晚的时间越大
SELECT name,sal,hiredate
FROM emp
ORDER BY hiredate;3、按照升序排序时，可以使用关键字ASC，但是通常不需要写，因为默认就是升序(从小到大)
SELECT name,sal,hiredate
FROM emp
ORDER BY hiredate ASC;4、按照工资从大到小排序(降序)，降序使用关键字DESC
SELECT name,sal
FROM emp
ORDER BY sal DESC;5、查看每个部门的工资排名
- ORDER BY可以按照多字段排序，排序优先级时先将结果集按照第一个字段的值排序，保证第一个字段排序顺序的前提下
- 将该字段值相同的记录中再按照第二个字段排序，依此类推。
SELECT name,dept_id,sal
FROM emp
ORDER BY dept_id,sal DESC
练习:
1. 查询有领导的员工信息,按照入职日期(hiredate) 升序排序
SELECT *
FROM emp
WHERE manager IS NOT NULL
ORDER BY hiredate;
2. 查询1号部门中名字中包含八的员工信息
SELECT *
FROM emp
WHERE dept_id=1 AND name LIKE '%八%';
# 3. 查询2号和3号部门中工资低于1500的员工信息
SELECT *
FROM emp
WHERE dept_id IN(2,3) AND sal<1500;SELECT *
FROM emp
WHERE (dept_id=2 OR dept_id=3) AND sal<1500;
# 4. 查询人事和程序员中工资高于2500的员工姓名,工资和工作
SELECT name,sal,job
FROM emp
WHERE job IN('人事','程序员') AND sal>2500;
# 5. 查询不是CEO的员工中工资高于2000的员工姓名,工资和工作,并且按照工资降序排序
SELECT name,sal,job
FROM emp
WHERE job<>'CEO' AND sal>2000
ORDER BY sal DESC
LIMIT：分页查询
- 将满足查询条件的数据分段分批的查询出来。这可以减少不必要的系统开销。
- 分页查询在SQL92标准没有定义，这意味着不同的数据库，分页查询方式完全不一样。
- 在ORDER BY中使用LIMIT来完成的
- LIMIT 跳过的记录数，请求的记录数(每页显示的记录数)
- LIMIT (页数-1)*每页显示的记录数，每页显示的记录数
1、按照工资降序后，每页显示3条，查看第二页
SELECT name,sal
FROM emp
ORDER BY sal LIMIT 3,3;2、按照工资降序后，每页显示3条，查看第三页
SELECT name,sal
FROM emp
ORDER BY sal LIMIT 6,3;3、按照工资降序后，每页显示4条，查看第3页
SELECT name,sal
FROM emp
ORDER BY sal LIMIT 8,4;
GROUP BY：分组
GROUP BY 也是为统计服务的，所以是搭配在聚合函数上使用的。
可以多字段分组：几个字段值都相同的记录，才会划分为一组。
1、查看每个部门的平均工资是多少?
SELECT sal,dept_idFROM emp
GROUP BY dept_id;
2、SELECT子句中不在聚合函数中的其他字段必须出现在GROUP BY子句中！
SELECT AVG(sal),dept_idFROM emp
GROUP BY dept_id;
3、每种职位的最高工资是多少?
SELECT MAX(sal),jobFROM emp
GROUP BY job
练习：
1.查询每个部门的最高工资
SELECT MAX(sal),dept_idFROM emp
GROUP BY dept_id
2.查询每个部门工资高于2000的人数
SELECT COUNT(*)
FROM emp
WHERE sal>2000
GROUP BY dept_id;
3.查询每种工作的最低工资
SELECT MIN(sal),jobFROM emp
ll/GROUP BY job;
4.查询1号部门和2号部门的人数
SELECT COUNT(*),dept_id
FROM emp
WHERE dept_id IN (1,2)
GROUP BY dept_id
5.查询平均工资最高的部门id和平均工资
SELECT AVG(sal),dept_id
FROM emp
GROUP BY dept_id
ORDER BY AVG(sal) DESC
LIMIT 0,1;#可以为函数或表达式字段取别名，然后利用别名排序。
SELECT AVG(sal) avg,dept_id
FROM emp
GROUP BY dept_id
ORDER BY avg DESC
LIMIT 0,1;
聚合函数不能写在WHERE子句中
原因是过滤时机并不相同：WHERE字句是，先过滤在得到结果集。
WHERE子句是添加过滤条件，在查询表中每条记录时，用于筛选记录。(查询表的过程中用于过滤的)
错误写法：
# 查看部门平均工资高于2000的那些部门的平均工资具体是多少?
SELECT AVG(sal),dept_id
FROM emp
WHERE AVG(sal)>2000
GROUP BY dept_id
想利用聚合函数的结果进行过滤时，应当已经是将表中数据查询出来(此时是WHERE过滤的时机)，并且对结果集进行了统计后，得到的结果集再进行过滤。
HAVING：对结果集进行了统计后，得到的结果集再进行过滤
HAVING子句是跟在GOURP BY子句之后，对分组统计的出的结果集再进行过滤的。和聚合函数一起使用。
1、查看部门平均工资高于2000的那些部门的平均工资具体是多少?
SELECT AVG(sal),dept_id
FROM emp
GROUP BY dept_id
HAVING AVG(sal)>2000;2、查看部门最低工资大于1000的部门的平均工资是多少?
SELECT AVG(sal),dept_id
FROM emp
GROUP BY dept_id
HAVING MIN(sal)>10003、查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400.
SELECT SUM(sal),dept_id
FROM emp
WHERE manager IS NOT NULL
GROUP BY dept_id
HAVING SUM(sal) > 5400
查询的字段可以使用表达式
1、查看每个员工的年薪是多少?
SELECT name,sal,sal*12 FROM emp;
# 查询时也可以用函数的结果作为字段
2、孙悟空的职位是销售 name+"的职位是"+job
SELECT CONCAT(name,'的职位是',job) FROM emp;3、查看每个员工的工资，奖金，工资+奖金
# 数字与NULL运算结果就为NULL
SELECT name,sal,comm,sal+comm FROM emp;
别名：可以为字段定义别名，也可以给表定义别名
为字段定义别名一般多用于:
1：隐藏实际表字段名
2：为计算表达式或函数的结果只作为字段时定义可读性更好的字段名
SELECT name ename,sal salary FROM emp;
# 支持的语法:
1、字段名 别名
SELECT name,sal*12 salary FROM emp;
2、字段名 as 别名
SELECT name as ename,sal*12 salary FROM emp;
3、字段名 as '别名'
SELECT name as 'ename',sal*12 salary FROM emp;
4、字段名 as "别名"
SELECT name as "ename",sal*12 salary FROM emp;
练习:
1. 查询员工表中3号部门工资高于1500的员工信息
SELECT *FROM emp
WHERE dept_id=3 AND sal>1500
2. 查询2号部门员工或者没有领导的员工信息
SELECT *FROM emp
WHERE dept_id=2 OR manager IS NULL
3. 查询有领导的员工姓名,工资按照工资降序排序
SELECT name,sal
FROM emp
WHERE manager IS NOT NULL
ORDER BY sal DESC
4. 查询2号和3号部门的员工姓名和入职日期hiredate按照入职日期降序排序
SELECT name,hiredate
FROM emp
WHERE dept_id IN (2,3)
ORDER BY hiredate DESC
5. 查询名字中包含僧和包含精的员工姓名
SELECT name
FROM emp
WHERE name LIKE '%僧%' OR name LIKE '%精%'6. 查询工资高于2000的工作有哪几种?
SELECT DISTINCT job
FROM emp
WHERE sal>2000
7. 查询工资升序第4页的2条数据
SELECT *
FROM emp
ORDER BY sal
LIMIT 6,2
数据库函数
NVL函数：用来替换NULL值
# NVL(arg1,arg2)
当arg1不为NULL时则函数返回arg1的值，如果arg1为NULL则返回arg2的值
SELECT name,sal,NVL(comm,0) FROM emp;
一、聚合函数：聚合函数(也称多函数)是用来多条记录统计为一条记录
- MIN()：求最小值
- MAX()：求最大值
- COUNT()：统计记录数
- AVG()：求平均值
- SUM()：求和
起别名，中文建议带单引号
SELECT MIN(sal) '最低工资' ,MAX(sal) '最高工资',AVG(sal) '平均工资',SUM(sal) '工资总和' FROM emp;
1、查看公司收入收入的员工工资是多少?
SELECT MIN(sal)FROM emp
#聚合函数忽略NULL值
在AVG中比较明显可以看出这一点，以下仅对4个有奖金的人取了个并均值，并非11个人的平均值
SELECT MIN(comm),MAX(comm),AVG(comm),SUM(comm) FROM emp;SELECT * FROM emp
修改为：SELECT AVG(IFNULL(comm,0))
SELECT * FROM emp
#将NULL值变为非空值0
练习:
1. 查询销售的平均工资
SELECT AVG(sal)
FROM emp
WHERE job='销售'
2. 查询程序员的最高工资
SELECT MAX(sal)
FROM emp
WHERE job='程序员'
3. 查询名字包含精的员工数量
SELECT COUNT(*)
FROM emp
WHERE name LIKE '%精%'
4. 查询和销售相关的工作一个月工资总和
SELECT SUM(sal)
FROM emp
WHERE job LIKE '%销售%'5. 查询2号部门的最高工资和最低工资起别名
SELECT MIN(sal) 最低工资,MAX(sal) 最高工资
FROM emp
WHERE dept_id=2
二、数学函数
-
abs(x) 返回x的绝对值
-
bin(x) 返回x的二进制（oct返回八进制，hex返回十六进制）
-
ceiling(x) 返回大于x的最小整数值
-
exp(x) 返回值e（自然对数的底）的x次方
-
floor(x) 返回小于x的最大整数值
-
greatest(x1,x2,...,xn)返回集合中最大的值
-
least(x1,x2,...,xn) 返回集合中最小的值
-
ln(x) 返回x的自然对数
-
log(x,y)返回x的以y为底的对数
-
mod(x,y) 返回x/y的模（余数）
-
pi()返回pi的值（圆周率）
-
rand()返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
-
round(x,y)返回参数x的四舍五入的有y位小数的值
-
sign(x) 返回代表数字x的符号的值
-
sqrt(x) 返回一个数的平方根
-
truncate(x,y) 返回数字x截短为y位小数的结果
三、字符串函数
-
ascii(char) 返回字符的ascii码值
-
bit_length(str) 返回字符串的比特长度
-
concat(s1,s2...,sn) 将s1,s2...,sn连接成字符串
-
concat_ws(sep,s1,s2...,sn) 将s1,s2...,sn连接成字符串，并用sep字符间隔
-
insert(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
-
find_in_set(str,list) 分析逗号分隔的list列表，如果发现str，返回str在list中的位置
-
lcase(str)或lower(str) 返回将字符串str中，所有字符改变为小写后的结果
-
left(str,x) 返回字符串str中最左边的x个字符
-
length(s) 返回字符串str中的字符数
-
ltrim(str) 从字符串str中切掉开头的空格
-
position(substr in str) 返回子串substr在字符串str中第一次出现的位置
-
quote(str) 用反斜杠转义str中的单引号
-
repeat(str,srchstr,rplcstr) 返回字符串str重复x次的结果
-
reverse(str) 返回颠倒字符串str的结果
-
right(str,x) 返回字符串str中最右边的x个字符
-
rtrim(str) 返回字符串str尾部的空格
-
strcmp(s1,s2) 比较字符串s1和s2
-
trim(str) 去除字符串首部和尾部的所有空格
-
ucase(str)或upper(str) 返回将字符串str中所有字符转变为大写后的结果
四、日期和时间函数
-
curdate()或current_date() ：返回当前的日期
-
curtime()或current_time() ：返回当前的时间
-
date_add(date,interval int keyword)：返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化)，如：selectdate_add(current_date,interval 6 month);
-
date_format(date,fmt) ：依照指定的fmt格式格式化日期date值
-
date_sub(date,interval int keyword) ：返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：selectdate_sub(current_date,interval 6 month);
-
dayofweek(date) ：返回date所代表的一星期中的第几天(1~7)
-
dayofmonth(date) ：返回date是一个月的第几天(1~31)
-
dayofyear(date) ：返回date是一年的第几天(1~366)
-
dayname(date) ：返回date的星期名，如：select dayname(current_date);
-
from_unixtime(ts,fmt) ：根据指定的fmt格式，格式化unix时间戳ts
-
hour(time) ：返回time的小时值(0~23)
-
minute(time) ：返回time的分钟值(0~59)
-
month(date) ：返回date的月份值(1~12)
-
monthname(date) ：返回date的月份名，如：select monthname(current_date);
-
now() ：返回当前的日期和时间
-
quarter(date) ：返回date在一年中的季度(1~4)，如select quarter(current_date);
-
week(date) ：返回日期date为一年中第几周(0~53)
-
year(date) ：返回日期date的年份(1000~9999)
五、加密函数
-
aes_encrypt(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用aes_encrypt的结果是一个二进制字符串，以blob类型存储
-
aes_decrypt(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
-
decode(str,key) 使用key作为密钥解密加密字符串str
-
encrypt(str,salt) 使用unixcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
-
encode(str,key) 使用key作为密钥加密字符串str，调用encode()的结果是一个二进制字符串，它以blob类型存储
-
md5() 计算字符串str的md5校验和
-
password(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和unix密码加密过程使用不同的算法。
-
sha() 计算字符串str的安全散列算法(sha)校验和
六、控制流函数
mysql有4个函数是用来进行条件操作的，这些函数可以实现sql的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。
mysql控制流函数：
-
case when [test1] then [result1]...else [default] end：如果testn是真，则返回resultn，否则返回default
-
case [test] when[val1] then [result]...else [default]end 如果test和valn相等，则返回resultn，否则返回default
-
if(test,t,f) 如果test是真，返回t；否则返回f
-
ifnull(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
-
nullif(arg1,arg2) 如果arg1=arg2返回null；否则返回arg1
这些函数的第一个是ifnull()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是null，函数就会向调用者返回第一个参数；如果是null,将返回第二个参数。
七、格式化函数
-
date_format(date,fmt) 依照字符串fmt格式化日期date值
-
format(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数
-
inet_aton(ip) 返回ip地址的数字表示
-
inet_ntoa(num) 返回数字所代表的ip地址
-
time_format(time,fmt) 依照字符串fmt格式化时间time值
八、类型转化函数
为了进行数据类型转化，mysql提供了cast()函数，它可以把一个值转化为指定的数据类型。类型有：binary,char,date,time,datetime,signed,unsigned
九、系统信息函数
-
database() 返回当前数据库名
-
benchmark(count,expr) 将表达式expr重复运行count次
-
connection_id() 返回当前客户的连接id
-
found_rows() 返回最后一个select查询进行检索的总行数
-
user()或system_user() 返回当前登陆用户名
-
version() 返回mysql服务器的版本
子查询：嵌套在其他SQL语句中的查询语句被称为叫做"子查询"
子查询通常用于要基于一个查询结果再进行操作的地方
1、公司的平均工资，子查询
SELECT AVG(sal) FROM emp;2、查看比公司平均工资高的那些员工的名字和工资是多少?
SELECT name,sal
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)3、查询工资高于2号部门平均工资的员工信息
SELECT name,sal
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp WHERE dept_id=2)4、查询比沙僧工资低的员工信息?
SELECT name,sal
FROM emp
WHERE sal<(SELECT sal FROM emp WHERE name='沙僧')5、查询和孙悟空同职位的员工信息?
SELECT *
FROM emp
WHERE job = (SELECT job FROM emp WHERE name='孙悟空')6、查询和公司最低工资员工同属一个部门的员工信息?
SELECT *
FROM emp
WHERE dept_id = (SELECT dept_id
FROM emp
WHERE sal=(SELECT MIN(sal) FROM emp))7、查询比2号和3号部门工资都高的员工名字和工资?
SELECT *
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE dept_id IN (2,3))8、查询比2号和3号部门工资任意一个员工工资高的员工信息?
SELECT *
FROM emp
WHERE sal>ANY(SELECT sal FROM emp WHERE dept_id IN (2,3))
子查询分类(按查询结果集分类)：
1.单行单列子查询：结果集只有1个值
单列子查询通常用于过滤条件中使用，单行单列可以配合>，>=，=，<，<=使用。
2.多行单列子查询：结果集有多个值
多行单列可以配合ANY，ALL，IN使用
ANY与ALL是与：>，>=，=，<，<=联合使用的
- >ALL(列表)：大于子查询结果集中最大的
- <ALL(列表)：小于子查询结果集中最小的
- >ANY(列表)：大于子查询结果集中最小的
- <ANY(列表)：小于子查询结果集中最大的
IN(列表)或NOT IN：在列表或不在列表中
3.多行多列子查询：结果集是一个表
多行多列子查询(结果集是一个表)，通常就当做一张表使用，可以跟在FROM字句中，或者跟在DDL语句中基于一个查询结果集创建表。
1、将1号部门员工信息单独定义一张表名为emp_dept1，AS后为子查询
CREATE TABLE emp_dept1
AS
SELECT * FROM emp WHERE dept_id=1;SELECT * FROM emp_dept1
#如果创建表基于的子查询中某个字段是一个表达式或函数时，要给该字段取别名，创建出来的表该字段会以别名作为字段名。
2、创建一张表emp_dept_sal，该表记录了每个部门的薪资情况，包含最高工资，最低工资，平均工资，工资总和，部门编号CREATE TABLE emp_dept_sal
AS
SELECT MAX(sal) max_sal,MIN(sal) min_sal,AVG(sal) avg_sal,SUM(sal) sum_sal,dept_id
FROM emp
GROUP BY dept_idSELECT * FROM emp_dept_sal
3、查看每个部门的最低薪水是多少?
SELECT min_sal FROM emp_dept_sal创建一张表emp_annual_salary，记录每个员工的名字，工资，年薪和部门，年薪字段用：a_salary，工资用：salary
CREATE TABLE emp_annual_salary
AS
SELECT name,sal salary,sal*12 a_salary,dept_id
FROM empSELECT * FROM emp_annual_salary
#4、名字里含"精"的员工年薪是多少?
SELECT name,a_salary
FROM emp_annual_salary
WHERE name LIKE '%精%'SELECT * FROM dept
关联查询
查询结果集中的数据来自多张表，而表与表中数据之间的对应关系就是关联关系
关联关系：两张表就可以产生关联关系了，关联关系分为三类:
1：一对一 A表中的1条记录只唯一对应B表中的1条记录
2：一对多 A表中的1条记录可以对应B表中的多条记录
3：多对多 A表与B表双向都是一对多时，就是多对多关系。
两张表关系为一对多时：关系为多的那张表应该有外键，对应关系为一的那张表的主键。
多对多关系在设计表时：需要一张关联关系表，并且通常不需要主键字段，一定有两个字段是两张多对多关系表主键字段所对应的外键字段。
关联查询：就是基于多张表联合查询数据而形成一个结果集的过程，在关联查询中一个至关重要的点就是关联条件。
关联条件原则：N张表关联查询至少要有N-1个连接条件。
笛卡尔积：缺失连接条件会产生笛卡尔积，该查询结果集的记录数是关联表中所有记录数乘积的结果，它通常是一个无意义的结果集，要尽量避免产生。
关联查询语法:
SELECT 字段
FROM 表A，表B[，表C，表D...]
WHERE 连接条件[AND 连接条件]
AND 过滤条件
注意：连接条件必须与过滤条件同时成立!!
笛卡尔积：产生了44条数据，将emp表每条记录都与dept表每条记录产生一条记录。
SELECT * FROM dept当表中出现了同名字段时，为了查询区分字段来自于哪张表，我们可以在字段名前用"表名."，来标识。
SELECT emp.name,emp.sal,emp.dept_id,dept.name,dept.loc
FROM emp,dept还可以为表取别名，用 "别名.字段名" 也可以标明查询的是那张表上的字段
SELECT e.name,e.sal,e.dept_id,d.name,d.loc
FROM emp e,dept d
实际关联查询要添加连接条件：连接条件最常见的就是等值连接。
查看每个员工的名字，工资，部门编号以及所在的部门名称和所在地区
SELECT e.name,e.sal,e.dept_id,d.name,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
注：emp表上的dept_id保存的值是dept表中主键字段的值，因此emp表中dept_id与dept表id值一样的记录才会被查询出来，作为一条记录显示在结果集中。
当一张表上的某个字段保存的是另一张表中的主键字段值时，这个字段就被称为"外键"
关联关系中经常用 A.主键=B.外键 作为连接条件。
练习：
查看在天庭工作的人都有谁?
SELECT e.name,e.sal,e.job,d.name,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
AND d.loc='天庭'名字里含“飞”的人来自哪里?
SELECT e.name,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
AND e.name LIKE '%飞%'天庭的最高工资?
SELECT MAX(e.sal),d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
AND d.loc='天庭'
GROUP BY d.loc查看每个地区的平均工资?
SELECT AVG(e.sal),d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
GROUP BY d.loc
内连接：JOIN..ON..
内连接与关联查询效果一致，区别是单独书写关联关系(关联条件与过滤条件分开)
JOIN..ON..子句可以写多个，当连接多张表时使用。
内连接JOIN子句格式：
SELECT 字段
FROM A表 a
JOIN B表 b
ON a.xx=b.xx(连接条件)
JOIN C表 c
ON c.xxx=b.xxx或c.xxx=a.xxx
JOIN .... ON ...
查看每个员工信息以及其对应的部分信息
SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
FROM emp e
JOIN dept d
ON e.dept_id = d.id;
在内连接中，过滤条件还是写在WHERE子句中
格式：
SELECT 字段
FROM 表1
JOIN 表2
ON 连接条件
WHERE 过滤条件
查看工资高于1300的员工信息和所在的部门信息
SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
FROM emp e
JOIN dept d
ON e.dept_id = d.id
WHERE e.sal>1300# 在关联查询中不满足连接条件的记录会被排除在外的
查看每个员工和部门信息
SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.idSELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
FROM emp e
JOIN dept d
ON e.dept_id = d.id
外连接：如果需要在结果集中列出不满足连接条件的记录时，需要使用外连接
外连接分为:
- 左外连接：以LEFT JOIN左侧表作为主表，其中的记录都要展示。不满足连接条件时，来自右侧表中记录的字段值全部为NULL。
- 右外连接：以RIGHT JOIN右侧表作为主表，其中的记录都要展示。不满足连接条件时，来自左侧表中记录的字段值全部为NULL。
- 全外连接：用UNION连接，结果集包含满足连接条件的左连接，右连接的所有数据
左连接：
SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
FROM emp e
LEFT JOIN dept d
ON e.dept_id = d.id右连接：
SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
FROM emp e
RIGHT JOIN dept d
ON e.dept_id = d.id全连接效果：UNION去重，UNION ALL不去重
SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
FROM emp e
LEFT JOIN dept d
ON e.dept_id = d.id
UNION
SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
FROM emp e
RIGHT JOIN dept d
ON e.dept_id = d.id
练习：
查看比本部门平均工资高的员工信息?
1：查看每个部门的平均工资，作为子查询
(SELECT AVG(sal) avg_sal,dept_idFROM emp
GROUP BY dept_id)
2：将子查询结果集当作表进行关联查询
SELECT e.name,e.sal,e.dept_id,a.avg_sal
FROM emp e,(SELECT AVG(sal) avg_sal,dept_id FROM emp GROUP BY dept_id) a
WHERE e.dept_id=a.dept_id
AND e.sal>a.avg_sal
练习：
查看比所在地区平均工资高的员工?
1:查看每个员工的工资及其所在的地区
SELECT e.sal,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id2:每个地区的平均工资(按照loc字段分组)
SELECT AVG(e.sal) avg_sal,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
GROUP BY d.loc3:关联三张表查询:员工表-部门表-(第2步子查询的结果集当成的表)
SELECT e.name,e.sal,a.avg_sal,d.loc
FROM emp e,dept d,(SELECT AVG(e.sal) avg_sal,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
GROUP BY d.loc) a
WHERE e.dept_id = d.id
AND d.loc = a.loc
AND e.sal>a.avg_sal
内连接写法：
SELECT e.name,e.sal,a.avg_sal,d.loc
FROM emp e
JOIN dept d
ON e.dept_id = d.id
JOIN (SELECT AVG(e.sal) avg_sal,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
GROUP BY d.loc) a
ON d.loc = a.loc
WHERE
e.sal>a.avg_sal# 查看每个地区的最高工资是谁?
SELECT e.name,e.sal,a.max_sal,d.loc
FROM emp e
JOIN dept d
ON e.dept_id = d.id
JOIN (SELECT MAX(e.sal) max_sal,d.loc
FROM emp e,dept d
WHERE e.dept_id=d.id
GROUP BY d.loc) a
ON d.loc = a.loc
WHERE e.sal=a.max_sal
自连接
该表中的一条记录可以对应该表中的其他多条记录时，就是自连接的关联关系。
自连接的设计是为了保存同样一组属性的数据之间存在上下级关系时(树状结构数据)
公司的员工组织结构，对于公司而言大家都属于员工，但是员工之间又存在上下级关系。
电商中常见的分类树。
自连接特点：即有主键，也有外键，且外键字段储存的是该表主键字段的值。
查看每个员工和他的领导是谁?
SELECT e.name,m.name
FROM emp e,emp m
WHERE e.manager=m.id内连接写法:
SELECT e.name,m.name
FROM emp e
JOIN emp m
ON e.manager=m.id刘备的手下都有谁?
SELECT e.name
FROM emp e
JOIN emp m
ON e.manager=m.id
WHERE m.name='刘备'谁的工资高于孙悟空的领导工资?
1：孙悟空的领导工资?
SELECT m.name,m.sal
FROM emp e
JOIN emp m
ON e.manager=m.id
WHERE e.name='孙悟空'2：查看谁的工资高于唐僧的?
SELECT name,sal
FROM emp
WHERE sal > (SELECT m.sal
FROM emp e
JOIN emp m
ON e.manager=m.id
WHERE e.name='孙悟空')CREATE TABLE userinfo(
id INT primary key auto_increment,
username VARCHAR(30) NOT NULL ,
password VARCHAR(30),
age INT(3),
salary DOUBLE(7,2)
)SELECT id,name,age,class_id
FROM student
WHERE age=6
练习：
1、新建数据库bbsdb
CREATE DATABASE bbsdb;
use bbsdb;2、创建表userinfo，字段(id,username,password,age)
CREATE TABLE userinfo(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30),
password VARCHAR(30),
age INT(3)
);3、创建表article，字段(id,title(300),content(3000),u_id)
CREATE TABLE article(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(300),
content VARCHAR(3000),
u_id INT
);4、修改userinfo表字段，在字段password后面添加字段nickname，字段类型为VARCHAR
ALTER TABLE userinfo
ADD nickname VARCHAR(30) AFTER password;5、显示所有表
SHOW TABLES
6、使用数据库bbsdb
use bbsdb
7、查看username为小花的全部信息
SELECT * FROM userinfo WHERE username='小花'
1232
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
