mysql相关基础知识

SQL与MySQL

SQL(Structured Query Language)是一种专门与数据库语言, 它提供了一种从数据库中读写数据的简单有效的方法.

SQL优点:

  1. SQL不是特定数据库供应商专有的语言, 几乎所有重要的DBMS都支持SQL, 也即是说, SQL更为通用和广泛.
  2. 语法简单易学, 语句通俗易懂.
  3. 能够进行非常复杂的高级的数据库操作.

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle旗下产品. 下载地址 MySQL: https://dev.mysql.com/downloads/windows/
Navicat Premium是一套数据库开发工具, 通过这个工具可以快速轻松地创建, 管理和维护数据库. 下载地址 Navicat Premium: https://www.navicat.com.cn/download/navicat-premium

安装好MySQL后会发现有四个自带的数据库: information_schema, mysql, performance_schema, sys, 简单介绍:

db desc
information_schema 提供了对数据库元数据的访问、关于MySQL服务器的信息, 如数据库或表的名称,列的数据类型或访问特权.
msyql 是核心数据库, 主要负责存储数据库的用户,权限设置,关键字等mysql自己需要使用的控制和管理信息.
performance_schema 主要用于收集数据库服务器性能参数.
sys 所有的数据源来自performance_schema, 目标是把performance_schema的复杂度降低, 让DBA能更好的阅读这个库里的内容.

数据库基础概念

数据库: 保存有组织的数据的容器, 其内容以表为单位, 一个数据库中会有很多表, 表之间会存在某些关联.
: 某种特定类型数据的结构化清单, 一个表中会有很多字段, 不同字段不同类型, 标识不同的数据.
记录: 填充表结构中的字段, 将其纳入表中, 即为表的一个记录.
主键: 担当一条记录的唯一标识(存在且唯一, 不可改变), 一个表中理应至少需要一个主键, 以便管理和操作数据.
外键: 外边是表中的一个字段, 但其值必须在另一张表里充当主键.

数据类型
在设计表时应该特别重视所用的数据类型, 错误的数据类型可能会严重影响应用程序的功能和性能.
串数据类型: 如果数值是计算中使用的数据, 则存储在数值数据类型中, 如果作为有数字的字符串则应该保存在字符串数据类型中.

数据类型 描述
CHAR 1~255个字符的定长字符串, 它的长度必须在创建时规定
NCHAR CHAR的特殊形式, 用来支持多字节或Unicode字符
TEXT 可变长文本
NVARCHAR TEXT的特殊形式, 用来支持多字节或Unicode字符

数值类型数据

数据类型 描述
BIT 单个二进制位, 0/1
DECIMAL 定点或精度可变的浮点值
FLOAT 浮点值
INT 4字节整数值
REAL 4字节浮点值
SMALLINT 2字节整数值
TINYINT 1字节整数值

日期和时间数据类型: 需要特别注意日期格式问题

数据类型 描述
DATA 日期值
DATATIME 日期时间值
SMALLDATETIME 时间日期值, 精确到分
TIME 时间值

二进制数据类型: 二进制数据类型包罗万象, 可以使二进制信息, 图像, 多媒体等

数据类型 描述
BINARY 定长二进制数据, 最大长度从255到8000字节
LONGRAW 定长二进制数据, 最长2GB
RAW 定长二进制数据, 最多255字节
VARBINARY 变长二进制数据, 最大长度在255到8000之间变动

SQL实践

对数据库的所有操作都可以在Navicat Premium点击完成, 它会生成SQL代码来执行, 点击操作不便描述, 并且了解代码写法更为重要, 所以下面对一些常用的操作的SQL代码做一个总结.

创建操作

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
-- 创建用户'zx'密码为'captzx', 并赋予增删查改权限
CREATE USER `zx`@`localhost` IDENTIFIED BY 'captzx';
GRANT Insert, Delete, Select, Update ON *.* TO `zx`@`localhost`;

-- 创建名为'testdb'数据库
CREATE DATABASE `testdb`;

-- 在数据库'testdb'中创建表'person', 字段如下, 其中设置字段'id'为主键
CREATE TABLE `testdb`.`person` (
`id` int(0) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(0) NOT NULL,
`sex` bit(1) NOT NULL,
`tel` varchar(255) NOT NULL,
`address` varchar(255) NULL,
PRIMARY KEY (`id`)
);
-- 在数据库'testdb'中创建表'school', 字段如下, 其中设置字段'id'为主键
CREATE TABLE `testdb`.`person` (
`id` int(0) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(0) NOT NULL DEFAULT 1, -- 默认值
`location` varchar(255) NULL,
PRIMARY KEY (`id`)
);

-- 谨慎的更新表
ALTER TABLE person ADD country CHAR(10) -- 为表person添加country字段
-- 谨慎的删除表
DROP TABLE person

插入数据

1
2
3
4
5
6
7
INSERT INTO `testdb`.`person`(`id`, `name`, `age`, `sex`, `tel`, `address`) VALUES (1, 'zx', 18, b'1', '152********', 'sc')
INSERT INTO `testdb`.`person`(`id`, `name`, `age`, `sex`, `tel`, `address`) VALUES (2, 'gy', 18, b'0', '152********', 'sc') -- 向person表中插入两条数据

INSERT INTO `testdb`.`school`(`id`, `name`, `age`, `location`) VALUES (1, 'cdu', 76, 'cdsl')
INSERT INTO `testdb`.`school`(`id`, `name`, `age`, `location`) VALUES (2, 'cju', 33, 'cdlq') --向person表中插入两条数据

INSERT INTO `testdb`.`school`(`name`, `age`, `location`) SELECT `name`, `age`, `location` FROM testdb.school -- 从别的表中导出输入到当前表, 注意主键重复问题

更新和删除表中的数据

UPDATE和DELETE时注意事项:

  1. 除非确实打算更新和删除每一行, 否则绝对不要使用不打WHERE子句的UPDATE或DELETE语句
  2. 保证每个键都有主键, 尽可能像WHERE子句那样使用它.
  3. 在UPDATE和DELETE语句使用WHERE子句前, 应该先用SELECT进行测试, 保证它过滤的是正确的记录吗, 以防编写的WHERE子句不正确
  4. 使用强制实施引用完整性的数据库, 这样DBMS将不运行删除其数据与其他表相关联的行.
  5. 可以的话, 使用DBMS允许数据库管理员施加约束, 防止执行不带WHERE子句的UPDATE或DELETE语句.
1
2
UPDATE school SET school.`name` = 'scu' WHERE school.id = 3 -- 更新school中id为3的'name'为'scu'
UPDATE school SET school.`name` = 'ecu', school.`age` = '123' WHERE school.id = 4 -- 更改多个值
1
DELETE FROM school WHERE school.`name` = 'ecu' -- 删除表school中'name'为'ecu'的数据

检索表中的字段

1
2
3
4
5
6
7
8
SELECT * FROM testdb.person -- 检索表中的所有数据
SELECT person.`name` FROM testdb.person -- 检索表'name'字段
SELECT DISTINCT person.`age` FROM testdb.person -- DISTANCT关键字表示检索的数据只返回不同的值
SELECT * FROM testdb.person WHERE person.`age` = 18 LIMIT 1 -- LIMIT限制检索的数量, LIMIT 1表示检索排最前的那一条记录
SELECT * FROM testdb.person LIMIT 1 OFFSET 1 -- OFFSET表示检索向后偏移, 此处表示从第1条记录开始, 检索一条记录, 此处可简写为LIMIT 1,1
SELECT * FROM testdb.person ORDER BY person.`name` -- ORDER BY排序检索的数据, 此处表示按字段'name'来排序, 排序参数是可多选的, 但排序优先级按出现顺序一次降低
SELECT person.`id`, person.`tel`, person.`name` FROM testdb.person ORDER BY 3 -- 依据检索字段的相对位置来排序, 此处表示按字段'name'来排序
SELECT * FROM testdb.person ORDER BY person.`name` DESC, person.`id` -- DESC表示降序, 此处为按字段'name'降序, 'id'升序检索

过滤表中的字段

1
2
3
4
5
6
7
8
SELECT * FROM testdb.person WHERE person.`name` = 'zx'
SELECT * FROM testdb.person WHERE person.`address` IS NULL
SELECT * FROM testdb.person WHERE person.`name` = 'zx' AND person.`age` BETWEEN 15 AND 20 -- AND/OR
SELECT * FROM testdb.person WHERE person.`name`IN ('zx') -- IN
SELECT * FROM testdb.person WHERE NOT person.`name`IN ('zx') -- NOT

SELECT person.`age` , COUNT(*) AS avg_count FROM testdb.person GROUP BY person.`age` HAVING count(*) >= 2 -- HAVING操作过滤
-- HAVING和WHERE的不同之处: WHERE在数据分组前进行过滤, HAVING在数据分组后进行过滤. 这是一个重要的区别, WHERE排除的行不包括在分组中. 这可能会改变计算值, 从而影响HAVING子句中基于这些值过滤掉的分组.

通配符

使用通配符注意事项:

  1. 不要过度使用通配符, 如果其他操作符能达到目的, 应该使用其他操作符.
  2. 在确实需要使用通配符时, 尽量不要把它们用在搜索模式的开始处, 因为这样做效率是最低的.
  3. 仔细注意通配符的位置, 不要放错了位置.
1
2
3
SELECT * FROM testdb.person WHERE person.`tel` LIKE '152%' -- 通配符%, 表示可以匹配任意长度的字符
SELECT * FROM testdb.person WHERE person.`tel` LIKE '152_' -- 通配符_, 表示仅能匹配一个字符
SELECT * FROM testdb.person WHERE person.`tel` LIKE '[z]' -- 通配符_, 表示仅能匹配一个字符

拼接字段

1
SELECT CONCAT(person.`tel`, '(',  person.`address` ,')') AS person_info FROM testdb.person -- CONCAT拼接函数, AS设置别名

SQL中的函数

不同DBMS中函数的定义和名称很可能并不相同, 所以SQL语句在这方面表现为不可移植的, 如果使用了函数最好写清楚注释
大多数SQL实现支持以下类型的函数:

  1. 用于处理文本字符串(转换大小写等)的文本函数.
  2. 用于在数字数据上进行算术操作(如绝对值, 代数运算等)的数值函数.
  3. 用于处理日期和时间并从这些值中提取特定成分(日期做差, 日期有效性等)的日期和时间函数.
  4. 返回DBMS正在使用的特殊信息(如返回用户登录信息)的系统函数.
1
2
SELECT UPPER(person.`name`) AS name_upper FROM testdb.person WHERE person.`name` = 'zx'
-- 更多函数就不列举了, 遇到可能要使用这几个方面的函数就看文档去吧

集合函数, 它们都会自动忽略NULL

1
2
3
4
SELECT AVG(person.`age`) AS avg_age FROM testdb.person -- AVG取平均
SELECT COUNT(*) AS count_age18 FROM testdb.person where person.`age` = 18 -- COUNT统计检索出来的记录数量
SELECT MAX(person.`age`) AS max_age FROM testdb.person -- MAX去最大值, MIN取最小值
SELECT SUM(person.`age`) AS sum_age FROM testdb.person -- SUM求和

分组

1
SELECT person.`age` , COUNT(person.`age`) AS avg_count FROM testdb.person GROUP BY person.`age` -- GROUP BY不同的'age'将被分组, 在统计不同'age'的数量

数据检索, 子句过滤

SELECT子句及其顺序

子句 说明 是否必须使用
SELECT 要返回的字段或表达式
FROM 从中检索数据的表 仅在从表中选择数据时使用
WHERE 行级过滤
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤
ORDER BY 输出排序顺序
1
2
3
4
5
6
SELECT
person.`name`
FROM
testdb.person
WHERE
person.`id` IN ( SELECT person.`id` FROM testdb.person WHERE person.`age` / 18 = 1 )

联结表

笛卡尔积: 由没有联结条件的表关系返回的结果为笛卡尔积, 检索处的行数目将是第一个表中的行数乘以第二个表中的函数.
DBMS在运行时关联指定的每个表, 以处理联结. 这种处理可能非常耗费资源, 因此应该注意, 不要联结不必要的表, 联结的表越多, 性能下降的越厉害.

1
2
3
4
5
6
7
8
9
10
SELECT person.`name`AS person_name,school.`name` AS school_name FROM testdb.person, testdb.school -- person表和school联结, 但此时得到的结果是纯粹的笛卡尔积, 没有逻辑依据的强行联结在一起
SELECT person.`name`AS person_name,school.`name` AS school_name FROM testdb.person, testdb.school WHERE person.`school_id` = school.`id` -- 使用WHERE过滤
SELECT person.`name`AS person_name,school.`name` AS school_name FROM testdb.person INNER JOIN testdb.school ON person.`school_id` = school.`id` -- 使用INNER JOIN内联结, 实际效果和上面使用WHERE一样

SELECT person.`name`, person.tel FROM testdb.person WHERE person.`name` IN (SELECT person.`name` FROM testdb.person WHERE person.`address` = 'sc') -- 自连接子句写法
SELECT Person1.`name`, Person1.tel FROM testdb.person AS Person1, testdb.person AS Person2 WHERE Person1.`name` = Person2.`name` AND Person1.address = 'sc' -- 自连接别名写法
-- 自连接通常作为外部链接, 用来替代从相同表中检索数据的使用子查询语句, 虽然最终的结果是相同的, 但许多DBMS处理联结远比处理子查询快得多, 应该测试两种方式, 然后确实使用性能更好的.

-- 外连接
-- 全外连接

组合查询

1
2
3
4
5
6
7
SELECT person.`name`, person.school_id FROM testdb.person WHERE person.`name` = 'zx'
UNION
SELECT person.`name`, person.school_id FROM testdb.person WHERE person.`name` = 'gy' -- UNION将结果组合

SELECT person.`name`, person.school_id FROM testdb.person WHERE person.`name` = 'zx'
UNION ALL
SELECT person.`name`, person.school_id FROM testdb.person WHERE person.`name` = 'gy' -- UNION在组合时会将重复项消除, UNION ALL将全部组合

视图

什么是视图?
为什么使用视图?

重用SQL语句
简化复杂的SQL操作, 在编写查询后, 可以方便的使用它而不需要知道其中细节.
使用表的一部分而不是整个表.
保护数据, 可以授予用于访问表的特定部分的权限, 而不是整个表的访问权限.
更改数据格式和表示, 视图可返回与底层表的表示与不同格式的数据.

1
2
3
4
5
6
7
8
-- 创建视图
CREATE VIEW FUNC AS
SELECT person.`name` AS PersonName, person.school_id, school.`name` AS SchoolName
FROM person, school
WHERE person.school_id = school.id
AND school.location LIKE'cd%' -- 创建视图FUNC

SELECT PersonName, SchoolName FROM FUNC WHERE PersonName = 'zx' -- 使用视图FUNC

存储过程

什么是存储过程?
为什么要使用存储过程?

通过把处理封装在一个易用的单元中, 可以简化复杂的操作.
不要求重复建立一系列处理步骤, 因为保证了数据的一致性, 防止出错.
通过修改存储过程, 来简化对变动的管理.
存储过程通常以编译过的形式存储, 所以DBMS处理命令的工作较少, 提高了性能.
存在一些职能在单个请求中的SQL元素和特性, 存储过程可以使用它们来编写功能更强更灵活的代码.
使用存储过程上的一些缺点:
很难编写可移植的存储过程.
一般来说编写存储过程比编写基本SQL语句复杂, 需要更高的技能, 更丰富的经验.

事务处理

事务处理是一种机制, 用来管理必须成批执行的SQL操作, 确保操作要么完全执行, 要么完全不执行, 以此来维护数据库的完整性.
相关术语:

事务(transaction)指一组SQL语句.
回退(rollback)指撤销指定SQL语句的过程.
提交(commit)指将未存储的SQL语句结果写入数据库表中.
保留点(savepoint)指事务处理中设置的临时占位符, 可以对它发布回退.
事务可以用来管理INSERT UPDATE和DELETE语句, 不能回退SELECT语句, 已不能回退CREATE或DROP操作.

游标(cursor)

游标是一个存储在DBMS服务器上的数据库查询, 它不是一条SELECT语句, 而是被该语句检索出来的结果集. 在存储了游标之后, 应用程序可以根据需要滚动或浏览其中的数据.
使用游标的步骤:

在使用游标前, 必须声明它, 这个过程实际上没有检索数据, 它只是定义要使用的SELECT语句和游标选项.
一旦声明, 就必须打开游标以供使用, 这个过程用前面定义的SELECT语句把数据实际检索出来.
对于填有数据的游标, 根据需要取出记录.
在结束游标使用时, 必须关闭游标, 可以的话, 释放游标.
关于存储过程, 事务处理, 游标等在业务需要时在做探讨, 此处不便深究.