使用正则表达式

-- ^ 开始 ， . 任意字符
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),
    CONSTRAINT detectives_pk PRIMARY KEY(id)
);

修改表

--删除字段
ALTER TABLE cases DROP criminal;
ALTER Table cases ADD id INT NOT NULL;
--添加字段
ALTER TABLE cases ADD criminal varchar(100);
ALTER TABLE cases ADD PRIMARY KEY (id);
--修改字段
ALTER TABLE detectives CHANGE certificationDate certification_date date;

插入数据

INSERT INTO TEST ( id, a, b ) VALUES ( 1, 'one', 'two' );

INSERT INTO TEST VALUES ( 3, 'three', 'four' );

insert into tablename
select * from sourcetablename where target_id between 687 and  840

INSERT INTO items(id,name,cost,seller_id,bids) values
('104','beff chops','799','1','0'),
('106','beff chops','799','1','0'),
('105','beff chops','799','1','0')

Schema查看

DESCRIBE test; --查看表结构 EXPLAIN test; SHOW TABLE STATUS; -- 显示数据库中所有表的状态 SHOW TABLE STATUS like ‘test'; -- 显示某个表的状态 SHOW CREATE TABLE test; --显示创建test表的sql语句 SHOW INDEXES FROM test; -- 显示test表的索引

数据类型

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;

函数

时间

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);

时区 所有时区清单 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')

杂项

length() --计算长度 bytes SELECT LAST_INSERT_ID() ; – 显示最后插入的id，注意这个是全局的，所以要看的话需要在插入后面调用 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 --整除

事务

-- 使用事务会增加性能
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;

其他

--显示后两行

select * from cases where start_date >'2005-01-01' limit 2 offset 2;

查看数据库大小

SELECT table_schema     "DB Name",
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables
GROUP  BY table_schema;

MySQL执行Select语句将结果导出到文件

mysql -h10.10.10.10 -uroot -p123456 -P3306 -Ne "select * from table_name into outfile '/tmp/table_name_result.txt' "
mysql -h10.10.10.10 -uroot -p123456 -P3306 -N < run.sql > /tmp/table_name_result.txt

参考

Install MySQL on macOS Sierra