数据库增删改查I

水深无声 2024-03-26 15:32 98阅读 0赞

**目录**

1.新增

1.1单行数据+全列插入

1.2 多行数据+指定列插入

1.3 时间日期插入

2.查询

2.1 全列查询

2.2指定列查询

2.3查询字段为表达式

2.4别名

2.5去重:distinct

2.6排序:order by

2.7条件查询:where

2.8分页查询:limit

3.修改

4.删除

--------------------

## 1.新增 ##

语法

insert into 表名 values(valus_list);

以宿舍表为例

![86788f4894e94eda8da66b2ae361e89e.png][]

### 1.1单行数据+全列插入 ###

\-- 插入两条记录，value\_list 数量必须和定义表的列的数量及顺序一致

insert into dormitory values(1,'a');
    insert into dormitory values(2,'b');

![d02e86f2ac324ae4aa12c69b3e40f5c4.png][]

### 1.2 多行数据+指定列插入 ###

一次插入N个数据,比一次插入一个分N次插入,效率高

insert into dormitory(id,number) values(4,'d'),(5,'e');

![b5e6f215a7c44f4d92e0d08517953d36.png][]

### 1.3 时间日期插入 ###

insert into homework values (1,'2023-02-28 15:25:00');

插入现在的时间

insert into homework values (1,now());

## 2.查询 ##

### 2.1 全列查询 ###

select* from dormitory;

### ![826e9a404f8f40fba558a3c066fb410e.png][] ###

### 2.2指定列查询 ###

\-- 指定列的顺序不需要按定义表的顺序来

select number,id from dormitory;

### ![d252352944f54092bd18a27cd2060b53.png][] ###

### 2.3查询字段为表达式 ###

列和列直接的运算

select id,10 from dormitory;

### ![a57e2ad0c5ee40b7a6676a5861724195.png][] ###

select number,id+10 from dormitory;

### ![15f8819b6f4f4d71a332b3edc2bd9355.png][] ###

### 2.4别名 ###

select 列名 (as) 别名 from 表名

as可以省

结果集中,表头的列名=别名

select id,name,chinese+math(as) 总分 from score;

### 2.5去重:distinct ###

select distinct math from score;

distinct去重多个列时,要相同才能去重

### 2.6排序:order by ###

select 列名 from 表名 (where ...) order by 列名 (asc/desc);

注:1.asc为升序,desc为降序,默认为asc

2.null数据排序,比任何值都小

3.可以对多个字段排序,排序优先级随书写顺序

select name,math,chinese from score order by math desc,chinese;

### 2.7条件查询:where ###

1.基本查询

用两个列比较

select name,english from score where english<60;

### 2.and 与or ###

select * from score where chinese>60 and math<60;

select * from score where chinese>60 or math<60;

优先级:先and后or(或者加括号)

select * from score where chinese>60 and math<60 or english<50;

3.范围查询

1.between.. and

select name,math from score where math between 80 and 85;

2.in

select name,math from score where math in(56,57,60);

4.模糊查询:like

select name from student where name like '王%';

%匹配任意多个(包括0个字符)

### 2.8分页查询:limit ###

起始下标为0

从0开始,筛选n条结果

select ... from 表名 (where...)(order by...)  limit n;

从 s 开始，筛选 n 条结果

select ... from 表名 (where...)(order by...)  limit s,n;

从 s 开始，筛选 n 条结果

select ... from 表名 (where...)(order by...)  limit n offset s;

## 3.修改 ##

update 表名 set ...;

## 4.删除 ##

delect from 表名 (where ...)(order by...)(limit...);

[86788f4894e94eda8da66b2ae361e89e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8d73da998e354b49b136e8422280545c.png
[d02e86f2ac324ae4aa12c69b3e40f5c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/e17b0c680a5740c882e7ab7358451253.png
[b5e6f215a7c44f4d92e0d08517953d36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/3c503fde82d0457ab27ae39836c35aa5.png
[826e9a404f8f40fba558a3c066fb410e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7a58f9c93aef4b6d9afd614bee1d8047.png
[d252352944f54092bd18a27cd2060b53.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ee7d41e0f7f345ed83c7b82b34976594.png
[a57e2ad0c5ee40b7a6676a5861724195.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/57c782b57dfc4765977cf2e63da98437.png
[15f8819b6f4f4d71a332b3edc2bd9355.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ae73d02682824c5ea8966e0947c5672e.png