Section 1 chapter Basic knowledge of database 1
1.1 Database overview 1
1.1.1 Data and database 1
1.1.2 Development of database 2
1.1.3 Database management system 3
1.1.4 database system 4
1.1.5 Structured query language 5
1.1.6 Common relational database management system 7
1.2 MySQL Installation and configuration of 8
1.2.1 MySQL Installation and configuration of server 8
1.2.2 MySQL Graphical management tools 13
1.2.3 Connecting and disconnecting servers 16
Unit summary 17
Practical training 1 17
Thinking and practicing 18
Section 2 chapter Database design 19
2.1 Relational database design 19
2.1.1 Database design 19
2.1.2 Concept of data model 20
2.1.3 conceptual model 22
2.1.4 E-R Figure design example 24
2.1.5 Database relational model 27
2.2 Standardization of database design 29
2.2.1 Relational database paradigm theory 29
2.2.2 Database normalization instance 32
2.3 Comprehensive examples Petstore Design of database 34
2.3.1 Build a conceptual model according to the business logic of pet store ——Petstore E-R chart 34
2.3.2 take Petstore E-R A relational model for transforming graphs into data worlds 34
2.3.3 Petstore Database standardization 35
Unit summary 36
Practical training 2 36
Thinking and practicing 37
Section 3 chapter Data definition 39
3.1 Create and manage databases 39
3.1.1 Create database 39
3.1.2 Management database 44
3.2 Create and manage database tables 45
3.2.1 Create database table 45
3.2.2 Manage database tables 49
3.3 Data integrity constraints 52
3.3.1 Data integrity constraints 52
3.3.2 Primary key constraint 54
3.3.3 Override key constraint 55
3.3.4 Referential integrity constraints 57
3.3.5 CHECK integrity constraint 60
3.4 Graphics management tools manage databases and tables 61
3.4.1 Graphical interface tool management database 62
3.4.2 GUI tools manage database tables 64
3.5 Comprehensive examples Petstore Data definition 66
3.5.1 Petstore Analysis of database table structure 66
3.5.2 establish Petstore Databases and tables 68
3.5.3 Establish data integrity constraints 69
Unit summary 70
Practical training 3 70
Thinking and practicing 72
Section 4 chapter Data update 75
4.1 Data insertion 75
4.2 Data modification 77
4.3 Data deletion 79
4.3.1 use DELETE Statement delete data 79
4.3.2 use TRUNCATE TABLE Statement to delete table data 80
4.4 Comprehensive examples Petstore Data update 80
4.4.1 Petstore Data sheet and its contents 80
4.4.2 Petstore Data entry 82
4.4.3 Petstore Data modification and deletion 84
Unit summary 86
Practical training 4 86
Thinking and practicing 87
Section 5 chapter Data query 88
5.1 Single table query 88
5.1.1 SELECT Statement definition 88
5.1.2 Select column 89
5.1.3 WHERE clause 93
5.2 multi-table query 97
5.2.1 FROM clause 97
5.2.2 Multi table connection 98
5.3 Subtotals and sorting 106
5.3.1 Aggregate function 106
5.3.2 GROUP BY clause 108
5.3.3 HAVING clause 110
5.3.4 ORDER BY clause 112
5.3.5 LIMIT clause 113
5.4 UNION sentence 113
5.5 Comprehensive examples Petstore Data query 114
5.5.1 SELECT Basic use of statements 114
5.5.2 Condition query 115
5.5.3 multi-table query 115
5.5.4 Subtotals and sorting 116
Unit summary 116
Practical training 5 117
Thinking and practicing 119
Section 6 chapter Data view 122
6.1 Create view 122
6.1.1 View concept 122
6.1.2 Create view 122
6.1.3 Query view 124
6.2 Operation view 125
6.2.1 Manipulate data through views 125
6.2.2 Modify view definition 127
6.2.3 Delete view 127
6.3 Comprehensive examples Petstore Data view 128
Unit summary 128
Practical training 6 129
Thinking and practicing 129
Section 7 chapter Indexes 131
7.1 Classification of indexes 131
7.2 Create index 132
7.2.1 use CREATE INDEX sentence 132
7.2.2 use ALTER TABLE sentence 132
7.2.3 Create index when creating table 133
7.3 Delete index 134
7.4 Influence of index on query 134
7.5 Comprehensive examples Petstore Indexes 135
Unit summary 136
Practical training 7 136
Thinking and practicing 137
Section 8 chapter Database programming 138
8.1 Programming basics 138
8.1.1 Constant and Variable 138
8.1.2 System built-in functions 140
8.1.3 Process control statement 143
8.2 Create stored procedure 146
8.2.1 Create stored procedure 146
8.2.2 Show stored procedures 148
8.2.3 Call stored procedure 149
8.2.4 Delete stored procedure 151
8.2.5 Nesting of stored procedures 151
8.3 Create and call storage functions 152
8.3.1 Create storage function 153
8.3.2 Call storage function 154
8.4 set trigger 155
8.4.1 Create trigger 155
8.4.2 Call stored procedure in trigger 159
8.4.3 Delete trigger 159
8.5 Comprehensive examples Petstore Database programming 160
Unit summary 164
Practical training 8 164
Thinking and practicing 165
Section 9 chapter data security 166
9.1 User and Data permission management 166
9.1.1 Add and delete users 166
9.1.2 Grant permission and recycle permission 168
9.1.3 Graphics management tools manage users and permissions 172
9.2 Data backup and recovery 175
9.2.1 Backup and recovery requirements analysis 175
9.2.2 Database backup and recovery 175
9.2.3 MySQL journal 179
9.3 Comprehensive examples Petstore Database management 182
Unit summary 183
Practical training 9 183
Thinking and practicing 183
Technology