Python Mysql基础

01.示例: 使用 Python 创建 MySQL 数据库

# 1. 首先创建一个cursor(),然后将SQL命令作为字符串传递给execute()方法
import mysql.connector

dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password"
)
cursorObject = dataBase.cursor()

#创建数据库
# cursorObject.execute("CREATE DATABASE gfg")

02.创建表格

#用于创建表的sql命令是：
# CREATE TABLE
# (
#     column_name_1 column_Data_type,
#     column_name_2 column_Data_type,
#     :
#     :
#     column_name_n column_Data_type
# );

-- Active: 1665886695756@@127.0.0.1@3306@gfg
--用sql语句创建一个表:chars
CREATE TABLE `chars` (
  `chr` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`chr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
insert  into `chars`(`chr`) values ('1'),('10'),('11'),('2'),('222'),('3');

#创建表格
dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password",
  database = "gfg"
)
cursorObject = dataBase.cursor()
studentRecord = """CREATE TABLE STUDENT2 (
                   NAME  VARCHAR(20) NOT NULL,
                   BRANCH VARCHAR(50),
                   ROLL INT NOT NULL,
                   SECTION VARCHAR(5),
                   AGE INT
                   )"""
#创建表格
cursorObject.execute(studentRecord)
#断开连接
dataBase.close()

show tables

• 在vscode中用的MySQL插件
  
  
  

03.向表格中添加数据

--用SQl语句
-- INSERT INTO table_name (column_names) VALUES (data)
INSERT INTO student2 (NAME, BRANCH, ROLL, SECTION , AGE) VALUES ("Ram", "CSE", "85", "B", "19") 

用python向表格中添加数据（单行）

dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password",
  database = "gfg"
)
 
# preparing a cursor object
cursorObject = dataBase.cursor()
sql = "INSERT INTO STUDENT2 (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = ("jack", "CSE", "85", "B", "19")
cursorObject.execute(sql,val)
dataBase.commit()
dataBase.close()

添加多行数据

dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password",
  database = "gfg"
)
 
# preparing a cursor object
cursorObject = dataBase.cursor()
  
sql = "INSERT INTO STUDENT2 (NAME, BRANCH, ROLL, SECTION, AGE)\
VALUES (%s, %s, %s, %s, %s)"
val = [("Nikhil", "CSE", "98", "A", "18"),
       ("Nisha", "CSE", "99", "A", "18"),
       ("Rohan", "MAE", "43", "B", "20"),
       ("Amit", "ECE", "24", "A", "21"),
       ("Anil", "MAE", "45", "B", "20"),
       ("Megha", "ECE", "55", "A", "22"),
       ("Sita", "CSE", "95", "A", "19")]
cursorObject.executemany(sql,val)
dataBase.commit()
dataBase.close()

04.查询数据

--SELECT attr1, attr2 FROM table_name
SELECT AGE FROM student2

dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password",
  database = "gfg"
)
 
# preparing a cursor object
cursorObject = dataBase.cursor()

query = "SELECT NAME,AGE FROM STUDENT2"
cursorObject.execute(query)

result = cursorObject.fetchall()

for x in result:
  print(x)

# disconnecting from server
dataBase.close()

('Ram', 19)
('jack', 19)
('Nikhil', 18)
('Nisha', 18)
('Rohan', 20)
('Amit', 21)
('Anil', 20)
('Megha', 22)
('Sita', 19)

Where Clause

--SELECT column1, column2, …. columnN FROM [TABLE NAME] WHERE [CONDITION];
SELECT NAME,AGE FROM student2 WHERE AGE>=19

# 用python
  
dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password",
  database = "gfg"
)
 
# preparing a cursor object
cursorObject = dataBase.cursor()
  
query = "SELECT NAME,AGE FROM STUDENT where AGE >=19"
cursorObject.execute(query)
   
myresult = cursorObject.fetchall()
   
for x in myresult:
    print(x)
 
# disconnecting from server
dataBase.close()

('Ram', 19)
('Ram2', 19)
('Ram3', 19)
('jack', 19)
('Rohan', 20)
('Amit', 21)
('Anil', 20)
('Megha', 22)
('Sita', 19)

Order By Clause

• OrderBy用于以升序或降序排列结果
• DESC: 倒序
• ASC:升序,ASC可以省略

--用SQL语句
/**
SELECT column1, column2
FROM table_name
ORDER BY column_name ASC|DESC;
*/
SELECT NAME,AGE 
FROM student2
ORDER BY AGE DESC;

用python排序

dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password",
  database = "gfg"
)
 
# preparing a cursor object
cursorObject = dataBase.cursor()
  
query = "SELECT NAME,AGE FROM STUDENT ORDER BY AGE DESC"
cursorObject.execute(query)
   
myresult = cursorObject.fetchall()
   
for x in myresult:
    print(x)
 
# disconnecting from server
dataBase.close()

('Megha', 22)
('Amit', 21)
('Rohan', 20)
('Anil', 20)
('Ram', 19)
('Ram2', 19)
('Ram3', 19)
('jack', 19)
('Sita', 19)
('Nikhil', 18)
('Nisha', 18)

Limit Clause

• 用SQL LIMIT子句来限制SELECT语句返回的行数

SELECT 
    NAME,AGE
FROM
    student2
ORDER BY AGE
LIMIT 3 OFFSET 0; --把结果集进行分页，每页三条记录，要获取第一页的记录
--注意，索引从0开始

SELECT 
    NAME,AGE
FROM
    student2
ORDER BY AGE
LIMIT 3 OFFSET 3; 

--要获取第2页的记录,则要跳过前面3条记录，从3号记录开始查询，OFFSET设为3
--索引从3开始

用python使用LIMIT语句

dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password",
  database = "gfg"
)
 
# preparing a cursor object
cursorObject = dataBase.cursor()
  
query = "SELECT NAME,AGE FROM STUDENT2 LIMIT 3 OFFSET 0"
cursorObject.execute(query)
   
myresult = cursorObject.fetchall()
   
for x in myresult:
    print(x)
 
# disconnecting from server
dataBase.close()

('Ram', 19)
('jack', 19)
('Nikhil', 18)

05.更新数据

UPDATE

--基本语法：
--UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
UPDATE student2 set AGE = 20 WHERE NAME = 'jack';

--查询
SELECT * FROM student2 WHERE NAME = 'jack'

用python更新MySQl表格

import mysql.connector
  
dataBase = mysql.connector.connect(
  host ="localhost",
  user ="root",
  passwd ="password",
  database = "gfg"
)
 
# preparing a cursor object
cursorObject = dataBase.cursor()
  
query = "UPDATE STUDENT2 SET AGE = 23 WHERE Name ='Ram'"
cursorObject.execute(query)
dataBase.commit()
 
# disconnecting from server
dataBase.close()

DELETE

--sql语法：DELETE FROM TABLE_NAME WHERE ATTRIBUTE_NAME = ATTRIBUTE_VALUE
DELETE FROM student2 WHERE NAME = 'jack';

python操作的流程是一样的

Drop Tables

--DROP TABLE tablename;
--DROP TABLE IF EXISTS tablename;

DROP TABLE student

• 数据库中只剩下了两个表格