MySQL 学习笔记
默认分类
2020-01-15
306
0
使用正则表达式
-- ^ 开始 , . 任意字符
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