Kenway's Blog

【笔记】数据库笔记

发表于 更新于 分类于

目录

基本语法

进阶语法

基础部分

进阶部分

未解决问题

基本语法

1.使用数据库之前要先用USE选择数据库

1
2
USE carash;
# 表示使用名为carash的数据库

2.SHOW可以用来显示当前所有数据库、表、行的名字

1
2
3
SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM 表名;

3.检索

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT 属性名 FROM 表名;

# 会返回不重复的值
SELECT DISTINCT 属性名 FROM 表名;

# 返回值不能多于五行
SELECT 属性名 FROM 表名 LIMIT 5;

# 返回值按属性1升序排序，属性1相同的再按属性2升序排序
SELECT 属性1,属性2 FROM 表名 ORDER BY 属性1,属性2;

# 返回值不多于五行、按降序排序
SELECT 属性1,属性2 FROM 表名 ORDER BY 属性1,属性2 DESC LIMIT 5;

# 第二大的属性1，OFFSET 1 表示忽略掉第一个符合的记录
# LIMIT 1 OFFSET 1可以写成 LIMIT 1,1
# 注意LIMIT后面不能有运算，加减也不行
SELECT DISTINCT 属性1 FROM 表名 ORDER BY 属性1 DESC LIMIT 1 OFFSET 1

4.条件检索

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 返回属性2==xx的属性1，并按属性3升序排序
SELECT 属性1 FORM 表名 WHERE 属性2 = xx ORDER BY 属性3;

# 返回属性2在[XX,XX]之间的属性1，并按属性3升序排序
SELECT 属性1 FORM 表名 WHERE 属性2 BETWEEN xx AND XX ORDER BY 属性3;

# 返回属性2等于xx1或xx2的属性1
SELECT 属性1 FORM 表名 WHERE 属性2 IN (xx1,xx2);

# 返回属性2为空值的属性1
SELECT 属性1 FORM 表名 WHERE 属性2 IS NULL;

# 返回属性2不为空值的属性1
SELECT 属性1 FORM 表名 WHERE 属性2 IS NOT NULL;

# 返回属性2符合通配符表达式的属性1，%表示任意个任意字符，_表示一个任意字符
SELECT 属性1 FORM 表名 WHERE 属性2 LIKE '通配符表达式';

# 返回属性2符合正则表达式的属性1，.表示一个任意字符，[123]和[1-3]代表含义相同表示'1'或'2'或'3'其中一个，^、$表示串开始处的定位符结束符，若用'^...$'的形式则与LIKE效果等同
# LIKE要整个串符合才匹配成功，REGEXP只要整个串的部分子串符合计算匹配成功
SELECT 属性1 FORM 表名 WHERE 属性2 REGEXP '正则表达式';

# 表示返回一个chs前有一个长度为1到5的字符串的属性1
SELECT 属性1 FORM表名 WHERE 属性2 REGEXP '[1-5]chs'

5.汇总数据

1
2
3
# 将满足条件并经过函数计算的属性1赋值给新属性并返回，不加WHERE的话会计算所有属性1有值的行
# AVG(),COUNT(),MAX(),MIN(),SUM()
SELECT 函数(属性1) AS 新属性名 FROM 表名 WHERE 条件;

6.分组数据

1
2
3
# 会返回以属性1为分组依据，返回每个不同属性1出现的行数的表
# 在GROUP BY后面加HAVING 条件，会只返回符合条件的分组
SELECT COUNT(属性1) AS 新属性名 FORM 表名 GROUP BY 属性1

7.IFNULL的使用

1
2
# 表示SELECT DISTINCT ... OFFSET 1有符合要求记录时就返回符合记录，没有符合的就返回NULL
SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary

8.事务中变量的声明

1
2
3
4
5
6
7
8
9
10
11

# 在BEGIN下线DECLARE xx 数据类型， 再另起一行SET
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE m int;
    SET m = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT IFNULL((SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET m), NULL)
  );
END

9.四大排名关键字的使用

1
2
3
4
5
6
# 遇到相同的会写成1,1,3形式
SELECT -, rank() OVER(ORDER BY number ) AS rank FROM num
# 遇到相同的会写成1,1,2形式
SELECT -, dense_rank() OVER(ORDER BY number ) AS dense_rank FROM num
# 遇到相同的会写成1,2,3形式
SELECT -, row_number() OVER(ORDER BY number ) AS row_num FROM num

10.多表链接的写法

1
2
3
4
5
select distinct l1.Num as ConsecutiveNums 
from Logs l1, Logs l2, Logs l3 
where l1.Id = l2.Id+1 and l2.Id = l3.Id+1 and l1.Num = l2.Num and l1.Num = l3.Num;

# 以上语句用于找出在Logs表中Id连续的Num相同的最少出现三次的项

11.子查询的写法

1
2
3
4
5
6
7
8
9
10
11
12
select cust_name, cust_contact
from custiomers
where cust_id in (select cust_id from orders where order_num = 1001);

# 以上语句表示从customers表中返回cust_id等于在orders表中的order_num = 1001的cust_id的cust_name、cust_contact

select l2.Name as Department, l1.Name as Employee, l1.Salary  
from Employee l1, Department l2
where l1.DepartmentId = l2.Id and (l1.DepartmentId , l1.Salary) in 
(select DepartmentId, max(Salary) from Employee group by DepartmentId);

# 这种where后面不只一个属性的子查询方式，要像(l1.DepartmentId , l1.Salary)一样把两个属性用括号括起来

12.存储过程

1
2
3
4
create procedure-创建存储过程
exec procedure-执行存储过程
alter procedure-修改存储过程
drop procedure-删除存储过程

13.date类型数据的比较

1
2
# datediff(l1.recordDate, l2.recordDate) = 1表示l2的日期要比l1的日期少一天，diff等于l1-l2
select l1.id from Weather l1, Weather l2 where l1.Temperature > l2.Temperature and datediff(l1.recordDate, l2.recordDate) = 1;

14.关键字顺序

1
2
3
4
5
6
1.select
2.from
3.where
4.group by
5.order by
6.limit

15.构建临时表查询

1
2
3
4
5
6
7
select class 
from 
(select class, count(distinct student) as num from courses group by class) as temple 
where num >= 5; 

# 构建了一个有着class属性和num属性作为临时表temple
# num变量是按class相同的分一组，每一组分别计算学生数量

16.索引

  • 创建索引
    1
    2

    CREATE INDEX 索引名 ON 表名(username(length));
  • 删除索引
    1
    2

    DROP INDEX [索引名] ON 表名;

17.数据库建表字段时最后不要允许其为null，原因如下

  • 会占用额外空间。官方文档介绍NULL列在行中需要额外的空间以记录其值是否为NULL。对于MyISAM表，每个NULL列都多花一位，四舍五入到最接近的字节
  • 对允许为null的记录进行查询会出现以下错误：
    • sum(字段名)函数没统计到任何记录时，会返回 null 而不是 0
    • count(字段名)，不会统计为null的那条记录
    • MySQL 中使用诸如 =、<、> 这样的算数比较操作符比较 NULL 的结果总是False。需要使用IS NULL、IS NOT NULL或ISNULL()函数来比较

18.数据库字段的长度意思

  • varchar(5)只允许最多5个字符或者5个汉字(取决于数据库编码格式)。
  • int(1)、int(4)、int(11)和int(110)表示意思是一样的，tinyint()也同样。
  • INT(M) ZEROFILL，加上ZEROFILL后M才表现出不同，比如INT(3)ZEROFILL,你插入到数据库里的是10,则实际插入为010,也就是在前面补充加了一个0。
  • float(7,3):字段总允许宽度为7，其中整数部分为4位（数值范围可在0-9999），小数部分为3位（数值范围可在0-999）第一次设置此字段成功后，不可再次手动在navicat中此字段所在数据行的所有字段值也不可手动删除这条记录，但可通过sql修改。
  • double(7,3)：字段总允许宽度为7，其中整数部分为4位（数值范围可在0-9999），小数部分为3位（数值范围可在0-999）可手动在navicat中改此字段及同数据行其他字段的值，也可通过sql修改。

19.左连接、右连接、内连接

  • 左连接保存左表所有信息 from 左表 left join 右表 on 条件语句
  • 右连接保存右表所有信息 from 左表 right join 右表 on 条件语句
  • 内连接只保存左表和右表的共同信息 from 左表 inner join left 右表 on 条件语句

20.group by执行详解

  • 任一语句执行 group by name后都会生成下面这张虚拟表
  • 此时若前面时select name的话，不会出错；但是若是select -或者select id等就会出错，因为一个行里现在有多行数据
  • 解决办法是使用函数把可能有多条结果的值聚合成一条记录，类似select name, count(id)、select name, max(id)就不会报错

21.partition by是函数里使用的group by

  • 示例，意为以department为依据进行分组分别排序，排序的名次作为新属性num填进去
    1
    2
    SELECT NAME,Department,salary,
    dense_rank() over(PARTITION BY department ORDER BY salary DESC) num

22.更新

1
update 表名 set email = "southern.emperor@outlook.com" where name = "Southern Emperor";

23.锁

  • 增加写锁
    1
    LOCK TABLES 表名 WRITE;
  • 解除锁
    1
    UNLOCK TABLES;

24.对字段值为null的判断应该用以下

1
where bonus is null;

25.选取customer_id最多订单的记录用以下逻辑，先分组，再对分组order by

1
select - from Orders group by customer_id  order by count(order_id) DESC limit 1;

26.求连续的座位

  • 逻辑是l1的一个记录和l2两个记录连接起来
  • 然后筛选出这l1.free为1时，两个l2.free中任意一个为1的情况的l1
  • 注意因为采用了abs()会有重复的l1，所以还要用distinct去重
    1
    2
    select distinct l1.seat_id from Cinema l1 left join Cinema l2 on abs(l1.seat_id - l2.seat_id) = 1 
    where l1.free = 1 and l2.free = 1 order by l1.seat_id;

27.返回合法三角形(case when …then xx1 else xx2)

1
select x, y, z, case when x+y>z and x+z>y and y+z>x then 'YES' else 'NO' from Triangle

28.一张有字段a、b、c的表，group by多个值(a和b)，那么只有a相同并且b也相同的数据才会去聚合，例如此时聚合选的是sum(c)，那么就会展示满足a1=a2，b1=b2的数据的c的和

29.sql判断字符串变量的长度用LENGTH()

1
2
SELECT -
WHERE LENGTH(xx) = 2;

30.插入时into和overwrite的区别

  • insert into table xx会直接将数据追加在xx表后面
  • insert overwrite table xx会先清空xx表再插入数据

31.一张Hive表的写法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
WITH old_table AS (
    SELECT  stat_time_day,
            event_type,
            ad_id,
            advertiser_id,
            stat_time_hour,
            event_source_type,
            apple_app_skan_event_cnt,
            mmp_retargeting_attribute_event_cnt,
            mmp_repetitive_non_retargeting_attribute_event_cnt,
            mmp_repetitive_retargeting_attribute_event_cnt,
            mmp_total_event_cnt,
            mmp_non_retargeting_attribute_event_cnt,
            event_source_id,
            CASE WHEN event_source_type = 1 THEN CAST(event_source_id AS BIGINT)
                 ELSE NULL
            END AS tiktok_app_id
    FROM    ad_app.dwa_event_manager_measurement_metric_daily
    WHERE   p_date = '${date}'
    AND     p_date < '20220228'
)
INSERT OVERWRITE TABLE addrd_test.dwa_event_manager_measurement_metric_daily_v2_test PARTITION (p_date = '${date}')
SELECT  `stat_time_day`,
        `event_type`,
        `ad_id`,
        `advertiser_id`,
        `stat_time_hour`,
        `event_source_type`,
        SUM(COALESCE(`apple_app_skan_event_cnt`, 0)) AS apple_app_skan_event_cnt,
        SUM(COALESCE(`mmp_retargeting_attribute_event_cnt`, 0)) AS mmp_retargeting_attribute_event_cnt,
        SUM(COALESCE(`mmp_repetitive_non_retargeting_attribute_event_cnt`, 0)) AS mmp_repetitive_non_retargeting_attribute_event_cnt,
        SUM(COALESCE(`mmp_repetitive_retargeting_attribute_event_cnt`, 0)) AS mmp_repetitive_retargeting_attribute_event_cnt,
        SUM(COALESCE(`mmp_total_event_cnt`, 0)) AS mmp_total_event_cnt,
        SUM(COALESCE(`mmp_non_retargeting_attribute_event_cnt`, 0)) AS mmp_non_retargeting_attribute_event_cnt,
        `event_source_id`,
        SUM(COALESCE(`sdkapi_non_retargeting_attribute_event_cnt`, 0)) AS sdkapi_non_retargeting_attribute_event_cnt,
        SUM(COALESCE(`sdkapi_repetitive_non_retargeting_attribute_event_cnt`, 0)) AS sdkapi_repetitive_non_retargeting_attribute_event_cnt,
        SUM(COALESCE(`sdkapi_retargeting_attribute_event_cnt`, 0)) AS sdkapi_retargeting_attribute_event_cnt,
        SUM(COALESCE(`sdkapi_repetitive_retargeting_attribute_event_cnt`, 0)) AS sdkapi_repetitive_retargeting_attribute_event_cnt,
        SUM(COALESCE(`sdkapi_total_event_cnt`, 0)) AS sdkapi_total_event_cnt,
        SUM(COALESCE(`web_adv_browser_attribute_event_cnt`, 0)) AS web_adv_browser_attribute_event_cnt,
        SUM(COALESCE(`web_adv_server_attribute_event_cnt`, 0)) AS web_adv_server_attribute_event_cnt,
        SUM(COALESCE(`web_adv_duplicated_attribute_event_cnt`, 0)) AS web_adv_duplicated_attribute_event_cnt,
        SUM(COALESCE(`web_adv_browser_total_event_cnt`, 0)) AS web_adv_browser_total_event_cnt,
        SUM(COALESCE(`web_adv_server_total_event_cnt`, 0)) AS web_adv_server_total_event_cnt,
        SUM(COALESCE(`web_adv_duplicated_total_event_cnt`, 0)) AS web_adv_duplicated_total_event_cnt,
        event_source_id_split,
        UNIX_TIMESTAMP(stat_time_hour) AS last_record_time,
        SUM(COALESCE(offline_upload_event_cnt, 0)) AS offline_upload_event_cnt,
        SUM(COALESCE(offline_matched_event_cnt, 0)) AS offline_matched_event_cnt,
        SUM(COALESCE(offline_total_event_cnt, 0)) AS offline_total_event_cnt
FROM    ad_app.dwd_event_manager_measurement_metric_detail_etl_hourly_v2
WHERE   (p_date = '${date}' OR (p_date = '${date+1}' AND hour < '8'))
AND     stat_time_day = '${DATE} 00:00:00'
AND     p_date >= '20220228'
GROUP BY
        `stat_time_day`,
        `event_type`,
        `ad_id`,
        `advertiser_id`,
        `stat_time_hour`,
        `event_source_type`,
        `event_source_id`,
        `event_source_id_split`,
        `last_record_time`
UNION ALL
SELECT  stat_time_day,
        event_type,
        ad_id,
        advertiser_id,
        stat_time_hour,
        event_source_type,
        apple_app_skan_event_cnt,
        mmp_retargeting_attribute_event_cnt,
        mmp_repetitive_non_retargeting_attribute_event_cnt,
        mmp_repetitive_retargeting_attribute_event_cnt,
        mmp_total_event_cnt,
        mmp_non_retargeting_attribute_event_cnt,
        event_source_id,
        0 AS sdkapi_non_retargeting_attribute_event_cnt,
        0 AS sdkapi_repetitive_non_retargeting_attribute_event_cnt,
        0 AS sdkapi_retargeting_attribute_event_cnt,
        0 AS sdkapi_repetitive_retargeting_attribute_event_cnt,
        0 AS sdkapi_total_event_cnt,
        0 AS web_adv_browser_attribute_event_cnt,
        0 AS web_adv_server_attribute_event_cnt,
        0 AS web_adv_duplicated_attribute_event_cnt,
        0 AS web_adv_browser_total_event_cnt,
        0 AS web_adv_server_total_event_cnt,
        0 AS web_adv_duplicated_total_event_cnt,
        '' AS event_source_id_split,
        UNIX_TIMESTAMP(stat_time_hour) AS last_record_time,
        0 AS offline_upload_event_cnt,
        0 AS offline_matched_event_cnt,
        0 AS offline_total_event_cnt
FROM    old_table

进阶语法

1.创建视图

1
2
3
4
5
6
7
CREATE VIEW v_teachers AS
SELECT - FROM teachers 
WHERE age < 30
WITH CHECK OPTION;

# 意为从teachers表选取所有age>30的记录创建一个视图
# WITH CHECK OPTION表示视图可更新，即原表teachers新插入age<30的记录后，视图中也会相应更新

32.多次使用的子查询和复杂的逻辑都可以在select上面用with写

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
### 以下语句得到的是ad_id相等的

WITH third_party AS (
    SELECT  SUM(value1) AS form_third_party, ad_id
    FROM    ad_dm.ad_stats_hourly
    WHERE   date = '${date}'
    AND hour = '${hour}'
    group by ad_id
)
SELECT 
    t1.advertiser_id,
    t1.campaign_id,
    t2.form_third_party
FROM ad_dwd.dwd_overseas_overture_event_log_hourly AS t1
LEFT JOIN
third_party AS t2
ON t1.ad_id = t2.ad_id

基础部分

1.存储过程就是一套已经预编译过的SQL语句

2.索引时对数据库表中一个或多个列的值进行排序的结构，能加快查找数据而不用扫描整个库

  • 索引分为普通索引、唯一索引、全文索引(主键索引是一种特殊的唯一索引)
    • 唯一索引是该列的值均不相同，一般把主键设为唯一索引，它也成为了主键索引
  • 索引加快检索速度，但降低插入、删除、修改的速度
  • 唯一索引可以确保每一行数据的唯一性
  • 使用索引可以在查询过程中使用优化隐藏器，提高系统性能
  • 索引需要占物理和数据空间，所有索引并不一定都能提高查询性能

3.事务时并发控制的基本单位，它是一个操作序列，这些操作要么都执行要么都不执行(ACID)

  • 原子性：事务中的所有元素作为一个整体提交或回滚，不允许分割
  • 一致性：事务完成时，数据库完整性约束没有被破坏(例如A向B转账，不可能出现A扣了钱，B却没有收到的现象)
  • 隔离性：每个事务相互独立，不能以任何方式影响别的事务(即同一时间只允许一个事务请求同一数据)
  • 持久性：事务一旦提交，那么对数据库中数据的改变就是永久性的，即使系统故障也一直保留，真实地修改了数据库

4.并发控制时确保在多个事务同时存取同一数据时不破坏事务的隔离性和一致性以及数据库的一致性，常用的技术手段有

  • 悲观锁：假定一定会发生并发冲突，屏蔽一切可能违法数据完整性的操作，效率降低，可能出现死锁
  • 乐观锁：假定不会发生并发冲突，只在提交操作时检查是否违反数据完整性，不会产生死锁，但会出现两个事务读同一行再后写回的不可预测结果

5.悲观锁的实现(用排他锁实现)

  • set autocommit; 关闭MySQL的自动提交
  • 用select … for update的方式锁定某条数据，这样的话，其他事务必须等本次事务提交后才能改这条数据

6.乐观锁的实现(用版本号实现)

  • 每个数据初始化时指定一个版本号
  • 更新数据时对当前版本号+1并判断是否时最新的版本号

7.drop、delete和truncate区别

  • drop删除整张表，速度最快，ddl不能回滚，立即生效
  • delete只删除表内数据。不删除表结构，附加where可以指定删除，速度最慢，ddl可以回滚，事务提交后才生效
  • truncate只删除表所有数据，不删除表结构，ddl不能回滚，立即生效

8.主键、外键、超键和候选键

  • 主键：一般是用户规定的，数据库表中对储存数据对象予以唯一和完整标识，一个数据列只能有一个主键，且主键的取值不能重复或为空NULL（主键是一种特殊的索引）
  • 外键：在一个表中存在的另一个表的主键
  • 超键：包括候选键和主键，可以是一个属性也可以是多个属性的组合
  • 候选键：一个属性的超键

9.视图：是一种虚拟的表，具有和物理表相同的功能，通常是一个表或者多个表的行、列的子集，相比多表查询更方便，修改视图也会影响到基本表，可以看作时从不同表抽取一部分组成的新表

10.三大范式

  • 第一范式：一个列只能有一个值
  • 第二范式：一条记录的所有属性与主关键字是关联的，不能部分依赖(如果部分绑定的话，要把不绑定的拿出来新起一张表)
  • 第三范式：一个数据库表中不包含其他表中的关键字(主键除外)（即消除传递依赖）
  • 不满足三范式会出现数据冗余（需要更新多行数据才能修改一个属性）、插入删除更新异常，比如一个属性在两张表都存在，要改的话要去两张表同时改，如果漏了的话，会出现数据不对版的情况

11.触发器是一个特殊的存储过程(一堆sql代码语句)，通过事件触发才被执行

12.数据库事务隔离级别

  • 读未提交：可以读到未提交的内容，查询不会加锁，基本不用(脏读、不可重复读、幻读)
  • 读提交：一个事务提交后，其他事务才能读到该事务更新的结果，查询不会加锁，用到快照机制，又称快照读(不可重复读、幻读)
  • 可重复读：在一个事务中，对同一数据的读取结果时相同的，也是mysql的默认级别(幻读)
  • 串行化：事务排队执行，效率低开销大基本不用

13.三种错误读

  • 脏读：事务B读取到了未提交的正在执行的事务A中的数据
  • 不可重复读：事务B两次读取间隔中，有一个事务A对同一数据就行了修改(针对同一数据项)
  • 幻读：事务B两次读取的间隔中，有一个事务A对同一数据就行了插入、删除(针对数据个数)
    • RR隔离事务启动后第一行select会进行一个快照，然后进行快照读
    • 然后select ..for update是当前读，当前读会出现幻读，可用间隙锁来解决，间隙锁是针对插入、删除数据的锁

14.锁的分类

  • 按功能分：共享锁(锁住时只能读)、排他锁(锁住时不能读也不能写)
  • 按范围分：表锁(sql语句没用到索引时mysql会自动用表锁)、行锁(基于索引，mysql默认行级锁)

15.设置索引了却没用上的情形

  • where子句中用!=、<>、函数操作、表达式操作和or，会跳过索引区扫描全表
    • 只有where =的时候才会去调索引，而索引内部有排序，所以可以高效地找到这个值
  • 对与多列索引，不是使用的第一部分则不会使用索引(最左匹配原则)
  • 列类型是字符串时，一定要在条件中将数据使用’’括起来，不然就不会使用索引
  • 通配符查找LIKE以%开头时，不会使用索引

16.SQL Server支持

  • 数据库备份
  • 事务日志备份
  • 差异备份(增量备份)
  • 文件和文件组备份

17.数据库系统指的是数据库和数据库管理系统

18.数据库模型分为

  • 概念模型：E-R图
  • 逻辑模型：关系模型(二维表)、非关系模型
  • 物理模型：数据物理存放/取策略

19.码就是键，例如主码、超码、候选码

20.数据的物理独立性：存储结构改变。模式和应用程序不变，模式-内模式映像改变

数据的逻辑独立性：模式改变，外模式和应用程序不变，外模式-模式映像改变

21.静态表和动态表

  • 静态表字段长度固定，自动填充，读写速度很快，便于缓存和修复，但比较占硬盘
  • 动态表是字段长度不固定，节省硬盘，但更复杂，容易产生碎片，速度慢，出问题后不容易重建

22.联合索引(又叫复合索引)

  • 两个或更多个列上的索引被称作复合索引
  • Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部分，但只能是最左侧部分。例如索引是key index （a,b,c），可以支持a|a,b|a,b,c三种组合进行查找，但不支持b,c进行查找 。当最左侧字段是常量引用时，索引就十分有效

23.索引失效情况

  • 在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引范围条件右边的列
  • 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select -
  • mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
  • is null,is not null也无法使用索引
  • like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作

24.主键索引和非主键索引区别

  • 主键索引的叶子节点存放的是整行数据，非主键索引的叶子节点存放的是主键的值
  • 如果查询语句是 select - from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。
  • 如果查询语句是 select - from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。

25.索引的实现方式可以是哈希表、b树和b+树

  • 为什么用 B+ 树做索引而不用哈希表做索引?
    • 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据
    • 哈希表不支持范围查找，例如查找ID为100 ~ 400的人，只能遍历全表。
    • 索引字段通过哈希映射成哈希码，会有哈希冲突问题，一般采用链地址解决，数据大的话查找的效率会趋近于线性遍历

26.顺序读写和随机读写

  • 顺序就是不在意位置，直接在当前位置读写
    • 举例：遍历、顺序数组等
  • 随机就是去找到对的位置，再去读写(易产生磁盘碎片问题)
    • 举例：链表、索引查询
  • 数据库为了提高性能就需要尽可能的顺序读写
    • 例如：HIVE用insert全复写一遍，也不允许用update(因为每次遍历只能更新一条记录，想更新n条数据就要遍历n遍)
  • 消息队列的使用就是为了给顺序读写提供方便

进阶部分

1.各数据类型占空间

  • char(10)无论存的数据够不够10字节都会用10字节空间，用空格填充
  • varchar则只保存存在的字符数，但额外有一个长度标记，因此处理时多一次运算
  • int(11)和int(3)都只占用4各字节空间

2.left join返回包括左表中的所有记录和右表中联结字段相等的记录(right join同理)，inner jon只返回两表中联结字段相等的行

3.Mysql引擎

  • MyIASM引擎是Mysl的默认引擎，不支持数据库事务、行级锁和外键，因此写操作需要锁定整张表，效率较低，适用于读多于写且不需要使用事务的情形
  • Innodb引擎支持事务、行级锁和外键，提供ACID的支持，并且实现了四种隔离级别，适用于大型数据库

4.数据库系统的数据独立性：不会因为数据存储结果与逻辑结构的变化而影响应用程序

5.关系模型中数据的逻辑结构是一张二维表

6.将E-R图转换为关系数据模型属于逻辑设计阶段

7.在高并发的线上事务中，无法避免等待和死锁的产生

8.单个用户使用的数据视图描述称为外模式

9.SQL语言分为交互式SQL和嵌入式SQL

10.Oracle

  • 最大保护模式
  • 最高可用性模式
  • 最大性能模式

11.MAX()不限制数据类型

12.数据库按使用的数据模型分为

  • 层级
  • 关系
  • 网状

13.数据模型质量的高低直接影响数据库性能

14.transact-SQL函数有：DAY()、MONTH()、YEAR()

15.数据库一般默认用utf-8编码，这种编码一个字符最多存3个字节，而emoji表情是4个字节所有会识别成？

  • 解决办法：改成utf-8mb4编码，作为utf-8的超集，该编码一个字符最多能有4字节

未解决问题

1.这个语句用来删除Id大的重复邮箱为什么提示You can’t specify target table ‘Person’ for update in FROM clause

1
delete from Person where Id not in (select min(Id) as Id from Person group by Email);

原因是：不能在一个语句中既修改、删除某表后又select该表

解决办法：用自连接

1
delete l1 from Person l1, Person l2 where l1.Email = l2.Email and l1.Id > l2.Id;