第七章 数据生成、转换和操作
1.基本描述
本章主要讲解对字符串、数字或临时数据的生成、转换和操作。
2.基本样例
INSERT INTO string_tbl (char_fld, vchar_fld, text_fld)
VALUES ('This is char data', 'This is varchar data', 'This is text data');
UPDATE string_tbl SET varchar_fld = 'This is a piece of extremely long varchar data';
UPDATE string_tbl SET text_fld = 'This string didn''t work, but it does now';
SELECT text_fld FROM string_tbl;
SELECT quote(text_fld) FROM string_tbl;
DELETE FROM string_tbl;
INSERT INTO string_tbl (char_fld, vchar_fld, text_fld) VALUES
('This string is 28 characters', 'This string is 28 characters', 'This string is 28 characters');
SELECT LENGTH(char_fld) char_length,
LENGTH(vchar_fld) varchar_length, LENGTH(text_fld) text_fld FROM string_tbl;
SELECT POSITION('characters' IN vchar_fld) FROM string_tbl;
SELECT LOCATE('is', vchar_fld, 5) FROM string_tbl;
DELETE FROM string_tbl;
INSERT INTO string_tbl(vchar_fld) VALUES('abcd');
INSERT INTO string_tbl(vchar_fld) VALUES('xyz');
INSERT INTO string_tbl(vchar_fld) VALUES('QRSTUV');
INSERT INTO string_tbl(vchar_fld) VALUES('qrstuv');
INSERT INTO string_tbl(vchar_fld) VALUES('12345');
SELECT vchar_fld FROM string_tbl ORDER BY vchar_fld;
SELECT STRCMP('12345', '12345') 12345_12345, STRCMP('abcd', 'xyz') abcd_xyz,
STRCMP('abcd', 'QRSTUV') abcd_QRSTUV, STRCMP('qrstuv', 'QRSTUV') qrstuv_QRSTUV,
STRCMP('12345', 'xyz') 12345_xyz, STRCMP('xyz', 'qrstuv') xyz_qrstuv;
SELECT name, name LIKE '%ns' ends_in_ns FROM department;
SELECT cust_id, cust_type_cd, fed_id, fed_id REGEXP '.{3}-.{2}-.{4}' is_ss_no_format FROM customer;
DELETE FROM string_tbl;
INSERT INTO string_tbl (text_fld) VALUES ('This string was 29 characters');
UPDATE string_tbl SET text_fld = CONCAT(text_fld, ', but now it is longer');
SELECT text_fld FROM string_tbl;
SELECT CONCAT(fname, ' ', lname, ' has been a ', title, ' since ', start_date) emp_narrative FROM employee
WHERE title = 'Teller' OR title = 'Head Teller';
SELECT INSERT('goodbye world', 9, 0, 'cruel') string;
SELECT INSERT('goodbye world', 1, 7, 'hello') string;
SELECT SUBSTRING('goodbye cruel world', 9, 5);
SELECT (37*59)/(78-(8*6));
SELECT MOD(10, 4);
SELECT POW(2, 8);
SELECT POW(2, 10) kilobyte, POW(2, 20) megabyte, POW(2, 30) gigabyte, POW(2, 40) terabyte;
SELECT CEIL(72.445), FLOOR(72.445);
SELECT CEIL(72.0000000001), FLOOR(72.999999999);
SELECT ROUND(72.49999), ROUND(72.5), ROUND(72.50001);
SELECT ROUND(72.0901, 1), ROUND(72.0909), ROUND(72.0909, 3);
SELECT TRUNCATE(72.0909, 1), TRUNCATE(72.0909, 2), TRUNCATE(72.0909, 3);
SELECT ROUND(17, -1), TRUNCATE(17, -1);
SELECT account_id, SING(avail_balance), ABS(avail_balance) FROM account;
人生忽如茶,莫辜负茶、汤和好天气。