数据库知识总结

发表于 | 分类于

视图的优点

1. 提高重用性
2. 对数据库进行重构
3. 提高安全性能

1
2
3
4
CREATE VIEW <视图名> as select <想要获得的数据库属性> from <数据库表名>;
例:
CREATE VIEW a as SELECT id,name,address FROM t_message;
SELECT * from a;

mysql的索引

1
2
3
4
5
<!--创建表时添加索引-->
CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
      [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
      [索引名](字段名1 [(长度)] [ASC | DESC])
);
  • UNIQUE：可选。表示索引为唯一性索引。
  • FULLTEXT；可选。表示索引为全文索引。
  • SPATIAL：可选。表示索引为空间索引。
  • INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
  • 索引名：可选。给创建的索引取一个新名称。
  • 字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
  • 长度：可选。指索引的长度，必须是字符串类型才可以使用。
  • ASC：可选。表示升序排列。
  • DESC：可选。表示降序排列。

BTree和Hash索引的区别

  • BTree索引

    innodb默认的索引方法，可以使用在大多数情况下，在like操作符中，不过要注意的是不能以通配符开头的常量，如select * from user where name like ‘%jac%k%’;

  • Hash索引

    优点：数据量少时比BTree性能高的多
    缺点：用的较少，局限性多，不能用于范围查询，每次都要全表扫描，不能用部分索引键来搜索，数据量大时没有BTree效率高

组合索引和单值索引

组合索引就是将多个属性组合为一个索引，组合索引遵循从左往右的规定，如果最左索引不成立则无法找到第二个索引位置，只有一个一个都找到才能组合成功，还有varchar类型最好规定一个合适的范围，过大会影响性能。
如建立ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);那么则等于建立如下三种组合索引

1
2
3
LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME

不走索引的案列

1
2
3
4
1. SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" 因为like后内容以通配符开头
2. 正则表达式不使用索引
3. SELECT * FROM `a` WHERE `a`=1 ,a为char类型，因为1没加引号，所以不使用索引，如果a为int类型则不影响
4. select * from dept where dname='xxx' or loc='xx' or deptno=45 条件中有or关键字则不使用索引

关于in，or的效率问题

如果所在列拥有索引或者主键，那么性能没啥差别，如果没有，那么or的性能要远远低于in

group by

group by的常规用法是配合聚合函数，利用分组信息进行统计，常见的是配合max等聚合函数筛选数据后分析，以及配合having进行筛选后过滤

1
2
3
SELECT <想要分组的属性，如最值,平均值> FROM <表名> group by <分组对象>  having <分组条件>
例：
SELECT max(user_id),grade FROM user_info group by grade  having grade>'A'

聚合函数

  1. AVG 求平均值
  2. COUNT 返回SELECT语句检索到的行的数目
  3. MIN/MAX 求最小、最大值
  4. SUM 求和

格式化函数

FORMAT(N,D,locale);

  • N是要格式化的数字。
  • D是要舍入的小数位数。
  • locale是一个可选参数，用于确定千个分隔符和分隔符之间的分组。如果省略locale操作符，MySQL将默认使用en_US。其中en_US表示12500为12,500,而de_DE则表示为12.500

经常FORMAT函数和CONCAT函数结合使用

例如显示的货物总金额

1
2
3
4
5
6
7
8
9
10
//将数量*单价得到的总金额取到小数点后2位，并且用“,”区分，再在数据前头加上'￥'符号
//例如￥387,209.73
//最后按照总金额分组
SELECT 
    productname,
    CONCAT('￥',
            FORMAT(number * Price, 2)) stock_value
FROM
    products
ORDER BY number * Price;

子查询

子查询允许把一个查询嵌套在另一个查询当中

  • any

    对于子查询返回的列中的任何一个数值，如果比较结果为TRUE，就返回TRUE

  • in

    in的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE

  • all

    对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE

关联

  • left join（左连接）

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).B表记录不足的地方均为NULL.

如select * from t_login l left join t_message m on l.id = m.id就会将t_login表内的内容都显示出来，然后将t_message表中id和t_login匹配的项显示出来

  • right join（右关联）

和left join（左关联）对应，以右表为准，右表全部显示，左表符合要求的显示，不足的地方补NULL

  • inner join（自关联）

inner join并不以谁为基础,它只显示符合条件的记录

注意：LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。

AND 和 OR 联用的注意

and 比 or具有更高的优先级，所以要注意sql语句的优先级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
优先级　　 运算符
(最高)　　  !
    　　　　-（负号）,~（按位取反）
    　　　　^（按位异或）
    　　　　*,/(DIV),%(MOD)
    　　　　+,-
    　　　　>>,<<
    　　　　&
    　　　　|
    　　　　=(比较运算),<=>,<,<=,>,>=,!=,<>(不等于),IN,IS NULL,LIKE,REGEXP
   　　　　BETWEEN AND,CASE,WHEN,THEN,ELSE
   　　　　NOT
   　　　　&&,AND
   　　　　XOR
   　　　　||,OR
(最低) 　　=(赋值运算),:=

id>1 and name like '%yy%' or age>18

表的设计默认值，注释，表注释

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
int类型：默认值也得是整型，并且default后边不要()括号。

 

char类型：默认值使用单引号。

 

DATETIME类型：NOW()函数以'YYYY-MM-DD HH:MM:SS'返回当前的日期时间，可以直接存到DATETIME字段中。不支持使用系统默认值。

 

DATE类型：CURDATE()以'YYYY-MM-DD'的格式返回今天的日期，可以直接存到DATE字段中。不支持使用系统默认值。

 

TIME类型：CURTIME()以'HH:MM:SS'的格式返回当前的时间，可以直接存到TIME字段中。不支持使用系统默认值。

//表的注释以及字段的注释
create table test1
(
field_name int comment '字段的注释'
)comment='表的注释';
赏个🍗吧
0%