数据库设计笔记——MySQL基础知识（四）

最新推荐文章于 2023-05-06 17:51:45 发布
最新推荐文章于 2023-05-06 17:51:45 发布
阅读量609 收藏
点赞数
版权所有©️都是我自己写哒！
本文链接：https://blog.csdn.net/cpongo1/article/details/100993863
版权

概述

  • 关系型数据库——由表来存储相关的数据，MySQL，SQL Server，Oracle等都是关系型数据库；
  • 元数据：用于集成并管理数据。

  • MySQL数据库

    • 用户数据库：用户根据需求创建数据库
    • 系统数据库
      • information_schema：主要存储系统中的一些数据库对象信息，如用户信息、列信息、权限信息、字符集信息和分区信息等
      • performance_schema：存储数据可以服务器性能参数
      • mysql：系统的用户权限信息
        • test

  • MySQl的数据对象

    • 表
      • 栏位：该表的列（也叫属性或者字段）的名字和数据类型
      • 索引：根据指定的数据库表列建立起来的顺序，提供快速访问的路径且可以监督表的数据。
    • 视图
    • 存储过程（手动调用，无返回值）
    • 函数（手动调用，必须有返回值）
    • 触发器（自动调用）
    • 事件

  • 数据库的命名规则

    • 由字母、数字、下划线、@、#、$符号组成，首字母不能是数字和$符号，标识符不允许是MySQL的保留字，不允许有空格和特殊字符，长度小于128位。

  • MySQL版本

    • MySQL Community Server（社区版）：完全免费，官方不提供技术支持
    • MySQL Enterprise Server（企业版服务器）：他能够以很高性价比为企业提供数据仓库应用，支持ACID事务处理，提供完整的提交、回滚、崩溃恢复和行级锁定功能。但是该版本付费，官方提供电话支持。

  • MySQL的优势：

    • 速度：运行速度快。
    • 价格：MySQL对大多数个人来说是免费的。
    • 容易使用：与其他大型数据库的设置和管理相比，其复杂程度较低，易于学习。
    • 可移植性：能够工作在众多不同的系统平台，例如：Windows、Linux、Unix、Mac OS。
    • 丰富的接口：提供了用于C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等语言的API。
    • 支持查询语言：MySQL可以利用标准SQL语法和支持ODBC的应用接口。
    • 安全性和连接性：十分灵活和安全的权限和密码系统，允许基于主机的验证。连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码安全。并且由于MySQL是网络化的，因此可以在因特网上的任何地方访问，提供数据共享的效率。

存储引擎

  • 存储引擎决定数据库对象表的类型，即如何如何存储、索引数据、是否支持事务，也决定了表在计算机中的存储方式。
  • 数据类型，决定数据库对象表可以存储数据的类型。
  • 存储引擎类别(9)
    • InnoDB（默认）

      支持事务，支持具有提交、回滚、崩溃恢复能力的事务安装，比MyISAM占磁盘空间大。

    • MyISAM

      不支持事务，也不支持外检，访问速度较快

    • MEMORY

      使用内存来存储数据，访问速度快，但安全上无保障

    • FEDERATED
    • MRG_MYISAM：
    • BLACKHOLE
    • CSV
    • ARCHIVE
    • PEFORMANCE_SCHEMA

  • 相关命令

    • 查看DBNS版本

      1
      SELECT version();

    • 查询默认存储引擎

      1
      SHOW　VARIABLE LIKE 'storage _engine%';

  • 修改默认存储引擎通过修改my.ini文件中的"default_storage-engine"参数

数据类型(每个字节8为)

  • 数值
    • 整型
      • TINYINT（1字节）
      • SMALLINT（2字节）
      • MEDIUMINT（3字节）
      • INT/INTEGER（4字节）
      • BIGINT（8字节）
    • 浮点型
      • FLOAT（4字节）
      • DOUBLE（8字节）
    • 定点型：DEC/DECIMAL(M/D)

      其中M是小数点左右数字之和，D是右边数字个数，M<=38

  • 字符串型

    • CHAR（0~255字节） ：固定字长
    • VARCHAR（0~65535字节）：可变长度

  • 日期时间类型

    • YEAR：年份，1字节
    • TIME：时分秒，3个字节
    • TIMESTAMP：经常插入或者更新日期为当前系统时间，4字节
    • DATATIME：年月日时分秒，8字节
    • DATE：年月日，4字节

查看表结构

  • 表的操作

    1
    2
    DESCRIBE table_name  //查看定义
    SHOW CREATE TABLE table_name;  //查看表详细定义

  • 操作表的约束

    • 通过表的约束来完成对表中所存储的数据是否合法、是否具有完整性，进行检查。

  • 约束

    • 单列约束：每个约束只约束一列数据。
    • 多列约束：每个约束约束多列数据。

  • 完整性约束

    • NOT NULL ：非空
    • DEFLAULT ：设置字段的默认值
    • UNIQUE KEY：设置字段的值是唯一
    • PRIMARY KEY（PK）：约束字段为表的主键，只作为表的唯一标识
    • AUTO_INCREMENT ：约束字段自动增加
    • FOREIGN KEY（FK）：约束字段为表的外键

  • 注意

    • 多字段主键：主键有多个字段组合而成。
    • 字段自动增加（AUTO_INCREMENT）：MySQL唯一扩展的完整性约束，当为数据库表中插入新记录时，字段上的值会自动生成唯一的ID。一个数据表中只能有一个字段使用该约束，且字段数据类型一定是整型，由于其唯一的ID，该字段经常会设置为PK主键。
    • 设置外键约束（FK）：外键约束保证多个表（通常两个）之间参照完整性，即构建两个字段之间的参照关系。设置外键的两个表之间具有父子关系，即子表中某个字段的取值范围由父表所决定。如全年级的成绩表，以学号作为总表的主键，而班级作为另一个表的主键，则班级为总表的外键，也就是总表的子表。

索引的操作

  • 表：包含数据库的所有数据的数据库对象，数据库对象表是存储和操作数据的逻辑结构。
  • 索引：一种有效组合数据的方式。通过索引对象，可以快速查询到数据库对象表中的特殊记录，是一种提高性能的常用方式，一个索引会包含表中安排一定顺序排序的一列或多列字段。
  • 根据索引的存储类型，可分为：
    • B型树索引（BTREE）<- InnoDB支持
    • 哈希索引（HASH）<- MEMORY支持

  • 索引作用

    • 提高查找数据的速度
    • 实现数据库对象表的完整性

  • MySQL支持6种索引

    • 普通索引： 在创建索引时，不附加任何限制条件（如唯一、非空等），可创立在任何数据类型的字段上。
    • 唯一索引：在创建索引时，限制索引的值必须是唯一的，可更快查询某记录。
    • 全文索引
      • 主要是关联在数据类型为CHAR、VARCHAR、TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型字段。
      • 全文索引只能在存储引擎为MYISAM的数据表上创建。
    • 单列索引
    • 多列索引
    • 空间索引

  • 适用于创建索引的情况

    • 经常查询的字段，即在where子句中出现的字段。
    • 在分组的字段，即group By子句中出现的字段。
    • 存在依赖关系的子表和父表之间的联合查询，即关键/外键字段。
    • 设置唯一完整性约束的字段。

  • 不适用于创建索引的情况

    • 在查询中很少被使用的字段
    • 拥有许多重复值的字段

注意，过多的创建索引则会占据许多磁盘空间。

视图的操作

  • 视图：通过视图的操作不仅可以实现查询的简化，而且可以提高安全性。所谓的视图，本质上是一种虚拟表，其内容与真实表相似包含一系列带有名称的列和行数据，但是视图并不在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

  • 视图的特点

    • 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
    • 视图时由基本表（实表）产生的虚表。
    • 视图的建立和删除不影响基本表。
    • 对视图内容的更新（添加、删除和修改）直接影响基本表。（相当于对基本表操作的工具）
    • 当视图来自多个基本表时，不允许添加和删除数据。

  • 创建视图语法 

    1
    CREATE VIWE 'database_name'.'VIWE_name' AS (SELECT * FROM ...);

    database_name为当前的数据库名称，VIWE_name为新建视图名称，“*”处填写要查询的列，用“,”隔开，“…”处写表名称。

  • 由于视图的功能实际上是封装查询语句，分为以下几类

    • (1）封装实现查询常量语句的视图，即所谓的常量视图

      1
      CREATE VIWE view_test1(新建视图的名称) AS SELECT 3.1415926;

      可将上面的语句输入到MySQL5.7 command line client 中。

    • (2)封装使用聚合函数(SUM、MIN、MAX、COUNT等)，查询语句的视图。

      1
      CREATE VIWE view_test2 AS SELECT COUNT(col_name) FROM table_name;

    • (3)封装了实现排列功能（ORDER BY）查询语句的视图

      1
      CREATE VIWE view_test3 AS SELECT col_name FROM table_name ORDER BY id DESC;   //排列方式

    • (4)封装了实现表内连接查询语句的视图

      1
      CREATE VIWE view_test4 AS SELECT col_name FROM table_name as s.t_group as g WHERE s.group_id=g.id AND g.id=2;

    • (5)封装了实现表外连接（LEFT JOIN和RIGHT JION）查询语句的视图

      1
      CREATE VIWE view_test5 AS SELECT col_name FROM table_name as s LEFT/RIGHT/ALL JION t_group as ON s.group=g.id WHERE g.id=2;

    • (6)封装实现子查询相关查询语句的视图

      1
      CREATE VIWE view_test6 AS SELECT col_name FROM table_name AS s WHERE s.group_id=g,id IN (SELECT id FROM t_group);

    • (7)封装了实现记录联合（UNION和UNION ALL）查询语句的视图

      1
      CREATE VIWE view_test7 AS SELECT id,name FROM table_name UNION ALL SELECT id,name FROM　t_group;

  • 查看视图

    1
    2
    USE database_name;
    SHOW TABLES;

  • 查看视图详细信息

    1
    2
    SHOW TABLE STATUS [FROM db_name]|[LIKE 'pattern']
    SHOW　TABLE STATUS FROM db_name \G

  • 查看视图定义信息

    1
    SHOW CREATE VIWE view_name;

  • 查看视图设计信息

    1
    DESCRIBE/DESC view_name;

  • 通过系统表查看视图信息

    1
    2
    USE infoemation_schema;
    SELECT * FROM views WHERE table_name='view_selectproduct' \G

    SQLyog软件中直接“工具”—>“信息”

触发器的操作

  • 触发器用来实现由一些表事件触发的某种操作，是与数据库对象表关联最紧密的数据库对象之一。在DBS中，当执事件间时，则会激活触发器，从而执行其包含的操作。触发器类似于编程语言中的函数。总的来说就是表发生更改时，自动进行一些处理。

  • MySQL的触发器语句（事件引导）：

    • DELETE语句：删除
    • INSERT语句：添加
    • UPDATE语句：修改

      其他SQL语句不会激发触发器，会经常使用触发器数据库对象，是由于该对象能够加强数据库表中数据的完整性约束和业务规则等。

  • 创建触发器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    DELIMITER $  //固有格式，当有多个触发器语句时使用
    CREATE TRIGGER t_afterinsert_on_doorlog AFTER INSERT 
    ON t_doorlog FOR EACH ROW 
    BEGIN
      DECLARE jobno VARCHAR(30);
      DECLARE time1 VARCHAR(20);
      SET jobno = (SELECT job_num FROM tb_park_user_base WHERE card_num = new.cardNo);
      SET time1 = DATE_FORMAT(new.operateTime,'%Y%m%d%H%i%s');
      INSERT INTO t_attendance (job_no, operateTime) 
      VALUES
        (jobno,time1);
    END$

创建触发器

  • DELIMITER $ 意思是告诉mysql下文执行语句以“$”结束，如果没有这句，下面语句会出错，mysql无法判断哪句结束。
  • CREATE TRIGGER t_afterinsert_on_doorlog AFTER INSERT ON t_doorlog FOR EACH ROW 创建触发器的语法 其中AFTER同级关键字有BEFORE，而 INSERT同级关键字还有DELETE，UPDATE
  • BEGIN和END之间的是触发器执行语句。DECLARE jobno VARCHAR(30) 意思是声明触发器SQL中的局部变量，SET jobno=….　　意思是给局部变量赋值，=右边可为查询语句或函数方法

单表数据记录查询

  • 四则运算：+(加)、-(减)、*(乘)、/(DIV除)、%(取余)。->字段间运算

    1
    SELECT ename(字段) sal*12 FROM table_name;  //查询年薪

  • 对运算结果/某字段修改字段名，使显示更直观

    1
    2
    SELECT ename(字段),sal*12 yearsalary FROM table_name;
    SELECT ename(字段),sal*12 AS yearsalary FROM table_name;

  • 设置数据显示格式，以便用户浏览所查询到的数据. 在MySQL软件中提供函数CONCAT()来连接字符串，从而实现设置显示数据的格式，语句如下：

    1
    SELECT CONCAT|ename ,'雇员的年薪为：', sal*12) yearsalary FROM table_name;

结果：SMITH雇员的年薪为：9600.00

多表数据记录查询

  • 多表数据记录查询

    • 内连接查询：笛卡尔积内连接表后查询

    • 外连接查询：笛卡尔积外连接表后查询

    • 子查询

      进行连接查询中，首先要对两张或两张以上表进行连接操作。多表数据记录查询是关系数据操作中专门用于数据库操作的关系运算。有并、笛卡尔积、连接（UNION）。

  • 查询语法

多表数据记录查询

多表数据记录查询

多表数据记录查询

多表数据记录查询

实现特殊功能比较运算符

  • 正则表达式匹配

    • ^：匹配字符串开始部分，如REGEXP '^c'

    • $:匹配字符串结束部分，如REGEXP 'g$'

    • .：匹配字符集合中任意一个字符，如REGEXP '^c···g$'

    • [字符集合]：匹配字符集合中任意一个字符，如REGEXP '[abc]'

    • [^字符集合]：匹配字符集合外任意一个字符，如REGEXP '^[abc]'

    • str1|str2|str3：匹配str1、str2、str3中任意一个字符

    • *：匹配字符，包括0个或1个，如REGEXP 'a*g'

    • +：匹配字符，包括1个，如REGEXP 'a+g'

    • 字符串{N}：字符串出现N次

    • 字符串{M,N}：字符串出现至少M次，至多N次

  • 位运算符

    • &：按位与

    • |：按位或

    • ~：按位取反

    • ^：按位异或

    • <<：按位左移

    • >>：按位右移

    • 例子：

      1
      2
      SELECT 5&6, BIN (5&6) 二进制；
      SELECT 4&5&6, BIN (4&5&6) 二进制；
结果：

按位运算符计算结果

MySQL常用函数

MySQL常用函数1

MySQL常用函数2

MySQL常用函数3

MySQL常用函数4

MySQL常用函数5

存储过程和函数的操作

MySQL事务

MySQL安全性机制

flybirding10011
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
上课笔记——虚拟机基础知识
04-29
上课笔记——虚拟机基础知识
尚硅谷数据库视频笔记总结mysql
07-11
尚硅谷数据库笔记总结
数据库系统概论习题3
huayu8086的博客
08-04 1294
图书表book（bno, bname, author, publisher, price）其属性依次表示图书编号、书名、作者、出版社、单价；用户zhang具有查询每个出版社图书的最高单价、最低单价、平均单价的权限，但他不能查看每本书的单价。读者表reader（cardno, rname, sex, address）其属性依次表示借书证号、姓名、性别、地址；建立一个“清华大学”出版社图书的视图，视图名称为qhpublisher_view，包括图书编号、书名、作者、单价。将所有图书的单价增加5元。...
数据库入门笔记（基础篇）
conquer_jsj的博客
05-06 919
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。目的：保证数据库中的数据的正确，有效性和完整性。分类：注意：约束时作用于表中字段上的，可以在创建表/修改表的时候添加约束。外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。SQL语句中嵌套select语句，称为嵌套查询，又称为子查询。子查询外部的语句可以是insert/update/delete/select的任何一个。
数据库的基本知识(学习笔记)
qq_62540010的博客
01-02 2041
## 在讲数据库之前，我们先了解一下现实世界，信息世界，机器世界，这三者之间的关系 1.1现实世界 现实世界也就是客观存在的世界，这一概念在数据库中指的是对某一事物的描述，描述其各方面特征以及是否与其他事物存在联系 2.1信息世界 信息世界是对客观事物的抽象描述，是对现实世界经过分析、归纳和抽象得到的信息，这样的信息具有一定的格式。 2.2信息世界中的信息是对现实世界事物的解释，是对我们所认识事物的加工，所以引入一些概念： 实体：指现实世界中一个客观存在并能区别的事物 属性：实体的某一特征 属性型
数据库基础
weixin_45761101的博客
12-04 814
SQL
MySQL数据库选择题
热门推荐
小啊宇的博客
01-05 2万+
1.SQL语言中数据定义语言DDL中包括哪个语句（A） A、DROP B、DELETE C、select D、grant 2.SQL语句中REVOKE all on auth.* from ‘zhangsan’@‘localhost’；表述正确的是（B） A、zhangsan账户可以在localhost登陆 B、撤销zhangsan用户的auth库权限 C、Zhangsan对ON库没有权限 D、撤销全部命令 3.备份所有数据库（C） A、mysqldump -u root -p mysql user &gt
MySQL 数据库设计复习笔记及项目实战
12-15
最近手头上有3个项目开动，其他2个都是从底层开始的，一个已经开始了一段时间的了，在小城市小团队开发的条件下，都没有专门的DBA来做数据库的设计和维护，往往都是开发人员顶上，可是看了很多的数据库的设计，感觉...
mysql 数据库基础笔记
09-11
mysql 数据库基础笔记，刚开始接触mysql的朋友可以参考下
数据库笔记
m0_58121776的博客
09-21 1122
数据库基础知识
脚本语言TCL教程:3
iteye_7332的博客
05-21 215
1． 字符串函数 序号 函数 解释 1 string tolower string1 把string1转换为小写字母 2 string toupper string1 把string1转换为大写字母 3 string trim string1 ? trimchars ? ...
Mysql中的关键字和保留字
书山路遥
07-12 3441
关键字VS保留字 最近经常看到同事的建表语句有很多问题，其中有个问题就是字段的命名问题。所以特意去查了下Mysql中的关键字和保留字的相关知识1，这里分享一下。 有一点大家应该都很清楚，就是SQL和编程语言一样，是有关键字的。但是我们所理解的关键字在SQL中分为两类： 关键字 这类字在Mysql中具有特殊含义，例如常见的alter、static、cache。虽然具有特殊含义，但是还是可以...
数据库基础入门知识笔记
weixin_45961921的博客
03-02 620
数据库小白笔记 文章目录数据库小白笔记数据库的启动数据库基本命令列的类型定义列的完整性约束调整列的约束数据库索引的概念表的插入更新表中的数据记录删除表中的记录数据查询视图操作触发器存储过程和函数概述光标使用详解if语句详解case语句使用详解loop语句使用详解leave语句使用详解iterate语句使用详解iterate: 迭代repeat语句使用详解while语句使用详解查看存储过程数据库的存储引擎其它 数据库的启动 数据库的启动 MySQL80图形化界面启动: 打开计算机管理,服务中找MySQL80,
数据库中的关键字——字段(列）、记录（元组）、表、主键、外键
weixin_47119687的博客
01-05 2494
一、字段（列） 某一个事物的一个特征，或者说是属性 其中姓名就是员工的一个属性，可称之为字段 二、记录（元组） 事物所有特征的组合，可以描述一个具体的事物 三、表 记录的组合 表示***同一类***事物的组合 四、主键 能唯一标识信息的事物 在说主键之前，先看上面一个表格，上述格式可以存储信息，但是某一天当出现两个姓名，性别、年龄都相同的王二麻子时，就无法辨别，我们管这种现象叫做***冗余***，并且这两信息就无法辨别，所以这是就需要设置一个特定的东西来唯一的标识信息。所以我们就用编号来唯一标识员工的
数据库库设计：字段默认值
xuexiaodong2009的专栏
03-02 1万+
定义表，一般情况是不建议定义成可为空的字段的，可以使用默认值来定义默认值。例如：alter table tablename add(defaultField INTEGER default 1 not null);定义字段的默认值是1，如果没有赋值，再默认值是1alter table tablename add(createtime Date default sysdate not null);定
数据库笔记（基础篇）
hmoji
01-19 2035
文章目录数据库（基础篇）关系数据库基本概念关系的完整性实体完整性参照完整性用户自定义完整性关系代数传统集合运算符专门关系运算符关系数据库标准语言SQL模式的定义与删除定义模式删除模式基本表的定义、删除和修改定义基本表数据类型模式与表修改基本表删除基本表索引的建立和删除建立索引修改索引删除索引数据字典数据查询单表查询连接查询嵌套查询集合查询基于派生表的查询数据更新插入数据修改数据修改数据视图定义视图删除视图查询视图更新视图数据库的安全性授权授予收回收据库角色审计数据库的完整性实体完整性参照完整性定义参照完整性
MySQL介绍以及基础命令
xing476255461的博客
09-14 334
1. MySQL概述 MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。 MySQL是一种关系数据库管理系统，关系数据库将数据保存在...
数据库基础笔记
m0_55956064的博客
08-17 216
javaWeb笔记
【数据库学习笔记（一）】基础知识
CPScienceMFQ的博客
10-21 355
【数据库学习笔记（一）】基础知识
cda备考学习学习笔记——基础知识篇(二)
最新发布
09-18
cda备考学习学习笔记——基础知识篇(二)主要涉及了计算机科学与技术领域的基本概念和知识。 首先，它介绍了计算机网络的基础知识。网络是将多台计算机通过通信链路连接起来，使它们能够相互通信和共享资源的系统。笔记中详细介绍了网络的组成、拓扑结构和通信协议等重要内容。 其次，笔记还解释了计算机系统的基本组成。计算机系统由硬件和软件两部分组成，其中硬件包括中央处理器、存储器、输入输出设备等，而软件则分为系统软件和应用软件。笔记详细介绍了各种硬件和软件的功能和作用。 此外，笔记还对数据库管理系统进行了介绍。数据库管理系统是一种用于管理和组织数据的软件系统，它能够实现数据的存储、检索和更新等操作。笔记中详细介绍了数据库的概念、结构和操作等内容。 最后，笔记还包括了算法和数据结构的基础知识。算法是解决问题的一系列步骤和规则，而数据结构则是组织和存储数据的方式。笔记中介绍了常用的算法和数据结构，如排序算法、树和图等。 总之，通过学习CDA备考学习笔记中的基础知识篇(二)，我们能够更好地理解计算机网络、计算机系统、数据库管理系统以及算法和数据结构等相关概念和知识。这些基础知识对于我们深入研究计算机科学与技术领域是非常重要的，也为我们日后的学习和工作奠定了坚实的基础。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

flybirding10011

谢谢支持啊999

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值