简单学习三、Mysql语句之表内容操作

秒速五厘米 2022-05-27 03:14 196阅读 0赞

#### 表内容的操作    ####

#### 1、增 ####

insert into 表 (列名,列名...) values (值,值,值...)
    insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)

![70][]

insert into 表 (列名,列名...) select 列名,列名... from 表

![70 1][]

#### 2、删 ####

#删除表
    drop table 表名 #先清空表
    #清表数据
    delete from 表名
    delete from 表名 where 条件1 and 条件2

![70 2][]

#### 3、改 ####

update 表 set 列名 ＝ '值' where 条件

![70 3][]

#### 4、查 ####

select * from 表
    select * from 表 where 条件
    select 列名1,列名2,列名3 as 新列名 from 表 where 条件 #as 更改列名3显示的列名

![70 4][]

#### 5、其他(条件、通配符、分页、排序、连表、组合、分组) ####

##### A、按条件 #####

~~~A.a~~~    select * from 表 where 条件1 and 条件2 and 条件3;
    
    mysql> select * from a where age = 23 and part_nid = 2;
    +-----+-------+------+----------+
    | nid | name  | age  | part_nid |
    +-----+-------+------+----------+
    |   7 | houyi |   23 |        2 |
    +-----+-------+------+----------+
    1 row in set (0.00 sec)

~~~A.b~~~    select * from 表 where nid between 2 and 5; #nid等于2到5范围内
    
    mysql> select * from a where nid between 2 and 5;
    +-----+----------+------+----------+
    | nid | name     | age  | part_nid |
    +-----+----------+------+----------+
    |   2 | wanwu    |   18 |        2 |
    |   3 | lisi     |   19 |        3 |
    |   4 | zhangfei |   22 |        5 |
    |   5 | wuhua    |   12 |        1 |
    +-----+----------+------+----------+
    4 rows in set (0.00 sec)

~~~A.c~~~    select * from 表 where nid in (1,3,4) #nid 等于1或3或4
    
    mysql> select * from a where nid in (1,3,4);
    +-----+----------+------+----------+
    | nid | name     | age  | part_nid |
    +-----+----------+------+----------+
    |   1 | zhangsan |   18 |        2 |
    |   3 | lisi     |   19 |        3 |
    |   4 | zhangfei |   22 |        5 |
    +-----+----------+------+----------+
    3 rows in set (0.00 sec)

~~~A.e~~~    select * from 表 where id not in (11,22,33)#id 不等于1或3或4
    
    mysql> select * from a where nid not in (1,3,4);
    +-----+------------+------+----------+
    | nid | name       | age  | part_nid |
    +-----+------------+------+----------+
    |   2 | wanwu      |   18 |        2 |
    |   5 | wuhua      |   12 |        1 |
    |   6 | laohu      |   33 |        4 |
    |   7 | houyi      |   23 |        2 |
    |   8 | zhengzheng |   33 |        5 |
    +-----+------------+------+----------+
    5 rows in set (0.00 sec)

~~~A.f~~~    select * from 表 where nid in (select nid from 表) #nid 是否包含在nid里
    #b表nid只有1-6
    mysql> select * from a where nid in (select nid from b);
    +-----+----------+------+----------+
    | nid | name     | age  | part_nid |
    +-----+----------+------+----------+
    |   1 | zhangsan |   18 |        2 |
    |   2 | wanwu    |   18 |        2 |
    |   3 | lisi     |   19 |        3 |
    |   4 | zhangfei |   22 |        5 |
    |   5 | wuhua    |   12 |        1 |
    |   6 | laohu    |   33 |        4 |
    +-----+----------+------+----------+
    6 rows in set (0.00 sec)

##### B、通配符 #####

~~~B.a~~~    select * from 表 where 列名 like 'zh%'   - zh开头的所有（多个字符串）
    
    mysql> select * from a where name like 'zhang%';
    +-----+----------+------+----------+
    | nid | name     | age  | part_nid |
    +-----+----------+------+----------+
    |   1 | zhangsan |   18 |        2 |
    |   4 | zhangfei |   22 |        5 |
    +-----+----------+------+----------+
    2 rows in set (0.00 sec)

~~~B.b~~~    select * from 表 where 列名 like 'zhangsa_'   - zhengsa开头的所有（一个字符）
    mysql> select * from a where name like 'zhangsa_';
    +-----+----------+------+----------+
    | nid | name     | age  | part_nid |
    +-----+----------+------+----------+
    |   1 | zhangsan |   18 |        2 |
    +-----+----------+------+----------+
    1 row in set (0.00 sec)

~~~B.c~~~    select * from 表 where 列名 like '%ng%'  - 列里面所有包含ng
    
    mysql> select * from a where name like '%ng%';
    +-----+------------+------+----------+
    | nid | name       | age  | part_nid |
    +-----+------------+------+----------+
    |   1 | zhangsan   |   18 |        2 |
    |   4 | zhangfei   |   22 |        5 |
    |   8 | zhengzheng |   33 |        5 |
    +-----+------------+------+----------+
    3 rows in set (0.00 sec)

##### C、分页 #####

~~~C.a~~~    select * from 表 limit 2;            - 前2行
    
    mysql> select * from a limit 2;
    +-----+----------+------+----------+
    | nid | name     | age  | part_nid |
    +-----+----------+------+----------+
    |   1 | zhangsan |   18 |        2 |
    |   2 | wanwu    |   18 |        2 |
    +-----+----------+------+----------+
    2 rows in set (0.00 sec)

~~~C.b~~~    select * from 表 limit 2,5;          - 从第2行开始的5行
                 select * from 表 limit 5 offset 2    - 从第2行开始的5行 #2种写法一样 尽量用这种
    
    mysql> select * from a limit 5 offset 2;
    +-----+----------+------+----------+
    | nid | name     | age  | part_nid |
    +-----+----------+------+----------+
    |   3 | lisi     |   19 |        3 |
    |   4 | zhangfei |   22 |        5 |
    |   5 | wuhua    |   12 |        1 |
    |   6 | laohu    |   33 |        4 |
    |   7 | houyi    |   23 |        2 |
    +-----+----------+------+----------+
    5 rows in set (0.01 sec)

##### D、排序 #####

~~~D.a~~~    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列 
    
    mysql> select * from a order by part_nid;
    +-----+------------+------+----------+
    | nid | name       | age  | part_nid |
    +-----+------------+------+----------+
    |   5 | wuhua      |   12 |        1 |
    |   1 | zhangsan   |   18 |        2 |
    |   2 | wanwu      |   18 |        2 |
    |   7 | houyi      |   23 |        2 |
    |   3 | lisi       |   19 |        3 |
    |   6 | laohu      |   33 |        4 |
    |   4 | zhangfei   |   22 |        5 |
    |   8 | zhengzheng |   33 |        5 |
    +-----+------------+------+----------+
    8 rows in set (0.00 sec)

~~~D.b~~~    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    
    mysql> select * from a order by part_nid desc;
    +-----+------------+------+----------+
    | nid | name       | age  | part_nid |
    +-----+------------+------+----------+
    |   4 | zhangfei   |   22 |        5 |
    |   8 | zhengzheng |   33 |        5 |
    |   6 | laohu      |   33 |        4 |
    |   3 | lisi       |   19 |        3 |
    |   1 | zhangsan   |   18 |        2 |
    |   2 | wanwu      |   18 |        2 |
    |   7 | houyi      |   23 |        2 |
    |   5 | wuhua      |   12 |        1 |
    +-----+------------+------+----------+
    8 rows in set (0.00 sec)

~~~D.c~~~    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
    
    mysql> select * from a order by age desc,age asc;
    +-----+------------+------+----------+
    | nid | name       | age  | part_nid |
    +-----+------------+------+----------+
    |   6 | laohu      |   33 |        4 |
    |   8 | zhengzheng |   33 |        5 |
    |   7 | houyi      |   23 |        2 |
    |   4 | zhangfei   |   22 |        5 |
    |   3 | lisi       |   19 |        3 |
    |   1 | zhangsan   |   18 |        2 |
    |   2 | wanwu      |   18 |        2 |
    |   5 | wuhua      |   12 |        1 |
    +-----+------------+------+----------+
    8 rows in set (0.00 sec)

##### E、分组  group by 必须在where之后，order by之前    #####

~~~E.a~~~    select 列名1 from 表 group by 列名1
    
    mysql> select part_nid from a group by part_nid;
    +----------+
    | part_nid |
    +----------+
    |        1 |
    |        2 |
    |        3 |
    |        4 |
    |        5 |
    +----------+
    5 rows in set (0.00 sec)

~~~E.b~~~    select 列名1,列名2 from 表 group by 列名1,列名2
    
    mysql> select part_nid,age from a group by part_nid,age;
    +----------+------+
    | part_nid | age  |
    +----------+------+
    |        1 |   12 |
    |        2 |   18 |
    |        2 |   23 |
    |        3 |   19 |
    |        4 |   33 |
    |        5 |   22 |
    |        5 |   33 |
    +----------+------+
    7 rows in set (0.00 sec)

~~~E.c~~~    select 列名1,列名2 from 表  where 条件1 group by 列名,列名1 order by 列名1 desc
    
    mysql> select part_nid,age from a where age < 30 group by part_nid,age order by age desc;
    +----------+------+
    | part_nid | age  |
    +----------+------+
    |        2 |   23 |
    |        5 |   22 |
    |        3 |   19 |
    |        2 |   18 |
    |        1 |   12 |
    +----------+------+
    5 rows in set (0.00 sec)

~~~E.d~~~    select 列名1,count(列名2) from 表 group by 列名1
    mysql> select part_nid,count(age) from a group by part_nid;
    
    +----------+------------+
    | part_nid | count(age) |
    +----------+------------+
    |        1 |          1 |
    |        2 |          3 |
    |        3 |          1 |
    |        4 |          1 |
    |        5 |          2 |
    +----------+------------+
    5 rows in set (0.00 sec)

~~~E.e~~~    select 列名1 from 表 group by 列名1 having max(列名2) > 条件
    
    mysql> select part_nid from a group by part_nid having max(age) > 30;
    +----------+
    | part_nid |
    +----------+
    |        4 |
    |        5 |
    +----------+
    2 rows in set (0.00 sec)

F、连表

mysql> select * from a;
    +-----+------------+------+----------+
    | nid | name       | age  | part_nid |
    +-----+------------+------+----------+
    |   1 | zhangsan   |   18 |        2 |
    |   2 | wanwu      |   18 |        2 |
    |   3 | lisi       |   19 |        3 |
    |   4 | zhangfei   |   22 |        5 |
    |   5 | wuhua      |   12 |        1 |
    |   6 | laohu      |   33 |        4 |
    |   7 | houyi      |   23 |        2 |
    |   8 | zhengzheng |   33 |        5 |
    +-----+------------+------+----------+
    8 rows in set (0.00 sec)
    
    mysql> select * from b;
    +-----+-----------+
    | nid | caption   |
    +-----+-----------+
    |   1 | it        |
    |   2 | 驯兽师    |
    |   3 | 飞行员    |
    |   4 | 清洁员    |
    |   5 | 所长      |
    |   6 | 经理      |
    +-----+-----------+
    6 rows in set (0.00 sec)

~~~F.a~~~    关联显示
    select A.列名1, A.列名2, B.列名2
    from A,B
    Where A.列名3 = B.列名3
    
    mysql> select a.nid,a.name,a.age,b.caption from a,b where a.part_nid = b.nid;
    +-----+------------+------+-----------+
    | nid | name       | age  | caption   |
    +-----+------------+------+-----------+
    |   5 | wuhua      |   12 | it        |
    |   1 | zhangsan   |   18 | 驯兽师    |
    |   2 | wanwu      |   18 | 驯兽师    |
    |   7 | houyi      |   23 | 驯兽师    |
    |   3 | lisi       |   19 | 飞行员    |
    |   6 | laohu      |   33 | 清洁员    |
    |   4 | zhangfei   |   22 | 所长      |
    |   8 | zhengzheng |   33 | 所长      |
    +-----+------------+------+-----------+
    8 rows in set (0.00 sec)

~~~F.b~~~    无对应关系则不显示
    select A.列名1, A.列名2, B.列名2
    from A inner join B
    on A.列名3 = B.列名3
    
    mysql> select a.nid,a.name,a.age,b.caption from a inner join b where a.part_nid = b.nid; 
    +-----+------------+------+-----------+
    | nid | name       | age  | caption   |
    +-----+------------+------+-----------+
    |   5 | wuhua      |   12 | it        |
    |   1 | zhangsan   |   18 | 驯兽师    |
    |   2 | wanwu      |   18 | 驯兽师    |
    |   7 | houyi      |   23 | 驯兽师    |
    |   3 | lisi       |   19 | 飞行员    |
    |   6 | laohu      |   33 | 清洁员    |
    |   4 | zhangfei   |   22 | 所长      |
    |   8 | zhengzheng |   33 | 所长      |
    +-----+------------+------+-----------+
    8 rows in set (0.00 sec)

~~~F.c~~~    A表所有显示，如果B中无对应关系，则值为null
    select A.列名1, A.列名2, B.列名2
    from A left join B
    on A.列名3 = B.列名3
    
    mysql> select a.nid,a.name,a.age,b.caption from a left join b on a.part_nid = b.nid;     
    +-----+------------+------+-----------+
    | nid | name       | age  | caption   |
    +-----+------------+------+-----------+
    |   5 | wuhua      |   12 | it        |
    |   1 | zhangsan   |   18 | 驯兽师    |
    |   2 | wanwu      |   18 | 驯兽师    |
    |   7 | houyi      |   23 | 驯兽师    |
    |   3 | lisi       |   19 | 飞行员    |
    |   6 | laohu      |   33 | 清洁员    |
    |   4 | zhangfei   |   22 | 所长      |
    |   8 | zhengzheng |   33 | 所长      |
    +-----+------------+------+-----------+
    8 rows in set (0.00 sec)

~~~F.e~~~    B表所有显示，如果B中无对应关系，则值为null
    select A.列名1, A.列名2, B.列名2
    from A right join B
    on A.列名3 = B.列名3
    
    mysql> select a.nid,a.name,a.age,b.caption from a right join b on a.part_nid = b.nid;     
    +------+------------+------+-----------+
    | nid  | name       | age  | caption   |
    +------+------------+------+-----------+
    |    1 | zhangsan   |   18 | 驯兽师    |
    |    2 | wanwu      |   18 | 驯兽师    |
    |    3 | lisi       |   19 | 飞行员    |
    |    4 | zhangfei   |   22 | 所长      |
    |    5 | wuhua      |   12 | it        |
    |    6 | laohu      |   33 | 清洁员    |
    |    7 | houyi      |   23 | 驯兽师    |
    |    8 | zhengzheng |   33 | 所长      |
    | NULL | NULL       | NULL | 经理      |
    +------+------------+------+-----------+
    9 rows in set (0.00 sec)

G、组合

~~~G.a~~~    组合，自动处理重合
    select 列名 from A union select 列名 from B
    
    mysql> select nid from a union select nid from b;
    +-----+
    | nid |
    +-----+
    |   1 |
    |   2 |
    |   3 |
    |   4 |
    |   5 |
    |   6 |
    |   7 |
    |   8 |
    +-----+
    8 rows in set (0.00 sec)

~~~G.b~~~    组合，不处理重合
    select 列名 from A union all select 列名 from B
    mysql> select nid from a union all select nid from b;
    +-----+
    | nid |
    +-----+
    |   1 |
    |   2 |
    |   3 |
    |   4 |
    |   5 |
    |   6 |
    |   7 |
    |   8 |
    |   1 |
    |   2 |
    |   3 |
    |   4 |
    |   5 |
    |   6 |
    +-----+
    14 rows in set (0.00 sec)

[70]: /images/20220527/ca41749e66084b6ab0a3108e644eb57c.png
[70 1]: /images/20220527/45f18c7d242c4ce785ed1bd89f02d544.png
[70 2]: /images/20220527/cd1f2fe270fa4501ab310f222515fc0d.png
[70 3]: /images/20220527/bea1c27494874fb1b0c07ed14c5a9fba.png
[70 4]: /images/20220527/d27b8c6f8bf24434bfd010a2da295523.png