mysql进阶

mysql表关系

https://www.cnblogs.com/bypp/p/7755307.html

创建Mysql表：User用户表，Order订单表，Orderdetail订单详情，item商品表

一个用户对应多个订单，一个订单只能对应一个用户；一个订单对应多个订单详情，一个订单详情只对应一个订单；一个订单详情只对应一个商品，一个商品可以包括在多个订单详情中；所以，用户和商品之间是多对多关系

CREATE TABLE user (
  id bigint(32) NOT NULL AUTO_INCREMENT COMMENT '客户id(主键)',
  username varchar(32) NOT NULL COMMENT '客户名称',
  birthday date DEFAULT NULL COMMENT '客户生日',
  sex char(1) DEFAULT NULL COMMENT '客户性别',
  address varchar(256) DEFAULT NULL COMMENT '客户地址',
  PRIMARY KEY (id)
) 

CREATE TABLE orders (
  id bigint(32) NOT NULL AUTO_INCREMENT COMMENT '客户id(主键)',
  user_id bigint(32) NOT NULL COMMENT '下单客户id(外键)',
  number varchar(32) NOT NULL COMMENT '订单号',
  createtime datetime NOT NULL COMMENT '创建时间',
  note varchar(32) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (id),
  KEY FK_user (user_id),
  CONSTRAINT FK_user FOREIGN KEY (user_id) REFERENCES user (id)
) 

CREATE TABLE orderdetail (
  id bigint(32) NOT NULL AUTO_INCREMENT COMMENT 'id(主键)',
  order_id bigint(32) NOT NULL COMMENT '订单id',
  item_id bigint(32) NOT NULL COMMENT '商品id',
  item_num bigint(32) DEFAULT NULL COMMENT '商品购买数量',
  PRIMARY KEY (id),
  KEY order_id (order_id),
  KEY orderdetail_ibfk_2_idx (item_id),
  CONSTRAINT orderdetail_ibfk_1 FOREIGN KEY (order_id) REFERENCES orders (id),
  CONSTRAINT orderdetail_ibfk_2 FOREIGN KEY (item_id) REFERENCES item (id)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

CREATE TABLE item (
  id bigint(32) NOT NULL AUTO_INCREMENT COMMENT 'id(主键)',
  name varchar(32) NOT NULL COMMENT '商品名称',
  price float(10,1) NOT NULL COMMENT '商品价格',
  detail text COMMENT '商品描述',
  pic varchar(512) DEFAULT NULL COMMENT '商品图片',
  createtime datetime DEFAULT NULL COMMENT '生产日期',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

一对一

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

比如：用户和订单编号

1)表结构设计(建物理表)

方法1:分开两个独立的表
create table wife(
id int primary key,
name varchar(10),
sex char(1)
);

create table husband(
id int primary key,
name varchar(10),
sex char(1),
wid int unique,
constraint foreign key(wid) references wife(id)
);

注意： 通过constraint约束体现一对一

连接： https://www.cnblogs.com/cangqinglang/p/10036680.html

复习与加深，什么是约束条件：

连接： https://blog.youkuaiyun.com/w_linux/article/details/79655073

方法2:合并在一个独立表
create table person(
id int primary key,
name varchar(10),
sex char(1),
wife int,
husband int
);
insert into person values(1,‘小花’,‘0’, 0,3);
insert into person values(2,‘玉芬’,‘0’, 0,4);
insert into person values(3,‘张三’,‘1’, 1,0);
insert into person values(4,‘李四’,‘1’, 2,0);
insert into person values(5,‘王五’,‘0’, 0,0);
insert into person values(6,‘张五’,‘2’, 0,0);

语法：case when then end

连接： https://www.cnblogs.com/anche/p/9038802.html

select name, (case sex when ‘0’ then ‘女’
when ‘1’ then ‘男’
else ‘未知’
end) as 性别, id from person;

通过分别建立视图来体现一对一的关系
create view women as select * from person where sex=‘0’;
create view men as select * from person where sex=‘1’;

查询夫妻信息
1)旧版本
select wife.name as 妻子, husband.name as 丈夫 from wife,husband
where wife.id= husband.id;
2)新版本(96):关联(效率更高)
select wife.name as 妻子, husband.name as 丈夫 from husband
INNER JOIN wife ON husband.id = wife.id;

一对多

表A中的一条记录可以与另一张表B中的任意数目的记录相关联，而表b中的某条记录最多与表A有一条相关

比如：订单和订单详情 一个订单对应多个订单详情，一个订单详情只对应一个订单；一个订单详情只对应一个商品，一个商品可以包括在多个订单详情中

# 先创建被关联的表dep                
create table dep(id primary key auto_increment,
                 dep_name char(10),
                 dep_comment char(60))
                 
# 后创建关联表emp              
create table emp(id int primary key auto_increment,
                 name char(15),
                 gender enum('male','femael')not null default 'male',
                 de_id int,
                 foreign key(dep_id),references dep(id));
                 
# 先向被关联的表dep中插入值
insert into dep(dep_name,dep_comment) values('教学组','给学生上课',
                                             '招生组','负责招生计划',
                                             '后勤组','维护教学设备'));
                                             
# 再向关联表emp中插入值
insert into emp(name,gender,de_id) values(('aaa','male',1),
                                          ('bbb','male',2),
                                          ('ccc','female',3),
                                          ('ddd','male',2)
                                            );

一对多查询语句：

连接： https://blog.youkuaiyun.com/hxy19971101/article/details/78032204

多对多

表A中的一个实体可以与表B中的任意数目（零个或多个）记录相关联，表B中的一个记录可以与表A中的任意数目（零个或多个）实体相关联

比如：用户和商品 书籍和出版商

表一：book

id	book_name	price
1	九阴真经	100
2	葵花宝典	200
3	吸星大法	300

表二：press

id	press_name	phone
1	人民出版社	123
2	青春文学	234
3	北京邮电出版社	345

一本书可以是多个出版社出版的,所以需要单独建一个关系表出来

id	book_id	p_id
1	1	1
2	1	3
3	2	1

# 创建表1
create table book(id int primary key auto_increment,
                  book_name char(15),
                  price int(5));
# 创建表2
create table press(id int primary key auto_increment,
                   press_name char(20),
                   phone int(11));                 
# 创建关系表
create table b_p(id int primary key auto_increment,
                 book_id int(3),
                 p_id int(3),
                foreign key(book_id) references book(id) on update cascade on delete cascade,
                foreign key(p_id) references press(id) on update cascade on delete cascade);

# 插入值
insert into book values(1,'九阴真经',100),(2,'九阳神功',200),(3,'吸星大法',300);

insert into press(press_name) values('人民出版社'),('青春文学'),('北京邮电出版社');

insert into b_p(book_id,p_id) values(1,1),(1,3),(2,1);

表关系外键约束： https://www.cnblogs.com/yy20141204bb/p/8405000.html

修改表和复制表

# 修改表名
    alter table 表名 rename 新表名;
    
    
# 增加新字段
    alter table 表名 add 字段名 数据类型[约束条件];
    

# 删除字段
    alter table 表名 drop 字段名
    
    
# 修改字段
    alter table 表名 modify 字段名 数据类型[约束条件];
    
    alter table 表名 change 旧字段名 新字段名 旧数据类型[约束条件];
    
    alter table 表名 change 旧字段名 新字段名 新数据类型[约束条件];
    
    
# 复制表
    create table book_copy select *from book;
    

单表查询

select id,name from emp
    where id >1 and name like'%on%'
    group by dep_id
    having 分组后的过滤条件
    order by 排序依据
    limit n;

group by & 聚合函数

# 设置sql_mode 为 only_full_group_by

select 字段1 from 表名 group by 字段1     # 注意前后两个字段名要相同

# 每个部门员工的最高工资
select post,max(salary) from emp group by post;

select post,min(salary) from emp group by post;

select post,avg(salary) from emp group by post;

select post,sum(salary) from emp group by post;

select post,count(gender) from emp group by post;

select post,avg(salary) from emp where age>=30 group by post;

group_concat & 拼接字符串

select post,group_concat(name) from emp group by post;

select post,group_concat(name,'sb')from emp group by post;

select post,group_concat(name,":",salary)from emp group by post;

concat & as

select concate('姓名',name) from emp ;

select name,salary*12 as annual_salary from emp;

select concate('姓名',name) as 姓名  from emp;

having

# 1. having 的语法给是与where一模一样，但是having是在group by之后进行一步过滤
# 2. where不能用聚合函数，但是having可以用聚合函数

# 统计个部门年龄在30岁以上的员工的平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
                        where age>=30
                        group by post
                        having avg(salary)>10000;
            
# 强调： having必须在group by后用

distinct

# 去重，执行顺序在having之后
select distinct post,avg(salary) from emp
        where age>=30
        group by post
        having avg(salary)>10000;

order by

select * from emp order by salary;      # 默认升序（asc)排

select * from emp order by salary desc; # 降序排

select * from emp order by age desc,salary asc; # 多个排序标准，

# 统计个部门年龄大于10岁，平均工资大于1000，且升序排列
select distinct post,avg(salary)from emp where age >10 
            group by post
            having avg(salary) > 1000
            order by post;

limit

# 限制显示条数

select * from emp limit 3;

# 选择工资前三的员工信息
select * from emp order by salary desc limit 3;

# 分页显示
select * from emp limit 5,5;

正则匹配

select * from emp where name regexp '^jin.*(n|g)$';

# $表示结束
# regexp关键字

多表连接查询

建表

create table department(id int,name varchar(20));

create table employee(id int primary key auto_increment,
                      name varchar(20),sex enum('male','female')not null default 'male',age intm dep_id int);

笛卡尔积查询

select * from emp,dep where emp.dep_id = dep.id

内连接

select * from emp inner join dep on emp.dep_id = dep.id;

左连接

select * from emp left join dep on emp.dep_id = dep.id;

右连接

select * from right join dep on emp.dep_id = dep.id;

全连接

select * from emp left join dep on emp.dep_id = dep.id
union
select * from right join dep on emp.dep_id = dep.id;

子查询

select * from emp inner join dep on demp.dep_id = dep.id
select name from emp where dep_id = (select id from dep where name = '技术');