MySQL数据库(1)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL数据库(1)

数据库管理系统(DBMS,Database Management System)的软件,可以帮助我们实现对文件夹中的文件进行操作,而我们只要学习DBMS能识别的指令, 就能控制它去帮助我们实现的文件和文件夹的处理。例如:

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

数据库管理系统(DBMS)专注于帮助开发者解决数据存储的问题,这样开发者就可以把主要精力放在实现业务功能上了。


业内有很多的的数据库管理系统产品,例如:


MySQL,原来是sun公司,后来被甲骨文收购。现在互联网企业几乎都在使用。【免费 + 收费】


Oracle,甲骨文。收费,一般国企、事业单位居多。【收费】


Microsoft SQL Server,微软。【收费】


DB2,IBM。【免费 + 收费】


SQLite,D. Richard Hipp个人开发。【免费】


Access, 微软。【收费】


PostgreSQL,加州大学伯克利分校。【免费】


等众多..


由于各大公司都是使用MySQL,所以我们课程主要给大家讲解MySQL数据库。


在项目开发中想要基于MySQL来进行数据存储,大致应该怎么做呢?

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

数据库管理

内置客户端操作

安装配置完成后后,进入mysql。

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

常用基本代码:


show databases;        查看当前数据库

create database xx ;        创建一个名为xx的数据库(默认utf-8编码)

drop database xx;        删除名为xx的数据库

use db1; -> show tables;        查看该数据库下的所有数据表

desc 表名;          查看数据表信息(如有什么列,默认值多少,是否可以为空)

select * from 表;      查看表里的内容

exit;        退出

Python代码操作

想要使用Python操作MySQL需要安装第三方模块:

import pymysql
# 1. 连接MySQL(底层就是用socket链接的,utf8不是utf-8)
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8')
cursor = conn.cursor() #创建一个游标 初学要注意哪里用conn.commit() 哪里用cursor.fetchone()
# 2. 创建数据库(新增、删除、修改)
# 发送指令
cursor.execute('show databases')
# 获取返回的结果(接收要加cursor.fetchall()等才能收到)
result = cursor.fetchall()
print(result)
#(('bookmanager',), ('information_schema',), ('mysql',), ('new_store',), ('performance_schema',)...)
#result = cursor.fetchone()     返回:('bookmanager',)
# 2. 创建数据库(新增、删除、修改要加conn.commit()才能执行)
# 发送指令(后面默认的编码排序等可以不写)
cursor.execute("create database adb3 default charset utf8 collate utf8_general_ci")
conn.commit()
# 4. 删除数据库
cursor.execute('drop database adb3')
conn.commit()
# 5. 进入数据库,查看表
cursor.execute('use bookmanager')
cursor.execute('show tables')
result1 = cursor.fetchall()
print(result1)
#(('app01_author',), ('app01_author_books',), ('app01_book',), ('app01_publisher',)...)
cursor.close()
conn.close()

对于一些基础的 命令错了、数据库创建重名、数据库不存在都会报错。


对于python操作,要先链接,之后设置游标,之后用游标发送命令。


链接:conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8')

设置游标:cursor = conn.cursor()

发送命令如:cursor.execute('show databases')

接收返回用cursor.fetchall()

执行增删改用conn.commit()

cursor.fecthall() 返回全部信息,元组里套元组,没有为一个空括号


cursor.fecthone() 返回第一条信息,一个元组里面是想要的信息,没有返回None

数据表管理

内置客户端操作

数据表常见操作的指令:

  • 进入数据库 use 数据库;,查看当前所有表:show tables;
  • 创建表结构

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

create table 表名(

   列名  类型,

   列名  类型,

   列名  类型

)default charset=utf8;

最后一个并不要加逗号

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

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

写一行一样,这样习惯。

 create table tb4(
    id int primary key,             -- 主键(不允许为空、不能重复)
    name varchar(16) not null,   -- 不允许为空
    email varchar(32) null,      -- 允许为空(默认)
    age int default 3            -- 插入数据时,如果不给age列设置值,默认值:3
)default charset=utf8;

主键一般用于表示当前这条数据的ID编号(类似于人的身份证),需要我们自己来维护一个不重复的值,比较繁琐。所以,在数据库中一般会将主键和自增结合。

create table tb5(
    id int not null auto_increment primary key,    -- 不允许为空 & 主键 & 自增
    name varchar(16) not null,           -- 不允许为空
    email varchar(32) null,              -- 允许为空(默认)
    age int default 3                    -- 插入数据时,如果不给age列设置值,默认值:3
)default charset=utf8;

注意:一个表中只能有一个自增列【自增列,一般都是主键】。

   注意:在mysql里 -- 代表注释

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

  • 查看表

       desc 表名;

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

删除表 drop table 表名;

清空表 delete from 表名; 或者 truncate from 表名;(速度快、无法回滚撤销等)

修改表

添加列

               alter table 表名 add 列名 类型;

               alter table 表名 add 列名 类型 DEFAULT 默认值;

               alter table 表名 add 列名 类型 not null default 默认值;

               alter table 表名 add 列名 类型 not null primary key auto_increment;


删除列

               alter table 表名 drop column 列名;


修改列类型

               alter table 表名 modify column 列名 类型;


修改列类型+名称

               alter table 表名 change 原列名 新列名 新类型;


               alter table  tb change id nid int not null;

               alter table  tb change id id int not null default 5;

               alter table  tb change id id int not null primary key auto_increment;


               alter table  tb change id id int; -- 允许为空,删除默认值,删除自增。


               可用上面这一种方式修改已有列


修改列默认值

               ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;


删除列默认值

               ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;


添加主键

               alter table 表名 add primary key(列名);


删除主键

               alter table 表名 drop primary key;

Python代码操作

与上面一样 不进行过多列举

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', charset='utf8')
cursor = conn.cursor() #创建一个游标 初学要注意哪里用conn.commit() 哪里用cursor.fetchone()
cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
conn.commit()
cursor.execute('use db3')
message = '''
create table t4(
    id int not null primary key auto_increment,
    title varchar(128),
    content text,
    ctime datetime
)default charset=utf8;
'''
cursor.execute(message)
cursor.execute('show tables')
cursor.execute('desc t4')
result = cursor.fetchall()
print(result)
#(('id', 'int', 'NO', 'PRI', None, 'auto_increment'), ('title', 'varchar(128)', 'YES', '', None, ''), ('content', 'text', 'YES', '', None, ''), ('ctime', 'datetime', 'YES', '', None, ''))
cursor.close()
conn.close()

但对于一些数据类型 显示的时候会告诉你这是什么数据

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

但遍历出来就没事了   很多东西都是这样,如字典.items()会生成一个伪列表。

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

数据行管理

内置客户端操作

数据行操作的相关SQL语句(指令)如下:

  • 数据

insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);

insert into tb1(name,password) values('卢本伟','123123');
insert into tb1(name,password) values('卢本伟','123123'),('alex','123');
insert into tb1 values('卢本伟','123123'),('alex','123'); -- 如果表中只有2列

放心写,不符合设定什么的都会报错。 记得values 和字符串要加引号。

左边的可以不写全(如有自增的,默认值的)但右边一定要和左边数量相等且相匹配。

主键自增也是默认自增,12345,你可以直接插入一条id是7的数据,像这种主键默认是不允许重复的。

  • 除数据

delete from 表名;

delete from 表名 where 条件;

delete from tb1;
delete from tb1 where name="lbw";
delete from tb1 where name="lbw" and password="123";
delete from tb1 where id>9;
  • 数据

update 表名 set 列名=值;

update 表名 set 列名=值 where 条件; (注:一个等号)

update tb1 set name="lbw";
update tb1 set name="lbw" where id=1;
update tb1 set age=age+1;  -- 整型
update tb1 set age=age+1 where id=2;
update L3 set name=concat(name,"db");
update L3 set name=concat(name,"123")  where id=2;  -- concat一个函数,可以拼接字符串
  • 询数据

select * from 表名;

select 列名,列名,列名 from 表名;

select 列名,列名 as 别名,列名 from 表名;

select * from 表名 where 条件;

select * from tb1;
select id,name,age from tb1;
select id,name as N,age, from tb1;
select id,name as N,age, 111 from tb1;
select * from tb1 where id = 1;
select * from tb1 where id > 1;
select * from tb1 where id != 1;
select * from tb1 where name="wupeiqi" and password="123";

Python代码操作

注意 这里链接的时候写了参数 db='userdb',即连接到userdb这个数据库。

相当于执行了 cursor.execute('use userdb')

import pymysql
# 连接MySQL,自动执行 use userdb; -- 进入数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()
# 1.新增(需commit)
"""
cursor.execute("insert into tb1(name,password) values('卢本伟','123123')")
conn.commit()
"""
# 2.删除(需commit)
"""
cursor.execute("delete from tb1 where id=1")
conn.commit()
"""
# 3.修改(需commit)
"""
cursor.execute("update tb1 set name='xx' where id=1")
conn.commit()
"""
# 4.查询
"""
cursor.execute("select * from tb where id>10")
data = cursor.fetchone() # cursor.fetchall()
print(data)
"""
# 关闭连接
cursor.close()
conn.close()

案例

例如:实现一个 用户管理系统。

先使用MySQL自带的客户端创建相关 数据库和表结构(相当于先创建好Excel结构)。

create database usersdb default charset utf8 collate utf8_general_ci;
create table users(
    id int not null primary key auto_increment,
    name varchar(32),
    password varchar(64)
)default charset=utf8;

再在程序中执行编写相应的功能实现 注册、登录 等功能。

import pymysql
def register():
    print("用户注册")
    user = input("请输入用户名:") # alex
    password = input("请输入密码:") # sb
    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
    cursor = conn.cursor()
    # 执行SQL语句(有SQL注入风险,稍后讲解)
    sql = 'insert into users(name,password) values("{}","{}")'.format(user, password)
    cursor.execute(sql)
    conn.commit()
    # 关闭数据库连接
    cursor.close()
    conn.close()
    print("注册成功,用户名:{},密码:{}".format(user, password))
def login():
    print("用户登录")
    user = input("请输入用户名:")
    password = input("请输入密码:")
    # 连接指定数据
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db="usersdb")
    cursor = conn.cursor()
    # 执行SQL语句(有SQL注入风险,稍后讲解)
    # sql = select * from users where name='wupeiqi' and password='123'
    sql = "select * from users where name='{}' and password='{}'".format(user, password)
    cursor.execute(sql)
    result = cursor.fetchone() # 去向mysql获取结果
    # 没有返回 None
    # 关闭数据库连接
    cursor.close()
    conn.close()
    if result:
        print("登录成功", result)
    else:
        print("登录失败")
    #这里可以细分 只去找账号,返回账号是否不存在,然后根据元组坎密码是否对应正确。
def run():
    choice = input("1.注册;2.登录")
    if choice == '1':
        register()
    elif choice == '2':
        login()
    else:
        print("输入错误")
if __name__ == '__main__':
    run()

注意,看到引号没 ->  这个要加 不然有时会出错

不过不应该这样写,不用字符串格式化,下面MySQL注入会讲

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

应该

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

So,你会发现, 在项目开发时,数据库 & 数据表 的操作其实就做那么一次,最最常写的还是 对数据行 的操作

关于SQL注入

假如,你开发了一个用户认证的系统,应该用户登录成功后才能正确的返回相应的用户结果。

import pymysql
# 输入用户名和密码
user = input("请输入用户名:") # ' or 1=1 -- 
pwd = input("请输入密码:") # 123
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='usersdb')
cursor = conn.cursor()
# 基于字符串格式化来 拼接SQL语句
# sql = "select * from users where name='alex' and password='123'"
# sql = "select * from users where name='' or 1=1 -- ' and password='123'"
sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
cursor.execute(sql)
result = cursor.fetchone()
print(result) # None,不是None
cursor.close()
conn.close()

如果用户在输入user时,输入了: ' or 1=1 -- ,这样即使用户输入的密码不存在,也会可以通过验证。


为什么呢?


因为在SQL拼接时,拼接后的结果是:


select * from users where name='' or 1=1 -- ' and password='123'

注意:在MySQL中 -- 表示注释。


那么,在Python开发中 如何来避免SQL注入呢?


切记,SQL语句不要在使用python的字符串格式化,而是使用pymysql的execute方法。

import pymysql
# 输入用户名和密码
user = input("请输入用户名:")
pwd = input("请输入密码:")
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()
cursor.execute("select * from users where name=%s and password=%s", [user, pwd])
# 或
# cursor.execute("select * from users where name=%(n1)s and password=%(n2)s", {"n1": user, 'n2': pwd})
result = cursor.fetchone()
print(result)
cursor.close()
conn.close()

其他常用

96447814-120fc980-1245-11eb-938d-6ea408716c72.png

条件

根据条件搜索结果。

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where info.id > 10;
select * from info where id between 2 and 4;   -- id大于等于2、且小于等于4
select * from info where name = 'xx' and age = 19;
select * from info where name = 'xx' or age = 49;
select * from info where (name = 'xx' or email="pyyu@live.com")  and age=49;
select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);
# select * from info where id in (1,2,3);
# exists select * from depart where id=5,去查数据是否存在,如果存在,如果不存在。
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);
select * from (select * from info where id>2) as T where age > 10;1.

如:查找年龄大于20小于60的数据:

select * from info where age between 20 and 60;
select * from info where age>=20 and age<=60;
select * from (select * from info where age>=20) as T where age<=60;
-- 最后一种的as要加的
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
存储 关系型数据库 MySQL
MySQL数据库的使用
MySQL数据库的使用
46 0
|
SQL 存储 关系型数据库
MYSQL数据库2
MYSQL数据库2
142 0
|
关系型数据库 MySQL 数据库
|
SQL 关系型数据库 MySQL
MYSQL数据库7
MYSQL数据库7
50 0
|
存储 关系型数据库 MySQL
|
存储 关系型数据库 MySQL
|
SQL 存储 监控
简单说一下使用SQL数据库的心得
简单说一下使用SQL数据库的心得
106 1
|
存储 SQL 关系型数据库
|
SQL 关系型数据库 MySQL
MySQL数据库(初步了解)
MySQL数据库(初步了解)
114 0
|
存储 SQL 缓存
Mysql数据库知识总结
如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。
96 0