Mysql基础1_没有找到字段>=2000 and 20000-CSDN博客

本文链接：https://blog.csdn.net/weixin_43571751/article/details/109412311

这篇博客介绍了MySQL的基础概念，包括DB、DBMS和SQL，详细讲解了5.5版本的安装、配置和启动停止方法。重点讨论了MySQL的连接方式，基本命令，以及SQL语法规范。在DQL部分，深入探讨了查询语言，包括基础查询、条件查询（如like、between and、in、is null等）、排序查询、函数应用、分组查询、连接查询、子查询和分页查询等内容，是初学者入门MySQL的宝贵资源。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mysql基础1

基础1

基础1

Mysql基础概念

DB：数据库（ database ）：存储数据的“仓库”，它保存了一系列有组织的数据。
DBMS：数据库管理系统（ Database Management System ），数据库是通过 DBMS 创建和操作的容器。
SQL：结构化查询语言（ Structure Query Language ），专门用来与数据库通信的语言。
在这里插入图片描述

SQL 的优点：

不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL。
简单易学。
虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

5.5版本安装

1. 安装：
在这里插入图片描述

2. 安装完成，开始配置：

如果不小心关掉配置页面，可以通过D:\Program Files\MySQL\MySQL Server 5.5\bin\MySQLInstanceConfig.exe打开。
在这里插入图片描述

在这里插入图片描述

mysql配置文件

D:\Program Files\MySQL\MySQL Server 5.5\my.ini
如果修改服务端配置，需要重启mysql服务。
在这里插入图片描述

启动和停止

方法一

开始 —> 右击计算机，选择管理 —> 服务 —> 找到MySQL。
也可以设置是否开机自启动。
在这里插入图片描述

方法二

启动： net start mysql
停止： net stop mysql
在这里插入图片描述

连接MySQL

使用mysql自带客户端

使用mysql自带客户端方式只能登陆root用户。
在这里插入图片描述
输入密码进入mysql：

CMD进入

mysql -h 用户名 -P 端口号 -u 用户名 -p 密码
在这里插入图片描述
注意： 如果直接输入密码，-p和密码之间不能有空格。

基本命令

查看数据库列表： show databases;
选择数据库： use 库名;
查看表： show tables [from 库名];
查看当前在哪个数据库： select database();
查看数据库版本： select version(); 或命令行执行：mgsql --version
查看表结构： desc 表名;
查看字符集： SHOW VARIABLES LIKE “%char%”;

Mysql语法规范

命令不区分大小写，但是建议关键字大写，表名、列名小写。
每条命令以分号结尾。
注释： # 单行注释； – 单行注释； /* 多行注释 */

图形界面SQLyog

在这里插入图片描述
导入测试数据：

表字段信息：

DQL（Data Query Language）查询语言

基础查询

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

查询单个表字段： SELECT colume FROM 表名;
查询多个表字段： SELECT colume1,colume2 FROM 表名;
查询表所有字段： SELECT * FROM 表名;
查询常量值： SELECT 100;
查询表达式： SELECT 100*99;
查询函数： SELECT VERSION();
起别名： SELECT 100*98 AS result; （①提高可读性 ②连接查询时如果有重名，可以通过别名区分开）
去重： SELECT DISTINCT department_id FROM employees;
拼接： SELECT CONCAT(last_name,first_name) FROM employees;

条件查询

语法： select 查询列表 from 表名 where 筛选条件；

按条件表达式筛选

运算符： >、<、 <>（也可以使用!=代表不等于）、 =、 >=、 <= 、

# 1. 查询员工薪资大于12000
SELECT * FROM employees WHERE salary > 12000;  
# 2. 查询员工id不为90的员工名和所属部门id       
SELECT first_name, department_id FROM employees WHERE employee_id!=90;
# 3. 查询员工id为176的员工名，部门id，年薪
#    因为commission_pct有可能为null，即没有年薪，所以通过ifnull关键字判断，如果为null就取0
SELECT first_name,department_id,salary*12*(1+IFNULL(commission_pct, 0)) AS 'yearly salary' FROM employees WHERE employee_id=176;

按逻辑表达式筛选

逻辑运算符： and、or、not 或&&、||、!

# 查询员工薪资为10000-20000之间的员工名
SELECT first_name,salary FROM employees WHERE salary>10000 AND salary <20000;  

# 查询员工薪资大于15000或部门id不在90到110之间的员工信息
SELECT first_name,salary,department_id FROM employees WHERE (department_id<90 OR department_id>110) OR salary>15000;
SELECT first_name,salary,department_id FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

模糊查询

like

一般与通配符搭配使用。
通配符：

% ：代表任意多个字符，包含0个字符，但是不能匹配NULL。
_ ：代表任意一个字符。

# 1. 员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
# 2. 员工名中第三个字母为n，第五个字母为l的员工信息
SELECT * FROM employees WHERE last_name LIKE '__n_l%';
# 3. 查询员工名中第二个字符为_的员工名---加\防止_转义为通配符
SELECT * FROM employees WHERE last_name LIKE '_\_%';
#    也可以通过ESCAPE关键字自定义转义字符
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

# 4. 如下两个查询语句不等价，因为commission_pct 有可能为null，而通配符只能匹配字符，不能匹配null
SELECT * FROM employees;
SELECT * FROM employees WHERE commission_pct LIKE "%";

between and

between and是闭区间；
employee_id BETWEEN 100 AND 120 等价于 employee_id>=100 AND employee_id<=120

# 1. 查询员工编号再100到120之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

in

用于判断某个字段的值在给定的列表内。
注意： in列表中的值必须类型统一，并且不可以使用通配符。

# 1. 查询员工工种是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT first_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRES');

is null

=或!=(或<>)不能判断 null，需要使用is null或is not null关键字。

# 判断没有奖金的员工名和奖金率
SELECT first_name,commission_pct  FROM employees WHERE commission_pct IS NULL;
# 判断有奖金的员工名和奖金率
SELECT first_name,commission_pct  FROM employees WHERE commission_pct IS NOT NULL;

安全等于 <=>

既可以判断数值类型，也可以判断null。

# 判断没有奖金的员工名和奖金率
SELECT first_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
# 判断工资等于12000
SELECT first_name,salary FROM employees WHERE salary <=> 12000;

排序查询

语法： select 查询列表 from 表名 [where 查询条件] order by 排序列表 [asc|desc]; order by子句一般是放在查询语句最后面，limit语句除外。
注意： asc 升序，desc降序，默认升序。

# 1. 按照员工薪资降序
SELECT * FROM employees ORDER BY salary DESC;
# 2. 查询部分编号>=90的员工信息，按入职时间先后排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate;
# 3. 按年薪高低显示员工信息和年薪【表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct, 0)) AS yearly_salary FROM employees ORDER BY yearly_salary DESC;
# 4. 按姓名长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(first_name) AS name_lenght, last_name, salary FROM employees ORDER BY name_lenght;
# 5. 查询员工信息，要求先按工资升序排序，再按员工编号降序排序【多个排序条件】
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

常见函数

将一组逻辑语句封装在方法体重，对外暴露方法名，提高代码复用性。
语法： select 函数名() [from 表]；

单行函数

字符函数

# 1. length 查询字符有几个字节---英文每个字母占1个字节；utf8编码下中文占3个字节，gbk编码下中文占2个字节。
SELECT LENGTH("john");   # 4个字节
SELECT LENGTH("张三丰");  # 9个字节

# 2. concat 拼接字段，如下拼接为last_name_first_name
SELECT CONCAT(last_name, "_", first_name) FROM employees;

# 3. upper、lower，转换字符大小写
SELECT CONCAT(UPPER(last_name), "_", LOWER(first_name)) FROM employees;

# 4. substr
SELECT SUBSTR('李莫愁爱上了陆展元', 7);     # 从第7个字符开始截取，SQL中下标是从1开始的。
SELECT SUBSTR('李莫愁爱上了陆展元', 1,3);   # 截取1-3个字符，闭区间
	# last_name 首字母大写，其他字母小写
SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)), "_", LOWER(SUBSTR(last_name, 2))) FROM employees;

# 5. instr， 返回子串第一次出现的下标，找不到的话返回0
SELECT INSTR('李莫愁爱上了陆展元', '陆展元');   # 返回7

# 6. trim 去掉前后指定，默认去掉空格
SELECT TRIM("    aaa    ") AS out_str;
SELECT TRIM('a' FROM "aaa哈哈哈aaa哈哈哈aaaaaa") AS out_str;

# 7. lpad 指定长度左填充， rlap类似
SELECT LPAD("殷素素", 10, "*") AS out_str;     # *******殷素素
SELECT LPAD("殷素素", 2, "*") AS out_str;      # 殷素

# 8. replace  替换所有字符
SELECT REPLACE("周芷若爱上了张无忌，周芷若", "周芷若", "赵敏") AS out_str;      # 赵敏爱上了张无忌，赵敏

数学函数

# 1.  round 四舍五入
SELECT ROUND(-4.65) AS out_data;         #  -5
SELECT ROUND(-4.658, 2) AS out_data;     #  -4.66  小数点后保留两位，并四舍五入

# 2. ceil 向上取整
SELECT CEIL(1.01) AS out_data;           #  2

# 3. floor 向下取整
SELECT FLOOR(1.91) AS out_data;          #  1
SELECT FLOOR(0.91) AS out_data;          #  0

# 4. truncate 小数点后截断
SELECT TRUNCATE(1.99999, 1) AS out_data;

# 5. mod(a, b) 取模 --- a-a/b*b
SELECT MOD(-10, 3) AS out_data;      # -10 - (-10)/(3)*(3)= -10-(-3*3) = -10+9 = -1
SELECT MOD(-10, -3) AS out_data;     # -10 - (-10)/(-3)*(-3)= -10-3*(-3) = -10+9 = -1

日期函数

# 1. 当前时间（包含日期、时间）
SELECT NOW();                 		#   2020-11-03 22:16:12

# 2. 当前日期
SELECT CURDATE();             		#   2020-11-03

# 3. 当前日期
SELECT CURTIME();             		#   22:16:12

# 4. 获取指定部分：年、月、日、时、分、秒
SELECT YEAR(NOW());           		#   2020
SELECT MONTH("1888-09-12");   		#   9

# 5. 将str转换为日期
SELECT STR_TO_DATE("1998-3-2", "%Y-%c-%d") as out_put;     #   1998-03-02
# 查询入职时间是1992/04/03号的员工（web页面传到后台的是str，并且格式不一定按照年-月-日排列，所以需要格式化）
SELECT * FROM employees WHERE hiredate=STR_TO_DATE("04-03 1992", "%m-%d %Y");

# 6. 将日期格式化为指定字符
SELECT DATE_FORMAT(NOW(), "%Y年%m月%d日") AS out_put;       #    2020年11月03日
# 查询有奖金的员工名称和入职日期（格式：xx月/xx日 xx年）
SELECT last_name, DATE_FORMAT(hiredate, "%m月/%d日 %y年") AS 入职日期 FROM employees WHERE commission_pct IS NOT NULL;

在这里插入图片描述

# 7. 查询最大入职日期与最小入职日期的差多少天---DATADIFF(date1, date2)，会将date1-date2再转换为天数 
SELECT DATEDIFF(MAX(hiredate), MIN(hiredate)) FROM employees;

其他函数

# 1. 查询当前登录的用户
SELECT USER();                 #   root@localhost 
# 2. 查询当前所在数据库
SELECT DATABASE();             #   myemployees
# 3. 查询数据库版本
SELECT VERSION();              #   5.5.62

流程控制函数

if函数

语法： IF(条件，条件成立返回值，不成立返回值)

# 查询员工名称，奖金信息，如果奖金不为null则输出"有奖金"，反之输出"没奖金"
SELECT last_name, commission_pct, IF(commission_pct IS NOT NULL, "有奖金", "没奖金") as 备注 FROM employees;

case函数

case语法1：
	CASE 要判断的字段或表达式
	WHEN 常量1 THEN 值1或语句
	WHEN 常量2 THEN 值2或语句
	...
	ELSE 值3
	END

# 查询员工名称，原始薪资，部门id， 新工资（部门id为30的原薪资*1.1，部门id为40的原薪资*1.2，部门id为50的原薪资*1.3，其他不变）
SELECT last_name, salary AS 原始工资, 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;

case语法2：
	CASE
	WHEN 条件1 THEN 值1或语句
	WHEN 条件2 THEN 值2或语句
	...
	ELSE 值3或语句
	END

# 查询员工名称、薪资、薪资级别（薪资大于20000为A级，大于15000为B级，大于10000位C级，其他为D级）
SELECT last_name, salary,
CASE
WHEN salary>20000 THEN "A"
WHEN salary>15000 THEN "B"
WHEN salary>10000 THEN "C"
ELSE "D"
END AS 薪资等级
FROM employees;

聚合函数（分组函数）

分组函数用于统计，又称为聚合函数或统计函数。

基本用法

注意： sum只适用于计算数值类型。

# 1. 求薪资总和
SELECT SUM(salary) FROM employees;
# 2. 求薪资平均值
SELECT avg(salary) FROM employees;
# 3. 求薪资最大值
SELECT max(salary) FROM employees;
# 4. 求薪资最小值
SELECT min(salary) FROM employees;
# 5. 求薪资不为null的人个数---count不会计算null值
SELECT count(salary) FROM employees;                     # 107

参数支持的类型

avg和sum基本只适用于数值类型。

max、min、count适用于任意类型。

# 1. 求名字的最大值、最小值---last_name可以排序，即有最大值，可以通过max计算
SELECT MAX(last_name), MIN(last_name) FROM employees;
# 2. 求奖金不为null的人个数
SELECT count(commission_pct) FROM employees;             # 35

是否忽略null

所有的聚合函数都会忽略null行。

# 计算奖金比总值，其中个别人commission_pct字段为null，sum会忽略null行，avg也会忽略null行
SELECT SUM(commission_pct), AVG(commission_pct) FROM employees;                # 7.80    0.222857

和distinct搭配

聚合函数都可以和distinct搭配使用

# 1 . 求薪资去重后的总和、总薪资
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;      #  397900.00       691400.00

count函数的详细介绍

# 1. 常用于统计总行数
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

效率问题：
MYISAM存储引擎下，count()效率更高；
INNODB引擎下，count(1)和count()效率基本一致。

分组查询

将数据按照部门id进行分组后，再查询每个部门的员工平均薪资。
在这里插入图片描述
语法：

基本用法

# 1. 查询每个部门的平均薪资
SELECT AVG(salary), department_id FROM employees GROUP BY department_id;
# 2. 查询每个工种的平均薪资
SELECT MAX(salary), job_id FROM employees GROUP BY job_id;
# 3. 查询每个位置上的部门个数
SELECT COUNT(*), location_id FROM departments GROUP BY location_id;

# 4. 查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary), department_id FROM employees WHERE email LIKE "%a%" GROUP BY department_id;

# 5. 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

复杂筛选

查询哪个部门的员工个数大于2

（1）先获取每个部门的员工个数
SELECT COUNT(*) AS num, department_id FROM employees GROUP BY department_id;

（2）需要根据（1）的拿到的新数据集再进行筛选num>2的部门，即通过 having 筛选（这里不能用where，where需要在group by之前）
SELECT COUNT(*) AS num, department_id FROM employees GROUP BY department_id HAVING num > 2;

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

（1）先获取每个工种有奖金的员工的最高工资
SELECT MAX(salary) AS max_salary, job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id;
（2）根据（1）获取的数据集，通过 having 判断最高工资大于12000的员工
SELECT MAX(salary) AS max_salary, job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING max_salary>12000;

查询领导编号大于102的每个领导首先的最低工资大于5000的领导编号及最低工资

（1） 查询领导编号大于102的每个领导手下的员工的最低工资
SELECT MIN(salary) AS min_salary, manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id ;
（2） 根据（1）获取的数据集，筛选出每个领导手下最低工资大于5000的
SELECT MIN(salary) AS min_salary, manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING min_salary>5000;

按员工姓名长度分组，查询每组员工个数，筛选员工个数大于5的有哪些

SELECT COUNT(*) AS num, LENGTH(last_name) AS name_len FROM employees GROUP BY name_len HAVING num>5;

查询每个部门、每个工种的员工的平均工资

# 按多个条件分组，会将两个条件结合起来，都相同的分为一组
SELECT AVG(salary), department_id, job_id FROM employees GROUP BY department_id, job_id;

查询每个部门每个工种的员工的平均工资，并且按照平均工资的升序显示：

SELECT AVG(salary) AS avg_salary, department_id, job_id FROM employees GROUP BY department_id, job_id ORDER BY avg_salary;.

查询各个管理者手下的员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT MIN(salary) AS min_salary,manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING min_salary>6000;

注意

分组查询中的筛选条件分为两类：分组前筛选和分组后筛选。
分组前筛选：数据集是原始表，关键字是where，位置在group by子句前面。
分组后筛选：数据集是分组后的结果集，关键字是having，位置在group by子句后面。
group by支持单个字段分组，多个字段分组（多个字段间逗号隔开，不区分顺序）。
可以使用order by 排序（排序放在最后）。

连接查询

当查询的字段涉及到多个表时，就需要使用连接查询，又称为多表查询。

# 语法
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

实验数据表结构如下：
在这里插入图片描述

sql92语法（内连接）

等值连接

多表连接的结果为多表的交集部分；
n表连接，至少需要n-1个连接条件；
多表的顺序没有要求；
可以搭配前面介绍的所有子句使用，例如：排序、分组、筛选等

# 1. 查询女神名和对应的男神名
SELECT NAME, boyName FROM beauty, boys WHERE beauty.boyfriend_id=boys.id;

# 2. 员工名和对应的部门名
SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;

# 3. 查询员工名、工种号、工种名
   注意：如果起了别名，就必须使用别名，不能再用原表名
SELECT employee_id, e.job_id, job_title FROM employees AS e, jobs WHERE e.job_id = jobs.job_id;

# 4. 查询有奖金的员工名、部门名
SELECT last_name, department_name FROM employees AS e, departments AS d WHERE e.department_id=d.department_id AND e.commission_pct IS NOT NULL;

# 5. 查询城市名中第二个字符为o的部门名和城市名---带筛选
SELECT department_name, city FROM departments AS d, locations AS l WHERE d.location_id=l.location_id AND city LIKE "_o%";

# 6. 查询每个城市的部门个数 --- 带分组查询
SELECT COUNT(*),l.city FROM departments AS d,locations AS l WHERE d.location_id=l.location_id GROUP BY l.city;

# 7. 查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资 --- 带分组
SELECT MIN(e.salary), e.manager_id, d.department_name FROM employees AS e,departments AS d WHERE e.department_id=d.department_id AND e.commission_pct IS NOT NULL GROUP BY d.department_name;

# 8. 查询每个工种的工种名和员工个数，并按照员工个数降序排序 --- 带分组
SELECT job_title,COUNT(*) AS num FROM jobs AS j, employees AS e WHERE e.job_id=j.job_id GROUP BY j.job_title ORDER BY num DESC;

# 9. 查询员工名、部门名、所在城市 --- 三表查询
SELECT last_name, department_name, city FROM employees AS e, departments AS d, locations AS l WHERE e.department_id=d.department_id AND d.location_id=l.location_id;

非等值连接

# 1. 查询员工的工资和工资级别 --- 将salary和jobs_grades表的每行lowest_sal和highest_sal比较，在两者之间的匹配该行的等级
SELECT salary, grade_level FROM employees AS e, job_grades AS j WHERE salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

在这里插入图片描述

员工名称为De Haan，找到对应的manager_id是100；
因为manager本身也是属于员工，即继续在employees查询对应的员工名称（manager 名称）；
这里需要把employees查询两次，先获取manager_id，再通过manager_id = employee_id拿到对应行的last_name。

# 1. 查询员工名和对应上级名称
SELECT e.last_name, m.last_name FROM employees AS e, employees AS m WHERE e.manager_id = m.employee_id;

练习题

# 1. 显示所有员工的姓名、部门号、部门名称
SELECT last_name, d.department_id, department_name FROM employees AS e, departments AS d WHERE e.department_id=d.department_id;

# 2. 查询部门号为90号的所有员工job_id、部门号为90号的location_id
SELECT e.job_id, d.location_id,d.department_id  FROM employees AS e, departments AS d WHERE d.department_id=90 AND e.department_id=d.department_id;

# 3. 查询所有有奖金的员工的last_name, department_name, d.location_id, city
SELECT last_name, department_name, d.location_id, city FROM employees AS e, departments AS d, locations AS l WHERE e.commission_pct IS NOT NULL AND e.department_id=d.department_id AND d.location_id=l.location_id;

# 4. 查询每个工种、每个部门的部门名、工种名和最低工资
SELECT job_title, department_name, MIN(salary) FROM employees AS e, departments AS d, jobs AS j WHERE e.department_id=d.department_id AND e.job_id=j.job_id GROUP BY job_title, department_name;

# 5. 查询每个国家的部门个数大于2的国家编号
SELECT country_id, COUNT(*) AS num FROM departments AS d, locations AS l WHERE d.location_id=l.location_id HAVING num>2;

sql99语法

语法： select 查询列表 from 表1 别名【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排列列表】
分类：

内连接： inner。
外连接：左外：left 【outer】
右外：right 【outer】
全外：full【outer】
交叉连接：cross

内连接

select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件；

**特点：**可以使用排序、分组、筛选；inner可以省略；筛选条件放在where后面，连接条件放在on后面。

等值连接：


# 1. 查询员工名、部门名
SELECT last_name, department_name FROM employees e INNER JOIN departments d ON e.department_id=d.department_id;

# 2. 查询名字中包含e的员工名和工种名
SELECT last_name, job_title FROM employees e INNER JOIN jobs j ON e.job_id=j.job_id WHERE e.last_name LIKE "%e%";

# 3. 查询部门个数大于3的城市名和部门个数
SELECT city, COUNT(*) num FROM locations l INNER JOIN departments d ON l.location_id=d.location_id GROUP BY l.city HAVING num>3;

# 4. 查询部门员工个数大于3的部门名和员工个数，并按照个数降序排列
SELECT department_name, COUNT(*) num FROM departments d INNER JOIN employees e ON e.department_id=d.department_id GROUP BY department_name HAVING num>3 ORDER BY num DESC;

# 5. 查询员工名、部门名、工种名，并按部门名降序
SELECT last_name, d.department_name, job_title FROM employees e INNER JOIN jobs j INNER JOIN departments d ON e.job_id=j.job_id AND e.department_id=d.department_id ORDER BY department_name DESC;

非等值连接：

# 1. 查询员工薪资与薪资等级
SELECT salary, grade_level FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

# 2. 查询工资级别对应的工资个数大于2，并按工资级别降序
SELECT grade_level, COUNT(*) num FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal GROUP BY grade_level HAVING num>20 ORDER BY grade_level DESC;

自连接：

# 查询姓名中包含字符“k”员工名称和对应上级名称
SELECT e.last_name, m.last_name FROM employees e INNER JOIN employees m ON e.manager_id=m.employee_id WHERE e.last_name LIKE "%k%";

外连接

应用场景： 查询主表有，但是从表没有的数据。
特点：

外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的则显示匹配的值，反之显示为null；
外连接的查询结果 = 内连接查询结果+主表中有而从表中没有的记录；
左外连接左边的是主表；
右外连接右边的是主表；
左外和右外交换两个表的顺序，可以达到同样的效果。

SELECT * FROM beauty b LEFT OUTER JOIN boys bo ON b.boyfriend_id=bo.id;

上面sql语句的执行结果：beauty表所有数据都会显示出来，然后拿着beauty表的boyfriend_id字段去匹配boys表的id字段，能匹配到就将对应的boys表数据拼接在beauty表数据后面，匹配不到就显示null。
在这里插入图片描述

# 1. 查询男朋友不在男生表中的女生信息：
# 左外链接
SELECT * FROM beauty b LEFT OUTER JOIN boys bo ON b.boyfriend_id=bo.id WHERE bo.id IS NULL;
# 右外连接
SELECT b.name FROM boys bo RIGHT OUTER JOIN beauty b ON b.boyfriend_id=bo.id WHERE bo.id IS NULL;

# 2. 查询没有员工的部门信息：
SELECT * FROM departments d LEFT OUTER JOIN employees e ON d.department_id=e.department_id WHERE e.employee_id IS NULL;

在这里插入图片描述

全外连接

全外连接 = 内连接 + 表1中有但表2没有 + 表2中有但表1没有
注意： mysql不支持全外连接。
在这里插入图片描述

交叉连接

交叉连接结果即为笛卡尔乘积。

SELECT * FROM boys CROSS JOIN beauty;

在这里插入图片描述

练习

# 1.查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.name, bo.* FROM beauty b LEFT OUTER JOIN boys bo ON bo.id=b.boyfriend_id WHERE b.id>3;

# 2. 查询哪个城市没有部门
SELECT * FROM locations l LEFT OUTER JOIN departments d ON l.location_id=d.location_id WHERE department_id IS NULL;

# 3. 查询部门名为SAL或IT的员工信息
SELECT * FROM employees e LEFT OUTER JOIN departments d ON e.department_id=d.department_id WHERE department_name IN("SAL", "IT");

子查询

概念：出现在其他语句中的select语句，称为子查询或内查询；内部嵌套其他select语句的查询，称为主查询或外查询。

where或having后子查询

特点：

子查询放在小括号内。
子查询一般放在条件的右侧。
标量子查询一般搭配单行操作符使用（>、< 、>=、 <=、 <>）。
列子查询一般搭配多行操作符使用（in/not in、any/some、all）。
子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果。

标量子查询（一行一列）

# 1.谁的工资比Abel高？
SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name="Abel");

# 2. 返回job_id与141号员工相同，salary 比143号员工多的员工姓名、job_id和工资
SELECT last_name,job_id,salary FROM employees WHERE job_id=(SELECT job_id FROM employees WHERE employee_id=141) AND salary>(SELECT salary FROM employees WHERE employee_id=143);

# 3. 返回公司工资最少的员工的last_name，job_id和salary
SELECT last_name,job_id,salary FROM employees WHERE salary=(SELECT MIN(salary) FROM employees);

# 4. 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary) FROM employees WHERE department_id=50;
SELECT MIN(salary) AS min_salary,department_id  FROM employees GROUP BY department_id;
SELECT MIN(salary) AS min_salary,department_id  FROM employees GROUP BY department_id HAVING min_salary>(SELECT MIN(salary) FROM employees WHERE department_id=50);

列子查询（一列多行）

# 1. 返回location_id是1400或1700的部门中的所有员工姓名

SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700);
SELECT last_name FROM employees WHERE department_id IN(SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));

# 2. 返回其他工种中比job_id为"IT_PROG"部门任一工资低的员工的员工号、姓名、job_id以及salary
SELECT salary FROM employees WHERE job_id="IT_PROG";
SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ANY(SELECT salary FROM employees WHERE job_id="IT_PROG") AND job_id!="IT_PROG";

# 3. 返回其他工种中比job_id为"IT_PROG"部门所有工资低的员工的员工号、姓名、job_id以及salary
SELECT salary FROM employees WHERE job_id="IT_PROG";
SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ALL(SELECT salary FROM employees WHERE job_id="IT_PROG") AND job_id!="IT_PROG";

行子查询（一行多列）

# 1. 查询员工编号最小并且工资最高的员工信息
SELECT MIN(employee_id) FROM employees;
SELECT MAX(salary) FROM employees;
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);

select后子查询

注意：select后是要查询的字段名称，所以select后的子查询返回值必须是一行一列。

# 1. 查询每个部门的员工个数
SELECT d.department_id, d.department_name, COUNT(*) FROM departments AS d INNER JOIN employees AS e ON d.department_id=e.department_id GROUP BY d.department_id;

SELECT d.department_id, d.department_name, (SELECT COUNT(*) FROM employees AS e WHERE d.department_id=e.department_id) FROM departments AS d;

from后子查询

注意：from后是要查询的表数据，所以from后的子查询返回值是结果集（一行一列、多行多列），并且必须起别名。

# 1. 查询每个部门的平均工资的等级
SELECT AVG(salary) FROM employees GROUP BY department_id;
SELECT av_dep.avg_salary,grade_level FROM job_grades INNER JOIN (SELECT AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS av_dep ON av_dep.avg_salary BETWEEN lowest_sal AND highest_sal;

exists后子查询（相关子查询）

注意：existe语句用于判断结果集是否为空，不为空返回1，为空返回0；一般情况下exists语句都可以被替代为in…，用处较少。

# 1.返回1 
SELECT EXISTS(SELECT salary FROM employees);
# 2.返回0 
SELECT EXISTS(SELECT salary FROM employees WHERE salary>1000000);

# 3. 有员工的部门名
SELECT department_name FROM departments AS d WHERE EXISTS(SELECT * FROM employees AS e WHERE d.department_id=e.department_id);
SELECT department_name FROM departments WHERE department_id IN(SELECT department_id FROM employees);

# 4. 查询没有女友的男神信息
SELECT * FROM boys AS b WHERE NOT EXISTS(SELECT * FROM beauty AS g WHERE b.id=g.boyfriend_id);
SELECT * FROM boys AS b WHERE b.id NOT IN(SELECT boyfriend_id FROM beauty);

子查询练习题

# 1. 查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary FROM employees WHERE department_id=(SELECT department_id FROM employees WHERE last_name="Zlotkey");

# 2. 查询工资比公司平均工资高的员工的员工号、姓名和工资。
SELECT employee_id, last_name, salary FROM employees WHERE salary>(SELECT AVG(salary) FROM employees);

# 3. 查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
SELECT employee_id, last_name, salary, e.department_id FROM employees AS e INNER JOIN (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) AS avg_e ON avg_e.department_id=e.department_id WHERE e.salary>avg_e.avg_sal;

# 4. 查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id, last_name, department_id FROM employees AS e WHERE e.department_id IN(SELECT DISTINCT department_id FROM employees WHERE last_name LIKE "%u%");

# 5. 查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id FROM employees AS e WHERE e.department_id IN(SELECT DISTINCT department_id FROM departments WHERE location_id=1700);

# 6. 查询管理者是K_ing的员工姓名和工资
SELECT last_name, salary FROM employees AS e WHERE e.manager_id IN(SELECT employee_id FROM employees WHERE last_name="K_ing");


# 7. 查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓.名
SELECT CONCAT(first_name,last_name) AS "姓.名" FROM employees WHERE salary=(SELECT MAX(salary) FROM employees);

分页查询

语法：

select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序字段】
limit offset，size；

offset代表偏移量，即起始位置；
size代表要查询的个数。

特点： ① limit语句放在查询语句的最后
② 分页公式：limit (page-1)*size, size

# 1. 查询第11-25条员工信息
SELECT * FROM employees LIMIT 10,15;

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

union联合查询

将多个查询语句的结果集合并成一个结果集。

语法：
查询语句1
union
查询语句2

注意： ① 多条查询语句查询的字段个数必须一致；
② 多条查询语句查询的字段类型、顺序最好一致（否则数据没有意义）；
③ 多条查询语句结果中有重复项，默认会去重，可以使用union all不去重。

# 查询中国用户中男性的信息以及外国用户中男性的信息
SELECT id, cname, csex FROM t_ca WHERE csex="男"
UNION
SELECT t_id, tname, tGender FROM t_ua WHERE tGender="male";

DML（Data Manipulation Language）语言

插入：insert
修改：update
删除：delete

插入语句

方式一

语法：
insert into 表名(列名,...)
values(值,...);


# 1.插入的值类型要与列的类型一致或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) VALUES(13, '唐艺昕', '女', '1990-4-23', '18988888888',NULL,2);

# 2. 不可以null的列必须插入值，可以为null的列如何插入值？
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id) VALUES(14, '娄艺潇', '女', '1990-4-23', '18988888888',NULL,2);
或
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id) VALUES(15, '热巴', '女', '1990-4-23', '18988888888',2);

# 3. 列的顺序可以调换,但列和值的个数必须一致
INSERT INTO beauty(id,sex,NAME,borndate,phone,boyfriend_id) VALUES(16, '女', '关晓彤',  '1990-4-23', '18988888888',2);

# 4. 可以省略列名，默认所有列，并且列的顺序和表中列的顺序一致
INSERT INTO beauty VALUES(17, '黄蓉', '女', '1990-4-23', '18988888888', NULL,2);

方式二

语法：
insert into 表名
set 列名1=值1,列名2=值2...

INSERT INTO beauty SET id=18,NAME='刘涛',phone='123456789';

两种方式对比

方式一支持一次性插入多条数据，方式二不支持

INSERT INTO beauty 
VALUES(19, '黄蓉1', '女', '1990-4-23', '18988888888', NULL,2),
      (20, '黄蓉2', '女', '1990-4-23', '18988888888', NULL,2),
      (21, '黄蓉3', '女', '1990-4-23', '18988888888', NULL,2);

方式一支持子查询，方式二不支持

INSERT INTO beauty(id,NAME,phone) SELECT id,boyname,'123456789' FROM boys WHERE id=3;

修改语句

修改单表记录

语法：
update 表名
set 列=值,列1=值1...
where 筛选条件；


# 1.修改beauty表中姓唐的女神的电话为13899999999
UPDATE beauty SET phone="13899999999" WHERE NAME LIKE "唐%";

修改多表记录

语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,列1=值1....
where 筛选条件；


# 1.修改张无忌的女友的手机号为11488888888
UPDATE boys AS bo INNER JOIN beauty AS be ON bo.id=be.boyfriend_id SET be.phone="11488888888" WHERE bo.boyName="张无忌";

# 2.修改没有男朋友的女神的男朋友编号都为2
# 左外连接beauty作为主表，连接条件是bo.id=be.boyfriend_id，如果be.boyfriend_id对应的值在bo.id中不存在则连接查询拿到的数据中bo.id会为null，以此筛选出“没有男朋友的女神”
UPDATE beauty AS be LEFT JOIN boys AS bo ON bo.id=be.boyfriend_id SET be.boyfriend_id=2 WHERE bo.id IS NULL;

删除语句

delete语句

单表删除语法：
delete from 表名
where 筛选条件

多表删除删除：
语法：
delete 表1别名，表2 别名
from 表1 别名
inner|left|right join 表2 别名
where 筛选条件；


# 1. 删除手机号最后一位为9的女神
DELETE FROM beauty WHERE phone LIKE "%9";

# 2. 删除张无忌的女友信息
DELETE be FROM beauty AS be INNER JOIN boys AS bo ON bo.id=be.boyfriend_id WHERE bo.boyName="张无忌";


# 3. 删除男神表中黄晓明信息以及对应的女友在女神表中的信息
DELETE be,bo FROM beauty AS be INNER JOIN boys AS bo ON bo.id=be.boyfriend_id WHERE bo.boyName="黄晓明";

truncate语句

用于删除整表数据
语法：truncate table 表;

delete VS truncate

truncate删除不支持筛选，用于删除整表数据，删除效率要高于delete。
加入表中有自增长列，使用delete删除后再插入数据，自增长列的值从断点开始，而使用truncate删除后再插入数据，自增长列的值从1开始。
truncate删除没有返回值，delete删除有返回值（显示几行被删除）。
truncate删除不能回滚，delete删除可以回滚。

Mysql基础1

Mysql基础1

基础1

Mysql基础概念

5.5版本安装

mysql配置文件

启动和停止

方法一

方法二

连接MySQL

使用mysql自带客户端

CMD进入

基本命令

Mysql语法规范

图形界面SQLyog

DQL（Data Query Language） 查询语言

基础查询

条件查询

按条件表达式筛选

按逻辑表达式筛选

模糊查询

like

between and

in

is null

安全等于 <=>

排序查询

常见函数

单行函数

字符函数

数学函数

日期函数

其他函数

流程控制函数

if函数

case函数

聚合函数（分组函数）

基本用法

参数支持的类型

是否忽略null

和distinct搭配

count函数的详细介绍

分组查询

基本用法

复杂筛选

注意

连接查询

分类

按年代分类

按功能分类

sql92语法（内连接）

等值连接

非等值连接

自连接

练习题

sql99语法

内连接

外连接

全外连接

交叉连接

练习

子查询

分类

where或having后子查询

标量子查询（一行一列）

列子查询（一列多行）

行子查询（一行多列）

select后子查询

from后子查询

exists后子查询（相关子查询）

子查询练习题

分页查询

union联合查询

DML（Data Manipulation Language）语言

插入语句

方式一

方式二

两种方式对比

修改语句

修改单表记录

DQL（Data Query Language）查询语言