Mysql

最新推荐文章于 2024-07-19 00:12:38 发布

【zpp】

最新推荐文章于 2024-07-19 00:12:38 发布

阅读量583

点赞数 23

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/zhangpanpang/article/details/137790349

版权

Mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.数据库概述

1.1数据库概念
- 数据库是按照数据结构来组织、存储数据的仓库，本质就是一套专业软件。全称是database，简称DB。
1.2数据库分类
- 关系型数据库
  - 一般称为SQL，它的数据结构是二维表格，即行和列。SQL是标准化查询语言的缩写，它适用于所有关系型数据库。比如：MySQL（开源免费，很多互联网项目在使用，目前被Oracle收购），Oracle（收费的，甲骨文公司的主打产品），SQL Server（微软的数据库，也是收费的）。
- 非关系型数据库
  - 也叫NOSQL，它的数据结构不固定，主要用于大数据高并发一些场景，特点是读写速度快。常见的有redis（主要用于缓存，数据存在内存里，读写速度很快），mongodb（主要用于大数据存储，对于性能要求高，对于功能要求低），Hbase（超大数据量，且拓展简单）
1.3关系型数据库结构
- 三级结构：数据库、数据表、数据行
  - 第一级数据库，安装好数据库管理软件之后，数据库管理软件会作为一个系统服务启动，然后就可以登录到这个软件服务上，去创建数据库。
  - 第二级数据表，数据是保存在表里面的，表由行和列组成，列是表的部分信息，也叫表的字段。表名是唯一的。表的结构是我们可以人为定义的，也就是一个表有多少个不同类型的字段组成，以及对应字段的特征和限制条件。比如说列名、数据类型、长度、是否可以为空、主键、索引等等。
  - 第三级数据行，每一行是一条完整的数据，以行来存储数据。我们可以对数据进行操作，增删改查。
- 总结：
  - 数据以表格形式存储
  - 一行是一条数据记录
  - 一列是一个字段
  - 许多行和列组成了一个表，一个表一般代表一类数据
  - 多个表组成一个数据库
- mysql服务默认的端口号：3306
1.4MySQL常见数据类型
- 数值类
  - 整数类型：int，由四个字节组成，共32位。
  - 浮点类型：float单精度浮点数，由四个字节组成；double双精度浮点数，由8个字节构成。decimal类型，用于在数据库中存储精确的数值。用于保留准确精确度的列，如货币金额数据。decimal(P，D)表示列可以存储D位小数的P位数。如果不指定，decimal默认是不带小数的10位数。最多存储65位数字，其中小数点前最多占35个，小数点后最多占30。小数点占一字节，数字部分最大空间占用为31字节。
- 字符串类
  - 字符串可以表示任何一种值，包括存储声音图形等二进制数据。
    - 固定长度：char(M)，代表最多可以容纳M个字符，M是个整数。M可以省略，省略的情况下，默认为1，比如char代表1个字符，相当于char(1)。char(10)可以存储10个字符。会存在空间浪费的情况，但是运行效率高。
    - 动态变化长度：varchar(M)，代表最多可以容纳M个字符，注意varchar的M值不能省略。varchar是可变长度，会根据存储的实际值长度动态变化。可以节省资源，但是效率低一些。
    - 动态变化长度：nvarchar(M)，是Unicode编码可变长度类型,varchar能存储的字节数就是它的长度，nvarchar能存储的字节数是它的长度乘2。一个汉字占两个字节。所以varchar(10)最多存储5个汉字，nvarchar(10)可以存储10个汉字
- 时间日期类型
  - 存放日期和时间的值，格式：YYYY-MM-DD hh:mm:ss，如：2023-05-09 11:35:45

2.SQL语句概述

2.1基本语法
- 不区分大小写
- 关键字、字段名（列名）、表名之间需要用空格或者逗号分隔
- 每一个SQL语句都要用分号结尾
- SQL语句可以写在一行，也可以分成多行来写，最终以分号结尾作为标志
2.2SQL的注释
- 多行注释
  - /*
  - 第一行
  - 第二行
  - 第n行
  - */
- 单行注释
  - 以两个–开头或者#号开头，只对本行有效

3.SQL：对库的操作

查看当前有哪些数据库：show databases;
创建一个新的数据库：create database 数据库名;
选中一个数据来使用：use 数据库名；
删除数据库：drop database 数据库名;

4.SQL：对表的操作

4.1创建表

语法：create table 表名（定义表的结构，也就是列名以及数据类型，约束条件，每一列都逗号分隔）；
列字段的常见约束条件：
- 不允许为空：not null
- 默认值：default 后面跟上默认值
- 主键（不能重复）：primary key，可以直接跟到字段后面，也可以使用函数形式独占一行，如：primary key(主键字段)
- 自动增长（从1）：auto_increment，注意：自增的数据被删除后，该数据的自增编号不能再次被使用，会继续累加。
- 唯一键：unique，可以直接跟到字段后面，也可以使用函数形式独占一行，如：unique（唯一约束字段）
- 外键：FOREIGN KEY （另一个表的主键字段） REFERENCES 另一个表名（另一个表的主键字段），创建外键约束，防止插入另一个表中不存在的数据
- 自动生成的字段：某些字段的值是根据其他字段自动生成的，根据as后面的表达式自动生成，语法如：总价 decimal(8,2) generated always as (单价*数量)，代表总价字段的值根据单价字段乘以数量字段自动计算生成。这样的值就不能手动插入了，它根据表达式自动生成。

举例：创建学生表

```
 CREATE TABLE student (
     stuid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
     stuname CHAR （10），
     age INT,
     tel CHAR(11),
     classid VARCHAR(10)
 );
```
- 4.2查看当前数据库有哪些表：show tables;
- 4.3删除表：drop table 表名；
- 4.4查看表结构：desc 表名；
- 4.5查看表的建表语句：show create table 表名；

4.6修改表结构

语法：alter table 表名 对表的具体修改；包括了添加新列（ADD）、修改列（modify可以修改除列名以外的属性，change还可以修改列名）、删除列（drop）、修改表名（rename to）
- 增加列：举例：ALTER TABLE student ADD addr VARCHAR(50) DEFAULT "xian";  增加了一列add
- 修改列modify：举例：ALTER TABLE student MODIFY tel INT NOT NULL; 修改tel列为int类型并且不能为空
- 修改列change：举例：ALTER TABLE student CHANGE tel phone CHAR(11); 修改tel列为phone列，char(11)是对新列名phone的描述。
- 删除列：举例：ALTER TABLE student DROP addr; 删除掉地址这一列
- 修改表名：举例：ALTER TABLE student RENAME TO studentinfo; 修改表名为studentinfo

5.SQL：对数据行的操作

5.1增：插入数据

关键字：insert into
语法：
 插入一条数据：insert into 表名（列名1，列名2，，，列名N） values（值1，值2，，，值N）；
 举例：INSERT INTO studentinfo(stuid,stuname,age,phone,classid) VALUES(43533,"lilei",23,"87665679","1");  
 举例：还有简写的方法，不用写列名，直接写值，但是简写是有条件的，就是你的values值在数据量和顺序上跟完整的列名保持一致。如：          INSERT INTO studentinfo VALUES(78587,"hanmeimei",22,"89009622","2");
 举例：注意，当插入的values值不完整，只是部分列的数据时，列名字段不能省略，因为需要指定你的values值对应的是哪几个列。如：            INSERT INTO studentinfo(stuname,classid) VALUES("xiaoliu","1");

插入多条数据：insert into 表名（列名1，列名2，，，列名N） values（第一条值1，第一条值2，，，第一条值N），（第二条值1，第二条值2，，，第二条值N），，，；
举例：INSERT INTO studentinfo VALUES(46975,"wangda",22,"9097332","2"),(893933,"liuxiao",20,"7292884","1");

5.2删：删除数据

关键字：delete、truncate
语法：delete from 表名 where 条件；
注意：一定要加where条件指定你要删除的数据，否则会全部删除表内所有数据。
举例：DELETE FROM studentinfo WHERE number='2006010004';  删除学号为2006010004的学生数据。
举例：DELETE FROM studentinfo WHERE address='深圳市龙岗区'; 删除地址是深圳市龙岗区的学生信息。
使用truncate清空整个表，然后新建一个新表，当你需要清空数据的时候，使用truncate效率高。由于是清空后新建一个表，所以表的状态也是全新的，比如自增列要重新计算。
    举例：truncate studentinfo;

5.3改：修改数据

关键字：update
语法：update 表名 set 列名1=新值1，列名2=新值2，，，where 条件；
注意：update更新数据也一定要加where指定条件，否则会修改所有的数据。
举例：UPDATE studentinfo SET stuname='赵六',age=22 WHERE number='2006010004'; 修改学号为2006010004的学生的姓名和年龄

6.条件操作符和逻辑运算符

6.1条件操作符

用于where条件的指定
    =
    >
    <
    >=
    <=
    !=
    between...and... 介于两个值之间

6.2逻辑运算符

用于多个条件之间的逻辑运算
    and：且逻辑，多个条件同时满足才能返回结果，and的优先级高于or。
    or：或逻辑，多个条件只要任意一个满足，即返回结果
    in：在某个集合里面，则返回结果，这个集合我们使用()来指定，()里面有多个元素，元素之间使用逗号间隔。如：in (2,4,5,3)
    not int：不在某个集合里面，是上面相反的情况
    ()：使用括号可以改变优先级

7.使用sqlyog导出sql脚本、数据备份、执行sql脚本。

练习：把student数据库的建库建表插入语句导出为sql脚本，然后新建一个stu数据，执行刚才的sql脚本，完成建表和插入数据。然后在stu数据库上完成增删改语句，并结合条件操作符和逻辑运算符使用。

8.mySql查询

8.1.1MySQL基本查询

关键字：select
语法：
    select * from 表名；查询某个表的所有数据
    select 字段1，字段2，，，from 表名；查询指定字段的数据
    举例：
        SELECT * FROM studentinfo;  查询所有列
        SELECT stuname,age FROM studentinfo;  查询姓名和年龄

对查询结果的一些处理：
    去重：关键字distinct，直接加到要去重的字段前面即可
        举例：SELECT DISTINCT address FROM studentinfo; 对地址进行去重
    设置返回结果的行数：关键字limit，一般放在查询语句的最后位置
        举例：SELECT * FROM studentinfo LIMIT 2; 只查询前两行数据
        举例：SELECT * FROM studentinfo LIMIT 1,2; 注意：这个limit后面有两个数字，第一个数字代表起点的行号（行号从0开始计数，所以1这个行号实际代表第二行），第二个数字代表返回几行。所以这个语句返回了第2-3行。

    排序：关键字order by，按照某个字段排序，排序的字段直接写在order by后面。排序规则有两种：升序ASC和降序DESC。默认的是升序。
        举例：SELECT * FROM studentinfo ORDER BY age DESC; 按照年龄降序排序

  联合使用去重、排序、设置返回行数：注意顺序，去重在字段前面，排序在后面，limit放在最后。
        举例：SELECT DISTINCT classnum FROM studentinfo ORDER BY age DESC LIMIT 1;按照年龄降序排序后查询班号，对班号去重，然后limit找出排第一的。

8.1.2条件查询

关键词：where
语法：select * from 表名 where 指定查询的条件；
举例：结合条件操作符指定条件
    SELECT * FROM studentinfo WHERE address='深圳市龙岗区';
    SELECT * FROM studentinfo WHERE age>=21;
    SELECT * FROM studentinfo WHERE age BETWEEN 19 AND 20;

举例：结合逻辑运算符指定条件
    SELECT * FROM studentinfo WHERE age>19 AND address='深圳市龙岗区' AND classnum='2006010901';
    SELECT * FROM studentinfo WHERE age>20 OR classnum='2006010901';
    SELECT * FROM studentinfo WHERE classnum IN ('2006010901','2006030102');
    SELECT * FROM studentinfo WHERE (age=21 OR address='深圳市宝安区') AND classnum='2006020405';

举例：模糊查询，使用like关键字，再配合通配符：% _
    %：匹配0到多个任意字符
    _：匹配一个任意字符
    举例：SELECT * FROM studentinfo WHERE stuname LIKE '张%'; 查询所有姓张的人
    举例：SELECT * FROM studentinfo WHERE stuname LIKE '张_'; 查询姓张的两个字的人

9.MySQL函数运算

9.1日期和时间函数

查询当前日期时间的函数：
    curdate() 查看当前数据库的日期部分（年月日）
    curtime() 查看当前数据库的时间部分（时分秒）
    now() 查看当前数据库的日期+时间
    使用以上函数的时候，需要配合select语句。
    举例：
        SELECT CURDATE();
        SELECT CURTIME();
        SELECT NOW();

获取日期时间的一部分：
    DATE(参数：日期时间的值) 返回年月日部分
    YEAR(参数：日期时间的值) 返回年部分
    MONTH(参数：日期时间的值) 返回月部分
    DAY(参数：日期时间的值) 返回日部分
    举例：
        SELECT DATE('2023-05-10 14:18:42');
        SELECT MONTH('2023-05-10 14:18:42');
        SELECT YEAR(NOW());
        SELECT DAY(NOW());
        
时间计算函数：ADDDATE()
    语法：ADDDATE(时间，INTERVAL 数字 年/月/天/周); 注意：数字为正数往后加时间，数字为负数，往前减时间。
    举例：
        SELECT ADDDATE(NOW(),INTERVAL 30 DAY); 当前时间往后30天的时间
        SELECT ADDDATE(NOW(),INTERVAL -30 DAY);当前时间往前30天的时间
        SELECT ADDDATE(NOW(),INTERVAL 3 YEAR);当前时间往后3年的时间
        SELECT ADDDATE('2023-05-10 14:18:42',INTERVAL -3 MONTH); 指定时间往前三个月的时间
        SELECT ADDDATE(NOW(),INTERVAL -3 WEEK); 当前时间往前三周的时间

日期时间函数的练习(查询租车系统数据库)：
    查询2019-10-15当天的数据（使用bus_car表）
        SELECT * FROM bus_car WHERE DATE(createtime)='2019-10-15';
        SELECT * FROM bus_car WHERE createtime LIKE '2019-10-15%'; #模糊查询也可以实现
    查询2019-10-15之后的数据
        SELECT * FROM bus_car WHERE DATE(createtime)>'2019-10-15';
    查询2019-10-15到2019-10-27之间的数据
        SELECT * FROM bus_car WHERE DATE(createtime) BETWEEN '2019-10-15' AND '2019-10-27';
    查询2019年10月的数据
        SELECT * FROM bus_car WHERE YEAR(createtime)='2019' AND MONTH(createtime)='10';

9.2数学运算符

加减乘除等运算符可以直接在MySQL中使用，主要用于列数据之间的计算
+、-、*、/
举例：SELECT carnumber,cartype,rentprice/price FROM bus_car ORDER BY rentprice/price; 查询结果中包含了一列新值rentprice/price，这个值是计算出来的临时值，它并不存在于表中，这个值有意义，代表了性价比，能用更少的钱租到更好的车。

10.分组查询和子查询

10.1分组查询

分组查询是为了方便对每个组进行聚集运算，所以说一般分组查询都要配合聚集函数一起使用。单纯的分组没意义，如果仅仅是分组，那会返回分组后的每个组的第一个结果。
分组后的查询字段要和一个分组列的字段对应起来，这样才可以把分组后的聚集计算结果和每一组对应起来。
语法：关键字group by，这个后面直接跟要分组的列名。
    举例：SELECT classnum,AVG(age) FROM studentinfo GROUP BY classnum;  根据班号分组，然后求每组也就是每个班学生年龄的平均值。

对分组后的数据进行条件过滤，使用having关键字，类似where的用法，having后面用于指定条件。having只能跟分组配合使用，不能跟where互换。where用于过滤指定的行，having过滤指定的分组后的数据。
    举例：SELECT cartype,AVG(price) FROM bus_car GROUP BY cartype HAVING AVG(price)>500000; 先根据车型分组，然后求车价平均值，最后使用having筛选分组后的数据，求出平均车价大于500000的数据。
    举例：SELECT color,COUNT(*) FROM bus_car GROUP BY color HAVING COUNT(*)>=2; 根据颜色分组，然后统计每种颜色车型的数量，最后having指定条件，求数量大于等于2的组。

综合练习：
    1.查询bus_rent表，查询租车订单数量排在前三名的操作员
        答案：SELECT opername,COUNT(*) FROM bus_rent GROUP BY opername ORDER BY COUNT(*) DESC LIMIT 3;
    2.查询bus_rent表，查询租车订单数量大于等于3个的操作员
        答案：SELECT opername,COUNT(*) FROM bus_rent GROUP BY opername HAVING COUNT(*)>=3;

联合使用where、group by、having、order by、limit这些关键字，注意顺序：
    select 分组列名，聚集函数 from 表名 where 条件 group by 分组列名 having 条件 order by 排序列名 limit 返回行数；
    综合练习：在bus_car表中，求租金大于2500的平均车价在30万以上的最贵的车型
    答案：SELECT cartype,AVG(price) FROM bus_car WHERE rentprice>2500 GROUP BY cartype HAVING AVG(price)>300000 ORDER BY AVG(price) DESC LIMIT 1; 先根据where条件取出租金大于2500的数据，然后按照车型分组，然后求每组的平均车价，然后根据having条件取出平均车价大于300000的组，然后根据每组的平均价进行倒序排序，最后取第一个值。

10.2子查询

子查询就是嵌套在父查询中的查询，查询是可以多层嵌套的。有些查询比较复杂，一层查询解决不了问题，这个时候可以考虑使用多级查询，把复杂的问题分解分步骤实现。子查询先查出来中间数据，然后再用外层查询处理中间数据，得到我们想要的数据。
多级嵌套查询的处理顺序：先处理最内层的查询，再处理外层查询，层层推进。
子查询使用括号括起来
子查询的使用场景：
    一般用于多表之间的联合查询，比如说我们要查询的数据，在当前表中不存在，而存在于另外一张表，那就可以先写一个子查询，去另外一张表拿数据，然后再把子查询返回的结果给父查询使用。对于多个表的联合查询，子查询的效率不如后面要学的联表查询。
    注意：子查询返回的结果一般都是一列数据，不要返回多列，这一列数据一般是两个表之间建立关系的列。因为返回多列数据会成为一个二维的数据结构，不便于父查询去处理。而返回单列数据，这些数据构成了一个集合，这个集合可以给父查询使用IN来筛选。如果要返回多列数据再进行处理，那就需要把这些数据当作临时表来使用，需要用到后面的别名知识。
    举例：SELECT stuname FROM studentinfo WHERE classnum IN (SELECT classnum FROM classinfo WHERE studentnums>30); 查询班级人数大于30人的班级的学生姓名，括号里面是子查询

综合练习：
    1.使用租车数据库，查询操作员李四出租的订单中车型的价格
        答案：SELECT price FROM bus_car WHERE carnumber IN (SELECT DISTINCT carnumber FROM bus_rent WHERE opername="李四");

    2.查询2019年10月的订单的车型描述信息
        SELECT description FROM bus_car WHERE carnumber IN (SELECT carnumber FROM bus_rent WHERE createtime LIKE "2019-10%");

11.联表查询

关系型数据设计表的时候一般都是一类信息一个表。那么当我们需要查询的信息来自于多张表的时候，就需要用到联表查询，联表查询的核心是要在多个表之间建立一种联系，一种条件。通过这个多表之间的条件去每个表拿数据筛选数据，这样才能得到正确的信息。如果不加条件，直接查多表的数据，是没有意义的，那会查出来多个表之间的数据的组合。
联表查询的三种链接方式：
内连接
    就是要求你的连接条件在每个表中都能满足，都能找到数据，才可以返回结果。就相当于在多个表的内部建立了连接，只要有任何一个表没有满足条件（也就是没有满足条件的数据），那么最终结果就是没找到，不会返回结果。
    语法：表1 inner join 表2 on 条件
    举例：SELECT stuname,assistant FROM studentinfo INNER JOIN classinfo ON studentinfo.classnum=classinfo.classnum; 使用内连接进行联表查询，联表的条件是两个表的班号这一列要相等。此时如果给学生表添加一个学生，这个学生的班号写一个在班级表中不存在的班号，那么上面的查询语句就不会查到这个学生的信息了。
    内连接另外一种语法，直接把两个表都写在from后面，逗号隔开：SELECT stuname,assistant FROM studentinfo,classinfo WHERE studentinfo.classnum=classinfo.classnum;

左外连接
    以左表为主，要求左表的每条数据都要返回匹配结果，根据联表条件去每个表逐个匹配，匹配不到也返回结果，但是会在没有匹配到数据的位置填null。所以左表的每条数据都会返回。
    语法：表1 left join 表2 on 条件
    举例：SELECT stuname,assistant FROM studentinfo LEFT JOIN classinfo ON studentinfo.classnum=classinfo.classnum; 以左表studentinfo为主进行左外连接，studentinfo表中的每一条数据都要返回，如果在其他表中没有满足条件的数据，那就填null

右外连接
    同上，就是以右表为主，去匹配，要显示右表中的每条数据记录。
    语法：表1 right join 表2 on 条件
    SELECT stuname,assistant FROM studentinfo RIGHT JOIN classinfo ON studentinfo.classnum=classinfo.classnum;

综合练习：
    使用租车数据库，查询哪个客户租车的次数最多，查出客户姓名、身份证、租车的次数。
        答案：
        SELECT bus_customer.`custname`,bus_rent.identity,COUNT(*) FROM bus_rent,bus_customer WHERE bus_rent.`identity`=bus_customer.`identity` GROUP BY bus_rent.identity ORDER BY COUNT(*) DESC LIMIT 1;

    使用租车数据库，查询车牌号码为鄂A77777的车被哪些客户租过，需要查询到车牌号和客户名。
        答案：
        SELECT bus_rent.carnumber,bus_customer.`custname` FROM bus_rent INNER JOIN bus_customer ON bus_rent.`identity`=bus_customer.`identity` AND bus_rent.`carnumber`="鄂A77777";
        另外一种语法：
        SELECT bus_rent.carnumber,bus_customer.`custname` FROM bus_rent,bus_customer WHERE bus_rent.`identity`=bus_customer.`identity` AND bus_rent.`carnumber`="鄂A77777";

12.别名的使用(AS可省略)

别名就是起个临时的新名字，使用as关键字来起别名。
给列起别名：为了方便查看，给列和列的计算结果起别名。
    举例：SELECT carnumber,rentprice/price AS '性价比' FROM bus_car;
    举例：SELECT carnumber AS '车牌号码',rentprice/price AS '性价比' FROM bus_car ORDER BY '性价比';
    举例：SELECT COUNT(*) AS '车辆数' FROM bus_car;

给表起别名：给某个表起别名，之后使用别名代替这个表
    举例：SELECT a.carnumber,rentprice,rentid FROM bus_car AS a,bus_rent AS b WHERE a.`carnumber`=b.`carnumber`;

给查询结果这种临时数据起别名，相当于一个临时的表
    SELECT number FROM (SELECT number,classnum FROM studentinfo WHERE address='深圳市龙岗区') AS a,(SELECT classnum FROM classinfo WHERE studentnums>40) AS b WHERE a.classnum=b.classnum;  要查询深圳市龙岗区并且班级人数大于40人的班级有哪些学生，使用别名保存临时表，第一个临时表先查询深圳市龙岗区的学号和班号，第二个临时表查询班级人数大于40人的班号，最后对这两个临时表a和b进行内连接查询。

13.索引视图存储过程触发器事物

13.1.索引

索引：索引可以理解成字典的目录。我们可以给表的某些列创建索引，这样可以提高查询速度。但是索引同时会降低增删改的速度。
索引分类：
    普通索引：INDEX，是最基本的索引，没有加什么限制。
    唯一索引：UNIQUE INDEX，索引列的值必须唯一，但是可以有空值。
    主键索引：主键本身就是索引，一张表只能有一个主键列，而且不能重复不能为空
    全文索引：主要查找文本中的关键字，主要用于全文检索，针对较大的数据，慎用，很耗性能。
    组合索引：多列组成的索引。

哪些场景使用索引：
    在经常需要搜索查询的列上，可以加快搜索的速度
    在作为主键的列上，强制该列的唯一性
    在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
    在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
    在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
    在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

创建索引：
    CREATE INDEX name_index ON studentinfo(stuname);

查询索引：
    SHOW INDEX FROM studentinfo;

删除索引：
    DROP INDEX name_index ON studentinfo;

组合索引：
    CREATE INDEX name_age_index ON studentinfo(stuname,age);

13.2视图

视图：视图其实是虚拟的表，不存在的表，其实就是提前写好的查询语句，当你执行这些语句的时候，就会得到一个查询结果，查询结果当作临时表。
    使用场景：我们可以把一些复杂的查询写成视图，提高了代码的复用性和便利性，后面直接使用这个视图即可，不必每次再写复杂的查询语句了。
    创建视图：
        CREATE VIEW q_stuname AS SELECT stuname FROM studentinfo;  创建了一个查询学生姓名的视图q_stuname
    查看视图的数据：
        SELECT * FROM q_stuname;
    查看当前数据库有哪些视图：
        SHOW TABLES;
    删除视图：
        DROP VIEW q_stuname;

13.3存储过程

存储过程：一组为了完成特定功能的sql语句的集合，包括了增删改查等各种操作。用户可以通过调用我们写好的存储过程来完成复杂的操作过程。类似于执行了一个函数。
    使用场景：主要用于一些复杂多个的sql数据库操作，写成一个存储过程，直接让别人调用即可。
    创建存储过程：使用电商数据库
        定义一个简单的存储过程
            DELIMITER $$ -- 定义存储过程之前，重新定义结束标志，使用$$代替;
            CREATE PROCEDURE avg_price() --创建一个存储过程，用于查询商品均价
            BEGIN
                SELECT AVG(prod_price) AS avg_price FROM products;
                DELETE FROM products WHERE prod_id="7";
            END$$
            DELIMITER ; --重新恢复到默认的结束符

        定义一个带参数的存储过程
            #定义一个带参数的存储过程
            DELIMITER $$
            CREATE PROCEDURE prod_name_by_id(IN id INT) -- 输入型参数IN，id是参数变量名，INT是它的类型
            BEGIN
                SELECT * FROM products WHERE prod_id=id;
            END$$
            DELIMITER ;


    调用存储过程：
        CALL avg_price();
        CALL prod_name_by_id(5);
    查看当前数据库有哪些存储过程：
        SHOW PROCEDURE STATUS;
    删除存储过程：
        DROP PROCEDURE avg_price;

13.4触发器

触发器：触发器是一种特殊的存储过程，它不需要通过手动调用，而是根据一些增删改的动作自动执行。
    触发器关键字trigger，就是给某个表绑定一段代码，当表中的数据发生变化的时候（增删改），系统会自动执行这些代码。
    举例：写一个删除用户自动减少用户数量的触发器，当我们删除用户表的一条记录的时候，会触发，然后去另外一张表给用户数量这个字段自动减一。
    DELIMITER $$
    CREATE TRIGGER sub_cout_by_sub_cust AFTER DELETE ON customers FOR EACH ROW
    BEGIN
        UPDATE customers_sum SET cust_sum=cust_sum-1;
    END$$
    DELIMITER ;
  当我们执行这个语句删除一条用户信息的时候，DELETE FROM customers WHERE cust_id=10003;就会在用户数量表里给对应字段自动减一
  删除触发器：
    DROP TRIGGER sub_cout_by_sub_cust;

    触发器的触发时间：
        before：在表中数据改变之前触发
        after：在表中数据改变之后触发

    触发事件：
        INSERT：增
        DELETE：删
        UPDATE：改

13.4事物

事物：事物就是一个完整的单元，是个原子性的整体概念，里面有很多sql语句，它们都是相互依赖的，就是说要么大家都成功，要么都不成功。都不成功就要回到起始状态。如果我们希望接下来执行的很多条sql语句全部执行成功，如果有任意一条失败，那么其他成功执行的sql语句也要撤销，回到初始状态，那么我们就把他们都封装到一个事务中就可以实现。
    事务的特性：
        原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。如果事务在执行过程中发生错误，我们通过回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
        一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。也就是我们查看到的所有数据都是事务开始之前的或者事务执行之后的。没有中间状态的数据。
        隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，是通过加锁的颗粒度不同来实现的，以下四种，隔离级别越来越高，处理并发的能力依次增强。分别是：
       1.读未提交（Read uncommitted，会出现脏读现象，就是某个事物读了另一个事物未提交的数据，此时不应该读到这条数据，所以数据是脏的）、
       2.读提交（read committed，会出现不可重复读现象，也就是某个事物两次读取的同一个数据不一致，因为期间这个数据被另外一个事物修改了）、
       3.可重复读（repeatable read，这是mysql默认的隔离级别，不会出现脏读和不可重复读现象，但代价是性能会下降）、
       4.串行化（Serializable，通过行级锁对每一行数据加锁，并发能力最高但性能降低厉害，很少用）。
        持久性：事务处理结束后，对数据的修改就是永久的，已存储到硬盘中，即便断电或系统故障也不会丢失。

    事物是需要较高版本的MySQL支持的，老版本使用的引擎myisam不支持事物。高版本引擎innoDB支持事物。
    MySQL默认操作模式是autocommit自动提交模式。表示除非显式地开始一个事务，否则每个sql语句都被当做一个单独的事务自动执行。数据库服务器自动开启事务(每一条sql语句开始执行的时候)，自动提交事务(sql语句执行成功)，自动回滚事务(sql语句执行失败)。我们可以通过设置autocommit变量的值改变自动提交模式。
    查询事务是否自动提交：select @@autocommit;
    可以用 SET 来改变 MySQL 的自动提交模式：
        SET AUTOCOMMIT=0 禁止自动提交
        SET AUTOCOMMIT=1 开启自动提交

    事务用 BEGIN, ROLLBACK, COMMIT来实现：
        BEGIN 开始一个事务
        ROLLBACK 事务回滚
        COMMIT 事务确认提交

    事物的一般使用流程：
    开始事务：通过 BEGIN 或者START TRANSACTION 声明事务的开始。
    执行操作：在事务中执行一系列的数据库操作。
    错误检测：在操作过程中检测任何可能的错误。一般使用try-catch异常处理程序检测异常，或者使用if条件来判断数据是否符合预期。
    提交或回滚：如果所有操作成功，通过 COMMIT 提交事务；如果遇到错误，通过 ROLLBACK 回滚事务。

    案例：
        手动回滚
            BEGIN;#开始一个事物
            INSERT INTO customers VALUES(10002,'李四','雁塔区科技路10号','西安市','1','710000','cn','87899988','286992@qq.com');#这个插入操作，由于主键重复导致执行失败
            UPDATE customers_sum SET cust_sum=cust_sum+1;#这个更新操作成功了
            这里检测一下，如果发现第一条插入命令失败，就在最后执行回滚操作。
            ROLLBACK;#这里执行了回滚操作，所以就把第二条sql语句的更新操作撤销了。

        手动提交
            BEGIN;#开始一个事物
            INSERT INTO customers VALUES(10002,'李四','雁塔区科技路10号','西安市','1','710000','cn','87899988','286992@qq.com');#这个插入操作，由于主键重复导致执行失败
            UPDATE customers_sum SET cust_sum=cust_sum+1;#这个更新操作成功了
            如果没有检测，强制提交，那么第一条插入命令执行失败了，第二条更新命令执行成功了，这样数据就失去了一致性。
            COMMIT; #手动提交事物，强制提交了事物，没有做回滚操作，那么第二条sql语句的操作就被写入数据库了

        事务写到存储过程中，带if-else条件控制语句用来做检测，然后决定是回滚还是提交
            准备工作：创建一个表插入数据，模拟一个银行账户的表，字段有账户id和账户余额
                CREATE TABLE accounts(
                account_id INT PRIMARY KEY,
                balance DECIMAL(10,2)
                );
                INSERT INTO accounts VALUES(12345,0);

            补充知识：
                在 MySQL 中，可以使用 SET 语句来定义变量。变量名以 @ 开头。如下：
                    -- 定义一个整数变量
                    SET @my_variable = 10;
                    -- 定义一个字符串变量
                    SET @my_string = 'Hello, world!';
                    -- 输出变量的值
                    SELECT @my_variable, @my_string;

 在 MySQL 中，可以使用 IF、THEN、ELSE 和 END IF 来构建条件控制语句。IF 语句必须以 THEN 关键字开始，以 END IF 结束。语法如下：
                    IF condition THEN
                        statement1;
                    ELSE
                        statement2;
                    END IF;
                    解释：condition 是一个条件表达式，如果该条件为真，则执行 statement1，否则执行 statement2。
                    实例：
                        -- 定义变量
                        SET @num = 10;
                        -- 使用IF-THEN-ELSE-END IF条件控制
                        IF @num > 5 THEN
                            SELECT 'Number is greater than 5';
                        ELSE
                            SELECT 'Number is not greater than 5';
                        END IF;


                除此之外，在 MySQL 中，还可以使用 IF 函数来执行条件判断。IF 函数的语法如下：
                    IF(expr, true_value, false_value)
                    解释：其中，expr 是一个条件表达式，如果该条件表达式为真，则返回 true_value，否则返回 false_value。
                    实例：
                        -- 定义变量
                        SET @num = 10;
                        -- 使用IF条件判断
                        SELECT IF(@num > 5, 'Number is greater than 5', 'Number is not greater than 5');


  写一个带事务处理的存储过程，用来给账户存入100元钱。
                DELIMITER $$ -- 定义存储过程之前，重新定义结束标志，使用$$代替;
                CREATE PROCEDURE deposit() 
                BEGIN
                	-- 设置 autocommit 为 0，即手动提交事务
                	SET autocommit=0;
                	-- 开始事务
                	START TRANSACTION;
                	-- 查询用户账户余额
                	SELECT balance INTO @old_balance FROM accounts WHERE account_id = 12345;
                	-- 假设用户存入 100 元
                	UPDATE accounts SET balance = balance + 100 WHERE account_id = 12345;
                	-- 检查是否存款成功
                	SELECT balance INTO @new_balance FROM accounts WHERE account_id = 12345;
                	-- 如果新余额小于等于旧余额，则回滚事务，这里用到了if-else条件控制语句，MySQL的IF语句仅在复合语句的上下文中起作用(即在BEGIN和END之间包含的语句块)
                	IF @new_balance <= @old_balance THEN
                	    -- 回滚事务
                	    ROLLBACK;
                	    SELECT 'Deposit failed. Rolling back changes.';
                	ELSE
                	    -- 提交事务
                	    COMMIT;
                	    SELECT 'Deposit successful.';
                	END IF;
                END$$
                DELIMITER ; --重新恢复到默认的结束符

            调用这个存储过程：CALL deposit();

14.MySQL设计实战

14.1.数据库设计三范式

1.1 简介
- 三大范式是数据库设计表结构所遵循的规范和指导方法，目的是为了减少冗余，建立结构合理的数据库，从而提高数据存储和使用的性能。
- 三大范式之间是具有依赖关系的，第二范式基于第一范式、第三范式基于第二范式。
1.2 第一范式：原子性
- 表中字段的数据要有原子性，不可再拆分。
- 如：有一个表存储咖啡店的点餐信息，字段名是：下单的产品order_product，存储的值是：大杯热生椰拿铁，这个字段就有问题，因为它不是原子性的，是可以再分的，它将咖啡的容量、品种、冷热都放在了一起。这样不便于数据的管理，用户下单的时候也不便于组合各种需求。应该拆分成三个字段：容量、品种、冷热，分别存储这些信息，用户根据需求来选择下单。这样，容量、品种、冷热这些字段都是不可再拆分的了，具备原子性。方便数据的管理，如我们想增加新品种、新容量只需修改对应字段即可。
1.3 第二范式：唯一性
- 表中每一行数据具有唯一性，唯一性通过主键确定，其他字段都依赖主键。
- 通常，通过给一个表加主键来实现唯一性。还要求非主键字段的值必须完全依赖主键，也就是非主键字段可以根据主键字段来唯一确定。更通俗的解释就是，一个表只存储一类信息，不相关的信息不要存储在一起，因为不相关的信息不具有对主键的依赖性。比如学生表就只能存储学生信息，不能存储课程信息，课程信息要存到课程表。因为课程跟学生没关系，课程不依赖学生表的主键，但是学生自己的信息跟某个具体的学生有关系，学生姓名、性别、年龄、联系方式、地址等信息，唯一依赖学生主键(如学号id)。
1.4 第三范式：独立性
- 表中的非主键字段不能相互依赖，它们都应该依赖主键。
- 比如，学生表中存储了学生所属的班级字段，还有班级的班主任字段，这就是不对的，因为班主任依赖班级字段。
- 通常，在实践中，满足3范式只要做到“一个表只存一种数据”基本就可以实现。
- 另外，范式只是一种规范指导，并不是绝对要求，具体使用可以根据需求决定，比如存储地址，一般情况下，存储的是省地市街道小区等具体的一长串地址，没必要分成省市区等。这里就不满足原子性。但这样做更符合我们的使用场景。

14.2.数据库设计过程

14.2.1需求分析

1.提出数据库技术方案
根据项目需要实现的技术目标，选择合适的数据库方案，比如是否需要SQL和NOSQL结合使用，是否需要考虑数据库性能优化？
2.分析系统功能
分析具体的功能模块，因为我们需要根据功能模块的具体要求，设计数据库。例如：有一个商品管理系统系统，功能结构如图：
不要完全照搬这个案例，大家需要自己设计一个商品管理系统，实现自己的功能，画出功能模块结构图，然后根据此图设计数据库结构

14.2.2、数据库结构设计，设计E-R图（可省略）

概念：ER模型，全称为实体关系模型，也称为E-R图。E-R模型由实体、属性、联系三部分构成。（如果数据库结构不复杂，可以省略）
- 其表示方法如下：
- （1）实体用矩形框表示，矩形框内写上实体名。实体其实就是表。
- （2）实体的属性用椭圆框表示，框内写上属性名，并用无向边与其实体集相连。属性就是表的字段。
- （3）实体间的联系用菱形框表示，表示表和表之间的互动关系。联系以适当的含义命名，名字写在菱形框中，用无向连线将参加联系的实体矩形框分别与菱形框相连，并在连线上标明联系的类型，即1—1（一对一）、1—N（一对多）或M—N（多对多）。
E-R模型的设计思路：
- 根据需求分析出系统有哪些实体以及每个实体有哪些属性，尽量遵循三范式。然后对实体之间的依赖关系进行整合，得出最终的E-R图。
- 实体分为强实体、弱实体和复合实体：
  - 一个实体必须依赖于另一个实体存在，那么前者是弱实体，后者是强实体，例如学生实体和成绩单实体，成绩单依赖于学生实体而存在，因此学生是强实体，而成绩单是弱实体。弱实体和强实体的联系必然只有1：N或者1：1，这里的1代表强实体。这是由于弱实体完全依赖于强实体，强实体不存在，那么弱实体就不存在
  - 复合实体：复合实体也称联合实体或桥接实体，常常用于实现两个或多个实体间的M：N多对多关系，它由每个关联实体的主键组成，说白了就是为了建立多个表之间关系的一个表，它没有存储真正的业务数据，存储的是表之间的关系。
画图工具：很多，如Mircosoft Office VISO2013、MySQL Workbench等，但这些工具需要安装有的还需要付费。推荐使用一个在线的画图工具：processon.com，可以画各种图，包括er图，并且由于是在线工具，不需要安装，使用方便。er图是嵌入在流程图中的，第一步-新建流程图，第二步-选择更多图形，找到er图。如果你嫌麻烦，推荐直接在白纸上手画。
下面就是根据上面的商品管理系统功能结构图，画出来的E-R图

14.2.3、表结构设计

将设计的E-R模型，转化成每一个实体的表结构，设计所需字段、字段类型、字段约束、表间关系。如下：
“客户信息表”（client），可以使用excel或者word中的表格完成。
其他的表结构大家自己设计
四、编写SQL脚本
- 完成了所有表的设计之后，编写SQL脚本，完成创建数据库创建表的过程，并插入数据进行测试。
  是弱实体。弱实体和强实体的联系必然只有1：N或者1：1，这里的1代表强实体。这是由于弱实体完全依赖于强实体，强实体不存在，那么弱实体就不存在
  - 复合实体：复合实体也称联合实体或桥接实体，常常用于实现两个或多个实体间的M：N多对多关系，它由每个关联实体的主键组成，说白了就是为了建立多个表之间关系的一个表，它没有存储真正的业务数据，存储的是表之间的关系。
画图工具：很多，如Mircosoft Office VISO2013、MySQL Workbench等，但这些工具需要安装有的还需要付费。推荐使用一个在线的画图工具：processon.com，可以画各种图，包括er图，并且由于是在线工具，不需要安装，使用方便。er图是嵌入在流程图中的，第一步-新建流程图，第二步-选择更多图形，找到er图。如果你嫌麻烦，推荐直接在白纸上手画。
下面就是根据上面的商品管理系统功能结构图，画出来的E-R图[外链图片转存中…(img-iLa9Lg3B-1713175712847)]

14.2.3、表结构设计

将设计的E-R模型，转化成每一个实体的表结构，设计所需字段、字段类型、字段约束、表间关系。如下：
“客户信息表”（client），可以使用excel或者word中的表格完成。[外链图片转存中…(img-LbYZeaqA-1713175712847)]
其他的表结构大家自己设计
四、编写SQL脚本
- 完成了所有表的设计之后，编写SQL脚本，完成创建数据库创建表的过程，并插入数据进行测试。

【zpp】

关注

23
点赞
踩
27

收藏

觉得还不错? 一键收藏
1
评论
Mysql

1.1数据库概念数据库是按照数据结构来组织、存储数据的仓库，本质就是一套专业软件。全称是database，简称DB。1.2数据库分类关系型数据库一般称为SQL，它的数据结构是二维表格，即行和列。SQL是标准化查询语言的缩写，它适用于所有关系型数据库。比如：MySQL（开源免费，很多互联网项目在使用，目前被Oracle收购），Oracle（收费的，甲骨文公司的主打产品），SQL Server（微软的数据库，也是收费的）。非关系型数据库。
复制链接

扫一扫