0731-MySql(2)

----------------------------------------------------------以下的可以看作跟=,<,>等等一样的特殊运算符----------------------------------------

模糊查询(低效率)：

like 

% 替换0，1或以上的字符

_ 替换1个

rlike

这个是以正则查找

用法示例：select name from students where name rlike "^周.*";

范围查询

in 在某个非连续范围内 ；not in 反之

select name,age from students where age in (12,18,34);

between 在某个连续的范围内(包含头包含尾) ; not between 反之 但是这是一种固定用法 不能age not (between 18 and 34)，会挂

select name,age from students where age not between 18 and 34;

判断空

is null; 或者 is NULL;

is not null; 反之

select name,height from students where height is null;

到此为止------------------------------------------------------------------------------------------------------------------------------------------

聚合函数（可以就看作python里一样的函数）

count 计数

select count(*) as 男性人数 from students where gender = "男";

select count(*) as 女性人数 from students where gender = "女";

# 前面应该记录过as...吧？

max/min 最大/最小值

select max(age) from students where gender = 2;

select min(height) from students where gender = 1;

sum 求和

select sum(age) from students;

avg 求平均值

select avg(age) from students;

说了这么多，这个应该也看出来了可以支持运算的，所以还可以这么写

select sum(age)/count(*) from students;

round 保留小数位数，【四舍五入】

select round( (sum(age)/count(*)) , 2 ) from students;

# C语言底层在内存中存储数据的时候都是约等于，而绝大多数语言的解释器都是用C语言编写的，所以他们也会约等于，就产生了误差

# 而在银行等等这些对数据要求很精密的地方，直接把数据扩大多少倍(100,1000,1^4...)，最终记得要除上相同的倍数

# 总之，这种不能有误差的地方在存储的时候不要用小数

select round(avg(height), 2) from students;

如果round前面加个name，那是不能的，会挂，需要用到接下来的功能-->分组

分组(一般跟聚合一起用)

group by

select gender from students group by gender; --根据性别分组

前面写上条件，要唯一能标记这个组的条件

跟聚合函数混在一起：

select gender,count(*) from students group by gender;

这时候是把分的组里面的每个元素进行操作，不是对原表操作

而且不止count，刚才所学的所有的聚合函数都能使用

select gender, round(sum(age)/count(*),2) from students group by gender;

还可以写上group_concat(name)来把具体的name拿出来，还可以加条件，加在group by前面

select gender,group_concat(name, age, id) from students where gender=1 group by gender;

group_concat实际上是把这几个东西链接在一起显示出来

所以上面是这仨东西连在一起输出的，那么我们可以在中间加任意字符分割，就是这样group_concat("名字:",name, ",年龄:", age, ",ID:", id)

having

这个感觉跟在group前面加where很像

select group,group_concat(name),avg(age) from students group gender having avg(age) > 30;

但是他跟where还是不一样，having是对这个组进行判断，where是直接对原表进行判断

链接查询：多个表的关联查询

# 回顾 外键：存的是某个表的主键，帮我们限制数据的范围

直白来讲，就是把多个表中的共有数据取出来查询

一共分为三种：

1. 内连接查询

inner join --固定语法，不能拆开

--      表A     表B

select * from students inner join classes;

这个就是简单的把俩表合起来，只是语法正确而已

on 后面加条件

select * from students inner join classes on students.cls_id = classes.id;

这个是找的到的就合并并显示，找不到的干脆不会显示，也就是超过3(因为classes表id一共三个)的

这个合并是咋合并呢，很简单，直接把俩拼接一起（

就是左边是表A所有符合On的数据，右边是所有对应的表B的数据

可以分别让他显示一部分，就像这样

select students.*, classes.name from students inner join classes on students.cls_id = classes.id; --可以短一点，用as

select s.*, c.name from students as s inner join classes as c on s.cls_id = c.id;

总结下，on后面的对的上的话(产生交集)，他就取两个表的交际部分一一对应，你也有，他也有才显示

还可以跟order by结合，好像是个排序的玩意，反正前面讲过...应该

select c.name, s.* from students as s inner join classes as c on s.slc_id = c.id order by c.name,s.id;

左连接和右连接（大体都是外连接）：

直白来讲：内间接取交接，左连接是哪个表在左边，以这个表为基准去取东西，取不出来的默认为null

语法（左连接）left join --还真是好理解（

select c.name, s.* from students as s left join classes as c on s.slc_id = c.id order by c.name,s.id

这个输出结果会多三个,这仨是右边找不到的，会显示null

再和前面结合下

select c.name, s.* from students as s left join classes as c on s.slc_id = c.id order by c.name,s.id where c.id is null;

where c.id is null 跟 having c.id is null一样的

自关联：自己其中一个字段跟另一个字段关联

比如地址

id   name  pid

1     xxx1      null

2     xxx2      null

3     xx1        2

4     xx2        2

5     x1     3

6     x2         3

就像这样，pid表示他的上级（老大）

子查询：比子关联稍微慢一点

一个select里面套另外一个select

select * from students where height = (select max(height) from students);

数据库(表)设计：

有专业的软件可以设计，如果大公司还有专门的人员负责

继软件三大范式之后，数据库设计三大范式(其实有8种，但是一般前三种就够)：

1NF: 一个数据一个字段，不要一堆数据塞一个字段里，要求把每个信息拆成一个字段

2NF:如果表里面的主键有多个字段组成，直接依赖于所有的主键，我理解就是如果有别的主键就把他专门拆开拆成一个新的数据表，新表有个外关联跟原本的表关联

3NF:如果一堆信息都依赖于某个主键，那个主键依赖于另一个主键，那他们不能把信息都塞一个表里

什么意思呢，就是比如一个订单表

里面有订单ID，订单信息，客户ID，客户信息

那么客户ID依赖于订单ID对吧，那他们就要拆开

订单ID单独一张表，里面有订单信息，ID和客户ID

客户ID单独一张表，这样的话如果这个客户多次下单就不会出现重复的客户信息了

这三大范式遵循了可以达到节省空间的效果，极大的缩减了数据的规模和重复性

而且把这个拆分成这样就解耦了，这个有点像MVC架构奉行的理念，高内聚，低耦合，各干各的

这就是为什么叫关系型数据库，关系非常强，后面还会学的NoSql等等非关系型数据库这些就不一样，他们是一张表就是一张表，没有任何关系

E-R模型

E(entry)实体，就像一个类一样，一个抽象的模板

R(relationship)关系，描述两个实体间的规则，可以为一对一，多对一等等，这个也是需要一个字段来描述的

一对一和多对一很简单，都是在左边加，但是多对多呢

多对多有点麻烦，得新开个表，存储每个表的主键，这样的表我们称之为聚合表

python操作mysql

流程：导入-->创建connection对象-->创建cursor-->执行mysql语句...-->关闭cursor-->关闭connection对象-->结束

基本就是操作中间部分，其他都是差不多固定的

    conn = connect(host="localhost", port=3306, user="root", password="123456", database="jing_dong", charset='utf-8')

    cs1 = conn.cursor()

    

    cs1.execute("--执行的sql语句")

    # 这个东西的返回值是生效的行数，也就是所查询到的行数，要数据的话就调游标对象对应的行数

    cs1.fetchone()  # 这个是一条一条取，返回元组类型

    cs1.fetchmany() # 返回个元组套元组，里面输入的参数决定返回几个，

sql注入攻击

就是直接写sql语句进去，让原本的name，变成一个sql语句

原来有个网址，乌云WooYun，东哥原来经常进去看，确实能学到不少东西，有帮人经常把他们sql攻击的图片啥的全截图下来发上去晒

后来东哥自己去试了下把某通快递公司的订单号全给拿出来了，里面收货人的手机号，地址啥的全都有

而且人家明明告诉了某通说让他改，人就是不改，后来什么中通啊，圆通啊，国通啊数据都泄露了，反正各大企业都泄露了

（网址已经被封了，被国家约谈了，抓了百多个人进去，现在法律规定，只要获取个人信息累加50条以上就寄了）

[我还真找到乌云遗产了，甚至找到个群和乌云的网址备份，15年的群了，8年了居然还没挂，具体信息存储于本篇附件内，不会跟笔记一起发布]

以及其实可以试试我自己写个mysql注入的字典，存txt里，里面写有可能的sql注入语句，程序接收返回结果，用循环自动化测试

找到结果后记录到log里，不过这应该是个很长的过程，而且几乎找不到测试案例，只能希望自己写个网站或者试试阿三哥的那个平台能不能用吧

    

    cs1.close()

    conn.close()