MySQL命令笔记+Python案例

MySQL数据库

1. 概要

• MySQL安装 & 配置
• MySQL的启动和关闭
• 指令
• Python第三方模块，发送指令并获取MySQL返回的结果。

2. 安装MySQL

MySQL，本质上就是一个软件。

• 8.x
• 5.x，在这里安装5.7.31版本

2.1 下载

https://downloads.mysql.com/archives/community/

2.2 安装

解压安装包放在自己知道的目录

2.3 创建配置文件

2.4 初始化

管理员终端cmd输入：

>>> "D:\mysql-5.7.31-winx64\bin\mysqld.exe" --initialize-insecure

安装完成后在mysql目录下会生成一个data目录

3. 启动MySQL

启动MySQL一般有两种方式：

• 临时启动（不建议）

回车后无保存即启动成功，关闭cmd窗口则关闭MySQL

>>> D:\mysql-5.7.31-winx64\bin\mysqld.exe

• 制作成Windows服务，服务来进行关闭和开启

  • 制作服务

    57为命名

  >>> "D:\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57
  

命令模式启动和关闭服务

>>> net start mysql57	// 开启服务
>>> net stop mysql57	// 关闭服务

图形化模式启动和关闭服务

4. 连接测试

发送MySQL指令分类：

• 代码类：Python/Java
• 工具类：Navicat Premium
• MySQL自带工具

MySQL自带工具（命令行）：

规范操作：-h 连接地址 、-P 连接端口、-u 用户名、-p 密码

>>> "D:\mysql-5.7.31-winx64\bin\mysql.exe" -h 127.0.0.1 -P 3306 -u root -p 

简化操作1：如果是在本机上进行连接可不用-h、-P 命令为：

>>> "D:\mysql-5.7.31-winx64\bin\mysql.exe" -u root -p

简化操作2：如果将D:\mysql-5.7.31-winx64\bin添加到环境变量，命令为：

>>> mysql -u root -p

4.1 设置密码

>>> set password = password("root");

4.2 查看已有的文件夹（数据库）

>>> show databases;

4.3 退出（关闭连接）

>>> exit;

4.4 再连接MySQL（输入密码）

5. 忘记密码（解决：重置密码）

Step1：关闭mysql服务

Step2：在my.ini中添加一行

skip-grant-tables=1

Step3：重新启动MySQL服务

Step4：此时进入MySQL命令行无需密码

Step5：设置新密码（执行两步命令）

>>> use mysql;
>>> update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';

Step6：在my.ini配置文件中删除 skip-grant-tables=1行

Step7：登录测试

6. MySQL指令

MySQL与平时认知比较

MySQL	认知
数据库	文件夹
数据表	文件

6.1 数据库管理（文件夹）

• 查看已有的数据库（文件夹）

show databases;

• 创建数据库（文件夹）

create database 数据库名字;

加编码 & 排序

create database 数据库名字 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

• 删除数据库（文件夹）

drop database 数据库名字;

• 进入数据库（文件夹）

use 数据库名字;

• 查看文件夹下所有的数据表（文件）

show tables;

6.2 数据表的管理（文件）

• 进入数据库（进入文件夹）

use 数据库;

• 查看当前数据库下的所有表（文件）

show tables;

• 创建表（文件）

create table tb1(
	id int,
	name varchar(16),
	age int
) default charset=utf8;

create table tb1(
	id int,
	name varchar(16) not null,	-- 不允许为空
	age int null				-- 允许为空（默认）
) default charset=utf8;

create table tb1(
	id int,
	name varchar(16),
	age int default 3		-- 插入数据时，age列的值默认为3
) default charset=utf8;

create table tb1(
	id int primary key,		-- 主键（不允许为空，不允许重复）
	name varchar(16),
	age int
) default charset=utf8;

create table tb1(
	id int auto_increament primary key,		-- 主键（不允许为空，不允许重复）、自递增
	name varchar(16),
	age int
) default charset=utf8;

• 删除表

drop table 表名称;

• 查看表属性

desc 表名称;

常用数据类型：

• tinyint

有符号，取值范围：-128 ~ 127 （有正有负）
无符号，取值范围：0 ~ 255 （只有正）

• int

int 				表示有符号，取值范围：-2147483648 ~ 2147483647
int unsigned		表示无符号，取值范围：0 ~ 4294967295

• bigint

有符号，取值范围：-9223372036854775808 ~ 9223372036854775807
无符号，取值范围：0 ~ 18446744073709551615

练习：

# 创建表
create table tb1(
	id bigint not null auto_increment primary key,		
	salary int,
	age tinyint
)default charset=utf8;

# 插入数据
insert into tb1(salary,age) values(10000,19);	# 单行插入
insert into tb1(salary,age) values(10000,19),(50000,20);	#批量插入

# 查看表中的数据
select * from tb1;

• float
• double
• decimal

准确的小数值，m是数字总个数（负号不算），d是小数点后个数。m最大值为65，d最大值为30。

例如：
create table tb2(
	id bigint not null auto_increment primary key,		
	salary decimal(8,2)
)default charset=utf8;

insert into tb2(salary) values(1.28);
insert into tb2(salary) values(122115.11);

• char，速度快

定长字符串。最多容纳255个字符。

char(11),固定用11个字符串进行存储，若没有11个字符，也会按照11个字符存储。

create table tb3(
	id bigint not null auto_increment primary key,		
	mobile char(11)
)default charset=utf8;

insert into tb3(mobile) values("aaa");

• varchar，节省空间

变长字符串。

varchar(11)，真实数据有多长就按照多长存储。

create table tb4(
	id bigint not null auto_increment primary key,		
	mobile varchar(11)
)default charset=utf8;

insert into tb3(mobile) values("aaa");

• text

text数据类型用于保存大字符串，最多可到65535个字符

一般情况下，用于文章、新闻等。

create table tb5(
	id bigint not null auto_increment primary key,		
	title varchar(128),
	content text
)default charset=utf8;

• mediumtext

范围：16777215

• longtext

范围：4GB

• datetime

YYYY-MM-DD HH:MM:SS

• date

YYYY-MM-DD

练习：

create table tb6(
	id bigint not null auto_increment primary key,		
	name varchar(64) not null,
    password char(64) not null,
    email varchar(64) not null,
    age tinyint,
    salary decimal(10,2),
	ctime datetime
)default charset=utf8;

insert into tb7(name,password,email,age,salary,ctime) values("刘备","123","xx@qq.com",50,1000.11,"2023-1-1 11:11:10");

6.3 数据行操作

1. 新增数据

insert into 表名(列名,列名) values(值,值);
insert into 表名(列名,列名) values(值,值),(值,值),(值,值);

2. 删除数据

delete from 表名;
delete from 表名 where 条件;

delete from tb6
delete from tb6 where id = 3;
delete from tb6 where id = 4 and name="刘备";
delete from tb6 where id > 4;

3. 修改数据

update 表名 set 列=值;
update 表名 set 列=值,列=值;
update 表名 set 列=值 where 条件;

update tb6 set password="aaa";
update tb6 set email="aaa" where id > 5;

4. 查询数据

select * from 表名称;
select * 列名称,列名称 from 表名称;

select 列名称,列名称 from 表名称 where 条件;

select * from tb6;
select id,name from tb6;
select id,name from tb6 where id > 10;
select id,name from tb6 where name="xx" and password="xx";

7.案例：员工管理

7.1 创建表结构

• 使用MySQL内置工具（命令）

  • 创建数据库：unicom
  • 数据一张表：admin

  表名：admin
  列：
  id，整形，自增，主键。
  username 字符串 不为空
  password 字符串 不为空
  mobile 字符串 不为空
  

• Python代码实现：

  • 添加用户
  • 删除用户
  • 查看用户
  • 更新用户信息

# 创建数据库
create database unicom;

# 创建表
create table admin(
 id bigint not null auto_increment primary key,
 username char(30) not null,
 password char(30) not null,
 mobile char(30) not null
 )default charset=utf8;

7.2 Python操作MySQL

1. 写入数据

使用Python写入数据

import pymysql

# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='abc123.', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
cursor.execute("insert into admin(username,password,mobile) values('liubei','123456','12345678900')")
conn.commit()

# 3.关闭连接
cursor.close()
conn.close()

占位符写法：

import pymysql

# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='abc123.', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
cursor.execute(sql, ["zhangfei", "456789", "12222222222"])
conn.commit()

# 3.关闭连接
cursor.close()
conn.close()

动态写入数据：

import pymysql

while True:
    user = input('请输入用户名：')
    if user.upper() == 'Q':         # upper小写字母转换为大写
        break
    pwd = input('请输入密码：')
    mobile = input('手机号：')

    # 1.连接MySQL
    conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='abc123.', charset='utf8', db='unicom')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 2.发送指令
    sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
    cursor.execute(sql, [user, pwd, mobile])
    conn.commit()

    # 3.关闭连接
    cursor.close()
    conn.close()

2. 查询数据

import pymysql

# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='abc123.', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.执行查询的指令

cursor.execute("select * from admin where id > %s", [1])
data_list = cursor.fetchall()       # fetchall获取数据，fetchone获取符合条件的第一条数据
# 循环按照每行显示
for row_dict in data_list:
    print(row_dict)

# 3.关闭连接
cursor.close()
conn.close()

3. 删除数据

import pymysql

# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='abc123.', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.执行删除的指令

cursor.execute("delete from admin where id=%s", [3])
conn.commit()

# 3.关闭连接
cursor.close()
conn.close()

4. 修改数据

import pymysql

# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd='abc123.', charset='utf8', db='unicom')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.执行删除的指令

cursor.execute("update admin set password=%s where id=%s", ["15555555555", 2])
conn.commit()

# 3.关闭连接
cursor.close()
conn.close()

注意：

• 在进行 新增、删除、修改时，一定要记得commit，不然没有数据。
• 在查询时，不需要commit，但需要使用fetchall、fetchone获取数据
• 不用用Python的字符串格式化进行拼接（会被SQL注入），一定要用execute+参数