数据库 - MySQL必知必会 - 《C 后台开发笔记文档》

第1章了解SQL
第2章 MySQL简介
第3章使用MySQL
第4章检索数据
第5章排序检索数据
第6章过滤数据
第7章数据过滤
第8章用通配符进行过滤
第9章用正则表达式进行搜索
第10章创建计算字段
第11章使用数据处理函数
第12章汇总数据
第13章分组数据
第14章使用子查询
第15章联结表
第16章创建高级联结
第17章组合查询
第18章全文本搜索
第19章插入数据
第20章更新和删除数据
第21章创建和操纵表
第22章使用视图
第23章使用存储过程
第24章使用游标
第25章使用触发器
第26章管理事务处理
第27章全球化和本地化
第28章安全管理
第29章数据库维护
第30章改善性能

第1章了解SQL

数据库和数据库管理系统
表
列
行/记录
数据类型
主键：一列（一组列），唯一区分表中的每一行
SQL（Structured Query Language）

第2章 MySQL简介

MySQL是一种DBMS
版本演化
4 -> InnoDB引擎，事务处理，并，改进全文搜索
5 -> 存储过程，触发器，游标，视图

第3章使用MySQL

mysql -hxxx -uxxx -pxxx

-- create Database
CREATE DATABASE database_name

-- change Database
USE database_name;

-- get information
SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM table_name;

-- 搭建环境
source create.sql
source populate.sql

第4章检索数据

DISTINCT关键字检索不同的行，应用于所有的列

-- select data with limit
SELECT ... FROM ... LIMIT 5;

-- select data with limit and offset
SELECT ... FROM ... LIMIT 3, 5;

-- distinct selection
SELECT DISTINCT ... FROM ...;

第5章排序检索数据

DESC关键字只应用到直接位于其前面的列名

-- sort seleted data
SELECT ... FROM ... ORDER BY ... LIMIT 10;

-- multi-sort seleted data
SELECT ... FROM ... ORDER BY ... DESC LIMIT 10;

第6章过滤数据

如果将值与串类型的列比较，则要限定引号，用来与数值列进行比较的值不用引号
NULL和匹配：在匹配过滤或不匹配过滤都不会返回NULL

-- filter data, using WHERE
SELECT ... FROM ... WHERE ... LIMIT 10;

-- using BETWEEN
SELECT ... FROM ... WHERE ... BETWEEN 10 AND 20 LIMIT 10;

-- check NULL
SELECT ... FROM ... WHERE ... IS NULL;

第7章数据过滤

-- AND operator
SELECT ... FROM ... WHERE ... AND ...;

-- OR operator
SELECT ... FROM ... WHERE ... OR ...;

-- IN operator
SELECT ... FROM ... WHERE ... IN (...);

-- NOT operator
SELECT ... FROM ... WHERE ... NOT IN (...);

第8章用通配符进行过滤

%匹配出现任意次数的任何字符（%无法匹配NULL）
_匹配单个字符

-- LIKE operator
SELECT ... FROM ... WHERE ... LIKE 'a%';
SELECT ... FROM ... WHERE ... LIKE '_a';

第9章用正则表达式进行搜索

LIKE和REGEXP：LIKE匹配整个列，REGEXP在列值内进行匹配
MySQL要求用两个反斜杠来进行转义

-- using regular expression
SELECT ... FROM ... WHERE ... REGEXP 'a';

-- using OR
SELECT ... FROM ... WHERE ... REGEXP 'app|d';

-- escape special character
SELECT ... FROM ... WHERE ... REGEXP '\\.';

更多的正则表达式规则

.匹配任意1个字符
*匹配0个或多个匹配
+匹配1个或多个匹配
?匹配0个或1个匹配
{n}指定数目的匹配
{n,}不少于指定数目的匹配
{n,m}匹配数目的范围
^文本开始
$文本结尾
[[:<:]]词的开始
[[:>:]]词的结尾

第10章创建计算字段

计算字段是运行时在SELECT语句创建的

Concat：拼接
RTrim：删除数据右侧多余空格
LTrim：删除数据左侧多余空格
Trim：删除数据左右侧多余空格
算术运算符（+，-，*，/）

-- concatenate fields
SELECT Concat(...) FROM ...;

-- alias
SELECT Concat(RTrim(...), ...) AS ... FROM ...;

-- calculation
SELECT ...*... AS ... FROM ...;

-- more topics on SELECT
SELECT 3*2;
SELECT Trim(' a b cc ');
SELECT Now();

第11章使用数据处理函数

文本处理函数
日期和时间处理函数
日期格式：yyyy-mm-dd 排除多义性
date() 只提取列的日期部分
time()
year()
month()
day()
hour()
minute()
second()
数值处理函数

第12章汇总数据

聚集函数：运行在行组上，计算和返回单个值的函数

AVG：返回某列的平均值
COUNT：返回某列的行数
COUNT()对表中行的数目进行计数，不管是NULL还是非空值
COUNT(column)对特定列具有值得行计数，忽略NULL**
MAX：返回某列的最大值
MIN：返回某列的最小值
SUM：返回某列的和

聚集不同值

对于聚集函数，如果对所有行执行计算，则指定ALL参数或者不给参数，如果要求只包含不同的值，则指定DISTINCT参数

SELECT AVG(DISTINCT prod_price) AS avg_price
  FROM products
  WHERE vend_id = 1003;

组合聚集函数

SELECT Count(*) AS num_items,
       Max(prod_price) AS price_max,
       Min(prod_price) AS price_min,
       Avg(prod_price) AS price_avg
  FROM products;

第13章分组数据

分组允许把数据分为多个逻辑组，以便对每个组进行聚集计算
如果分组列中有NULL值，则NULL将作为一个分组返回
GROUP BY位于WHERE子句之后，ORDER BY子句之前
WHERE过滤的是行而不是分组，使用HAVING过滤分组
SELECT子句顺序：SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT

-- GROUP BY
SELECT vend_id, COUNT(*) AS num_prod
  FROM products
  GROUP BY vend_id;

-- HAVING
SELECT cust_id, Count(*) AS orders
  FROM orders
  GROUP BY cust_id
  HAVING Count(*) >= 2;

SELECT vend_id, Count(*) AS num_prod
  FROM products
  WHERE prod_price >= 10
  GROUP BY vend_id
  HAVING Count(*) >= 2;

SELECT order_num, Sum(quantity*item_price) AS ordertotal
  FROM orderitems
  GROUP BY order_num
  HAVING Sum(quantity*item_price) >= 50
  ORDER BY ordertotal;

第14章使用子查询

子查询总是从内向外处理

-- sub query
SELECT cust_id
  FROM orders
  WHERE order_num IN (
    SELECT order_num
      FROM orderitems
      WHERE prod_id = 'TNT2'
  );

第15章联结表

主键
外键：某个表的一列，包含另外一个表的主键值
分解数据为多个表能更有效地存储，更方便地处理，并具有更大的可伸缩性
完全限定列名：在引用的列可能出现二义性时必须使用完全限定列名
使用WHERE子句建立联结关系
笛卡尔积（交叉联结）
内部联结（等值联结）

SELECT vend_name, prod_name, prod_price
  FROM vendors, products
  WHERE vendors.vend_id = products.vend_id
  ORDER BY vend_name, prod_name;

SELECT vend_name, prod_name, prod_price
  FROM vendors INNER JOIN products
  ON vendors.vend_id = products.vend_id;

第16章创建高级联结

表别名
缩短SQL语句
允许在单条SELECT语句中多次使用相同的表
自联结
外联结：左外联结、右外联结

-- table alias
SELECT cust_name, cust_contact
  FROM customers AS c, orders AS o, orderitems AS oi
  WHERE c.cust_id = o.cust_id
    AND o.order_num = oi.order_num
    AND oi.prod_id = 'TNT2';

-- self-join
SELECT p1.prod_id, p1.prod_name
  FROM products AS p1, products AS p2
  WHERE p1.vend_id = p2.vend_id
    AND p2.prod_id = 'DTNTR';

-- outer-join
SELECT customers.cust_id, orders.order_num
  FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

第17章组合查询

使用UNION可把多条查询的结果作为一条组合查询返回
UNION默认去除重复的行，如果想返回所有匹配行，可使用UNION ALL
UNION可取代复杂的WHERE子句，简化多个表中检索数据的操作
对组合查询结果排序，ORDER BY子句只出现在最后一条SELECT语句之后，且排序所有返回结果

-- sort UNION result
SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_id;

第18章全文本搜索

MyISAM支持全文本搜索，InnoDB不支持全文本搜索

使用通配符或者正则表达式性搜索的限制

第19章插入数据

一般不使用没有明确给出列的列表的INSERT语句

-- more safe insert
INSERT INTO customers(cust_id, cust_name, ...)
  VALUES(NULL, 'Pep E. LaPew', ...);

-- multiple values
INSERT INTO customers (cust_id, cust_name, ...)
  VALUES(NULL, 'Pep E. LaPew', ...),
  VALUES(10002, 'Mouse House', ...);

-- insert from other table
INSERT INTO customers (cust_id, cust_name, ...)
  SELECT cust_id, cust_name, ... FROM custnew;

第20章更新和删除数据

使用UPDATE或DELETE时一定要注意不要省略WHERE子句，否则就会更新表中所有行
在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试
DELETE删除的是行而不是列，删除列可以使用UPDATE语句
DELETE不删除表

-- UPDATE
UPDATE customers
  SET cust_email = 'jack@gmail.com'
  WHERE cust_id = 10005;

UPDATE customers
  SET cust_email = 'jack@gmail.com',
      cust_name = 'jack'
  WHERE cust_id = 10005;

-- DELETE
DELETE FROM customers
  WHERE cust_id = 10006;

第21章创建和操纵表

NULL值就是没有值或缺值，允许NULL值的列允许在插入行时不给出该列的值，不允许NULL值的列不接受该列没有值的行
主键只能使用不允许NULL值的列，允许NULL值的列不能作为唯一标识

-- Create customers table
CREATE TABLE customers(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

CREATE TABLE orderitems(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

-- add column
ALTER TABLE vendors
  ADD vend_phone char(20);

-- drop column
ALTER TABLE vendors
  DROP COLUMN vend_phone;

-- Define foreign keys
ALTER TABLE orderitems
  ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num)
  REFERENCES orders (order_num);

-- drop table
DROP TABLE vendors;

-- rename table name
RENAME TABLE backup_customers TO customers;

第22章使用视图

视图是虚拟的表

-- create view
CREATE VIEW prodcust AS
  SELECT cust_name, cust_contact, prod_id
  FROM customers, orders, orderitems
  WHERE customers.cust_id = orders.cust_id
    AND orders.order_num = orderitems.order_num;

SELECT cust_name, cust_contact
  FROM prodcust
  WHERE prod_id = 'TNT2';

-- formatting field using views
CREATE VIEW vendorlocations AS
  SELECT Concat(RTrim(vend_name), ' (', Trim(vend_country) ,')') AS vend_title
    FROM vendors
    ORDER BY vend_name;

SELECT * FROM vendorlocations;

-- filtering data
CREATE VIEW custemaillist AS
  SELECT cust_id, cust_email
    FROM customers
    WHERE cust_email IS NOT NULL;

-- calculate field
CREATE VIEW orderitemsexpanded AS
  SELECT order_num
         prod_id,
         quantity,
         item_price,
         quantity*item_price AS expanded_price
    FROM orderitems;

第23章使用存储过程

第24章使用游标

第25章使用触发器

第26章管理事务处理

事务处理：管理必须成批执行的MySQL操作

-- 标识事务的开始
START TRANSACTION;
-- 撤销
ROLLBACK;
-- 提交
COMMIT;
-- 设置保留点delete1
SAVEPOINT delete1;
-- 回滚到保留点delete1
ROLLBACK TO delete1;

-- 不自动提交
SET autocommit = 0;

MySQL必知必会

第1章 了解SQL

第2章 MySQL简介

第3章 使用MySQL

第4章 检索数据

第5章 排序检索数据

第6章 过滤数据

第7章 数据过滤

第8章 用通配符进行过滤

第9章 用正则表达式进行搜索

第10章 创建计算字段

第11章 使用数据处理函数

第12章 汇总数据

第13章 分组数据

第14章 使用子查询

第15章 联结表

第16章 创建高级联结

第17章 组合查询

第18章 全文本搜索

第19章 插入数据

第20章 更新和删除数据

第21章 创建和操纵表

第22章 使用视图

第23章 使用存储过程

第24章 使用游标

第25章 使用触发器

第26章 管理事务处理

第27章 全球化和本地化

第28章 安全管理

第29章 数据库维护

第30章 改善性能