个人学习使用
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 ' ';
