数据库的基本操作(一)

---

数据库的基本操作

数据库 表：create增，drop删、alter改，show create（databases/table）
数据：insert增 delete删 update改 select查

---

一、数据库的基本操作

1.1 数据库的基本概念

什么是数据库？

数据库可以看做是存储数据对象的一个容器，
是数据库管理系统的核心和基础，
不同数据库中的数据对象相对隔离

在mysql中，数据库可以分为系统数据库和用户数据库两大类.
系统自带的数据库，和MySQL相关的数据
用户数据库，和用户自身定义的数据相关的数据库

查看当前拥有的数据库语句：

Show databases;

1.2 创建数据库

创建数据库语法

Create database 数据库名[ character set 字符集]

数据库名：

• 一般由字母和下划线组成，不允许有空格，可以是英文单词、英文短语或相应缩写；

• 不允许是MySQL关键字；

• 长度最好不超过128位；

• 不能与其他数据库同名

[例1.2.1]创建数据库db_shop。

Create database db_shop；

查看指定数据库的创建信息语法

Show create database db_shop；

[例1.2.2]：查看db_shop的创建信息

Show create database db_shop；

1.3 修改数据库

修改数据库语法：

Alter database 数据库名 ｛character set 字符集 | collate 校对规则｝

[例1.3.1]：修改db_shop，将字符集由utf8变成latin1

Alter database db_shop character set latin1；

1.4选择数据库

[例1.4.1 ]两个数据库A和B中都有user表，当我要查询A库中的user表中数据时，怎样选取？

方法一：select * from xgl.user;
方法二：如果一直在使用某个库中的数据
mysql> use A;
Database changed
mysql> select * from user;

1.5 删除数据库

删除数据库语法：

Drop database 数据库名；

[例1.5.1]：删除db_shop数据库

Drop database db_shop；

二、字符集

字符集是指一套文字符号及其编码和排序规则的集合。实际应用中，用户通常会向MySQL系统中插入大量文字，但计算机只能存储二进制码，这时就需要通过字符集将文字转换为二进制码再存入数据库。
MySQL字符集包括字符集和排序规则两个概念，其中字符集定义MySQL存储字符串的方式，排序规则定义字符串的排序方式。每个字符集至少对应一个排序规则

[例2.1.1]导入xkgl数据库时，选择latin1的字符集，看一下数据表里的数据有什么特点。

中文变成了乱码，英文和阿拉伯数字没有变化

用户应该在向数据库插入数据之前确定字符集的种类。MySQL默认的字符集为latin1，字符集和排序规则分为四个级别：服务器级别、数据库、数据表和字段。

查看当前数据库级别的字符集:

SHOW VARIABLES LIKE 'character_set_server';

查看当前数据表级别的字符集:

SHOW VARIABLES LIKE 'character_set_database';

数据库默认的字符集与服务器设置的字符集相同，数据库的字符集和排序规则可以在数据库创建时指定，也可以在数据库创建之后修改。

创建数据库时定义字符集语法:

Create database 数据库名[ character set 字符集] 

三、数据表操作（包含数据类型以及完整性约束）

1.引入库

1.创建数据表

[例]根据下图信息，实现该数据表

创建数据表，实际上是规定______列属性______和实现_数据完整性约束_的过程，基本语法形式如下：

Create table 表名(
字段1 数据类型1 [约束]，
字段2 数据类型2 [约束]，
….
字段n 数据类型n [约束]
)

数据表命名应遵循以下原则：

• 数据表命名应遵循以下原则：
• 多个单词之间使用下划线“_”分隔，不允许有空格；
• 不允许为MySQL关键字；
• 不允许与同一数据库中的其他数据表同名。

2.数据类型

数据表由多个字段构成，每个字段可以指定不同的数据类型，数据类型用于规定数据的存储格式、约束和有效范围。在定义数据表字段时，应根据实际情况选择合适的数据类型。MySQL提供的数据类型主要包括数值类型、日期和时间类型、字符串类型等。

2.1数值型：

数据类型	所占字节
TINYINT	1字节
SMALLINT	2字节
MEDIUMINT	3字节
INT	4字节
BIGINT	8字节

存储范围（有符号）	存储范围（无符号）
-128～127	0～255
-32768～32767	0～65535
-8388608～8388607	0～16777215
-2147483648～2147483647	0～4294967295
-9223372036854775808～9223372036854775807	0～18446744073709551615

2.1.1整型

[实验]在课堂的tb_demo的age字段中插入300，能够成功吗？如果能，截图，如果不能，说出错误提示是什么？

不能，超出范围了

2.1.2浮点数类型和定点数类型

浮点数类型分为两种，单精度（FLOAT）和双精度（DOUBLE），而定点数类型只有DECIMAL一种。
浮点数类型和定点数类型都可以使用类型名称后面加“(M,D)”的形式来定义

• M称为精度，表示数值的位数（整数位+小数位）；
• D称为标度，表示小数点后的位数。

数据类型	占用字节
FLOAT	4字节
DOUBLE	8字节
DECIMAL	M+2字节

负数存储范围	非负数存储范围
-3.402823466E+38～-1.175494351E-38	0和1.175494351E-38～3.402823466E+38
-1.7976931348623157E+308～-2.2250738585072014E-308	0和2.2250738585072014E-308～1.7976931348623157E+308
与DOUBLE相同	与DOUBLE相同

[实验]在课堂的tb_demo插入如下数据（分数90.16），数据能插入成功吗？插入成功的数据是输入的数据吗？

可以插入，但不是输入的数据

2.2 日期类型

MySQL 5.7版本中用于存储日期和时间的数据类型有YEAR，DATE，TIME，DATETIME和TIMESTAMP，每一种类型都有固定的存储范围。
向日期和时间类型的字段插入数据时，最好使用引号将值包含起来。
Insert into y(y) values(‘1999’)
Y表中 y1列数据类型为year

数据类型	占用字节
YEAR	1字节
DATE	3字节
TIME	3字节+小数秒存储
DATETIME	5字节+小数秒存储
TIMESTAMP	4字节+小数秒存储

存储格式	存储范围
YYYY	1901～2155
YYYY-MM-DD	1000-01-01～9999-12-31
HH:MM:SS	-838:59:59～838:59:59
YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00～9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC～2038-01-19 03:14:07 UTC

TIMESTAMP：时间戳数据类型。
时间戳所在列不需要你手动输入时间，当你添加或者修改某行数据时，将当前时间自动写在timestamp所在列 |

2.3 字符串类型

MySQL支持的字符串数据类型包括CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM和SET等。

类型名称	占用字节
CHAR(M)	M*w字节
VARCHAR(M)	L+1字节
BINARY(N)	N字节
VARBINARY(N)	L+1字节
BLOB	L+2字节
TEXT	L+2字节
ENUM	1或2字节
SET	1、2、3、4或8字节

存储范围	备注
0<=M<=255	M指的是字符数，较短的字符串定长，如果你的输入的数据字符不够M，则右边会用空格填充，直到满足M个字符为止
0<=M<=65535	M指的是字符数，较短的字符串变长，最多能容纳M个字符，如果输入数据不足M个字符，存储空间相应减少。
0<=N<=255	存放较短的二进制数据，比如图片。定长
0<=N<=65535	存放较短的二进制数据，比如图片。变长
L<216	存放较长的二进制数据，比如视频，音频
L<216	存放较长的二进制数据，比如新闻，文章正文等等
0～65535	存储“单选项”的数据
最多64个成员	存储“多选项的数据”

[例]实现该数据表

CREATE TABLE tb_demo( 
id int(11), 
NAME VARCHAR(30), 
age TINYINT(4), 
score FLOAT(4,1), 
sex enum("m","w"), 
hobby SET("f","v","b"), 
spend DECIMAL(5,1), 
intro text 
) 

---

实验题：

1.建立一个商品销售数据库SELL，创建数据库的时候字符集设置为gbk，创建好后，查看SELL数据库目前默认的字符集

create database SELL character set 'gbk';
SHOW VARIABLES LIKE 'character_set_server'；

2.查看目前账户有哪些数据库

show databases;

3.使用数据库SELL

USE sell;

4.创建 customer（顾客表），sales（订单表），product（产品表），detailed（订单细则表）
信息分别如下：（数据类型可依据存储示例来自己决定)，在SELL库中建立起以上四个表。

顾客表：

属性名	数据类型
Cid	int(10)
Cname	varchar(10)
Cphone	char(11)
VIP	ENUM(‘S’,’R’,’J’,’O’)

含义	存储示例
顾客号	例如：20200101（固定长度）
顾客名	例如：张三
电话	例如：18482112222
会员	目前会员只有”食品会员“，”日用品会员“，”家居会员"三种，可以同时拥有0-3个会员身份

建表语句：
create table customer(
Cid int comment '顾客号',
Cname varchar(5) comment '顾客名',
Cphone char(11) comment '电话',
VIP set('食','日','家') comment '会员'
);

产品表：

属性名	数据类型
Pid	varchar(5)
Pname	varchar(10)
Pprice	float(4,2)

含义	存储示例
产品号	例如：K1001，长度固定
产品名	例如果冻， 最多不超过10个字
单价	例：20.12（精确到小数点后2位）

建表语句：
create table products(
Pid char(5) comment '产品号',
Pname varchar(10) comment '产品名',
Pprice float(6,2) comment '单价'
);

订单表：

属性名	数据类型
Oid	char(12)
Cid	char
Cnumber	int
Cotime	DATETIME
Cdtime	DATE

含义	存储示例
订单编号	例如：D20200728001，定长
顾客号	例如：20200101（固定长度）
订货数量	整数
订货日期	自动获取下单时间，格式为年月日时分秒
交货日期	记录交货时间，格式为年月日

建表语句：
create table sales(
Oid char(12) comment '订单编号',
Cid int comment '顾客号',
Cnumber int comment '订货数量',
Cotime TIMESTAMP comment '订货日期',
Cdtime DATE comment '交货日期'
 );

订单明细表：

属性名	数据类型
Oid	char
Pid	varchar
Dnumber	int
Dmoney	float(100,2)

含义	存储示例
订单编号	例如：D20200728001，定长
产品号	例如：K1001，长度固定
订货数量	整数
金额	精确到小数点后2位

建表语句：
create table detailed(
Oid char(12) comment '订单编号',
Pid varchar(5) comment '产品号',
Dnumber int comment '订货数量',
Dmoney decimal(7,2) comment '金额'
 );