MySQL从建库到删库跑路 -- 7.CRUD

7 CRUD

7.1 create(新建操作)

• 单行插入

-- test create

create table test_crud.test_create
(
        id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        name varchar(64) UNIQUE comment '名字,不可重复',
        gender enum('未知', '男', '女') DEFAULT '未知' comment '性别',
        age int UNSIGNED NOT NULL comment '年龄',
        phone_number varchar(30) NOT NULL UNIQUE comment '电话号码,不能重复,不能不填'
);

insert into test_crud.test_create 
values 
(
        1000,
        '张三', 
        '男',
        12, 
        '18888888888'
);

insert into test_crud.test_create(name, gender, age, phone_number) 
values 
(
        '李四', 
        '女',
        11, 
        '19988888888'
);

select * from test_crud.test_create;

• 连续插入

insert into test_crud.test_create(name, gender, age, phone_number) 
values 
        ('王五', '女', 18, '11188888888'), 
        ('赵六', '男', 20, '12388888888');

select * from test_crud.test_create;

• 这个事件触发器可以用于处理一个复杂场景,即如果插入的行主键或者唯一键在表中已经存在了,那么将需要替换的部分做替换

ON DUPLICATE KEY UPDATE col = value [col = value];

insert into test_crud.test_create(name, gender, age, phone_number) 
values 
        ('王五', '女', 18, '11188888888'); 

-- 如果能够插入,主键和唯一键没冲突,那就直接插入
-- 如果主键和唯一键有冲突,那么将需要替换的部分做替换(值覆写)

insert into test_crud.test_create(name, gender, age, phone_number) 
values 
        ('王五', '女', 23, '11188888888')
        ON DUPLICATE KEY UPDATE age = 23;

select * from test_crud.test_create;

• 注意这里事件发生器的效果

• 这是替换,效果其实和前面提到的事件发生器实现的效果差不多,但是有一个问题,这里的替换是整行替换

REPLACE INTO test_crud.test_create(name, gender, age, phone_number)
values 
        ('王五', '女', 25, '11188888888');

select * from test_crud.test_create;

• 你会发现这里id自增了,所以说,替换其实是两个步骤,首先删掉重复行,然后新插一个行,那么会导致id被迫自增1,无辜消耗一个id是不太好的行为,如果id本身就不太够用了,这么做可能会有一些问题

7.2 retrieve(查询操作)

7.2.1 基本查询

SELECT 
	[DISTINCT] {* | {column [, column] ...} 
	[FROM table_name] 
	[WHERE ...] 
	[ORDER BY column [ASC | DESC], ...] 
	LIMIT ...

create table test_crud.test_retrieve
(
        id int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        name varchar(64) UNIQUE comment '名字,不可重复',
        gender enum('未知', '男', '女') DEFAULT '未知' comment '性别',
        age int UNSIGNED NOT NULL comment '年龄',
        phone_number varchar(30) NOT NULL UNIQUE comment '电话号码,不能重复,不能不填'
);

insert into test_crud.test_retrieve(name, gender, age, phone_number) 
values
        ('张三', '男', 12, '18888888888'),
        ('李四', '女', 11, '19988888888'),
        ('王五', '女', 18, '11188888888'), 
        ('赵六', '男', 20, '12388888888');

• 整列查询

select * from test_crud.test_retrieve;

• 选择列查询

select id, name, phone_number from test_crud.test_retrieve;

• 含有表达式的查询

select id, name, age > 22 from test_crud.test_retrieve;

• 可以给列取别名

select id, name, age > 22 AS 比我大吗 from test_crud.test_retrieve;

• 可以用DISTINCT给表做去重

select DISTINCT gender from test_crud.test_retrieve;

7.2.2 WHERE

7.2.2.1 相关操作符与关键字

• 支持常用的算术比较: =, <>, <, >, <=, >=, !=, <=>

不等于是一个特殊情况,MySQL等大部分数据库是支持!=的,但是并不是标准的"不等于"的运算符,标准"不等于"是<>,"等于"是=而不是== 因为NULL和任何值比较都是NULL,所以NULL = NULL的结果是NULL,那么<=>这个操作符是NULL安全的,比方说NULL <=> NULL的结果是TRUE

• 支持逻辑相关的关键字: AND, OR, NOT

可以类比于CPP的&&, ||, !

• 支持范围和集合: BETWEEN ... AND ..., IN (...), IS NULL, IS NOT NULL

BETWEEN ... AND ...用于表示在两个数值之间,比方说BETWEEN 15 AND 30 IN (...)用于表示在一个集合里,比方说IN ('张三', '李四', '王五', '赵六') IS NULL, IS NOT NULL用于空值的判定,空值不等于任何值,空值甚至不等于空值自己

• 支持模糊匹配: LIKE 'char_', LIKE 'char%'

用于支持字符串的模糊查找,比方说我要找性李的,就可以用LIKE '李%',我要找性李且名字是三个字的,就可以用LIKE '李__' 注意,查找%四这种的,因为B+树排序是按照首字母顺序排序的,所以肯定要知道首字母才能快快地找,直接不给首字母,那就只能全盘扫了,二级索引也救不回来(后面我们可能会聊到吧,可能吧)

7.2.2.2 场景

• 这里我搞了一个这个表

• 基本上常用的我都会测一下

• 查一下年龄小于20岁的人的名字

select name, age from test_crud.test_retrieve where age < 20;

• 查一下年龄大于20岁且小于30岁的人的名字和手机号

select name, age, phone_number from test_crud.test_retrieve where age BETWEEN 20 AND 30;

• 查一下年龄小于等于22的人且性别为女的名字和手机号

select name, gender, age, phone_number from test_crud.test_retrieve where age <= 22 AND gender = '女';

• 查一下名字在('张三','李四','王五','赵六')这个列表中的人的性别

select name, gender from test_crud.test_retrieve where name IN ('张三', '李四', '王五', '赵六');

• 查一下手机号不为空的人

select name, phone_number from test_crud.test_retrieve where phone_number IS NOT NULL;

• 查一下姓王的人

select * from test_crud.test_retrieve where name LIKE '王%';

• 查一下姓王且名字是两个字的人

select * from test_crud.test_retrieve where name LIKE '王_';

• 查一下姓王且性别是女的人,和手机号8888结尾的人

select * from test_crud.test_retrieve where (name LIKE '王%' AND gender = '女') OR (phone_number LIKE '%8888');

7.2.2.3 分页显示

• 这个命令可以限制查询显示的区间,n为限制的行数,s为偏移量

select ... from table_name [where ...] order by ... limit n offset s;

• 从第四行开始,显示三行

select * from test_crud.test_retrieve order by id limit 3 offset 3;

7.3 update(更新操作)

• 一样是这个表

• 单行单列更新

select * from test_crud.test_update where name = '王圆圆';

UPDATE test_crud.test_update SET age = 18 where name = '王圆圆'; 

select * from test_crud.test_update where name = '王圆圆';

• 单行多列更新

UPDATE test_crud.test_update SET age = 28, gender = '男' where name = '王圆圆'; 

select * from test_crud.test_update where name = '王圆圆';

• 自增

UPDATE test_crud.test_update SET age = age + 10 where name = '王圆圆'; 

select * from test_crud.test_update where name = '王圆圆';

• 多列更新

select * from test_crud.test_update;

UPDATE test_crud.test_update SET age = age + 1; 

select * from test_crud.test_update;

7.4 delete(删除操作)

• 依旧同样的表

• 指定行删除

delete from test_crud.test_delete where name = '王圆圆';

select * from test_crud.test_delete;

• 多行删除,比方说这里就是删除年龄最小的三个人

delete from test_crud.test_delete order by age limit 3;

select * from test_crud.test_delete;

• 多行删除,删除女生

delete from test_crud.test_delete where gender = '女';

select * from test_crud.test_delete;

• 整表删除(谨慎操作)

delete from test_crud.test_delete;

select * from test_crud.test_delete;

• 重新插值,你会发现auto_increment仍然在之前的基础上自增了

insert into test_crud.test_delete(name, gender, age, phone_number) 
values 
        ('lee', '男', 5, '15287650987');

select * from test_crud.test_delete;

show create table test_crud.test_delete;

• 表截断操作

这个操作不可在事务中回滚,所以要谨慎使用

truncate table test_crud.test_delete;

select * from test_crud.test_delete;

insert into test_crud.test_delete(name, gender, age, phone_number) 
values 
        ('lee', '男', 5, '15287650987');

select * from test_crud.test_delete;

show create table test_crud.test_delete;

• 你会发现截断表除了不会删除表本身之外,会一并清空表的所有数据,区别于delete的整表清楚,截断的速度很快,因为它不会一条条清除,而是一整个全部清除,所以无法做回滚,同时也因此,效率非常高,因为是整个都清除了,所以auto_increment也会重置

7.5 插入查询行

• 依旧经典表

• 比方说我要把这个表按照男女性别分到另外两个表里

-- 按照相同的建表方式建出男女两个表
create table test_crud.table_male like test_crud.test_insert_with_select;

create table test_crud.table_female like test_crud.test_insert_with_select;

show create table test_crud.table_male;

show create table test_crud.table_female;

-- 查男,并插入新表
insert into test_crud.table_male select * from test_crud.test_insert_with_select where gender = '男';
-- 查女,并插入新表
insert into test_crud.table_female select * from test_crud.test_insert_with_select where gender = '女';

select * from test_crud.table_male;

select * from test_crud.table_female;

7.6 聚合函数

• 典

-- 统计行数(元素个数)
select count(*) from test_crud.test_aggre_func;
-- 统计年龄的和
select sum(age) from test_crud.test_aggre_func;
-- 计算平均年龄
select avg(age) from test_crud.test_aggre_func;
-- 挑出最大年龄
select max(age) from test_crud.test_aggre_func;
-- 挑出最小年龄
select min(age) from test_crud.test_aggre_func;

7.7 group by

SELECT col1, col2, ... from table_name GROUP BY col3, col4, ...;

• 按照group by后的列进行归类后,再对每个归好的类(也是个表)进行select操作

• 先搞一个很大的表(这是Oracle的一个经典测试表)

DROP TABLE IF EXISTS test_crud.EMP;
DROP TABLE IF EXISTS test_crud.DEPT;
DROP TABLE IF EXISTS test_crud.SALGRADE;

-- 创建部门表 (DEPT)
CREATE TABLE test_crud.DEPT (
    DEPTNO INT(2) PRIMARY KEY, -- 部门编号
    DNAME VARCHAR(14),         -- 部门名称
    LOC VARCHAR(13)            -- 所在地
);

-- 创建员工表 (EMP)
CREATE TABLE test_crud.EMP (
    EMPNO INT(4) PRIMARY KEY,  -- 员工编号
    ENAME VARCHAR(10),         -- 姓名
    JOB VARCHAR(9),            -- 职位
    MGR INT(4),                -- 上级经理编号
    HIREDATE DATE,             -- 入职日期
    SAL DECIMAL(7,2),          -- 工资
    COMM DECIMAL(7,2),         -- 奖金/提成
    DEPTNO INT(2),             -- 部门编号
    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES test_crud.DEPT(DEPTNO)
);

-- 创建工资等级表 (SALGRADE)
CREATE TABLE test_crud.SALGRADE (
    GRADE INT,                 -- 等级
    LOSAL INT,                 -- 最低工资
    HISAL INT                  -- 最高工资
);

-- 插入部门数据
INSERT INTO test_crud.DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO test_crud.DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO test_crud.DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO test_crud.DEPT VALUES (40,'OPERATIONS','BOSTON');

-- 插入员工数据
INSERT INTO test_crud.EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO test_crud.EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO test_crud.EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO test_crud.EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO test_crud.EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO test_crud.EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO test_crud.EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO test_crud.EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO test_crud.EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO test_crud.EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO test_crud.EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO test_crud.EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO test_crud.EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO test_crud.EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

-- 插入等级数据
INSERT INTO test_crud.SALGRADE VALUES (1,700,1200);
INSERT INTO test_crud.SALGRADE VALUES (2,1201,1400);
INSERT INTO test_crud.SALGRADE VALUES (3,1401,2000);
INSERT INTO test_crud.SALGRADE VALUES (4,2001,3000);
INSERT INTO test_crud.SALGRADE VALUES (5,3001,9999);

select * from test_crud.DEPT;
select * from test_crud.EMP;
select * from test_crud.SALGRADE;

• 每个部门的平均工资和最高工资

select DEPTNO, avg(SAL), max(SAL) from test_crud.EMP group by DEPTNO;

• 每个部门每个岗位的平均工资和最低工资

select DEPTNO, JOB, avg(SAL), min(SAL) from test_crud.EMP group by DEPTNO, JOB;