2021-11-09

数据库学习day01

查询优先级(查询时,语句的执行顺序)

1. WHERE
2. GROUP BY
3. SELECT 聚合函数 FROM 表名
4. HAVING
5. ORDER BY
6. LIMIT

数据库和数据表建立:

create database 数据库名  # 在此用data1为例
# 创建数据表 以sango表为例
create table sango(
	id int primary key auto_increment,
    name varchar(20),
    attack int,
    defense int,
    gender varchar(20),
    country varchar(20)
)charset=utf8;

对数据库插入数据：

# 以插入的数据如下为例
insert into sango(name,attack,defense,gender,country) value
(曹操,150,150,男,魏国)
(夏侯惇,200,150,男,魏国)
(典韦,300,150,男,魏国)
(刘备,150,150,男,蜀国)
(刘禅,50,50,男,蜀国)
(关羽,360,360,男,蜀国)
(孙权,150,150,男,吴国)
(孙尚香,100,60,女,吴国)
(貂蝉,50,50,女,吴国);

对数据进行查询、更新：

-- 查询蜀国的人 --
SELECT * FROM sango WHERE country='蜀国';
-- 更新关羽的设置  --                          -
UPDATE sango SET attack=360 ,defense=360 WHERE name='关羽';
-- 将吴国攻击值未60改未攻击值为50防御值为50
UPDATE sango SET attack=50 ,defense=50 WHERE country='吴国' AND attack='60';
-- 找出攻击力高于200的蜀国英雄的名字和攻击力
SELECT attack,name FROM sango WHERE country='蜀国' AND attack>200;
-- 将蜀国英雄按攻击力从高到低排序
SELECT  * FROM sango WHERE country='蜀国' ORDER BY attack DESC;
-- 将魏吴两国英雄名为3个字的按防御值升序排列
SELECT * FROM sango WHERE country in('魏国','吴国')  AND name LIKE '___' ORDER BY defense;

数据库的聚合函数和示例：

-- 聚合函数  平均值/最大/最小/总和/个数总和(id为好) AVG([字段名) MAX([字段名) MIN([字段名) SUM([字段名) COUNT([字段名)
--  找出表中最大攻击力的值
SELECT MAX(attack) FROM sango;
-- 找出表中有多少个英雄
SELECT count(id) FROM sango;
--  蜀国英雄中攻击值大于200的个数
SELECT count(id) FROM sango WHERE attack>200 AND country='蜀国';
-- GROUP BY 查询的结果分组 查询和group by 一致(不一致看能不能聚合,不能则报错)
-- 计算每个国家的平均攻击力 
SELECT country,AVG(attack) FROM sango GROUP BY country; 
-- 所有国家的男英雄中 英雄的数量最多的前两名的 国家名称及英雄数量
SELECT country,COUNT(id) FROM sango WHERE gender = '男' 
GROUP BY country 
ORDER BY COUNT(id) DESC 
LIMIT 2;
-- HAVING 语句 对分组聚合后的结果进行进一步筛选
-- HAVING 使用就是将 WHERE中的条件替换掉(WHERE是聚合筛选前的数据)
-- 找出平均攻击力大于105的国家的前两名,显示国家名称和平均攻击力
SELECT country,AVG(attack) FROM sango  
GROUP BY country 	
HAVING AVG(attack)>105 
ORDER BY AVG(attack) DESC
LIMIT 2;
-- distinct语句 不显示字段重复值
-- 表中都有哪些国家
SELECT DISTINCT country FROM sango;
-- 计算一共有多少个国家
SELECT COUNT(DISTINCT country) FROM sango;
-- 查询攻击力小于平均攻击力的英雄名字和攻击值
SELECT `name`,attack FROM sango WHERE attack<(SELECT AVG(attack) FROM sango);
-- 查询每个国家的最大攻击值的名字和攻击值
SELECT `name`,attack FROM sango WHERE (country,attack) in 
(SELECT country,MAX(attack) FROM sango GROUP BY country);

B树和B+树的特点：

B树的特点：全部节点包含索引和数据，范围查询都是从根节点遍历到指定数据
B+树特点：1 非叶子节点只保存索引【树宽度优于B树，从而降低了磁盘IO】
2 叶子节点保存所有的索引和数据
3 叶子节点之间相互连接，形成链表结构
4 范围查询 无需在从根节点开始

数据量庞大时提高查询的速率：

创建普通索引或者唯一索引:

-- 此处省略了创建数据库的操作，可继续用前面创建的数据库也可创建新的数据库看个人喜好
-- 创建索引有两种方式
-- 第一种：最开始创建的数据表
create table students_name(
	id int primary key auto_increment,
    name varchar(20),
    -- 创建普通索引 index(字段名)
    INDEX name
    -- 唯一索引 
    -- unique(字段名)
    
)charset=utf8;
-- 我们为数据表添加数据 用pymysql添加

import pymysql
# 数据库的连接
conn = pymysql.connect(host='localhost', user='root', password='123456', database='data1', port=3306,
charset='utf8')
# 游标的创建
couser = conn.cursor()

# 数据的插入
sql = 'insert into students_name(name) value ("%s")'
data_list = []
for i in range(2000000):
    name = 'Tom_%s'%(i)
    data_list.append(name)
try:
    couser.executemany(sql,data_list)
    conn.commit()
    print("插入成功")
except Exception as e:
	# 关闭数据库
    print("插入失败")
    conn.rollback()
    couser.close()
    conn.close()
-- 第二种创建方式是在表已经创建完成，并没有对相应字段名创建索引
create index name on students_name(name);
create unique name on students_name(name);
-- 数据表中有了数据
可通过查询时间来检测是否有效 
-- 查看执行时间
show profiles

第一天数据库的学习到此结束，第二天将讲述数据库对表的操作，敬请期待下次分享，谢谢！！