day02python开发boSQL查询 group by（分组）limit 表记录管理

修改字段名

 

1、添加add	alter table 表名 add 字段名 数据类型 first|after 字段名
2、修改modify	alter table 表名 modify 字段名 新数据类型;
3、删除drop	alter table 表名 drop 字段名;

日期时间函数

1、NOW（）	返回服务器当前时间
2、CURDATE()	返回当日期
3、CURTIME()	返回当前时间

示例：

Insert into t1 values

(2，“刘备”，25，CURTIME()，CURDATE()，NOW());

 

日期时间运算

语法格式	Select … from  表名 Where 字段名 运算符 （时间 – interval 时间间隔单位）；
时间间隔单位	1 day| 2 hour | 1 minute | 1 year | 3 month

示例：

Where  id  < 8

Where  meeting  > (now()  -  interval  1  day);

                  现在时间 – 1天的时间 = 1 天前的时间点

时间日期示例：

练习

1、查询1天以内的记录

select * from t1 where meeting>(now()-interval 1 day);

2、查询1年以前的记录

select * from t1

where meeting<(now()-interval 1 year);

3、查询1天以前,3天以内的记录

select * from t1 where

meeting<(now()-interval 1 day) and

meeting>(now()-interval 3 day);

练习

1、创建库 studb2

create database studb2;

2、在库中创建表 stuinfo,字段有3个：

name、age、phone char(11)

use studb2;

create table stuinfo(

name varchar(20),

age tinyint unsigned,

phone char(11)

);

3、查看表结构

desc stuinfo;

4、在表中第一列添加一个 id 字段

alter table stuinfo add id int first;

5、把 phone 的数据类型改为 bigint

alter table stuinfo modify phone bigint;

6、在表中最后一列添加一个字段：注册时间 register ,数据类型为：timestamp

alter table stuinfo add register timestamp;

7、在表中 id name age phone 四个字段插入1条记录

insert into stuinfo(id,name,age,phone) values(1,"关羽",23,1363638438);

8、查询5分钟内的注册信息

select * from stuinfo where register>(now()-interval 5 minute);

#####修改字段名######

alter table 表名 change 原字段名 新字段名 数据类型;

 

表记录管理

1、删除表记录（delete）	delete from 表名 where 条件； 注意！！ delete语句后没有加where条件，表中所有记录全部清除
2、更新表记录 （update）	update 表名 set 字段1=值1，字段名2=值2，… where 条件 注意！！ delete语句后没有加where条件，表中所有记录全部修改

练习

1、查找所有蜀国人信息

select * from hero where country="蜀国";

2、把id为2的英雄名字改为司马懿,国家改为魏国

update hero set name="司马懿",country="魏国" where id=2;

3、查找女英雄的姓名、性别和国家

select name,sex,country from hero where sex="女";

4、删除所有魏国的英雄

delete from hero where country="魏国";

5、查找所有蜀国男英雄的信息

select * from hero where country="蜀国" and sex="男";

6、删除所有的英雄

delete from hero;

 

 

 

 

 

 

运算符操作（查询、修改、删除）

数值比较	=、 !=、 >、 >=、 <、<=
字符比较	=、 ！=
逻辑比较	and、 or
范围内比较	between 值1 and 值2
	in(值1，值2，…)
	not  in(值1，值2，…)
	示例；查找攻击值在100到200之间的蜀国英雄信息 select * from sanguo  where gongji  between 100 and 200 and country=”蜀国”; 找到蜀国和吴国以外的国家的女英雄信息 Select * from sanguo where country not in(“蜀国”，”吴国”) and sex=”女”；
匹配空、非空	空： is null
	非空： is not null
	示例：查找姓名为NULL的魏国女英雄信息 select * from sanguo where (name is null and country=”蜀国”) or sex=”男”；
注意 1、 NULL ： 空值，必须用 is 或者 is not 去匹配 2、 “ ”   :  空字符串，只能用 = 或 !=  去匹配

练习：

1、找出攻击力高于150的蜀国英雄的名字和攻击值

select name,gongji from sanguo where gongji>150 and country="蜀国";

2、将赵云的攻击力改为666,防御力改为88

update sanguo set gongji=666,fangyu=88 where name="赵云666";

3、查找蜀国和魏国的英雄信息

select * from sanguo where country="蜀国" or country="魏国";

4、将吴国英雄中攻击值为110的英雄的攻击值设置为100,防御力设置为60

update sanguo set gongji=100,fangyu=60 where country="吴国" and gongji=110;

 

 

 

 

 

 

 

模糊比较

用法	Where 字段名 like 表达式
表达式	1. _ :p匹配单个字符
	2. %：匹配0到多个字符
	示例： 1、匹配名字找那个至少有两个字符的记录 Select * from sanguo where name like “_%_”; 2、匹配非空null 的记录 Select * from sanguo where name like “%”; 3、 匹配名字里面只有三个字符的记录 Select * from sanguo where name like “___”; 4、匹配名字里面姓赵开头的记录 Select * from sanguo where name like “赵%”;

 

SQL查询

总结	3、Select …聚合函数from 表名
	1、where …
	2、group by …
	4、having …
	5、order by …
	6、limit …

 

order by …	1、给查询的结果进行排序
	2、order by 字段名 排序方式
	3、排序方式	1、 升序 : ASC（默认）
		2、降序 ：DESC

 

练习

1、 将蜀国的英雄按照攻击值从高到低排序

select * from sanguo

where country=”蜀国” order by gongji DESC;

2、 将魏蜀两国的男英雄中名字为三个字符的英雄按防御值升序排列

select * from sanguo

where country in("蜀国","魏国") and sex="男" and name like "___"

   order by fangyu ASC;

 

 

limit (永远放在SQL语句的最后)

作用	限制显示查询记录的个数
用法	1、 limit n --à显示n条记录
	2、limit m,n m:表示从m+1条记录开始显示 n:表示显示n条 示例： limit 2,4：显示第3、4、5、6四条记录 limit0,2: 显示第1、2两条记录
分页	示例： 每页显示5(n)条记录，显示第4（m）页 第一页：limit 0,5  ### 1 2 3 4 5 第二页：limit 5,5 ### 6 7 8 9 10         ## (2-1)*5 第三页：limit 10,5 ### 11 12 13 14 15    ##(3-1)*5 第四页： limit 15,5 ### 16 17 18 19 20   ##(4-1)*5 公式： limit (m-1) * n,n       m： 第几页 n:每页显示记录条数

示例

1、 在蜀国英雄中查找攻击值前三名且名字不为NULL的英雄姓名、攻击值和国家

mysql> select name,gongji,country from sanguo

    -> where

    -> country="蜀国" and name is not NULL

    -> order by gongji DESC

    -> limit 3;

 

2、 查找防御值倒数第2名至倒数第4名的英雄信息

mysql> select *from sanguo

     -> where

 -> country="蜀国" order 缺

 

聚合函数

分类	avg（字段名）：平均值
	max（字段名）：最大值
	min（字段名）：最小值
	sum（字段名）：求和
	count（字段名）：统计该字段记录的格式

示例：

攻击力最强值为：

select max(gongji) from sanguo;

统计一下表中id、name 字段分别有多少条记录

select count(id),count(name) from sanguo;

注意：空值NULL不会被统计

select count(*) from sanguo;

 

 

group by（分组）

作用	给查询的结果进行分组
示例	计算所有国家的平均攻击力，显示国家名和平均攻击力 select country,avg(gongji) from sanguo group by country;

     

先分组	再聚合-	去重
蜀国	400	蜀国
蜀国
蜀国
魏国	300	魏国
魏国
吴国	200	吴国

 

练习：

查找所有国家中所有国家中，英雄数量最多的前两名，显示国家名称和英雄的数量

select country,count(*) as number from sanguo

group by country

order by number DESC limit 2;

 

注意 

group by 之后的字段名必须要为select 之后的字段名，如果select后的字段名和group by之后的字段步一致，则必须对该字段进行结合处理（聚合函数）

 

having

作用	对查询的结果进一步筛选
示例	找出平均攻击力大于105的国家的前两名，显示国家名称和平均攻击力 Select country,avg(gongji) from sanguo group by country having avg (gongji)>105 order by avg(gongji) DESC limit 2;

 

注意

 1、having 语句通常和group by 语句联合使用，过滤由groub by 语句返回的记录集

2、where只能操作表中实际存在的字段，having操作有聚合函数生成的显示列

 

distinct

作用	不显示字段的重复值
示例	1、Sanguo表中有哪些国家 Select distinct country from sanguo; 2、计算魏国一共有多少个英雄 Select count(destinct name)from sanguo where country =”魏国”;

注意

1、 处理distinct 和from 之间的所有字段，所有字段值必须全部相同才可以去重

 

 

约束

作用	保证数据完整性、一致性、有效性的规则
分类	1、默认约束（default）	1、插入记录时，如果不给该字段赋值 则使用默认值
		2、字段名 数据类型 default 默认值
	2、非空约束（not null）	1、不允许该字段值有NULL记录
		2、字段名 数据类型 not null default 值

 

索引

优点	加快数据的检索速度
缺点	1、 当对表中的数据进行增加、修改、删除时 索引需要动态维护 降低了数据的维护速度
	2、索引需要占用物理存储空间