MySQL之创建和管理表

目录

1. MySQL中的数据类型​编辑​编辑

2. 创建和管理数据库

 方式1：创建数据库

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

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

总结

2.2 使用数据库

 查看当前所有的数据库

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

查看指定库下所有的表

查看数据库的创建信息

使用/切换数据库

总结

 2.3 修改数据库

更改数据库字符集

总结

2.4 删除数据库

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

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

总结

 3. 创建表

3.1 创建方式1

必须具备：

语法格式：

必须指定：

可选指定：

3.2 创建方式2

 4. 修改表

 使用 ALTER TABLE 语句可以实现

4.1 追加一个列

语法格式

举例：

4.2 修改一个列

语法格式：

举例

 4.3 重命名一个列

语法格式

举例：

4.4 删除一个列

语法格式

举例

5. 重命名表

方式一：使用RENAME

方式二：

6. 删除表

语法格式

举例

7. 清空表

TRUNCATE TABLE语句：

如：

大总结

1. MySQL中的数据类型

2. 创建和管理数据库

 方式1：创建数据库

CREATE DATABASE 数据库名;

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

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

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

CREATE DATABASE IF NOT EXISTS 数据库名;

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

总结

数据库的创建创建数据库create database 数据库名；判断在创建数据库create database if not exists 数据库名；创建数据库指定字符集create database 数据库名 character set 字符集；创建数据库指定排序方式create database 数据库名 collate 排序方式；创建数据库指定字符集和排序方式create database 数据库名 character set 字符集 collate 排序方式；查询数据库的字符集和排序方式mysql18:默认 utf8mb4 utf8mb4_0900_ai_cishow variables like 'character_set_database';show variables like 'collate_database';

2.2 使用数据库

 查看当前所有的数据库

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

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

SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

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

使用/切换数据库

USE 数据库名;

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

据库名.”。

总结

1.2 数据库查看查看所有库show databases;查看当前使用库select database();查看库下所有表show tables from 数据库名;查看创建库的信息和语句show create database 数据库名;选中和切换库;use 数据库名;注意: 对数据进行操作之前,必须要选中库! use 数据库; use 数据库名;

 2.3 修改数据库

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

1.3 数据库修改修改字符集alter database 数据库名 character set 字符集;修改排序方式alter database 数据库名 collate 排序方式;修改字符集和排序方式alter database 数据库名 character set 字符集 collate 排序方式;注意: 数据库中没有修改名称的指令,如果你想改名字,备份数据,删除旧库,创建新库,恢复数据即可!

DROP DATABASE 数据库名;

DROP DATABASE IF EXISTS 数据库名;

总结

 1.4 数据库删除直接删除drop database 数据库名;判断删除drop database if exists 数据库名;注意: 删除是一个危险命令,确认明确,再操作!!

 3. 创建表

 修改和删除表修改表中列添加列alter table 表名 add 列名 类型 [first|alter 列名] ;修改列名alter table 表名 change 原列名 新列名 新类型 [first|alter 列名] ;修改列类型alter table 表名 modify 列名 新类型 [first|alter 列名] ;删除列alter table 表名 drop 列名;修改表名alter table rename [to] 新表名;删除表drop table [if exists ] 表名;清空表数据truncate  table 表名;

3.1 创建方式1

必须具备：

• CREATE TABLE权限
• 存储空间

语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

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

必须指定：

1. 表名
2. 列名(或字段名)，数据类型，长度

可选指定：

1. 约束条件
2. 默认值

-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);

3.2 创建方式2

1. 使用 AS subquery 选项，将创建表和插入数据结合起来
2. 
3. 指定的列和子查询中的列要一一对应
4. 通过列名和默认值定义列

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

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
DESCRIBE dept80;

 4. 修改表

 修改和删除表修改表中列添加列alter table 表名 add 列名 类型 [first|alter 列名] ;修改列名alter table 表名 change 原列名 新列名 新类型 [first|alter 列名] ;修改列类型alter table 表名 modify 列名 新类型 [first|alter 列名] ;删除列alter table 表名 drop 列名;修改表名alter table rename [to] 新表名;删除表drop table [if exists ] 表名;清空表数据truncate  table 表名;

 使用 ALTER TABLE 语句可以实现

1. 向已有的表中添加列
2. 修改现有表中的列
3. 删除现有表中的列
4. 重命名现有表中的列

4.1 追加一个列

语法格式

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

举例：

SHOW CREATE TABLE 表名\G
ALTER TABLE dept80
ADD job_id varchar(15);

4.2 修改一个列

可以修改列的数据类型，长度、默认值和位置

语法格式：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名
2】;

ALTER TABLE dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;

 4.3 重命名一个列

使用 CHANGE old_column new_column dataType子句重命名列。

语法格式

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

举例：

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

4.4 删除一个列

删除表中某个字段的

语法格式

ALTER TABLE 表名 DROP 【COLUMN】字段名

举例

ALTER TABLE dept80
DROP COLUMN job_id;

5. 重命名表

 修改和删除表修改表中列添加列alter table 表名 add 列名 类型 [first|alter 列名] ;修改列名alter table 表名 change 原列名 新列名 新类型 [first|alter 列名] ;修改列类型alter table 表名 modify 列名 新类型 [first|alter 列名] ;删除列alter table 表名 drop 列名;修改表名alter table rename [to] 新表名;删除表drop table [if exists ] 表名;清空表数据truncate  table 表名;

方式一：使用RENAME

RENAME TABLE emp
TO myemp;

方式二：

ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略

必须是对象的拥有

6. 删除表

 修改和删除表修改表中列添加列alter table 表名 add 列名 类型 [first|alter 列名] ;修改列名alter table 表名 change 原列名 新列名 新类型 [first|alter 列名] ;修改列类型alter table 表名 modify 列名 新类型 [first|alter 列名] ;删除列alter table 表名 drop 列名;修改表名alter table rename [to] 新表名;删除表drop table [if exists ] 表名;清空表数据truncate  table 表名;

1. 在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
2. 数据和结构都被删除
3. 所有正在运行的相关事务被提交
4. 所有相关索引被删除

语法格式

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

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

举例

DROP TABLE dept80;

注意：DROP TABLE 语句不能回滚

7. 清空表

 修改和删除表修改表中列添加列alter table 表名 add 列名 类型 [first|alter 列名] ;修改列名alter table 表名 change 原列名 新列名 新类型 [first|alter 列名] ;修改列类型alter table 表名 modify 列名 新类型 [first|alter 列名] ;删除列alter table 表名 drop 列名;修改表名alter table rename [to] 新表名;删除表drop table [if exists ] 表名;清空表数据truncate  table 表名;

TRUNCATE TABLE语句：

• 删除表中所有的数据
• 释放表的存储空间

如：

TRUNCATE TABLE detail_dept;

大总结

/*1.1数据库的创建创建数据库create database 数据库名；判断在创建数据库create database if not exists 数据库名；创建数据库指定字符集create database 数据库名 character set 字符集；创建数据库指定排序方式create database 数据库名 collate 排序方式；创建数据库指定字符集和排序方式create database 数据库名 character set 字符集 collate 排序方式；查询数据库的字符集和排序方式mysql18:默认 utf8mb4 utf8mb4_0900_ai_cishow variables like 'character_set_database';show variables like 'collate_database';*//*练习:创建ddl_d1库,指定字符集为utf8,且排序方式用大小写敏感的utf8mb4_0900_as_cs模式
*/
CREATE DATABASE IF NOT EXISTS ddl_d1 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;/*1.2 数据库查看查看所有库show databases;查看当前使用库select database();查看库下所有表show tables from 数据库名;查看创建库的信息和语句show create database 数据库名;选中和切换库;use 数据库名;注意: 对数据进行操作之前,必须要选中库! use 数据库; use 数据库名;
*/SHOW DATABASES;SELECT DATABASE();USE mysql;SHOW TABLES FROM mysql;SHOW CREATE DATABASE ddl_d1;/*
1.3 数据库修改修改字符集alter database 数据库名 character set 字符集;修改排序方式alter database 数据库名 collate 排序方式;修改字符集和排序方式alter database 数据库名 character set 字符集 collate 排序方式;注意: 数据库中没有修改名称的指令,如果你想改名字,备份数据,删除旧库,创建新库,恢复数据即可!
*//*1.4 数据库删除直接删除drop database 数据库名;判断删除drop database if exists 数据库名;注意: 删除是一个危险命令,确认明确,再操作!!
*/# 2. DDL之数据表操作
/*2.1 建表语法建表语法总结create table [if not exist] 表名(# 列的信息列名 类型 [列的约束] [列的注释],列名 类型 [列的约束] [列的注释],...列名 类型 [列的约束] [列的注释])[描述][注释]建表事项1. 表名 列名 列类型必须填写的2. 推荐使用if not exists3. 注释不是必须得,但是是很有必要的!4. 列之间使用,隔开,最后一列没有,
*/CREATE DATABASE IF NOT EXISTS book_libs CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;USE book_libs;CREATE TABLE IF NOT EXISTS books(# 列的信息book_name VARCHAR(20) COMMENT '图书名',book_price DOUBLE(4,1) COMMENT '图书价格',book_num INT COMMENT '图书数量'
)CHARSET = utf8mb4 COMMENT '图书表';SHOW TABLES FROM book_libs;/*2.3 建表类型[整数]整数类型(类型,占有空间,范围)标准sql:int / integer   4字节  无符号 0 - 2/32-1  有符号 -2 31 / 2 / 31 -1smallint        2字节  无符号 0 - 2/16-1  有符号 -2 17 / 2 / 17 -1mysql方言:tinyint         1字节  无符号 0 - 2/8 -1  有符号 -2 7 / 2/7-1mediumint       3字节  无符号 0 - 2/24 -1  有符号 -2 23 / 2/23-1bigint          8字节  无符号 0 - 2/64 -1  有符号 -2 63 / 2/63-1有符号: 列名 整数类型 -> 有符号| 有符号 有负值和正值列名 整数类型 unsigned -> 无符号|无符号 没有负值,都是正值,将负值部分,绝对值后,加入正值部分!注意: 选合适范围,范围合适先占有空间最小的!创建一个ddl_d1库中,创建一个t1表,包含: 年龄和学号(范围不确定,但是没有负值)*/CREATE TABLE t1(t1_age TINYINT UNSIGNED COMMENT '年龄,无符号,范围就是 0 - 255',t1_number BIGINT UNSIGNED COMMENT '学号,最大的,且没有负号'
);/*2.4 建表类型[浮点/定值]浮点类型(类型,M,D)float(m,d)   4字节   m 24   d 8double(m,d)  8字节   m 53   d 30定值类型(类型,M,D)decimal(m,d) 动态占有 m 65   d 30使用对比:精度要求不高,例如:身高,体重 float / double精度要求特别高,钱 工资,价格 decimal*//*2.5 建表类型[浮点/定值]浮点类型(类型,M,D)float(m,d)   4字节   m 24   d 8double(m,d)  8字节   m 53   d 30定值类型(类型,M,D)decimal(m,d) 动态占有 m 65   d 30使用对比:精度要求不高,例如:身高,体重 float / double精度要求特别高,钱 工资,价格 decimal*//*2.6 建表类型[字符串]字符串类型char 固定长度类型 一旦声明固定占有对应的空间 M 最大255 [性能较好]varchar 可变长度类型 一旦声明,可以插入小于的长度,自动进行伸缩 M 占有的空间不能超过一行的最大显示65535字节 [性能一般]text 大文本类型,声明不要指定长度,有固定的大小限制, text [65535] , 不占有一行的最大限制空间细节理解1. char声明的时候可以不写m char = char(1)2. char声明了最大长度限制,输入的文本小于长度限制,会在右侧补全空格 char(5) -> 'abc' -> 'abc  '3. char类型在读取的时候,会自动去掉右侧的空格 'abc  ' -> 'abc'4. varchar声明的时候,必须添加m限制 varchar(m)5. mysql4.0以下版本 varchar(20) -> 20字节限制  mb3 -> 66. mysql5.0以上版本 varchar(20) -> 20字符限制7. varchar类型中识别空格,插入空格 读取也是有空格演示varchar最大限制前提: mysql中一行数据最大的占有空间是65535字节,除了TEXT or BLOBs类型的列(不占有65535限制 法外狂徒)一行-> name1列 -> name1列占有的最大空间65535字节varchar类型默认会使用1字节标识是否为null -> 65535-1 = 65534字节字符集utf8mb4 1个字符 = 4个字节   65534 / 4 = 16383解决方案 :1. 缩小字符大小限制 m变小 [不合理]2. 可以修改字符集 [不合理]3. 可以将字符串类型变成TEXT,不占有一行的限制*/CREATE TABLE t1(name1 VARCHAR(16000),name2 TEXT
)CHARSET=utf8mb4;/*2.7 建表类型[时间类型]时间类型year    1 yyyy | yy   '1910' | 1910time    3 HH:MM:SS    '10:10:10'date    3 YYYY-MM-DD  '1910-10-10'datetime 8 YYYY-MM-DD HH:MM:SS '1910-10-10 10:10:10'timestamp 4 YYYY-MM-DD HH:MM:SS '1970-10-10 10:10:10'注意情况1. year可以写两位年 00 - 99  00-69 =2000 - 2069 70 - 99 = 1970 - 1999 推荐四位的年2. 时间类型,要遵循他们的格式插入,插入的时候就是按字符插入,时间默认不会自动赋值扩展自动填写时间:1.插入默认添加时间datatime | timestamp default current_timestamp2.修改默认更改时间datatime | timestamp default current_timestamp on update current_timestamp演示: 创建t2表,注册日期 字段插入自动添加时间,更新数据不变更新日期 字段插入自动添加时间,更新数据时间改变
*/CREATE TABLE t2(name1 VARCHAR(20),reg_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册日期,插入数据自动维护时间',up_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT  '更新日期,插入数据填写时间,更新数据自动改变时间'
);/*2.8 修改和删除表修改表中列添加列alter table 表名 add 列名 类型 [first|alter 列名] ;修改列名alter table 表名 change 原列名 新列名 新类型 [first|alter 列名] ;修改列类型alter table 表名 modify 列名 新类型 [first|alter 列名] ;删除列alter table 表名 drop 列名;修改表名alter table rename [to] 新表名;删除表drop table [if exists ] 表名;清空表数据truncate  table 表名;
*/
/*2.9 表操作实战要求1：创建表格employees要求2：将表employees的mobile字段修改到code字段后面。要求3：将表employees的birth字段改名为birthday;要求4：修改sex字段，数据类型为char（1）。要求5：删除字段note；要求6：增加字段名favoriate_activity，数据类型为varchar（100）；要求7：将表employees的名称修改为 employees_info*/CREATE TABLE  employeess(emp_num INT,last_name VARCHAR(50),first_name VARCHAR(50),mobile VARCHAR(25),CODE INT,job_time VARCHAR(50),birth DATE,note VARCHAR(255),sex VARCHAR(5)
);# 要求2：将表employees的mobile字段修改到code字段后面。
ALTER TABLE employeess MODIFY mobile VARCHAR(25) AFTER CODE;# 要求3：将表employees的birth字段改名为birthday;
ALTER TABLE employeess CHANGE birth birthday DATE;# 要求4：修改sex字段，数据类型为char（1）。
ALTER TABLE employeess MODIFY sex CHAR(1);DESC employeess;# 要求5：删除字段note；
ALTER TABLE employeess DROP note;#要求6：增加字段名favoriate_activity，数据类型为varchar（100）；
ALTER TABLE employeess ADD favoriate_activity VARCHAR(100);#要求7：将表employees的名称修改为 employees_info
ALTER TABLE employeess RENAME employees_info;