数据库、表和数据类型

请注意,应在自己的测试环境中进行操作!

操作数据库,最直接的方法就是使用SQL(结构化查询语言);其关键字是不区分大小写的,但对于对象名,如数据库名称、表名称或字段名称等,则应注意使用统一的标准,在某些平台下,它是区分字母大小写的。本教程中,对象名称将使用小写字母,需要时使用下画线连接。请注意,HeidiSQL会自动将一些关键字转换为大写,这并不影响执行结果。

数据库

使用HeidiSQL或其它MySQL操作工具,连接到MySQL服务器后,可以通过如下代码查看服务器中存在的数据库(database)。

MySQL
show databases;

如下图就显示了MySQL8中默认创建的数据库。

创建数据库可以使用create database语句,如下面的代码。

MySQL
create database cdb_demo;

其中,cdb_demo为新的数据库名称。此外,在创建数据库时,还可以使用一些子句,如下面的代码。

MySQL
CREATE DATABASE if NOT exists cdb_demo DEFAULT CHARSET='utf8';

这里,if not exists子句表示如果数据库不存在就创建它,如果不添加此子句,当数据库已经存在时执行创建操作就会产生错误;default charset='utf8’将设置数据库的默认字符集为UTF-8。

删除数据库时使用drop database语句,如下面的代码。

MySQL
drop database cdb_demo;

再次提醒:删除和修改操作一定要谨慎!!!

MySQL是一种关系型数据库,其中的数据会以二维表的形式进行管理,如Excel中的工作表(Worksheet)就是一个典型的二维表,如下图。

创建一个表时,会定义字段的名称、数据类型等属性,然后,每一行的数据会与字段一一对应。

讨论如何创建表之前,我们先来了解一些MySQL中常用的数据类型。

数据类型

MySQL中支持的数据类型是非常丰富的,这里了解一常用的类型,如:

  • 整数,支持int(32位整数)、bigint(64位整数)等类型。
  • 实数,如decimal(m,n)类型,其中,m表示整数部分和小数部分合计的最大位数,n表示小数位。如decimal(5,1)可以处理xxxx.x格式的数值。
  • 日期和时间,如datetime等类型。
  • 文本类型,如char(n)类型表示不可变长文本类型,n表示字符数量;varchar(n)类型表示可变长文本类型,n表示最大字符数;text类型支持最多65535个字符;longtext类型支持最多4294967295个字符。
  • 字节数据类型,如longblob类型最多可以保存4294967295字节的数据。

数值类型数据使用直接量,如1、1.23等;文本和日期时间数据需要使用一对单引号定义,如'abc'、'2020-3-31 21:39:01'等;null值表示没有数据,这和0或空字符串是有区别的。

此外,一些数据类型并不常用,如set、enum等类型。在MySQL8中还新增了json类型及其相关的处理方法。

创建表

创建表时使用create table语句,其主要格式为:

MySQL
create table if not exists <表名> (
<字段定义>
)engine=innodb,default charset='utf8';

其中,if not exists为可选,指定在表不存在时创建它。engine=innodb,default charset='utf8'也为可选内容,如果不指定则使用默认的表引擎类型和字符集,这里指定表引擎使用innodb,而表的字符集使用UTF-8。

下面的代码,将创建一个保存用户基本信息的数据表。

MySQL
CREATE TABLE cdb_demo.user_main(
userid BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
username VARCHAR(15) NOT NULL UNIQUE,
userpwd VARCHAR(15) NOT NULL CHECK(LENGTH(userpwd)>=6),
fullname VARCHAR(15),
sex INT DEFAULT 0 CHECK(sex IN(0,1,2)),
email VARCHAR(30),
islocked INT DEFAULT 1 CHECK(islocked IN(0,1))
)ENGINE=INNODB,DEFAULT CHARSET='utf8';

下面,通过user_main表的创建来具体了解一些常用的表和字段的定义要素。

一个表属于某个数据库,这里使用cdb_demo.user_main指定user_main表属于cdb_demo数据库。如果不使用“<数据库>.<表>”格式,也可以首先使用use语句引用数据库,再执行其它SQL,如:

MySQL
use cdb_demo;

CREATE TABLE user_main(
userid BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
username VARCHAR(15) NOT NULL UNIQUE,
userpwd VARCHAR(15) NOT NULL CHECK(LENGTH(userpwd)>=6),
fullname VARCHAR(15),
sex INT DEFAULT 0 CHECK(sex IN(0,1,2)),
email VARCHAR(30),
islocked INT DEFAULT 1 CHECK(islocked IN(0,1))
)ENGINE=INNODB,DEFAULT CHARSET='utf8';

userid字段定义的要素包括:

  • bigint定义其类型为64位整数。
  • auto_increment定义这是一个自动管理的增量数据,默认的记录ID从1开始,每次加1。这个字段的数据并不需要手动添加和修改,它可以自动生成。
  • not null表示这个字段的数据不能为空,即必须有数据。
  • primary key表示这个字段会作为表的主键字段。如果主键有多个字段,可以在字段定义后添加,如primary key (a,b)就是将a字段和b字段的组合定义为主键,即a字段和b字段的数据组合是不允许重复的。

username定义为文本内容,最长15个字符,不能为空。unique定义此字段为唯一键约束,即这个字段的数据也是不能重复的。

userpwd定义为文本内容,最长15个字符,不能为空。check(length(userpwd)>=6)指定字段数据必须大于等于6个字符。这里请注意,实际应用开发过程中,用户密码不应该使用明文保存,而是使用一定的算法进行加密,这里只是为了更直观地演示数据库的基本操作。

fullname定义为文本内容,最长15个字符,可以为空,即可以没有数据(null)。

sex定义为32位整数,默认值为0,数据应该是0、1或2。

email定义为文本类型,最多30个字符,可以为空。

islocked定义为32位整数,默认为1,数据必须是0或1。

此外,user_main表使用innodb引擎,此类型的表引擎可以使用事务(transaction);字符集使用UTF-8,这样就可以有效处理中文等各种类型的文本数据。

查看和删除表

查看数据库中有哪些表时,可以使用show tables语句,如:

MySQL
USE cdb_demo;
SHOW TABLES;

也可以使用from子句直接指定数据库,如“show tables from cdb_demo;”。

需要查看表的定义,可以describe语句,如。

MySQL
DESCRIBE cdb_demo.user_main;

我们定义的user_main表结构如下图所示。

删除数据表时使用drop table语句,其格式如下。

MySQL
drop table <表名>;

修改表和字段定义

修改表的名称时,可以使用rename table语句,如。

MySQL
RENAME TABLE cdb_demo.user_main
TO cdb_demo.user_main1;

修改表中字段的定义的时,可在使用alter table语句,并配合相应的子句来完成。

添加字段时,使用add column子句,如下面的代码就是在user_main表中添加一个名为phone的字段,定义为可变长文本,最多为15个字符,默认可能为null。

MySQL
ALTER TABLE cdb_demo.user_main
ADD COLUMN phone VARCHAR(15);

添加多个字段时,需要使用多个add column子句,如下面的代码添加了a字段和b字段。

MySQL
ALTER TABLE cdb_demo.user_main
ADD COLUMN a INT,
ADD COLUMN b VARCHAR(15);

修改字段名称时,使用rename子句,如下面的代码会将a字段名修改为c。

MySQL
ALTER TABLE cdb_demo.user_main
RENAME COLUMN a TO c;

修改字段的定义,使用change column子句,一般用于扩大字段值的允许范围,如下面的代码就是将b字段的定义从最多15个字符修改为最多30个字符。

MySQL
ALTER TABLE cdb_demo.user_main
CHANAGE COLUMN b b VARCHAR(30);

删除字段时使用drop column子句,如下面的代码就是删除c字段(a字段改名)和b字段。

MySQL
ALTER TABLE cdb_demo.user_main
DROP COLUMN c,DROP COLUMN b;

复制表结构和数据

复制一个表的结构时,可以在create table语句中使用like子句,如下面的代码,我们创建cdb_bak数据库,并将cdb_demo数据库中的user_main表复制到cdb_bak数据库中。

MySQL
CREATE database if NOT EXISTS cdb_bak DEFAULT CHARSET='utf8';

CREATE TABLE if NOT EXISTS cdb_bak.user_main
LIKE cdb_demo.user_main;

INSERT INTO cdb_bak.user_main
SELECT * FROM cdb_demo.user_main;

代码的操作分为三个部分,分别是创建cdb_bak数据库、复制表结构、复制表中的数据;此操作可以完全复制一个表的结构和数据。

另一种方法,虽然可以复制表的数据和基本结构,但某些定义不能复制,如字段的auto_increment、唯一键约束、主键等信息。下面的代码会在cdb_bak数据库创建一个user_main表数据的副本,一个名为user_main1的表。

MySQL
CREATE TABLE if NOT EXISTS cdb_bak.user_main1
SELECT * FROM cdb_demo.user_main;
本站内容均为原创作品,转载请注明出处,本页面网址为:http://caohuayu.com/chy/article/Article.aspx?code=cc004002