TAILIEUCHUNG - SQL PROGRAMMING STYLE- P17

SQL PROGRAMMING STYLE- P17:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | VIEWs Provide Row- and Column-Level Security 137 FROM Projects WHERE authorized_user CURRENT_USER Or if you need to limit access to a team you can create a table of teams to which only team managers have access. CREATE VIEW MyProjects . AS SELECT . FROM Projects AS P WHERE CURRENT_USER IN SELECT team_user_id FROM ProjectTeams AS PT WHERE Another trick is to use the CURRENT_TIMESTAMP or CURRENT_DATE in VIEWs to get an automatic update to schedules and other time-related events. CREATE TABLE AssignmentSchedule ssn CHAR 9 NOT NULL REFERENCES Personnel ssn ON UPDATE CASCADE ON DELETE CASCADE task_code CHAR 5 NOT NULL start_date TIMESTAMP NOT NULL end_date TIMESTAMP NOT NULL CHECK start_date end_date PRIMARY KEY upc start_date CREATE VIEW Assignments now ssn task_code AS SELECT CURRENT_TIMESTAMP ssn task_code FROM AssignmentSchedule WHERE CURRENT_TIMESTAMP BETWEEN start_date AND end_date Each time the VIEW is invoked it will check the clock and see if anything has changed for you. 138 CHAPTER 7 HOW TO USE VIEWS VIEWs Ensure Efficient Access Paths By coding the appropriate join criteria into the VIEW definition SQL you can ensure that the correct join predicate will always be used. Of course this technique becomes more useful as the SQL becomes more complex. VIEWs Mask Complexity from the User Somewhat akin to coding appropriate access into VIEWs complex SQL can be coded into VIEWs to mask the complexity from the user. This can be extremely useful when your shop employs novice SQL users whether those users are programmers analysts managers or typical end users . As an example consider the code for a relational division. Relational division is one of the eight basic operations in Codd s 1979 relational algebra. The idea is that a divisor table is used to partition a dividend table and produce a quotient or results table. The quotient table consists of those values of one column for which a second column had all of the values in the .

• Cơ sở dữ liệu
• cơ sở dữ liệu MySQL
• database SQL
• thiết kế CSDL quan hệ
• Kiến trúc hệ quản trị CSDL
• đặc tính cơ sở dữ liệu
• bảo mật cơ sở dữ liệu
• Cơ sở dữ liệu
• Hệ quản trị cơ sở dữ liệu MySQL
• Quản trị cơ sở dữ liệu
• Làm việc với MySQL
• Giới thiệu về MySQL
• Bài giảng Phần mềm nguồn mở
• Phần mềm nguồn mở
• Hệ quản trị cơ sở dữ liệu
• Lịch sử phát triển MySQL
• Kiến trúc MySQL
• MySQL Command Line
• MySQL Query Browser
• giáo trình cơ sở dữ liệu
• lý thuyết cơ sở dữ liệu
• bái giảng cơ sở dữ liệu
• tài liệu cơ sở dữ liệu
• Kết nối với MySQL
• Truy xuất cơ sở dữ liệu
• Quản lý dữ liệu
• Kết nối cơ sở dữ liệu
• Cập nhật dữ liệu
• Kích hoạt dữ liệu
• cơ sở dữ liệu sql server
• MySQL cơ bản