MySQL与MariaDB学习笔记，mariadb学习笔记

和通数据库htsjk.Com2019-09-14 01:05 来源:未知阅读:12843 评论 505 热度2

标签：

MySQL与MariaDB学习笔记，mariadb学习笔记

博客链接: http://codeshold.me/2017/10/learning_mysql_mariadb_notes.html

- 0x01 准备
- 0x02 相关操作
- 0x03 常用函数
- 0x04 权限管理
- 0x04 备份和恢复
- 0x05 导入导出
- 0x06 编程接口

0x01 准备

自己没有安装相关工具，使用了docker mariadb 镜像
相关命令如下
- docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root --name mariadb -d mariadb:latest
- docker exec -it mariadb bash
- docker start mariadb

登陆说明

root@b2659b21f321:/# mysql -uroot -proot  # 默认用户名和密码为root

Welcome to the MariaDB monitor.  Commands end with ; or \g.  # 命令要以分号(;) 或斜线 +g(\g)结尾
Your MariaDB connection id is 10    # 本次连接标识符
Server version: 10.2.7-MariaDB-10.2.7+maria~jessie mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # 可清除之前缓存的输入一半的mysql命令

MariaDB [(none)]>

help 帮助命令, 最有用的 help contents
- help
- help Data Manipulation
- help SHOW DATABASES
prompt 可修改提示符
大小写区别
- MySQL 不区分关键字(如 SHOW)的大小写,所以可以用 show 甚至 sHoW
- 数据库、表和列的名字却可能是区分大小写的,尤其是在那些大小写敏感的操作系统上,如Mac OS X或Linux
用户自定义变量, 以 @ 开头的名字,再用 = 连接上一个值,或者一个表达式,又或者一条返回单个值的 SQL 语句, 即SET @fav_site_total =
show tables from msyql; 显示mysql数据库中的所有表
describe mysql.user;
创建数据库和表
- CREATE SCHEMA swf == CREATE DATABASE swf
- CREATE DATABASE swf2 CHARACTER SET latin1 COLLATE latin1_bin; # MySQL 数据的存储方式是二进制拉丁字符
- 表名可以是 SQL 保留字以外的任何东西。事实上,用保留字也可以,但需要加上引号以作区分
- AUTO_INCREMENT 选项告诉 MySQL 此列的值是自增的
- TEXT类型,即长度可变,但最多65 535字节
- 如果你能确定某列内容的长度,那就用 CHAR,否则用 VARCHAR
- UNIQUE, 会截短显示为 UNI
- ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin, 首先是表类型,或者说是该表使用的存储引擎的类型; 剩下两个是表的默认字符集(latin1)和默认校对方式(latin1_bin)
- 校对方式,则可选 latin5_turkish_ci,它会根据土耳其语的字母表来排序
- BLOB 二进制大对象
备份数据库 mysqldump --user='russell' -p rookery birds > /tmp/birds.sql
导入数据库 mysql --user='russell' -p rookery < rookery-ch2-end.sql
CREATE TABLE test.birds_new LIKE birds; 仅复制表结构
CREATE TABLE birds_new_alternative SELECT * FROM rookery.birds; 复制表结构和表数据

0x02 相关操作

ALTER TABLE birds ADD COLUMN test INT
ALTER TABLE birds_new ADD COLUMN wing_id CHAR(2);
ALTER TABLE birds_new ADD COLUMN wing_id CHAR(2) AFTER family_id; # 在特定的列之后加入一列
ALTER TABLE birds_new DROP COLUMN wing_id;
ALTER TABLE birds_new ADD COLUMN bill_id CHAR(2) AFTER body_id, ADD COLUMN endangered BIT DEFAULT b'1' AFTER bill_id;可同时添加多列
ALTER TABLE birds_new CHANGE COLUMN common_name common_name VARCHAR(255); 在使用CHANGE COLUMN时,就算只想修改该列的某一方面, 也需要完整地声明整个新列。

MODIFY COLUMN子句的语法只要求输入一次列名, MODIFY COLUMN是不能用来改列名的

ALTER TABLE birds_new
MODIFY COLUMN endangered
ENUM('Extinct',
     'Extinct in Wild',
     'Threatened - Critically Endangered',
     'Threatened - Endangered',
     'Threatened - Vulnerable',
     'Lower Risk - Conservation Dependent',
     'Lower Risk - Near Threatened',
     'Lower Risk - Least Concern')
AFTER family_id;

SHOW COLUMNS FROM birds_new LIKE 'endangered' \G 结果仅显示 endangered 列的设定
- SHOW FULL COLUMNS显示更全的信息
ALTER TABLE birds_new ALTER conservation_status_id DROP DEFAULT; 修改默认值的设定
SELECT auto_increment FROM information_schema.tables WHERE table_name = 'birds';
ALTER TABLE birds AUTO_INCREMENT = 10;
- 这个命令虽说是更改 bird_orders 表,但实际修改的是服务器上保存 AUTO_INCREMENT 值的表 (information_schema数据库中的)
重命名一个表 RENAME TABLE table1_altered TO table1;
将一个表移动到另一个数据库中 RENAME TABLE rookery.birds TO rookery.birds_old, test.birds_new TO rookery.birds;
SHOW TABLES FROM mysql LIKE 'plugin'; 或者 SHOW TABLES IN rookery LIKE 'birds%';
SHOW INDEX FROM birdwatchers.humans \G
ALTER TABLE birdwatchers.humans ADD INDEX human_names (name_last, name_first); 添加索引
EXPLAIN语句返回的信息中， possible_keys域表示可能用到的键， key 域表示确实用到的键
ALTER TABLE conservation_status DROP PRIMARY KEY, CHANGE status_id conservation_status_id INT PRIMARY KEY AUTO_INCREMENT;
SHOW WARNINGS \G
DELETE FROM bird_families WHERE family_id = 101;
INSERT INTO ...... VALUES ......
INSERT IGNORE INTO ...... 指示服务器忽略所有错误,并插入那些没有产生错误的行, 接下来可以通过SHOW WARNINGS 显示那些有问题的行
UPDATE ....... SET ...... WHERE ......
REPLACE INTO ...... VALUES ...... 或 REPLACE INTO ...... SELECT ......
- REPLACE 语句可用于替换含有重复键的整行数据,或新增原表中所没有的数据。
- 整行替换
INSERT LOW_PRIORITY INTO bird_sightings
- InnoDB 引擎的表不支持 LOW_PRIORITY 和 HIGH_ PRIORITY,因为 InnoDB 只锁定相关的行而不锁定整个表,所以这两个选项对它没有意义。
LIMIT 子句带了两个值:一个是开始位置,一个是行数
如果列的别名就是一个单词,那么没有必要加引号。否则,要加。另外,保留字(如 Order)也要加引号
在 ORDER BY 中不能使用列的别名,但表的别名则可以。
REGEXP正则匹配 WHERE common_name REGEXP 'Great|Least' AND common_name NOT REGEXP 'Hawk-Owl'
别名关键字 AS 可以可无！
UPDATE humans SET formal_title = SUBSTRING(formal_title, 1, 2);

在MySQL使用UPDATE的多表语法时,不能带有ORDER BY或LIMIT——但在 UPDATE 单表时就可以

UPDATE prize_winners, humans
     SET winner_date = CURDATE()
     WHERE winner_date IS NULL
     AND country_id = 'uk'
     AND prize_winners.human_id = humans.human_id
     ORDER BY RAND()  # 随机
     LIMIT 2;  # 只更新两行

INSERT...ON DUPLICATE KEY UPDATE...

 INSERT INTO humans
     (formal_title, name_first, name_last, email_address, better_birders_site)
     VALUES('Mr','Barry','Pilson', 'barry@gomail.com', 1),
           ('Ms','Lexi','Hollar', 'alexandra@mysqlresources.com', 1),
           ('Mr','Ricky','Adams', 'ricky@gomail.com', 1)
     ON DUPLICATE KEY
     UPDATE better_birders_site = 2;  # 在重复的行上设置那个字段为2

多表删除操纵, DELETE FROM table[, table] USING table[, . . . ] [WHERE condition];

 DELETE FROM humans, prize_winners
 USING humans JOIN prize_winners
 WHERE name_first = 'Elena'
 AND name_last = 'Bokova'
 AND email_address LIKE '%yahoo.com'
 AND humans.human_id = prize_winners.human_id;

连接时在左表(humans)中找不到对应行,右表(prize_winners)的数据也会被删除

DELETE FROM prize_winners
USING humans RIGHT JOIN prize_winners
ON humans.human_id = prize_winners.human_id
WHERE humans.human_id IS NULL;

因为使用 UNION 时,MySQL 只会取第一个 SELECT 的域名作为最终结果集的标题,而之后的 SELECT 的域名都会被忽略
编写SQL语句时，连接条件与筛选条件最好分开！
使用 USING 时,连接条件中的列必须是在两表中都存在的

不要把 USING...JOIN 和 JOIN...USING 搞混了

SELECT book_id, title, status_name
     FROM books
     JOIN status_names ON(status = status_id);
SELECT book_id, title, status_name
     FROM books
     JOIN status_names USING(status_id);

UPDATE birds
LEFT JOIN conservation_status USING(conservation_status_id)
JOIN bird_families USING(family_id)
SET birds.conservation_status_id = 9
WHERE bird_families.scientific_name = 'Ardeidae'
AND conservation_status.conservation_status_id IS NULL;

动态列
子查询

0x03 常用函数

文本作为参数时,需要使用引号

列作为参数时,不要用引号——否则列名会被当成文本。如果列名是保留字或含有可能引起问题的字符,可用反引号标示列名。

如果字符串函数返回的值过长(即返回了太多的字符串),超出了系统限制(max_allowed_packet选项),MySQL 就会返回 NULL。

有些参数用于指定字符串中字符的位置。字符串第一个字符的位置是1,不是0。当需要从后往前数时(有些函数允许这么做),最后一个字符的位置是 -1。

有些参数用于表示字符串长度。如果用到小数,MySQL就会将其四舍五入为最接近的整数。

0x04 权限管理

用户账号: 用户名与主机的组合”

角色

CREATE ROLE 'admin_import_role';
GRANT FILE ON *.* TO 'admin_import_role'@localhost;
启用角色 SET ROLE 'admin_import_role'; … , SET ROLE NONE;

0x04 备份和恢复

0x05 导入导出

导入 LOAD DATA INFILE


-- 统一导入
LOAD DATA INFILE '/tmp/Clements-Checklist-6.9-final.csv'
INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  -- 指定域用的是双引号
IGNORE 1 LINES;  -- 忽略指定的行数

-- 导入对应的域
LOAD DATA INFILE '/tmp/Clements-Checklist-6.9-final.csv' INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
     (id, change_type, @niente, @niente,
     @niente, bird_order, family, @niente,
 @niente, @niente, @niente, @niente);  -- 那些不想要的域则被导入了临时变量 @niente

-- 设置列（family）
LOAD DATA INFILE '/tmp/Clements-Checklist-6.9-final.csv' INTO TABLE rookery.clements_list_import
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
     (id, change_type, @niente, @niente,
     @niente, bird_order, @family, @niente,
     @niente, @niente, @niente, @niente, @niente)
SET family = SUBSTRING(@family, 1, LOCATE(' (', @family) );

-- 比较全的 
LOAD DATA INFILE '/tmp/birdwatcher-prospects.csv'
INTO TABLE birdwatchers.birdwatcher_prospects_import 
FIELDS TERMINATED BY '|' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '[' TERMINATED BY ']\r\n'  -- STARTING BY 行以左中括号开头; TERMINATED BY指定行由右中括号、回车和换行符结尾
IGNORE 1 LINES
(prospect_name, prospect_email, prospect_country);

忽略错误，LOAD DATA INFILE ... IGNORE INTO TABLE ...
替换，LOAD DATA INFILE ... REPLACE INTO TABLE ...

使用mysqlimport, 所有的选项与LOAD DATA INFILE的一样,只是变成了小写,并以两个横杠开头, 但是没有--lines-starting-by选项,所以支持不够

mysqlimport –user='marie_dyer' --password='sevenangels' \
--replace --low-priority --ignore-lines='1' \
--fields-enclosed-by='"' --fields-terminated-by='|' --fields-escaped-by='\\' \ --lines-terminated-by=']\r\n' \
--columns='prospect_name, prospect_email, prospect_country' \
birdwatchers '/tmp/birdwatcher_prospects_import.csv'

没有FILE权限也能导入数据 P253

0x06 编程接口

P258