数据库
是一个能存数据
的软件, 提供各种数据的查询操作
, 以及对数据的修改操作
mysql
的具体安装操作,这里就不做过多的介绍了。主要讲讲如何通过py程序来实现mysql操作。
具体的安装操作可以看下面这一篇
链接
: mysql的安装与配置
这里的操作基本都在navicat里实现可视化
pip install pymysql
import pymysql
用SQL语句
创建表格
create table student(
-- 字段=列=column=属性
sno int(10) primary key auto_increment,
sname varchar(50) not null,
sbirthday date not null,
saddress varchar(255),
sphone varchar(12),
class_name varchar(50)
);
项目 | 数据类型 |
---|---|
double | 小数 |
varchar | 字符串 |
date | 时间(年月日) |
datetime | 时间(年月日时分秒) |
text | 大文本 |
项目 | 约束条件 |
---|---|
primary key | 主键, 全表唯一值. 就像学号. 身份证号. 能够唯一的确定一条数据 |
auto_increment | 主键自增. 必须是整数类型 |
not null | 不可以为空. |
null | 可以为空 |
default | 设置默认值 |
-- 添加一列
ALTER TABLE table_name
ADD COLUMN column_name datatype
-- eg
ALTER TABLE student
ADD COLUMN f_name VARCHAR(20) NOT NULL
AFTER sno; -- AFTER 用于将新加的列在指定列的后面插入
-- 删除一列
ALTER TABLE table_name
DROP COLUMN column_name
-- 修改一列的数据类型
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
-- 表格重命名
ALTER TABLE table_name RENAME TO new_name;
创建完毕后,还需要右键或者双击打开连接
和连接一样,也是需要进行开启的
至此, Navicat可以操纵你的数据库了.
傻瓜式操作
选择创建好的表,右键设计表,就可以进行修改表操作了
INSERT INTO table_name(col1, col2, col3...) values (val1,val2,val3)
-- 添加学生信息
INSERT INTO STUDENT(sname, sbirthday, saddress, sage, class_name) values ('周杰伦', '2020-01-02', "北京市昌平区", 18, "二班");
注意, 如果主键设置自增
, 就不用处理主键
了. mysql会自动
的帮我们按照自然顺序
进行逐一自增
.
DELETE FROM table_name where_clause
-- 删除学生信息
DELETE FROM STUDENT where sno = 1 ;
UPDATE table_name SET col1 = val1, col2 = val2... where_clause
-- 修改学生信息
UPDATE STUDENT SET SNAME = '王力宏' where sno = 1;
SELECT *|col1, col2, col3
FROM table_name
where_clause
-- 全表查询
SELECT * FROM STUDENT;
-- 查询学生姓名, 年龄
SELECT sname, sage FROM STUDENT;
-- 查询学号是1的学生信息
select * from student where sno = 1;
-- 查询年龄大于20的学生信息
select * from student where sage > 20;
-- 查询学生年龄大于20 小于40的信息(包含)
select * from student where sage >= 20 and sage <= 40;
select * from student where sage between 20 and 40 ;
-- 查询姓张的学生信息
-- _一位字符串
-- %多位字符串
select * from student where sname like '张%';
如何查询每个班级学生的平均年龄?
我们先把数据扩充一下下
每个班级的平均年龄. 我们是不是需要先把班级与班级先分开. 每个班级自己内部进行计算.对吧. 此时, 我们需要的就是一个分组的操作. 此时需要用到group by语句
select * from table_name group by col_name
注意, 上方的sql是无法使用的. sql要求分组后, 到底要做什么必须明确指出. 否则报错
那很容易呀, 我们分完组要计算每个班级的平均年龄. 平均数如何计算, 这就要用到聚合函数. sql中提供5种聚合函数, 分别是: avg(), sum(), min(), max(), count()
-- 查询每一个班级的平均年龄
select avg(sage), class_name from STUDENT group by class_name;
-- 查询每个班级最小的年龄
select min(sage), class_name from STUDENT group by class_name;
-- 查询每个班的最大年龄
select max(sage), class_name from STUDENT group by class_name;
-- 查询每个班的学生数量
select count(*), class_name from STUDENT group by class_name;
-- 查询每个班级的年龄和
select sum(sage), class_name from STUDENT group by class_name;
注意, 不要把没有放在group by的内容直接放在select中. 你想想. 按照班级来查询平均年龄, 你非要把某一个人的信息放在结果里. 是不合适的.
如果我们需要对聚合函数计算的结果进一步的筛选. 可以用having语句
-- 查询平均年龄在15岁以上的班级信息
select avg(sage), class_name from student group by class_name having avg(sage) > 15;
having和where的区别
:
where, 在原始数据上进行的数据筛选
.
having, 在聚合函数计算后的结果进行筛选
.
sql中使用order by
语句对查询结果进行排序
.
-- 按照年龄从小到大查询学生信息
select * from student order by sage asc
-- 按照年龄从大到小查询学生信息
select * from student order by sage desc
在实际使用中, 一个表格肯定是无法满足我们数据存储的. 比如, 在学生选课系统中. 我们就可以设计成以下表结构:
在这样的表结构中:
优势
: 每个表的结构相对明确. 不存在歧义. 数据保存完整, 没有冗余.
劣势
: 新手不太好想. 想不通为什么要这样设计. 这里涉及到数据库表结构设计范式, 该模型属于第三范式(听过就行).
在该模型表结构中. 成绩表是非常重要的. 在成绩表中, 明确的说明了哪个学生的哪一门课程得了多少分. 它将两个原来毫不相关的表关联了起来. 建立了主外键关系.
何为主外键关系
:
把A表中的主键放在另一张表里作为普通字段使用, 但数据要求必须来自于A. 这个很好理解. 比如, 学生成绩表中的学生编号数据就必须来自于学生表. 否则该数据是无意义的.
注意, 以上结构只是为了讲解多表关系. 并非完整的学生选课系统表结构.
建表语句:
-- 创建学生表, 课程表, 成绩表
-- 1. 学生表: 学号, 姓名, 性别, 住址等...
-- 2. 课程表: 课程编号, 课程名称, 授课教师等...
-- 3. 学生课程-成绩表: 成绩表编号, 学号, 课程编号, 成绩
create table stu(
sid int primary key auto_increment,
sname varchar(50) not null,
gender int(1),
address varchar(255)
);
create table course(
cid int primary key auto_increment,
cname varchar(50) not null,
teacher varchar(50)
);
create table sc(
sc_id int primary key auto_increment,
s_id int,
c_id int,
score int,
CONSTRAINT FK_SC_STU_S_ID FOREIGN key(s_id) REFERENCES stu(sid),
CONSTRAINT FK_SC_COURSE_C_ID FOREIGN key(c_id) REFERENCES course(cid)
);
在where语句中可以进行另外的一个查询.
例如, 查询选择了"编程"这门课的学生
-- 查询选择了"编程"这门课的学生
-- 先查询编程课程的编号
select cid from course where cname = '编程';
-- 根据cid可以去sc表查询出学生的id
select s_id from sc where c_id = 2;
-- 根据学生ID查询学生信息
select * from stu where sid in (1,2,3,4,5,6);
-- 把上面的sql穿起来
select * from stu where sid in (
select s_id from sc where c_id in (
select cid from course where cname = '编程'
)
);
-- 查询课程名称为“编程”,且分数低于60的学生姓名和分数
select stu.sname, sc.score from stu, sc where stu.sid = sc.s_id and sc.score < 60 and sc.c_id in (
select cid from course where cname = '编程'
)
关联查询
就是把多个表格通过join的方式
合并在一起. 然后进行条件检索.
语法规则:
select ... from A xxx join B on A.字段1 = b.字段2
表示: A表和B表连接. 通过A表的字段1和b表的字段2进行连接. 通常on后面的都是主外键关系
-- 查询每门课程被选修的学生数
-- count(*)
-- group by cid
select c.cid,c.cname, count(*) from sc inner join course c on sc.c_id = c.cid group by c.cid, c.cname
-- 查询所有学生的选课情况
select s.sname, c.cname from stu s left join sc on s.sid= sc.s_id left join course c on sc.c_id = c.cid
-- 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
-- score > 70 sc
-- sname student
-- cname course
select s.sname, c.cname, sc.score from stu s inner join sc on s.sid = sc.s_id inner join course c on sc.c_id = c.cid
where sc.score > 70
import pymysql # 导入模块
from pymysql.cursors import DictCursor # 导入字典模块
# 1. 创建连接
conn = pymysql.connect(
# 当忘记参数是什么的时候,直接按住commond点进去看看
user='root', # 用户名
password="x", # 密码
host='127.0.0.1', # 端口
database='test', # 数据库名
)
# 2. 创建cursor, 游标 -> 用于执行sql语句,,以及获取sql执行结果
cursor = conn.cursor()
# 2.1 执行sql语句
cursor.execute('select * from student')
r = cursor.fetchall() # 获取结果
print(r) # 运行完毕,会发现是元组套元组的形式 # ( (), () )
# 而我们喜欢的数据类型应该是 [{cno:1, cname:xxx, xxx: xxx}, {}, {}]
# 所以需要导入一个字典模块
# 将导入的模块放到游标里
cursor1 = conn.cursor(DictCursor)
# 2.1 执行sql语句
cursor1.execute('select * from student')
r = cursor1.fetchall() # 获取结果
print(r) # 可以发现已经成为我们想要的那个类型了
运行结果
import pymysql # 导入模块
from pymysql.cursors import DictCursor # 导入字典模块
# 1. 创建连接
conn = pymysql.connect(
# 当忘记参数是什么的时候,直接按住commond点进去看看
user='root', # 用户名
password="x", # 密码
host='127.0.0.1', # 端口
database='test', # 数据库名
)
# 2. 新增数据
cursor = conn.cursor()
sname = 'wby'
sbirthday = '2010-08-10'
saddress = '浙江宁波'
class_name = '少年团'
# 准备好sql语句
# 注意: 这种sql的问题 1. 很乱, 2. 有被注入的风险,可以选择下面的方式
sql = f'insert into student(sname, sbirthday, saddress, class_name) values ("{sname}", "{sbirthday}", "{saddress}", "{class_name}")'
cursor.execute(sql)
# 数据增加后,需要提交
conn.commit()
# %s字符串的占位符 用来预处理,有几个参数要填入,就写几个%s -> 推荐这种方法
sql = f'insert into student(sname, sbirthday, saddress, class_name) values (%s, %s, %s, %s)'
# 在execute中放预处理的内容, 注意传入的是元组的形式
cursor.execute(sql, (sname, sbirthday, saddress, class_name))
conn.commit()
增加数据
操作insert into 表(字段1,字段2,字段3...) values (值1,值2,值3...)
修改数据
操作update 表 set 字段=值, 字段=值 where 条件
删除数据
操作delete from 表 where 条件
查询数据
操作select * from 表 where 条件