推荐阅读

  • MySQl官方文档（MySQL 8.0 Reference Manual）
  • 数据结构可视化工具—— Data Structure Visualizations
  • 阿里何大的博客 —— MySQL 系列文章

  • 公众号 【架构师之路】—— MySQL 系列文章

    DML（Data Manipulation Language，数据操作语言）

  • 数据库意义 : 数据存储、数据管理

  • 用于操作数据库对象中所包含的数据

  • 包括 :

    • INSERT (添加数据语句)
    • UPDATE (更新数据语句)
    • DELETE(删除外键)

      Insert

      语法

      添加单条数据

      语法: INSERT INTO 表名[(字段1,字段2,字段3,…)] VALUES(‘值1’,’值2’,’值3’)
      INSERT INTO users(username, password, avatar) VALUES (‘a’, ‘b’, ‘c’)

      添加多条数据

      语法:INSERT INTO 表名[(字段1,字段2,字段3,…)] VALUES(‘值1’,’值2’,’值3’), (‘值1’,’值2’,’值3’)
      INSERT INTO users(username, password, avatar) VALUES (‘a’, ‘b’, ‘c’),(‘1’, ‘2’, ‘3’)

      注意事项

  • 字段和字段之间使用英文逗号隔开

  • 字段是可以省略的，但是后面的值必须要一一对应，不能少

    Update

    语法

  • UPDATE 表名 SET column=value [,column=value2,…] WHERE [条件];

    • column:要更改的数据列
    • value 为修改后的数据，可以为变量，具体指，表达式或者嵌套的SELECT结果
    • condition 为筛选条件，如不指定则修改该表的所有列数据

      Where 条件子句

  • 有条件地从表中筛选数据 | 运算符 | 含义 | 范围 | 结果 | | —- | —- | —- | —- | | = | 等于 | 5=6 | false | | <> 或 != | 不等于 | 5!=6 | true | | > | 大于 | 5>6 | false | | < | 小于 | 5<6 | true | | >= | 大于等于 | 5>=6 | false | | <= | 小于等于 | 5<=6 | true | | BETWEEN | 在某个范围之间 | BETWEEN 5 AND 10 | | | AND | 并且(&&) | 5 > 1 AND 1 > 2 | false | | OR | 或(||) | 5 > 1 OR 1 > 2 | true |

注意事项

  • column是数据库的列,尽量带上``
  • 条件,筛选的条件,如果没有指定,则会修改所有的列
  • value,可以是一个具体的值,也可以是一个变量

  • 多个设置的属性之间,使用英文逗号隔开

    Delete &&Truncate

    Delete语法

    DELETE FROM 表名 WHERE [条件];
    DELETE FROM users WHERE id=1;

    TRUNCATE语法

    TRUNCATE [TABLE] table_name;
    TRUNCATE TABLE users


    Delete 与 Truncate 的区别

    相同

    都能删除数据,不删除表结构，但是TRUNCATE速度更快

    不同

  • 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器

  • 使用TRUNCATE TABLE 不会对事务有影响

    Delete删除的问题 重启数据库现象

  • InnoDB 自增列会从1开始(存在内存中的,断电即失)

  • MyISAM 继续从上一个自增量开始(存在文件中的,不会丢失)

    DQL（ Data Query Language，数据查询语言）

  • 所有的查询操作都要用它 Select

  • 简单的查询，复杂的查询都能做~
  • 数据库中最核心的语言，最重要的语句

  • 使用频率最高的语句

    SELECT语法

  • [] 括号代表可选的，{}括号代表必选得

    1. SELECT [ALL | DISTINCT]
    2. {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]} 
    3. FROM table_name [as table_alias]
    4. [left | right | inner join table_name2] -- 联合查询 [WHERE ...] -- 指定结果需满足的条件
    5. [GROUP BY ...] -- 指定结果按照哪几个字段来分组 [HAVING] -- 过滤分组的记录必须满足的次要条件
    6. [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
    7. [LIMIT {[offset,]row_count | row_countOFFSET offset}]; 
    8.   -- 指定查询的记录从哪条至哪条

    指定查询字段

    ```sql — 查询student表的全部数据 SELECT * FROM student

— 查询指定字段 SELECT StudentNo, StudentName FROM student

  1. <a name="SGeoq"></a>
  2. ### AS 字句作为别名
  4. - 可给数据列取一个新别名
  5. - 可给表取一个新别名
  6. - 可把经计算或者总结果用另一个新名称来代替
  8. ```sql
  9. -- 为列去别名, AS可以省略
  10. SELECT `StudentNo` AS 学号, `StudentName` AS 姓名 FROM studenS
  12. -- 为表取别名
  13. SELECT `StudentNo` As 学号, `StudentName` AS 姓名 FROM student AS s
  15. -- 为查询结果取一个新名字, CONCAT()函数拼接字符串
  16. SELECT CONCAT('姓名：', `StudentName` ) AS 新名字 FROM student


DISTINCT 去重关键字

  1. -- 查询result表所有的数据
  2. SELECT * FROM result;
  4. -- 查询result表的StudentNo字段
  5. SELECT r.StudentNo From result as r;
  7. -- DISTINCT 去除重复项 , (默认是ALL)
  8. SELECT DISTINCT r.StudentNo FROM result as r;

使用表达式的列

数据库中的表达式：一般由文本值，列值，NULL，函数和操作符等组成
应用场景：

  • SELECT语句返回的结果列中使用
  • SELECT语句中的ORDER BY, HAVING等字句中使用
  • DML语句中的Where条件语句中使用表达式 ```sql — 查询系统版本(函数) SELECT VERSION(); — 计算77-1(表达式) SELECT 77-1 AS 计算结果; — 查询自增的步长(变量) SELECT @@auto_increment_increment

— 学院考试成绩+1分查看 SELECT StudentNo, StudentResult + 1 AS 提分后 FROM result AS r

  1. <a name="n1aId"></a>
  2. # Where条件语句<br />    
  3. 用于检索数据表中符合条件的记录<br />搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假
  4. <a name="KxWaQ"></a>
  5. ## 逻辑操作符
  6. | **操作符** | **语法** | **描述** |
  7. | --- | --- | --- |
  8. | AND 或 && | a AND b 或 a && b | 逻辑与，同时为真结果才为真 |
  9. | OR 或 &#124;&#124; | a OR b 或 a &#124;&#124; b | 逻辑或，只要一个为真，则结果为真 |
  10. | NOT 或 ! | NOT a 或 !a | 逻辑非，若操作数为假，则结果为真! |
  12. ```sql
  13. -- 示例SQL语句
  14. -- 查询result表的学号与学生成绩
  15. SELECT `StudentNo`, `StudentResult` FROM result;
  16. -- 查询成绩在95-100之间的
  17. SELECT `StudentNo`, `StudentResult` FROM result WHERE StudentResult >=95 AND StudentResult <= 100;
  18. -- AND也可以写成&&
  19. SELECT `StudentNo`, `StudentResult` FROM result WHERE StudentResult >=95 && StudentResult <= 100;
  20. -- 模糊查询(区间)
  21. SELECT `StudentNo`, `StudentResult` FROM result WHERE StudentResult BETWEEN 95 AND 100;
  22. -- 查询除了1000号同学的成绩
  23. SELECT `StudentNo`, `StudentResult` FROM result WHERE NOT StudentNo = 1000;
  24. -- NOT也可以写成!
  25. SELECT `StudentNo`, `StudentResult` FROM result WHERE StudentNo != 1000;

模糊查询:比较操作符

  • 数值数据类型的记录之就按才能进行算术运算;
  • 相同数据类型的数据之间才能进行比较; | 操作符名称 | 语法 | 描述 | | —- | —- | —- | | IS NULL | a IS NULL | 若操作符为NULL，则结果为真 | | IS NOT NULL | a IS NOT NULL | 若操作符不为NULL，则结果为真 | | BETWEEN | a BETWEEN b AND c | 若 a 范围在 b 与 c 之间，则结果为真 | | LIKE | a LIKE b | SQL 模式匹配，若a匹配b，则结果为真 | | IN | a IN (a1，a2，a3，……) | 若 a 等于 a1,a2….. 中的某一个，则结果为真 |
  1. -- ======= like =======
  2. -- like结合 %(代表0到任意个字符) _(一个字符)
  3. -- 查询姓刘的同学
  4. select `StudentNo`, `StudentName` from student where `StudentName` like '刘%';
  5. -- 查询姓刘的同学，名字后面只有一个字的
  6. select `StudentNo`, `StudentName` from student where `StudentName` like '刘_';
  7. -- 查询姓刘的同学，名字后面只有两个字的
  8. select `StudentNo`, `StudentName` from student where `StudentName` like '刘__';
  9. -- 查询名字中间有嘉字的同学
  10. select `StudentNo`, `StudentName` from student where `StudentName` like '%嘉%';
  12. -- ======= in =======
  13. -- 查询 1001，1002，1003号学员
  14. select `StudentNo`, `StudentName` from student where StudentNo in (1001,1002,1003);
  15. -- 查询 地址在北京，南京，河南洛阳的学生
  16. select `StudentNo`, `StudentName`, `Address` from student where Address in ('北京', '南京', '河南洛阳');
  18. -- ======= null/not null =======
  19. -- 查询出生日期没有填写的同学
  20. select `StudentName` from student where BornDate is null;
  21. -- 查询出生日期填写了的同学
  22. select `StudentName` from student where BornDate is not null;
  23. -- 查询没有写家庭地址的同学(空字符串不等于null)
  24. select `StudentName` from student where Address='' or Address is null;

连接查询

Join 对比

  • join on：连接查询
  • where：等值查询 | 操作符名称 | 描述 | | —- | —- | | inner join | 如果表中有至少一个匹配,则返回行 | | right join | 左边中返回所有的值,即使右表中没有匹配数据 | | left join | 右边中返回所有的值,即使左表中没有匹配数据 |

基础使用

  1. -- ======= inner join =======
  2. select s.StudentNo, StudentName, SubjectNo, StudentResult
  3. from student as s
  4. inner join result as r on s.StudentNo = r.StudentNo;
  6. -- ======= right join =======
  7. select s.StudentNo, StudentName, SubjectNo, StudentResult
  8. from student as s
  9. right join result as r on s.StudentNo=r.StudentNo
  11. -- ======= left join =======
  12. select s.StudentNo, StudentName, SubjectNo, StudentResult
  13. from student as s
  14. left join result as r on s.StudentNo=r.StudentNo

进阶使用

  1. -- 查询缺考的同学(左连接应用场景)
  2. select s.StudentNo, StudentName, SubjectNo, StudentResult
  3. from student as s
  4. left join result as r on s.StudentNo=r.StudentNo
  5. where StudentResult is null;
  7. -- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
  8. select s.StudentNo, s.StudentName, r.StudentResult, sub.SubjectName
  9. from student as s
  10. right join result as r on s.StudentNo = r.StudentNo
  11. inner join subject as sub on r.SubjectNo = sub.SubjectNo

自连接(了解)

数据表与自身进行连接

  1. -- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
  2. -- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
  3. select a.categoryName as '父栏目', b.categoryName as '子栏目'
  4. from category as a, category as b
  5. where a.categoryid = b.pid;
  7. -- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
  8. select s.StudentNo, s.StudentName,s2.SubjectName,r.StudentResult
  9. from student as s
  10. inner join result r on s.StudentNo = r.StudentNo
  11. inner join subject s2 on r.SubjectNo = s2.SubjectNo;
  13. -- 查询学员及所属的年级(学号,学生姓名,年级名)
  14. select s.StudentNo, s.StudentName,g.GradeName
  15. from student as s
  16. inner join grade g on s.GradeId = g.GradeID;
  18. -- 查询科目及所属的年级(科目名称,年级名称)
  19. select s.SubjectName, g.GradeName
  20. from subject as s
  21. inner join grade g on g.GradeID = s.GradeID;
  23. -- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
  24. select s.StudentNo, s.StudentName,s2.SubjectName,r.StudentResult
  25. from student as s
  26. inner join subject s2 on s.GradeId = s2.GradeID
  27. inner join result r on s.StudentNo = r.StudentNo
  28. where s2.SubjectName = '数据库结构-1';

分页

  • 语法:[LIMIT {[offset,] row_count | row_count OFFSET offset}];
    1. -- 第N页 : limit (PageNo-1)*PageSize,
    2. -- PageSzie [PageNo:页码,PageSize:单页面显示条数]
    ```sql — 每页显示5条数据 select s.StudentNo, s.StudentName,s2.SubjectName,r.StudentResult from student as s inner join subject s2 on s.GradeId = s2.GradeID inner join result r on s.StudentNo = r.StudentNo where s2.SubjectName = ‘数据库结构-1’ order by r.StudentResult asc limit 0,5;

— 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数) select s.StudentNo,s.StudentName,s2.SubjectName,r.StudentResult from student as s inner join result r on s.StudentNo = r.StudentNo inner join subject s2 on r.SubjectNo = s2.SubjectNo where s2.SubjectName = ‘JAVA第一学年’ order by r.StudentResult desc limit 0,10;

  1. <a name="hEpeh"></a>
  2. #### 排序<br /><br />
  4. - 语法：order by [字段名] [desc/asc]
  5. - desc：降序，由大到小
  6. - asc：升序，由小到大
  7. ```sql
  8. -- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩) 降序
  9. select s.StudentNo, s.StudentName,s2.SubjectName,r.StudentResult
  10. from student as s
  11. inner join subject s2 on s.GradeId = s2.GradeID
  12. inner join result r on s.StudentNo = r.StudentNo
  13. where s2.SubjectName = '数据库结构-1'
  14. order by r.StudentResult desc ;
  16. -- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩) 升序
  17. select s.StudentNo, s.StudentName,s2.SubjectName,r.StudentResult
  18. from student as s
  19. inner join subject s2 on s.GradeId = s2.GradeID
  20. inner join result r on s.StudentNo = r.StudentNo
  21. where s2.SubjectName = '数据库结构-1'
  22. order by r.StudentResult asc ;

子查询

  • 在查询语句中的WHERE条件子句中，又嵌套了另一个查询语句
  • 嵌套查询可由多个子查询组成，求解的方式是由里及外
  • 子查询返回的结果一般都是集合，故而建议使用IN关键字 ```sql — 查询 数据库结构-1 的所有考试结果(学号，科目编号，成绩)，降序排列 — 方式一 select r.StudentNo, s.SubjectName, r.StudentResult from result as r
    1.    inner join subject s on r.SubjectNo = s.SubjectNo
    where s.SubjectName = ‘数据库结构-1’ order by r.StudentResult desc;

— 方式二:使用子查询(执行顺序:由里及外) select r.StudentNo, r.SubjectNo, r.StudentResult from result as r where SubjectNo = ( select SubjectNo from subject where SubjectName = ‘数据库结构-1’ ) order by r.StudentResult desc; — 方法一:使用连接查询 — 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名

select s.StudentNo, s.StudentName, s2.SubjectName, r.StudentResult from student as s inner join result r on s.StudentNo = r.StudentNo inner join subject s2 on r.SubjectNo = s2.SubjectNo where s2.SubjectName = ‘高等数学-2’ and r.StudentResult >= 80;

— 方法二:使用连接查询+子查询 — 分数不小于80分的学生的学号和姓名 select s.StudentNo, s.StudentName, s2.SubjectName, r.StudentResult from student as s inner join result r on s.StudentNo = r.StudentNo inner join subject s2 on s.GradeId = s2.GradeID where StudentResult >= 80;

— 在上面SQL基础上,添加需求:课程为 高等数学-2 select s.StudentNo, s.StudentName, r.StudentResult from student as s inner join result r on s.StudentNo = r.StudentNo where StudentResult >= 80 and r.SubjectNo = ( select SubjectNo from subject where SubjectName = ‘高等数学-2’ );

— 方法三:使用子查询 — 分步写简单sql语句,然后将其嵌套起来 select s.StudentNo,s.StudentName from student as s where StudentNo in ( select r.StudentNo from result as r where r.StudentResult >= 80 AND r.SubjectNo = ( select sub.SubjectNo from subject as sub where sub.SubjectName = ‘高等数学-2’ ) );

/ 练习题目: 查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数) 使用子查询,查询郭靖同学所在的年级名称 /

— C语言-1 的前5名学生的成绩信息(学号,姓名,分数) select s.StudentNo, s.StudentName, r.StudentResult from student as s inner join result r on s.StudentNo = r.StudentNo order by r.StudentResult desc limit 0,5 ;

— 使用子查询,查询周丹同学所在的年级名称 select g.GradeName from grade as g where GradeID = ( select s.GradeId from student as s where StudentName = ‘周丹’ );

  1. <a name="co9mD"></a>
  2. #### 分组和过滤
  3. ```sql
  4. -- 查询不同课程的平均分,最高分,最低分 
  5. -- 前提:根据不同的课程进行分组
  6. select s.SubjectName, avg(r.StudentResult) as 平均分, max(r.StudentResult) as 最高分, min(r.StudentResult) as 最低分
  7. from result as r
  8. inner join subject s on r.SubjectNo = s.SubjectNo
  9. group by r.SubjectNo
  10. having  平均分 > 80

MySQL函数

常用函数

数据函数

  1. -- ======= 数学运算 =======
  2. -- 绝对值
  3. select abs(-8);
  4. -- 向上取整
  5. select ceiling(9.4);
  6. -- 向下取整
  7. select floor(9.4);
  8. -- 返回一个0~1之间的整数
  9. select rand();
  10. -- 判断一个数的符号，负数返回1，正数返回1，0返回0
  11. select sign(10);

字符串函数

  1. -- ======= 字符串函数 =======
  2. -- 字符串长度
  3. select char_length('你猜我猜不猜你猜不猜');
  4. -- 拼接字符串
  5. select concat('a', 'b', 'c');
  6. -- 查询，从某个位置开始替换某个长度
  7. select insert('我爱Golang',1,1,'SliverHorn');
  8. -- 小写字母
  9. select lower('SliverHorn');
  10. -- 大写字母
  11. select UPPER('SliverHorn');
  12. -- 返回第一次出现的索引
  13. select instr('SliverHorn', 'r');
  14. -- 替换出现的指定字符串
  15. select replace('SliverHorn说SliverHorn', '说', '-->');
  16. -- 返回制定本的子字符串(源字符串，截取的位置，截取的长度)
  17. select substr('SliverHorn', 4, 6);
  18. -- 字符串反转
  19. select reverse('SliverHorn');
  21. -- 查询姓周的同学,改成邹
  22. select replace(StudentName, '周', '邹') as 名字
  23. from student where StudentName LIKE '周%';

日期和时间函数

  1. -- ======= 日期和时间函数 =======
  2. -- 获取当前日期
  3. select current_date();
  4. -- 获取当前日期
  5. select curdate();
  6. -- 获取当前的时间
  7. select now();
  8. -- 获取本地时间
  9. select localtime();
  10. -- 获取系统时间
  11. select sysdate();
  13. -- 获取当前年份
  14. select year(now());
  15. -- 获取当前月份
  16. select month(now());
  17. -- 获取当前日
  18. select day(now());
  19. -- 获取当前时
  20. select hour(now());
  21. -- 获取当前的分
  22. select minute(now());
  23. -- 获取当前秒
  24. select second(now());

系统信息函数

  1. -- ======= 系统信息函数 =======
  2. -- 版本
  3. select version();
  4. -- 用户
  5. select user();

聚合函数

函数名 描述
COUNT() 返回满足Select条件的记录总和数，如 select count() 【不建议使用 ，效率低】
SUM() 返回数字字段或表达式列作统计，返回一列的总和。
AVG() 通常为数值字段或表达列作统计，返回一列的平均值
MAX() 可以为数值字段，字符字段或表达式列作统计，返回最大的值。
MIN() 可以为数值字段，字符字段或表达式列作统计，返回最小的值。 
  1. -- ======= 聚合函数 =======
  2. select count(StudentName) from student;
  3. select count(*) from student;
  4. select count(1) from student;
  5. -- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
  6. -- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。
  7. -- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录;
  8. -- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
  10. /* 很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1) 可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的 条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不 同，它会统计该字段不为null的记录条数。
  11. 下面它们之间的一些对比:
  12. 1)在表没有主键时，count(1)比count(*)快 2)有主键时，主键作为计算条件，count(主键)效率最高; 3)若表格只有一个字段，则count(*)效率较高。
  13. */
  14. select sum(r.StudentResult) as 总和 from result as r;
  15. select avg(r.StudentResult) as 平均分 from result as r;
  16. select max(r.StudentResult) as 最高分 from result as r;
  17. select min(r.StudentResult) as 最低分 from result as r;

事务

什么是事务

  • 事务就是将一组SQL语句放在同一批次内去执行
  • 如果一个SQL出错，则改批次内的所有SQL都将被取消执行
  • MySQL事务处理只支持InnoDB和BDB数据表类型

事务的ACID原则


原子性(Atomic)

整个事务中的所有操作，要么全部完成，要么全部失败，不可能停滞在中间的某个环节.事务在执行过后才能中发生错误，会被回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)

一个事务可以封装状态改变(除非它是一个只读的).事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少.如果事务是并发多个，系统也必须如同串行事务一样的操作，其主要特征是保护性和不变性(Presering an lnvarriant)。
以转账案例为例，假如有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同事发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元.五个账户的总额也应该还是500，这就是保护性和不变性。


隔离性(isolated)

隔离状态执行事务，是它们好像是系统在给定时间内执行的唯一操作。
如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统.这种属性有时称为串行化，为了防止事务操作的混淆，必须串行化或者序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)

在事务完成后，该事务对数据库所作的更改，持久的保存在数据库之中，并不会被回滚。

事务实现：基本语法

  1. -- mysql 是默认开启事务，自动提交
  2. -- 关闭自动提交
  3. set autocommit = 0;
  4. -- 开启自动提交
  5. set autocommit = 1;
  7. -- 手动处理事务
  8. set autocommit = 0;
  10. -- 事务开启：标记一个事务的开始，从这个之后的sql都在同一个事务内
  11. start transaction;
  14. -- 提交：持久化到磁盘(成功)
  15. commit;
  17. -- 回滚：回到原来的样子(失败！)
  18. rollback;
  20. -- 事务结束开启自动提交
  21. set autocommit = 1;
  23. -- 设置一个事务的保存点
  24. savepoint 保存点名称;
  26. -- 回滚到保存点
  27. rollback to savepoint 保存点名称;
  29. -- 删除保存点
  30. release savepoint 保存点名称;

事务实现：测试题目

  1. /*
  2. 课堂测试题目
  3. A在线买一款价格为500元商品,网上银行转账. A的银行卡余额为2000,然后给商家B支付500. 商家B一开始的银行卡余额为10000
  4. 创建数据库shop和创建表account并插入2条数据 */
  5. CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci; USE `shop`;
  6. CREATE TABLE `account` (
  7.     `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(32) NOT NULL,
  8.   `cash` DECIMAL(9,2) NOT NULL, PRIMARY KEY (`id`)
  9. ) ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO account (`name`,`cash`)
  10. VALUES('A',2000.00),('B',10000.00)
  11. -- 转账实现
  12. SET autocommit = 0; -- 关闭自动提交
  13. START TRANSACTION; -- 开始一个事务,标记事务的起始点 UPDATE account SET cash=cash-500 WHERE `name`='A'; UPDATE account SET cash=cash+500 WHERE `name`='B'; COMMIT; -- 提交事务
  14. # rollback;
  15. SET autocommit = 1; -- 恢复自动提交

索引

  • 索引(Index)是帮助MySQL高效获取数据的数据结构。
  • 提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的作用

  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表和表之间的连接,实现表和表之间的参照完整性
  • 使用分组和排序字句进行数据检索时,可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化

索引的分类

  • 主键索引(Primary Key)
  • 唯一索引(Unique)
  • 常规索引(Index)
  • 全文索引(FullText)

主键索引

主键：某一属性组能唯一标识一条记录
特点：

  • 最常见的索引类型
  • 确保数据记录的唯一性

  • 确定特定数据记录子在数据库中的位置

    唯一索引

    作用：避免同一个表中数据列中的值重复
    与主键索引的区别是：

  • 主键索引只能有一个

  • 唯一索引可能有多个


    常规索引

    作用：快速定位特定数据
    注意：

  • index和key关键字都可以设置常规索引

  • 应加在查询找条件的字段
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作

全文索引

作用：快速定位特定的数据

注意：

  • MySQL5.6版本开始支持InnoDB引擎的全文索引
  • 只能用于CHAR，VARCHAR，TEXT数据列类型
  • 适合大型数据集

索引准则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加载查找条件的字段


    索引的数据结构

    1. -- 我们可以在创建上述索引的时候，为其指定索引类型，分两类 hash类型的索引:查询单条快，范围查询慢 btree类型的索引:b+树，层数越多，数据量指数级增长(我们就用它，因为innodb默认支持它)
    2. -- 不同的存储引擎支持的索引类型也不一样
    3. InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引; 
    4. MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引; 
    5. Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引; 
    6. NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引; 
    7. Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引;
    关于索引的本质:http://blog.codinglabs.org/articles/theory-of-mysql-index.html


    权限管理


    用户管理

    ```sql / 用户和权限管理 / ————————— 用户信息表:mysql.user — 刷新权限 FLUSH PRIVILEGES — 增加用户 CREATE USER kuangshen IDENTIFIED BY ‘123456’ CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  • 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。 - 只能创建用户，不能赋予权限。
  • 用户名，注意引号:如 ‘user_name’@’192.168.1.1’
  • 密码也需引号，纯数字密码也要加引号
  • 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的 混编值，需包含关键字PASSWORD — 重命名用户 RENAME USER kuangshen TO kuangshen2 RENAME USER old_user TO new_user — 设置密码 SET PASSWORD = PASSWORD(‘密码’) — 为当前用户设置密码 SET PASSWORD FOR 用户名 = PASSWORD(‘密码’) — 为指定用户设置密码

— 删除用户 DROP USER kuangshen2 DROP USER 用户名 — 分配权限/添加用户 GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] ‘password’]

  • all privileges 表示所有权限 - . 表示所有库的所有表
  • 库名.表名 表示某库下面的某表 — 查看权限 SHOW GRANTS FOR root@localhost; SHOW GRANTS FOR 用户名 — 查看当前用户权限 SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER(); — 撤消权限 REVOKE 权限列表 ON 表名 FROM 用户名 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 — 撤销所有权限 ```

    权限解释

    ```sql — 权限列表 ALL [PRIVILEGES] — 设置除GRANT OPTION之外的所有简单权限 ALTER — 允许使用ALTER TABLE ALTER ROUTINE — 更改或取消已存储的子程序 CREATE — 允许使用CREATE TABLE CREATE ROUTINE — 创建已存储的子程序 CREATE TEMPORARY TABLES — 允许使用CREATE TEMPORARY TABLE CREATE USER — 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。 CREATE VIEW — 允许使用CREATE VIEW — 允许使用DELETE — 允许使用DROP TABLE DELETE DROP EXECUTE FILE INDEX INSERT LOCK TABLES PROCESS REFERENCES RELOAD REPLICATION CLIENT — 允许用户询问从属服务器或主服务器的地址 REPLICATION SLAVE — 用于复制型从属服务器(从主服务器中读取二进制日志事件) SELECT — 允许使用SELECT SHOW DATABASES — 显示所有数据库 — 允许用户运行已存储的子程序 — 允许使用SELECT…INTO OUTFILE和LOAD DATA INFILE — 允许使用CREATE INDEX和DROP INDEX — 允许使用INSERT — 允许对您拥有SELECT权限的表使用LOCK TABLES — 允许使用SHOW FULL PROCESSLIST — 未被实施 — 允许使用FLUSH — 允许使用SHOW CREATE VIEW — 允许使用mysqladmin shutdown SHOW VIEW SHUTDOWN SUPER mysqladmin debug命令;允许您连接(一次)，即使已达到max_connections。 UPDATE — 允许使用UPDATE USAGE — “无权限”的同义词 GRANT OPTION — 允许授予权限

/ 表维护 / — 分析和存储表的关键字分布 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 … — 检查一个或多个表是否有错误 CHECK TABLE tbl_name [, tbl_name] … [option] … option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} — 整理数据文件的碎片 OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

  1. <a name="KPyZF"></a>
  2. ## MySQL备份
  3. <a name="o405Z"></a>
  4. ### <br />数据库备份必要性<br /><br />
  6. - 保证重要数据不丢失
  7. - 数据转移
  8. <a name="jdhJh"></a>
  9. ### <br />MySQL数据库备份方法<br /><br />
  11. - mysqldump备份工具
  12. - 数据库管理工具,如SQLyog
  13. - 直接拷贝数据库文件和相关配置文件
  14. <a name="Wky6E"></a>
  15. ### <br />mysqldump客户端<br /><br />
  16. 作用：
  18. - 转储数据库
  19. - 搜集数据库进行备份
  20. - 将数据转移到另一个SQL服务器,不一定是MySQL服务器
  21. ```sql
  22. -- 导出
  23. 1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
  24.     mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
  25. 2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
  26.     mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql) 3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
  27.     mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
  28. 4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
  29.     mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql) 可以-w携带备份条件
  31. 可以-w携带备份条件
  32. -- 导入
  33. 1. 在登录mysql的情况下: -- source D:/a.sql
  34. source 备份文件 2. 在不登录的情况下
  35. mysql -u用户名 -p密码 库名 < 备份文件

规范化数据库设计

为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计

  • 数据冗余,存储空间浪费
  • 数据更新和插入的异常
  • 程序性能差

良好的数据库设计

  • 节省数据的存储空间
  • 能够保证数据的完整性
  • 方便进行数据库应用系统的开发

软件项目开发周期中数据库设计

  • 需求分析阶段：分析客户的业务和数据处理需求
  • 概要设计阶段：设计数据库的E-R模型图，确认需求信息的正确和完整

设计数据库步骤

  • 收集信息
    • 与该系统有关人员进行交流，座谈，充分了解用户需求，理解数据库需要完成的任务.
  • 标识实体[Entity]
    • 标识数据库要管理的关键对象或实体,实体一般是名词
  • 标识每个实体需要存储的详细信息[Attribute]

  • 标识实体之间的关系[Relationship]

    三大范式

    不合规范的表设计会导致的问题：

  • 信息重复

  • 更新异常
  • 插入异常
    • 无法正确表示信息
  • 删除异常
  • 丢失有效信息

第一范式 (1st NF)

第一范式的目标是确保每列的原子性，如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

  • 第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，即满足第二范式(2NF)必须先满足第一范式(1NF)。
  • 第二范式要求每个表只描述一件事情

第三范式(3rd NF)

  • 如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
  • 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范化和性能的关系

  • 为满足某种商业目标，数据库性能比规范化数据库更重要
  • 在数据规范化的同时，要综合考虑数据库的性能
  • 通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
  • 通过在给定的表中插入计算列，以方便查询