CIS 363 MySQL
Chapter 10 SQL Expressions Chapter 11 Updating Data
Ch.10 SQL Expressions
Expressions are a common element of SQL statements, and they occur in many contexts. Terms of expressions consist of Constants (literal numbers, strings, dates, and times), NULL values, References to table columns Function calls.
Ch.10 SQL Expressions
Here are some examples of expressions:
mysql> SELECT Name, Population FROM Country; (reference to table columns) mysql> SELECT 14, 0312.82, 4.32E-03, 'I am a string'; (literal values and a string value)
mysql> SELECT CURDATE(), VERSION(); (functions)
Ch.10 SQL Expressions
Combination example:
mysql> SELECT name, TRUNCATE(population/surfaceArea, 2) As 'People/sq. km', IF (GNP > GNPOld, 'Increasing', 'Not Increasing') As 'GNP Trend' FROM Country ORDER BY Name LIMIT 10;
Table columns: Name, Population, SurfaceArea, GNP, and GNPGOLD. Literal values: 'Increasing', 'Not increading' and column aliases are all string constants. Functions: TRUNCATE() and IF()
Ch.10 SQL Expressions
Numeric Expressions

Numbers can be exact-values (Integer and Decimal data type) or approximate-value literals (Float or Double data type). BOOLEAN Result: 1=Yes, 0=No.
mysql> SELECT 1.1 + 2.2 = 3.3, 1.1E0 + 2.2E0 = 3.3E0;
If you mix numbers and strings, mysql will attempt a string>number conversion.
mysql> SELECT 1+'1', 1 = '1';
Ch.10 SQL Expressions
String Expressions
Literal string in expressions are written as quoted values. The data type for representing strings in tables include CHAR, VARCHAR, BINCHAR, VARBINARY, and the TEXT and BLOB types. The usual comparison operators apply to string values (=, , SELECT CONCAT('abc', 'def', REPEAT('X',3));
Ch.10 SQL Expressions
|| operator is usually a logical OR, but under ANSI SQL it s/b Concatenation.
mysql> SELECT 'abc' || 'def'; (perform a logical OR operation)
mysql> SET sql_mode='PIPES_AS_CONCAT';

下一页