MySQL数据库（四）--查询回顾，数据备份，用户权限管理，索引

一、查询练习

学生宿舍（一对多）

room
  id
  name
student
  id
  name
  r_id

1.每个学生的宿舍情况

select * from student s left join room r on s.r_id=r.id;

2.查询哪些学员没有宿舍

select *from student left join room on student.r_id=room.id where room.id is null;

3.查询哪个宿舍没有学生住

select *from room left join student on student.r_id=room.id where student.id is null;

4.统计每个宿舍都住了多少人

select room.name,count(student.id) from room left join student on student.r_id=room.id group by room.name;

多表查询练习

type

shop

user

shop_user

1.查询每一个商品分类下有多少个商品

select t.id,t.type,count(s.id) from type t left join shop s on t.id=s.t_id group by t.id;

2.所有用户购买记录

select u.name,s.name from user u inner join shop_user s_u on u.id=s_u.u_id inner join shop s on s_u.s_id=s.id;

3.小美都买了什么

select u.name,s.name from user u inner join shop_user s_u on u.id=s_u.u_id inner join shop s on s_u.s_id=s.id where u.name='小美';

4.所有商品的销售记录

select s.id,s.name,u.name from shop s inner join shop_user s_u on s.id=s_u.s_id inner join user u on s_u.u_id=u.id order by s.id;

5.查看商品的售卖数量

select s.name,count(*) from shop s inner join shop_user s_u on s.id=s_u.s_id inner join user u on s_u.u_id=u.id group by s.id;

6.超短裙被谁买过

select s.id,s.name,u.name from shop s inner join shop_user s_u on s.id=s_u.s_id inner join user u on s_u.u_id=u.id where s.name='超短裙';

二、数据备份

表

导出

格式：mysqldump -uroot -p 库名 表名>表名.sql（默认当前路径）

会出现一个文件

导入

格式：mysql -uroot -p 库名<(备份文件地址)名.sql

数据库

导出

格式：mysqldump -uroot -p 库名>库名.sql

会出现一个文件

导入

三、权限管理

用户：连接的用户
权限：允许用户操作的范围
组：权限的集合

修改密码

MySQL中有一个默认的mysql库，库中有个user表，这个表中存放的就是用户信息。

1.切换到mysql库中

2.执行update user set authentication_string=password(‘123456’) where User=‘root’ and Host=‘localhost’;

3.重启服务

忘记密码

1.修改配置文件my.cnf，跳过权限认证
  在mysqld中的最后一行加上skip-grant-tables

2.重启服务
  不用输入密码即可直接登录

3.执行修改密码操作

4.把配置文件修改回来

5.重启服务，使用新密码登录即可

创建用户（用户名可重名）

格式：create user’用户名’@‘ip地址’ identified by’密码’;

ip地址可以写为%，表示允许该用户在任何ip地址访问。

例：create user ‘lisi’@‘1.1.1.2’ identified by ‘123123’;–该用户只能在ip地址为1.1.1.2的设备登录

  create user ‘lisi’@‘localhost’ identified by ‘123123’;–仅本机可连接

给用户授权

格式：grant 权限 on 库名.表名 to 用户@‘ip地址’;

例：

给李四添加所有库所有表的查询权限：
grant select on . to’lisi’@’%’;

给远程连接的zhaoliu用户添加demo1数据库下所有表的所有权限：
grant all on demo1.* to ‘zhaoliu’@’%’;

删除用户

四、索引

一种高效获取数据的存储结构，类似于字典的目录。

不要给重复值很多的字段，添加索引，不然反而会降低查询速度。

使用目的

可以提高查询数据的效率，减少IO的操作。

但创建索引时，数据库会额外的创建一张表（或文件），来维护索引，会占据磁盘的资源，一般索引的空间比数据大。

优缺点

优：提高数据的查询速度，减少IO操作。
缺：维护成本高，会降低其他操作（增删改）的执行速度。

!!!索引不是越多越好。

最左原则：（将索引字段添加在最左）
select * from user where username=‘zhangsna’ and age=18;

索引分类

普通索引：index，可以为空，可以重复（当使用物理外键时，就默认给该字段添加了普通索引）。

唯一索引：unique，可以为空，但唯一。

主键索引：promary key，不能为空，且唯一。一般要求一个表要有至少一个主键，但这不是必须的。

多列索引：将多个字段绑定到一起，添加一个索引。

添加索引

格式：alter table 表名 add 索引类型【索引名】(字段名);

不指定索引名时，默认为字段名。

查看索引

格式：show index from 表名\G;

删除索引

格式：drop index 索引名 on表名;

!!!删除主键索引时（必须保证该字段无自增属性）
格式：alter table 表名 drop primary key;

主键索引

普通索引

多列索引

补充–添加物理外键

1.在创建表时添加

2.建表之后添加

格式：alter table 表名 add foreign key(字段名) references 关联的表名(关联的字段);