"MySQL5.7 from beginner to Proficient (Video tutorial Edition)"

The video address: http://pan.baidu.com/s/1slVvI8H password: 7e4n; PDF in the group file of QQ Group (668345923)

This book mainly includes the installation and configuration of MySQL, database creation, data table creation, data types and operators, MySQL functions, query data, data table operations (INSERT, update and delete data), indexes, stored procedures and functions, views, triggers, user management, data backup and recovery, Logs, performance optimizations, and more. Finally, the application of MySQL in the practical work is further narrated through the database design of two comprehensive cases.

This book focuses on practical operation, helping the reader to master the various techniques in MySQL in a gradual manner. There are 480 examples and 14 comprehensive cases in this book, as well as a large number of classic exercises. The download provides a video tutorial of nearly 20 hours of training sessions, detailing every knowledge point in the book and the methods and techniques of each database operation. The source code for all the examples in this book is also available in the download file, which can be viewed and invoked directly by the reader.

This book is suitable for beginners of MySQL database, MySQL database developer and MySQL database administrator, but also can be used as a teaching book for teachers and students of relevant majors in colleges and universities.

The 1th chapter of the first knowledge of MySQL 1
1.1 Database Fundamentals 1
1.1.1 What is a database 1
1.1.2 Table 2
1.1.3 Data Type 2
1.1.4 PRIMARY Key 2
1.2 Database Technology Composition 3
1.2.1 Database System 3
1.2.2 SQL Language 4
1.2.3 Database Access Interface 5
1.3 What is MySQL 6
1.3.1 Client-Server Software 6
1.3.2 MySQL version 6
1.3.3 MySQL Advantage 7
New features of 1.3.4 MySQL 5.7 8
1.4 MySQL Tool 8
1.4.1 MySQL command line utility 9
1.4.2 MySQL Workbench 10
1.5 How to learn MySQL 10
2nd. Installation and configuration of MySQL 12
2.1 Installation and configuration under Windows platform MySQL 5.7 12
2.1.1 Installing MySQL 5.7 12
2.1.2 Configuring MySQL 5.7 18
2.2 Start the service and log in to MySQL database 22
2.2.1 Starting MySQL service 22
2.2.2 Log in to MySQL database 24
2.2.3 Configuring the PATH variable 27
2.3 MySQL Common graphics management tool 28
Installing and configuring MySQL 2.4 Linux Platform 5.7 29
2.4.1 Linux operating system MySQL version introduction 29
2.4.2 Installing and configuring the RPM package for MySQL 31
2.4.3 Installing and configuring the source bundle for MySQL 35
2.5 Expert doubts 36
2.6 Classic Exercise 36
3rd basic operation of the database 37
3.1 Creating a Database 37
3.2 Deleting a database 39
3.3 Database Storage Engine 39
3.3.1 MySQL Storage engine Introduction 39
3.3.2 InnoDB Storage Engine 41
3.3.3 MyISAM Storage Engine 42
3.3.4 Memory Storage Engine 43
3.3.5 Storage Engine Selection 43
3.4 Comprehensive case--creation and deletion of databases 44
3.5 Expert doubts 46
3.6 Classic Exercise 46
4th. Basic operation of Data Sheet 47
4.1 Creating a data Table 47
4.1.1 syntax form for creating a table 47
4.1.2 using PRIMARY KEY constraints 49
4.1.3 using FOREIGN KEY constraints 50
4.1.4 using non-null constraints 51
4.1.5 Using Uniqueness Constraints 52
4.1.6 using Default Constraints 53
4.1.7 Setting the property value of the table automatically increases by 53
4.2 Viewing the data table structure 54
4.2.1 View Table Basic structure statements describe 54
4.2.2 View Table Detail Structure Statement show CREATE Table 55
4.3 Modifying the data Table 56
4.3.1 Modifying table names 57
4.3.2 Modifying the data type of a field 57
4.3.3 Modifying field names 58
4.3.4 Adding Fields 59
4.3.5 Deleting a field 62
4.3.6 Modifying a field's arrangement position 63
4.3.7 Changing a table's storage engine 64
4.3.8 Delete a foreign key constraint on a table 65
4.4 Deleting a data table 67
4.4.1 Deleting a table that is not associated 67
4.4.2 Deleting a primary table associated with another table 67
4.5 Comprehensive case--basic operation of data Sheet 69
4.6 Expert doubts 77
4.7 Classic Exercise 78
Chapter 5th data types and operators 80
5.1 MySQL Data type description 80
5.1.1 Integer Type 80
5.1.2 floating-point type and fixed-point number type 82
5.1.3 Date and Time type 84
5.1.4 Text string Type 96
5.1.5 binary String Type 101
5.2 How to choose a data type 104
5.3 Introduction to common operators 105
5.3.1 Operator Overview 105
5.3.2 Arithmetic operator 106
5.3.3 comparison operator 107
5.3.4 logical operator 116
5.3.5-bit operator 119
Priority of the 5.3.6 operator 122
5.4 General case--use of operators 123
5.5 Expert doubts 125
5.6 Classic Exercise 126
6th MySQL function 127
6.1 MySQL Function Introduction 127
6.2 Mathematical Functions 128
6.2.1 Absolute function abs (x) and return pi function pi () 128
6.2.2 square root function sqrt (x) and the remainder function mod (y x) 128
6.2.3 functions that get integers ceil (x), CEILING (x), and floor (x) 129
6.2.4 the function of obtaining a random number rand () and rand (x) 130
6.2.5 functions ROUND (x), ROUND (x, y), and truncate (x, y) 130
6.2.6 Symbolic function sign (x) 131
6.2.7 Power Arithmetic function pow (x, y), POWER (x, y) and exp (x) 132
6.2.8 Logarithmic arithmetic functions log (x) and LOG10 (x) 132
6.2.9 function radians (x) and degrees (x) 133 for angle and radian conversion
6.2.10 sine function sin (x) and inverse chord function asin (x) 133
6.2.11-Chord function cos (x) and inverse cosine function acos (x) 134
6.2.12 tangent function, inverse tangent function, and cotangent function 134
6.3 String Function 135
6.3.1 function and string length function for calculating the number of string characters 135
6.3.2 Merging String Functions CONCAT (S1,s2,...), Concat_ws (x,s1,s2,...) 136
6.3.3 replacing a string function insert (S1,X,LEN,S2) 137
6.3.4 Letter-Case Conversion function 137
6.3.5 function to get a string of the specified length left (s,n) and Right (S,N) 138
6.3.6 functions that populate strings Lpad (S1,LEN,S2) and Rpad (S1,LEN,S2) 139
6.3.7 Remove whitespace functions ltrim (s), RTRIM (s), and trim (s) 139
6.3.8 Remove function Trim for the specified string (S1 from s) 140
6.3.9 function for repeating strings repeat (s,n) 141
6.3.10 Space function space (n) and replacement function replace (S,S1,S2) 141
6.3.11 Comparison of string size functions strcmp (S1,S2) 142
6.3.12 Get substring functions substring (s,n,len) and mid (S,n,len) 142
6.3.13 function that matches the starting position of a substring 143
6.3.14 string inverse function reverse (s) 143
6.3.15 function to return a string at the specified position 144
6.3.16 function to return the specified string position field (S,s1,s2,...) 144
6.3.17 function to return substring position find_in_set (S1,S2) 145
6.3.18 function Make_set (x,s1,s2,...) 145
6.4 Date and Time functions 146
6.4.1 gets the current date of the function and gets the current time of the function 146
6.4.2 a function that gets the current date and time 146
6.4.3 Unix Timestamp function 147
6.4.4 A function that returns a UTC date and a function that returns UTC time 148
6.4.5 get month's function month (date) and MonthName (date) 148
6.4.6 Get Week functions Dayname (d), DAYOFWEEK (d) and weekday (d) 149
6.4.7 functions for getting the number of weeks week (d) and WeekOfYear (d) 150
6.4.8 functions to get days dayofyear (d) and DayOfMonth (d) 151
6.4.9 functions for year, quarter, hour, minute, and second 151
6.4.10 function extract (type from date) 152 to get the specified value of the date
6.4.11 functions for time and seconds conversion 153
6.4.12 function to calculate date and time 153
6.4.13 a function that formats a date and time 156
6.5-Piece Judgment function 159
6.5.1 IF (expr,v1,v2) function 159
6.5.2 ifnull (v1,v2) function 160
6.5.3 Case Function 160
6.6 System Information Functions 161
6.6.1 function to get MySQL version number, number of connections and database name 161
6.6.2 function to get the user name 163
6.6.3 function to get the character set and sort method of a string 163
6.6.4 a function that gets the last auto-generated ID value 164
6.7 Add/Decrypt function 166
6.7.1 Cryptographic function password (str) 166
6.7.2 Cryptographic function MD5 (str) 167
6.7.3 Cryptographic functions Encode (STR,PSWD_STR) 167
6.7.4 decryption function decode (CRYPT_STR,PSWD_STR) 167
6.8 Other functions 168
6.8.1 formatted function format (X,N) 168
6.8.2 A function that converts a number to a different binary 168
6.8.3 functions for converting IP addresses to numbers 169
6.8.4 lock function and unlock function 170
6.8.5 functions that perform the specified operation repeatedly 170
6.8.6 function to change the character set 171
6.8.7 function to change data type 172
6.9 Comprehensive case--mysql use of the function 172
6.10 Expert doubts 176
6.11 Classic Exercise 176
The 7th chapter inquires the data 178
7.1 Basic Query Statements 178
7.2 Single-table query 180
7.2.1 Querying all Fields 181
7.2.2 querying a specified field 182
7.2.3 querying a specified record 184
7.2.4 with in keyword query 185
7.2.5 range query with between and 187
7.2.6 character matching query with like 188
7.2.7 querying null Values 190
7.2.8 Multi-criteria query with and 191
7.2.9 Multi-condition query with or 192
7.2.10 query results are not duplicated 193
7.2.11 sort query Results by 195
7.2.12 Group Query 198
7.2.13 limiting the number of query results by using limit 203
7.3 Querying using Aggregate functions 205
7.3.1 COUNT () function 205
7.3.2 SUM () function 206
7.3.3 AVG () function 207
7.3.4 MAX () function 208
7.3.5 MIN () function 209
7.4 Connection Query 210
7.4.1 Internal Connection Query 210
7.4.2 External Connection Query 214
7.4.3 Compound conditional Join Query 216
7.5 Sub-Query 217
7.5.1 with the Any, some keyword subquery 217
7.5.2 subquery with all keyword 218
7.5.3 sub-query with EXISTS keyword 218
7.5.4 subquery with in keyword 220
7.5.5 Sub-query with comparison operator 221
7.6 Merging Query Results 223
7.7 Taking aliases for tables and fields 225
7.7.1 Aliases for Tables 226
7.7.2 to alias a field 227
7.8 using regular expression queries 228
7.8.1 querying for records beginning with a specific character or string 229
7.8.2 querying for records ending with a specific character or string 230
7.8.3 with symbols "." To replace any one of the characters in a string 230
7.8.4 use "*" and "" to match multiple characters 231
7.8.5 matches the specified string 231
7.8.6 matches any one of the specified characters 233
7.8.7 matches a character other than the specified character 233
7.8.8 use {n,}, or {n,m} to specify the number of consecutive occurrences of a string 234
7.9 Comprehensive case--data table query operation 235
7.10 Expert doubts 243
7.11 Classic Exercise 244
Chapter 8th Inserting, updating and deleting data 245
8.1 Inserting Data 245
8.1.1 inserting data into all fields of a table 245
8.1.2 inserting data into a specified field of a table 247
8.1.3 inserting multiple records at the same time 249
8.1.4 inserting query results into a table 251
8.2 Update Data 252
8.3 Deleting data 254
8.4 Comprehensive Case--insert, update, and delete records 256
8.5 Expert doubts 261
8.6 Classic Exercise 262
Chapter 9th Index 263
9.1 Introduction to the index 263
9.1.1 meaning and features of the index 263
Classification of 9.1.2 Indexes 264
Design principles for 9.1.3 Indexes 265
9.2 Creating an Index 265
9.2.1 Creating an index when creating a table 266
9.2.2 Creating an index on a table that already exists 271
9.3 Deleting an index 278
9.4 Comprehensive case--create INDEX 280
9.5 Expert doubts 283
9.6 Classic Exercise 283
Chapter 10th stored Procedures and Functions 285
10.1 Creating stored procedures and Functions 285
10.1.1 Creating a Stored procedure 286
10.1.2 Creating a Storage function 288
Use of 10.1.3 variables 289
10.1.4 defining conditions and handlers 290
10.1.5 Use of Cursors 293
10.1.6 use of Process Control 294
10.2 Calling stored procedures and functions 299
10.2.1 Calling stored Procedure 299
10.2.2 calling a storage function 300
10.3 Viewing stored procedures and functions 301
10.3.1 using the Show status statement to view the status of stored procedures and functions 301
10.3.2 using the show create statement to view the definition of stored procedures and functions 302
10.3.3 from INFORMATION_SCHEMA. View information about stored procedures and functions in the Routines Table 302
10.4 Modifying stored procedures and functions 304
10.5 Deleting stored procedures and functions 306
10.6 Comprehensive case--creating stored procedures and functions 306
10.7 Expert doubts 309
10.8 Classic Exercise 310
11th Chapter View 311
11.1 Views Overview 311
Meaning of the 11.1.1 view 311
The role of the 11.1.2 view 312
11.2 Creating a View 313
11.2.1 creating the syntax for a view 313
11.2.2 creating views on a single table 314
11.2.3 creating views on multiple Tables 315
11.3 Viewing views 316
11.3.1 using describe statements to view basic information 316
11.3.2 using the show TABLE status statement to view basic information 316
11.3.3 viewing View details using the Show CREATE View statement 318
11.3.4 View details in views table 318
11.4 Modifying views 320
11.4.1 using the Create OR REPLACE view statement to modify the view 320
11.4.2 Modifying a view using the ALTER statement 321
11.5 Update View 322
11.6 Deleting views 324
11.7 Comprehensive Case-view application 325
11.8 Expert doubts 333
11.9 Classic Exercise 334
12th MySQL Trigger 335
12.1 Creating a Trigger 335
12.1.1 creating a trigger with only one execution statement 336
12.1.2 creating a trigger with multiple execution statements 337
12.2 Viewing triggers 339
12.2.1 Show triggers statement view trigger information 339
12.2.2 viewing trigger information in the Triggers table 341
12.3 Use of Triggers 342
12.4 Deleting a trigger 343
12.5 Comprehensive case--use of triggers 344
12.6 Expert doubts 346
12.7 Classic Exercise 346
13th MySQL User Management 347
13.1 Permissions Table 347
13.1.1 User Table 347
13.1.2 DB table and host table 350
13.1.3 tables_priv table and Columns_priv table 352
13.1.4 Procs_priv Table 353
13.2 Account Management 353
13.2.1 logging in and exiting MySQL server 354
13.2.2 new Normal User 355
13.2.3 Delete a normal user 359
13.2.4 root user to modify their password 360
13.2.5 root user changes password for normal user 362
13.2.6 Normal User Change password 363
13.2.7 root user Password loss Resolution 364
13.3 Rights Management 366
13.3.1 MySQL's various permissions 366
13.3.2 Authorization 368
13.3.3 Revoke Permissions 370
13.3.4 Viewing permissions 371
13.4 Access Control 372
13.4.1 Connection Verification Phase 372
13.4.2 Request Verification Phase 372
13.5 Comprehensive cases--integrated management user Rights 373
13.6 Expert doubts 377
13.7 Classic Exercise 377
14th. Data backup and Recovery 379
14.1 Data Backup 379
14.1.1 using the mysqldump command to back up 379
14.1.2 directly replicate the entire database directory 386
14.1.3 Quick Backup using the Mysqlhotcopy tool 386
14.2 Data Recovery 387
14.2.1 using MySQL command to recover 387
14.2.2 copy directly to database directory 388
14.2.3 mysqlhotcopy Fast Recovery 388
14.3 Database Migration 389
14.3.1 migration between the same version of MySQL database 389
14.3.2 migration between different versions of MySQL databases 389
14.3.3 migrating between different databases 390
14.4 Export and import of tables 390
14.4.1 using Selecti ... into outfile exporting text files 390
14.4.2 Exporting a text file using the mysqldump command 394
14.4.3 using the MySQL command to export a text file 396
14.4.4 Importing a text file using load DATA infile 399
14.4.5 Importing a text file using the Mysqlimport command 402
14.5 Comprehensive case--backup and recovery of data 403
14.6 Expert doubts 408
14.7 Classic Exercise 409
15th MySQL Log 410
15.1 Introduction to the log 410
15.2 Binary Log 411
15.2.1 starting and setting up binary logs 411
15.2.2 Viewing binary logs 412
15.2.3 Delete binary log 414
15.2.4 using binary logs to recover a database 416
15.2.5 temporarily stop the binary logging feature 417
15.3 Error Log 417
15.3.1 starting and setting error logs 417
15.3.2 Viewing error log 418
15.3.3 Delete error log 419
15.4 General Query Log 419
15.4.1 starting and setting the General query log 419
15.4.2 View General Query log 420
15.4.3 Deleting a generic query log 420
15.5 Slow Query Log 421
15.5.1 starting and setting the slow query log 421
15.5.2 View slow query log 421
15.5.3 Delete Slow query log 422
15.6 Comprehensive case--mysql comprehensive management of logs 422
15.7 Expert doubts 428
15.8 Classic Exercise 429
16th. Performance Optimization 430
16.1 Introduction to Optimization 430
16.2 Optimization Query 431
16.2.1 Parsing Query Statements 431
The effect of 16.2.2 index on query speed 435
16.2.3 Using index Queries 436
16.2.4 Optimization subquery 439
16.3 Optimizing the database structure 439
16.3.1 splitting a table of many fields into multiple tables 439
16.3.2 Increase in intermediate table 441
16.3.3 adding redundant fields 442
16.3.4 optimizing the speed of inserting records 443
16.3.5 Analysis tables, checklists, and tuning tables 445
16.4 Optimizing MySQL Server 446
16.4.1 Optimizing server Hardware 447
16.4.2 Optimizing MySQL Parameters 447
16.5 comprehensive case study--optimize MySQL server 448
16.6 Expert doubts 451
16.7 Classic Exercise 452
17th MySQL Replication 453
17.1 MySQL Replication Overview 453
17.2 MySQL master-slave replication under the Windows environment 454
17.2.1 Pre-replication preparations 454
17.2.2 implementation of Master-slave replication in Windows environments 455
17.2.3 the master-slave replication test under Windows environment 462
17.3 MySQL replication in Linux environments 463
17.3.1 download and install MySQL 5.7 463
17.3.2 stand-alone pre-master copy preparation work 465
17.3.3 Mysqld_multi implementation of single-machine master-slave replication 469
17.3.4 for master-slave replication between different servers 476
17.3.5 MySQL Primary replication boot option 478
17.3.6 specifying a replicated database or table 479
17.4 Viewing replication progress for slave 486
17.5 daily management and maintenance 488
17.5.1 Understanding the status of a server 488
17.5.2 cause of server replication error 489
17.6 switching between master and slave servers 492
17.7 expert doubts 496
17.8 Classic Exercise 497
18th use of MySQL Workbench 498
18.1 MySQL Workbench Introduction 498
18.1.1 MySQL Workbench Overview 498
18.1.2 MySQL Workbench Advantage 499
18.1.3 MySQL Workbench Installation 499
18.2 Basic operations for SQL development 501
18.2.1 Creating a database connection 501
18.2.2 Creating a new database 502
18.2.3 Creating and deleting new data tables 504
18.2.4 adding, modifying Table Records 507
18.2.5 query Table Records 508
18.2.6 Modifying a table structure 509
Basic operation of 18.3 Data modeling 510
18.3.1 establishing an ER model 510
18.3.2 Importing ER models 515
Basic operations for 18.4 Server Administration 517
18.4.1 managing MySQL users 517
18.4.2 backing up MySQL database 520
18.4.3 Recovering MySQL Database 523
18.5 expert doubts 524
18.6 Classic Exercise 524
19th MySQL Cluster 526
19.1 MySQL Cluster Overview 526
19.1.1 MySQL cluster Basic Concepts 526
19.1.2 Understanding MySQL Cluster node 527
19.2 MySQL cluster installation and configuration in Linux environment 528
19.2.1 installing MySQL cluster software 531
19.2.2 Management node Configuration step 535
19.2.3 Configuring SQL Nodes and Data nodes 536
19.3 managing MySQL Cluster 537
19.3.1 Cluster Start-up 537
Test of 19.3.2 Cluster 539
19.3.3 Cluster's off 541
19.4 Maintenance MySQL Cluster 542
19.4.1 Cluster Log Management 544
19.4.2 Online backup of cluster 545
Data recovery for 19.4.3 cluster 546
19.5 Configuring cluster 547 in the Windows operating system
19.6 Expert doubts 553
19.7 Classic Exercise 554
20th MySQL Management tool--mysql Utilities 555
20.1 MySQL Utilities Overview 555
20.2 Installation and Configuration 556
20.2.1 download and install MySQL Utilities 556
20.2.2 MySQL Utilities Connection Database 559
20.3 Management and Maintenance 560
20.3.1 using Mysqldbcompare to compare data 560
20.3.2 copying data using Mysqldbcopy 561
20.3.3 exporting data using Mysqldbexport 561
20.3.4 importing data using Mysqldbimport 562
20.3.5 using Mysqldiff to compare object definitions 562
20.4 expert doubts 563
20.5 Classic Exercise 564
The 21st chapter of the separation of reading and writing--mysql Proxy 565
21.1 Overview 565
21.2 Installation and Configuration 566
21.2.1 download and install MySQL Proxy 566
21.2.2 configuring MySQL proxy parameter 567
21.2.3 Configuring the PATH variable 569
21.3 using MySQL proxy for read-write separation 571
21.4 expert doubts 572
21.5 Classic Exercise 572
22nd PHP Operation MySQL Database 573
22.1 General steps for PHP to access MySQL database 573
22.2 Preparing to connect to a database 574
22.3 PHP operation MySQL database function 575
22.3.1 accessing MySQL database via Mysqli class library 575
22.3.2 using the Mysqli_connect () function to connect to MySQL server 577
22.3.3 using the mysqli_select_db () function to select a database file 577
22.3.4 using the Mysqli_query () function to execute SQL statements 578
22.3.5 using the MYSQLI_FETCH_ASSOC () function to get information from an array result set 578
22.3.6 using the Mysqli_fetch_object () function to get a row from the result as an object 578
22.3.7 using the Mysqli_num_rows () function to get the number of records in a query result set 579
22.3.8 using the Mysqli_free_result () function to free up resources 579
22.3.9 using the Mysqli_close () function to close the connection 579
22.4 Synthetic Examples Add user information dynamically using INSERT statements 580
22.5 Synthetic instance 2--querying data information using SELECT statements 583
22.6 expert doubts 585
22.7 Classic Exercise 585
The 23rd chapter of the press release system database design 586
23.1 System Overview 586
23.2 System Functions 587
23.3 Database design and implementation 588
23.3.1 Design Table 588
23.3.2 Design Index 592
23.3.3 Design View 593
23.3.4 Design Trigger 594
23.4 Case Summary 595
The 24th chapter of the Forum Management system database design 596
24.1 System Overview 596
24.2 System Functions 597
24.3 Database design and implementation 598
24.3.1 Design plan diagram 598
24.3.2 Design Table 600
24.3.3 Design Index 603
24.3.4 Design View 604
24.3.5 Design Trigger 605
24.4 Case Summary 606

"MySQL5.7 from beginner to Proficient (Video tutorial Edition)"