DQL基础应用
1. select 语句应用
1.1 select(单表)的执行逻辑
select 列1，列2
from 表
where 条件
group by 条件
having 条件
order by 条件
limit 条件
1.2 select单独使用的情况(MySQL独家)
(1) select @@参数名；
SELECT @@datadir;
SELECT @@port;
SELECT @@socket;
SELECT @@innodb_flush_log_at_trx_commit; #必会参数
SHOW VARIABLES LIKE '%trx%';
(2) select 函数();
SELECT NOW();
USE mysql;
SELECT DATABASE();
SELECT USER();
SELECT 16*16;
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
2. from 子句语句
USE world; #练习前进入world库
SHOW TABLES;
city 城市
country 国家
countrylanguage 国家使用的语言
=============================
DESC city;
id 主键
NAME 城市名
countrycode 国家代码(USA,CHN,JPN)
district 省,州
population 城市人口数
=============================
SELECT * FROM city; #相当于cat /etc/passwd
SELECT NAME,countrycode FROM city; #相当于awk $2 $3
3. where子句应用
相当于grep命令，过滤
3.1 等值查询 grep "root"
-- 查询中国城市的信息
SELECT * FROM city WHERE countrycode='CHN';
3.2 不等值查询
-- 查询人口数量少于100人的
SELECT * FROM city WHERE population<100;
-- 查询ID小于10的城市信息
SELECT * FROM city WHERE id<10;
-- 查询不是中国的城市信息(尽量不使用不等于,可能不走索引)
SELECT * FROM city WHERE countrycode!='CHN';
SELECT * FROM city WHERE countrycode<>'CHN';
3.3 模糊查询
-- 查询国家代号为CH开头的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';
-- 查询国家代号包含CH的城市信息(避免使用)
SELECT * FROM city WHERE countrycode LIKE '%CH%';
-- 注意：避免使用 like 中 前面带%的模糊查询
3.4 逻辑连接符(and,or)
-- 查询中国城市人口超过500万的城市信息
SELECT *
FROM city
WHERE countrycode='CHN' AND population>5000000
-- 将peking修改为beijing
UPDATE city SET NAME='beijing' WHERE id='1891';
-- 查看山东省或河北省的城市信息
SELECT *
FROM city
WHERE district='shangdong' OR district='hebei';
-- 查询人口数量在500w-600w的城市
SELECT *
FROM city
WHERE population>5000000 AND population<6000000;
3.5 where 配合 between and 的使用
-- 查询人口数在100w-200w区间的城市信息(包含头尾)
SELECT * FROM city
WHERE population BETWEEN 1000000 AND 2000000;
3.6 where 配置 in 使用
-- 查看山东省或河北省的城市信息
SELECT * FROM city
WHERE district IN('shandong','hebei');
4. group by 子句+聚合函数应用
4.1什么是分组
按照某个列进行分组
4.2常用的聚合函数
COUNT() 计数
MAX() 最大值
MIN() 最小值
AVG() 平均值
SUM() 求和
GROUP_CONCAT() 列转行
4.3 例子
-- 统计每个国家的城市个数
SELECT countrycode,COUNT(id) FROM city
GROUP BY countrycode
-- 统计每个国家的总人口数量
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
-- 统计中国每个省的城市个数及省总人口数
SELECT district,COUNT(id),SUM(population) FROM city
WHERE countrycode IN('CHN')
GROUP BY district;
-- 统计各个国家的城市名列表(列转行)
SELECT countrycode,GROUP_CONCAT(NAME) FROM city
GROUP BY countrycode;
5. having语句 后过滤
-- 统计中国，每个省的，城市个数，省总人口数
-- 只显示人口总数大于800w的省
SELECT district,COUNT(id),SUM(population)
FROM city
WHERE countrycode IN('CHN')
GROUP BY district
HAVING SUM(Population)>8000000;
6. order by 子句
实现先排序，by后添加条件列
-- 以上例子，将人口数进行排序输出
SELECT district,COUNT(id),SUM(population)
FROM city
WHERE countrycode IN('CHN')
GROUP BY district
HAVING SUM(Population)>8000000
ORDER BY SUM(Population) DESC;
-- 查询中国所有城市信息，并以人口数降序输出
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY Population DESC;
7. limit 应用
-- 查询中国所有城市信息，并以人口数降序输出，只显示前五名
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY Population DESC
LIMIT 5;
-- 跳过前N行,显示M行(N和M代表的是数字)
LIMIT M offset N
LIMIT N M
8. distinct 应用
-- 查询所有的国家代号信息,重复的只显示一遍
SELECT DISTINCT countrycode FROM city;
9.union 与 union all
-- 查看山东省或河北省的城市信息
SELECT *
FROM city
WHERE District='shandong' OR district='hebei';
-- 改写：性能更好一些，索引级别更高
SELECT *
FROM city
WHERE district='shandong'
UNION ALL
SELECT *
FROM city
WHERE district='hebei';
面试题：union和 union all的区别？
union 带有去重复功能
union all 没有去重复 √
二、多表查询
2.1 准备环境
多表连接查询的环境准备:
use school
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
teacher ：教师表
tno： 教师编号
tname： 教师名字
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
score ：成绩表
sno： 学号
cno： 课程编号
score： 成绩
-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school
CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;
CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;
INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');
INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');
INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');
INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');
DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);
DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
