SQL介绍
学习目的
数据库
总述
数据库组织数据的形式
mySQL
使用
mysql -uroot -p
show databases;
use world;
show tables;
exit;
SQL基础和DDL
SQL基础
SQL分类
SQL语法特征
show
-- 我是注释
# 我是注释
/*
我是注释
*/
Databases;
数据库管理
show databases;
use world;
select database();
create database test charset utf8;
drop database test;
DDL-表管理
use world;
show tables;
create table student(
id int,
name varchar(10),
age int
);
drop table student;
DML-数据操作语言
插入
use world;
create table student(
id int,
name varchar(10),
age int
);
-- 仅插入id列数据
insert into student(id) values(10001), (10002), (10003);
# 插入全部数据
insert into student(id, name, age) values(10001, '周杰', 31), (10002, '林君君', 35), (10003, '李军', 26);
# 插入全部数据,快捷写法
insert into student values(10001, '周杰', 31), (10002, '林君君', 35), (10003, '李军', 26);
删除
use world;
create table student(
id int,
name varchar(10),
age int
);
insert into student(id) values(10001), (10002), (10003);
insert into student(id, name, age) values(10001, '周杰', 31), (10002, '林君君', 35), (10003, '李军', 26);
insert into student values(10001, '周杰', 31), (10002, '林君君', 35), (10003, '李军', 26);
-- 删除name为林君君的数据
delete from student where name = '林君君';
-- 删除age > 33的数据
delete from student where age > 30;
-- 删除全部数据
delete from student;
更新
use world;
create table student(
id int,
name varchar(10),
age int
);
insert into student values(10001, '周杰', 31), (10002, '林君君', 35), (10003, '李军', 26);
# 修改id为10001的name为张姐
update student set name = '张姐' where id = 10001;
# 修改全部数据的age为11
update student set age = 11;
练习
use world;
create table student(
id int,
name varchar(10),
age int,
gender varchar(10)
);
delete from student ;
insert into student values(10001, '周杰伦', 33, '男'),
(10002, '王力宏', 33, '男'), (10003, '蔡依林', 35, '女'),
(10004, '林志玲', 36, '女'), (10005, '刘德滑', 33, '女'),
(10006, '张大山', 10, '男'), (10007, '刘志龙', 11, '女'),
(10008, '王潇', 45, '男'), (10009, '张一梅', 23, '女'),
(10010, '王一倩', 33, '男'), (100011, '陈一迅', 12, '女'),
(10012, '张晓光', 34, '男'), (100013, '李大晓', 35, '女'),
(10014, '吕甜甜', 13, '男'), (100015, '曾悦悦', 32, '女'),
(10016, '刘佳慧', 32, '男'), (100017, '项羽凡', 25, '女'),
(10018, '刘德强', 45, '男'), (100019, '王强强', 31, '女'),
(10020, '林志慧', 5, '男');
DQL(Data QueryLanguage 数据查询语言)
聚集函数
基础查询
-- 查询年龄大于20人的信息
select * from student where age > 20;
10001 周杰伦 33 男
10002 王力宏 33 男
10003 蔡依林 35 女
10004 林志玲 36 女
10005 刘德滑 33 女
10008 王潇 45 男
10009 张一梅 23 女
10010 王一倩 33 男
10012 张晓光 34 男
100013 李大晓 35 女
100015 曾悦悦 32 女
10016 刘佳慧 32 男
100017 项羽凡 25 女
10018 刘德强 45 男
100019 王强强 31 女
分组聚合
-- 根据性别分组查询年龄大于20人的信息
select gender,count(name) from student where age > 20 group by gender ;
-- 查询男生女生的人数,年龄总和,平均年龄,最大值,最小值
select gender, count(name), sum(age), AVG(age), max(age), min(age) from student group by gender;
排序分页
-- 对年龄大于20岁的人按年龄降序排序结果
select * from student where age > 20 order by age desc;
10008 王潇 45 男
10018 刘德强 45 男
10004 林志玲 36 女
10003 蔡依林 35 女
100013 李大晓 35 女
10012 张晓光 34 男
10001 周杰伦 33 男
10002 王力宏 33 男
10005 刘德滑 33 女
10010 王一倩 33 男
100015 曾悦悦 32 女
10016 刘佳慧 32 男
100019 王强强 31 女
100017 项羽凡 25 女
10009 张一梅 23 女
-- 对年龄大于20岁的人按学号升序排序结果
select * from student where age > 20 order by id asc;
10001 周杰伦 33 男
10002 王力宏 33 男
10003 蔡依林 35 女
10004 林志玲 36 女
10005 刘德滑 33 女
10008 王潇 45 男
10009 张一梅 23 女
10010 王一倩 33 男
10012 张晓光 34 男
10016 刘佳慧 32 男
10018 刘德强 45 男
100013 李大晓 35 女
100015 曾悦悦 32 女
100017 项羽凡 25 女
100019 王强强 31 女
limit限制查询结果
-- 在年龄大于20岁的人中选出年龄最大的前五名
select * from student where age > 20 order by age desc limit 5;
10018 刘德强 45 男
10008 王潇 45 男
10004 林志玲 36 女
100013 李大晓 35 女
10003 蔡依林 35 女
-- 在年龄大于20岁的人中跳过年龄最大的十名后选出年龄较最大的前五名
select * from student where age > 20 order by age desc limit 10,5;
100015 曾悦悦 32 女
10016 刘佳慧 32 男
100019 王强强 31 女
100017 项羽凡 25 女
10009 张一梅 23 女
-- 计算个年龄段中的人数,并按人数降序排列
select age,count(id) from student group by age order by age desc;
45 2
36 1
35 2
34 1
33 4
32 2
31 1
25 1
23 1
13 1
12 1
11 1
10 1
5 1
pymysql
总述
pymysql的安装
pycharm连接pymysql的方法
# 从pymysql包中中导入一个Connection的对象
from pymysql import Connection
# 获取到mysql数据库的链接对象
coon = Connection(
host="localhost", # 主机名(或IP地址)
port=3306, # 端口
user="root", # 账户名
password="123456" # 密码
)
# 打印mysql数据库软件信息
print(coon.get_server_info())
# 关闭到数据库的链接
coon.close()
报错解决方案
解决办法:
mysql -h hostname -u username -p
例如:mysql -h 127.0.0.1 -u root -p
将 hostname 替换为 MySQL 服务器的地址,将 username 替换为您的 MySQL 用户名。
按 Enter 键后,系统会提示您输入密码。输入密码后按 Enter 键。
如果连接成功,您将看到类似于以下内容的提示符:
mysql>
执行非修改语句性质的SQL语句
创建表
from pymysql import Connection
coon = Connection(
port=3306,
host="localhost",
user="root",
password="123456"
)
cusor = coon.cursor()
coon.select_db("test") # 选择test数据库
cusor.execute("create table student(id int, name varchar(10), age int)") # 使用游标对象,执行sql语句,创建student表,这里的SQL语句可以不用;
coon.close()
查询表中数据
cusor = coon.cursor()
coon.select_db("world") # 选择world数据库
cusor.execute("select * from student") # 使用游标对象,执行sql语句
results: tuple = cusor.fetchall()
print(results)
for r in results:
print(r)
((10001, '周杰伦', 33, '男'), (10002, '王力宏', 33, '男'), (10003, '蔡依林', 35, '女'), (10004, '林志玲', 36, '女'), (10005, '刘德滑', 33, '女'), (10006, '张大山', 10, '男'), (10007, '刘志龙', 11, '女'), (10008, '王潇', 45, '男'), (10009, '张一梅', 23, '女'), (10010, '王一倩', 33, '男'), (100011, '陈一迅', 12, '女'), (10012, '张晓光', 34, '男'), (100013, '李大晓', 35, '女'), (10014, '吕甜甜', 13, '男'), (100015, '曾悦悦', 32, '女'), (10016, '刘佳慧', 32, '男'), (100017, '项羽凡', 25, '女'), (10018, '刘德强', 45, '男'), (100019, '王强强', 31, '女'), (10020, '林志慧', 5, '男'))
(10001, '周杰伦', 33, '男')
(10002, '王力宏', 33, '男')
(10003, '蔡依林', 35, '女')
(10004, '林志玲', 36, '女')
(10005, '刘德滑', 33, '女')
(10006, '张大山', 10, '男')
(10007, '刘志龙', 11, '女')
(10008, '王潇', 45, '男')
(10009, '张一梅', 23, '女')
(10010, '王一倩', 33, '男')
(100011, '陈一迅', 12, '女')
(10012, '张晓光', 34, '男')
(100013, '李大晓', 35, '女')
(10014, '吕甜甜', 13, '男')
(100015, '曾悦悦', 32, '女')
(10016, '刘佳慧', 32, '男')
(100017, '项羽凡', 25, '女')
(10018, '刘德强', 45, '男')
(100019, '王强强', 31, '女')
(10020, '林志慧', 5, '男')
执行修改语句性质的SQL语句
cusor = coon.cursor()
coon.select_db("test") # 选择world数据库
cusor.execute("insert into student(id) values(1001),(1002),(1003)") # 使用游标对象,执行sql语句
coon.commit()
from pymysql import Connection
coon = Connection(
port=3306,
host="localhost",
user="root",
password="123456",
autocommit=True
)
cusor = coon.cursor()
coon.select_db("test") # 选择world数据库
cusor.execute("insert into student(id) values(1001),(1002),(1003)") # 使用游标对象,执行sql语句
coon.close()
综合案例
data_define
# 数据的封装
class record:
def __init__(self, date, order_id, money, province):
self.date = date
self.order_id = order_id
self.money = money
self.province = province
def __str__(self):
return f"{self.date}, {self.order_id}, {self.money}, {self.province}"
file_define
# 设计一个抽象类,定义文件读取的相关功能,并使用子类实现具体功能读取文件
from data_define import record
import json
# 先定义一个抽象类用来做顶层设计,确定有哪些功能需要实现
class FileReader:
# 文件读取
def read_data(self) -> list[record]:
"""读取数据文件,读到的每一行数据都转化为record对象,将它们都封装到list内返回即可
:return:list[record]
"""
pass
class TextFileReader(FileReader):
def __init__(self,path):
self.path = path
def read_data(self) -> list[record]:
f = open(self.path, "r", encoding="UTF_8")
lines = f.readlines()
record_list = []
for line in lines:
line = line.strip()
line = line.split(",")
record_list.append(record(line[0],line[1],int(line[2]),line[3]))
f.close()
return record_list
class JsonFileReader(FileReader):
def __init__(self,path):
self.path = path
def read_data(self) -> list[record]:
f = open(self.path, "r", encoding="UTF_8")
record_list: list[record] = []
lines = f.readlines()
for line in lines:
line = line.strip()
json_dict = json.loads(line)
record_list.append(record(json_dict['date'], json_dict['order_id'], json_dict['money'], json_dict['province']))
f.close()
return record_list
if __name__ == "__main__":
text_file_reader = TextFileReader("C:/Users/18757/Desktop/pythontext/2011年1月销售数据.txt")
json_file_reader = JsonFileReader("C:/Users/18757/Desktop/pythontext/2011年2月销售数据JSON.txt")
l1 = text_file_reader.read_data()
l2 = json_file_reader.read_data()
for l in l1:
print(l)
for l in l2:
print(l)
main.py
from data_define import record
from file_define import FileReader, JsonFileReader, TextFileReader
from pymysql import Connection
January = TextFileReader("C:/Users/18757/Desktop/pythontext/2011年1月销售数据.txt").read_data()
Feburary = JsonFileReader("C:/Users/18757/Desktop/pythontext/2011年2月销售数据JSON.txt").read_data()
# 合并两个列表
sumlist = January + Feburary
coon = Connection(
host="localhost",
user="root",
password="123456",
database="sale",
port=3306,
autocommit=True
)
cusor = coon.cursor()
cusor.execute("create table day_sale(date DATE,order_id varchar(255),money int,province varchar(10))")
for r in sumlist:
cusor.execute(f"insert into day_sale values('{r.date}','{r.order_id}',{r.money},'{r.province}')")
课后作业
data_define.py
# 数据的封装
class record:
def __init__(self, date, order_id, money, province):
self.date = date
self.order_id = order_id
self.money = money
self.province = province
def __str__(self):
return f"{self.date}, {self.order_id}, {self.money}, {self.province}"
date_read.py
from data_define import record
from pymysql import Connection
class FileReader:
def read_data(self)->list[record]:
"""
从数据库中读取数据,将每一个元组封装成一个record对象返回即可
:return:
"""
pass
class DateBaseReader(FileReader):
def read_data(self)->list[record]:
coon = Connection(
host="localhost",
user="root",
password="123456",
port=3306,
autocommit=True
)
cusor = coon.cursor()
record_list: list[record] = []
cusor.execute("use sale")
cusor.execute("select * from day_sale")
result: tuple = cusor.fetchall()
for r in result:
record_list.append(record(str(r[0]),r[1],r[2],r[3]))
return record_list
if __name__ == "__main__":
Reader = DateBaseReader()
result = Reader.read_data()
for r in result:
print(r)
main.py
"""
1.设计record类
2.设计一个类用于读取数据库中的数据
3.将读出的数据转化为JSON所需要的数据类型
4.细调JSON数据
5.写入文件
"""
import json
from date_read import FileReader,DateBaseReader
from data_define import record
from json import loads
# 把数据读出来
Reader = DateBaseReader()
result_list:list[record]= Reader.read_data()
# 将其转化为符合JSON数据格式的Python数据
JSON_list:list[dict] = []
for r in result_list:
minDict = {}
# 把每一行数据转化为字典数据,存储起来
minDict["date"] = str(r.date)
minDict["order_id"] = r.order_id
minDict["money"] = r.money
minDict["province"] = r.province
JSON_list.append(minDict)
JSON_TEXT = json.dumps(JSON_list,ensure_ascii=False)
JSON_TEXT = JSON_TEXT.replace("["," ")
JSON_TEXT = JSON_TEXT.replace("]"," ")
JSON_TEXT = JSON_TEXT.replace("},","}\n")
f = open("C:/Users/18757/Desktop/pythontext/homework.txt","w",encoding= "UTF-8")
f.write(JSON_TEXT)
f.flush()