最全的数据库笔记

2023-01-04 19:07:40

数据库笔记

第一章 了解Sql

创建数据库表

  • 不应该将客户得清单和订单得清单混合在一起，因为他们之间得逻辑相差比较大，日后得索引和查找 都比较困难

多列组合主键

  • 多列作为主键时，所有列得组合必须是唯一的

第二章 MySql简介

MySql受欢迎

  • 免费，性能高，可信赖，简单

第三章 使用MySql

MySql用户列表

  • MySql内部维护了一张用户信息权限表，不同得用户具有不同得权限，并且把用户得权限关联起来

数据库关键字（语句结束加；）

  • 函数 说明
    use crashcourse 使用crashcourse数据库
    show databases 返回可用数据库得一个列表
    show tables 返回数据库中可用表得列表
    show columns from customers 返回表得结构信息，相当于：describe customers

第四章 检索数据

检索单个列

  • select prod_name from products;
    • 返回所在列名得所有行，数据没有经过过滤，也没有经过排序

检索多个列

  • select prod_id，prod_name，prod_price from products;
    • 返回所在列名得所有行，数据没有经过过滤，也没有经过排序
  • 数据得格式化是一个表示问题，而不是一个检索问题

检索所有列

  • select * from products;
    • 返回所有列名得所有行，数据没有经过过滤，也没有经过排序

检索所在列不重复行

  • select distinct prod_name from products;
    • 只返回prod_name所在列不同的行
  • distinct应用于所有的列，而不仅仅是他前置的列，除非指定的几个列都不相同，否则所有的行都要被检索出来

检索所在列的n条数据

  • select prod_name from products limit 5;
    • 返回所在列名得5行，数据没有经过过滤，也没有经过排序
  • limit 5，5：会从标记为5的行开始的5行数据展示出来

使用完全限定表明

  • select products.prod_name from products;
    • 返回所在列名得所有行，数据没有经过过滤，也没有经过排序

第五章 排序检索数据

排序数据

  • 数据最初被添加到表中，但是数据后来进行更新或者删除，他们初始的顺序会受到MySql重用回收存储空间的影响

按单个列排序

  • select prod_name from products order by prod_name;
    • 返回所在列名得所有行，数据经过prod_name为规则进行排序
  • order by子句中所使用的列是为了显示所选择的列，用非检索的列排 序数据也是合法的

按多个列排序

  • select prod_name，prod_id，prod_price from products order by prod_price，prod_name;
    • 先按prod_price进行排序，然后按照 prod_name进行排序 -
  • 如果prod_price所有的值都是唯一的，那么将不会按照prod_name 的值进行排序

指定排序方向

  • select prod_name，prod_id，prod_price from products order by prod_price desc，prod_name;
    • prod_price列进行降序排序，而另外两列则会按照prod_name的值进行标准升序排列

第六章 过滤数据

使用WHERE子句

  • SELECT id FROM acl_role_permission WHERE id = 1;
    • 查找出id列所有行中id = 1的行
  • 能在数据库中过滤的数据，就在数据库阶段进行过滤，因为在业务层过滤数据的时候，会传递很多数据，造成运行缓慢

检测单个值

  • SELECT id，price FROM acl_role_permission WHERE id = 1;

不匹配检查

  • SELECT id，price FROM acl_role_permission WHERE id <> 1;

范围值检查

  • SELECT id，price FROM acl_role_permission WHERE id between 1 and 5
    • 查询范围中所有的值，包括开始值和结束值

空值检查

  • SELECT id，price FROM acl_role_permission WHERE id is null;
    • 检查id=null的行

第七章 数据过滤

组合WHERE子句

  • 通过组合子句的方式进行数据的过滤，使得过滤的数据更加的准确，包括and和or两种方式

And操作符

  • SELECT id，price FROM acl_role_permission WHERE id = 1 and price = 5;
    • 查找id = 1 and price = 5所在行的数据（与关系）

Or操作符

  • SELECT id，price FROM acl_role_permission WHERE id = 1 or price = 5;
    • 查找id = 1 or price = 5所在行的数据（或关系）

计算次序

  • Where子句中可以包含无限多的and和or，他们之间的运算顺序。And操作符号的优先级比or的，可以使用（）来调整他们之间的优先级，一般情况使用（），有利于理解和正确的运行结果

In操作符

  • SELECT id,price FROM acl_role_permission WHERE id in (1,5);
    • 范围内的 id in (1,5) 条件都会进行匹配，会检索id = 1 or price = 5，他的作用和or的作 用是一样的

not操作符

  • SELECT id,price FROM acl_role_permission WHERE id not in (1,5);
    • 范围内的 id in (1,5) 条件都不会进行匹配，否定他之后所跟的条件

第八章 用通配符进行过滤

Link操作符

  • 通配符：用来匹配一部分的特殊字符 在搜索子句中添加通配符，必须使用Link

%通配符

  • SELECT id,price FROM acl_role_permission WHERE id link 1%;
    • 检索以1开头的字符，并且接受1之后的任意长度字符，%可以在任意位置

_ 通配符

  • SELECT id,price FROM acl_role_permission WHERE id link 1_;
    • 检索以1开头的字符，并且接受1之后一个字符的长度的字符
  • 注意
    • 通配符运算比较慢，不要过度使用不要放在检索的首要条件

第九章 用正则表达式进行搜索

使用正则表达式

  • 正则表达式的作用是为了匹配文本，将一个模式与一个文本字符串进行比较，MySql用where子句对 正则表达式提供了初步的支持，允许使用正则表达事过滤select检索出数据

基本字符匹配

  • SELECT id,price FROM acl_role_permission WHERE id regexp 1000 order by id;

    • 筛选出id regexp 1000的字符出所在的行

  • SELECT id,price FROM acl_role_permission WHERE id regexp .000 order by id;

    • 筛选出id regexp （匹配任意一个字符）000的字符出所在的行

  • 使用regexp进行正则表达式的匹配，不区分大小写的，想要区分大小写的话regexp binary筛选出id regexp 1000|2000的字符出所在的行（或关系）

匹配几个字符之一

  • SELECT id,price FROM acl_role_permission WHERE id regexp [123] order by id;
    • 筛选出id regexp [123]的字符出所在的行（或关系），匹配 id=1，2，3
  • 一般来说，整个的进行或关系，可以使用`1000|2000`，但是只有部分匹配的话，使用[123]

匹配范围

  • SELECT id,price FROM acl_role_permission WHERE name regexp [1-9]ton order by id;
    • 筛选出name regexp [1-9]ton的字符出所在的行（或关系），匹配 name = 1ton，2ton，3ton，…
  • 注意 这里的关系是包含关系，即只要他们的关系中包含[1-9]ton就可以进行匹配

匹配特殊字符

  • SELECT id,price FROM acl_role_permission WHERE name regexp \\. order by id;
    • 对.进行转义，使得按照.进行匹配使用or进行匹配
  • SELECT id,price FROM acl_role_permission WHERE id regexp 1000|2000 order by id;

匹配字符类

  • 类 说明
    [:alnum:] 任意字母和数字
    [:alpha:] 任意字符
    [:blank:] 空格和制表
    [:cntrl:] ASCII控制字符
    [:dight:] 任意数字
    [:graph:] 与[:print:]相同，但不包括空格
    [:lower:] 任意小写字母
    [:print:] 任意可打印字符
    [:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
    [:space:] 包括空格在内的任意空白字符
    [:upper:] 任意大写字母
    [:xdight:] 任意十六进制数字

匹配多个实例

  • 元字符 说明
    * 0个或者多个匹配
    + 1个或者多个匹配
    ？ 0个或者1个匹配
    {n} 指定数目的匹配
    {n,} 不少于指定数目的匹配
    {n,m} 在指定数目之间的匹配

  • SELECT id,price FROM acl_role_permission WHERE name regexp \\([1-9] ton?\\) order by id;

    • 筛选出name regexp \\([1-9] ton?\\)的字符出所在的行

定位符

  • 为了匹配特定位置的文本

  • 元字符 说明
    ^ 文本的开始
    $ 文本的结束
    [[:<:]] 词的开始
    [[:>:]] 词的结束

第十章 创建计算字段

拼接字段

  • 在数据库中有很多字段并不是我们所需要的格式，所以我们进行拼接字段达到我们想要的格式

concat函数

  • SELECT concat(id,` `price) FROM acl_role_permission order by id;
    • 对id，price字段进行连接

RTtim函数

  • SELECT concat(RTtim(id),` `price) FROM acl_role_permission order by id;
    • RTtim函数会去掉数据库中值的右侧的所有空格
    • LTtim函数会去掉数据库中值的左侧的所有空格
    • Ttim函数会去掉数据库中值的两侧侧的所有空格

使用别名

  • SELECT concat(RTtim(id),` `LTtim(price)) as title FROM acl_role_permission order by id;
    • 方便辨识

执行算数计算

  • SELECT id,quantity,price,quantity*price as expanded_price FROM acl_role_permission order by id;
    • 会在客户机上多输出一列quantity*price as expanded_price的信息
  • 在SQL中可以使用的算计计算
    • 加减乘除

第十一章 使用数据处理函数

使用函数

  • 用于处理文本字符串
  • 在数值数据上进行算数运算
  • 用于处理日期和时间值
  • 返回DBMS正使用的特殊信息

文本处理函数

  • SELECT concat(RTtim(id),` `upper(LTtim(price)) as title FROM acl_role_permission order by id;

    • 变成大写
  • 函数 说明
    Left() 返回串左边的字符
    Length() 返回串的长度
    Locate() 找出串的一个字串
    Lower() 将串变成小写
    LTrim(),RTrim(),Tirm() 去掉串左边（右边，两边）的空格
    Right() 返回串右边的字符
    Soundex() 返回串的Soundex值
    Substring() 返回子串的字符
    Upper() 将串转换为大写

日期和时间处理函数

  • 常用的日期和时间处理函数

  • 函数 说明
    AddDate() 增加一个日期
    AddTime() 增加一个时间
    CurTime() 返回当前时间
    Date() 返回时间的日期部分
    DateDiff() 计算两个日期之差
    Date_Add() 高度灵活的日期运算函数
    Date_format() 返回一个格式化的日期或时间串
    Day() 返回日期的天数部分
    DayOfWeek() 对于一个日期，返回对应星期几
    Hour() 返回时间的小时部分
    Minute() 返回时间的分钟部分
    Month() 返回时间的月部分
    Now() 返回当前日期和时间
    Second() 返回时间的秒部分
    Time() 返回日期时间的时间部分
    Year() 返回时间的年部分
    CurDate() 返回当前日期

数值处理函数

  • 函数 说明
    Abs() 返回一个数的绝对值
    Cos() 返回一个角度的余弦
    Exp() 返回一个数的指数值
    Mod() 返回一个除操作的余数
    Pi() 返回圆周率
    Rand() 返回一个随机数
    Sin() 返回一个角度的正弦
    Sqrt() 返回一个数的平方根
    Tan() 返回一个角度的正切

第十二章 汇总函数

聚集函数

  • 函数 说明
    AVG() 返回某列的平均值
    COUNT() 返回某列的行数
    MAX() 返回某列的最大值
    MIN() 返回某列的最小值
    SUM() 返回某列的和

AVG()

  • SELECT AVG(price) AS myprice FROM products;

COUNT()

  • SELECT COUNT(price) AS myprice FROM products;

MAX()

  • SELECT MAX(price) AS myprice FROM products;

MIN()

  • SELECT MIN(price) AS myprice FROM products;

SUM()

  • SELECT SUM(price) AS myprice FROM products;

组合聚集函数

  • SELECT SUM(price) , MIN(price) , MAX(price) FROM products;

第十三章 分组数据

规定

  • GROUP BY可以包含任意数目的列
  • 如果嵌套分组，那么数据将在最后规定的分组上进行汇总
  • GROUP BY子句中列出的每个列都必须是检索列或者有效表达式
  • 除聚集计算语句外，Select语句中的，每个列都必须在GROUP BY子句中给出
  • 如果分组列中有NULL，则NULL将作为一个分组返回
  • GROUP BY子句必须实在where语句后，ORDER BY语句之前

过滤分组

  • where是为了过滤行，having是为了过滤分组
  • SELECT id,COUNT(*) AS orders FROM orders GROUP BY id HAVING COUNT(*) >= 2;

第十四章 使用子查询

  • 查询语句中可以嵌套查询语句

第十五章 联结表

好处

  • 分解数据可以使表更加的容易管理，更方便的处理，并且有很大的伸缩性

  • SELECT vend_name,prod_name,prod_price FROM vendors,products

    WHERE vendors.vend_id, products.vend_id ORDER BY vend_name,

    prod_name;

注意

  • 表与表之间的联结是在运行中建立的
  • 当没有where语句时，将第一个表的每一行与第二个表进行匹配，不管有没有逻辑

内部联结

  • SELECT vend_name,prod_name,prod_price FROM vendors inner jion products on vendors.vend_id = products.vend_id ;

联结多个表

  • SELECT vend_name,prod_name,prod_price,quantity FROM vendors ,orderitems, products where vendors.vend_id = products.vend_id

    AND orderitems.prod_id = products.vend_id AND order_num = 20005;

  • 联结的表越多，性能下降的越厉害

第十六章 使用带聚集函数的联结

使用表别名

  • SELECT vend_name,prod_name,prod_price,quantity FROM vendors as v,orderitems as o, products as p where v.vend_id = p.vend_id

    AND o.prod_id = p.vend_id AND order_num = 20005;

自联结

  • SELECT vend_name,prod_name FROM products where vend_id = （SELECT vend_id FROM products where prod_id = ` DTNTR`）;
    • 这是一个子查询
  • SELECT vend_name,prod_name FROM products as p1,products as p2 where p1.vend_id = p2.prod_id and p2.prod_id = ` DTNTR`;
    • 自己和自己联结，需要使用表别名进行区分是使用的哪一个表
    • 一般情况下，用自联结而不用子查询，因为自联结的速度比子查询的速度快

自然联结

  • SELECT c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price FROM customers as c ,order as o ,orderitems as oi where c.cust_id = o.cust_id and oi.order_num = o.order_num and prod_id = `FB`;

外部联结

  • 内连接

  • SELECT customers .cust_id ,orders.order_num FROM customers

    inner join orders on customers .cust_id = orders.cust_id ;

  • 左连接

  • SELECT customers .cust_id ,orders.order_num FROM customers

    left join orders on customers .cust_id = orders.cust_id ;

  • 右连接

  • SELECT customers .cust_id ,orders.order_num FROM customers

    right join orders on customers .cust_id = orders.cust_id ;

使用聚集函数的联结

  • SELECT customers .cust_id ,count(orders.order_num ) FROM customers

    left join orders on customers .cust_id = orders.cust_id ;

第十七章 组合查询

组合查询

  • MySql允许多个查询，并将结果作为单个查询结果集返回

使用UNION

  • SELECT customers .cust_id ,orders.order_num FROM customers

    left join orders on customers .cust_id = orders.cust_id UNION SELECT customers .cust_id ,orders.order_num FROM customers

    left join orders on customers .cust_num = orders.cust_num;

    • 将两个SELECT 的查询结果作为单个查询结果进行输出
    • 和or类似

注意

  • UNION 必须是由两条以上的SELECT 语句进行组成

  • 每个查询必须含有相同的列，表达式，聚集函数，抒写的次序没有要求

  • 列的类型必须兼容，不相同的转换为可以兼容的类型

  • UNION 在查询的时候重复的行，会被默认取消不显示，那么我们使用UNION ALL进行显示所有的行

  • 在使用UNION 进行查询的时候，只能使用一条order by语句，并且这一条语句出现在最后一个select之后

  • SELECT customers .cust_id ,orders.order_num FROM customers

    left join orders on customers .cust_id = orders.cust_id UNION SELECT customers .cust_id ,orders.order_num FROM customers

    left join orders on customers .cust_num = orders.cust_num order by customers .cust_id;

第十八章 全文本搜索

启用全文本搜索

  • 要在建表的时候添加FULLTEXT（列名）

进行全文索引

  • SELECT note_text FROM productnotes WHERE MAtch(note_text) Against(`rabbit`);
    • MAtch指定被搜索的列，Against指定被搜索的表达式
    • MAtch指定被搜索的列必须与FULLTEXT（列名）相同
  • SELECT note_text ,MAtch(note_text) Against(`rabbit`) as rank FROM productnotes ;
    • 返回所有的行，并且显示rank 列中显示关键字出现的次数

使用扩展查询

  • SELECT note_text FROM productnotes WHERE MAtch(note_text) Against(`rabbit` WITH QUERY EXPANSION);
    • 会返回匹配到的，并且也会返回和第一行相关程度高的

使用布尔文本搜索

  • SELECT note_text FROM productnotes WHERE MAtch(note_text) Against(`rabbit\ - repo*` IN BOOLEAN MODE );

  • 布尔操作符 说明
    + 包含，词必须存在
    - 排除，词不存在
    < 包含，而且增加等级值
    > 包含，而且减少等级值
    () 把词组成子表达式
    ~ 取消一个词的排序值
    * 词尾的通配符
    “” 定义一个短语

第十九章 插入数据

数据插入

  • 插入完整的行
  • 插入行的一部分
  • 插入多行
  • 插入某些查询的结果

插入完整的行

  • INSERT INTO customers VALUES(各列的输入的值，中间用,隔开);
    • 不安全
  • INSERT INTO customers(各列的列名，中间用,隔开) VALUES(各列的输入的值，中间用,隔开);

插入多个行

  • INSERT INTO customers(各列的列名，中间用,隔开) VALUES(各列的输入的值，中间用,隔开) ,(各列的输入的值，中间用,隔开)，

    …;

插入索引出的数据

  • INSERT INTO customers(各列的列名，中间用,隔开) SELECT (各列的列名，中间用,隔开) FROM custnew;

第二十章 更新和删除数据

更新数据

  • 更新特定的行
  • 更新所有的行

更新特定的行

  • UPDATE customers SET cust_emaol = `hanqingzong@qq.com` WHERE cuit_id = 10005;

删除数据

  • 删除特定的行
  • 删除所有的行

删除特定的行

  • DELETE FROM customers WHERE cust_emaol = `hanqingzong@qq.com` ;

第二十一章 创建和操纵表

创建表

  • CREATE TABLE 表名(列名，中间用，隔开);

使用NULL,NOT NULL

  • 规定该字段能否是null

主键

  • 主键必须唯一，单列不同，多列组合不同
  • PRIMARY KEY(列名，列名)

使用AUTO_INCREMENT

  • 每个表只允许有一个，并且他必须被索引，通常为主键

DEFAILT

  • 设置默认值

引擎类型

  • ENGINE
  • innodb是一个可靠的事务搜索引擎，他不支持全文搜索
  • MyISAM性能极高的引擎，支持全文搜索，但是不支持事务处理
  • MEMORY数据存储在内存中，速度很酷啊哦，和MyISAM类似

更新表

  • ALTER TABLE vendors ADD vend_phone CHAR(20);
  • ALTER TABLE vendors DROP COLUMN vend_phone ;

删除表

  • DROP TABLE 表名;

重命名表

  • RENAME TABLE vendors TO vendors2；

第二十二章 使用视图

为什么使用视图

  • 重用sql语句
  • 简化复杂的sql语句
  • 使用表的组成部分而不是整个表
  • 保护数据
  • 更改数据格式和表示

第二十三章 管理事务处理

上一篇：2021.3.21 哥哥的受难日


下一篇：无锁队列