学习MySQL进阶笔记1

进阶笔记 多表联合查询

#(1) 查询员工号，姓名，工资以及工资提高百分20%后的结果。
select id,name,salary,1.2*salary as newS from employee;
#(2) 查询员工最高工资和最低工资的差距 (提示写运算得一个结果，别名diff)
select max(salary) -min(salary) as diff from employee;
#(3） 选择工资不在5000-10000的员工的姓名和工资， 按工资降序
select name,salary from employee where salary<5000 or salary >10000
order by salary desc;
#(4) 查询员工名中第二个字符为_的员工名
select name from employee where name like '_#_%' escape '#';
#(5)  查询没有奖金的员工名和奖金值。
select name,bonus from employee where bonus=0;
#(6) 查询哪个部门的员工人数大于2
select  department_id,count(*) as c
from employee
group by department_id
having c >2;
#(7）每个工种(有奖金的员工的)(最高工资>5000的)工种编号和最高工资，按最高工资升序
select  job_id,max(salary) as msalary
from employee
where bonus !=0
group by job_id
having msalary >5000
order by msalary asc;

#(8) 多表联合查询
#  a. 查询名字中包含a的员工名和工种名(job)(添加条件)
select e.name,j.jname
from employee as e
inner join job as j on e.job_id = j.id
where e.name like '%a%';
#  b. 查询哪个部门(员工大于2的)部门名(depart)和员工个数
select count(*) as c,d.dname
from employee as e
inner join depart as d on e.department_id = d.id
group by e.department_id
having c >2;
#  C 查询部门名（depart）为(销售，会计的)员工信息(employee )
select e.*,d.dname
from employee as e
inner join depart as d on e.department_id = d.id
where d.dname in ('销售','会计');


# 约束： 按约束字段个数分
#a.列约束:在字段后面实现约束NOT NULL ,DEFAULT必须用列约束实现 
#b.表约束:对两个字段或两个字段以上实现的约束PRIMARY KEY ，UNIQUE KEY，FOREIGN KEY可以实现表约束

# eg1 列约束
CREATE TABLE user(
    id SMALLINT UNSIGNED PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE KEY
);
# eg2 表约束
CREATE TABLE user2(
    id SMALLINT UNSIGNED,
    name VARCHAR(30) NOT NULL,
	primary key(id),
	unique(name)
);

show create table user2;  # 查询创建表命令

# eg3 多个字段同时为主键
create table key1( # 错误的 两个主键了
n1 int primary key,
n2 int primary key
)

create table key1( #只能写表约束,一个表只能有一个主键
n1 int,
n2 int,
primary key(n1,n2)
)
insert into key1(n1,n2) values 
(1,1),
(1,2),
(2,1); # 再写 (1,1) (1,2) 主键重复了

/* 
(4)FOREIGN KEY (外键) 

 1.实现对两个表的字段完整性和一致性约束
 2.
     FOREIGN KEY(外键列名称)
     REFERENCES 参考表名称(字段名称)
    说明： 5.7以前 MyISAM 不支持 外键，事务，锁
       a.MYSQL引擎为 InnoBD alter table employee engine=InnoDB;
       b.数据类型必须一致，如果是整型时大小,UNSIGNED 必须一致，如果字符型长度可以不同，但编码必须一致
       c.外键列如果不是索引，MYSQL引擎会自动将外键列定义为索引
       d.外键列(子表)添加信息，参考表必须有相应信息，参考表不能删除/更新在子表中已经使用的外键值。
 
 */
# a. 先创建父表（参考表）
# job 表
create table job(
id int unsigned primary key auto_increment comment '编号',
jname varchar(30) not null comment '工种名称'
);
insert into job(jname) values 
('电工'),
('汽修'),
('瓦工'),
('水泥工');
# depart 表
create table depart(
id int(3) zerofill primary key auto_increment comment '编号',
dname varchar(30) not null comment '部门名称'
);
insert into depart(dname) values 
('装修'),
('销售'),
('会计');

# b.子表(外键表)
create table employee(
id int unsigned primary key auto_increment comment '编号',
job_id int unsigned not null comment '工种编号',
name varchar(30) not null comment '名称',
department_id int(3) zerofill not null comment '部门编号',
salary decimal(10,2) not null default 0 comment '薪水',
bonus decimal(10,2) not null default 0 comment '奖金',
foreign key(job_id) references job(id),
foreign key(department_id) references depart(id)
);
insert into employee(job_id,name,department_id,salary,bonus) values 
(1,'tom',1,6000,1000)
(1,'jerry',1,7000,1000),
(2,'alice',1,6700,1000),
(3,'tina',1,8000,1000),
(1,'zhangs',2,9000,1000),
(2,'lisi',2,9000,0),
(3,'zhaowu',2,9000,0);

# eg1 外键列(子表)添加信息，参考表必须有相应信息，
insert into employee(job_id,name,department_id,salary,bonus) values
(5,'zhaowu5',2,9000,0); #不能添加
# eg2参考表不能删除/更新在子表中已经使用的外键值。

delete from job where id = 1; #不能删除
update job set id = 8 where id = 2; #不能更新

# eg3 不能先删除参考表（父表），在上外键列表（子表）

/* 
  3  完整格式：
     FOREIGN KEY (外键列名称)
     REFERENCES 参考表名称(字段名称)
     [ON UPDATE RESTRICT|CASCADE|SET NULL|NO ACTION
     ON DELTED RESTRICT|CASCADE|SET NULL|NO ACTION]
         说明：
       a.RESTRICT:默认值 ,等同NO ACTION
                 参考表(父表)不能更新/删除外键表(子表)中被应用的字段值
       b.CASCADE(级联)：参考表(父表)
                 更新/删除外键表(子表)也更新或删除相应的信息
       c.SET NULL:参考表(父表)
                 更新/删除将外键表(子表)中更新或删除相应信息设置为NULL
                 外键表(子表)的字段必须允许为NULL即不能为NOT NULL
 
 */

# 数据库表三范式
/* 
 #1.第一范式：要求有主键，并且要求每一个字段原子性不可再分 (员工编号id,员工名称name,员工情况X)
 
 #2.第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
 #3.第三范式：所有非主键字段和主键字段之间不能产生传递依赖
 (employee  id  job_id工种编号  job表 jname工种名称X)
 */
# 表设计种类
# 一对一: （article表  id title author content(内容特别大),created）拆成一对一的两个表
   #a 两个表共享一个主键 article1(id titel author created) article2(id content) 提高查询效率
   #b 两个表做外键
# 一对多: 给多的一个添加外键，这个外键字段引入参考表中的主键字段
 #  employee(多) job(一) :一个工种编号对应多个员工，一个员工属于一个工种
 #  employee (多)depart(一) :一个部门对于多个员工，一个员工属于一个部门
 #  商品表goods(多)和分类表cates（一）:一个分类有多个商品，一个商品属于一个分类
# 多对多：一般3个表
   收藏 :goods user :一个商品可以被多个用户收藏，一个用户可以收藏多个商品
   goods (id,title,price)
   user(id,name,sex)
   favi (id,goods_id,user_id)
          1     1       1
		  2     1       2
		  3     1       3
		  4     2       1
		  5     2       2
		  
	成绩表 score,学生表 student ,课程表 course:一门课可以被多个学生学习,一个学生可以学多门课
	student(sid,sname,sage,ssex) 
	course(cid,cname,tid) 课程表
	sC(id,sid,cid,score) 成绩表
	    1  1  1     90
		2  1  2     89
		3  1  3     86
		4  2  1     68
		5  2  2     88
		6  2  3     79
		
# 一对一
create table article1(
id int unsigned primary key auto_increment comment '编号',
title varchar(30) not null comment '文章标题',
author varchar(30) not null default '' comment '文章标题',
created datetime not null
);
insert article1(id,title,created) values
(1,'一只蝴蝶',now()),
(2,'一只小鸟',now()),
(3,'一只老虎',now());
create table article2(
id int unsigned primary key auto_increment comment '编号',
content text not null comment '内容'
);
insert article2(id,content) values
(1,'一只蝴蝶内容'),
(2,'一只小鸟内容'),
(3,'一只老虎内容');
	
# 一对多
create table cates(
id int unsigned primary key auto_increment comment '编号',
name varchar(30) not null comment '分类名'
);
insert cates(name) values
('女装'),
('男装'),
('童装');
create table goods(
id int unsigned primary key auto_increment comment '编号',
title varchar(30) not null comment '商品名',
price decimal(10,2) not null default 0 comment '价格',
cate_id int unsigned not null comment '分类编号',
foreign key(cate_id) references cates(id)
);
insert goods(title,price,cate_id) values
('裙子',300,1),
('大衣',190,1),
('童装衬衫',166,3);

# 多对多
/* student(sid,sname,sage,ssex) 
course(cid,cname,tid) 课程表
teacher(tid,tname) 教师表
score(id,sid,cid,score) 成绩表 */
create table student(
sid int unsigned primary key auto_increment comment '编号',
sname varchar(30) not null comment '学生名',
sage  tinyint unsigned not null default 18 comment '年龄',
ssex tinyint unsigned not null default 0 comment '性别'
);
insert student(sname) values
('tom'),
('alice'),
('tina');

create table course(
cid int unsigned primary key auto_increment comment '编号',
cname varchar(30) not null comment '课程名',
tid int unsigned  not null comment '教师编号'
);
insert course(cname,tid) values
('数学',1),
('语文',2),
('英语',3);

create table sorce(
id int unsigned primary key auto_increment comment '编号',
sid int unsigned  not null comment '学员编号',
cid int unsigned  not null comment '课程编号',
score int unsigned not null default 0 comment '成绩',
foreign key(sid) references student(sid),
foreign key(cid) references course(cid)
);
insert sorce(sid,cid,score) values
(1,1,90),
(1,2,88),
(1,3,90),
(2,1,95),
(2,2,91),
(2,3,98),
(3,1,75),
(3,2,81),
(3,3,78);

create table teacher(
tid int unsigned primary key auto_increment comment '编号',
tname varchar(30) not null comment '课程名'
);
insert teacher(tname) values
('张红'),
('刘丽'),
('陈浩');