2 . DDL创表修改、数据类型、表关系

SQL语句分类

• DDL(数据定义语言)
  • 创建(create)、删除(drop)、修改(alter) 表
• DCL(数据控制语言)
  • 用于数据库授权、角色控制等管理工作 ,主要包含 grant 、revoke
• DML(数据操作语句)
  • 用于对数据库中数据的操作，主要包含 select、 insert 、update、delete、explain
• TCL(事务控制语句)
  • 用于数据库的事务管理,主要包含 savepoint、rollback、commit、set transaction

创建表完整语法(DDL)

#如果存在名为xx的数据库就删除它
drop database if exists 库名;

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)engine=innodb charset=utf8;

# []可选参数
# 引擎针对的是表,不是数据库。(没有指定使用默认的)

-- 如果存在名为school的数据库就删除它
drop database if exists school;

-- 创建名为school的数据库并设置默认的字符集和排序方式
create database school default charset utf8;

-- 切换到school数据库上下文环境
use school;

-- 创建学院表
create table tb_college
(
collid 		int auto_increment comment '编号',
collname 	varchar(50) not null comment '名称',
collintro 	varchar(500) default '' comment '介绍',
primary key (collid)
);

-- 创建学生表
create table tb_student
(
stuid 		int not null comment '学号',
stuname 	varchar(20) not null comment '姓名',
stusex 		boolean default 1 comment '性别',
stubirth 	date not null comment '出生日期',
stuaddr 	varchar(255) default '' comment '籍贯',
collid 		int not null comment '所属学院',
primary key (stuid),
foreign key (collid) references tb_college (collid)
);

-- 创建教师表
create table tb_teacher
(
teaid 		int not null comment '工号',
teaname 	varchar(20) not null comment '姓名',
teatitle 	varchar(10) default '助教' comment '职称',
collid 		int not null comment '所属学院',
primary key (teaid),
foreign key (collid) references tb_college (collid)
);

-- 创建课程表
create table tb_course
(
couid 		int not null comment '编号',
couname 	varchar(50) not null comment '名称',
coucredit 	int not null comment '学分',
teaid 		int not null comment '授课老师',
primary key (couid),
foreign key (teaid) references tb_teacher (teaid)
);

-- 创建选课记录表
create table tb_record
(
recid 		int auto_increment comment '选课记录编号',
sid 		int not null comment '选课学生',
cid 		int not null comment '所选课程',
seldate 	datetime default now() comment '选课时间日期',
score 		decimal(4,1) comment '考试成绩',
primary key (recid),
foreign key (sid) references tb_student (stuid),
foreign key (cid) references tb_course (couid),
unique (sid, cid)
);
# 总结: 宽度和约束条件为可选参数, 用来限制存放数据的规则

数据库的模式

# sql_mode: 反映数据库的全局变量
# 数据库模式限制的是客户端对服务器操作数据的方式(是否严格)

# 两种模式
no_engine_substitution：非安全性，默认。数据存储不下会部分丢失。
strict_trans_tables：安全模式   数据存储不下会报错

# 查看当前数据库模式:
show variables like "%sql_mode%";  # %匹配0~n个任意字符 => 模糊查询

# 设置为安全模式
set global sql_mode="strict_trans_tables";

# 重启连接(客户端)
quit

数据类型

• mysql数据库支持存放哪些数据
  整型| 浮点型 | 字符型| 时间类型 | 枚举类型 | 集合类型

• 整型

  '''类型
  tinyint：1字节 -128~127 *
  smallint：2字节
  mediumint：3字节
  int：4字节 -2147483648~2147483647 *
  bigint：8字节
  '''
  '''约束 *
  unsigned：无符号
  zerofill：0填充
  '''
  # 不同类型所占字节数不一样, 决定所占空间及存放数据的大小限制
  # eg:
  create table t8(x tinyint);
  insert into t8 values(200);  # 非安全模式存入,值只能到最大值127
  select (x) from t8;
  
  '''宽度
  1.不能决定整型存放数据的宽度, 超过宽度可以存放, 最终由数据类型所占字节决定
  2.如果没有超过宽度,且有zerofill限制, 会用0填充前置位的不足位
  3.没有必要规定整型的宽度, 默认设置的宽度就为该整型能存放数据的最大宽度 *
  '''
  # eg:1
  create table t9(x int(5));
  insert into t9 values(123456); 
  select (x) from t9; # 结果: 123456
  insert into t9 values(2147483648); 
  select (x) from t9; # 结果: 2147483647
  insert into t9 values(10); 
  select (x) from t9; # 结果: 10
  # eg:2
  create table t10(x int(5) unsigned zerofill); # 区域0~4294967295
  insert into t10 values(10); 
  select x from t10; # 结果: 00010
  insert into t10 values(12345678900); 
  select x from t10; # 结果: 4294967295
  

• 浮点型

  '''类型
  float：4字节，3.4E–38～3.4E+38 *		（3.4*2.7**-38 ~ 3.4*2.7**38）
  double：8字节，1.7E–308～1.7E+308
  decimal：M，D大值基础上+2
  '''
  '''宽度：
  限制存储宽度
  (M, D) => M为位数，D为小数位
  float(255, 30)：精度最低，最常用
  double(255, 30)：精度高，占位多
  decimal(65, 30)：字符串存，全精度
  '''
  # eg:1
  create table t11 (age float(256, 30)); # Display width out of range for column 'age' (max = 255)
  create table t11 (age float(255, 31)); # Too big scale 31 specified for column 'age'. Maximum is 30.
  # eg:2
  create table t12 (x float(255, 30));
  create table t13 (x double(255, 30));
  create table t14 (x decimal(65, 30));
  
  insert into t12 values(1.11111111111111111111);
  insert into t13 values(1.11111111111111111111);
  insert into t14 values(1.11111111111111111111);
  
  select * from t12; # 1.111111164093017600000000000000 => 小数据,精度要求不高, 均采用float来存储 *
  select * from t13; # 1.111111111111111200000000000000
  select * from t14; # 1.111111111111111111110000000000
  alter table t14 modify x decimal(10, 5); # 1.11111 => 限制了数据的存储宽度
  

• 字符型

  '''类型
  char：定长
  varchar：不定长
  '''
  '''宽度
  限制存储宽度
  char(4)：以4个字符存储定长存储数据
  varchar(4)：数据长度决定字符长度，为可变长度存储数据，最大长度为4
  '''
  # eg:
  create table t15 (x char(4), y varchar(4));
  insert into t15 values("zero", 'owen'); # '' | "" 均可以表示字符
  select x,y from t15; # 正常
  insert into t15 values("yanghuhu", 'lxxVSegon'); # 非安全模式数据丢失,可以存放, 安全模式报错
  select x,y from t15; # 可以正常显示丢失后(不完整)的数据
  
  '''重点: 存储数据的方式 **  => 数据库优化
  char:    一定按规定的宽度存放数据, 以规定宽度读取数据, 通常更占空间
  varchar: 首先根据数据长度计算所需宽度, 并在数据开始以数据头方式将宽度信息保存起来, 是一个计算耗时过程, 取先读取宽度信息,以宽度信息为依准读取数据, 通常节省空间
  '''
  总结: 数据长度相近的数据提倡用char来存放数据, 数据需要高速存取,以空间换时间, 采用char
  

• 时间类型

  '''类型
  year：yyyy(1901/2155)
  date：yyyy-MM-dd(1000-01-01/9999-12-31)
  time：HH:mm:ss
  datetime：yyyy-MM-dd HH:mm:ss(1000-01-01 00:00:00/9999-12-31 23:59:59)
  timestamp：yyyy-MM-dd HH:mm:ss(1970-01-01 00:00:00/2038-01-19 ??)
  '''
  # eg: 1
  create table t16(my_year year, my_date date, my_time time);
  insert into t16 values(); # 三个时间类型的默认值均是null
  insert into t16 values(2156, null, null); # 在时间范围外,不允许插入该数据
  insert into t16 values(1, '2000-01-01 12:00:00', null); # 2001 2000-01-01 null
  insert into t16 values(2019, '2019-01-08', "15-19-30"); # time报格式错误 => 按照时间规定格式存放数据
  alter table t16 change my_year myYear year(2); # 时间的宽度修改后还是采用默认宽度 => 不需要关系宽度
  
  # eg:2
  create table t17(my_datetime datetime, my_timestamp timestamp);
  insert into t17 values(null, null); # 可以为空, 不能为null,赋值null采用默认值current_timestamp
  insert into t17 values('4000-01-01 12:00:00', '2000-01-01 12:00:00'); # 在各自范围内可以插入对应格式的时间数据
  
  # datetime VS timestamp
  datetime：时间范围，不依赖当前时区，8字节，可以为null
  timestamp：时间范围，依赖当前时区，4字节，有默认值CURRENT_TIMESTAMP
  

• 枚举与集合

  '''类型
  enum：单选
  set：多选
  '''
  create table t19(
      sex enum('male','female','wasai') not null default 'wasai', # 枚举
      hobbies set('play','read','music') # 集合
  );
  
  insert into t19 values (null, null); # sex不能设置null
  insert into t19 values (); # wasai null
  insert into t19 (hobbies) values ('play,read'), ('music,play'); # sex采用默认值, 对hobbies字段添加两条记录
  insert into t19 (sex,hobbies) values ('male,female', 'play'); # sex字段只能单选
  

• 类型总结

此外保存很大的字符串，使用Text
    保存很大的字节串,使用BLOB
    保存时间日期,DATETIME类型优于TIMESTAMP类型,因为前者能表示的时间日期范围更大

约束条件

"""
primary key：主键，唯一标识，表都会拥有，不设置为默认找第一个 不空、唯一 字段，没有则创建隐藏字段
foreign key：外键
unique key：唯一性数据, 该条字段的值需要保证唯一,不能重复

auto_increment：自增，只能加给key字段辅助修饰

not null：不为空
default：默认值

unsigned：无符号
zerofill：0填充
"""

注：
1.键是用来讲的io提供存取效率
2.联合唯一
create table web (
    ip char(16),
    port int,
    unique(ip,port)
);
3.联合主键
create table web (
    ip char(16),
    port int,
    primary key(ip,port)
);

# eg:1
# 单列唯一
create table t20 (
	id int unique
);
# 联合唯一
create table web (
    ip char(16),
    port int,
    unique(ip,port)
);
# 如果联合两个字段,两个字段全相同才相同,否则为不同
insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);

# 注: 
# 1.表默认都有主键, 且只能拥有一个主键字段(单列主键 | 联合主键)
# 2.没有设置主键的表, 数据库系统会自上而下将第一个规定为unique not null字段自动提升为primary key主键
# 3.如果整个表都没有unique not null字段且没有primary key字段, 系统会默认创建一个隐藏字段作为主键
# 4.通常必须手动指定表的主键, 一般用id字段, 且id字段一般类型为int, 因为int类型可以auto_increment

# eg:2
create table t21(id int auto_increment); # 自增约束必须添加给key的字段
# eg:3
create table t21(id int primary key auto_increment); # 自增要结合key,不赋值插入,数据会自动自增, 且自增的结果一直被记录保留
# eg:4
# 联合主键
create table t22(
	ip char(16),
    port int,
    primary key(ip,port)
);
# 如果联合两个字段,两个字段全相同才相同,否则为不同
insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);

表的修改操作

'''
1.修改表名
alter table 旧表名 rename 新表名;

2.修改表的引擎与字符编码
alter table 表名 engine="引擎名" charset="编码名";

3.复制表 *
# 结构
create table 新表名 like 旧表名;
eg:1
create table nt like tt; # 将tt的表结构复制到新表nt中, 约束条件一并复制
eg:2
create table nt1 select * from tt where 1=2; # 将tt的表结构复制到新表nt1中, 约束条件不会复制

# 结构+数据
create table 新表名 select * from 旧表名;
注: 会复制表结构+数据, 但不会复制约束条件

拷贝表的结构+数据
create table nt like tt；			复制结构
insert into nt select * from tt； 	复制数据

4.清空表
truncate 表名;
注：表被重置，自增字段重置
'''

表中字段的修改操作

create table t2(
	id int primary key auto_increment,
    x int,
    y int
);
insert into t2(x, y) values(10, 20), (100, 200), (1000, 2000);

'''
1.修改字段信息
alter table 表名 modify 字段名 类型[(宽度) 约束];
alter table t2 modify x bigint default 0;  # 模式不同, 涉及精度问题

2.修改字段名及信息
alter table 表名 change 旧字段名 新字段名 类型[(宽度) 约束];
alter table t2 change y c char(10) not null; # 模式不同, 涉及类型转换问题

3.添加字段名
# 末尾添加
alter table 表名 add 字段名 类型[(宽度) 约束], ..., add 字段名 类型[(宽度) 约束];
alter table t2 add age int, add gender enum("male", "female", "wasai") default "wasai";

# 首尾添加
alter table 表名 add 字段名 类型[(宽度) 约束] first;
# 指定位添加：指定字段后
alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名;
alter table t2 add y int after x;

# 将字段名移动
alter table 表名 modify 字段名 类型[(宽度) 约束] unsigned auto_increment first; 移到第一位
alter table 表名 modify 字段名 类型[(宽度) 约束] after 字段名; 移到某个字段之后
4.删除字段名
alter table 表名 drop 字段名;
alter table t2 drop y;
'''

特殊表 (mysql.user) => 用户管理

'''
# 操作前提：登录root用户

1.重要字段
Host | User | Password

2.新建用户
create user 用户名@主机名 identified by '密码'; # 正确
create user zero@localhost identified by 'zero';

注：insert into mysql.user(Host,User,Password) values("主机名","用户名",password("密码")); # 错误

3.设置用户权限
grant 权限们 on 数据库名.表名 to 用户名@主机名 [with grant option];
grant create on db1.* to zero@localhost with grant option;
注：权限有select,delete,update,insert,drop..., all代表所有权限
注：数据库名，表名可以用*替换，代表所有
注：设置权限时如果没有当前用户，会自动创建用户，提倡使用
重点: grant all on db1.* to owen@localhost identified by 'owen'; # (创建用户)设置权限

4.撤销权限
revoke 权限名 on 数据库名.表名 from 用户名@主机名;
revoke delete on db1.* from owen@localhost;

5.修改密码
set password for 用户名@主机名 = password('新密码');
set password for owen@localhost = password('123');

6.删除用户
drop user 用户名@主机名;
'''

表关系

• 多对一

  '''
  案例：员工employees表 | 部门department表
  建表规则：
  先建立主表，再建立从表，在从表中设置主表的唯一字段(通常为主键)作为外键
  建表语法：
  create table 主表(
  	id int primary key auto_increment,
  	...
  );
  create table dep(
  	id int primary key auto_increment,
  	name varchar(16),
  	work varchar(16)
  );
  create table 从表(
  	id int primary key auto_increment,
  	...
  	主表_id int, # 只是在从表中起了一个名字, 该名字和主表主键对应,所有起了个见名知义的名字
  	foreign key(主表_id) references 主表(唯一字段名id)
  	on update cascade
      on delete cascade
  );
  create table emp(
  	id int primary key auto_increment,
  	name varchar(16),
  	salary float,
  	dep_id int,
  	foreign key(dep_id) references dep(id)
  	on update cascade # 设置级联
  	on delete cascade
  );
  
  插入记录：
  先插入主表数据，再插入从表数据
  insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课');
  insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1);
  更新删除数据：
  两表间相互影响，先从依赖数据入手，再进行更新删除操作
  eg:1
  删除主表dep中一个部门
  delete from dep where id=1; => 从表emp中属于该部门的员工都被删除了
  更新从表emp中一个员工的部门
  update emp set dep_id=3 where name='egon'; <= 部门必须存在
  insert into dep values(3, '管理部', '吃饭睡觉打豆豆, 明确团队方针');
  '''
  

• 多对多

  '''
  案例：作者author表 | 书book表
  
  建表规则：
  新建第三张表，通过两个外键形成多对多关系
  
  建表语法：
  create table 表1(
      id int primary key auto_increment,
      ...
  );
  create table book(
  	id int primary key auto_increment,
  	name varchar(16),
  	price int
  );
  create table 表2(
      id int primary key auto_increment,
      ...
  );
  create table author(
  	id int primary key auto_increment,
  	name varchar(16)
  );
  create table 关系表(
      id int primary key auto_increment,
      表1_id int,
      表2_id int,
      foreign key(表1_id) references 表1(id)
      on update cascade
      on delete cascade,
      foreign key(表2_id) references 表2(id)
      on update cascade
      on delete cascade
  );
  create table book_author(
      id int primary key auto_increment,
      book_id int,
      author_id int,
      foreign key(book_id) references book(id)
      on update cascade
      on delete cascade,
      foreign key(author_id) references author(id)
      on update cascade
      on delete cascade
  );
  '''
  

• 一对一

  '''
  案例：丈夫husband表 | 妻子wife表
  
  建表规则：
  未存放外键的表被依赖，称之为左表；存放外键的表示依赖表，称之为右表；先操作左边再操作右表
  
  建表语法：
  create table 左表(
      id int primary key auto_increment,
      ...
  );
  create table husband(
  	id int primary key auto_increment,
  	name varchar(16)
  );
  create table 右表(
      id int primary key auto_increment,
      ...
      左表_id int unique, # 一对一的外键需要唯一性
      foreign key(左表_id) references 左表(id)
      on update cascade
      on delete cascade
  );
  create table wife(
  	id int primary key auto_increment,
  	name varchar(16),
  	husband_id int unique, # 一对一的外键需要唯一性
  	foreign key(husband_id) references husband(id)
      on update cascade
      on delete cascade
  );
  '''
  

y auto_increment,
…
);
create table husband(
id int primary key auto_increment,
name varchar(16)
);
create table 右表(
id int primary key auto_increment,
…
左表_id int unique, # 一对一的外键需要唯一性
foreign key(左表_id) references 左表(id)
on update cascade
on delete cascade
);
create table wife(
id int primary key auto_increment,
name varchar(16),
husband_id int unique, # 一对一的外键需要唯一性
foreign key(husband_id) references husband(id)
on update cascade
on delete cascade
);
‘’’