INTRODUCTION ..1
RDBMS: AN INTRODUCTION .2
1. OVERVIEW 3
2. SQL- 92 ..3
3. RELATIONAL DATABASES.4
3.1. Relational vs. flat file databases ..4
3.2. Database Normalization ..4
MYSQL: AN INTRODUCTION .6
2. CONFORMANCE WITH SQL STANDARDS 7
3. MYSQL CLIENT PROGRAMS ..7
3.1. The mysql Client Program..7
3.2. The mysqlimport Client Program ..8
3.3. The mysqldump Client Program .8
3.4. The mysqlcheck and myisamchk Client Program 9
4. GENERAL DATABASE AND TABLE MANIPULATION 9
5. STORAGE ENGINES AND TABLE TYPES ..10
6. COLUMN TYPES10
6.1. Numerics11
6.2. Strings12
6.3. Dates ..13
6.4. Complex Types ..14
7. COLUMN OPTIONS 15
8. OPERATORS 15
8.1. Precedence Rule 15
8.2. Arithmetic Operators ..16
8.3. Comparison Op erators ..16
8.4. Logical Operators17
8.5. MySQL Conversions17
9. CONNECTING TO, AND DISCONNECTING FROM, THE SERVER .17
10. CREATING AND USING A DATABASE 18
11. TABLE MANIPULATION.19
11.1. Creating a Table.19
11.2. Dropping a Table ..19
11.3. Altering a Table ..19
11.4. Renaming a table20
12. INDEXES 20
12.1. Non-unique Indexes: .21
12.2. Unique Indexes 21
12.3. Primary Key Indexes21
12.4. Dropping Indexes ..22
13. MYSQL QUERIES 22
13.1. The SELECT Statements .22
13.1.1. Basic Data Retrieval.. .22
13.1.2. Restricting a selection using WHERE C lause . .23
13.1.3. Sorting a select ion using ORDER BY Clause ..23
13.1.4. Limiting a select ion using LIMIT Clause 23
13.1.5. Grouping a selection using G ROUP BY Clause . .24
13.2. The INSERT Statement 25
13.2.1. Adding Multiple Records with a Single INSERT Statement . 26
13.3. REPLACE Statement27
13.4. UPDATE Statement ..27
13.5. DELETE Statement28
14. JOINS .28
14.1. Cross-join..28
14.2. Inner Join..28
14.3. Outer Join..29
14.3.1. Left Join.29
14.3.2. Right Join .. ..30
15. REFERENCE .30
MYSQL: ADMINISTRATION .31
1. DOWNLOADING AND INSTALLING MYSQL ..32
2. THE MYSQL DATA DIRECTORY 33
2.1. Location of the Data Directory ..33
2.2. Structure of Data Directory .34
2.3. How the MySQL Server provides access to Data ..34
2.4. Database Table Representation.34
3. MYSQL SERVER START-UP AND SHUTDOWN 35
3.1. The MySQL Server and Server Startup Scripts ..35
3.1.1. An Overview of the Server-Side Scripts .. ..35
3.1.2. Methods of Start ing the Server 35
3.1.3. Regaining the control of the Server if you cannot connect: . .35
4. MYSQL USER ACCOUNT MANAGEMENT .36
4.1. MySQL Usernames and Passwords .36
4.2. Adding New User Accounts to MySQL..37
4.3. Removing User Accounts from MySQL..39
4.4. Limiting Account Resources 40
4.5. Assigning Account Passwords 41
5. DISASTER PREVENTION AND RECOVERY .41
5.1. Database Back-ups .41
5.2. Back-up Principles ..41
5.3. Back-up Methods ..41
5.3.1. Direct copy method 41
5.3.2. Mysqlhotcopy . ..41
5.3.3. mysqldump .42
6. REPLICATION IN MYSQL .43
6.1. Set-up Database Replication in MySQL 43
6.1.1. Configure the Master .. ..43
6.1.2. Configure the Slave .. .44
7. REFERENCE 45
COMPARISON OF ORACLE, MYSQL AND POSTGRESQL DBMS 46
1. ELEMENTARY FEATURES: 47
1.1. Conformity with SQL standards: ..47
1.2. Binary and Character Large Objects: 47
1.3. User-defined data types: 47
1.4. Object-relational extensions: ..47
1.5. Special data types (multimedia, etc.) ..47
1.6. Sub-queries in SQL query: 48
1.7. Primary and Unique keys: 48
1.8. Foreign key: 48
1.9. Check:48
1.10. Views: ..48
1.11. Updateable views: 48
1.12. Synonyms (an alias for any table, view or other object in database): ..48
1.13. Auto Increment (counter) Columns: ..49
1.14. Automatic conversion of code pages (e.g. between client and server): 49
2. TRANSACTIONS .49
2.1. Support for transaction pro cessing:49
2.2. Partial rollback of transaction:.49
2.3. Locking level (table, page, and row): .49
2.4. Deadlock Detection and Resolving:49
3. PROGRAMMING IN DB 49
3.1. Languages for writing stored procedures: 49
3.2. Triggers:50
4. ADMINISTRATION ..50
4.1. User authorization: ..50
4.2. Encrypted Client - Server connection: 50
4.3. Object access privileges (level).50
4.4. Access privileges grouping: .51
4.5. Incremental and on-line backups: .51
4.7. Tools for data import: .51
5. PORTABILITY AND SCALABILITY 51
5.1. Hardware and system platforms supported: 51
5.2. Portability of data and code (e.g. stored procedures) .51
5.3. Support for SMP systems (parallel query execution, etc.): ..52
6. PERFORMANCE AND VLDB 52
6.1. Known VLDB implementations: 52
6.2. Maximum number of rows and columns in a table: .52
6.3. Maximum number of tables and indexes:..52
6.4. "Manual" tuning of the allocation: ..52
6.5. Automatic partitioning of large tables/indexes and using partitions in query optimization:52
6.6. Access to multiple databases in one session: ..53
6.7. Replication: .53
6.8. Gateways to other DBMSs:..53
7. APPLICATION DEVELOPMENT AND INTERFACES ..53
7.1. Standard interfac es ODBC and JDBC: .53
7.2. Application support:..54
7.3. Dedicated Web servers: .54
7.4. XML support integrated in DBMS: ..54
7.5. Support from CASE packages: 54
8. RELIABILITY 54
8.1. Automatic recovery from failures:54
9. COMMERCIAL ISSUES .55
9.1. License type: 55
9.2. Technical support:55
9.3. Market share: 55
9.4. Specific market segments occupied:55

Attached Files

Chapter 5 RDBMS MySQL (bzupages.com).pdf (683.2 KB, 316 views)

__________________