(二) 初入MySQL 【数据库管理】

一、SQL语言分类

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

1.1、DDL

数据定义语言，用来定义数据库对象（数据库，表，字段）

关键词create   drop ,truncate    alter，

➢创建

1、创建新的数据库

create  database  数据库名;

2、创建新的表

• 主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。可以用在最后用  ,  primary key（主键名）定义，也可以写在数据类型之后的 primary key。

create  table  表名 (字段1  数据类型,字段2   数据类型, primary key (主键名)  );

1.1.1、常用的数据类型

注意：

主键是唯一的，但主键可以由多个字段构成

char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错。

➢删除

drop database 数据库名;

drop table  [数据库名.]表名

truncate table 表名;

详细请看二

➢修改表名和表结构

alter table  表  rename   新表;

➢扩展表结构(增加字段)

alter  table  表  add  新字段   varchar(20)   default   '地址不详';

➢修改字段(列)名，添加唯一键

alter  table  表名 change  旧列名   新列名  想要修改的数据类型   [unique key];

可以选

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

但是数据不能冲突，比如数据内有不唯一数据就不可以，unique key；

alter table  表名 modify column 字段名   想修改的类型。

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

➢删除字段

alter table 表名 drop  字段名;

 补充

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

1.2、DML

管理表中的数据记录----数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据
关键词 insert   update    delete

➢插入

insert  into  表名(字段1,字段2)  values(字段1的值，字段2的值,...);

或者

insert  into  表名   values(字段1的值，字段2的值,...);

示例:
INSERT INTO KY13  (id,name,score,passwd) values(1,'zhangsan',70.5,PASSWORD('123456'));
#PASSWORD('123456'):查询数据记录时，密码字串以加密形式显示:若不使用PASSWORD()，查询时以明文显示。

➢修改，更新

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

➢删除

delete from  表名   where  条件表达式;

1.3、DQL

DQL：数据查询语言，用于从数据表中查找符合条件的数据记录

关键词select 、show

show属于DQL属于查询

➢查询

查询所有数据库

show databases;

进入数据库

use xxxxx;

查询所有表

show tables;

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

describe 表;

或者简写 desc  表；

➢查询

select   字段名1,字段名2     from   表名    where 条件表达式;

示例：
SELECT * FROM zzz;
SELECT id,name,score FROM zzz WHERE id=2;
select name from zzz\G           #以列表方式竖向显示
select * from info limit 2;        #只显示头2行
select * from info limit 2,3;     #显示第2行后的前3行

查看当前库

select database();

1.4、DCL

DCL：数据控制语言，用于设置或者更改数据库结构、用户或角色权限

查看博客mysql（三）
 

二、数据表删除

数据很重要，删除请谨慎

清空表的两种方法，删除表内的所有数据
方法一：

delete from 表名;

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

方法二：

truncate table 表名;

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

删除总结

速度       ：drop> truncate > delete
安全性   ：delete 最好

删除之前可以用select一下，查看内容（delete换成select）删除之前可以报备。

总结：

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

三、创建临时表

临时表创建成功之后，使用 show tables；命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以可执行增删改查等操作，比如使用drop table语句手动直接删除临时表。

PS：临时表无法创建外键

关键词：temporary
create temporary table  表名 (字段1 数据类型,字段2 数据类型, PRIMARY KEY (主键名));

3.1、sql操作过程

sql 写入——》数据表中—》先把数据保存在内存中—》写入到磁盘
insert into xxx——》xxx这张表，会先复制一份表数据到内存里面，给我们进行修改---》

敲完回车之后，确定提交了，才会写入数据表中—》再保存在磁盘里面

create table test01—》只会保存在内存中，在数据库退出连接之前的所有操作，都是在内存中进行的，不会保存在磁盘里面，退出连接后，临时表会释放掉

示例:
 

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 xxx;（可以发现有数据）
show tables;（没有存在临时表）
quit  （退出）
select * from xxx; （数据不存在）

• int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001
• primary key 是一个字段（或字段的组合），用于唯一地标识表中的每一行。每个表只能有一个主键，且主键的值必须唯一，不能包含NULL值。主键的主要作用是帮助数据库快速定位到表中的某一行记录，同时也保证了数据的完整性。
• auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
• 自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
• unique key：表示此字段唯一键约束，此字段数据不可以重复（就像确保身份证号是一样）一张表中只能有一个主键，但是一张表中可以有多个唯一键
• not null：表示此字段不允许为NULL

四、克隆表

方法一

复制格式，通过LIKE方法，复制A表结构生成B表

#只有表结构

create table B like A;    

#然后备份内容

insert into B select * from A;      

方法二

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

#复制test 表数据到test02中

craeate table   test02 (select * from test);  

#获取数据表的表结构、索引等信息  \G是显示横向的，创建过程

show create table test02\G；   
 

五、整体流程

primary key       主键 ，也可以写在外边

not null              不为空

decimal              精确数值类型

describe             描述表;

temporary          临时的

truncate              截断

auto_increment  自增量（必须为主键）

drop                    减少

unique  key         唯一键

if not exists

1、查看所有的数据库

2、创建一个数据库AAA （进入，查看在不在）

3、创建一个表BBB

内容为

id     数字20  不为空，主键，自增

name  字符串

sex     字符串 默认值为 空

passwd  字符串（50）

cardid    数字  唯一键

4、查看表属性，字段

5、插入数据

1  xiaoming   男   密码加密123   身份证 666666

2  lisi   女        密码加密321   身份证 666667

1  wangwu   男   密码加密666   身份证 666668

6、查看表内容（只看男生）

7、删除第一条数据

8、改变第id为2的数据的密码为456（明文）

9、查看表内容

10、克隆表（2种方式）CCC

11、查看所有表

12、查看表内容

13、修改表名为DDD和表结构

14、删除表（三种方式）DDD

15、创建一个临时表

create table 表（字段 数据类型 状态）insert into 表 values（）delete from 表 whereupdate   表名  set   字段名=字段值  where  条件表达式;  select * fromalter table 表 rename 新表;字段的扩展alter table 表 add 新字段 varchar(20) default '地址不详';修改alter table 表名 change 旧列名 新列名 想要修改的数据类型 [unique key];删除字段alter table 表名 drop 字段名;（谨慎）修改类型alter table 表名 modify column 字段名 想修改的类型。克隆1、创建  like   （复制表结构）加  插入 查询2、另一个是创造的时候查询想要克隆的表内容删除表（三种方式）drop   table      （整体表删除）truncate  table（只能删除数据，表结构还在）delete     （条件删除）

1

2

3

4、

5、

6、

7

8、

9

10、11、

也可以

12

13、

14

15、

退出就临时表就消失了 

六、MySQL数据库的数据文件

扩展
MySQL数据库的数据文件存放在/usr/local/mysql/data目录下，每个数据库对应一个子目录，用于存储数据表文件。

每个数据表对应为三个文件，扩展名分别为“.frm”、“.MYD”和“.MYI”。

• MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起。
• “.MYI”文件也是专属于 MyISAM 存储引擎的，主要存放 MyISAM 表的索引相关信息，索引，对于 MyISAM 存储来说，可以被 cache 的内容主要就是来源于“.MYI”文件中。每一个MyISAM 表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。
• frm就是存储熟悉列的

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

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