Python-Level2-day06:时间类型字段用法；高级查询:模糊查询like/重命名as/排序order/限制limit/联查union/子查询,聚合函数/分组/筛选/去重；索引操作之创建索引

3.6.7 时间类型数据

• 日期 ： DATE

• 日期时间： DATETIME，TIMESTAMP

• 时间： TIME

• 年份 ：YEAR

• 时间格式

  date ："YYYY-MM-DD"
  time ："HH:MM:SS"
  datetime ："YYYY-MM-DD HH:MM:SS"
  timestamp ："YYYY-MM-DD HH:MM:SS"

e.g.
create table marathon (
    id int primary key auto_increment,
    athlete varchar(32),
    birthday date,
    registration_time datetime,
    performance time);
​
insert into marathon values
(1,"尼古拉斯赵四","1998-02-20","2021-1-16 10:18:29","2:47:59"),
(2,"托尼帕克","1995/5/18","2021-1-22 19:37:1","2:29:19"),
(3,"曹操","2000/1/7","2021-2-1 20:17:1","2:18:55");

• 日期时间函数

  • now() 返回服务器当前日期时间,格式对应datetime类型

  alter table marathon modify registration_time datetime default now();用作默认时间
  --再插入数据不设置registration_time的话就默认当前时间。
  select now(); 打印当前时间
  selec * from marathon where registration_time < now(); 用作条件筛选

• 时间操作

  时间类型数据可以进行比较和排序等操作，在写时间字符串时尽量按照标准格式书写。

   select * from marathon where birthday>='2000-01-01';
   select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

 
练习 使用book表
--1. 将呐喊的价格修改为45元
update book set price=45 where bname="呐喊";
​
--2. 增加一个字段出版时间 类型为 date 放在价格后面
alter table book add publish_time date after price;
​
--3. 修改所有老舍的作品出版时间为 2018-10-1
update book set publish_time="2018-10-1"
where author="老舍";
​
--4. 修改所有中国文学出版社出版的但是不是老舍的作品出版时间为 2020-1-1
update book set publish_time="2020-1-1"
where press="中国文学出版社" and author != "老舍";
​
--5. 修改所有出版时间为Null的图书 出版时间为 2019-10-1
update book set publish_time="2019-10-1"
where publish_time is null;
​
--6. 所有鲁迅的图书价格增加5元
update book set price=price+5 where author="鲁迅";
​
--7. 删除所有价格超过70元或者不到40元的图书
delete from book where price not between 40 and 70;

3.7 高级查询语句

• 模糊查询

  LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号%来表示任意0个或多个字符，下划线_表示任意一个字符。

  SELECT field1, field2,...fieldN 
  FROM table_name
  WHERE field1 LIKE condition1

  e.g. 
  mysql> select * from class where name like 'A%';---查询名字首字母是A开头的人
  mysql> select * from class where name like '%a%';---查询名字包含a的人
  mysql> select * from class where name like '___';---查询名字包含三个字母的人
  select * from class where hobby like '%sing%';在集合类型的字段hobby中查询有sing的

• as 用法

  在sql语句中as用于给字段或者表重命名，并且只在当前语句起作用。

  select name as 姓名,age as 年龄 from class;
  select * from class as cls where cls.age > 17;

• 排序

  ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果，排序可以是时间，数字或英文。使用 ORDER BY 子句将查询数据排序后再返回数据：

  SELECT field1, field2,...fieldN from table_name1 where field1
  ORDER BY field1 [ASC [DESC]]

  默认情况ASC表示升序，DESC表示降序

  select * from class where sex='m' order by age desc;   #先筛选出性别男的然后按age排序

  复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序

  select * from class order by score desc,age;分数降序排，相同就按照年龄升序排

• 限制

  LIMIT 子句用于限制由 SELECT 语句返回的数据数量 ，或者 UPDATE,DELETE修改语句的操作数量带有 LIMIT 子句也可以用。其中应用于 SELECT 查询语句的基本语法如下：

  SELECT column1, column2, columnN 
  FROM table_name
  WHERE field
  LIMIT [num] [OFFSET num]---跳过几个

  select * from class limit 2 #只查询班级表的前2人
  ​
  select * from class where sex='w' order by score desc limit 1 #只查询班级最高女生
  ​
  select * from cls where sex='m' order by score desc limit 1 offset 2;查询班级男生第三名

• 联合查询

  UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据（加上all参数就会显示两次了）。UNION 操作符语法格式：

  SELECT expression1, expression2, ... expression_n
  FROM tables
  [WHERE conditions]
  ​
  UNION [ALL | DISTINCT]
  ​
  SELECT expression1, expression2, ... expression_n
  FROM tables
  [WHERE conditions];

  默认UNION后 DISTINCT表示删除结果集中重复的数据。如果使用ALL则返回所有结果集， 包含重复数据。

  select * from class2 where sex='m' 
  UNION ALL 
  select * from class2 where age > 9;
  ​
  #字段数量必须相同，字段内容可以不同，也支持查询不同数据表进行联合显示
  select name from class2 where sex='m' 
  UNION ALL 
  select age from class2 where age > 9;

• 子查询

  • 定义 ： 当一个语句中包含另一个select 查询语句，则称之为有子查询的语句

  • 子查询使用位置：

    1. from 之后 ，此时子查询的内容作为一个新表的数据提供，再进行外层select查询

    #如果在多张表的查询数据用点操作就指明哪个表的哪个字段，一张表用不用一样。
    select name from (select * from class where sex='m') as s where s.score > 90;
    ​
    select name from (select * from class where sex='m') as s where score > 90;

    注意： 语要求需要将子查询结果集重命名一下，方便where子句中的引用操作

    1. where子句中，此时select查询到的内容作为外层查询的条件值

        #查询年龄与Tom一样大的班级里面人员，因此子查询需要先查询Tom年龄
        select *  from class where age = (select age from class where name='Tom');
        #查询班级里面报了兴趣爱好的同学
        select * from class where name in (select name from hobby);

    注意：

    1. 子句结果作为一个值使用时，返回的结果需要一个明确值，不能是多行或者多列。

    2. 如果子句结果作为一个集合使用，即where子句中是in操作，则结果可以是一个字段的多个记录。

• 查询过程

通过之前的学习看到，一个完整的select语句内容是很丰富的。下面看一下select的执行过程：

(5)SELECT DISTINCT <select_list>                     
​
(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>
​
(2)WHERE <where_predicate>
​
(3)GROUP BY <group_by_specification>
​
(4)HAVING <having_predicate>
​
(6)ORDER BY <order_by_list>
​
(7)LIMIT <limit_number>

​
高级查询练习
​
在stu下创建数据报表 sanguo
​
字段：id  name  gender  country  attack  defense
​
create table sanguo(
id int primary key auto_increment,
name varchar(30),
gender enum('男','女'),
country enum('魏','蜀','吴'),
attack smallint,
defense tinyint
);
​
​
insert into sanguo
values (1, '曹操', '男', '魏', 256, 63),
       (2, '张辽', '男', '魏', 328, 69),
       (3, '甄姬', '女', '魏', 168, 34),
       (4, '夏侯渊', '男', '魏', 366, 83),
       (5, '刘备', '男', '蜀', 220, 59),
       (6, '诸葛亮', '男', '蜀', 170, 54),
       (7, '赵云', '男', '蜀', 377, 66),
       (8, '张飞', '男', '蜀', 370, 80),
       (9, '孙尚香', '女', '蜀', 249, 62),
       (10, '大乔', '女', '吴', 190, 44),
       (11, '小乔', '女', '吴', 188, 39),
       (12, '周瑜', '男', '吴', 303, 60),
       (13, '吕蒙', '男', '吴', 330, 71);
​
查找练习
1. 查找所有蜀国人信息，按照攻击力排名
2. 将赵云攻击力设置为360，防御设置为70
3. 吴国英雄攻击力超过300的改为300，最多改2个
4. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
5. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序
6. 查找名字为3字的
7. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄
8. 找到魏国防御力排名2-3名的英雄
9. 查找所有女性角色中攻击力大于180的和男性中攻击力小于250的
​
--1. 查找所有蜀国人信息，按照攻击力排名
select * from sanguo where country="蜀"
order by attack desc;
​
--2. 将赵云攻击力设置为360，防御设置为70
update sanguo set attack=360,defense=70
where name="赵云";
​
--3. 吴国英雄攻击力超过300的改为300，最多改2个
update sanguo set attack=300
where country = "吴" and attack > 300
limit 2;
​
--4. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
select name as 姓名,attack as 攻击力
from sanguo where country="魏" and attack>200;
​
--5. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序
select * from sanguo order by attack desc,defense;
​
--6. 查找名字为3字的
select * from sanguo where name like "___";
​
--7. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄
select * from sanguo
where country="蜀" and attack>(
select attack from sanguo where country="魏"
order by attack desc limit 1);
​
--8. 找到魏国防御力排名2-3名的英雄
select * from sanguo where country= "魏"
order by defense desc limit 2 offset 1;
​
--9. 查找所有女性角色中攻击力大于180的和
--男性中攻击力小于250的
select * from sanguo where gender="女" and attack>180
union
select * from sanguo where gender="男" and attack < 250;
​
​

3.8 聚合操作

聚合操作指的是在数据查找基础上对数据的进一步整理筛选行为，实际上聚合操作也属于数据的查询筛选范围。

3.8.1 聚合函数

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数(不统计NULL)

eg1 : 找出表中的最大攻击力的值？

select max(attack) avg(attack) from sanguo;

eg2 : 表中共有多少个英雄？

select count(name) as 数量 from sanguo;---可以给查询的字段as取名称

eg3 : 蜀国英雄中攻击值大于200的英雄的数量

select count(*) from sanguo where attack > 200; ---*代表所有记录即一共多少条记录

注意： 此时select 后只能写用上聚合函数字段，无法写其他字段，除非该字段值全都一样。

例如：select max(attack) country from sanguo where country="魏";

3.8.2 聚合分组

• group by 给查询的结果进行分组

e.g. : 计算每个国家的平均攻击力

select country,avg(attack) from sanguo group by country;理解为对国家分组后再对每组的攻击力进行聚合操作

e.g. : 对多个字段创建分组，此时多个字段都相同时为一组

select age,sex,count(*) from class1 group by age,sex;

e.g. :哪个国家男英雄的平均攻击力是排名第一的

​
select country,avg(attack) as 平均攻击力
from sanguo
where gender = "男"
group by country
order by 平均攻击力 desc
limit 1;

e.g. : 所有国家的男英雄中 英雄数量最多的前2名的 国家名称及英雄数量

select country,count(id) as number from sanguo 
where gender='M' group by country
order by number DESC
limit 2;

注意： 使用分组时select 后的字段必须为group by分组的字段，不能包含其他内容，但可以包含聚合函数的其他字段。group by也可以同时依照多个字段分组，如group by A，B ，此时必须A,B两个字段值都相同才算一组。

 ​​​​​​​

 

国家与性别同时分组得到6组，例如每组内的成员必须要满足国家与性别相同才能到一个组内。用聚合操作求出每个组成员的平均攻击力。

3.8.3 聚合筛选

• having语句 对分组聚合后的每一组分别进行进一步筛选

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
​
select country,avg(attack) from sanguo 
group by country
having avg(attack)>105
order by avg(attack) DESC
limit 2;

注意

1. having语句必须与group by联合使用。

2. having语句存在弥补了where关键字不能对聚合函数结果筛选的的不足,因为where执行在聚合之前，having在聚合之后。where只能操作表中实际存在的字段，不能对聚合的结果进行赛选

3.8.4 去重语句

• distinct语句 不显示字段重复值

eg1 : 表中都有哪些国家
  select distinct country from sanguo;
eg2 : 计算一共有多少个国家
  select count(distinct country) from sanguo;

注意: distinct和from之间所有字段都相同才会去重

例如select distinct ,country gender from sanguo;--国家与性别都相同的成员只保留一个

聚合练习
​
--1. 统计每位作家出版图书的平均价格
select author,avg(price) from book group by author;
​
--2. 统计每个出版社出版图书数量
select press,count(*) from book group by press;
​
--3. 查看总共有多少个出版社
select count(distinct press) from book;
​
--4. 筛选出那些出版过超过50元图书的出版社，并按照其出版图书的平均价格降序排序
select press,avg(price) from book
group by press
having max(price) > 50
order by avg(price) desc;
​
--5. 统计同一时间出版图书的最高价格和最低价格
select publish_time,max(price),min(price)
from book
group by publish_time;

3.9 索引操作

3.9.1 概述

• 定义 :索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

  如果姓名字段经常用于查询，把姓名字段提取出来，单独另存储一个内部结构表格，即索引表。特点是查询快。索引表查询到结果去到原表找对应结果，然后返回给查询语句使用者。

• 优缺点

  • 优点 ： 加快数据检索速度,提高查找效率

  • 缺点 ：占用数据库物理存储空间，当对原数据表中数据进行修改时,索引需要动态维护,降低数据写入效率

注意 ：

1. 通常我们只在经常进行查询操作的字段上创建索引

2. 对于数据量很少的表或者经常进行写操作而不是查询操作的表不适合创建索引

3.9.2 索引分类

• 普通(MUL)

普通索引 ：字段值无约束,KEY标志为 MUL

• 唯一索引(UNI)

唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI

• 主键索引（PRI）

一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

3.9.3 索引创建

• 创建表时直接创建索引

create table 表名(
字段名 数据类型，
字段名 数据类型，
​
index 索引名1(字段名),---创建普通索引
index 索引名2(字段名),
unique 索引名3(字段名) ---创建唯一索引
);
​
​
eg:
create table index_test (
id int auto_increment,
name varchar(30),
score float,
​
index scoreindex(score),---创建普通索引
primary key(id),---创建主键索引
unique nameIndex(name)---创建唯一索引
);

• 在已有表中创建索引：

create [unique] index 索引名 on 表名(字段名);----unique表示可选为唯一索引
e.g.
create unique index name_index on class(name);

• 主键索引添加

alter table 表名 add primary key(id);---基本很少在创建表后再添加主键索引

• 查看索引

1、desc 表名;  --> KEY标志为：MUL 、UNI。
2、show index from 表名;

• 删除索引

drop index 索引名 on 表名;
alter table 表名 drop primary key;  # 删除主键

• 扩展： 借助性能查看选项去查看索引性能

set  profiling = 1； 打开功能 （项目上线一般不打开）
​
show profiles  查看语句执行信息

---

前情回顾
​
1. 什么是数据库
​
   数据库
   数据库管理系统
   关系型数据库 & 非关系型
   关系型数据库 ： 表   字段  记录
   mysql ： 开源  关系型   中型   c/c++
   mysql 安装
   mysql 服务启动
   连接数据库 ：  mysql -u root -p
   SQL : 关系型数据库数据操作指令
​
2. 数据库操作
​
   create database [db];
   show databases;
   use [db];
   drop database [db];
   select database();
​
3. 数据表操作
​
   数据类型 ： 数字  tinyint  int  float  bit
             字符串 char  varchar  enum()
​
   约束 ： primary key  auto_increment
          not null
          default
          unsigned
          comment
​
   create table 表名 (字段 类型 约束,....);
   show tables;
   desc 表名;
   show create table 表名;  ``
   drop table [tb];
​
4. 数据操作
​
 写 insert into [tb] (col,..) values (val1...);
    update [tb] set col=val,... where ...
    delete from [tb] where ......
​
 读 select col,.. from [tb] where ...
​
作业： 1. 前两天基础语句练习
      2. 练习再做一下
​

--练习 使用book表
--1. 将呐喊的价格修改为45元
update book set price=45 where bname="呐喊";
​
--2. 增加一个字段出版时间 类型为 date 放在价格后面
alter table book add publish_time date after price;
​
--3. 修改所有老舍的作品出版时间为 2018-10-1
update book1 set publish_time="2018-10-1"
where author="老舍";
​
--4. 修改所有中国文学出版社出版的但是不是老舍的作品出版时间为 2020-1-1
update book1 set publish_time="2020-1-1"
where press="中国文学出版社" and author != "老舍";
​
--5. 修改所有出版时间为Null的图书 出版时间为 2019-10-1
update book1 set publish_time="2019-10-1"
where publish_time is null;
​
--6. 所有鲁迅的图书价格增加5元
update book1 set price=price+5 where author="鲁迅";
​
--7. 删除所有价格超过70元或者不到40元的图书
delete from book where price not between 40 and 70;
​
--查找练习
--1. 查找所有蜀国人信息，按照攻击力排名
select * from sanguo where country="蜀"
order by attack desc;
​
--2. 将赵云攻击力设置为360，防御设置为70
update sanguo set attack=360,defense=70
where name="赵云";
​
--3. 吴国英雄攻击力超过300的改为300，最多改2个
update sanguo set attack=300
where country = "吴" and attack > 300
limit 2;
​
--4. 查找攻击力超过200的魏国英雄名字和攻击力并显示为姓名， 攻击力
select name as 姓名,attack as 攻击力
from sanguo where country="魏" and attack>200;
​
--5. 所有英雄按照攻击力降序排序，如果相同则按照防御生序排序
select * from sanguo order by attack desc,defense;
​
--6. 查找名字为3字的
select * from sanguo where name like "___";
​
--7. 查找攻击力比魏国最高攻击力的人还要高的蜀国英雄
select * from sanguo
where country="蜀" and attack>(
select attack from sanguo where country="魏"
order by attack desc limit 1);
​
--8. 找到魏国防御力排名2-3名的英雄
select * from sanguo where country= "魏"
order by defense desc limit 2 offset 1;
​
--9. 查找所有女性角色中攻击力大于180的和
--男性中攻击力小于250的
select * from sanguo where gender="女" and attack>180
union
select * from sanguo where gender="男" and attack < 250;
​
​
--聚合练习
​
--1. 统计每位作家出版图书的平均价格
select author,avg(price) from book
group by author;
​
--2. 统计每个出版社出版图书数量
select press,count(*) from book
group by press;
​
--3. 查看总共有多少个出版社
select count(distinct press) from book;
​
--4. 筛选出那些出版过超过50元图书的出版社，
--并按照其出版图书的平均价格降序排序
​
select press,avg(price) from book
group by press
having max(price) > 50
order by avg(price) desc;
​
​
--5. 统计同一时间出版图书的最高价格和最低价格
select publish_time,max(price),min(price)
from book
group by publish_time;
​
​

"""
use books;
create table index_test(
 id int primary key auto_increment,
 name varchar(30)
 );
本程序插入两百万条数据进入数据库，用于验证带索引字段的查询会不会
比没有带索引字段的查询更快
"""
​
import pymysql
​
db = pymysql.connect(user='root', password="123456",
                     database='books1', charset='utf8')
​
cur = db.cursor()
​
sql = "insert into index_test (name) values (%s);"
exe = []
s = "Tom"
for i in range(2000000):
    name = s + str(i)
    exe.append(name)
​
try:
    cur.executemany(sql, exe)
    db.commit()
except:
    db.rollback()
​
db.close()
​

插入时间类型数据
insert into marathon values
(1,"Jack","1998-02-20","2021-1-16 10:18:29","2:47:59"),
(2,"Dva","1995/5/18","2021-1-22 19:37:1","2:29:19"),
(3,"Mali","2000/1/7","2021-2-1 20:17:1","2:18:55");
​
--设置默认时间为now()
alter table marathon
modify registration_time datetime
default now();
​
insert into marathon
(athlete,birthday,performance)
values
("Duke","1994-10-20","2:55:15");
​
--高级查询语句
​
--模糊查询
select * from class where name like "L%";
select * from class where name like "___";
select * from hobby where hobby like "%sing%";
​
​
--limit
update class set score=76
where sex='w' limit 1;
​
select * from class where sex='w'
order by score desc
limit 1;
​
--union  字段数量一致即可
 select name,age,score from class
 where sex='m'
 union all
 select name,hobby,price from hobby;
​
​
--聚合操作 （如果有普通字段则该字段值必须全都一样）
select country,avg(attack),max(attack)
from sanguo
where country="魏";
​
--哪个国家男英雄的平均攻击力是排名第一的
select country,avg(attack) as 平均攻击力
from sanguo
where gender = "男"
group by country
order by 平均攻击力 desc
limit 1;
​
select country,gender,avg(attack)
from sanguo
group by country,gender;
​
--索引
create table index_test (
id int auto_increment,
name varchar(30),
primary key(id),
unique nameIndex(name)
);
​
​