MySQL基础----py全栈

MySQL基础----py全栈

目录

一、引言

1、什么是数据？

描述事物的符号记录，可以使数字，也可以是文字，图形、图像等，数据有多种形式，它们都可以经过数字化存入计算机，数据的含义成为数据的语义

2、什么是数据库（DB）？

存储数据的仓库，是长期存放计算机内、有组织、可共享的大量数据的集合，数据库中的数据是按照一定数据模型组织的，描述和存储，具有较小的冗余度，较高的独立性和易扩展性，并为各种用户共享，即数据库有永久存储、有知识和可共享的三个基本特点。

3、什么是数据库管理系统（DBMS）？

数据库管理系统又称DBMS(Database Management System ),顾名思义，位于用户和操作系统之间的一层数据管理软件。数据库管理系统主要有以下功能：

  • 数据定义功能：提供数据定义语言（DDL），让用户方便地对数据库中数据对象进行定义
  • 数据组织、存储和管理：要提高存储空间利用率和存储效率
  • 数据操纵功能：提供数据操纵语言（DML），实现对数据库基本操作，如增删改查
  • 数据库的建立和维护功能：统一管理控制，以保证安全、完整、多用户并发使用。
  • 其他功能：与网络中中的其他软件系统通信功能，异构数据库之间的互访和互操作功能。

常听说的数据库管理系统有MySQL、Oracle、SQLite、Access、MS SQL Server等，他们均是软件，它们属于关系型数据库。他们都有两个主要功能：

  • 将数据保存到文件或内存
  • 接受client特定的命令，然后对文件进行相应的操作

4、什么是数据库系统？

指的是计算机系统中引入数据库后的系统，一般有数据库、数据库管理系统、应用系统、数据库管理员（DBA）构成，常简称为数据库。

5、数据库管理系统由来

  • 数据库管理系统由来

    我们思考几个问题，在网络编程中我们的远端Client的用户产生的数据，我们要存放到什么地方，比较数据保存在本地硬盘，来分析，下面是分析过程：

    数据存放位置 在本地管理数据 在远端管理数据
    功能实现 1、创建本地目录、存放文件
    2、有多个目录，用来归类存放不同类型的数据文件
    3、不同用户有不同的权限，用户可以访问，修改自己的数据
    		 1、用户只要有socketClient客户端，就可以访问远端拿到数据
    2、远端存放数据的位置有socket服务端接受数据，并保存数据。
    3、不同用户可以在远端服务器通过socket执行命令（来操作保存数据文件）
    4、不能 用户有不同的权限，对文件

6、什么是数据模型？

是对现实世界数据特征的抽象，是对现实世界的模拟，是数据库系统的核心和基础。数据模型应满足三方面需求：1.真是模拟现实世界；2.容易为人理解；3.便于在计算机上实现。数据模型按应用目的分为两类：

  • 第一类是概念模型：按用户观点对数据和信息建模，主要用于数据库设计，也称信息模型。
  • 第二类是逻辑模型和物理模型：逻辑模型是按计算机观点对数据建模，主要包括层次模型、网络模型、关系模型、面向对象模型、对象关系模型；物理模型是对数据最底层的抽象，它描述数据在系统内部的表示方式和存取方法。

数据模型通常由数据结构、数据操作和完整性约束三部分组成，是严格定义的一组概念的集合，精确的描述了系统的静态特写、动态特性和完整性约束条件。

7、什么是概念模型？

用于信息世界的建模，是现实世界到信息世界的第一层抽象，是数据库设计人员进行数据库设计的有力的工具，也是设计人员与用户之间进行交流的语言。信息世界涉及的概念主要有：

  1. 实体（Entity）：客观存在并可相互区别的事物成为实体。可以是人、事、物，也可以是抽象的概念和联系。
  2. 属性（Attribute）：实体所具有的某一特性。一个实体可以又若干个属性来刻画。
  3. 码（Key）：唯一标识实体的属性集。
  4. 域（Domain）：域是一组具有相同数据类型的值的集合。属性的取值范围来自某个域。
  5. 实体型（Entity Type）：具有相同属性的实体必然具有共同的特征和性质。用实体名和属性名集合来抽象和刻画同类实体，称为实体型。
  6. 实体集（Entity Set）:同一类型实体的集合。
  7. 联系（Relationship）：实体内部和实体之间的联系。实体之间的联系有一对一，一对多、多对多三种。

8、什么是关系型数据库？

指建立在关系模型基础上的，而关系模型本质上就是若干个存储数据的二维表。

二、MySQL数据库的安装以及服务配置

MySQL是一个关系型数据库管理系统，目前属于Oracle旗下，MySQL最流行的关系型数据库管理系统（又称RDBMS，Relational Database Management System），想要使用MySQL数据库我们需要以下步骤：

  • 安装mysql服务端
  • 安装mysql客户端
  • 客户端连接服务端
  • 客户端发送命令给服务端，服务端根据指令执行对数据的相应操作。

1、windows版本

windows下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

  • 安装

    我们下载的是zip版本，解压之后，就可以了，即可。

MySQL目录解释：

bin -----存放mysql的可执行文件，如：mysql、mysqld等。
data -----用于防止一些日志文件以及数据库，默认没有，初始化之后生成
docs ------存放，mysql用法的说明文档
include ----用于放置一些头文件
lib -----用于放置一些mysql要用到的库文件
share ------用于放置一些字符集、语言等信息。

  • 服务配置

    • 初始化mysql数据库，生成data目录

      cd D:\Download\mysql-5.7.25-winx64\bin
mysqld  --initialize-insecure

    • 启动MySQL服务端

      #----------------------方法一-----------
#打开命令窗口
#进入可执行文件目录
cd D:\Download\mysql-5.7.25-winx64\bin
#启动mysql服务
mysqld


#-------------------方法二----------------
#将mysql的可执行文件目录添加到系统的环境变量PATH中
#步骤：
#控制面板->系统和安全->系统->高级系统设置，打开【系统属性】面板
#在【系统属性】面板中，选中高级选项卡，点击环境变量，打开【环境变量】面板
#在【环境变量】面板中，在【系统变量】里查找选中变量为PATH的一行，点击编辑，
#在变量值得末尾，添加值【;D:\Download\mysql-5.7.25-winx64\bin】,注意一点要加上分号。

# 在任意目录，打开命令窗口，直接输入mysqld命令，即可开启服务


#---------------------方法三------------
#步骤：
#在命令窗口执行以下命令，注册mysqld为windows服务
"D:\Download\mysql-5.7.25-winx64\bin" --install
#执行以下命令，移除mysqld服务。
"D:\Download\mysql-5.7.25-winx64\bin" --remove
#注册成功之后，以后再启动和关闭mysql服务时，仅需在命令行输入
#启动服务
net start mysqld
#关闭服务
net stop mysqld

      对比三种方法：

      1、方法一：在命令行开启服务，关闭命令窗口，服务停止，并且每次需要进入到bin目录下执行命令

      2、方法二：不需要进入bin目录下执行命令，但是关闭命令窗口，服务也停止

      3、方法三：不需要进入并bin目录下执行命令，并且关闭命令窗口，服务仍然在执行。

      总结：

      最佳方式是结合方法二和方法三，

      将bin目录添加到path路径下，然后注册mysqld为windows服务。

    • 启动MySQl客户端

      我们下载的软件中，包含了mysql服务端程序，也包含了客户端程序，统一放到了bin目录下，如果我们已经将bin添加到path路径下的话，我们执行下面的命令去连接数据库：

      #连接MySQL服务器，之后敲两个回车就行了，一开始默认没有设置密码
mysql -u root -p    #本地登陆（客户端和服务端在一起）

#命令解释：
#mysql -u 用户名 -h 服务器ip地址  -p 密码

2、linux系统

  • 安装

    这里我们介绍CentOS7以上系统，默认自带yum包管理工具。

    yum install mariadb-server mariadb
# mariadb-server 主要提供mysql服务
# mariadb    主要提供一些客户端命令行工具

  • 配置

    #初始化数据库，
mysql_secure_installation  #设置root密码。删除车市数据库，修改匿名用户等操作。
#启动服务
systemctl  start mariadb

3、client端连接远程数据库（Mysql数据库）

  • 使用命令行工具

    #命令解释：
mysql -u 用户名 -h 服务器ip地址  -p 密码 -P 端口号
# 注意前提是安装mysql自带的客户端工具。windows默认安装，linux只需要安装mariadb这个包
#默认端口号3306
#默认本地ip地址

  • 使用一些数据库连接软件

    windows：phpMyAdmin、Navicat 、DBeaver

  • 一些库支持

    python语言版本：pymysql、MySQLsb、mysqlclient

三、MySQL数据库

1、一些概念解释

  • 什么是SQL？

    MySQL、Oracle等软件可以接受命令，并作出相应的操作，由于命令中可以包含删除文件、获取文件内容等众多操作，对于编写的命令就是SQL语句。SQL是结构化语言的缩写，用来专门与数据库进行通信的语言。

2、数据库基本命令

  1. 显示数据库

    show databases;
#显示当前登录用户可以查看的数据库列表。

    默认root用户有四张表：

    information_schema ：提供了访问数据库元数据的方式。(元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有时用于表述该信息的其他术语包括“数据词典”和“系统目录”。)
    mysql ：mysql的核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息
    performance_schema ：主要用于收集数据库服务器性能参数。主要用于收集数据库服务器性能参数。
    sys：Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低

  2. 创建数据库：

    # utf8编码
CREATE DATABASE test1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
#整条语句语义：创建数据库名为test1，设置缺省的编码格式（字符集）为utf8，按照utf8进行排序，比较等操作。

#gbk编码
CREATE DATABASE test DEFAULT CHARACTER SET gbk  COLLATE gbk_general_ci;
#整条语句语义：创建数据库名为test1，设置缺省的编码格式（字符集）为gbk，按照gbk编码进行排序

#字段解释：
#collate：设置比较操作，用什么编码方式进行比较。
#charset  utf8  :设置数据库使用utf8字符集
#character set gbk：设置数据库使用gbk字符集

#sql语句不强制使用大小写，但是为了便于观看，我们将系统字段写成大写，用户输入写成小写

  3. 数据表简单操作

    #进入数据库
USE test1;
#显示test2数据库中所有的表
SHOW TABLES；

3、数据库用户管理

  1. 创建用户

    #格式：
create  user '用户名'@'ip地址' identified by '密码'；
#例子
create user 'dman'@'localhost' identified by 'password'；
#创建用户名为dman，密码为password。该用户只能通过在本地登陆。

  2. 删除用户

    #格式
drop user '用户名'@'IP地址';
#例子
drop user 'dman'@'localhost'；

  3. 修改用户

    #格式
rename user '用户名'@'IP地址' to '新用户名'@'IP地址'；
#例子
rename user 'dman'@'localhost' to 'test'@'localhost'；

  4. 修改密码

    #格式
 set password for '用户名'@'IP地址' = password('新密码')
#例子
set password for 'test'@'localhost' = password('redhat');

    注意事项：

    1、用户权限相关数据保存在mysql数据库的user表中，在root用户下可以对其直接进行操作，但是不建议这样做。

    2、当用户创建之后，用户只能登陆到mysql，但是不能干任何事情，需要我们去授权之后才可以。

4、数据库用户授权

  1. 查看用户权限

    show grants for '用户'@'IP地址'；

  2. 授予某用户什么权限

    grant  权限 on 数据库.表 to   '用户'@'IP地址'

  3. 取消某用户权限

    revoke 权限 on 数据库.表 from '用户'@'IP地址'

  4. 立刻刷新数据到内存，使权限更改立刻生效

    flush privileges  #将数据读取到内存中，从而立即生效。

    ​ all privileges 除grant外的所有权限

    ​ select 仅查权限
    ​ select,insert 查和插入权限
    ​ ...
    ​ usage 无访问权限
    ​ alter 使用alter table
    ​ alter routine 使用alter procedure和drop procedure
    ​ create 使用create table
    ​ create routine 使用create procedure
    ​ create temporary tables 使用create temporary tables
    ​ create user 使用create user、drop user、rename user和revoke all privileges
    ​ create view 使用create view
    ​ delete 使用delete
    ​ drop 使用drop table
    ​ execute 使用call和存储过程
    ​ file 使用select into outfile 和 load data infile
    ​ grant option 使用grant 和 revoke
    ​ index 使用index
    ​ insert 使用insert
    ​ lock tables 使用lock table
    ​ process 使用show full processlist
    ​ select 使用select
    ​ show databases 使用show databases
    ​ show view 使用show view
    ​ update 使用update
    ​ reload 使用flush
    ​ shutdown 使用mysqladmin shutdown(关闭MySQL)
    ​ super 􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
    ​ replication client 服务器位置的访问
    ​ replication slave 由复制从属使用

    5、其他

    • 目标数据库的格式怎么写？

      数据库名.* #数据库中所有的表

      数据库名.表名 #指定数据库中的某张表

      *.* #所有的数据库中所有的表

    • 用户名格式怎么写？

      '用户名'@'ip地址' #用户只能在该ip下才能访问

      '用户名'@'192.168.1.%' #用户只能在168网段下进行访问

      '用户名'@'%' #用户可以在任意ip下进行访问。

    • 修改用户密码的几种方式

      #方法一：
#使用phpmyadmin，修改mysql库的user表，要使用password函数包裹密码。

#方法二：
#使用shell命令mysqladmin
mysqladmin -u root -p password mypassword
#密码将改为mypassword；password为原密码。

#方法三：
#使用insert插入语句，直接修改mysql.user表
mysql> insert into  mysql.user(Host,User,Password) values('%','user1',PASSWORD('mypassword'));
mysql>flush privileges;
#注意使用password包裹密码

#方法四：
#使用replace语句
mysql> replace into mysql.user(Host,User,Password) values('%','user2',PASSWORD('mypassword'));
mysql> flush privileges;
#注意使用password语句包裹密码

#方法五：
#使用set语句
mysql> set password for 'user01'@'%' = PASSWORD('mypassword');
#需要用password包裹密码，不需要flush privileges

#方法六：
#使用grant USAGE语句
mysql>grant USAGE on *.* to 'user01'@'%' identified by 'mypassword';
#可以不用password函数包裹密码；

#方法七
#使用update语句
mysql>update mysql.user set password = PASSWORD('mypassword') where user='username'and host='hostname';
mysql>flush priviledges;

    • 补充：

      # 启动免授权服务端
mysqld --skip-grant-tables

四、SQL语句学习

1、数据库操作--表级别

  1. 创建表

    create table 表名(
    列名  类型 是否可以为空，
    列名  类型 是否可以为空
)engine=InnoDB default charset=utf8

#default CHARSET utf8   #设置编码格式为uft8
#COLLATE  utf8_general_ci  #设置排序比较的顺序

#default CHARACTER SET gbk  #设置编码格式为gbk
#COLLATE gbk_general_ci  #设置排序比较的顺序

    参数说明：

    1、是否可以为空，用null来表示

    ​ not null ---不可以为空值

    ​ null ----可以为空值

    2、设置默认值

    ​ 创建列的时候可以指定默认值

    ​ create table tb1(nid int not null default 2,num int not null)

    3、设置某列为自增列

    ​ 如果某列为自增列，插入数据时无需设置此列，默认自增（一个表中只能有一个自增列）

    ​ create table tb1(nid int not null auto_increment,num int null,index(nid))

    或者 create table tb1(nid int not null auto_increment primary key,num int null）

    注意：1、自增列，必须是索引，或者是主键

    ​ 2、对于自增可以设置步长和起始值

    show session variables like 'auto_inc%';
set session auto_increment_increment=2;
set session auto_increment_offset=10;

show global  variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10;

    4、主键，是一种特殊的索引，不允许有空值，如果主键使用单个列，则它的值必须是唯一的，如果是多列，则其组合必须唯一

    create table tb1(
 nid int not null auto_increment primary key,
 num int null
 )
 或
create table tb1(
 nid int not null,
 num int not null,
 primary key(nid,num)
 )

    5、外键，一个特殊的索引，为该列设置选项（只能是另一个列表的某个值）

    creat table color(
   nid int not null primary key,
   name char(16) not null
         )

create table fruit(
 nid int not null primary key,
 smt char(32) null ,
 color_id int not null,
 constraint fruit_color foreign key (color_id) references color(nid)
         )

  2. 删除表

    drop table 表名   #直接删除表

  3. 查看表字段

    desc tb1;

  4. 清空表

    delete from 表名   #清空表
truncate table 表名   #清空表内容，速度快，并且自增列回到起始计数值。

  5. 修改表

    添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名，类型
  
添加主键：
        alter table 表名 add primary key(列名);
删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;
  
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
  
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

  6. 基本数据类型

    MySQL的基本数据类型有数值、时间和字符串

    • 数值

    • 时间

    • 字符串

    • 枚举类型

    • 二进制类型

    • 集合类型

  7. 其他

    1. mysql支持的Engine引擎都有那些，以及他们的差别？
    2. 数据库中的原子操作

2、数据库操作--行级别

  1. 增

    insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表

  2. 删

    delete from 表
delete from 表 where id＝1 and name＝'dman'

  3. 改

    update 表 set name ＝ 'dman' where id>1

  4. 查

    select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

  5. 其他

    a、条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
 
    select * from 表 where id between 5 and 16;
 
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
 
b、通配符
    select * from 表 where name like 'dma%'  - ale开头的所有（多个字符串）
    select * from 表 where name like 'dma_'  - ale开头的所有（一个字符）
 
c、限制，分页
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
 
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
 
e、分组
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
 
    select num from 表 group by num having max(id) > 10
 
    特别的：group by 必须在where之后，order by之前
 
f、连表
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid
 
g、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

    总结以及一些注意事项：

    1、对于查询操作

    select * from tb1；（效率低）
select  col1,col2 from tb1 (尽量将表头写一遍，这样效率高。)

    2、分页操作，主要当数据量过大的时候，如果一次返回的数据过多，会导致服务器宕机，分页操作主要是限制一次返回的数据量。

五、关于数据库的其他操作

1、数据库导入与导出

  1. 导出整个数据库

    #mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -h127.0.0.1 -P3306 -u dbuser -p dbname > dbname.sql

  2. 导出一个表

    mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

mysqldump -h127.0.0.1 -P3306 -u dbuser -p dbname users > dbname.sql

  3. 导出一个数据库结构，在原有基础上加-d选项

    mysqldump -h127.0.0.1 -P3306 -d --add-drop-table -u dbuser -p dbname users > dbname.sql

-d 没有数据

–add-drop-table  在每个create语句之前增加一个drop table

  4. 导入数据库

    mysql>use 数据库

#然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql> source /path.../dbname.sql

2、mysql文件的注释

/*
多行注释
*/

select * /*这是一个在行中间的注释*/ from device limit 1；

#单行注释

-- 当行注释（双长划）这个注释必须在两个长划后至少有一个空格。

3、数据库优化相关(后续更新)

posted @ 2019-04-09 20:43  yanling0813  阅读(388)  评论(0)    收藏  举报
编辑推荐：
· 大数据高并发核心场景实战，数据持久化之冷热分离
· 运维排查 | SaltStack 远程命令执行中文乱码问题
· Java线程池详解：高效并发编程的核心利器
· 从“看懂世界”到“改造世界”：AI发展的四个阶段你了解了吗？
· 协程本质是函数加状态机——零基础深入浅出 C++20 协程
阅读排行：
· .NET 8 gRPC 实现高效100G大文件断点续传工具
· STM32学会要花费多长时间？一个从机械转行老程序员的血泪史
· LinqPad：C#代码测试学习一品神器
· .NET入行第4年(第二集)
· C#/.NET/.NET Core技术前沿周刊 | 第 43 期（2025年6.16-6.22）
点击右上角即可分享
微信分享提示