mysql查询语言_MySQL数据库-第三章：MySQL查询语言

上集回顾:

1. create table like 只能复制表结构,那有什么命令可以连数据一起复制?

(1) 方法一:

create table stu select * from student;

主键等特性没有被复制.

(2) 方法二:

create table st like student;

insert into st select * from student

2. pt 索引章节之后

pt-archivher

pt-osc

3. 主键是干什么用的

(1) 约束 : unique not null

(2) 聚簇索引索引: 组织和存储数据.

(3) 加速查询

4. update 和 delete 范围操作

update t1 set name='王二麻子' where name like '王二%'

delete from t1 where name like '王二%';

delete from t1 where id >1 and id <5 ;

delete from t1 where id between 1 and 5 ; >=1 <=5

===========================================

DQL 数据查询语言

1. SELECT

1.1 SELECT 单独使用 ***

(1) 查询数据库的参数.

SELECT @@port;

SELECT @@datadir;

SELECT @@basedir;

SELECT @@innodb_flush_log_at_trx_commit;

SHOW VARIABLES LIKE '%trx%';

(2) 调用内置函数.

USE oldguo

SELECT DATABASE();

SELECT NOW();

SELECT CONCAT(USER,"@",HOST) FROM mysql.user;

SELECT GROUP_CONCAT(xid) FROM student;

SELECT SUM(xid) FROM student;

(3) 简易计算器

SELECT 4*5;

1.2 select 配合其他子句使用

1.2.1 子句列表介绍

FROM -- 查询对象(表,视图)

WHERE -- 过滤子句(grep)

GROUP BY -- 分组子句(统计分析类)

ORDER BY -- 排序子句

HAVING -- 后过滤子句

LIMIT -- 限制子句(分页子句)

1.2.2 配合FROM应用

-- world 模板库介绍

--- 英文单词介绍

--- city -- 城市

--- id -- 序号ID主键

--- NAME -- 城市名

--- Countrycode -- 国家代码(CHN,USA,JPN)

--- District -- 省,州

--- Population -- 城市人口数

例子:

1.查询表中所有数据(cat)

SELECT * FROM city;

查询name和population信息 (awk取列)

SELECT NAME,population FROM city;

1.2.3 select+ from + where(grep)使用

where 配合等值查询

例子:

1. 查询中国所有的城市信息

SELECT * FROM city

WHERE countrycode='CHN';

2. 查询ID为100的城市信息

SELECT * FROM city

WHERE id=100;

3. 查询 中国河北省的城市信息

SELECT * FROM city

WHERE countrycode='CHN' AND district='hebei' ;

4. 查询 中国或者美国的城市

SELECT * FROM city

WHERE countrycode='CHN' OR countrycode='USA';

或者:

SELECT * FROM city

WHERE countrycode IN ('CHN','USA');

或者:

SELECT * FROM city

WHERE countrycode='CHN'

UNION ALL

SELECT * FROM city

WHERE countrycode='USA' ;

where 配合范围查询

例子 :

1. 查询人口数量小于100人的城市

SELECT * FROM city

WHERE population<100;

2. 查询人口数量100w-200w之间的

SELECT * FROM city

WHERE population>=1000000 AND population<=2000000 ;

或者:

SELECT * FROM city

WHERE population BETWEEN 1000000 AND 2000000 ;

3. 查询国家代号是CH开头的城市信息

SELECT * FROM city

WHERE countrycode LIKE 'CH%';

1.2.4 group by 分组子句+聚合函数应用

聚合函数?

COUNT() -- 计数

SUM() -- 求和

AVG() -- 求平均值

MAX() -- 求最大值

MIN() -- 最小值

GROUP_CONCAT() -- 聚合列值

结果集显示特点: 必须是1v1,不能是1vN

例子 :

1. 统计一下每个国家的人口总数

SELECT countrycode,SUM(population)

FROM city

GROUP BY countrycode;

2. 统计中国每个省的人口总数

SELECT district,SUM(population) FROM city

WHERE countrycode='CHN'

GROUP BY district;

3. 统计下中国每个省的城市个数及城市名.

SELECT district,COUNT(NAME),GROUP_CONCAT(NAME) FROM city

WHERE countrycode='CHN'

GROUP BY district;

4. 统计每个国家城市个数

SELECT countrycode ,COUNT(NAME) FROM city

GROUP BY countrycode;

1.2.6 having 后判断

1. 统计中国每个省的人口总数,只显示总人口数大于500w的省信息.

SELECT district,SUM(population) FROM city

WHERE countrycode='CHN'

GROUP BY district

HAVING SUM(population) >=5000000;

1.2.7 order by 排序子句

例子:

1. 查询中国所有城市信息,人口数从大到小排序输出.

SELECT * FROM city

WHERE countrycode='CHN'

ORDER BY population DESC ;

2. 查询中国所有城市信息,按城市名排序.

SELECT * FROM city

WHERE countrycode='CHN'

ORDER BY NAME;

3. 查询中国所有省的总人口,并按总人口数从大到小排序输出.

SELECT district,SUM(population) FROM city

WHERE countrycode='CHN'

GROUP BY district

ORDER BY SUM(population) DESC;

1.2.8 limit 分页限制子句

查询中国所有省的总人口,并按总人口数从大到小排序输出.

SELECT district,SUM(population) FROM city

WHERE countrycode='CHN'

GROUP BY district

ORDER BY SUM(population) DESC

LIMIT 5 OFFSET 1;

SELECT district,SUM(population) FROM city

WHERE countrycode='CHN'

GROUP BY district

ORDER BY SUM(population) DESC

LIMIT 10;

SELECT district,SUM(population) FROM city

WHERE countrycode='CHN'

GROUP BY district

ORDER BY SUM(population) DESC

LIMIT 1,5;

注意: LIMIT 谨慎使用, 500w+的表. LIMIT 5000000,100,一般会改为明确查找范围