⑨创建和管理表（MySQL）

已于 2022-06-01 11:27:41 修改
阅读量469 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2022-05-31 22:09:32 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yihjh/article/details/125069228
版权

1.基础知识

1.1一条数据存储的过程

存储数据是处理数据的第一步。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。

在MySQL中，一个完整的数据存储过程总共有4步，分别是创建数据库、确认字段、创建数据表、插入数据。

为什么我们要先创建一个数据库，而不是直接创建数据表呢？

因为从系统架构的层次上看，MySQL数据库系统从小到大依次是 数据库服务器 、 数据库 、数据表 、数据表的 行与列。

1.2标识符的命名规则

  • 数据库名、表名不得超过30个字符，变量名限制为29个
  • 必须只能包含A-Z,a-z，0-9，_（共63个字符）
  • 数据库名、表名、字段名等对象名中间不要包含空格
  • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
  • 必须保证字段没有和保留字、数据库系统或常用的方法冲突。如果坚持使用，需要在SQL语句中使用`（着重号）引起来
  • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

1.3MySQL中的数据类型

整数类型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;

集合：MULTIPOINT、MULTILINESTRING、MULTIPLYGON、GEOMETRYCOLLECTION

其中，常用的几类类型介绍如下：

数据类型描述
INT从-2^31到2^31-1的整型数据。存储大小为4个字节
CHAR(size)定长字符数据。若未指定，默认为1个字符，最大长度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日期型数据，格式'YYYY-MM-DD'
BLOB二进制形式的长文本数据，最大可达4G
TEXT长文本数据，最大可达4G

2.创建和管理数据库

2.1创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）***

CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

注意：DATABASE不能改名。一些可视化工具可以改名，它是建新库，再删旧库完成的。

2.2使用数据库

查看当前所有的数据库

SHOW DATABASES; -- 有一个S，代表有多个数据库

查看当前正在使用的数据库

SELECT DATABASE(); --使用一个 MySQL 中的全局函数

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或
SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

注意：要操作表格和数据之前必须先说明对那个数据库进行操作，否则就要对所有对象加上“数据库名.”。

2.3修改数据库

更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集; -- 比如：gbk、utf8等

2.4删除数据库

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式2：删除指定的数据库（推荐）

DROP DATABASE IF EXISTS 数据库名;

3.创建表

3.1创建方式1

  • 必须具备
    • CREATE TABLE权限
    • 存储空间
  • 语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(

        字段1,数据类型 [约束条件] [默认值],

        字段2,数据类型 [约束条件] [默认值],

        字段3,数据类型 [约束条件] [默认值],

        ……

        [表约束条件]

);

加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

  • 必须指定
    • 列表
    • 列表（或字段名），数据类型，长度
  • 可选指定
    • 约束条件
    • 默认值
  • 创建表举例1：
-- 创建表
CREATE TABLE haha(
	-- int类型
	emp_id INT,
	-- 最多保存20个中英文字符
	emp_name VARCHAR(20),
	-- 中位数不超过15位
	salary DOUBLE,
	-- 日期类型
	birthday DATE
);
DESC haha;
--  查询haha这张表
-- 这张表的查询结果如下

 MySQL在执行建表语句时，将id字段的类型设置为int(11),这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11.也可以在创建数据表的时候指定数据的显示宽度。

  • 创建表举例2
CREATE TABLE haha2(
	-- int类型，自增
	deptno INT(2) AUTO_INCREMENT,
	dname VARCHAR(14),
	loc VARCHAR(13),
	-- 主键
	PRIMARY KEY(deptno)
);
DESC haha2;
-- 查询haha2表
-- 其查询结果如下图

 3.2创建方式2

  • 使用AS subquery选项，将创建表和插入数据结合起来 
    CREATE TABLE table1
    [(column,column...)]
AS subquery;
  • 指定的列和子查询中的列要一 一对应

通过列名和默认值定义列

CREATE TABLE haha3
AS
SELECT *
FROM employees;
CREATE TABLE haha4
AS
SELECT *
FROM employees
WHERE 1 = 2; -- 创建的haha4是空表

3.3查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用 DESCRIBE/DESC语句查看数据库表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

4.修改表

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

使用ALTER TABLE语句可以实现：

  • 向已有的表中添加列
  • 修改现有表中的列
  • 删除现有表中的列
  • 重命名现有表中的列

4.1追加一个列

语法格式如下：

ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];

举例：

ALTER TABLE haha2
ADD job_id VARCHAR(15);

未添加job_id时haha2表的查询结果为：

 添加job_id后haha2表的查询结果为：

 4.2修改一个列

  • 可以修改列的数据类型、长度、默认值和位置
  • 修改字段数据类型、长度、默认值、位置的语法格式如下：
ALTER TABLE 表名 MONDIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FALSE|AFTER 字段名2]；
  • 举例：
ALTER TABLE haha2
MODIFY job_id VARCHAR(30);

4.3重命名一个列

使用CHANGE old_column new _column dataType子句重命名列。语法格式如下：

ALTER TABLE 表名 CHANGE [COLUMN] 列名 新列名 新数据类型;

举例：

ALTER TABLE haha2
CHANGE deptno dept_no INT;

4.4删除一个列

删除表中某个字段的语法格式如下：

ALTER TABLE 表名 DROP [COLUMN] 字段名
-- 删除haha2 表中的 job_id字段
ALTER TABLE haha2
DROP job_id;

5.重命名表

方式1：使用RENAME

-- 将haha2的表名改为 haha22
RENAME TABLE haha2
TO haha22;

方式2

-- 将haha22 表修改为 haha2
ALTER TABLE haha22
RENAME TO haha2;

6.删除表

  • 在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
  • 数据和结构都被删除
  • 所有正在运行的相关事务都被提交
  • 语法格式
DROP TABLE [IF EXISTS] 数据表1 [,数据表2,数据表3,...，数据表n];

IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。 

  • 举例：
DROP TABLE haha;
  • DROP TABLE语句不能回滚。

7.清空表

  • TRUNCATE TABLE 语句：
    • 删除表中所有的数据
    • 释放表的存储空间
  • 举例
TRUNCATE TABLE detail_dept;
  • TRUNCATE语句 不能回滚，而使用 DELETE语句删除数据，可以回滚
  • 对比：
    • DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。（因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT 操作不受SET autocommit = FALSE影响的。）
    • DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了SET autocommit = FALSE,则执行的DML操作就可以实现回滚。
--  DELETE FROM
-- 1
COMMIT;
-- 2
SELECT *
FROM tt;
-- 3
SET autocommit = FALSE;
-- 4
DELETE FROM tt;
-- 5
SELECT *
FROM tt;
-- 6
ROLLBACK;
-- 7
SELECT *
FROM tt;
2
6
7 

-- RRUNCATE TABLE
 -- 1
 COMMIT;
 -- 2
 SELECT *
 FROM stu;
 -- 3
 SET autocommit = FALSE;
 -- 4
 TRUNCATE TABLE stu;
 -- 5
 SELECT *
 FROM stu;
 -- 6
 ROLLBACK;
 -- 7
 SELECT * 
 FROM stu;

2
5
7

 阿里开发规范：

【参考】TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同。

 8.MySQL8新特性——DDL原子化

 在MySQL8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚。DDL操作回滚日志写入到data dictionary 数据字典表mysql.innidb_ddl_log(该表是隐藏的表，通过show tables无法看到)中，用于回滚操作。通过设置参数，可以将DDL操作日志打印输出到MySQL错误日志中。

分别在MySQL5.7版本和MySQL8.0版本中创建数据库和数据表，结果如下：

CREATE DATABASE mytest;

USE mytest;

CREATE TABLE book1( 
book_id INT , 
book_name VARCHAR(255) 
);


SHOW TABLES;

我们可以看到，在mytest数据库中只有table1这张表，并不存在table2.

在MySQL5和MySQL8版本中分别执行如下代码

DROP TABLE book1,book2;

MySQL5，虽然在删除时报错了，但也成功的删除了book1这张表。

MySQL8，并没有删除book1这张表。

9.课后练习题

-- 第10章_创建和管理表课后练习

-- 练习1

-- 1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作 

CREATE DATABASE IF NOT EXISTS test01_office CHARSET 'utf8';

USE test01_office;

-- 2. 创建表dept01 
/*
字段 类型 
id INT(7) 
NAME VARCHAR(25) 
*/

CREATE TABLE IF NOT EXISTS dept01(
	id INT(7),
	`name` VARCHAR(25)
);

-- 3. 将表departments中的数据插入新表dept02中 

CREATE TABLE IF NOT EXISTS dept02
AS
SELECT *
FROM atguigudb.departments;

-- 4. 创建表emp01 
/*
字段 类型 
id INT(7)
first_name VARCHAR (25) 
last_name VARCHAR(25) 
dept_id INT(7) 
*/

CREATE TABLE IF NOT EXISTS emp01(
	id INT(7),
	first_name VARCHAR(25),
	last_name VARCHAR(25),
	dept_id INT(7)
);

-- 5. 将列last_name的长度增加到50 

ALTER TABLE emp01
MODIFY last_name VARCHAR(50);

-- 6. 根据表employees创建emp02 

CREATE TABLE IF NOT EXISTS emp02
AS
SELECT *
FROM atguigudb.`employees`;

-- 7. 删除表emp01 

DROP TABLE emp01;

-- 8. 将表emp02重命名为emp01 

RENAME TABLE emp02
TO emp01;

-- 9.在表dept02和emp01中添加新列test_column，并检查所作的操作 

ALTER TABLE dept02
ADD test_column VARCHAR(20);

DESC dept02;

ALTER TABLE emp01
ADD test_column VARCHAR(20);

DESC emp01;

-- 10.直接删除表emp01中的列 department_id 

ALTER TABLE emp01
DROP department_id;


-- 练习二

-- 1、创建数据库 test02_market 

CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';

USE test02_market;

SHOW CREATE DATABASE test02_market;

-- 2、创建数据表 customers

CREATE TABLE IF NOT EXISTS customers(
	c_num INT,
	c_name VARCHAR(50),
	c_contact VARCHAR(50),
	c_city VARCHAR(50),
	c_birth DATE
);

-- 3、将 c_contact 字段移动到 c_birth 字段后面 

ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth;

-- 4、将 c_name 字段数据类型改为 varchar(70) 

ALTER TABLE customers
MODIFY c_name VARCHAR(70);

-- 5、将c_contact字段改名为c_phone 

ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);

-- 6、增加c_gender字段到c_name后面，数据类型为char(1) 

ALTER TABLE customers
ADD c_gender CHAR(1) AFTER c_name ;

-- 7、将表名改为customers_info

RENAME TABLE customers
TO customers_info;

-- 8、删除字段c_city

ALTER TABLE customers_info
DROP c_city;




-- 练习三

-- 1、创建数据库test03_company 

CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';

USE test03_company;

-- 2、创建表offices

CREATE TABLE IF NOT EXISTS offices(
	officeCode INT,
	city VARCHAR(30),
	address VARCHAR(50),
	country VARCHAR(50),
	postalCode VARCHAR(25)
);

-- 3、创建表employees

CREATE TABLE IF NOT EXISTS employees(
	empNum INT,
	lastName VARCHAR(50),
	firstName VARCHAR(50),
	mobile VARCHAR(25),
	`code` INT,
	jobTitle VARCHAR(50),
	birth DATE,
	note VARCHAR(255),
	sex VARCHAR(5)
);


DESC employees;

-- 4、将表employees的mobile字段修改到code字段后面 

ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER `code`;

-- 5、将表employees的birth字段改名为birthday 

ALTER TABLE employees
CHANGE birth birthday DATE;

-- 6、修改sex字段，数据类型为char(1) 

ALTER TABLE employees
MODIFY sex CHAR(1);

-- 7、删除字段note 

ALTER TABLE employees
DROP note;

-- 8、增加字段名favoriate_activity，数据类型为varchar(100) 

ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);

-- 9、将表employees的名称修改为 employees_info

RENAME TABLE employees
TO employees_info;

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值