MySQL学习（二）DML基础之C&R

目录

 1.Create 新增

1.1 单行+全列

1.2 多行+全列

1.3 单行+指定列

1.4 多行+指定列

1.5其他注意事项

2.Retrieve 查找

2.1 查找表中所有数据（轻易不要使用，以防数据过大）

2.2 指定列查询

2.3 排序 

2.4 分页查找

 2.5 distinct 关键字用于去重

 2.6 where条件查询

---

【前言】DML ——Data Manipulation Language，数据操控语言，主要就是增删查改语句

数据准备，先建一张表student表，表内字段有：主键id，学号sn，姓名name，QQ邮箱qq_mail

 1.Create 新增

数据准备，先建一张表student表，表内字段有：主键id，学号sn，姓名name，QQ邮箱qq_mail

以下增加操作我们在这张表里进行增删查改操作

DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT,
    sn INT comment '学号',
    name VARCHAR(20) comment '姓名',
    qq_mail VARCHAR(20) comment 'QQ邮箱'
);

1.1 单行+全列

-- values 中的值的顺序，必须严格按照建表时的顺序
INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);

1.2 多行+全列

无别的写法，只能重复上述单行操作

INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

1.3 单行+指定列

-- 插入单行+指定列，即id列和sn列
insert into student (id,sn) values (103,67899);

1.4 多行+指定列

-- 插入多行+指定列,多行可直接用逗号分割
insert into student (id,sn) values 
(103,67899),
(104,343646),
(105,333333);

1.5其他注意事项

• 日期格式

-- 带一个日期格式的字段的插入 
insert into student 
	values (103, 3001, 'xxx', null, '2022-03-18 19:36:27', '2000-12-12');

• null

  -- 没有出现的字段，填充默认值
  -- 如果缺失的字段没有默认值怎么办？会填充为Null
  INSERT INTO student (id, sn, name) VALUES 
  	(102, 20001, '曹孟德');
      select * from student;

   

2.Retrieve 查找

数据准备：

 -- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
	id INT,
	name VARCHAR(20),
	chinese DECIMAL(3,1),
	math DECIMAL(3,1),
	english DECIMAL(3,1)
);

-- 插入测试数据（刚学过的多行+指定列插入）
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
	(1,'唐三藏', 67, 98, 56),
	(2,'孙悟空', 87.5, 78, 77),
	(3,'猪悟能', 88, 98, 90),
	(4,'曹孟德', 82, 84, 67),
	(5,'刘玄德', 55.5, 85, 45),
	(6,'孙权', 70, 73, 78.5),
	(7,'宋公明', 75, 65, 30);

2.1 查找表中所有数据（轻易不要使用，以防数据过大）

    -- 查找所有数据
    select * from exam_result;

查找结果：

2.2 指定列查询

（1）查找指定字段

    -- 查找id和name
select name from exam_result;

（2）select后可跟表达式，返回表达式的列

-- select后面跟的可以是一个表达式，如查找id *10的结果
select id * 10 from exam_result;

 

 （3）查找任意一个数字结果会是什么呢

-- 查找数字，也会返回，有几行返回几行
select 105 - 200 from exam_result;

（4） select后跟布尔表达式，查找结果里，0为fase,1为true

--  查找语文成绩大于80的
select name,chinese > 80 from exam_result;

 (5)as + 别名，其中as也可省略

-- 将表达式表达的 english + chinese + math 起别名为 总分
select id, name, english + math + chinese as 总分 from exam_result;
select id, name, english + math + chinese 总分 from exam_result;

 （6）关于Null值

-- 有 null 参与的运算结果还是 null
-- 即使是 null，做bool值的时候，也看作 false
select qq_mail = '111' from student;
select qq_mail + 10 from student;

 

-- =null 的表达式，结果肯定全部为null，因为Null表示不知道指向，故而 null 跟 null 也不知道是否相等
select qq_mail = null from student;

 

2.3排序 

• order by asc 升序排列，其中asc可省略不写
• order by desc 降序排列
• 可指定某字段为第一顺序，当第一顺序相等时，可定义按第二个字段排列，两个字段的升降序互不干扰（不止两个，可自定义多个字段）

-- 排序
-- order（排序）by（按照哪个字段、表达式）
-- asc : ascend（升序），不写默认是升序
-- desc : descend（降序）
-- 可以指定指定多个排序字段，是前面相等的情况下，产生意义
-- 不写 order by 的结果集，一律认为无序
-- 下面这句的意思是按数学升序排列，如果数学一样，则按照语文降序排列，如果语文还一样，再按照英语升序排列
select * from exam_result order by math, chinese desc, english;

 

2.4 分页查找

• limit 限制每页几个结果
• offset 限制起始位置，从下标0开始计算
• limit n 默认从0开始，取前n条
• limit s n 从s开始，取n条 不建议使用，不明晰意思
• limit n offset s 从s开始取n条，建议使用
• 一般分页的前提是先有序，不然没啥意义

为便于比较查到的是不是结果，我们先将全表输出看看：

-- 先结果集有序
select * from exam_result order by math;

 

 接着进行分页查询：

-- 从0开始取前2条
select * from exam_result order by math limit 2;
-- 从下标3开始取2条
select * from exam_result order by math limit 2 offset 3;
-- 从下标3开始取2条
select * from exam_result order by math, id limit 3, 2;

查询结果依次是：

第一和第二

 第四和第五

 第四和第五

 2.5 distinct 关键字用于去重

-- 完整数据
select math from exam_result;
-- 去重
select distinct  math from exam_result;

结果：

 2.6 where条件查询

（1）运算表达式基本查询

select id, name, math, math > 90 from exam_result;
-- where 条件,就是把上面一行查询结果为1的挑选出来
select id, name, math from exam_result where math > 90;

 

 （2）关于null

对于一般的运算表达式，是无法运算Null值的，所以用=连接去运算Null结果仍为null，且对于null值默认为false

-- null 在 bool 中视为 false，所以下面这句什么也查不到
select * from student where qq_mail = null;

 

 故而，对于Null值的布尔判断，可以用以下两种方法：

• <=>

• is null
• is not null
•  

-- <=> 对于Null敏感
select * from student where qq_mail <=> null;
-- = 不敏感
select * from student where qq_mail = null;
-- 用 is null
select * from student where qq_mail is null;
-- 用 is not null
select * from student where qq_mail is not null;

 结果依次为：

查出所有null

 查不到任何东东

 查到所有null

 查到所有不为null

 

 （3）范围查询

• in

select * from exam_result where name in ('孙悟空', '唐三藏', '猪悟能');

• between ……and ……

select * from exam_result where math between 60 and 65;

(4) 模糊查询 like

模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

-- 孙开头
select name from exam_result where name like '孙%' ;
-- 孙结尾
select name from exam_result where  name like '%孙';
-- 只要有孙
select name from exam_result where name like '%孙%';
-- 两个字，孙开头
select name from exam_result where name like '孙_';