Mysql基本知识与命令下：-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44226962/article/details/99689091

Mysql基本知识与命令下

上次给大家分享的Mysql基本知识与命令上中，已经分享到数据定义语言，接下来给大家分享剩下的四类数据语言。
数据操作语言(DML)
关键字：INSERT、UPDATE、DELETE

说明：数据操作语言在大多数操作数据库中都是增删改

现在创建一张用于测试的表

 create table star(
 id int auto_increment,
 name varchar(20) not null,
 money float not null,
 province varchar(20) default null,
 age tinyint unsigned not null,
 sex tinyint not null,
 primary key(id)
 )engine=innodb default charset=utf8;

插入数据
- 方式1：不指定字段,添加数据的时候需要指定所有的字段值，
```
insert into star values(1,'丽丽',100000,'湖南',22,0)
```
  可以一次性插入多个，每条数据都必须使用()扩起来，使用之间使用逗号隔开:
方式2：指定字段，只需要传递指定字段值，通常使用这个方式，如：
```
insert into star (name, money, age, sex, province)
values("狗蛋", 100000,20,0,"北京"),
("小妹", 200000,20,0,"上海");
```
在这里需要说一下，在指定插入数据字段的顺序的时候，后边输入的值要与前面指定的，name字段对应values的"狗蛋"要一致，与数据库里面的字顺序无关。
当有一下字段的时候插入数据不用传值：
- 自增的字段
- 有默认值
- 可以为空
修改数据：
示例：update star set money=888 where id=3;
警告：修改的时候，一定不要忘了添加条件，否则就是修改所有的字段了
删除数据：
示例：delete from star where name=“狗蛋”;
警告：删除操作一定不要了忘了加条件，否则就是删除所有的字段了，这个很严重，但是在真实的项目中，不会真的删除，大多使用逻辑删除。

数据查询语言（DQL）
关键字：SELECT … FROM … WHERE。

基本查询：select * from star(表名);
指定字段查询：select name, money from star;
过滤重复的记录,使用“distinct”关键字：select distinct province from star;
- 说明：使用distinct 指定的额字段不能重复，指定多个字段

条件查询：

条件

条件	说明
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
！=或 <>	不等于
and	并且
or	或者
[not] between m and n	[不]在[m,n]的区间
[not] in ()	[不]在指定的集合中
[not] like 条件	模糊匹配，%表示任意字符
is [not] NULL	是否为空

示例：

select * from star where id > 2;
select * from star where id >5 and age >30;
select * from star where age between 30 and 40;
select * from star where id in (2,4,6);
select * from star where province like "湖%";
select * from star where province is null;

结果集排序（order by）
```
select name, money from star order by money desc;
select name, money, age from star order by age asc,money desc;
```
- 说明:
  - 默认是升序asc，降序desc
  - 多个字段进行排序的时候，先安排第一个排序，相同的话再按照第二个进行排序

限制结果集（limit）

 select * from star limit 3;#提取3条数据
 select * from star limit 3 offset 2;#跳过两条提取3条数据
 select * from star limit 2,3;#同上

分页：

每页 pagesize=10,page表示页码数，请写出对应的查询语句
第一页：limit 0,10
d第二页：limit 10,10
第三页：limit 20,10
第4页：limit 30,10

page页：limit (page - 1)*pagesize,pagesize

常用的聚合函数selec

函数	说明
count	统计个数
sum	求和
max	最大值
min	最小值
avg	平均值
	s

示例：

select count(*)  as c from star;    #可以给查询的字段起别名
select max(money) as max_money from star;

分组及过滤（重要）（group by…having）

select sex from star group by sex;    #以性别进行分组
select count(*) as c, sex from star group by sex;#分组以后并统计
select count(*) as c, province from star group by province having c=1;#分组统计以后过滤

多表联合查询
在查询语言中，多表联合查询是非常重要的，也是用得最多的。

首先创建两张表，用于举例，其中user的gid对应goods的id。

create table user (
id int(11) not null auto_increment,
username varchar(20) default null,
gid int(11) ,
primary key(id));


create table goods (
id int(11) not null auto_increment,
name varchar(40) default null,
price float default null,
category varchar(20) default null,
primary key(id));

隐式内连接，使用where来进行表的关联
- 说明：查询哪个用户购买了什么商品
- 示例： select username,name from user,goods where user.gid =goods.id;
显示内连接
- 说明：功能和隐式内连接一样，使用的关键字是join，一定要注意不是where，是on。
- 示例：select username,name from user join goods on user.gid=goods.id;
左外连接
- 说明：使用left jion…on进行左连接，以左边的表为主，主要显示左边的表，右边的表有对应的数据就显示，没有话就显示null
- 示例：select username,name from user left join goods on user.gid=goods.id;
右外连接
- 说明：使用right jion…on进行右连接，会显示右边的所有数据，左表有对应的数据的话就显示，没有的话就是null
- 示例：select username,name from user right join goods on user.gid=goods.id;
记录联合，使用union关键字，格式如下：
- 格式：select 语句1 union select 语句2
- 示例：select username,name from user right join goods on user.gid=goods.id union
  
  select username,name from user left join goods on user.gid=goods.id;
- union all:将两边的查询结果直接拼接到一起
- union:去重之后进行拼接
联合更新
- 示例： update user u, goods g set u.gid=3,g.price=g.price+0.1 where u.gid=g.id and u.id=2;
子（嵌套）查询
- select * from user where gid in (select id from goods);
- 说明：条件是一个sql语句

数据事务语言（DTL）
就是指一组相关的SQL操作，我们所有的操作都是事务中的。在数据库中，执行业务的基本单位是【事务】，不是以某一条SQL。数据库在默认情况下，事务是都打开的，也就是说它一种处在事务当中的，一个事务的结束，代表着下一个事务的开启。
执行commit或者rollback指令时，会结束当前事务

说明：用于测试的表的存储引擎必须是InnoDB
关键字：COMMIT、ROLLBACK、SAVEPOINT
K开启事务：禁止自动提交

set autocommit = 0;

sql

提交事务：整个事务过程没有出现问题

commit;

操作回滚：事务出现了问题，然后进行回滚，事务开始之前的状态

rollback;

事务的四大特性（ACID）：
atomic,原子性，事务是不可分割的，要么同时成功，要么同时失败；
consistency,一致性，事务一旦结束，内存中的数据和数据库中的数据是保持一致;
isolation,隔离性，事务之间互不干扰，一个事务的结束意味着下一个事务的开启；
duration，持久性，事务一旦提交，则数据持久化到数据库中，永久保存

数据控制语言（DCL）
用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成员才有权利执行数据控制语言。
关键字：GRANT、REVOKE

查看授权
- 格式：show grants [for ‘user’@‘localhost’]
- 示例：show grants [for ‘root’@‘localhost’]
- 说明：查看指定的用户权限，若不指定用户，则查看的是当前登录的用户
创建用户
- 格式：create user ‘user’@‘host’ identified by ‘password’
- 示例：create user ‘user’@‘10.20.159.%’ identified by ‘123456’
- 说明：%是通配符
用户授权
- 格式：grant 权限 privileges on 库.表 to “user”@‘host’ identified by ‘password’
- 示例： grant all privileges on test.user to ‘user’@‘10.20.159.%’ identified by '123456‘ #赋予所有权限
- 说明：
  - 权限可以是：insert,delete,update,select, all是所有的权限
  - 当不是赋予全部权限的时候，格式为：
  - grant 权限 on test.user to ‘user’@‘10.20.159.%’ identified by '123456‘
  - %表示的是任意主机
  - *表示的是所有的库和表
刷新权限：flush privileges
取消权限
- 格式：revoke 权限 privileges on 库.表 from ‘user’@‘host’
- 示例：revoke all privileges on test.* from ‘user’@‘10.20.159.%’;
删除用户
- 格式：drop user ‘user’@‘localhost’
- 示例：drop user ‘user’@‘10.20.159.%’;

接下来再说说数据的备份和恢复

备份：
- 将数据库中的数据保存到文件中，使用管理员运行命令提示符，进入到Mysql的安装路径下的bin目录下就可以按照一下操作导出导入数据库。
- 示例：mysqldump -u root -p test > test.sql，把test数据库导出为test.sql文件，文件就保存在bin目录下
- 大家也可以使用navicate把数据库导出:右键->转储sql文件的，给大家分享一个链接，是关于navicate的按照和破解的，地址如下：https://blog.youkuaiyun.com/tqs314/article/details/80760401
恢复：
- 从保存的sql、文件中，解析执行sql语句
- 示例：mysql -uroot -p test2 < test.sql,把test.sql文件数据导入到test数据库中