mysql表间的关系和查询
mysql间的关系及查询
多对一
# 先创建被关联的表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)
);
多对多关系
表一：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);
修改表和复制表
# 修改表名
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 = '技术');
