DQL-查询语言的应用
生活随笔
收集整理的這篇文章主要介紹了
DQL-查询语言的应用
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
第1章 DQL-查詢語言的應用
1.1 select單獨使用情況
1.1.1 查看當前所在的庫
select database();
1.1.2 查看當前時間
select now();
1.1.3 查看軟件的安裝目錄
select @@basedir;
1.1.4 查看端口號
select @@port;
1.1.5 查看雙一標準期中的一個
select @@innodb_flush_log_at_trx_commit;
1.1.6 對于這些參數長的值，可以使用模糊查詢
show variables like '%trx%';
第2章 select的應用
1.1 select 語句執(zhí)行順序
4 1
select…………..from………..
2 3
where………….gruop by………….
5
having…………
6
order by………….
7
limit………
1.2 環(huán)境準備
mysql -uroot -p456 <world.sql
1.2 select配合from子句的使用
select 列,列,列 from 表
例子1：查詢表中所有的信息
--實際工作中沒有這種需求(對服務器的性能影響很大)
mysql> use world;
mysql>select * from city;
--生產中不要使用這種語句
1.3 select 配合 where 子句使用
-- select 列,列,列 from 表 where 過濾條件
例子1：查詢中國所有的城市名和人口數
select name,population from world.city where countrycode='CHN';
例子2：世界上小于100人的城市名和人口數
where 配合比較判斷查詢(> < >= <=)
select name,population from world.city where population<100;
1.4 where 配合 邏輯連接符('and' 'or' 'between…..and…...')
例子1：查詢中國人口數量大于800w的城市名和人口
select name,population from world.city where countrycode='CHN' and population>8000000;
例子2：查詢中國或美國的城市名和人口數
select name,population from world.city where countrycode='CHN' or countrycode='USA';
例子3: 查詢人口數量在500w到600w之間的城市名和人口數
第一種方法
select name,population from city where population>=5000000 and population<=6000000;
第二種方法
select name,population from city where population between 5000000 and 6000000;
例子4： 查詢中國和美國的城市信息
select * from world.city where countrycode in ('CHN','USA');
1.5 where 配合 like 子句 模糊查詢
例子1：查詢contrycode中帶有CH開頭,城市信息
select * from city where countrycode like 'CH%';
--注意:
1. 不要出現(xiàn)類似于 %CH%,前后都有百分號的語句,因為不走索引,性能極差
2. 如果業(yè)務中有大量需求,我們用"ES"來替代
第3章 聚合函數
1.1 常用的聚合函數
max() 最大值
min() 最小值
avg() 平均值
sum() 總和
count() 個數
group_concat() 列轉行
1.2 例子
例子1：統(tǒng)計世界上每個國家的總人口數
select countrycode,sum(population) from world.city group by countrycode;
例子2：統(tǒng)計中國各個省的總人口數量
select district,sum(population) from world.city where countrycode='CHN' group by district;
例子3：統(tǒng)計世界上每個國家的城市數量
SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
例子4：統(tǒng)計中國，每個省總人口，城市個數，城市名稱列表
select district,sum(population),count(id),name
from world.city
where countrycode='CHN'
group by district;
--ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
-- 報錯原因
select 后面的列要么是group by的列，要么是包裹在聚合函數中
--原理
MySQL不支持結果是1行對多行的顯示方式
正確的sql語句如下
select district,sum(population),count(id),group_concat(name)
from world.city
where countrycode='CHN'
group by district;
1.3 having子句的應用
1.1 作用
與where子句類型類似，屬于后過濾
1.2 例子1：統(tǒng)計中國每個省的總人口數，只打印總人口數小于100
select countrycode,sum(population)
from world.city
where countryccode='CHN'
group by district
having sum(population)<100000;
1.4 order by + limit
1.1 作用
實現(xiàn)先排序，by后添加條件列
1.2 例子
例子1：統(tǒng)計中國每個省得總人口，只顯示人口數大于500W的信息，并按總人口數從大到小排序
select district,sum(population)
from world.city
where countrycode='CHN'
group by district
having sum(population)>5000000
order by sum(population) desc;
例子2：統(tǒng)計中國每個省得總人口，只顯示人口數大于500W的信息，并按總人口數從大到小排序，只顯示前5名
select district,sum(population)
from world.city
where countrycode='CHN'
group by district
having sum(population)>5000000
order by sum(population) desc
limit 5;
如果要顯示，6到10行
select district,sum(population)
from world.city
where countrycode='CHN'
group by district
having sum(population)>5000000
order by sum(population) desc
limit 5,5;(表示跳過前面的5行，顯示第5行后面的5行)
第4章 多表連接查詢
1.1 環(huán)境準備
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;
1.2 練習題
1. 查詢一下世界上人口數量小于100人的城市名和國家名
2. 查詢城市shenyang，城市人口，所在國家名（name）及國土面積（SurfaceArea）
3. 統(tǒng)計zhang3,學習了幾門課
4. 查詢zhang3,學習的課程名稱有哪些
5. 查詢oldguo老師教的學生名
6. 每位老師所教課程的平均分,并按平均分排序
7. 查詢oldguo所教的不及格的學生姓名
8. 查詢所有老師所教學生不及格的信息
9. 查詢平均成績大于60分的同學的學號和平均成績；
10. 查詢所有同學的學號、姓名、選課數、總成績；
11. 查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
12. 統(tǒng)計各位老師,所教課程的及格率
13. 查詢每門課程被選修的學生數
14. 查詢出只選修了一門課程的全部學生的學號和姓名
15. 查詢選修課程門數超過1門的學生信息
16. 統(tǒng)計每門課程:優(yōu)秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的學生列表
17. 查詢平均成績大于85的所有學生的學號、姓名和平均成績
1.3 sql50題
1.1 環(huán)境準備
SId 學生編號,Sname 學生姓名,Sage 出生年月,Ssex 學生性別
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-01-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-01-01' , '女');
insert into Student values('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '趙六' , '2013-06-13' , '女');
insert into Student values('13' , '孫七' , '2014-06-01' , '女');
課程表
Course(CId,Cname,TId)
CId 課程編號,Cname 課程名稱,TId 教師編號
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數學' , '01');
insert into Course values('03' , '英語' , '03');
教師表
Teacher(TId,Tname)
TId 教師編號,Tname 教師姓名
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
成績表
SC(SId,CId,score)
SId 學生編號,CId 課程編號,score 分數
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
1.2 習題
查詢" 01 “課程比” 02 "課程成績高的學生的信息及課程分數
查詢同時存在" 01 “課程和” 02 "課程的情況
查詢存在" 01 “課程但可能不存在” 02 "課程的情況(不存在時顯示為 null )
查詢平均成績大于等于 60 分的同學的學生編號和學生姓名和平均成績
查詢在 SC 表存在成績的學生信息
查詢所有同學的學生編號、學生姓名、選課總數、所有課程的成績總和
查詢學過「張三」老師授課的同學的信息
查詢沒有學全所有課程的同學的信息
查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信息
詢和" 01 "號的同學學習的課程完全相同的其他同學的信息
查詢沒學過"張三"老師講授的任一門課程的學生姓名
查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
檢索" 01 "課程分數小于 60，按分數降序排列的學生信息
按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
查詢各科成績最高分、最低分和平均分,以如下形式顯示：
課程ID 課程name 課程人數 最高分 最低分 平均分 及格率 中等率 優(yōu)良率 優(yōu)秀率
CId Cname number max min mean >=60 70-80 80-90 90-100
按各科成績進行排序，并顯示排名， Score 重復時保留名次空缺
按各科成績進行排序，并顯示排名， Score 重復時合并名次
查詢學生的總成績，并進行排名，總分重復時不保留名次空缺
統(tǒng)計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
查詢各科成績前三名的記錄
查詢每門課程被選修的學生數
查詢出只選修兩門課程的學生學號和姓名
查詢男生、女生人數
查詢名字中含有「風」字的學生信息
查詢同名學生名單，并統(tǒng)計同名人數
查詢 1990 年出生的學生名單
查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列
查詢平均成績大于等于 85 的所有學生的學號、姓名和平均成績
查詢課程名稱為「數學」，且分數低于 60 的學生姓名和分數
查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況)
查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數
查詢存在不及格的課程
查詢課程編號為 01 且課程成績在 80 分及以上的學生的學號和姓名
求每門課程的學生人數
成績不重復，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
成績有重復的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
查詢不同課程成績相同的學生的學生編號、課程編號、學生成績
查詢每門功成績最好的前兩名
統(tǒng)計每門課程的學生選修人數（超過 5 人的課程才統(tǒng)計）
索至少選修兩門課程的學生學號
查詢選修了全部課程的學生信息
按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
查詢本周過生日的學生
查詢下周過生日的學生
查詢本月過生日的學生
查詢下月過生日的學生
學習的進階之路
總結
以上是生活随笔為你收集整理的DQL-查询语言的应用的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： systemverilog 起步
- 下一篇： 教你学会Linux/Unix下的vi文本