Mysql速成笔记1(DDL)

原创已于 2025-10-03 13:35:36 修改 · 216 阅读

CC 4.0 BY-SA版权

文章标签：

于 2025-10-02 20:52:41 首次发布

# 1.DDL之数据库创建
/*
    1.1 数据库创建
        创建数据库
        CREATE DATABASE 数据库名;
        判断再创建数据库
        CREATE DATABASE IF NOT EXITS 数据库名;
        创建数据库指定字符集
        CREATE DATABASE IF NOT EXITS CHARACTER SET 字符集;
        创建数据库指定排序方式
        CREATE DATABASE IF NOT EXITS COLLETE 排序方式;
        创建数据库指定字符集和排序方式
        CREATE DATABASE IF NOT EXITS CHARACTER SET 字符集 COLLETE 排序方式;
        查询数据库的字符集和排序方式
        # mysql8：默认 utf8mb4 utf8mb4_0900_ai_ci
        show variables like 'character_set_database' 在数据库中想写字符是单引号双引号都行但是推荐是单引号
        show variables like 'collations_set_database'
        练习：创建ddl_dl库，指定字符集为utf8，且排序方式用大小写敏感的utf8mb4_0900_as_cs模式
 */
 CREATE DATABASE IF NOT EXISTS ddl_dl CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs #utf8等同于utf8mb3 这俩不配套
 
 /* 
     1.2 数据库查看
         查看所有库
         show databases;
         查看当前使用库 
         select database();
         查看库下所有表
         show tables from 数据库名；
         查看创建库的信息和语句
         show create database 数据库名;
         选中和切换数据库名
         use 数据库名;
         
 */

SHOW DATABASES
         
SELECT DATABASE();

USE ddl_dl

SHOW TABLES FROM mysql;

SHOW CREATE DATABASE ddl_dl;

/*
    1.3 数据库修改
        修改数据库字符集
        ALTER DATABASE 数据库名 CHARACTER SET 字符集;
        修改数据库排序方式
        ALTER DATABASE 数据库名 COLLATE 排序方式;
        修改数据库字符集和排序方式
        ALTER DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序方式;
        注意：
        1. 数据库修改只能修改字符集和排序方式，不能修改数据库名
        2. 如果你想改名字，被反数据，删除旧库，创建新库，恢复数据即可！

    1.4 数据库删除
        直接删除数据库
        DROP DATABASE 数据库名;
        判断删除数据库
        DROP DATABASE IF EXISTS 数据库名;
        注意：删除是一个危险命令，确认明确，再操作!!
*/

/* 
    1.5数据库管理练习
    场景1:
    假设你正在为一个多语言的博客平台设计数据库。你需要创建一个名为 bog platlorm 的数据库，
    支持存储多语言的文章和评论。由于博客平台可能包含来自不同语言的用户，你决定使用 utf8mb4字符集，
    排序方式选择默认值，以支持广泛的Unicode 字符
    场景2:
    查看数据库字符集和排序规则
    场景3:
    假设在后续的发展中，你决定将排序方式修改为 utf8mb4 0900 as cs，以实现大小写敏感的比较。
    场景4:查看数据库字符集和排序规则
    场景5:项目惨遭放弃，需要删除项目库，并且跑路
 */

CREATE DATABASE IF NOT EXISTS blog_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_set_database';

ALTER DATABASE blog_platform COLLATE utf8mb4_0900_as_cs;

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_set_database';


DROP DATABASE IF EXISTS blog_platform;

#2.DDL之数据表操作
/*
    2.1 建表谱法建表谱法总结
    create table [if not exist]表名
    (#列的信息
    列名 类型[列的约束][列的注释]，
    列名 类型[列的约束][列的注释]，
    列名 类型[列的约束][列的注释]
    )[描述] [注释]
建表事项
1.表名 列名 列类型必须填写的
2.推荐使用if not exists
3.注释不是必须得，但是是很有必要的!
4.列之间使用,隔开,最后一列没有

*/

/* 
2.2 建表实战
建表语法总结
    场录1:
    假设你正在设计一个简单的在线图书管理系统。需要创建一个名为book libs的数据库,
    你决定使用 utf8mb4 字符集，排序方式选用大小写敏感的utf8mb4 0900_as_cs。
    场录2:创建一个图书表books，判断不存在再创建，并且手动设置books表字符集为utf8mb4，
    添加表注释内容国时图书表books中应该以下列:
    图书名称book name列,类型为varchar(20)，加注释。
    图书价格bookprice列，类型为double(4,1)，添加注释
    图书数量booknum列，类型为int，添加注释。
    按以上要求完成图书表的创建! 
*/
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 '图书表';#在mysql中可以用双引号也可以用单引号,推荐使用单引号

SHOW TABLES FROM book_libs;book_libs;

/*
2.3 建表类型[整数]
整数类型(类型,占有空间,范围)

标准sql:
int /integer 4字节 无符号0-2^32-1 有符号 -2^31-2^31-1 
smallint     2字节 无符号0-2^16-1 有符号 -2^15-2^15-1
mysql方言:
tinyint      1字节 无符号0-2^8-1 有符号 -2^7-2^7-1
mediumint    3字节 无符号0-2^24-1 有符号 -2^23-2^23-1
bigint       8字节 无符号0-2^64-1 有符号 -2^63-2^63-1
有符号:列名 整数类型 ->有符号!有负号 有负值和正值
      列名 整数类型 unsigned ->无符号|无负号 没有负值,都是正值,将负值部分,绝对值后,加入正值部分!
      这个无符号只能跟着数值类型 并且紧贴着数值类型写，他俩是一个组合，要谢就一起写。
注意:选合适范围,范围合适先选占有空间最小的！
    创建一个dd1d1库中,创建一个t1表,包含:年和学号(范围不确定，但是没有负值)

*/

CREATE TABLE IF NOT EXISTS t1(
    t1_age TINYINT unsigned COMMENT '年龄,无符号,范围是 0 -255',
    t1_id BIGINT unsigned COMMENT '学号,最大的,且没有负号'
);

/* 
2.4 建表类型[浮点/定值]
浮点类型(类型,M,D) 基于二进制存储
    float(m，d) 4宁节 m 24 d 8
    double(m,d) 8宁节 m 53 d 30 这俩加unsigned 不会把负值迁移到正值(与int相对)
定值类型(类型，M，D) 基于十进制存储
    decimal(m,d) 动态占有(不同Mysql版本的计算公式有差异) m 65 d 30
    使用对比:精度要求不高,例如:身高,体重 float / double
    精度要求特别高，钱工资，价格 decimal
 */

 /* 
 2.6建表类型[字符串]
字符串类型
    char 周定长度类型一旦声明固定占有对应的空间最大255 char类型永远不为空 [性能较好,因为不需要去做运算]
    varchar 可变长度类型 一旦声明,可以插入小于的长度,自动进行伸缩 M ，理论上说M的大小没有限制，但是其占有的空间不能超过一行的最大限制65535字节 varchar有可能为空[性能一般,因为内部需要去做伸缩运算]
    text 大文本类型,声明不要指定长度,有固定的大小限制，text[65535]，不占有一行的最大限制空间 [性能较差,因为读写会带来大量的IO时间消耗]
        大文本，建议存储到文本文件，使用varchar记录文本文件地址，不是用TEXT，直接存大文本，因为TEXT性能较差，占用空间较大，读取消耗的时间太多
细节理解1.char声明的时候可以不写m char = char(1)
        2.char声明了最大长度限制,输入的文本小于长度限制,会在右侧补全空格 char(5)->'abc'->'abc  '
        3.char类型在读取的时候,会自动去掉右侧的空格'abc '->'abc'
        4.varchar声明的时候，必须添加m限制varchar(m)
        5.mysg14.0以下版术 varchar(20)->20 字节限制mb3->6
        6.mysq15.0以上版本varehar(20)->20字符限制
        7.varchar类型中识别空格,插入空格读取也是有空格
演示varchar最大限制
    前提: mysql中一行数据最大的占有空间是65535字节，除了TEXT和BLOBs类型的列
        一行->name1列->name1列占有的最大空间65535字节
        varchar类型默认会使用1字节标识是否为nu11->65535-1=65534字节
        字符集utf8mb4 1个字符=4个字节 65534/4=16383.xxx
解决方案:
    1.缩小字符大小限制 m变小[不合理]
    2.可以修改字符集[不合理]
    3.可以将字符串类型变成TEXT,不占有一行的限制
  */

USE ddl_dl;

CREATE TABLE IF NOT EXISTS t1(
    name1 VARCHAR(16000) COMMENT '姓名,固定长度10字节',
    name2 VARCHAR(384) COMMENT '年龄,可变长度3字节' 
)charset=utf8mb4 COMMENT '即使这样也会报错，因为每行最多只能有16383个字符';

CREATE TABLE IF NOT EXISTS t2(
    name1 VARCHAR(16000) COMMENT '姓名,固定长度10字节',
    name2 TEXT COMMENT '年龄,可变长度3字节' 
)charset=utf8mb4;
/* 
2.7 建表类型[时间类型]
时间类型
    year 1 yyyy | yy '09' 09
    time 3 HH:MM:SS '10:10:10'
    date 3 YY-MM-DD '2000-10-10'
    ◇atetime 8 YY-MM-DD HH:MM:SS
    timestamp 4 YY-MM-DD HH:MM:SS
注意情况
    1.year类型有两位或者四位的表达形式,两位00-69 =2000-2069 70-99=1970- 1999两位需要转换记忆不推荐
    2.时间类型就是一个特殊格式的字符串,插入数据的时候'' , 时间类型需要自动赋值,需要手动添加相关的设置!
扩展自动填写时间:
    1.插入默认添加时间
    datetime |timestamp default current timestamp ;
    2.修改默认更改时间(插入的默认时间)
    datetime | timestamp default current timestamp on update current timestamp;
演示:创建t2表，
    注册日期 字段插入自动添加时间,更新数据不变(插入数据的时候,自动维护时间,后续修改数据,时间不变!)
    更新日期 字段插入自动添加时间,更新数据时间改变(插入数据需要自动维护时间,修改数据也需要维护时间!)

 */

CREATE TABLE t2(
NAME VARCHAR(20) COMMENT '姓名',
register_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '插入自动维护时间',
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新自动维护时间'
)

/* 
2.8创建表实战
场景2:
创建一个学生表(student)米存储借书的学员信息，其中应包含学生姓名、年龄、身高、生日以及注册时间和更新时间等属性

student->字符集->默认
姓名->stu_name varchar(10)
性别->stu_sex char(1) COMMENT '男M,女F'
年龄-> stu_age tinyint unsigned COMMENT '0-255'
身高-> stu_height double(4,1) COMMENT '4位整数,1位小数'
生日-> stu_birthday date COMMENT '2000-10-10'
注册-> stu_regtime timestamp default current timestamp COMMENT '插入自动维护时间'
更新-> stu_uptime timestamp default current timestamp on update current timestamp COMMENT '更新自动维护时间'


 */
CREATE DATABASE IF NOT EXISTS books_libs CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs #utf8等同于utf8mb3 这俩不配套
 
 CREATE TABLE IF NOT EXISTS student(
    stu_name VARCHAR(10) COMMENT '姓名',
    stu_sex CHAR(1) COMMENT '男M,女F',
    stu_age TINYINT UNSIGNED COMMENT '年龄0-255',
    stu_height DOUBLE(4,1) COMMENT '身高 4位整数,1位小数',
    stu_birthday DATE COMMENT '生日 2000-10-10',
    stu_regtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册日期 插入的时候自动维护',
    stu_uptime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期 插入和更新的时候自动维护时间'
 )charset=utf8mb4 COMMENT '学生表';

/* 
2.8 修改和删除表
修改表中列
    添加列
    alter table 表名 add 列名 类型 [first |after 原列名]
    修改列名
    alter table 表名 change 原列名 新列名 新类型[first |after 原列名]修改列类型
    修改列类型
    alter table 表名 modify 列名 新类型 [first |after 原列名]
    删除列
    alter table 表名 drop 列名:
    修改表名
    alter table 表名 rename[to]新的表名;
    删除表
    drop table [if exists]表名:
    清空表数据
    truncate table 表名:  #删除表中数据+ 删除表的关联记录 
*/