MySQL数据库存储

MySQL数据库简介

  • 数据持久化
  • MySQL是关系型数据库,MSSQL, Oracle,等等…

MySQL开发环境

MySQL安装

图形化界面工具Navicat使用

表的操作

表的概念

  • 关系型数据库中, 数据都是以表格的形式进行存储。
    例表,学生:学号(唯一标识),姓名,生日,家庭住址,电话号等信息

OK. 按照这个规则来看. 每一条数据对应的就是一个人的信息.

3.2 创建表

创建表有两种方案:

  1. 用SQL语句创建表格
    create table table_student(
    	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),
    )
    
  • 数据类型:
数据类型说明
int整数
double小数
varchar字符串
date时间(年月日)
datetime时间(年月日时分秒)
text文本
  • 约束条件:
数据类型说明
primary key主键, 全表唯一值
auto_increment主键自增.
not null不可以为空.
null可以为空
default设置默认值
  1. 用Navicat图形化工具建表

3.3 修改表

  1. SQL方式
  • 添加一列
    ALTER TABLE table_name ADD COLUMN column_name datatype
  • 删除一列
    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
  1. Navicat方式
    在表上右键,点:Design Table,然后根据说明更改

数据的操作-增删改查

4.1 增加数据

INSERT INTO table_name(col1, col2, ccol3...) values (val1,val2,val3)
  • 添加学生信息
INSERT INTO STUDENT(sname, sbirthday, saddress, sage, class_name) values ('周杰伦', '2020-01-02', "北京市昌平区", 18, "二班");
  • 如果主键设置自增, 就不用处理主键了. mysql会自动照自然顺序进行逐一自增.

4.2 删除数据

DELETE FROM table_name where 条件语句
  • 删除sno = 1的学生信息
DELETE FROM STUDENT where sno = 1 ; 

4.3 修改数据

UPDATE table_name SET col1 = val1, col2 = val2... where 条件语句
  • 修改sno = 1的学生的名字信息
UPDATE STUDENT SET SNAME = '王力宏' where sno = 1;
  • 修改和删除数据的时候一定要带上where条件
  • 不带条件的修改和删除,就是对全表更新、删除

4.4 查询数据

4.4.1 基础查询

SELECT *|col1, col2, col3 FROM table_name where_条件
  • 全表查询
SELECT * FROM STUDENT;
  • 查询学生表的某一字段,姓名、年龄
SELECT sname, sage FROM STUDENT;
  • 根据学号查询学生信息
select * from student where sno = 1;
  • 查询年龄大于20的学生信息
select * from student where sage > 20;

  • and查询学生年龄大于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 '张%';

4.4.2 分组查询和聚合函数

  • 分组group by
  • 不能单独使用,会报错,需配合聚合函数
select * from table_name group by col_name
  • 5种聚合函数, 分别是: avg(), sum(), min(), max(), count()
  • avg()查询每一个组的平均年龄
select avg(sage), class_name from STUDENT group by class_name;
  • min()查询每个组最小的年龄
select min(sage), class_name from STUDENT group by class_name;
  • max()查询每个组的最大年龄
select max(sage), class_name from STUDENT group by class_name;
  • count()查询每个组的学生数量
select count(*), class_name from STUDENT group by class_name;
  • sum()查询每个组的年龄和
select sum(sage), class_name from STUDENT group by class_name;
  • ****不要把没有放在group by的内容直接放在select中。先分组,按分组信息进行查询,原来的字段未分组,放进select中组表显示,就显得混乱。

4.4.4 having语句

  • having语句对聚合函数计算的结果进一步的筛选
  • 查询平均年龄在15岁以上的组信息
select avg(sage), class_name from student group by class_name having avg(sage) > 15;
  • having和where的区别:where是在原始数据上进行的数据筛选。而having是在聚合函数计算后的结果进行筛选。

4.4.5 排序

  • order by语句对查询结果进行排序.
  • asc按照年龄从小到大查询学生信息
select * from student order by sage asc
  • desc按照年龄从大到小查询学生信息
select * from student order by sage desc

4.5 多表联合查询

  • 数据库表结构设计范式, 第三范式,每个表的结构相对明确. 不存在歧义. 数据保存完整, 没有冗余,按此建表:
  1. 学生表: 学号, 姓名, 性别, 住址等…
  2. 课程表: 课程编号, 课程名称, 授课教师等…
  3. 学生课程-成绩表: 成绩表编号, 学号, 课程编号, 成绩
  • 在该模型表结构中. 成绩表是非常重要的. 在成绩表中, 明确的说明了哪个学生的哪一门课程得了多少分. 它将两个原来毫不相关的表关联了起来. 建立了主外键关系.
  • 主外键关系: 把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)
);

4.5.1 子查询

​ 在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 = '编程'
)

4.5.2 关联查询

  • 把多个表格通过join的方式合并在一起进行条件检索。

  • A表和B表连接. 通过A表的字段1和b表的字段2进行连接。

  • on后面的都是主外键关系

select ... from A xxx join B on A.字段1 = b.字段2
4.5.2.1 inner jion
-- 查询每门课程被选修的学生数
-- 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
4.5.2.2 left join
-- 查询所有学生的选课情况
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

Python连接MySQL

  • python用pymysql模块连接mysql
  • 安装pymysql
pip install pymysql
  • pymysql基本使用:
import pymysql

# 链接数据库
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='test123456',
    database='spider_back'
)
# 创建游标,游标执行增删改查
cursor = conn.cursor()

5.1 pymysql执行增删改操作

5.1.1 添加数据

try:
    cursor = conn.cursor()
    result = cursor.execute("insert into stu(sname, address, gender) values ('张三', '恒大别野', 1)")
    print(cursor.lastrowid)  # 获取自增的ID值
    print(result)  # result是该sql会影响多少条数据
    conn.commit()  # 提交
except:
    conn.rollback()  # 回滚

5.1.2 修改数据

try:
    cursor = conn.cursor()
    result = cursor.execute("update stu set gender = 2 where sid = 12")
    print(result)  # result是该sql会影响多少条数据
    conn.commit()  # 提交
except:
    conn.rollback()  # 回滚

5.1.3 删除数据

try:
    cursor = conn.cursor()
    result = cursor.execute("delete from stu where sid = 12")
    print(result)  # result是该sql会影响多少条数据
    conn.commit()  # 提交
except:
    conn.rollback()  # 回滚

5.2 查询操作

# 查询
from pymysql.cursors import DictCursor
# cursor = conn.cursor(DictCursor)  # 使用字典游标. 查询出的结果自动保存在字典中
cursor = conn.cursor()  # 默认游标. 查询出的结果自动保存在元组中

sql = """
    select * from stu
"""
ret_num = cursor.execute(sql)
# result = cursor.fetchall()  # 获取全部结果
# result = cursor.fetchmany(5)  # 获取部分结果
result = cursor.fetchone()  # 获取单个结果
print(result)
# 一个游标如果被拿空了. 则不能再次获取内容. 
result = cursor.fetchone()  # 获取单个结果, 可以连续获取
print(result)

5.3 封装一个简单的DBHelper

import pymysql
from pymysql.cursors import DictCursor
class NoDataBaseException(Exception):
    pass
class DBHelper:
    def __init__(self, database=None, host="localhost", port=3306, username="root", password="123456"):
        if database:
            self.conn = pymysql.connect(
                host=host,
                port=port,
                user=username,
                password=password,
                database=database
            )
        else:
            raise NoDataBaseException("没有提供正确的数据库")

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        return self.conn.close()

    def _change(self, sql,  *args, isInsert=False):
        cursor = self.conn.cursor()
        try:
            rownum = cursor.execute(sql, args)
            self.conn.commit()
            if isInsert:
                return cursor.lastrowid
            else:
                return rownum
        except Exception as e:
            print("报错了", e)
            self.conn.rollback()
        finally:
            cursor.close()

    def insert(self, sql, *args):
        return self._change(sql, *args, isInsert=True)

    def update(self, sql, *args):
        return self._change(sql, *args)

    def delete(self, sql, *args):
        return self._change(sql, *args)

    def query_list(self, sql, *args):
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql, args)
            result = cursor.fetchall()
            return result
        finally:
            cursor.close()

    def query_one(self, sql, *args):
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql, args)
            result = cursor.fetchone()
            return result
        finally:
            cursor.close()


if __name__ == '__main__':
    with DBHelper("spider_back") as db:
        # result = db.query_list("select * from stu where gender=%s and address like %s", 1, "%北京%")
        # print(result)
        result = db.delete("delete from stu where sid = %s", 10)
        print(result)

  • MySQL远程连接时, 必须对数据库做一个简短的配置

1, 打开mysql命令行.

2, 输入以下内容

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
FLUSH PRIVILEGES;
  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值