MySQL之增删改查基础，多例子

MMMMMMMay

已于 2022-08-16 11:15:29 修改

阅读量1.1k

点赞数

分类专栏： java MYSQL 文章标签：数据库 sql sqlserver

于 2022-08-16 11:12:37 首次发布

本文链接：https://blog.csdn.net/qq_36066908/article/details/126361652

版权

java 同时被 2 个专栏收录

22 篇文章 0 订阅

订阅专栏

MYSQL

2 篇文章 0 订阅

订阅专栏

目录

1. 基本的SELECT语句

1.1. SELECT...

1.2. SELECT ... FROM

1.3. 列的别名:以在列名和别名之间加入关键字AS，别名可以使用双引号，AS关键字可以省略(建议不省略，增加可读性)

1.4. 在SELECT语句中使用关键字DISTINCT去除重复行

1.5. 运算符

1.6. 排序和分页 ORDER BY ,LIMIT

2. 多表查询

2.1多表查询的分类

3. 函数

3.1 聚合函数

3.2. 流程控制

4. 子查询

5. 增删改操作 INSERT DELETE/DROP UPDATE

5.1. 添加数据

5.2. 更新数据UPDATE ... SET...WHERE...

5.3. 删除数据 DELETE FROM...WHERE...

6. view 视图就是虚拟表自身不具备数据

SQL语言在功能上主要分为如下3大类： DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。 SELECT是SQL语言的基础，最为重要。
DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

创建数据库

CREATE DATABASE test1 CHARACTER SET 'utf8';
OR
CREATE DATABASE  IF NOT EXISTS  test2 CHARACTER SET 'utf8';

如何创建数据表

USE test2; #任何操作前先选择对应的库

创建表

CREATE TABLE IF NOT EXISTS myempl(
id INT,
emp_name VARCHAR(18),
hire_date DATE
);

基于现有表去创建并且复制表记录

CREATE TABLE empl1
AS
SELECT * FROM employees;

修改表 ---->ALTER TABLE

# 添加一个字段
ALTER TABLE empl1 ADD salary(10,2);#默认添加到最后一个字段的位置

COMMIT / ROLLBACK

COMMIT 提交数据，一旦提交数据就被永久的保存在了数据库中，不可逆转
ROLLBACK 回滚数据,可以将数据回滚到上一次COMMIT之后

举例核心表介绍

employees 员工信息表

departments 部门信息表

locations 地址信息表

1. 基本的SELECT语句

1.1. SELECT...

SELECT 1; #没有任何子句 
SELECT 9/2; #没有任何子句

1.2. SELECT ... FROM

SELECT 选择列 FROM table_name #从表中读取某些列
SELECT * FROM table_name;  ##从表中读取全部列

1.3. 列的别名:以在列名和别名之间加入关键字AS，别名可以使用双引号，AS关键字可以省略(建议不省略，增加可读性)

SELECT last_name AS name, commission_pct comm FROM employees;

1.4. 在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT last_name AS name, commission_pct comm FROM employees;

1.5. 运算符

算数运算符：+ - * / %

SELECT 100 AS "加",100+50 AS "减",100*10 AS "乘法",100/10 AS "除法",100%10 AS "模" FROM DUAL; # DUAL是虚表的意思 % = MOD  = DIV

# 例：查询员工ID为偶数的员工信息

SELECT employees.employee_id,employees.last_name,employees.department_id 
FROM employees WHERE employees.department_id % 2 = 0;

基础比较运算符 = ，<=> ,!= , < , <= , > , >=

例：查询员工提成系数为空的员工薪资信息

SELECT last_name,salary,commission_pct FROM employees
WHERE commission_pct = NULL; #查询为0条记录 NULL的比较都为NULL
OR
SELECT last_name,salary,commission_pct FROM employees
WHERE commission_pct IS NULL;
OR
SELECT last_name,salary,commission_pct FROM employees
WHERE commission_pct IS NOT NULL;
OR
SELECT last_name,salary,commission_pct FROM employees
WHERE commission_pct <=> NULL;

LEAST(value1,value2,...) 返回最小的值
GREATEST(value1,value2,...) 返回最大的值

SELECT LEAST(1,1,2,3),GREATEST(1,1,3,4);

BETWEEN AND 满足条件1 和条件 2 之间的

#例：查询员工工资在6000-8000的工资

SELECT employee_id,last_name,salary FROM employees
WHERE salary BETWEEN 6000 AND 8000;
OR
SELECT employee_id,last_name,salary FROM employees
WHERE salary >= 6000 AND salary <= 8000;

#相反

SELECT employee_id,last_name,salary FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
OR
SELECT employee_id,last_name,salary FROM employees
WHERE salary < 6000 OR salary > 8000;

IN , NOT IN 离散值之间的查询

例：查询部门号为10，20，30部门的员工信息

SELECT employee_id,last_name,salary,department_id FROM
employees WHERE department_id IN(10,20,30);
OR
SELECT employee_id,last_name,salary,department_id FROM
employees WHERE department_id =10 OR department_id =20 OR department_id=30;

#反之

SELECT employee_id,last_name,salary,department_id FROM
employees WHERE department_id NOT IN(10,20,30);
OR
SELECT employee_id,last_name,salary,department_id FROM
employees WHERE department_id !=10 AND department_id !=20 AND department_id != 30;

LIKE 模糊查询

% 不确定的个数字符
_ 代表一个不确定字符
...
...

例：查询名字包含字母a的员工

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

例：查询名字包含字母a和e的员工

SELECT last_name FROM employees WHERE last_name LIKE '%a%' 
AND last_name LIKE '%e%';

SELECT last_name FROM employees WHERE last_name LIKE '%a%e%' 
OR last_name LIKE '%e%a%';

------------ 练习------------

# 工资不在5000 和 12000之间的员工

SELECT last_name,salary FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
OR
SELECT last_name,salary FROM employees
WHERE salary <5000 OR salary >12000;

# 选择在20或者50号部门工作的员工姓名和部门号

SELECT last_name,department_id FROM employees
WHERE department_id = 20 OR department_id = 50;
OR
SELECT last_name,department_id FROM employees
WHERE department_id IN (20,50);

# 选择公司没有管理者的员工姓名以及job_id

SELECT last_name,job_id,manager_id FROM employees
WHERE manager_id IS NULL;
OR
SELECT last_name,job_id,manager_id FROM employees
WHERE  ISNULL(manager_id);
OR
SELECT last_name,job_id,manager_id FROM employees
WHERE manager_id <=> NULL;

# 选择公司中有奖金的员工姓名，工资和奖金级别

SELECT last_name,salary,commission_pct FROM employees
WHERE commission_pct IS NOT NULL;
OR
SELECT last_name,salary,commission_pct FROM employees
WHERE NOT commission_pct <=> NULL;

# 选择员工姓名的第三个字母是a或者k的员工姓名

SELECT last_name FROM employees
WHERE last_name LIKE '__a%' OR last_name LIKE '__k%';

1.6. 排序和分页 ORDER BY ,LIMIT

ORDER BY

ASC 升序默认升序

# 按照salary排序显示

SELECT employee_id,last_name,salary FROM employees
ORDER BY salary ASC;

DESC 降序

# 按照salary排序显示

SELECT employee_id,last_name,salary FROM employees
ORDER BY salary DESC;

## note：SELECT * FROM employees; #默认是记录添加的顺序显示

使用列的别名排序

SELECT employee_id,salary,salary*12 AS annual_sal FROM employees
ORDER BY annual_sal DESC;

SELECT employee_id,salary,salary*12 AS annual_sal FROM employees
WHERE annual_sal >0; ##错误
#解释：代码执行逻辑出错
#执行逻辑：先执行FROM employees再执行WHERE annual_sal >0最后再SELECT employee_id,salary,salary*12 AS annual_sal 所以别名再没有创建之前就被使用，所以报错
#总节：代码是先执行 找表操作(FROM) 再进行过滤操作(WHERE) 再是取字段(SELECT) 最后排序(ORDER BY)

二级排序.多级排序后续加字段就可

# 显示员工信息，按照department_id降序排列，相同的department_id数据再按照升序排列

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

分页 LIMIT

# 每页显示20条数据，显示第一页

SELECT employee_id,last_name,salary FROM employees
LIMIT 0,20;

# 每页显示20条数据，显示第二页

SELECT employee_id,last_name,salary FROM employees
LIMIT 20,20;#（想显示的页数-1）*每页显示条数，每页显示条数

扩展执行顺序先FROM-->WHERE-->SELECT-->ORDER BY-->LIMIT

# 例：
SELECT employee_id,last_name,salary 
FROM employees
WHERE salary > 5000 
ORDER BY salary DESC 
LIMIT 0,10;

# 例：显示第5条数据和第6条数据

SELECT employee_id,last_name,salary 
FROM employees
WHERE salary > 5000
LIMIT 4,2;

# MYSQL8.0新特性 LIMIT...OFFSET...

SELECT employee_id,last_name,salary 
FROM employees
WHERE salary > 5000
LIMIT 2 OFFSET 4;

# 例：查询最高工资的员工信息

SELECT employee_id,last_name,salary 
FROM employees
ORDER BY salary DESC
LIMIT 1;

2. 多表查询

# 例：查询员工"Able"在那个城市工作

SELECT t1.last_name,t1.department_id,t2.department_name,t2.location_id,t3.city
FROM employees AS t1,departments AS t2,locations AS t3 
WHERE t1.department_id = t2.department_id AND t2.location_id = t3.location_id
AND t1.last_name ='Abel';
OR
SELECT t1.last_name,t1.department_id,t2.department_name,t2.location_id,t3.city
FROM employees AS t1 
JOIN departments AS t2 ON t1.department_id = t2.department_id
JOIN locations AS t3 ON t2.location_id = t3.location_id
WHERE t1.last_name = 'Abel';

# 子查询方式

SELECT * FROM locations
WHERE location_id =(
SELECT location_id FROM departments
WHERE department_id =(
SELECT department_id FROM employees
WHERE last_name = 'Abel')); # 注意 子查询中的 SELECT后面字段只能和上一级的相同，不能多字段

2.1多表查询的分类

等值连接，非等值连接
自连接，非自连接
内连接，外连接

#非等值连接

例：SELECT emp.last_name,emp.salary,job.grade_level 
FROM employees AS emp,job_grades AS job
WHERE emp.salary BETWEEN job.lowest_sal AND job.highest_sal;

#自连接，非自连接

例：查询员工ID，员工姓名及其管理员的ID姓名

SELECT t1.manager_id,t1.last_name,t2.department_id,t2.last_name 
FROM employees AS t1,employees t2
WHERE t1.manager_id = t2.employee_id;

#内连接，外连接
-- 内连接只满足多表数中据键值相匹配的记录
-- 外连接分为左外连接，右外连接，满外连接

例：查询所有员工的last_name，department_name信息

SELECT * FROM employees; #107条记录

SELECT * FROM departments;#27条记录

SELECT * FROM employees AS emp INNER JOIN departments AS dep
ON emp.department_id = dep.department_id; #内连接 #106条记录

SELECT * FROM employees AS emp INNER JOIN departments AS dep
USING(department_id);

SELECT * FROM employees AS emp LEFT OUTER JOIN departments AS dep
ON emp.department_id = dep.department_id; #左外连接 OUTER可省略 #107条记录

SELECT * FROM employees AS emp RIGHT OUTER JOIN departments AS dep
ON emp.department_id = dep.department_id; #右外连接 OUTER可省略 #122条记录

SELECT * FROM employees AS emp FULL JOIN departments AS dep
ON emp.department_id = dep.department_id; #满外连接 mysql不支持

#MySQL支持的满外连接
#UNION合并查询
 -- UNION 去重 UNION ALL 没去重
#自然连接 NATURAL JOIN

-------- 练习多表查询-------

SELECT * FROM employees;
SELECT * FROM departments;
SELECT * FROM locations;

# 例：显示所有的员工姓名，部门号，部门名称

SELECT emp.last_name,emp.department_id,dep.department_name
FROM employees AS emp LEFT JOIN departments AS dep
ON emp.department_id = dep.department_id;

# 例：查询90号部门员工的job_id和90号部门号的location_id,city

SELECT emp.job_id,emp.department_id,loc.location_id,loc.city 
FROM employees AS emp,departments AS dep,locations AS loc
WHERE emp.department_id = dep.department_id AND dep.location_id = loc.location_id
AND emp.department_id = 90;
OR
SELECT emp.job_id,emp.department_id,loc.location_id,loc.city 
FROM employees AS emp JOIN departments AS dep
ON emp.department_id = dep.department_id
JOIN locations AS loc
ON dep.location_id = loc.location_id
AND emp.department_id = 90;

# 例：查询所有有奖金的员工的last_name,departments_name,lacation_id,city

SELECT emp.last_name,dep.department_name,loc.location_id,loc.city
FROM employees AS emp,departments AS dep,locations AS loc
WHERE emp.department_id = dep.department_id AND dep.location_id = loc.location_id
AND emp.commission_pct IS NOT NULL; # 34条记录 少一条

SELECT emp.last_name,dep.department_name,loc.location_id,loc.city
FROM employees AS emp
LEFT JOIN departments AS dep
ON emp.department_id = dep.department_id
LEFT JOIN locations AS loc
ON dep.location_id = loc.location_id
WHERE emp.commission_pct IS NOT NULL;#35条记录 正确

# 例：查询在city在Toronto工作的员工的last_name,job_id,departments_id,departments_name

SELECT emp.last_name,emp.job_id,dep.department_name,dep.department_name,loc.city
FROM employees AS emp,departments AS dep,locations AS loc
WHERE emp.department_id = dep.department_id AND dep.location_id = loc.location_id
AND loc.city = 'Toronto';

例：查询指定员工姓名，员工号，以及他的管理者姓名和员工号，结果显示如下

employees	Emp#	manager	Mgr#
aaa	002	aaa	001

SELECT staf.last_name AS "employees",staf.employee_id AS "Emp#"
,mgr.last_name AS "manager",mgr.employee_id AS "Mgr#"
FROM employees AS staf,employees AS mgr
WHERE staf.manager_id = mgr.employee_id; # 106条记录 少老板的记录 内连接

SELECT staf.last_name AS "employees",staf.employee_id AS "Emp#"
,mgr.last_name AS "manager",mgr.employee_id AS "Mgr#"
FROM employees AS staf 
LEFT JOIN employees AS mgr
ON staf.manager_id = mgr.employee_id; # 107条记录 老板没有管理者 外连接

例：查询那些部门没有员工

SELECT dep.department_id,dep.department_name 
FROM employees AS emp
RIGHT JOIN departments AS dep
ON dep.department_id = emp.department_id
WHERE emp.department_id IS NULL;

例：查询 部门为'Sales' 或者 'IT'的员工信息

SELECT emp.last_name,dep.department_name
FROM employees AS emp
JOIN departments AS dep
ON emp.department_id = dep.department_id
WHERE dep.department_name IN('Sales','IT');

3. 函数

3.1 聚合函数

常见聚合函数AVG, SUM,MAX,MIN,GROUP BY, HAVING

# 例：SELECT AVG(salary) "平均值",SUM(salary) "总和",MAX(salary) "最大值",MIN(salary) "最小值"
FROM employees;

COUNT 出现的次数

# 例：SELECT COUNT(employee_id) FROM employees;

GROUP BY 分组查询

# 例：各个部门的平均工资

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

# 例：查询各个department_id,job_id的平均工资

SELECT AVG(salary),department_id,job_id
FROM employees GROUP BY department_id,job_id;

# 例：查询各个部门中最高工资比10000高的部门信息

SELECT department_id,MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000; #聚合函数不能用WHERE

3.2. 流程控制

IF(expr1,expr2,expr3)

# 例：
SELECT last_name,salary,IF(salary>6000,'优秀','继续加油') AS detail
FROM employees;

# 例：
SELECT last_name,commission_pct,IF(commission_pct IS NULL,0,commission_pct)
FROM employees;

# 例：
SELECT last_name,commission_pct,IFNULL(0,commission_pct)
FROM employees;

CASE WHEN ...THEN...WHEN...THEN...ELSE...END

# 例：
SELECT last_name,salary,CASE 
       WHEN salary>6000 THEN 'C'
       WHEN salary>10000 THEN 'B'
       ELSE 'A'	
       END AS detail
       FROM employees; 

# 例：
SELECT last_name,salary,CASE department_id
	WHEN 10 THEN
		salary * 1.1
	WHEN 20 THEN
      salary * 1.2
	ELSE
		salary * 1.4
END  AS "涨工资"
FROM employees
WHERE department_id IN(10,20);

总结 SQL语法顺序

# 总结 SQL执行顺序

SELECT...  #6
FROM....  #1
(LEFT/RIGHT)JOIN...ON...  #2  先ON...再 JOIN
WHERE...AND/OR  #3
GROUP BY...  #4
HAVING...  #5
ORDER BY...  #7
LIMIT...  #8

4. 子查询

常规子查询

# 例：谁的工资比Abel的高？
-- 方式一分两步

SELECT salary 
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

-- 方式二子查询

SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary 
		FROM employees
		WHERE last_name = 'Abel'
		); 
# 例：查询工资大于149号员工工资的员工信息

SELECT last_name,salary
FROM employees
WHERE salary >(
	SELECT salary 
	FROM employees
	WHERE employee_id = 149
);

# 例：返回job_id与141号相同并且salary比143号员工多的员工姓名,job_id,salary
#讲解步骤
-- 第一步拆分需求job_id与141号,salary比143号

SELECT job_id FROM employees WHERE employee_id = 141;#ST_CLERK
SELECT salary FROM employees WHERE employee_id = 143;#2600

#第二步翻译题目

#翻译题目：查询job_id是ST_CLERK并且salary比2600高的员工...
SELECT last_name,job_id,salary
FROM employees
WHERE salary >() AND job_id = ();

#第三步 填充子查询语句

SELECT last_name,job_id,salary
FROM employees
WHERE salary > (
	SELECT salary 
	FROM employees
	WHERE employee_id = 143 
	AND job_id = (
		SELECT job_id 
		FROM employees 
		WHERE employee_id = 141
	)
);

# 例： 返回公司工资最少的员工的last_name,job_id,salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
	SELECT MIN(salary) FROM employees
);

HAVING 中的子查询

# 例：查询最低工资大于110号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 110
	);

多行子查询 IN,ANY/SOME, ALL

# 例：
SELECT last_name,job_id,salary
FROM employees
WHERE salary IN (
	SELECT MIN(salary) FROM employees GROUP BY department_id
);

# 例：查询平均工资最低的部门id

SELECT AVG(salary) AS avg_salary,department_id
FROM employees
GROUP BY department_id
ORDER BY avg_salary ASC 
LIMIT 1;

SELECT MIN(avg_table.avg_salary)
FROM (SELECT AVG(salary) AS avg_salary,department_id
FROM employees
GROUP BY department_id ) AS avg_table;

# 例：查询员工中工资大于公司平均工资的员工的last_name,salary和department_id

SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
	);	
# 例：查询员工中工资大于本部门平均工资的员工的last_name,salary和department_id

SELECT e1.last_name,e1.salary,e1.department_id
FROM employees AS e1
WHERE salary > (
	SELECT AVG(salary)
	FROM employees AS e2
	WHERE e2.department_id = e1.department_id
	);
	SELECT t2.last_name,t2.salary,t2.department_id
	FROM employees AS t2,(
	SELECT AVG(salary) AS avg_sa,department_id
	FROM employees
	GROUP BY department_id) AS t1
	WHERE  t1.department_id = t2.department_id AND t2.salary > t1.avg_sa;

## 子查询 除了在 GROUP BY ,LIMIT 其他地方都可以声明子查询 ##

5. 增删改操作 INSERT DELETE/DROP UPDATE

# 创建表：
CREATE TABLE IF NOT EXISTS myempl(
	id INT,
	emp_name VARCHAR(18),
	hire_date DATE
	);
#查看：
SELECT * FROM myempl;

5.1. 添加数据

一次一条添加

例：
INSERT INTO myempl 
VALUES(1,'tom','2000-12-21');
OR
INSERT INTO myempl(id,emp_name,hire_date)
VALUES(2,'nico','2000-12-21');

一次多条

# 例：
INSERT INTO myempl(id,emp_name,hire_date)
VALUES(3,'fuyuhua','2000-12-21'),(4,'qinbo','2000-12-21');

将查询的结果插入到表中

# 例：
INSERT INTO myempl(id,emp_name,hire_date)
SELECT employee_id,last_name,hire_date
FROM employees
WHERE department_id IN (90,80);

5.2. 更新数据UPDATE ... SET...WHERE...

SELECT * FROM myempl;
# 例子：
UPDATE myempl
SET hire_date = CURDATE(),emp_name = 'BIGLOGO'
WHERE id = 1; # 不加WHERE 会全部删除

5.3. 删除数据 DELETE FROM...WHERE...

SELECT * FROM myempl;
# 例子：
DELETE FROM myempl WHERE id = 1;

6. view 视图就是虚拟表自身不具备数据

创建视图

CREATE VIEW employees_view
AS
SELECT last_name,salary
FROM employees;	 

SELECT * FROM employees_view; # 查看视图

创建视图（自带字段名）

CREATE VIEW employees_view(`name`,salary)
AS
SELECT last_name,salary
FROM employees;

查看视图

CREATE VIEW empl_depart_view
AS
SELECT emp.last_name,emp.salary FROM
employees AS emp,departments AS dep
WHERE emp.department_id = dep.department_id;# 创建视图	

SELECT * FROM empl_depart_view;  # 查看视图

7. 存储过程与函数

语法

CREATE PROCEDURE [name] (IN | OUT | INOUT)
BEGIN
方法体
END;

DELIMITER $ 设置分割符号

创建存储过程

# 例：
CREATE PROCEDURE selset_all_data()
BEGIN
	SELECT * FROM
	employees;
END;
# 存储过程的调用
CALL selset_all_data();

带返回值的

CREATE PROCEDURE select_out_data(OUT min_salary DOUBLE)
BEGIN
	SELECT MIN(salary) INTO min_salary
	FROM employees;
END;

SELECT @min_salary;	
CALL select_out_data(@min_salary); # 调用

带参数的

CREATE PROCEDURE select_in_data(IN l_name VARCHAR(20))
BEGIN
	SELECT salary ,last_name
	FROM employees
	WHERE last_name = l_name;
END;	

CALL select_in_data('Abel');  # 调用

带参数和带返回值的

CREATE PROCEDURE select_in_out_data(IN dep_id INT,OUT sal DECIMAL(10,2))
BEGIN
	SELECT salary INTO sal
	FROM employees
	WHERE department_id = dep_id
	LIMIT 1;
END;

SET @dep_id = 90;	
CALL select_in_out_data(@dep_id,@sal);
SELECT @sal;