MySQL【创建和管理表】

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

MySQL中的数据类型

1.1如何创建数据库


#1.1 如何创建数据库
#方式1：
CREATE DATABASE mytest1;  # 创建的此数据库使用的是默认的字符集
 
#查看创建数据库的结构
SHOW CREATE DATABASE mytest1;

从下面的测试结果可以看出我们默认的字符集是utf8mb4

当然我们也可以指定我们想要创建的字符集


#方式2：显式了指名了要创建的数据库的字符集
CREATE DATABASE mytest2 CHARACTER SET 'gbk';
 
#
SHOW CREATE DATABASE mytest2;


#方式3（推荐）：如果要创建的数据库已经存在，则创建不成功，但不会报错。
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'utf8';
 
#如果要创建的数据库不存在，则创建成功
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';
 
SHOW DATABASES;

1.2管理数据库


#1.2 管理数据库
#查看当前连接中的数据库都有哪些
SHOW DATABASES;


#切换数据库
USE mytest1;


#查看当前数据库中保存的数据表
SHOW TABLES;


#查看当前使用的数据库
SELECT DATABASE() FROM DUAL;


#查看指定数据库下保存的数据表
SHOW TABLES FROM mysql;

1.3修改数据库

一般数据库是最好不要去修改的，最多只是修改一下字符集。

注意：数据库是不能改名的，但是可以通过一些工具创建新的库，把所有的表复制到新的库，再删除旧的库完成重命名操作。这个修改成本是非常高的。


#1.3 修改数据库
#更改数据库字符集
SHOW CREATE DATABASE mytest2;
 
ALTER DATABASE mytest2 CHARACTER SET 'utf8';

1.4删除数据库


#1.4 删除数据库
#方式1：如果要删除的数据库存在，则删除成功。如果不存在，则报错
DROP DATABASE mytest1;
 
SHOW DATABASES;


#方式2：推荐。 如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错。
DROP DATABASE IF EXISTS mytest1;
 
DROP DATABASE IF EXISTS mytest2;
show databases ;

二、如何创建数据表

2.如何创建数据表


#2. 如何创建数据表
USE mytest1;
 
SHOW CREATE DATABASE mytest1; #默认使用的是utf8
 
SHOW TABLES;

需要用户具备创建表的权限。

方式一：白手起家


#方式1："白手起家"的方式
CREATE TABLE IF NOT EXISTS myemp1(   #需要用户具备创建表的权限。
id INT,
emp_name VARCHAR(15), #使用VARCHAR来定义字符串，必须在使用VARCHAR时指明其长度。
hire_date DATE
);


#查看表结构
DESC myemp1;


#查看创建表的语句结构
SHOW CREATE TABLE myemp1; #如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。


#查看表数据
SELECT * FROM myemp1;

方式二：基于现有的表导入数据


#方式2：基于现有的表，同时导入数据
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;
select * from myemp2;

DESC myemp2;

DESC myemp2;


#说明1：查询语句中字段的别名，可以作为新创建的表的字段的名称。
#说明2：此时的查询语句可以结构比较丰富，使用前面章节讲过的各种SELECT
CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;


SELECT *
FROM myemp3;

DESC myemp3;


#练习1：创建一个表employees_copy，实现对employees表的复制，包括表数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;
 
SELECT * FROM employees_copy;


#练习2：创建一个表employees_blank，实现对employees表的复制，不包括表数据
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
#使用过滤语句将原来的数据中的全部筛掉
#下面这个语句需要保证id没有大于10000的
#where department_id > 10000;
#使用下面的这个代码更加保险，因为1是永远不可能等于2的，所以一条记录都不会被选中
WHERE 1 = 2; 
 
 
SELECT * FROM employees_blank;

三、修改表


#3. 修改表  --> ALTER TABLE 
DESC myemp1;

3.1添加一个字段


# 3.1 添加一个字段
ALTER TABLE myemp1
ADD salary DOUBLE(10,2); #默认添加到表中的最后一个字段的位置
DESC myemp1;


ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;
DESC myemp1;


ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;
DESC myemp1;

3.2修改一个字段


# 3.2 修改一个字段：数据类型、长度、默认值（略）
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ;
DESC myemp1;


ALTER TABLE myemp1
#设置默认值为aaa
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';
DESC myemp1;

3.3重命名一个字段


# 3.3 重命名一个字段
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);
DESC myemp1;


#可以在修改名字的时候顺便把这个字段的类型也给改了
ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);
DESC myemp1;

3.4删除一个字段


# 3.4 删除一个字段
ALTER TABLE myemp1
DROP COLUMN my_email;
DESC myemp1;

四、重命名表

方式一


#4. 重命名表
#方式1：
RENAME TABLE myemp1
TO myemp11;
 
DESC myemp11;

方式二


#方式2：
ALTER TABLE myemp2
RENAME TO myemp12;
 
DESC myemp12;

五、删除表


#5. 删除表
#不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS myemp2;
 
DROP TABLE IF EXISTS myemp12;

六、清空表


#5. 删除表
#不光将表结构删除掉，同时表中的数据也删除掉，释放表空间
DROP TABLE IF EXISTS myemp2;
 
DROP TABLE IF EXISTS myemp12;

DESC employees_copy;


7. DCL 中 COMMIT 和 ROLLBACK
 COMMIT:提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
 ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。
 
8. 对比 TRUNCATE TABLE 和 DELETE FROM 
 相同点：都可以实现对表中所有数据的删除，同时保留表结构。
 不同点：
	TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，数据是不可以回滚的。
	DELETE FROM：一旦执行此操作，表数据可以全部清除（不带WHERE）。同时，数据是可以实现回滚的。
 
 
9. DDL 和 DML 的说明
  ① DDL的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL
    操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)
  
  ② DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了 
    SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

delete from


# 演示：DELETE FROM 
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;

将我们自动提交关闭，然后删除我们的数据


#3)
SET autocommit = FALSE;
#4)
DELETE FROM myemp3;
#5)
SELECT *
FROM myemp3;

回滚数据，我们发现我们数据回来了（回到最后一次提交的时候）


#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

truncate table


# 演示：TRUNCATE TABLE
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;

使用truncate table删除表


#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;

这里我们发现使用truncate table是不能回滚的，也就是说现在咱删了库已经可以跑路了。


#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

阿里开发规范：

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

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
【强制】表必备三字段：id, gmt_create, gmt_modified。
说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create,
gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新
【推荐】表的命名最好是遵循 “业务名称_表的作用”。
正例：alipay_task 、 force_project、 trade_config
【推荐】库名与应用名称尽量一致。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
正例：无符号值可以避免误存负数，且扩大了表示范围。

MySQL8新特性—DDL的原子化

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


#9.测试MySQL8.0的新特性：DDL的原子化
 
CREATE DATABASE mytest;
 
USE mytest;
 
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);
 
SHOW TABLES;

这里我们实际上是没有表book2的，如果实在MySQL5.0中，因为删除book1在删除book2前面，所以在删除book1之后我们的数据库就提交了操作记录，然后在发现数据库没有会报错，然后我们查看的时候就发现book1没了，但是如果我们在MySQL8.0的环境下，也就是我们下面的测试代码，我们发现我们的book1还在，这是因为MySQL8.0是先删除掉book1之后并没有提交，而是检查book2，发现book2没有之后，就报错，同时回滚删除book1的操作，所以我们的book11又回来了。

由于原子具有不可分割性（先忽略夸克等），也就是说我们的MySQL8.0只有在保证一整条语句都是可以执行的时候才会去执行，也就是像原子一样不可分割语句。


DROP TABLE book1,book2;
 
SHOW TABLES;

相关阅读:
Mistral 7B 比Llama 2更好的开源大模型（一）
灵感乍现！造了个与众不同的Dubbo注册中心扩展轮子
 Excel导入数据下载模板示例（备份）
Python装饰器通俗理解
 InfiniBand 的前世今生
 嵌入式Linux基础学习笔记目录
 【MySQL】用户管理&权限控制
 从 Newtonsoft.Json 迁移到 System.Text.Json
Gitlab CI如何实现安全获取ssh-key拉取依赖项目，打包成品
 2022年最新安徽建筑安全员考试题库及答案
原文地址：https://blog.csdn.net/weixin_62684026/article/details/126093752

MySQL【创建和管理表】

一、创建数据库

标识符命名规则

MySQL中的数据类型

1.1如何创建数据库

1.2管理数据库

1.3修改数据库

1.4删除数据库

二、如何创建数据表

2.如何创建数据表

方式一：白手起家

方式二 ：基于现有的表导入数据

三、修改表

3.1添加一个字段

3.2修改一个字段

3.3重命名一个字段

3.4删除一个字段

四、重命名表

方式一

方式二

五、删除表

六、清空表

delete from

truncate table

MySQL8新特性—DDL的原子化

方式二：基于现有的表导入数据