一、数据库
数据库就是组织数据并存储的库，作用就是组织数据并存储数据。一般是按库——>表——>数据的层级组织数据。
数据库管理软件常见的有ORACLE，MySQL，SQLServer，SQLite等等。MySQL是由瑞典的DataKonsultAB公司研发，目前被Oracle收购。MySQL是一个中小型的数据库，具有体积小、速度快、成本低等特点。
一般开发者都会使用第三方的图形化工具进行MySQL的操作，比如本课程用的DBeaver，是一款跨平台、开源、免费的图形化工具。
*SQL
全称为Structured Query Language,结构化查询语言，SQL语言是专门针对数据库及数据进行操作、管理、查询的工具语言。
SQL在1974年提出，经过多年发展，已经成为数据库领域统一的数据操作标准语言，几乎所有的数据库系统都支持SQL语言。
数据库的操作不仅仅是存储数据，还包括：数据的管理、表/库的管理、账户管理、权限管理等等。基于功能，SQL语言可以划分为四类：
1、数据定义：DDL（Data Dedinition Language）。库/表的创建删除等。
2、数据操纵：DML（Data Manipulation Language）。新增数据、删除/修改数据等。
3、数据控制：DCL（Data Control Language）。新增/删除用户、密码修改、权限管理等。
4、数据查询：DQL（Data Query Language）。基于需求查询和计算数据。
*SQL语言大小写不敏感；
*每个语句以分号";"结束，并且同一句可单行或多行书写，只要以分号结束即可。
*SQL的注释：1、单行注释：-- 注释内容（--后面必须要有一个空格）；2、单行注释：#注释内容（#后面可以不加空格，但建议加上）；3、多行注释：/*注释内容*/
一、DDL 数据定义语言
（一）库管理语句
1、查看数据库：show databases;
2、使用数据库：use 数据库名;
3、创建数据库：create database 数据库名 [charset utf8]（可选设置编码字符集）;
4、删除数据库：drop database 数据库名;
5、查看当前使用的数据库：select database();
# 创建一个名为test的数据库，使用过utf8编码 create database test charset utf8; # 查看数据库 show databases; # 删除数据库test drop database test;
（二）表管理语句
1、查看表：show tables; 需要先选择数据库（use 数据库名）
2、创建表：
create table 表名(
列名称 列类型，
列名称 列类型,
...
);
列类型主要包括：int-整数，float-浮点数，varchar（最大长度）-文本，date-日期，timestamp-时间戳类型.
3、删除表：drop table 表名;或者drop table if exits 表名;
# 使用world数据库 use world;# 查看有哪些表 show tables;# 创建一个名为student的表,存储学生id，姓名，年龄 create table student(id int,name varchar(12),# 长度限制为10age int );# 删除表student drop table student;
二、DML 数据操纵语言
（一）数据插入语句
insert into 表名[(列1，列2，...）] value(值1,值2,...)
create database test charset utf8;use test;create table student(id int,name varchar(12),age int )# 对单列写入数据 insert into student(id) values(1),(2),(3);# 对所有列写入数据 insert into student(id, name, age) values(4, '李晓华', 18), (5, '刘东来', 19)# 所有列的数据写入也可以不使用(id, name, age) insert into student values(6, '王辉', 18), (7, '肖德生', 21)
（二）数据删除语句
delete from 表名 [where 条件判断]
# 删除student表中id为null的数据 delete from student where id=1;# 删除id大于5的数据 delete from student where id>5;# 删除age为19的数据 delete from student where age=19;
（三）数据更新语句
update 表名 set 列=值 【where条件判断】
# 更新student表中姓名为李华的id为4 update student set id=4 where name='李华';# 更新student表中姓名为王晓明的id为5 update student set id=5 where name='王晓明';update student set id=6 where name='刘东来';# 更新整张表中age均为18 update student set age=18;
课后作业：
use test; create table student2(id int,name varchar(12),age int,gender varchar(4) )# 先单列写入3行 insert into student2(id) values(10001),(10002),(10003);# 更新（修改）这三行的数据 update student2 set name='周杰论' where id=10001; update student2 set age=31 where id=10001; update student2 set gender='男' where id=10001; update student2 set name='王丽红' where id=10002; update student2 set age=33 where id=10002; update student2 set gender='男' where id=10002; update student2 set name='蔡译林' where id=10003; update student2 set age=35 where id=10003; update student2 set gender='女' where id=10003;# 再整体写入后续数据 insert into student2 values(10005,'林志林',38,'女'),(10006,'张大山',12,'男'),(10007,'刘志龙',32,'男'),(10008,'张晓光',18,'男'),(10009,'吕甜甜',28,'女'),(10010,'项羽凡',25,'男');
三、DQL 数据查询语言
基础语法：select 字段列表|* from 表名，表示从表中选择某些列进行展示
也可以带有指定条件：select 字段列表|* from 表名 where 条件判断，
# 查看studednt2的id,name,age列 select id, name, age from student2;# 查看student2所有列数据 select * from student2;#条件查看 select * from student2 where id<10006;select name, age from student2 where name="蔡译林";
分组聚合查询，就是指按类别分组后的查询统计。比如统计班级中男生和女生人数，就需要：1、按性别分组；2、统计每组人数；这就称为分组聚合。
基本语法： select 字段|聚合函数 from 表名 group by 列名，
聚合函数有：SUM（列） 求和、AVG（列）求平均值、MIN（列）求最小值、Max（列）求最大值、COUNT（列|*）求数量。
语法方面的一个限制是group by 后面出现了哪个列名，select中才能展示哪个。（因为按某个列名进行分组了，其他列与这列一般情况下是不同的分组方式，所以无法按该列的分组展示）
# 求按gender分组的年龄平均值 select avg(age) from student2 group by gender;# 上行代码只会显示两个平均年龄，但没有性别对应，所以增加性别信息显示： select gender, avg(age) from student2 group by gender;# 按姓名分组，显示各分组的平均年龄——当然，这样每个人都是对应一个分组，求平均及统计数量并无意义，只用来理解代码 select name, avg(age), count(*) from student2 group by name;select age, avg(age), count(*) from student2 group by age;#也可以多个聚合函数同时展示 select gender, avg(age), max(age), min(age), count(*) from student2 group by gender;
对查询结果进行筛选，条件判断：语句后加上where
select name, gender, age from student2 where age > 25;
对查询结果进行排序：使用order by 列名 升序/降序。
#先筛选，再进行排序(age asc表示按年龄升序) select name, gender, age from student2 where age > 20 order by age asc;
对查询结果进行分页限制：使用limit n[,m] ,表示跳过前面n条数据，筛选从第n+1开始的m个数据
# 限制只输出5条 select * from student2 limit 5;# 限制前2条跳过，筛选从第3条开始的5条 select * from student2 limit 2, 5;
同一语句出现多个关键字，编写顺序为先where，再group by ，再order by,再limit。
select age, avg(id), max(age), count(*) from student2 where id > 10002 group by age order by age asc limit 8;
二、使用pymysql库进行mysql数据库操作
""" 演示python pymysql库的基础操作，创建表、查看表内容 """from pymysql import Connection# 构建到MySQL数据库的链接 conn = Connection(host = "localhost", # 主机名（IP）port = 3306, # 端口user = "root", # 账户password = "####" # 密码 )# 获取数据库信息，测试是否连接上 print(conn.get_server_info())# 获取游标对象 cursor = conn.cursor() # 先选择数据库 conn.select_db("test")# 通过游标对象的execute()方法执行SQL # 执行SQL的创建表语句：“create table student_pymysql(id int);” # cursor.execute("create table student_pymysql(id int);")# 执行SQL查询语句 cursor.execute("select * from student2")# 通过游标对象的fetchall()方法获取数据 results = cursor.fetchall()for r in results:print(r)# 关闭连接对象和MySQL的链接 conn.close()
""" 演示使用pymysql库进行数据插入的操作 """from pymysql import Connection #构建连接 conn = Connection(host = "localhost", # 主机名（IP）port = 3306, # 端口user = "root", # 账户password = "####", # 密码autocommit = True )# 获取游标对象 cursor = conn.cursor() # 选择数据库 conn.select_db("test") # 执行SQL语句，插入数据 cursor.execute("insert into student2 values(10020, '梅大同', 27, '男'),(10021, '古小力', 21, '男'),(10020, '崔允珍', 22, '女')")# 数据内容的更改需要commit()方法确认 # 如果嫌麻烦，可以在构建conn连接对象时候增加autocommit=True # conn.commit()# 查询新的student2表 cursor.execute("select * from student2")# 输出查看 results = cursor.fetchall() for r in results:print(r)#关闭连接 conn.close()
综合案例一：将指定文件中数据写入数据库
SQL语句：
create database py_sql charset utf8;use py_sql;create table orders(order_date date,order_id varchar(255),money int,province varchar(10) );
python代码：
"""综合案例：使用SQL语句和pymysql库完成---通过SQL语言新建一个数据库，名称为py_sql，在其中新建一个表order---使用Python语言读取文件中数据，并写入py_sql数据库中的order表中 """# 数据封装类 class FileData:def __init__(self, order_date, order_id, money, province):self.order_date = order_dateself.order_id = order_idself.money = moneyself.province = provincedef __str__(self):return f"order_date:{self.order_date} order_id:{self.order_id} money:{self.money} province:{self.province}"# 定义文件读取类（抽象父类） class FileReader:def __init__(self, path):self.path = path# 约定类方法实现返回元素为FileData类对象的列表def file_read(self) ->list[FileData]:pass# 定义文本文件读取类,继承FileReader类 class TxtFileReader(FileReader):#复写file_reader()def file_read(self) ->list[FileData]:data_list = []f = open(self.path, "r", encoding="UTF-8")for line in f.readlines():line = line.strip()line_list = line.split(",")line_data = FileData(line_list[0], line_list[1], int(line_list[2]), line_list[3])data_list.append(line_data)# 测试语句# print(line_data) f.close()return data_list# 执行读取文件，获取数据（数据封装类对象） # 数据封装到列表中，每个元素为FileData类型的一组数据（类对象的四个属性） file_data : list[FileData] = TxtFileReader("1月省份销售额").file_read()# 新建数据库 from pymysql import Connection#建立连接 conn = Connection(host = "localhost", #主机名port = 3306, #端口号user = "root", #用户名password = "***", #密码autocommit = True #自动提交SQL语句 )# 获取游标对象 cursor = conn.cursor()# 选择test数据库 conn.select_db("py_sql")# 组织sql语句并逐行执行语句插入表中 for record in file_data:sql = f"insert into orders (order_date, order_id, money, province) " \f"values ('{record.order_date}', '{record.order_id}', {record.money}, '{record.province}')"cursor.execute(sql)conn.close()
综合案例二：将指定数据库表中数据写入文件
# 从数据库中读取数据到文件中import json from pymysql import Connectionconn = Connection(host = "localhost",port = 3306,user = "root",password = "****",autocommit = True )cursor = conn.cursor()conn.select_db('py_sql')cursor.execute("select * from orders")results = cursor.fetchall()#创建文件 f = open("1json.txt", "w", encoding="UTF-8") for r in results:r_dict = {"order_date" : str(r[0]), "order_id" : r[1], "money" : r[2], "province" : r[3]} # r[0]为Date类型，JSON中没有，需要转换类型r_json = json.dumps(r_dict, ensure_ascii=False) #涉及到中文需要第二个参数#写入文件f.write(r_json)
f.write('\n')
f.close()