49mysql数据库管理

mysql数据库管理

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

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

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

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

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

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

数据库基本操作

大部分SQL操作命令必须以；结束

查看数据库信息

show database；

查看数据库中的表信息

use数据库名；#切换到数据库中
show tables；

或者show tables in mysql；

显示数据表的结构（字段）

describe [数据库名]表名；

describe user;

Field:字段名称
type:数据类型
Null:是否允许为空
Key:主键
Type:数据类型
Null:是否允许为空key:主键
Default:默认值
Extra:扩展属性，例如：标志符列（标识了种子，增量/步长）12

SQL语句概述

SQL语言

Structured Query Languagel的缩写，即结构化查询语言，关系型数据库的标准语言，用于维护管理数据库:包括数据查询、数据更新、访问控制、对象管理等功能。

SQL分类

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

DDL

1、创建新的数据库

CREATE DATABASE数据库名；

2、创建新的表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值(NULL),一个表只能有一个主键。
use hobby;
create table syw(id int(6) not null,name char(10) not null,primary key(id))
desc syw;

拓展：常用的数据类型

int:整型，用于定义整数类型的数据
float:单精度浮点4字节32位，准确表示到小数点后六位
double:双精度浮点8字节64位
char:固定长度的字符类型，用于定义字符类型数据。
varchar:可变长度的字符类型
text:文本
image:图片
decimal(5,2):5个有效长度数字，小数点后面有2位

3、删除指定的数据表

#如不用USE进入库中，则需加上数据库名

DROP TABLE [数据库名.]表名;

4、删除指定的数据库

DROP DATABASE数据库名;

DML

管理表中的数据记录——数据操纵语言，用于对表中的数据进行管理，用来插入、删除和修改数据库中的数据。
INSERT:插入新数据
UPDATE:更新原有数据
DELETE：删除不需要的数据

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

格式：
insert into 表名（字段1，字段2，字段3） values（字段1的值，字段2的值，字段3的值）；

#示例1：可以指定字段添加
insert into xjw(id,name,score,passwd) values(1,'aaa',10,123);      
#示例2：直接使用表明加入字段
insert into xjw values(3,'苹果',34,123);
#示例3：密码加密
insert into xjw(id,name,score,passwd) values(5,'香蕉',55,password('12343'));

2、修改、更新数据表中的数据记录

格式
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

#示例1：
update xjw set name='猕猴桃' where id=1;
#示例2：通过直接修改数据库的方式，实现修改数据库管理员密码
update mysql.user set authentication_string=PASSWORD('123457)
where user='root';

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

格式
DELETE FROM 表名 WHERE条件表达式

#示例1：
delete from wtf where id=1;
#示例2：
不带where条件的语句表示删除表中所有记录
delete from auth.users;

DQL

DQL是数据查询语句，只有SELECT，用于从数据表中查找符合条件的数据记录，查询时可不指定条件。

查询表的数据记录

#格式
SELECT 字段名1,字段名2[,…] FROM 表名 [WHERE 条件表达式];

#示例1：查看所有数据
select * from info;
#示例2：查询指定数据
select name from info where id=1;
#示例3：以列表方式竖向显示
select * from info \G;
#示例4：只显示头2行
select * from info limit 2;
#示例5:显示第2行后的前3行
select * from info limit 2,3;

DCL

修改表名和表结构

1、修改表名

格式
ALTER TABLE 旧表名 RENAME 新表名;

#示例：将表名wtf修改成hhh
alter table wtf rename hhh;

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

格式
ALTER TABLE 表名 ADD address varchar(50) default ‘default值’;

#default ‘default值’：表示此字段设置默认值 地址未填写！；可与 NOT NULL 配合使用

#示例
alter table hhh add address char(50) default'活该';

3、修改字段（列）名，添加唯一键

格式
ALTER TABLE表名CHANGE I旧列名新列名数据类型[unique key];

alter table hhh change score sz char(50) unique key;

unique key:唯一键（特性：唯一，但可以为空，空值只允许出现一次）
Primary key:唯一且非空（主键一般选择能代表唯一性的字段不允许取空值(NULL),一个表只能有一个主键。）

4、删除字段

#格式
ALTER TABLE 表名 drop 字段名;

#示例
alter table hhh drop sz;

数据表高级操作

克隆表

将数据表的数据记录生成到新的表中

法一：先创建再导入
#通过 LIKE 方法，复制 info 表结构生成 test01 表
create table hhh01 like hhh;	
#导入数据
insert into hhh01 select * from hhh;

法二：创建的时候同时导入
create table test02 (select * from info);

清空表，删除表内的所有数据

方法一：

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最好

创建临时表

临时表创建成功之后，使用SHOWTABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
如果在退出连接之前，也可以可执行增删改查等操作，比如使用DROP TABLE语句手动直接删除临时表。
PS:无法创建外键
CREATE TEMPORARY TABLE 表名（字段1数据类型，字段2数据类型[，···] [,PRIMARY KEY(主键名)])：

sql写入一》数据表中一》先把数据保存在内存中-》写入到磁盘
insert into info一》info这张表，会先复制一份表数据到内存里面，给我们进行修改一》敲完回车之后，确定提交了，才会写入数据表中一》再保存在磁盘里面。
create table tst01-》只会保存在内存中，在数据库退出连接之前的所有操作，都是在内存中进行的，不会保存在磁盘里面，退出连接后，临时表会释放掉。

##添加临时表test
create temporary table test (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 test values(1,'大脑胡',123456789,'吃西瓜');	
##查看当前表中所有数据
select * from test;
##退出数据库
quit					
##重新登录后进行查看		
mysql -u root -p
##查看之前创建的临时表中所有数据，发现已经被自动销毁
select * from test;		

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