轩小陌的Python笔记-day26 必备SQL语句、表关系及授权

于 2022-04-13 10:09:09 发布
阅读量2.6k 收藏
点赞数 1
文章标签： python
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hjjqazwsx/article/details/124141304
版权

day26 必备SQL和表关系及授权

在这里插入图片描述

课程目标：掌握开发中最常见的SQL语句和表关系及授权相关知识点。

课程概要：

  • 必备SQL（8个必备）
  • 表关系
  • 授权

1. 必备SQL语句

上一节讲解了最基础SQL语句：增删改查，其实在日常的开发中还有很多必备的SQL语句。

这一部分的SQL语句都是围绕着对表中的数据进行操作。

例如：现在创建如下两张表。

在这里插入图片描述

create database day26db default charset utf8 collate utf8_general_ci;

create table depart(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table info(
	id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int
)default charset=utf8;

insert into depart(title) values("开发"),("运营"),("销售");

insert into info(name,email,age,depart_id) values("轩小陌","xuan@live.com",19,1);
insert into info(name,email,age,depart_id) values("于超","pyyu@live.com",49,1);
insert into info(name,email,age,depart_id) values("alex","alex@live.com",9,2);
insert into info(name,email,age,depart_id) values("tony","tony@live.com",29,1);
insert into info(name,email,age,depart_id) values("kelly","kelly@live.com",99,3);
insert into info(name,email,age,depart_id) values("james","james@live.com",49,1);
insert into info(name,email,age,depart_id) values("李杰","lijie@live.com",49,1);

1.1 条件

根据条件搜索结果。

在这里插入图片描述

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4;  

select * from info where name = '轩小陌' and age = 19;
select * from info where name = 'alex' or age = 49;
select * from info where (name = '李杰' or email="pyyu@live.com")  and age=49;

select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);		# select * from info where id in (1,2,3);

select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);

select * from info where info.id > 1;
select * from (select * from info where id>2) as T where age > 10;
select * from (select * from info where id>2) as T where T.age > 10;

1.2 通配符

一般在数据表中用于模糊搜索。

在这里插入图片描述

select * from info where name like "%小%";
select * from info where name like "%小";
select * from info where email like "%@live.com";
select * from info where name like "轩%陌";
select * from info where name like "k%y";
select * from info where email like "xuan%";

select * from info where email like "_@live.com";
select * from info where email like "_uan@live.com";
select * from info where email like "__an@live.com";
select * from info where email like "_ua_@live.co_";

注意：数据量少可以使用以上模糊搜索，但如果数据量大便不适合使用该模糊搜索的方式。

1.3 映射

获取指定的列。

在这里插入图片描述

select * from info;
建议：少用select * ,根据自己需求选取需要的列。

select id, name					  from info;
select id, name as NM 			  from info;
select id, name as NM, 123 as age from info;

select 
	id,
	name,
	666 as num,
	( select max(id) from depart ) as mid, -- max/min/sum
	( select min(id) from depart) as nid, -- max/min/sum
	age
from info;

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1,
	( select title from depart where depart.id=info.id) as x2
from info;

select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1,
	case depart_id when 1 then "第1部门" else "其他" end v2,
	case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
	case when age<18 then "少年" end v4,
	case when age<18 then "少年" else "油腻男" end v5,
	case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

1.4 排序

在这里插入图片描述

select * from info order by age desc; -- 倒序
select * from info order by age asc;  -- 顺序

select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age顺序排列；如果age相同则按照id倒序排列。

select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;

1.5 取部分

用于获取部分数据。

在这里插入图片描述

select * from info limit 5;   										-- 获取前5条数据
select * from info order by id desc limit 3;						-- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3;			-- 先取符合条件的数据，再排序，最后获取前3条数据


select * from info limit 3 offset 2;								-- 从索引2开始，向后顺序获取前3条数据

数据库表中：1000条数据。

  • 第一页：select * from info limit 10 offset 0;
  • 第二页：select * from info limit 10 offset 10;
  • 第三页：select * from info limit 10 offset 20;
  • 第四页：select * from info limit 10 offset 30;
  • …

1.6 分组

在这里插入图片描述

-- 按age分组后取每组id最大的，取每组id最小的，每组计数，每组id求和，每组id求平均值
select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

-- 按age分组后对每组数据计数
select age,count(1) from info group by age;		

-- 按depart_id分组后对每组数据计数
select depart_id,count(id) from info group by depart_id;

-- 按depart_id分组后对每组数据计数，再取计数大于2的数据
select depart_id,count(id) from info group by depart_id having count(id) > 2;

-- 按age分组后取每组id最大的那条数据，再取info表中有这些id的数据
select * from info where id in (select max(id) from info group by age);	

-- 按age分组后对每组数据计数，再取计数大于2的数据
select age,count(id) from info group by age having count(id) > 2;
-- 先取info中id>4的数据，再按age分组，再对每组数据计数，最后取计数大于2的数据
select age,count(id) from info where id > 4 group by age having count(id) > 2; 

到目前为止SQL执行语句的优先级顺序：
    where 
    group by
    having 
    order by
    limit 

select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
-- 要查询的表info
-- 条件 id>2
-- 根据age分组
-- 对分组后的数据再根据聚合条件过滤 count(id)>1
-- 根据age从大到小排序
-- 获取第1条

1.7 左右连表

将多个表连接起来进行查询。

在这里插入图片描述

展示 info 表中所有的用户信息 + 部门名称列：

-- 格式一：主表 left outer join 从表 on 连接条件(主表.x = 从表.id)
-- 简写：主表 left join 从表 on 连接条件(主表.x = 从表.id)

select * from info left outer join depart on info.depart_id = depart.id;

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

-- 格式二：从表 right outer join 主表 on 连接条件(主表.x = 从表.id)
-- 简写：主表 right join 从表 on 连接条件(主表.x = 从表.id)

select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

在这里插入图片描述

可见两种连接方式，展示的数据信息是一样的，只是顺序不同。

为了更加直观地展示两种连接的区别，我们先在 depart 表中额外插入一条数据：

insert into depart(title) values("运维");

这样一来主从表就有区别：

  • info主表，就以 info 数据为主，depart 为辅。

    select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

  • depart主表，就以 depart 数据为主，info 为辅。

    select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

-- 内连接格式：表1 inner join 表2 on 连接条件(表1.x = 表2.id)
select * from info inner join depart on info.depart_id=depart.id;

到目前为止SQL执行语句的优先级顺序：
    join 
    on 
    where 
    group by
    having 
    order by
    limit

注意：不仅两张表可以连接，多张表也可以连接。

1.8 联合

在这里插入图片描述

-- 列数相同即可上下连接，数据类型不同也可以连接成功
select id,title from depart 
union
select id,name from info;

select id,title from depart 
union
select email,name from info;

-- 上下连接会自动去重
select id from depart 
union
select id from info;

-- 上下连接也可以不进行去重(保留所有)
select id from depart 
union all
select id from info;

小结

到目前为止，已经掌握了如下相关指令（SQL语句）：

  • 数据库
  • 数据表
  • 数据行
    • 增加
    • 删除
    • 修改
    • 查询（各种变着花样的查询）

2.表关系

在开发项目时，需要根据业务需求去创建很多的表结构，以此来实现业务逻辑，一般表结构有三类：

2.1 单表，单独一张表就可以将信息保存。

在这里插入图片描述

2.2 一对多，需要两张表来存储信息，且两张表存在 一对多 或 多对一关系。

、在这里插入图片描述

2.3 多对多，需要三张表来存储信息，两张单表 + 关系表，创造出两个单表之间多对多关系。

在这里插入图片描述

  • 在上述的表：一对多的 info.depart_id字段、多对多的 boy_girl.boy_id 、girl_id 直接用整型存储就可以，因为他们只要存储关联表的主键ID即可。

  • 另外在开发中往往还会为他们添加一个外键约束，保证某一个列的值必须是其他表中的特定列已存在的值，例如：info.depart_id的值必须是 depart.id中已存在的值。

一对多示例：
在这里插入图片描述

create table depart(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;


create table info(
	id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int not null,
    constraint fk_info_depart foreign key (depart_id) references depart(id)		-- 创建外键约束
)default charset=utf8;

如果表结构已创建好了，额外想要增加外键约束：

alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

删除外键约束：

alter table info drop foreign key fk_info_depart;

多对多示例：

在这里插入图片描述

create table boy(
	id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;

create table girl(
	id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;


create table boy_girl(
	id int not null auto_increment primary key,
    boy_id int not null,
    girl_id int not null,
    constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),			-- 创建外键约束
    constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)		-- 创建外键约束
)default charset=utf8;

如果表结构已创建好了，额外想要增加外键外键约束：

alter table boy_girl add constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id);
alter table boy_girl add constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id);

删除外键约束：

alter table info drop foreign key fk_boy_girl_boy;
alter table info drop foreign key fk_boy_girl_girl;

在以后项目开发时，设计表结构及其关系的是一个非常重要的技能。一般项目开始开发的步骤：

  • 需求调研
  • 设计数据库表结构（根据需求）
  • 项目开发（写代码）

大量的工作应该放在前2个步骤，前期的设计完成之后，后续的功能代码开发就比较简单了。

案例：

在这里插入图片描述

-- 创建数据库
create database course default charset utf8 collate utf8_general_ci;
-- 进入数据库
use course;

-- 创建用户表
create table info(
	id int not null auto_increment primary key,
    username varchar(16) not null,
    mobile char(11) not null,
    password varchar(64) not null
)default charset=utf8;

-- 创建课程表
create table course(
	id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;

-- 创建模块表
create table module(
	id int not null auto_increment primary key,
    title varchar(16) not null,
    course_id int not null,
    constraint fk_module_course foreign key (course_id) references course(id)
)default charset=utf8;

-- 创建课时表：day
create table day(
	id int not null auto_increment primary key,
    title varchar(16) not null,
	module_id int not null,
    constraint fk_day_module foreign key (module_id) references module(id)
)default charset=utf8;

-- 创建课时表：video
create table video(
	id int not null auto_increment primary key,
    title varchar(16) not null,
	day_id int not null,
    constraint fk_video_day foreign key (day_id) references day(id)
)default charset=utf8;

-- 创建模块开通表
create table module_record(
	id int not null auto_increment primary key,
    user_id int not null,
    module_id int not null,
    constraint fk_user_id foreign key module_record(user_id) references info(id),
    constraint fk_module_id foreign key module_record(module_id) references module(id)
)default charset=utf8;

3. 授权

之前我们无论是基于 Python 代码还是基于 MYSQL 自带的客户端 去连接 MySQL 时，均使用的是root账户，拥有对 MySQL 数据库操作的所有权限。

在这里插入图片描述

如果有多个程序的数据库都放在同一个MySQL中，且这些程序都使用同一个root账户，那就存在风险了。这种情况该怎么办呢？

在MySQL中支持创建账户，并给账户分配权限，例如：只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的读权限等。

3.1 用户管理

在 MySQL 的默认数据库 mysql 中的 user 表中存储着所有的账户信息（含账户、权限等）。

mysql> select user,authentication_string,host from mysql.user;
+----------------------------------+-------------------------------------------+-------------------------------+
| user                             | authentication_string                     | host                          |
+----------------------------------+-------------------------------------------+-------------------------------+
| root                             | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | localhost                     |
| mysql.session                    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
| mysql.sys                        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
+----------------------------------+-------------------------------------------+-------------------------------+
3 rows in set (0.00 sec)
3.1.1 创建和删除用户
-- 格式：create user '用户名'@'连接者的IP地址' identified by '密码';

-- 创建用户，限制连接ip为：127.0.0.1
create user xuanxiaomo@127.0.0.1 identified by 'root123';
-- 删除用户
drop user xuanxiaomo@127.0.0.1;

-- 创建用户，限制连接ip为：127.0.0.*
create user xuanxiaomo@'127.0.0.%' identified by 'root123';
-- 删除用户
drop user xuanxiaomo@'127.0.0.%';

-- 创建用户，不限制连接ip
create user xuanxiaomo@'%' identified by 'root123';
-- 删除用户
drop user xuanxiaomo@'%';

-- 创建用户，用户名统一用引号(可加可不加)
create user 'xuanxiaomo'@'%' identified by 'root123';
-- 删除用户
drop user 'xuanxiaomo'@'%';
3.1.2 修改用户
-- 格式：rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

rename user xuanxiaomo@127.0.0.1 to xuanxiaomo@localhost;
rename user 'xuanxiaomo'@'127.0.0.1' to 'xuanxiaomo'@'localhost';
3.1.3 修改密码
-- 格式：set password for '用户名'@'IP地址' = Password('新密码')

set password for xuanxiaomo@'%' = Password('123123');
set password for 'xuanxiaomo'@'%' = Password('123123');

3.2 授权管理

创建好用户之后，就可以为用户进行授权了。

3.2.1 授权
-- 格式：grant 权限 on 数据库.表 to '用户'@'IP地址'

grant all privileges on *.* TO 'xuanxiaomo'@'localhost';         -- 用户xuanxiaomo拥有所有数据库的所有权限
grant all privileges on day26.* TO 'xuanxiaomo'@'localhost';     -- 用户xuanxiaomo拥有数据库day26的所有权限
grant all privileges on day26.info TO 'xuanxiaomo'@'localhost';  -- 用户xuanxiaomo拥有数据库day26中info表的所有权限

grant select on day26.info TO 'xuanxiaomo'@'localhost';          -- 用户xuanxiaomo拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'xuanxiaomo'@'localhost';      -- 用户xuanxiaomo拥有数据库day26所有表的查询和插入权限

flush privileges;   											 -- 将授权数据读取到内存中，从而让权限立即生效

  • 对于权限

    all privileges  		-- 除grant外的所有权限
select          		-- 仅查权限
select,insert   		-- 查和插入权限
...
usage                   -- 无访问权限
alter                   -- 使用alter table
alter routine           -- 使用alter procedure和drop procedure
create                  -- 使用create table
create routine          -- 使用create procedure
create temporary tables -- 使用create temporary tables
create user             -- 使用create user、drop user、rename user和revoke  all privileges
create view             -- 使用create view
delete                  -- 使用delete
drop                    -- 使用drop table
execute                 -- 使用call和存储过程
file                    -- 使用select into outfile 和 load data infile
grant option            -- 使用grant 和 revoke
index                   -- 使用index
insert                  -- 使用insert
lock tables             -- 使用lock table
process                 -- 使用show full processlist
select                  -- 使用select
show databases          -- 使用show databases
show view               -- 使用show view
update                  -- 使用update
reload                  -- 使用flush
shutdown                -- 使用mysqladmin shutdown(关闭MySQL)
super                   -- 􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
replication client      -- 服务器位置的访问
replication slave       -- 由复制从属使用

  • 对于数据库和表

    数据库名.*            -- 数据库中的所有表
数据库名.表           -- 数据库中的某张表
数据库名.存储过程名    -- 数据库中的存储过程
*.*                 -- 所有数据库的所有表
3.2.2 查看授权
-- 格式：show grants for '用户'@'IP地址'

show grants for 'xuanxiaomo'@'localhost';
show grants for 'xuanxiaomo'@'%';
3.2.3 取消授权
-- 格式：revoke 权限 on 数据库.表 from '用户'@'IP地址'

revoke ALL PRIVILEGES on day26.* from 'xuanxiaomo'@'localhost';
revoke ALL PRIVILEGES on day26db.* from 'xuanxiaomo'@'%';
flush privileges;   -- 将取消授权数据读取到内存中，从而让取消授权立即生效

一般情况下，在很多的公司，数据库都是由 DBA 来统一进行管理，DBA为每个项目的数据库创建用户，并赋予相关的权限。

今日总结

本节主要讲解的三大部分的知识点：

  • 常见SQL语句，项目开发中使用最频繁的知识点。
  • 表关系，项目开发前，项目表结构设计时必备知识点。
    • 单表
    • 一对多
    • 多对多
  • 授权，在 MySQL 中创建用户并赋予相关权限。

今日作业

详见 day27

轩小陌小轩
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值