【SQL】表的相关操作-2增删改查

青旅半醒 2024-04-19 15:03 0阅读 0赞

## 一、 增（create） ##

**1. 语法**

INSERT [INTO] table_name [(column[,column]...)] VALUES
    (value_list)[,(value_list)]...
    value_list:value,[,value]...

例：

drop table if exists student;
    create table student(
    	id int,
    	sn int comment'学号',
    	name varchar(20) comment'姓名',
    	qq_mail varchar(20) comment'QQ邮箱'
    );

**2. 单行数据全列插入**  
插入时，value\_list 数量必须和定义表的列的数量、顺序一致

insert into student values(100,10000,'唐三藏',NULL);
    insert into student values(101,10001,'孙悟空',NULL);

**3. 多行数据指定列插入**  
value\_list 数量必须和指定列数量、顺序一致

insert into student(id,sn,name) values
    (102,20001,'曹孟德'),
    (103,20002,'孙仲谋');

## 二、 删 ##

**语法**

DELETE FROM table_name [WHERE...] [ORDER BY...] [LIMIT...]

**例：**

-- 删除孙悟空同学的考试成绩
    delete from exam_result where name='孙悟空';
    
    -- 删除整张表数据 
    -- 准备测试表 
    drop table if exists for_delete;
    create table for_delete(
    	id int,
    	name varchar(20)
    );
    -- 插入测试数据 
    insert into for_delete(name) VALUES
    ('A'),
    ('B'),
    ('C');
    -- 删除整表数据 
    delete from for_delete;

## 三、 改 ##

**语法**

UPDATE table_name SET column = expr [,column=expr...] [WHERE...] [ORDER BY...] [LIMIT...]

**例：**

-- 将孙悟空同学的数学成绩变更为80分
    update exam_result set math=80 where name='孙悟空';
    -- 将曹孟德同学的数学成绩变更为60分，语文成绩变更为70分
    update exam_result set math=60,chinese=70 where name='曹孟德';
    -- 将总成绩倒数前三的3位同学的数学成绩加上30分
    update exam_result set math=math+30 order by chinese+math+english limit 3;
    -- 将所有同学的语文成绩更新为原来的2倍
    update exam_result set chinese=chinese*2;

## 四、查 ##

**1.语法**

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

例：

-- 创建考试成绩表 
    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(name,chinese,math,english) values
    ('唐三藏',67,98,56),
    ('孙悟空',87.5,78,77),
    ('猪悟能',88,98,90),
    ('曹孟德',82,84,67),
    ('刘玄德',55.5,85,45),
    ('孙权',70,73,78.5),
    ('宋公明',75,65,30);

**2.全列查询**

select * from exam_result;

【注】**通常情况下不建议使用\*进行全列查询**  
因为：  
查询的列越多，意味着需要传输的数据量越大；  
可能会影响到索引的使用；

**3.指定列查询**

select id,name,english from exam_result;

**指定列查询时，查询列表的顺序不需要按定义表的顺序来**

**4.查询字段为表达式**

-- 表达式不包含字段
    select id,name,10 from exam_result;
    -- 表达式包含一个字段
    select id,name,english+10 from exam_result;
    -- 表达式包含多个字段
    select id,name,chinese+math+english from exam_result;

**5.别名**  
语法：  
`SELECT column [AS] alias_name [...] FROM table_name;`

**为查询结果中的列指定别名，即：返回的结果集中，以别名作为该列的名称**

-- 结果集中：表头的列名=别名
    select id,name,chinese+math+english 总分 from exam_result;

**6.去重（distinct）**

**使用 distinct 关键字可对某列数据进行去重**  
例：对于之前创建的表 exam\_result 中，math列里98分重复了

select distinct math from exam_result;

**7.排序（order by）**  
语法：  
`SELECT ... FROM table_name [WHERE...] ORDER BY column [ASC | DESC],[...];`  
**升序 ---- ASC（小 -> 大）  
降序 ---- DESC（大 -> 小）  
一般默认为ASC**  
【注】  
1.没有 order by 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

2.NULL数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面  
例：

-- 查询同学姓名和qq_mail，按qq_mail排序显示
    select name,qq_mail from student order by qq_mail;
    select name,qq_mail from student order by qq_mail desc;

3.使用 表达式+别名 排序

--查询同学及总分，由高到低 
    select name,chinese+english+math from exam_result order by chinese+english+math desc;
    select name,chinese+english+math total from exam_result order by total desc;

4.可以对多个字段进行排序，排序优先级随书写顺序

--查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示
    select name,math,english,chinese from exam_result order by math desc,english,chinese;

**8.条件查询（where）**

*  比较运算符：

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&gt;、&gt;=、&lt;、&lt;=</td> 
   <td>大于、大于等于、小于、小于等于</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>等于，NULL不安全，例如NULL=NULL的结果是NULL</td> 
  </tr> 
  <tr> 
   <td>&lt;=&gt;</td> 
   <td>等于，NULL安全，例如NULL&lt;=&gt;NULL的结果是TRUE(1)</td> 
  </tr> 
  <tr> 
   <td>!=、&lt;&gt;</td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td>between a0 and a1</td> 
   <td>范围匹配[a0,a1]，如果a0&lt;=value&lt;=a1，返回TRUE(1)</td> 
  </tr> 
  <tr> 
   <td>in(option,…)</td> 
   <td>如果是option中的任意一个，返回TRUE(1)</td> 
  </tr> 
  <tr> 
   <td>is null</td> 
   <td>是null</td> 
  </tr> 
  <tr> 
   <td>is not null</td> 
   <td>不是null</td> 
  </tr> 
  <tr> 
   <td>like</td> 
   <td>模糊匹配。%表示任意多个（包括0个）任意字符；_表示任意一个字 符</td> 
  </tr> 
 </tbody> 
</table>

*  逻辑运算符：

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>and</td> 
   <td>多个条件必须都为TRUE(1)，结果才是TRUE(1)</td> 
  </tr> 
  <tr> 
   <td>or</td> 
   <td>任意一个条件为TRUE(1),结果为TRUE(1)</td> 
  </tr> 
  <tr> 
   <td>not</td> 
   <td>条件为TRUE(1)，结果为FALSE(0)</td> 
  </tr> 
 </tbody> 
</table>

**注：**  
1.where 条件可以使用表达式，但不能使用别名

2.and 的优先级高于 or ，在同时使用时，需要使用小括号()包裹优先执行的部分

*  **例：**

**基本查询：**

-- 查询英语成绩<60的同学
    select name,english from exam_result where english<60;
    -- 查询语文成绩好于英语成绩的同学 
    select name,chinese,english from exam_result where chinese>english;
    -- 查询总分在200分以下的同学 
    select name,chinese+math+english 总分 from exam_result where chinese+math+english<200;

**and 与 or：**

-- 查询语文成绩大于80分 且 英语成绩大于80分的同学 
    select * from exam_result where chinese>80 and english>80;
    -- 查询语文成绩大于80分 或 英语成绩大于80分的同学 
    select * from exam_result where chinese>80 or english>80;
    --观察 and 和 or 的优先级：
    select * from exam_result where chinese>80 or math>70 and english>70;
    select * from exam_result where (chinese>80 or math>70) and english>70;

**范围查询：**  
between … and…

-- 查询语文成绩在[80,90]分的同学及语文成绩
    select name,chinese from exam_result where chinese between 80 and 90;
    -- 使用 and 也可以实现
    select name,chinese from exam_result where chinese>=80 and chinese <=90;

-- 查询数学成绩是58或者59或者98或者99分的同学及数学成绩
    select name,math from exam_result where math in(58,59,98,99);
    -- 使用OR也可以实现
    select name,math from exam_result where math=58 or math=59 or math =98 or math=99;

模糊查询：like

-- %匹配任意多个（包括0个）字符
    select name from exam_result where name like '孙%';--匹配到孙悟空、孙权
     -- _匹配严格的一个任意字符
    select name from exam_result where name like'孙_';--匹配到孙权

null 查询：is \[not\] null

--查询qq_mail已知的同学姓名
    select name,qq_mail from student where qq_mail is not null;
    --查询qq_mail未知的同学姓名
    select name,qq_mail from student where qq_mail is null;

**9.分页查询（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;

例：按 id 进行分页，每页3条记录，分别显示第1、2、3页

--第1页 
    select id,name,math,english,chinese from exam_result order by id limit 3 offset 0;
     
     --第2页 
    select id,name,math,english,chinese from exam_result order by id limit 3 offset 3;
     
     --第3页，如果结果不足3个，不会有影响
    select id,name,math,english,chinese from exam_result order by id limit 3 offset 6;