2. mysql
1. 安装MySQL(5.7.31)
MySQL本质就是一个软件
MySQL :: Download MySQL ***munity Server (Archived Versions)
创建配置文件
在目录中创建一个my.ini配置文件
[mysqld]
# port
# set basedir to your installation path
basedir=D:\\Code\\mysql-5.7.31-winx64
# ser datadir to the location of your data directory
datadir=D:\\Code\\mysql-5.7.31-winx64\\data
初始化
以管理员身份在cmd运行D:\Code\mysql-5.7.31-winx64\bin\mysqld.exe" --initialize-insecure
2. MySQL启动
-
临时启动(不建议)
"D:\Code\mysql-5.7.31-winx64\bin\mysqld.exe"
临时启动的话这个黑框不能关掉,关掉就关闭了
-
制作成Windows服务,服务来进行关闭和开启
-
制作服务
"D:\Code\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57
-
启动服务
-
3. 连接测试
MySQL有很多连接方式,这里我们先试一下MySQL自带的工具连接
>>> "D:\Code\mysql-5.7.31-winx64\bin\mysql.exe" -h 127.0.0.1 -P 3306 -u root -p
>>> "D:\Code\mysql-5.7.31-winx64\bin\mysql.exe" -u root -p
为方便以后使用,我们将 D:\Code\mysql-5.7.31-winx64\bin 添加到环境变量
4. 命令
mysql -u root -p #进入mysql
set password = password("123456"); #设置密码
show databases; #查看所有文件夹
exit; #退出
5. 忘记密码
-
停止现在的mysql服务
-
修改配置文件以无账号模式登录
在my.ini文件中 + skip-grant-tables=1
-
再次登录,无需密码
执行命令设置密码
use mysql;
update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';
- 重新修改配置文件为有账号登陆模式
- 登陆时输入新的密码即可
6. MySQL指令
MySQL | 认知 |
---|---|
数据库 | 文件夹 |
数据表 | 文件(Excel) |
6.1 数据库管理
-
查看已有的数据库
show databases;
-
创建数据库
create database 数据库名字 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-
删除数据库
drop database 数据库名字;
-
进入数据库
use 数据库名字;
-
查看数据库下所有数据表
show tables;
6.2 数据表管理
-
创建表
create table 表名( id int primary key, -- 主键(不允许空,不允许重复) name varchar(16) not null, -- 不允许为空 age int null, -- 允许为空(默认) height int default 3 -- 默认值为3 )default charset=utf8;
-
create table 表名( id int auto_increment primary key, -- 内部维护 自增 name varchar(16) not null, age int null, height int default 3 )default charset=utf8;
-
一般我们都这么写:
create table table1( id int not null auto_increment primary key, -- 内部维护 自增 name varchar(16) not null, age int null, height int default 3 ) default charset=utf8;
desc 表名; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(16) | NO | | NULL | | | age | int(11) | YES | | NULL | | | height | int(11) | YES | | 3 | | +--------+-------------+------+-----+---------+----------------+
-
删除表
drop table 表名;
常用数据类型:
-
tinyint
有符号,取值范围:-128 ~ 127 【默认】 无符号,取值范围:0 ~ 255 -- 加上unsigned
-
int
int 有符号,取值范围:-2147483648 ~ 2147483647 int unsigned 有符号,取值范围:0 ~ 4294967659
-
bigint
特别大
练习:
# 建表
create table tb2(
id bigint not null auto_increment primary key,
salary int,
age tinyint unsigned
) default charset=utf8;
# 插入数据
insert into tb2(salary, age) values(10000, 18);
insert into tb2(salary, age) values(20000, 28), (30000, 38);
# 查看表中数据
select * from tb2;
-
float
-
double
-
decimal
准确的小数值,m是数字的总个数(负号不算),d是小数点后个数。m最大值为65,d最大值为30. 例如: create table tb3( id int not null primary key auto_increment, salary decimal(8,2) ) default charset=utf8; insert into tb3(salary) values(2.222); 小数点后超过两位了,会进行四舍五入
-
char,速度快
定长字符串,最大为255 char(11),固定11个字符存储,哪怕没有11个字符,也会按11个存储
-
varchar,节省空间
变长字符串,最大65535个字节 / 3 = 最大m varchar(最大长度) 真实多长就按多长存储
-
text
用于保存变长的大字符串,可以组到(2**16 - 1)个字符
-
mediumtext
-
longtext
-
datetime
YYYY-MM-DD HH:MM:SS
-
date
YYYY-MM-DD
create table tb4(
id int not null primary key auto_increment,
name varchar(64) not null,
email varchar(64) not null,
password char(64) not null,
age tinyint,
salary decimal(10,2),
ctime datetime
) default charset=utf8;
insert into tb4(name, email, password, age, salary, ctime) values("高宇轩", "123@outlook.***", "123", 19, 10000.12, "2004-07-26 06:16:16");
insert into tb4(name, email, password, age, salary, ctime) values("叶鹏辉", "123@outlook.***", "123", 19, 10000.12, "2004-07-26 06:16:16");
insert into tb4(name, email, password, age, salary, ctime) values("田伟胜", "123@outlook.***", "123", 19, 10000.12, "2004-07-26 06:16:16");
insert into tb4(name, email, password, age, salary, ctime) values("陈潇鹏", "123@outlook.***", "123", 19, 10000.12, "2004-07-26 06:16:16");
insert into tb4(name, email, password, age, salary, ctime) values("陈琦煜", "123@outlook.***", "123", 19, 10000.12, "2004-07-26 06:16:16");
insert into tb4(name, email, password, age, salary, ctime) values("林博亦", "123@outlook.***", "123", 19, 10000.12, "2004-07-26 06:16:16");
6.3 数据行操作
1. 新增数据
insert into 表名(列名,列名) values(值,值), (值,值);
2. 删除数据
delete from 表名;
delete from 表名 where 条件;
delete from tb4 where id = 1;
delete from tb4 where id = 1 and name = "高宇轩";
delete from tb4 where id = 1 or name = "叶鹏辉";
delete from tb4 where id >=4;
delete from tb4 where id != 4;
delete from tb4 where id in (1, 5, 3);
3. 修改数据
update 表名 set 列=值 列=值;
update 表名 set 列=值 where 条件;
update tb4 set age = age + 4 where id = 3;
4. 查询数据
select * from 表名; #查询表中所有数据
select 列名,列名 from 表名;
select 列名,列名 from 表名 where 条件;
7. 案例:员工管理
-
使用MySQL内置工具
-
创建数据库:uni***
-
数据一张表:admin
表名:admin 列: id,整型,自增,主键 username 字符串,不为空 password 字符串,不为空 mobile 字符串 不为空
-
-
Python代码实现
- 添加用户
- 删除用户
- 查看用户
- 更新用户信息
7.1 创建数据表
create database uni*** DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
use uni***;
create table admin(
id int not null primary key auto_increment,
username varchar(64) not null,
password char(64) not null,
mobile char(11) not null
) default charset=utf8;
7.2 Python操作MySQL
pip install pymysql
import pymysql
# 1.连接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', password='123', charset='utf8', database='uni***')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2.发送指令(千万不要用字符串格式化去做SQL的拼接,安全隐患SQL注入)
# sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
# cursor.execute(sql, ['高宇轩', '456', '19999'])
sql = "insert into admin(username,password,mobile) values(%(n1)s,%(n2)s,%(n3)s)"
cursor.execute(sql, {"n1": "gyx", "n2": "789", "n3": "188888"})
conn.***mit()
# 3.关闭连接
cursor.close()
conn.close()
动态
import pymysql
while True:
user = input("用户名:")
if user.upper() == "QUIT":
break
pwd = input("密码:")
mobile = input("手机号码:")
# 1.连接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', password='123', charset='utf8', database='uni***')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2.发送指令(千万不要用字符串格式化去做SQL的拼接,安全隐患SQL注入)
# sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
# cursor.execute(sql, ['高宇轩', '456', '19999'])
sql = "insert into admin(username,password,mobile) values(%s, %s, %s)"
cursor.execute(sql, [user, pwd, mobile])
conn.***mit()
# 3.关闭连接
cursor.close()
conn.close()
获取数据
import pymysql
# 1.连接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', password='123', charset='utf8', database='uni***')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2.发送指令
cursor.execute("select * from admin where id > %s", [2, ])
result = cursor.fetchall()
for row in result:
print(row)
# 3.关闭连接
cursor.close()
conn.close()
删除数据
import pymysql
# 1.连接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', password='123', charset='utf8', database='uni***')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2.发送指令
sql = "delete from admin where id = %s"
cursor.execute(sql, [2, ])
conn.***mit()
# 3.关闭连接
cursor.close()
conn.close()
修改数据
import pymysql
# 1.连接mysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', password='123', charset='utf8', database='uni***')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2.发送指令
sql = "update admin set mobile = %s where mobile = %s"
cursor.execute(sql, [18888888, 199999999])
conn.***mit()
# 3.关闭连接
cursor.close()
conn.close()
强调:
- 在进行增删改时,一定要记得***mit,不然数据库没有数据
cursor.execute("...")
conn.***mit()
- 在查询时不需要***mit,但是需要执行fetchall/fetchone
- 对于SQL语句不要用Python的字符串格式化进行拼接(会被SQL注入),一定要用execute+参数
8. Flask + MySQL案例
from flask import Flask, render_template, request
import pymysql
app = Flask(__name__)
@app.route("/add/user", methods=['POST', 'GET'])
def add_user():
if request.method == "GET":
return render_template("add_user.html")
# 获取数据
username = request.form.get("user")
pwd = request.form.get("pwd")
mobile = request.form.get("mobile")
# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="123", database="website", charset="utf8")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2.执行MySQL
sql = "insert into admin(username,pwd,mobile) values(%s, %s, %s)"
cursor.execute(sql, (username, pwd, mobile))
conn.***mit()
# 3.关闭连接
cursor.close()
conn.close()
return "添加成功"
@app.route("/show/user", methods=['GET', 'POST'])
def show_user():
# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="123", database="website", charset="utf8")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2.执行MySQL
sql = "select * from admin"
cursor.execute(sql)
data_list = cursor.fetchall()
# 3.关闭连接
cursor.close()
conn.close()
print(data_list)
return render_template("show_user.html", data_list=data_list)
if __name__ == "__main__":
app.run(debug=True)