Day02回顾
- 数据类型
- 数值类型
- 字符类型
- 枚举类型
- 日期时间类型
- date
- time
- datetime : 不给值默认返回NULL
- timestamp :不给值默认返回系统当前时间
- 日期时间函数
- NOW()
- CURDATE()
- CURTIME()
- YEAR(字段名)
- DATE(字段名)
- TIME(字段名)
- 日期时间运算
- select … from 表名
where 字段名 运算符(now()-interval 时间间隔单位)
1 day | 2 day | 3 hour
- select … from 表名
- 表字段操作
- 添加(add)
alter table 表名 add 字段名 数据类型 first | after 字段名; - 删除(drop)
alter table 表名 drop 字段名; - 修改(modify)
alter table 表名 modify 字段名 新数据类型; - 表重命名(rename)
alter table 表名 rename 新表名; - 表字段重命名(change)
alter table 表名 change 原名 新字段名 数据类型;
- 添加(add)
- 表记录管理
1、删除 :delete from 表名 where 条件;
2、修改 :update 表名 set 字段1=值1,… where 条件; - 运算符
- 数值&&字符&&逻辑比较
1、数值 :> >= < <= = !=
2、字符 := !=
3、逻辑 :and or - 范围内比较
in(值1,值2)
not in(值1,值2)
between 值1 and 值2 - 空、非空
is null
is not null - 模糊查询
where 字段名 like “%_”
- 数值&&字符&&逻辑比较
- SQL查询
- order by 字段名 ASC/DESC
- limit
1、limit n
2、limit m,n
3、分页:每页m条记录,显示第n页内容
limit (n-1)*m,m - 聚合函数
avg(字段名) sum(…) max(…) min(…)
count(字段名)
Day03笔记
-
SQL查询
- 总结
- select 聚合函数 from 表名
- where 条件
- group by …
- having …
- order by …
- limit …
- 总结
-
group by :给查询的结果进行分组
1、计算每个国家的平均攻击力select country,avg(gongji) from sanguo
group by country;2、查找所有国家英雄中,英雄数量最多的国家的前2名,显示国家名称和英雄数量
select country,count(id) as number from sanguo
group by country
order by number desc
limit 2;先分组 再聚合 去重
蜀国
蜀国
蜀国 100 蜀国魏国
魏国 100 魏国吴国
吴国 99 吴国
3、注意
查询字段和group by后字段不一致,则必须对该查询字段进行聚合处理(聚合函数) -
having语句 :对分组聚合后的结果进一步筛选
1、找出平均攻击力>105的国家的前2名,显示国家名称和平均攻击力select country,avg(gongji) from sanguo
group by country
having avg(gongji)>105
order by avg(gongji) desc
limit 2;2、注意
having语句通常和group by语句联合使用,having语句弥补了where关键字不能与聚合函数使用的不足,where只能操作表中实际存在字段
-
distinct :不显示字段的重复值
1、表中有哪些国家
select distinct country from sanguo;
2、计算蜀国有多少个英雄
select count(distinct id) as number from sanguo
where country=“蜀国”; -
查询表记录时做数学运算
+ - * / %
查询时所有英雄攻击力翻倍
select name,gongji2 as xgj from sanguo;
update sanguo set gongji=gongji2;
嵌套查询
-
嵌套查询(子查询)
- 定义 :把内层的查询结果作为外层的查询条件
- select … from 表名 where 字段名 运算符 (查询);
- 把攻击值小于平均攻击值的英雄名字和攻击值显示
1、先算出平均攻击值
select avg(gongji) from sanguo;
2、找小于平均值
select name,gongji from sanguo where gongji<…
3、嵌套查询实现
select name,gongji from sanguo where gongji<(select avg(gongji) from sanguo); - 找出每个国家攻击力最高的英雄的名字和攻击值
select name,gongji from sanguo
where
(country,gongji) in (select country,max(gongji) from sanguo group by country);
-
多表查询
1、笛卡尔积 :select … from 表1,表2; 不加where
2、多表查询 :加 where 条件
3、显示省、市详细信息河北省 石家庄市
河北省 廊坊市
广东省 深圳市
select sheng.s_name,city.c_name from sheng,city
where
sheng.s_id=city.cfather_id;4、显示省、市、县详细信息
select sheng.s_name,city.c_name,xian.x_name from sheng,city,xian
where
sheng.s_id=city.cfather_id and
city.c_id=xian.xfather_id; -
连接查询
-
内连接(inner join :只显示满足匹配条件的结果)
1、select … from 表1 inner join 表2 on 条件;2、显示省、市详细信息
select sheng.s_name,city.c_name from sheng
inner join city
on sheng.s_id = city.cfather_id;3、显示省、市、县详细信息
select sheng.s_name,city.c_name,xian.x_name from sheng
inner join city
on sheng.s_id=city.cfather_id
inner join xian
on city.c_id=xian.xfather_id; -
左外连接(left join:以左表为主显示查询结果)
1、select … from 表1 left join 表2 on 条件;
2、显示省、市详细信息,要求省全都显示select sheng.s_name,city.c_name from sheng
left join city
on sheng.s_id=city.cfather_id;3、显示省、市、县详细信息,要求 市 全都显示
select sheng.s_name,city.c_name,xian.x_name from
sheng right join city
on sheng.s_id=city.cfather_id
left join xian
on city.c_id=xian.xfather_id; -
右外连接(right join:以右表为主显示查询结果)
-
-
约束
1、非空约束(NOT NULL)
不允许该字段值有NULL记录
create …(
name varchar(20) NOT NULL,
2、默认约束(DEFAULT 值)
插入记录时,不给该字段赋值,则使用默认值
create …(
sex enum(“M”,“F”,“S”) not null default “S” -
索引
- 定义 :对数据表的一列或者多列的值进行排序的一种结构(BTree)
- 优点 :加快数据检索速度
- 缺点
1、占用物理存储空间
2、当对表中数据更新时,索引需要动态维护,降低数据维护速度 - 索引示例
查看变量状态 :show variables like “%pro%”;
1、开启运行时间监测
mysql> set profiling=1;
2、执行1条查询语句(无索引)
select name from t1 where name=“lucy99999”;
3、在name字段创建索引
create index name on t1(name);
4、再执行1条查询语句(有索引)
select name from t1 where name=“lucy100000”;
5、查看执行时间
mysql> show profiles;
6、关闭运行时间监测
mysql> set profiling=0;
-
索引分类
- 普通索引(index)、唯一索引(unique)使用规则
1、可创建多个字段
2、普通索引无约束,唯一索引要求字段值不允许重复,但可为NULL
3、KEY标志 :普通(MUL) 唯一(UNI)
4、经常用查询的字段、where后、order by排序字段创建索引 - 创建
create table t1(
… …,
index(字段名),
index(字段名),
unique(字段名),
unique(字段名)
)charset=utf8;
create [unique] index 索引名 on 表名(字段名);- 查看
1、desc 表名; --> KEY标志
2、show index from 表名\G; - 删除
drop index 索引名 on 表名;
- 普通索引(index)、唯一索引(unique)使用规则