Mysql高级操作

时间:2021-07-19 01:58:17

多数据插入

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

主键冲突

  1. 主键冲突更新
  • 主键冲突时,更新数据

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

  1. 主键冲突替换
  • 主键冲突时,删除原来的数据,重新插入数据

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(同名连接字段)

子查询

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

2.主查询概念

  • 主查询,主要的查询对象,第一条select语句,确定的用户所有获取的数据源
  1. 关系
  • 子查询嵌入到主查询中
  • 子查询要么作为条件,要么作为数据源
  • 子查询是一句完整的select语句
  1. 分类
    |按功能父类|返回的数据|
    |-|-|
    | 表量子查询 | 一行一列|
    |列子查询 | 一列多行|
    | 行子查询 | 一行多列|
    |表子查询 | 多行多列|
    |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...] ] ] > 备份文件地址

  1. 分类
  • 整库备份

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

mysqldump -hlocalhost -p3306 - uroot -proot kkk > F:桌面sqlbeifengTextDocument.sql
  • 单表备份

  • 多表备份

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

数据还原

  1. 通过nysqldump 客户端还原

mysql -hPup 数据库 < url

  1. 通过SQL指令

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

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

用户权限管理

用户管理

  • mysql 中的所有用户信息都在mysql库的user表中
  • user表中
    • User : 用户名
    • Host : 允许访问的客户端
      • localhost : 允许本机访问
      • * 允许所有用户访问
创建用户
  • 用户: 用户名@主机地址
    • 主机地址
      • ‘’ :没有限定
  1. 使用root 用户在mysql.user表中插入新用户数据

  2. 通过专门的SQL语句

    • create user 用户名 indentified by ‘明文密码’
     create user 'root'@'' identified by '147258';
    create user user2; //不限定客户端ip ,没有密码

?

删除用户

drop user 用户名 @host

drop user [email protected]'';
修改用户密码
  • password : 加密处理
  1. 专门的SQL语句

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

  1. 使用更新语句

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用户密码

  1. 关闭MySQL服务器
  2. 通过mysqld启动服务:mysqld --skip-grant -tables; //跳过服务器权限启动服务器

  3. 修改密码
  4. 在任务管理器中终止mysqld进程
  5. 启动MYSQL服务

外键(外键关键字)

  • 外键 :foreign key
  • 外键关系 :一张表(从表)中的有一个字段,保存着指向另一张表(主表)的主键

  • 外键创建时会产生一个索引,但删除的时候只会删除索引自身不会删除该索引。

增加外键

  1. 要求:
  • 字段类型要一样
  • 属性要相同
  • 外健只能使用innodb存储引擎
  1. 在创建表时,创建外键

[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) 
    );
  1. 在创建表后,创建外键

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 索引名字; //删除索引

外键约束

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

    • ...

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

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 回滚点名字 //回滚到某个回滚点上

变量

系统变量

  1. 查看系统变量

show variables [like ‘pattern‘]

  1. 查看系统变量的值

select @@变量名 ;

  1. 修改系统变量的值

    • 局部修改 (只针对当前客户端,本次链接有效)

    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      |
 ----------- --------- ------- -------- ------- -------- ---- ------------- 
*/
  • 用于复杂的语句块中(函数、触发器...)