第二章数据库基本操作

第二章数据库基本操作

0 目标

掌握数据库的创建、查看、选择与删除操作
掌握数据表的创建、查看、修改与删除操作
掌握数据的添加、查询、修改与删除操作

1 数据库操作

创建数据库

DBMS 数据库管理系统
DB 数据库
data 数据

操作

create database s数据库名称[库选项];

数据库名称：由字母、数字和下划线组成的任意字符串。
库选项：用于设置此数据库的相关特性，如字符集charset

例如

create database mydb;

-- 打开data/mydb/db.opt
default-character-set=latin1
default-collaction=latin1_swedish_ci

create database if not exists mydb;

防止创建的数据库已存在，否则程序会报错。
创建的数据库已存在，返回警告信息。

-- 查看警告信息
show warnings;

查看数据库

查看MySQL服务器下所有数据库
```
show databases;
```

查看指定数据库的创建信息
```
show create database 数据库名
```

选择数据库

use 数据库名;

删除数据库

drop database 数据库名;
drop database if exists 数据库名;

2 数据表操作

创建数据表

create [temporary] table [if not exists] 表名 (字段名 字段类型[字段属性]...)[表选项]

字段名：是数据表的列名
字段类型：字段中保存的数据
temporary：可选，表示创建临时表，仅在当前会话中可见。
字段属性：可选，字段的某些特殊约束条件。
表选项：可选，用于设置表的相关特性，如存储引擎(ENGINE)

举例

create table goods (
    id int comment '编号',
    name varchar(32) comment '商品名',
    price int comment '价格',
    description varchar(255) comment '商品描述'
);

int：设置字段数据类型是整型
varchar(L)：表示可变长度的字符串，L表示字符数，如varchar(32)表示可变的字符数是32；
comment：创建表时添加注释内容，并将其保存到表结构中。

注意：

数据表的名称，一般选用数据库前几个字母作为前缀。
创建数据表时，要选择数据库。
选择数据库可使用use [database]或database.table。

查看数据表

show tables;

查看数据表的状态信息

show table status;

匹配模式

show tables [like 匹配模式];

省略可选项，表示查看当前数据库中的所有数据表。
添加可选项，则按照“匹配模式”查看数据表。
匹配模式符%匹配一个或多个字符，代表任意长度的字符串。
匹配模式符_仅可以匹配一个字符。

在mydb数据库下再创建一张数据表(演示案例)

create table new_goods (
    id int comment '编号',
    name varchar(32) comment '商品名',
    price int comment '价格',
    description varchae(255) comment '商品描述'
);

示例

-- 查看所有数据表
show tables;
-- 查看名称中含有new的数据表
show tables like '%new%';

使用匹配模式查看数据表状态

show table status [from 数据库名][like 匹配模式]

省略可选项，表示查看当前数据库中的所有数据表的状态。
添加可选项，则按照“匹配模式”查看数据表的状态。

示例

show table status from mydb like '%new%'\g;

修改数据表

修改数据表名称

-- 语法格式1
alter table 旧表名 rename [to|as] 新表名;
-- 语法格式2
rename table 旧表名1 to 新表名1[,旧表名2 to 新表名2]...;

alter table...rename后的to或as可省略。
rename table...to可以同时修改多个数据表的名称。

示例

将new_goods表名修改为my_goods

rename table new_goods to my_goods;
-- 查看修改结果
show tables;

修改数据表选项

alter table 表名 表选项 [=] 值;

常见的表选项有字符集、存储引擎以及校对集。

示例

-- 将my_goods表的字符集改为utf8
alter table my_goods charset = utf8;
-- 查看修改结果
show create table my_goods \G

查看表结构

查看数据表的字段信息

-- 语法1:查看所有字段的信息
{describe|desc} 数据表名;
-- 愈发2:查看指定字段信息
{describe|desc} 数据表名 字段名;

describe语句可以简写为desc。
desc可以查看数据表的字段信息也可以查看指定字段的信息。

示例

-- 所有字段
desc my_goods;
-- name 字段
desc my_goods name;

Field表示字段名称
Type表示字段的数据类型
Null表示该字段是否可以为空
Key表示该字段是否已设置了索引
Default表示该字段是否有默认值
Extra表示获取到的与该字段相关的附加信息

查看数据表的创建语句

show create table 表名

课查看创建数据表的具体SQL语句
可查看数据表的字符编码

示例

show create table my_goods \g

table表示查询的表名。
create table 表示创建该数据表的SQL语句。

查看数据表结构

-- 语法1
show [full] columns from 数据表名 [from 数据库名];
-- 语法2
show [full] columns from 数据库名.数据表名;

省略可选项full，查询结果与desc语法相同。
添加可选项full，可以额外查看字段权限、comment字段注释等。
上述语法中，数据表名from数据库名与数据库名.数据表名等价。

示例

show full columns from my_goods;

Field表示字段名称
Type表示字段的数据类型
Null表示该字段是否可以为空
Key表示该字段是否已设置了索引
Default表示该字段是否有默认值
Extra表示获取到的与该字段相关的附加信息
Collation 校对集
Comment 注释信息
Privileges 权限信息

修改表结构

修改字段名

alter table 数据表名 change [column] 旧字段名 新字段名 字段类型[字段属性];

旧字段名:指字段修改前的名称。
新字段名:指字段修改后的名称。
数据类型:表示新字段名的数据类型，不能为空，即使与旧字段的数据类型相同，也必须重新设置。

示例

将my_goods数据表中名为description的字段修改为des

alter table my_goods change description des varchar(255);

修改字段类型

alter table 数据表名 modify [column] 字段名 新类型[字段属性];

在MySQL中仅修改数据表中的字段类型，通常使用modify实现。

示例

将my_goods数据表中des字段的类型varchar(255)改为char(255)

alter table my_goods modify des char(255);

修改字段位置

alter table 数据表名
modify [column] 字段名1 数据类型[字段属性][first|after 字段名2];

first：表示将字段名1调整为数据表的第1个字段。
after 字段名2：表示将字段名1插入到字段名2的后面。

示例

将my_goods表中最后一个字段des移动到name字段后

alter table my_goods modify des varchar(255) after name;

新增字段

-- 1 新增一个字段，并可指定其位置
alter table 数据表名
add [column] 新字段名 字段类型 [first|after 字段名];
-- 2 同时新增多个字段
alter table 数据表名
add [column] (新字段名1 字段类型1,新字段名2 字段类型2,...);

在不指定位置的情况下，新增的字段默认添加到表最后。
新增多个字段时不能指定字段位置。

示例

在my_goods数据表中字段name后新增一个num字段，表示商品的数量。

alter table my_goods add num int after name;

删除字段

alter table 数据表名 drop [column] 字段名;

删除字段指的是将某个字段从数据表中删除。

示例

删除my_goods表中num字段

alter table my_goods drop num;

删除数据表

drop [temporary] table [if exists] 数据表1 [,数据表2]...;

删除数据表操作指的是删除指定数据库中已经存在的表。
在删除数据表的同时，存储在数据表中的数据都将被删除。
同时删除多个数据表时，多个数据表之间使用逗号分隔。
可选项if exists用于在删除一个不存在的数据表时，防止产生错误。

3 数据操作

添加数据

为所有字段添加数据

insert [into] 数据表名 {values|value}(值1[,值2]...);

严格按照数据表结构(字段的位置)插入对应的值。
值列表“值1[,值2]…”中多个值之间使用逗号分隔。

示例

为goods表添加一条商品记录

insert into goods
values (1,'notebook',4998,'High cost performance');

为部分字段添加数据

-- 1
insert [into] 数据表名 (字段名1[,字段名2]...)
{values|value} (值1[,值2]...);
-- 2
insert [into] 数据表名 set 字段名1 = 值1 [,字段名2 = 值2]...;

字段名必须与数据相对应
insert [into]...set一次只能添加一条记录。

示例

为goods表添加一条商品记录

insert into goods (id,name) values (3,'Mobile phone');

一次添加多行数据

insert [into] 数据表名 [(字段列表)] {values|value}(值列表)[,(值列表)]...;

多个值列表之间使用逗号(,)分隔。
省略字段列表时，插入数据需要严格按照数据表创建的顺序插入。
添加字段列表时，值列表插入的数据仅需与字段列表中的字段相对应即可。

示例

insert into goods values
(1,'notebook',4998,'High cost performance'),
(2,'笔记本',9998,'续航时间超过10个小时'),
(3,'Mobile phone',null,null);

在多数据插入时若一条数据插入失败，则整个插入语句都会失败。

注意：

前提条件：默认情况下，MySQL中若创建的数据表未指定子符集，数据表及表中的字段将使用latin1.

产生问题：若用户插入的数据含有中文，则会产生错误。

解决方案：

创建数据表时设置字符集

create [temporary] table [if not exists] 表名 (字段名 字段类型[字段属性]...) [default] {character|set|charset}[=]utf8;

修改已经创建的数据表中对应的字段

alter table...modify 字段名 数据类型 character set utf8;
alter table...change 字段名 字段名 数据类型 character set utf8;

示例

修改goods表中name和description字段的字符集

alter table goods
modify name varchar(32) character set utf8,
modify description varchar(255) character set utf8;

查询数据

查询表中全部数据

select * from 数据表名;

星号*通配符代替数据表中的所有字段名。

示例

查看goods表中插入的全部数据

select * from goods;

查询表中部分字段

select {字段名1,字段名2,字段名3,...} from 数据表名;

示例

查询goods表中的id和name字段中的数据

select id,name from goods;

简单条件查询数据

select * | {字段名1,字段名2,字段名3,...}
from 数据表名 where 字段名 = 值;

示例

查询goods表中id为1的数据

select * from goods where id = 1;

修改数据

update 数据表名
set 字段名1 = 值1 [,字段名2 = 值2,...][where 条件表达式];

有where条件，修改符合要求的对应字段
无where条件，修改表中所有对应的字段。因此读者在修改数据时，请谨慎操作。

示例

将goods表中编号为2的商品价格由9998元修改为5899元

update goods set price = 5899 where id = 2;
-- 查看結果
select * from goods where id = 2;

刪除数据

delete from 数据表名 [where 条件表达式];

有where条件，删除符合要求的记录。
无where条件，删除表中所有的记录。因此读者在修改数据时，请谨慎操作。

示例

删除goods表中编号的等于3的商品数据

delete from goods where id = 3;
-- 查看結果
select * from goods;

第二章 数据库基本操作

第二章 数据库基本操作

0 目标

1 数据库操作

创建数据库

查看数据库

选择数据库

删除数据库

2 数据表操作

创建数据表

查看数据表

查看数据表的状态信息

匹配模式

使用匹配模式查看数据表状态

修改数据表

修改数据表名称

修改数据表选项

查看表结构

查看数据表的字段信息

查看数据表的创建语句

查看数据表结构

修改表结构

修改字段名

修改字段类型

修改字段位置

新增字段

删除字段

删除数据表

3 数据操作

添加数据

为所有字段添加数据

为部分字段添加数据

一次添加多行数据

查询数据

查询表中全部数据

查询表中部分字段

简单条件查询数据

修改数据

刪除数据

第二章数据库基本操作

第二章数据库基本操作