MySQL数据库（2）管理

一、MySQL数据库管理

1.1库和表

 

数据库–>数据表–>行（记录）：用来描述一个对象的信息

_______________ 列（字段）：用来描述对象的一个属性

常用的数据类型

 

int ：整型

float ：单精度浮点 4字节32位

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

char ：固定长度的字符类型

varchar ：可变长度的字符类型

text ：文本

image ：图片

decimal(5,2) ：5个有效长度数字，小数点后面有2位

二、查看数据库结构

2.1 查看数据库结构

 

1．查看当前服务器中的数据库

 

SHOW DATABASES; #大小写不区分，分号“;”表示结束

2．查看数据库中包含的表

USE 数据库名;

SHOW TABLES;

查看mysql数据库中的表

3．查看表的结构（字段）

 

USE 数据库名;

DESCRIBE [数据库名.]表名;

可缩写成：DESC 表名;

 

相对路径查看：

绝对路径查看

4. 查看mysql版本

 

select version(); #在mysql内查看mysql版本

mysql -V #在linux系统中查看mysql版本

2.2 SQL语句

 

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

 

SQL语句分类：

语句 代表的意思

DDL 数据定义语言，用于创建数据库对象，如库、表、索引等

DML 数据操纵语言，用于对表中的数据进行管理

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

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

2.3 创建及删除数据库和表

1） 创建新的数据库

CREATE DATABASE 数据库名;

2） 创建新的表

 

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

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

 

例：

 

CREATE DATABASE ali;

USE ali;

create table market(id int(4) NOT NULL, name char(10), age int(3), PRIMARY KEY(id));

DESC market;

3） 删除指定的数据表

 

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

 

删除方法一：进入目标库

删除方法二：在要删除的表前面加上库名

4） 删除指定的数据库

DROP DATABASE 数据库名;

2.4 管理表中的数据记录

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

 

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

例：

insert into market(id,name,age,passwd) values(2,‘lisi’,20,PASSWORD(‘112233’));

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

 

INSERT INTO KY08 VALUES(2,'lisi',90.5,654321);

 

2） 查询数据记录

 

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

 

例：查询全部数据

 

SELECT * FROM market;

例：查询指定数据

 

select id,name from market where id=4;

例：以列表方式竖向显示

 

select from market\G;

例：只显示头2行

select * from market limit 2;

例：显示第2行后的前2行

select * from market limit 2,2;

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

 

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

例：修改age

 

update market set age=19 where id=4;

update market set age=20,passwd='111111' where id=4; #同时修改age和passwd

 

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

 

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

 

例：删除当前表中id为4的数据

delete from market where id=4;

2.5 修改表名和表结构

1） 修改表名

 

ALTER TABLE 旧表名 RENAME 新表名;

 

例：

alter table market rename sales;

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

 

ALTER TABLE 表名 ADD address varchar(50) default '地址未填写！';

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

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

 

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

 

例：修改字段并添加唯一键，然后创建数据测试。

 

alter table market change name user_name char(30) unique key;

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

 

4） 删除字段

ALTER TABLE 表名 drop 字段名;

例：删除表中 passwd字段

ALTER TABLE market drop passwd;

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

 

use ali; #先进入指定的库

 

create table if not exists yy (

id int(4) zerofill primary key auto_increment,

name char(20) not null,

sid int(18) not null unique key,

age 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

测试：创建数据测试自动补齐和自增长。

测试：数据创建失败，id的补齐和自增情况

三、数据表高级操作

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

3.1.1 方法一：先创建再导入

 

create table test01 like info; #通过 LIKE 方法，复制 info 表结构生成 test01 表

insert into test01 select * from info; #导入数据

 

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

 

create table test02 (select * from info);

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

 

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

3.2.1 方法一：deldte删除

 

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

 

delete from info; #delete from 表名；

3.2.2 方法二：truncate删除

 

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

 

truncate table info;

3.2.2 方法三：创建临时表

 

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

 

##添加临时表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.3 创建外键约束

create table pro (pid int, pname char(20));

create table student (id int, name char(10), age int, proid int);

alter table pro add constraint PK_pid primary key (pid);

alter table student add constraint FK_proid foreign key (proid) references pro (pid);

pro表结构如下：

student表结构如下：

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

 

#为子表添加一个主键约束。主键名建议以“PK_”开头。

 

alter table pro add constraint PK_pid primary key (pid);

 

#为 branch 表添加外键，并将 student表的 proid 字段和 pro 表的 pid 字段建立外键关联。外键名建议以“FK_”开头。

 

alter table student add constraint FK_proid foreign key (proid) references pro (pid);

 

测试删除：从表的数据未删除，主表的数据无法删除

 

查看和删除外键约束：

 

desc test03;

show create table student\G #查看表约束

 

alter table student drop foreign key FK_proid; 解除外键约束

alter table student drop key FK_proid; 删除外键

desc student;