MySQL数据库管理（一）

一.数据库基本操作

1.查看数据库结构

1.1 查看数据库信息

show databases;

1.2 查看数据库中的表信息

#切换到书库中
use 数据库名；
#展示库中的表
show tables;
show tables in mysql;

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

describe user;

字段的各个部分含义：

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

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

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

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

2.数据库的存储方式

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

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

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

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

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

二.数据库管理

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

SQL语言分类：

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

1.DDL：数据定义语言

作用：用于创建数据库对象，如库、表、索引等

create——创建
drop——删除
alter——常用于对现有数据库元素进行修改

1.1 创建新的数据库

CREATE DATABASE 数据库名;

例：

create database blue;

1.2 创建新的表

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

示例：

create database blue;
use blue;
create table summer (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwwd char(48) DEFAULT' ',PRIMARY KEY (id));
desc summer;

注：

NOT NULL 不允许为空值
DEFAULT’ ’ 默认值为空

PRIMARY KEY :主键一般选择没有重复并且不为空值的字段

1.3 删除指定的数据表

drop

方法一：

use 数据库名
DROP TABLE 表名

方法二：

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

示例：

use qqbb;
create table ffbb (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwwd char(48) DEFAULFAULT' ',PRIMARY KEY (id));
desc ffbb;
drop table qqbb.ffbb;

1.4 删除指定的数据库

DROP DATABASE 数据库名;

示例：

drop database zz;

2.DML管理表中的数据记录

作用：数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据

insert ——插入
update——修改、更新数据表中的数据记录
delete——删除

2.1 INSERT在表中插入数据

（1）格式：

INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值,...);

SELECT * FROM zzz ;      #查询表的数据记录

（2）示例：

INSERT INTO summer (id,name,score,password) values(1,'zhangsan',70.5,PASS0.5,PASSWORD('123456'));

INSERT INTO summer (id,name,score,password) values(2,'lisi',90,PASSWORD(' '));

insert into summer (id,name,score) values(3,'cc',88.50);

select * from summer;

insert into summer values(4,'zjf',100,123456,null);

注：

PASSWORD(‘123456’):查询数据记录时，密码字串以加密形式显示:若不使用PASSWORD()，查询时以明文显示。

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

（1）格式:

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

（2）示例：

update summer set name='txbb' where id=3;
update summer set score='100' where id=3;
update summer set password=password('123') where name='txbb';

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

（1） 格式:

DELETE FROM 表名 [WHERE 条件表达式];

（2）示例：

delete from summer where id=4

3.DQL查询数据记录

select

（1）格式

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

（2）示例：

#查询数据表信息
SELECT * FROM summer;
SELECT id,name,score FROM summer WHERE id=2;
#以列表方式竖向显示
select name from summer\G 
#只显示头2行
select * from summer limit 2;
#显示第2行后的前3行
select * from summer limit 2,3;     

4.DCL 数据控制语言

用于设置或者更改数据库用户或角色权限（数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如COMMIT、ROLLBACK、GRANT、REVOKE）

4.1 修改表名和表结构

（1）格式：

ALTER TABLE 旧表名 RENAME 新表名;

（2）示例：

alter table summer rename tree;

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

（1）格式

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

（2）示例：

alter table tree add address varchar(50) default'地址不详';

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

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

ALTER TABLE tree CHANGE name user_name varchar(10) unique key;

#CHANGE可修改字段名、数据类型、约束等所有项。

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

4.4 删除字段

格式：

ALTER TABLE 表名 DROP 字段名;

示例：

alter table summer drop season;

总

drop删除和delete删除的区别
delete 和 drop 都是常用于删除数据的 SQL 关键字，它们的主要区别在于：

• 功能不同
  delete 用于从表中删除行或数据，其中可以根据特定条件删除选定的行或数据。delete 会将数据装入事务日志，并且能够回退操作。删除后还可以进行 rollback 操作，将删除的数据重新插入到表中。
  drop 用于删除整张表，也可以删除其他数据库对象，例如视图或索引。drop 操作是不可恢复的，也就是说，删除了对象就无法再恢复，因此在使用 drop 命令时应谨慎操作。
• 执行速度和效率不同
  由于不同的操作方式， delete 和 drop 对数据库性能和效率的影响也不同。
  delete 执行速度较慢，因为它需要遍历整个表来查找要删除的行
  drop 执行速度较快，因为它仅需要删除表和表的元数据，不会对表数据进行查找或删除。
  因此，如果您只需要删除几行记录，可以使用 delete，但是如果您要删除整张表或其他数据库对象，则应该使用 drop。

总之，delete 和 drop 都是一些常见的 SQL 关键字，对于使用它们进行数据操作时，应当根据具体情况选择合适的方法。