您可以从表创建视图
user_roles：
CREATE VIEW user_roles_view AS SELECt U.user_name, R.role_name FROM user_roles AS UR INNER JOIN users AS U ON u.user_id = UR.user_id INNER JOIN roles AS R ON r.role_id = UR.role_id;
SQLite 中的视图是只读的，除非您在其上创建INSTEAD OF 触发器。通过这种方式，您可以指定在使用
INSERT,
UPDATe或
DELETE语句修改视图时执行的命令或命令序列。因为
INSERT它可以是这样的：
CREATE TRIGGER user_roles_view_insert INSTEAD OF INSERT ON user_roles_viewBEGIN INSERT OR IGNORE INTO users (user_name) VALUES (NEW.user_name); INSERT OR IGNORE INTO roles (role_name) VALUES (NEW.role_name); INSERT OR IGNORE INTO user_roles (user_id, role_id) VALUES ( (SELECT user_id FROM users WHERe user_name = NEW.user_name), (SELECt role_id FROM roles WHERe role_name = NEW.role_name) );END;
请注意
INSERT OR IGNORE防止将重复值插入到所有三个表中的用法。以下是通过视图插入值的方法：
INSERT INTO user_roles_view VALUES ('Joe', 'admin');-- The above statement creates:-- a row (1, 'Joe') in table users,-- a row (1, 'admin) in table roles,-- a row (1, 1) in table user_roles.INSERT INTO user_roles_view VALUES ('Joe', 'admin');-- The above statement doesn't add any additional records, because all appropriate records-- already exist.INSERT INTO user_roles_view VALUES ('Joe', 'system');-- The above statement creates:-- a row (2, 'system') in table roles,-- a row (1, 2) in table user_roles.INSERT INTO user_roles_view VALUES ('Alice', 'admin'), ('Bob', 'system');-- The above statement creates:-- a row (2, 'Alice') in table users,-- a row (3, 'Bob') in table users,-- a row (2, 1) in table user_roles,-- a row (3, 2) in table user_roles以上所有语句都从 user_roles_view (
SELECt * FROM user_roles_view)产生以下输出：
