Skip to main content

MySQL

mySQL資料庫​

  • mySQL資料庫系統是關聯是資料庫管理系統
  • 效能高,成本低,可靠性高
  • 到mysqlclient下載並安裝
  • 打開Ananconda -> 環境 -> 選擇虛擬環境 -> 綠色箭頭 -> Terminal
pip install mysqlclient
  • 新建資料庫
import MySQLdb
db = MySQLdb.connect(host="localhost",user="root",passwd="123456")  #連線mysql
cursor = db1.cursor()
cursor.execute("DROP DATABASE IF EXISTS mydata")                    #如果有的話刪除
sql = "CREATE DATABASE mydata"                                      #建立全新資料庫
cursor.execute(sql)
db.close()

新建資料表​

import MySQLdb
db = MySQLdb.connect(host="localhost",user="root",passwd="123456")
cursor = db.cursor()
sql = "CREATE TABLE employee(fname char(20) not null, lname char(20), age  int)"    #建立資料表
cursor.execute(sql)
db.close()

Insert新增資料​

import MySQLdb
db = MySQLdb.connect(host="localhost",user="root",passwd="123456", db="mydata", charset='utf-8')
cursor = db.cursor()
cursor.execute("INSERT INTO employee(fname, lname, age) VALUES('mary','sawyer',20)")
db.commit()
db.close()

Query查詢資料​

import MySQLdb
db = MySQLdb.connect(host="localhost",user="root",passwd="123456", db="mydata", charset='utf-8')
cursor = db.cursor()
cursor.execute("SELECT * FROM employee")
result = cursor.fetchall()
for row in results:
    fname = row[0]
    lname = row[1]
    age = row[2]
    print("Employee: {} {}, age={}".format(fname,lname,age))
db.close()

Update更新資料​

import MySQLdb
db = MySQLdb.connect(host="localhost",user="root",passwd="123456", db="mydata", charset='utf-8')
cursor = db.cursor()
cursor.execute("UPDATE employee SET age=25 WHERE fname='mary'")
db.commit()
db.close()

Delete刪除資料​

import MySQLdb
db = MySQLdb.connect(host="localhost",user="root",passwd="123456", db="mydata", charset='utf-8')
cursor = db.cursor()
cursor.execute("DELETE FROM employee WHERE fname='mary'")
db.commit()
db.close()