2-数据库基本ddl操作
1.针对库的增删改查：
# 增
create database db1;
create database db2 charset='gbk';
# 查
show databases; # 查所有
show create database db1; # 查单个
# 改
alter database db1 charset='gbk';
# 删
drop database db2;
2.针对表的增删改查
# 查看当前所在库的名字
select database();
# 切换库
use db1;
# 增
create table t1(id int,name char(4));
create table db2.t1(id int); 也可以用绝对路径的方式操作不同的库
# 查
show tables; # 查看当前库下所有的表名
show create table t1;
desc t1; # describe t1;
# 改
alter table t1 modify name char(16);
# 删
drop table t1;
3.针对数据的增删改查
# 增
insert into t1 values(1,'jason'),(2,'egon'),(3,'tank');
# 查
select * from t1; 该命令当数据量特别大的时候不建议使用
select name from t1;
# 改
update t1 set name='DSB' where id > 1;
# 删
delete from t1 where id > 1;
delete from t1 where name='jason';
# 将表数据清空
delete from t1;
4.存储引擎
日常生活中文件格式有很多种,并且针对不同的文件格式会有对应不同存储方式和处理机制(txt,pdf,word,mp4...)
针对不同的数据应该有对应不同的处理机制来存储,存储引擎就是不同的处理机制
Mysql主要存储引擎：
1.innodb：mysql5.5版本及之后默认的存储引擎，支持事务，行锁，外键，存储数据更加安全；
2.myisam：mysql5.5版本之前默认的存储引擎，速度要比innodb更快，但是我们更加注重数据安全
3.memory：内存引擎(数据全部存放在内存中)断电数据丢失
4.blackhole：无论存什么,都立刻消失(黑洞)
查看所有的存储引擎：
show engines;
用不同的存储引擎创建4张表
create table t1(id int) engine=innodb; # 表数据(ibd)，表结构(frm)
create table t2(id int) engine=myisam; # 表数据(MYD)，表结构(frm)，索引(MYI)
create table t3(id int) engine=memory; # 表结构(frm)(数据在内存，无需文件存储)
create table t4(id int) engine=blackhole; # 表结构(frm)
往4张表中存数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
补充：
严格模式
如何查看严格模式：show variables like "%mode";
修改严格模式：
set session 只在当前窗口有效
set global 全局有效
set global sql_mode = 'STRICT_TRANS_TABLES';
修改完之后，重启服务
5.创建表的完整语法
create table 表名(
字段名1 类型(宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件
)
注意：
1.在同一张表中字段名不能重复
2.宽度和约束条件是可选(可写可不写),字段名和字段类型是必须的，约束条件也支持写多个
3.最后一行不能有,号
补充：
宽度：一般情况下是指的对存储数据的限制
create table t1(name char);默认宽度是1
约束条件：null not null非空
create table t1(id int,name char not null);
总结：宽度是用来限制数据的存储，约束条件是在宽度的基础之上增加的额外的约束
6.基本数据类型
1.整型
分类：TINYINT SMALLINT MEDUIMINT INT BIGINT
作用：存储年龄，等级，id，号码等
以TINYINT为例：
是否有符号：默认情况下是带符号的
超出会如何：超出限制报错
create table t5(id tinyint);
insert into t5 values(-129),(256); 报错，不允许插入超出宽度
约束条件之unsigned无符号
create table t6(id tinyint unsigned);
insert into t6 values(-10); 报错，不允许插入负值
以INT为例：
int默认也是带符号的
整型默认情况下都是带符号的
create table t7(id int);
insert into t7 values(-10);
整型括号里面的数字不是表示限制位数而是显示长度,最大范围11位
create table t8(id int(8));
insert into t8 values(123456789); # 超出8位有几位存几位，但是要遵循最大范围
insert into t8 values(12345); # 没有超出8位默认用空格填充至8位
总结：针对整型字段，括号内无需指定长度，它默认的宽度足够显示所有的数据了
2.浮点型
分类：FLOAT，DOUBLE，DECIMAL
作用：身高，体重，薪资
存储限制：
float(255,30) # 总共255位，小数部分占30位
double(255,30) # 总共255位，小数部分占30位
decimal(65,30) # 总共65位，小数部分占30位
精确度验证：
create table t9(id float(255,30));
create table t10(id double(255,30));
create table t11(id decimal(65,30));
insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);
总结：float < double < decimal # 结合实际应用场景，三者都能使用
3.字符类型
char：定长，char(4)数据超过四个字符直接报错，不够四个字符空格补全
varchar：变长，varchar(4)数据超过四个字符直接报错，不够有几个存几个
验证：
create table t12(name char(4));
create table t13(name varchar(4));
insert into t12 values('a');
insert into t13 values('a');
首先肯定的是char硬盘上存的绝对是真正的数据，带有空格的，但是显示的时候mysql会自动将多余的空格删除
修改sql_mode让Mysql不要自动剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
select char_length(name) from t12;
select char_length(name) from t13;
char：
缺点：浪费空间
优点：存取都很简单
直接按照固定的字符存取数据即可
jason egon alex tank
存按照五个字符，取也按照五个字符取
varchar：
优点：节省空间
缺点：存取较为麻烦
1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank
存的时候需要制作报头，取得时候也需要先读取报头，之后才能读取真实数据
4.日期类型
分类：
date年月日：2022-05-16
datetime年月日时分秒：2022-05-16 11:11:11
time时分秒：11:11:11
Year年：2022
create table student(
id int,
name varchar(10),
bron_year year,
birth date,
study_time time,
reg_time datetime
);
5.枚举与集合类型
枚举(enum)：多选一
集合(set)：多选多
create table user(
id int,
name varchar(10),
gender enum('male','female','others')
);
insert into user values(1,'jason','male');
insert into user values(2,'egon','xxoo'); # 报错
枚举字段，后期在存数据时只能从枚举里面选择一个存储
create table teacher(
id int,
name varchar(10),
gender enum('male','female','others'),
hobby set('read','DBJ','hecha')
);
insert into teacher values(1,'jason','male','read');
insert into teacher values(2,'egon','male','read,DBJ');
insert into teacher values(3,'tank','female','吃生蚝'); # 报错
集合可以写一个或多个，但是不能写集合里面没有的
七.约束条件
1.default默认
create table t1(
id int,
name varchar(10),
gender enum('male','female','others') default 'male'
);
insert into t1(id,name) values(1,'jason');
insert into t1 values('2','egon','female');
2.unique唯一
单列唯一：
create table t2(
id int unique,
name varchar(10)
);
insert into t2 values(1,'egon'),(2,'jason');
insert into t2 values(1,'egon'),(1,'egon'); # 报错
联合唯一：ip和port，单个可以重复，但是加在一起必须是唯一的
create table t3(
id int,
ip varchar(10),
port int ,
unique(ip,port)
);
insert into t3 values(1,'127.0.0.1',8080);
insert into t3 values(2,'127.0.0.1',8081);
insert into t3 values(3,'127.0.0.2',8080);
insert into t3 values(4,'127.0.0.1',8080); # 报错
3.primary key主键
1）单从约束效果来看primary key等价于not null + unique非空且唯一
create table t4(id int primary key);
insert into t4 values(1),(1); # 报错
insert into t4 values(null); # 报错
insert into t4 values(1),(2);
2）它除了有约束效果之外，它还是Innodb存储引擎组织数据的依据，innodb存储引擎在创建表时必须要有primary key，因为它类似于书的目录，能够帮助提升查询效率并且也是建表的依据
情况一：一张表中有且只有一个主键，如果你没有设置主键，那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t5(
id int,
name varchar(10),
age int not null unique,
addr varchar(10) not null unique
);
情况二：如果表中没有主键也没有其它任何的非空且唯一字段，那么innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用它，就无法提升查询速度
情况三：一张表中通常都应该有一个主键字段，并且通常将id/uid/sid字段作为主键
# 单个字段主键
create table t6(
id int primary key,
name varchar(10)
);
# 联合主键(多个字段联合起来作为表的主键，本质还是一个主键)
create table t7(
ip varchar(16) ,
port int,
primary key(ip,port)
);
# auto_increment 自增
当编号特别多的时候，人为地去维护太麻烦，注：auto_increment通常加在主键上，不能给普通字段加
create table t8(
id int primary key auto_increment,
name varchar(16)
);
结论：以后在创建表地id(数据的唯一标识id,uid,sid)字段时：id int primary key auto_increment
注意：delete from 在删除表中数据的时候,主键的自增不会停止；truncate table 清空表数据并且主键重置
4.foreign key外键：外键就是用来帮助我们建立表与表之间关系的
表关系分类：一对多，多对多，一对一
1）一对多：员工表与部门表，一个员工不能对应多个部门，一个部门可以对应多个员工。结论：员工表与部门表存在单向一对多关系
1.一对多表关系，外建字段建在多的一方
2.在创建表的时候，一定要先建被关联表
3.在录入数据时，也必须先录入被关联表
create table dep(
id int primary key auto_increment,
dep_name varchar(16),
dep_desc varchar(32)
);
create table emp(
id int primary key auto_increment,
name varchar(16),
gender enum('male','female','others') default 'male',
dep_id int，
constraint pk_id foreign key(dep_id) references dep(id)
);
insert into dep(dep_name,dep_desc) values('教学部','教书育人'),('外交部','四处奔波'),('技术部','技术有限部门');
insert into emp(name,dep_id) values('egon',1),('jason',2),('tank',2),('kevin',3)
# 修改dep表里面的id字段
update dep set id =20 where id =2; 不行
# 删除dep表里面的数据
delete from dep; 不行
解决方法一：先删除emp表数据，再删除dep表数据，操作太过频繁
解决方法二：真正做到数据之间有关系，更新就同步更新，删除就同步删除
联级更新，联级删除
create table dep(
id int primary key auto_increment,
dep_name varchar(16),
dep_desc varchar(32)
);
create table emp(
id int primary key auto_increment,
name varchar(16),
gender enum('male','female','others') default 'male',
dep_id int，
constraint pk_id foreign key(dep_id) references dep(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
2）多对多：book表与作者表，一本书可以对应多个作者，一个作者也可以对应多本书
针对多对多字段表关系，不能在两张原有的表中创建外键
需要单独再开设一张，专门用来存储两张表数据之间的关系
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
constraint pk_id foreign (author_id ) references author(id )
on update cascade # 同步更新
on delete cascade, # 同步删除
constraint pk_id foreign (book_id ) references book(id )
on update cascade # 同步更新
on delete cascade # 同步删除
);
3）一对一：用户表与用户详情表。一个用户不能对应多个用户详情，一个用户详情也不能对应多个用户。
结论：单向的一对多都不成立，那么这个时候表的关系就是一对一或者没有关系
一对一外键字段在任意一方都可以，但是推荐建在查询频率比较高的表中
create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);
create table author(
id int primary key auto_increment,
name varchar(16),
age int,
authordetail_id int unique,
constraint pk_id foreign (authordetail_id ) references authordetail(id )
on update cascade # 同步更新
on delete cascade # 同步删除
);
5.修改表
1）修改表名
alter table 表名 rename 新表名;
2）增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
3）删除字段
alter table 表名 drop 字段名;
4）修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件; # modify一般用来修改字段数据类型，约束条件等，不能修改字段名
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
6.复制表结构
create table 表名 as select * from 目标表 where 1=2;
