今天群里一个哥们问我mysql怎么将decimal转成varchar，经过查阅资料发现，mysql好像不能将decimal直接转换成varchar，但是可以转成char，原文链接：http://stackoverflow.com/questions/5114959/mysql-casting-from-decimal-to-string
类似于这样的：
SELECT CAST(SUM(decimal_name) AS CHAR(50)) FROM article，注意关于mysql的sum函数返回值:
The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE). (Before MySQL 5.0.3, SUM() and AVG() return DOUBLE for all numeric arguments.)
也就是说在5.0.3之前返回值都是double，而在之后如果sum(integer)或者sum(decimal)返回的是decimal，当sum(float) or sum(boudle)时返回的是double。
今天群里一个哥们问我mysql怎么将decimal转成varchar，经过查阅资料发现，mysql好像不能将decimal直接转换成varchar，但是可以转成char，原文链接：http://stackoverflow.com/questions/5114959/mysql-casting-from-decimal-to-string类似于这样的：SELECT CAST(SUM(de
This hands-on guide teaches, step by step, how to use JavaScript Object Notation (JSON) with
MySQL
. Written by a
MySQL
Community Manager for Oracle,
MySQL
and JSON: A Practical Programming Guide shows how to quickly get started using JSON with
MySQL
and clearly explains the latest tools and functions. All content is based on the author’s years of interaction with
MySQL
professionals. Throughout, real-world examples and sample code guide you through the syntax and application of each method. You will get in-depth coverage of programming with the
MySQL
Document Store.
•See how JavaScript Object Notation (JSON) works with
MySQL
•Use JSON as
string
data and JSON as a data type
•Find the path, load data, and handle searches with REGEX
•Work with JSON and non-JSON output
•Build virtual generated columns and stored generated columns
•Generate complex geometries using GeoJSON
•Convert and manage data with JSON functions
•Access JSON data, collections, and tables through
MySQL
Document Store
1 Introduction
MySQL
The Example Database
How to Use This Book
2 JSON as
String
Data vs. JSON as a Data Type
JSON
String
Data
The JSON Data Type
3 Finding the Path
Examining the world_x Data
Seeing the Keys
Digging Deeper
4 Finding and Getting Data
All Keys
Searching for a Key
Searching for a Path
Searching for a Value
5
Cha
nging Data
Using Arrays
Appending Arrays
Inserting into an Array
Using TRUNCATE Before Adding New Data
Using JSON_INSERT
Using JSON_REPLACE
JSON_REMOVE
JSON_SET
JSON_UNQUOTE
The Three JSON_MERGE Functions
JSON_MERGE
JSON_MERGE_PRESERVE
JSON_DEPTH
JSON_LENGTH
JSON_TYPE
JSON_VALID
JSON_STORAGE_SIZE
JSON_STORAGE_FREE
6 JSON and Non-JSON Output
JSON-Formatted Data
JSON_OBJECT
JSON_ARRAY
Casting
Non-JSON Output
Missing Data
Nested Data
7 Generated Columns
Using Generated Columns
Columns Generated from JSON
Generated Columns: Common Errors
8 GeoJSON
ST_GeomFromGeoJSON
ST_AsGeoJSON
9 PHP’s JSON Functions
JSON_DECODE
JSON_ENCODE
10 Loading JSON Data
From Download to Database
Step 1: Examine the Data
Step 2: Create the Table
Step 3: Load the Data Using a Wrapper
Step 4: Double-Check the Data
jq: JSON CLI Parser
With No Arguments
Select Certain Fields
The Restaurant Collection
11 The
MySQL
Document Store
The X DevAPI
mysql
sh
Connections
Session Types
Collections and Documents
CRUD: Create, Replace, Update, Delete
Filtering Find
Sorting
Binding
Indexing Collections
Dropping a Collection
12 Programming with the
MySQL
Document Store
Programming Examples
Python Example
Node.JS Example
PHP Example
Traditional
SQL
vs.
MySQL
Document Store
The
MySQL
Shell and JavaScript
Relational Tables
Both Relational and Document
Document as Relational
A Additional Resources
Index
CHA
PTER 1 Introduction to
MySQL
. . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
1.2 Database, Database Server, and Database Language. . . . . . . . . 4
1.3 The Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.4 What Is
SQL
? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.5 The History of
SQL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.6 From Monolithic via Client/Server to the Internet . . . . . . . . . . 18
1.7 Standardization of
SQL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.8 What Is Open Source Software?. . . . . . . . . . . . . . . . . . . . . . . . 25
1.9 The History of
MySQL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
1.10 The Structure of This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
CHA
PTER 2 The Tennis Club Sample Database . . . . . . . . . . . . . . . . 29
2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.2 Description of the Tennis Club . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.3 The Contents of the Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.4 Integrity Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
CHA
PTER 3 Installing the Software . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.2 Downloading
MySQL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
3.3 Installation of
MySQL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.4 Installing a Query Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.5 Downloading
SQL
Statements from the Web Site . . . . . . . . . . 38
3.6 Ready? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
CHA
PTER 4
SQL
in a Nutshell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
4.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
4.2 Logging On to the
MySQL
Database Server . . . . . . . . . . . . . . . 41
4.3 Creating New
SQL
Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
4.4 Creating Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
4.5 Selecting the Current Database . . . . . . . . . . . . . . . . . . . . . . . . 45
4.6 Creating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
4.7 Populating Tables with Data . . . . . . . . . . . . . . . . . . . . . . . . . . 48
4.8 Querying Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
4.9 Updating and Deleting Rows . . . . . . . . . . . . . . . . . . . . . . . . . . 52
4.10 Optimizing Query Processing with Indexes. . . . . . . . . . . . . . . 54
4.11 Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
4.12 Users and Data Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
4.13 Deleting Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
4.14 System
Var
iables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
4.15 Grouping of
SQL
Statements . . . . . . . . . . . . . . . . . . . . . . . . . 59
4.16 The Catalog Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
4.17 Retrieving Errors and Warnings . . . . . . . . . . . . . . . . . . . . . . . 68
4.18 Definitions of
SQL
Statements . . . . . . . . . . . . . . . . . . . . . . . . 69
PART II Querying and Updating Data . . . . . . . . . . . . . . . . 71
CHA
PTER 5 SELECT Statement: Common Elements . . . . . . . . . . . . 73
5.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
5.2 Literals and Their Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . 74
5.3 Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
5.4 Assigning Names to Result Columns . . . . . . . . . . . . . . . . . . . . 92
5.5 The Column Specification. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
5.6 The User
Var
iable and the SET Statement . . . . . . . . . . . . . . . . 95
5.7 The System
Var
iable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
5.8 The Case Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
5.9 The Scalar Expression Between Brackets . . . . . . . . . . . . . . . . 106
viii Contents
5.10 The Scalar Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
5.11
Casting
of Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
5.12 The Null Value as an Expression . . . . . . . . . . . . . . . . . . . . . . 114
5.13 The Compound Scalar Expression . . . . . . . . . . . . . . . . . . . . 115
5.14 The Aggregation Function and the Scalar Subquery. . . . . . . 136
5.15 The Row Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
5.16 The Table Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
5.17 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
CHA
PTER 6 SELECT Statements, Table Expressions,
and Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
6.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
6.2 The Definition of the SELECT Statement . . . . . . . . . . . . . . . . 145
6.3 Processing the Clauses in a Select Block. . . . . . . . . . . . . . . . . 150
6.4 Possible Forms of a Table Expression . . . . . . . . . . . . . . . . . . . 156
6.5 What Is a SELECT Statement? . . . . . . . . . . . . . . . . . . . . . . . . 159
6.6 What Is a Subquery?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
6.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
CHA
PTER 7 SELECT Statement:The FROM Clause. . . . . . . . . . . . . 171
7.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
7.2 Table Specifications in the FROM Clause . . . . . . . . . . . . . . . . 171
7.3 Again, the Column Specification. . . . . . . . . . . . . . . . . . . . . . . 173
7.4 Multiple Table Specifications in the FROM Clause . . . . . . . . . 174
7.5 Pseudonyms for Table Names. . . . . . . . . . . . . . . . . . . . . . . . . 178
7.6
Var
ious Examples of Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
7.7 Mandatory Use of Pseudonyms . . . . . . . . . . . . . . . . . . . . . . . 183
7.8 Tables of Different Databases . . . . . . . . . . . . . . . . . . . . . . . . 185
7.9 Explicit Joins in the FROM Clause. . . . . . . . . . . . . . . . . . . . . . 185
7.10 Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
7.11 The Natural Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
7.12 Additional Conditions in the Join Condition. . . . . . . . . . . . . 196
7.13 The Cross Join. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
7.14 Replacing Join Conditions with USING. . . . . . . . . . . . . . . . . 199
7.15 The FROM Clause with Table Expressions . . . . . . . . . . . . . . 200
7.16 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Contents ix
CHA
PTER 8 SELECT Statement: The WHERE Clause . . . . . . . . . . . 213
8.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
8.2 Conditions Using Comparison Operators . . . . . . . . . . . . . . . 215
8.3 Comparison Operators with Subqueries . . . . . . . . . . . . . . . . 222
8.4 Comparison Operators with Correlated Subqueries. . . . . . . . 227
8.5 Conditions Without a Comparison Operator. . . . . . . . . . . . . 229
8.6 Conditions Coupled with AND, OR, XOR, and NOT . . . . . . . 231
8.7 The IN Operator with Expression List. . . . . . . . . . . . . . . . . . . 235
8.8 The IN Operator with Subquery . . . . . . . . . . . . . . . . . . . . . . . 241
8.9 The BETWEEN Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
8.10 The LIKE Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
8.11 The REGEXP Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
8.12 The MATCH Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
8.13 The IS NULL Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
8.14 The EXISTS Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
8.15 The ALL and ANY Operators . . . . . . . . . . . . . . . . . . . . . . . . 281
8.16 Scope of Columns in Subqueries . . . . . . . . . . . . . . . . . . . . . 289
8.17 More Examples with Correlated Subqueries . . . . . . . . . . . . . 294
8.18 Conditions with Negation. . . . . . . . . . . . . . . . . . . . . . . . . . . 299
8.19 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
CHA
PTER 9 SELECT Statement: SELECT Clause and
Aggregation Functions . . . . . . . . . . . . . . . . . . . . . . . . 315
9.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
9.2 Selecting All Columns (*) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
9.3 Expressions in the SELECT Clause . . . . . . . . . . . . . . . . . . . . . 317
9.4 Removing Duplicate Rows with DISTINCT. . . . . . . . . . . . . . . 318
9.5 When Are Two Rows Equal?. . . . . . . . . . . . . . . . . . . . . . . . . . 321
9.6 More Select Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
9.7 An Introduction to Aggregation Functions. . . . . . . . . . . . . . . 324
9.8 COUNT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
9.9 MAX and MIN Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
9.10 The
SUM
and AVG Function. . . . . . . . . . . . . . . . . . . . . . . . . 336
9.11 The
VAR
IANCE and STDDEV Functions. . . . . . . . . . . . . . . . 341
9.12 The
VAR
_SAMP and STDDEV_SAMP Functions . . . . . . . . . 343
9.13 The BIT_AND, BIT_OR, and BIT_XOR Functions . . . . . . . . 343
9.14 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
x Contents
CHA
PTER 10 SELECT Statement: The GROUP BY Clause . . . . . . . . 349
10.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
10.2 Grouping on One Column . . . . . . . . . . . . . . . . . . . . . . . . . . 350
10.3 Grouping on Two or More Columns . . . . . . . . . . . . . . . . . . 353
10.4 Grouping on Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
10.5 Grouping of Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
10.6 Grouping with Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
10.7 General Rules for the GROUP BY Clause . . . . . . . . . . . . . . . 359
10.8 The GROUP_CONCAT Function . . . . . . . . . . . . . . . . . . . . . 362
10.9 Complex Examples with GROUP BY. . . . . . . . . . . . . . . . . . . 363
10.10 Grouping with WITH ROLLUP . . . . . . . . . . . . . . . . . . . . . . 369
10.11 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
CHA
PTER 11 SELECT Statement: The HAVING Clause . . . . . . . . . . 375
11.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
11.2 Examples of the HAVING Clause . . . . . . . . . . . . . . . . . . . . . 376
11.3 A HAVING Clause but not a GROUP BY Clause . . . . . . . . . 378
11.4 General Rule for the HAVING Clause . . . . . . . . . . . . . . . . . . 379
11.5 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
CHA
PTER 12 SELECT Statement: The ORDER BY Clause . . . . . . . . 383
12.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
12.2 Sorting on Column Names . . . . . . . . . . . . . . . . . . . . . . . . . . 383
12.3 Sorting on Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
12.4 Sorting with Sequence Numbers . . . . . . . . . . . . . . . . . . . . . 387
12.5 Sorting in Ascending and Descending Order . . . . . . . . . . . . 389
12.6 Sorting Null Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
12.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
CHA
PTER 13 SELECT Statement: The LIMIT Clause. . . . . . . . . . . . . 395
13.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
13.2 Get the Top… . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
13.3 Subqueries with a LIMIT Clause . . . . . . . . . . . . . . . . . . . . . . 402
13.4 Limit with an Offset . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
13.5 The Select Option
SQL
_CALC_FOUND_ROWS . . . . . . . . . 405
13.6 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
Contents xi
CHA
PTER 14 Combining Table Expressions . . . . . . . . . . . . . . . . . . . 409
14.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
14.2 Combining with UNION. . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
14.3 Rules for Using UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
14.4 Keeping Duplicate Rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
14.5 Set Operators and the Null Value. . . . . . . . . . . . . . . . . . . . . 417
14.6 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
CHA
PTER 15 The User
Var
iable and the SET Statement . . . . . . . . . . 421
15.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
15.2 Defining
Var
iables with the SET Statement . . . . . . . . . . . . . 421
15.3 Defining
Var
iables with the SELECT Statement . . . . . . . . . . 423
15.4 Application Areas for User
Var
iables . . . . . . . . . . . . . . . . . . 425
15.5 Life Span of User
Var
iables . . . . . . . . . . . . . . . . . . . . . . . . . . 426
15.6 The DO Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
15.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
CHA
PTER 16 The HANDLER Statement. . . . . . . . . . . . . . . . . . . . . . 429
16.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
16.2 A Simple Example of the HANDLER Statement . . . . . . . . . . 429
16.3 Opening a Handler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
16.4 Browsing the Rows of a Handler . . . . . . . . . . . . . . . . . . . . . 431
16.5 Closing a Handler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
16.6 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
CHA
PTER 17 Updating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
17.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
17.2 Inserting New Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
17.3 Populating a Table with Rows from Another Table . . . . . . . 442
17.4 Updating Values in Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
17.5 Updating Values in Multiple Tables . . . . . . . . . . . . . . . . . . . 450
17.6 Substituting Existing Rows . . . . . . . . . . . . . . . . . . . . . . . . . . 452
17.7 Deleting Rows from a Table . . . . . . . . . . . . . . . . . . . . . . . . . 454
17.8 Deleting Rows from Multiple Tables. . . . . . . . . . . . . . . . . . . 456
17.9 The TRUNCATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . 458
17.10 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458
xii Contents
CHA
PTER 18 Loading and Unloading Data . . . . . . . . . . . . . . . . . . . 461
18.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
18.2 Unloading Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
18.3 Loading Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
CHA
PTER 19 Working with XML Documents . . . . . . . . . . . . . . . . . . 471
19.1 XML in a Nutshell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
19.2 Storing XML Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
19.3 Querying XML Documents . . . . . . . . . . . . . . . . . . . . . . . . . . 476
19.4 Querying Using Positions . . . . . . . . . . . . . . . . . . . . . . . . . . . 484
19.5 The Extended Notation of XPath . . . . . . . . . . . . . . . . . . . . . 486
19.6 XPath Expressions with Conditions . . . . . . . . . . . . . . . . . . . 488
19.7
Cha
nging XML Documents. . . . . . . . . . . . . . . . . . . . . . . . . . 489
PART III Creating Database Objects. . . . . . . . . . . . . . . . . 491
CHA
PTER 20 Creating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
20.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
20.2 Creating New Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
20.3 Data Types of Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496
20.4 Adding Data Type Options. . . . . . . . . . . . . . . . . . . . . . . . . . 508
20.5 Creating Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . 514
20.6 What If the Table Already Exists? . . . . . . . . . . . . . . . . . . . . . 515
20.7 Copying Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516
20.8 Naming Tables and Columns . . . . . . . . . . . . . . . . . . . . . . . . 521
20.9 Column Options: Default and Comment . . . . . . . . . . . . . . . 522
20.10 Table Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
20.11 The CSV Storage Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . 532
20.12 Tables and the Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
20.13 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
CHA
PTER 21 Specifying Integrity Constraints. . . . . . . . . . . . . . . . . . 539
21.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539
21.2 Primary Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 541
21.3 Alternate Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544
21.4 Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546
21.5 The Referencing Action. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
21.6 Check Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . . . 553
Contents xiii
21.7 Naming Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . 556
21.8 Deleting Integrity Constraints. . . . . . . . . . . . . . . . . . . . . . . . 557
21.9 Integrity Constraints and the Catalog . . . . . . . . . . . . . . . . . 557
21.10 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558
CHA
PTER 22
Char
acter Sets and Collations . . . . . . . . . . . . . . . . . . . 561
22.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
22.2 Available
Char
acter Sets and Collations. . . . . . . . . . . . . . . . 563
22.3 Assigning
Char
acter Sets to Columns. . . . . . . . . . . . . . . . . . 564
22.4 Assigning Collations to Columns . . . . . . . . . . . . . . . . . . . . . 566
22.5 Expressions with
Char
acter Sets and Collations. . . . . . . . . . 568
22.6 Sorting and Grouping with Collations . . . . . . . . . . . . . . . . . 571
22.7 The Coercibility of Expressions. . . . . . . . . . . . . . . . . . . . . . . 573
22.8 Related System
Var
iables . . . . . . . . . . . . . . . . . . . . . . . . . . . 574
22.9
Char
acter Sets and the Catalog . . . . . . . . . . . . . . . . . . . . . . 576
22.10 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576
CHA
PTER 23 The ENUM and SET Types . . . . . . . . . . . . . . . . . . . . . 577
23.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577
23.2 The ENUM Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578
23.3 The SET Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582
23.4 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
CHA
PTER 24
Cha
nging and Dropping Tables . . . . . . . . . . . . . . . . . . 591
24.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
24.2 Deleting Entire Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
24.3 Renaming Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
24.4
Cha
nging the Table Structure . . . . . . . . . . . . . . . . . . . . . . . . 593
24.5
Cha
nging Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
24.6
Cha
nging Integrity Constraints. . . . . . . . . . . . . . . . . . . . . . . 599
24.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
CHA
PTER 25 Using Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
25.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
25.2 Rows, Tables, and Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 604
25.3 How Does an Index Work?. . . . . . . . . . . . . . . . . . . . . . . . . . 605
25.4 Processing a SELECT Statement: The Steps . . . . . . . . . . . . . 610
25.5 Creating Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614
xiv Contents
25.6 Defining Indexes Together with the Tables . . . . . . . . . . . . . . 617
25.7 Dropping Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618
25.8 Indexes and Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
25.9 The Big PLAYERS_XXL Table . . . . . . . . . . . . . . . . . . . . . . . . 620
25.10 Choosing Columns for Indexes. . . . . . . . . . . . . . . . . . . . . . 622
25.11 Indexes and the Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
25.12 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
CHA
PTER 26 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
26.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
26.2 Creating Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
26.3 The Column Names of Views . . . . . . . . . . . . . . . . . . . . . . . . 635
26.4 Updating Views: WITH CHECK OPTION. . . . . . . . . . . . . . . 636
26.5 Options of Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
26.6 Deleting Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
26.7 Views and the Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 640
26.8 Restrictions on Updating Views . . . . . . . . . . . . . . . . . . . . . . 641
26.9 Processing View Statements . . . . . . . . . . . . . . . . . . . . . . . . . 642
26.10 Application Areas for Views . . . . . . . . . . . . . . . . . . . . . . . . 645
26.11 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
CHA
PTER 27 Creating Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . 653
27.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
27.2 Databases and the Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . 653
27.3 Creating Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 654
27.4
Cha
nging Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655
27.5 Dropping Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 656
CHA
PTER 28 Users and Data Security . . . . . . . . . . . . . . . . . . . . . . . 659
28.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 659
28.2 Adding and Removing Users . . . . . . . . . . . . . . . . . . . . . . . . 660
28.3
Cha
nging the Names of Users . . . . . . . . . . . . . . . . . . . . . . . 662
28.4
Cha
nging Passwords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663
28.5 Granting Table and Column Privileges . . . . . . . . . . . . . . . . . 664
28.6 Granting Database Privileges . . . . . . . . . . . . . . . . . . . . . . . . 667
28.7 Granting User Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670
28.8 Passing on Privileges: WITH GRANT OPTION . . . . . . . . . . 673
28.9 Restricting Privileges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674
Contents xv
28.10 Recording Privileges in the Catalog . . . . . . . . . . . . . . . . . . 675
28.11 Revoking Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 677
28.12 Security of and Through Views . . . . . . . . . . . . . . . . . . . . . . 680
28.13 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 682
CHA
PTER 29 Statements for Table Maintenance . . . . . . . . . . . . . . . 683
29.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
29.2 The ANALYZE TABLE Statement . . . . . . . . . . . . . . . . . . . . . 684
29.3 The CHECK
SUM
TABLE Statement . . . . . . . . . . . . . . . . . . . 685
29.4 The OPTIMIZE TABLE Statement. . . . . . . . . . . . . . . . . . . . . 686
29.5 The CHECK TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . 687
29.6 The REPAIR TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . 689
29.7 The BACKUP TABLE Statement . . . . . . . . . . . . . . . . . . . . . . 690
29.8 The RESTORE TABLE Statement . . . . . . . . . . . . . . . . . . . . . 691
CHA
PTER 30 The SHOW, DESCRIBE, and HELP Statements. . . . . . 693
30.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
30.2 Overview of SHOW Statements . . . . . . . . . . . . . . . . . . . . . . 693
30.3 Additional SHOW Statements . . . . . . . . . . . . . . . . . . . . . . . 698
30.4 The DESCRIBE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . 699
30.5 The HELP Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699
PART IV Procedural Database Objects. . . . . . . . . . . . . . . 701
CHA
PTER 31 Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . 703
31.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 703
31.2 An Example of a Stored Procedure . . . . . . . . . . . . . . . . . . . . 704
31.3 The Parameters of a Stored Procedure . . . . . . . . . . . . . . . . . 706
31.4 The Body of a Stored Procedure. . . . . . . . . . . . . . . . . . . . . . 707
31.5 Local
Var
iables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709
31.6 The SET Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712
31.7 Flow-Control Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . 712
31.8 Calling Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 719
31.9 Querying Data with SELECT INTO. . . . . . . . . . . . . . . . . . . . 722
31.10 Error Messages, Handlers, and Conditions . . . . . . . . . . . . 726
31.11 Retrieving Data with a Cursor. . . . . . . . . . . . . . . . . . . . . . . 731
31.12 Including SELECT Statements Without Cursors . . . . . . . . . 736
31.13 Stored Procedures and User
Var
iables . . . . . . . . . . . . . . . . 737
31.14
Char
acteristics of Stored Procedures . . . . . . . . . . . . . . . . . 737
xvi Contents
31.15 Stored Procedures and the Catalog . . . . . . . . . . . . . . . . . . 740
31.16 Removing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . 741
31.17 Security with Stored Procedures . . . . . . . . . . . . . . . . . . . . . 742
31.18 Advantages of Stored Procedures. . . . . . . . . . . . . . . . . . . . 743
CHA
PTER 32 Stored Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745
32.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745
32.2 Examples of Stored Functions . . . . . . . . . . . . . . . . . . . . . . . 746
32.3 More on Stored Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 752
32.4 Removing Stored Functions . . . . . . . . . . . . . . . . . . . . . . . . . 753
CHA
PTER 33 Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755
33.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 755
33.2 An Example of a Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . 756
33.3 More Complex Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . 759
33.4 Triggers as Integrity Constraints. . . . . . . . . . . . . . . . . . . . . . 763
33.5 Removing Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765
33.6 Triggers and the Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . 765
33.7 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765
CHA
PTER 34 Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 767
34.1 What Is an Event?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 767
34.2 Creating Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 768
34.3 Properties of Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 777
34.4
Cha
nging Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 778
34.5 Removing Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
34.6 Events and Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
34.7 Events and the Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 780
PART V Programming with
SQL
. . . . . . . . . . . . . . . . . . . 783
CHA
PTER 35
MySQL
and PHP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 785
35.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 785
35.2 Logging On to
MySQL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 786
35.3 Selecting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 787
35.4 Creating an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 788
35.5 Retrieving Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . 790
35.6 Multiple Connections Within One Session . . . . . . . . . . . . . . 791
35.7
SQL
Statements with Parameters . . . . . . . . . . . . . . . . . . . . . 793
35.8 SELECT Statement with One Row . . . . . . . . . . . . . . . . . . . . 794
Contents xvii
35.9 SELECT Statement with Multiple Rows . . . . . . . . . . . . . . . . 796
35.10 SELECT Statement with Null Values . . . . . . . . . . . . . . . . . . 800
35.11 Querying Data About Expressions . . . . . . . . . . . . . . . . . . . 801
35.12 Querying the Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 803
35.13 Remaining
MYSQL
Functions . . . . . . . . . . . . . . . . . . . . . . . 805
CHA
PTER 36 Dynamic
SQL
with Prepared Statement. . . . . . . . . . . . 807
36.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 807
36.2 Working with Prepared
SQL
Statements . . . . . . . . . . . . . . . 807
36.3 Prepared Statements with User
Var
iables. . . . . . . . . . . . . . . 810
36.4 Prepared Statements with Parameters . . . . . . . . . . . . . . . . . 810
36.5 Prepared Statements in Stored Procedures . . . . . . . . . . . . . 811
CHA
PTER 37 Transactions and Multiuser Usage. . . . . . . . . . . . . . . . 815
37.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815
37.2 What Is a Transaction? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815
37.3 Starting Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 821
37.4 Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 822
37.5 Stored Procedures and Transactions . . . . . . . . . . . . . . . . . . 824
37.6 Problems with Multiuser Usage . . . . . . . . . . . . . . . . . . . . . . 825
37.7 Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 829
37.8 Deadlocks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 830
37.9 The LOCK TABLE and UNLOCK TABLE Statements . . . . . . 830
37.10 The Isolation Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 832
37.11 Waiting for a Lock . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 834
37.12 Moment of Processing Statements . . . . . . . . . . . . . . . . . . . 834
37.13 Working with Application Locks . . . . . . . . . . . . . . . . . . . . . 835
37.14 Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 837
APPENDIX A Syntax of
SQL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839
A.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839
A.2 The BNF Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839
A.3 Reserved Words in
SQL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 843
A.4 Syntax Definitions of
SQL
Statements . . . . . . . . . . . . . . . . . . 845
APPENDIX B Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 903
APPENDIX C System
Var
iables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 953
APPENDIX D Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 963
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 967
Robotics research and technology development have been on the road to
grow and advance for almost half a century. The history of expedition can be
divided into three major periods: the early era, the middle age and the recent
years. The official definition of robot by the Robot Institute of America (RIA)
early on was:
“A robot is a reprogrammable multi-functional manipulator designed
to move material, parts, tools, or specialized devices through
var
iable programmed motions for the performance of a
var
iety of
tasks.”
Today, as commonly recognized, beyond such a professional definition from
history, the general perception of a robot is a manipulatable system to mimic
a human with not only the physical structure, but also the intelligence and
even personality. In the early era, people often remotely manipulated material
via a so-called teleoperator as well as to do many simple tasks in industrial
applications. The teleoperator was soon “married” with the computer numerically
controlled (CNC) milling machine to “deliver” a new-born baby that
was the robot, as depicted in Figure 1.1.
Since then, the robots were getting more and more popular in both industry
and research laboratories. A chronological overview of the major historical
events in robotics evolution during the early era is given as follows:
1947- The 1st servoed electric powered teleoperator was developed;
1948- A teleoperator was developed to incorporate force feedback;
1949- Research on numerically controlled milling machines was initiated;
1954- George Devol designed the first programmable robot;
1956- J. Engelberger bought the rights to found Unimation Co. and produce
the Unimate robots;
1961- The 1st Unimate robot was installed in a GM plant for die
casting
;
1961- The 1st robot incorporating force feedback was developed;
1963- The 1st robot vision system was developed;
當我們需要將
DECIMAL
字段的值轉為
VARCHAR
型時, 常常會遇到一個問題: 如果此字段有4位小數, 那小數點後面都會自動被0補齊. 例如: 18.0000 或 12.0300.
轉為
VARCHAR
時后面的0仍然被保留.
T-
SQL
如下:
select top 3 a.el_qty4 ,'('+ cast(a.el_qty4 as
varchar
(10))+'人)'
（1）执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。
（2）执行外部查询，并显示整个结果。
非相关子查询一般可以分为：返回单值的子查询和返回一个列表的子查询，
在我们写代码的实际业务中，有时候实体类用的是
String
，数据库中自然是
VARCHAR
类型
，但是如果这个实体的属性值放的是数字
类型
，你查询的时候又需要对它进行排序。
sql
怎么写呢。
别担心
mysql
提供了
转换
方法：CAST ,CONVERT
例如：我把
VARCHAR
类型
的价格
转换
成
DECIMAL
然后再进行排序
1.select pri
SELECT `单位详细名称`,`行政区`,`二氧化硫排放量(吨)(核算结果)` from `企业排放量汇总表0815` ORDER BY CONVERT(`二氧化硫排放量(吨)(核算结果)`,
DECIMAL
) desc limit 200;
但是这样对于0值
转换
是有问题的，因此采用如下技巧强制
类型
转换
...
为什么出错 Cannot cast ufunc 'true_divide' output from dtype('float64') to dtype('int64') with
casting
rule 'same_kind'
这个错误是由于计算中出现了浮点数和整数之间的除法操作，导致结果的数据
类型
不一致。在 Python 中，浮点数和整数之间的除法操作会自动将结果
转换
为浮点数，而不能直接将浮点数
转换
为整数。
解决方法是将浮点数
转换
为整数。可以使用函数 int() 将浮点数
转换
为整数，并且在计算中避免使用浮点数除以整数。例如，将除法操作改成整数除法操作，即使用 // 符号代替 / 符号。
早川不爱吃香菜:
Java synchronized偏向锁后hashcode存在哪里？
早川不爱吃香菜:
