DB_数据库设计

数据库设计遗漏点【开发前必看】

数据库命名格式

• 数据库的每个字段一定要有备注，这样的话生成代码的时候才能标记上去

• 每个表的主键要确定，不然肯定会出错

• 数据库Date的格式一般定义为TimeMap。字段名称要写成update_time。注意下划线

  

• postman发送日期格式要写成如下：

  

• 字段和命名一定要正确、正规、规范。不然前端容易对不上

• 数据库的url的长度

  

• 数据库的表名尽量使用c_order这种格式，避免使用order这种。即使你加了@TableName还是没用

• eq后面的参数写的是数据库里面的参数，不是实体类的（volunteer_id）

QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("volunteer_id", volunteerId);

• 还有这个配置类映射文件 mybatis-plus.mapper-locations=classpath:/mapper/*.xml一定要配置，不然找不到

数据库sql语句

• 左连接left join

  

• 内连接join

实际上，相当与inner join

• 加上where 筛选

SELECT o2.order_id, o2.order_status_id, o2.status, o2.description, o1.record_id, o1.update_time
FROM order_record AS o1
LEFT JOIN order_status AS o2 ON o1.order_id = o2.order_id
WHERE o1.order_id = 1730208938441322497;

mybatis_plus的方法分析

• mybatis_plus中updateById就是后面放实体就行

• mybatis_plus中insert就是后面放实体就行，注意区别

• req、resq、po都要加上data，resultType映射的时候会用到

• mybatis_plus的selectById必须要是使用主键

• selectList后面只要wrap就行了，会返回List数组

Map<String, Object> filter = JSONObject.parseObject(JSON.toJSONString(companionReq));

Sql升级

1.between 20 and 23 是左闭右闭 大于等于20并且小于等于23

2.NOT between 20 and 23 是小于20或者大于23

3.IN

//查找非计算机系非经管系的学生的姓名和性别
select Sname,Ssex 
from students
where Sdept NOT IN ('计算机','经营')

4.通配符

% 任意长度为n的字符串

_ 任意字符

[] 指定范围内 （要看sql版本）

[^] 不在指定氛围 （要看sql版本）

like '[Ck]%'    第一个是C或者K
like '[^A-D]%'  第一个不是A-D之间

也就是说如果是连在一起的，就不代表是范围

mybatis还支持正则表达式通配符

<select id="selectUsersByPattern" resultType="User">  
  SELECT * FROM users WHERE name REGEXP #{pattern}  
</select>

//在这个例子中，#{pattern} 应该是一个包含正则表达式的参数，例如 '[李王].*'，它将匹配所有以“李”或“王”开头的名字。

5.排序

ASC是升序

DESC是降序

//查找学生信息所在系的升序，年龄降序排列
select * 
from Students
order by 
Sdept ASC,Sage DESC

6.集合查询

并 UNION

交 intersect

差 except

使用前提，这些查询结果提供的关系具有相同的属性和属性类型列表，默认情况下去掉重复元组

//查找即是女明星又是净资产在1000万以上的制片人的姓名和地址
select name,address
from MovieStars
where gender = 'f'
INTERSECT
select name,address
from MovieExecs
where netWorth >= '10000000'
//集合查询一般很少使用，可以在service层调用Java api来搞定

7.子查询

//查询与刘雪同系的学生学号、姓名及所在系
         
         SELECT Sno, Sname, Sdept 
         FROM  Students 
         WHERE Sdept =                      返回单值  对比IN
                   (SELECT Sdept 
                    FROM  Students 
                    WHERE Sname=‘刘雪’ )

//查询与刘雪同系的学生 学号、姓名及所在系
SELECT Sno, Sname, Sdept 
         FROM  Students  s1
         WHERE s1.Sdept IN
                        ( SELECT Sdept
                          FROM  Students s2
                           WHERE s2.Sname=‘刘雪’ )

//查询选修了‘数据结构’的学生的学号、姓名          
         SELECT Sno, Sname
         FROM Students 
         WHERE Sno IN            集合
                   ( SELECT Sno 
                      FROM SC
                      WHERE Cno  =          单值
                              (SELECT Cno
                                FROM Courses 
                                WHERE Cname=‘数据结构’))
//用学号和课程号做中间结果
//尽量使用  IN  ，如果用 = 号，返回的是多个节点会报错的

//查询其他系比经管系某一学生年龄小的学生的姓名、年龄
SELECT s1.student_name, s1.age  
FROM students s1  
WHERE s1.age < (  
    SELECT s2.age  
    FROM students s2  
    WHERE s2.department = '经管系' AND s2.student_id = target_student_id  
)  
AND s1.department <> '经管系';

<>是不等于的意思，当然也可以使用！=

查询其他系比经管系所有学生年龄都小的学生的姓名、年龄
SELECT s1.student_name, s1.age  
FROM students s1  
WHERE s1.age < (  
    SELECT MIN(s2.age)  
    FROM students s2  
    WHERE s2.department = '经管系'  
)  
AND s1.department <> '经管系';

exist关键字

//查询选修了‘数据结构’的学生的学号、姓名 
SELECT Sname,Sno
      FROM Students
      WHERE  EXISTS 
             ( SELECT  SC.*
                  FROM  SC,Courses
                     WHERE  Sno=Students.Sno
                             AND  SC.Cno=Courses.Cno
                             AND Cname=‘数据库’ );
//只要有返回数据就是为真
//注意看where后面的Student.Sno

8.聚集函数

  	 AVG ([ALL|DISTINCT]  列名 )             
     COUNT ([ALL|DISTINCT] 列名)       去重
     COUNT (*)                                        
     MAX ( 列名 )
     MIN( 列名 )
     SUM ([ALL|DISTINCT] 列名)
     
默认为all

使用聚集函数时，可用GROUP BY 子句对查询结果进行分组计算 分组的原则：指定列相等的分为一组

//例: 统计每门课程的选课人数
       SELECT  Cno, COUNT(Sno)
       FROM  SC
       GROUP  BY Cno

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

例：查询经管系选修3门以上课程学生的姓名
        SELECT Students.Sname 
        FROM   SC,Students
        WHERE  Sdept=‘经管’ 
                         AND  Students.Sno=SC.Sno
        GROUP  BY  SC.Sno    
        HAVING   COUNT(*)>3       此时的count是对每一组统计

//只有一个表，表里面只有两个索引，一个学号，一个选的课程。一个学生可以选择多门课程。
SELECT course_id  
FROM student_courses  
GROUP BY course_id  
HAVING COUNT(DISTINCT student_id) >= 5;

sql中的查询顺序

（这个是书写顺序）
SELECT                                   必须
FROM                                     必须
WHERE 
GROUP BY  
HAVING 
ORDER BY


执行顺序依次为：
from ：先确定查询范围
ON：确定多表联合查询的条件
JOIN：指定联合哪些数据表
WHERE ：全表查询的筛选条件，生成第一个结果集
GROUP BY：分组条件，对第一个结果集进行分组，得到第二个结果集
HAVING ：过滤条件，与group by进行连用，对第二个结果集中的每组数据，进行筛选过滤，得到第三个结果集
SELECT：指定获取的列项，得到第四个结果集
DISTINCT ：对指定列进行去重操作
ORDER BY：对结果集按照指定字段进行排序整理
LIMIT：对最终结果集进行截取，一般和offset连用，可用于分页


所以select的属性别名在where,goudby当中不能使用，但是order by可以的
不过表别名都可以的，因为表别名的话第一个就已经使用了

• 只需要记住 from 后面的表别名的话，都可以使用

• select 后面的属性别名只有 order by可以

空值

1.在构成分组时，NULL被作为一般的值对待。
	例如: SELECT a, AVG(b) FROM R GROUP BY a中，当a的属性值为NULL时，就会统计a=NULL的所有元组中b的均值。
	
2.空值在任何聚集操作中被忽视，除了count（*）

3。分组和聚集操作是不一样的

对于聚合函数如 COUNT 来说，NULL 值通常不会被计入总数，除非使用了特定的 COUNT 变种，如 与 COUNT(*) 的行为略有不同。

当使用 COUNT(*) 时，它会计算表中的所有行，不论这些行中的字段值是否为 NULL。因此，即使关系 R(A,B) 中的唯一元组的两个字段值均为 NULL，SELECT COUNT(*) FROM R 的结果仍然是 1，因为你在计算的是元组的数量，而不是非 NULL 值的数量。

关于聚合函数和空值（NULL）的处理，以下是一些例子来说明它们之间的关系：

COUNT 函数

例子 1: 计算表中所有行的数量，包括包含NULL值的行。

>SELECT COUNT(*) FROM table_name;

在这个例子中，COUNT(*) 会计算 table_name 表中的所有行，不论这些行中的字段是否包含NULL值。

例子 2: 计算某个字段中非NULL值的数量。

>SELECT COUNT(column_name) FROM table_name;

在这个例子中，COUNT(column_name) 只会计算 column_name 字段中非NULL值的数量。如果某行的 column_name 字段值为NULL，那么这行不会被计入总数。

新增
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
删除
DELETE FROM table_name
WHERE condition;
更新
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引 （PRIMARY KEY）

  • 唯一的标识，主键不可重复，只能有一个列作为主键

• 唯一索引 （UNIQUE KEY）

  • 加载UNIQUE 字段上，UNIQUE 可以保证一个或者多个列允许有重复的字段
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引

• 常规索引（KEY/INDEX）

  • 加在普通字段上
  • 默认的，index,key关键字来设置

• 全文索引（FULLTEXT）

  • 在特点的数据库引擎下才有，MyISAM
  • 快速定位数据
  • MyISAM 存储引擎支持Full-text索引，用于查找文本中的关键词，而不是直接比较是否相等。Full-text索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM 表

-- 增加一个索引
ALTER TABLE 表 ADD FULLTEXT INDEX 索引名（字段名）

-- EXPLAIN 分析sql执行状况
EXPLAIN SELECT * FROM student where age = 30 -- 非全文索引

--在这里主要是用来检查如果要查询这个语句，要进行几次比较（io查询）

测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入100万数据
DELIMITER $$ --  写函数之前必写
CREATE FUNCTION mock_data()
RETURNS INT 
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;

WHILE i<num DO
-- 插入语句
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100));

SET i = i+1;
END WHILE;
RETURN i;


END;

INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('用户',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100))


SELECT mock_data();

SELECT * FROM app_user WHERE `name`='用户9999' -- 接近半秒

EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'  -- 查询99999条记录

-- id _ 表名_字段名
-- create index on 字段
CREATE INDEX id_app_user_name ON app_user(`name`); -- 0.001 s
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'  -- 查询一条记录

索引在小数据的时候，用处不大，但是在大数据的时候，区别十分明显

阅读： http://blog.codinglabs.org/articles/theory-of-mysql-index.html