【MySQL】基础的增删改查

待我称王封你为后i 2024-02-05 14:41 63阅读 0赞

#### 目录 ####

*  一、插入数据
 *   *  1.insert
     *  2.replace(替换)
 *  二、查找数据
 *   *  1.select
     *  2.where
     *  3.order by asc/desc（对结果进行升序/降序）
     *  4.limit（筛选少量结果）
 *  三、修改数据
 *  四、删除数据
 *  五、insert + select

## 一、插入数据 ##

我们要对一张表进行插入数据操作，就得拥有一张表。

--创建学生表
    mysql> create table if not exists students(
        -> id int unsigned primary key auto_increment,
        -> sn int unsigned unique key,
        -> name varchar(20) not null,
        -> qq varchar(32) unique key
        -> );
    Query OK, 0 rows affected (0.31 sec)

### 1.insert ###

**单行指定列插入**

mysql> insert into students (sn,name,qq) values (100,'张三','123456');
    Query OK, 1 row affected (0.04 sec)

**单行全列插入**

mysql> insert into students values (2,101,'李四','222222');
    Query OK, 1 row affected (0.05 sec)

**多行数据插入**

# 插入的数据使用逗号隔开
    mysql> insert into students (sn,name,qq) values (102,'王五','333333'),(103,'赵六',444444);
    Query OK, 2 rows affected (0.04 sec)
    Records: 2  Duplicates: 0  Warnings: 0

**插入或替换更新**

--打印当前学生表数据
    mysql> select* from students;
    +----+------+--------+--------+
    | id | sn   | name   | qq     |
    +----+------+--------+--------+
    |  1 |  100 | 张三   | 123456 |
    |  2 |  101 | 李四   | 222222 |
    |  3 |  102 | 王五   | 333333 |
    |  4 |  103 | 赵六   | 444444 |
    +----+------+--------+--------+
    4 rows in set (0.00 sec)
    --重复插入主键为2的值，主键冲突，插入失败
    mysql> insert into students values (2,104,'小明','111111');
    ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
    --插入变更新，注意作为更新的值可以和插入的值不一样
    mysql> insert into students values (2,104,'小明',111111) on duplicate key update sn=104,name='小明',qq='111111';
    Query OK, 2 rows affected (0.04 sec)
    --打印当前学生表数据，发现id=2的数据被更新
    mysql> select* from students;
    +----+------+--------+--------+
    | id | sn   | name   | qq     |
    +----+------+--------+--------+
    |  1 |  100 | 张三   | 123456 |
    |  2 |  104 | 小明   | 111111 |
    |  3 |  102 | 王五   | 333333 |
    |  4 |  103 | 赵六   | 444444 |
    +----+------+--------+--------+
    4 rows in set (0.01 sec)

### 2.replace(替换) ###

--如果替代的数据在表中无冲突，replace将被当成插入
    mysql> replace into students (sn,name,qq) values (105,'小红','555555');
    Query OK, 1 row affected (0.46 sec)
    --打印当前学生表数据，可以看到表中多了一行小红的信息
    mysql> select* from students;
    +----+------+--------+--------+
    | id | sn   | name   | qq     |
    +----+------+--------+--------+
    |  1 |  100 | 张三   | 123456 |
    |  2 |  104 | 小明   | 111111 |
    |  3 |  102 | 王五   | 333333 |
    |  4 |  103 | 赵六   | 444444 |
    |  5 |  105 | 小红   | 555555 |
    +----+------+--------+--------+
    5 rows in set (0.00 sec)
    --再次replace，唯一键存在冲突，小红1的数据将替换小红的数据
    mysql> replace into students (sn,name,qq) values (105,'小红1','555555');
    Query OK, 2 rows affected (0.02 sec)
    --打印当前学生表数据，发现小红1的id变为6
    mysql> select* from students;
    +----+------+---------+--------+
    | id | sn   | name    | qq     |
    +----+------+---------+--------+
    |  1 |  100 | 张三    | 123456 |
    |  2 |  104 | 小明    | 111111 |
    |  3 |  102 | 王五    | 333333 |
    |  4 |  103 | 赵六    | 444444 |
    |  6 |  105 | 小红1   | 555555 |
    +----+------+---------+--------+
    5 rows in set (0.00 sec)

> 有由id自增长规律不难看出  
> 主键 或者 唯一键 没有冲突，则直接插入；
> 
> 主键 或者 唯一键 如果冲突，则删除后再插入。

## 二、查找数据 ##

要查找数据首先数据表中有数据，我们先创建一张成绩表，插入一些数据

--创建成绩表
    mysql> CREATE TABLE exam_result (
        -> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        -> name VARCHAR(20) NOT NULL COMMENT '同学姓名',
        -> chinese float DEFAULT 0.0 COMMENT '语文成绩',
        -> math float DEFAULT 0.0 COMMENT '数学成绩',
        -> english float DEFAULT 0.0 COMMENT '英语成绩'
        -> );
    Query OK, 0 rows affected (0.14 sec)
    --插入测试数据
    mysql> INSERT INTO exam_result (name, chinese, math, english) VALUES
        -> ('A', 67, 98, 56),
        -> ('B', 87, 78, 77),
        -> ('C', 88, 98, 90),
        -> ('D', 82, 84, 67),
        -> ('E', 55, 85, 45),
        -> ('F', 70, 73, 78),
        -> ('G', 75, 65, 30);
    ds: 7 Duplicates: 0 Warnings: 0Query OK, 7 rows affected (0.03 sec)
    Records: 7  Duplicates: 0  Warnings: 0

### 1.select ###

**全列查询**

mysql> select* from exam_result;
    +----+------+---------+------+---------+
    | id | name | chinese | math | english |
    +----+------+---------+------+---------+
    |  1 | A    |      67 |   98 |      56 |
    |  2 | B    |      87 |   78 |      77 |
    |  3 | C    |      88 |   98 |      90 |
    |  4 | D    |      82 |   84 |      67 |
    |  5 | E    |      55 |   85 |      45 |
    |  6 | F    |      70 |   73 |      78 |
    |  7 | G    |      75 |   65 |      30 |
    +----+------+---------+------+---------+
    7 rows in set (0.00 sec)

**指定列查询**

--指定姓名和数学成绩列进行查询
    mysql> select name,math from exam_result;
    +------+------+
    | name | math |
    +------+------+
    | A    |   98 |
    | B    |   78 |
    | C    |   98 |
    | D    |   84 |
    | E    |   85 |
    | F    |   73 |
    | G    |   65 |
    +------+------+
    7 rows in set (0.00 sec)

### 2.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>不等于(这两个符号都是NULL不安全的)</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>模糊匹配。like ‘A%’。% 表示任意多个（包括 0 个）任意字符；like ‘A_’。_ 表示任意一个字符</td> 
  </tr> 
  <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>

**实例：**

**①找到英语小于60分的同学及其英语成绩**

mysql> select name,english from exam_result where english<60;
    +------+---------+
    | name | english |
    +------+---------+
    | A    |      56 |
    | E    |      45 |
    | G    |      30 |
    +------+---------+
    3 rows in set (0.00 sec)

**②语文成绩在 \[80, 90\] 分的同学及其语文成绩**

--  >=和<=
    mysql> select name,chinese from exam_result where chinese>=80 and chinese <=90;
    +------+---------+
    | name | chinese |
    +------+---------+
    | B    |      87 |
    | C    |      88 |
    | D    |      82 |
    +------+---------+
    3 rows in set (0.01 sec)
     
    --between a0 and a1
    mysql> select name,chinese from exam_result where chinese between 80 and 90;
    +------+---------+
    | name | chinese |
    +------+---------+
    | B    |      87 |
    | C    |      88 |
    | D    |      82 |
    +------+---------+
    3 rows in set (0.00 sec)

**③语文成绩在 \[80, 90\] 分的同学及其语文成绩**

**④数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩**

--可以采用多个or
    mysql> select name,math from exam_result where math=58 or math=59 or math=98 or math=99;
    +------+------+
    | name | math |
    +------+------+
    | A    |   98 |
    | C    |   98 |
    +------+------+
    2 rows in set (0.02 sec)
     
    --可以用in(......)
    mysql> select name,math from exam_result where math in(58,59,98,99);
    +------+------+
    | name | math |
    +------+------+
    | A    |   98 |
    | C    |   98 |
    +------+------+
    2 rows in set (0.00 sec)

**⑤筛选出叫孙某某的同学及叫孙某的同学(模糊搜索)**

--对新表插入数据
    INSERT INTO exam_result (name, chinese, math, english) VALUES
    ('唐三藏', 67, 98, 56),
    ('孙悟空', 87, 78, 77),
    ('猪悟能', 88, 98, 90),
    ('曹孟德', 82, 84, 67),
    ('刘玄德', 55, 85, 45),
    ('孙权', 70, 73, 78),
    ('宋公明', 75, 65, 30);

-- like '孙%'找到所有孙姓同学，%代表后面模糊匹配0-n个字符
    mysql> select name from exam_result where name like '孙%';
    +-----------+
    | name      |
    +-----------+
    | 孙悟空    |
    | 孙权      |
    +-----------+
    2 rows in set (0.00 sec)
     
    --like '孙_'找到所有孙某同学，_代表模糊匹配1个字符
    mysql> select name from exam_result where name like '孙_';
    +--------+
    | name   |
    +--------+
    | 孙权   |
    +--------+
    1 row in set (0.00 sec)

**⑥语文成绩高于英语成绩的**

mysql> select name,chinese,english from exam_result where chinese>english;
    +-----------+---------+---------+
    | name      | chinese | english |
    +-----------+---------+---------+
    | 唐三藏    |      67 |      56 |
    | 孙悟空    |      87 |      77 |
    | 曹孟德    |      82 |      67 |
    | 刘玄德    |      55 |      45 |
    | 宋公明    |      75 |      30 |
    +-----------+---------+---------+
    5 rows in set (0.00 sec)

**⑦总分在200分以下的同学**

mysql> select name,chinese,math,english,chinese+math+english 总分 from exam_result where chinese+math+english<200;
    +-----------+---------+------+---------+--------+
    | name      | chinese | math | english | 总分   |
    +-----------+---------+------+---------+--------+
    | 刘玄德    |      55 |   85 |      45 |    185 |
    | 宋公明    |      75 |   65 |      30 |    170 |
    +-----------+---------+------+---------+--------+
    2 rows in set (0.00 sec)

**⑧语文成绩大于80 且不性孙的同学**

mysql> select name,chinese from exam_result where chinese>80 and name not like '孙%';
    +-----------+---------+
    | name      | chinese |
    +-----------+---------+
    | 猪悟能    |      88 |
    | 曹孟德    |      82 |
    +-----------+---------+
    2 rows in set (0.00 sec)

**⑨孙某同学，总分大于200分，语文成绩 < 数学成绩 并且 英语成绩 > 80**

mysql> select name,chinese,math,english,chinese+math+english 总分 from exam_result where name like '孙_' or (chinese+math+english>200 and chinese<math and english>80);
    +-----------+---------+------+---------+--------+
    | name      | chinese | math | english | 总分   |
    +-----------+---------+------+---------+--------+
    | 猪悟能    |      88 |   98 |      90 |    276 |
    | 孙权      |      70 |   73 |      78 |    221 |
    +-----------+---------+------+---------+--------+
    2 rows in set (0.00 sec)

### 3.order by asc/desc（对结果进行升序/降序） ###

**同学及数学成绩，按数学成绩升序/降序显示**

mysql> select name,math from exam_result order by math asc;
    +-----------+------+
    | name      | math |
    +-----------+------+
    | 张三      | NULL |
    | 宋公明    |   65 |
    | 孙权      |   73 |
    | 孙悟空    |   78 |
    | 曹    |   84 |
    | 刘玄德    |   85 |
    | 唐三藏    |   98 |
    | 猪悟能    |   98 |
    +-----------+------+
    8 rows in set (0.00 sec)
     
    mysql> select name,math from exam_result order by math desc;
    +-----------+------+
    | name      | math |
    +-----------+------+
    | 唐三藏    |   98 |
    | 猪悟能    |   98 |
    | 刘玄德    |   85 |
    | 曹孟德    |   84 |
    | 孙悟空    |   78 |
    | 孙权      |   73 |
    | 宋公明    |   65 |
    | 张三      | NULL |
    +-----------+------+
    8 rows in set (0.00 sec)

> – ASC 为升序（从小到大）
> 
> – DESC 为降序（从大到小）
> 
> – 默认为 ASC
> 
> – NULL 视为比任何值都小

### 4.limit（筛选少量结果） ###

--从表开始连续读取5行
    mysql> select* from exam_result limit 5;
    +----+-----------+---------+------+---------+
    | id | name      | chinese | math | english |
    +----+-----------+---------+------+---------+
    |  1 | 唐三藏    |      67 |   98 |      56 |
    |  2 | 孙悟空    |      87 |   78 |      77 |
    |  3 | 猪悟能    |      88 |   98 |      90 |
    |  4 | 曹孟德    |      82 |   84 |      67 |
    |  5 | 刘玄德    |      55 |   85 |      45 |
    +----+-----------+---------+------+---------+
    5 rows in set (0.01 sec)
     
    --limit 1,3;开始位置1是下标，3是步长
    mysql> select* from exam_result limit 1,3;
    +----+-----------+---------+------+---------+
    | id | name      | chinese | math | english |
    +----+-----------+---------+------+---------+
    |  2 | 孙悟空    |      87 |   78 |      77 |
    |  3 | 猪悟能    |      88 |   98 |      90 |
    |  4 | 曹孟德    |      82 |   84 |      67 |
    +----+-----------+---------+------+---------+
    3 rows in set (0.01 sec)
     
    --limit s offset n其中s代表步长，n代表下标
    mysql> select* from exam_result limit 3 offset 5;
    +----+-----------+---------+------+---------+
    | id | name      | chinese | math | english |
    +----+-----------+---------+------+---------+
    |  6 | 孙权      |      70 |   73 |      78 |
    |  7 | 宋公明    |      75 |   65 |      30 |
    |  8 | 张三      |    NULL | NULL |    NULL |
    +----+-----------+---------+------+---------+
    3 rows in set (0.00 sec)

## 三、修改数据 ##

**语法:**

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

**实例：**

**①将孙悟空的数学成绩变更为 80 分**

mysql> update exam_result set math=80 where name ='孙悟空';
    Query OK, 1 row affected (0.07 sec)

**②将曹孟德的数学成绩变更为 60 分，语文成绩变更为 70 分**

mysql> update exam_result set math=60,chinese=70 where name ='曹孟德';
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

**③将总成绩倒数前三的同学数学成绩加上30分**

mysql> update exam_result set math=math+30 order by chinese+math+english asc limit 3;
    Query OK, 2 rows affected (0.05 sec)
    Rows matched: 3  Changed: 2  Warnings: 0

**④将所有同学的成绩更新为原来的2倍**

mysql> update exam_result set chinese=chinese*2;
    Query OK, 7 rows affected (0.04 sec)
    Rows matched: 8  Changed: 7  Warnings: 0
     
    mysql> select * from exam_result;
    +----+-----------+---------+------+---------+
    | id | name      | chinese | math | english |
    +----+-----------+---------+------+---------+
    |  1 | 唐三藏    |     134 |   98 |      56 |
    |  2 | 孙悟空    |     174 |   80 |      77 |
    |  3 | 猪悟能    |     176 |   98 |      90 |
    |  4 | 曹孟德    |     140 |   60 |      67 |
    |  5 | 刘玄德    |     110 |  115 |      45 |
    |  6 | 孙权      |     140 |   73 |      78 |
    |  7 | 宋公明    |     150 |   95 |      30 |
    |  8 | 张三      |    NULL | NULL |    NULL |
    +----+-----------+---------+------+---------+
    8 rows in set (0.00 sec)

## 四、删除数据 ##

**语法：**

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

**实例：**

**①删除孙悟空的考试成绩**

mysql> delete from exam_result where name='孙悟空';
    Query OK, 1 row affected (0.04 sec)
     
    mysql> select * from exam_result;
    +----+-----------+---------+------+---------+
    | id | name      | chinese | math | english |
    +----+-----------+---------+------+---------+
    |  1 | 唐三藏    |     134 |   98 |      56 |
    |  3 | 猪悟能    |     176 |   98 |      90 |
    |  4 | 曹孟德    |     140 |   60 |      67 |
    |  5 | 刘玄德    |     110 |  115 |      45 |
    |  6 | 孙权      |     140 |   73 |      78 |
    |  7 | 宋公明    |     150 |   95 |      30 |
    |  8 | 张三      |    NULL | NULL |    NULL |
    +----+-----------+---------+------+---------+
    7 rows in set (0.00 sec)

**②删除总分最高同学的数据**

mysql> delete from exam_result order by chinese+math+english desc limit 1;
    Query OK, 1 row affected (0.02 sec)

**③删除整张表的数据**

--准备测试表
    mysql> CREATE TABLE for_delete (
        -> id INT PRIMARY KEY AUTO_INCREMENT,
        -> name VARCHAR(20)
        -> );
    Query OK, 0 rows affected (0.15 sec)
    --插入测试数据
    mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
    Query OK, 3 rows affected (0.04 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    --查看表结构
    mysql> select* from for_delete;
    +----+------+
    | id | name |
    +----+------+
    |  1 | A    |
    |  2 | B    |
    |  3 | C    |
    +----+------+
    3 rows in set (0.00 sec)
    --查看创建表时的SQL，可以发现，下一次自增的id值是4
    mysql> show create table for_delete\G;
    *************************** 1. row ***************************
           Table: for_delete
    Create Table: CREATE TABLE `for_delete` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk
    1 row in set (0.00 sec)
    --删除表
    mysql> delete from for_delete;
    Query OK, 3 rows affected (0.04 sec)
    --查看创建表的SQL，发现自增值仍为4
    mysql> show create table for_delete\G;
    *************************** 1. row ***************************
           Table: for_delete
    Create Table: CREATE TABLE `for_delete` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk
    1 row in set (0.02 sec)

## 五、insert + select ##

**语法：**

INSERT INTO table_name [(column [, column ...])] SELECT ...

**实例：** **将一张表中的重复数据去除**

--创建表结构
    mysql> CREATE TABLE duplicate_table (id int, name varchar(20));
    Query OK, 0 rows affected (0.26 sec)
    --插入测试数据
    mysql> INSERT INTO duplicate_table VALUES
        -> (100, 'aaa'),
        -> (100, 'aaa'),
        -> (200, 'bbb'),
        -> (200, 'bbb'),
        -> (200, 'bbb'),
        -> (300, 'ccc');
    Query OK, 6 rows affected (0.03 sec)
    Records: 6  Duplicates: 0  Warnings: 0

创建一张属性和原表一样的空表

利用distinct筛选出原表中去重后的数据

--对原表select出来的结果insert进新表中
    mysql> insert into no_duplicate_table select distinct * from duplicate_table;
    Query OK, 3 rows affected (0.05 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    --查看新表数据
    mysql> select* from no_duplicate_table;
    +------+------+
    | id   | name |
    +------+------+
    |  100 | aaa  |
    |  200 | bbb  |
    |  300 | ccc  |
    +------+------+
    3 rows in set (0.01 sec)

将原表名字修改为其他，将新表数据修改为原表名字

--改名
    mysql> rename table duplicate_table to old_duplicate_table,no_duplicate_table to duplicate_table;
    Query OK, 0 rows affected (0.17 sec)
    --查看表
    mysql> select* from duplicate_table;
    +------+------+
    | id   | name |
    +------+------+
    |  100 | aaa  |
    |  200 | bbb  |
    |  300 | ccc  |
    +------+------+
    3 rows in set (0.02 sec)