python与MySQL-优快云博客

SQL语言相关知识点

一、SQL语言分类

SQL（Structured Query Language，结构化查询语言）是用于管理关系型数据库的标准语言，主要分为以下几类：

DDL（Data Definition Language，数据定义语言）：用于定义数据库对象，如创建、修改、删除数据库和表等。常用命令有CREATE、ALTER、DROP等。

DML（Data Manipulation Language，数据操纵语言）：用于对数据库中的数据进行操作，如插入、删除、更新数据。常用命令有INSERT、DELETE、UPDATE等。

DQL（Data Query Language，数据查询语言）：用于查询数据库中的数据，主要命令是SELECT。

DCL（Data Control Language，数据控制语言）：用于控制数据库的访问权限，如授予或撤销用户权限。常用命令有GRANT、REVOKE等。

TCL（Transaction Control Language，事务控制语言）：用于管理数据库事务，如提交、回滚事务。常用命令有COMMIT、ROLLBACK、SAVEPOINT等。

二、DDL语法

DDL用于定义和管理数据库对象，以下是常用的DDL命令及语法：

1. 数据库操作

创建数据库：

CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET 字符集] [COLLATE 校对规则];

示例：

CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看所有数据库：

SHOW DATABASES;

使用数据库：

USE 数据库名;

删除数据库：

DROP DATABASE [IF EXISTS] 数据库名;

示例：

DROP DATABASE IF EXISTS mydb;

2. 表操作

创建表：

CREATE TABLE [IF NOT EXISTS] 表名 (

列名1 数据类型 [约束条件],

列名2 数据类型 [约束条件],

...

[表级约束条件]

);

示例：

CREATE TABLE IF NOT EXISTS students (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

age INT,

gender ENUM('男', '女'),

birthday DATE

);

查看表结构：

DESCRIBE 表名; 或 DESC 表名;

查看所有表：

SHOW TABLES;

修改表名：

ALTER TABLE 旧表名 RENAME TO 新表名;

添加列：

ALTER TABLE 表名 ADD 列名数据类型 [约束条件];

示例：

ALTER TABLE students ADD address VARCHAR(100);

修改列的数据类型或约束：

ALTER TABLE 表名 MODIFY 列名新数据类型 [新约束条件];

示例：

ALTER TABLE students MODIFY age TINYINT;

修改列名：

ALTER TABLE 表名 CHANGE 旧列名新列名数据类型 [约束条件];

示例：

ALTER TABLE students CHANGE address home_address VARCHAR(150);

删除列：

ALTER TABLE 表名 DROP 列名;

示例：

ALTER TABLE students DROP home_address;

3. 删除表

DROP TABLE [IF EXISTS] 表名;

示例：

DROP TABLE IF EXISTS students;

三、DML：数据插入、删除、更新

DML用于对表中的数据进行操作，以下是常用的DML命令及语法：

1. 插入数据（INSERT）

语法1：插入指定列的数据

INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

语法2：插入所有列的数据（列名可省略，但值的顺序需与表中列的顺序一致）

INSERT INTO 表名 VALUES (值1, 值2, ...);

语法3：批量插入数据

INSERT INTO 表名 (列名1, 列名2, ...) VALUES

(值1, 值2, ...),

...;

示例：

INSERT INTO students (name, age, gender, birthday) VALUES

('张三', 20, '男', '2003-05-15'),

('李四', 19, '女', '2004-08-20');

2. 更新数据（UPDATE）

UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, ... [WHERE 条件];

注意：如果省略WHERE条件，将更新表中所有记录，使用时需谨慎。

示例：

-- 更新id为1的学生年龄为21

UPDATE students SET age = 21 WHERE id = 1;

-- 将所有女生的年龄增加1

UPDATE students SET age = age + 1 WHERE gender = '女';

3. 删除数据（DELETE）

DELETE FROM 表名 [WHERE 条件];

注意：如果省略WHERE条件，将删除表中所有记录，使用时需谨慎。

示例：

-- 删除id为3的学生记录

DELETE FROM students WHERE id = 3;

-- 删除年龄大于22的学生记录

DELETE FROM students WHERE age > 22;

四、DQL：基础查询、分组、聚合、排序、分页

DQL主要用于查询表中的数据，核心命令是SELECT，以下是常用的查询操作：

1. 基础查询

语法：

SELECT 列名1, 列名2, ... FROM 表名 [WHERE 条件];

查询所有列：

SELECT * FROM 表名 [WHERE 条件];

示例：

-- 查询所有学生的姓名和年龄

SELECT name, age FROM students;

-- 查询年龄大于19的学生的所有信息

SELECT * FROM students WHERE age > 19;

2. 分组查询（GROUP BY）

用于将数据按照指定列进行分组，通常与聚合函数一起使用。语法：

SELECT 分组列, 聚合函数(列名) FROM 表名 [WHERE 条件] GROUP BY 分组列 [HAVING 分组条件];

示例：

-- 按性别分组，查询每组的人数

SELECT gender, COUNT(*) AS 人数 FROM students GROUP BY gender;

-- 按性别分组，查询每组年龄大于18的人数

SELECT gender, COUNT(*) AS 人数 FROM students WHERE age > 18 GROUP BY gender HAVING 人数 > 5;

3. 聚合函数

用于对一组数据进行计算并返回单一值，常用的聚合函数有：

COUNT(列名)：计算指定列的非NULL值的数量

MAX(列名)：求指定列的最大值

MIN(列名)：求指定列的最小值

SUM(列名)：求指定列的总和

AVG(列名)：求指定列的平均值

示例：

-- 计算学生总数

SELECT COUNT(*) AS 学生总数 FROM students;

-- 计算学生的平均年龄

SELECT AVG(age) AS 平均年龄 FROM students;

-- 计算最大年龄和最小年龄

SELECT MAX(age) AS 最大年龄, MIN(age) AS 最小年龄 FROM students;

4. 排序（ORDER BY）

用于对查询结果进行排序，语法：

SELECT 列名1, 列名2, ... FROM 表名 [WHERE 条件] ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...;

说明：ASC表示升序（默认），DESC表示降序。

示例：

-- 按年龄升序排序

SELECT * FROM students ORDER BY age;

-- 按年龄降序排序，年龄相同则按id升序排序

SELECT * FROM students ORDER BY age DESC, id ASC;

5. 分页（LIMIT）

用于限制查询结果的数量，实现分页效果，语法：

SELECT 列名1, 列名2, ... FROM 表名 [WHERE 条件] [ORDER BY 列名] LIMIT 起始索引, 记录数;

说明：起始索引从0开始，若省略起始索引，则默认从0开始。

示例：

-- 查询前5条学生记录

SELECT * FROM students LIMIT 5;

-- 从第6条记录开始，查询5条学生记录（即第2页，每页5条）

SELECT * FROM students LIMIT 5, 5;

五、Python与代码执行SQL语句操作MySQL数据库基础使用和数据插入

在Python中，可以使用第三方库（如mysql-connector-python或pymysql）来操作MySQL数据库，以下是基础使用方法和数据插入操作：

1. 安装相关库

-- 安装mysql-connector-python

pip install mysql-connector-python

-- 或安装pymysql

pip install pymysql

2. 连接数据库

使用mysql-connector-python连接数据库示例：

import mysql.connector

# 连接数据库

mydb = mysql.connector.connect(

host="localhost", # 数据库主机地址

user="yourusername", # 数据库用户名

password="yourpassword", # 数据库密码

database="mydb" # 要连接的数据库名

)

# 创建游标对象，用于执行SQL语句

mycursor = mydb.cursor()

3. 执行SQL语句

执行DDL、DML、DQL等SQL语句：

# 执行DDL语句，创建表

create_table_sql = """

CREATE TABLE IF NOT EXISTS products (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100) NOT NULL,

price DECIMAL(10, 2),

stock INT

)

"""

mycursor.execute(create_table_sql)

# 执行DML语句，插入数据

insert_sql = "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)"

values = ("笔记本电脑", 5999.99, 10)

mycursor.execute(insert_sql, values)

# 提交事务，对于插入、更新、删除操作需要提交事务

mydb.commit()

# 执行DQL语句，查询数据

select_sql = "SELECT * FROM products"

mycursor.execute(select_sql)

# 获取查询结果

results = mycursor.fetchall() # 获取所有结果

for row in results:

print(row)

4. 批量插入数据

# 批量插入数据

insert_sql = "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)"

values = [

("手机", 3999.99, 20),

("平板电脑", 2999.99, 15),

("耳机", 799.99, 30)

]

# 使用executemany()方法批量执行

mycursor.executemany(insert_sql, values)

# 提交事务

mydb.commit()

print(f"批量插入了 {mycursor.rowcount} 条数据")

5. 关闭连接

操作完成后，需要关闭游标和数据库连接：

# 关闭游标

mycursor.close()

# 关闭数据库连接

mydb.close()