MySQL基础（中）

最新推荐文章于 2024-07-02 08:14:38 发布

ruozhuliufeng

最新推荐文章于 2024-07-02 08:14:38 发布

阅读量409

点赞数 2

分类专栏： # MySQL笔记文章标签： MySql

本文链接：https://blog.csdn.net/ruozhuliufeng/article/details/102789344

版权

MySQL笔记专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL基础（中）

MySQL基础（上）

连接查询

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2 有n行，结果有m x n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

一、sql92标准

1.等值连接

多表等值连接的结果为多表的交集部分

n表连接，至少需要n-1个连接条件

多表的顺序没有要求

一般需要为表起别名

可以搭配前面介绍的所有子句使用，比如排序，求值

1).等值连接
案例1：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employee,departments
WHERE employee.department_id = departments.department_id;
2).为表起别名

提高语句的简洁度

区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能用原来表的名称

案例2：查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,job_id,j.job_title
FROM employee e,job j	
WHERE e.job_id = j.job_id;

3).添加筛选条件

案例3：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employee e,departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

案例4：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location.id=l.location_id
AND city LIKE '_O%';

4).添加分组

案例5：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;

案例6：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employee e
WHERE d.department_id = e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

5).排序

案例7：查询每个工种的工种名和员工的个数，并且按照员工个数降序
SELECT job_title,COUNT(*)
FROM employee e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

6).实现三表连接

案例8：查询员工名，部门名和所在的城市
SELECT last_name,department_name,city
FROM employee e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND	city LIKE 's%';

2.非等值连接

案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employee e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

3.自连接

案例：查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee.id m.last_name
FROM employee e,employee m
WHERE e.manager_id = m.employee.id

二、sql99标准

支持内连接:inner

外连接（左外:left [outer]，右外:right [outer]，全外full [outer]）

交叉连接:cross

语法：

SELECT 查询列表

FORM 表1 别名 [连接类型]

JOIN 表2 别名

ON 连接条件

[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]

1.内连接

语法：
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件;

特点
- 1.添加排序，分组，筛选
- 2.INNER 可以省略
- 3.筛选条件放在WHERE后面，连接条件放在ON后面，提高分离性，便于阅读
- INNER JOIN 连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1).等值连接

案例1：查询员工名、部门名
SELECT last_name,department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id;
案例2：查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employee e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE e.last_name LIKE '%e%';
案例3：查询员工名、部门名、工种名，并按部门名降序
SELECT last_name,department_name,job_title
FROM employee e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
GROUP BY department_name DESC;

2).非等值连接

案例4：查询员工的工资级别
SELECT salary,grade_level
FROM employee e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

3).自连接

案例5：查询员工的姓名和上级的名字
SELECT e.last_name,m.last_name
FROM employee e
JOIN employee m
ON e.manager_id = m.employee_id

2.外连接

应用：用于查询一个表中有，另一个表没有的记录

特点：
1.外连接的查询记过为主表中的所有记录，若从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null
2.左外连接，LEFT JOIN左边的是主表，右外连接，RIGHT JOIN右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果

4.全外连接=内连接的结果+表1中有的，但表2中没有+表2中有的，但表1中没有

  案例6：查询哪个部门没有员工
  SELECT d.*,e.emploee_id
  FROM departments d
  LEFT OUTTER JOIN employee e
  ON d.department_id = e.department_id
  WHERE e.employee_id IS NULL

3.全外连接

案例：
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boy_id = bo.id

4.交叉连接

实现笛卡尔乘积现象
案例：
SELECT b.,bo.
FROM beauty b
CROSS JOIN boy bo

子查询

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

分类：
- 按子查询出现的位置
  - select后面
    - 仅仅支持标量子查询
  - from后面
    - 支持表子查询
  - where或having后面（重要)
    - 支持标量子查询
    - 支持列子查询
    - 支持行子查询（用的较少）
  - exists后面（相关子查询）
    - 支持表子查询
- 按结果集的行列数不同
  - 标量子查询（结果集只有一行一列）
  - 列子查询（结果集只有一列多行）
  - 行子查询（结果集有一行多列）
  - 表子查询（结果集一般为多行多列）
    一、WHERE 和HAVING 后面

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

1.标量子查询（单行子查询）

  案例1：查询工资比Abel高的人
  SELECT *
  FROM employee
  WHERE salary>(
  	SELECT salary
  	FROM employee
  	WHERE last_name='Abel'		
  );
  案例2：查询job_id与141号员工相同，salary比143号员工高的员工姓名，job_id和工资
  分析：1）查询141号员工的job_id
  SELECT job_id
  FROM employee
  WHERE employee_id=141
  分析：2）查询143号员工的salary
  SELECT salary
  FROM employee
  WHERE employee_id =143
  分析：3）查询员工的姓名，job_id和工资，要求job_id=1)并且salary>2)
  SELECT last_name,job_id,salary
  FROM employee
  WHERE job_id=(
  	SELECT job_id
  	FROM employee
  	WHERE employee_id=141
  ) AND salary>（
  SELECT salary
  FROM employee
  WHERE employee_id =143）;

2.列子查询（多行子查询）

  案例1：返回location_id是1400或1700的部门中的所有员工姓名
  分析：1）查询location_id是1400或1700的部门编号
  SELECT DISTINCT department_id
  FROM department
  WHERE locatioin_id IN (1400,1700);
  分析：2）查询员工姓名，要求部门号是1列表中的某一个
  SELECT last_name
  FROM employee
  WHERE department_id IN (
  	SELECT DISTINCT department_id
  	FROM department
  	WHERE locatioin_id IN (1400,1700)
  );

  案例2：返回其他部门中job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary
  分析：1）查询Job_id为'IT_PROG'部门任一工资
  SELECT DISTINCT salary
  FROM employee
  WHERE job_id='IT_PROG';
  分析：2）查询员工号、姓名、job_id,以及salary,salary<(1)的任意一个
  SELECT last_name,employee_id,job_id,salary
  FROM employee
  WHERE salary< (
  	SELECT MAX(salary)
  	FROM employee
  	WHERE job_id='IT_PROG'
  )AND job_id<>'IT_PROG'；

3.行子查询（多列多行）了解

  案例：查询员工编号最小并且工资最高的员工信息
  SELECT * 
  FROM employee 
  WHERE (employee_id,salary)=(
  	SELECT MIN(employee_id),MAX(salary)
  	FROM employee
  );

二、SELECT后面

案例：查询每个部门的员工个数
SELECT d.*(
	SELCET COUNT(*)
	FROM employee e
	WHERE e.department_id = d.department_id)
FROM departments d;

三、FROM后面

将子查询结果充当一张表，要求必须起别名

案例：查询每个部门的平均工资的工资等级
分析：1）查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM departments
GROUP BY department_id;
分析：2）连接1的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
SELECT 
FROM (
	SELECT AVG(salary) ag,department_id
	FROM departments
	GROUP BY department_id) ag_dep
INNER JOIN job_grades
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

四、exists后面（相关子查询）

语法：
exists(完整的查询语句）
结果：
1或0

案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employee e
	WHERE d.department_id = e.department_id
);

分页查询（重点）

应用场景：
当需要显示的数据，一页显示不全，需要分页提交SQL请求

语法：

SELECT 查询列表

FROM 表

[JOIN type JOIN 表2

ON 连接条件

WHERE 筛选条件

GROUP BY 分组字段

HAVING 分组后的筛选

ORDER BY 排序的字段]

LIMMIT offset,size;

offset:要显示条目的起始索引（起始索引从0开始）

size:要显示的条目数

特点：

1.LIMIT语句放在查询语句的最后
2.公式：
- 要显示的页数 page，每页的条目数size
- select 查询列表 from 表
- limit (page-1)*size,size;

	案例1：查询前五条员工信息
	SELECT * 
	FROM employee
	LIMIT 0,5; #第一条开始的话，0可以省略
	
	案例2：查询第11到25条
	SELECT *
	FROM employee
	LIMIT 10,15;
	
	案例3：有奖金的员工信息，并且工资较高的前10名显示出来
	SELECT * 
	FROM employee
	WHERE commission_pct IS NOT NULL
	ORDER BY salary DESC
	LIMIT 10

联合查询

联合合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
…
应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但是查询的信息一致

特点

1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句查询的每一列的类型和顺序是一致的

3.UNION关键字默认是去重的，若不想去重可以使用UNION ALL显示全部数据

  案例1：查询部门编号>90或者邮箱包含的员工信息
  SELECT * FROM employee WHERE email LIKE '%@%' OR department_id>90(以前）
  使用联合查询
  SELECT * FROM employee WHERE email LIKE '%@%'
  UNION
  SELECT * FROM employee WHERE department_id>90

DML语言

数据操作语言：

插入：INSERT

修改：UPDATE

删除：DELETE

插入语句

语法：
INSERT INTO 表名（列名，…)

VALUES （值1,…);

特点：

1.插入的值的类型要与列的类型一致或兼容
2.不可以为null的列必须插入值，可以为null的列可以插入null
3.列数和值的个数必须一致
4.使用列名时，顺序可以不一致

5.列名可以省略，但是列的顺序和表中的列的顺序必须一致

  案例1：传统插入数据：支持插入多行数据，支持子查询
  INSERT INTO students(sid,sname,sage)
  VALUES(1,Rick,21);
  列名可以省略
  INSERT INTO students
  VALUES(2,Motty,18);

  案例2：使用SET插入数据：不支持插入多行数据，不支持子查询
  INSERT INTO students
  SET sid=3,sname=Mike,sage=20;

修改语句

1.修改单表的记录
语法：
UPDATE 表名
SET 列=新值，列=新值，…
WHERE 筛选条件;

2.修改多表的记录（补充）
UPDATE 表1 别名，表2 别名
SET 列=值，…
WHERE 连接条件
AND 筛选条件

sql99语法
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列=值,…
WHERE 筛选条件;

	案例：修改学生表中学号为1的学生
	UPDATE students
	SET sname='John'
	WHERE sid=1;

删除语句

方式一：DELETE
语法：
1.单表的删除

DELETE FROM 表名 WHERE 筛选条件

2.多表的删除
SQL92语法

DELETE 表1的别名

FROM 表1 别名，表2 别名

WHERE 连接条件

AND 筛选条件；

SQL99语法
DELETE 表1的别名

FROM 表1 别名

INNER|LEFT|RIGHT JOIN 表2 笔名

ON 连接条件

WHERE 筛选条件

方式二：清空数据
语法:TRUNCATE TABLE 表名

方式对比：

1.delete可以加条件，truncate不可以
2.truncate效率更高
3.如果要删除的表中有自增长列，如果用delete删除后，在插入数据，自增长列的值从短点开始，而用truncate删除后，再插入数据，自增长列的值从1开始
4.truncate删除没有返回值，delete删除有返回值

5.truncate删除无法回滚，delete删除可以回滚

  案例1：删除学生表学号为3的学生
  DELETE FROM students
  WHERE sid=3
  案例2：清空学生表的数据
  TRUNCATE TABLE students;

DDL语言

数据定义语言
库和表的管理和操作
创建：CREATE
修改：ALTER
删除：DROP

一、库的管理

1.创建库
- 语法：CREATE DATABASE [IF NOT EXISTS] 库名;
- 案例：创建库BOOKS
  - CREATE DATABASE IF NOT EXISTS books;
2.修改库
- 语法：ALTER DATABASE 库名 SET 值
- 案例：修改库BOOKS的字符集
  - ALTER DATABASE books CHARACTER SET gbk;
3.删除库
- 语法：DROP DATABASE [IF EXISTS] 库名
- 案例：删除库books
  - DROP DATABASE IF EXISTS books;

二、表的管理

1.创建表

CREATE TABLE 表名（
列名列的类型（长度），
列名列的类型（长度），
…
列名列的类型（长度）);

案例1：创建表Book

  CREATE TABLE book(
  	id INT,#编号
  	bName VARCHAR(20),#图书名
  	price DOUBLE,#价格
  	authorId INT,#作者编号
  	publishDate DATETIME#出版日期);

2.修改表
- 语法：ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUNM 列名 [列类型或约束]
- 修改列名
  - ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
- 修改列的类型或约束
  - ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
- 添加新列
  - ALTER TABLE book ADD COLUMN annual DOUBLE;
- 删除列
  - ALTER TABLE book DROP COLUNM annual;
- 修改表名
  - ALTER TABLE book RENAME TO book_author;
3.删除表
- 语法：DROP TABLE [IF EXISTS] 表名；
- 案例：
  - DROP TABLE book_author;
4.复制表
- 复制表的结构：CREATE TABLE 表名 LIKE 被复制的表名;
  - CREATE TABLE copy LIKE book;
- 复制表的结构+数据：CREATE TABLE 表名 SELECT * FROM 被复制的表名
  - CREATE TABLE copy2 SELECT * FROM book;

MySQL基础（下）

ruozhuliufeng

关注

2
点赞
踩
14

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL基础（中）

MySQL基础（中）MySQL基础上连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询笛卡尔乘积现象：表1 有m行，表2 有n行，结果有m x n行发生原因：没有有效的连接条件如何避免：添加有效的连接条件分类按年代分类：sql92标准：仅仅支持内连接sql99标准（推荐）：支持内连接+外连接（左外和右外）+交叉连接按功能分类：内连接：等值连...
复制链接

扫一扫