vlambda博客
学习文章列表

mysql 插入 、修改 、删除 方式

#方式一 支持插入多行
INSERT INTO beauty
VALUES(23,'张三1','女','2000-4-23','12132424',NULL,2),
(24,'张三2','女','2000-4-23','12132424',NULL,2),
(25,'张三3','女','2000-4-23','12132424',NULL,2);

#方式二
INSERT INTO beauty
SET id =19,NAME = '张三',phone = '911';

#修改方式
语法:
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

-- 单表修改
UPDATE beauty SET name='夏雪雪',borndate='2020-08-10' WHERE id = 11
-- 多表关联修改
#修改张无忌的女朋友的手机号为114
UPDATE beauty b
INNER JOIN boys bo ON b.boyfriend_id = bo.id
SET b.phone = '114'
WHERE bo.boyName = '张无忌'

#删除方式
语法:
DELETE FROM table_name [WHERE Clause]

DELETE FROM beauty WHERE id = 15

mysql 示例

# 每个部门的员工数
SELECT
d.*,
( SELECT COUNT( 1 ) FROM employees e WHERE e.department_id = d.department_id ) -- 员工
FROM
departments d -- 部门

#1 员工号=102的部门名
SELECT
*
FROM departments d
WHERE d.department_id IN
(
SELECT
e.department_id
FROM
employees e
WHERE e.employee_id = '102')

#2 员工号=102的部门名 (简单)
SELECT
d.department_name
FROM departments d
JOIN employees e on e.department_id = d.department_id
WHERE e.employee_id = '102'


-- 画面sql
SELECT
TEMP.SUPPNAME,-- 供应商名称
TEMP.MATMAXNAME,-- 物资大类
TEMP.MATMEDNAME,-- 物资中类
TEMP.MATMINNAME, -- 物资小类
QUANBUNUM,-- 全部数量
HEGENUM,-- 合格数量
ROUND((CASE WHEN QUANBUNUM = '0' THEN 0 ELSE HEGENUM/QUANBUNUM END)*100,2) HEGELV -- 抽检合格率
FROM
(SELECT
T1.SUPPNAME,-- 供应商名称
T2.MATMAXNAME,-- 物资大类
T2.MATMEDNAME,-- 物资中类
T2.MATMINNAME, -- 物资小类
COUNT(T3.QUALIFIEDSTATUS IS NOT NULL) QUANBUNUM,
COUNT(CASE WHEN T3.QUALIFIEDSTATUS = '1' THEN T3.QUALIFIEDSTATUS ELSE NULL END) HEGENUM
FROM
DWD_MAT_PURDELIVEYORD T1
INNER JOIN DWD_MAT_CONMATITEM T2 ON T1.CONID = T2.CONID
INNER JOIN DWD_MAT_SAMSAMPLING T3 ON T2.CONITEMCODE = T3.FIRSTNO
WHERE 1=1
GROUP BY T2.MATMAXNAME) TEMP