一、数据库
数据库就是组织数据并存储的库,作用就是组织数据并存储数据。一般是按库——>表——>数据的层级组织数据。
数据库管理软件常见的有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()