day44

（1）约束条件
（2）表三种关系（多对一，多对多，一对一）
（3）记录相关操作
（4）单表查询
（5）where语句
模糊查询
正则方法
（6）group by
（7）having
（8）order by
（9）limit
# 约束条件
# not null      不能为空
create table t1(x int not null);
# default       默认值
create table t2(x int not null default 111);
# insert t1 values ();        # 不能传空，会报错
insert t2 values ();        # 传空则为默认值

#unique     单列唯一
create table t3(name varchar(10) unique);
insert t3 values('aaa');
select * from t3;
# insert t3 values('aaa');        # 重复传值报错

# 联合唯一
create table server(
    id int,
    name varchar(10),
    ip varchar(15),
    port int,
    unique(ip,port),
    unique(name)
);
# ip和port 整体唯一
insert into server values (1,"web1","10.10.0.11",8080);
insert into server values (2,"web2","10.10.0.11",8081);
# insert into server values (4,"web4","10.10.0.11",8081);     # 报错

# not null 和 unique 一起使用会被识别为表的主键
create table t4(id int,name varchar(10) not null unique);

# 主键primary key
# 特点：
#       主键的约束效果是not null unique
#       innodb 表有且只有一个主键，该主键可以是联合主键
create table t6(
    id int primary key auto_increment,
    name varchar(5)
);
insert t6(name) values('aa'),('bb'),('cc');
select * from t6;

# 联合主键
create table t7(
    id int,
    name varchar(5),
    primary key(id,name)
);



# 表三种关系（多对一，多对多，一对一）
# 多对一
# 先创建被关联表
create table dep(
    id int primary key auto_increment,
    name varchar(6)
);
# 再创建关联表
create table emp(
    id int primary key auto_increment,
    name varchar(10),
    gender varchar(6),
    dep_id int,
    foreign key(dep_id) references dep(id) on delete cascade on update cascade
);
# 先向被关联表插入数据
insert dep(name) values('技术部'),('人力资源部'),('销售部');
# 再向关联表中插入数据
insert emp(name, gender, dep_id) VALUES
('aa','male',1),
('bb','male',2),
('cc','female',2),
('dd','male',3),
('ee','female',3);
select * from emp;
select * from dep;
# 级联更新验证
update dep set id=33 where id=3;
# delete from dep where id=2;
select * from dep;
select * from emp;

# 多对多
# （一位作者可写多本书，一本书可被多位作者完成）
# table author 和 table book 被 table author2book 关联
create table author(id int primary key auto_increment,name varchar(10));
create table book(id int primary key auto_increment,name varchar(8));
create table author2book(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key (author_id) references author(id) on delete cascade on update cascade,
    foreign key (book_id) references book(id) on delete cascade on update cascade
);
insert author(name) values('柯南道尔'),('刘慈欣'),('三叔'),('aaa');
insert book(name) values('福尔摩斯'),('三体'),('吞噬地球'),('藏海花'),('沙海'),('bbb');
insert author2book(author_id, book_id) VALUES (1,1),(2,2),(2,3),(3,4),(3,5),(4,1),(3,6);
select * from author;
select * from book;
select * from author2book;

# 一对一
create table customer(
    id int primary key auto_increment,
    name varchar(10),
    phone_number varchar(11)
);
create table student(
    id int primary key auto_increment,
    class varchar(10),
    course varchar(10),
    customer_id int unique,             # 被关联对象加上约束条件(唯一)实现一对一
    foreign key (customer_id) references customer(id) on delete cascade on update cascade
);
insert customer(name, phone_number) VALUES ('移不动',68001),('信不过',00001),('联不通',01001);
insert student(class, course, customer_id) VALUES (1,'python',1),(1,'python',2),(2,'linux',3);
select * from customer;
select * from student;



# 记录相关操作
# 插入
# insert into t4 select user,host from mysql.user;
# 删除
# delete from 表名 where 条件;
# 更新
# update 表名 set 字段=值 where 条件;


单表查询：
https://www.cnblogs.com/linhaifeng/articles/7267592.html
# 单表查询语法
# select (distinct 去重) 字段1（可以加上 as+自定义字段名称）,字段2... from 表名
#                                                              where 过滤条件
#                                                              group by 分组条件
#                                                              having 分组之后过滤条件
#                                                              order by 排序字段
#                                                              limit n 限制显示几条
# （优先级：from > where > group by > having > distinct > order by > limit）
# distinct 避免记录重复

# 可进行四则运算
select id*12 from author;

# concat() 拼接记录的内容
select concat('编号',id) as 作者编号,name as 名字 from author;
select concat('编号',':',id) as 作者编号,name as 名字 from author;
select concat_ws(':','编号',id,'号') as 作者编号,name as 名字 from author;       # concat_ws() 以第一个字符为拼接符

# 补充
select (
    case
    when name='柯南道尔' then
        concat('柯南','·','道尔')
    when name='三叔' then
        concat('南派',name)
    else
        name
    end
           ) as 名字
from author;



# where 语句
select * from book where id >= 2 and id <= 5;
select * from book where id between 2 and 5;
select * from book where id not between 2 and 5;

select * from book where id=2 or id=5 or id=6;
select * from book where id in (2,5,6);

# 模糊查询  （ %可接收任意个数的字符，_只能接收一个字符 ）
select * from customer where phone_number like '10%';
select * from customer where phone_number like '1000_';
select * from customer where phone_number like '100__';
# 正则方法
select * from customer where phone_number regexp '0$';
select * from customer where phone_number regexp '^100';

select * from emp where dep_id is not null;
select hex(name) from author where id=2;       # hex() 转换为16进制



# group by 分组
set global sql_mode='ONLY_FULL_GROUP_BY';       # 修改sql模式， 之后只能看到分组的字段，以及聚合的结果
select * from emp group by dep_id;          # 报错
select dep_id from emp group by dep_id;

# max(),min(),avg(),sum(),count()   最大值，最小值，平均值，总和，计数
select dep_id ,count(dep_id) as 个数 from emp group by dep_id;
select dep_id ,group_concat(name) from emp group by dep_id;       # group_concat()输出聚合后的某一字段的全部内容



# having
# having与where本质区别就是在于having是在分组之后发生过滤，可以使用聚合函数，而where不能
 select max(id) from emp where max(id) > 2;         # 报错  Invalid use of group function（无效使用分组函数）
 select max(id) from emp having max(id) > 2;        # 可以正常运行



 # order by
select * from emp order by id;  # 默认升序排列
select * from emp order by id desc ;  # desc降序排列
select * from emp order by dep_id,id ;  # 先按dep_id排序，dep_id相同再按id排序



# limit
select * from emp limit 0,2;        # limit 起始位置 ，显示条数
select * from emp limit 2,2;
select * from emp limit 4,2;