hiskio

    MySQL Notes for Professionals book

    Amazing collection of free programming books

    收集中
    免費
    Amazing collection of free programming books

    The Notes for Professionals book is compiled from Stack Overflow Documentation, the content is written by the beautiful people at Stack Overflow. Text content is released under Creative Commons BY-SA. See credits at the end of this book whom contributed to the various chapters. Images may be copyright of their respective owners unless otherwise specified

    Book created for educational purposes and is not affiliated with group(s), company(s) nor Stack Overflow. All trademarks belong to their respective company owners

    https://goalkicker.com/

    章節目錄

    • 1-1
      Content list
    • 1-2
      About
    • 1-3
      Chapter 1: Getting started with MySQL
    • 1-4
      Section 1.1: Getting Started
    • 1-5
      Section 1.2: Information Schema Examples
    • 1-6
      Chapter 2: Data Types
    • 1-7
      Section 2.1: CHAR(n)
    • 1-8
      Section 2.2: DATE, DATETIME, TIMESTAMP, YEAR, and TIME
    • 1-9
      Section 2.3: VARCHAR(255) -- or not
    • 1-10
      Section 2.4: INT as AUTO_INCREMENT
    • 1-11
      Section 2.5: Others
    • 1-12
      Section 2.6: Implicit / automatic casting
    • 1-13
      Section 2.7: Introduction (numeric)
    • 1-14
      Section 2.8: Integer Types
    • 1-15
      Section 2.9: Fixed Point Types
    • 1-16
      Section 2.10: Floating Point Types
    • 1-17
      Section 2.11: Bit Value Type
    • 1-18
      Chapter 3: SELECT
    • 1-19
      Section 3.1: SELECT with DISTINCT
    • 1-20
      Section 3.2: SELECT all columns (*)
    • 1-21
      Section 3.3: SELECT by column name
    • 1-22
      Section 3.4: SELECT with LIKE (%)
    • 1-23
      Section 3.5: SELECT with CASE or IF
    • 1-24
      Section 3.6: SELECT with Alias (AS)
    • 1-25
      Section 3.7: SELECT with a LIMIT clause
    • 1-26
      Section 3.8: SELECT with BETWEEN
    • 1-27
      Section 3.9: SELECT with WHERE
    • 1-28
      Section 3.10: SELECT with LIKE(_)
    • 1-29
      Section 3.11: SELECT with date range
    • 1-30
      Chapter 4: Backticks
    • 1-31
      Section 4.1: Backticks usage
    • 1-32
      Chapter 5: NULL
    • 1-33
      Section 5.1: Uses for NULL
    • 1-34
      Section 5.2: Testing NULLs
    • 1-35
      Chapter 6: Limit and Oset
    • 1-36
      Section 6.1: Limit and Oset relationship
    • 1-37
      Chapter 7: Creating databases
    • 1-38
      Section 7.1: Create database, users, and grants
    • 1-39
      Section 7.2: Creating and Selecting a Database
    • 1-40
      Section 7.3: MyDatabase
    • 1-41
      Section 7.4: System Databases
    • 1-42
      Chapter 8: Using Variables
    • 1-43
      Section 8.1: Setting Variables
    • 1-44
      Section 8.2: Row Number and Group By using variables in Select Statement
    • 1-45
      Chapter 9: Comment MySQL
    • 1-46
      Section 9.1: Adding comments
    • 1-47
      Section 9.2: Commenting table definitions
    • 1-48
      Chapter 10: INSERT
    • 1-49
      Section 10.1: INSERT, ON DUPLICATE KEY UPDATE
    • 1-50
      Section 10.2: Inserting multiple rows
    • 1-51
      Section 10.3: Basic Insert
    • 1-52
      Section 10.4: INSERT with AUTO_INCREMENT + LAST_INSERT_ID()
    • 1-53
      Section 10.5: INSERT SELECT (Inserting data from another Table)
    • 1-54
      Section 10.6: Lost AUTO_INCREMENT ids
    • 1-55
      Chapter 11: DELETE
    • 1-56
      Section 11.1: Multi-Table Deletes
    • 1-57
      Section 11.2: DELETE vs TRUNCATE
    • 1-58
      Section 11.3: Multi-table DELETE
    • 1-59
      Section 11.4: Basic delete
    • 1-60
      Section 11.5: Delete with Where clause
    • 1-61
      Section 11.6: Delete all rows from a table
    • 1-62
      Section 11.7: LIMITing deletes
    • 1-63
      Chapter 12: UPDATE
    • 1-64
      Section 12.1: Update with Join Pattern
    • 1-65
      Section 12.2: Basic Update
    • 1-66
      Section 12.3: Bulk UPDATE
    • 1-67
      Section 12.4: UPDATE with ORDER BY and LIMIT
    • 1-68
      Section 12.5: Multiple Table UPDATE
    • 1-69
      Chapter 13: ORDER BY
    • 1-70
      Section 13.1: Contexts
    • 1-71
      Section 13.2: Basic
    • 1-72
      Section 13.3: ASCending / DESCending
    • 1-73
      Section 13.4: Some tricks
    • 1-74
      Chapter 14: Group By
    • 1-75
      Section 14.1: GROUP BY using HAVING
    • 1-76
      Section 14.2: Group By using Group Concat
    • 1-77
      Section 14.3: Group By Using MIN function
    • 1-78
      Section 14.4: GROUP BY with AGGREGATE functions
    • 1-79
      Chapter 15: Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...
    • 1-80
      Section 15.1: Misusing GROUP BY to return unpredictable results: Murphy's Law
    • 1-81
      Section 15.2: Misusing GROUP BY with SELECT *, and how to fix it
    • 1-82
      Section 15.3: ANY_VALUE()
    • 1-83
      Section 15.4: Using and misusing GROUP BY
    • 1-84
      Chapter 16: Joins
    • 1-85
      Section 16.1: Joins visualized
    • 1-86
      Section 16.2: JOIN with subquery ("Derived" table)
    • 1-87
      Section 16.3: Full Outer Join
    • 1-88
      Section 16.4: Retrieve customers with orders -- variations on a theme
    • 1-89
      Section 16.5: Joining Examples
    • 1-90
      Chapter 17: JOINS: Join 3 table with the same name of id.
    • 1-91
      Section 17.1: Join 3 tables on a column with the same name
    • 1-92
      Chapter 18: UNION
    • 1-93
      Section 18.1: Combining SELECT statements with UNION
    • 1-94
      Section 18.2: Combining data with dierent columns
    • 1-95
      Section 18.3: ORDER BY
    • 1-96
      Section 18.4: Pagination via OFFSET
    • 1-97
      Section 18.5: Combining and merging data on dierent MySQL tables with the same columns into unique rows and running query
    • 1-98
      Section 18.6: UNION ALL and UNION
    • 1-99
      Chapter 19: Arithmetic
    • 1-100
      Section 19.1: Arithmetic Operators
    • 1-101
      Section 19.2: Mathematical Constants
    • 1-102
      Section 19.3: Trigonometry (SIN, COS)
    • 1-103
      Section 19.4: Rounding (ROUND, FLOOR, CEIL)
    • 1-104
      Section 19.5: Raise a number to a power (POW)
    • 1-105
      Section 19.6: Square Root (SQRT)
    • 1-106
      Section 19.7: Random Numbers (RAND)
    • 1-107
      Section 19.8: Absolute Value and Sign (ABS, SIGN)
    • 1-108
      Chapter 20: String operations
    • 1-109
      Section 20.1: LENGTH()
    • 1-110
      Section 20.2: CHAR_LENGTH()
    • 1-111
      Section 20.3: HEX(str)
    • 1-112
      Section 20.4: SUBSTRING()
    • 1-113
      Section 20.5: UPPER() / UCASE()
    • 1-114
      Section 20.6: STR_TO_DATE - Convert string to date
    • 1-115
      Section 20.7: LOWER() / LCASE()
    • 1-116
      Section 20.8: REPLACE()
    • 1-117
      Section 20.9: Find element in comma separated list
    • 1-118
      Chapter 21: Date and Time Operations
    • 1-119
      Section 21.1: Date arithmetic
    • 1-120
      Section 21.2: SYSDATE(), NOW(), CURDATE()
    • 1-121
      Section 21.3: Testing against a date range
    • 1-122
      Section 21.4: Extract Date from Given Date or DateTime Expression
    • 1-123
      Section 21.5: Using an index for a date and time lookup
    • 1-124
      Section 21.6: Now()
    • 1-125
      Chapter 22: Handling Time Zones
    • 1-126
      Section 22.1: Retrieve the current date and time in a particular time zone
    • 1-127
      Section 22.2: Convert a stored `DATE` or `DATETIME` value to another time zone
    • 1-128
      Section 22.3: Retrieve stored `TIMESTAMP` values in a particular time zone
    • 1-129
      Section 22.4: What is my server's local time zone setting?
    • 1-130
      Section 22.5: What time_zone values are available in my server?
    • 1-131
      Chapter 23: Regular Expressions
    • 1-132
      Section 23.1: REGEXP / RLIKE
    • 1-133
      Chapter 24: VIEW
    • 1-134
      Section 24.1: Create a View
    • 1-135
      Section 24.2: A view from two tables
    • 1-136
      Section 24.3: DROPPING A VIEW
    • 1-137
      Section 24.4: Updating a table via a VIEW
    • 1-138
      Chapter 25: Table Creation
    • 1-139
      Section 25.1: Table creation with Primary Key
    • 1-140
      Section 25.2: Basic table creation
    • 1-141
      Section 25.3: Table creation with Foreign Key
    • 1-142
      Section 25.4: Show Table Structure
    • 1-143
      Section 25.5: Cloning an existing table
    • 1-144
      Section 25.6: Table Create With TimeStamp Column To Show Last Update
    • 1-145
      Section 25.7: CREATE TABLE FROM SELECT
    • 1-146
      Chapter 26: ALTER TABLE
    • 1-147
      Section 26.1: Changing storage engine; rebuild table; change file_per_table
    • 1-148
      Section 26.2: ALTER COLUMN OF TABLE
    • 1-149
      Section 26.3: Change auto-increment value
    • 1-150
      Section 26.4: Renaming a MySQL table
    • 1-151
      Section 26.5: ALTER table add INDEX
    • 1-152
      Section 26.6: Changing the type of a primary key column
    • 1-153
      Section 26.7: Change column definition
    • 1-154
      Section 26.8: Renaming a MySQL database
    • 1-155
      Section 26.9: Swapping the names of two MySQL databases
    • 1-156
      Section 26.10: Renaming a column in a MySQL table
    • 1-157
      Chapter 27: Drop Table
    • 1-158
      Section 27.1: Drop Table
    • 1-159
      Section 27.2: Drop tables from database
    • 1-160
      Chapter 28: MySQL LOCK TABLE
    • 1-161
      Section 28.1: Row Level Locking
    • 1-162
      Section 28.2: Mysql Locks
    • 1-163
      Chapter 29: Error codes
    • 1-164
      Section 29.1: Error code 1064: Syntax error
    • 1-165
      Section 29.2: Error code 1175: Safe Update
    • 1-166
      Section 29.3: Error code 1215: Cannot add foreign key constraint
    • 1-167
      Section 29.4: 1067, 1292, 1366, 1411 - Bad Value for number, date, default, etc
    • 1-168
      Section 29.5: 1045 Access denied
    • 1-169
      Section 29.6: 1236 "impossible position" in Replication
    • 1-170
      Section 29.7: 2002, 2003 Cannot connect
    • 1-171
      Section 29.8: 126, 127, 134, 144, 145
    • 1-172
      Section 29.9: 139
    • 1-173
      Section 29.10: 1366
    • 1-174
      Section 29.11: 126, 1054, 1146, 1062, 24
    • 1-175
      Chapter 30: Stored routines (procedures and functions)
    • 1-176
      Section 30.1: Stored procedure with IN, OUT, INOUT parameters
    • 1-177
      Section 30.2: Create a Function
    • 1-178
      Section 30.3: Cursors
    • 1-179
      Section 30.4: Multiple ResultSets
    • 1-180
      Section 30.5: Create a function
    • 1-181
      Chapter 31: Indexes and Keys
    • 1-182
      Section 31.1: Create index
    • 1-183
      Section 31.2: Create unique index
    • 1-184
      Section 31.3: AUTO_INCREMENT key
    • 1-185
      Section 31.4: Create composite index
    • 1-186
      Section 31.5: Drop index
    • 1-187
      Chapter 32: Full-Text search
    • 1-188
      Section 32.1: Simple FULLTEXT search
    • 1-189
      Section 32.2: Simple BOOLEAN search
    • 1-190
      Section 32.3: Multi-column FULLTEXT search
    • 1-191
      Chapter 33: PREPARE Statements
    • 1-192
      Section 33.1: PREPARE, EXECUTE and DEALLOCATE PREPARE Statements
    • 1-193
      Section 33.2: Alter table with add column
    • 1-194
      Chapter 34: JSON
    • 1-195
      Section 34.1: Create simple table with a primary key and JSON field
    • 1-196
      Section 34.2: Insert a simple JSON
    • 1-197
      Section 34.3: Updating a JSON field
    • 1-198
      Section 34.4: Insert mixed data into a JSON field
    • 1-199
      Section 34.5: CAST data to JSON type
    • 1-200
      Section 34.6: Create Json Object and Array
    • 1-201
      Chapter 35: Extract values from JSON type
    • 1-202
      Section 35.1: Read JSON Array value
    • 1-203
      Section 35.2: JSON Extract Operators
    • 1-204
      Chapter 36: MySQL Admin
    • 1-205
      Section 36.1: Atomic RENAME & Table Reload
    • 1-206
      Section 36.2: Change root password
    • 1-207
      Section 36.3: Drop database
    • 1-208
      Chapter 37: TRIGGERS
    • 1-209
      Section 37.1: Basic Trigger
    • 1-210
      Section 37.2: Types of triggers
    • 1-211
      Chapter 38: Configuration and tuning
    • 1-212
      Section 38.1: InnoDB performance
    • 1-213
      Section 38.2: Parameter to allow huge data to insert
    • 1-214
      Section 38.3: Increase the string limit for group_concat
    • 1-215
      Section 38.4: Minimal InnoDB configuration
    • 1-216
      Section 38.5: Secure MySQL encryption
    • 1-217
      Chapter 39: Events
    • 1-218
      Section 39.1: Create an Event
    • 1-219
      Chapter 40: ENUM
    • 1-220
      Section 40.1: Why ENUM?
    • 1-221
      Section 40.2: VARCHAR as an alternative
    • 1-222
      Section 40.3: Adding a new option
    • 1-223
      Section 40.4: NULL vs NOT NULL
    • 1-224
      Chapter 41: Install Mysql container with Docker-Compose
    • 1-225
      Section 41.1: Simple example with docker-compose
    • 1-226
      Chapter 42: Character Sets and Collations
    • 1-227
      Section 42.1: Which CHARACTER SET and COLLATION?
    • 1-228
      Section 42.2: Setting character sets on tables and fields
    • 1-229
      Section 42.3: Declaration
    • 1-230
      Section 42.4: Connection
    • 1-231
      Chapter 43: MyISAM Engine
    • 1-232
      Section 43.1: ENGINE=MyISAM
    • 1-233
      Chapter 44: Converting from MyISAM to InnoDB
    • 1-234
      Section 44.1: Basic conversion
    • 1-235
      Section 44.2: Converting All Tables in one Database
    • 1-236
      Chapter 45: Transaction
    • 1-237
      Section 45.1: Start Transaction
    • 1-238
      Section 45.2: COMMIT , ROLLBACK and AUTOCOMMIT
    • 1-239
      Section 45.3: Transaction using JDBC Driver
    • 1-240
      Chapter 46: Log files
    • 1-241
      Section 46.1: Slow Query Log
    • 1-242
      Section 46.2: A List
    • 1-243
      Section 46.3: General Query Log
    • 1-244
      Section 46.4: Error Log
    • 1-245
      Chapter 47: Clustering
    • 1-246
      Section 47.1: Disambiguation
    • 1-247
      Chapter 48: Partitioning
    • 1-248
      Section 48.1: RANGE Partitioning
    • 1-249
      Section 48.2: LIST Partitioning
    • 1-250
      Section 48.3: HASH Partitioning
    • 1-251
      Chapter 49: Replication
    • 1-252
      Section 49.1: Master - Slave Replication Setup
    • 1-253
      Section 49.2: Replication Errors
    • 1-254
      Chapter 50: Backup using mysqldump
    • 1-255
      Section 50.1: Specifying username and password
    • 1-256
      Section 50.2: Creating a backup of a database or table
    • 1-257
      Section 50.3: Restoring a backup of a database or table
    • 1-258
      Section 50.4: Tranferring data from one MySQL server to another
    • 1-259
      Section 50.5: mysqldump from a remote server with compression
    • 1-260
      Section 50.6: restore a gzipped mysqldump file without uncompressing
    • 1-261
      Section 50.7: Backup database with stored procedures and functions
    • 1-262
      Section 50.8: Backup direct to Amazon S3 with compression
    • 1-263
      Chapter 51: mysqlimport
    • 1-264
      Section 51.1: Basic usage
    • 1-265
      Section 51.2: Using a custom field-delimiter
    • 1-266
      Section 51.3: Using a custom row-delimiter
    • 1-267
      Section 51.4: Handling duplicate keys
    • 1-268
      Section 51.5: Conditional import
    • 1-269
      Section 51.6: Import a standard csv
    • 1-270
      Chapter 52: LOAD DATA INFILE
    • 1-271
      Section 52.1: using LOAD DATA INFILE to load large amount of data to database
    • 1-272
      Section 52.2: Load data with duplicates
    • 1-273
      Section 52.3: Import a CSV file into a MySQL table
    • 1-274
      Chapter 53: MySQL Unions
    • 1-275
      Section 53.1: Union operator
    • 1-276
      Section 53.2: Union ALL
    • 1-277
      Section 53.3: UNION ALL With WHERE
    • 1-278
      Chapter 54: MySQL client
    • 1-279
      Section 54.1: Base login
    • 1-280
      Section 54.2: Execute commands
    • 1-281
      Chapter 55: Temporary Tables
    • 1-282
      Section 55.1: Create Temporary Table
    • 1-283
      Section 55.2: Drop Temporary Table
    • 1-284
      Chapter 56: Customize PS1
    • 1-285
      Section 56.1: Customize the MySQL PS1 with current database
    • 1-286
      Section 56.2: Custom PS1 via MySQL configuration file
    • 1-287
      Chapter 57: Dealing with sparse or missing data
    • 1-288
      Section 57.1: Working with columns containg NULL values
    • 1-289
      Chapter 58: Connecting with UTF-8 Using Various Programming language.
    • 1-290
      Section 58.1: Python
    • 1-291
      Section 58.2: PHP
    • 1-292
      Chapter 59: Time with subsecond precision
    • 1-293
      Section 59.1: Get the current time with millisecond precision
    • 1-294
      Section 59.2: Get the current time in a form that looks like a Javascript timestamp
    • 1-295
      Section 59.3: Create a table with columns to store sub-second time
    • 1-296
      Section 59.4: Convert a millisecond-precision date / time value to text
    • 1-297
      Section 59.5: Store a Javascript timestamp into a TIMESTAMP column
    • 1-298
      Chapter 60: One to Many
    • 1-299
      Section 60.1: Example Company Tables
    • 1-300
      Section 60.2: Get the Employees Managed by a Single Manager
    • 1-301
      Section 60.3: Get the Manager for a Single Employee
    • 1-302
      Chapter 61: Server Information
    • 1-303
      Section 61.1: SHOW VARIABLES example
    • 1-304
      Section 61.2: SHOW STATUS example
    • 1-305
      Chapter 62: SSL Connection Setup
    • 1-306
      Section 62.1: Setup for Debian-based systems
    • 1-307
      Section 62.2: Setup for CentOS7 / RHEL7
    • 1-308
      Chapter 63: Create New User
    • 1-309
      Section 63.1: Create a MySQL User
    • 1-310
      Section 63.2: Specify the password
    • 1-311
      Section 63.3: Create new user and grant all priviliges to schema
    • 1-312
      Section 63.4: Renaming user
    • 1-313
      Chapter 64: Security via GRANTs
    • 1-314
      Section 64.1: Best Practice
    • 1-315
      Section 64.2: Host (of user@host)
    • 1-316
      Chapter 65: Change Password
    • 1-317
      Section 65.1: Change MySQL root password in Linux
    • 1-318
      Section 65.2: Change MySQL root password in Windows
    • 1-319
      Section 65.3: Process
    • 1-320
      Chapter 66: Recover and reset the default root password for MySQL 5.7+
    • 1-321
      Section 66.1: What happens when the initial start up of the server
    • 1-322
      Section 66.2: How to change the root password by using the default password
    • 1-323
      Section 66.3: reset root password when " /var/run/mysqld' for UNIX socket file don't exists"
    • 1-324
      Chapter 67: Recover from lost root password
    • 1-325
      Section 67.1: Set root password, enable root user for socket and http access
    • 1-326
      Chapter 68: MySQL Performance Tips
    • 1-327
      Section 68.1: Building a composite index
    • 1-328
      Section 68.2: Optimizing Storage Layout for InnoDB Tables
    • 1-329
      Chapter 69: Performance Tuning
    • 1-330
      Section 69.1: Don't hide in function
    • 1-331
      Section 69.2: OR
    • 1-332
      Section 69.3: Add the correct index
    • 1-333
      Section 69.4: Have an INDEX
    • 1-334
      Section 69.5: Subqueries
    • 1-335
      Section 69.6: JOIN + GROUP BY
    • 1-336
      Section 69.7: Set the cache correctly
    • 1-337
      Section 69.8: Negatives
    • 1-338
      Appendix A: Reserved Words
    • 1-339
      Section A.1: Errors due to reserved words
    • 1-340
      Credits
    • 1-341
      You may also like

    常見問答

    您可以透過手機、平板或是電腦登入 HiSKIO 平台，在【我的學習】＞【我的書籍】頁面，選擇想看的電子書。

    猜你喜歡

    用戶評價

    | 收集中

    銷售方案