mysql

./security.md
./mysql_grant.md

官方文档

启动

../database/start启动与停止.md

docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -ti mysql

mariadb

初始化数据库
sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

tutorial 命令大全

CREATE

表的名称绝对不能有引号
最后一行千万不要有逗号

示例

CREATE DATABASE menagerie character set utf8mb4;
CREATE TABLE <tablename> (column, column);
create table user ( 
    id SMALLINT unsigned not null auto_increment primary key,
    name VARCHAR(50) comment '用户名',  -- comment来写注释
    )
CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    `title` varchar(31) COLLATE utf8mb4_unicode_ci NOT NULL
)

参考
- DEFAULT: 默认值
- NOT NULL: 不能为空
- COLLATE: 编码

column定义

顺序不能错

col_name data_type [NOT NULL|NULL] [DEFAULT {literal|expr}] [AUTO_INCREMENT] [[PRIMARY] KEY]
id int not null auto_increment primary key

Drop

    drop table <table>; --表整个删除
    delete from table;  -- 保留表的结构

DESCRIBE

DESCRIBE <table>;

INSERT 官方参考

INSERT INTO pet VALUES ('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL);
INSERT INTO pet VALUES ('Puffball', 'Diane'), ('Puffball', 'Diane')  # 一次新插入多个数据

* insert最后一个数据后面不能加逗号

mysql --help
mysql -h host -u user -p[passwoed] [<databasename>]

SELECT

A条件 AND B条件 OR C条件, AND的优先级比较高，但是为了不弄混，还是加括号()比较好
DISTINCT是针对结果的。结果数据一致就算是一致
ORDER BY birth [DESC]; 对某个列进行排序
- ORDER BY name 按照名字排序（不分大小写）
- ORDER BY BINARY name 按照名字排序（区分大小写）
- ORDER BY name, birth DESC; 按照几个字段排序
NULL 的查询
- SELECT name FROM pet WHERE death is null;

正则查询, 默认不区分大小写

SELECT name FROM pet WHERE name like 'b%': b开头
SELECT name FROM pet WHERE name like '_____': 5个字母
SELECT name FROM pet WHERE name REGEXP/RLIKE '^b': b开头
'%fy': fy结尾
'%w%': 包含w
'_': 一个任意字符
区分大小写: SELECT name FROM pet WHERE name like binary '%w';
.: 匹配任意单个字符串
[abc]: abc中任意一个
*: 任意数量
{5}: 指定5个
...to be continued

COUNT:

COUNT(*) 求和。 COUNT(field) 会排除field为null的数据
如果 SET sql_mode = 'ONLY_FULL_GROUP_BY' 那COUNT后面必须有group by, 如果 SET sql_mode = '' 那COUNT后面可以没有group by

SELECT [DISTINCT] field FROM table WHERE conditions_to_satisfy ORDER BY column [DESC];

用户自定义变量:

select @min_price:=MIN(price) from shop;
select @min_price;
select * FROM shop WHERE price=@min_price;

使用外键

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

SHOW

SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE pet;
SHOW INDEX FROM pet;
SHOW CREATE TABLE pet\G; # \G 可以让代码变整洁，具体意思以后再看
show full columns from group_group; 查看所有的信息

UPDATE 更新数据

UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

USE

batch

mysql < batch-file > outfile

tutorial 函数大全

普通函数
- CURDATE: 当前日期
- CURRENT_DATE: 当前日期
- CURRENT_TIME: 当前时间（不带日期）
- DATABASE(): 当前数据库
- DATE_ADD(CURDATE(), INTERVAL 1 MONTH): 返回下个月的今天。1月29, 30, 31的结果都一样
- DAYOFMONTH: 返回日期
- LAST_INSERT_ID: 最后一次插入的数据
- MAX(column): 最大值
- MOD(MONTH(CURDATE()), 12) + 1: MOD去模，用来返回下个月的月份
- MONTH: 返回月份
- NOW(): 当前时间
- TIMESTAMPDIFF(YEAR, birth, CURDATE()): 年份。直接比较时间戳去尾，如果跨过了闰年的2月29号，就要和366天比，否则和365天比
- USER(): 当前用户
- VERSION(): 查看版本
不常用函数
- BIT_OR:
数学函数
- PI(): 3.141592
- SIN: sin三角函数

Creating and Using a Database

Retreving information from a Table

Using More Than one Table

Installing and Upgrading MySQL

Postinstallation Setup and Testing

Securing the Initial MySQL Account

mysql> ALTER USER 'root@localhost' IDENTIFIED BY 'new_password';

Tutorial

Creating and Using a Database

Loading Data into a Table 见SQL Statements - Data Manipulation Statements - LOAD DATA Statement

MySQL Programs::Client Programs::mysql Client Commands

mysql> help
\c 取消当前的输入

SQL Statements

Data Definition Statements

CREATE TABLE Statements

[ ] FOREIGN KEY Constraints

示例

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
)

reference的字段，必须需要index
如果外键的字段有重复。只要里面一条数据被删除了，那么整个表里面的关联数据都会被删除。

[ ] CHECK Constraints

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
CREATE TABLE t1
(
  CHECK (c1 <> c2),
  c1 INT CHECK (c1 > 10),
  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);
CREATE TABLE people (
  id int,
  birth date,
  death date,
  CONSTRAINT mycheck CHECK(death > birth)
);

Data Manipulation Statements

DELETE

DELETE FROM tbl_name 
    WHERE where_condition
    ORDER BY ...
    LIMIT row_count

LOAD DATA Statement
- 例子
  
  LOAD DATA INFILE 'file_name' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY """ IGNORE number {LINES | ROWS}
- \N代表了空置NULL
- example:
  
  LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';

SELECT Statement

basic 基础
SELECT ... INTO STATEMENT 导出数据

JOIN

LEFT JOIN

SELECT * from pet LEFT JOIN event ON pet.name = event.name;
找出每个动物。存在事件就插进去。最多可能pet的行数 x event的行数

RIGHT JOIN

SELECT * FROM pet RIGHT JOIN event ON pet.name = event.name;
找出所有的事件，如果有对应的动物，就插进去。如果对应多个，就插入多个。所以最多的行数等于pet的行数 x event的行数

INNER JOIN

SELECT * FROM pet INNER JOIN event ON pet.name = event.name;
找出所有的匹配，然后只看里面pet.name == event.name. 如果没匹配上，就不显示。所以最多显示的数量是 pet的数量 × event的数量

案例

SELECT pet.name TIMESTAMPDIFF(YEAR, birth, date) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';

INNER JOIN: 把两个表格里面合并起来，只有on的条件满足了才会一起出现，否则就不显示

UNION Clause

Security 安全机制

./access_control.md

Using Encrypted Connections

Security Components and Plugins

FIPS Support

数据类型 Data Types

Data Type Overview

数字类型

* 基础: INT(2)
* 可用参数: `UNSIGNED, ZEROFILL, AUTO_INCREMENT`
* `ALTER TABLE tb1 AUTO_INCREMENT = 100;`  # 不是自动加1,而是自动加100
* YEAR(4)

时间和日期

TIMESTAMP 时间戳类型插入的时候，会把时间变成时间戳保存，取出的时候，会自动根据链接的时区变成datetime

CREATE TABLE test7 ( n int DEFAULT NULL, updateat timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )

字符串类型

枚举类 ENUM TYPE

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM("x-small", "small", "medium", "large", "x-largs")
)
ALTER TABLE shirts MODIFY size ENUM("small", "big");
ERROR 1265 (01000): Data truncated for column 'size' at row 2;  /* 如果enum不存在就报错了 */

实际上数据库用1个字节来保存这个数据，所以速度会快很多
注意排序，会根据ENUM的顺序来排序

Spatial Data Types空间类型

创建空间类型列

    CREATE TABLE geom (g GEOMETRY);

获取空间数据

    SELECT ST_AsText(g) from geom;
    > | POINT(1 1) |
    SELECT ST_AsBinary(g) from geom;
    > | 010010000 |

JSON Data Type

插入数据

insert into testjson values (JSON_ARRAY(1,2,3));

过滤

select *, JSON_EXTRACT(json, '$.key1') b from testjson where JSON_EXTRACT(json, '$.key1') is not null;

Functions and Operators

通用的函数和操作

Date and time

时间操作相关

CURTIME

DATE 从datetime里面或者他的日期

select DATE('2003-12-31 01:02:03');
-> '2003-12-31'

DATE_ADD

过滤

基础: WHERE species = 'dog'
IN: SELECT * FROM pet WHERE species IN ('dog', 'cat');

backup and restore 备份与恢复

outfile

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

mysqldump

示例代码

mysqldump -u root -p test --extended-insert=FALSE > test.sql  # windows下不正确，因为windows用了UTF-16
mysqldump -u root -p test --extended-insert=FALSE --result-file=test.sql
## 我的习惯
mysqldump   \
    -u <username> \
    --set-gtid-purged=OFF \
    --column-statistics=0 \
    --result-file result.sql \
    --lock-tables=False \
    --skip-add-locks \
    --net-buffer-length=32k \
    <database> <table>

选项
- --add-locks, --skip-add-locks: dump出来的sql语句, 是否有LOCK TABLES <table> WRITE;
- --result-file: 保存路径
- --extended-insert: 是否把所有数据的insert写成一句，默认True
- --complete-insert: insert语句里面是否带上columns的参数，默认False
- --net-buffer-length: 32K比较不错.既不会太慢,也不会卡顿
Performance and Scalability Considerations 性能和企业数据要考虑

It's recommended to use mysqlbackup command of MySQL Enterprise Backup product,
因为mysqldump要考虑索引，io，处理大型数据会很慢

恢复

mysql -h localhost -u root -p < ./test.sql  # 处理dump出来的
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl  # 处理outfile的结果
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

Optimization 性能优化

Optimization and Indexes 索引

to be continued

SQL Statement Syntax

Data Definition Statements

ALTER TABLE Syntax ALTER TABLE score smallint unsigned not null; this will set the default value 0

案例

alter table <table> add <column> <data-type> [after <column>]
wer123
ALTER TABLE t1 RENAME t2;
ALTER TABLE t2 MODIFY a TYNYINT NOT NULL, CHANGE b c CHAR(20);
ALTER TABLE t1 RENAME COLUMN hometown_match TO hometown_match2;  /* 重命名 */
ALTER TABLE t2 ADD d TIMESTAMP;
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
ALTER TABLE t2 DROP COLUMN c;  删除列
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c);

Performance and Space Requirements ALTER TABLE use one the the following algorithms (COPY, INPLACE(before 8.0), INSTANT(new in 8.0.12 default)) ALTER 的时候，会复制原先的数据表，此时数据库处于只读状态，不能改写或插入(除非是把一个table移动到另外一个文件夹的RENAME TO操作)

CREATE INDEX Syntax

不能够加双引号，加了会导致报错

CREATE INDEX t1_hometown_match_28b57695 ON t1 (hometown_match);
SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME="t1";
DROP INDEX index_name on tbl_name

有待整理

配置

utf8与utf8mb4的问题
1. ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
2. ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3. ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
4. SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
utf8mb4_unicode_ci和utf8mb4_general_ci 尽量用前面的

`id` varchar(31) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
alter table group_groupapply convert to character set utf8mb4 collate utf8mb4_unicode_ci;

性能问题

参考链接
1. mysql使用B+树，是因为磁盘上一次读取的信息很多，只存一个节点有点亏。多以干脆多存几个，减少层级数
2. mysql的innodb是直接在叶子节点存了数据(聚集索引)，如果对其他的key做索引，会有回表再次查询的问题（为了节约空间）。所以每个表必须要有主键。但是mysql的叶子节点数据也有物理地址啊。给其他的字段创建索引时为什么不直接用物理地址呢？这个物理地址会变化吗
  - 主键不要太长，因为其他的索引数据就太大了
3. myisam则是保存了物理地址。索引查到数据后直接能读取数据

administrator

查看占用空间比较大的表

SELECT table_schema, table_name, ROUND((data_length+index_length)/POWER(1024,2),2)
AS tablesize_mb
FROM information_schema.tables
ORDER BY tablesize_mb
DESC LIMIT 20;

Name		Name	Last commit message	Last commit date
parent directory ..
mysqldump		mysqldump
README.md		README.md
access_control.md		access_control.md
mysql_grant.md		mysql_grant.md
security.md		security.md

FilesExpand file tree

mysql

Directory actions

More options

Directory actions

More options

Latest commit

History

mysql

Folders and files

parent directory

mysql

启动

mariadb

tutorial 命令大全

CREATE

column定义

Drop

DESCRIBE

SELECT

正则查询, 默认不区分大小写

COUNT:

用户自定义变量:

使用外键

SHOW

USE

batch

tutorial 函数大全

Creating and Using a Database

Retreving information from a Table

Installing and Upgrading MySQL

Postinstallation Setup and Testing

Tutorial

Creating and Using a Database

MySQL Programs::Client Programs::mysql Client Commands

SQL Statements

Data Definition Statements

CREATE TABLE Statements

[ ] FOREIGN KEY Constraints

[ ] CHECK Constraints

Data Manipulation Statements

SELECT Statement

Using Encrypted Connections

Security Components and Plugins

FIPS Support

过滤

outfile

恢复

to be continued

SQL Statement Syntax

Data Definition Statements

有待整理

配置

性能问题

administrator