MySQL命令笔记+Python案例

2024-01-25 54

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，集群版 2核4GB 100GB

云数据库 RDS MySQL，高可用版 2核4GB 50GB

简介： 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         表示有符号，取值范围：-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+参数