✨你好啊，我是“ 罗师傅”，是一名程序猿哦。
🌍主页链接：楚门的世界 - 一个热爱学习和运动的程序猿
☀️博文主更方向为：分享自己的快乐 briup-jp3-ing
❤️一个“不想让我曾没有做好的也成为你的遗憾”的博主。
💪很高兴与你相遇，一起加油！

前言

目标：Mysql数据库的使用及数据库分析及设计实践

数据操作语言DML

插入数据

格式1：按默认顺序往表中插入数据（含所有字段）

1
2
3

insert into tb_name values(value1,value2,....);
insert into emp values(1,'lisi',30);
insert into emp values(2,'tom',null);

注意事项：
- 插入数据如果是字符串、日期、json对象，需要使用单引号引起来
- values也可以写成value，values是标准写法
格式2：往表插入指定字段数据值

1 2	insert into tb_name(col_name [, col_name2, ..., col_namen]) values(value1 [,value2, ..., valuen]); insert into emp(id,name) values(3,'wangwu');

格式3：同时插入多条记录

insert into tb_name values(value1 [,value2, ..., valuen]),
(value1 [,value2, ..., valuen]),
...
(value1 [,value2, ..., valuen]);
# 或
insert into tb_name(col_name [, col_name2, ..., col_namen])
values(value1 [,value2, ..., valuen]),
(value1 [,value2, ..., valuen]),
...
(value1 [,value2, ..., valuen]);

1	insert into emp values(4,'xiaowang',1000),(5,'xiaoli',1200),(4,'xiaosong',2000);

格式4：特殊形式，将查询的结果插入到表中

INSERT子句中插入列应和子查询中的查询列表相对应。如果查询的列名和插入的列名不一致，建议起别名保持一致。

insert into tb_name(tar_col_name1 [, tar_col_name2, ...,tar_col_namen])
SELECT (src_col_name1 [, src_col_name2, ..., src_col_namen])
FROM src_tb_name
[where condition]

insert into emp
select id,last_name name,salary
from s_emp
where id <=8 and id >=6;

注意事项：如果要往表中插入完整列数据，insert后的列名可以不写

数据更新

update tb_name
set col_name=value1, col_name=value2, ... , col_namen=valuen
[where condition]

update emp set salary=2000 where id<3;

注意事项：不加where条件表示对所有数据更新，加where条件会选出符合条件的行进行更新。

数据删除

1
2
3

delete from tb_name [where condition]
delete from emp where name like 'xiao%';
delete from emp; # 清空表

注意：不加where条件表示删除所有数据，加where表示选出符合条件的数据删除。

数据库设计

软件开发

数据建模

E-R图

数据库设计

主键与外键

数据库范式

关系转换

数据库定义语言

DDL（Data Definition Language）是 SQL 中用于定义和管理数据库对象的语句集合。DDL语句主要包括 CREATE、ALTER、RENAME、DROP和TRUNCATE 。

创建表

-- 格式1
create table 表名(
字段名 数据类型 [列约束类型],
字段名 数据类型 [列约束类型],
字段名 数据类型 [列约束类型],
字段名 数据类型 [列约束类型],
字段名 数据类型 [列约束类型]
);

-- 格式2
create table 表名(
字段名 数据类型 [列约束类型],
字段名 数据类型 [列约束类型],
字段名 数据类型 [列约束类型],
字段名 数据类型 [列约束类型],
[表级约束],
[表级约束]
);

数据类型

数值类型

文本类型

注意事项：

普通字符串：CHAR，VARCHAR
存储文本：TEXT
存储二进制数据：BLOB

char：定长，即指定存储字节数后，无论实际存储了多少字节数据，最终都占指定的字节大小。默认只能存1字节数据，存取效率高
varchar：不定长，效率偏低，但是节省空间，实际占用空间根据实际存储数据大小而定。必须要指定存储大小varchar(50)

日期类型

日期时间函数

now() 返回服务器当前日期时间，格式对应datetime类型

时间操作

时间类型数据可以进行比较和排序等操作，在写时间字符串时尽量按照标准格式书写。

-- 马拉松比赛登记表
create table marathon (
id int primary key auto_increment,
name varchar(32),
birthday date,
register_time datetime,
performance time
);
insert into marathon values
(1,'Jack','1995-2-18','2021-12-18 19:20:20','2:48:58'),
(2,'Tony','2000-6-8','2021/12/22 9:30:25','2:29:8'),
(3,'Tom','1998-2-28','2022-1-1 16:21:21','2:33:55');
# 可以根据时间来进行比较
select * from marathon where birthday>='2000-01-01';
select * from marathon where birthday>='2000-07-01' and
performance<='2:30:00';

额外补充（了解即可）：存储选项型数据 ENUM，SET

ENUM 是一种特殊的数据类型，用于存储固定的选项列表。

drop TABLE if EXISTS my_table;
CREATE TABLE my_table (
	id INT,
	option_column ENUM('Option 1', 'Option 2', 'Option 3')
);

-- option_column只能添加上述3个值
INSERT INTO my_table(id, option_column) VALUES (1, 'Option 1');
INSERT INTO my_table(id, option_column) VALUES (2, 'Option 4');  # Data truncated for column 'option_column' at row 1
SELECT * FROM my_table;

SET 数据类型可以用来存储一组预定义的选项。SET 类型允许你在一个列中存储多个选项值，每个选项值可以被设置为 1（选中）或 0（未选中）。

-- 删除表
drop table my_table;
CREATE TABLE my_table (
    id INT,
    options SET('Option 1', 'Option 2', 'Option 3')
);
-- options可以同时添加多个值
INSERT INTO my_table(id, options) VALUES (2, 'Option 1,Option 2');
SELECT * FROM my_table;

常见约束

1）约束类型

NOT NULL
- 设置的列不能为空，换言之必须有值，所有类型的默认值都为null；
- NOT NULL 只能用于列级约束不能用于表级约束
- 所有类型和null进行运算都为null
UNIQUE：用来限制某个字段/某列的值不能重复
- 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一
- 创建唯一约束，不指定约束名字，mysql基于列名作为约束名
- 唯一性约束允许列值为空
- 唯一性约束默认自带索引
PRIMARY KEY：用来唯一标识表中的一行记录
- 主键约束列不允许重复，也不允许出现空值
- 一个表最多只能由一个主键约束
- 主键约束对应着表中的一列或者多列，组合列同样不允许重复，也不允许出现空值
- 创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引
- 删除主键约束了，主键约束对应的索引就会自动删除了
FOREIGN KEY：外键约束，维护表与表之间的关系

create table tb_name(
    col_name type primary key,
    col_name1 type
);

create table tb_name(
col_name type primary key,
col_name1 type,
col_name2 type
[constraint fg_name] foreign key(col_name2) references
tb_name(col_name) [on update [cascade|set null][on delete
[cascade|set null]]
);

# 命名规则
constraint tOrder_customerId_fk FOREIGN KEY(customer_id) REFERENCES t_customer(id),
constraint 表名(小驼峰)_字段名(小驼峰)_fk(表示外键) FOREIGN KEY(字段名) REFERENCES 外键表名(字段)

FOREIGN KEY
- 外键用于维护表与表之间的关系，外键必须引用另外一张表的主键或者唯一约束
- 创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名
- 创建表时就指定外键约束的话，先创建主键约束的表，再创建外键的表
- 删表时，先删外键的表，再删除主键表，删除数据同理
- 一张表可以创建多个外键，引用多个不同的表的主键维护关系
- 创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名
- 删除外键约束后，必须手动删除对应的索引
- 外键和主键类型必须一致
CHECK：检查某个字段的值是否符合要求，一般指的是值得范围
- MySQL5.7可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告
- MySQL8.0中可以使用check约束
DEFAULT
- 给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。默认值约束一般不在唯一键和主键列上加。

2）约束分类

表级约束

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(
字段1 数据类型 ,
字段2 数据类型 ,
字段3 数据类型 ,
......
[constraint 约束名] 表级约束
[constraint 约束名] 表级约束
......
)]engine=innodb default charset=utf8

列级约束

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(
字段1 数据类型 [constraint 约束名] [列级约束] ,
字段2 数据类型 [constraint 约束名] [列级约束] ,
字段3 数据类型 [constraint 约束名] [列级约束] ,
......
)]engine=innodb default charset=utf8;

# 案例1：普通的建表例子
create table student(
id int primary key,
name varchar(200) not null,
age int,
birthday date
);
drop table student;

# 案例2：使用四种列级约束 主键约束 非空约束 唯一约束 check约束
create table student(
id int primary key,
name varchar(100) not null,
email varchar(100) unique,
gender char(1) check(gender in('f','m')),
age int,
birthday date
);
drop table student;

# 案例3：使用列级约束声明外键约束
drop table if exists t_order;
drop table if exists t_customer;
create table t_customer(
id int primary key,
name varchar(200) not null
);
insert into t_customer values(1,'zs'),(2,'ls'),(3,'ww');
create table t_order(
id int primary key,
content varchar(200) not null,
customer_id int,
foreign key(customer_id) references t_customer(id)
);
insert into t_order values(1,'xiaomi',1),(2,'huawei',2),
(3,'phone',3);
-- 此时删除失败，因为另一张表 引用了该条记录
delete from t_customer where id = 3;

注意事项：非空约束(not null)，不能声明成表级约束

表级约束和列级约束对比：

表级约束和列级约束所写的位置不一样
not null约束不能用表级约束来声明
表级约束和列级约束声明语法稍有所不同
如果要声明的约束为联合主键、联合外键、联合唯一的时候，就一定要用表级约束
新版本外键约束只能为表级约束

特殊建表

# 案例1：建立一张表和s_dept一模一样，s_dept的表结构和表中的数据全部复制过来
create table test1
as
select * from s_dept; 

# 案例2：建立一张表和s_dept一模一样，只拿来s_dept的表结构，没有数据
create table test2
as
select * from s_dept
where 1=2;

# 案例3：建立一张表和s_dept一模一样，只复制表中某几个指定列的数据
create table test3
as
select id,last_name,salary
from s_emp;

级联动作

restrict(默认) : on delete restrict on update restrict
- 当主表删除记录时，如果从表中有相关记录则不允许主表删除
- 当主表更改主键字段值时，如果从表有相关记录则不允许更改

cascade：数据级联更新 on delete cascade on update cascade
- 当主表删除记录或更改被参照字段的值时，从表会级联更新
set null：on delete set null on update set null
- 当主表删除记录时，从表外键字段值变为null
- 当主表更改主键字段值时，从表外键字段值变为null

修改表

添加字段

alter table tb_name
add [column] col_name type [first|after col_name];

alter table user3
add name varchar(20);

alter table user3
add column uid int first; # 第一列插入，同理after最后一列

修改字段

alter table tb_name 
modify [column] col_name type [default 默认值];

alter table user3
modify column uid varchar(32) default '1';

注意事项：对默认值的修改只影响今后对表的修改，如果原表中有数据，类型不允许修改。

列重命名

alter table tb_name 
change [column] old_col_name new_col_name type;

alter table user3
change column name username text;

删除字段

alter table tb_name 
drop [column] col_name;

alter table user3 
drop column age;

重命名表

1
2
3

rename table old_tb_name to new_tb_name; # 建议记这个
或
alter table old_tb_name rename [to] new_tb_name;

删除表

1
2
3

drop table [if exists] tb_name [, tb_name1, ..., tb_namen];
drop table if exists my_user1;
drop table emp,emp1,test_bit,test_int; # 删除多个表

注意事项：

删除表的时候当前表不能被其它表引用
数据、结构、索引全都被删除
5.7版本删除【一批】表，其中表不存在也能成功执行，8.0版本必须保证表存在才可以删除（所以要使用 if exists）

截断表

TRUNCATE 是一个 DDL语句，用于快速删除表中的所有行，同时保留表的结构、索引和约束。

TRUNCATE 可以快速删除表中数据，相比于 DELETE 语句，它的执行速度更快，因为它不会逐行删除数据，而是直接删除整个表的数据。

1	truncate table tb_name;

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

❤️❤️❤️忙碌的敲代码也不要忘了浪漫鸭！

功崇惟志，业广惟勤💪