day03 数据的导入和导出以及简单查询

1.数据的导入和导出

#导入
create database king_of_honor;

#方式一(cmd):mysql -u root -p db_name <file.sql
use king_of_honor;
show tables;
select * from heros;
drop table heros;
#方式二(mysql):source file.sql
show tables;#注意use king_of_honor数据库
select * from heros;

#导出
# 方式(cmd): mysqldump -u $user -p db_name > file.sql

2.常用运算符

#算术运算符:+ ,-, *, / ,%
#比较运算符:=, <=>, <>(!=), >, >=, <, >=, is null, is not null, in, not in, between and, like

# =, <=>,is null, is not null
# 查询没有辅助角色定义的英雄有哪些？
select *from heros;
select name,role_assist from heros where role_assist=null;#NULL的值是没有任何意义的。所以=号运算符不能把NULL作为有效的结果。所以：请使用<=>
select name,role_assist from heros where role_assist is null;
select null<=> null;
select null=null;
select *from heros;
# 查询有辅助角色定位的英雄有哪些？(和上面的道理相同)
select name, role_assist from heros where role_assist != null;
select name, role_assist from heros where role_assist is not null;

# between and: 是否在某个闭区间内
# in: 是否在列表内
# not in: 是否不在列表内 
# 查询最大生命大于等于5399,小于等于7350的英雄有哪些？
select name hp_max from heros where hp_max between 5399 and 7350;
 # 查询主要角色定位为法师和射手的英雄有哪些？
select name,role_main from heros where role_main='法师' or role_main='射手';#第二个role_main不能省略
select name,role_main from heros where role_main in('法师' , '射手');
# 查询主要角色定位不是辅助和坦克的英雄有哪些？
select name,role_main from heros where role_main not in('辅助','坦克');

# like: 与通配符搭配使用，表示模糊查询。
# %: 匹配任何数目的字符，包括零字符
# _: 只能匹配一个字符
# 查询姓张的英雄有哪些？ 
select name from heros where name like '张%';
select name from heros where name like '%太%';
# 查询除第一个字符外，名字中包含'太'的英雄有哪些？
select name from heros where name like '_%太%';
# 注意：LIKE 关键字一般是与通配符一起使用, 在 MySQL 中 '_' 表示匹配单个字符, 
# '%' 表示匹配任意个字符。在不同的 DBMS 中，通配符的符号可能不一样。
# 比如，在 ACCESS 中使用 '?' 匹配单个字符，使用 '*' 匹配任意个字符。

#逻辑运算符 and(&&), or(||), not(!)
#位运算符：&, |, ^, ~, <<, >>

3.简单查询

# 1. 计算表达式和函数的值
select 2*3;
select now();
select concat('a','b','c','d');
select sqrt(9);

# 2. 查询表中的字段
select name from heros;
select name,hp_max,mp_max,role_main from heros;
select * from heros;#所有字段

# 3. 使用 WHERE 子句过滤记录
# 查询主要角色定位为'法师'的英雄有哪些?
select name,role_main from heros where role_main='法师';

# 4. 给字段起别名
select name,hp_max as hp,mp_max as mp from heros;
select *from heros;
# 注意：AS 关键字可以省略，但是不推荐这样做。 AS 关键字不仅仅可以给字段起别名，还可以给表起别名。

# 5. 去除重复行 distinct
# 查询主要角色定位有哪些？ 
select distinct role_main from heros;
# 查询不同的主要角色定位和辅助角色定位。
select distinct role_main,role_assist from heros;
# 注意：a.DISTINCT 是对所有查询字段的组合进行去重，也就是说每个字段都相同，才认为两条记录是相同的。
# b. DISTINCT 关键字必须放在所有查询字段的前面.

# 6.排序 
select name,hp_max from heros order by hp_max;#默认升序
select name,hp_max from heros order by hp_max asc;#升序
select name,hp_max from heros order by hp_max desc;#降序
# 按照多个字段排序 
select name,hp_max,mp_max from heros order by hp_max, mp_max;
select name,hp_max,mp_max from heros order by hp_max, mp_max desc; #在前面的优先级高
select name,hp_max,mp_max from heros order by hp_max asc, mp_max desc;
# 对非选择字段进行排序 
select name,hp_max from heros order by hp_max asc,mp_max desc;
# 对计算字段排序 
select name,hp_max,mp_max from heros order by (hp_max + mp_max);

# 7. 限制结果集的数量
#语法:limit [offset,]nums;     或者: limit nums OFFSET offset;
# 练习：我们想查询最大生命值最高的5名英雄。
select name,hp_max from heros order by hp_max desc limit 5 offset 0;
# 当偏移量为0时,我们可以省略offset
select name, hp_max from heros order by hp_max desc limit 5;
# 分页查询(page, rows)
#limit rows offset (page-1)*rows;
# 注意：不同的 DBMS 用来限制结果集的关键字是不一样的。
# 比如，Microsoft SQL Server 和 Access  使用的是 TOP 关键字。

# 8. 计算字段
select name,hp_max+mp_max from heros;
select name,hp_max+mp_max as total_max from heros;

# 9. 聚合函数 
# count(),sum(),avg(),max(),min()
# a. count()
# `COUNT(*)` 可以统计记录数, 可以统计null记录。
use mydb1;
create table t_count(
	a int,
    b int
);
insert into t_count values (null, null), (1, null),(null, 2),(3, 3);
select *from t_count;
select count(*) as nums from t_count;#包括null
# `COUNT()` 作用于某个具体的字段，可以统计这个字段的非 `NULL` 值的个数。
select count(a) from t_count;
# b. sum()
use king_of_honor;
SELECT SUM(hp_max) FROM heros;
# c. avg()
select avg(hp_max) from heros;
select round(avg(hp_max), 2) as avg from heros;#保留两位小数
# d. max()
select max(hp_max) from heros;
# e. min()
select min(hp_max) from heros;
# f. distinct 
select sum(hp_max) from heros;
select sum(distinct hp_max) from heros;#去除重复行

# 10. 分组(group by)
# a.搭配聚合函数使用
# 练习：按照主要角色定位进行分组，并统计每一组的英雄数目。
select role_main,count(*) from heros group by role_main;
# 练习：按照次要角色定位进行分组，并统计每一组的英雄数目。
select role_assist,count(*) from heros group by role_assist;#包括null
# b.GROUP_CONCAT
# 练习：我们想查询每种角色的英雄都有哪些？
select role_main,name from heros group by role_main;#只显示一个
select role_main,group_concat(name) from heros group by role_main;#连接字段
# c. 多字段分组
select role_main,role_assist,count(*) as num from heros group by role_main,role_assist order by num desc;
# d. HAVING过滤分组
# 练习：我们想要按照英雄的主要角色定位，次要角色定位进行分组，并且筛选分组中英雄数目大于 5 的组，
# 最后根据每组的英雄数目从高到低进行排序。
select role_main,role_assist,count(*) as num from heros 
group by role_main,role_assist 
having num>5 
order by num desc;
# 练习：筛选最大生命值大于 6000 的英雄，按照主要角色定位，次要角色定位分组，
# 并且筛选英雄数目大于 5 的分组，最后按照英雄数目从高到低进行排序。
select hp_max,role_main,role_assist,count(*) as num from heros where hp_max >6000 
group by role_main,role_assist
having num>5
order by num desc;