SQL学习-----查询

SQL学习

索引从1开始

基础命令

1.开和停用服务器

net start *
net stop *

2.登录和退出

退出exit

登录 mysql {-h 主机 -P 端口} -u 用户名 -p密码

如果是主机端可去掉-h,如果端口为3306可去掉-P

DQL语言

基础

1.当在使用查询命令时需要使用USE确定当前使用的库

USE myemployees;

2.查询命令主要使用select形式是

select * from ****

*可以是字段，常量，表达式，函数，可以为多

****查询结果为虚拟表

3.修饰符有去重复distinct和取名as

SELECT DISTINCT last_name AS 名字 FROM empty;

4.使用函数连接CONCAT(str1,str2...)和查看为非重复ISNULL(*,empty)*代表为指向元素，empty代表返回值

SELECT CONCAT(last_name," ",first_name) FROM empty;
SELECT ISNULL(empt) FROM empty;

5.取名也可以直接使用空格代替as

如:SELECT last_name as 姓名 FROM empty和SELECT last_name 姓名 FROM empty等价

SELECT `last_name`+`first_name` AS 姓名 FROM employees;
SELECT `last_name`+`first_name` 姓名 FROM employees;

6.+号只能做加法运算，如果不是数值常量，直接转化为数值，若不能转化，转为0在进行加。

条件查询[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mSmNWxWy-1637811491136)(C:\Users\99479\Desktop\屏幕截图 2021-11-24 133201.png)]

语法

select 查询列表
from 表名
where 条件

简单条件运算符

有<,>,!=(<>),>=,<=,=

只能进行数值的判断，不能判断null

<=>可以对null进行判断也可以对普通数值进行判断

逻辑运算符

有&&，||，!,and,or,nor

模糊查询

有like，between…and…,in,is null/is not null

like可以使用通配符

%代表有n个任意字符，n为0

_代表有一个任意字符，

#like
SELECT `last_name` 
FROM employees WHERE last_name LIKE '%a%';
#between and
SELECT
	*
FROM
	employees
WHERE 
	employee_id BETWEEN 100 AND 120;
#in
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN('AD_PRES','AD_VP');
#is not null
SELECT
	last_name,
	`commission_pct`
FROM
	employees
WHERE
	`commission_pct` IS NOT NULL;

排序查询

SELECT *
FROM *
{WHERE *}
OEDER BY *;#可以使用多个进行也可以为函数，表达式，别名

常见函数

SELECT 函数名()
{FROM 表}

单行函数

有返回值

字符函数

LENGTH()#返回字节个数
CONCAT()#连接字符串
UPPER()
LOWER()#变为大小写
SUBSTR()#截取字符串
INSTR()#查找并返回字串出现的第一个索引，无为0
TRIM()#去除空格，前后
SELECT TRIM('   a  ')#显示a
SELECT TRIM('a' FROM 'aaaalafaaaa') AS a;#显示laf
LPAD()#左填充
SELECT LPAD("xin",5,'*') AS a;
#显示**xin，如果中间小于字符串大小，截取前位置
SELECT LPAD("xin",2,'*') AS a;
#显示xi
RPAD()#右填充
REPLACE() #替换
SELECT REPLACE("aacd","a","c");
#显示cccd

数学函数

ROUND()#四舍五入
SELECT ROUND(-1.55);#-2
SELECT ROUND(-1.55,1);#-1.6
CEIL()#向上取整
FLOOR()#向下取整
TRUNCATE()#截断
SELECT TRUNCATE(-1.55,1);#-1.5
MOD()#取余%(a - a / b * b)

日期函数

NOW()#当前日期 + 时间，详细
CURDATE()#系统日期
CURTIME()#时间
#年，月，日，小时，分，秒，提取一段内容
YEAR() MONTH() MONTHNAME() DAY() HOUR() MINUTE()
SECOND()#对应单词
STR_TO_DATA()#将字符通过指定格式转化为日期
SELECT STR_TO_DATE('1999-3-2','%Y-%c-%d') AS yea;
#显示1999-03-02
DATE_FORMAT()#将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')；

其他函数

VERSION()#版本
DATABASE()#显示库名
USER()#显示当前用户

流程控制函数

IF()#相当于if else
SELECT IF(10>5,'big','small');#big
CASE
#案例一，用法一
SELECT 	
	salary 工资,department_id, 
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新
FROM employees;
#案例二，用法二
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
ELSE 'C'
END AS xin
FROM employees;

分组函数（统计函数）

进行统计等操作

#运算忽视null值
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
#可以加上DISTINCT去重
SELECT SUM(DISTINCT salary) FROM employees;
#行数
SELECT COUNT(*) FROM employees;
SELECT COUNT(1)#可加入任意常量
FROM employees;

分组查询

#查询格式
SELECT *
FROM *
{WHERE *}
GROUP BY *
{ORDER BY *}
{HAVING *}
#添加分组后筛选，使用HAVING关键字
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

连接查询

查询字段使用多个表时使用

按年代分为SQ192标准（仅支持内连接）和SQ199标准（推荐）支持内连接，外连接（左外，右外），交叉

99语法

SELECT *
FROM *
JOIN * {连接方式}  
ON * 
{WHERE *}
{GROUP BY *}
{HAVING *}
{ORDER BY *}

99连接方式：

内：inner

外：左外：left [outer]

右外：right [outer]

全外：full [outer]

交叉：cross

内连接

等值连接

#可以为表取别名方便调用，但是取别名后无法使用原名 92
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#99
SELECT last_name,department_name
FROM departments d
INNER JOIN employees e
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;

非等值连接

#92
SELECT grade_level,salary
FROM employees e,job_grades g
WHERE salary 
BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';
#99

自连接

#92
SELECT e.last_name yuan,b.last_name ling
FROM employees e,employees b
WHERE e.`manager_id` = b.`employee_id`;
#99

外连接

查询结果是内连接结果加主表中有的表而从表中没有的表

左外连接

 SELECT b.name,bo.*
 FROM beauty b
 LEFT OUTER JOIN boys bo
 ON b.`boyfriend_id`= bo.`id`
 WHERE bo.`id` IS NULL;

右外连接

SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`= bo.`id`
WHERE bo.`id` IS NULL;

全外连接

结果：内连接+表1中有表二中没有+表二中有但是表一中没有的

交叉连接

 #一个迪卡尔乘机
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

子查询

#出现位置
select #仅支持标量子查询
from #支持表子查询
where
having#标量子查询（重点），列子查询（重点），行子查询
exists#（相关子查询）表子查询
#按照结果
/*
标量子查询(结果集只能一行一列)
列子查询(结果集只能一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般多行多列)
特点：
1.子查询放在小括号
2.子查询一般放在条件的右侧
3.标量子查询一般搭配着单行操作符使用
列子查询，一般搭配着多行操作符使用
*/
##标量子查询
#select
SELECT  d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id` 
)
FROM departments d;
#where
SELECT last_name 姓名,job_id,salary
FROM employees 
WHERE job_id=(
	SELECT job_id
	FROM employees e
	WHERE e.`employee_id`=141
)
AND salary>(
	SELECT salary
	FROM employees e
	WHERE e.`employee_id`=143
)
#having
SELECT MIN(salary),department_id
FROM employees e
GROUP BY e.`department_id`
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees 
	WHERE department_id=50
);
##列子查询
#where
SELECT last_name
FROM employees
WHERE department_id IN
(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
##行子查询
#where，要求条件较高
SELECT *
 FROM employees
 WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
 );
 ##表子查询
 #from
 SELECT ag_dep.*,g.grade_level
FROM (
	SELECT AVG(salary) ag,department_id 
	FROM employees
	GROUP BY department_id
) ag_dep#别名必须取
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
#相关子查询exists
#结果只有1或0
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

分页查询

#语法
select *
from *
{join type
join *
on *
where *
group by *
having *
order by *}
limit offset,size;
#offset:要显示条目的起始索引，从0开始可以省略
#size：要显示条目个数
SELECT *
FROM employees
LIMIT 10,15;

联合查询

#多个查询结合一个
/*
查询可以多个表同时查询
查询的结果必须一致
自动去掉重复项
*/
#实例
SELECT * 
FROM employees
WHERE email 
LIKE '%a%'
UNION##关键字后加ALL取消去重
SELECT *
FROM employees
WHERE department_id>90;