牛客网SQL篇刷题篇（38-47）

1.视图：视图是可视化的表。

视图的作用：

第一点：使用视图，可以定制用户数据，聚焦特定的数据。

解释：

在实际过程中，公司有不同角色的工作人员，我们以销售公司为例的话，

采购人员，可以需要一些与其有关的数据，而与他无关的数据，对他没

有任何意义，我们可以根据这一实际情况，专门为采购人员创建一个视

图，以后他在查询数据时，只需select * from view_caigou 就

可以啦。

第二点：使用视图，可以简化数据操作。

解释：我们在使用查询时，在很多时候我们要使用聚合函数，同时还要

显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能

会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我

们只需要select * from view1就可以啦～，是不是很方便呀～

第三点：使用视图，基表中的数据就有了一定的安全性

解释：

因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以

将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集

合，数据是随着基表的更新而更新。同时，用户对视图，不可以随意的更改

和删除，可以保证数据的安全性。

第四点：可以合并分离的数据，创建分区视图

解释：

随着社会的发展，公司的业务量的不断的扩大，一个大公司，下属都设有很

多的分公司，为了管理方便，我们需要统一表的结构，定期查看各公司业务

情况，而分别看各个公司的数据很不方便，没有很好的可比性，如果将这些

数据合并为一个表格里，就方便多啦，这时我们就可以使用union关键字，

将各分公司的数据合并为一个视图。

创建视图:

CREATE VIEW view_name

AS SELECT column_name(s) FROM table_name WHERE condition

2.强制索引

MYSQL中强制索引查询使用：FORCE INDEX(indexname);

SQLite中强制索引查询使用：INDEXED BY indexname;

题目：针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005，使用强制索引。

在SQLite中：

1	SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005

在Mysql中：

1 2	create index idx_emp_no on salaries(emp_no); select * from salaries FORCE INDEX (idx_emp_no) where emp_no = 10005;

2.ALTER TABLE 语句

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

如需在表中添加列，请使用下列语法:

ALTER TABLE table_name ADD column_name datatype

要删除表中的列，请使用下列语法：

ALTER TABLE table_name DROP COLUMN column_name

要改变表中列的数据类型，请使用下列语法：

ALTER TABLE table_name ALTER COLUMN column_name datatype

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

ALTER TABLE actor

ADD create_date datetime NOT NULL default '2020-10-01 00:00:00'

3.删除重复记录

题目描述：删除emp_no重复的记录，只保留最小的id对应的记录。

 

讨论中答案：

题目描述：删除emp_no重复的记录，只保留最小的id对应的记录。

思路：1.找出每个emp_no里对应的最小id。2.删除除1里记录的所有数据。

1 2 3 4 5 6 7

delete from titles_test where id not in (     select min(id)     from titles_test     group by emp_no )

遭遇问题：you can't specify target table 'titles_test' for update in FROM clause

网查原因：在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录，

解决办法：将select得到的结果，再通过中间表select一遍，这样就规避了错误，这个问题只出现于mysql，mssql和oracle不会出现此问题。

复制代码

1 2 3 4 5 6 7 8 9 10

delete from titles_test where id not in (     select * from     (         select min(id)         from titles_test         group by emp_no     ) as T1 )

4.replace函数

描述

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现，直接使用update会报错。

CREATE TABLE titles_test (    id int(11) not null primary key,    emp_no  int(11) NOT NULL,    title  varchar(50) NOT NULL,    from_date  date NOT NULL,    to_date  date DEFAULT NULL); insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'), ('2', '10002', 'Staff', '1996-08-03', '9999-01-01'), ('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'), ('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'), ('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'), ('6', '10002', 'Staff', '1996-08-03', '9999-01-01'), ('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

题解

update titles_test set emp_no=replace(emp_no,"10001","10005")

where id=5

http://https://blog.csdn.net/bingguang1993/article/details/80592579/（SQL中的替换函数replace()使用）

5.修改表名

题目：将titles_test表名修改为titles_2017

题解：

RENAME TABLE titles_test to titles_2017;

ALTER TABLE titles_test RENAME TO titles_2017;

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

创建外键语句结构：

ALTER TABLE <表名>

ADD CONSTRAINT FOREIGN KEY (<列名>)

REFERENCES <关联表>（关联列）

eg：alter table audit

add constraint FK_ID foreign key (emp_no)

REFERENCES employees_test(id)