SQL语言总结的知识点（续）

1.常用思想

（1）分页问题，自己的这个记录就很直观了。
https://blog.csdn.net/qq_38070686/article/details/115179356
(2)EXISTS 和 IN的用法以及区别

1.什么时候用EXISTS，什么时候用IN？
主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：
当主表比从表大时，IN查询的效率较高；
当从表比主表大时，EXISTS查询的效率较高；
原因如下：
in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次
https://www.cnblogs.com/hider/p/12446035.html
(3) 工作表一般都要注意工作日期这种隐含的约束条件。
观察每个表的字段。
(4) CASE WHEN ELSE,这个很全面
https://www.cnblogs.com/gengyufei/p/12614387.html

2.必须知道的知识

（1）触发器
触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。
https://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html

https://blog.csdn.net/qq_36330228/article/details/90582493

1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
2、触发器执行的内容写出 BEGIN与END 之间
3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录
CREATE TRIGGER audit_log
AFTER INSERT ON employees_test
FOR EACH ROW
BEGIN
INSERT INTO audit VALUES(NEW.id,NEW.name);
END;

(2)DELETE 的常见问题

中间添加一个SELECT就可以了，这是mysql的问题：
DELETE FROM sql_test WHERE id NOT IN (SELECT * FROM (SELECT MIN(id) AS col1 FROM sql_test GROUP BY num,NAME,lesson_name,lesson_num,mark
) AS tmp);

加粗的部分不可以少！！ AS 。。也不能少。否则报错
You can’t specify target table ‘sql_test’ for update in FROM clause
或者另外一种方法：
DELETE FROM titles_test WHERE id NOT IN (SELECT t.min_id FROM
(SELECT min(id) as min_id FROM titles_test b GROUP BY emp_no ) as t);
https://www.cnblogs.com/pcheng/p/4950383.html

(3)UPDATE
UPDATE 表名称 SET 列名称 = 新值, 列名称 = 新值WHERE 列名称 = 某值
(4)INSERT
INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)
INSERT INTO 表名称 VALUES (值1, 值2,…)

(5)Replace
REPLACE(String,from_str,to_str)
将String中所有出现的from_str替换为to_str
https://blog.csdn.net/qq_31909625/article/details/89517413?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-0&spm=1001.2101.3001.4242

(6) 修改表名：
RENAME TABLE t1 TO t2;

(7)在表上创建外键约束

-sql语句创建表的同时添加外键约束
CREATE TABLE tb_UserAndRole --用户角色表
(
ID INT PRIMARY KEY IDENTITY(1,1),
UserID INT NOT NULL,–用户ID
RoleID INT NOT NULL,–角色ID
foreign key(UserID) references tb_Users(ID)

–tb_Users表的ID作为tb_UserAndRole表的外键
)

–2、添加外键约束(关联字段要用括号括起来)

– ALTER TABLE 从表

– ADD CONSTRAINT 约束名 FOREIGN KEY (关联字段) references 主表**(关联字段)**;

–例如：

ALTER TABLE tb_UserAndRole

ADD CONSTRAINT FK__tb_UandR_Role FOREIGN KEY (RoleID) references tb_Role(ID);

ALTER TABLE audit
ADD CONSTRAINT fk_emp_no FOREIGN KEY (emp_no) REFERENCES employees_test(id);

（8）SQL运算
加减乘除可以直接使用。

（9）SQL连接字段
https://blog.csdn.net/qq_21101587/article/details/76229089
SQL> SELECT CONCAT(id, name, work_date)
-> FROM employee_tbl;
SELECT CONCAT(last_name,’’’’,first_name) FROM employees;#单引号作分隔符
(10) 查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
统计字符串长度：

char_length('string')/char_length(column_name)

1、返回值为字符串string或者对应字段长度，长度的单位为字符，一个多字节字符（例如，汉字）算作一个单字符；
2、不管汉字还是数字或者是字母都算是一个字符；
3、任何编码下，多字节字符都算是一个字符；

https://blog.csdn.net/iris_xuting/article/details/53763894

length('string')/length(column_name)

1、utf8字符集编码下,一个汉字是算三个字符,一个数字或字母算一个字符。
2、其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。

字符串替换：REPLACE(s,s1,s2)，将字符串 s2 替代字符串 s 中的字符串 s1
MySQL常用函数：https://www.runoob.com/mysql/mysql-functions.html

代码：

复制代码

select char_length("10,A,B")-char_length(replace("10,A,B",",",""))

或者直接利用瓦特了的OJ系统

select 2

(10)取字段的一部分

select
name as 正常,
right(name,3) as 右边3位,
left(name,3) as 左边3位
from sys.databases

(11) 分组后，将分组内的字段按特定符号进行连接。

dept_no employees
d001 10001,10002
d002 10006
d003 10005
d004 10003,10004
d005 10007,10008,10010
MYSQL的group_concat()函数

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
逗号分隔(默认)
SELECT dept_no,group_concat(emp_no) as employees FROM dept_emp GROUP BY dept_no;
SELECT dept_no,group_concat(emp_no separator ‘,’) as employees FROM dept_emp GROUP BY dept_no;

（12）SQL平均值函数AVG()

（13）去掉一个最大值，去掉一个最小值

错误：

~~SELECT AVG(salary) as avg_salary FROM salaries WHERE to_date='9999-01-01' AND salary NOT IN
(SELECT MIN(salary),MAX(salary) FROM salaries);~~ 

SELECT AVG(salary) as avg_salary FROM salaries WHERE to_date='9999-01-01' 
AND salary NOT IN
(SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')
AND salary NOT IN
(SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01');

（14）EXISTS

select *
from table_a a
where exists (select 1 from table_b b where b.id = b.id);

exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录;反之如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false。
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次
exists关键字:强调的是是否返回结果集，不要求知道返回什么。只要返回了字段，就是真。

SELECT * FROM employees a WHERE NOT EXISTS 
(SELECT emp_no FROM dept_emp b WHERE b.emp_no =a.emp_no);

IN：确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

EXISTS：指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。
(15)CASE WHEN 的应用
https://blog.csdn.net/rongtaoup/article/details/82183743

SELECT a.emp_no,a.first_name,a.last_name,bo.btype,bo.salary,bo.bonus 
FROM  employees a INNER JOIN
(SELECT a.emp_no,a.btype,b.salary,(
      CASE WHEN a.btype=1 THEN b.salary*0.1
           WHEN  a.btype=2 THEN b.salary*0.2
           WHEN  a.btype=3 THEN b.salary*0.3
      ELSE 'UNnormal' 
      END
) AS bonus FROM emp_bonus a  JOIN salaries b ON a.emp_no=b.emp_no WHERE b.to_date='9999-01-01') bo
ON a.emp_no=bo.emp_no;

(16)67题，关于查询分组内最大值的问题。
本题的易错点在于
-group by 分组后默认显示的是第一条记录
-MAX() 取最大值
举个例子

select id,name,MAX(num)
from xx
group by id

在这个代码里，只能查询出来num的最大值所对应的id，但是name很有可能不对应。
显示出的 MAX值与name并不对应的，解决方法可以用子查询进行替代。
MAX取的的确是最大值，但如果你select * 之后会发现最大值并不是对应它同一行的信息，只是group by这个组的最大值而已。

（17）排序函数！！！
https://www.cnblogs.com/shizhijie/p/9366247.html