我的mysql笔记

最新推荐文章于 2022-03-30 23:12:28 发布

阿兰你在哪

最新推荐文章于 2022-03-30 23:12:28 发布

阅读量370

点赞数 2

分类专栏：笔记

本文链接：https://blog.csdn.net/qq_40945081/article/details/111993568

版权

MYSQL笔记

一、基础查询

语法：select 查询列表 from 表名;

查询列表可以是：字段、表达式、常量、函数等。

特点：

1、查询的结果集是一个虚拟表

2、select 查询列表类似于System.out.println(打印内容);

执行顺序：

① from子句

② select子句

1、查询常量

SELECT 100 ;

2、查询表达式

SELECT 100%3;

在使用java操作的时候，可以直接查询计算过后的数值。

3、查询单个字段

SELECT `last_name` FROM `employees`;

4、查询多个字段

SELECT `last_name`,`email`,`employee_id` FROM employees;

5、查询所有字段

SELECT * FROM `employees`;
SELECT 
    `last_name`,
    `first_name`,
    `last_name`,
    `commission_pct`,
    `hiredate`,
    `salary` 
FROM
    employees ;

推荐使用第二个。

但是字段顺序是固定的，不推荐使用，最好将各个字段都写出来，还可以自己排序。

再sqlyog中，可以使用F12对齐格式

6、查询函数（调用函数，获取返回值）

SELECT DATABASE();
SELECT VERSION();
SELECT USER();

7、起别名

方式一：使用as关键字

SELECT USER() AS 用户名;
SELECT USER() AS "用户名";
SELECT USER() AS '用户名';

SELECT last_name AS "姓 名" FROM employees;

方式二、使用空格

SELECT USER()   用户名;
SELECT USER()   "用户名";
SELECT USER()   '用户名';

SELECT last_name   "姓 名" FROM employees;

8、+的作用

需求：查询first_name 和last_name 拼接成的全名，最终起别名为：姓名

+的作用：

两个数值型就是加法操作。 100+1.5===>101.5
一个操作数为字符型，若无法转为数值型当零处理。‘张无忌’+100===>100
其中一个操作数为null。 null+100===>null

方案一：使用+

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

方案二：使用concat拼接函数

SELECT CONCAT(first_name,last_name) AS '姓 名'
FROM employees;

9、distinct的使用

需求：查询员工涉及到的部门编号有哪些？

SELECT DISTINCT department_id FROM employees;

10、查看表的结构

DESC employees;
SHOW COLUMNS FROM employees;

二、条件查询

语法：

select 查询列表

from 表名

where 筛选条件

执行顺序：

① from子句

② where子句

③ select子句

例子：

SELECT last_name,first_name FROM employees WHERE salary>2000

特点：

1、按关系表达式筛选

关系运算符：>、<、 >=、<=、=、<>

补充：也可以使用!=，但不建议

2、按逻辑表达式筛选

逻辑运算符：and、or、not

补充：也可以使用&& || ! ，但不建议

3、模糊查询

关键字：like、in、between and、is null

一、按照关系表达式筛选

案例一、查询部门编号不是100的员工信息

SELECT * FROM employees WHERE department_id <>100;

案例二、查询工资<15000的姓名、工资

SELECT last_name,salary FROM employees WHERE salary<15000;

二、按逻辑表达式筛选

案例一、插查询部门编号不是 50-100之间员工姓名、部门编号、邮箱

方式一：

SELECT last_name,department_id,email FROM employees
WHERE department_id<50 OR department_id>100

方式二：

SELECT last_name,department_id,email FROM employees
WHERE NOT(department_id>50 OR department_id<100)

案例二、查询奖金率>0.03 或者员工编号在60-110之间的员工信息

SELECT * FROM employees 
WhERE commission_pct>0.03 OR (employee_id>=60 AND employee_id<=110)

三、模糊查询

1、like

功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询

常见的通配符：

_：任意单个字符

%:任意多个字符

like/not like

案例一、查询姓名中包含字符a的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '%a%';

案例二、查询姓名中包含最后一个字符为e的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '%e';

案例三、查询姓名中包含第一个字符为e的员工信息

SELECT *
FROM employees
WHERE last_name LIKE 'e%';

案例四、查询姓名中包含第三个字符为x的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '__x%';

案例五、查询姓名中包含第二个字符为_的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '_\_%';
#\_ 转义字符 

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';

2、in

功能：查询某字段的值是否属于指定的列表之内

用法：

a in(常量值1,常量值2,常量值3,…)

a not in(常量值1,常量值2,常量值3,…)

案例一、查询部门编号是30/50/90的员工名、部门编号

# 方式一
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

# 方式二
SELECT last_name,department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;

案例二、查询工种编号不是SH_CLERK或IT_PROG的员工信息

# 方式一
SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');

#方式2：
SELECT *
FROM employees
WHERE NOT(job_id ='SH_CLERK'
OR job_id = 'IT_PROG');

3、between and

功能：判断某个字段的值是否介于xx之间

用法：between and/not between and

案例一、查询部门编号式30-90之间的部门编号、员工姓名

# 方式一
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

# 方式二
SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;

案例二、查询年薪不是100000-200000之间的员工姓名、工资、年薪

# 方式一
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))<100000 OR salary*12*(1+IFNULL(commission_pct,0))>200000;

#方式二
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

4、is null/is not null

案例一、查询没有奖金的员工信息

SELECT *
FROM employees
WHERE commission_pct IS NULL;

案例二、查询有奖金的员工信息

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

对比=、IS、<=>

= 只能判断普通的内容

IS 只能判断NULL值

<=> 安全等于，既能判断普通内容，又能判断NULL值

SELECT *
FROM employees
WHERE salary <=> 10000;

SELECT *
FROM employees
WHERE commission_pct <=> NULL;

三、排序查询

语法：

SELECT 查询列表

FROM 表名

【WHERE 筛选条件】

ORDER BY 排序列表

执行顺序：

①from子句

②WHERE 子句

③SELECT 子句

④ORDER BY 子句

特点：

1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合

2、升序 asc 降序 desc

一、按照单个字段排序

案例一、将员工编号>120的员工信息进行工资的升序

SELECT * 
FROM employees 
WHERE employee_id>120
ORDER BY salary;

SELECT * 
FROM employees 
WHERE employee_id>120
ORDER BY salary DESC;

二、按表达式排序

案例一、对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

三、按别名排序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪
DESC;

四、按照函数的结果排序

案例一、按姓名的字数长度进行排序

SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

五、按多个字段排序

案例一、查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序

SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;

六、按照列数排序

SELECT * FROM employees 
ORDER BY 2 DESC;
# 按照表的第二列的降序排列

四、常见函数

对外仅暴露方法名，供外部调用

1、自定义方法（函数）

2、调用方法（函数）

叫什么：函数名

干什么：函数功能

单行函数

	字符函数
	concat
	substr
	length（str）
	char_length
	upper
	lower
	trim
	left
	right
	lpad
	rpad
	instr
	strcmp
数学函数
	abs
	ceil
	floor
	round
	truncate
	mod
	
日期函数
	now 
	curtime
	curdate
	datediff
	date_format
	str_to_date
	
流程控制函数
	if
	case

一、字符函数

1、CONCAT拼接字符

SELECT CONCAT('hello,',first_name,last_name)  备注 FROM employees;

2、LENGTH 获取字节长度

SELECT LENGTH('hello,郭襄');

3、CHAR_LENGTH 获取字符个数

SELECT CHAR_LENGTH('hello,郭襄');

4、SUBSTRING 截取子串

注意：起始索引从1开始！

substr(str,起始索引，截取的字符长度)
substr(str,起始索引)

SELECT SUBSTR('张三丰爱上了郭襄',1,3);
SELECT SUBSTR('张三丰爱上了郭襄',7);

5、INSTR获取字符第一次出现的索引

返回的索引也是从1开始的。

SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');

6、TRIM去前后指定的字符，默认是去空格

去除前后的x。

SELECT TRIM(' 虚  竹    ')  AS a;
SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx')  AS a;

7、LPAD/RPAD 左填充/右填充

LAPD(字符串，长度，填充的串) 左填充

RPAD(字符串，长度，填充的串)右填充

SELECT LPAD('木婉清',10,'a');
SELECT RPAD('木婉清',10,'a');

8、UPPER/LOWER 变大写/变小写

案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”

SELECT CONCAT(UPPER(SUBSTR(first_name,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) 'OUTPUT'
FROM employees;

9、STRCMP 比较两个字符大小

相等为0，前面大于后面为1，前面小于后面为-1

SELECT STRCMP('aec','aec');

10、LEFT/RIGHT 截取字串

SELECT LEFT('鸠摩智',1); 结果为：鸠
SELECT RIGHT('鸠摩智',1); 结果为：智

二、数学函数

1、ABS绝对值

SELECT ABS(-2.4)

2、CEIL向上取整返回>=该参数的最小整数

SELECT CEIL(-1.09);
SELECT CEIL(0.09);
SELECT CEIL(1.00);

3、FLOOR 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);
SELECT FLOOR(1.00);

4、四舍五入

SELECT ROUND(1.8712345);
SELECT ROUND(1.8712345,2);

5、TRUNCATE 截断

SELECT TRUNCATE(1.8712345,1);结果为：1.8 截取一位小数

6、MOD取余

SELECT MOD(-10,3)
a%b = a-(INT)a/b*b
SELECT 10%3;

三、日期函数

1、NOW

SELECT NOW(); 结果为：2020-12-09 14:27:53

2、CURDATE

SELECT CURDATE(); 结果为：2020-12-09

3、CURTIME

SELECT CURTIME(); 结果为：14:29:31

4、DATEDIFF

DATEDIFF() 函数返回两个日期之间的时间。

SELECT DATEDIFF('1998-7-16','2019-7-13');

5、DATE_FORMAT

SELECT DATE_FORMAT('1998-7-16','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期;
SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')入职日期 
FROM employees;

6、STR_TO_DATE 按指定格式解析字符串为日期类型

SELECT STR_TO_DATE('3/15 1998','%m/%d %Y'); 
结果为：1998-03-15

四、流程控制函数

1、IF函数

类似于java中的三元表达式

SELECT IF(100>9,'1','0');

需求：如果右奖金，则显示最终奖金，如果没有，则显示0

SELECT IF(commission_pct IS NULL,0,commission_pct*12*salary) 奖金，commission_pct
FROM employees;

2、CASE函数

**情况1：**类似switch语句，可以实现等值判断

CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
…
ELSE 结果n
END

案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变

SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END newSalary
From employees;

**情况2：**类似于多重IF语句，实现区间判断

CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
…

ELSE 结果n

END

案例：如果工资>20000,显示级别A
工资>15000,显示级别B
工资>10000,显示级别C
否则，显示D

SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END level
FROM employees

五、分组函数

清单：

sum(字段名)：求和
avg(字段名)：求平均数
max(字段名)：求最大值
min(字段名)：求最小值
count(字段名)：计算非空字段值的个数

count的补充：

统计结果集的行数，推荐使用count(*)

SELECT COUNT(*) FROM employees;

搭配distinct实现去重的统计

#查询有员工的部门的个数
SELECT COUNT(DISTINCT department_id) FROM employees;

思考：每个部门的总工资、平均工资？(GROUP BY)

SELECT department_id,SUM(salary)
FROM employees
GROUP BY department_id;

五、分组查询

语法：

select 查询列表
from 表名
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;

执行顺序：

①from子句

②where子句

③group by子句

④having 子句

⑤select子句

⑥order by子句

特点：

①查询列表往往是分组函数和被分组的字段

②分组查询中的筛选分为两类

执行时间	筛选的基表	使用的关键字	位置
分组前筛选	原始表	where	GROUP BY之前
分组后筛选	分组后的结果集	having	GROUP BU之后

1、简单的分组

案例一、查询每个工种的员工平均工资

SELECT AVG(salary) FROM employees

最低0.47元/天解锁文章

阿兰你在哪

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

我的mysql笔记

MYSQL笔记

一、基础查询

1、查询常量

2、查询表达式

3、查询单个字段

4、查询多个字段

5、查询所有字段

6、查询函数（调用函数，获取返回值）

7、起别名

方式一：使用as关键字

方式二、使用空格

8、+的作用

方案一：使用+

方案二：使用concat拼接函数

9、distinct的使用

10、查看表的结构

二、条件查询

一、按照关系表达式筛选

案例一、查询部门编号不是100的员工信息

案例二、查询工资<15000的姓名、工资

二、按逻辑表达式筛选

案例一、插查询部门编号不是 50-100之间员工姓名、部门编号、邮箱

案例二、查询奖金率>0.03 或者 员工编号在60-110之间的员工信息

三、模糊查询

1、like

案例一、查询姓名中包含字符a的员工信息

案例二、查询姓名中包含最后一个字符为e的员工信息

案例三、查询姓名中包含第一个字符为e的员工信息

案例四、查询姓名中包含第三个字符为x的员工信息

案例五、查询姓名中包含第二个字符为_的员工信息

2、in

案例一、查询部门编号是30/50/90的员工名、部门编号

案例二、查询工种编号不是SH_CLERK或IT_PROG的员工信息

3、between and

案例一、查询部门编号式30-90之间的部门编号、员工姓名

案例二、查询年薪不是100000-200000之间的员工姓名、工资、年薪

4、is null/is not null

案例一、查询没有奖金的员工信息

案例二、查询有奖金的员工信息

对比=、IS、<=>

三、排序查询

一、按照单个字段排序

案例一、将员工编号>120的员工信息进行工资的升序

二、按表达式排序

案例一、对有奖金的员工，按年薪降序

三、按别名排序

四、按照函数的结果排序

案例一、按姓名的字数长度进行排序

五、按多个字段排序

案例一、查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序

六、按照列数排序

四、常见函数

一、字符函数

1、CONCAT拼接字符

2、LENGTH 获取字节长度

3、CHAR_LENGTH 获取字符个数

4、SUBSTRING 截取子串

5、INSTR获取字符第一次出现的索引

6、TRIM去前后指定的字符，默认是去空格

7、LPAD/RPAD 左填充/右填充

8、UPPER/LOWER 变大写/变小写

9、STRCMP 比较两个字符大小

10、LEFT/RIGHT 截取字串

二、数学函数

1、ABS绝对值

2、CEIL向上取整 返回>=该参数的最小整数

3、FLOOR 向下取整，返回<=该参数的最大整数

4、四舍五入

5、TRUNCATE 截断

6、MOD取余

三、日期函数

1、NOW

2、CURDATE

3、CURTIME

4、DATEDIFF

5、DATE_FORMAT

6、STR_TO_DATE 按指定格式解析字符串为日期类型

四、流程控制函数

1、IF函数

案例二、查询奖金率>0.03 或者员工编号在60-110之间的员工信息

2、CEIL向上取整返回>=该参数的最小整数

五、分组查询