Mysql Day Five

最新推荐文章于 2023-06-23 15:48:51 发布

奔波在软件测试道路的小白

最新推荐文章于 2023-06-23 15:48:51 发布

阅读量134

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/wf1532861978/article/details/90736921

版权

MYSQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

数据的高级操作

1.主键冲突

在数据插入的时候，假设主键对应的值已经存在，插入会失败

-- 解决方式1：当主键冲突时进行更新操作
-- insert into 表名 values （值列表） on duplicate key update 字段=值；
desc m_primary;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | NO   | PRI | NULL    |       |
| number | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
select * from m_primary;
+-------+--------+
| name  | number |
+-------+--------+
| name1 |      1 |
| name2 |      2 |
+-------+--------+

insert into m_primary values ("name1",3);
ERROR 1062 (23000): Duplicate entry 'name1' for key 'PRIMARY'
insert into m_primary values ("name1",3)
on duplicate key
update number=3;

select * from m_primary;
+-------+--------+
| name  | number |
+-------+--------+
| name1 |      3 |
| name2 |      2 |
+-------+--------+
2 rows in set (0.00 sec)

-- 解决方式2:当主键冲突时进行替换操作
-- replace into 表名 values （值列表）
replace into m_privary values("name1",1);
select * from m_primary;
+-------+--------+
| name  | number |
+-------+--------+
| name1 |      1 |
| name2 |      2 |
+-------+--------+

2.蠕虫复制

-- 从已有表复制表结构
-- create table 表名 like 数据库.表名  （只复制表结构不复制表数据）
desc m_primary;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | NO   | PRI | NULL    |       |
| number | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
 select * from m_primary;
+-------+--------+
| name  | number |
+-------+--------+
| name1 |      1 |
| name2 |      2 |
+-------+--------+

create table m_copy like test.m_primary;
desc m_copy;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | NO   | PRI | NULL    |       |
| number | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

select * from m_copy;
Empty set (0.00 sec)

-- 数据复制
-- insert into 表名 select 字段名 from 数据表；
insert into m_copy select * from m_primary;
select * from m_copy;
+-------+--------+
| name  | number |
+-------+--------+
| name1 |      1 |
| name2 |      2 |
+-------+--------+

3.更新数据

-- 基本语法：update 表名  set 字段 = 值；
-- 限制更新：update 表名  set 字段 = 值 where 字段=值 limit 数值；

4.删除数据

-- 清空表，重置自增长
-- truncate table;
select * from m_copy1;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
+----+-------+
show create table m_copy1;
------+
| Table   | Create Table                                                                                                                                        ----------------------------------------+
| m_copy1 | CREATE TABLE `m_copy1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+---------------------------------------+

truncate m_copy1;

show create table m_copy1;
+-----------------------------+
| Table   | Create Table                                                                                                                                               |
+---------+-------------------+
| m_copy1 | CREATE TABLE `m_copy1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------------------+

5.查询数据

-- 查询完整语句
-- select [select 选项] 字段列表[字段别名] /* from 数据源 [where 条件子句 ][group by 子句] [having 子句] [order by 子句] [limit 子句]
-- select选项：对查到的数据结果进行处理方式
--            all ：保留所有结果
--            distinct :去重，将重复的数据从结果中去掉
-- 字段别名： 字段名 as 别名
select * from m_copy1;
+----+-------+
| id | name  |
+----+-------+
|  2 | name2 |
|  3 | name3 |
|  4 | name4 |
+----+-------+

select distinct id,name as n from m_copy1 ;
+----+-------+
| id | n     |
+----+-------+
|  2 | name2 |
|  3 | name3 |
|  4 | name4 |
+----+-------+

-- 数据源
-- 单表数据源：select * from 表名；
-- 多表数据源：select * from 表名1，表名2；（交叉连接）
-- 子查询语句：select * from （select * from 表名 as 新表名）

-- 条件查询where 子句
-- 判断条件：运算符：<,>,>=,!=,like,between（是闭区间）,and,in,not in  or  &&  ||  !=
-- where是唯一一个直接从磁盘取数据的时候就来时判断条件，从磁盘取出一条记录，判断为真则放入内存
select distinct * from m_copy1 where name="name2";
+----+-------+
| id | name  |
+----+-------+
|  2 | name2 |
+----+-------+

-- group by 分组：根据字段进行分组
-- group by 字段名；分组是为了统计 
-- count() :统计分组后每组记录  count(字段)：不会统计字段中为空的地方
-- max(): 统计每组总最大数
-- min(): 统计每组中最小值
-- avg(): 统计平均值
-- sun(): 统计和
select sex,count(*),max(number),min(number),sum(number) from m_copy group by sex desc;
+-------+----------+-------------+-------------+-------------+
| sex   | count(*) | max(number) | min(number) | sum(number) |
+-------+----------+-------------+-------------+-------------+
| women |        2 |           2 |           1 |           3 |
| man   |        2 |           4 |           3 |           7 |
+-------+----------+-------------+-------------+-------------+

-- 多字段分组
select * from m_copy order by class;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    5 | name5 | women |     1 |
|    6 | name6 | women |     1 |
|   13 | name5 | man   |     1 |
|   14 | name6 | man   |     1 |
|    3 | name3 | women |     2 |
|    4 | name4 | man   |     2 |
|   11 | name3 | women |     2 |
|   12 | name4 | man   |     2 |
|    7 | name7 | women |     3 |
|    8 | name8 | women |     3 |
|   15 | name7 | man   |     3 |
|   16 | name8 | man   |     3 |
|    1 | name1 | women |     4 |
|    2 | name2 | man   |     4 |
|    9 | name1 | man   |     4 |
|   10 | name2 | man   |     4 |
+------+-------+-------+-------+
select class,sex,count(*) from m_copy group by class,sex;
+-------+-------+----------+
| class | sex   | count(*) |
+-------+-------+----------+
|     4 | women |        1 |
|     4 | man   |        3 |
|     2 | women |        2 |
|     2 | man   |        2 |
|     1 | women |        2 |
|     3 | women |        2 |
|     1 | man   |        2 |
|     3 | man   |        2 |
+-------+-------+----------+
select class,sex,count(*),group_concat(name,id) from m_copy group by class,sex;
+-------+-------+----------+-----------------------+
| class | sex   | count(*) | group_concat(name,id) |
+-------+-------+----------+-----------------------+
|     1 | man   |        2 | name513,name614       |
|     1 | women |        2 | name55,name66         |
|     2 | man   |        2 | name44,name412        |
|     2 | women |        2 | name33,name311        |
|     3 | man   |        2 | name715,name816       |
|     3 | women |        2 | name77,name88         |
|     4 | man   |        3 | name22,name19,name210 |
|     4 | women |        1 | name11                |
+-------+-------+----------+-----------------------+

-- 回溯统计  with rollup
select class,sex,count(*) from m_copy group by class;
+-------+-------+----------+
| class | sex   | count(*) |
+-------+-------+----------+
|     4 | women |        4 |
|     2 | women |        4 |
|     1 | women |        4 |
|     3 | women |        4 |
+-------+-------+----------+
select class,sex,count(*) from m_copy group by class with rollup;
+-------+-------+----------+
| class | sex   | count(*) |
+-------+-------+----------+
|     1 | women |        4 |
|     2 | women |        4 |
|     3 | women |        4 |
|     4 | women |        4 |
|  NULL | women |       16 |
+-------+-------+----------+


--having 子句：与where子句一样，进行条件判断；分组之后的结果having子句进行处理
select class,sex,count(*) from m_copy group by class,sex having count(*)>2;
+-------+-------+----------+
| class | sex   | count(*) |
+-------+-------+----------+
|     4 | man   |        3 |
|     3 | women |        3 |
+-------+-------+----------+

-- order by 根据某个字段进行升序和降序排序
-- asc 升序  desc 降序
select * from m_copy order by class;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    5 | name5 | women |     1 |
|    6 | name6 | women |     1 |
|   13 | name5 | man   |     1 |
|   14 | name6 | man   |     1 |
|    3 | name3 | women |     2 |
|    4 | name4 | man   |     2 |
|   11 | name3 | women |     2 |
|   12 | name4 | man   |     2 |
|    7 | name7 | women |     3 |
|    8 | name8 | women |     3 |
|   15 | name7 | man   |     3 |
|   16 | name8 | women |     3 |
|    1 | name1 | women |     4 |
|    2 | name2 | man   |     4 |
|    9 | name1 | man   |     4 |
|   10 | name2 | man   |     4 |
+------+-------+-------+-------+
select * from m_copy order by class,sex,id asc;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|   13 | name5 | man   |     1 |
|   14 | name6 | man   |     1 |
|    5 | name5 | women |     1 |
|    6 | name6 | women |     1 |
|    4 | name4 | man   |     2 |
|   12 | name4 | man   |     2 |
|    3 | name3 | women |     2 |
|   11 | name3 | women |     2 |
|   15 | name7 | man   |     3 |
|    7 | name7 | women |     3 |
|    8 | name8 | women |     3 |
|   16 | name8 | women |     3 |
|    2 | name2 | man   |     4 |
|    9 | name1 | man   |     4 |
|   10 | name2 | man   |     4 |
|    1 | name1 | women |     4 |
+------+-------+-------+-------+

-- limit 子句
-- limit 数据量
-- limit 起始位置，数据量（用于分页）