学习MySQL（四）：记录的增删改查

记录的增、删、改

• 增

-- 插入一条数据
INSERT INTO 表名（字段 1，字段2，字段3） VALUES（值 1，值2，值3） 
INSERT INTO 表名 VALUES（值 1，值2，值3）


-- 插入多条数据
INSERT INTO 表名（字段 1，字段2，字段3） VALUES（值 1，值2，值3），（值4，值5，值6）··· 
INSERT INTO 表名 VALUES（值 1，值2，值3），（值4，值5，值6）···

• 删

-- 指定条件删除
DELETE FROM 表 WHERE 条件;


-- 清空表
DELETE FROM 表;
  
注：不加 WHERE条件，整个表数据都没了，慎用DELETE
思考：DROP TABLE与DELETE FROM 表的区别

• 改

-- 更新一个字段
UPDATE 表名 SET 字段 =新值 WHERE 条件；


-- 更新多个字段
UPDATE 表名 SET 字段 1 =新值 1，字段2 =新值 2 WHERE 条件；

记录的单表查询

-- 语法：
SELECT DISTINCT 字段 1,字段 2 [,...] FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选条件 ORDER BY filed LIMIT 条数


-- 注：
GROUP BY field 根据什么进行分组，一般是某个字段或多个字段 
ORDER BY field 根据什么进行排序，一般是某个字段或多个字段
HAVING主要配合GROUP BY使用，对分组后的数据进行过滤，里面可以使用聚合函数 WHERE是针对SELECT查询的过滤，各有区别和用处


-- 优先级
  FROM
  WHERE
  GROUP BY
  SELECT
  DISTINCT
  HAVING
  ORDER BY
  LIMIT


-- 解释说明：
1、先找到表：FROM
2、拿着WHERE指定的约束条件，去表中去除符合条件的一条条数据
3、将去除的数据进行分组GROUP BY，如果没有GROUP BY，则每行为一组
4、执行SELECT查询所指定的字段
5、若有DISTINCT则去重
6、将结果按照条件排序：ORDER BY
7、限制结果的显示条数：LIMIT

• 例

-- 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment,
  emp_name CHAR ( 20 ) NOT NULL,
  sex enum ( "male", "female" ) NOT NULL DEFAULT "male",
  age INT ( 3 ) UNSIGNED NOT NULL DEFAULT 28,
  hire_date date NOT NULL,
  post CHAR ( 50 ),
  post_comment CHAR ( 100 ),
  salary DOUBLE ( 15, 2 ),
  office INT,
  depart_id INT 
);


-- 插入数据
-- 管理部
INSERT INTO emp ( emp_name, sex, age, hire_date, post, salary, office, depart_id )
VALUES
  ( 'huahua', 'male', 18, '20170301', 'teacher', 7300.33, 401, 1 ),
  ( 'weiwei', 'male', 78, '20150302', 'teacher', 10000000.31, 401, 1 ),
  ( 'lala', 'male', 81, '20130305', 'teacher', 8300, 401, 1 ),
  ( 'zhangsan', 'male', 16, '20170301', 'teacher', 3300, 401, 1 ),
  ( 'liulaogen', 'male', 35, '20180506', 'teacher', 2100, 401, 1 ),
  ( 'aal', 'female', 33, '20251015', 'teacher', 8300, 401, 1 ),
  ( 'zhugeliang', 'male', 25, '20090623', 'teacher', 30000, 401, 1 ),
-- 以下是运维部
  ( '歪歪', 'female', 48, '20150501', 'sale', 2300.13, 402, 2 ),
  ( '丫丫', 'female', 38, '20110311', 'sale', 4300.35, 402, 2 ),
  ( '梅梅', 'female', 18, '20160311', 'sale', 1300.25, 402, 2 ),
  ( '丁丁', 'female', 28, '20220311', 'sale', 3300.65, 402, 2 ),
-- 以下是技术部
  ( '七七', 'female', 38, '20120311', 'operation', 10300.65, 403, 3 ),
  ( '卡卡', 'male', 20, '20180331', 'operation', 11000.65, 403, 3 ),
  ( '程翔', 'female', 30, '20150321', 'operation', 11000.65, 403, 3 ),
  ( '采薇', 'male', 18, '20140611', 'operation', 11000.65, 403, 3 ),
  ( '玫瑰', 'female', 18, '20130312', 'operation', 11000.65, 403, 3 );

简单查询

SELECT * FROM  emp;


-- 指定字段查询
SELECT emp_name, salary FROM emp;


-- 去重查询
SELECT DISTINCT post FROM emp;


-- 拼接字符串
SELECT concat( "姓名：", emp_name, "年龄：", age ) AS 信息 FROM emp;


-- concat_ws() 第一个参数为分隔符
SELECT concat_ws( ":", emp_name, salary ) as 信息 FROM emp;


-- 条件判断case when
SELECT emp_name, salary,
CASE    
    WHEN salary >= 10000 THEN
    "高级技工" 
    WHEN salary >= 5000 THEN
    "中级技工" ELSE "初级技工" 
  END AS 职级 
FROM emp;


-- case when 结合聚合函数
SELECT office,count( CASE WHEN salary >= 10000 THEN emp_name ELSE NULL END ) AS 人数 FROM emp GROUP BY office;


-- where表达，二者区别
SELECT office, count( emp_name ) AS 人数 FROM emp WHERE salary >= 10000 GROUP BY office;

where条件筛选

• 比较运算符：> < >= <= <> !=
• between...and
• in("a","b","c")是否属于括号内的元素，满足其中一个就可以
• like ”%好“ 通配符： %表示任意字符(0个到n个)， _表示一个字符
• 逻辑运算符and or not：多条件时使用

-- 男员工的相关信息
SELECT * FROM emp WHERE sex = 'male';


-- 薪资大于等于1万，部门为管理部的员工信息
SELECT * FROM emp WHERE salary >= 10000 AND office = 401;


-- between...and 都是闭区间
SELECT * FROM emp WHERE salary BETWEEN 1000.37 AND 3500;


-- like 模糊查询 %表示任意字符，_表示一个字符
SELECT * FROM emp WHERE  emp_name LIKE '%h%';
SELECT * FROM emp WHERE  emp_name LIKE '_h%';


-- in/not in
SELECT * FROM emp WHERE  office IN ( 401, 402 );


-- is null/ is not null
SELECT * FROM emp WHERE  office IS NOT NULL;

group by 分组

-- 每个部门的员工人数，分组字段于查询字段要保持一致
SELECT office,count( emp_name ) AS 员工个数 FROM emp GROUP BY office;


-- group_concat
SELECT office,group_concat( emp_name ) AS 员工个数 FROM emp GROUP BY office;


-- 常用聚合函数
count() 计数
sum() 求和
avg() 求平均
max() 最大值
min() 最小值

having分组后过滤条件

-- 查找大于5个员工数以上的部门
SELECT office, count( emp_name ) AS 员工个数 FROM emp GROUP BY office HAVING count( emp_name )> 5;

order by排序

-- 排序，默认升序ASC，降序DESC
SELECT * FROM emp ORDER BY salary;


-- 多列排序，字段越靠前，优先级越高
SELECT * FROM emp ORDER BY post,salary DESC;

limit

m,n m从第几条开始，n显示前多少条，m若等于0则默认不写

-- 从0开始，显示前三 select  from emp limit 3;-- 从0开始，显示前三
SELECT * FROM emp LIMIT 3;


-- 从第2条开始，显示三条
SELECT * FROM emp LIMIT 2, 3;


-- 从第2条开始，显示三条
SELECT * FROM emp LIMIT 2, 3;

记录的多表查询

笛卡尔积：交叉连接把表合并，没有其他操作

SELECT * FROM emp_new,dep;

内连接：join

SELECT e.name,e.sex,e.age,d.name 部门名称 FROM emp_new AS e
JOIN dep AS d ON e.dep_id = d.id;

左连接：left join

SELECT
  e.NAME,
  e.sex,
  e.age,
  d.NAME AS 部门名称 
FROM
  emp_new AS e
  LEFT JOIN dep AS d ON e.dep_id = d.id;

右连接: right join

SELECT
  e.NAME,
  e.sex,
  e.age,
  d.NAME AS 部门名称 
FROM
  emp_new AS e
  RIGHT JOIN dep AS d ON e.dep_id = d.id;

全外连接: union/union all

SELECT
  e.NAME,
  e.sex,
  e.age,
  d.NAME 部门名称 
FROM
  emp_new AS e
  LEFT JOIN dep AS d ON e.dep_id = d.id UNION
SELECT
  e.NAME,
  e.sex,
  e.age,
  d.NAME 部门名称 
FROM
  emp_new AS e
  RIGHT JOIN dep AS d ON e.dep_id = d.id;

子查询: in/not in/exits

1. 子查询是将一个查询语句嵌套在另一个查询语句中
2. 内层查询语句的查询结果，可以为外层查询语句提供查询条件
3. 子查询中可以包含：in、not in、exists、not exists等关键字
4. 还可以包含比较运算符：=、!=、>、<等

-- 查询平均年龄在25岁以上的部门名
SELECT NAME FROM dep WHERE id IN ( SELECT dep_id FROM emp_new GROUP BY dep_id HAVING avg ( age )>25 );
  
-- 查询技术部员工姓名
SELECT id,NAME FROM  emp_new WHERE dep_id = ( SELECT id FROM dep WHERE NAME = "技术" );
  
-- 联系方式
SELECT emp_new.id,emp_new.NAME,dep.NAME FROM emp_new
JOIN ( SELECT id, NAME FROM dep WHERE NAME = "技术" ) dep ON emp_new.dep_id = dep.id;
  
-- 查询不足1人的部门名（子查询得到的是有人部门的id）--》换句话说就是查询没有人的部门
SELECT id,NAME FROM dep WHERE id NOT IN ( SELECT dep_id FROM emp_new GROUP BY dep_id HAVING count( id )>= 1 );
  
-- dep表中是否存在dep_id=203
SELECT * FROM emp_new WHERE EXISTS ( SELECT id FROM dep WHERE id = 203 );

示例数据

CREATE TABLE dep ( id INT PRIMARY KEY, NAME CHAR ( 20 ) );
CREATE TABLE emp_new (
  id INT PRIMARY KEY auto_increment,
  NAME CHAR ( 20 ),
  sex enum ( "male", "female" ) NOT NULL DEFAULT "male",
  age INT,
  dep_id INT 
);-- 插入数据
INSERT INTO dep
VALUES
  ( 200, '技术' ), ( 201, '人力资源' ), ( 202, '销售' ), ( 203, '运营' );
INSERT INTO emp_new ( NAME, sex, age, dep_id )
VALUES
  ( 'ailsa', 'male', 18, 200 ),
  ( 'lala', 'female', 48, 201 ),
  ( 'huahua', 'male', 38, 201 ),
  ( 'zhangsan', 'female', 28, 202 ),
  ( 'lisi', 'male', 18, 200 ),
  ( 'shenteng', 'female', 38, 204 );

来自: 学习MySQL（四）：记录的增删改查