MySQL 表的约束、数据库设计及多表查询

DQL 查询语句

– 查询所有数据,使用年龄降序排序 select * from student order by age desc;

– 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;

五个聚合函数

– 查询年龄大于 20 的总数
select count(*) from student where age>20;
– 查询数学成绩总分
select sum(math) 总分 from student;
– 查询数学成绩平均分
select avg(math) 平均分 from student;
– 查询数学成绩最高分
select max(math) 最高分 from student;
– 查询数学成绩最低分
select min(math) 最低分 from student;

– 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;


– 查询学生表中数据，从第 3 条开始显示，显示 6 条。
select * from student3 limit 2,6;

数据库表的约束

– 创建部门表(id,dep_name,dep_location)
– 一方，主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
– 创建从表 employee 并添加外键约束 emp_depid_fk
– 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, – 外键对应主表的主键
– 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references
department(id) on update cascade on delete cascade
)
删除外键 ALTER TABLE 从表 drop foreign key 外键名称;

多表操作

#创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
)
insert into dept (name) values (‘开发部’),(‘市场部’),(‘财务部’);
创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), – 性别
salary double, – 工资
join_date date, – 入职日期
dept_id int,
foreign key (dept_id) references dept(id) – 外键，关联部门表(部门表的主键) )

内连接 用左边表的记录去匹配右边表的记录，如果符合条件的则显示。
如：从表.外键=主表.主键
隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定
select * from emp,dept where emp.dept_id = dept.id;
显示内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER
select * from emp e inner join dept d on e.dept_id = d.id;
左外连接：使用 LEFT OUTER JOIN … ON，OUTER 可以省略
可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

右外连接：使用 RIGHT OUTER JOIN … ON，OUTER 可以省略
可以理解为：在内连接的基础上保证右表的数据全部显示

子查询

– 使用子查询 一个查询的结果做为另一个查询的条件
select * from emp where dept_id = (select id from dept where name=‘市场部’);
子查询的结果是一个值的时候
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
select * from emp where salary < (select avg(salary) from emp);
子查询结果是多行单列的时候
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
select name from dept where id in (select dept_id from emp where salary > 5000);