SQL碎知识

易夫人0

已于 2022-04-19 18:25:59 修改

阅读量100

点赞数

文章标签： sql 触发器

于 2022-03-04 23:31:00 首次发布

本文链接：https://blog.csdn.net/weixin_43849243/article/details/123026732

版权

1、在已知表格添加一列
ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名];

在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’

alter table actor
add COLUMN create_date datetime not null DEFAULT '2020-10-01 00:00:00'
after last_update

2、构建触发器：即当发生某一事件时，如果满足给定条件，则执行相应的动作。

创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。
【NEW 与 OLD 详解】
MySQL 中定义了 NEW 和 OLD，用来表示
触发器的所在表中，触发了触发器的那一行数据。
具体地：

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
参考
https://blog.csdn.net/weixin_41177699/article/details/80302987

eg:
create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end

CREATETRIGGER trigger_name

ON table|view

FOR|AFTER|INSTEADOF [DELETE][,INSERT][,UPDATE]

AS

IFUPDATE(column)

[{AND|OR}UPDATE(COLUMN)][…]

IF(COLUMNS_UPDATED())

Sql_statement[…n]

3、在表中删除重复数据
注：在SQL中子查询中删除和更新不可同时进行

DELETE FROM titles_test
WHERE id NOT IN(
    SELECT * FROM(
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no)a);  -- 把得出的表重命名，那就不是原表了

4、更新表数据
结构：
UPDATE 表名
SET 字段=值
WHERE 过滤条件

UPDATE titles_test
SET to_date = NULL, from_date = '2001-01-01'
WHERE to_date = '9999-01-01';

5、插入数据
insert into： 正常的插入数据，插入数据的时候会检查主键或者唯一索引，如果出现重复就会报错；
replace into： 表示插入并替换数据，若表中有primary key或者unique索引，在插入数据的时候，若遇到重复的数据，则用新数据替换，如果没有数据效果则和insert into一样；
replace into titles_test values
(5,10005, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’)

insert ignore into： 插入并忽略数据，如果中已经存在相同的记录，则忽略当前新数据。这样不用校验是否存在了，有则忽略，无则添加

详细参考：https://blog.csdn.net/weixin_42272869/article/details/116480732?spm=1001.2014.3001.5501

6、关于表的操作
（1）alter
ALTER TABLE 表名 ADD 列名/索引/主键/外键等；
ALTER TABLE 表名 DROP 列名/索引/主键/外键等；
ALTER TABLE 表名 ALTER 仅用来改变某列的默认值；
ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名；
ALTER TABLE 表名 RENAME TO/AS 新表名;
（rename：rename table titles_test to titles_2017）
ALTER TABLE 表名 MODIFY 列的定义但不改变列名；
ALTER TABLE 表名 CHANGE 列名和定义都可以改变。

7、创建外键
创建外键语句结构：
ALTER TABLE <表名>
ADD CONSTRAINT FOREIGN KEY (<列名>)
REFERENCES <关联表>（关联列）

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

8、按组汇总
group_concat(x,y)
其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号

SELECT dept_no, group_concat(emp_no)AS employees
FROM dept_emp
GROUP BY dept_no;

9、根据表格匹配查找 in 、exists
exists判断是否存在某种条件的记录，如果存在这种记录就返回TRUE,否则返回FALSE.相当于IN。NOT EXISTS 相当于NOT IN

select * from employees
where emp_no not IN
(
select emp_no from dept_emp
)

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

10、case when then end 名

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary,
case when eb.btype=1 then s.salary*0.1
     when eb.btype=2 then s.salary*0.2
     else s.salary*0.3
end bonus
from emp_bonus eb,employees e,salaries s
where eb.emp_no=e.emp_no
and s.emp_no=e.emp_no
and s.to_date='9999-01-01'

11、窗口函数之sum()over() 求和当前累计数据

sum(salary)over(order by emp_no acs)

12、奇数、偶数表达形式
查询奇数：字段&1
SELECT a.user_id FROM t_user_info a WHERE a.user_id&1 LIMIT 5

查询偶数：字段=（字段>>1）<<1
这是左移和右移运算符，先整除再乘，意思就是先整除2再乘以2，看前后结果是否一样，一样的就是偶数。
SELECT a.user_id FROM t_user_info a WHERE a.user_id=(a.user_id>>1)<<1 LIMIT 5

正则表达形式参考：https://blog.csdn.net/ccStroy/article/details/78061861

13、排序函数
row_number()对应唯一排序：1、2、3、4
dense_rank()对应相同次序可重复，但不跳过下一个次序值：1、2、2、3
rank()对应相同次序可重复，并且跳过下一个次序值：1、2、2、4

14、取中位数位置及位置上的数
（1）取中位数的位置（无须区分奇数偶数个总个数）

#FLOOR(x) 返回小于或等于 x 的最大整数.
#sum(1)跟count(*)是一个意思都是对获取总行数的意思。
floor((sum(1)+1)/2) as start     #中位数开始的位置
floor((sum(1)+2)/2)as end      #中位数结束的位置

（2）取中位数上的数
思想：正序排列和逆序排列大于1/2总数公共的部分则是中位数

select grade from (select grade,(select sum(number) from class_grade) as total,
        sum(number) over(order by grade) a,
        sum(number) over(order by grade desc) b
        from class_grade) t1
where a >= total/2 and b >=total/2
order by grade;

14、删除表中重复数据
可考虑表自连接，where筛选

易夫人0

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL碎知识

1、在已知表格添加一列ALTER TABLE <表名> ADD COLUMN <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名];在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’alter table actoradd COLUMN create_date datetime not null DEFAULT '202
复制链接

扫一扫