Mariadb学习笔记3

备份还原数据库

在改表之前，尤其是改含有数据的表之前，应该做好数据备份。无论你的改动有多小，

都应该这么做。

备份rookery下的birds数据库

[root@localhost ~]# mysqldump --user='jingpan' -p rookery birds> /tmp/birds.sql

备份rookery数据库

mysqldump --user='jingpan' -p rookery >/tmp/rookery.sql

恢复数据库

mysql --user='jingpan' -p rookery </root/rookery-ch4-end.sql

必修的改表技能

给bird_families表增加一列，使其能与bird_orders表关联

ALTER TABLE bird_families ADD COLUMN order_idINT; 

新增加的列叫order_id,与bird_order中的一样。

给bird_families表加了一个名为order_id的，能容纳整数的列，不过没有让它像bird_orders的order_id那样

自增。我们不需要它自增，因为我们只会在bird_orders中插入新的order_id,而在bird_families中参考

bird_orders.

把birds表复制到test数据库下,只复制表结构

MariaDB [rookery]> CREATE TABLE test.birds_new LIKEbirds;

插入数据，使用INSERT INTO...SELECT....子句

 INSERT INTO birds_new

SELECT * FROM rookery.birds; 

在birds_new表中增加wind_id列

ALTER TABLE birds_new

ADD COLUMN wing_id CHAR(2);

注意到wing_id列，它被加到了末尾。MySQL和MariaDB不关心列的排位。但是

 

我们很关注。尤其是面对那些拥有大量列的表时。所以，我们重新增加wing_id并且

这次要告诉MySQL把它放在family_id的后面。

删除birds_new下的wing_id列

 ALTER TABLEbirds_new 

 DROP COLUMN wing_id;

在family_id列后增加wind_id列

ALTER TABLE birds_new

ADD COLUMN wing_id CHAR(2) AFTER family_id;

若想将新列加在最前面，要用FIRST，而不是AFTER。FIRST后不需要带列名。

增加新列并修改common_name的类型长度

ALTER TABLE birds_new

ADD COLUMN body_id CHAR(2) AFTER wing_id,

ADD COLUMN bill_id CHAR(2) AFTER body_id,

ADD COLUMN endangered BIT DEFAULT b'1' AFTER bill_id,

 CHANGE COLUMN common_name common_nameVARCHAR(255);

BIT 数据类型。这个类型只占一位，状态有两种：1 代表有设值，0代表没设值。我们会用它来表示该物种是否濒危。注意这里我们用DEFAULT指定了该列的默认值。还有，为了给位类型设值，我们需要将值用引号包围，并在前面加上字母b

 

改变endangered列的值

UPDATE birds_new SET endangered = 0

WHERE bird_id IN (1,2,4,5,6);

 

根据endangered来用SELECT 语句获取数据

SELECT bird_id,scientific_name,common_name

FROM birds_new

WHERE endangered \G

 

果想选取没设置endangered 的行

 SELECT bird_id,scientific_name,common_nameFROM birds_new WHERE endangered = 0 \G

 SELECT bird_id,scientific_name,common_nameFROM birds_new WHERE  NOT endangered \G

为endangered 列增加枚举值，增加濒危级别

 ALTER TABLE birds_new

 MODIFY COLUMN endangered

 ENUM('Extinct',

 'Extinct in Wild',

 'Threatened - Critically Endangered',

'Threatened - Endangered',

 'Threatened - Vulenerable',

 'Lower Risk - Conservation Dependent',

 'Lower Risk - Near Threatened',

 'Lower Risk - Least Concern')

 AFTER family_id;

数据类型ENUM ,枚举出可接受的值

显示endangered列的设定

SHOW COLUMNS FROM birds_new LIKE 'endangered' \G;

将所有鸟都 设为Lower Risk - LeastConcern

UPDATE birds_new SET endangered = 8;

 

动态列

创建surveys表
CREATE TABLEsurveys
 (survey_id INT AUTO_INCREMENT KEY,
 survey_name VARCHAR(255));

用于存放调查问卷清单

 

创建survey_question 表

CREATE  TABLE survey_questions

(question_id INT AUTO_INCREMENT KEY,

survey_id INT,

question VARCHAR(255),

choices BLOB); 

用于存放问卷里的问题。因为我们只需要受访者投票，所以choices列会包含各种选项。这列使用一个非常通用的类型--------------BLOB，但实际上它将用于动态列。所先的数据类型必须能容纳动态列实际的数据。而BLOG就是一个不错的选择。

 

 

创建survey_answers表

CREATE TABLE survey_answers

(answer_id INT AUTO_INCREMENT KEY,

human_id INT, question_id INT,

date_answered DATETIME,

answer VARCHAR(255));

 表用于存放问题的回答。这次我们的动态列使用VARCHAR类型。最后survey_
answers 会以question_id 跟survey_questions 关联，survey_questions会以survey_id 跟
surveys 关联。

 

插入两个问卷，一个问人们喜欢到哪里观鸟，另一个则问人喜欢哪种鸟。COLUMN_CREATE()用于创建选项的枚举：每个选项都有一个键和一个值。

 

INSERT INTO surveys (survey_name)
VALUES("Favorite Birding Location");

 

INSERT INTO survey_questions

(survey_id,question,choices)

VALUES(LAST_INSERT_ID(),

"What's your favorite setting for bird-watching?",

COLUMN_CREATE('1','forest','2','shore','3','backyard'));

 

 INSERT INTO surveys (survey_name)
VALUES("Preferred Birds");

 

INSERT INTO survey_questions
(survey_id,question,choices)
 VALUES(LAST_INSERT_ID(),
"Which type of birds do you like best?",
 COLUMN_CREATE('1','perching','2','shore','3','fowl','4','rapture'));

 

COLUMN_CREATE() 用于创建选项的枚举：每个选
项都有一个键和一个值。例如，选项1 是forest，选项2 是shore，选项3 是backyard。而
从MariaDB v10.0.1 开始，键还可以用字符，而不只是数字。

从动态列获取数据

 SELECT COLUMN_GET(choices,3 AS CHAR)

AS 'LOCATION'

FROM survey_questions

WHERE survey_id = 0;

 

这个命令会返回第三个选项。我们使用了COLUMN_GET,其中第一个参数填动态列的列名，第二个参数填该动态列的键，这样就能获取其对应的值了。同时，我们还包含了一个AS来将返回的数据转换成我们指定的类型(即CHAR)。

 

录入 会员的答案

INSERT INTO survey_answers
(human_id, question_id, date_answered, answer)
VALUES
(29, 1, NOW(), 2),
(29, 2, NOW(), 2),
(35, 1, NOW(), 1),
(35, 2, NOW(), 1),
(26, 1, NOW(), 2),
(26, 2, NOW(), 1),
(27, 1, NOW(), 2),
(27, 2, NOW(), 4),
(16, 1, NOW(), 3),
(3, 1, NOW(), 1),
(3, 2, NOW(), 1);

 

给第一个问题点票

SELECT IFNULL(COLUMN_GET(choices, answer AS CHAR),'total')
AS 'Birding Site', COUNT(*) AS 'Votes'
FROM survey_answers
JOIN survey_questions USING(question_id)
WHERE survey_id = 1
AND question_id = 1
GROUP BY answer WITH ROLLUP;

 

选修的改表技能

 

创建conservation_status表

 CREATE TABLE conservation_status
(status_id INT AUTO_INCREMENT PRIMARY KEY,
 conservation_category CHAR(10),
 conservation_state CHAR(24));

 

插入数据

INSERT INTO rookery.conservation_status
(conservation_category, conservation_state)
VALUES('Extinct','Extinct'),
('Extinct','Extinct in Wild'),
('Threatened','Critically Endangered'),
('Threatened','Endangered'),
('Threatened','Vulnerable'),
('Lower Risk','Conservation Dependent'),
('Lower Risk','Near Threatened'),
('Lower Risk','Least Concern');

 

修改列名和默认值

ALTER TABLE birds_new

CHANGE COLUMN endangered conservation_status_id INT DEFAULT 8;

 

设置默认值为7

ALTER TABLE birds_new

ALTER conservation_status_id SET DEFAULT 7;

 

MariaDB [test]> SHOW COLUMNS FROM birds_new LIKE'conservation_status_id' \G
*************************** 1. row***************************
  Field: conservation_status_id
   Type: int(11)
   Null: YES
    Key:
Default: 7
  Extra:

ALTER TABLE birds_new
ALTER conservation_status_id DROP DEFAULT;

关键字DROP 只会更改列的默认值。

 

 设置AUTO_INCREMENT的值

 数据库中很多主表的主键都会用到AUTO_INCREMENT选项。它会在information_schema库的tables表中创建一个AUTO_INCREMENT变量。每次建表时,MySQL都会在information_schema库的tables表中加入一行。而tables中就有一列叫作auto_increment。新增行时所需的自增值就从那里获取。

 

SELECT table_name,auto_increment

FROM  information_schema.tables

WHERE table_name = 'birds';

 

改变auto_increment值

 

 改表和建表的另一种方法

 

创建一个birds_new表

CREATE TABLE birds_new LIKE birds;

 

除了表名外，其它都一样。

只复制了表结构，并没有复制数据

 

此方法适用于对表进行大改的情况。

 

之前说过，它们除了表名，其他方面都一样，但这并不完全正
确。还可能有一点不同的是，如果新表有一列使用AUTO_INCREMENT 作为默认值，那么它
自增值将是从0 开始。你需要根据birds 表AUTO_INCREMENT 的当前值来设定新表AUTO_
INCREMENT 的初始值，以使新表的新行能获取正确的标识号。

 

MariaDB [rookery]> SHOW CREATE TABLE birds \G
*************************** 1. row***************************
      Table: birds
Create Table: CREATE TABLE `birds` (
  `bird_id` int(11) NOT NULL AUTO_INCREMENT,
  `scientific_name` varchar(255) COLLATElatin1_bin DEFAULT NULL,
  `common_name` varchar(50) COLLATE latin1_binDEFAULT NULL,
  `family_id` int(11) DEFAULT NULL,
  `description` text COLLATE latin1_bin,
  PRIMARY KEY (`bird_id`),
  UNIQUE KEY `scientific_name`(`scientific_name`)
) ENGINE=InnoDB AUTO_INCREMENT=20DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

 

MariaDB [rookery]> SHOW CREATE TABLE birds_new \G
*************************** 1. row***************************
      Table: birds_new
Create Table: CREATE TABLE `birds_new`(
  `bird_id` int(11) NOT NULL AUTO_INCREMENT,
  `scientific_name` varchar(255) COLLATElatin1_bin DEFAULT NULL,
  `common_name` varchar(50) COLLATE latin1_binDEFAULT NULL,
  `family_id` int(11) DEFAULT NULL,
  `description` text COLLATE latin1_bin,
  PRIMARY KEY (`bird_id`),
  UNIQUE KEY `scientific_name`(`scientific_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

 

 

ALTER TABLE birds_new  AUTO_INCREMENT =20;

 

MariaDB [rookery]> SHOW CREATE TABLE birds_new\G                                                                           
*************************** 1. row***************************
      Table: birds_new
Create Table: CREATE TABLE `birds_new`(
  `bird_id` int(11) NOT NULL AUTO_INCREMENT,
  `scientific_name` varchar(255) COLLATElatin1_bin DEFAULT NULL,
  `common_name` varchar(50) COLLATE latin1_binDEFAULT NULL,
  `family_id` int(11) DEFAULT NULL,
  `description` text COLLATE latin1_bin,
  PRIMARY KEY (`bird_id`),
  UNIQUE KEY `scientific_name`(`scientific_name`)
) ENGINE=InnoDB AUTO_INCREMENT=20DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

 

 

 

 

 

创建birds_details表并插入birds表中bird_id,和description表中的数据
 CREATE TABLE birds_details
SELECT bird_id, description
FROM birds;

 

 

删除birds表中的description列

ALTER TABLE birds
DROP COLUMN description;

 

 

重命名一个表

先将rookery 数据库中的birds 表重命名为birds_old，然后将test数据库中的birds_new
表移到rookery，并同时将其改名为birds。要用一条SQL 语句来完成这些改动，可以这
样写：
RENAME TABLE rookery.birds TO rookery.birds_old,
test.birds_new TO rookery.birds;

 

重排序一个表

使用order by子句排序

 

例子：

ALTER TABLE country_codes
ORDER BY country_code;

 

 索引

索引的作用是令MYSQL快速的定位数据，

如果没有索引，MySQL 就需要一行行去搜索。
因为索引的体积更小，而且已经组织好以便快速遍历，所以MySQL 可以通过它快速定位
数据，然后直接跳到相应的行的位置。所以，创建表时，尤其是创建会拥有大量行的表
时，请顺便创建索引。这样数据库会跑得更快。

索引不是列，尽管它与列有关。

 

 

 有一个索引与列human_id相关，human_id不是索引，它是索引的根源。它是索引的根源。

 它的名字与索引的名字相同，而且索引也与这一列绑定，但它们绝不等同。

 

通过在SELECT 前加上EXPLAIN，来确认是否是这样。它会告知
我们SELECT 是基于什么来进行查找的，即向我们解释，在执行SELECT 时，服务器做了什
么——所以它不返回任何表内的数据，而是返回索引被如何利用的相关信息。

possible_key 表示SELECT 语句本应用到的键。
在我们这个例子中，name_last 是没有索引的。key 会列出语句实际用到的索引。而在本
例中，它显示NULL。因为现在表中只有四行，所以有没有索引都不会带来明显的性能区
别。然而，如果有一天表中有了数千行数据，那么索引将大大提升查找人名的效率。

 

有时，用户不只会按姓来查询humans 表，他们还会按名来查，甚至姓名一起查。为了对这
些可能性有所准备，以及提升数据量增长后的查询性能，我们在这两列上建立索引。具体
做法是， 使用ALTER TABLE 加ADD INDEX 子句

ALTER TABLE birdwatchers.humans

ADD INDEX human_names (name_last,name_first);

 

显示索引长什么样

MariaDB [rookery]> SHOW CREATE TABLE birdwatchers.humans\G
*************************** 1. row***************************
      Table: humans
Create Table: CREATE TABLE `humans` (
  `human_id` int(11) NOT NULLAUTO_INCREMENT,
  `formal_title` varchar(25) DEFAULT NULL,
  `name_first` varchar(25) DEFAULT NULL,
  `name_last` varchar(25) DEFAULT NULL,
  `email_address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`human_id`),
  KEY `human_names`(`name_last`,`name_first`)
) ENGINE=InnoDBAUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

显示human_names索引信息

MariaDB [(none)]> SHOW INDEX FROM birdwatchers.humans
    -> WHEREkey_name = 'human_names' \G
*************************** 1. row***************************
       Table: humans
   Non_unique: 1
    Key_name: human_names
 Seq_in_index: 1
  Column_name: name_last
    Collation:A
  Cardinality: 4
    Sub_part: NULL
      Packed: NULL
        Null: YES
   Index_type: BTREE
     Comment:
Index_comment:
*************************** 2. row***************************
       Table: humans
   Non_unique: 1
    Key_name: human_names
 Seq_in_index: 2
  Column_name: name_first
    Collation:A
  Cardinality: 4
    Sub_part: NULL
      Packed: NULL
        Null: YES
   Index_type: BTREE
     Comment:
Index_comment:

 

索引与它依赖的列的名字不同。即使索引只根据一列建
立，而且索引名与列名相同，也不代表索引和列是同一个东西。