sql教程-DQL

DQL

Data Query Language

基础查询

select 查询列表 from 表名
查询列表可以是表中字段，常量值，表达式，函数

USE myemployees;   #表示在指定库操作
#1.查询单个字段
SELECT last_name FROM employees;

#2.查询多个字段
SELECT 
		last_name,
		salary,
		email 
FROM 
		employees;

#3.查询所有字段
SELECT * FROM employees;


#4.查询常量值
SELECT 100;
SELECT 'john';
SELECT 100%98;
SELECT VERSION();    #得到返回值

#5.起别名--后续连接操作，如果查询的字段有重名的情况，使用别名可以区分开来
#方式一
SELECT 100%98 as 结果;
SELECT last_name as 姓,first_name as 名 FROM  employees;
#方式二
SELECT last_name 姓,first_name 名 FROM  employees;
#PS：若别名有空格等，加上""或''表示一个整体
SELECT last_name as “out put” FROM  employees;

#6.去重-DISTINCT(不能写多个字段)
SELECT DISTINCT employee_id FROM employees;

#7.+作用：不能作字符串拼接，只能当运算符。其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算。失败则当成0继续运算。如果一方为null。则运算结果为Null

SELECT '123'+60;

#null遇到任何都为null。可以利用IFNULL(字段,0);将指定列的Null改为0
 
 #8.拼接字段
 SELECT CONCAT(last_name,first_name) as 姓名 FROM employees;
 
#9.显示表中结构
DESC departments;

条件查询

	SELECT 查询列表 FROM 表名 where 筛选条件
	常见符号：> < = <>(!+=) >= <= like "between and" in "is null"
					$$ || ! and or not
	
	# 按条件表达式筛选
	SELECT * FROM employees WHERE salary>12000; 
	
	#按逻辑表达式筛选
	SELECT * FROM employees WHERE salary>12000 AND salary <20000；

	#模糊查询
	#like经常与通配符使用。%表示多个字符（包含0个），_表示单个字符。也可用于数值型
	SELECT * FROM employees WHERE last_name LIKE '%a%';       #名中包含a
	#转移用\或者指定转移符号
	SELECT * FROM employees WHERE last_name LIKE '_\_a';
	SELECT * FROM employees WHERE last_name LIKE '_\$_a' ESCAPE '$';
	
	#BETWEEN AND  包含临界值。
	SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;
	不在：	SELECT * FROM employees WHERE salary NOT BETWEEN 10000 AND 20000;

	# in：不支持通配符
	SELECT last_name,job_id FROM employees WHERE job_id IN('AD_VP','IT_PROG');

	# is null：不能用=判断null值
	SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
	SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
	
	#安全等于：<=>：可以判断null，也可以当普通等于使用
	SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;

排序查询

SELECT 查询列表 FROM 表名 where 筛选条件 order by 排序列表 [asc|desc]
order by子句一般是放在查询语句最后，limit子句除外

# 按某列排序  asc升序，desc降序。不写默认升序
SELECT * FROM employees ORDER BY salary DESC;

#按计算结果排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) as 年薪 
FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#按别名排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) as 年薪 
FROM employees ORDER BY 年薪 DESC;

#带有函数
SELECT last_name,salary FROM employees ORDER BY LENGTH(last_name);

# 多关键词排序
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

常见函数

SELECT 函数名(实参列表) FROM 
分类：
	单行函数：concat,length等，给一个值返回一个值
	分组（统计）函数：给一组值，返回一个值，又称聚会函数

#1、字符函数
SELECT LENGTH(‘数据库yyds’);        #一个汉字占三个字符(utf-8)，jdk占两个字节
SELECT concat(last_name,'_',first_name) 姓名 FROM employees;
SELECT UPPER('jdk');  SELECT LOWER('JDK');
SELECT SUBSTR(‘I LOVE YOU’,1,3);                  #截取字符串。注意数据库的索引是从1开始的
SELECT INSTR('I LOVE YOU','YOU') AS output  #寻找第一次出现的起始索引，找不着返回0
SELECT TRIM(‘       LO  VE      ’)  ；                     #去空格，中间的不去
SELECT TRIM('a' FROM ‘aaaLOVEaaa’)  ；                     #去指定字符
SELECT LPAD ('数据库' ，10,'*');                    #左填充指定字符。右填充一个道理
SELECT REPLACE('I LOVE YOU','YOU','HIM');  #改指定字符串

#2、数学函数
SELECT ROUND(1.678,位数);   #四舍五入指定位数，默认到整数
SELECT CEIL(1.8);                 #向上取整
SELECT FLOOR(1.8);            #向下取整
SELECT TRUNCATE(1.65,位数);  #截断到第几位
SELECT MOD(10,3);              #取模

#3、日期函数
SELECT NOW();        #返回当前日期和时间
SELECT CURDATE(); #返回日期，不包含时间
SELECT CURTIME();#返回时间，不包含日期
SELECT YEAR(NOW()); #返回指定日期的年
SELECT MONTH(NOW()); #返回指定日期的月
……
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');#将日期格式字符转换成指定格式日期
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日')； #将日期转化为字符串

#4、其他函数
SELECT VERSION();  #查看版本
SELECT DATABASE();  #查看当前数据库
SELECT USER();   #查看当前用户
SELECT PASSWORD(str); #给字符串加密形式

#5、流程控制函数
	if函数：SELECT IF(10>5,'大','小')；  #三元运算
	case函数：
		使用1：swithc case效果：
		case 要判断的字段和表达式
		when 常量1 then 要显示的语句或值
		when……
		else  要显示语句和值
		end
		
		使用2：多重if
		case
		when 条件1 then 要显示的值或表达式
		when 条件2 then ……
		……
		else then 要显示的值或表达式
		end

分组函数：sum求和 avg平均值 max最大值 min最小值 count计算个数
SELECT sum(salary) FROM  employees;
SELECT avg(salary) FROM  employees;
SELECT max(salary) FROM  employees;
SELECT min(salary) FROM  employees;
SELECT count(salary) FROM  employees;         #非空的值有几个
SELECT count(*) FROM  employees;         #统计表中函数
也可这么些：SELECT count(1) FROM  employees;         

分组查询

SELECT 分组函数，列(要求出现在group by 后面) FROM 表名 where 筛选条件 group by 条件 order by 排序列表 [asc|desc]
#明确where一定要放在from后面

#分组前的筛选
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;  #每个工种的最高工资

#分组后的筛选
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

总结：分组前筛选是原始表，用where(GROUP BY子句前面)；分组后筛选是分组后的结果集，需要用having（GROUP BY子句后面）

#按表达式或函数分组
SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;

#按多个字段分组
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;

#排序
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) ASC ;

连接查询（多表查询）

sql92

#笛卡尔乘积现象（没有有效连接条件，因此N=n*m）
SELECT `name`,boyName FROM beauty,boys;

#限定
SELECT `name`,boyName FROM beauty,boys WHERE beauty.boyfriend_id = boys.id;

#分类：
	内连接：等值连接，非等值连接，自连接
	外连接：左外连接，右外连接，全外连接
	交叉连接

#因为有多表，因此尽量为表取别名以示区分

sql92连接语法：用where连接筛选条件和连接条件（注意下面sql99实现分离）
#1、等值连接
SELECT `name`,boyName FROM beauty,boys WHERE beauty.boyfriend_id = boys.id;
#当表名很长时，可以为表起别名。但如果为表起了别名，则查询的字段就不能使用原来的表名去限定
SELECT `name`,boyName FROM beauty be,boys b WHERE be.boyfriend_id = b.id;
#加限定：
SELECT `name`,boyName FROM beauty be,boys b WHERE be.boyfriend_id = b.id AND b.boyName IS NOT NULL.;

#2、非等值连接--利用其他运算符求解
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

#3、自连接--相同的表进行连接
use myemployees;
SELECT e.employee_id,e.last_name,e.manager_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;

sql99

#sql99连接语法
SELECT 查询列表
FROM 表1 别名 【连接条件】
join 表2 别名
on 连接条件
where 筛选条件
GROUP BY 分组
HAVING 筛选条件
ORDER BY 排序列表
内连接：inner
外连接：
		左外：left 【outer】
		右外：right 【outer】
		全外：full 【ouder】
交叉连接：cross

#1、内连接--等值，非等值，内连接。inner可以省略
SELECT 查询列表 FROM 表1 别名  INNER join 表2 别名 on 连接条件
#两表连接
SELECT last_name,department_name 
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
#多表连接
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

#2、外连接：没有匹配用null替代
#左外
SELECT b.`name`,bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id = bo.id;
#全外
SELECT b.*,bo.*
FROM beauty b
FULL JOIN boys bo
ON b.boyfriend_id = bo.id;

PS:更老的版本中，外连接这么写的
Select Student.Sno, Sname, Cno, Grade                               
From Student, SC                                                                                
Where Student.Sno=SC.Sno(*)
这里表示左外连接，即student没有与之匹配的SC都要（设为null）

#3、交叉连接--本质上是笛卡尔乘积，sql92语法用的是','
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

子查询

出现在其他语句中的SELECT语句，称为子查询或内查询。外部的查询语句称为主查询
分类：
按主查询出现顺序：
	SELECT后面：仅支持标量子查询
	FROM后面：支持表子查询
	WHERE或HAVING后面：标量子查询，列子查询，行子查询（重点）
	exists后面（相关子查询）：表子查询
按结果集的行列数不同：
	标量子查询/单行子查询（结果集只有一行一列）
		一般搭配单行操作符使用：> < >= <= = <>
	列子查询/多行子查询（结果集有一列多行）
		一般搭配多行操作符使用：IN，ANY/SOME，ALL（解释如下图）
	行子查询（结果集有一行多列）
	表子查询（结果集为多行多列）

#1、WHERE或HAVING后面
	#标量子查询：查询谁的工资比 Abel高
	SELECT *
	FROM employees
	WHERE salary > (
			SELECT salary
			FROM employees
			WHERE last_name = 'Abel'
	);
	#列子查询：返回location_id是1400或1700的部门中所有员工姓名
	SELECT last_name
	FROM employees
	WHERE department_id IN(
			SELECT DISTINCT department_id
			FROM departments
			WHERE location_id IN(1400,1700)
	)
	#行子查询：查询员工编号最小并且工资最高的员工信息
	SELECT *
	FROM employees
	WHERE employee_id=(
		SELECT MIN(employee_id)
		FROM employees 
	)
	AND salary=(
		SELECT MAX(salary)
		FROM employees
	)
	上述做法用行子查询：
	SELECT *
	FROM employees
	WHERE (employee_id,salary)=(
		SELECT MIN(employee_id),MAX(salary)
		FROM employees
	);
#2、SELECT后面
	#查询每个部门的员工个数
	SELECT d.*,(
		SELECT COUNT(*)
		FROM employees e
		WHERE e.department_id = d.department_id
	)
	FROM departments d;

#3.FROM后面
	#查询每个部分的平均工资的工资等级
	SELECT a.*,g.grade_level 
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) AS a
	INNER JOIN job_grades g
	ON a.ag BETWEEN lowest_sal AND highest_sal;

#4、exists后面（相关子查询）
	#查询有员工的部门名
	SELECT department_name
	FROM departments d
	WHERE EXISTS(
		SELECT * 
		FROM employees e
		WHERE d.department_id=e.department_id
	);

分页查询

应用场景：当要显示的数据一页显示不全，需要分页提交sql请求
语法：
		SELECT 查询列表
		FROM 表
	   【join type】 join 表2
	    on 连接条件
		where 筛选条件
		GROUP BY 分组
		HAVING 筛选条件
		ORDER BY 排序列表 
		limit offset,size;
		offset是要显示条目的起始索引（从0开始，若是0可以省略）；sizes是要显示的条目个数

		#有奖金的员工信息，并且工资较高的前10名显示出来
		SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;

联合查询

	union 联合，合并：将多条查询语句的结果合并成一个结果
	语法：
	查询语句1
	union
	查询语句2
	union
	……
	注意这样写若是多条查询语句中有重复结果会自动去重，若是不想去重需要写union all
	#查询部门编号>90或邮箱包含a的员工信息，可以写成
	SELECT * FROM employees WHERE email LIKE ' %a%' OR department id>90; ;
	利用union
	SELECT * FROM employees WHERE email LIKE '%a%'
	UNION
	SELECT * FROM employees WHERE department_ id>90;