0 数据库脚本

• 《mysql必知必会》读书笔记

• 本书的数据库脚本：mysql必知必会官网

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################


########################
# 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 orderitems table
#########################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
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;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################


##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

1 了解SQL

数据库：保存有组织的数据的容器（通常是一个文件或者一组文件）

数据库软件（DBMS）：人们通常使用的是数据库软件，数据库是通过DBMS创建和操纵的容器

表：某种特定类型数据的结构化清单

模式：关于数据库和表的布局及特性的信息

列：表中的一个字段，所有表都是由一个或多个列组成的。

行：表中的一个记录

主键：表中每一行都应该有可以唯一标识自己的一列，其值可以唯一区分表中的每个行。没有主键，更新或者删除表中特定的行很困难，因为没有安全的方法保证只涉及相关的行。

Mysql主键规则：

• 任意两行都不具有相同的主键值

• 每个行都必须具有一个主键值，主键值不允许为null

2 mysql简介

• 简单介绍一下Mysql，自行查询相关资料即可

3 使用MySQL

# 创建一个数据库learn_mysql，连接数据库
use learn_mysql;

# 展示某个数据库中的表
show databases;

# 展示某个表中所有列的设计信息：describe table_name等价
show columns from customers;
# 与上面等价
describe customers;

4 检索数据

# 展示的数据是无序的：sql语句返回的是原始、无格式的数据
select prod_name from products;

# 去重检索
select vend_id from products;
select distinct vend_id from products;

# 数据库行数从0开始;rowIndex = (前端页数-1)*每页展示数量
select prod_name  from products limit 0,1;

5 排序检索数据

• order by :必须在from字句后，如果结合limit，则limit必须在order by之后

# 单个列排列：order by，检索数据通常需要排序才有意义,默认是升序排列
select prod_name from products order by prod_name;

# 多个列排列:多个列来排序，前一个相同后一个才进行排序，如果前一个都不同，则后一个排序条件永不成立
select prod_id,prod_price,prod_name from products order by prod_price,prod_name;

# 升序排序
select prod_id,prod_price,prod_name from products order by prod_price desc;

# 前一个升序，后一个降序
select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;

# 找出最高价格
select prod_price from products order by prod_price desc limit 1;

6 过滤数据

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
大于
>=	大于等于
between	两者之间

# where:不区分大小写
select prod_name,prod_price from products where prod_name ='fuses';
select prod_name,prod_price from products where prod_name ='FUses';

# 不等于的两种写法
select vend_id,prod_name from products where vend_id <> 1003;
select vend_id,prod_name from products where vend_id != 1003;

# between:范围内查询
select prod_name,prod_price from products where prod_price between 5 and 10;

# 空值查询：is null
select cust_id from customers where cust_email is null;

7 数据过滤

in 运算符优点：

1. 语法更清楚和直观
2. 计算次序更容易管理
3. in 比 or 计算速度更快
4. in 最大优点是可以包含其他select 语句

# where+and:两者都要匹配
select prod_id,prod_price,prod_name from products where vend_id = 1003 and prod_price <=10;

# where+or:匹配任何一个都可以
select prod_name,prod_price from products where vend_id = 1002 or vend_id =1003;

# and+or:and的优先级比or高，vend_id=1003 and prod_price >=10先执行
select prod_name,prod_price from products where vend_id=1002 or vend_id=1003 and prod_price >=10;
# or字句需要先执行的话，需要加括号
select prod_name,prod_price from products where (vend_id=1002 or vend_id=1003) and prod_price >=10;

# in：in (a,b) 等价 a or b
select  prod_name,prod_price from products where vend_id in (1002,1003) order by prod_name ;
select  prod_name,prod_price from products where vend_id  = 1002 or  vend_id =1003 order by prod_name;

# not + in :mysql支持对in、between、exists句子使用
select  prod_name,prod_price from products where vend_id not in (1002,1003) order by prod_name ;

8 用通配符的技巧

注意尾部空格：%anvil不能匹配最后一个为一个空格或多个空格，解决办法是在%anvil%或者第11章的去掉首尾空格的函数

注意Null：尽管%可以匹配任何东西，但是不能匹配null值

# 通配符%：默认是不区分大小写
select prod_id,prod_name from products where prod_name like 'Jet%';
select prod_id,prod_name from products where prod_name like 'jet%';

# 通配符%前后都使用:
select prod_id,prod_name from products  where prod_name like '%anvil%';

# 通配符_:匹配单个字符,.5 ton anvil就匹配不出来，%才行
select prod_id,prod_name from products  where prod_name like '_ ton anvil';
select prod_id,prod_name from products  where prod_name like '%ton anvil';

9 用正则表达式进行搜索

空白元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

字符类	说明
[:alnum:]	任意字母或数字(同[a-zA-Z0-9])
[:alpha:]	任意字符(同[a-zA-Z])
[:blank:]	空格和制表(同[\\t])
[:cntrl:]	ASCII控制字符(ASCII0到32和127)
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包含空格
[:lower:]	任意小写字母(同[a-z])
[:prinit:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v])
[:upper:]	任意大写字符(同[A-Z])
[:xdigit:]	任意十六进制数字(同[a-fA-F0-9])

重复元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
？	0或1个匹配(等于{0,1})
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

定位元字符	说明
^	文本的开始
$	文本的结束
[[:<:]]	词的开始
[[:>:]]	词的结尾

# regexp:表示使用正则表达式，'1000'等价于%1000%,相当于模糊匹配
select prod_name from products where prod_name regexp '1000';
select prod_name from products where prod_name regexp '.000';

# like和regexp的区别
select prod_name from products where prod_name like '1000';# like匹配整个列，被匹配文本在列值中出现，相应的行也不会被返回
select prod_name from products where prod_name regexp '1000';# regexo匹配整个列，被匹配文本在列值中出现，相应的行会被返回

# regexp：默认是不去区分大小写，如果需要区分大小写，regexp binary
select prod_name from products where prod_name regexp binary 'JetPack .000';

# regexp+|：与or功能类似
select prod_name from products where prod_name regexp '1000|2000' order by prod_name;

# []:表示匹配特定的某个字符
# [123] ton = 严格按照[1|2|3] ton
select prod_name from products where prod_name regexp '[123] ton' order by prod_name;
# ^：取反
select prod_name from products where prod_name regexp '[^123] ton' order by prod_name;
# 1|2|3 ton：列里包含1或者2或者3 ton，与[123 ton]含义区分开
select prod_name from products where prod_name regexp '1|2|3 ton' order by prod_name;
# [1-5]：表示范围
select prod_name from products where prod_name regexp '[1-5] ton' order by prod_name;

# .表示匹配任意字符，
select vend_name from vendors where vend_name regexp '.' order by vend_name;
# 匹配特殊字符加//，//.才是匹配.字符
select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

# 匹配(数字 stick)或者(数字 sticks)
select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;
select prod_name from products where prod_name regexp '\\([:digit:] sticks?\\)' order by prod_name;

# 匹配包含4个数字
select prod_name from products where prod_name regexp '[:digit:]{4}' order by prod_name;

# 定位符：^在正则表达式表示从文本的开始
# 匹配以一个数或者小数点开始的所有产品
select prod_name from products where prod_name regexp '^[0-9\\.]' order by prod_name;

10 创建计算字段

字段：基本上与列意思相同，但是列名是数据库实际存储的列的名字，而字段通常是经过用在计算字段的连接上

拼接（concatenate）：将值拼接到一起构成单个值

# CONCAT使用拼接，mysql的拼接是使用concat，别的sql会有区别
select CONCAT(vend_name,' (',vend_country,')')  from vendors order by vend_name;
# Rtrim:去掉右边的空格
select CONCAT(Rtrim(vend_name),' (',Rtrim(vend_country),')')  from vendors order by vend_name;
# 取别名
select CONCAT(Rtrim(vend_name),' (',Rtrim(vend_country),')')  as vend_title from vendors order by vend_name;

# 执行计算,mysql算术运算符支持+,-,*,/四种
select prod_id,quantity,item_price from orderitems where order_num =20005;
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num =20005;

# mysql的select可以省略from字句便于简单地访问和处理数据
select 3*2;
select trim(' abc ');
select Now();# 返回当前日期和时间

11 使用数据处理函数

常用的文本处理函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边空格
Right()	返回串右边的字符
Rtrim()	去掉串右边的空格
Soundex()	返回串的SoundDex值
SubString()	返回子串的字符
Upper	将串转换为大写

Soundex（）：将任何文本串转换为描述其语言表示的字母数字模式的算法，以下举个例子

# SOUNDEX():匹配发音相似的文本
select cust_name,cust_contact from customers where cust_contact = 'Y. Lie';
select cust_name,cust_contact from customers where soundex(cust_contact) = soundex('Y. Lie');

常用日期和时间处理函数	说明
AddDate()	添加一个日期(天、周等)
AddTime()	添加一个日期(时、分等)
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回小时部分
Minute()	返回分钟部分
Month()	返回月份部分
Now()	返回当前日期和时间
Second()	返回秒部分
Time()	返回时间部分
Year()	返回年份部分

常用数值处理函数	说明
Abs()	绝对值
Cos()	余弦
Exp()	指数值
Mod()	除操作的余数
Pi()	圆周率
Rand()	随机数
Sin()	正弦
Sqart()	平方根
Tan()	正切

# 文本处理函数：Upper
select vend_name,Upper(vend_name) as vend_name_upcase from vendors order by vend_name;

# 日期yyyy-mm-dd，时间HH:MM:SS
# 这样日期是不严谨的,只能查出时间默认为0的日期，比如2005-09-01 00:00:00
# 但是2005-09-01 10:20:30就无法查出想要的结果
select cust_id,order_num from orders where order_date = '2005-09-01';
# 使用Date()和Time()函数
select cust_id,order_num from orders where Date(order_date) = '2005-09-01';
select cust_id,order_num from orders where Time(order_date) = '01:00:00';

# 搜索2005年9月以内的订单
# 方法1:需要知道月份的具体天数
select cust_id,order_num from orders where Date(order_date) between '2005-09-01' and '2005-09-30';
# 方法2:不需要知道具体的天数，最方便
select cust_id,order_num from orders where Year(order_date) = '2005' and Month(order_date)='9';

12 汇总数据

聚集函数	说明
AVG()	平均值，忽略null值
COUNT()	行数
MAX()	最大值
MIN()	最小值
SUM()	之和

# avg:算平均值
select avg(prod_price) as avg_price from products;
# 算特定列的平均值
select avg(prod_price) as avg_price from products where vend_id=1003;
# 算去重后特定列的平均值
select avg( distinct prod_price) as avg_price from products where vend_id=1003;

# count(*)和(1):算行数
select count(*) as num_cust from customers;
select count(1) as num_cust from customers;
select count(cust_email) as num_cust from customers;

# max
select max(prod_price) as max_price from products;

# min
select min(prod_price) as max_price from products;

# sum
select sum(quantity) as times_ordered from orderitems where order_num =20005;
# 数学运算后的sum值
select sum(item_price*quantity) as total_price from orderitems where order_num =20005;

# 组合聚集函数
select 
	count(*) as num_items,
	min(prod_price) as min_price,
	max(prod_price) as max_price,
	avg(prod_price) as avg_price
from products;

13 分组数据

使用group by的注意事项：

• group by字句可以包含任意数目的列。这使得能对分组进行嵌套而数据分组提供更细致的控制
• 如果在group by字句嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算
• group by字句中列出的每个列都必须是检索列或有效的表达式（但不是聚集函数）。如果在select 中使用表达式，则必须在group by 字句中指定相同的表达式
• 除聚集计算语句外，select 语句中的每个列都必须在group by字句中给出
• 如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，他们将分在一组
• group by 字句必须出现在where之后，order by之前

having和where区别：

• where在分组前进行过滤，having 在数组分组后进行过滤

order by	group by
排序产生的输出	分组行。但输出可能不是分组的顺序
任意列都可以使用	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果和聚集函数一起使用列（或表达式），则必须使用

字句执行顺序	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在从表选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚焦使用
having	组级过滤	否
order by	输出排序顺序	否
limit	要检索的行数	否

# 如果要返回每个供应商提供的产品数目怎么办？
select count(1) from products  where vend_id =1003;

# group by:将数组进行分组，对每个组而不是每个结果集进行了聚集
# 使用group by输出可能不是分组的原本排序，所以好习惯是group by + order  by一起使用
select vend_id,count(*) as num_prods from products group by vend_id order by vend_id; 
# group by +  rollup：对每个分组汇总了总数(级别)
select vend_id,count(*) as num_prods from products group by vend_id with rollup order by vend_id ;  

# gruop by是分组，+having 就是过滤分组
select cust_id,count(*) as orders from orders group by cust_id having count(*) >=2;

# where+group by组合使用：where先对数据进行过滤，然后再分组
select vend_id,count(*) as num_prods from products where prod_price >=10 group by vend_id having count(*)>=2 order by vend_id; 
select vend_id,count(*) as num_prods from products group by vend_id having count(*)>=2 order by vend_id; 

# 检索总计订单价格>=50的订单号和总结订单价格,使用group by + order by好习惯的由来
select order_num ,sum(quantity * item_price) as ordertotal  from orderitems group  by order_num having sum(quantity * item_price) >=50;
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 使用子查询

子查询最常见的使用是在where字句的in操作符中+以及用来填充计算列。

# 列出订购物品TNT2的所有客户
# 1.检索包含物品TNT2的所有订单的编号
# 2.检索具有前一步列出的订单编号的所有客户ID
# 3.检索前一步返回的所有客户ID的客户信息
SELECT
	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' ) );

# customers表中每个客户的订单总数，订单和相应客户的ID存储在orders表中
# 该子查询对检索出的每个客户执行了一次，子查询执行了5次，因此检索出了5个客户
select  
 cust_name,
 cust_state,
 (
	select count(*) from orders where orders.cust_id = customers.cust_id
 ) as orders
from customers order by cust_name;	

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;
# 多表连接查询时，请使用where搭配，否则会出现很多无用数据
select vend_name,prod_name,prod_price from vendors,products 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 order by vend_name,prod_name;

# 联结多个表
SELECT
	prod_name,
	vend_name,
	prod_price,
	quantity 
FROM
	orderitems,
	products,
	vendors 
WHERE
	products.vend_id = vendors.vend_id 
AND 
	orderitems.prod_id = products.prod_id 
AND 
	order_num = 20005; 
	
# 14章的订购产品TNT2的客户列表改成联结查询
# 14章子查询
SELECT
	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_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';

16 高级联结

使用联结和联结条件：

• 注意使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结类型，否则将返回不正确的数据
• 应该总是提供联结条件，否则会出现笛卡尔积
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。但是要做好测试，便于排查和书写

# 列别名
select CONCAT(Rtrim(vend_name),' (',Rtrim(vend_country),')')  as vend_title from vendors order by vend_name;
# 表别名：不仅作用于where字句，select列表，order by字句以及语句的其他部分 
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';
	
# 查询生产DTNTR的供应商其他产品
# 自联结
select prod_id,prod_name from products where vend_id = (select vend_id from products where prod_id  = 'DTNTR');
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';

# 自然联结：通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来
SELECT
	c.*,
	o.order_num,o.order_date,
	oi.prod_id,oi.quantity,oi.item_price
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 = 'FB';
	
# 内连接:检索出所有的客户和订单，不会产生null值，因为不以哪个表为主表
select customers.cust_id ,orders.order_num from customers inner join orders on customers.cust_id = orders.cust_id;
select customers.cust_id ,orders.order_num from customers left join orders on customers.cust_id = orders.cust_id;

# 外部联结:包括没有关联的行也展示出来，所以必须搭配left或right
select customers.cust_id ,orders.order_num from customers right outer  join orders on customers.cust_id = orders.cust_id order by customers.cust_id;
select customers.cust_id ,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id order by customers.cust_id;

# 检索所有客户及每个客户所下的订单数
# 使用聚集函数的内部联结
SELECT
	customers.cust_name,
	customers.cust_id,
	count( orders.order_num ) AS num_ord 
FROM
	customers
	INNER JOIN orders ON customers.cust_id = orders.cust_id 
GROUP BY
	customers.cust_id;
	
# 使用聚集函数的外部联结
SELECT
	customers.cust_name,
	customers.cust_id,
	count( orders.order_num ) AS num_ord 
FROM
	customers
	left outer join orders ON customers.cust_id = orders.cust_id 
GROUP BY
	customers.cust_id;

17 组合查询

union规则：

• union必须有2条或2条以上的select语句组成，语句之间用关键字union分隔
• union中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐式转换的类型

# union:可以将多个查询汇集到同一个结果集中
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);
# union与where多条件查询相同
select vend_id,prod_id,prod_price from products where prod_price <=5  or vend_id in(1001,1002) ;

# union会默认取消重复的行，使用union all就可以返回所有的行
select vend_id,prod_id,prod_price from products where prod_price <=5 
union all
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);

# union的排序用order by,并且是作用与所以的表，因为union都组合了肯定使用所有的表
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_price;

18 全文本搜索

全文本布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不存在
包含，但增加等级值
<	包含，降低等级值
()	把词组成子表达式，允许这些子表达式作为一个组被包含、排序、排列等
~	取消一个词的等级值
*	词尾通配符
“”	定义一个短语（与单个词的列表不同，它匹配整个短语以便包含或排除这个短语）

全文本搜素使用说明：

• 在搜素全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词
• Mysql带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。
• 许多词出现的频率很高，搜索它们没有用处(返回太多的结果)，因此，mysql规定了一个50%的规则，如果一次出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于in boolean mode
• 如果表中的行数<3行，则全文本搜索不返回结果（因为每个次或者不出现，或者至少出现50%的行中）
• 忽略词中的单引号。
• 不具有词的分隔符的语言不能恰当地返回全文本搜索结果
• 仅在MyISAM数据库引擎中支持全文本中搜索

# 全文本搜索仅支持MyISAM引擎
select note_text from productnotes where match(note_text) against('rabbit');
# 虽然like模糊查询也可实现但排序是随机的，全文本匹配可以按照文本匹配程度由高到底排序
select note_text from productnotes where note_text like '%rabbit%';

# 查询扩展:不止全文本匹配除你想的文本，还搜索出可能与之相关的行
select note_text from productnotes where match(note_text) against('anvils');
select note_text from productnotes where match(note_text) against('anvils' with query expansion);

# 布尔操作符
# 没有使用布尔操作符
select note_text from productnotes where match(note_text) against('heavy' in boolean mode);
# 使用布尔操作符：-排除，*截断(=通配)
select note_text from productnotes where match(note_text) against('heavy -rope*' in boolean mode);
# +必须包含:说明同时包含rabbit bait
select note_text from productnotes where match(note_text) against('+rabbit +bait' in boolean mode);
# 没有+,说明rabbit bait中至少一个都行
select note_text from productnotes where match(note_text) against('rabbit bait' in boolean mode);
# "rabbit bait"是匹配短语rabbit bait
select note_text from productnotes where match(note_text) against('"rabbit bait"' in boolean mode);
# >:增加等级值，<:减少等级值
select note_text from productnotes where match(note_text) against('>rabbit <bait"' in boolean mode);
# ();把词组成子表达式，允许这些子表达式作为一个组被包含、排除、排列等
select note_text from productnotes where match(note_text) against('+safe +(<combination)"' in boolean mode);

19 插入数据

# 不安全的插入一整行，因为表列顺序可能会改变
insert into customers 
values(
	null,
	'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
);

# 单条数据逐渐插入，书写繁琐,优点是性能比多条数据一次插入快
insert into customers (
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
)
values(
	'Pep E. LaPew',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA'
);
insert into customers (
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
)
values(
	'M. Martin',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA'
);

# 多条数据一次书写，优点是书写简单，缺点性能没有单条数据逐行插入快
insert into customers (
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
)
values(
	'Pep E. LaPew',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA'
),(
	'M. Martin',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA'
);

# 创建custnew表，模拟insert select操作
CREATE TABLE custnew
(
  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;

# 复制customers表中一行数据到custnew中,注意将主键cust_id修改为customers中未出现的主键值 
INSERT INTO `learn_mysql`.`custnew`(`cust_id`, `cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`, `cust_contact`, `cust_email`) VALUES (20001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');

# insert select操作，将一条查询语句结果插入到另一个表中,注意是所有查询的数据全部插入
insert into customers(
	cust_id,
	cust_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
)
select 
	cust_id,
	cust_contact,
	cust_email,
	cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country
from custnew;

20 更新和删除数据

更新和删除的指导原则

• 除非实在打算删除更新和删除每一行，否则绝不使用不带where字句的update，delete语句
• 保证每个表都有主键，尽可能像where字句那样使用它
• 在对update或delete语句使用where时，最好先用select保证where过滤的正确性
• 使用强制实施引用完整性的数据库，mysql将不允许删除具有其他表关联的数据行

# update更新语句要与where搭配使用，否则将更新整个列的数据
UPDATE customers 
SET cust_email = 'elmer@fudd.com' 
WHERE
	cust_id = 10005;

# 更新多个列
UPDATE customers 
SET cust_name = 'The Fudds',cust_email = 'elmer@fudd.com' 
WHERE
	cust_id = 10005;
	
# delete删除也得搭配where使用,否则也是删除全部数据 
# 如果想表中删除所有行,用delete;删除原来的表并重新创建一个,用truncate table 
delete from customers where cust_id = 10006;

21 创建和操作数据库表

使用auto_increment：

• 每个表只允许一个auto_increment列，并且它碧玺被索引（比如让它成为主键）
• select last_insert_id()查询最后一次auto_increment的值

引擎类型：

• InnoDB是一个可靠的事务处理引擎，但是不支持全文本搜索
• Memery在功能上等于MyISAM，但由于数据存储在内存，速度很快（适用于临时表）
• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务
• 外键不能跨引擎使用，所以定义表就显得尤其重要

# 创建表：create table table
CREATE TABLE customers
(
	# AUTO_INCREMENT每个表只能有一个，并且必须被索引
  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;

# select last_insert_id()查询最后一次auto_increment的值
select last_insert_id();


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,# 不指定插入默认是1
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item) # 两个主键
) ENGINE=InnoDB;

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;# 指定引擎为MyISAM

# 更新表alter table xx
# 增加一列
alter table vendors add vend_phone char(20);

# 删除一列
alter table drop column vend_phone;

# alter创建外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

# 删除整个表
drop table custnew;

# 重命名一个表
rename table  customers to customers1;
rename table  customers1 to customers;

22 使用视图

视图

• 是虚拟的表，是mysql对select语句的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据

• 视图本身不包含数据，因此他们返回的数据是从其他表中检索出来的。在添加和改变这些表中的数据时，视图将返回改变过的数据

• crate view创建视图，show craete view viewname查看创建的视图

• drop view删除视图

• 更新视图时，可以吸纳drop再crate,或者 create or replace view

• 如果视图定义中有如下操作，则视图不能更新，需要手动删除视图再创建，一般来说视图只用来查询！

  • 分组（group by和having)
  • 联结
  • 子查询
  • 并
  • 聚集函数

# 第15章的多表联结查询，可以使用视图将它封装
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';
	
# 创建视图，可以像执行简单sql哪样执行原本复杂sql
CREATE VIEW productcustomers AS SELECT
	cust_name,
	cust_contact, 
	prod_id #需要添加后续需要where查询的列名 
FROM
	customers,
	orders,
	orderitems 
WHERE
	customers.cust_id = orders.cust_id 
AND orderitems.order_num = orders.order_num;
	
select 
	cust_name,
	cust_contact 
from productcustomers
where prod_id = 'TNT2';


# 创建视图，可以便于重新格式化之前的数据
create view vendorlocations as 
SELECT
	CONCAT( Rtrim( vend_name ), ' (', Rtrim( vend_country ), ')' ) AS vend_title 
FROM
	vendors 
ORDER BY
	vend_name;

select * from vendorlocations;

# 创建视图过滤不想要的数据
create view customeremaillist as
select cust_id,cust_name,cust_email from customers where cust_email is not null;

select * from customeremaillist;

# 创建视图，保存计算结果，便于where使用
create view  orderitemsexpanded as  
SELECT
	order_num,
	prod_id,
	quantity,
	item_price,
	quantity * item_price AS expanded_price 
FROM
	orderitems;

select * from orderitemsexpanded where order_num = 20005;

23 使用存储过程

# 创建存储过程并使用，注意逗号分号的使用
create procedure productpricing()
begin
	select avg(prod_price) from products;
end;
# 使用存储过程
call productpricing();

# 删除存储过程，不需要()
drop procedure productpricing;

# 函数式存储过程：mysql只支持in、out、inout三个类型的函数参数
# 三个out
create procedure productpricing(
	out pl decimal(8,2),
	out ph decimal(8,2),
	out pa decimal(8,2)
)
begin
	select min(prod_price) into pl from products ;
	select max(prod_price) into ph from products;
	select avg(prod_price) into pa from products;
end;

# mysql变量都以@开头，是一段临时存储的数据
call productpricing(
	@pricelow,
	@pricehigh,
	@pricehigh
);

select @pricelow,@pricehigh,@pricehigh

# in和out使用
create procedure ordertotal(
	in onumber  int,
	out ototal decimal(8,2)
)
begin
	select sum(item_price * quantity) from orderitems where order_num = onumber
	into ototal;
end;
# 查询20005订单的合计价格
call ordertotal(20005,@totoal);

select @totoal;

建立智能存储过程：需要对订单合计增加营业税

• 获得合计（与以前一样）
• 把营业税有条件地添加到合计
• 返回合计

create procedure ordertotal(
	in onumber int,
	in taxable boolean,
	out ototal decimal(8,2)
)comment'选择性计算有/无税收下的订单总价'
begin
	# 定义临时变量total
	declare total decimal(8,2);
	# 税率为6个点
	declare taxrate int default 6;
	# 查询出无税收时订单总价
	select sum(quantity * item_price) from orderitems where order_num = onumber into total; 
	# 是否存在税收
	if taxable then 
		select total+(total/100 * taxrate) into total;
	end if;
	# 临时变量结果返回给函数返回参数
	select total into ototal;
	
end;

call ordertotal(20005,0,@total);
select @total;

call ordertotal(20005,1,@total);
select @total;

# 查看存储过程等详细信息
show procedure status;

24 使用游标

使用游标：

• 在能够使用游标前，必须声明它。这个过程实际上没有检索数据，它只是定义要使用的select语句
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的select语句把游标实际检索出来
• 对于填有数据的游标，根据需要取出各行
• 在结束游标使用时，必须关闭游标

 create procedure processorders(
   out outnumber int
 )
 begin
	# 临时变量
	declare o int;
	# 创建游标
	declare ordernumbers cursor
	for 
	select order_num from orders;
	# 开启游标
	open ordernumbers;
	# 使用游标
	fetch ordernumbers into o;
	
	# 返回数据给函数参数
	select o into outnumber;
	
	# 关闭游标
	close ordernumbers;
end;

call processorders(@outnumber);

select @outnumber;

25 触发器

mysql5以后不支持触发器返回结果，自己手动添加一个变量@变量名，返回该结果可以看到触发器结果；触发器只对insert、delete、update有用

# mysql5以后不支持触发器返回结果，添加一个变量@变量名，返回该结果就行

# 第一次使用触发器：在每次往products添加一行时，都返回“Product added”
create trigger newproduct after insert on products 
for each row select 'Product added' into @ee;

insert into products(prod_id,vend_id,prod_name,prod_price,prod_desc) 
VALUES("ANV011","1001","3 ton anvil",2.33,"good");

select @ee;

insert触发器：

• 在insert触发器代码内，可以用一个名为new的虚拟表，访问被插入的行
• 在before insert触发器中，new中的值也可以被更新(允许更改被插入的值)
• 对于auto_increment列，new在insert执行之前包含0，在insert之后包含新的自动生成值

# insert触发器：auto_increment列，new在insert执行前包含0，insert执行后包含自动生成值
create trigger neworder after insert on orders
for each row select NEW.order_num into @neworderRes;

insert into orders(order_date,cust_id) values(Now(),10001);

select @neworderRes;

drop trigger neworder;

delete触发器：

• 在delete触发器代码内，可以引用一个名为old的虚拟表，访问被删除的行
• old中的值全是只读的，不能更新

# delete触发器：任意订单被删除之前,old都将结果存回achive_orders表中
create trigger deleteorder before delete on orders
for each row 
begin
	insert into achive_orders(order_num,order_date,cust_id) values(old.order_num,old.order_date,old.cust_id);
end;

update触发器：

• 在update触发器代码中，可以引用一个名为old的虚拟表访问之前(update语句值)，引用一个名为new的虚拟表访问新更新的值
• 在before update触发器中，new中的值可能也会被更新(允许更改将要用于update语句中的值)
• old中的值全是只读的，不能更新

# update触发器:new和old都可以使用
create trigger updatevender before  update on vendors
from each row set new.vend_state = upper(new.vend_state) into @updatevenderRes; 

26 管理事务处理

事务(transaction)：指一组sql语句

回退(rollback):指撤销指定sql的过程

提交(commit)：值将未存储的sql语句结果写入数据库表

保留点(savepoint)：指事务处理设置中的临时占位符(place-holder，可以对它发布回退)

# 开启事务:学习回滚
start TRANSACTION;

	select * from orderitems;
	delete from orderitems;
	select * from orderitems;
	# 回退事务,TRANSACTION之后的语句全部不执行
ROLLBACK;

select * from orderitems;

# 开启事务:学习commit
start transaction;

	delete from orderitems where order_num = 20010;
	delete from orders where order_num = 20010;
# 事务块内，commit提交一段sql，前提是没报错
commit;

# 开启事务:设置保留点
start TRANSACTION;
	# 设置一个事务的保留点()
	savepoint deletel;
	# 如果出现问题，就回退到设置的保留点
rollback to deletel;

# 设置mysql不自动提交 
set autocommit = 0;
set autocommit = 1;

27 全球化和本地化

# 显示所有可用的字符集,以及他们默认的校队
show character set;
# 显示所有可用的校队，以及他们默认的字符集
show collation;


# 显示所有的字符集和校队的名字
show variables like 'character%'; 
show variables like 'collation%';


# 创建表指定字符集和校队方式
create table mytable(
	column1 int,
	column2 varchar(10)
)engine = Innodb default character set hebrew collate hebrew_general_ci; 

# 列指定字符集和校队方式
create table mytable1(
	column1 int,
	column2 varchar(10),
	column3 varchar(100) character set latin1 collate latin1_general_ci
)engine = Innodb default character set hebrew collate hebrew_general_ci; 

# 查询语句时order by指定校队方式
select * from customers order by cust_name collate utf8_general_ci;

28 安全管理

# mysql的用户权限，放在mysql库(默认的一个库)里的user表(默认有的一个表)
use mysql;
select * from user;

# 创建一个数据库用户:ben 
# 账户:ben
# 密码:password
create user ben identified by 'password';
# 但是这样创建的用户没有任何权限,查询一下就知道
select * from user;
# 重命名数据库用户名
rename user ben to  ben1;
rename user ben1 to  ben;

# 查看ben用户的权限
show grants for ben;

# 给ben用户添加learn_mysql库的查询权限
grant select on learn_mysql.* to ben;

# 撤销ben用户查询权限
revoke select on learn_mysql.* to ben;

# 修改用户密码，但是必须有权限才行
set password for ben = PASSWORD('password1');
# 不指定用户名，默认是修改本次登录的用户的密码
set password = PASSWORD('password1');

29 数据库维护

# analyze:分析表建是否有问题
analyze table orders;

# check:检查一个表
check table orders,orderitems;

30 改善性能

• 查看当前配置：show variables;show status;

• 显示所有进程：show processlist（服务器上查看ip非常有用）

• explain来查看select语句性能，再优化

• 绝不要检索比需求还要多的数据，也就是select *减少使用

• select 检索数据时or使用过多，可以用union来拼接多条select语句，性能好很多

• like很慢，最好是使用fulltext而不是like