MySQL——管理库和表

本文详细介绍了MySQL中管理数据库和表的各种操作,包括创建、管理、修改和删除数据库,创建、修改、重命名和删除表,以及DCL中的commit和rollback。对比了truncate table与delete from的区别,并探讨了DDL和DML的操作特点,最后讨论了MySQL 8.0的新特性——DDL的原子化。
摘要由CSDN通过智能技术生成

一、管理库

1.1 创建数据库

  • 方式1:创建的此数据库使用的是默认的字符集UTF8
CREATE DATABASE mytest1;	
  • 方式2:指定了要创建的数据库的字符集
CREATE DATABASE mytest3 CHARACTER SET 'gbk';
  • 方式3:创建的数据库若存在,则无法创建,但不会保存,并且可以指定数据库的字符集**(推荐)**
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';

1.2 管理数据库

  • 查看当前连接中的数据库有哪些
SHOW DATABASES;
  • 切换数据库
USE mytest2;
  • 查看当前数据库中保存的数据表
SHOW TABLES;
  • 查看当前使用的数据库
SELECT DATABASE() FROM DUAL;
  • 查看指定数据库下保存的数据表
SHOW TABLES FROM mysql;

1.3 修改数据库(一般不改动!)

  • 修改字符集gbk、utf8等
ALTER DATABASE mytest 2 CHARACTER SET 'gbk';
# 数据库不能改名,可视化工具中是建新库

1.4 删除数据库(一般不用!)

# 方式1
DROP DATABASE mytest1;
# 方式2
DROP DATABASE IF EXISTS mytest1;

二、管理表

写在前面

  • 若创建表时没有指明使用的字符集,则默认使用表所在的数据库的字符集
  • 对原表中数据或复制表中的数据进行删除,不会相互影响
  • 创建一个表,实现对另一个表的复制,但不包括表数据,在where后添加一个恒不成立的条件即可
  • 库、表删除后无法撤销

2.1 创建表

  • 方式1:直接创建
CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),
hire_date DATE
);
# 使用varchar来定义字符串,必须在使用varchar时指明长度
CREATE TABLE
[IF NOT EXISTS] tb_name 	-- 不存在才创建,存在就跳过
(column_name1 data_type1	 -- 列名和类型必选
  [ PRIMARY KEY				 -- 可选的约束,主键
   | FOREIGN KEY 				-- 外键,引用其他表的键值
   | AUTO_INCREMENT 			-- 自增ID
   | COMMENT comment 			-- 列注释(评论)
   | DEFAULT default_value		 -- 默认值
   | UNIQUE 					-- 唯一性约束,不允许两条记录该列值相同
   | NOT NULL 					-- 该列非空
  ], ...
) [CHARACTER SET charset] 		-- 字符集编码
[COLLATE collate_value] 		-- 列排序和比较时的规则(是否区分大小写等)
  • 查看表结构
DESC myemp1;
  • 查看创建表的语句结构
SHOW CREATE TABLE myemp1;
  • 方式2:基于现有的表创建表,同时导入数据
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;
  • 将查询的结果放入一张新表中。查询语句中字段的别名,可以作为新创建的表的字段的名称
CREATE TABLE IF NOT EXISTS myemp4
AS
SELECT employee_id emp_id,last_name lname,salary sal
FROM employees e JOIN departments d
ON e. department_id = d.department_id;

2.2 修改表(alter table)

{ ADD COLUMN <列名> <类型>  -- 增加列
 | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
 | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
 | MODIFY COLUMN <列名> <类型> -- 修改列类型
 | DROP COLUMN <列名> -- 删除列
 | RENAME TO <新表名> -- 修改表名
 | CHARACTER SET <字符集名> -- 修改字符集
 | COLLATE <校对规则名> } -- 修改校对规则(比较和排序时用到)
  • 添加一个字段(默认添加到表中最后一个字段的位置)
ALTER TABLE myemp1
ADD salary DOUBLE(10,2);
# 一共十位数,小数点后有两位
  • 指定添加字段的位置,第一位
ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;
  • 放在指定字段位置的后面
ALTER TABLE myemp1
ADD email VARCHAR(50) AFTER emp_name;
  • 可修改字段的数据类型、长度、默认值
    • 修改长度
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) DEFAULT 'aaa';
  • 重命名一个字段
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);
  • 在修改名称时,可修改字段长度
ALTER TABLE myemp1
CHANGE email my_email VARCHAR(55);
  • 删除一个字段
ALTER TABLE myemp1
DROP COLUMN my_email;

2.3 重命名表

  • 方式1
RENAME TABLE myemp1
TO myemp11;
  • 方式2
ALTER TABLE myemp2
RENAME TO myemp22;

2.4 删除表

  • 删除表结构以及表中数据
DROP TABLE IF EXISTS myemp22;

2.5 清空表

  • 清空表中数据,保留表结构
SELECT * FROM employee_copy;
TRUNCATE TABLE employee_copy;

三、DCL中 commit 和 rollback

  • commit:提交数据。一旦执行commit,则数据就被永久的保存在数据库中,意味着数据不可以回滚
  • rollback:回滚数据。一旦执行rollback,则可以实现数据的回滚。回滚到最近的一次commit之后

四、对比 truncate table 和 delecte from

  • 相同点:都可以实现对表中所有数据的删除,同时保留表结构
  • 不同点:
    • truncate table:一旦执行此操作,表数据全部清除。同时,数据不可以回滚
    • delecte from:一旦执行此操作,表数据可以全部清除(不带where)。同时,数据是可以实现回滚
    • truncate 的速度快,效率高,不能 where 筛选条件。一句清空表中所有的数据
    • truncate 删除后再插入值时自增长列从1开始,而 delete 是从断点开始
    • truncate 删除时不会返回受影响行数,delete 会返回受影响行数

五、DDL 和 DML 的说明

  • DDL的操作一旦执行,就不可回滚
    • 指令 ‘set autocommit = false’ 对DDL操作失效。因为在执行完DDL操作之后,一定会执行一次commit,而此commit操作不受 set autocommit = false 的影响
  • DML的操作默认情况,一旦执行,也是不可回滚
    • 若在执行sql之前,执行了 ‘set autocommit = false’ ,则执行的DML操作就可以实现回滚

六、SQL8.0的新特性:DDL的原子化

  • 要么成功,要么回滚

参考:尚硅谷-宋红康老师的视频及课件。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值