数据库的知识点和遇到的错误

一、知识点

1.

学生表(学号，姓名，性别，班级)     学号是主键
成绩表(学号,成绩) 
->成绩表的学号是外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。以另一个关系的外键作主关键字的表被称为主表,具有此外键的表被称为主表的从表。

外键必须是外表主键吗？

可以不是。只要表属性所存数据具有唯一性，那么该属性就可以为主键。 
外键是表间关系的参照，可以不是主键。只是表明两个表之间的关系是通过那个属性连接起来的。                                        参考： https://www.iteye.com/problems/56369

2.rs获得的内容的判断不能用null来进行判断，因为rs一定在数据库操作后不管成功还是失败都一定不是空对象

3.数据库的字段名和实体类的字段不一定非要一样 

对于jdbc： insert语句是写明插入什么什么字段的；查询返回对象也是，将resultset.getString()/...赋给对象的属性，再将对象返回                                                                                                       对于Mybatis:     insert语句是自己写名插入什么什么字段的；select resultType返回类时，也可以用select  as；resultMap的话也是有映射的，甚至在SpringBoot的Mybatis的mybatis.configuration.map-underscore-to-camel-case=true驼峰规范还能省略resultMap的映射                                                      

4.通配符%和_           

%与零个或多个字符组成的字符串匹配                                                                                                _与单个字符匹配                                                                

SELECT * FROM Persons WHERE City LIKE 'N%'		//以 "N" 开始
SELECT * FROM Persons WHERE City LIKE '%g'		//以 "g" 结尾
SELECT * FROM Persons WHERE City LIKE '%lon%'	//包含 "lon" 的

SELECT * FROM [user] WHERE u_name LIKE '_三_'  	//u_name为三个字且中间一个字是“三”的
SELECT * FROM [user] WHERE u_name LIKE '三__'	//name为三个字且第一个字是“三”的

5..where和group by 下面的having的区别：
having可以有聚合函数（如count()),where 后不能有聚合函数

6.数据库的数组下标从1开始,而不是从0开始                      

mysql> SELECT LOCATE('bar', 'foobarbar');

-> 4

而对于limit ,初始记录行的偏移量是 0(而不是 1)
参考：
https://bbs.csdn.net/topics/390211131
https://www.cnblogs.com/cai170221/p/7122289.html

7.JOIN是用于把表横向连接，UNION/UNION ALL是用于把表纵向连接(UNION 操作符用于合并两个或多个 SELECT 语句的结果集。)

8.关于distinct多列问题：
若想查询多列，如`select distinct name,nickname,department from photos;`是将三列完全相同的内容过滤掉，但凡三列有一列不同，均会列出来（也就是说此时distinct和不写是一样的）
若将distinct放后面，如select nickname,department，distinct name from photos;报错，distinct必须放在开头。将distinct放到where里，也是报错。
为了得到单一的值，我们需要使用这样的语句：`select name,nickname,department from photos where id in (select max(id) from photos group by name;（其实到这里就和distinct没什么关系了）`但是执行效率很低，能明显感觉出数据库的处理时间
参考：https://hae.iteye.com/blog/2183736
https://blog.csdn.net/wild46cat/article/details/78715099

9.mysql主查询和子查询的执行顺序：
子查询分为非相关嵌套子查询、相关嵌套子查询
对于非相关嵌套子查询：执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用；执行外部查询，并显示整个结果。　　
对于相关嵌套子查询：之前我只是从语义上意识到子查询要用到主查询，下面这是执行过程。
相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。
exists一定是相关子查询。
（1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。
（2）执行内层查询，得到子查询操作的值。
（3）外查询根据子查询返回的结果或结果集得到满足条件的行。
（4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。
很明显，一般情况下关联子查询的效率是比较低下的，实际上本例中的关联子查询例子也仅是为了演示关联子查询的原理及用法。如果可以的话，关联子查询尽量使用 JOIN 或其他查询来代替
参考：https://blog.csdn.net/yanhui_wei/article/details/22872633
https://www.jb51.net/article/76599.htm
https://blog.csdn.net/XiaodunLP/article/details/87743587

注意，不要写这种关联嵌套非关联的子查询，优化器都不知道怎么处理了。   

以及下面这个并非相关子查询。这个sql四号查询可能算不上相关子查询(比如某个字段值和c1相应字段值有什么关系），只是单纯用了外面查询的表c1，所以sql执行时会先执行子查询，这时c1还未生成，所以就报表c1不存在的错误了。

	select *                                            --把它称为一号查询
	from (select sc1.`c#` cc from  sc sc1 where sc1.`s#`='s3' )  c1       --把它称为二号查询
	where c1.cc not in (                                --把它称为三号查询
		select sc2.`c#`                                                                         
		from sc sc2
		where sc2.`s#` ='s1' and sc2.`c#` in (
			select  c1.cc                               --把它称为四号查询
			from c1
		) 
	);

参考：多层次子查询的sql执行顺序的问题

10.mysql查询记录返回的顺序是按什么排的?
需要顺序请使用orderby，无orderby的或者orderby的字段值重复了select返回的是集合，理论上无序的。（实际由于db的实现，有一定的顺序，但是这个是实现相关的，不依赖为上策）。如果你没有显示的指定返回结果的排序顺序, 请不要假定返回数据结果的顺序。有人说myisam引擎按插入修改的先后排，innodb引擎根据主键大小排。

一旦 order by 的 colunm 有多个相同的值的话，结果集是非常不稳定。   

那怎么解决呢，其实很简单，就是order by 加上唯一不重复的列即可，即在后面加上一个唯一索引就可以了，ORDER BY idnumber DESC , id DESC

select sc.`s#`
,sc.`c#`
,sc.score
from sc 
order by sc.`c#`

参考：https://www.iteye.com/problems/99656
https://bbs.csdn.net/topics/360005369                                                            https://blog.csdn.net/a241903820/article/details/81359379

11.mysql alter  add:     

1) 加索引
mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);
例子： mysql> alter table employee add index emp_name (name);      
2) 加主关键字的索引
mysql> alter table 表名 add primary key (字段名);
例子： mysql> alter table employee add primary key(id);
3) 加唯一限制条件的索引
mysql> alter table 表名 add unique 索引名 (字段名);
例子： mysql> alter table employee add unique emp_name2(cardnumber);
注意：对于上面的几种索引，删除某个索引
mysql> alter table 表名 drop index 索引名;
4) 增加字段
mysql> ALTER TABLE table_name ADD field_name field_type;                                                                                                      参考：http://www.fyluo.com/?post=79       

 

12. (1)mysql 注意了 null 不能用 '!='，'='，'<>' 来判断 虽然不会报错，但数据不正确。                                                                参考：https://blog.csdn.net/wangzaza/article/details/79484069

(2)sql中<>0 为什么不包含null的值                                                                                                                                         https://zhidao.baidu.com/question/315295921.html         

如果想囊括null的话，方法有：SELECT * FROM A WHERE B1 != 1 OR B1 is Null  或者  SELECT * FROM A WHERE IFNULL(B1,'') != 1    参考：https://blog.csdn.net/qq_33833327/article/details/81513152

(3)在分组子句与排序子句中，sql视null是相等的，即：GROUP BY会把所有NULL值分到一组。ORDER BY会把所有NULL值排列在一起。

 

13.case when 条件1 then 值1 (when 条件2 then 值2) (else 值3 ) end                                                                               （1）then 是返回值的，而不能做操作  不同于mybatis的<choose> <when> </when>里面是可以写操作的

正确：
update t_sd_youzansobill set  fnum= case when fnum is null then 2 END
错误：
update t_sd_youzansobill set  case when fnum is null then fnum=2 END

（2）case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略。

下面这段sql，你永远无法得到“第二类”这个结果
case when col_1 in ( 'a', 'b') then '第一类'
when col_1 in ('a') then '第二类'
else'其他' end

（3）如果没写else语句的话，又一个条件都不满足，返回null           

 mysql官方文档:

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END                                                                   CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

The first CASE syntax returns the result for the first value=compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

参考：https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#operator_case                                                  

update t_sd_youzansobill set  fnum= case when fnum is null then 2 END

 理所当然，sql不加限制条件，case无论是查询还是修改语句，在不加限制条件的情况下，默认是对全表进行操作（没有else的话就返回null）。所以从逻辑上来说，要么加上else ，使得其余情况等于原来的值（set t.moduleicon=case ...else t.moduleicon）；要么加上限制条件后，此时else可以省略，因为只有有限记录符合要求并要修改，不存在else的情况。

update t
set t.moduleicon = case 
                    when t.moduleicon="1"  then "11"
                    when t.moduleicon="2"  then "22"
                    when t.moduleicon="3"  then "33"
                    else t.moduleicon
                    end


update t
set t.moduleicon = case 
                    when t.moduleicon="1"  then "11"
                    when t.moduleicon="2"  then "22"
                    when t.moduleicon="3"  then "33"
                    else t.moduleicon        (此时else可以省略）
                    end
where （t.moduleicon=1 or t.moduleicon=2 or t.moduleicon=3）

参考：https://stackoverflow.com/questions/12754470/mysql-update-case-when-then-else                                                              https://www.bbsmax.com/A/gAJGMoLZdZ/           

14.对于聚合函数sum（A+B+C），如果运算的列中任意一列的值为NULL，则忽略这行的记录。

                                                      

二、报错

1.今天创建一个表时，感觉sql没错，但是表死活创建不出来
建表语句：

mysql> create table reader(
    -> reId int(11) not null AUTO_INCREMENT,
    -> reName varchar(45) not null,
    -> PRIMARY KEY (reId)
    -> );

报错`ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'reName varchar(45) not null,PRIMARY KEY (reId))' at line 3 `
经过查资料发现，RENAME是mysql的关键字，所以有保留的关键字必须用反引号` 包起来，非保留的关键字（如begin,and）不需要
参考资料：https://dev.mysql.com/doc/refman/5.5/en/keywords.html                                                                         

修改后：

mysql> create table reader(
    -> reId int(11) not null AUTO_INCREMENT,
    -> `reName` varchar(45) not null,
    -> PRIMARY KEY (reId)
    -> );