SQL查漏补缺

最新推荐文章于 2023-03-05 16:59:39 发布

原创最新推荐文章于 2023-03-05 16:59:39 发布 · 151 阅读

1 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

4 篇文章

订阅专栏

1.奇偶数

奇数 – [expression]%2 = 1
偶数 – [expression]%2 = 0

SELECT *
FROM employees
WHERE emp_no%2=1
AND last_name NOT LIKE 'Mary'
ORDER BY hire_date DESC;

2.MySQL中四种方式给字段添加索引

使用Alter创建索引

添加主键

ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);

// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

添加唯一索引

ALTER TABLE tbl_name ADD UNIQUE INDEX index_name (col_list);

// 这条语句创建索引的值必须是唯一的。

添加普通索引

ALTER TABLE tbl_name ADD INDEX index_name (col_list);

// 添加普通索引，索引值可出现多次。

添加全文索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);

// 该语句指定了索引为 FULLTEXT ，用于全文索引。
PS: 附赠删除索引的语法：

DROP INDEX index_name ON tbl_name;

// 或者

ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;

使用Create创建索引

语法：create index 索引名 on 表名(字段)

添加唯一索引

create index 索引名 on table 表名(col1, col2, ..., )

添加普通索引

create unique index 索引名 on table 表名(col1, col2, ..., )

两种创建索引方式的区别

Alter可以省略索引名。如果省略索引名，数据库会默认根据第一个索引列赋予一个名称；Create必须指定索引名称。

Create不能用于创建Primary key索引；

Alter允许一条语句同时创建多个索引；Create一次只能创建一个索引

ALTER TABLE 表名 ADD Primary key (id), ADD index <索引名> (col1, col2, …,)

3.强制索引FORCE INDEX

FORCE INDEX强制查询优化器使用指定的命名索引。查询优化器是MySQL数据库服务器中的一个组件，它为SQL语句提供最佳的执行计划。查询优化器使用可用的统计信息来提出所有候选计划中成本最低的计划。
书写顺序;

SELECT……
FROM ……
FORCE INDEX(index_name)
WHERE……
参考：MySQL 强制索引

4.新增加一列
默认值 ‘2020-10-01 00:00:00’

ALTER TABLE actor
ADD column create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00';

构造一个触发器audit_log

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

在MySQL中，创建触发器语法如下：
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.youkuaiyun.com/weixin_41177699/article/details/80302987

5.表名修改

alter table titles_test
rename as titles_2017

6.ADD CONSTRAINT FOREIGN KEY 创建外键约束

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

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

7.连接两列
聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
此函数必须与GROUP BY配合使用。此题以dept_no作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。

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

8.MySQL IFNULL() 函数

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL() 函数语法格式为：

IFNULL(expression, alt_value)

9.DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

date 参数是合法的日期。format 规定日期/时间的输出格式。

可以使用的格式有：

常用格式对应描述
%Y --年，4 位
%m --月，数值（00-12）
%M --月名
%k --小时（0-23）
日期函数详解转载自：https://www.runoob.com/sql/func-date-format.html

– 按月统计数量并排序

select job,date_format(date,'%Y-%m') as mon,sum(num) as cnt
from resume_info
where date like '2025%'  -- 符合最左前缀匹配原则，也走索引
group by job,mon
order by mon desc,cnt desc;