一文入门mysql 数据库

一、数据库概述

什么是数据库

       数据库是一个用于存储和管理数据的仓库。数据按照特定的格式存储，可以对数据库中的数据进行增加、修改、删除和查询操作。数据库的本质是一个文件系统，按照一定的逻辑结构组织数据，以方便高效地访问和维护。

什么是数据库管理系统

       数据库管理系统（DataBase Management System，DBMS）是一个操作和管理数据库的软件。它用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。

常见的数据库管理系统

MYSQL ：

MySQL 不仅用于小型数据库，还可以扩展到大型企业级应用场景。它在许多高流量的网站和大型企业系统中得到了广泛应用。
虽然 Oracle 收购了 MySQL，但开源版本仍然存在且免费。MySQL 有多个版本（如社区版、企业版等），其中一些版本收费，但免费的社区版仍在维护和更新。
Oracle ：收费的大型关系数据库管理系统，主要用于大型企业级应用。

SQLServer：MicroSoft 公司收费的中型的数据库。SQL Server 不仅适用于 C# 和 .NET 语言，还可以与其他编程语言（如 Java、Python、PHP 等）一起使用。

SQLite : 嵌入式的小型数据库，SQLite 不仅应用于手机端，还可以应用于桌面应用程序和物联网（IoT）设备等。

PostgreSQL：PostgreSQL 是一种开源的关系数据库管理系统，功能强大且可扩展，适用于各种规模的应用场景。它在许多大型企业和开源项目中得到了广泛应用。

NoSQL：除了上述描述的关系数据库管理系统外，还有一类称为 NoSQL（Not Only SQL）数据库，它们使用非关系模型来存储和管理数据。这些数据库包括 MongoDB（文档存储）、Redis（键值存储）、Apache Cassandra（列存储）和 Neo4j（图数据库）等。这些数据库在大数据、分布式系统和实时应用场景中具有优势。

数据库表

数据库中以表为组织单位存储数据。

表类似我们的Java类，每个字段都有对应的数据类型。

那么用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系。

类----------表

类中属性----------表中字段

对象----------记录

二、MySql数据库

1.安装：

可以观看我之前的作品

2.登录,在windows命令窗口

mysql -u(用户名) -p(密码) -h(主机ip地址) -P（主机ip端口号） 

3.基础指令

(1)数据库基本指令：

show  databases;  显示所有的数据库

create database 数据库名 charest=utf8;  创建数据库

drop database 数据库名;  删除数据库

use 数据库名； 使用数据库

select database();  查看当前使用的数据库

(2)数据表基本指令：

show tables;  显示数据库中的所有数据表

create  table 表名 (列名 类型 约束1 约束2...，列名  类型  约束1  约束2...);  创建数据表

desc 表名;  查看数据表结构

drop table 表名;  删除数据表

(3)数据表中列的增删改：

alter table 数据表名 drop 列名;  删除列

alter table 数据表名  add  列名 类型 约束1  约束2...;  创建列

alter table 数据表名  change 列名 新列名 类型 约束1  约束2...;  修改列

(4)列的约束条件：

1.主键 :primary  key ，可以确定唯一的一行

2.非空 :not null ，不能为空，必须有值

3.自增长: auto_increment  ，每次+1

4.唯一: unique, 列中内容不重复

5.默认：default，默认值

6.外键：foreign，与其他表关联，是其他表的主键

7.检查：check，Mysql 8 才有，检查满足条件

4.高级指令

(1)、CURD  增删改查

查：

1.select * from 数据表名 where 条件; 慎用，非常耗时

2.select 列名，列名，列名 from 数据表名 where 条件；

3.select  列名 as 别名,列名 as 别名 from 数据表名 where 条件;

条件：

1.比较运算符： =   >  >=  <  <=  !=  <>

2.逻辑运算符：and or  not

3.成员运算符： in [a,b,c]

4.between a and b

5.判断是否为空：is null  is not null

6.like   % 代表多个字符，_ 代表一个字符

关联查询：表与表之前存在关联关系

嵌套查询：第一次查询的结果作为第二次查询的条件

表连接：

1.笛卡尔连接：一个表中每一行都和另外一个表中所有行连接

2.内连接 ：inner join 

举例：select user.id as 用户id, user.username as  用户名, role.nick as 昵称, role.level as 等级 from user inner join role  on user.id = role.user_id;

3.外连接：左外连接，右外连接

左外连接：left join，以左表为主，左表条件满足 正常显示 不满足 右侧补null。

举例：select * from user left join role on user.id = role.user_id;

右外连接：right join，以右表为主，右表条件满足 正常显示 不满足 左侧补null。

举例：select * from role right join user on user.id = role.user_id;

4.全连接：full join，left join   union  right join， mysql不支持关键字 full join

举例： select * from user right join role on user.id = role.user_id union  select * from user left join role on user.id = role.user_id;

增

1. insert into  表名 values (列1， 列2，列n)，（列1， 列2， 列n）....  列的个数与值的个数一致

2 . insert into  表名  （列1， 列2） values （值1， 值2）， （值1， 值2）...    可以指定哪些列的数据，没有默认值的列必须插入

3. insert into   表名  set  列1=值1，列2=值2；  插入1行指明插入的列与其值

改

update 数据表名 set 列名1 = 值1 where 条件；  如果没有条件就修改整个表

删

delete from 表名 where 条件；  如果没有条件就清空表

(2)、分组、分页、排序

1. 去重：distinct  ，单独列展示 并对列去重

举例： select  distinct user_id from role;

2. 分组：group by ，一般会结合count （*）进行统计

举例： select info as '组信息', count(*) as 个数 from role group by info;

3. 分页：limit ，一个数字n 代表 前n个，两个数字start， count，每页显示count个  第n页公式：（n-1）* count， count

4. 排序： order by， asc 升序、desc 降序

举例：select * from role order by user_id desc , id  desc  ;  id 降序

(3)、用户操作

1.root系统管理员+ 其他用户，% 代表所有ip都可以访问

2.创建用户：create user '用户名'@'%' identified by '123456';

3.分配权限：grant 权限列举 to ‘用户名’@‘%’ on  数据名.表名 ，all 所有权限，*.*  所有数据库中的所有表

4. 刷新权限： flush privileges;

5. 删除用户：drop user  '用户名'@'%'；

6. 修改密码：alter user ‘用户名’@‘%’  identified by ‘123456’；

(4)、外键

1.创建、添加外键：constraint  外键名 foreign key   （列名）  references  表 （列名）on update  cascade  on  delete   cascade;

2. 删除外键：alter table 表名 drop foreign key 外键名；

(5)、函数

1. 查看当前数据库：select database();

2. 查看当前用户： select  user();

3. 查看版本： select version();

4. 现在时间： select now();

5. 时间戳： select unix_timestamp()；

(6)、特殊数据类型

1. int

2. varchar、char

3. datetime，default current_timestamp，default current_timestamp on update current_timestamp

4. enum，enum("男“，”女“， ”保密“)  default "保密"

三、pymysql

1、pymysql 基础使用

(1).安装

终端输入：pip install pymysql

(2). 导包

import pymysql

(3). 构建连接实例

con = pymysql.connect(user="qyc", password='123456')

 (4). 使用数据库

con.select_db("python2407")

(5). 创建游标(游动的鼠标)，用于执行sql语句

cur = con.cursor()

5.1 使用游标执行sql

row = cur.execute("show tables")
print(f"{row}个表")

5.2 打印所有表 fetchall

datas = cur.fetchall()
for data in datas:
    print(data[0])

 5.3 打印一个表（游标前进一位） fetchone

datas = cur.fetchone()
print(datas[0])
datas = cur.fetchone()
print(datas[0])

5.4 打印n个表  fetchmany(size=n) 取n个

datas = cur.fetchmany(size=2)
print(datas)

5.5 游标滚动,默认相对(relative), (absolute)绝对，正值前进，负值后退

cur.scroll(1)  # 打印 第一个
data = cur.fetchone()
print(data)
cur.scroll(0, mode="absolute") # 同样打印第一个
data = cur.fetchone()
print(data)

(6). 关闭游标，关闭连接

 cur.close() con.close()

2、查询插入参数

(1). 查询单个数据

cur.execute("select * from role where nick =%s or level = %s", args=('射手', 3))
#结果：(4, '射手', 3, 1002)

(2).查询多行

cur.executemany("select * from role where id = %s and level =%s", [(3, 1), (5, 15)])
#结果：(5, '打野', 15, 1003)

(3). 插入单行数据

row = cur.execute("insert into student values (%s,%s,%s)", (0, '赵六', 30))

插入前：

插入后：

(4). 插入多行数据

row = cur.executemany("insert into student values (%s,%s,%s)", [(0, '吕布', 6), (0, '李白', 3)])

插入前：

插入后：

3、pymysql 辅助类

笔者编写了一个小小案例,应该挺好理解的，创建类，封装了进入mysql的方法，每次初始化的时候都可以进入mysql

import pymysql


class MySqlHelper:
    def __init__(self, db="python2407"):
        self.con = None
        self.cur = None
        self.con = pymysql.connect(user="root", password="123456", database=db)
        self.cur = self.con.cursor()

    def query_one(self, query, args=None):
        self.cur.execute(query, args)
        return self.cur.fetchone()

    def query_all(self, query, args=None):
        self.cur.execute(query, args)
        return self.cur.fetchall()

    def update(self, query, args=None):
        row = self.cur.execute(query, args)
        self.con.commit()
        return row

    def __del__(self):
        if self.cur:
            self.cur.close()
        if self.con:
            self.con.close()


class UserMysqlHelper:
    @staticmethod
    def login(username, password):
        sql = MySqlHelper()
        user = sql.query_one("select * from user where username=%s and password = %s ", (username, password))
        return user

    @staticmethod
    def regist(username, password):
        sql = MySqlHelper()
        row = sql.update("insert into user (username,password) values (%s,%s)", (username, password))
        return row


def main():
    while True:
        menu = """0、退出
1、登录
2、注册
"""
        print(menu)
        option = input("输入数字选择对应选项")
        if option not in [str(i) for i in range(3)]:
            print(f"输入不合法，重新输入")
        else:
            if option == "0":
                break
            elif option == "1":
                while True:
                    username = input("输入用户名")
                    if 2 <= len(username) <= 4:
                        break
                    else:
                        print(f"输入不合法，重新输入")
                while True:
                    password = input("请输入密码")
                    if 2 <= len(password) <= 10:
                        break
                    else:
                        print(f"输入不合法，重新输入")
                user = UserMysqlHelper.login(username, password)
                if user:
                    print(f"登陆成功")
            elif option == "2":
                while True:
                    username = input("输入用户名")
                    if 2 <= len(username) <= 4:
                        break
                    else:
                        print(f"输入不合法，重新输入")
                while True:
                    password = input("请输入密码")
                    if 2 <= len(password) <= 10:
                        break
                    else:
                        print(f"输入不合法，重新输入")
                while True:
                    password2 = input("请再次输入密码")
                    if 2 <= len(password2) <= 10:
                        break
                    else:
                        print(f"输入不合法，重新输入")
                if password != password2:
                    print(f"密码不一致，重新注册")
                else:
                    row = UserMysqlHelper.regist(username, password)
                    if row > 0:
                        print(f"注册成功")
                    else:
                        print(f"注册失败，重新注册")


if __name__ == '__main__':
    main()

4. pymysql 执行函数与过程

(1). 构建过程函数

构建函数：

 输入：

CREATE DEFINER=`root`@`localhost` PROCEDURE `regist_user`(IN `n` int)
BEGIN
	#Routine body goes here...
	# 清空用户表
  
	# 注册n个用户
	
	# 声明一个变量i 他的类型为int  他的初始值为1
	DECLARE i INT DEFAULT 1;
	
	# 使用循环 创建n个账户
	WHILE i <= n DO
		INSERT INTO `user` VALUES (0, CONCAT('name',i), '123456');
		SET i = i + 1;		
	END WHILE;

END

函数执行前：

单击 运行

输入参数：100

函数运行后：多了100个数据

(2)、构建函数

保存，运行，输入参数  a,b

 结果：

以上就是小编使出浑身解数写的mysql了，创作不易，请大家多多点赞支持！