本周周报-20220116

1．MySQL使用 C和 C++编写，并使用了多种编译器进行测试，保证了源代码的可移植性。
2．支持 AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统。
3．为多种编程语言提供了 API。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby,.NET和 Tcl 等。
4．支持多线程，充分利用 CPU 资源。
5．优化的 SQL查询算法，有效地提高查询速度。
6．既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中。
7．提供多语言支持，常见的编码如中文的 GB 2312、BIG5，日文的 Shift_JIS等都可以用作数据表名和数据列名。
8．提供 TCP/IP、ODBC 和 JDBC等多种数据库连接途径。
9．提供用于管理、检查、优化数据库操作的管理工具。
10．支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
11．支持多种存储引擎。
12.MySQL 是开源的，所以你不需要支付额外的费用。
13.MySQL 使用标准的 SQL数据语言形式。
14.MySQL 对 PHP 有很好的支持，PHP是比较流行的 Web 开发语言。
15.MySQL是可以定制的，采用了 GPL协议，你可以修改源码来开发自己的 MySQL 系统。
16.在线 DDL/更改功能，数据架构支持动态应用程序和开发人员灵活性
17.复制全局事务标识，可支持自我修复式集群
18.复制无崩溃从机，可提高可用性
19.复制多线程从机，可提高性能
20.3倍更快的性能
21.新的优化器
22.原生JSON支持
23.多源复制
24.GIS的空间扩展

3.MySQL与Oracle对比

首先，最核心的区别是Oracle是商用闭源的，需要付费，而MySQL是开源的。

详细区别可以参考：

mysql和oracle的区别有哪些-mysql教程-PHP中文网https://www.php.cn/mysql-tutorials-413588.html我认为比较重要的几点：

1.数据的物理隔离，oracle是一个实例操作一个库，一个库分为多个表空间，表空间物理隔离。而mysql是一个实例操作多个库，每个库的数据物理隔离。

2.主键的管理：oracle使用序列，mysql没有序列，可使用自动增长。

3.字符串：oracle使用单引号，mysql单双引号都可以。

4.分页和获取某一数据集合中的前n条记录，oracle需要使用内建视图，mysql使用limit函数

5.事务处理：mysql自动提交，oracle不自动提交。

6.mysql修改列信息时，必须指定列名。

7.mysql定义列级约束时，无法指定约束名称，需要使用表级约束来指定。

8.mysql中，删除每个约束的语法格式都不同（尽量使用完成的语法，避免误将列信息删除）

9.mysql会为外键列自动添加索引,删除外键约束还需要删除相应的索引才能完全删除。索引名与约束名相同。

同时，mysql不支持PL/SQL语言，SQL语法上也有一定的区别，本周我也学习了一些简单的PHP语法，用以实现相对复杂的操作

二、MySQL进阶

因为MySQL与Oracle同为关系型数据库，因此部分基础知识不再赘述。

1. 常见问题及解决

1.1 中文字符集问题

中文字符集在部分场景下可能出现无法识别的情况。这本质上是字符集问题，计算机将字符转换成二进制时出现了问题。

在MySQL中，中文有好几种编码方式，不同编码方式的冲突是导致问题的原因。使用如下命令可以查看当前的编码状态：

show variables like 'char%';

结果如图：

character_set_server：默认的内部操作字符集
character_set_client：客户端来源数据使用的字符集
character_set_connection：连接层字符集
character_set_results：查询结果字符集
character_set_database：当前选中数据库的默认字符集
character_set_system：系统元数据(字段名等)字符集

通过修改这些字段，可以使服务器与客户端使用的编码格式一致，避免出现乱码的问题。

例如我使用的命令行的默认代码格式为gbk，因而character_set_client、character_set_results这两个字段必须为GBK，才能保证

在修改character_set_client参数为utf8后，出现如下结果：

修改之后重新插入，插入成功，可以正常显示。

1.2 校对集问题

校对集是数据比较的方式，有三种格式：

_bin:binary,二进制比较，取出二进制位，一位一位的比较，区分大小写
_cs:case sensitive,大小写敏感，区分大小写
_ci:case insensitice,大小写不敏感，不区分大小写

校对集只有数据产生比较的时候才生效，因而必须在没有数据之前生成好，如果有了数据，那么在进行校对集修改：那么修改无效。

示例：创建两个表，设置不同校对集

进行查找

发现两个表按成功设定的方式进行了排序。

1.3 web乱码问题

无法限定用户行为，无法决定用户使用的字符类型，因而用PHP进行处理

本部分因为我还尚未涉及网络编程，等待下阶段学习后进一步深入。

2.MySQL列

2.1 列类型

MySQL支持多种列类型：数值类型、日期/时间类型和字符串(字符)类型。

关于各类型的介绍可以参考官方文档：第11章：列类型_MySQL 中文文档 (mysqlzh.com)https://www.mysqlzh.com/doc/105.html

2.2 列属性

mysql中的6个列属性：null，default，comment，primary key，unique key，auto_increment

2.2.1 NULL

代表字段为空。mysql的记录长度为65535字节，如果表中有字段允许为NULL，那么系统会设计保留1个字节来存储NULL，最终有效存储长度为65534字节

mysql> create table tbTest (
          name varchar(10) NOT NULL  -- 不能为空
       ) charset utf8;

2.1.2 default 默认值

mysql> create table tbTest (
          name varchar(10) NOT NULL
          age int default 18  -- 默认为18	
       ) charset utf8;
mysql> -- 两种方式触发默认值
mysql> insert into tbTest values('csxiaoyao');
mysql> insert into tbTest values('csxiaoyao', default);

2.1.3 comment 列描述

基本语法：comment '字段描述';

mysql> create table tbTest (
          name varchar(10) NOT NULL COMMENT '用户名不能为空'
       ) charset utf8;
mysql> -- 查看Comment必须通过查看表创建语句
mysql> show create table tbTest;

2.1.4 primary key 主键

1.创建主键

随表创建

mysql> -- 方法1，给字段增加 primary key 属性
mysql> create table tbTest (
          name varchar(10) primary key
       ) charset utf8;

mysql> -- 方法2，在所有字段之后增加 primary key 选项
mysql> create table tbTest (
          name varchar(10),
          primary key(name)
       ) charset utf8;

表后增加

基本语法：alter table <表名> add primary key(<字段>);

2. 查看主键

mysql> -- 方案1：查看表结构
mysql> desc tbTest;
mysql> -- 方案2：查看建表语句
mysql> show create table tbTest;

3.删除主键

基本语法：alter table <表名> drop primary key;

4.复合主键

mysql> create table tbTest (
          student_no char(10),
          course_no char(10),
          score tinyint not null,
          primary key(student_no, course_no)
       ) charset utf8;

5.主键约束

主键数据不能为空
主键数据不能重复

6.主键分类

业务主键：主键所在的字段，具有业务意义（学生ID，课程ID）
逻辑主键：自然增长的整型（应用广泛）

2.1.5 unique key 唯一键

主键也可以用来保证字段数据唯一性，但一张表只有一个主键

唯一键在一张表中可以有多个。
唯一键允许字段数据为NULL，NULL可以有多个（NULL不参与比较）

1.创建唯一键

随表创建

mysql> -- 方法1，给字段增加 unique[ key] 属性
mysql> create table tbTest (
          name varchar(10) unique
       ) charset utf8;

mysql> -- 方法2，在所有字段之后增加 unique key 选项
mysql> create table tbTest (
          name varchar(10),
          unique key(name)
       ) charset utf8;

表后增加

基本语法：

alter table <表名> add unique key(<字段1,字段2,...>);

2. 查看唯一键

mysql> -- 方案1：查看表结构
mysql> desc tbTest;
mysql> -- 方案2：查看建表语句
mysql> show create table tbTest;
mysql> -- 系统会为唯一键自动创建一个名字(默认为字段名)

CREATE TABLE `tbTest` (
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.删除唯一键

基本语法：

alter table <表名> drop index < 唯一键名>;

4.修改唯一键

先删除后增加

5.复合唯一键

mysql> create table tbTest (
          student_no char(10),
          course_no char(10),
          score tinyint not null,
          unique key(student_no, course_no)
       ) charset utf8;

2.1.6 auto_increment 自动增长

通常自动增长用于逻辑主键，只适用于数值，sqlserver中使用identity(1,1)

1.自动增长原理

在系统中维护一组数据保存当前使用自动增长属性的字段，记住当前对应的数据值，再给定一个指定的步长

2.使用自动增长

mysql> create table tbTest2 (
          id int primary key auto_increment,
          name varchar(10)
       ) charset utf8;

3.修改自动增长

查看自增长：自增长触发使用后，会自动在表选项中增加一个选项 (一张表最多只能有一个自增长)

CREATE TABLE `tbTest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

可以通过修改表结构来修改自动增长

基本语法：

alter table <表名> auto_increment = <值>;

4.删除自动增长

删除自增长：修改自动增长的字段，字段属性之后不再保留 auto_increment 即可

5.初始设置

在系统中有一组变量维护自增长的初始值和步长

show variables like ‘auto_increment%’;

6.细节问题

一张表最多只有一个自增长，自增长会上升到表选项中
如果数据插入没有触发自增长(给定了数据)，那么自增长不会表现，但是会根据当前用户设定的值初始化下一个值，例如当前id=1，插入数据给定id=3，则AUTO_INCREMENT=4
自增长修改时，值可以较大，但不能比当前已有的自增长字段的值小

3.设计范式

范式是设计数据库表的时候所依据的规范

3.1 第一范式

数据库表中不能出现重复记录，每个字段是原子性的不能再分。

示例如下：表一的代课时间可以再细分，因而不符合范式

3.2 第二范式

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖。

比如有两个主键，某个字段仅仅依赖其中之一，就是不可以的，例如下表中：性别仅依赖讲师，教室仅依赖班级。

3.3 第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段（不要产生传递依赖）。

例如该表中主键为IDP，但性别依赖于讲师，讲师再依赖于班级，这就不符合第三范式。

3.4 逆规范化

我的理解：在部分情况下，为了数据库的性能或者查询的方便性，可以不按规范进行设计，例如大数据量下的多表关联查询，就不如把数据进行整合查询单表的效率高。

4.高级数据操作

4.1 主键冲突

1.更新主键

Insert into tab[(字段，包含主键）] values(值列表) on duplicate kay update 字段=新值

2.替换主键

replace into 表名  [(字段列表：包含主键)]  values（值列表）

3.示例

4.2 蠕虫复制

将我们已经存在的某张数据表中数据内容，查询出来然后复制新增到指定的另一张数据表中，这个操作就叫做蠕虫复制

或是对本表内容进行不断地复制，实现成倍增长

语法与插入相同：

insert into + 数据表名 +字段列表/* + from 数据表名;

示例如下：

4.3 更新记录限制

更新语法如下：

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

使用limit子句可对数据进行限制，示例如下：

限制更改三行后，显示只有三行收到影响

4.4 删除数据限制

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

同4.3，可使用limit子句可对删除的行数进行限制

5.高级数据查询

5.1 基本语法

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

其中，各条子句的含义如下：

{*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。
<表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。
WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。
GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
[ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。
[LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。

5.2 连接查询

1.交叉连接：一般用来返回连接表的笛卡尔积

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

或


SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

示例如下：

2.内连接：主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]

示例如下：

3.外连接：先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

外连接可以分为左外连接和右外连接。

左连接的语法格式如下：

SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2>

右连接的语法格式如下：

SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2>

示例如下：

5.3 外键维护

外键约束是表的一个特殊字段，经常与主键约束一起使用。在 CREATE TABLE 语句中，通过 FOREIGN KEY 关键字来指定外键，具体的语法格式如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

示例如下：

5.4 联合查询

将多条查询语句的结果合并成一个结果。应用场景为要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：

要求多条查询语句的查询列数是一致的
要求多条查询语句的查询的每一列的类型和顺序最好一致
union关键字默认去重，如果使用union all 可以包含重复项

示例如下：

#引入的案例：查询部门编号>90或邮箱包含a的员工信息
#
SELECT * FROM employees WHERE department_id > 90 OR email LIKE '%a%';
#使用UNION联合查询
SELECT * FROM employees WHERE department_id > 90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';

5.5 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。示例如下;

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id IN
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type= 'A' );

6.视图

视图view是基于 SQL 语句的结果集的可视化的表，视图是由查询结果形成的一张虚拟表，视图也包含行和列，就像一个真实的表。使用视图查询可以使查询数据相对安全，通过视图可以隐藏一些敏感字段和数据，从而只对用户暴露安全数据。视图查询也更简单高效，如果某个查询结果出现的非常频繁或经常拿这个查询结果来做子查询，将查询定义成视图可以使查询更加便捷。

6.1 创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

1）OR REPLACE：表示替换已有视图

2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法；merge合并；temptable临时表

3）select_statement：表示select语句

4）[WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内

　　cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件

　　local表示更新视图的时候，要满足该视图定义的一个条件即可

TIPS：推荐使用WHIT [CASCADED|LOCAL] CHECK OPTION选项，可以保证数据的安全性

6.2 查看视图

SHOW CREATE VIEW 视图名

视图一旦创建完毕，就可以像一个普通表那样使用，视图主要用来查询。
有关视图的信息记录在information_schema数据库中的views表中

6.3 修改视图

ALTER VIEW 视图名 AS SELECT 查询语句;

注意：修改视图是指修改数据库中已存在的表的定义，当基表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致

6.4 删除视图

DROP VIEW 视图名

删除视图是指删除数据库中已存在的视图，删除视图时，只能删除视图的定义，不会删除数据，也就是说不动基表

7.还原与备份

7.1 备份

1.备份所有的数据库

 mysqldump -u username -ppassword --all-databases > bak.sql

2.备份指定的数据库db1、db2和db3

mysqldump -u username -ppassword --databases db1 db2 db3 > bak.sql

3.备份db数据库，当仅备份一个数据库时，--databases可以省略

mysqldump --u username -ppassword db4 > bak.sql

mysqldump -u username -ppassword --databases db4 > bak.sql

两者之间的差别在于不使用 --databases 选项，则备份输出信息中不会包含CREATE DATABASE或USE语句。不使用 --databases 选项备份的数据文件，在后期进行数据还原操作时，如果该数据库不存在，必须先创建该数据库。

4.备份db数据库指定的表tb_name1、tb_name2和tb_name3

mysqldump -u username -ppassword -t db --tables tb_name1 tb_name2 tb_name3 > bak.sql

7.2 还原

使用mysql命令读取备份文件，实现数据还原功能：

mysql -u username -ppassword < bak.sql

mysql -u username -ppassword db4 < bak.sq1

8.事务

事务主要用于处理操作量大，复杂度高的数据。

8.1 事务条件

一般来说，事务是必须满足4个条件：

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

8.2 事务控制语句

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

示例如下：

mysql> start transaction;#手动开启事务
mysql> insert into t_user(name) values('pp');
mysql> commit;#commit之后即可改变底层数据库数据
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
|  3 | pp   |
+----+------+
3 rows in set (0.00 sec)


mysql> start transaction;
mysql> insert into t_user(name) values('yy');
mysql> rollback;
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
|  3 | pp   |
+----+------+
3 rows in set (0.00 sec)

9.函数

函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值，注意，在MySQL中，任何函数都有返回值

9.1 系统函数

MySQL提供了丰富的系统函数，详细可见官方文档：

MySQL函数 - MySQL教程™ (yiibai.com)https://www.yiibai.com/mysql/functions.html

9.2 自定义函数

语法如下;

    CREATE  
        [DEFINER = { user | CURRENT_USER }]
        FUNCTION functionName ( varName varType [, ... ] )
        RETURNS returnVarType
        [characteristic ...] 
        routine_body

functionName：函数名，同MySQL内置函数一样，大小写不敏感
varName: 形参名
varType: 形参类型，其与varName配对使用。形参数量不限( $\geq 0$)
returnVarType: 返回值类型。函数必须有且只能有一个返回值
characteristic：函数特性，下将详述
routine_body：函数体。函数体中必须含有 return 语句，当函数体为复合结构时，需要使用begin ... end 语句

示例如下：

   create
        function myfun_getMax(num1 int, num2 int)
        returns int        
    begin
        declare res int;
        if(num1 > num2) then
            set res = num1;
        elseif (num1 < num2) then
            set res = num2;
        else
            set res = num1;
        end if;
        return res;
    end;

9.3 函数作用域

全局变量：使用set关键字定义使用@符合标志
局部变量：使用declare关键字声明,没有@符合,所有的局部变量必须函数体前声明

示例：


  DELIMITER $$

  CREATE FUNCTION method1(int_1 INT) RETURNS INT

  BEGIN

  DECLARE i INT DEFAULT 1;

  DECLARE res INT DEFAULT 0;

  mywhile:WHILE i<=int_1 DO

  IF i%5=0 THEN 

  SET i=i+1;

  ITERATE mywhile;

  END IF;

  SET res=res+i; 

  SET i=i+1;

  END WHILE;

  RETURN res;

  END $$

  DELIMITER ;

10.存储过程

存储过程是在数据库中存储复杂程序，以便调用，是一种没有返回值的函数。

10.1 创建存储过程

示例如下：

create procedure mypro(in a int,in b int,out sum int) 
begin 
set sum = a+b; 
end;

10.2 存储过程参数

MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型：

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）；
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）；
INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

10.3 变量

MySQL 中的存储过程类似 java 中的方法。既然如此，在存储过程中也同样可以使用变量。

示例如下：

DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

三、物流仿真技术

观看教学视频，学习plant simulation内置优化工具。
继续学习建模相关知识及产线基本知识

本周总结及体会

本周我真正深入了零储这边的业务一线，了解到了现场的很多业务流程和逻辑，也了解到业务与技术对问题的不同思路和理解方式。

我也学习了MySQL的相关知识，加上之前学习的Oracle，对整个数据库的知识有了了解，熟悉了数据库的创建删除增加维护、增删改查等基础操作，做到了这些操作的熟练使用。同时对于数据库的各种高级操作有了一定的认识和理解，在实际需要应用这些知识的时候，可以进行快速的学习和提高。

Meta physika

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
本周周报-20220116

本周工作总结熟悉蔚来库业务流程，熟悉云途系统，与业务进行需求对接调研智慧大屏项目的显示内容及其与业务场景的匹配度辅助进行智慧仓库项目的部分可用性测试本周学习内容一、MySQL数据库简介1.MySQL简介2.MySQL特性3.MySQL与Oracle对比二、SQL语言进阶1. 常见问题及解决1.1 中文字符集问题1.2 web乱码问题1.3校对集问题2.SQL列属性、索引及设计范式3.高级数据操作4.高级查询4.1 基本语法4.2 连接查询
复制链接

扫一扫