第十九周-day82-数据库day04

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 offet 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;