统计性别为男的用户人数MySQL

转载

架构领航博主 2024-09-15 20:53:50

文章标签 统计性别为男的用户人数MySQL 爬虫 python 数据挖掘数据 文章分类 MySQL 数据库

本周知识回顾

理论基础：关系代数（关系运算、集合论、一阶谓词逻辑）

主键（primary key）外键（foreign key）
列（column）：字段（field） —> attribute —> 列数（degree）

数据定义语言：create / drop / alter
数据操作语言：insert / delete / update
数据查询语言：select
数据控制语言：grant / revoke

语法：create table tb_xxx (…) engine=innodb default charset utf8mb4;
数据类型的选择：
? data types —> ? int~ 整数：int (integer) / bigint —> unsigned / primary key —> auto_increment
int —> -2^31 ~ 2^31-1 / 0 ~ 2^32-1
bigint —> -2^63 ~ 2^63 - 1 / 0 ~ 2^64-1
boolean —> tinyint —> 0 / 1~ 小数：float（不推荐） / double（推荐）/ decimal
经验：涉及到钱的业务一律不使用小数，都用整数表示~ 字符串：char / varchar（推荐使用）/ —> clob（text / longtext）—> 放文件路径或者资源的URL
字符集 —> utf8mb4 —> 排序规则 —> 默认
和字符串相关的函数：
- char_length / length / concat / format / left / right / substring / trim / ucase/ lcase~ 日期时间：year / date / time / datetime / timestamp（不推荐）
和日期时间相关的函数：
- adddate / subdate / curdate / now / datediff / year / quarter / month / day / hour / minute / second / weekday

JSON对象：{“key1”: “value1”, “key2”: “value2”, …}
json_unquote(json_extract(col_name, ‘$.key1’)) as alias_name col_name ->> ‘$.key1’ as alias_name elem member of (col_name->’$’) json_contains(col_name->’$’, ‘[e1, e2, …]’) json_overlaps(col_name->’$’, ‘[e1, e2, …]’)
MySQL特有的类型：set / enum

not null —> 非空约束 default —> 默认值约束 check —> 检查约束 primary key —> 主键约束 foreign key —> 外键约束 unique —> 唯一约束
外键事务（把多个操作视为不可分割的原子性操作，要么全做，要么全不做）—> transaction —> 一致性行级锁（增删改的操作不需要锁住整个表，只需要锁住对应的行，能支持高并发）
create table … as select … from …;
create table … as select … from … where 1<>1;

drop table …;
alter table … add column …;
alter table … drop column …;
alter table … modify column …;
alter table … change column …;
alter table … add constraint …;
alter table … drop constraint …;

ER图和表关系

矩形框：实体 —> 表椭圆框：属性 —> 字段

一对一：主键一对一 / 多对一特例（推荐）
一对多/多对一：在多的一方添加外键列
多对多：通过中间表将多对多转换成两个一对多关系

insert into … values (…, …, …); —> 不推荐
insert into … (…, …, …) values (…, …, …);
insert into … (…, …, …) values (…), (…), (…);
insert into … select … from …;

delete from … where key=…;
删除操作一定要慎重的执行，一定是带条件的删除；很多产品里面的删除操作，其实都不是真正的删除，可能是一个更新或者数据转移的操作如果配置了bin-log，可以通过日志进行误删除的恢复（可以通过专业工具）
如果要删除全表而且不走日志 —> truncate table …;

update … set …, … where key=…;
show variables like ‘sql_safe_updates’;
select @@sql_safe_updates;
set @@sql_safe_updates=on; —> 不能够不带条件更新
set @@sql_safe_updates=off; —> 不带条件更新全表
—> 更新的时候也可以使用 case … when … else … 语法构造分支结构

语法：

select … as …, … as …, … as …, …

—> 窗口函数 over (partition by … order by …) —> 排名 / TopN

—> row_number / rank / dense_rank / lead / first_value / cume_dist

from t1, t2, …

[from t1 … join t2 on …]

where … and … or … —> like / regexp

group by …, … —> 聚合函数（max / min / avg / sum / count / stddev_pop / var_pop）

having … and … or …

order by … desc, … asc

limit … offset … —> MySQL方言

/ %
case … when … then … when … then … else … end
函数：

数值函数 --> abs / ceil / floor / round / exp / log / log2 / log10 / rand / sqrt / power
流程控制 --> if / ifnull
其他 —> md5 / sha1 / aes_encrypt / aes_decrypt / compress / uncompress / uuid

谓词：

= / <> / > / < / >= / <= / between … and …

like / regexp —> % _

in / not in

exists / not exists

is null / is not null

where —> 分组之前的数据筛选
having —> 分组以后的数据筛选（这里可以使用分组聚合的结果）

数据透视 —> 分组聚合（先将数据分成若干组，在组内使用聚合函数）

数据按某个维度拆解 —> 定位数据异常到底是什么引起的

根据 A 统计 B —> 根据性别统计男女学生人数 / 根据学号统计每个学生的平均成绩

嵌套查询 —> 把一个查询的结果作为另外一个查询的一部分来使用

派生表：把查询的结果当做表来使用

连接查询 —> 如果要查询的字段来自于多张表

如果没有连接数据的条件 —> 笛卡尔积

内连接

自然连接 —> 外键 / 同名列 —> 笛卡尔积

外连接（左外连接 / 右外连接）

6. DCL 创建用户

create user …@… identified by …;

drop user …;

授权

grant select on xxx.* to …;

grant all privileges on

. to … with grant option;

召回权限

revoke select on xxx.* from …;

7. Python程序接入MySQL

mysqlclient —> import MySQLdb
pymysql —> import pymysql —> pip install pymysql

创建连接：connect(host, port, user, password, database, charset) —> Connection
try-except-else:
获取游标：Connection --> cursor() —> Cursor
发送SQL：Cursor —> execute(’…’, (…)) / executemany()
增删改：Connection —> commit() / rollback() 查询：Cursor —> fetchone() / fetchmany(size) / fetchall()
finally:
关闭连接：Connection --> close()

8. 视图 —> 查询的快照

create view … as select …;
drop view …;

复用SQL语句
将访问权限控制到指定的列（只给用户查看视图的权限）

9. 函数和过程函数：create function 函数名(…) returns 返回类型 no sql … 过程：create procedure 过程名(…) … 区别：过程没有返回值，但是可以使用输出参数获取数据 10. 事务 —> 事务环境 —> 支持原子性操作定义：把多个DML操作视为不可分割的原子性操作，要么全都做，要么全都不做。

start transaction; —> 开启事务环境
commit; —> 提交
rollback; —> 回归

第一类丢失更新
第二类丢失更新
脏读（读脏数据）
不可重复读
幻读
—> 锁（表锁/行锁）—> 事务隔离级别
查看事务隔离级别：select @@transaction_isolation;
修改事务隔离级别：global / session
set session transaction isolation level read committed;
set session transaction isolation level serializable;