目录
一、示例代码
再贴一下上一部的代码，该代码展示了建立链接，建库，建表，主键，外键，链接表，增删改查，删库这一系列操作。
import pymysql
database_name = 'testmysql'
delete_database = True # 删库
# delete_database = False # 不删库
connection = pymysql.connect( # 建立链接
host='localhost', # 主机位置（本地）
port=3306, # 端口
user='test', # 用户名
password='mysql', # 密码
charset='utf8mb4', # 字符集
# database=database_name, # 数据库名
)
# 创建数据库‘testmysql’，IF NOT EXISTS 如果不存在才执行
create_database_sql = f'CREATE DATABASE IF NOT EXISTS {database_name};'
show_sql = 'SHOW databases' # 查询数据库
goto_database = f'USE {database_name}' # 选择操作的数据库
# 创建班级表
create_table_Classes_sql = '''
CREATE TABLE IF NOT EXISTS 班级 ( -- 创建Classes表（班级）
班号 INT AUTO_INCREMENT PRIMARY KEY, -- 班级id int 自动递增 且为主键，主键自动递增比较好管理
班名 VARCHAR(100) NOT NULL, -- 班名 可变长字符串，不能为空
班导 VARCHAR(100) -- 班主任 可空（让我想起了当年一学年换5次班主任）
);
'''
# 创建学生表
create_table_Students_sql = '''
CREATE TABLE IF NOT EXISTS 学生 ( -- 创建Students表（学生）
学号 INT AUTO_INCREMENT PRIMARY KEY, -- 学号 int 自动递增 且为主键
姓名 VARCHAR(100) NOT NULL, -- 学生名 可变长字符串，不能为空
性别 ENUM('男', '女', '其他','直升机') NOT NULL, -- 枚举类型的性别
民族 VARCHAR(50),
班级 INT, -- 班级为外键,注意ON DELETE CASCADE表示级联删除，班没了学生外键班级也会删除，
-- CASCADE ON UPDATE CASCADE 表示级联更新，班号要是改了，这里也会同步更改
FOREIGN KEY (班级) REFERENCES 班级(班号) ON DELETE CASCADE ON UPDATE CASCADE
);
'''
delete_database_sql = f'DROP DATABASE IF EXISTS {database_name};' # 若存在，删库（删库跑路）
try:
with connection.cursor() as cursor: # 创建游标对象
cursor.execute(show_sql) # 执行 查询 sql语句
all_database_name = cursor.fetchall() # 获取查询所有行
if not (database_name,) in all_database_name: # 返回了元组所以需要包装一下
# if True:
# 建库===================================================================
cursor.execute(create_database_sql) # 执行 创建 sql语句
cursor.execute(show_sql) # 查看创建好了没(有问题一般就报错了，通常不用看)
all_database_name = cursor.fetchall()
if (database_name,) in all_database_name:
print('数据库创建完成')
cursor.execute(goto_database) # 选择操作的数据库
# 建表===================================================================
cursor.execute(create_table_Classes_sql)
cursor.execute(create_table_Students_sql)
# connection.commit() # 建库，建表都会隐式提交，不需要写提交事务语句，同时也无法回滚
# 增===================================================================
insert_Class_sql = '''
INSERT INTO 班级 (班名, 班导) VALUES (%s, %s),(%s, %s),(%s, %s)
'''
data_Class = ['日升班', '道光', '伏流班', '汪洋', '腾渊班', '菲杨']
cursor.execute(insert_Class_sql, data_Class)
insert_Students_sql = '''
INSERT INTO 学生 (姓名, 性别,民族,班级) VALUES (%s, %s, %s, %s)
'''
name = ['赵匡胤', '钱镠', '孙悟空', '武曌', '曹雪芹', '杰瑞', '张三', '约翰·纳什', '武直-10']
sex0 = ['男', '男', '男', '女', '男', '其他', '男', '男', '直升机'] # 枚举类型，如果填别的会报错
nati = ['汉', '汉', '汉', '汉', '满', '米奇', '汉', '维京', '中华民族']
clas = [2, 3, 3, 2, 1, 1, 1, 2, 3] # 注意，因为主键自动递增控制，所以，初始设置的三个班主键分别为1，2，3
data = [[name[i], sex0[i], nati[i], clas[i], ] for i in range(len(name))]
cursor.executemany(insert_Students_sql, data)
connection.commit() # 通常来说，事件全完成再提交，这里是为了方便打断点看
# 删===================================================================
# 听说，生产环境中不删数据，只设置一个变量，查询不显示。
delete_sql = 'DELETE FROM 学生 WHERE 姓名=%s;' # 删掉学生：杰瑞
cursor.execute(delete_sql, '杰瑞')
# cursor.execute(delete_sql, ('杰瑞',)) # 一个参数没什么格式要求
# cursor.execute(delete_sql, (('杰瑞'),))
connection.commit()
# 改===================================================================
change_sql = 'UPDATE 学生 SET 性别 = %s,民族 = %s WHERE 姓名 = %s;' # 悟空天生地养，无族无性
cursor.executemany(change_sql, (('其他', None, '孙悟空'),))
# cursor.executemany(change_sql, [['其他', None, '孙悟空']]) # 列表也行
connection.commit()
# 查===================================================================
select_classes_sql = 'SELECT * FROM 班级' # 显示所有班级
cursor.execute(select_classes_sql)
classes = cursor.fetchall()
print('班级有：')
for class_info in classes:
print(class_info)
# 跨表方式1
select_students_sql = '''
SELECT * FROM 学生
WHERE 班级 in (SELECT 班号 FROM 班级 WHERE 班名 = %s);
'''
cursor.execute(select_students_sql, '腾渊班') # 查询腾渊班的学生
students = cursor.fetchall()
print('腾渊班的学生有:')
for student_info in students:
print(student_info)
# 跨表方式2
select_students_sql = '''
SELECT 学生.*
FROM 学生 JOIN 班级 ON 学生.班级 = 班级.班号
WHERE 班级.班名 = %s;
'''
cursor.execute(select_students_sql, '伏流班') # 查询伏流班的学生
students = cursor.fetchall()
print('伏流班的学生有:')
for student_info in students:
print(student_info)
# 查不修改数据，无需提交
# 删库==================================================================
if delete_database:
cursor.execute(delete_database_sql) # 删库
cursor.execute(show_sql) # 查询
all_database_name = cursor.fetchall() # 获取查询所有行，返回列表
if not (database_name,) in all_database_name:
print('数据库删除完成')
else:
print('未删除数据库')
else:
print(f"存在 {database_name} 数据库，为防止删除重要数据数据，已停止执行测试程序")
# 提交事务
connection.commit()
except pymysql.MySQLError as e:
print(f"Error: {e}")
connection.rollback() # 回滚
finally:
# 关闭数据库连接
connection.close()
print('链接已关闭')二、常用SQL语句及概念
sql对大小写不敏感，但数据库名称、表名称、字段名称会根据配置来判断是否区分。
MySQL每条语句都要以分号结尾，语句内部会忽视空白符，具体就是你可以在任何一个单词后面加回车或者任意个空格。
SQL语句通常分为：数据定义语言（ddl）、数据操作语言（dml）、数据控制语言（dcl）、事务控制语言（tcl）。功能可看下表：
| DDL-数据定义语言 | 创建、修改和删除数据库对象 |
| DML-数据操作语言 | 操作数据 |
| DCL-数据控制语言 | 管理权限 |
| TCL-事务控制语言 | 管理事务 |
| 单行注释1 | -- 注释内容，注意“--”后面要加空格再跟内容 |
| 单行注释2 | #注释内容，不需要加空格，和python一样 |
| 多行注释 | /*注释内容*/,注释内容可跨行*/后面的内容能正常执行 |
DDL-数据定义语言
DDL的语句一般隐式提交，你执行了没有回滚的说法，所以操作小心
数据库名和表名可能会与MySQL的保留关键字冲突，使用反引号括起来（~键）
| 创建数据库 | CREATE DATABASE [IF NOT EXISTS] `数据库名` [CHARACTER SET '字符集'] [COLLATE '排序规则']; |
| 删除数据库 | DROP DATABASE [IF EXISTS] `数据库名`; |
| 查询所有数据库 | SHOW DATABASES; |
| 选择数据库 | USE`数据库名`; |
| 查看当前数据库 | SELECT DATABASE(); |
创建和删除数据库建议用可视化软件做，比较方便。
| 查找所有表 | SHOW TABLES; |
| 查看表结构 | DESC `表名`; |
| 查看表的创建语句 | SHOW CREATE TABLE `表名`; |
创建表
基础语句：
CREATE TABLE `表名` (
字段名 类型 [约束] [COMMENT '注释'],
字段名 类型 [约束] [COMMENT '注释'],
······
);其中， 类型和约束可以填下面的值：
| 整型 | BIGINT INT MEDIUMINT SMALLINT TINYINT | 分别为8，4，3，2，1字节 |
| 浮点数 | FLOAT DOUBLE | 单精度和双精度，经典 |
| 定点数 | DECIMAL | 如DECIMAL(3,2)3位数字其中2位是小数 |
| 日期和时间 | DATE TIME DATETIME TIMESTAMP | DATE格式 YYYY-MM-DD， TIMESTAMP 时间戳（自计算机元年1970以来的秒数） |
| 字符串 | CHAR VARCHAR TEXT | 这个类似C语言， VARCHAR 可变长字符串，VARCHAR(5)为最大长度5的字符串，实际使用存储字符空间+1字节长度（一个字节存实际保存长度） TEXT 类似VARCHAR 不用指定长度，使用2字节保存实际长度。 |
| 枚举和集合 | ENUM SET | ENUM 预定义一组字符串值，选一个值。 SET 集合类型，预定义一组字符串值，可选多个值 |
| PRIMARY KEY | 主键（该行的唯一标识） |
| UNIQUE | 唯一约束（该字段每个值都唯一） |
| NOT NULL | 非空约束（不能为空） |
| DEFAULT | 默认值约束（设置未填写的默认值） |
| FOREIGN KEY | 外键约束（与另外一个表某个数据一致） |
| CHECK | 检查约束（取值符合某个规则） |
| 添加列 | ALTER TABLE 表名 ADD 字段名 字段类型 [约束]; |
| 删除列 | ALTER TABLE 表名 DROP COLUMN 字段名; |
| 修改列 | ALTER TABLE 表名 MODIFY COLUMN 字段名 新类型 ; |
| 改表名 | ALTER TABLE 表名 RENAME TO 新表名; |
DML-数据操作语言
| 增 | INSERT INTO 表名 [(字段1, 字段2)] VALUES (值1,值2),(值1,值2),(值1,值2); | 省略字段名每个字段都要一一对应的赋值。 |
| 删 | DELETE FROM 表名 WHERE 条件; | 条件多种多样，甚至套个查询子语句进去。 |
| 改 | UPDATE 表名 SET 字段1 = 表达式,字段2 = 表达式 WHERE 条件; | 如上 |
| 查 | SELECT 字段1,字段2 FROM 表名 WHERE 条件; | 字段名是查询返回的值，用 * 返回全部字段 |
DCL-数据控制语言
这个主要赋权限，不常用，权限名就是操作的关键字
| 授予权限 | GRANT 权限1, 权限2 ON 表名 TO 用户名 [WITH GRANT OPTION]; |
| 撤销权限 | REVOKE 权限1, 权限2 ON 表名 FROM 用户名; |
TCL-事务控制语言
事务是一组作为单个逻辑工作单元执行的操作，这些操作要么全都执行，要么全都不执行，以确保数据库的一致性和完整性，听说过的回滚就是事务执行失败，数据库回溯到这个事务执行前的状态。不设事务的情况下，每条SQL语句都被视为一个事务，自动提交。
| 开启事务 | BEGIN TRANSACTION; 或 START TRANSACTION; |
| 提交事务 | COMMIT; |
| 回滚事务 | ROLLBACK; |
| 设保存点 | SAVEPOINT; |
三、常用pymysql函数
数据库链接：connection = pymysql.connect(）
参数：主机名（host）、端口号（port）、用户名（user）、密码（password）、数据库名（database）和字符集（charset）等，用完connection.close()关闭
提交事务：connection.commit()
回滚事务：connection.rollback()
创建游标对象：cursor = connection.cursor()
connection是数据库链接，游标用完要关，用cursor.close()关常用以下代码用完自动关闭：
with connection.cursor() as cursor: # 创建游标对象
所谓游标，就是SQL语句执行返回的结果集合，个人把它看成是结果的指针列表。
执行SQL语句：cursor.execute()
cursor是游标对象，执行后可以使用下述几条获取数据语句
获取第一条结果：cursor.fetchone()
获取多条数据：cursor.fetchmany(size) # 参数 size 指定要获取的数据条数。
获取所有结果：cursor.fetchall()
这次操作所影响的行数：cursor.rowcount
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
