Mysql高级操作

多数据插入

insert into 表名 [(字段名)] values(值列表1)，(值列表2)，...

主键冲突

主键冲突更新

主键冲突时，更新数据

insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值；

主键冲突替换

主键冲突时，删除原来的数据，重新插入数据

replace into [(字段列表)] values(列表值)

蠕虫复制

成倍的增加
蠕虫复制时，要注意主键冲突

insert into 表名1 [(字段列表)] select *from /(字段列表) from 表名2

更新数据

一般采用条件更新

update 表名 set 字段名 = 新值 where 条件 limit 限制数量

删除操作

一般采用部分删除
普通的删除命令（delete from 表名）无法重置 auto_increment

truncate 表名

查询数据

select select选项字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limite 限制数量

select选项 ：系统应如何对待查询的结果
- all : 默认，保留所有查到的数据
- distinct : 去除重复的数据（所有的字段都相同）

select distinct *from 表名;

as 别名

 select distinct 字段名1 as 别名1 ,字段名2  别名2 ... from 表名;

from数据源

- 数据源只要是一个符合二维表的结构即可
- 单表数据 ： from 表名 
- 多表数据： from 表名1 ，表名2...  (字段数拼接 ，数据数相乘---笛卡尔乘积)

动态数据
- form 后面跟的不是实体表，而是一个从表中查询的一个数据表

from （select 字段列表 from 表名）as 表别名

where 子句

- 条件筛选（从数据表中获取数据的时候）
- 原理 ：在磁盘一条条获取数据时，每获取一条数据，就对其筛选（不符合的数据就不放到内存中）

group by子句

- 根据指定的字段进行分组，方便统计
- 分组后，只会保留各组的第一个数据。
- 统计函数
    -  count () :  统计每组中的数量
    - avg() : 求平均值
    - sum() : 求和
    - max() :...
    - min() : ...
	- group_concat() : 拼接
> group by 字段名

多分组

group by 字段1，字段2 ； //先按照字段1进行分组，然后将结果按字段2进行分组

insert into text2 values('xiaoming','cq',1,'female',60),('xiaoli','bj',2,'male',60),('kongming','cq',2,'female',45),('xiaoqing','sh',1,'female',75),('jiangming','gz',2,'male',30),('wangming','gz',1,'male',90);

select class , gender ,group_concat(name),avg(score),min(score),max(score) ,count(*) from text2 group by class,gender;

--结果：
+-------+--------+--------------------+------------+------------+------------+----------+
| class | gender | group_concat(name) | avg(score) | min(score) | max(score) | count(*) |
+-------+--------+--------------------+------------+------------+------------+----------+
|     1 | female | xiaoming,xiaoqing  |    67.5000 |         60 |         75 |        2 |
|     1 | male   | wangming           |    90.0000 |         90 |         90 |        1 |
|     2 | female | kongming           |    45.0000 |         45 |         45 |        1 |
|     2 | male   | xiaoli,jiangming   |    45.0000 |         30 |         60 |        2 |
+-------+--------+--------------------+------------+------------+------------+----------+

回溯统计
- 每一次分组向上统计的过程都会产生一次新的统计数据，且该数据的字段为null
group by 字段 [asc|desc] whith rollup

	select class , gender ,min(score),max(score)  from text2 group by class,gender with rollup;
	-- 结果
+-------+--------+------------+------------+
| class | gender | min(score) | max(score) |
+-------+--------+------------+------------+
|     1 | female |         60 |         75 |
|     1 | male   |         90 |         90 |
|     1 | NULL   |         60 |         90 |
|     2 | female |         45 |         45 |
|     2 | male   |         30 |         60 |
|     2 | NULL   |         30 |         60 |
|  NULL | NULL   |         30 |         90 |
+-------+--------+------------+------------+

having 子句

和 where相似用于数据筛选
having 位于 group by 子句之后，可对分组数据进行筛选（where不行）
group by 在 where之后 , having 在group by 之后 , where 之后的操作(group by,having...)是发生在内存中的

insert into text2 values('songtiti','lz',2,'female',98),('wanwu','km',3,'male',89);
select group_concat(name) ,class ,count(*)from text2 group by class having count(*)>2;

-- 结果
+------------------------------------+-------+----------+
| group_concat(name)                 | class | count(*) |
+------------------------------------+-------+----------+
| xiaoming,xiaoqing,wangming         |     1 |        3 |
| xiaoli,kongming,jiangming,songtiti |     2 |        4 |
+------------------------------------+-------+----------+

order by 子句

单字段排序

order by 字段 [asc|desc]; //asc 升序(默认)， desc 降序。

多字段排序
order by 字段1 [asc|desc] ，字段2 [asc|desc] ; // 先按字段1排序，然后对字...

// select * from text2 having class = 2 order by score asc;
select * from text2 order by class , score asc;

// 结果：
+-----------+------+-------+--------+-------+
| name      | city | class | gender | score |
+-----------+------+-------+--------+-------+
| xiaoming  | cq   |     1 | female |    60 |
| xiaoqing  | sh   |     1 | female |    75 |
| wangming  | gz   |     1 | male   |    90 |
| jiangming | gz   |     2 | male   |    30 |
| kongming  | cq   |     2 | female |    45 |
| xiaoli    | bj   |     2 | male   |    60 |
| songtiti  | lz   |     2 | female |    98 |
| wanwu     | km   |     3 | male   |    89 |
+-----------+------+-------+--------+-------+

limit 子句

限制获取的数据的数量

limit 数值

分页

limit offset ,length ; //offset 偏移量（获取数据的初始位置），length 数据条数

select * from text2 order by class , score asc limit 2,5;

-- 结果：
+-----------+------+-------+--------+-------+
| name      | city | class | gender | score |
+-----------+------+-------+--------+-------+
| wangming  | gz   |     1 | male   |    90 |
| jiangming | gz   |     2 | male   |    30 |
| kongming  | cq   |     2 | female |    45 |
| xiaoli    | bj   |     2 | male   |    60 |
| songtiti  | lz   |     2 | female |    98 |
+-----------+------+-------+--------+-------+

运算符


+ - * \	算术运算符
< > = >= <= <> =	比较运算符
and or not (非)	逻辑运算符
in()	In运算符	in(结果1，结果2，结果3..,)
is	is运算符	is null /is not null
like	like运算符

算数运算符

select name,class,score,class*score from text2;

-- 结果：
+-----------+-------+-------+-------------+
| name      | class | score | class*score |
+-----------+-------+-------+-------------+
| xiaoming  |     1 |    60 |          60 |
| xiaoli    |     2 |    60 |         120 |
| kongming  |     2 |    45 |          90 |
| xiaoqing  |     1 |    75 |          75 |
| jiangming |     2 |    30 |          60 |
| wangming  |     1 |    90 |          90 |
| songtiti  |     2 |    98 |         196 |
| wanwu     |     3 |    89 |         267 |
+-----------+-------+-------+-------------+

比较运算符

= 等价于 == ,<=> ，mysql 中没有==
<> 等价于 !=

between x and y (x<=y)

闭区间 [x , y]

select name,class,score,class*score from text2 having score between 60 and 90;

逻辑运算符

select name,class,score,class*score from text2 having score >=60 and score <=90;

In运算符

匹配结果

select * from text2 having name in('xiaoli','wanwu');

-- 结果：
+--------+------+-------+--------+-------+
| name   | city | class | gender | score |
+--------+------+-------+--------+-------+
| xiaoli | bj   |     2 | male   |    60 |
| wanwu  | km   |     3 | male   |    89 |
+--------+------+-------+--------+-------+

Is运算符

用于判断字段是否为null

is null /is not null

Like运算符

用于模糊匹配
- _ : 单个字符
- % ：多个字符

like '匹配模式'

联合查询

字段数要要一样
将多个查询的结果合并在一起
将同一张表中不同的结果合并在一起显示
将分表数据联合在一起显示

select 语句
union [union选项]
select 语句；

union选项
- distinct : 去重 (默认)
- all : 保留所有

    select *from text2 
    union all 
    select * from text2 ;

注意
- order by 在联合查询中要用括号括起来
- order by 要配合limit 才能生效

	(select * from text2 where gender='male' order by score limit 50 )
	union 
	(select * from text2 where gender='female' order by score limit 50 );

连接查询

将多张表连接到一起进行查询（会导致数据和字段了发生改变）

分类

交叉连接
内连接
外连接
自然连接

交叉连接

将两张表的数据与另一张表的彼此交叉（笛卡尔乘积）
与from 表1 ，表2 相似

表1 cross join 表2

select * from text2 cross join www;

内连接

从一张表中取出所有的数据域另一张表中的数据匹配（利用匹配条件进行匹配，成功则保留，失败舍弃）

表1 [inner] join 表2 on 匹配条件；

select * from www inner join text2 on name = name_id;
-- 或：
select * from www inner join text2 on text2.name = www.name_id;
-- 或
select * from www as W inner join text2 as T on T.name =W.name_id;		//别名
-- 或
select * from www as W inner join text2 as T where T.name =W.name_id;		//where=>on
//用where（having）代替on : 先笛卡尔积后 where 筛选数据
//（效果）等价于 ：select * from www ,text2 having text2.name = www.name_id;
--结果
+------+----------+----------+------+-------+--------+-------+
| age  | name_id  | name     | city | class | gender | score |
+------+----------+----------+------+-------+--------+-------+
|   12 | xiaoming | xiaoming | cq   |     1 | female |    60 |
+------+----------+----------+------+-------+--------+-------+

外连接

以一张表为主表，根据条件与另一张表进行连接
用主表的每一条数据，去匹配从表中的每一条数据。
主表中匹配从表中的每一条数据都失败，也会保留该数据（从表中的字段值为null）。
左连接对应的主数据在左边，右连接对应的主数据在右边。
外连接的结果的数据量一定会 >= 主表中的数据量

左连接

左表为主表

主表 left join 从表 on 连接条件

select * from text2 left join www on name = name_id;

-- 结果
+-----------+---------+-------+--------+-------+------+----------+
| name      | city    | class | gender | score | age  | name_id  |
+-----------+---------+-------+--------+-------+------+----------+
| xiaoming  | cq      |     1 | female |    60 |   12 | xiaoming |
| xiaoli    | bj      |     2 | male   |    60 | NULL | NULL     |
| kongming  | cq      |     2 | female |    45 | NULL | NULL     |
| xiaoqing  | sh      |     1 | female |    75 | NULL | NULL     |
| jiangming | gz      |     2 | male   |    30 | NULL | NULL     |
| wangming  | gz      |     1 | male   |    90 | NULL | NULL     |
| songtiti  | lz      |     2 | female |    98 | NULL | NULL     |
| wanwu     | km      |     3 | male   |    89 | NULL | NULL     |
| qingtian  | beijing |     1 | male   |     0 | NULL | NULL     |
| xiayu     | beijing |  NULL | male   |     0 | NULL | NULL     |
+-----------+---------+-------+--------+-------+------+----------+

右连接

右表为主表

主表 right join 从表 on 连接条件

 select * from text2 right join www on name = name_id;
 
 -- 结果
 +----------+------+-------+--------+-------+------+----------+
| name     | city | class | gender | score | age  | name_id  |
+----------+------+-------+--------+-------+------+----------+
| xiaoming | cq   |     1 | female |    60 |   12 | xiaoming |
| NULL     | NULL |  NULL | NULL   |  NULL |   15 | zouming  |
+----------+------+-------+--------+-------+------+----------+

Using 关键字

连接查询中的一个关键字，用于替代对应的on,进行条件匹配
使用前提，两张表的连接字段要同名。
使用using后,只会保留连接同名字段中的一个。

表1 [inner/left/right] join 表2 using(同名连接字段)

子查询

子查询概念

子查询是一种常用的计算机语言SELECT-SQL语言中嵌套查询下层的程序模块
当一个查询是另一个查询的条件时，称之为子查询
即：在一句select一句中嵌套了另一句select语句。

2.主查询概念

主查询，主要的查询对象，第一条select语句，确定的用户所有获取的数据源

关系

子查询嵌入到主查询中
子查询要么作为条件，要么作为数据源
子查询是一句完整的select语句

分类
|按功能父类|返回的数据|
|-|-|
| 表量子查询 | 一行一列|
|列子查询 | 一列多行|
| 行子查询 | 一行多列|
|表子查询 | 多行多列|
|exists子查询| 1/0|

按位置父类
where子查询	子查询位置在where条件中
from子查询	子查询的位置在from数据源中

标量子子查询

select * form 数据源1 where 条件逻辑符号 (select 字段名 form 数据源2 where 条件 )

列子查询

主查询 where 条件 in (列子查询)

select * from www where www.name in(select name from text2);

行子查询

行元素：多个字段对应的值

主查询 where 条件 [(一个行元素)] = （行子查询）

select * from text2 where (score , height)=(select max(score),min(height) from text2);

-- 结果：（身高最矮且分数最高）
+----------+------+-------+--------+-------+--------+
| name     | city | class | gender | score | height |
+----------+------+-------+--------+-------+--------+
| songtiti | lz   |     2 | female |    98 |      0 |
+----------+------+-------+--------+-------+--------+


select * from text2 where (score , height)=(select max(score),max(height) from text2);

--结果：（身高最高且分数最高）
 Empty set (0.00 sec)

表子查询

select 字段列表 from （表子查询）[where][group by ][having][order by][limit][...]

select *from (select * from text2 order by score desc) as ww where ww.class is not null group by class;

-- 结果(获取每个班级中分数最高的学生信息)
+----------+------+-------+--------+-------+--------+
| name     | city | class | gender | score | height |
+----------+------+-------+--------+-------+--------+
| wangming | gz   |     1 | male   |    90 |      0 |
| songtiti | lz   |     2 | female |    98 |      0 |
| wanwu    | km   |     3 | male   |    89 |    165 |
+----------+------+-------+--------+-------+--------+

exists子查询

查询返回的结果只有0/1

where exitst(查询语句)

where 1 ：保留所有的数据

 select * from www as W where exists(select name from text2 as T where T.name = W.name);
 
 -- 结果（表text2与表www中名字相同的学生信息）
 +------+----------+
| age  | name     |
+------+----------+
|   12 | xiaoming |
+------+----------+

列子查询关键字


In	主查询 where 条件 in(列子查询)
Any	=any(列子查询)	任意一个匹配即可
Any	<>any(列子查询)	不匹配其中的任意一个
some	与any一样
All	all(列子查询)	匹配其中的所有
All	<>all(列子查询)	不...

-- any:
select * from www where name in(select name from text2);
-- 等价于
select * from www where name =any(select name from text2);
-- 等价于
 select * from www as W where exists(select name from text2 as T where T.name = W.name);
 +------+----------+
| age  | name     |
+------+----------+
|   12 | xiaoming |
+------+----------+

-- all:
select * from www where name <>all(select name from text2); 
-- 结果（www表中学生姓名 与 表 text2中的姓名都不相同的学生信息 ）
+------+---------+
| age  | name    |
+------+---------+
|   15 | zouming |
+------+---------+

数据还原与备份

mysql 提供了一个用于备份的客户端 mysqlldump.exe
数据备份的结果都是SQL指令

数据备份

语法： mysqldump -hPup 数据库名字 [[表1 [表2...] ] ] > 备份文件地址

分类

整库备份

mysqldimp -hlocalhost -p3306 - uroot -proot 库名 > url

mysqldump -hlocalhost -p3306 - uroot -proot kkk > F:\桌面\sqlbeifeng\TextDocument.sql

单表备份
多表备份

mysqldump -hlocalhost -p3306 - uroot -proot 库名表1 表2 > url

数据还原

通过nysqldump 客户端还原

mysql -hPup 数据库 < url

通过SQL指令

source SQL文件url //必须先选库

人为复制备份备份文件的SQL指令，然后run...

用户权限管理

用户管理

mysql 中的所有用户信息都在mysql库的user表中
user表中
- User : 用户名
- Host : 允许访问的客户端
  - localhost : 允许本机访问
  - * 允许所有用户访问

创建用户

用户：用户名@主机地址
- 主机地址
  - ‘’ ：没有限定

使用root 用户在mysql.user表中插入新用户数据
通过专门的SQL语句
- create user 用户名 indentified by ‘明文密码’
```
 create user 'root'@'' identified by '147258';
```

create user user2; //不限定客户端ip ,没有密码
```

删除用户

drop user 用户名 @host

drop user root@'';

修改用户密码

password : 加密处理

专门的SQL语句

set password for 用户 = password('新的明文密码');

使用更新语句

update mysql.user set password = password(‘新的明文密码’) where 条件

set password for user1 = password('159263');

权限管理

数据权限

数据的增删改查

结构权限

结构操作（create/drop）

管理权限

权限管理（create user \grant \revoke）

授权

grant 权限列表 on 数据库.表名 to 用户 // (*.*代表整个所有数据库、 )

权限列表
- 权限之间用逗号分隔
- all privileges 代表全部权限

数据库.表名

数据库1.表1 分配数据库1中的表1的权限

数据库1.* 分配数据库1的权限（数据库1中的所有表）

*.* 所有的数据库

grant select on kkk.www to user1;		//查
grant update on kkk.www to user1;		// 增

撤权

revoke 权限列表 /all privileges on 数据库.表 from 用户

revoke all privileges on *.* from user1;

权限刷新（Flush）

将对用户的权限操作，同步到mysql.user表中

flush privileges;

忘记root用户密码

关闭MySQL服务器
通过mysqld启动服务：mysqld --skip-grant -tables; //跳过服务器权限启动服务器
修改密码
在任务管理器中终止mysqld进程
启动MYSQL服务

外键（外键关键字）

外键：foreign key
外键关系：一张表（从表）中的有一个字段，保存着指向另一张表（主表）的主键
外键创建时会产生一个索引，但删除的时候只会删除索引自身不会删除该索引。

增加外键

要求：

字段类型要一样
属性要相同
外健只能使用innodb存储引擎

在创建表时，创建外键

[constraint 外键名] foreign key(外键字段) references 主表(主键)

	create  table tableA (
		id int primary key auto_increment,
		name varchar(11) not null,
		class int ,
	foreign key(id) references text2(id) 
	);

在创建表后，创建外键

alter table 从表 add [constraint 外键名] foreign key(外键字段) reference 主表(主键)

alter table  tableA add foreign key(class) references text2(id);

-- show create table tablea;

+-----------------------------------------------------------+
| tablea | CREATE TABLE `tablea` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) NOT NULL,
  `class` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),			
  KEY `class` (`class`),		//自动添加的索引
  CONSTRAINT `tablea_ibfk_1` FOREIGN KEY (`class`) REFERENCES `text2` (`id`)
    // `tablea_ibfk_1`  : 外键名
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |	
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

删除外键

alter table 从表 drop foreign key 外键名字

 alter table tableA drop foreign key `tablea_ibfk_1`;

alter table 表名 drop index 索引名字； //删除索引

外键约束

创建外键后，从表会受限于主表的数据。
- 不能插入主表不存在的数据
- ...

可以在创建外键的时候，对外建约束进行选择性的操作

add foreign key(外键字段) reference 主表(主键) 约束模式

约束模式
- district : 严格模式，默认
- cascade : 级联模式，同步操作
- set null : 置空

事务

自动事务

当客户端发送一条SQL语句（增删改）给服务器时，服务器执行完后。不用的等待用户的同步命令，会自动同步数据。
查看自动事务是否开启

show variables like 'autocommit%';

show variables like 'autocomm%';

-- 结果
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

关闭自动事务
- commit // 提交（同步数据，事务也将被清空）
- rollback //回滚（清空之前的操作）

set autocommit = off;

手动事务

开启事务后到事务终止中的所有SQL语句都不会写入到数据库中（保存到事务日志中），但当前客户端可以查看到数据库的变化（其他的客户端看不到---没有数据同步）
事务是一个整体，若其中有一条SQL语句出错，则所有操作都失败（原子性）


start transaction	开启事务
commit	事务提交（数据同步）
rollback	回滚（撤销）
savepoint	回滚点	提前设置好回滚点，当后面操着有误时可以回滚到该点出的操作，不必rollback ---- 撤销全部操作
end transaction

savepoint 回滚点名字 // 设置回滚点
rollback to 回滚点名字 //回滚到某个回滚点上

变量

系统变量

查看系统变量

show variables [like 'pattern']

查看系统变量的值

select @@变量名；

修改系统变量的值
- 局部修改（只针对当前客户端，本次链接有效）
set 变量名 = 新值
- 全局修改（所有的客户端，所有时刻有效）
set global 变量名 = 值；

或：

select @@global.变量名 = 值；

全局的修改只针对新客户端有效（客户端要重启）

会话变量

会话变量即用户变量
会话变量对当前用户使用的客户端有效

set @变量名 := 值（:= 为专用赋值符号）
重新赋值

select @变量名 := 值 from 数据源
查看变量

select @变量名；

局部变量

局部变量用 declare声明
declare语句要在begin 和 end 之间( begin end相当于语句块 )

declare 变量名数据类型 [default 默认值...]

If分支语句

用于select 语句中

select 字段列表，if(条件，为真结果，为假结果) as 别名 from 表名

select * ,if(score>60, 'jige','bujige') as score_judge from kkk.text2;

-- 结果
/*
+-----------+---------+-------+--------+-------+--------+----+-------------+
| name      | city    | class | gender | score | height | id | score_judge |
+-----------+---------+-------+--------+-------+--------+----+-------------+
| xiaoming  | cq      |     1 | female |    60 |    175 |  1 | bujige      |
| xiaoli    | bj      |     2 | male   |    60 |    177 |  2 | bujige      |
| kongming  | cq      |     2 | female |    45 |      0 |  3 | bujige      |
| xiaoqing  | sh      |     1 | female |    75 |    182 |  4 | jige        |
| jiangming | gz      |     2 | male   |    30 |    147 |  5 | bujige      |
| wangming  | gz      |     1 | male   |    90 |      0 |  6 | jige        |
| songtiti  | lz      |     2 | female |    98 |      0 |  7 | jige        |
| wanwu     | km      |     3 | male   |    89 |    165 |  8 | jige        |
| qingtian  | beijing |     1 | male   |     0 |      0 |  9 | bujige      |
| xiayu     | beijing |  NULL | male   |     0 |    161 | 10 | bujige      |
+-----------+---------+-------+--------+-------+--------+----+-------------+
*/

用于复杂的语句块中（函数、触发器...）

posted @ 2020-01-22 11:25 Tolbert 阅读(292) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Tolbert

Mysql高级操作

多数据插入

主键冲突

蠕虫复制

更新数据

删除操作

查询数据

from数据源

where 子句

group by子句

having 子句

order by 子句

limit 子句

运算符

算数运算符

比较运算符

between x and y (x<=y)

逻辑运算符

In运算符

Is运算符

Like运算符

联合查询

连接查询

交叉连接

内连接

外连接

左连接

右连接

Using 关键字

子查询

标量子子查询

列子查询

行子查询

表子查询

exists子查询

列子查询关键字

数据还原与备份

数据备份

数据还原

用户权限管理

用户管理

创建用户

删除用户

修改用户密码

权限管理

数据权限

结构权限

管理权限

授权

撤权

权限刷新（Flush）

忘记root用户密码

外键（外键关键字）

增加外键

删除外键

外键约束

事务

自动事务

手动事务

变量

系统变量

会话变量

局部变量

If分支语句

公告