Mysql基本知识与命令下：

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.csdn.net/tqs314/article/details/80760401
• 恢复：
  • 从保存的sql、文件中，解析执行sql语句
  • 示例：mysql -uroot -p test2 < test.sql,把test.sql文件数据导入到test数据库中