结合写的查询文件复习E:\Navicat\MySQL\Servers\ZH\yuanjiuyuanexicse\数据库操作.sql
数据库操作分类✨✨✨✨✨
DDL 数据定义语言 定义和管理数据对象, 如数据库,数据表等 CREATE、DROP、ALTER
DML 数据操作语言 用于操作数据库对象中所包含的数据 INSERT、UPDATE、DELETE
DQL 数据查询语言 用于查询数据库数据 SELECT
DCL 数据控制语言 用来管理数据库的语言,包括管理权 限及数据更改 GRANT、COMMIT、ROLLBACK
字段类型✨✨✨✨✨✨✨✨
char(M) 面试问区别
- char[(M)] 固定长字符串,检索快但费空间, 0 <= M <= 255 M字符数量
- 例如 char(5) 'abcde' 可以存 'abcdef' 不可以存 ,当字符不够存abc时 'abc ' 不用计算长度效率高但是会浪费存储空间
varchar(M)
- varchar[(M)] 可变字符串0 <= M <= 65535 变长度(只能变小不能超过最大范围)
- 例如 varchar(5) 'abcde' 可以存 'abcdef' 不可以存,当字符不够存abc时 'abc' 需要计算字符长度 效率低 但是不浪费存储空间
decimal(m,n)
- 字符串形式的浮点数 m是总位数(1~65) n是小数位数(0~30且不能大于m) 例如 decimal(5,2) 就是 123.56
null
- 是一种类型 0或NULL都表示假
存储引擎 ✨✨✨✨✨✨✨✨✨✨✨✨✨
查看存储引擎 show engines
InnoDB 与 MyISAM
int(n)中数值的含义✨✨✨✨✨
n就是告诉mysql 你存的数值的宽度是n 即位数是n 当然如果你不是n位数(只要在该类型的存储范围之内)mysql也能正常存储
我们把这个字段的"属性"修改为UNSIGNED ZEROFILL看一下效果
`uid` int(3) unsigned zerofill NOT NULL
现在我的uid
字段:长度(M)=3, 属性=UNSIGNED ZEROFILL(无符号,用0来填充位数)。
设置这个属性后往表时插入数据,系统会自动把uid字段M不够3位的在左侧用0来填充。
效果如下(目前大多数图形化界面会不显示不足位数补的0):
insert into `test` (`uid`) VALUES(11);
执行结果 就是011 他会在不足位数补0
长度M与你存放的数值型的数的大小无关
表结构修改关键字(alter)✨✨✨✨✨
1. 创建表 create table 表名
create table student
2. 删除表 drop table 表名
drop table student
3. rename as 改名 alter table 表名 rename as 数据;
alter table sutdent rename as xuesheng
4. add 添加字段名 alter table 表名 add 新字段名 类型 属性;
alter table student add stu_qq varchar(20) comment '学生qq号';
5. drop 删除字段名 alter table 表名 drop 字段名;
alter table student drop stu_age;
修改字段名类型以及属性的注意事项
- 覆盖式修改
- 有默认值
- 如果已经存在数据-->数据可以做隐式转换的话就不会报错,转换不了就会报错
6. modify 修改类型 属性 alter table 表名 modify 字段名 新类型 新属性;
alter table sutdent modify stu_qq int comment '学生qq号';
7. change 修改字段名 类型 属性 alter table 表名 change 旧字段名 新字段名 新类型 新属性;
alter table student change stu_qq qq varcahr(20) comment '学生qq号';
三范式✨✨✨✨✨✨
1NF 第一范式(原子性)
确保每列保持原子性(不可再分) 类似于地址这个字段名可以分为省市区县村 这就不符合原子性,但是如果分开,在使用时候就需要拼接,消耗性能,所以并不一定必须要分成省市区县村。
2NF 第二范式(完全依赖主键)
确保每列都与主键完全依赖且不存在部分依赖 (主要是说在联合主键的情况下,非主键列不能只依赖于主键的一部分。或者是一个表中只能存一种数据不能把多种数据存在一个表中)
stu_id | kc_id | score | kc_name |
001 | 1011 | 85 | 高数3-1 |
001 | 1022 | 79 | 计算机组成原理 |
002 | 1011 | 59.9 | 高数3-1 |
这个表中,score完全依赖于联合逐渐(stu_id,kc_id),但是kc_name只依赖于kc_id,存在部分依赖不符合第二范式。修改方法就是把成绩表和课程表分开,修改如下:
成绩表:
stu_id | kc_id | score |
001 | 1011 | 85 |
001 | 1022 | 79 |
002 | 1011 | 59.9 |
课程表:
kc_id | kc_name |
1011 | 高数3-1 |
1022 | 计算机组成原理 |
3NF 第三范式
确保每列对主键直接相依赖,而不是传递依赖
id | name | 所在院校 | 院校地址 |
001 | 张三 | 1 | 北京 |
002 | 李四 | 2 | 西安 |
003 | 王五 | 3 | 上海 |
这个表中所在院校直接依赖于id 但是院校位置依赖于所在院校 再依赖于id这是传递依赖
院校地址--->所在院校 所在院校--->id 院校地址--->id
需要消除传递依赖,修改如下:
学生表:
id | name | 所在院校 |
001 | 张三 | 1 |
002 | 李四 | 2 |
003 | 王五 | 3 |
院校表
院校id | 院校地址 |
1 | 北京 |
2 | 西安 |
3 | 上海 |
总结三范式:1NF:确保字段原子性;2NF:确保非主键与主键完全依赖,消除部分依赖;3NF:确保非主键与主键直接依赖,消除传递依赖。
约束
非空约束 not null
create table tb_user(
user_name varchar(10) not null default '无名', -- 因为是非空约束 所以要有默认值
user_age int
);
-- 在已经创建好的表里添加非空约束
alter table 表名 modify 字段名 char(10) not null;
唯一性约束 unique
-- 行级约束
create table tb_user(
user_name varchar(10) unique, -- 唯一约束 行级约束
user_age int unique -- 每个字段都是各自的唯一约束
);
-- 表级约束
create table tb_user(
user_name varchar(10),
user_age int,
unique(username,userage) -- 表级约束, 联合唯一约束
-- 字段是有关联的,必须每个字段的值都相同时才会触发唯一约束
-- '张三',12 '张三',20 '王五',12 '张三',12 当插入'张三',12 时候就会报错
);
-- 给约束起名字
create table tb_user(
user_name varchar(10),
user_age int,
CONSTRAINT name_age_unique unique(user_name,user_age)
);
-- 在已经创建好的表里添加唯一性约束
alter table 表名 add unique(表字段名,字段,字段,字段);
主键约束 primary key
-- 行级约束
create table tb_user(
user_name varchar(10) primary key auto_increment,
user_age
);
-- 联合主键(表级约束)
create table tb_user(
user_name varchar(10),
user_age int,
primary key(user_name,user_age) -- 联合主键,联合约束
-- 多个字段的值,只有完全相同时,才会触发约束
);
-- 在已创建好的表里添加主键约束
alter table 表名 add primary key(字段名);
外键约束 foreign key
-- 不用太上心,因为上班不会用的,外键不适合分布式高并发等场景
-- 阿里的文档中强制要求保留外键关系,不使用外键约束
-- 不得使用外键与级联,一切外键概念必须在应用层解决。
-- 父表
create table a(
aid int primary key auto_increment,
aname varchar(10)
);
-- 子表
create table b(
bid int primary key auto_increment,
bname varchar(10),
aid int,
FOREIGN key(aid) references a(aid)
);