文章目录
主键冲突（Duplicate key）
当插入数据的时候，假设主键已经存在，则一定会插入失败；
但是有时候，我们就是想直接替换掉原来的数据，但是我们又不想先删除，再赋值，就想一步到位 ；
有两种办法：
-- 选择更新部分字段
-- 语法
insert into 表名[字段列表] values(值列表)
on duplicate key update
字段名 = 新值 ； -- 没有set
-- 插入主键冲突数据
mysql> insert into student values(3,'haha','男')
-- 处理冲突，如果没发生冲突，则直接插入上面的数据，不执行下面的更新
-> on duplicate key update
-- 更新想要更新的字段的值，多个字段间用逗号间隔
-> sex = 'female',name = 'dd' ;
-- 最终影响2行
Query OK, 2 rows affected
-- 小结
-- 为什么插入一条记录是影响2行
-- 因为，先插入的时候，发现数据已经存在了，会导致插入失败，然后再进行更新操作
-- 也就是插入失败一次，更新一次，一共2次 ；
-- 如果存在自增长，那么自增长在插入失败的时候，会被触发，因为不管成功与否，你是执行插入了 ；
-- -----------------
-- 如果没有产生冲突，则直接插入成功，影响一行 ；
-- --------------------------------------------
-- 直接替换掉原来的数据
replace into 表名[字段列表] values(值列表) ;
mysql> replace into student values(3,'ww','female') ;
Query OK, 2 rows affected
-- 影响2行的原因，是因为，它是先删除，再插入，因此影响2行 ；
-- 如果要替换的记录不存在，则直接进行插入，影响一行 ；
表（结构）复制
有时候，我们想测试线上数据库，需要在本地搭建的数据库环境中，将线上的数据导进来，我们一般是不想去备份线上数据库，或者我们只需要测试部分表，备份整个数据库，显得意义不大 ；
这时候，我们可以使用表复制，将我们需要的表复制过来，仅仅是复制表结构，表中数据不会得到复制，然后使用 蠕虫复制，导进来数据 ；
-- 语法
create table 表名 like 数据库.表名;
mysql> create table my_copy_student like student ;
Query OK, 0 rows affected
mysql> desc student ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(10) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
mysql> desc my_copy_student ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(10) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
蠕虫复制
从已有的数据中获取数据，然后将获取的数据新增到表中，使得数据成倍增加 ；
-- 语法
insert into 表名[(字段列表)] select 字段列表/* from 表名;
--从自己本身复制 ，数据在成倍的增加
mysql> insert into student(name,sex) select name,sex from student ;
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
mysql> insert into student(name,sex) select name,sex from student ;
Query OK, 10 rows affected
Records: 10 Duplicates: 0 Warnings: 0
mysql> insert into student(name,sex) select name,sex from student ;
Query OK, 20 rows affected
Records: 20 Duplicates: 0 Warnings: 0
mysql> insert into student(name,sex) select name,sex from student ;
Query OK, 40 rows affected
Records: 40 Duplicates: 0 Warnings: 0
-- 从其他表复制，表结构相同
mysql> insert into my_copy_student select * from student ;
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
不止可以从自己本身表复制，只要是表结构一样的表，都可以进行复制 ；
蠕虫复制的意义：
- 从已有的表复制数据到其他表 ；
- 快速的膨胀数据，以达到压力测试数据的要求 ；
清空表（可重置自增长）
-- 当前自增长
mysql> select auto_increment from information_schema.tables where tables.table_schema = 'database' and tables.table_name = 'student' ;
+----------------+
| auto_increment |
+----------------+
| 109 |
+----------------+
1 row in set
mysql> -- 摧毁表，然后重建表
-> truncate student ;
Query OK, 80 rows affected
-- 再次查询 自增长是 1
mysql> select auto_increment from information_schema.tables where tables.table_schema = 'database' and tables.table_name = 'student' ;
+----------------+
| auto_increment |
+----------------+
| 1 |
+----------------+
1 row in set
有时候，我们想清空某张表，可以使用 truncate 代替 delete ；
前者是直接摧毁表，而不是一条一条的删除，因此更快；
限制记录 （limit）
更新、删除 是可以按照要求更新或者删除一部分数据的 ；
mysql> select * from my_copy_student ;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 2 | dog | male |
| 3 | ww | female |
| 12 | 哈哈 | 男 |
| 22 | haha | NULL |
| 33 | haha | 男 |
+----+------+--------+
5 rows in set
-- 只更新一条 limit 1 ；
mysql> update my_copy_student set name = 'ww' where name = 'haha' limit 1 ;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from my_copy_student ;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 2 | dog | male |
| 3 | ww | female |
| 12 | 哈哈 | 男 |
| 22 | ww | NULL |
| 33 | haha | 男 |
+----+------+--------+
5 rows in set
-- 只删除一条
mysql> delete from my_copy_student where name = 'ww' limit 1 ;
Query OK, 1 row affected
mysql> select * from my_copy_student ;
+----+------+------+
| id | name | sex |
+----+------+------+
| 2 | dog | male |
| 12 | 哈哈 | 男 |
| 22 | ww | NULL |
| 33 | haha | 男 |
+----+------+------+
4 rows in set
查询数据
基础语法：
select 字段列表/* from 表名 [where 条件] ；
完整语法：
select [select 选项] 字段列表 [字段别名] /* from 数据源 [where 条件子句] [group by 子句][having 子句][order by 子句][limit 子句] ；
完整语法是真的长，其中五子句，如果使用，则必须按照其定义的顺序使用 ；
select 选项
select 对查出来的结果进行处理 ；
All ：默认的，保留所有的结果 ；
Distinct：去重，去掉查询出来的结果中重复的；（重复，所有字段都相同的记录）
select distinct * from student ;
字段别名
当数据查询出来的时候，我们可以为字段起个别名，防止多表查询的时候出现字段名冲突 ；
语法：
字段名 [as] 别名 ；
数据源
直译为，数据的来源，关系型数据库的数据来源都是数据表，其实本质上只要是二维表都可以作为数据源 ；
数据源分类：
-
单表数据源
select * from student ; -
多表数据源
-- 语法 select * from 表1,表2... select * from student,teacher ;这样查出来的结果是个 笛卡尔积 ；
也就是
表1的每一条记录都会去匹配表2的数据，反过来表2也是这样，每一条记录都匹配表1的记录 ；最后得到的结果记录数就是，表1的记录数 x 表2的记录数 ；（多张表以此类推下去）
-
查询语句
也叫 子查询 ；数据的来源是一条查询语句，因为查询语句查询的结果，其实就是一个二维表，满足数据源是二维表的要求 ；
select * from (select * from student) as 表名（别名） mysql> select * from (select * from my_copy_student ) haha ; +----+------+------+ | id | name | sex | +----+------+------+ | 2 | dog | male | | 12 | 哈哈 | 男 | | 22 | ww | NULL | | 33 | haha | 男 | +----+------+------+ 4 rows in set
where条件
where 子句：用来判断数据，筛选数据 ；
where 子句返回结果：0或者1，0 代表 false ，1代表true ;
判断条件：
比较运算符：
>，<，<=，>=，!=/<>，like，between ... and ...，in/not in
其中 between 是个闭区间，between 左值必须小于等于右值 ；
逻辑运算符：
&&(and)，||（or），!(not)
where原理 ：where 是唯一一个直接从磁盘获取数据的时候，就开始判断的条件，每次从磁盘中读取一条记录，就会进行where条件判断，判断为真，则存进内存，判断为假，则直接放弃，读取下一条记录 ；
group by 子句
分组，根据字段进行分组（相同的放一组，不同，分到不同的组）；
-- 语法
group by 字段名 ;
分组的意思，是为了统计数据，按照分组字段进行统计 ；
SQL 提供了一系列的统计函数：
count() ; -- 统计分组后的记录数，每一组有多少记录 ；
max() ; -- 统计每组中的最大值 ；
min() ; -- 统计每组中的最小值 ；
avg() ; -- 统计每组的平均值 ；
sum() ; -- 统计每组的和 ；
其中，count() 函数，填入 * 的时候，是统计记录数，如果填入 字段名 则统计是字段的记录数，但是假如字段的值为 null ，则不算入统计 ；
分组会自动的排序，默认是 升序排序 ；
group by 字段名 [asc | desc ] ； -- 对分组的结果进行排序
多字段分组（按照字段依次排序 ）
group by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... ； -- 依次分组，然后排序
可以进行多次分组，按照分组字段的顺序进行多次分组 ，分组内部再分组；
group_concat(字段)
可以对分组结果的某个字段进行字符串的拼接；
回溯统计（按照字段依次排序 ）
group by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... with rollup ； -- 依次分组，然后排序
每层分组都会向上一层回溯自己分组的总记录数 ；
having 子句
与 where 子句一样，进行条件判断 ；
having 能做 where 能做的许多事 ，但是 where却不能 做许多having 可以做到事；
-
where不能对数据进行分组操作 ，或者说统计函数，只有having可以用 ；原因 ：
where是针对磁盘数据的进行判断，数据进入内存以后，分组以后，where就不能再次对数据进行操作了，只能使用having来操作 ； -
having可以使用别名，但是where不可以使用别名；原因 ：
where是针对磁盘数据的进行判断，再读磁盘数据的时候，只有字段名可以读，当数据进入内存以后，才会有别名，而where不能操控 内存中的数据，因此不可以使用别名；只能使用having来操作 ；
order by 子句
根据某个字段进行升序或者降序排序，依赖校对集 ；
多字段排序（按照字段依次排序 ）
order by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... ； -- 排序中再次排序
在根据前面的字段的排序情况下，再对其进行排序；
limit 子句
一种限制结果的语句：限制数量；
-
限制长度（数据量）
limit 数据量；前面的限制记录那里，就是限制记录数；
-
限制起始位置，限制数量 ，
limit 起始位置，数据长度；起始位置从
0开始算，第一条记录是0;主要用于分页；