DB——Mysql 基础篇

mysql 核心技术手册 第二版
阅读此博文假设你具备一些mysql基础知识，或者是已阅读过上一篇博文：mysql的安装与配置-（快速入门）

数据备份与恢复

数据库备份

mysqldump -u root -p dbname [tab1 tab2...] > backname.sql
[ ]内为可选参数，为指定该数据库下需备份的表，不加则备份所有表

数据恢复

mysql -u root -p [dbname]< backname.sql
如果数据库间版本差别比较大，数据恢复时可能会需要对backname.sql做一些修改

导出数据csv/escel

• 导出数据为xls格式
  select * from tb_name into outfile 'path.xls' character set gbk;
• 导出数据为csv格式
  select * from tb_name into outfile 'path.xls' character set gbk fields terminated by ',' optionally enclosed by '"' escaped by '"' 2 lines terminated by '\r\n'
  ps：指定格式设置
  terminated by分隔符：意思是以什么字符作为分隔符
  enclosed by字段括起字符
  escaped by转义字符
  terminated by描述字段的分隔符，默认情况下是tab字符（\t）
  enclosed by描述的是字段的括起字符。
  escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）

！！！如果出现
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
说明导出路径设置错误或者禁止导出
show variables like ‘%secure%’; 查看secure_file_priv是否配置导出路径
配置my.cnf (linux/mac windows配置文件是另一个名字)
[mysqld]
secure-file-pri=“your path”

创建数据库和表

• 为一个虚拟的书店建立一个数据库

CREATE DATABASE bookstore;

!通过上述语句就创建了一个 bookstore 的数据库。
Mysql 语句对保留字的大小写并不敏感，对数据库与表名的大小写是否敏感取决于系统。最好以保留字大写，表名等小写的形式进行书写。

• 切换数据库，查看当前数据库

USE bookstore;
SELECT DATABASE();

• 创建一个存放图书基本信息的数据表

CREATE TABLE books(
book_id INT,
title VARCHAR (50),
author vARCHAR (50)
)

• 查看表结构

对于接触一个表来说，这个命令可以让你更好的了解表的结构

DESCRIBE books;

• 修改表结构

考虑到数据规模的增大以及数据结构的复杂性，对数据表的结构修改也是很有必要的

ALTER TABLE books
CHANGE COLUMN book_id book_id INT AUTO_INCREMENT PRIMARY KEY,
CHANGE COLUMN author author_id INT,
ADD COLUMN description TEXT,
ADD COLUMN genre ENUM('novel','poetry','drama'),
ADD COLUMN publisher_id INT,
ADD COLUMN pub_year VARCHAR(4),
ADD COLUMN isbn VARCHAR(20)
;

上述的 sql 语句 修改了原表中 book_id author 字段，并又增加了 5 个字段。其中book_id 设置为外键
ENUM() 为了确保统一性，事先列举几个可用值

• 创建一个 authors 表

CREATE TABLE authors(
author_id INT AUTO_INCREMENT PRIMARY KEY,
author_last VARCHAR(50),
author_first vARCHAR(50),
country VARCHAR(50)
)

##显示结果

SHOW DATABASES;   # 显示所有数据库
SHOW TABLES;      # 显示所有数据表

##插入数据
向表中添加图书作者信息

INSERT INTO authors
(author_last,author_first,country)
VALUES
('Greene','Graham','United Kingdom');

向表中添加图书信息

INSERT INTO books
(title,author_id,isbn,genre,pub_year)
VALUES
('The End of the Affair',1,'0099478447','novel','1951');

##选择查询
查询小说类型的书籍 id 书名 描述

SELECT book_id, title,description
FROM books
WHERE genre = 'novel';

查询小说类型及其作者全名的列表

SELECT book_id,title,pub_year,
CONCAT (author_first,'','author_last') AS author
FROM books
JOIN authors USING (author_id)
WHERE author_last = 'Greene';

字符串函数 CONCAT() 将取出数据连接起来
USING(author_id) 等价于 ON books.book_.id = authors.author_id
##排序 限制 和分组
使用 ORDER BY对输出结果进行排序
假设我们还建立了一个出版信息数据表：publishers

SELECT book_id,title,publisher
FROM books
JOIN authors USING (author_id)
JOIN publishers USING (publisher_id)
WHERE author_last = 'Shakespeare'
AND genre = 'play'
ORDER BY title,pub_year DESC;

上述 sql 语句的功能是查找作者是莎士比亚（Shakespeare）的话剧（play）书籍的 id，书名，出版信息。
ORDER BY 表示依据什么字段排序，默认为升序，希望降序排列可以在字段后加关键字 DESC。

使用 LIMIT 选取二十行以后的十行数据

SELECT book_id,title,publisher
FROM books
JOIN authors USING (author_id)
JOIN publishers USING (publisher_id)
WHERE author_last = 'Shakespeare'
AND genre = 'play'
ORDER BY title,pub_year DESC
LIMIT 20,10;

LIMIT 有两个参数，第一个参数是指起始行，第二个参数需要显示的行的数量。
当 LIMIT 后只跟一个参数， 例如：20。则代表从第一行开始要显示行的个数。

使用 GROUP BY 对数据进行分组

SELECT book_id,title
FROM books
JOIN authors USING (author_id)
WHERE author_last = 'Shakespeare'
GROUP BY title;

对莎士比亚的作品进行分组显示，重复的话只显示一个
##分析处理数据
对数据进行统计，计数统计

# 统计数据库中有多少莎士比亚的作品
SELECT COUNT (*)
FROM books
JOIN authors USING (author_id)
WHERE author_last = 'Shakespeare' 

对数据进行求和，销售额统计

# 从订单表中统计莎士比亚的哈姆雷特销售额
SELECT SUM (SALE_AMOUNT) AS 'Hamlet Sales'
FROM orders
JOIN books USING (book_id)
JOIN authors USING (author_id)
WHERE title = 'Hamlet'
AND author_last = 'Shakespeare'

对时间格式化输出

SELECT DATE_FORMAT (purchase_date, %M %d %Y)
AS 'Purchase Date'
FROM orders
WHERE sale_id = '1250';

##修改数据
修改id为2的书籍的出版年份

UPDATE books
SET pub_year = '1938'
WHERE book_id = '2';

声明要修改的表名，然后用SET重新设置字段的值，用 WHERE 来限定更新行。

限定数据唯一性

因为 id 是设置的自动增加的外键，和书籍的信息并没有什么实质的关系，所以是无法根据id来判定数据唯一性的，书籍中的 ISBN 码可以用来作为判断依据

ALTER TABLE books
CHANGE COLUMN isbn isbn VARCHAR (20) UNIQUE;

将列加入 UNIQUE属性设置字段数据的唯一性
##删除数据
删除数据与查询数据是非常相似的，因为只有先选择数据才能删除
删除数据时，只需要将查询此数据的 sql 语句中 FROM前的 SELECT语句整体替换为 DELETE.
##模糊查询

当并不清楚自己要查询的数据的精确值或者查与某值类似的一些数据时，可以使用模糊查询
查询 title 中包含 today 的所有数据

SELECT book_id,title,
CONCAT (author_first,' ',author_last) AS author
FROM books
JOIN authors USING (author_id)
WHERE title LIKE '%today%';

# 查询以today开头的
# WHERE title LIKE 'today%'; 

# 查询从第二位开始以today开头的
# WHERE title LIKE 'today%';