mysql数据库基础与管理

目录

一、数据库基本概念

1.1数据

1.2表

1.3数据库

1.4数据库系统

1.5数据库系统

二、数据库发展史

三、关系数据库与非关系数据库

四、mysql数据库管理

4.1释义

4.2常见的数据类型

4.3扩展

4.4数据库管理命令

4.4.1DDL数据定义语言，用于创建数据库对象，如库、表、索引等（create、drop、alter）

4.4.2DML管理表的数据记录—— 数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据（insert 、update、delete）

 4.4.3DQL查询记录

 4.5清空表

4.6创建临时表，克隆表

 4.7DCL

---

一、数据库基本概念

1.1数据

（1）描述事物的符号记录称为数据（Data）。数字、文字、图形、图像、声音、档案记录等都是数据。

（2）数据是以“记录”的形式按照统一的格式进行存储的，而不是杂乱无章的。 相同格式和类型的数据统一存放在一起，而不会把“人”和“书”混在一起存储。这样，数据的存储就能够井然有序。

1.2表

记录：行

（字段）属性：列

以行+列的形式就组成了表

1.3数据库

数据库就是表的集合，它是以一定的组织方式存储的有关数据集合

1.4数据库系统

数据库管理系统（DatabaseManagementSystem，DBMS）是实现对数据库资源有效组织、管理和存取的系统软件。它在操作系统的支持下，支持用户对数据库的各项操作。

BMS主要包括以下功能：

1）数据库的建立和维护功能：包括建立数据库的结构和数据的录入与转换、数据库的 转储与恢复、数据库的重组与性能监视等功能。

2）数据定义功能：包括定义全局数据结构、局部逻辑数据结构、存储结构、保密模式及信息格式等功能。保证存储在数据库中的数据正确、有效和相容，以防止不合语义的错误数据被输入或输出。

3）数据操纵功能：包括数据查询统计和数据更新两个方面。

4）数据库的运行管理功能：这是数据库管理系统的核心部分，包括并发控制、存取控 制、数据库内部维护等功能。

5）通信功能：DBMS 与其他软件系统之间的通信,如 Access 能与其他 Office 组件进行数据交换。

1.5数据库系统

数据库系统（DatabaseSystem，DBS）是一个人机系统，一般由硬件、操作系统、数据库、DBMS、应用软件和数据库用户（包括数据库管理员）组成。用户可以通过 DBMS 操作数据库，也可以通过应用程序操作数据库。

应用程序是利用DBMS为解决某个具体的管理或数据处理的任务而编制的一系列命令的有序集合。如果应用程序比较完善，能够提供友好的人机界面，并编译成可执行文件发行，使得普通用户不需要具备计算机的专业知识，在较短时间就学会使用，那么就称为数据库应用软件。 navicat

常用的数据库应用软件有人事管理、财务管理、图书管理等信息管理软件及各类信息咨 询系统等。

数据库管理员（DatabaseAdministrator，DBA）负责数据库的更新和备份、数据库系统的维护、用户管理等工作，保证数据库系统的正常运行。DBA 一般由业务水平较高、资历较深的人员担任。

需要注意的是数据库、数据库系统、数据库管理系统，甚至数据库表等名词，在日常讨论中通常不严格区别。遇到此情况时，可以根据具体情况，判断出实际所指的是什么。

DBMS的工作模式

1>接受应用程序的数据请求和处理请求

2>将用户的数据请求（高级指令）转换为复杂机器代码（底层指令）

3>实现对数据库的操作

4>从数据库的操作中接受查询结果

5>对查询结果进行处理（格式转换）

6>将处理结果返回给用户

二、数据库发展史

1、 层次型和网状型： 代表产品是1969年IBM公司研制的层次模型数据库管理系统IMS。
2、 关系型数据型库： 目前大部分数据库采用的是关系型数据库。1970年IBM公司的研究员E．F．Codd提出了关系模型。其代表产品为sysem R和Inges。
3、第三代数据库将为更加丰富的数据模型和更强大的数据管理功能为特征，以提供传统数据库系统难以支持的新应用。它必须支持面向对象，具有开放性，能够在多个平台上使用。管理技术的3个阶段1 人工管理2 文件管理3 数据库系统
 

三、关系数据库与非关系数据库

关系数据库

关系型数据库（RDBMS） 是一种使用表格（二维表）来存储和管理数据的数据库系统，数据通过行（记录）和列（字段）组织，并且表与表之间通过关系（如外键）进行关联。常见的关系型数据库包括 MySQL、PostgreSQL 和 Oracle。

非关系数据库

非关系型数据库（NoSQL） 是一种不使用传统表格结构的数据存储系统，数据可以以文档、键值对、图或列族的形式存储，适合处理大量非结构化或半结构化数据。常见的非关系型数据库包括 MongoDB、Redis。

关系数据库和非关系数据库的区别

 数据结构：关系型数据库使用表格，非关系型数据库使用更灵活的结构（如文档、键值）。
 查询语言：关系型数据库使用 SQL，非关系型数据库通常使用特定的API或查询语言。
 扩展性：关系型数据库更适合垂直扩展（增加硬件能力），非关系型数据库更适合水平扩展（增加节点）

四、mysql数据库管理

4.1释义

Field:字段名称
type:数据类型
Null :是否允许为空
Key :主键

Default :默认值
Extra :扩展属性，例如:标志符列（标识了种子，增量/步长）1 2
id：1 3 5 7

4.2常见的数据类型

int：整型                        用于定义整数类型的数据
float：单精度浮点4字节32位        准确表示到小数点后六位        
double：双精度浮点8字节64位
char：固定长度的字符类型        用于定义字符类型数据。 20字节  4 
varchar：可变长度的字符类型                               20   4 
text：文本
image：图片
decimal(5,2)：5个有效长度数字，小数点后面有2位        指定长度数组 
#Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错

#char和varchar的区别，char读取快，但是占用内存多

varchar读取慢，但是占用内存少

4.3扩展

MySQL数据库的数据文件存放在/usr/local/mysql/data目录下，每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为三个文件，扩展名分别为“.frm”、“.MYD”和“.MYI”。

MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起。

“.MYI”文件也是专属于 MyISAM 存储引擎的，主要存放 MyISAM 表的索引相关信息。对于 MyISAM 存储来说，可以被 cache 的内容主要就是来源于“.MYI”文件中。每一个MyISAM 表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。

MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件
（frm,myd,myi）。 每个表都有且仅有这样三个文件做为 MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI 文件中。

另外还有“.ibd”和 ibdata 文件，这两种文件都是用来存放 Innodb 数据的，之所以有两种文件来存放 Innodb 的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储 方式使用

“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和 MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用 ibdata  文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。

4.4数据库管理命令

SQL语言分类：
DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限（数据控制语句，用于控制不通数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如COMMIT、ROLLBACK、GRANT、REVOKE）

show databases;   //查看数据库

use 数据库名 

show tables;     //查看表

或者

show tables in mysql;  //也是查看表

describe 表名;   //查看数据表结构

4.4.1DDL数据定义语言，用于创建数据库对象，如库、表、索引等（create、drop、alter）

create database 数据库名 //创建新的数据库

create table 表名 (字段1 数据类型 ，字段2 数据类型)；

##创建表并定义它的属性

#主键一般选择能代表唯一性的字段不允许取空值（null），一个表只能有一个空值

示例：

create database admin2;

use admin2;

create table ky37(id int not null,name char(20) not null,score decimal(5,2),passwd char(60) default'',primary key (id));

desc admin2;

##not null      不允许为空值

##default''     默认值为空

##primary key   主键一般选择没有重复且不为空值的字段

删除指定的数据表

use admin2;


drop table ky37;
select * from ky37;

//这个是运行结果，发现表已经删除ERROR 1146 (42S02): Table 'admin2.ky37' doesn't exi

也可以用

crop table [数据库名.] 表名;

删除指定数据库

 crop database 数据库名;

4.4.2DML管理表的数据记录—— 数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据（insert 、update、delete）

格式一：

insert into 表名 （字段1，字段2，……）values(字段1的值，字段2的值，……)

格式二：

insert into 表名（字段1的值，字段2的值……）;

示例：

insert into ky37 (id,name,score ,passwd) values(1,'wam',20.3,password('123456'));

 ##这里password()是加密密码，如果加密密码的话会可能会导致长度过大，所以给字段属性设置长度的时候可以设置的大一点

 修改，更新数据库，表中的数据记录

update 表名 set 字段名1=字段值1，字段名2=字段值2 where 条件表达式

 实例：

update ky37 set name='qqq' where id=1;

在数据表中删除指定的数据记录

delete from 表名 where 条件判断语句

 示例：

delete from ky37 where id=1

 4.4.3DQL查询记录

格式：

select 字段名1，字段名2…… from 表名 where 条件判断语句；

 示例：

select * from ky37;  //查看表ky37的内容
select id,name,score from ky37 where id=1;  //查看表ky37 id为1的id，名字，成绩
select name from ky37\G;   //以列表方式竖向显示
select * from info limit 2; //只显示前两行
select * from info limit 2,3; //显示第二行后的前3行 

 4.5清空表

方法一：
delete from yyy3;
#DELETE清空表后，返回的结果内有删除的记录条目; 
DELETE 工作时是一行一行的删除记录数据的;如果表中有自增长字段，使用DELETE FROM 删除
所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。

方法二：
truncate table test01;
#TRUNCATE清空表后，没有返回被删除的条目: TRUNCATE 工作时是将表结构按原样重新建立，
因此在速度上TRUNCATE会比DELETE清空表快;使用TRUNCATE TABLE 清空表内数据后，
ID会从1开始重新记录

小结:
#删除类型
drop table table_name
1）属于DDL
2)不可回滚（无法恢复)
3)不可带where
4)表内容和结构删除
5)删除速度快

truncate table table_name
1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
5)删除速度快

delete from table_name
1)属于DML
2)可回滚(可恢复)
3)可带where
4)表结构在，表内容要看where执行的情况
5)册删除速度慢，需要逐行删除

不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

#删除
速度
drop> truncate > delete
安全性
delete 最好

4.6创建临时表，克隆表

格式：

create temporary table 表名 （字段1 数据类型，字段2 数据类型……）values(数值1，数值2，数值3……);

 示例：

create temporary table xxx (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
insert into xxx values (1,'zhangsan',123456,'running');
select * from test03;
show tables;
quit
select * from test03;

 #int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且
添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL

克隆表

 格式：

create table 旧表名 like 新表名;  //复制表的格式，属性
insert into 旧表名 select * from 新表名; //复制备份内容
##这时候会有主键

create table 旧表名（select * from 新表名）;  //复制旧表名到新表名中

 4.7DCL

修改表名和表结构
ALTER TABLE 旧表名 RENAME 新表名;

例：
ALTER TABLE zzz RENAME yyy;
ALTER TABLE yyy RENAME zzz;

扩展表结构(增加字段)
ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值为地址不详，可与NOT NULL配合使用
ALTER TABLE yyy ADD address varchar(50) NOT NULL default '地址不详';

修改字段(列)名，添加唯一键
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
unique key:唯一键（特性:唯一，但可以为空，空值只允许出现一次)   
Primary key:唯一且非空(主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。)

ALTER TABLE yyy CHANGE name user_name varchar(10) unique key;
#CHANGE可修改字段名、数据类型、约束等所有项。

ALTER TABLE 表名 modify column 字段名 类型。
数据库中表 字段是varchar(30)，修改类型可以用（谨慎修改类型，可能会导致原有数据出错）

删除字段
格式：
ALTER TABLE 表名 DROP 字段名;

示例：
ALTER TABLE yyy DROP passwd;

##案列扩展
use school;

示例：

create table if not exists info (
id int(4) zerofill primary key auto_increment,     #指定主键的第二种方式
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且
添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL