第七章 数据生成、转换和操作

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;