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)