MySQL表的增加、查询的基础操作

一、CRUD

• 注释：在SQL中可以使用“–空格+描述”来表示注释说明
• CRUD : Create（新增数据）、Retrieve（查询数据）、Update（修改数据）、Delete（删除数据）

二、新增数据

• 虽然新增是create，但在sql中使用的关键字是insert

-- into可以省略
insert into 表名 values(值,值...);
-- 其中值的数量、含义、类型都要和定义的表头的结构相匹配

这里首先创建一张学生表

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

下面有两种插入方式

2.1 单行数据 + 全列插入

insert into student values(001,'张三');
insert into student values(002,'李四');

sql中使用单引号也可以表示字符串

2.2 多行数据 + 指定列插入

多行全列插入

insert into student(id,name)values(003,'王五'),(004,'张麻子');

• 注：SQL是一种“弱类型（比较支持隐式类型转换的语言）”的编程语言，和Java这种“强类型”的编程语言是不一样的

以下类型并未与表头相匹配，但运行成功了 字符串’100’自动转为整形100

• 静态类型：一个变量在程序运行中不允许发生改变，这个类型就是静态类型
• 动态类型：一个变量在程序运行中允许发生改变，这个类型就是动态类型

多行指定列插入
插入过程中，可以指定其中部分列插入，未插入部分默认为NULL（相当于啥都没填）

insert into student(name)values('王六'),('张七');

一次插入n条数据要比一次插入1条数据，插入n次效率要高
MySQL是一个客户端-服务器结构的程序
客户端中执行的每个操作，都会通过网络请求发送给服务器，服务器返回一个响应内容

注：当插入内容是时间时，要注意格式：年-月-日 时:分:秒
有时候需要插入当前的系统时间，MySQL提供了专门的函数now()来获取

insert into test1 values(now());

三、查询

在增删改查中，查询是最复杂的

3.1 全列查询

select * from 表名;

*为通配符，表示所有
能够查询表中所有行和列

select *也是一个危险操作，当数据库体量很大时，当进行select * 时，服务器会进行大量的硬盘IO操作，同时把得到的结果通过网络返回，触发大量的网络IO操作；但硬盘和网卡同一时刻处理的数据量是有限的，所以就可能会导致卡顿，有可能引发bug

3.2 指定列查询

select 列名,列名... from 表名;

3.3 表达式查询

表达式查询：查询的不仅仅是列，而是可以把列带入表达式中进行运算
准备以下数据

INSERT INTO student1 (id,name, chinese, math, english) VALUES(1,'唐三藏', 67, 98, 56),(2,'孙悟空', 87.5, 78, 77),(3,'猪悟能', 88, 98.5, 90),(4,'曹孟德', 82, 84, 67),(5,'刘玄德', 55.5, 85, 45),(6,'孙权', 70, 73, 78.5),(7,'宋公明', 75, 65, 30);

执行下列代码

select name,math,math+10 from student1;

可以求和

select name,math+chinese+english from student1;

可以看出，查询过程中可以展现出运算后的结果，这就是表达式查询
注意：MySQL是一个“客户端-服务器”结果的程序，此时的数据是存储在服务器硬盘上的，上述表达式查询，只是针对服务器响应临时得到的结果进行计算，不影响硬盘上的数据本体
select所有的用法都不会影响服务器硬盘上存储的数据本体
一般不建议在这里进行太复杂的运算，对于数据来说，本身就容易成为性能瓶颈

在上述表达式查询中可以发现，表头和表达式一样，当表达式比较复杂时，表头就不是很直观，可以通过使用“别名”来解决这一问题

语法：

//as可以省略
select 表达式 as 别名 from 表名;

3.4 去重：distinct

去重查询：针对查询结果，进行去重，存在重复数据，就会把重复数据合并为1行
语法：

select distinct 列名 from 表名;

通过查询发现，存在两个65的数据

使用distinct去重查询发现少了一行重复的65

当你指定多个列时，只有多个列的某些行都相同时才算重复，如马超和宋公明的math和english算重复，chinese和math不算重复

3.5 带有排序的查询：order by

select查询的结果默认是无序的，想要使其有序可以使用 order by 关键字
asc 为升序（从小到大）
desc 为降序（从大到小）
order by默认为 asc
注：desc表名是describe的缩写，desc降序是descend的缩写
语法：

select ... from 表名 order by 列名/表达式[asc/desc];

按照math降序展示

NULL在order by视为最小值，如果存在多个NULL，多个NULL的排序是不确定的
在排序过程中，当一个列中存在相同的值，在相同的这两个值当中排序规则可以使用order by根据其他列排序，语法如下：

select ... from 表名 order by 列名[desc],列名[desc]...;

先按照第一列排序，第一列有相同的部分，对这些部分再根据第二列排序…

3.6 条件查询：where（重要&常用）

比较运算符

条件运算符

具体案例：
student1表内容如下：

1. 查询chinese值大于等于70的信息

 select * from student1 where chinese>=70;

2. 查询总分大于260分的信息，并且按照总分降序排序
   错误写法：
   
   因为where加载的时候别名还未加载，所以识别不出别名
   SQL语句执行顺序：1. 遍历表 2. 带入条件 3. 计算列名中的表达式（定义别名） 4. 排序/聚合 等操作

 select name,chinese+math+english as total from student1 where chinese+math+english >200 order by total desc;

注：order by要写在where后面

3. 查询数学成绩比语文成绩好的同学

select * from student1 where math>chinese;

理解上述带有where语句的select执行过程：
数据库收到SQL语句时，就会遍历表中每一个数据（行），取出当前行，带入到条件当中，如果条件成立，那么这一行数据就会进入结果集合中，如果不成立，那么跳过，进入下一条语句
上述条件可以理解为按照条件进行筛选，因此，select本身就是选择

4. 观察and和or的优先级

SELECT * FROM student1 WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM student1 WHERE (chinese > 80 or math>70) and english > 70;

通过上述结果可以观察到，and优先级大于or
建议多写()来区分，因为当代码复杂时光靠默认优先级来判断任意混乱

5. 查询语文成绩在 [80, 90] 分的同学及语文成绩

select * from student1 where chinese between 80 and 90;

也可以使用and来实现

select * from student1 where chinese>=80 and chinese<=90;

6. 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

select * from student1 where math in(58,59,98,99);

in描述的离散的集合，而between描述的是连续的区间
使用 OR 也可以实现

SELECT name, math FROM student1 WHERE math = 58 OR math = 59 OR math 
= 98 OR math = 99;

7. 模糊查询：LIKE

% 匹配任意多个（包括 0 个）字符
孙%–匹配孙开头的内容；%孙–匹配孙结尾的内容；%孙%–匹配包含孙的内容

SELECT name FROM student1 WHERE name LIKE '孙%';

_ 匹配严格的一个任意字符

SELECT name FROM student1 WHERE name LIKE '孙_';

like这种模糊匹配，性能开销比较大，慎用

8. NULL 的查询：IS [NOT] NULL
   语法：

--查询列名未知的信息
select ... from 表名 where 列名 is null;
--查询列名已知的信息
select ... from 表名 where 列名 is not null;

3.7 分页查询 limit

有时候虽然是进行条件查询，但是查询出来的结果仍然会非常多，如果把内容全部展示出来，也不一定合适（如在浏览器搜索某个内容，不进行分页的话会非常长，不方便浏览，并且对浏览器和网络都有一定的压力），这时候需要分页查询来解决
limit关键字可以限制返回内容个数
如下：对学生表进行limit限制，返回前n条信息

搭配offset关键字

--在表中，按照排列顺序，从下标n开始，往后查找m行元素
select * from 表名 limit m offset n;

offset称为偏移量，相当于下标，不写offset相当于offset 0


写法2：

--从下标m开始，往后查n条数据
select * from 表名 limit m,n;

题目：查询总成绩前三的同学

select id,name,chinese+math+english as total from student1 order by total desc limit 3;