mysql中语句的操作

一 约束条件

　　1.1 default默认值

# 补充知识点  插入数据的时候可以指定字段
create table t1(
    id int,
    name char(16)
);
insert into t1(name,id) values('jason',1);

create table t2(
    id int,
    name char(16),
    gender enum('male','female','others') default 'male'
);
insert into t2(id,name) values(1,'jason');
insert into t2 values(2,'egon','female');

　　1.2 unique唯一

# 单列唯一
create table t3(
    id int unique,
    name char(16)
);
insert into t3 values(1,'jason'),(1,'egon');
insert into t3 values(1,'jason'),(2,'egon');

# 联合唯一
"""
ip和port
单个都可以重复 但是加载一起必须是唯一的
"""
create table t4(
    id int,
    ip char(16),
    port int,
    unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);
insert into t4 values(3,'127.0.0.2',8080);
insert into t4 values(4,'127.0.0.1',8080);  报错

　　1.3 primary key主键

"""
1.单单从约束效果上来看primary key等价于not null + unique
非空且唯一！！！
"""
create table t5(id int primary key);
insert into t5 values(null);  报错
insert into t5 values(1),(1);  报错
insert into t5 values(1),(2); 

"""
2.它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据
"""
# 1 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t6(
    id int,
    name char(16),
    age int not null unique,
    addr char(32) not null unique
);

# 2 如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提示查询速度

# 3 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键
# 单个字段主键
create table t5(
    id int primary key
    name char(16)
);
# 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
create table t7(
    ip char(16),
    port int,
    primary key(ip,port)
);

"""
也意味着 以后我们在创建表的时候id字段一定要加primary key
"""

　　1.4auto_increment自增

# 当编号特别多的时候 人为的去维护太麻烦
create table t8(
    id int primary key auto_increment,
    name char(16)
);
insert into t8(name) values('jason'),('egon'),('kevin');

# 注意auto_increment通常都是加在主键上的 不能给普通字段加
create table t9(
    id int primary key auto_increment,
    name char(16),
    cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

　　补充

delete from t1  删除表中数据后 主键的自增不会停止

truncate t1  清空表数据并且重置主键

二 表与表之间建立关系

定义一张员工表 表中有很多字段
id name gender dep_name dep_desc
create table workers(id int primary key auto_increment,
　　　　　　　　　　　　 name varchar(16),
　　　　　　　　　　　　 gender enum('male','female','others') default 'male',
　　　　　　　　　　　　 dep_name varchar(10),
　　　　　　　　　　　　 dep_desc varchar(10))

1 该表的组织结构不是很清晰(可忽视)
2 浪费硬盘空间(可忽视)
3 数据的扩展性极差(无法忽视的)

如何优化？
上述问题就类似于你将所有的代码都写在了一个py文件中"""
将员工表拆分  员工表和部门表

　　2.1 foreign key外键

　　　　外键就是用来让我们建立表与表之间关系的

　　2.2 表关系（外键的使用）

　　　　表与表之间最多只有四种关系：

　　　　　　一对多关系，多对多，一对一，没有关系

　　　　一对多关系

判断表与表之间关系的时候，前期不熟悉的情况下，一定要按照我给你的建议换位思考，分别站在两张表的角度考虑


　　　　员工表与部门表为例

　　　　先站在员工表：思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)

　　　　不能！！！

　　　　再站在部门表：思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)

　　　　能！！！

　　　　得出结论：员工表与部门表示单向的一对多，所以表关系就是一对多

　　　　foreign key

　　　　1 一对多表关系 外键字段建在多的一方

　　　　2 在创建表的时候 一定要先建被关联表 

　　　　3 在录入数据的时候 也必须先录入被关联表

create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);

　　　　多对多

"""
按照上述的方式创建 一个都别想成功！！！
其实我们只是想记录书籍和作者的关系
针对多对多字段表关系 不能在两张原有的表中创建外键
需要你单独再开设一张 专门用来存储两张表数据之间的关系
"""
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,
    foreign key(author_id) references author(id) 
    foreign key(book_id) references book(id) 
);

　　　　一对一

"""
id name age addr phone hobby email........
如果一个表的字段特别多 每次查询又不是所有的字段都能用得到
将表一分为二  
    用户表
        用户表
            id name age
        用户详情表
            id addr phone hobby email........
    
    站在用户表
        一个用户能否对应多个用户详情   不能！！！
    站在详情表
        一个详情能否属于多个用户      不能！！！
    结论:单向的一对多都不成立 那么这个时候两者之间的表关系
        就是一对一
        或者没有关系(好判断)

客户表和学生表
    在你们报名之前你们是客户端
    报名之后是学生(期间有一些客户不会报名)
一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
"""

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(32),
    age int,
    authordetail_id int unique,
    foreign key(authordetail_id) references authordetail(id) 
)

　　　　总结

表关系的建立需要用到foreign key
    一对多
        外键字段建在多的一方
    多对多
        自己开设第三张存储
    一对一
        建在任意一方都可以 但是推荐你建在查询频率较高的表中

判断表之间关系的方式
    换位思考！！！
        员工与部门
    
        图书与作者
    
        作者与作者详情

　　2.3 级联更新和级联删除

　　　　在设置了外键的表上进行删除数据是会报错的，只有先在被设外键的表上删除数据才能在设置了外键的表上删除。但是这样做很麻烦，就用到了级联更新和删除

　　　　级联更新>>>同步更新


　　　　级联删除>>>同步删除

#以这张表为例，设置级联更新和删除只要在每个外键的下面加上这两句话即可
create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_desc char(32)
);

create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade  # 同步更新
    on delete cascade  # 同步删除
);

三 了解知识点

　　3.1 修改表

# MySQL对大小写是不敏感的
"""
1 修改表名
    alter table 表名 rename 新表名;

2 增加字段
    alter table 表名 add 字段名 字段类型(宽度)  约束条件;
    alter table 表名 add 字段名 字段类型(宽度)  约束条件 first;
    alter table 表名 add 字段名 字段类型(宽度)  约束条件 after 字段名;

3 删除字段
    alter table 表名 drop 字段名;

4 修改字段
    alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
    
    alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
    
"""

　　3.2 复制表

"""
我们sql语句查询的结果其实也是一张虚拟表
"""
create table 表名 select * from 旧表;  不能复制主键 外键 ...

create table new_dep2 select * from dep where id>3;