MySQL表的基础操作(crud)

1. 新增（Create）

insert into 表名 values (值, 值…);
此处列出的这些值,的数目和类型要和表的列相匹配。

-- 在student 表中插入学号1，姓名zhangsan的数据
insert into student values(1, 'zhangsan');
-- 指定列插入
insert into student (name) values('王五');
-- 插入多个数据
insert into student values('2','李四'),('3','赵六'),('4','刘七');

2. 查询（Retrieve）

查询语句让我们能看到表中包含的数据内容。

1) 全列查询

这个操作,就是查询出当前数据库中所有的行,和所有的列。

select * from 表名

符号 * 是通配符。 * 就能够代指任意的列。

-- 全列查询 student 表  
select * from student;

MySQL 是一个 客户端 服务器 结构的程序。
客户端 发送 select * from student请求。服务器解析 sql 并执行就会读取硬盘中 student 表的每一条记录把所有的记录都通过网络返回给客户端。然后客户端再把收到的服务器返回的数据显示到界面上。

select * 是一个非常危险的操作。如果,当前 select*查询的这个表,里面的数据非常非常多(比如,几十亿条）。这就会使服务器无法给外面的普通用户提供服务了,此时服务器就像"卡死”了一样，导致这个服务器一瞬间硬盘的带宽和网卡的带宽就都被吃满了。

2) 指定列查询

手动指定要查询的某一列或者某几列。服务器返回的结果,就只包含想要的数据。

select 列名,列名… from 表名;

-- 创建一个成绩表
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.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30),
(8,'诸葛孔明',80.0,80.0,80.0),
(9,'孙行者',null,null,null),
(10,'者行孙',null,null,null),
(11,'行者孙',null,null,null);

 -- 查询语文成绩select name,chinese from exam_result;

未来实际开发中,一张表有十几列都是很正常的. 通常就得按需查询。数据库的增删改查,都是比较慢的，也是比较吃硬件资源的能省点尽量省点)。

3) 查询字段为表达式

查询的同时,可以进行计算.

-- 查询所有同学,数学成绩加10分后的效果
select name ,math + 10 from  exam_result;

MySQL 是一个 客户端 服务器 结构的程序。查询字段为表达式的时候，看到的客户端中显示的结果其实是一个“临时表select 操作不管怎么写,都不会影响到数据库服务器硬盘上存储的原始数据。

也可以使用两个列/多个列进行运算：

-- 查询总成绩
select name,chinese + math + english from exam_result;

查询结果临时表的列名和咱们当前表达式是一致的。万一查询的表达式非常复杂,就会导致列名也同样复杂不利于用户来阅读。

为了解决上面的问题，可以指定别名

4) 查询的时候给列/表达式 指定别名

select 列名 as 别名 from 表名

• 别名会最终显示在查询结果的临时表中
• as 关键字,可以省略.但是不建议省略

-- 查询总成绩起total别名
select name,chinese + math + english as total  from exam_result;

SQL 中,列名 和 表名 都时可以起别名的也都是使用 as

5) 查询时去重

把重复的行去掉,只保留一份。
select distinct 列名 from 表名;

-- 查询数学成绩去重
select distinct math from exam_result;

distinct 后面的列名,也可以是多个则要求必须所有的列的值都相同，才算“重复“。

6) 排序查询

针对查询到的结果进行排序.
mysql 是一个客户端-服务器结构的程序。因此这里的排序,也只是针对临时表进行的对于数据库服务器上原始的数据没有任何的顺序上的影响。

select 列名 from 表名 order by 列名 [asc | desc];

列名：排序的时候,依据什么来进行排的。这个是我们在排序的时候要明确的

-- 对英语成绩进行排序(默认升序排序)
select * from exam_result order by english;
-- 对英语成绩进行降序排序
select * from exam_result order by english desc;

desc 表名; 描述一个表

order by desc; 表示降序 descend

如果 SQL 中,没有指定 order by, 此时我们的代码中就不应该依赖结果集合(临时表)的顺序。mysql 并不承诺,这个不带order by 的查询结果是带有一定顺序的。

order by 还能指定多个列排序(order by 后面可以写多个列)

-- 优先数学升序，次之英语
select * from exam_result order by math, english;

7) 指定一个筛选条件

把符合条件的结果保留下来.不符合的就剔除掉。

条件查询需要有运算符来表述。

比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于(两个运算符是等价的)
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字 符

最开始，= 在数学上就是表示相等。咱们开始学编程了之后,费了老大劲,把 = 的含义纠正过来,= 表示赋值
现在在 SQL 里 = 又是表示比较相等了。
= 和 <=> 的差别：有些列是可以不填的(不填就相当于是 NULL)，NULL 参与各种运算,的结果还是 NULL。

• NULL = NULL => NULL(相当于 false,条件不成立了)
• NULL <=> NULL => true(条件成立)

逻辑运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

条件比较的时候,并不只是使用列名和常量比较也可以使用列名和其他列名比较。也可以结合一些更复杂的表达式。

• 基本查询

-- 查询英语不及格的同学及英语成绩 ( < 60 )select name,english from exam_result where english < 60;-- 查询语文成绩好于英语成绩的同学select * from exam_result where chinese > english;-- 查询总分在 200 分以下的同学
select name,chinese + english + math as total from exam_result where  chinese + english + math < 200;
-- 条件里的内容,和查询的这个列,没啥关系

注意理解查询语句执行的过程:
1.服务器需要先遍历表中的每一个记录
2.针对当前记录,带入条件,看是否成立
3.如果条件成立，则这一条记录加入结果集,并返回给客户端如果条件不成立,则这一条记录跳过.

-- 下面的查询总分在 200 分以下的同学语句是错误的
select name,chinese + english + math as total from exam_result where total < 200;
-- 当我们在条件中,尝试使用别名的时候,这个别名不能被正确识别出来。
-- mysql的 where 条件中,无法使用列的别名。

站在 sql 的执行顺序上,也能一定程度的解释上述现象
select 语句执行顺序：

1. 遍历到某个指定的行
2. 带入条件筛选
3. 条件为 true,被筛选成功之后, 再计算 select 列务这里的表达式

这种说法是比较牵强的，实现 SQL 解析是完全可以做到，先把别名定义出来，再执行条件的。

• AND与OR：

-- 查询语文成绩大于80分，且英语成绩大于80分的同学
select name,chinese,english from exam_result where chinese > 80 and english > 80;
-- 查询语文成绩大于80分，或英语成绩大于80分的同学
select name,chinese,english from exam_result where chinese > 80 or english > 80;

在 SQL 中,and 的优先级比 or 更高。但是没啥卵用建议大家忘记这个规则，而是使用()手动的明确优先级。

• 范围查询：

计算机中谈到区间，大部分都是前闭后开。但是也有例外: between and 是闭区间:

1. between … and …

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩select * from exam_result where chinese >= 80 and chinese <= 90;
-- 用 between ... and ... 
select * from exam_result where chinese between 80 and 90;

2. IN

-- 查询数学成绩是 58 或者 80 或者 98 或者 99 分的同学及数学成绩
select * from exam_result where math = 58 or math = 80 or math = 98 or math = 99;
-- 用 in 
select * from exam_result where math in(58,80,98,99);

• 模糊查询：LIKE

mysql 提供的模糊查询,相对比较简单的

• % 匹配 0个或者任意个任意字符
• _ 匹配 1个任意字符

-- 查询姓孙并且名为1个字的同学
select * from exam_result where name like '孙_';
-- 查询姓孙并且名为2个字的同学
select * from exam_result where name like '孙__';
-- 查询姓孙的同学
select * from exam_result where name like '孙%';
-- %孙 匹配以 孙 结尾的
select * from exam_result where name like '%孙';
-- %孙% 则是只要包含孙即可
select * from exam_result where name like '%孙%';

mysql 进行 like 模糊查询是一个比较低效的操作。尤其是针对一些比较长的字符串，实际工作中使用模糊查询还是要慎重。

• NULL 的查询：IS [NOT] NULL

用 = 进行比较将会一个数据也没有所以得需要用其他运算符。

-- 查询语文成绩为null的同学
-- 用 <=>
select * from exam_result where  chinese <=> null;
-- 用 IS NULL  
select * from exam_result where  chinese is null;

<=> 和 is null 的区别是什么：

• <=> 可以拿两个列进行比较的
• is null 只能是一个列和 null 比较

8) 分页查询limit

针对查询出来的结果进行截取，取出其中的一个部分。

语法：

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

练习：

-- 从 0 开始，筛选 4 条结果
select * from exam_result limit 4;
-- 从 4 开始，筛选 4 条结果
select * from exam_result limit 4 offset 4;
-- offset 4 描述了当前的结果,从哪一条开始算这里的 
-- offset 4 就可以当做从下标为4的记录开始获取.(下标从 0 开始算)
-- limit 4  约束了结果中最多包含几个记录-- 从 8 开始，筛选 4 条结果
select * from exam_result limit 4 offset 8;

上述这 8 种 select 的写法，都是 select 最基础的写法也是工作中最常用的写法。

3. 删除 (Delete）

delete from 表名 where 条件;直接删除符合条件的行

-- 删除数学成绩为null的数据
delete from exam_result where math is null;
-- 删除孙悟空同学的考试成绩
delete from exam_result where name = '孙悟空';

删除,是按照行来删除的.无法删除某些列(要想按照列来删除,可以通过 update,把指定条件的行的指定列,设为 null)
alter table 可以修改列，针对所有的行进行的。不能针对有些行进行删除列 (关系型数据库，所有的行的列都得是一致的)
如果在 delete 的时候,没有指定条件,就会把整个表的所有数据都删除掉，效果和删除表就差不多了，这个操作就是一个危险操作 。

4. 修改 (update)

update 表名 set 列名 = 值 where 条件…;

进行修改,要明确一些重要的信息:

• 改哪个表？
• 改这个表里的哪个列/哪些列,改成什么？
• 改这个表的哪些行？

-- 将孙悟空同学的数学成绩变更为 80 分
update exam_result set math = 80 where name = '孙悟空';
-- update 也可以一次操作修改多个列
-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
update exam_result set chinese = 70,math = 60 where name = '曹孟德';
-- 修改操作,也可以搭配 order by 这样的排操作
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
update exam_result set math = math + 10 order by chinese + math + english limit 3;

select 中支持的 条件，排序, 分页，对于 update 来说同样生效的，update 可以理解成先查询，再修改。

update 也是一个危险操作，这个操作的危害和删库相比不遑多让，甚至还犹有过之。删库，所有的数据都没了，能做的操作就是把之前备份的给导入过来。update 修改的条件,没设定好,你也不知道,当前哪些行被改了，哪些没被改!!恢复的成本可能是更高的。

数据库中的危险操作：
drop database
drop table
select *
update
delete

基础增删查改总结

1.insert into 表名 values (值, 值....);
2. select1) select * from 表名2) select 列名,列名... from 表名;3) select 表达式 from 表名4) select 表达式 as 别名 from 表名;5) select distinct 列名 from 表名6) select 列名 from 表名 order by 列名 asc/desc;7) select 列名 from 表名 where 条件;8) select 列名 from 表名 limit N offset M;
3.update 表名 set 列名 = 值 where 条件/order by/limit;
4. delete from 表名 where 条件/order by/limit;