By default, "mysql" returns query output in text table format. If you want to receive query output in XML format, you need to use the "-X" command option. Here is a good tutorial exercise:
>cd mysqlbin
>mysql -u root -X test
mysql> SELECT * FROM links;
<?xml version="1.0"?>
<resultset statement="SELECT * FROM links">
<row>
<field name="id">1</field>
<field name="name">www.GlobalGuideLine.com</field>
</row>
<row>
<field name="id">10</field>
<field name="name">www.GlobalGuideLine.com</field>
</row>
</resultset>
问题 52
What Is SQL in MySQL?
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.
问题 53
How Many Groups of Data Types?
MySQL support 3 groups of data types as listed below:
► String Data Types - CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET
► Numeric Data Types - BIT, TINYINT, BOOLEAN, SMALLINT, MEDIUMINT, INTEGER, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL.
► Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.
问题 54
What Are String Data Types?
MySQL supports the following string data types:
► CHAR(n) same as CHARACTER(n) - Fixed width and " " padded characters strings. Default character set is ASCII.
► NCHAR(n) same as NATIONAL CHARACTER(n) - Fixed width and " " padded character strings with UTF8 character set.
► VARCHAR(n) same as CHARACTER VARYING(n) - Variable width character strings. Default character set is ASCII.
► NVARCHAR(n) same as NATIONAL CHARACTER VARYING(n) - Variable width character strings with UTF8 character set.
► BINARY(n) - Fixed width and 0x00 padded byte strings.
► VARBINARY(n) same as BINARY VARYING(n) - Variable width byte string.
► TINYBLOB - BLOB (Binary Large Object) upto 255 bytes.
► BLOB - BLOB (Binary Large Object) upto 64K bytes.
► MEDIUMBLOB - BLOB (Binary Large Object) upto 16M bytes.
► LONGBLOB - BLOB (Binary Large Object) upto 4G bytes.
► TINYTEXT - CLOB (Binary Large Object) upto 255 characters.
► TEXT - CLOB (Binary Large Object) upto 64K characters.
► MEDIUMTEXT - CLOB (Binary Large Object) upto 16M characters.
► LONGTEXT - CLOB (Binary Large Object) upto 4G characters.
► ENUM - An enumeration to hold one entry of some pre-defined strings.
► SET - A set to hold zero or more entries of some pre-defined strings.
问题 55
What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length string data types. But they have the following differences:
► CHAR's full name is CHARACTER.
► NCHAR's full name is NATIONAL CHARACTER.
► By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.
► By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character could be stored as 1 byte or upto 4 bytes.
► Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.
The following column definitions are the same:
CREATE TABLE faq (Title NCHAR(80));
CREATE TABLE faq (Title NATIONAL CHAR(80));
CREATE TABLE faq (Title NATIONAL CHARACTER(80));
CREATE TABLE faq (Title CHAR(80) CHARACTER SET utf8);
CREATE TABLE faq (Title CHARACTER(80) CHARACTER SET utf8);
问题 56
What Are the Differences between CHAR and VARCHAR?
CHAR and VARCHAR are both ASCII character data types by default. But they have the following major differences:
► CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
► VARCHAR stores values in variable lengths. Values are not padded with any characters. But 1 or 2 extra bytes are added to store the length of the data.
The table below shows you a good comparison of CHAR and VARCHAR data types:
Value CHAR(4) Length
'' ' ' 4 bytes
'ab' 'ab ' 4 bytes
'abcd' 'abcd' 4 bytes
Value VARCHAR(4) Length
'' '' 1 byte
'ab' 'ab' 3 bytes
'abcd' 'abcd' 5 bytes
问题 57
What Are the Differences between BINARY and VARBINARY?
Both BINARY and VARBINARY are both binary byte data types. But they have the following major differences:
► BINARY stores values in fixed lengths. Values are padded with 0x00.
► VARBINARY stores values in variable lengths. Values are not padded with any bytes. But 1 or 2 extra bytes are added to store the length of the data.
问题 58
What Are Numeric Data Types?
MySQL supports the following numeric data types:
► BIT(n) - An integer with n bits.
► BOOL same as BOOLEAN - Boolean values stored in 1 bit.
► TINYINT - A small integer stored in 1 byte.
► SMALLINT - A small integer stored in 2 bytes.
► MEDIUMINT - A medium integer stored in 3 bytes.
► INT same as INTEGER - An integer stored in 4 bytes.
► BIGINT - An integer stored in 8 bytes.
► FLOAT - A single-precision floating-point number stored in 4 bytes.
► DOUBLE same as DOUBLE PRECISION - A double-precision floating-point number stored in 8 bytes.
► REAL - Same DOUBLE by default.
► DECIMAL(m,d) - A fixed-point number with m as the precision (total number of digits) and d as the scale (number of digits after the decimal point).
► Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.
问题 59
What Are Date and Time Data Types?
MySQL supports the following date and time data types:
► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
► DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".
问题 60
How To Calculate Expressions with SQL Statements?
There is no special SQL statements to calculate expressions. But you can use the "SELECT expression FROM DUAL" statement return the calculated value of an expression. "DUAL" is a dummy table in the server. The tutorial exercise below shows you some good examples:
SELECT 'Hello world!' FROM DUAL;
Hello world!
SELECT (1+2)*3/4 FROM DUAL;
2.2500
SELECT TRUE FROM DUAL;
1
SELECT TRUE AND FALSE FROM DUAL;
0
SELECT TIME(SYSDATE()) FROM DUAL;
21:30:26
问题 61
How To Include Comments in SQL Statements?
If you want to include comments in a SQL statement, you can first enter "--", then enter your comment until the end of the line. The tutorial exercise below shows you some good examples:
SELECT 'Hello world!' FROM DUAL; -- My first SQL statement!
INSERT INTO links VALUES ('GlobalGuideLine.com'); -- Top rated!
CREATE TABLE faq (
id INTEGER, -- primary key
title VARCHAR(80) -- FAQ title
);
问题 62
How To Include Character Strings in SQL statements?
If you want to include character strings in your SQL statements, you need to quote them in one of the following formats:
► Using single quotes. For example 'GlobalGuideLine.com'.
► Using double quotes. For example "ggl Center".
► Using single quotes prefixed with N for NATIONAL characters (same as UTF8 characters). For example N'Allo, Francois.'.
► Using single quotes prefixed with _utf8 for UTF8 characters. For example _utf8'Allo, Francois.'.
问题 63
How To Escape Special Characters in SQL statements?
There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:
► The escape character () needs to be escaped as ().
► The single quote (') needs to be escaped as (') or ('') in single-quote quoted strings.
► The double quote (") needs to be escaped as (") or ("") in double-quote quoted strings.
► The wild card character for a single character (_) needs to be escaped as (_).
► The wild card character for multiple characters (%) needs to be escaped as (%).
► The tab character needs to be escaped as (t).
► The new line character needs to be escaped as (n).
► The carriage return character needs to be escaped as (r).
Here are some examples of how to include special characters:
SELECT 'It''s Sunday!' FROM DUAL;
It's Sunday!
SELECT 'Allo, C'est moi.' FROM DUAL;
Allo, C'est moi.
SELECT 'MontTuetWedtThutFri' FROM DUAL;
Mon Tue Wed Thu Fri
问题 64
How To Concatenate Two Character Strings?
If you want concatenate multiple character strings into one, you need to use the CONCAT() function. Here are some good examples:
SELECT CONCAT('Welcome',' to') FROM DUAL;
Welcome to
SELECT CONCAT('wb','center','.com') FROM DUAL;
WithoutBook.com
问题 65
How To Include Numeric Values in SQL statements?
If you want to include a numeric value in your SQL statement, you can enter it directly as shown in the following examples:
SELECT 255 FROM DUAL; -- An integer
255
SELECT -6.34 FROM DUAL; -- A regular number
-6.34
SELECT -32032.6809e+10 FROM DUAL; -- A floating-point value
-3.20326809e+014
问题 66
How To Enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (0x). A HEX number string will be automatically converted into a character string, if the expression context is a string. Here are some good examples:
SELECT X'313233' FROM DUAL;
123
SELECT 0x414243 FROM DUAL;
ABC
SELECT 0x46594963656E7465722E636F6D FROM DUAL;
WithoutBook.com
问题 67
How To Enter Numeric Values as HEX Numbers?
If you want to enter numeric values as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (0x). A HEX number string will be automatically converted into a numeric value, if the expression context is a numeric value. Here are some good examples:
SELECT X'10' + 16 FROM DUAL;
32
SELECT 0x1000 + 0 FROM DUAL;
4096
问题 68
How To Enter Binary Numbers in SQL Statements?
If you want to enter character strings or numeric values as binary numbers, you can quote binary numbers with single quotes and a prefix of (B), or just prefix binary numbers with (0b). Binary numbers will be automatically converted into character strings or numeric values based on the expression contexts. Here are some good examples:
SELECT B'010000010100001001000011' FROM DUAL;
ABC
SELECT 0b1000 + 0 FROM DUAL;
8
问题 69
What Are NULL Values?
NULL is a special value that represents no value. Here are basic rules about NULL values:
► NULL presents no value.
► NULL is not the same as an empty string ''.
► NULL is not the same as a zero value 0.
► NULL can be used as any data type.
► NULL should not be used in any comparison options.
► NULL has its own equality operator "IS".
► NULL has its own not-equality operator "IS NOT".
问题 70
What Happens If NULL Values Are Involved in Expressions?
If NULL values are used in expressions, the resulting values will be NULL values. In other words:
► Arithmetic expressions with NULL values result NULL values.
► Comparison expressions with NULL values result NULL values.
► Logical expressions with NULL values result NULL values.
The tutorial exercise shows you some interesting examples:
SELECT NULL + NULL FROM DUAL;
NULL
SELECT NULL + 7 FROM DUAL;
NULL
SELECT NULL * 7 FROM DUAL;
NULL
SELECT NULL = NULL FROM DUAL;
NULL
SELECT 0 < NULL FROM DUAL;
NULL
SELECT '' > NULL FROM DUAL;
NULL
SELECT NULL AND TRUE FROM DUAL;
NULL
SELECT NULL OR TRUE FROM DUAL;
1
-- This is contradicting against the rules!
问题 71
How To Convert Numeric Values to Character Strings?
You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples:
SELECT CAST(4123.45700 AS CHAR) FROM DUAL;
4123.45700
-- How to get rid of the last 2 '0's?
SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
4123.457
SELECT CAST(1/3 AS CHAR);
0.3333
-- Very poor conversion
问题 72
How To Convert Character Strings to Numeric Values?
You can convert character strings to numeric values by using the CAST(string AS DECIMAL) or CAST(string AS SIGNED INTEGER) function as shown in the following examples:
SELECT CAST('4123.45700' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion
SELECT CAST('4.12345700e+3' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion
SELECT CAST('4123.45700' AS SIGNED INTEGER) FROM DUAL;
4123
SELECT CAST('4.12345700e+3' AS SIGNED INTEGER) FROM DUAL;
4
-- Very poor conversion
问题 73
How To Use IN Conditions?
An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.
Some examples are given in the tutorial exercise below:
SELECT 3 IN (1,2,3,4,5) FROM DUAL;
1
SELECT 3 NOT IN (1,2,3,4,5) FROM DUAL;
0
SELECT 'Y' IN ('F','Y','I') FROM DUAL;
1
问题 74
How To Use LIKE Conditions?
A LIKE condition is also called pattern patch. There are 3 main rules on using LIKE condition:
* '_' is used in the pattern to match any one character.
* '%' is used in the pattern to match any zero or more characters.
* ESCAPE clause is used to provide the escape character in the pattern.
The following tutorial exercise provides you some good pattern matching examples:
SELECT 'WithoutBook.com' LIKE '%center%' FROM DUAL;
1
SELECT 'WithoutBook.com' LIKE '%CENTER%' FROM DUAL;
1
-- Case insensitive by default
SELECT 'WithoutBook.com' LIKE '%CENTER_com' FROM DUAL;
1
问题 75
How To Use Regular Expression in Pattern Match Conditions?
If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern condition: REGEXP. The following tutorial exercise provides you some good examples:
SELECT 'WithoutBook.com' REGEXP '.*ggl.*' FROM DUAL;
1
SELECT 'WithoutBook.com' REGEXP '.*com$' FROM DUAL;
1
SELECT 'WithoutBook.com' REGEXP '^F.*' FROM DUAL;
1
问题 76
How To Use CASE Expression?
There are 2 ways to use the CASE expression. The first way is to return one of the predefined values based on the comparison of a given value to a list of target values. The second way is to return one of the predefined values based on a list of conditions. Here is the syntax of both types of CASE expressions:
CASE value WHEN target_value THEN result
WHEN target_value THEN result
WHEN target_value THEN result
...
ELSE result
END
CASE WHEN condition THEN result
WHEN condition THEN result
WHEN condition THEN result
...
ELSE result
END
The tutorial exercise below gives two good examples:
SELECT CASE 'Sun' WHEN 'Mon' THEN 'Open'
WHEN "Fri" THEN "Open" ELSE 'Closed' END FROM DUAL;
Closed
SELECT CASE WHEN HOUR(CURRENT_TIME())<9 THEN
'Closed'
WHEN HOUR(CURRENT_TIME())>17 THEN 'Closed'
ELSE 'Open' END FROM DUAL;
Closed
问题 77
What Are Date and Time Data Types in MySQL?
MySQL supports the following date and time data types:
► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
► DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
► YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".
问题 78
How To Write Date and Time Literals?
MySQL offers a number of formats for you to use to enter date and time literals:
► ANSI standard format: "YYYY-MM-DD HH:MM:SS".
► Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS" or "YYYY.MM.DD HH-MM-SS".
► No limiters. Like: "YYYYMMDD" for a date or "HHMMSS" for a time.
► Decimal numbers. Like: 8 digits dddddddd for a date or 6 digits dddddd for a time.
The tutorial exercise below gives you some good examples:
SELECT DATE('1997-01-31') FROM DUAL;
1997-01-31
SELECT DATE('1997-01-31 09:26:50') FROM DUAL;
1997-01-31
SELECT TIME('1997-01-31 09:26:50') FROM DUAL;
09:26:50
SELECT DATE('1997/01/31 09^26^50') FROM DUAL;
1997-01-31
SELECT TIME('1997/01/31 09^26^50') FROM DUAL;
09:26:50
SELECT DATE('19970131') FROM DUAL;
1997-01-31
SELECT TIME('092650') FROM DUAL;
09:26:50
SELECT DATE(19970131) FROM DUAL; -- Crazy format
1997-01-31
SELECT TIME(092650) FROM DUAL; -- Crazy format
09:26:50
问题 79
How To Enter Microseconds in SQL Statements?
If you want to enter microseconds in a SQL statements, you can enter them right after the time string as a 6-digit number delimited with '.'. '0' will be padded to right if not enough digits. Here are some good examples:
SELECT TIME('1997/01/31 09^26^50.123') FROM DUAL;
09:26:50.123000
SELECT TIME('1997/01/31 09^26^50.000123') FROM DUAL;
09:26:50.000123
问题 80
How To Convert Dates to Character Strings?
You can convert dates to character strings using the DATE_FORMAT(date, format) function. MySQL supports the following basic formatting codes:
► %a Abbreviated weekday name (Sun..Sat)
► %b Abbreviated month name (Jan..Dec)
► %c Month, numeric (0..12)
► %D Day of the month with English suffix (0th, 1st, 2nd, 3rd,...)
► %d Day of the month, numeric (00..31)
► %e Day of the month, numeric (0..31)
► %f Microseconds (000000..999999)
► %H Hour (00..23)
► %h Hour (01..12)
► %I Hour (01..12)
► %i Minutes, numeric (00..59)
► %j Day of year (001..366)
► %k Hour (0..23)
► %l Hour (1..12)
► %M Month name (January..December)
► %m Month, numeric (00..12)
► %p AM or PM
► %r Time, 12-hour (hh:mm:ss followed by AM or PM)
► %S Seconds (00..59)
► %s Seconds (00..59)
► %T Time, 24-hour (hh:mm:ss)
► %W Weekday name (Sunday..Saturday)
► %w Day of the week (0=Sunday..6=Saturday)
► %Y Year, numeric, four digits
► %y Year, numeric (two digits)
问题 81
How To Convert Character Strings to Dates?
If you have a character string that represents a date, and you want to convert it into a date value, you can use the STR_TO_DATE(string, format) function. STR_TO_DATE() shares the same formatting codes with DATE_FORMAT() function. The tutorial exercise below shows you some good examples:
SELECT STR_TO_DATE('Friday, January 31, 1997',
'%W, %M %e, %Y') FROM DUAL;
1997-01-31
SELECT STR_TO_DATE('Friday, January 31, 1997, 09:26:50 AM',
'%W, %M %e, %Y, %h:%i:%s %p') FROM DUAL;
1997-01-31 09:26:50
SELECT STR_TO_DATE('31-Jan-1997 09:26:50.000123',
'%d-%b-%Y %H:%i:%s.%f') FROM DUAL;
1997-01-31 09:26:50.000123
问题 82
What Are Date and Time Intervals?
A date and time interval is special value to be used to increment or decrement a date or a time at a given date or time unit. A data and time interval should be expression in the format of "INTERVAL expression unit", where "unit" and "expression" should follow these rules:
unit - expression value format
MICROSECOND - MICROSECONDS
SECOND - SECONDS
MINUTE - MINUTES
HOUR - HOURS
DAY - DAYS
WEEK - WEEKS
MONTH - MONTHS
QUARTER - QUARTERS
YEAR -YEARS
SECOND_MICROSECOND - 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND - 'MINUTES.MICROSECONDS'
MINUTE_SECOND - 'MINUTES:SECONDS'
HOUR_MICROSECOND - 'HOURS.MICROSECONDS'
HOUR_SECOND - 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE - 'HOURS:MINUTES'
DAY_MICROSECOND - 'DAYS.MICROSECONDS'
DAY_SECOND - 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE - 'DAYS HOURS:MINUTES'
DAY_HOUR - 'DAYS HOURS'
YEAR_MONTH - 'YEARS-MONTHS'
问题 83
How To Increment Dates by 1 in MySQL?
If you have a date, and you want to increment it by 1 day, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval add operation as "date + INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:
SELECT DATE_ADD(DATE('1997-01-31'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-01
SELECT DATE('1997-01-31') + INTERVAL 1 DAY FROM DUAL;
1997-02-01
问题 84
How To Decrement Dates by 1 in MySQL?
If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:
SELECT DATE_SUB(DATE('1997-03-01'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-28
SELECT DATE('1997-01-31') - INTERVAL 1 DAY FROM DUAL;
1997-02-28
问题 85
How To Calculate the Difference between Two Dates?
If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(date1, date2) function as shown below:
SELECT DATEDIFF(DATE('1997-02-28'), DATE('1997-03-01'))
FROM DUAL;
-1
问题 86
How To Calculate the Difference between Two Time Values?
If you have two time values, and you want to know the time difference between them, you can use the TIMEDIFF(time1, time2) function as shown below:
SELECT TIMEDIFF(TIME('19:26:50'), TIME('09:26:50'))
FROM DUAL;
10:00:00
SELECT TIMEDIFF('1997-03-01 19:26:50.000123',
'1997-02-28 09:26:50.000000') FROM DUAL;
34:00:00.000123
问题 87
How To Present a Past Time in Hours, Minutes and Seconds?
If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:
SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;
06:42:58
SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),
'%H hours, %i minutes and %s seconds ago.') FROM DUAL;
30 hours, 45 minutes and 22 seconds ago.
问题 88
How To Extract a Unit Value from a Date and Time?
If you want to extract a specific date or time unit value out of a date or a time, you can use the EXTRACT(unit FROM expression) function. The tutorial exercise below gives you some good examples:
ELECT EXTRACT(DAY FROM NOW()) FROM DUAL;
28
ELECT EXTRACT(HOUR FROM NOW()) FROM DUAL;
23
ELECT EXTRACT(SECOND FROM NOW()) FROM DUAL;
36
问题 89
What Are Date and Time Functions in MySQL?
MySQL offers a number of functions for date and time values:
► ADDDATE(date, INTERVAL expr unit) - Adding days to a date. Same as DATE_ADD().
► ADDTIME(time1, time2) - Adding two time values together.
► CURDATE() - Returning the current date. Same as CURRENT_DATE().
► CURTIME() - Returning the current time. Same as CURRENT_TIME().
► DATE(expression) - Returning the date from the expression.
► DATEDIFF(date1, date2) - Returning dates difference in days.
► DATE_ADD(date, INTERVAL expr unit) - Adding days to a date.
► DATE_SUB(date, INTERVAL expr unit) - Subtracting days from a date.
► DATE_FORMAT(date, format) - Returning a character string representing a date.
► DAY(date) - Returning an integer representing the day of the month. Same as DAYOFMONTH()
► DAYNAME(date) - Returning the name of week day.
► DAYOFMONTH(date) - Returning an integer representing the day of the month.
► DAYOFWEEK(date) - Returning an integer representing the day of the week.
► DAYOFYEAR(date) - Returning an integer representing the day of the year.
问题 90
What Is TIMESTAMP in MySQL?
A TIMESTAMP data type allows you to record a date and time like DATETIME data type. But it has some interesting features when used on a table column:
► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an INSERT statement.
► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an UPDATE statement.
► If a TIMESTAMP column is assigned with NULL, the current date and time will be persisted to this column.
► You can turn off the default current date and time on INSERT by defining the column with "DEFAULT 0".
► The tutorial exercise below provides you 2 equivalent ways to create a TIMESTAMP column:
CREATE TABLE links (lastDate TIMESTAMP);
CREATE TABLE links (lastDate TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
问题 91
How Many Ways to Get the Current Time?
There are 8 ways to get the current time:
SELECT NOW() FROM DUAL;
2006-07-01 10:02:41
SELECT CURRENT_TIME() FROM DUAL;
10:02:58
SELECT SYSDATE() FROM DUAL;
2006-07-01 10:03:21
mysql> SELECT CURRENT_TIMESTAMP() FROM DUAL;
2006-07-01 10:04:03
SELECT LOCALTIME() FROM DUAL;
2006-07-01 10:07:37
mysql> SELECT LOCALTIMESTAMP() FROM DUAL;
2006-07-01 10:08:08
mysql> SELECT UTC_TIME() FROM DUAL;
14:09:22
mysql> SELECT UTC_TIMESTAMP() FROM DUAL;
2006-07-01 14:09:49
问题 92
What Are DDL Statements in MySQL?
DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:
► CREATE - Creating a new database object.
► ALTER - Altering the definition of an existing data object.
► DROP - Dropping an existing data object.
问题 93
How To Create a New Table in MySQL?
If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip":
mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)
This scripts creates a testing table called "tip" with 4 columns in the current database.
问题 94
What Happens If You No CREATE Privilege in a Database?
In order to create tables in a database, your user account must have the CREATE privilege for that database. Otherwise you will get an error as shown in the following tutorial exercise:
>cd mysqlbin
>mysql -u guest -ppub
mysql> use ggl;
Database changed
mysql> CREATE TABLE test (id integer);
ERROR 1142 (42000): CREATE command denied to user
'guest'@'localhost' for table 'test'
If you get this error, you need to see the DBA to obtain the CREATE privilege.
问题 95
How To Get a List of All Tables in a Database?
If you want to see the table you have just created, you can use the "SHOW TABLES" command to get a list of all tables in database. The tutorial script gives you a good example:
mysql> SHOW TABLES;
+---------------+
| Tables_in_ggl |
+---------------+
| links |
| tip |
+---------------+
3 rows in set (0.00 sec)
问题 96
How To Get a List of Columns in an Existing Table?
mysql> SHOW COLUMNS FROM test;
问题 97
How To See the CREATE TABLE Statement of an Existing Table?
问题 98
How To Create a New Table by Selecting Rows from Another Table in MySQL?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The tutorial script below gives you a good example:
mysql> INSERT INTO tip VALUES (1, 'Learn MySQL',
'Visit www.GlobalGuideLine.com','2006-07-01');
Query OK, 1 row affected (0.62 sec)
mysql> CREATE TABLE tipBackup SELECT * FROM tip;
Query OK, 1 row affected (0.49 sec)
Records: 1 Duplicates: 0 Warnings: 0
问题 99
How To Add a New Column to an Existing Table in MySQL?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD COLUMN" statement. The tutorial script below shows you a good example:
mysql> ALTER TABLE test ADD COLUMN author VARCHAR(40);
Query OK, 1 row affected (0.18 sec)
Records: 1 Duplicates: 0 Warnings: 0
问题 100
How To Delete an Existing Column in a Table?
If you have an existing column in a table and you do not need that column any more, you can delete it with "ALTER TABLE ... DROP COLUMN" statement. Here is a tutorial script to delete an existing column:
mysql> ALTER TABLE test DROP COLUMN create_date;
Query OK, 1 row affected (0.48 sec)
Records: 1 Duplicates: 0 Warnings: 0
问题 101
How To Rename an Existing Column in a Table?
If you have an existing column in a table and you want to change the column name, you can use the "ALTER TABLE ... CHANGE" statement. This statement allows you to change the name of a column, and its definition. The tutorial script below gives you a good example:
mysql> ALTER TABLE test CHANGE COLUMN subject
title VARCHAR(60);
Query OK, 1 row affected (0.51 sec)
Records: 1 Duplicates: 0 Warnings: 0
问题 102
How To Rename an Existing Table in MySQL?
If you want to rename an existing table, you can use the "ALTER TABLE ... RENAME TO" statement. The tutorial script below shows you a good example:
mysql> ALTER TABLE test RENAME TO wb;
Query OK, 0 rows affected (0.01 sec)
问题 103
How To Drop an Existing Table in MySQL?
If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below:
mysql> DROP TABLE tipBackup;
Query OK, 0 rows affected (0.00 sec)
问题 104
How To Create a Table Index in MySQL?
If you have a table with a lots of rows, and you know that one of the columns will be used often as a search criteria, you can add an index for that column to improve the search performance. To add an index, you can use the "CREATE INDEX" statement as shown in the following script:
mysql> CREATE TABLE wb(id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE INDEX wb_subject ON wb(subject);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
问题 105
How To Get a List of Indexes of an Existing Table?
If you want to see the index you have just created for an existing table, you can use the "SHOW INDEX FROM tableName" command to get a list of all indexes in a given table. The tutorial script below shows you a nice example:
mysql> SHOW INDEX FROM test;
问题 106
How To Drop an Existing Index in MySQL?
If you don't need an existing index any more, you should delete it with the "DROP INDEX indexName ON tableName" statement. Here is an example SQL script:
mysql> DROP INDEX wb_subject ON test;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
问题 107
How To Create a New View in MySQL?
You can create a new view based on one or more existing tables by using the "CREATE VIEW viewName AS selectStatement" statement as shown in the following script:
mysql> CREATE TABLE comment (faqID INTEGER,
message VARCHAR(256));
Query OK, 0 rows affected (0.45 sec)
mysql> INSERT INTO comment VALUES (1, 'I like it');
Query OK, 1 row affected (0.00 sec)
mysql> CREATE VIEW faqComment AS SELECT f.id, f.title,
f.description, c.message FROM faq f, comment c
WHERE f.id = c.faqID;
Query OK, 0 rows affected (0.06 sec)
问题 108
How To Drop an Existing View in MySQL?
If you have an existing view, and you don't want it anymore, you can delete it by using the "DROP VIEW viewName" statement as shown in the following script:
mysql> DROP VIEW faqComment;
Query OK, 0 rows affected (0.00 sec)
