Mysql 查询语句

DQL:查询语句

准备sql

CREATE TABLE student3 (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math int, -- 数学
 english int -- 英语
);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

1 排序查询

*语法 * order by 子句
* order by 排序字段1 排序方式1 , 排序字段2 ，排序方式2

-- 查询数学成绩升序排序(默认查询顺序就是ASC这里可以不写)
SELECT * FROM student3 ORDER BY math ASC;

-- 查询数学成绩降序排序
SELECT * FROM student3 ORDER BY math DESC;

-- 查询数学成绩按照升序排序,如果数学成绩一样就按英语成绩排序
SELECT * FROM student3 ORDER BY math ASC , english ASC ;

2.聚合函数

• 将一列数据作为一个整体，进行纵向的计算
• 1.count: 计算个数
• 2.max：计算最大值
• 3.min：计算最小值
• 4.sum：计算和
• 5.avg： 计算平均
  注意：所有的聚合函数都是排除的非空的字段

-- 计算总人数
SELECT COUNT(NAME)  FROM student3;

-- 计算数学的平均值
select avg(math) from student3;

-- 计算数学的最大值
SELECT max(math) FROM student3;

-- 计算数学的最小值
SELECT MIN(math) FROM student3;

-- 计算数学的总成绩
SELECT SUM(math) FROM student3;

-- 如果以英语成绩查询总的人数但是英语成绩有一列是空的所以需要判断
SELECT COUNT(IFNULL(english,0)) FROM student3;

分组查询

-- 按照性别分组,分别查询男女的数学平均分
SELECT sex, AVG(math) FROM student3 GROUP BY sex ;

-- 按照性别分组,分别查询男女的数学平均分,以及男女的人数
SELECT sex, AVG(math) ,count(id)  FROM student3 GROUP BY sex ;

-- 按照性别分组,分别查询男女的数学平均分,以及男女的人数,如果数学成绩大于70才参数分组
SELECT sex, AVG(math) ,count(id)  FROM student3 WHERE math>70 GROUP BY sex ;

-- 按照性别分组,分别查询男女的数学平均分,以及男女的人数,如果数学成绩大于70才参数分组,分组之后组员小于2个不参与分组
SELECT sex, AVG(math) ,count(id) as c FROM student3 WHERE math>70 GROUP BY sex HAVING c>2;

注意：分组之后，在select后 要么加分组的字段，要么加聚合函数

经典面试题
where 和having 的区别？

• where在分组之前进行判定,如果不满足条件，就不参与分组
• having在分组之后进行判定，如果不满足条件，就不会被查询出来
  注意：where之后不能跟聚合函数的判定,having之后可以跟聚合函数的判定

外键约束

准备sql

CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

数据有冗余，需要建立外键关联
决解方案，建立多张表与主表关联

-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- 外键对应主表的主键
)
-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;