DQL应用(select )

作者: 小熊渣渣 | 来源:发表于2021-01-28 18:07 被阅读0次

01，单独使用

-- select @@xxx 查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;

-- select 函数();

SELECT NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg

02，单表子句-from

SELECT 列1,列2 FROM 表
SELECT  *  FROM 表

例子:
-- 查询stu中所有的数据(不要对大表进行操作)

SELECT * FROM stu ;

-- 查询stu表中,学生姓名和入学时间

SELECT sname , intime FROM stu;

03，单表子句-where

SELECT col1,col2 FROM TABLE WHERE colN 条件;

1 ，where配合等值查询

例子:
-- 查询中国(CHN)所有城市信息

-- 查询北京市的信息

SELECT * FROM city WHERE NAME='peking';

-- 查询甘肃省所有城市信息

SELECT * FROM city WHERE district='gansu';

2， where配合比较操作符(> < >= <= <>)

例子:
-- 查询世界上少于100人的城市

3， where配合逻辑运算符(and or )

例子:
-- 中国人口数量大于500w

SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;

-- 中国或美国城市信息

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

###4，where配合模糊查询

例子:
-- 查询省的名字前面带guang开头的

SELECT * FROM city WHERE district LIKE 'guang%';    
注意:%不能放在前面,因为不走索引.

5， where配合in语句

-- 中国或美国城市信息

SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA');

6 ，where配合between and

例子:
-- 查询世界上人口数量大于100w小于200w的城市信息

SELECT * FROM city  WHERE population >1000000 AND population <2000000;
SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;

04 ，group by + 常用聚合函数

作用：根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

1，常用聚合函数

**max()**      ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：个数
group_concat() : 列转行

3 ，例子：

例子1：统计世界上每个国家的总人口数.

USE world
SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;

例子2：统计中国各个省的总人口数量(练习)

SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;

例子3：统计世界上每个国家的城市数量(练习)

SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;

05，having

where|group|having

例子4：统计中国每个省的总人口数，只打印总人口数小于100

SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;

06 ，order by + limit

作用：实现先排序，by后添加条件列

应用案例

1，查看中国所有的城市，并按人口数进行排序(从大到小)

SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

2，统计中国各个省的总人口数量，按照总人口从大到小排序

SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;

3，统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

7 ，distinct：去重复

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;

8 ，联合查询- union all

-- 中国或美国城市信息

SELECT * FROM city 
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION     去重复
UNION ALL 不去重复

9， join 多表连接查询

1，案例准备

按需求创建一下表结构:

use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

sc  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

-- 项目构建
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,'ma5',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES 
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldbaby',1,'m');

INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

INSERT INTO course(cno,cname,tno) VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

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,1000,61),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1000,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

2，语法

图片.png

查询张三的家庭住址

SELECT A.name,B.address FROM
A JOIN  B
ON A.id=B.id
WHERE A.name='zhangsan'

3，例子：

查询一下世界上人口数量小于100人的城市名和国家名

SELECT b.name ,a.name ,a.population
FROM  city  AS a
JOIN  country AS b
ON    b.code=a.countrycode
WHERE  a.Population<100

查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

SELECT a.name,a.population,b.name ,b.SurfaceArea
FROM city  AS a JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';

4，多表SQL练习题

1，统计zhang3,学习了几门课

SELECT student.sname ,COUNT(sc.cno)
FROM student
JOIN 
sc
ON student.sno = sc.sno
WHERE student.sname = 'zhang3';

图片.png

2，查询zhang3,学习的课程名称有哪些?

SELECT student.sname,GROUP_CONCAT(course.cname)
FROM student
JOIN sc
ON student.sno = sc.sno
JOIN course
ON sc.cno = course.cno
WHERE student.sname = 'zhang3';

图片.png

3，查询oldguo老师教的学生名.

SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname='oldguo'

图片.png

4，每位老师所教课程的平均分,并按平均分排序

SELECT teacher.tname,AVG(sc.score)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
GROUP BY teacher.tno
ORDER BY AVG(sc.score) DESC;

图片.png

5，查询oldguo所教的不及格的学生姓名

SELECT  teacher.tname,student.sname,sc.score
FROM student    
JOIN sc
ON student.sno=sc.sno
JOIN course
ON course.cno=sc.cno
JOIN teacher
ON teacher.tno=course.tno
WHERE teacher.tname='oldguo' AND sc.score<60;

图片.png

6，查询所有老师所教学生不及格的信息

SELECT  teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score))
FROM student    
JOIN sc
ON student.sno=sc.sno
JOIN course
ON course.cno=sc.cno
JOIN teacher
ON teacher.tno=course.tno
WHERE sc.score<60
GROUP BY teacher.tno;

图片.png

5，别名应用

表别名

SELECT  t.tname,GROUP_CONCAT(CONCAT(s.sname,":",sc.score))
FROM student  AS s  
JOIN sc
ON s.sno=sc.sno
JOIN course AS c
ON c.cno=sc.cno
JOIN teacher AS t
ON t.tno=c.tno
WHERE sc.score<60
GROUP BY t.tno;

列别名

SELECT  t.tname AS 讲师名,GROUP_CONCAT(CONCAT(s.sname,":",sc.score)) AS 不及格
FROM student  AS s  
JOIN sc
ON s.sno=sc.sno
JOIN course AS c
ON c.cno=sc.cno
JOIN teacher AS t
ON t.tno=c.tno
WHERE sc.score<60
GROUP BY t.tno;

图片.png

ps：表别名是全局调用的，列别名可被 having 和 order by 调用

06，information_schema.tables视图

DESC information_schema.TABLES
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）

1，查询整个数据库中所有库和所对应的表信息

SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

图片.png

2，统计所有库下的表个数

SELECT table_schema,COUNT(table_name)
FROM information_schema.TABLES
GROUP BY table_schema

图片.png

3，查询所有innodb引擎的表及所在的库

SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE='innodb';

图片.png

4，统计world数据库下每张表的磁盘空间占用

SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA='world';

图片.png

5，统计所有数据库的总的磁盘空间占用

SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

图片.png

6，生成整个数据库下的所有表的单独备份语句

模板语句：
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
FROM information_schema.tables
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;

CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

7，107张表，都需要执行以下2条语句

ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';

07，show 命令

show  databases;                                     #查看所有数据库
show tables;                                         #查看当前库的所有表
SHOW TABLES FROM                                     #查看某个指定库下的表
show create database world                           #查看建库语句
show create table world.city                         #查看建表语句
show  grants for  root@'localhost'                   #查看用户的权限信息
show  charset；                                      #查看字符集
show collation                                       #查看校对规则
show processlist;                                    #查看数据库连接情况
show index from                                      #表的索引情况
show status                                          #数据库状态查看
SHOW STATUS LIKE '%lock%';                           #模糊查询数据库某些状态
SHOW VARIABLES                                       #查看所有配置信息
SHOW variables LIKE '%lock%';                        #查看部分配置信息
show engines                                         #查看支持的所有的存储引擎
show engine innodb status\G                          #查看InnoDB引擎相关的状态信息
show binary logs                                     #列举所有的二进制日志
show master status                                   #查看数据库的日志位置信息
show binlog evnets in                                #查看二进制日志事件
show slave status \G                                 #查看从库状态
SHOW RELAYLOG EVENTS                                 #查看从库relaylog事件信息
desc  (show colums from city)                        #查看表的列定义信息

DQL应用(select )

01，单独使用

02，单表子句-from

03，单表子句-where

1 ，where配合等值查询

2， where配合比较操作符(> < >= <= <>)

3， where配合逻辑运算符(and or )

5， where配合in语句

6 ，where配合between and

04 ，group by + 常用聚合函数

作用：根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

1，常用聚合函数

3 ，例子：

05，having

06 ，order by + limit

作用：实现先排序，by后添加条件列

应用案例

7 ，distinct：去重复

8 ，联合查询- union all

9， join 多表连接查询

1，案例准备

2，语法

3，例子：

4，多表SQL练习题

5，别名应用

ps：表别名是全局调用的，列别名可被 having 和 order by 调用

06，information_schema.tables视图

07，show 命令

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

DQL应用(select )

01， 单独使用

02，单表子句-from

03，单表子句-where

1 ，where配合等值查询

2， where配合比较操作符(> < >= <= <>)

3， where配合逻辑运算符(and or )

5， where配合in语句

6 ，where配合between and

04 ，group by + 常用聚合函数

作用：根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

1， 常用聚合函数

3 ，例子：

05，having

06 ，order by + limit

作用：实现先排序，by后添加条件列

应用案例

7 ，distinct：去重复

8 ，联合查询- union all

9， join 多表连接查询

1，案例准备

2，语法

3，例子：

4，多表SQL练习题

5，别名应用

ps：表别名是全局调用的，列别名可被 having 和 order by 调用

06，information_schema.tables视图

07，show 命令

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

01，单独使用

1，常用聚合函数