数据库及SQLMySQL基础
jie
2020-10-16
响应和提供数据 存储数据 MySQL基础 数据库概述 Java应用于数据库的关系 安装MySQL 删除MySQL MySQL启动(Window) 关系结构模型数据库 使用二维表格来存储数据 常见数据库 Oracle(甲骨文) DB2(IBM) SQL Server(微软) Sybase(赛尔斯) MySQL(甲骨文) 理解数据库 数据库泛指"关系型数据库管理系统"(RDBMS : Relation database management system) 应用程序 操作和查询 数据库服务器 数据库 net start mysql net stop mysql MySQL服务端登录 mysqld -h 主机名 -u 用户名 -p 本机 : mysqld -u root -p MySQL安装路径及配置信息 bin目录下 开启关闭服务器及登录退出客户端 SQL语言概述 mysqld.exe是服务端执行程序 mysql.exe是客户端执行程序 MySQL客户端登录 mysql -u用户名 -p密码 -h主机名 本机 : mysql -u用户名 -p密码 -h localhost 退出 exit和quit 结构化查询语言(Structured Query Language) SQL作用 客户端使用SQL来操作服务器 启动mysql.exe连接服务器后, 就可以使用sql来操作服务器了 将来会使用Java程序连接服务器, 然后用sql操作 SQL 标准 有国际标准化组织(ISO)制定的, 对DBMS的统一操作方式(如mysql, oracle等) 注意 某种DBMS不会只支持SQL标准, 也会有自己的语法 SQL语法 SQL语句可以在单行或多行书写, 以分号结尾 可使用空格和缩进来增强语句的可读性, 换行符, 空格, tab 在SQL中不起作用 MySQL不区分大小写, 建议使用大写 SQL语句分类 DDL(Data Definition Language) DML(Data Manipulation Language) DCL(Data Control Language) DQL(Data Query Language) 数据定义语言, 用来定义数据库对象 : 库、表、列等 数据库或表的结构操作(创建. 删除, 修改) 数据操作语言,用来定义数据库记录(数据) 数据控制语言, 用来定义访问权限和安全级别 数据查询语言, 用来查询记录(数据) 对表的记录进行更新(增, 删, 改) alter user user() identified by "root"; s 官网 MySQL Workbench 图形化界面更方便管理数据库 USE 数据库名称 ; 选中的数据库在SCHEMAS [ˈskiːməz](模式) 中会以粗体显示 也可以双击要选择的数据库 SELECT 列名称, 列名称 FROM 表名称 ; SELECT * FROM 表名称 ; 检索所有列 WHERE 表达式(以列名称做比较) ; 获取指定列里是特定值的行数据 ORDER BY 子句 让指定列 升序排列 2.选择语句 注意 : 这些命令的顺序很重要, 不对会报错 列名称可接算数表达式 SELECT 列名称, 列名称+2 例 : SELECT last_name, points, points * 3 非数字的字符会变成数字, 数字中有其他数据类型会只保留数字, 然后运算 SELECT 列名称, 列名称+2 AS 新的列名称 赋予新的列名称 列名称例有空格需要加" "或' ' 说明 SELECT DISTINCT 列名称 获得没有值重复的清单 比较 : >, >=, <, <=, =, !=, <> 除了数字, 还可以比较字符串, 日期等 逻辑 : AND( && ), OR( || ), NOT( ! ) IN运算符 WHERE 列名称 IN (值1, 值2, ...) 等同于: WHERE 列名称 = 值1 OR 列名称 = 值2 ... 3.多张表格中检索数据 内连接(一个数据库里) INNER JOIN (INNER可写可不写) BETWEEN运算符 WHERE points BETWEEN 1000 AND 3000 -- 显示points列里1000到3000的值 LIKE运算符 WHERE last_name LIKE 'b%' -- 显示last_name列中 字符以'b'开头的值, 也可以是其他组合, 如'%b%'等等 ' % ' : 表示任意字符数 ' _ ' : 表示一个字符 REGEXP(正则表达式,Regular Expression) ' ^ ' : 表示字符串开头 ' $ ' : 表示字符串结尾 ' | ' : 相当于或 WHERE last_name REGEXP '^m|f|[gim]e' ; -- 显示last_name列里 字符以'm'开头或 字符里包含'f'的 ' [ ] ' : 例, [gim]e, 方括号里的任意字符都可以出现在e前, 或 [a-h]e, 表a到h范围的 字符可在e前 IS NULL运算符 WHERE phone IS NULL -- 显示phone列里值是NULL的 关系型数据库里, 每个表里都有一个主键列, 主键列是默认排序的 ORDER BY 列名称 ORDER BY 列名称 DESC 降序排列 说明 : ORDER BY不需要SELECT, 可以排列任意列, 例如只SELECT了name, 可以按 birth_date排列 避免使用列位置排列(ORDER BY 1, 2), 如果插入了新的列, 位置会改变 ORDER BY后面可接多个列, 优先排列第一个 ORDER BY后可以是算数表达式 LIMIT 子句 -- 只返回前几行数据 LIMIT 3 LIMIT 6, 9 -- 跳过前6行 获取三行数据 说明: LIMIT要放在最后 SELECT oi.product_id, name, quantity, oi.unit_price FROM order_items oi Join products p ON oi.product_id = p.product_id ; 用o引用order, 减少代码 ambiguous(模棱两可的, 即有存在相同名称列的表)的列前要追加"表名称." 将两表中product_id值一样的合并起来起来 跨数据库连接 USE sql_store ; SELECT * FROM order_items oi JOIN sql_inventory.products p ON oi.product_id = p.product_id -- 将sql_store数据库中的order_items表格与sql_inventory库中的products表格 合并(product_id列相同) 自连接(一个表里) SELECT m.employee_id, m.first_name, e.first_name AS manager FROM employees e JOIN employees m ON e.employee_id = m.reports_to e, m是别名 -- 将员工id 与管理源id合并, 得到每个员工对应的管理员 多表连接 USE sql_store ; SELECT o.order_id, o.order_date, c.first_name, c.last_name, os.order_status_id AS status FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_statuses os ON o.status = os.order_status_id 复合连接条件 SELECT * FROM order_items oi JOIN order_item_notes oin ON oi.order_Id = oin.order_Id AND oi.product_id = oin.product_id 隐式连接语法 SELECT * FROM orders o, customers c WHERE o.customer_id = c.customer_id -- 跟用JOIN多表连接一样的作用, 但是不建议这样用, 如果忘了写WHERE语句, 两 表会交叉连接, 生成很多行(表1行数*表2行数)数据, 外连接 OUTER JOIN LEFT JOIN RIGHT JOIN SELECT c.customer_id, c.first_name, o.order_id FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id 保留左表的所有记录(FROM后的) 保留右表的所有记录 -- 返回所有有订单的客户信息, 如果是RIGHT JOIN, 则返回所有客户信息 多表外连接 建议统一使用左连接 自外连接 USING 子句 与内连接一样, 就是用了LEFT或RIGHT JOIN JOIN customer c ON orders. customer.id = c. customer.id JOIN customer c USING (customers_id) -- 合何列相同的列名称, 等同于 -- 如果表的主键有两个字段(即两列), 可以这样: USING(列1, 列2) 自然连接( NATURAL) 交叉连接( CROSS ) 联合( UNION ) FROM orders o NATURAL JOIN customers c -- 数据库会自动基于共同的列连接, 不用写列名称 -- 不太建议这样用, 没有控制 FROM customers c CROSS JOIN products p -- 合并每一行, 返回的行数有 c行数*p行数 等同于 : FROM customers, order 应用 : 大中小型与颜色的合并 -- 合并多段查询的记录, 查询返回的列数量必须是一样的, 否则会报错 SELECT first_name FROM customers ; UNION SELECT name FROM shippers 6.插入、更新和删除数据 列属性 16.阿里云的教程(备份) 以下是MySQL Workbench 表格设计模式里的描述 Datatype（数据类型） INT VARCHAR(50) 可变字符, 表示这列最多可以有50个字符, 一般存储字符串或文本值 CHAR(50) 会一直占用50个字符的空间 Column(列) 列出所有的列 左边的黄色钥匙 为主键的标注 NN : not NUll(非空) 决定了改列是否可以是空值 PK : primary key(主键) 标注主键 UQ : unique index() BIN : ZF : AI : auto increment(自动递增) G : 通常用在主键列 Default / Expression 标明每列的默认值 插入单行(INSERT) INSERT INTO customs VALUES (值, 值, ...) -- 每个列都要有值, 插入值要对应 该列的数据类型, 也可以是DEFAULT(默认值)和 NULL(如果列允许为空) INSERT INTO customs(列名称, 列名称 ,...) VALUES (值, 值, ...) -- 指定插入那些列, 目的是为了不用填写那些是DEFAULT或NULL的列 插入多行 INSERT INTO shippers (name) VALUES ('Tom'), ('jerry') 整数, 输入小数会取四舍五入的值 插入分层行 USE sql_store; INSERT INTO orders ( customer_id, order_date , status) VALUES (1, '2020-07-05' , 1) ; INSERT INTO order_items VALUES ( last_insert_id() , 1, 1, 2.95), ( last_insert_id() , 2, 1, 2.95) -- last_insert_id() : 上次主键的插入值 创建表复制(CREATE TABLE ) CREATE TABLE orders_archived AS SELECT * FROM orders -- 创建orders的复制, 名称为orders_archived, 这样创建的表格默认没有标注 PK(主键),AI(自动排序) 练习 : 注意 : 判断不为空要用 WHERE phone IS NOT NULL, 不能用 != NULL 更新单行( UPDATE ) UPDATE invoices SET payment_total = invoice_total*50, payment_date = '2020-07-05' WHERE invoice_id = 1 -- WHERE invoice_id in (3, 5) 更新3和5行 -- 接选择语句, 省略这行即更改所有 在UPDATE中用子查询语句 子查询 是在另一段SQL语句里的选择语句 删除行(DELETE) DELETE FROM invoices WHERE invoice_id = 1 -- 可接子查询语句(选择语句) 恢复数据库(Restoring) 教程里是用他提供的代码重建数据库, 并没有恢复... 4.汇总数据 取系列值并聚合它们,导出一个单一值, 所以叫聚合函数, 例如 max()函数取最大值。 GROUP BY 子句 HAVING 子句 ROLLUP 运算符 聚合函数(Aggregate Function) SELECT MAX (invoice_total) AS highest, MIN (invoice_total) AS lowest, AVG (invoice_total) AS avgrage, SUM (invoice_total * 0.8) AS total, COUNT (*) AS number_of_invoice, FROM invoices WHERE invoice_date > '2020-07-05' COUNT ( DISTINCT client_id) AS total_records -- client_id有重复， 查询client_id唯一的记录数 -- 没有分组时聚合列的所有值 1.准备 5.编写复杂查询 介绍 子查询 SELECT子句的子查询 FROM子句的子查询 7.MySQL的基本函数 数值函数 字符串函数 MySQL中的日期函数 格式化日期和时间 计算日期和时间 IFNULL 和 COALESE 函数 IF函数 CASE运算符 8.视图(Views) 创建视图 更新或删除视图 可更新视图 WITH OPTION CHECK子句 视图的其他优点 9.存储过程 什么是存储过程 创建一个存储过程 使用MySQL工作台创建存储过程 删除存储过程 参数 带默认值的参数 参数验证 输出参数 变量 函数 其他约定 10.触发器和事件 触发器 查看触发器 删除触发器 使用触发器进行审计 事件 查看、删除和更改事件 11.事务和并发 12.数据类型 13.设计数据库 14.高效的索引 15.保护数据库 事务 创建事务 并发和锁定 并发问题 事务隔离级别 死锁 介绍 字符串类型 整数类型 定点数类型和浮点数类型 布尔类型 枚举和集合类型 日期和时间类型 二进制大对象类型 JSON类型 介绍 数据建模 概念模型 逻辑模型 实体模型 主键 外键 外键约束 标准化 第一范式 连接表(建立多对多关系) 第二范式 第三范式 建议 不要对什么都建模, 根据眼前的需求的创建最佳方案, 不要过多考虑未来 5.反向搭建数据库(由表建模) 项目：航班订票系统 解答：概念模型 解答：逻辑模型 项目：视频租赁应用 解答：概念模型 解答：逻辑模型 创建和删除数据库 创建表 更改表 创建关系 更改主键和外键约束 字符集(Charset)和排序规则(Collation) 存储引擎 索引(indexes) 创建删除索引 看索引 前缀索引 全文索引 组合索引 组合索引的列顺序 索引无效时 使用索引排序 覆盖索引 维护索引 介绍 创建一个用户 查看用户 删除用户 修改密码 权限许可 查看权限 撤销权限 用来筛选分组后的数据 跟WHERE区别：HAVING筛选的列必须在SELECT选择里 例 ： 分组 -- 把聚合的组加起来， 有多个分组时可以的每个组的聚合值整个结果值 SELECT client_id, SUM (invoice_total) AS total_sales FROM invoices i GROUP BY client_id -- 作用， 把client_id相同的invoice_total加起来， 即分组 USE sql_invoicing; SELECT i.client_id, c.city, SUM(invoice_total) AS total_sales FROM invoices i JOIN clients c USING (client_id) GROUP BY client_id, c.city WITH ROLLUP 注意 ：ROLLUP只在MySQL中有， 不是标准的SQL语言，其他数据库也会有类似 功能的命令） USE sql_hr; SELECT first_name, last_name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ) Manipulation [məˌnɪpjəˈleɪʃən] (操作) 在SELECT里用 子查询 例 SELECT里子查询 (....AS 列名称)生成的列不能直接用, 要(SELECT 列名称) 例 后面会用视图保存FROM子句里的内容 ROUND SELECT ROUND(5.88) SELECT ROUND(5.88, 1) 保留1位小数做四舍五入 四舍五入保留整数 TRUNCATE SELECT TRUNCATE(5.577, 2) 保留2位小数 truncate [ˈtrʌŋkeɪt] 截断 CEILING SELECT CEILING(5.577) 返回大于或等于这个数的最小整数 ceiling [ˈsiːlɪŋ] 上限 FLOOR SELECT FLOOR(5.888) 返回小于或等于这个数的最小整数 ABS SELECT ABS(-5.888) 返回这个数的绝对值 RAND SELECT RAND() 生成0~1的随机浮点数 ...... 更多 LENGTH SELECT LENGTH("asdfg"); 返回字符串长度 UPPER SELECT UPPER("asdfg") 转大写字母 LOWER SELECT LOWER("ASDF"); 转小写字母 LTRIM SELECT LTRIM(" \nJerry"); 清除左侧空白或其他转移字符 left trim(修剪) RTRIM TRIM 清除空白字符 LEFT SELECT LEFT("KindJerry", 4); 得到前四个字符 RIGHT 得到右侧5个字符 SELECT RIGHT("KindJerry", 4); SUBSTRING SELECT SUBSTRING("KindJerry", 2, 4); 从第2个字符开始取4个字符 SELECT SUBSTRING("KindJerry", 2); 字符串分割 从第2个字符开始取所有字符 LOCATE SELECT LOCATE("d", "KindJerry"); 返回字母"d"的下标位置, 不区分大小写, 找不到返回0 CONCAT concatenate(使连接) SELECT CONCAT("Hello", "KindJerry"); 连接字符串 USE sql_store; SELECT CONCAT(first_name, " ", last_name ) AS name FROM customers ...... 更多 NOW() 当前日期时间 CURDATE() 返回日期(年月日) CURTIME() 返回时间(时分秒) SELECT YEAR(NOW()) ; 获取年份, 同样的 : MONTH, DAY, HOUR, MINUTE, SECOND 其他 : MONTHNAME, DAYNAME EXTRACT函数 SQL语言 extract [ˈekstrækt] 摘录, 提取 SELECT EXTRACT(DAY FROM NOW()); 输出日 DATE_FORMAT(NOW(), "%M %d %Y") 说明符 一般大写显示英文名或完整, 小写显示数字或省略缩写 TIME_FORMAT(NOW(), "%H:%i:%s %p"); 大写H 24小时制, 小写h 12小时制 DATE_ADD (NOW(), INTERVAL -1 YEAR) interval [ˈɪntərvl] 间隔 当前时间减一年 DATE_SUB (NOW(), INTERVAL 1 YEAR) 当前时间减去一年 SELECT DATEDIFF ("2019-01-03", "2019-01-01"); 返回日期的天数间隔(没有时分) SELECT TIME_TO_SEC ("01:00"); 返回秒数 USE sql_store; SELECT order_id, IFNULL (shipper_id, "......"), COALESCE (shipper_id, comments, "Not Assigned") FROM orders IFNULL: 用"......"替换掉comments里的空值 COALESCE : 替换后面一堆值里的第一个非空值(例中替换comments值, 如为null, 替换Not Assigned) coalesce [ˌkoʊəˈles] 合并;联合; IF(表达式, 为true时返回的值, 为false时返回的值) USE sql_store; SELECT order_id, order_date, IF (YEAR(order_date) > YEAR(NOW()), "Active", "Archived") FROM orders CASE WHEN 表达式 返回值 WHEN 表达式 返回值 ELSE 返回值 END AS 表名称 CREATE VIEW 名称 AS 返回表格数据的代码 删除 DROP VIEW 视图名称 重建视图 CREATE OR REPLACE VIEW 名称 AS 返回表格数据的代码 常常把视图保存成sql文件 没有DINSTINCT 条件 没有聚合函数(MIN, MAX, SUM, ......) 没有GROUP BY / HAVING 没有UNION 可以在INSERT, UPDATE和DELETE语句中使用这类视图 通过视图更新或删除数据时, 一些行可能会消失 例 : 例 简化你的查询 减少改动带来的影响 加强数据的安全性 ???? 在代码里写SQL语句会让代码混乱且难以维护, 要把SQL语句存在数据库里 (Stored Procedure ) 一组为了完成特定功能的SQL 语句集，它存储在数据库中， 一次编译后永久有效 DELIMITER : 改变分隔符, 用完之后要改回分号, Mysql要需要, 其他DBMS可能不 需要 C#, Java, Python等语言通过Stored Procedure(存储过程)调用数据库 例 : DELIMITER $$ CREATE PROCEDURE 名称() BEGIN SQL语句 ; END$$ DELIMITER ; 调用 : CALL 名称(); Stored Procedures右键 点击Create... 不用再自己写分隔符, 自动生成的名称用的反单引号` `避免名称重复 Apply(应用), Revert(撤销) DROP PROCEDURE 名称 删除不存在的数据库会出错, 用IF EXISTS避免这种情况 DROP PROCEDURE IF EXISTS 名称 基本架构 SQL语句只有一行也要加分号, Mysql规定的 例 格式 : 参数名 数据类型 CHAR不带()默认长度1 INT(4个字节), TINYINT(1个字节, 范围0~255) SQL语句里要加WHERE或其他运用参数 如果IF后面有大于1条的执行语句, 要加 END IF 参数必须要被赋值, 就算没有传参也要写NULL填充 IF 表达式 THEN ELSE END IF; 例 加个IF 判断, 参数为NULL, 设置值或者做其他操作 传入NULL, 显示所有 例 传入NULL时设置参数为"WV" 例 传入client_id和payment id IF payment_amount <= 0 THEN SIGNAL SQLSTATE '22003' SET MESSAGE_TEXT = "错误的输入"; 22003是错误代码, 可以百度 原 : 输入顾客id, 输出未支付订单(订单数和总额) 加了输出参数的 定义 : SET @变量名 = 0 用户或会话变量 本地变量 在Mysql关闭时会被清除 SET GLOBAL 变量名 或者 set @@global.变量名 全局变量 在存储过程和函数中通过 DECLARE 定义变量在BEGIN...END中 总结 基本关键字以及执行先后循序 select 查询列表 from 表 连接类型 join 表2 where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表 limit 偏移, 条目数 放在where或having后 IN运算符 子查询vs连接 ALL关键字 ANY关键字 相关子查询 EXISTS运算符 USE sql_store; SELECT * FROM products WHERE product_id NOT IN ( SELECT DISTINCT product_id FROM order_items ) USE sql_store; SELECT DISTINCT customer_id, first_name FROM customers JOIN orders o USING (customer_id) JOIN order_items oi USING (order_id) WHERE oi.product_id = 3 用JOIN更加易读, 明确了orders , customers和order_items的关联, 表面要查什么 USE sql_invoicing; SELECT * FROM invoices WHERE invoice_total > ALL ( SELECT invoice_total FROM invoices WHERE client_id = 3 ) 原理 : 一 一比对每一个ALL(元素, 元素,....)里的元素 返回大于子查询语句里返回的所有值 返回子查询里包含的内容 返回子查询里包含的任意内容, 可等效于IN关键字 子查询和外查询存在关联性,子查询返回的值会在主查询的每一行上执行 返回工资大于所属部门(office_id)平均值的员工 USE sql_hr; SELECT * FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE office_id = e.office_id ) 非关联子查询 以上写的都是非关联子查询, 子查询返回的一系列值赋值给主查询使用 子查询 关联子查询 注意 关联子查询执行很慢 EXISTS效率更高, 返回true或false, 不像IN查询返回一个较大的结果集, USE sql_store; SELECT * FROM products p WHERE NOT EXISTS ( SELECT product_id FROM order_items WHERE product_id = p.product_id ) 子主题 视图简介 Mysql从5.0.1开始提供的功能, 一种虚拟存在的表, 只保存了sql逻辑语句, 不保存查 询结果, 数据是在使用视图时动态生成的 与存储过程相比, 函数只能返回单一值, 明确了返回值的数据类型 属性 deterministic (确定性)确定输入参数和返回值 reads sql data 读取数据 modifies sql data 修改数据 要做什么操作需要在begin前添加该属性 returns integer 返回int类型数据 例 调用函数 命名规范问题 : get_risk_factor, getRiskFactor, fnGetRiskFactor 有不同的习惯, 遵循项目原本的命名规范就好 简介 trigger(触发), 触发器是在插入,更新和删除语句前后自动执行的一堆SQL代码 创建触发器 该触发器关联到付款表, 在插入一条记录后触发 命名习惯 : 表面_after(before)_操作 关键字 NEW 返回更新后(插入)数据 OLD 返回更新前(删除)的数据 SHOW TRIGGERS 例 : 查看与payment表有关的触发器 SHOW TRIGGERS LIKE 'payments%' DROP TRIGGER IF EXISTS 触发器名 建议放在创建触发器前面, 先删除后创建 触发器的另一个常见用途是记录对数据库的修改 : 创建一个表用于记录对数据库的 操作信息, 在触发器里INSERT 根据计划执行的一堆SQL代码, 用events可以实现自动化维护数据库, 如删除过期的 信息, 把表数据复制到存档表或汇总数据生成报告 例 查看事件 SHOW EVENTS 删除事件 SHOW EVENTS LIKE 'yearly' 查看以yearly开头的事件 DROP EVENT IF EXISTS 事件名 更改事件 ALTER EVENT 事件名 DISABLE 暂时禁用事件 ALTER EVENT 事件名 ENABLE 启用事件 事务代表某个工作单元的一组SQL语句, 将两个不同功能的SQL语句合为一个事务, 只能同时操作成功或失败(例如转账, 要减少余额, 一边要增加余额) 属性(ACID properties) 原子性(Atomicity) 每个事务都是一个工作单元, 不管有多少语句, 要么全部成功执行, 要么所有更改被 撤销 一致性(Consistency) 数据库始终保持一致, 不会出现有订单没有项目的情况 隔离性(Isolation) 不同事务间不会相互干扰, 一次只有一个事务能更新表 持久性(Durability) 事务产生的更改时永久的 例 可以用Query→Execute Current Statement 执行一段语句再断开 来测试 START TRANSACTION (SQL语句) COMMIT ; 用 ROLLBACK 代替COMMIT可以撤销更改, 用于检查错误 两个或更多用户同时访问一个数据库的情况就是并发 并发 锁 系统默认一个事务在执行更新时会放一个锁, 其他事务无法执行修改 如果两个事务没有上锁, 较晚提交的会覆盖较早提交的数据 脏读(dirty reads) 一个事务读取了尚未被提交的数据(ROLLBACK撤销修改了) 解决 : 为事务建立隔离级别. 这样事务创建的数据不会立即被其他事务读取, 除非已 经提交(COMMIT) 隔离级别 读已提交(READ COMMITTED) 可重复读取(REPEATABLE READ) 只能读取已提交的数据 序列化( SERIALIZABLE ) 如果其他事务在更新要查询的数据, 必须要等它更新完才能查询, 即事务按顺序执行 注意 用户和事务越多, 执行速度会越慢, 隔离级别会损失性能和可拓展性, 尽量不要用除 非真的有必要 读未提交(READ UNCOMMITTED) 幻读(phantom reads) 性能最高, 因为未设置锁, 不关注并发问题 解决幻读, 没有并发问题, 但性能最差 默认情况使用 , 可以解决除幻读(phantom reads)外的其他并发问题 查看隔离 SHOW VARIABLES LIKE 'transaction_isolation' 设置隔离级别 为下一个事务设置 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 为当前会话和以后所有事务设置隔离级别(当前事务不会受到影响) SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 为所有新事物设置全局隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; 更新(UPDATE)会锁定记录, 其他事务执行会报错(死锁), 必须等它更新完才能执行, 这与隔离级别无关 减少死锁 更新多条记录时遵循相同的顺序 简化事务, 缩小事务运行时长 尽量避开高峰期运行事务 CHAR(x) VARCHAR(x) MEDIUMTEXT 最大 : 16MB 最大 : 65535字节(~64KB) 存储固定长度字符串, 存储范围内长度字符串超过长度会被截断, 如用户名, 邮箱, 地址等, 习惯长度设置为 50或255 中文本, 可用于存储JSON对象, SCV字符串和中长的文本书 LONGTEXT 最大 : 4GB 可用于存储书或多年的日志文件等 TINYTEXT 最大 : 255 bytes TEXT 最大 : 64KB 其他 一个中文符号占3个字节, 如果长度设置10, 会预留30个字节空间 TINYINT 占用 : 1b , 范围 : [-128, 127] UNSIGNED TINYINT 占用 : 1b , 范围 : [0, 255] SMALLINT 占用 : 2b , 范围 : [-32K, 32K] MEDIUMINT 占用 : 3b , 范围 : [-8M, 8M] INT 占用 : 4B , 范围 : [-2B, 2B] BIGINT 占用 : 8b , 范围 : [-9Z, 9Z] 分类 : 字符串类型, 数字类型, 日期与时间类型, 二进制类型, 空间类型(地理位置) DECIMAL(p, s) 小数, p: 总位数[1, 65], s: 小数点后保留位数 小数还有 : DEC, NUMERIC, FIXED 小数使用这个就足够了 FLOAT DOUBLE 占用 : 4b 占用 : 8b TURE, FALSE 也可以写成1, 0 , 建议写TRUE, FALSE更直观 枚举(ENUM) 格式 : ENUM('值1', '值2', '值3') 作用 : 限制插入的值是定义好的, 插入其他的会报错 注意 枚举很不方便, 要修改枚举的值, Mysql要重建整个表, 应避免使用 更好的方式是创建另一个保存值(如大中小)表(叫查找表), 并且可以添加这些值的精 确值,且可以复用 集合(SET) 与枚举类似, 同样也会造成很多问题 DATE TIME DATETIME TIMESTAMP YEAR 时间戳, 经常用于记录数据插入和最后修改的时间 占用 : 4b, 最大到2038年 占用 : 8b 保存日期(年月日) 保存时间(时分秒) 保存二进制文件,如图像,视频等 TINYBLOB 占用 : 255b BLOB 65KB MEDIUMBLOB 16MB LONGBLOB 4GB 注意 : 只是可以存放, 关系型数据库是用来处理结构化数据的, 而不是二进制数据 把文件放在库中, 占用大小很大, 备份会很慢, 比从文件系统中读取要慢很多 JSON简单来说是一种将数据在互联网传播的简化方法, 很多时候移动应用都将数据 以JSON方式发送给后端 格式: { key: value } 例 : 使用JSON_OBJECT()函数定义 定义JSON(赋值) 查询属性值 JSON_EXTRACT(列名, '$.key名') 或 列名 -> '$.key名' 例: '->' 列路径操作符 -> 对于嵌套JSON : 列名 ->> '$.key名.key名' 更新添加JSON属性 JSON_SET() 删除JSON属性 JSON_REMOVE() 四流程步 1.理解并分析需求 2.进行概念性建模 将业务中的事物,事情具体化, 并将关系确定下来, 是一个视觉上的表现 3.逻辑建模 不受数据库语言约束的抽象模型, 只展现所需的表和列 4.创建一个具体数据库系统的实体模型 特定数据类型, 设置列的默认值, 色痕迹存储过程, 触发器等等 使用实体关系题(ER, Entity Relationship)或 UML 进行实体可视化描述 工具 : Microsoft Visio, draw.io , LucidCharts 概念模型只表现了业务实体和他们之间的关系, 没有体现数据存储的细节, 用于理解 需求和与产品经理 细化概念模型, 例如属性的数据类型, 不同表的具体关系等 逻辑模型会变成具体的实体 例 例 1.在MySQL Workbench→File→New Model, 建立EER画图建表 联合主键 2.Place New Table, 根据实体模型将表建好 3.建立表间关系 将两个属性联合在一起作为主键 因为主键的值必须是唯一的, 即使是first_name和last_name联合也可能重名, 且主 键在操作中需要复制到有关系的表, 过长会占用过多资源 一般建一个表面_id列作为主键(PK,黄色钥匙) 给两个表添加关系时, 一个表称为父表(主键表), 一个表称为子表(外键表), 上例中学 生为主键表, 注册时间为外键表 简介 外键(标志,小黄点)就是在一个表中引用了其他表的主键 外键表设置主键 方法一 : 用所有外键设成联合主键 方法二 : 添加外键表名_id的列作为主键 如果数据量很大, 且后面会添加父表建议新建id列作为主键 如果不用考虑未来会添加父表用联合主键, 有效防止垃圾数据 只要表中有外键就要对外键添加约束, 防止数据损坏 Foreign Key Options Restrict(限制) Cascade(级联) Set NULL No Option Mysql会在父表修改后自动修改子表数据 拒绝所有修改 父表id修改后子表id设置为空, 这样就出现了一条没有子表记录的父表数据, 叫孤儿 记录(垃圾数据), 很少用这个选项 跟restrict差不多 练习 可减少数据的重复性的标准 , 共有7个范式, 一般做到前三个范式就行了 每个单元格都应该是单一值, 并且列不重复 说明 如果需要重复列, 如tag1,tag2, 那就建一个tag表, 与原表建议多对多关系 关系型数据库里只有一对多, 多对一关系, 要建立多对多关系需要借助一个新的表, 叫中间表(连接表) 创建 建议一个表1名_表2名(习惯命名), 分别建议一对多关系 每个表都应该是单一功能的, 描述一个单一实体, 表中的属性只能描述这个表标的物 例如: 一个订单表有日期和用户名字, 那么名字就应该移出去, 因为名字不属于订单的属性 不遵循第二方式带来的问题 1.浪费空间, 如果同一个用户有多个订单, 那它的名字就会重复 2.更新数据困难, 如果要修改用户名, 就需要修改很多地方 解决 : 让用户名字保存在一个单一的表, 这样重复的用户id只是4个字节的INT 表中的属性只依赖与主键, 不应该由其他属性推导来的 不用死背范式, 重点考虑 避免数据的重复性 即可, 而不是逐条应用范式, 从需求出发, 不一定要满足范式 设计数据库先以逻辑模型或抽象模型开始, 别一上来就设计表, 注意需求分析 4.实体模型转为数据库 Database → Forward Engineer(还没有数据库,新建) → Synchronize Model (同步模型到数据库) Database → Reverse Enginner 其他 错误 Mysql5.7之后默认启用了ONLY_FULL_GROUP_BY SQL模式, 一些情况下使用 GROUP BY会报错 解决 select @@sql_mode; 查看是否启用了ONLY_FULL_GROUP_BY set@@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_Z ERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 设置配置(删除ONLY_FULL_GROUP_BY) CREATE DATABASE IF NOT EXISTS sql_store2; DROP DATABASE IF EXISTS sql_store2; 创建数据库 删除数据库 手动创建数据库 例 : ALTER TABLE customers ADD last_name VARCHAR(50) NOT NULL AFTER 'first_name' 给表customers增加列last_name, 位置在'first_name'后面 增加列 修改列属性 ALTER TABLE customers MODIFY COLUMN first_name VARCHAR(55) DEFAULT ' ' 删除列 ALTER TABLE customers DROP points 注意 更改表先在测试环境下确保是可行的, 在到生产环境中修改 创建表时添加外键 给已存在的表添加外键 添加删除主键 ALTER TABLE customers ADD PRIMARY KE Y (order_id), DROP PRIMARY KEY 删除主键不需要列名 SHOW CHARSET 查看Mysql支持的所有字符集, utf8是默认使用的 排序规则 Default collation, 约定了字符集中的字符是如何排序的, 例如utf8的排序规则是 utf8_general_ci maxien, 字符集最大长度, utf_8每个字符分配3个字节 一般使用默认, 有时可通过修改maxien减少内存占用 改变规则 CHARACTER SET latin1 查看 : SHOW ENGINES 最常用的引擎是MylSAM和InnoDB MylSAM是MySQL5.5之前很流行的老引擎 InnoDB是主要用的引擎, 有事务等新特性 可在创建库, 表或者添加列是使用 改变引擎 索引实际上是数据结构, 通常以二叉树方式保存, 用来快速检索数据的 优势 索引远比遍历查找快得多 索引表往往小到足够放在内存中, 从内存读取数据比硬盘快得多 弊端 增加了数据库的体积, 因为他需要和表一起保存 每次修改数据, MySQL会自动更新索引, 影响当前操作的效率 使用 一般给特别重要的查询添加索引 以查询内容创建索引, 而不是表 其他 使用 ' EXPLAIN 查询语句', 可以看到扫描类型和行数等信息 CREATE INDEX idx_state ON customers (state); 在表customers上创建一个名为 idx_state 的state索引 SHOW INDEXES IN orders 索引是字符串(CHAR, VARCHAR, TEXT, BLOB)时, 截取前面一部分, 占用内存小,索 引效率更高 例 CREATE INDEX idex_lastname ON customers (last_name (5) ) 取last_name前三个字符作为索引, 可通过COUNT判断最优参数 实现快速强大的文字搜索( 搜索引擎 ), 用于搜索长的文本 创建 CREATE FULLTEXT INDEX idx_title_body ON posts (title, body); 在posts表里创建全文索引idx_title_body给title,body列 使用 SELECT * FROM posts WHERE MATCH (title, body) AGAINST ('react redux') 返回在title或body中含有一个或以上词的结果, 这些词可以是任意顺序, 也可以被任 意词分隔开 MATCH(列名...) AGAINST(关键词, ...)会返回检索的 关联度, 结果是按关联度排序 的, 就像搜索引擎 两种模式 自然语言模式(默认) 布尔模式 返回布尔值, 可以想Google那样包含或排除某些词 例 WHERE MATCH(title, body) AGAINST ('react -redux +form' IN BOOLEAN MODE) 搜索'react'但不包含'redux'必须包含'form'的结果 -关键词 不包含该词 +关键词 必须包含该词 "关键词" 精确搜索该关键词 WHERE MATCH(title, body) AGAINST ('"handling a form"' IN BOOLEAN MODE) 精确搜索包含"handling a form"的结果(固定顺序, 不能被分隔开) 用两个或多个属性做索引, 效率最高, 现实中多数情况都在使用组合索引 例 CREATE INDEX idx_state_points ON customers (state, points); 如果索引中有多列, 索引也可以 帮助快速排序 其它 索引越多, 每次修改数据需要更新的也就越多 其他 MySQL最大支持16列组合索引, 建议最好4到6列左右(根据查询需求) 删除索引 DROP INDEX idx_state ON customers 两个原则 1.将最常用的列放在前面 2.将高唯一性的列放在前面 最大程度减少查询次数 注意 两个原则不一定是优的, 根据实际情况选择列顺序, 测试一下 使用WHERE ... AND ...时也要注意顺序, MySQL是从左往右查询的想下先查那个查 询次数是最少的 WHERE选择的优化 WHERE...OR...查询了所有次数 分成两个小查询 WHERE 计算式, 条件式含有计算会扫描全记录 优化,运算移到右边 ORDER BY 列名 : 排序会执行filesort, 非常消耗性能, 不是必须不要对数据排序 优化 : ORDER BY 索引, 设计好索引, 用索引排序, 效率会高很多陪 注意 ORDER BY子句的列排序方向要一致(升序,降序), 并应该和索引的列一致, 否则排序 会变成全盘扫描 查看上一个查询扫描次数 SHOW STATUS LIKE 'last_query_cost' 查询覆盖所有需求, 索引包含所有要查询的列, 只用索引就可以完成查询, 重复索引 type : ALL(全表扫描), index(索引扫描) 作用 : 避免全表扫描, 速度更快 MySQL可以生成重复的索引, 在新建索引前要检查是否存在该索引 无用索引 已有了(A, B)的组合索引, 又建了一个(A)索引, 这样的索引就是无用索引 指定那个用户可以访问, 以及可以访问的权限 学习中用的都是'root'账号, 生成环境里需要添加新的账号, 并配置正确的权限 CREATE USER jerry@127.0.0.1 创建一个用户, 这个用户只能在IP 127.0.0.1(本机)中访问数据库, 这个功能在云服务环境中很有用, 一般有一个数据库服务器, 一个Web服务器, 数据 库服务器限制了只能Web服务器访问 CREATE USER jerry@'%.jerrys.xyz' jerry可以在'jerrys.xyz''和它的子域中访问数据库 CREATE USER jerry jerry可以从任何地方访问该数据库 设置认证密码 CREATE USER jerry IDENTIFIED BY '1234' SELECT * FROM mysql.user; DROP USER jerry@127.0.0.1; SET PASSWORD FOR jerry = '1234' SET PASSWORD = '1234' 给自己设置密码 给'jerry'设置密码 管理员导航面版修改 常见两种情况 1.让Web或桌面应用读写数据库 2.管理员, 授权若干权限管理数据库 例 MySQL官网列出的权限 GRANT ALL ON *.* TO jerry 给jerry所有权限 SHOW GRANTS FOR jerry; SHOW GRANTS ; 返回root用户的权限 返回'jerry'用户的权限 REVOKE CREATE VIEW ON sql_store.* FROM moon_app; 注意 给账户需要的权限即可, 可太多权限可能会带来管理漏洞