windows,pycharm,python，PyMySQL,mysql及应用案例

1.下载安装Mysql

1.1 安装mysql57

下载Mysql5.7，暂不使用8.0版本：https://downloads.mysql.com/archives/community/
mysql-5.7.40-winx64.zip
解压到C:\Program Files\，这代表将mysql安装在这个位置
在C:\Program Files\mysql-5.7.40-winx64下新建：my.ini文件

[mysqld]
#端口
port=3306
#mysql的安装目录
basedir=C:\\Program Files\\mysql-5.7.40-winx64
#mysql的data目录
datadir=C:\\Program Files\\mysql-5.7.40-winx64\\data

在终端中：
1，初始化mysql
"C:\Program Files\mysql-5.7.40-winx64\bin\mysqld.exe" --initialize-insecure
2，将mysql制作成windows服务
"C:\Program Files\mysql-5.7.40-winx64\bin\mysqld.exe" --install mysql57
3，启动mysql服务
net start mysql57
4，关闭mysql服务
net stop mysql57

1.2 测试mysql57

1，连接mysql:注意，这里使用的是mysql.exe而非mysqld.exe
"C:\Program Files\mysql-5.7.40-winx64\bin\mysql.exe" -h 127.0.0.1 -P 3306 -u root -p
2，默认是空密码，直接enter;
3，显示当前的数据库
show databases;

4，退出mysql
exit;

1.3 修改密码

1，设置密码：进入mysql后：
set password = password("123456")
则密码设置成功；
2，忘记密码：
1）在my.ini中增加一行：
skip-grant-tables=1
2）重启mysql，则可以不用密码登录
net stop mysql57
net start mysql57
3）进入mysql
"C:\Program Files\mysql-5.7.40-winx64\bin\mysql.exe" -h 127.0.0.1 -P 3306 -u root -p
3）按照步骤1设置密码；
4）删除my.ini中增加的skip-grant-tables=1

1.4 卸载mysql57

1，删除mysql57服务
"C:\Program Files\mysql-5.7.40-winx64\bin\mysqld.exe" --remove mysql57
2，删除mysql57目录

2. 安装配置pycharm

2.1 安装pycharm

略，参考相关教程

2.2 创建工程

略，参考相关教程

2.2 配置pycharm以使用PyMySQL

file -> settings -> project:xx -> python interpreter

即可新建python文件并使用PyMySQL了

3. MySQL基础

3.1 MySQL数据类型

int

int :有符号，取值范围：-2147483648 ~ -2147483647
int unsigned: 无符号取值范围0~ 4294967295
int(5)zerofill:很少使用，仅用于显示

tinyint

使用方法与int一样，但数据范围为-128~127，0~255

bigint

使用方法与int一样，但数据范围为-9223372036854775808~9223372036854775807，0~1844674407379551615

decimal [m[,d]] [unsigned] [zerofill]

m：数字总数（符号不算），最大值65
d：小数点后个数，最大值30
decimal(8,2)：数字总体为8位，小数点后保留2位，小数超出时会4舍5入，整数位超出时会报错

float [m[,d]] [unsigned] [zerofill]

单精度浮点数，非精准小数，不常用

double [m[,d]] [unsigned] [zerofill]

双精度浮点数，非精准小数值，不常用

char(m)

定长字符，m代表字符串长度，最多配置位255个字符，字符不够时补充空格为m个字符，字符超出时会报错

varchar(m)

变长字符，m代表字符串长度，最多容纳65535个字符，字符不够时按照真实长度存储，字符超出时会报错

text

保存变长大字符串，一般用于文章或新闻，最长可到65535（2**16-1）个字符

mediumtext

最长可到16777215（2**24-1）个字符

longtext

最长可到4GB（2**32-1）个字符

datetime

YYYY-MM-DD:HH:MM:SS（1000-01-01 00:00:00 / 9999-12-31 23:59:59）
客户端存入的时间时不作改变，原样输入和输出。常用。

timestamp

YYYY-MM-DD:HH:MM:SS（1970-01-01 00:00:00 / 2037）
客户端存入的时间时，从当前时区转化为UTC（世界标准时间）进行存储，查询时有将其转化为客户端当前时区进行返回。不常用。

date

YYYY-MM-DD（1000-01-01 / 9999-12-31）

time

HH:MM:SS（-838:59:59 / 838:59:59）

3.2 表的关系

单表：单独一张保存信息的

一对多：两张表存储信息，且两张表存在一对多或多对一的关系

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

为建立表的关系，需要使用外键来建立约束

4. 使用PyMySQL操作数据库

4.1 连接数据库connect()

import pymysql
 
# 连接数据库
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',charset='utf8',password="123456")
cursor = conn.cursor()

4.2创建数据库和表

#1.创建数据库，charset编码规则，collate排序规则
cursor.execute("create database db1 default charset utf8 collate utf8_general_ci")
#查询不需要commit()，增删改需要commit()
conn.commit()
 
#2.进入数据库创建表,并查看
cursor.execute("use db1")
sq1 = """
create table L1(
    id int not null primary key auto_increment,
    title varchar(128),
    content text,
    ctime datetime
)default charset=utf8;
"""
cursor.execute(sq1)
conn.commit()

4.2-补1：创建数据表时的关键字

create table L1(
    id int primary key auto_increment,    --primary key主键，不允许为空，不能重复
                                          --auto_increment自增
                                          --一张表只能有一个主键，一个自增列，自增列一般为主键
    name varchar(16) not null.            --不允许为空
    email varchar(32) null,               --允许为空
    age int default 3                     --默认值为3
    
)default charset=utf8;

4.2-补2：关联表时的外键

1，外键是一种索引，是通过一张表中的一列指向另一张表的 主键，使得这两张表产生关联
2，外键可以在创建表时添加，也可以后续使用alter table 表名 add constraint ......来添加；
3，外键也可通过alter table 表名 drop foreign key ....来删除
4，有多张表时，关系表需要有多个外键约束

参考： MySQL外键（详解）
参考： sql_外键

4.3 查看数据库和表，以及表的内容

# 查看数据库
cursor.execute("show databases")
result = cursor.fetchall()
print(result)
 
#进入数据库，查看数据表
cursor.execute("use db1")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)
 
#查看数据表的内容
cursor.execute("desc l1")
result = cursor.fetchall()
print("desc:",result)
 
#查看数据表的数据
cursor.execute("select * from l1")
result = cursor.fetchall()
print("select:",result)

4.4删除数据库和表，清空表

#删除数据库
cursor.execute("drop database db1")
conn.commit()
 
#删除数据表
cursor.execute("use db1")
cursor.execute("drop table l1")
conn.commit()
 
#清空数据表
cursor.execute("delete from l1")    
或：
cursor.execute("truncate table l1")    --速度快，但无法回滚和撤销

4.5修改表

添加列

alter table 表名 add 列名 类型 [其他关键字]；

删除列

alter table 表名 drop column 列名 类型；

修改类的类型

alter table 表名 modify column 列名 类型；

修改列名和类型

alter table 表名 change 原列名 新列名 新类型；

修改列默认值

alter table 表名 alter 列名 set default 默认值；

删除列默认值

alter table 表名 alter 列名 drop default；

4.6 数据行增删改查

新增数据：

insert into 表名 (列名,列名,...,列名) values(值,值,...,值)
insert into 表名 (列名,列名,...,列名) values(值,值,...,值),(值,值,...,值)  --插入多行数据
insert into 表名 values(值,值,值),(值,值,值)    --如果插入值的个数和列的个数相同，则可以不写列名

删除数据

delete from 表名    --删除表里的所有数据
delete from 表名 where 条件    --按条件删除
例1：delete from tb1 where name="sdd"
例2：delete from tb1 where name='sdf' and id='123'
例3：delete from tb1 where name='sdb' or id='123'
例4：delete from tb1 where id>9

修改数据

update 表名 set 列名=值
update 表名 set 列名=值 where 条件
例1：update tb1 set name="sdd" where id='123'  --修改id为123的行的name为sdd
例2：update tb1 set age=age+1 where id='123'  --修改id为123的行的age为原有值+1
例3：update tb1 set name=concat(name,"123") where id='123'  --在id为123的行的name后加上123后缀

查询数据

select * from 表名                       
select 列名,列名,列名 from 表名            
select 列名,列名 as 别名 from 表名
select * from 表名 where 条件           --按条件搜索整张表 
select 列名,列名 from 表名 where 条件    --按条件收索指定列

例：
select * from tb1
select id,name from tb1
select id,name,111 from tb1   --表里面没有111时，查询结果会增加一列，其值全为111
select id,name,111 as age from tb1 --表里面没有111时，查询结果会增加一列，其值全为111,同时表头为age
 
select * from tb1 where id=1
select * from tb1 where id >1
select * from tb1 where id!=1
select * from tb1 where name="sdd" and password='123'

4.7 where语句

条件查询： SQL语句之条件查询--WHERE(where)

4.8 排序order by

参考： SQL语句之排序查询--ORDER BY

4.9 取部分及分页查询limit

参考： SQL中limit的用法
参考： sql语句中的limit n，limit n,m 和 limit m offset n

4.10 聚合函数、分组group by及having

参考： sqlserver之group by 与over函数
参考： SQL之HAVING
参考： SQL中group by的用法总结

4.11 连表 join

参考： SQL连接表(内连接、左连接、右连接、交叉连接、全外连接

一般用左外连接，较少用右外连接

可以连接多张表

4.12 上下连表union

参考： SQL UNION运算符

union会去重，union all不去重

4.13 用户授权（权限管理）

参考：MySQL查看用户权限及权限管理

用户和权限信息存储在mysql.user这张表中

查询用户和权限：SELECT user,authentication_string,host FROM mysql.user;

创建用户：create user username@ip indentified by '密码'
例如：
1）create user 'zhangsan'@'127.0.0.1' indentified by '123456' --在127.0.0.1这个ip地址上用zhangsan这个账户，用密码123456登录
2）create user 'zhangsan'@'%' indentified by '123456' --在任意ip地址上用zhangsan这个账户，用密码123456登录mysql
3）create user 'zhangsan'@'127.0.0.1' indentified by '123456' --在127.0.0.1这个ip地址上用zhangsan这个账户，用密码123456登录

删除用户：drop user 'zhangsan'@127.0.0.1

修改用户：rename user '张三'@'127.0.0.1' to 'lisi'@'128.3.12.2'

修改密码：set password for '张三'@'127.0.0.1' = password('738')

授权:grant 权限 on 数据库.表 to '用户'@'ip地址'
授权后刷新：FLUSH PRIVILEGES;
查看权限：show grants for '用户'@'ip地址'
取消授权：remoke 权限 on 数据库.表 from '用户'@'ip地址'

5. SQL数据库的导入/出

参考： Pycharm连接Mysql数据库操作、以Excel文件导入导出

6. 示例1：班级管理数据库操作

6.1 表结构

6.2 数据库操作

1创建数据库和表结构

--创建和使用数据库
create database db1 default charset utf8 collate utf8_general_ci;
use db1;
--创建表
create table class(
    cid int not null auto_increasement primary key,
    caption varchar(16) not null
)default charset=utf8;
--添加数据
insert into class values ('1','一年级一班'),('2','一年级二班'),('3','三年级一班')
......其余表（略）

2创建用户并赋予权限

create user 'luffy'@'%' indentified by 'root123';
grant all privileges on db1.* to 'luffy'@'%';
flush privileges;

3查询'李'姓老师名单

select * from teacher where tname like '李%'

4查询男生、女生人数

select gender,count(1) from student group by gender;

5查询同名同姓学生名单，并统计同名人数

select sname,count(1) from student group by sname having count(1)>1;

6查询“三年级一班”的所有学生信息

select
    *
from
    student
    left join class on student.class_id = class.cid
where class.caption='三年级一班';

7查询每个班级的班级名称、班级人数

select
    class.caption,count(1)
from
    student
    left join class on student.class_id = calss.cid
group by class.caption;

8查询成绩小于60分的同学的学号、姓名、成绩、课程名称

select 
    student.sid,
    student.sname,
    score.number,
    course.cname
from
    score
    left join student on score.student_id=student.sid
    left join cource on score.course_id=course.cid
where number<60;

9查询选修了“生物课”的所有学生ID、学生姓名、成绩

select
    student.sid,
    student.sname,
    score.number
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
where
    course.cname='生物';

10查询选修了“生物课”且成绩低于60分的所有学生ID、学生姓名、成绩

select
    student.sid,
    student.sname,
    score.number
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
where
    course.cname='生物' and score.number<60;

11查询所有同学的学号、姓名、选课数、总成绩

select
    student_id,
    student.sname,
    count(1),
    sum(number)
from
    score
    left join student on score.student_id=student.sid
group by student_id;

12查询各科选修学生的人数

select
    coourse_id,
    course.cname,
    count(1)
from
    score
    left join course on course.cid=score.course_id
group by course_id;

13查询各科成绩的总分、最高分、最低分，显示:课程ID、课程名称、总分、最高分、最低分

select
    course_id,
    course.cname,
    sum(number),
    max(number),
    min(number)
from
    score
    left join course on score.course_id=course.cid
group by course_id;

14查询各科成绩的平均分，显示：课程id、课程名称、课程平均分

select
    course_id,
    course.cname,
    avg(num)
from
    score
    left join course on score.course_id=course.cid
group by course_id;

15查询各科成绩的平均分，显示：课程id、课程名称、课程平均分(按照从大到小排队)

select
    course_id,
    course.cname,
    avg(num) as A
from
    score
    left join course on score.course_id=course.cid
group by course_id
order by A desc;

16 查询各科成绩 平均分和及格率，显示：课程id、课程名称、平均分、及格率

select
    course_id,
    course.cname,
    avg(num),
    sum(case when score.num>60 then 1 else 0 end)/count(1)*100 as percent
from
    score
    left join course on score.course_id=course.cid
group by course_id

17查询平均成绩大于60的所有学生的学号，平均成绩

select
    student_id,
    avg(number)
from
    score
group by
    student_id having avg(num)>60

18查询平均成绩大于85的所有学生的学号、平均成绩、姓名

select
    student_id,
    student.sname,
    avg(number)
from
    score
    left join student on score.student_id=student.sid
group by
    student_id having avg(number)>=85

19 查询“二年级一班”每个学生的学号、姓名、总成绩、平均成绩

select
    student_id,
    student.sname,
    sum(number),
    avg(number)
from
    score
    left join student on score.student_id=student.sid
    left join class on student.class_id=class.cid
where
    class.caption='二年级一班'
group by 
    student_id

20查询各个班级的班级名称、总成绩、平均成绩、及格率（按平均成绩从大到小排序）

select
    class.caption,
    sum(number),
    avg(number) as av,
    sum(case when score.num>60 then 1 else 0 end)/count(1)*100 as JG
from
    score
    left join student on score.student_id=student.sid
    left join class on student.class_id=class.cid
group by
    class.caption
order by
    av desc

21查询学过李老师课程的同学的学号和姓名

select
    student_id,
    student.sname
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
    left join teacher on course.teacher_id=teacher.tid
where
    teacher.tname='李老师'

22查询没学过李老师课程的同学的学号和姓名

select * from student where sid not in( 
   select
        student_id
    from
        score
        left join student on score.student_id=student.sid
        left join course on score.course_id=course.cid
        left join teacher on course.teacher_id=teacher.tid
    where
        teacher.tname！='李老师'
)

23查询选修李老师所授课程的学生中，成绩最高的学生姓名及成绩（不考虑并列）

select
    student.sname,
    number
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
    left join teacher on course.teacher_id=teacher.tid
where
    teacher.tname='李老师'
order by
    score.number desc
    limit 1

24查询选修李老师所授课程的学生中，成绩最高的学生姓名及成绩（考虑并列）

select
    student.sname,
    number
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
    left join teacher on course.teacher_id=teacher.tid
where
    teacher.tname='李老师'
    and score.number={
        select
            max(number)
        from
            score
            left join course on score.course_id=course.cid
            left join teacher on course.teacher_id=teacher.tid
        where
            teacher.tname='李老师'
    }

25查询只选修了一门课的全部学生的学号、姓名

select
    student_id,
    student.sname
from
    score
    left join student on score.student_id=student.sid
group by
    student_id
having
    count(1)=1

26查询至少选修了2门课程的学生、学生姓名、选修课程数量

select
    student_id,
    student.sname,
    count(1)
from
    score
    left join student on score.student_id=student.sid
group by
    student_id
having
    count(1)>=2

27查询两门及以上不及格的同学的学号、姓名、选修课数量

select
    student_id,
    student.sname,
    count(1)
from
    score
    left join student on score.student_id=student.sid
where
    number<60
group by
    student_id
having
    count(1)>=2

28查询选修了所有课程的学生的学号、姓名

select
    student_id,
    student.sname
from
    score
    left join student on score.student_id=student.sid
group by
    student_id
having
    count(1) = (select count(1) from course)

29查询未选修所有课程的学生的学号、姓名

select
    student_id,
    student.sname
from
    score
    left join student on score.student_id=student.sid
group by
    student_id
having
    count(1) < (select count(1) from course)

30查询所有学生都选修了的课程的课程号和课程名

select
    course_id,
    course.cname
from
    score
    left join course on score.course_id=course.cid
group by
    course_id
having
    count(1) = ( select count(1) from student )

31查询选修了生物和体育课程的所有学生的学号、姓名

select
    student_id,
    student.sname
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
where
    course.cname in ("生物","体育")
group by
    student_id
having
    count(1)=2

32查询至少有一门与学号为“1”的学生所选修的课程相同的其他学生学号和姓名

select
    student_id,
    student.sname
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
where
    score.student_id!=1 and
    score.course_id in ( select course_id from score where student_id=1 )
group by
    student_id
having
    count(1)>=1

33查询与学号为2的同学选修的课程完全相同的其他学生学号和姓名

select
    student_id,
    student.sname
from
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
where
    score.course_id in ( select course_id from score where student_id=1 )
    and 
    --第二个条件为找到选修课程数量与学号为2的学生一样的id
    score.student_id in(
        select
            student_id
        from
            score
        where
            student_id!=2
        group by 
            student_id
        having
            count(1) = (select count(1) from score where student_id=2)
    )
group by
    student_id
having
    count(1)=( select count(1) from score where student_id=2 )

34查询生物课程比物理课程高的所有学生的学号和姓名

select
    student_id,
    student.sname,
    max(case course.cname when '生物' num else -1 end) as sw,
    max(case course.cname when '物理' num else -1 end) as wl
form
    score
    left join student on score.student_id=student.sid
    left join course on score.course_id=course.cid
where
    course.cname in ('生物','物理')
group by
    student_id
having
    sw > wl    

35查询每门课程成绩最好的前3名（不考虑成绩并列）

select
    cid,
    cname,
    --第一名
    (select
        student.sname
    from
        score
        left join student on score.student_id=student.sid
    where
        course_id = course.id
    order by
        number desc
    limit
        1
    offset
        0        
    ) as '第一名',
    --第二名
    (select
        student.sname
    from
        score
        left join student on score.student_id=student.sid
    where
        course_id = course.id
    order by
        number desc
    limit
        1
    offset
        1        
    ) as '第二名',
    --第三名
    (select
        student.sname
    from
        score
        left join student on score.student_id=student.sid
    where
        course_id = course.id
    order by
        number desc
    limit
        1
    offset
        2        
    ) as '第三名'
from
    course
    

36创建一张有外键的表sc，把score的所有数据都插入到sc中

create table 'sc'{
    'sid' int not null auto_increment primary key,
    'student_id' int not null,
    'course_id' int not null,
    'num' int not null,
    constraint 'fk_sc_course' foreign key ('course_id') reference 'course'('cid'),
    constraint 'fk_sc_student' foreign key ('student_id') reference 'student'('sid')
} default charset=utf8;
 
insert into sc select * from score;

37 向sc中插入一些记录，1）没有上过课程id为2的课程的学生id，2）课程id为2，3）成绩为80

insert into sc (student_id, course_id, number)
select
    sid,
    2,
    80
from
    student
where
    sid not in (
        select
            student_id
        from
            score
        where
            course_id=2
    )

38 向sc中插入一些记录，1）没上过id为2的课程的学生id，2）课程id为2，3）成绩为课程id为3的最高分

insert into sc (student_id,course_id,number)
select
    sid,
    2,
    (select 
        max(num)
    from
        score
    where
        course_id=3    
    ) as num
from
    student
where
    sid not in (
        select
            student_id
        from
            score
        where
            course_id=2
    )    

7.示例2： 博客系统与索引

7.1 表结构

7.2索引

常见索引：

1，主键索引：加速查找，不能为空，不能重复，（多列） 联合主键索引

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(id)  --主键索引
);
 
create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(id，name)  --多列，联合主键（不常用）
);
 
alter table 表名 add primary key(列名); -- 表创建完成后添加主键索引
drop table 表名 drop primary key;

2，唯一索引：加速查找，不能重复 ，允许最大1个空（多列）联合唯一索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_name(name)
);
 
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_name(name),  --唯一索引，每一列的内容不能重复
    unique ix_emai(email)  --多个唯一索引
);
 
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_group1(name,email)  --多列，联合唯一索引，多列的内容，可以有一部分重复，但不能完全重复
);
 
create unique index 索引名 on 表名（列名）；
drop unique index  索引名 on 表名

3，普通索引：加速查找 （多列）联合索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_name(name)
);
 
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_name(name),  --索引
    index ix_emai(email)  --多个索引
);
 
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_group1 (name,email)  --多列，联合索引
);
 
create index 索引名 on 表名（列名）；
drop index  索引名 on 表名

7.3索引不命中的情况

1，类型不一致

select * from tb1 where name=123  --未命中，效率低，因为表中name为字符串型
但主键索引类型不一致时不影响效率

2，使用不等于

select * from tb1 where name！='123'  --未命中，效率低，因为使用了不等于
但主键索引使用不等于时不影响效率

3，or，当or条件中与未建立索引的列

select * from tb1 where name！='123' or password="xsd"  --如果password未建立索引，则效率低

4，排序，根据索引排序时，选择的映射如果不是索引，则不走索引

select * from tb1 order by name --即使name建立了索引，也无法命中，因为选择的是*
但使用主键order by时不受影响

5，like，模糊匹配时通配符在前面或中间

select * from tb1 where name like '%uuu' --未命中
select * from tb1 where name like '__uuu' --未命中
select * from tb1 where name like 'u%-_uu' --未命中
 
select * from tb1 where name like 'uuu%' --命中
select * from tb1 where name like 'uuu_' --命中

6,使用mysql内置函数时

select * from tb1 where reverse(name)='asdlf' --未命中
 
但对条件使用内置函数时命中
select * from tb1 where name=reverse('asdfj') --命中

7，联合索引，应遵循最左前缀

如果联合索引为（name,password)
select * from tb1 where name='122' and password='233' --命中
select * from tb1 where name='122' --命中
select * from tb1 where password='233' --未命中
select * from tb1 where name='122' or password='233' --未命中

7.4 索引 执行计划

在查询语句前，加explain，查看返回数据的type列。如果结果是all，则查询速度最低；如果结果是system/const，则查询速度最快；排序为all<index<range<index_merge<ref_or_null<ref<eq_ref<system/const

7.5博客系统 表索引 设计

对于推荐表：
1）id为主键索引；
2）user_id和article_id可以设计为联合唯一索引，因为用户只能评价一篇文章一次。

对于用户表：
1）id为主键索引；
2）用户名+密码，设计为联合索引，加快搜索进度；
3）手机号：唯一索引，因为不能重复；
4）邮箱：唯一索引，不能重复。

8.示例3：MySQL函数

8.1 内置函数

count()
max()
min()
avg()
reverse()
concat()
now()--获取当前时间
date_format(now(),'%Y-%m-%d %H:%m:%s')--按格式格式化时间
sleep(1)--等待1s
......

8.2 自定义MySQL函数(一般不用)

--定义函数
delimiter $$
create function f1(
    i1 int,
    i1 int)
returns int
begin
    declare num int;
    declare maxID int;   
    select max(id) from tb1 into maxID;
    set num = i1 + i2 +maxID;
    return(num);
end $$
delimiter;
 
--调用函数
select f1(11,22);
select f1(11,id),name from tb1;
--删除函数
drop function f1;

8.3 存储过程（不常用）

将一系列SQL语句集合存储在数据库中，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

相比与使用python执行SQL，可以节约数据传输的时间，但是修改存储过程比较麻烦。

（更新中......）

附1：MySQL时区设置

时区

set time_zone='+0:00'：设置时区为0区
show variables like '%time_zone%'：查询时区

附2：SQL注入

基于字符串格式化来拼接SQL语句,存在SQL注入风险，如下面代码所示：
例如用户输入user为‘ or 1=1 -- 输入pwd为123,
则下述sql为select * from db1 where name='' or 1=1 -- and password='{}'
--后的被认为是注释，则where条件为1==1就是True，则实际上用户名不需要正确，也不需要密码，也能登录

cursor.execute("use db1")
user = input("请输入用户名")
pwd = input("请输入密码")
sql = "select * from db1 where name='{}' and password='{}'".format(user,pwd)
cursor.excute(sql)
result = cursor.fetchall()
print(result)

为避免SQL注入问题，涉及到用户输入，应避免用传统的字符串格式化，应该用pymysql提供的方法
这种方法会帮助检测输入是否非法，且会帮助转义

cursor.execute("select * from db1 where name=%s and password=%s",[user,pwd])
#或者
cursor.execute("select * from db1 where name=%(n1)s and password=%(n2)s",{"n1":user,"n2":pwd})

附3：sql执行顺序

join
on
where
group by
having
order by
limit