This website requires JavaScript.

MySQL 使用记录

记录了我日常使用 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\'';

数据库操作

创建数据库

CREATE DATABASE `bihell` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */

查看数据库大小

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;

导出数据库

导出整个数据库

mysqldump -u 用户名 -p –default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1)  

导出一个数据库结构

# -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table  
mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql  

导入数据库

使用 source 命令导入

mysql>source wcnc_db.sql  

使用 mysqldump 命令导入

mysqldump -u username -p dbname < filename.sql

使用 mysql 命令导入

mysql -u username -p -D dbname < filename.sql  

表操作

创建表

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表的索引 show columns from MyClass;

导出表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名  

数据类型

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

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

杂项

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;

配置

获取 MySQL 配置文件路径

要找到配置文件,我们首先先要定位mysqld文件的位置

$ which mysqld
/usr/sbin/mysqld

执行以下命令获得配置文件的读取路径

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

调整sql_mode

执行select @@sql_mode获取当前sql_mode,然后在配置文件中添加设置:

vim /usr/local/etc/my.cnf

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

远程访问

修改my.cnf 配置文件

将 bind-address 改为外网 IP 或者 0.0.0.0

vim /usr/local/etc/my.cnf
bind-address = 0.0.0.0

设置权限

# 允许用户远程访问
CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

通过SSH 隧道连接MySQL

在本机ssh连接远程主机,并且映射端口。

ssh root@www.bihell.com -L 3307:127.0.0.1:3306 -N

通过客户端直接连接映射好的本地端口

比如用DataGrip连接27.0.0.1:3307

其他

--显示后两行

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

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

删除Validate Password插件

uninstall plugin validate_password;

一些代码段

mysql-snippets

0条评论
avatar