#coding:utf-8
import mysql.connector
#D:\chrome download\mysql-5.7.29-winx64\mysql-5.7.29-winx64\data
#打开这个data文件夹,找到一个类型为err的文件,用记事本打开,里面有句A temporary password is generated for root@localhost:这个后面记录的就是你的初始密码
#在cmd中启动MySQL并将密码修改为123456
mydb = mysql.connector.connect(
host='localhost',
user='root',
passwd='123456',
database='mydatabase',
buffered = True
)
print(mydb)
#1、创建数据库
mycursor = mydb.cursor()
# mycursor.execute("CREATE DATABASE mydatabase") #已创建的数据库再创建会报错
# mycursor.execute("CREATE DATABASE yangdatabase")
#/*tableName:为要查询的表,selSql为查询语句,val为要查询的where条件中的值***/
def selectFn(mycursor,tableName,selSql,val):
if selSql=="":
selSql="select * from "+tableName
mycursor.execute(str(selSql))
elif val!="":
mycursor.execute(str(selSql),val)
elif val=="":
mycursor.execute(str(selSql))
myRes = mycursor.fetchall()
print(tableName," 表中数据有:")
for x in myRes:
print(x)
#/*tableName:为要插入值的表名,colStr为表中的字段,valList为要插入的值***/
def insertFn(mycursor,tableName,colStr,valList):
#插入的SQL语句:insert into customers(name,address) values(%s,%s)
splitStr = colStr.split(",")
valStr=""
#如下for循环是生成(%s,%s)
for x in range(len(splitStr)):
valStr+="%s"
if (x == (len(splitStr)-1)):
break
valStr += ","
insertSql = "insert into " + tableName + "(" + colStr + ")" +" values(" + valStr + ")"
mycursor.executemany(str(insertSql),valList)
mydb.commit()
print(mycursor.rowcount, " record inserted")
# print("1 record inserted,ID:", mycursor.lastrowid)
# select出重复数据并删除
def deleteSameData(delSql,tableName):
mycursor.execute(delSql)
mycursor.execute(str("select * from " +tableName))
myresult1 = mycursor.fetchall()
for x in myresult1:
print(x)
#1、检查数据库是否创建好
print("1、检查数据库是否创建好")
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
#2、创建表
mycursor.execute("drop table if exists users")
mycursor.execute("drop table if exists products")
createSql="create table if not exists users(name varchar(255),fav int)"
createSql2="create table if not exists products(id int,name varchar(255))"
valUsers = [
('John', 154),
('Peter', 154),
('Amy', 155),
('Hannah', 0),
('Michael', 0)
]
valProducts=[
(154, 'Chocolate Heaven'),
(155, 'Tasty Lemons'),
(156, 'Vanilla Dreams'),
(159, 'yang Cake')
]
mycursor.execute(createSql)
mycursor.execute(createSql2)
mydb.commit()
#3、开始对表插入数据
print("3、开始对表插入数据")
insertFn(mycursor,'users','name,fav',valUsers)
insertFn(mycursor,'products','id,name',valProducts)
mycursor.execute("alter table users add column uid int auto_increment primary key")
#4、查询表中的数据
print("4、查询表中的数据")
selectFn(mycursor,'users','','')
selectFn(mycursor,'products','','')
sql2 = "SELECT * FROM customers WHERE address = %s"
adr=("Yellow Garden 2", )
selectFn(mycursor,'customers',sql2,adr)
#5、练习join相关sql语句
print("5、练习join相关sql语句:")
joinSql ="select users.name as user,products.name as favor from users inner join products on users.fav = products.id"
# mycursor.execute(selectSql)
# # selectRes=mycursor.fetchall()
# # for x in selectRes:
# # print(x)
selectFn(mycursor,'users',joinSql,'')
leftJoinSql ="select users.name as user,products.name as favor from users left join products on users.fav = products.id"
rightJoinSql ="select users.name as user,products.name as favor from users right join products on users.fav = products.id"
selectFn(mycursor,'users',leftJoinSql,'')
selectFn(mycursor,'users',rightJoinSql,'')
join或inner join (例子:组合用户和产品,查看用户最喜欢的产品名称)
组合两张或更多表
您可以使用 JOIN 语句,根据它们之间的相关列组合两个或多个表中的行
LEFT JOIN(例子:选择所有用户及其喜爱的产品)
在上例中,Hannah 和 Michael 被排除在结果之外,这是因为 INNER JOIN 只显示匹配的记录。
如果希望显示所有用户,即使他们没有喜欢的产品,请使用 LEFT JOIN 语句;
RIGHT JOIN(例子:选择所有产品以及喜欢它们的用户)
如果您想要返回所有产品以及喜欢它们的用户,即使没有用户喜欢这些产品,请使用 RIGHT JOIN 语句:
执行结果:
1、检查数据库是否创建好
('mydatabase',)
('mysql',)
('yangdatabase',)
3、开始对表插入数据
5 record inserted
4 record inserted
4、查询表中的数据
users 表中数据有:
('John', 154, 1)
('Peter', 154, 2)
('Amy', 155, 3)
('Hannah', 0, 4)
('Michael', 0, 5)
products 表中数据有:
(154, 'Chocolate Heaven')
(155, 'Tasty Lemons')
(156, 'Vanilla Dreams')
(159, 'yang Cake')
customers 表中数据有:
('Vicky', 'Yellow Garden 2', 11)
5、练习join相关sql语句:
组合用户和产品,查看用户最喜欢的产品名称:
('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemons')
选择所有用户及其喜爱的产品:
('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemons')
('Hannah', None)
('Michael', None)
选择所有产品以及喜欢它们的用户:
('John', 'Chocolate Heaven')
('Peter', 'Chocolate Heaven')
('Amy', 'Tasty Lemons')
(None, 'Vanilla Dreams')
(None, 'yang Cake')