MySQL 学习笔记
--使用正则表达式
-- ^ 开始 ， . 任意字符
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP '^.[a-e].*' ORDER BY Name;
-- $ 结尾
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP '.*g$' ORDER BY Name;
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP 'o.o' ORDER BY Name;
-- + 匹配一个或多个前一个字符
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP 'oc+o' ORDER BY Name;
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP 'oc*o' ORDER BY Name;
-- ？ 匹配零个或多个前一个字符
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP 'oc?o' ORDER BY Name;
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP '^.[aeiou].*' ORDER BY Name;
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP '^.[a-e].*' ORDER BY Name;
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP '^.[^a-e].*' ORDER BY Name;
--中间的[:space:]表示匹配空格
SELECT Name, Continent, Population FROM Country WHERE Name REGEXP '[[:space:]]' ORDER BY Name;
SELECT Name, Continent, Population FROM Country WHERE Name RLIKE '[[:space:]]' ORDER BY Nam
-- 可以查看官方网站帮助获得更多内容 http://dev.mysql.com/doc/refman/5.6/en/regexp.html
--字符串
SELECT 'hello, world';
SELECT 'hello' ', ' 'world';
SELECT "hello, world";
SELECT 'hello, "world"';
SELECT 'hello, ''world''';
SELECT 'hello, \'world\'';
--创建表
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id INTEGER not null default 47,
-- id INTEGER AUTO_INCREMENT primary key,
-- 其中 INTEGER AUTO_INCREMENT primary key 可以简写为SERIAL （4.1 以前的版本不支持）唯一的区别是integer的大小由11变为了20
a VARCHAR(255) unique not null,
b VARCHAR(255) unique,
stamp TIMESTAMP, -- 默认会随记录更新而更新
INDEX(a) ,
FOREIGN KEY (a) REFERENCES client(id)
);
INSERT INTO TEST ( id, a, b ) VALUES ( 1, 'one', 'two' );
INSERT INTO TEST ( id, a, b ) VALUES ( 2, 'two', 'three' );
INSERT INTO TEST ( id, a, b ) VALUES ( 3, 'three', 'four' );
DESCRIBE test; --查看表结构
SHOW TABLE STATUS; -- 显示数据库中所有表的状态
SHOW TABLE STATUS like ‘test'; -- 显示某个表的状态
SHOW CREATE TABLE test; --显示创建test表的sql语句
SHOW INDEXES FROM test; -- 显示test表的索引
SELECT LAST_INSERT_ID() ; – 显示最后插入的id，注意这个是全局的，所以要看的话需要在插入后面调用
-- ENUM
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id SERIAL,
a ENUM( 'Pablo', 'Henri', 'Jackson' )
);
INSERT INTO test ( a ) VALUES ( 'Pablo' );
INSERT INTO test ( a ) VALUES ( 'Henri' );
INSERT INTO test ( a ) VALUES ( 'Jackson' );
INSERT INTO test ( a ) VALUES ( 1 );
INSERT INTO test ( a ) VALUES ( 2 );
INSERT INTO test ( a ) VALUES ( 3 );
SELECT * FROM test;
-- SET
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id SERIAL,
a SET( 'Pablo', 'Henri', 'Jackson' )
);
INSERT INTO test ( a ) VALUES ( 'Pablo' );
INSERT INTO test ( a ) VALUES ( 'Henri' );
INSERT INTO test ( a ) VALUES ( 'Jackson' );
INSERT INTO test ( a ) VALUES ( 'Pablo,Jackson,Henri,Henri,Henri' );
INSERT INTO test ( a ) VALUES ( 1 );
INSERT INTO test ( a ) VALUES ( 2 );
INSERT INTO test ( a ) VALUES ( 3 );
INSERT INTO test ( a ) VALUES ( 4 );
INSERT INTO test ( a ) VALUES ( 5 );
INSERT INTO test ( a ) VALUES ( 6 );
INSERT INTO test ( a ) VALUES ( 7 );
SELECT COUNT(*) FROM test;
SELECT * FROM test;
DESCRIBE test;
SHOW CREATE TABLE test;
函数
length() --计算长度 bytes
CHAR_LENGTH() --计算长度 字符数，运算时间比length()长，因为要解析unicode
mid(name,2,3)
concat(Name, localname) -- 字符串拼接
concat_ws(‘,’,name,locaname) -- 可以设置分隔符
SELECT GROUP_CONCAT(Name) FROM Country WHERE Region = 'Western Europe';
SELECT GROUP_CONCAT(Name) FROM Country GROUP BY Region;
SELECT GROUP_CONCAT(DISTINCT Continent ORDER BY Continent SEPARATOR '/') FROM Country;
SELECT SUBSTR(a, 1, 2) AS State, SUBSTR(a, 3) AS SCode, SUBSTR(b, 1, 2) AS Country,
SUBSTR(b, 3) AS CCode FROM t;
locate(‘bar’,’foobarbaz’) – 显示bar在foobarbaz的位置
reverse(name) -- 反转大小写
select 7 DIV 3 --整除
SELECT CURRENT_TIMESTAMP();
SELECT UNIX_TIMESTAMP(); – 表示19700101至今过去的秒数
SELECT UTC_TIMESTAMP();
SELECT DAYNAME(NOW());
SELECT DAYOFMONTH(NOW());
SELECT DAYOFWEEK(NOW());
SELECT DAYOFYEAR(NOW());
SELECT MONTHNAME(NOW());
SELECT TIME_TO_SEC('00:03:00'); -- 时间变秒
SELECT SEC_TO_TIME(180); --秒变时间
SELECT ADDTIME('1:00:00', '00:29:45');
SELECT SUBTIME('1:30:00', '00:15:00');
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
--时区
-- 可以在https://en.wikipedia.org/wiki/List_of_tz_database_time_zones 发现所有时区清单
SHOW VARIABLES LIKE '%time_zone%';
SELECT NOW();
SET time_zone = 'US/Eastern';
SHOW VARIABLES LIKE '%time_zone%';
SELECT NOW();
--时间格式
SELECT DATE_FORMAT(NOW(), '%W, %D of %M, %Y');
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T');
-- 事务
-- 使用事务会增加性能
START TRANSACTION;
INSERT INTO widgetSales ( inv_id, quan, price ) VALUES ( 1, 5, 500 );
UPDATE widgetInventory SET onhand = ( onhand - 5 ) WHERE id = 1;
COMMIT;
START TRANSACTION;
INSERT INTO widgetInventory ( description, onhand ) VALUES ( 'toy', 25 );
ROLLBACK;
-- 触发器
-- 01 Updating a table with a trigger
CREATE TABLE widgetCustomer ( id SERIAL, name VARCHAR(255), last_order_id BIGINT );
CREATE TABLE widgetSale ( id SERIAL, item_id BIGINT, customer_id BIGINT, quan INT, price DECIMAL(9,2) );
INSERT INTO widgetCustomer (name) VALUES ('Bob');
INSERT INTO widgetCustomer (name) VALUES ('Sally');
INSERT INTO widgetCustomer (name) VALUES ('Fred');
SELECT * FROM widgetCustomer;
CREATE TRIGGER newWidgetSale AFTER INSERT ON widgetSale
FOR EACH ROW
UPDATE widgetCustomer SET last_order_id = NEW.id WHERE id = NEW.customer_id
;
INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (1, 3, 5, 19.95);
INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (2, 2, 3, 14.95);
INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (3, 1, 1, 29.95);
SELECT * FROM widgetSale;
SELECT * FROM widgetCustomer;
-- 02 Preventing automatic updates with a trigger
DROP TABLE IF EXISTS widgetSale;
CREATE TABLE widgetSale ( id SERIAL, item_id BIGINT, customer_id BIGINT, quan INT, price DECIMAL(9,2), reconciled INT );
INSERT INTO widgetSale (item_id, customer_id, quan, price, reconciled) VALUES (1, 3, 5, 19.95, 0);
INSERT INTO widgetSale (item_id, customer_id, quan, price, reconciled) VALUES (2, 2, 3, 14.95, 1);
INSERT INTO widgetSale (item_id, customer_id, quan, price, reconciled) VALUES (3, 1, 1, 29.95, 0);
SELECT * FROM widgetSale;
-- 下面DELIMITER 做用时允许在语句中出现分号，不至于被当作语句结束而执行。
DELIMITER //
CREATE TRIGGER updateWidgetSale BEFORE UPDATE ON widgetSale
FOR EACH ROW
BEGIN
IF ( SELECT reconciled FROM widgetSale WHERE id = NEW.id ) > 0 THEN
-- 这里 signal xxxx 是抛出异常
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: cannot update reconciled row in widgetSale';
END IF;
END
//
DELIMITER ;
START TRANSACTION;
UPDATE widgetSale SET quan = quan + 9 WHERE id = 2;
COMMIT;
SELECT * FROM widgetSale;
-- Alternate error code for MySQL versions prior to 5.5
IF ( SELECT reconciled FROM widgetSale WHERE id = NEW.id ) > 0 THEN
UPDATE `Error: cannot update reconciled row in widgetSale` SET x=1;
END IF;
-- 03 Logging transactions with a trigger
DROP TABLE IF EXISTS widgetSale;
CREATE TABLE widgetSale ( id SERIAL, item_id BIGINT, customer_id BIGINT, quan INT, price DECIMAL(9,2) );
CREATE TABLE widgetLog ( id SERIAL, stamp TIMESTAMP, event VARCHAR(255), username VARCHAR(255),
tablename VARCHAR(255), table_id BIGINT);
DELIMITER //
CREATE TRIGGER stampSale AFTER INSERT ON widgetSale
FOR EACH ROW
BEGIN
UPDATE widgetCustomer SET last_order_id = NEW.id
WHERE widgetCustomer.id = NEW.customer_id;
INSERT INTO widgetLog (event, username, tablename, table_id)
VALUES ('INSERT', 'TRIGGER', 'widgetSale', NEW.id);
END
//
DELIMITER ;
INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (1, 3, 5, 19.95);
INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (2, 2, 3, 14.95);
INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (3, 1, 1, 29.95);
Stored Routines
-- stored functions
DROP FUNCTION IF EXISTS track_len;
CREATE FUNCTION track_len(seconds INT)
RETURNS VARCHAR(16) DETERMINISTIC
RETURN CONCAT_WS(':', seconds DIV 60, LPAD(seconds MOD 60, 2, '0' ));
SELECT title, track_len(duration) FROM track;
-- stored procedures
-- with parameter
DROP PROCEDURE IF EXISTS list_albums;
DELIMITER //
CREATE PROCEDURE list_albums (a VARCHAR(255))
BEGIN
SELECT a.artist AS artist,
a.title AS album,
t.title AS track,
t.track_number AS trackno,
track_len(t.duration) AS length
FROM track AS t
JOIN album AS a
ON a.id = t.album_id
WHERE a.artist LIKE a
ORDER BY artist, album, trackno
;
END //
DELIMITER ;
CALL list_albums('%hendrix%');
-- with output parameter
DROP PROCEDURE IF EXISTS total_duration;
DELIMITER //
CREATE PROCEDURE total_duration (a VARCHAR(255), OUT d VARCHAR(255))
BEGIN
SELECT track_len(SUM(duration)) INTO d
FROM track
WHERE album_id IN (SELECT id FROM album WHERE artist LIKE a)
;
END //
DELIMITER ;
CALL total_duration('%hendrix%', @dur);
SELECT @dur;
