MySQL表的增删改查（基础）

MySQL表的增删改查（基础）

新增（create）

--创建一张学生表
create table student(
	id int,
    sn int,
    name varchar(20),
);

单行数据+全列插入

--插入两条记录，参数列表数量必须和定义表的列的数量及顺序一致
insert [into] student values(100, 1000, "张三");
insert [into] student values(101, 1001, "李四");

多行数据+指定列插入

--插入两条记录，参数列表数量必须和指定列数量及顺序一致
insert student(sn, name) values
	(1002, "王五"), 
	(1003, "刘六");

查询（retrieve）

全列查询

select * from student;

指定列查询

--指定列的顺序不需要按定义表的顺序
select id, sn from student;

查询字段为表达式

--表达式不包含字段
select id, sn, 10 from student;
--表达式包含一个字段
select id, sn, sn + 10 from student;
--表达式包含多个字段
select id, sn, sn + id from student;
--微查询结果中的列指定别名
select id, sn, sn + id si from student;

去重distinct

--使用distinct关键字对某列数据进行去重
select distinct id from student;

排序order by

--查询id和sn，按照id升序显示
select id, sn from student order by id asc;
--使用表达式及别名降序排序
select id, sn + id from student order by sn + id desc;
select id, sn +id si from student order by si desc;
--对多个字段进行排序，排序优先级随书写顺序
select id, sn from student order by sn desc, id; 

分页查询limit

--第一页
select id from student order by id limit 3;
select id from student order by id limit 3 offset 0;
--第二页
select id from student order by id limit 3 offset 3;
--第三页，如果结果不足三个不会影响
select id from student order by id limit 3 offset 6;

条件查询where

--基本查询
select * from student where id < 102;
select * from student where id < sn;
select id, id + sn si from student where id + sn > 1000;
--AND与OR(AND优先级大于OR)
select * from student where id < 200 and sn < 800;
select * from student where id < 200 or sn < 800;
--范围查询
select name from student where id between 90 and 110;
select name from student where id in(101, 102);
--模糊查询
select name from student where name like '张%';
select name from student where name like '张__';
--NULL查询
select name from student where id is not NULL;
select name from student where id is NULL;

修改（update）

--将张三的id改为1
update student set id = 1 where name = '张三';
--将id倒数前三的同学加上50
update student set id = id + 50 order by id limit 3;
--将所有同学的sn更新为原来的两倍
update student set sn = sn * 2;

删除（delete）

--删除张三的信息
delete from student where name = '张三';
--删除整张表
drop table student;