MySQL-增删改查CRUD

最新推荐文章于 2024-09-25 14:34:16 发布

海滩上的猪

最新推荐文章于 2024-09-25 14:34:16 发布

阅读量413

点赞数 7

分类专栏： MySQL专栏文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_46580819/article/details/136433116

版权

MySQL专栏专栏收录该内容

23 篇文章 0 订阅

订阅专栏

文章详细介绍了在SQL数据库中进行INSERT（插入）、UPDATE（更新）、DELETE（删除）和SELECT（查询）操作的具体示例，包括使用WHERE子句进行条件筛选，以及ORDERBY语句对结果排序。

摘要由CSDN通过智能技术生成

添加INSERT

时间和文本类型需要单引号
如果没设置NOT NULL就可以赋值NULL
如果没有赋值，默认为NULL

#添加insert
INSERT INTO `employee` (id,`username`,birthday,entry_date,job,salary,`resume`,image)
	VALUES (3,'cyt','2000-09-24','2025-04-01','研究员',15000,'研究所的一名高级研究员','pig'),
		(4,'cyt','2000-09-24','2025-04-01','研究员',15000,'研究所的一名高级研究员','pig');
	
INSERT INTO `employee` (id,`username`,birthday,entry_date,job,salary,`resume`,image)
	VALUES (5,'cyt','2000-09-24','2025-04-01','研究员',15000,NULL,'pig');

INSERT INTO `employee` (id,`username`,birthday,entry_date,job,salary,image)
	VALUES (6,'cyt','2000-09-24','2025-04-01','研究员',15000,'pig');

修改UPDATE

set查找列，where查找行

#修改update
UPDATE employee SET salary = 5000 -- 修改所有人指定列salary

UPDATE employee SET salary = 15000 WHERE username = 'ybj' -- 修改指定人ybj的指定列salary

UPDATE employee SET salary = salary + 2000 WHERE username = 'cyt' -- 原有基础上添加

UPDATE employee SET salary = 15000, job = '研究生'
	WHERE username = 'ybj' -- 修改指定人ybj的多项列

删除DELETE

#删除delete
DELETE FROM employee WHERE id = 5; -- 删除id5

DELETE FROM employee; -- 删除所有数据

查找SELECT

在这里插入图片描述

ALTER TABLE employee
	ADD bons INT 
	AFTER salary
UPDATE employee SET bons = 2000
#查询select
SELECT * FROM employee -- *代表所有列

SELECT username,salary FROM employee -- 查找指定列

SELECT DISTINCT username,salary FROM employee -- distinct 过滤重复的数据
 
SELECT `username`,(salary + bons) FROM employee -- 统计总工资

SELECT `username`,(salary + bons - 100) FROM employee -- 总工资基础上扣除100

SELECT `username` AS '名字',(salary + bons - 100) AS `total_salary` FROM employee -- 更改显示名称

where语句

在这里插入图片描述

SELECT * FROM employee
	WHERE username = 'ybj'

SELECT * FROM employee
	WHERE salary > 10000
	
SELECT * FROM employee
	WHERE (salary + bons) > 9000
	
SELECT * FROM employee
	WHERE salary > 10000 AND bons >1000

SELECT * FROM employee
	WHERE salary > bons

SELECT * FROM employee
	WHERE (salary + bons) < 10000 AND job LIKE '%生' -- 模糊查询，%代表任意
	
	
SELECT * FROM employee
	WHERE salary >= 5000 AND salary <= 9000	
SELECT * FROM employee
	WHERE salary BETWEEN 5000 AND 9000 -- 闭区间between..and..
	
SELECT * FROM employee
	WHERE salary = 7000 OR salary = 15000 -- or
SELECT * FROM employee 
	WHERE salary IN (5000, 7000, 15000) -- in（）中的某一项，不是区间

order by语句

在这里插入图片描述

SELECT * FROM employee
	ORDER BY salary -- 默认升序asc

SELECT * FROM employee
	ORDER BY salary DESC -- 降序desc
	
SELECT 'username' ,(salary + bons) AS 'total' FROM employee
	ORDER BY total

SELECT * FROM employee
	WHERE username LIKE '%t'
	ORDER BY bons