MySQL - DQL数据查询语言


个人学习使用

cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core)
----------------------------------------------------------------------
getenforce 
Enforcing
----------------------------------------------------------------------
systemctl status firewalld.service 
   Active: active (running)
----------------------------------------------------------------------
systemctl status NetworkManager
   Active: active (running)
----------------------------------------------------------------------
MySQL
Server version: 5.7.26

一)select

将world.sql添加到MySQL数据库中

mysql -uroot -p123456 < world.sql

1.1)mysql独有的select功能

  • 查询数据库参数
select @@port;
select @@datadir;
select @@innodb_flush_log_at_trx_commit;
show variables;
show variables like '%trx%';
  • 查询函数
select database();
select current_user();
select now();
  • 做计算器
select 1+2;
select 2*8;

1.2)select单表通用方法

  • 单表查询语法
select--->from--->where--->group by--->select_list--->having--->order by--->limit

1.2.1)from

  • 查看全表数据
select * from city;
  • 查看部分表数据
select name,population from city;

1.2.2)where

  • where 等值查询
#查询中国所有城市信息.
select * from city where countrycode='CHN';
--------------------------------------------------------------------------
#查询中国所有城市信息(省,城市,人口)
select district,name,population from city where countrycode='CHN';
  • where 不等值查询 (>,<,>=,<=,<> !=,like)
#查询一下人口数小100人的城市
select * from city where population<100;
--------------------------------------------------------------------------
#查询一下国家代号US开头的
select * from city where countrycode like 'US%';
  • where 配合逻辑连接符,多条件查询(and ,or)
#查询一下美国和日本的城市信息
select * from city where countrycode='JPN' or countrycode='USA';
select * from city where countrycode in ('JPN','USA');
--------------------------------------------------------------------------
#查询一下中国人口超过500w的城市信息
select * from city where countrycode='CHN' and population > '5000000';
--------------------------------------------------------------------------
#查询中国人口100w-200w城市
select * from city where countrycode='CHN' and population>'1000000' and population<'2000000';
select * from city where countrycode='CHN' and population between '1000000' and '2000000';

1.2.3)group by + 聚合函数

count()计数器、sum()求和、avg()平均值、max()最大值、min()最小值、group_concat()列转行

  • 统计每个国家总人口数量
select countrycode,sum(population) from city
group by countrycode;
  • 统计每个国家的城市数量
select countrycode,count(name) from city
group by countrycode;
  • 统计中国每个省的人口数量
mysql> select district,sum(population) from city
where countrycode='CHN'
group by district;
  • 统计中国每个省的城市数量,并列出城市名
select district,count(name),group_concat(name) from city 
where countrycode='CHN'
group by district;

1.2.4)having 后判断

  • 统计中国每个省的人口数量,并且人口数量小于100W
select district,sum(population) from city
where countrycode='CHN'
group by district
having sum(population) < 1000000;

1.2.5)order by 排序

  • 统计中国每个省的人口数量,并且人口数量小于100W,人口数量从大到小排序
select district,sum(population) from city
where countrycode='CHN'
group by district
having sum(population) < 1000000
order by sum(population) desc;
#默认排序从小到大;
#desc排序从大到小

1.2.6)limit

  • 统计中国每个省的人口数量并且人口数量小于100W,人口数量从大到小排序,只显示第三行与第四行
select district,sum(population) from city
where countrycode='CHN'
group by district
having sum(population) < 1000000
order by sum(population) desc
limit 2,2;

二)show

  • 查询帮助
help show;
  • 查看所有库
show databases;
  • 查看所有表
show tables;
  • 查看用户授权
show grants for root@'localhost';
  • 查看字符集
show charset;
  • 查看校对规则
show collation;
  • 查看数据库引擎
show engines;
  • 查看建库语句
show create database world;
  • 查看建表语句
show create table city;
  • 查看表索引
show index from city;
  • 查看表索引
show index from city;
show processlist;
show engine innodb status \G
show mater status;
show slave status \G
show binary logs;
show binlog events in ' ';
show status like ' ';
show variables like ' ';
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容