w3school学习笔记2(MySQL)

系列文章目录

文章目录

系列文章目录
前言
一、MySQL入门
- 1.安装MySQL
- 2.测试MySQL Connector是否安装成功
- 3.创建连接
二、Python创建数据库
- 1.创建名为mydatabase的数据库
- 2.检查数据库是否存在
- 3.在建立连接时尝试访问数据库
三、Python创建表
- 1.创建表customers
- 2.检查表customers是否存在
- 3.主键
四、Python Insert Into Table
- 1.插入表，在customers中插入记录
- 2.插入多行
- 3.获取已插入ID
五、Python Select From
- 1.从表中选取
- 2.选取列
- 3.使用fetchone()方法
六、Python MySQL Where
- 1.使用筛选器来选取
- 2.通配符
- 3.防止SQL注入
七、Python MySQL Order By
- 1.结果排序
- 2.降序排序
八、Python MySQL Delete From
- 1.删除记录
- 2.防止SQL注入
九、Python MySQL Drop Table
- 1.删除表
- 2.只在表存在时删除表
十、Python 更新表
- 1.更新表
- 2.防止SQL注入
十一、Python MySQL Limit
- 1.限定结果
- 2.从另一个位置开始
十二、Python MySQL Join
- 1.组合两张或更多表
- 2.Left Join
总结

前言一、MySQL入门 1.安装MySQL

MySQL安装
Python 需要MySQL驱动程序来访问MySQL数据库；

pip install mysql-connector

2.测试MySQL Connector是否安装成功

新建一个文件sqltest.py，输入：

import mysql.connector

运行后无报错，安装成功！！
提示：新建的文件名不能为mysql.py，否则运行会出错！！
报错链接

3.创建连接

使用mysql数据库中的用户名和密码，查看数据库的用户名：
1、mysql -u root -p登录；
2、查看host和user

 select hosr,user from mysql.user;

新建文件demomysql.py

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****"
)

print(mydb)

Result：

二、Python创建数据库 1.创建名为mydatabase的数据库

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

2.检查数据库是否存在

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")

for x in mycursor:
  print(x)

Result：

('information_schema',)
('mydatabase',)
('mysql',)
('performance_schema',)
('sys',)
('test',)

3.在建立连接时尝试访问数据库

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

如果不存在则会显示错误：
例如：

三、Python创建表 1.创建表customers

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (name VARCHAr(255), address VARCHAr(255))")

执行未出现错误，说明我们已经成功创建了一个表。

2.检查表customers是否存在

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
  print(x)

Result：

('customers',)

3.主键

创建表时，我们还应该为每一条记录创建一个具有唯一键的列，我们可以通过定义PRIMARY KEY来完成。我们使用语句"INT AUTO_INCREMENT PRIMARY KEY"，它将为每条记录插入唯一的编号，从0开始递增1；

创建表时同步创建主键：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAr(255), address VARCHAr(255))")

如果表已经存在，请使用ALTER TABLE关键字：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

四、Python Insert Into Table 1.插入表，在customers中插入记录

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

提示：注意语句mydb.commit()需要进行更改，否则表不会有任何改变。

2.插入多行

要在表中插入多行，请使用executemany()方法，executemany()方法的第二个参数是元组列表，包含要插入的数据；
用数据填充customers表：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted.")

3.获取已插入ID

我们可以通过询问cursor对象来获取刚插入的行的id。
注释：如果插入不止一行，则返回最后插入的行的id。
插入一行，并返回id:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Michelle", "Blue Village")
mycursor.execute(sql, val)

mydb.commit()

print("1 record inserted, ID:", mycursor.lastrowid)

五、Python Select From 1.从表中选取

从表customers中选取所有记录，并显示结果，可以使用select语句

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

('John', 'Highway 21', 1)
('Peter', 'Lowstreet 4', 2)
('Amy', 'Apple st 652', 3)
('Hannah', 'Mountain 21', 4)
('Michael', 'Valley 345', 5)
('Sandy', 'Ocean blvd 2', 6)
('Betty', 'Green Grass 1', 7)
('Richard', 'Sky st 331', 8)
('Susan', 'One way 98', 9)
('Vicky', 'Yellow Garden 2', 10)
('Ben', 'Park Lane 38', 11)
('William', 'Central st 954', 12)
('Chuck', 'Main Road 989', 13)
('Viola', 'Sideway 1633', 14)
('Michelle', 'Blue Village', 15)

注释：我们用了fetchall()方法，该方法从最后执行的语句中获取所有行。

2.选取列

如需只选择表中的某些列，我们可以使用select语句，后跟列名。
仅选取名称和地址列：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("SELECT name, address FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

('John', 'Highway 21')
('Peter', 'Lowstreet 4')
('Amy', 'Apple st 652')
('Hannah', 'Mountain 21')
('Michael', 'Valley 345')
('Sandy', 'Ocean blvd 2')
('Betty', 'Green Grass 1')
('Richard', 'Sky st 331')
('Susan', 'One way 98')
('Vicky', 'Yellow Garden 2')
('Ben', 'Park Lane 38')
('William', 'Central st 954')
('Chuck', 'Main Road 989')
('Viola', 'Sideway 1633')
('Michelle', 'Blue Village')

3.使用fetchone()方法

如果我们只返回一行，可以使用fetchone()方法。
仅获取一行：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECt * FROM customers")

myresult = mycursor.fetchone()

print(myresult)

Result：

('John', 'Highway 21', 1)

六、Python MySQL Where 1.使用筛选器来选取

从表中选择记录时，我们可以使用Where语句进行选择删选：
选择记录为"Park Lane 38"的记录

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()
sql = "SELECt * FROM customers WHERe address ='Park Lane 38'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

('Ben', 'Park Lane 38', 11)

2.通配符

我们可以选择以给定字母或短语开头，包含或结束的记录。使用%表示通配符；
选择地址中包含单词way的记录

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()
sql = "SELECt * FROM customers WHERe address LIKE '%way%'"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

('John', 'Highway 21', 1)
('Susan', 'One way 98', 9)
('Viola', 'Sideway 1633', 14)

3.防止SQL注入

当用户提供查询值时，我们应该转义这些值，为了防止SQL注入，这是一种常见的网络黑客技术，可以破坏或滥用我们的数据库。
mysql.connector模块拥有转义查询值的方法；
使用占位符%s方法来转义查询值：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()

sql = "SELECt * FROM customers WHERe address = %s"
adr = ("Yellow Garden 2", )

mycursor.execute(sql, adr)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

('Vicky', 'Yellow Garden 2', 10)

七、Python MySQL Order By 1.结果排序

我们可以使用order by语句按升序或降序对结果进行排序。order by 关键字按升序对结果进行排序，若要按降序进行排序，可以使用desc关键字。
以字符顺序对姓名进行排序：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECt * FROM customers ORDER BY name"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

('Amy', 'Apple st 652', 3)
('Ben', 'Park Lane 38', 11)
('Betty', 'Green Grass 1', 7)
('Chuck', 'Main Road 989', 13)
('Hannah', 'Mountain 21', 4)
('John', 'Highway 21', 1)
('Michael', 'Valley 345', 5)
('Michelle', 'Blue Village', 15)
('Peter', 'Lowstreet 4', 2)
('Richard', 'Sky st 331', 8)
('Sandy', 'Ocean blvd 2', 6)
('Susan', 'One way 98', 9)
('Vicky', 'Yellow Garden 2', 10)
('Viola', 'Sideway 1633', 14)
('William', 'Central st 954', 12)

2.降序排序

我们可以使用desc关键字按降序对结果进行排序
按反转字母顺序对姓名的结果进行排序：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECt * FROM customers ORDER BY name DESC"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

('William', 'Central st 954', 12)
('Viola', 'Sideway 1633', 14)
('Vicky', 'Yellow Garden 2', 10)
('Susan', 'One way 98', 9)
('Sandy', 'Ocean blvd 2', 6)
('Richard', 'Sky st 331', 8)
('Peter', 'Lowstreet 4', 2)
('Michelle', 'Blue Village', 15)
('Michael', 'Valley 345', 5)
('John', 'Highway 21', 1)
('Hannah', 'Mountain 21', 4)
('Chuck', 'Main Road 989', 13)
('Betty', 'Green Grass 1', 7)
('Ben', 'Park Lane 38', 11)
('Amy', 'Apple st 652', 3)

八、Python MySQL Delete From 1.删除记录

我们可以使用delete from语句从已有的表中删除记录；
删除地址为mountain 21 的任何记录：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETe FROM customers WHERe address = 'Mountain 21'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

Result：

1 record(s) deleted

重要：请注意语句mydb.commit()。需要进行更改，否则表不会有任何更改。请注意delete语法中的where子句，where子句指定应删除哪些记录，如果省略where子句，则将删除所有记录。

2.防止SQL注入

在delete语句中，转义任何查询值也是一种好习惯，是为了房子SQL注入，这是一种常见的网络黑客技术，可以破坏或滥用我们的数据库。mysql.connector模块使用占位符%s来转义delete语句中的值。
使用占位符%s方法来转义值：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERe address = %s"
adr = ("Yellow Garden 2", )

mycursor.execute(sql, adr)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

Result：

1 record(s) deleted

九、Python MySQL Drop Table 1.删除表

我们可以使用Drop Table语句来删除已有的表
删除customers表

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE customers"
mycursor.execute(sql)

2.只在表存在时删除表

如果删除的表已被删除，或者由于其他原因不存在，我们可以使用IF EXISTS关键字避免出错。
删除表customers

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS customers"
mycursor.execute(sql)

十、Python 更新表 1.更新表

我们可以使用update语句来更新表中的现有记录；
把地址中的valley 345覆盖为Canyoun 123

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERe address = 'Valley 345'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

Result：

1 record(s) affected

重要：请注意语句mydb.commit。需要进行更改，否则表不会有任何改变。请注意update语法中的where子句，where子句指定应更新的记录，如果省略where子句，则所有的记录都将更新；

2.防止SQL注入

在update语句中，转义任何查询的值都是个好习惯。此举是为了防止SQL注入，这是一种常见的网络黑客技术，可以破坏或滥用我们的数据库。mysql.connector 模块使用占位符%s来转义delete语句中的值
使用占位符%s方法来转义值

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s WHERe address = %s"
val = ("Valley 345", "Canyon 123")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

十一、Python MySQL Limit 1.限定结果

我们可以使用Limit语句限制从查询返回的记录数。
选取customers表中的前五条记录:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

(1, 'Peter', 'Lowstreet 4')
(2, 'Amy', 'Apple st 652')
(3, 'Hannah', 'Mountain 21')
(4, 'Michael', 'Valley 345')
(5, 'Sandy', 'Ocean blvd 2')

2.从另一个位置开始

我们需要从第三条记录开始返回5条记录，我们可以使用offset关键字
从位置3开始返回5条记录：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)
mycursor = mydb.cursor()
mycursor.execute("SELECt * FROM customers LIMIT 5 offset 2")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Result：

(3, 'Hannah', 'Mountain 21')
(4, 'Michael', 'Valley 345')
(5, 'Sandy', 'Ocean blvd 2')
(6, 'Betty', 'Green Grass 1')
(7, 'Richard', 'Sky st 331')

十二、Python MySQL Join 1.组合两张或更多表

我们可以使用Join语句，根据它们之间的相关列组合两个或多个表中的行。
如果我们有uses表和products表：
uses

{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

products

{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

可以使用uses的fav字段和products的id字段来组合这两个表。
组合用户和产品，查看用户最喜欢的产品名称：

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="mmjsmymm*****",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECt 
  users.name AS user, 
  products.name AS favorite 
  FROM users 
  INNER JOIN products ON users.fav = products.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

注释：我们可以使用Join而不是Inner Join我们会得到相同的结果。

2.Left Join

在上例中，Hannah和Michael被排除在结果之外，这是因为Inner Join只显示匹配的记录，如果希望显示所有用户，即使他们没有喜欢的产品，我们可以使用Left Join语句
选择所有用户及喜欢的产品：

sql = "SELECt 
  users.name AS user, 
  products.name AS favorite 
  FROM users 
  LEFT JOIN products ON users.fav = products.id"

如果我们想要返回所有产品以及喜欢它们的用户，即使没有用户喜欢它们，可以使用Right Join语句
选择所有产品及喜欢它们的用户：

sql = "SELECt 
  users.name AS user, 
  products.name AS favorite 
  FROM users 
  RIGHT JOIN products ON users.fav = products.id"

总结

分享：
跟着阳光走，无论在繁华喧嚷的都市，还是在炊烟袅袅的乡村，无论在瑞雪飘飘的冬日，还是在姹紫嫣红的春天，你都会欣然遇见许多美好，会欢喜地发现，草木有情，山水有意，寻常的日子里也有迷人的风景。

w3school学习笔记2(MySQL)

Python相关栏目本月热门文章