mysql数据库和表管理

---

一、创建和管理表

1.表的基本概念

在mysql数据库中，表是一种很重要的数据库对象，是组成数据库的基本元素，由若干个字段组成，主要用来实现存储数据记录。表的操作包含创建表，查看表，修改表和删除表，这些操作是数据库对象的表管理中最基本、最重要的操作。
表是包含数据库中所有数据的数据库对象，如下图

2.标识符命名规范

1. 数据库名、表名不得超过30个字符，变量名限制为29个
2. 只能包含大写、小写、数字字符以及下划线
3. 同一个MySql软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
4. 不能和保留字、函数名冲突
5. 保证字段名和类型的一致性：假如某个字段在一个表里面是整形，那在另一个表里面可就别变成了字符串

3.MySQL中的数据类型

类型	具体类型
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
json类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON 集合：MULTPOINT、MULTILINESTRING、MULTIPOLGON、GEOMTRYCOLLECTION

常用的有

类型	描述
INT	4个字节
CHAR(size)	定长字符数据，若未指定，默认为一个字符，最大长度255
VARCHAR(size)	可变字符数据，更具字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，4个字节。M=整数位+小数位，D=小数位（D<=M<=255,0<=D<=30）,默认M+D<=6
DOUBLE(M,D)	双精度，8个字节。D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度，M+2个字节。D<=M<=65,0<=D<=30,默认最大取值范围和DOUBLE一样
DATE	日期类型，格式‘YYY-MM-DD’
BLOD	二进制心事的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

二、创建和管理数据库

1.CREATE创建数据库

CREATE DATABASE 数据库名;

创建数据库并制定字符集

CREATE DATABASE 数据库名 CHARACTER SET "utf8";

判断数据库是否存在，不存在则创建（推荐）

CREATE DATABASE IF NOT EXISTS 数据库名;

数据库不能改名，不过一些可视化工具提供了这个功能。

数据库创建完成可以使用SHOW CREATE DATABASE 数据库名;查看创建数据库时用的语句，以及字符集等信息。

2.使用数据库

1. 查看当前连接中有哪些数据库

SHOW DATABASES;

2. 在多个数据库间切换

USE 数据库名;

3. 查看当前使用的数据库

SHOW DATABASE();

4. 查看指定数据库的所有表

SHOW TABLES FROM 数据库名;

3.修改数据库

修改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集; #gbk、utf8
#查看有哪些默认字符集
SHOW VARIABLES LIKE '%character%';
#查看所有字符集
SHOW CHARSET;

4.删除数据库

DROP DATABASE 数据库名;

DROP DATABASE IF EXISTS 数据库名;

三、表管理

在 MySQL数据库管理系统中创建表通过SQL语句CREATE TABLE 来实现。

1.表创建

CREATE TABLE [IF NOT EXISTS] 表名(
字段名 数据类型，
字段名 数据类型，
。。。
字段名 数据类型,
);

关键字COMMENT可以给字段添加注释

基于现有的表创建新表（会将数据也复制）

CREATE TABLE 新表名
AS
SELECT 字段1，字段2，字段3... FROM 旧表;

多个表连接后创建

CREATE TABLE 新表名
AS
SELECT  字段1，字段2，字段3...
FROM 旧表1 JOIN 旧表2
ON 连接条件;

基于现有表创建新表，只复制表结构，不导入数据

CREATE TABLE 新表名
AS
SELECT 字段1，字段2，字段3... FROM 旧表WHERE 1 = 2;

1=2永远不会成立，那就意味着没有数据符合。

2.查看表结构

简单的展示

DESC/DESCRIBE 表名;

详细的

SHOW CREATE TABLE 表名;

3.修改表

修改表指的是修改数据库中已经存在的数据表的结构。

1.修改表名

1. 通过 ALTER TABLE 语句来修改表名，只能操作一张表

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

2. 通过 RENAME TABLE 语句来修改表名，可以多张

RENAME TABLE 旧表名 TO 新表名,旧表名2 TO 新表名2;

2.修改字符集

通过 ALTER TABLE 语句来实现表字符集的修改

ALTER TABLE <表名> [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;

其中，DEFAULT 为默认值可选参数，使用与否均不影响结果。

3.增加字段

1. 增加一列

ALTER TABLE table_name ADD 字段名 数据类型;

2. 在第一个位置添加字段

ALTER TABLE table_name ADD 字段名 数据类型 FIRST;

3. 在指定位置添加字段

ALTER TABLE table_name ADD 字段名1 数据类型 AFTER 字段名2;

把字段1 添加到字段2之后

4.删除字段

ALTER TABLE table_name DROP 字段名;

5.修改字段

修改字段的数据类型

ALTER TABLE table_name MODIFY 字段名 数据类型;

修改字段名

ALTER TABLE table_name CHANGE 旧字段名 新字段名 数据类型;

不同的数据类型才需要写上数据类型。

修改字段的顺序

ALTER TABLE table_name MODIFY	字段名 数据类型 FIRST;
ALTER TABLE table_name MODIFY	字段名1 数据类型 AFTER 字段名2;	

6.删除表

DROP TABLE [IF EXISTS] 表名[,表1,表2...];