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
- 数据库中只剩下了两个表格
