1200字范文 > 《mysql必知必会》学习笔记

《mysql必知必会》学习笔记

时间：2021-08-04 18:06:47

相关推荐

《mysql必知必会》学习笔记

文章目录

附录语法总结DQL（数据库查询语句）DML（数据库操作语句）DDL（数据库定义语句）DCL（数据库控制语句）附录数据库脚本1 了解SQL2 mysql简介3 使用MySQL4 检索数据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 改善性能

附录语法总结

DQL（数据库查询语句）

# select select columnname,...fromtablename,...[where...][union...][group by...][having...][order by...]

DML（数据库操作语句）

# insert insert into table_name [(columns,...)] values [(values,...)];# insert select insert into table_name1 [(columns,...)] select columns,... form table_name2,... [where]# update:更新表中一行或者多行update table_name set columnname= value,...[where...]# delete:删除一行或者多行delete from table_name [where ...];# dropdrop dateabase|index|procedure|table|trigger|user|view item_name;

DDL（数据库定义语句）

# create user create user user_name[@hostname][identified by [password] 'user_password']# alter table:更新已存在表的模式 alter table table_name(add column datatype [null|not null] [constraints],change column columns datatype [null|not null] [constraints],drop column)# create table table_name(column datetype [null||not null] [constraints],column datetype [null||not null] [constraints],... # 最后不能有逗号);# create index create index index_name on table_name(column [asc|desc],...)# create view create [on replace]view view_nameas select ...;# create procedurecreate procedure precedure_name([parameters])begin...end;

DCL（数据库控制语句）

# start transaction:开启一个事务块start transaction;# commit commit;# rollback:撤销一个事务块 rollback [to savepointname];# save point:为rollback设置保留点savepoint spl;# 授权

附录数据库脚本

备注：《mysql必知必会》读书笔记

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

######################################### MySQL Crash Course# /books/0672327120/# Example table creation scripts################################################################# Create customers table########################CREATE TABLE customers(cust_idint 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_zipchar(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 intNOT NULL ,order_item intNOT NULL ,prod_id char(10)NOT NULL ,quantity intNOT NULL ,item_price decimal(8,2) NOT NULL ,PRIMARY KEY (order_num, order_item)) ENGINE=InnoDB;###################### Create orders table#####################CREATE TABLE orders(order_num intNOT NULL AUTO_INCREMENT,order_date datetime NOT NULL ,cust_id intNOT 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 textNULL ,PRIMARY KEY(prod_id)) ENGINE=InnoDB;####################### Create vendors table######################CREATE TABLE vendors(vend_idintNOT 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_zipchar(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 textNULL ,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# /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@');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@');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@');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, '-09-01', 10001);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20006, '-09-12', 10003);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20007, '-09-30', 10004);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20008, '-10-03', 10005);INSERT INTO orders(order_num, order_date, cust_id)VALUES(20009, '-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', '-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', '-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', '-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', '-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', '-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', '-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', '-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', '-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', '-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', '-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', '-09-02','Shipped unassembled, requires common tools (including oversized hammer).');INSERT INTO productnotes(note_id, prod_id, note_date, note_text)VALUES(112, 'SAFE', '-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', '-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', '-09-07','Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be ment forwarded to vendor.');

1 了解SQL

数据库：保存有组织的数据的容器（通常是一个文件或者一组文件）数据库软件（DBMS）：人们通常使用的是数据库软件，数据库是通过DBMS创建和操纵的容器表：某种特定类型数据的结构化清单模式：关于数据库和表的布局及特性的信息列：表中的一个字段，所有表都是由一个或多个列组成的。行：表中的一个记录主键：表中每一行都应该有可以唯一标识自己的一列，其值可以唯一区分表中的每个行。没有主键，更新或者删除表中特定的行很困难，因为没有安全的方法保证只涉及相关的行。Mysql主键规则：任意两行都不具有相同的主键值每个行都必须具有一个主键值，主键值不允许为null

# 添加主键alter table t add column t.id int primary key auto_increment;

2 mysql简介

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

3 使用MySQL

# 展示所有数据库show databases;# 创建数据库create database learn_mysql;# 使用数据库use learn_mysql;# 展示该数据库下所有的表show tables;# 查看表结构:展示某个表中所有列的设计信息,与describe table_name等价show columns from customers;# 与上面等价,简化书写describe customers;

4 检索数据

重点：学习limit

# select默认查询出来的数据是无序的 SELECTprod_name FROMproducts;# 查询和去重查询SELECTvend_id FROMproducts;SELECT DISTINCT vend_id FROMproducts;# limit,数据库行数是从0开始的:行0,行1,行2...# llimit (0,1) = 第一个数为开始为位置(行0)，第二个数为检索的行数# rowIndex = (前端页数-1)*每页展示数量SELECTprod_name FROMproducts LIMIT 0,1;

5 排序检索数据

order by :默认是升序，desc改成降序;可以加limit找出最值

# 单个列排列:order by，默认是升序排列SELECTprod_name FROMproducts ORDER BYprod_name;# 多个列排列:prod_price相同，prod_name才进行组内排序;prod_price都不相同，prod_name就不会排序SELECTprod_price,prod_name FROMproducts ORDER BYprod_price,prod_name;# desc 降序排序SELECTprod_id,prod_price,prod_name FROMproducts ORDER BYprod_price DESC;# 前一个降序，后一个升序SELECTprod_id,prod_price,prod_name FROMproducts ORDER BYprod_price DESC,prod_name;# 找出最高价格SELECTprod_price FROMproducts ORDER BYprod_price DESC LIMIT 1;

6 过滤数据

# where:不区分大小写SELECTprod_name,prod_price FROMproducts WHEREprod_name = 'fuses';SELECTprod_name,prod_price FROMproducts WHEREprod_name = 'FUses';# 不等于的两种写法SELECTvend_id,prod_name FROMproducts WHEREvend_id <> 1003;SELECTvend_id,prod_name FROMproducts WHEREvend_id != 1003;# 范围查找:between + andSELECTprod_name,prod_price FROMproducts WHEREprod_price BETWEEN 5 AND 10;# 区分：1.in是匹配准确的值 2.between是匹配范围内的值，两者完全不相同SELECTprod_name,prod_price FROMproducts WHEREprod_price IN ( 5, 6 );# 空值查询：is nullSELECTcust_id FROMcustomers WHEREcust_email IS NULL;

7 数据过滤

in 运算符优点：

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

# where+and:两者都要匹配SELECTprod_id,prod_price,prod_name FROMproducts WHEREvend_id = 1003 AND prod_price <= 10;# where+or:匹配任何一个都可以SELECTprod_name,prod_price FROMproducts WHEREvend_id = 1002 OR vend_id = 1003;# and+or:and的优先级比or高，vend_id=1003 and prod_price >=10先执行SELECTprod_name,prod_price FROMproducts WHEREvend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;# or字句需要先执行的话，需要加括号SELECTprod_name,prod_price FROMproducts WHERE( vend_id = 1002 OR vend_id = 1003 ) AND prod_price >= 10;# in：in (a,b) 等价 a or bSELECTprod_name,prod_price FROMproducts WHEREvend_id IN ( 1002, 1003 ) ORDER BYprod_name;SELECTprod_name,prod_price FROMproducts WHEREvend_id = 1002 OR vend_id = 1003 ORDER BYprod_name;# not + in :mysql支持对in、between、exists句子使用SELECTprod_name,prod_price FROMproducts WHEREvend_id NOT IN ( 1002, 1003 ) ORDER BYprod_name;

8 用通配符的技巧

# 通配符%：默认是不区分大小写不能匹配null;如果是'%anvil'不能匹配最后有空格的情况,'%anvil%'才行SELECTprod_id,prod_name FROMproducts WHEREprod_name LIKE 'Jet%';SELECTprod_id,prod_name FROMproducts WHEREprod_name LIKE 'jet%';# 通配符%前后都使用:SELECTprod_id,prod_name FROMproducts WHEREprod_name LIKE '%anvil%';# 通配符_:只匹配单个字符,所有.5 ton anvil就匹配不出来，%才行SELECTprod_id,prod_name FROMproducts WHEREprod_name LIKE '_ ton anvil';SELECTprod_id,prod_name FROMproducts WHEREprod_name LIKE '%ton anvil';

9 用正则表达式进行搜索

# regexp:表示使用正则表达式，'1000'等价于%1000%,相当于模糊匹配SELECTprod_name FROMproducts WHEREprod_name REGEXP '1000';SELECTprod_name FROMproducts WHEREprod_name REGEXP '.000';# like和regexp的区别SELECTprod_name FROMproducts WHEREprod_name LIKE '1000';# like匹配整个列，被匹配文本在列值中出现，相应的行也不会被返回SELECTprod_name FROMproducts WHEREprod_name REGEXP '1000';# regexo匹配整个列，被匹配文本在列值中出现，相应的行会被返回# regexp：默认是不去区分大小写，如果需要区分大小写，regexp binarySELECTprod_name FROMproducts WHEREprod_name REGEXP BINARY 'JetPack .000';# regexp+|：与or功能类似SELECTprod_name FROMproducts WHEREprod_name REGEXP '1000|2000' ORDER BYprod_name;# []:表示匹配特定的某个字符# [123] ton = 严格按照[1|2|3] tonSELECTprod_name FROMproducts WHEREprod_name REGEXP '[123] ton' ORDER BYprod_name;# ^：取反SELECTprod_name FROMproducts WHEREprod_name REGEXP '[^123] ton' ORDER BYprod_name;# 1|2|3 ton：列里包含1或者2或者3 ton，与[123 ton]含义区分开SELECTprod_name FROMproducts WHEREprod_name REGEXP '1|2|3 ton' ORDER BYprod_name;# [1-5]：表示范围SELECTprod_name FROMproducts WHEREprod_name REGEXP '[1-5] ton' ORDER BYprod_name;# .表示匹配任意字符，SELECTvend_name FROMvendors WHEREvend_name REGEXP '.' ORDER BYvend_name;# 匹配特殊字符加//，//.才是匹配.字符SELECTvend_name FROMvendors WHEREvend_name REGEXP '\\.' ORDER BYvend_name;# 匹配(数字 stick)或者(数字 sticks)SELECTprod_name FROMproducts WHEREprod_name REGEXP '\\([0-9] sticks?\\)' ORDER BYprod_name;SELECTprod_name FROMproducts WHEREprod_name REGEXP '\\([:digit:] sticks?\\)' ORDER BYprod_name;# 匹配包含4个数字SELECTprod_name FROMproducts WHEREprod_name REGEXP '[:digit:]{4}' ORDER BYprod_name;# 定位符：^在正则表达式表示从文本的开始# 匹配以一个数或者小数点开始的所有产品SELECTprod_name FROMproducts WHEREprod_name REGEXP '^[0-9\\.]' ORDER BYprod_name;

10 创建计算字段

字段：基本上与列意思相同，但是列名是数据库实际存储的列的名字，而字段通常是经过用在计算字段的连接上拼接（concatenate）：将值拼接到一起构成单个值

# CONCAT使用拼接，mysql的拼接是使用concat，别的sql会有区别SELECTCONCAT( vend_name, ' (', vend_country, ')' ) FROMvendors ORDER BYvend_name;# Rtrim:去掉右边的空格SELECTCONCAT( Rtrim( vend_name ), ' (', Rtrim( vend_country ), ')' ) FROMvendors ORDER BYvend_name;# 取别名SELECTCONCAT( Rtrim( vend_name ), ' (', Rtrim( vend_country ), ')' ) AS vend_title FROMvendors ORDER BYvend_name;# 执行计算,mysql算术运算符支持+,-,*,/四种SELECTprod_id,quantity,item_price FROMorderitems WHEREorder_num = 20005;SELECTprod_id,quantity,item_price,quantity * item_price AS expanded_price FROMorderitems WHEREorder_num = 20005;# mysql的select可以省略from字句便于简单地访问和处理数据SELECT3 * 2;# 去掉首尾的空格SELECTtrim( ' abc ' );# 返回当前日期和时间SELECTNow();

11 使用数据处理函数

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

# SOUNDEX():匹配发音相似的文本SELECTcust_name,cust_contact FROMcustomers WHEREcust_contact = 'Y. Lie';SELECTcust_name,cust_contact FROMcustomers WHEREsoundex( cust_contact ) = soundex( 'Y. Lie' );

# 文本处理函数：UpperSELECTvend_name,Upper( vend_name ) AS vend_name_upcase FROMvendors ORDER BYvend_name;# 日期yyyy-mm-dd，时间HH:MM:SS# 这样日期是不严谨的,只能查出时间默认为0的日期，比如-09-01 00:00:00# 但是-09-01 10:20:30就无法查出想要的结果SELECTcust_id,order_num FROMorders WHEREorder_date = '-09-01';# 使用Date()和Time()函数解析Datetime类型数据SELECTcust_id,order_num FROMorders WHEREDate( order_date ) = '-09-01';SELECTcust_id,order_num FROMorders WHERETime( order_date ) = '01:00:00';# 搜索9月以内的订单# 方法1:需要知道月份的具体天数SELECTcust_id,order_num FROMorders WHEREDate( order_date ) BETWEEN '-09-01' AND '-09-30';# 方法2:不需要知道具体的天数，最方便SELECTcust_id,order_num FROMorders WHEREYEAR ( order_date ) = '' AND MONTH ( order_date )= '9';

12 汇总数据

# avg:算平均值SELECTavg( prod_price ) AS avg_price FROMproducts;# 算特定列的平均值SELECTavg( prod_price ) AS avg_price FROMproducts WHEREvend_id = 1003;# 算去重后特定列的平均值SELECTavg( DISTINCT prod_price ) AS avg_price FROMproducts WHEREvend_id = 1003;# count(*)和(1):算行数# count(*):返回所有的行数，包含null数量# count(column):返回指定数值非null的数量SELECTcount(*) AS num_cust FROMcustomers;SELECTcount( 1 ) AS num_cust FROMcustomers;SELECTcount( cust_email ) AS num_cust FROMcustomers;# maxSELECTmax( prod_price ) AS max_price FROMproducts;# minSELECTmin( prod_price ) AS max_price FROMproducts;# sumSELECTsum( quantity ) AS times_ordered FROMorderitems WHEREorder_num = 20005;# 数学运算后的sum值# 思维:查出来的列可以进行各种算术运算,然后使用sum()等函数计算SELECTsum( item_price * quantity ) AS total_price FROMorderitems WHEREorder_num = 20005;# 组合聚集函数SELECTcount(*) AS num_items,min( prod_price ) AS min_price,max( prod_price ) AS max_price,avg( prod_price ) AS avg_price FROMproducts;

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 在数组分组后进行过滤

# 如果要返回每个供应商提供的产品数目怎么办？SELECTcount( 1 ) FROMproducts WHEREvend_id = 1003;# group by:将数组进行分组，对每个组而不是每个结果集进行了聚集# 使用group by输出可能不是分组的原本排序，所以好习惯是group by + order by一起使用SELECTvend_id,count(*) AS num_prods FROMproducts GROUP BYvend_id ORDER BYvend_id;# group by + rollup：对每个分组汇总了总数(级别)SELECTvend_id,count(*) AS num_prods FROMproducts GROUP BYvend_id WITH ROLLUP ORDER BYvend_id;# gruop by是分组，+having 就是过滤聚合分组SELECTcust_id,count(*) AS orders FROMorders GROUP BYcust_id HAVINGcount(*) >= 2;# where先过滤需要的行,然后group by+having,体会执行顺序SELECTvend_id,count(*) AS num_prods FROMproducts WHEREprod_price >= 10 GROUP BYvend_id HAVINGcount(*)>= 2 ORDER BYvend_id;SELECTvend_id,count(*) AS num_prods FROMproducts GROUP BYvend_id HAVINGcount(*)>= 2 ORDER BYvend_id;# 检索总计订单价格>=50的订单号和总结订单价格,使用group by + order by好习惯的由来SELECTorder_num,sum( quantity * item_price ) AS ordertotal FROMorderitems GROUP BYorder_num HAVINGsum( quantity * item_price ) >= 50;SELECTorder_num,sum( quantity * item_price ) AS ordertotal FROMorderitems GROUP BYorder_num HAVINGsum( quantity * item_price ) >= 50 ORDER BYordertotal;

14 使用子查询

select * from table where col in（子查询）注意：子查询列个数和wherect col个数对应

# 列出订购物品TNT2的所有客户# 1.检索包含物品TNT2的所有订单的编号# 2.检索具有前一步列出的订单编号的所有客户ID# 3.检索前一步返回的所有客户ID的客户信息SELECTcust_name,cust_contact FROMcustomers WHEREcust_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个客户SELECTcust_name,cust_state,( SELECT count(*) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders FROMcustomers ORDER BYcust_name;

15 联结表

笛卡尔积：

由没有联结条件的表关系返回的结构为笛卡尔积。检索出的行数目将是第一个表中的行数乘以第二个表中的行数说明：使用多表查询时候，请必须使用where搭配，否则会出现很多虚假数据

# from后跟两个表=联结两张表,有相同的列名,必须指定表名.columnSELECTvend_name,prod_name,prod_price FROMvendors,products# 多表连接查询时，请使用where搭配，否则会出现很多无用数据=笛卡尔积WHEREvendors.vend_id = products.vend_id ORDER BYvend_name,prod_name;

下面与上面sql等价，推荐使用下边写法联结条件on替代上面where条件

SELECTvend_name,prod_name,prod_price FROMvendors# 内连接:两张表交集列数据必须有,其余列可为nullINNER JOIN products ON vendors.vend_id = products.vend_id ORDER BYvend_name,prod_name;

可以联结多个表，但是联结过多性能越差

# 联结多个表SELECTprod_name,vend_name,prod_price,quantity FROMorderitems,products,vendors WHEREproducts.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;

14章的订购产品TNT2的客户列表改成联结查询

# 14章的订购产品TNT2的客户列表改成联结查询# 14章子查询SELECTcust_name,cust_contact FROMcustomers WHEREcust_id IN ( SELECT cust_id FROM orders WHERE order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ) );# 多表联结查询是最简单写法SELECTcust_name,cust_contact FROMcustomers,orders,orderitems WHEREcustomers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2';

16 高级联结

看到表数据，第一反应是必须有相同id下自连接的数据模型，这是许多复杂sql 的基础

SELECT* FROMproducts AS p1,products AS p2 WHEREp1.vend_id = p2.vend_id AND p1.prod_name = '1 ton anvil' ORDER BYp1.vend_id,p1.prod_id;

学会取别名不仅作用于where字句，select列表，order by字句以及语句的其他部分

# 给列取别名SELECTCONCAT( Rtrim( vend_name ), ' (', Rtrim( vend_country ), ')' ) AS vend_title FROMvendors ORDER BYvend_name;# 给表取别名SELECTcust_name,cust_contact FROMcustomers as c,orders as o ,orderitems as oi WHEREc.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2';

联结查询，比子查询快很多，应该习惯使用联结查询

# 找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品# 子查询法,速度是最慢的,尽量少使用SELECTprod_id,prod_name FROMproducts WHEREvend_id = ( SELECT vend_id FROM products WHERE prod_id = 'DTNTR' );# 自连接法,速度最快SELECTp1.prod_id,p1.prod_name FROMproducts AS p1,products AS p2 WHERE# 逻辑:相同供应商下,prod_id指定为xx出来p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

自然联结:对某个表使用通配符，其他表指定没有产生重复的列，所以不会报错

# 自然联结：通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来SELECTc.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_priceFROMcustomers as c,orders as o ,orderitems as oi WHEREc.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';

联结产生null的问题：区分inner join，left join 的区别

# inner join:只返回两张表都有的数据,不会出现nullSELECTcustomers.cust_id,orders.order_num FROMcustomersINNER JOIN orders ON customers.cust_id = orders.cust_id;# left join:以左边表所有字段都返回，关联右边表没有的数据显示为nullSELECTcustomers.cust_id,orders.order_num FROMcustomersLEFT JOIN orders ON customers.cust_id = orders.cust_id;

内部联结和外部联结的练习：检索所有客户及每个客户所下的订单数，是使用inner join 还是lfet join呢？

# inner join内部联结SELECTcustomers.cust_name,customers.cust_id,count( orders.order_num ) AS num_ord FROMcustomersINNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BYcustomers.cust_id;# left outer join = left join外部联结SELECTcustomers.cust_name,customers.cust_id,count( orders.order_num ) AS num_ord FROMcustomersleft outer join orders ON customers.cust_id = orders.cust_id GROUP BYcustomers.cust_id;

17 组合查询

union规则：允许将多个select查询出的结果集作为单个结果集返回

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

# union:可以将多个查询汇集到同一个结果集中SELECTvend_id,prod_id,prod_price FROMproducts WHEREprod_price <= 5 # union连接UNIONSELECTvend_id,prod_id,prod_price FROMproducts WHEREvend_id IN ( 1001, 1002 );# union与where多条件查询相同SELECTvend_id,prod_id,prod_price FROMproducts WHEREprod_price <= 5 OR vend_id IN ( 1001, 1002 );

# union all:重复行多次显示,不会合并# union :默认合并重复的行SELECTvend_id,prod_id,prod_price FROMproducts WHEREprod_price <= 5 # union allUNION ALLSELECTvend_id,prod_id,prod_price FROMproducts WHEREvend_id IN ( 1001, 1002 );

# union排序:使用order by是针对union所以组合的表的SELECTvend_id,prod_id,prod_price FROMproducts WHEREprod_price <= 5 # unionUNIONSELECTvend_id,prod_id,prod_price FROMproducts WHEREvend_id IN ( 1001, 1002 ) ORDER BYvend_id,prod_price;

18 全文本搜索

全文本搜素使用说明：

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

# 全文本搜索仅支持MyISAM引擎SELECTnote_text FROMproductnotes WHERE#match:要搜索的列,against:要匹配的文本MATCH ( note_text ) against ( 'rabbit' );# 虽然like模糊查询也可实现但排序是随机的，全文本匹配可以按照文本匹配程度由高到底排序SELECTnote_text FROMproductnotes WHEREnote_text LIKE '%rabbit%';# 查询扩展:不止全文本匹配除你想的文本，还搜索出可能与之相关的行SELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( 'anvils' );SELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( 'anvils' WITH QUERY expansion );# 布尔操作符# 没有使用布尔操作符SELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( 'heavy' IN boolean MODE );# 使用布尔操作符：-排除，*截断(=通配)SELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( 'heavy -rope*' IN boolean MODE );# +必须包含:说明同时包含rabbit baitSELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( '+rabbit +bait' IN boolean MODE );# 没有+,说明rabbit bait中至少一个都行SELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( 'rabbit bait' IN boolean MODE );# "rabbit bait"是匹配短语rabbit baitSELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( '"rabbit bait"' IN boolean MODE );# >:增加等级值，<:减少等级值SELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( '>rabbit <bait"' IN boolean MODE );# ();把词组成子表达式，允许这些子表达式作为一个组被包含、排除、排列等SELECTnote_text FROMproductnotes WHEREMATCH ( note_text ) against ( '+safe +(<combination)"' IN boolean MODE );

19 插入数据

# 未指定列名的插入:是不安全的，不推荐这么插入INSERT INTO customersVALUES( 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' );

insert + select操作:没有了values

# 创建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@' );# insert select操作，将一条查询语句结果插入到另一个表中,注意是所有查询的数据全部插入INSERT INTO customers ( cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) SELECTcust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country FROMcustnew;

20 更新和删除数据

更新和删除的指导原则

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

# update更新语句要与where搭配使用，否则将更新整个列的数据UPDATE customers SET cust_email = 'elmer@' WHEREcust_id = 10005;# 更新多个列UPDATE customers SET cust_name = 'The Fudds',cust_email = 'elmer@' WHEREcust_id = 10005;# delete删除也得搭配where使用,否则也是删除全部数据 # 如果想表中删除所有行,用delete;删除原来的表并重新创建一个,用truncate tableDELETE FROMcustomers WHEREcust_id = 10006;

21 创建和操作数据库表

使用auto_increment：

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

引擎类型：

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

# 创建表：create table tableCREATE TABLE customers(# AUTO_INCREMENT每个表只能有一个，并且必须被索引cust_idint 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_zipchar(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 intNOT NULL ,order_item intNOT NULL ,prod_id char(10)NOT NULL ,quantity intNOT NULL default 1,# 不指定插入默认是1item_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 textNULL ,PRIMARY KEY(note_id),FULLTEXT(note_text)) ENGINE=MyISAM;# 指定引擎为MyISAM

# 更新表alter table xx# 增加一列ALTER TABLE vendors ADD vend_phone CHAR ( 20 );# 删除一列ALTER TABLE vendors DROP COLUMN vend_phone;# 修改一列ALTER TABLE vendors MODIFY COLUMN vend_id INT ( 255 );# 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 使用视图

视图：相当于对一个较复杂的sql进行了一层包装 = 隐藏复杂sql,下次直接使用这个封装调用结果 = 简化复杂sql

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

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

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

drop view删除视图

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

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

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

# 第15章的多表联结查询，可以使用视图将它封装SELECTcust_name,cust_contact FROMcustomers,orders,orderitems WHEREcustomers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num AND prod_id = 'TNT2';# 创建视图,CREATE VIEW tablename AS + 原先的复杂sqlCREATE VIEW productcustomers AS SELECTcust_name,cust_contact, prod_id FROMcustomers,orders,orderitems WHEREcustomers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;# 使用之前创建的视图productcustomersSELECTcust_name,cust_contact FROMproductcustomers WHEREprod_id = 'TNT2';# 视图另一个重要作用:可以便于重新格式化之前的数据CREATE VIEW vendorlocations AS # 第10章这条sql结果如果需要反复使用,可以使用视图SELECTCONCAT( Rtrim( vend_name ), ' (', Rtrim( vend_country ), ')' ) AS vend_title FROMvendors ORDER BYvend_name;# 调用刚才创建的视图vendorlocationsSELECT* FROMvendorlocations;# 创建视图过滤不想要的数据CREATE VIEW customeremaillist AS SELECTcust_id,cust_name,cust_email FROMcustomers WHEREcust_email IS NOT NULL;SELECT* FROMcustomeremaillist;# 创建视图，保存计算结果，便于where使用CREATE VIEW orderitemsexpanded AS SELECTorder_num,prod_id,quantity,item_price,quantity * item_price AS expanded_price FROMorderitems;SELECT* FROMorderitemsexpanded WHEREorder_num = 20005;

23 使用存储过程

# 初识存储过程CREATE PROCEDURE productpricing () BEGINSELECTavg( prod_price ) FROMproducts;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 ) ) BEGINSELECTmin( prod_price ) INTO pl FROMproducts;SELECTmax( prod_price ) INTO ph FROMproducts;SELECTavg( prod_price ) INTO pa FROMproducts;END;# mysql变量都以@开头，是一段临时存储的数据# 调用存储过程，用三个变量接受CALL productpricing ( @pricelow, @pricehigh, @priceavg );# 使用这三个变量SELECT@pricelow,@pricehigh,@priceavg ;# IN:相当于函数参数的定义CREATE PROCEDURE ordertotal ( IN onumber INT, OUT ototal DECIMAL ( 8, 2 ) ) BEGINSELECTsum( item_price * quantity ) FROMorderitems WHEREorder_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# 定义临时变量totalDECLARE total DECIMAL ( 8, 2 );# 税率为6个点DECLARE taxrate INT DEFAULT 6;# 查询出无税收时订单总价SELECTsum( quantity * item_price ) FROMorderitems WHEREorder_num = onumber INTO total;# 如果有税收,算出税收总价IFtaxable THENSELECTtotal +(total / 100 * taxrate ) INTO total;END IF;# 临时变量传递给结果参数SELECTtotal 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;# 创建游标DECLAREordernumbers CURSOR FOR SELECTorder_num FROMorders;# 开启游标OPEN ordernumbers;# 使用游标FETCH ordernumbers INTO o;# 返回数据给函数参数SELECTo INTO outnumber;# 关闭游标CLOSE ordernumbers;END;CALL processorders ( @outnumber );SELECT@outnumber;

25 触发器

为什么要使用触发器？

要某条语句（或某些语句）在事件发生时自动执行

触发器只对insert、delete、update有用

mysql5以后不支持触发器返回结果，自己手动添加一个变量@变量名，返回该结果可以看到触发器结果；

初识触发器：

# 初识触发器：当每次往products表中添加一行数据时,列为addPro记录一条“Product added”CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROWSELECT'Product added' INTO @addPro;INSERT INTO products ( prod_id, vend_id, prod_name, prod_price, prod_desc )VALUES( "ANV0111", "1001", "3 ton anvil", 2.33, "good" );SELECT@addPro;

insert触发器：

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

# insert触发器：可以访问一个虚拟表NEWCREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW# insert有一个虚拟的NEW表,表示访问被插入的那一行# NEW.order_num:访问那一行的order_num列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 ROWBEGIN# 任意订单被删除前将执行此触发器。使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive orders的存档表中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 ROWSET new.vend_state = upper( new.vend_state ) INTO @updatevenderRes;

触发器注意事项：

与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

26 管理事务处理

事务(transaction)：指一组sql语句回退(rollback):指撤销指定sql的过程提交(commit)：值将未存储的sql语句结果写入数据库表保留点(savepoint)：指事务处理设置中的临时占位符(place-holder，可以对它发布回退)

# 开启事务:学习回滚START TRANSACTION;SELECT* FROMorderitems;DELETE FROMorderitems;SELECT* FROMorderitems;# 回退事务,TRANSACTION之后的语句全部不执行ROLLBACK;SELECT* FROMorderitems;# 开启事务:学习commitSTART TRANSACTION;DELETE FROMorderitems WHEREorder_num = 20010;DELETE FROMorders WHEREorder_num = 20010;# 事务块内，commit提交一段sql，前提是没报错COMMIT;# 开启事务:设置保留点START TRANSACTION;# 设置一个事务的保留点()SAVEPOINT deletel;# 如果出现问题，就回退到设置的保留点ROLLBACK TO deletel;# autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。# autocommit标志是针对每个连接而不是服务器的。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* FROMcustomers ORDER BYcust_name COLLATE utf8_general_ci;

28 安全管理

# mysql的用户权限，放在mysql库(默认的一个库)里的user表(默认有的一个表)USE mysql;SELECT* FROMUSER;# 创建一个数据库用户:ben # 账户:ben# 密码:passwordCREATE USER ben IDENTIFIED BY 'password';# 但是这样创建的用户没有任何权限,查询一下就知道SELECT* FROMUSER;# 重命名数据库用户名RENAME USER ben TO ben1;RENAME USER ben1 TO ben;# 查看ben用户的权限SHOW GRANTS FOR ben;# 给ben用户添加learn_mysql库的查询权限GRANT SELECT ONlearn_mysql.* TO ben;# 撤销ben用户查询权限REVOKE SELECT ONlearn_mysql.* TO ben;# 修改用户密码，但是必须有权限才行SET PASSWORD FOR ben = PASSWORD ( 'password1' );# 不指定用户名，默认是修改本次登录的用户的密码SET PASSWORD = PASSWORD ( 'password1' );