hiskio

    MySQL Notes for Professionals book

    Amazing collection of free programming books

    Collecting
    Free
    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/

    Content

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

    FAQ

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

    Recommendations

    Reviews

    | Collecting

    Sales Plans