文章目录
- 主键冲突(Duplicate key)
- 表(结构)复制
- 蠕虫复制
- 清空表(可重置自增长)
- 限制记录 (limit)
- 查询数据
- select 选项
- 字段别名
- 数据源
- where条件
- group by 子句
- having 子句
- order by 子句
- limit 子句
主键冲突(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行 ;
-- 如果要替换的记录不存在,则直接进行插入,影响一行 ;
-- 选择更新部分字段
-- 语法
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
-- 语法
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
-- 语法
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
-- 当前自增长
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
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 字段列表/* from 表名 [where 条件] ;
完整语法:
select [select 选项] 字段列表 [字段别名] /* from 数据源 [where 条件子句] [group by 子句][having 子句][order by 子句][limit 子句] ;
select [select 选项] 字段列表 [字段别名] /* from 数据源 [where 条件子句] [group by 子句][having 子句][order by 子句][limit 子句] ;
完整语法是真的长,其中五子句,如果使用,则必须按照其定义的顺序使用 ;
select 选项
select
对查出来的结果进行处理 ;
All
:默认的,保留所有的结果 ;Distinct
:去重,去掉查询出来的结果中重复的;(重复,所有字段都相同的记录)
select distinct * from student ;
select distinct * from student ;
字段别名
当数据查询出来的时候,我们可以为字段起个别名,防止多表查询的时候出现字段名冲突 ;
语法:
字段名 [as] 别名 ;
字段名 [as] 别名 ;
数据源
直译为,数据的来源,关系型数据库的数据来源都是数据表,其实本质上只要是二维表都可以作为数据源 ;
数据源分类:
- 单表数据源
select * from student ;
select * from student ;
- 多表数据源
-- 语法
select * from 表1,表2...
select * from student,teacher ;
-- 语法
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
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
>,<,<=,>=,!=/<>,like,between ... and ...,in/not in
其中 between
是个闭区间,between
左值必须小于等于右值 ;
逻辑运算符:
&&(and),||(or),!(not)
&&(and),||(or),!(not)
where原理
:where
是唯一一个直接从磁盘获取数据的时候,就开始判断的条件,每次从磁盘中读取一条记录,就会进行where条件
判断,判断为真,则存进内存,判断为假,则直接放弃,读取下一条记录 ;
group by 子句
分组,根据字段进行分组(相同的放一组,不同,分到不同的组);
-- 语法
group by 字段名 ;
-- 语法
group by 字段名 ;
分组的意思,是为了统计数据,按照分组字段进行统计 ;
SQL
count() ; -- 统计分组后的记录数,每一组有多少记录 ;
max() ; -- 统计每组中的最大值 ;
min() ; -- 统计每组中的最小值 ;
avg() ; -- 统计每组的平均值 ;
sum() ; -- 统计每组的和 ;
count() ; -- 统计分组后的记录数,每一组有多少记录 ;
max() ; -- 统计每组中的最大值 ;
min() ; -- 统计每组中的最小值 ;
avg() ; -- 统计每组的平均值 ;
sum() ; -- 统计每组的和 ;
其中,count()
函数,填入 *
的时候,是统计记录数,如果填入 字段名
则统计是字段的记录数,但是假如字段的值为 null
,则不算入统计 ;
分组会自动的排序,默认是 升序排序
group by 字段名 [asc | desc ] ; -- 对分组的结果进行排序
group by 字段名 [asc | desc ] ; -- 对分组的结果进行排序
多字段分组(按照字段依次排序 )
group by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... ; -- 依次分组,然后排序
group by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... ; -- 依次分组,然后排序
可以进行多次分组,按照分组字段的顺序进行多次分组 ,分组内部再分组;
group_concat(字段)
可以对分组结果的某个字段进行字符串的拼接;
回溯统计(按照字段依次排序 )
group by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... with rollup ; -- 依次分组,然后排序
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 ] ,.... ; -- 排序中再次排序
order by 字段名 [asc | desc ] ,字段名 [asc | desc ] ,.... ; -- 排序中再次排序
在根据前面的字段的排序情况下,再对其进行排序;
limit 子句
一种限制结果的语句:限制数量;
- 限制长度(数据量)
limit 数据量
;
前面的限制记录那里,就是限制记录数; - 限制起始位置,限制数量 ,
limit 起始位置,数据长度
;
起始位置从0
开始算,第一条记录是0
;
主要用于分页;