MySQL数据库的基本管理操作

引言

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

一、基本概念

（1）MySQL数据库常用数据类型

常用的数据类型

int：整形                                               用于定义整数类型的数据

float：单精度浮点4字节32位                准确表示到小数点后六位

double：双精度浮点8字节64位           

char：固定长度的字符类型                  用于定义字符类型数据

varchar：可变长度的字符类型

text：文本

image：图片

decimal（5，2）：5个有效长度数字，小数点后面有两位            指定长度数组

（2）MySQL数据库的数据文件

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

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

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

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

另外还有"ibd"和 ibdata文件，这两种文件都是用来存放Innodb数据的，之所以有两种文件来存放Innodb
的数据(包括索引)，是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储
方式使用"ibd"文件来存放数据，且每个表一个"ibd"文件，文件存放在和MYISAM.数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata文件

二、MySQL基本操作

1、登录MySQL

第一种
mysql -uroot -p123456   ### -p后面加密码这种方式不安全在任意泄漏密码

第二种
mysql -uroot -p    #回车
123456             #在输入密码，相对安全  

2、查看数据库信息

mysql> show databases;

3、进入数据库查看数据表

（1）进入数据库

mysql> use mysql;

（2）查看数据库中的表

mysql> show tables;

 （3）查看表结构

mysql> describe  user;

 数据表结构字段含义

Field：字段名称

type：数据类型

Null：是否允许为空

Key：主键

Type: 数据类型

Null：是否允许为空Key：主键

Default：默认值

Extra：扩展属性，例如：标志符列（标识了种子，增量/步长）1 2

id：1 3 5 7

加\G查看表结构

 绝对路径查看表结构

 4、查看mysql的版本

#在mysql内查看mysql版本
select version();
 
#在linux系统中查看mysql版本
mysql -V

（1）在mysql内查看mysq版本

（2）在linux查看mysql版本

 三、SQL语句

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

SQL语句分类：

语句	代表的意思
DDL	数据定义语言，用于创建数据库对象，如库、表、索引等（create ）
DML	数据操纵语言，用于对表中的数据进行管理(insert drop delete update )
DQL	数据查询语言，用于从数据表中查找符合条件的数据记录(select )
DCL	数据控制语言，用于设置或者更改数据库用户或角色权限(grant )

1、创建及删除数据库和表

（1）创建数据库

 CREATE DATABASE 数据库名; 
 
 #示例：创建一个名为yuangong的数据库
 creat database yuangong;

 （2）创建新的表

#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

#示例：
use yuangong;
create table info(id int(4) not null,name char(10),age int(3),hobby char(20),primary
key(id))
desc info;

 （3）删除指定的数据表

#如不用USE进入库中，则需加上数据库名
DROP TABLE [数据库名.]表名;				

#法一：使用绝对路径，需要进入表所在数据库
use hobby;
drop table info;
desc info;

#法二：使用相对路径，不需要进入表所在数据库
drop table student.info;
show tables in student;

 （4）删除指定的数据库

DROP DATABASE 数据库名;

 2、管理表中的数据记录

（1）向数据表中插入新的数据记录

#向数据表中插入新的数据记录
insert into 表名（字段1，字段2，字段3） values（字段1的值，字段2的值，字段3的值）；
 
#示例1
insert into info(id,name,age,hobby) values(2,'yxp',18,'dance')
select * from info;
 
#示例2：直接使用表明加入字段
insert into info values(1,'dhc',20,'basketball')
 
#示例3：可以指定字段添加
insert into info(id,name,age) values(3,'dhc',20)

 示例一

 示例2；直接使用表明加入字段

示例3：可以指定字段添加

 （2）查询数据记录

#格式
SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];
 
#示例1：查看所有数据
select * from info;
 
#示例2：查询指定数据
 select name,hobby from info where id=1;
 
#示例3：以列表方式竖向显示
select * from info \G;
 
#示例4：只显示头2行
select * from info limit 2;
 
#示例5:显示第2行后的前2行

 示例1：查看所有数据

 示例2：查询指定数据

 示例3：以列表方式竖向显示

示例4：按行查找

 示例5:显示第2行后的前2行

 （3）修改、更新数据表中的数据记录

#格式
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
 
#示例1：修改姓名和年龄
update info set name='xg',age=35 where id=1;

示例1：修改姓名和年龄

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

#格式：
DELETE FROM 表名 [WHERE 条件表达式];
 
#示例：删除表中id为4的数据
delete from info where id=4

 （5）修改表名·和表结构

1、修改表名

ALTER TABLE 旧表名 RENAME 新表名;
 
#示例：将表名info修改成base

示例：将表名info修改成base

 2、扩展表结构（增加字段）

#default 'default值'：表示此字段设置默认值 地址未填写！；可与 NOT NULL 配合使用
ALTER TABLE 表名 ADD address varchar(50) default 'default值';
 
#示例
alter table base add address char(100) default'china'

 3、修改字段名。添加唯一键

#格式
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
#示例：
alter table base change age user_age int(4) unique key;

 4、删除字段

#格式
ALTER TABLE 表名 drop 字段名;
#示例
alter table base drop user_age;

 （6）使用if判断创建表并测试自增和填充

use hobby;
 
create table if not exists info.bak (
id int(4) zerofill primary key auto_increment,
name char(20) not null,
sid int(18) not null unique key,
ge int(3) not null,
address char(100));

分析：

    if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建

    int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001

    auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次

    unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键

    not null：表示此字段不允许为NULL

 测试

 四、数据表高级操作

1、克隆表——将数据表的数据记录生成到新的表中

方法一：先创建再导入

#通过 LIKE 方法，复制 info 表结构生成 test01 表
create table test01 like info;		
#导入数据
insert into test01 select * from info;	

 

方法二：创建的时候同时导入

create table test02 (select * from info);

 #获取数据表的表结构、索引等信息
show create table test01\G;

 2、清空表———删除表内的所有数据

（1）deldte删除

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

#delete from 表名；
delete from info;					

 （2）truncate删除

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

truncate table info;

 （3）创建临时表

##添加临时表test03
create temporary table test03 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));
 
## 查看当前库中所有表
show tables;	
##在临时表中添加数据
insert into test03 values(1,'zhangsan',123456789,'watch a film');	
##查看当前表中所有数据
select * from test03;
##退出数据库
quit						
 
##重新登录后进行查看		
mysql -u root -p
##查看之前创建的临时表中所有数据，发现已经被自动销毁
select * from test03;			

 

 3、创建外键约束

（1）创建主键pro1表

主键：pro1表结构如下：

（2）创建子表stu

子表stu表结构如下;

（3）设置主表主键与外表外键关联

 与外键关联的子表的字段必须设置为主键。要求主表外键字段和子表的字段具备相同的数据类型、字符长度和约束。

#为主表设置主键
alter table pro add primary key(id);
#为从表设置外键
alter table stu add foreign key (proid) references pro(pid);

（4）测试1

测试：此时不可以删除主表，因为主表被外表引用，要查看和删除外键约束 ，如果要删除外键约束字段先删除外键约束，再删除外键名

（5）测试2

 测试：删数数据记录时，要先从表再主表，也就是说删除主键表时必须先删除其他与之关联的表。

 

 总结

本文主要列举了一些mysql的基本操作。列如增加库、表，删除库、表。表中的属性定义，数据的增删改查，以及表的简单关联。