- 了解SQL：结构化查询语言，与数据库通信
- MySQL简介
- 使用SQL
- 基本语法
- 通配符进行过滤—WHERE
- 正则表达式搜索—WHERE
- 创建计算字段
- 使用数据处理函数
- 汇总数据集
- 分组数据
- 使用子查询：子查询总是从内向外处理
- 联接表：连接数据库中的多个表
- 创建高级连接
- 组合查询
- 插入数据
- 更新和删除数据
- DELECT删除数据
- 创建和操纵表
- mysql -u root -ppassword
>CREATE DATABASE new_db;
#mysqldump old_db -u root -ppassword—skip-extended-insert —add-drop-table | mysql new_db -u root -ppassword
1.了解SQL 2.MySQL简介 3.使用SQL
warning：每次命令结尾加结束语— ；或者\h
sql语句不区分大小写，空格和分行被忽略：习惯约定关键字大写，列和表名小写，子句换行。
mysql -u root -p
password：123456
了解SQL：结构化查询语言，与数据库通信
数据库：有组织的方式存储的数据集合，保存有组织的数据的容器
DBMS：数据库存储软件，数据库是通过DBMS创建和操纵的容器。
模式：描述表的这组信息的所谓模式，模式可以用来描述数据库的特定的表以及整个数库。
模式：描述表的这组信息就是所谓的模式，模式可以用来描述数据库中特定的表以及整个数据库
模式：关于数据库和表的布局及特性的信息。
MySQL简介
MySQL是一种DBMS，是一种数据库软件。
MySQL的优点：
成本: MySQL是开放源代码，免费
性能: 执行很快
可信赖
简单：容易安装和使用
DBMS（数据库管理系统）分为两类：
基于共享文件系统的DBMS
Microsoft Access和FileMaker，用于桌面用途，通常不用于高端或者关键的应用
基于客户机-服务器的DBMS
MySQL、Oracle和Microsoft SQL Server等数据库
服务器：
1.服务器部分，是负责所有数据访问和处理的一个软件，这个软件运行在称为数据库服务器的计算机上。
2.与数据文件打交道的只有服务器软件，对数据库增删改差所有的请求都由服务器软件完成。
客户机：
1.客户机是与用户打交道的软件
eg：人请求一个按照顺序列出的产品表
人—请求—>客户及软件—网络提交请求—>服务器软件—送回结果—>客户机软件
mysql命令行的选项和参数：
- mysql -u ben : 指定用户登陆名为ben
- mysql -u ben -p -h myserver -P 9999 : 指定用户名、主机名、端口和口令。
- \g或者；表示结束
- help或者\h获得帮助 —- help select
- quit或者exit退出命令行实用程序
MySQL Administrator （MySQL管理器）：
图形交互客户机，简化MySQL服务器的管理。
MySQL Administrator 提示输入服务器和登录信息，显示允许选择不同视图的图标：
- service information：显示客户机和被连接的服务器的状态和版本信息
- service control：允许停止和启动mysql以及指定服务器特性
- user administration：用来定义mysql用户、登录权限
- catalogs：列出可用数据库并允许创建数据库和表
MySQL Query Browser
图形交互客户机，用来编写和执行MySQL命令。
使用SQL
连接MySQL
MySQL内部保存自己的用户列表，并且把每个用户与各种权限关联起来。
1.主机名—-连接到本地MySQL服务器，为localhost
2.端口—-使用默认端口3306之外的端口
3.合法的用户名
4.用户口令
展示数据库基本信息
USE crashcourse—-打开某个数据库
show Databases—-返回可用数据库的列表
show tables—-返回一个数据库内的表的列表
show columns from customers—-显示表列
describe columns== show columns from customers
show status—-显示广泛的服务器状态信息
show create database 和 show create table —-显示创建特定数据库或表的mysql语句
show grants—-显示授予用户（所有用户或者特定用户）的安全权限
show errors 和 show warnings—-显示服务器错误或警告消息
information_schema—-获得和过滤模式信息
基本语法
clause子句：关键字+所提供的数据，子句组成SQL语句；例句：select语句的from子句
- SELECT: 检索
单列：SELECT prod_name FROM products；—检索输出结果未过滤未排序
多列：SELECT prod_id,prod_name,prod_price FROM products；
所有列：SELECT FROM products；
// —-通配符，降低检索和应用程序的性能，可查找未知列
去重：SELECT DISTINCT vend_id FROM products;
SELECT DISTINCT vend_id,prod_price FROM products;—-去除vend_id,prod_price都一样的重复列
//DISTINCT—-仅仅适用于所有列;
SELECT：测试
LIMIT：限制起始行（默认0）,返回行数。
限制返回行：SELECT prod_name FROM products LIMIT 5；—-rows：start=0，num=5
SELECT prod_name FROM products LIMIT 3,4；—-rows：start=3，num=4
SELECT prod_name FROM products LIMIT 4 offset 3；—-rows：start=3，num=4
完全限定：SELECT products.prod_name FROM crashcourse.products；ORDER BY：排序
单列：SELECT prod_name FROM products ORDER BY prod_name；
多列：SELECT prod_name FROM products ORDDER BY pro_price,prod_name; —优先级：pro_price>prod_name
降序：SELECT prod_id,prod_price,prod_name FROM products ORDDER BY pro_price DESC;
升序：SELECT prod_id,prod_price,prod_name FROM products ORDDER BY pro_price ASC;—默认升序排序，不写
SELECT prod_name FROM products ORDDER BY pro_price DESC,prod_name;—降排pro_price升排prod_name
- WHERE：过滤数据，过滤数据前注意NULL处理。
操作符：=, <>, !=, <, <=, >, >=, BETWEEN 5 AND 10;
空值检查：IS NULL—-匹配过滤和不匹配过滤时都不返回NULL值，NULL代表未知具有特殊含义。
单值：SELECT prod_name FROM products WHERE prod_name=’fuses’; —-替换操作符
空值检查：SELECT cust_id FROM customers WHERE cut_email IS NULL;
操作符==逻辑操作符：连接或者该表WHERE子句中的子句的关键字：
AND：SELECT pro_id,prod_name FROM products WHERE vend_id=1003 AND prod_price<=10;
OR: SELECT prod_name,pro_price FROM products WHERE vend_id=1002 OR vend_id=1003
AND+OR: SELECT pro_id,prod_name FROM products
WHERE (ven_id=1002 OR vend_id=1003) AND prod_price<=10
优先级：（）> AND > OR
IN: SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
// IN: 匹配清单，功能类似于OR，IN操作符比OR快，IN 还可以包含其他SELECT语句。
NOT IN: SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
NOT取反应用：NOT IN; NOT EXISTS;NOT BETWEEN
通配符进行过滤—WHERE
LIKE操作符：//LIKE作为谓词的时候不是操作符
wildcard通配符：通配符搜索的处理一般要比之前的搜索花费的时间更长。
%：任何字符出现任意次数（包括0次）。
SELECT prodname FROM products WHERE prod_name LIKE ‘%S%E%’ ; —- 空格也算作字符
SELECT prod_name FROM products WHERE prod_name LIKE ‘%’; —- 不的匹配NULL字段
: 与%功能一样，但是只能匹配单个字符,不多不少。
SELECT prodid,prodname FROM products WHERE prod _name LIKE ‘ ton anvil’;
BINARY: 区分大小写.
SELECT * FROM products WHERE BINARY prod_name=’detonator’;
正则表达式搜索—WHERE
正则表达式匹配的列值内部，只要列值内部包含正则表达式的规定就好了
REGEXP: 正则表达式是用来匹配文本的特殊的串（=字符集合）
REGEXP和LIKE区别：
LIKE匹配整个列，’1000’需要列值刚好为’1000’，才返回相应的行；
REGEXP匹配列值，列值只要包含’1000’,就返回相应的行；
.: 匹配任意单个字符
SELECT prod_name FROM products WHERE prod_name REGEXP ‘.000’;
|: SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000|2000|3000’; —-OR 匹配
[]: —-选择性匹配
单一字符：
SELECT prod_name FROM products WHERE prod_name REGEXP ‘[123] Ton’ ORDER BY prod_name;
等价于：SELECT prod_name FROM products WHERE prod_name REGEXP ‘[1|2|3] Ton’ ORDER BY prod_name;
//注意选择匹配中，需要加上[]：因为 1|2|3 Ton==1 OR 2 OR 3 Ton选择范围取反: [^]
SELECT FROM products WHERE prod_name REGEXP ‘[^123] Ton’
SELECT FROM products WHERE prod_name REGEXP ‘[^1|2|3] Ton’匹配范围
SELECT FROM products WHERE prod_name REGEXP ‘[1-3] Ton’;
SELECT FROM products WHERE prod_name REGEXP ‘[a-z] Ton’;
\: 匹配特殊字符 —- 去除 特殊字符 的 特殊含义
SELECT vend_name FROM vendors WHERE vend_name REGEXP ‘\.’;
匹配字符类：
匹配多个实例：
定位符：
举例：\([0-9] sticks?\): \( 匹配括号 （；[0-9] 匹配任意数字；s? 匹配s一次或者0次；
- 匹配连在一起的四位字符
等价于
- ^定位开始位，进行匹配
// ^ ：在集合内部[] —- 表示非； 在集合外面[] —- 表示定位开始位置；
创建计算字段
从数据库中检索出转换、计算或者格式化过的数据，然后再在客户机应用程序或者报告程序中重新格式化。
字段：同义 列，一般数据库称为 列，计算字段连接时候称为 字段。
客户机和服务器模式：在数据库服务器上完成快于在客户机上完成。
Concat()拼接：将两个列连接到一起
- 多数DBMS使用+或者||实现拼接，MySQL则使用Concat()函数实现。
- 拼接： 名字，（，国家，）
SELECT Concat(vend_name,’(‘,vend_country,’)’) FROM vendors ORDER BY vend_name;
- Trim去除串左右两边的空格；RTrim清除右边的空格；LTrim清出左边的空格；
- AS 重新命名 ，算数运算符
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price
FROM orderitems WHERE order_num = 20005;
- 操作符：+ - * /
使用数据处理函数
文本处理函数 —- 处理字符串
- Upper: 将文本转换为大写
- 常见的文本处理函数
- soundex：将任何文件文本串描述其语音表示的字母数字模式的算法，对串的发音进行比较。
日期和时间处理函数
注意不管是过滤、插入还是更新，日期格式都必须为yyyy-mm-dd,首选日期格式。
- 2位数的年份MySQL也会自动补全：70-99 补全为 1970-1999
- 简单的字符串直接比较
- 如果数据库中存储的date为2005-09-01 11：30：00，则匹配不上。
修改思路：将列值的部分数据与已知的数据进行比较，对列值进行格式转换。
- 根据匹配的日期进行选择：eg： Date() 和 Time() 和 Year()
- 数值的范围比较：Between…AND…
- 修改列值，进行AND或者OR操作
数据的处理函数
数值处理函数仅处理数值数据，这些函数一般用于代数、三角或者几何运算。
汇总数据集
聚集函数
用于检索数据，运行在行组上，计算和返回单个值的函数。
- 列名必须作为参数传入聚合函数，函数处理时忽略列值为NULL的数值行,聚合函数必须具有参数。
- count(*): 对行数进行统计，包括NULL值
- MAX()函数在处理文本列的时候，按照数据的列进行相应的排序，最后一行为Max
例子：
- MAX 运用在日期上
- MAX 函数运用在字符串上
- MAX 函数比较字符串的收，从首字母进行比较；
- AVG 处理字符串当0来处理；
- SUM()函数
- 考虑不同价格的平均值
分组数据
GROUP BY：对数据进行分组。
- GROUP BY 子句可以包含任意数目的列
- GROUP BY 子句中的所有列都必须是 检索列 或者 有效的 表达式，不能是聚合函数。
- 分组中有NULL数值，则NULL 作为一个单独的分组列出来。
WITH ROLLUP: 对已经统计的分组数据进行二次统计，得出一个总的数据。
HAVING：对分组数据进行过滤
- HAVING 和 WHERE 用法一样，但是区别在于WHERE用在分列，HAVING用在分组。
- HAVING 和 WHERE 用法一样
使用子查询：子查询总是从内向外处理
- 子查询过滤：列出订购物品 （products（prod_id）） 的所有客户(customers(cust_name))
解决方案：反复套娃
SELECT cust_id,cust_name,cust_contact
FROM customers WHERE cust_id IN
(SELECT cust_id
FROM orders
WHERE order_num IN
(SELECT order_num
FROM orderitems
WHERE prod_id=’TNT2’));
答案：
SELECT cust_id FROM orders
WHERE order_num IN
(SELECT order_num FROM orderitems WHERE prod_id=’TNT2’);
- SELECT 子查询 的过程有点像 顺藤摸瓜
- SELECT 受性能限制，不能嵌套太多子查询。
- SELECT 一般与IN 连接使用，但是有时候也与 = 或者 <>一起使用；
- 字段子查询：显示customers中每个客户的订单(orders)总数
答案：在子查询中用表列的值相等做连接，使得字段与其他表进行联动；、
SELECT cust_name,
(SELECT count(*) FROM orders WHERE orders.cust_id=customers.cust_id)
AS order_num FROM customers;
//—此处的customers.cust_id是外层当前正从customers中检查到的当前这条cust_id;
/如果过滤设置为cust_id=cust_id，则mysql会在子查询中自动将两个cust_id都当做子查询中的表格，即自身与自身比较。
- 注意此处的子查询中，不用ORDER BY,因为每次外层的SELECT查到一条数据对应的cust_id时，都传入子循环中进行该cust_id的相应的计数统计。
- 相关子查询：涉及到外部的子查询，适用于列明可能有多层含义时候
联接表：连接数据库中的多个表
- 注意理解join的用法，join是SELECT的重要功能。
外键：外键为某个表中的一个列，包含另外一个表的主键值；
可伸缩性scale：能够适应不断增加的工作量而不失败。设计良好的数据库或者应用程序称为伸缩性良好。SsS（关系数据库>非关系数据库）
完全限定名：引用的列出现二义性时候，必须使用完全限定名。表名.列名
- 等值连接：WHERE: 用WHERE进行连接表格—进行数据的过滤
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id;
- 利用 WHERE 进行限定，实际上做的是将第一个表的每一个行与第二个表格中的每一行进行配对筛选。
- 类似于傻瓜式比较，做了len(table a)*len(table b)次判断。
- 不用WHERE进行过滤返回的结果，返回的结果为笛卡尔集。
- 如果不进行WHERE 过滤，则将a表中的每一项都和表b进行配对，select返回len(table a)*len(table b)条数据。
等值连接WHERE 和 内部连接 INNER JOIN … ON对比：
- 等值连接WHERE：WHERE 使用的该方法称为等值连接，是基于两个表之间的相等测试，也成为内部连接。
- 内部连接INNER JOIN … ON: 为了更好的体现出内部连接的名称意义，可以使用INNER JOIN … ON,ON 功能等价为 WHERE
- 等值连接和内部连接的作用一样，返回结果也一样。
- WHERE 更加简洁，INNER JOIN … ON 性能好
- 内部连接: INNER JOIN… ON 等价于 WHERE 等值连接
SELECT vend_name,prod_name
FROM vendors INNER JOIN products
ON vendors.vend_id=products.vend_id;
- 联接多个表格：WHERE … AND: 连接多个表格同时进行数据处理
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num
AND prod_id=’TNT2’;
创建高级连接
- concat: 将多个列值按照一定的格式进行合并，合并为一个值进行返回。
- 别名：列值别名 以及 表别名
SELECT cust_name,cust_contact
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id=o.cust_id
AND oi.order_num=o.order_num
AND prod_id=’TNT2’;
- 自连接：在单条SELECT检索中不止一次的引用相同的表。
Eg: 查找DINIR供应商提供的其他产品
（1） 子查询
SELECT prod_id, prod_name
FROM products
WHERE vend_id=
(SELECT vend_id FROM products WHERE prod_id=’DTNTR’);
（2）自连接—等值连接
SELECT a.prod_id,a.prod_name
FROM products AS a,products AS b
WHERE a.vend_id=b.vend_id AND b.prod_id=’DTNTR’;
- 自然联接：排除表连接之后的重复列，使得每个列只是显示一次。
标准联接：对表进行连接时，应该至少有一个列不知出现一次，标准连接返回所有数据，包括重复列重复返回。
外部联接：OUTER JOIN
访问相关表格中没有关联的行，例如：访问没有下订单的客户。
(1) 检索所有用户，包括那些没有订单的用户。（customers和orders两张表格如下）
LEFT OUTER JOIN：外部联接左边的表格
SELECT customers.cust_id,orders.order_num
FROM customers
LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id;
customers表格中全都留着，然后去对orders表格中的数据，没有order的customer用NULL 显示，有多次返回多次。
RIGHT RIGHT JOIN: 外部联结右边的表格
SELECT customers.cust_id,orders.order_num
FROM customers
RIGHT OUTER JOIN orders ON customers.cust_id=orders.cust_id;
orders表格中的值都有，在customer中找对应的值，没有的写NULL，有出现多次的返回多次。
- 拿每个orders中的值去比对customers表格中的值。
- 聚集函数的联接
mysql> SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num)AS num_ord
-> FROM customers INNER JOIN orders
-> on orders.cust_id=customers.cust_id
-> GROUP BY customers.cust_id;
组合查询
执行多个查询（SELECT 语句），并将结果作为单个查询结果返回。
组合查询通常称之为并（union）或复合查询（compound query）。
创建组合查询
UNION：给出每天SELECT语句，并且用UNION联接，并且每个SELECT语句访问的列的数要相同，类型可以不同但是必须兼容。
(SELECT vend_id,prod_price FROM products WHERE prod_price<=5)
UNION
(SELECT vend_id,prod_price FROM products WHERE vend_id IN (1001,1002));
同列数不同列，也能union
(SELECT vend_id,prod_price FROM products WHERE prod_price<=5)
UNION
(SELECT vend_id,prod_name FROM products WHERE vend_id IN (1001,1002));
组合查询，对返回结果进行排序，只能使用一条ORDER BY子句，必须出现在最后一条SELECT 语句之后，同时这条排序回对整个查询结果产生影响（全排）。
- 如果需要每个条件的匹配行都出现（包括重复行），则必须使用UNION ALL 而不是WHERE.
全文本索引:FULLTEXT
必须索引被搜索的列，而且要随着数据的改变不断地重新索引。
在索引之后，SELECT可和Match()和Against()一起使用实现执行。
- 定义全文本搜索
CREATE TABLE productnotes(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text) —- 定义全文本搜索
)ENGINE=MyISAM;
- 不要再导入数据使用FULLTEXT，更新索引下需要花时间。所以先引入数据，然后再修改表添加FULLTEXT。
- 进行全文本搜索
SELECT note_text
FROM productnotes
WHERE Match(note_text)
Against(‘rabbit’);
- Match(note_text) ：指示MYSQL针对指定的列进行搜索。
- Against(‘rabbit’)：指定词rabbit作为搜索文本，包含词rabbit的行被返回。
- 除非使用BINARY，否则全文本搜索不区分大小写。
全文本搜索返回以文本匹配的良好程度排序的数据
eg：第3个词的行的等级比作为第20个词的行高，全文本搜索的一个重要部分就是对结果排序，具有较高等级的行先返回。
- 查询扩展
SELECT note_text
FROM productnotes
WHERE Match(note_text)
Against(‘rabbit’ WITH QUERY EXPANSION);
- 不仅返回包含rabbit的行，同时也会返回可能与rabbit有关系的行。
- 布尔文本搜索
SELECT note_text
FROM productnotes
WHERE Match(note_text)
Against(‘rabbit’ IN BOOLEAN MODE);
(1) +: 包含，词必须存在。
SELECT note_text
FROM productnotes
WHERE Match(note_text)
Against(‘+rabbit +bait’ IN BOOLEAN MODE);
- 搜索包含词rabbit和bait的行
(2) - : 排除，词不出现。
SELECT note_text
FROM productnotes
WHERE Match(note_text)
Against(‘+rabbit -bait’ IN BOOLEAN MODE);
- 搜索包含词rabbit但是不包含bait的行
(3) : 可选择，至少出现一个。
SELECT note_text
FROM productnotes
WHERE Match(note_text)
Against(‘rabbit bait’ IN BOOLEAN MODE);
- 搜索包含词rabbit和bait中的至少一个词的行。
(4) <>: 等级加权，等级高的。
<:降低一个等级，>：增加一个等级
SELECT note_text
FROM productnotes
WHERE Match(note_text)
Against(‘+rabbit +(<bait)’ IN BOOLEAN MODE);
- 搜索包含词rabbit和bait的行,降低后者等级。
() 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
“” 定义一个短语（与单个词列表不一样，它匹配整个短语以便包含或排除这个短语）
- 在布尔方式中，不按等级制降序排序返回的行。
（个人不确定的理解，再匹配行的时候按照等级制度，但是返回的行不做二次等级排序）
全文本搜索的使用说明
- 索引全文数据时，短词被忽略且从索引中排除。何为短语：那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）
- MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表
- 许多词出现的频率很高，搜索它们没有用处（返回太多结果）因此MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE
- 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者只是出现在50%的行中）
-
插入数据
INSERT：要求指定表名字和被插入到新行中的值。
按照表格定义的列值顺序进行值的填充：必须给出每个列的值
INSERT INTO customers
VALUES(NULL,’Pep E. LaPew’,’100 MAIN Street’,’Los Angeles’,’CA’,’90046’,’USA’,NULL,NULL);
- ID值设置为NULL，因为是自增量，不给值会自动填充。
- 指定列名对表格进行填充：首列自增量可以省略不写，其余的值都需要指明完整。
INSERT INTO customers
(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES
(‘Pep E. LaPew’,’100 MAIN Street’,’Los Angeles’,’CA’,’90046’,’USA’,NULL,NULL);
- 一次性插入多条列名和次序相同的值
INSERT INTO customers
(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES
(‘Pep E. LaPew’,’100 MAIN Street’,’Los Angeles’,’CA’,’90046’,’USA’,NULL,NULL),
(‘P’,’100 MAIN Street’,’Los Angeles’,’CA’,’90046’,’USA’,NULL,NULL);
- 批量插入数据
INSERT INTO customers
(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
SELECT
cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email
FROM custnew;
- 多列值一起更新
UPDATE customers
SET cust_name=’The Fudds’,
cust_email=’elmer@fudd.com’
WHERE cust_id=10005;
- IGNORE：如果更新过程中发生错误，则忽略错误继续执行。
UPDATE IGNORE customers
SET cust_name=’The Fudds’,
cust_email=’elmer@fudd.com’
WHERE cust_id=10005;
-
DELECT删除数据
DELECT：只能删除表格内部，不能删除表本身和该表表的结构。
DELECT FROM customers
WHERE cust_id=10006; 如果想要删除表中所有的行，不要使用DELECT，使用 TRUNCATE TABLE，速度更快。
- TRUNCATE 实际上是删除原来的表格在创建一个新的空表
TRUNCATE 也可以用于计算
如果省略过滤WHERE，UPDATE和DELECT操作将贯穿整个数据表。
- 在使用UPDATE和DELECT前，为了保证安全性，应该先用SELECT进行测试，保证过滤的值是正确的。
使用强制实施引用完整性的数据库，这样MYSQL 就不允许删除和其他表相关联的数据。
创建和操纵表
当创建的表名字在数据库中存在，应该先删除然后再创建一个新的，不能直接覆盖。
CREATE 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;
IF NOT EXISTS: 创建表格前进行判断，该表是否存在。
CREATE TABLE IF NOT EXISTS customers_new2(
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;-
AUTO_INCREMENT：每当增加一行时，自动增量。
每次执行一次INSERT操作，MYSQL自动对该列进行增量，给该列赋予下一个可用的值，每次的值都是唯一的。
每张表只允许存在一个AUTO_INCREMENT的列，而且该列必须称为主键（成为主键）。
DEFAULT: 指定默认值
再插入行没有给出值的时候，MYSQL 用关键字DEFAULT来指定默认值，一般跟NOT NULL 解和。
cust_name char(50) NOT NULL DEFAULT 1，
- MYSQL 的特别之处在于，不允许使用函数作为默认值，支持常量作为默认值分配。
- 实际开发中，多使用NOT NULL…DEFAULT，很少使用NULL。
ENGINE: 引擎类型
定义：具体管理事务和处理数据的内部引擎，创建表以及增删改查，都在该引擎内执行操作。
MYSQL不同于其他DBMS，MYSQL具有多个引擎，每个引擎都能执行增删改查，并且都隐藏在MYSQL服务器中。
MYSQL的每个引擎具有各自不同的功能和特性，任务不同需要选择对应的引擎。
重要的引擎类型：
- InnoDB是一个可靠的事务处理(出现错误，该事务的所有操作都不执行)引擎，它不支持全文本搜索，多用于银行，安全性高，更加可靠。
- MEMORY在功能等同于MyISAM，由于数据存储在内存中(非磁盘)，速度很快(特别适合临时表)。
- MyISAM性能极高，考虑到了查的频率远远高于改的频率，查的速度很快，支持全文本搜索，但是不支持事务处理，默认情况下(不指明引擎的情况下)使用。
- 注意：每个表配置一个引擎，一个数据库可以混用引擎，但是外键使用不能跨引擎引用。
ALTER：更改表结构
- 增加列：给vendors表增加一个列vend_phone
ALTER TABLE vendors
ADD vend_phone CHAR(20);
- 定义外键，类比增加其他约束
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_ordres
FOREIGN KEY(order_num)
REFERENCES orders(order_num);
- 删除一个列
ALTER TABLE 表名 DROP column 列名
- 对一个表做多个操作，可以卸载一个ALTER语句，用逗号隔开。
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_ordres
FOREIGN KEY(order_num)
REFERENCES orders(order_num),
ADD order_detail CHAR(20);
DROP: 删除表
- DROP TABLE customers2;
RENAME: 重命名
- 单表：RENAME TABLE customers2 TO customers;
多表：RENAME TABLE backup_customers TO customers, backup_products TO products;
复制表
复制表结构，不要数据
create table_tst select * from products where 1=2;
- table_tst 为空表
- 不复制外键，还有AUTO_INCREMENT.
- 插入另外一个表格的数据(假设两个表结构一样)
insert into table_tst select * from products;
- 复制表结构加数据(假设两个表结构一样)
create table_tst select * from products;
- 复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,…….) SELECT 字段1,字段2,…… FROM 旧表
- 可以将表1结构复制到表2
SELECT * INTO 表2 FROM 表1 WHERE 1=2
- 可以将表1内容全部复制到表2
SELECT * INTO 表2 FROM 表1
- show create table 旧表;
这样会将旧表的创建命令列出。我们只需要将该命令拷贝出来，更改table的名字，就可以建立一个完全一样的 表
- mysqldump
用mysqldump将表dump出来，改名字后再导回去或者直接在命令行中运行
- 复制旧数据库到新数据库（复制全部表结构并且复制全部表数据）
mysql -u root -ppassword
>CREATE DATABASE new_db;
#mysqldump old_db -u root -ppassword—skip-extended-insert —add-drop-table | mysql new_db -u root -ppassword
- 表不在同一数据库中（如，db1 table1, db2 table2)
sql: insert into db1.table1 select from db2.table2 (完全复制)
insert into db1.table1 select distinct from db2.table2(不复制重复纪录）
视图：虚拟的表—类似操作打包
视图定义
视图只是包含使用时，动态检索数据的查询，视图本身不包含数据起查看作用。
- 视图是讲SELECT出来的结果(需要明确的知道表结构和联接)命名为一个虚拟表，可以直接调用。
- 可以保护数据(原表的部分数据可见)，更改数据格式(返回与底层表的表示和格式不同的数据)。
-
视图的规则
视图必须唯一命名，视图命名不能和其他的表和视图相撞。
- 创建视图的数目没有限制。
- 创建视图，必须具有足够的访问权限，比如数据管理人员。
- 视图的构造可以来源于另一个视图的检索结果。
- 视图不能索引，不能有关联的触发器或默认值
使用视图
- 创建：CREATE VIEW
- 查看视图：SHOW CREATE VIEW viewname
- 删除视图：DROP VIEW viewname
- 更新视图：（1）DROP+CREATE （2）CREATE OR REPLACE VIEW—-存在就replace，不存在就create
举例
EG: (1) 创建视图 —— 设置过滤条件的视图
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num
AND cust_email IS NOT NULL;
(2) 查找视图
SELECT cust_name,cust_contact
FROM productcustomers,
WHERE prod_id=’TNT2’;
EG: (1) 创建视图 —- 重塑数据格式
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),’(‘,RTrim(vend_country),’)’) AS vend_title
FROM vendors
ORDER BY vend_name;
(2) 查找视图
SELECT *
FROM vendorlocations;
- 如果在视图中检索数据有WHERE筛选， 视图本身构造也含有WHERE筛选，那么在执行整条语句时，两个地方的WHERE 自动合并筛选。
注意：
- 视图更新，其实原表更新了，视图下次使用时动态查找，就自动更新了
- 对视图进行增加或者删除行，实际上是对数据库基表的增加或者删除
如果视图定义有分组，联结，子查询，并，聚集函数，DISTINCT,导出列 则不能进行视图的更新。—-why？
存储过程
定义
存储过程(Stored Procedure)是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它
特点：简单(将处理封装在单元中，对业务变化可以直接修改存储过程)，安全()，高效(存储过程比MYSQL要快)功能
主要提供以下功能，方便用户设计符合应用需求的程序：
1) 变量说明
2) ANSI(美国国家标准化组织)兼容的SQL命令(如Select,Update….)
3) 一般流程控制命令(if…else…、while….)
4) 内部函数语法格式
创建存储过程
MYSQL 处理这一段代码的时候，创建一个新的存储过程，但是这段代码并没有调用存储过程，
并没有执行返回数据，这里只是执行并没有调用。
DELIMITER // ———-分隔符设置为’/‘
create procedure GetPriceByID(
in prodID int,
in isDisc boolean,
out prodPrice decimal(8,2)
)
begin
declare tmpPrice decimal(8,2)；
declare prodDiscRate decimal(8,2)；
set prodDiscRate = 0.88；
select price from products<br /> where id = prodID<br /> into tmpPrice；if isDisc then<br /> select tmpPrice*prodDiscRate into tmpPrice；<br /> end if ；select tmpPrice into prodPrice//<br />end //<br />DELIMITER ; ---------结束时，将分隔符改回； <br /> （1）CREATE 定义存储过程过，并且接收参数在括号内<br /> （2）BEGIN 和 END 之间限定存储过程，存储过程体本身是一个简单的SELECT语句<br /> （3）在实际运用过程中，存储过程中的select以'；'作为结束，同时；也是MYSQL默认的命令行客户机的分隔符<br /> 解决办法，临时修改分隔符：
- 调用存储过程，调用时，传入的参数：值或者@变量名
call sp_name [参数名]
CALL productpricing(@pricelow,@pricehigh); —— 调用且传入数据
CALL productpricing(); —— 调用且不传入数据
- 删除存储过程
drop procedure sp_name
- 一般情况下存储过程并不显示结果，而是把结果传给一个指定变量
CREATE PROCEDURE productpricing(
OUT pl DECIMAL(8,2), ——- OUT 从存储过程中返回传出一个值
OUT ph DECIMAL(8,2), ——- IN 向存储过程中，传入一个值
OUT pa DECIMAL(8,2) ——- OUTIN 传入并返回一个值
)
BEGIN
SELECT Min(prod_price) INTO pl FROM products;
SELECT Max(prod_price) INTO ph FROM products; ——INTO 查找关联到指定的变量
SELECT AVG(prod_price) INTO pa FROM products;
END;
- 创建存储过程
- 调用存储过程
- 检查返回的元素
- 检查存储过程
show create procedure ordertotal
显示创建一个存储过程的create语句
show procedure status
获得何时，由谁创建等详细信息的存储过程列表
show procedure status like ‘ordertotal’
使用like定义一个过滤模式
游标
Mysql的游标只能用于存储过程(和函数)。
存储过程或者函数结束，游标就消失，因为游标局限于存储过程。
定义
游标是一个存储在MYSQL服务器上的数据库查询，它不是一个SELECT语句，而是被语句检索出来的结果集。
- 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
- 游标充当指针的作用。
- 尽管游标能遍历结果中的所有行，但他一次只指向一行。
游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作
用法
声明一个游标：此处仅仅是定义和命名，一个存储过程(或者函数内部)声明仅需要一次
DECLARE 光标名称 CURSOR FOR 查询语法;
declare cursor_name cursor for select_statement;
这个过程并没有执行select检索数据，只是定义了要使用的select语句。
在声明游标后，可以根据需要频繁的打开和关闭游标。
- 打开定义的游标
open 游标名称;
打开游标的时候，执行select查询语句，兵器而存储检索出来的数据。
- 获得下一行数据：只能获取一行数据
FETCH 光标名称 INFO var_name [，var_name ]…..
fetch cursor_name info var_name1,var_name2,…;
- 需要执行的语句(增删改查):这里视具体情况而定
- 释放游标
CLOSE 游标名称;
释放游标内部所有的内存和资源，不再需要游标时记得关闭游标。
例1
例2
ordertotal：
mysql> CREATE PROCEDURE ordertotal(
-> IN onumber INT,
-> IN taxable BOOLEAN,
-> OUT ototal DECIMAL(8,2)
-> )COMMENT ‘Obtain order total,optionally adding tax’
-> BEGIN
-> DECLARE total DECIMAL(8,2);
-> DECLARE taxrate INT DEFAULT 6;
-> SELECT Sum(item_pricequantity)
-> FROM orderitems
-> WHERE order_num=onumber INTO total;
-> IF taxable THEN
-> SELECT total+(total/100taxrate) INTO total;
-> END IF;
-> SELECT total INTO ototal;
-> END//
- 字段或列的注释是用属性comment来添加。eg：id int not null default 0 comment ‘用户id’
if 判断 主要有两种用法：
- IF(condition, value_if_true, value_if_false)。 eg：SELECT IF(500<1000, 5, 10);
IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;
INTO：将查询到的值传入到变量中
processorders
mysql> CREATE PROCEDURE processorders()
-> BEGIN
-> DECLARE done BOOLEAN DEFAULT 0;
-> DECLARE o INT;
-> DECLARE t DECIMAL(8,2) ;
-> DECLARE ordernumbers CURSOR
-> FOR
-> SELECT order_num FROM orders;
-> DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;
-> CREATE TABLE IF NOT EXISTS ordertotals(order_num INT,total DECIMAL(8,2));
-> OPEN ordernumbers;
-> REPEAT
-> FETCH ordernumbers INTO o;
-> CALL ordertotal(o,1,t); ——- 存储变量之间互CALL
-> INSERT INTO ordertotals(order_num,total)VALUES(o,t);
-> UNTIL done END REPEAT;
-> CLOSE ordernumbers;
-> END//
- DECLARE done BOOLEAN DEFAULT 0; ——声明变量并且设置默认值
- REPEAT … END REPEAT ——-循环
- DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1; ——SQLSTATE ‘02000’表示没找到
- DECLARE EXIT HANDLER FOR SQLEXCEPTION 语句后面可以跟一个 begin end的复合语句块，也可以直接跟一个简单语句例如 ：DECLARE EXIT HANDLER FOR SQLEXCEPTION v_succ=0;
- EXIT会在执行异常后执行执行 FOR SQLEXCEPTION 后的语句或块而整个停止下来；CONTINUE选项会在异常后继续执行，从而将id为2的记录写入到数据库中。
执行调用：
mysql> DELIMITER ;
mysql> CALL processorders(); ——调用执行才会创建表
Query OK, 1 row affected (0.13 sec)
mysql> SELECT FROM ordertotals;
*运行：
调用检查
触发器
触发器的定义
触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
MYSQL的触发器和存储过程一样，都是前入到MYSQL中的一段程序，是MYSQL管理的有效工具。
- 不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要执行CALL等手工启动，而是对数据库进行相关操作的时候来出发、激活实现。
触发器与数据库关系密切，住哟啊用于保护表中的数据。特别是当时有多个表具有一定的相互联系时，触发器能够让不同的表保持一致性。
触发器的优缺点
触发器的优点如下
触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。
- 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。
- 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。
触发器的缺点如下：
- 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。
- 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性，
- 如果需要变动的数据量较大时，触发器的执行效率会非常低。
MYSQK支持的触发器
在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。
- INSERT触发器
- 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
- 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
- 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。
- UPDATE触发器
- 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
- 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
- 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
- OLD 中的值全部是只读的，不能被更新。
- 注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。
DELECT 触发器
对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将会回滚
- 非事务性的表，则不能执行此类回滚，及时语句失败，失败前所作的任何更改依然有效
- 若BEFORE触发程序失败，则MYSQL将不执行相应行上的操作。
- 仅当触发程序和行操作均已被成功执行，MYSQL才会执行AFTER触发程序
创建触发器
- BEFORE通常使用与数据验证和数据净化，目的是保证插入表格的数据的确是需要的数据。
- 删除触发器
DROP TRIGGER newproduct
代码1：
- 创建触发器
触发器返回select ‘product added’报错：
MYSQL5以后，不允许触发器返回任何结果，因此使用into @变量名，将结果赋值到变量中，用select调用即可
- SQL 中的变量名前记得加@，因为不加默认表示columns。
- @g：@p代表普通的变量，@@p代表的是系统变量
- 触发
- create的时候并不执行代码，出发时才会执行代码
代码2：插入时同时做修改另外一张表格，并且使用NEW.列名。
报错的地方
（1）创建时候可以通过，没有语法错误。
- You can’t specify target table ‘products’ for update in FROM clause
错误的地方：不能先select出同一表中的某些值，再update这个表(在同一语句中)
修改的方式：select进行嵌套，转一下
- 注意事项：select出来的数据视作衍生表，此处加在from后面 要对衍生表进行别名话 AS a.
- fix掉第一个问题以后，出现了第二个问题：不能更新存储函数/触发器中的表’products’，因为它已经被调用该存储函数/触发器的语句使用。
* 触发器调用products之后，不能再对这个表格进行UPDATE。
代码3：update创建触发器
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state); —set赋值
管理事务
定义
事务处理用来维护事务的完整性，它保证成批的MYSQL操作要么完全执行，要么完全不执行。
- 在MYSQL 中只有使用了Innodb数据库引擎的数据库或表才支持事务
-
ACID
一般来说，事务必须满足四个条件(ACID):
原子性（Atomicity，又称不可分割性）：一个事务中的所有操作，要么全部执行要么全部不执行
- 一致性（Consistency）：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。
- 隔离性（Isolation, 又称独立性）：数据库允许有多个并发事务同时对其数据进行读写和修改能力。
- 持久性（Durability）：事务结束后，对数据的修改就是永久的。
事务控制语句
- START TRANSACTION（或BEGIN）： 显式地开启一个事务；
- COMMIT（或COMMIT WORK）：COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
- ROLLBACK（或ROLLBACK WORK）：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINT identifier（或SAVEPOINT）： 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
- SAVEPOINT ，是数据库事务处理中实现”子事务”，也成为嵌套事务的办法，事务可回滚到savepoint而不影响savepoint创建前的变化，不需要放弃整个事务，identifier表示savepoint_name。
- RELEASE SAVEPOINT identifier： 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACK TO identifier： 把事务回滚到标记点；
- SET TRANSACTION：用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
- SET AUTOCOMMIT=1 自动提交
事务运用
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表
Query OK, 0 rows affected (0.04 sec)
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
mysql> commit; # 提交事务 ——进行事务保存，永久性
Query OK, 0 rows affected (0.01 sec)
mysql> select * from runoob_transaction_test;
+———+
| id |
+———+
| 5 |
| 6 |
+———+
2 rows in set (0.01 sec)
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into runoob_transaction_test values(7); —-此时数据已经插入
Query OK, 1 rows affected (0.00 sec)
mysql> rollback; # 回滚 ——撤销
Query OK, 0 rows affected (0.00 sec)
mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入
+———+
| id |
+———+
| 5 |
| 6 |
+———+
2 rows in set (0.01 sec)
代码举例
一点点小错：
begin后面加 ；。end后面不加
- rollback默认是回滚此事务批次的所有行动。
- rollback只是在此次语言批次内有效
若有收获，就点个赞吧
0 人点赞
