本文为《MySQL必知必会》[1]读书笔记,用于总结知识点和框架,仅供参考和交流,如有不妥请联系。由于软件版本更新,书中的一些代码已经不再适用,本文主要从SQL基本语句进行增删减、窗口函数等操作。使用的软件版本:MySQL Server 8.0、Navicat Premium12.1[2]
博文推荐:
专栏:SQL学习笔记(作者:圣洁不吃冰淇淋):牛客网SQL实战、窗口函数、面试
面试:MySQL数据库面试题(最新版)题库:
牛客网:数据库实战
leetcode:数据库学习前准备:
01 MySQL service的启用[3]:
进入mysql编辑状态::mysql -uroot -p
02 创建、删除数据库、查看所有的数据库:
创建库: create database if not exists 库名;
删除库:drop database 库名;
查看:show databases;
03 在Navicat Premium中执行sql语句 [4]
(1)用命令列界面执行sql语句
单击右键【数据库】—>【命令列界面】即可出现mysql命令执行窗口。创建表和修改表
(2)进入查询状态执行sql语句
进入连接【本地】—>点击要进行查询的数据库【test】—>【查询】—>【新建查询】
注:本书数据表的建表语句、数据语句参考[5],建议提前生成。总结(图片来源网络,出处不详):
执行顺序(来源:《MySQL技术内幕–SQL编程》)
(8)SELECT (9)DISTINCT<select_list>(1)FROM<left_table> (3)<join_type>JOIN<right_table>(2)ON<join_condition>(4)WHERE<where_condition>(5)GROUP BY<group_by_list>(6)WITH{CUBE|ROLLUP}(7)HAVING<having_condition>(10)ORDER BY<order_by_list>(11)LIMIT<limit_number>
目录
查找类第4章 检索数据4.1 - 4.4 检索列:select语句4.5 检索不同行:distinct关键字4.6 限制结果:limit子句4.7 使用完全限定的表名(同时使用表名和列名)第5章 排序检索数据5.1 - 5.2 排序数据:order by子句5.3 指定排序方向:desc关键词第6章 过滤数据6.1 where子句第7章 数据过滤7.1 组合where子句:and子句或or子句7.2 指定条件范围:in操作符7.3 找出条件列表不匹配的行:not操作符第8章 用通配符进行过滤8.1 like操作符8.1.1 百分号(%)通配符8.1.2 下划线(_)通配符第9章 用正则表达式进行搜索9.1-9.2 正则表达式9.2.1 基本字符匹配:regexp9.2.2 进行or匹配:|9.2.3 - 9.2.4 匹配字符和范围:[ ]9.2.5 匹配特殊字符:`\\`(匹配)9.2.6 匹配字符类:[:class:]9.2.7 匹配多个实例:* + ? {n} {n,} {n,m}9.2.8 定位元字符:`^ $ [[:<:]] [[:>:]]`第10章 创建计算字段10.1-10.2 计算字段、拼接字段:Concat()10.3 执行算术计算第11章 使用数据处理函数11.1-11.2 函数11.2.1 文本处理函数11.2.2 日期和时间处理函数11.2.3 数值处理函数第12章 汇总数据12.1 聚集函数(aggregate function)12.2 聚集不同值 distinct12.3 组合聚集函数:多行第13章 分组数据13.1-13.2 创建数据:group by子句13.3 过滤分组:having 子句13.4 分组和排序:group by与order by13.5 子句总结第14章 使用子查询(嵌套)14.1-14.2 利用子查询进行过滤14.3 作为计算字段使用子查询第15章 联结表15.1 - 15.2 创建联结第16章 创建高级联结16.1 使用表别名16.2 使用不同类型额联结16.2.1 自联结16.2.2 自然联结16.2.3 外部联结16.3 使用带聚集函数的联结第17章 组合查询17.1 - 17.2 组合查询 union第18章 全文本搜索18.1 - 18.2 使用全文本搜索18.2.1 启用全文本搜索:fulltext(note_text)18.2.2 进行全文本搜索:match()、against()18.2.3 使用查询扩展:with query expansion18.2.4 布尔文本搜索:in boolean mode数据操作类第19章 插入数据:insert19.3 插入多个行19.4 插入检索出来的数据第20章 更新和删除数据:update, delete第21章 创建和操纵表第22章 使用视图第23章 使用存储过程第24章 使用游标第25章 使用触发器查找类
第4章 检索数据
4.1 - 4.4 检索列:select语句
1、SQL语句不区分大小写,但为了便于阅读和调试,对所有SQL关键词使用大写,对所有列和表名使用小写。多条SQL语句必须以分号(;)分隔。
2、select
语句:检索单个、多个、所有列
SELECT prod_id,prod_price FROM products;SELECT * FROM products; #检索所有列
4.5 检索不同行:distinct关键字
distinct
关键字:应用于所有列而不仅是前置它的列。
SELECT DISTINCT vend_id FROM products;
4.6 限制结果:limit子句
1、limit
子句:返回
2、行0:检索出来的第一行为行0,而不是行1。如limit 1,1 检索的是第2行。
SELECT prod_name FROM products LIMIT 5,5;
4.7 使用完全限定的表名(同时使用表名和列名)
SELECT products.prod_name FROM crashcourse.products;
第5章 排序检索数据
5.1 - 5.2 排序数据: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;
5.3 指定排序方向:desc关键词
1、默认升序。
2、desc
关键词:指定按照降序排列。只应用到直接位于其前面的列名。
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
第6章 过滤数据
6.1 where子句
1、where子句应位于order by子句之前,否则会报错。
2、子句操作符:
不等于:<>、!=
在指定的两个值之间:between
3、单引号用来限定字符串,将值与串类型的列进行比较,需要限定引号。
#检查单个值SELECT prod_name, prod_price FROM products WHERE prod_name ='fuses';SELECT prod_name, prod_price FROM products WHERE prod_price <10;#不匹配检查SELECT ven_id, prod_name FROM products WHERE vend_id <>1003;#范围值检查SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;#空值检查SELECT cust_id FROM customers WHERE cust_email IS NULL;
第7章 数据过滤
7.1 组合where子句:and子句或or子句
1、优先处理and操作符,所以需要加括号。
SELECT prod_name, prod_price FROM productsWHERE (vend_id = 1002 OR vend_id =1003) AND prod_price >=10;
7.2 指定条件范围:in操作符
SELECT prod_name, prod_price FROM productsWHERE vend_id IN (1002,1003)ORDER BY prod_name;
7.3 找出条件列表不匹配的行:not操作符
MySQL支持使用 not 对 in、between、exists子句取反。
SELECT prod_name, prod_price FROM productsWHERE vend_id NOT IN (1002,1003)ORDER BY prod_name;
第8章 用通配符进行过滤
8.1 like操作符
1、利用通配符[2](用来匹配值的一部分的特殊字符)可创建比较特定数据的搜索模式。
2、搜索模式:由字面符、通配符或两者组合的搜索条件。
8.1.1 百分号(%)通配符
#以词jet开头SELECT prod_id, prod_name FROM productsWHERE prod_name LIKE 'jet%';#匹配任何位置包含SELECT prod_id, prod_name FROM productsWHERE prod_name LIKE '%anvil%';
8.1.2 下划线(_)通配符
_总是匹配一个字符,不能多也不能少。
#匹配任何位置包含SELECT prod_id, prod_name FROM productsWHERE prod_name LIKE '_anvil';
第9章 用正则表达式进行搜索
9.1-9.2 正则表达式
9.2.1 基本字符匹配:regexp
1、'.000'
是正则表达式语言中一个特殊字符,表示匹配任意一个字符。
2、like
与regexp
的区别[3]:
(1)LIKE匹配整个列。如果被匹配的文本仅在列值中出现,LIKE并不会找到它,相应的行也不会返回(当然,使用通配符除外)。而REGEXP在列值内进行匹配,如果被匹配的匹配的文本在列值中出现,REGEXP将会找到它,相应的行将被返回,这时一个非常重要的差别(当然,如果适应定位符号^和$,可以实现REGEXP匹配整个列而不是列的子集)。
3、关于大小写的区分:MySQL中正则表达式匹配(从版本3.23.4后)不区分大小写 。如果要区分大小写,应该使用BINARY
关键字,如where post_name REGEXP BINARY 'Hello .000'
SELECT prod_name FROM productsWHERE prod_name REGEXP '.000'ORDER BY prod_name;
9.2.2 进行or匹配:|
SELECT prod_name FROM productsWHERE prod_name REGEXP '2000|3000'ORDER BY prod_name;
9.2.3 - 9.2.4 匹配字符和范围:[ ]
1、[123]Ton=[1|2|3]Ton
2、匹配除指定字符外其他:[^123]
#匹配几个字符之一SELECT prod_name FROM productsWHERE prod_name REGEXP '[123]Ton'ORDER BY prod_name;#匹配范围SELECT prod_name FROM productsWHERE prod_name REGEXP '[1-5]Ton'ORDER BY prod_name;
9.2.5 匹配特殊字符:\\
(匹配)
1、\\.
能够匹配.
2、空白元字符:
\\f
:换页,\\n
:换行, \r:回车,\t:制表,\v纵向制表
SELECT vend_name FROM vendorsWHERE vend_name REGEXP '\\.'ORDER BY vend_name;
9.2.6 匹配字符类:[:class:]
[:alnum:] 任意字母和数字(通[a-zA-Z0-9]
[:alpha:] 任意字符(同[a-zA-Z])
[:blank:] 空格和制表符(同[\t])
[:digit:] 任意数字(同[0-9])
[:lower:] 任意小写字母
[:upper:] 任意大写字母
[:space:] 包括空格在内的任意空白字符
9.2.7 匹配多个实例:* + ? {n} {n,} {n,m}
(1)* 0个或多个匹配
(2) + 1个或多个匹配(等于{1,})
(3) ? 0个或1个匹配(等于{0,1})
(4){n} 指定数目的匹配
(5){n,} 不少于指定数目的匹配
(6) {n,m} 匹配数目的范围(m不超过255)
#匹配连在一起的4位数字SELECT prod_name FROM products WHERE prod_name REGEXP '[[:DIGIT:]]{4}' ORDER BY prod_name;
9.2.8 定位元字符:^ $ [[:<:]] [[:>:]]
^ 文本的开始
& 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
第10章 创建计算字段
10.1-10.2 计算字段、拼接字段:Concat()
1、删除多余空格
(1)删除数据左侧多余空格ltrim()
(2)删除数据右侧多余空格rtrim()
(3)删除数据两侧多余空格trim()
SELECT concat(vend_name,' (',vend_country,')') FROM vendors ORDER BY vend_name; SELECT concat(rtrim(vend_name),' (',rtrim(vend_country),')') FROM vendors ORDER BY vend_name;
2、 列别名 as
SELECT concat(rtrim(vend_name),' (',rtrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
10.3 执行算术计算
SELECT prod_id,quantity,item_price,quantity * item_price AS expanded_priceFROM orderitems WHERE order_num = 20005; # 计算总价expanded_price
第11章 使用数据处理函数
11.1-11.2 函数
11.2.1 文本处理函数
# left() 返回串左边的字符 # length() 返回串的长度 # locate() 找出串的一个子串 # lower() 将串转换为小写# ltrim() 去掉串左边的空格# right() 返回串右边的字符 # rtrim() 去掉串右边的空格 # soundex() 返回串的soundex值# substring() 返回子串的字符 # upper() 将串转换为大写SELECT vend_name, upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;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'); # 按发音搜索
11.2.2 日期和时间处理函数
1、首选的日期格式yyyy-mm-dd
,避免多义性[3]
2、坑点:order_date为datetime数据类型,含有时间信息;如果时间信息不是00:00:00,上句查找无结果,因此用date()
对日期进行过滤信息。
# adddate() 增加一个日期(天,周等)# addtime() 增加一个时间(时、分等)# curdate() 返回当前日期 # curtime() 返回当前时间 # date() 返回日期时间的日期部分# datediff() 计算两个日期之差 # date_add() 高度灵活的日期运算函数 # date_format() 返回一个格式化的日期或时间串 # day() 返回一个日期的天数部分# dayofweek() 对于一个日期,返回对应的星期几 # hour() 返回一个时间的小时部分 # minute() 返回一个时间的分钟部分 # month() 返回一个日期的月份部分 # now() 返回当前日期和事件 # second() 返回一个时间的秒部分 # time() 返回一个日期时间的时间部分 # year() 返回一个日期的年份部分 SELECT cust_id,order_num FROM orders WHERE Date(order_date) = "-09-01";# 检索9月下的订单 SELECT cust_id,order_num FROM orders WHERE Year(order_date) = AND Month(order_date) = 9;
11.2.3 数值处理函数
# abs() 返回一个数的绝对值# cos() 返回一个角度的余弦# exp() 返回一个数的指数值# mod() 返回除操作的余数# pi() 返回圆周率 # sin() 返回一个角度的正弦 # sqrt() 返回一个数的平方根 # tan() 返回一个角度的正切
第12章 汇总数据
12.1 聚集函数(aggregate function)
# avg() 返回某列的平均值,忽略列值位null的行。# count() 返回某列的行数 ,分为count()和count(*)# max() 返回某列的最大值 # min() 返回某列的最小值 # sum() 返回某列值之和,忽略列值位null的行。# COUNT(*)对表中行的数目进行计数,不忽略空值 SELECT Count(*) AS num_cust FROM customers; # 使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值SELECT Count(cust_email) AS num_cust FROM customers; # 在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行select max(prod_name) from products; # 在用于文本数据时,如果数据按相应的列排序,则MIN()返回最前面一行select min(prod_name) from products;
12.2 聚集不同值 distinct
distinct 只能作用于count(),不能用于count(*)
SELECT Avg(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
12.3 组合聚集函数:多行
SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS price_avgFROMproducts;
第13章 分组数据
13.1-13.2 创建数据:group by子句
1、group by
子句必须出现在where子句之后,order by 子句之前。
2、with rollup
关键字[1]:可以生成汇总值
# 按vend_id排序并分组数据select vend_id, count(*) as num_prods from products group by vend_id;# 使用WITH ROLLUP关键字,可以得到每个分组的汇总值和总值select vend_id, count(*) as num_prods from products group by vend_id with rollup;
13.3 过滤分组:having 子句
where
在数据分组前过滤,having
在数据分组后过滤。
# COUNT(*) >=2(两个以上的订单)的那些分组select cust_id, count(*) as orders from ordersgroup by cust_id having count(*)>=2;
-- where和having组合使用 #列出具有2个(含)以上、价格为10(含)以上的产品的供应商select vend_id,count(*) as num_prods from products where prod_price >=10 group by vend_id having count(*)>=2;#不加where条件,结果不同 SELECT vend_id,count(*) AS num_prods FROM productsGROUP BY vend_id HAVING count(*) >=2;
13.4 分组和排序:group by与order by
# 检索总计订单价格大于等于50的订单的订单号和总计订单价格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 orderitemsGROUP BY order_num HAVING SUM(quantity * item_price) >= 50ORDER BY ordertotal;
13.5 子句总结
第14章 使用子查询(嵌套)
14.1-14.2 利用子查询进行过滤
# 列出订购物品TNT2的所有客户SELECT cust_name, cust_contactFROM customersWHERE cust_id IN (SELECT cust_idFROM ordersWHERE order_num IN (SELECT order_numFROM orderitemsWHERE prod_id = 'TNT2'));
(1)第一表:orderitems
(2)第一层操作后:
(3)第二张表格:orders
(4)第二层操作后:
(5)第三张表格:customers
(6)第三层操作后:
14.3 作为计算字段使用子查询
# 显示customers 表中每个客户的订单总数SELECT cust_name,cust_state, (SELECT count(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS ordersFROM customers ORDER BY cust_name;
第15章 联结表
15.1 - 15.2 创建联结
1、笛卡尔积:由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表的行数。
SELECT vend_name,prod_name,prod_price FROM vendors,productsWHERE vendors.vend_id = products.vend_idORDER BY vend_name,prod_name;
2、内部联结: 表间相等测试
inner join
:两边表同时有对应的数据,即任何一边缺失数据就不显示。
SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN productsON vendors.vend_id = products.vend_id;
第16章 创建高级联结
16.1 使用表别名
SELECT cust_name,cust_contact FROM customers as c,orders as o,orderitems as oiWHERE c.cust_id = o.cust_idAND oi.order_num = o.order_numAND prod_id = 'TNT2';
16.2 使用不同类型额联结
16.2.1 自联结
处理联结比处理子查询快得多。
#方法:子查询 select prod_id,prod_name from productswhere vend_id = (select vend_id from products where prod_id = 'DTNTR');#方法:使用联结 select p1.prod_id,p1.prod_namefrom products as p1, products as p2where p1.vend_id = p2.vend_idand p2.prod_id = 'DTNTR';
16.2.2 自然联结
标准联结(如:内部联结)返回所有数据甚至相同的列多次出现,自然联结使每个列只返回一次。
事实上,迄今为止我们建立的每个内部联结都是自然联结,很可能永远都不会用到不是自然联结的内部联结。
#通配符只对第一个表使用,所有其他列明确列出。select c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_pricefrom customers as c,orders as o,orderitems as oiwhere c.cust_id = o.cust_idand oi.order_num = o.order_numand prod_id = 'FB';
16.2.3 外部联结
联结包含了那些在相关表中没有关联行的行,称为外部联结。
# 检索所有客户及其订单,包括那些没有订单的客户# 01 : 左外部联结select customers.cust_id,orders.order_numfrom customers left outer join orderson customers.cust_id = orders.cust_id;# 03: 使用右外部联结 调换两表位置select customers.cust_id,orders.order_numfrom orders right outer join customerson customers.cust_id = orders.cust_id;
16.3 使用带聚集函数的联结
# 检索所有客户分别对应的订单数,inner join select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ordfrom customers inner join orders on customers.cust_id = orders.cust_idgroup by customers.cust_id;
# 检索所有客户分别对应的订单数,包括没有订单的客户,left outer join select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ordfrom customers left outer join orders on customers.cust_id = orders.cust_idgroup by customers.cust_id;
第17章 组合查询
17.1 - 17.2 组合查询 union
1、union
可极大地简化复杂的where子句。
2、union
默认自动去除重复的行;union all,匹配所有行 ,不取消重复行。
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <=5UNIONSELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002)ORDER BY vend_id,prod_price;
第18章 全文本搜索
18.1 - 18.2 使用全文本搜索
18.2.1 启用全文本搜索:fulltext(note_text)
1、mysql中engine=innodb
和engine=myisam
的区别[1]
①ISAM
执行读取操作的速度很快,而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于,它不支持事务处理,也不能够容错。
②InnoDB
提供了事务控制能力功能,它确保一组命令全部执行成功,或者当任何一个命令出现错误时所有命令的结果都被回退,可以想像在电子银行中事务控制能力是非常重要的。支持COMMIT、ROLLBACK和其他事务特性。
CREATE TABLE productnotes(note_id int NOT NULL AUTO_INCREMENT,prod_id char(10) NOT NULL,note_data datetime NOT NULL,note_text text NULL,PRIMARY KEY(note_id),FULLTEXT(note_id))ENGINE=MyISAM;
18.2.2 进行全文本搜索:match()、against()
1、match()
指定被搜索的列,against()指定要使用的搜索表达式。
# match()、against()select note_text from productnotes where match(note_text) against('rabbit');# 如果用like语句 select note_text from productnotes where note_text like '%rabbit%';
2、排序:rank
由于保留字冲突,这里命名为rank1,以作区分。
select note_text, match(note_text) against('rabbit') as 'rank' from productnotes;
18.2.3 使用查询扩展:with query expansion
查询拓展功能不仅返回搜索值(第一列),也列出其他值。
select note_text from productnotes where match(note_text) against('anvils' with query expansion);
18.2.4 布尔文本搜索: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);# -rope* 排除包含rope*(任何以rope开始的词,包括ropes)的行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和carrot,增加前者的等级,降低后者的等级select note_text from productnotes where match(note_text) against('>rabbit <carrot' in boolean mode);# 必须匹配词safe和combination,降低后者的等级select note_text from productnotes where match(note_text) against('+safe +(<combination)' in boolean mode);
数据操作类
第19章 插入数据:insert
19.3 插入多个行
单条insert
语句有多组值,每组值用一对圆括号括起来,用逗号分隔。
# 方法1: 提交多个insert 语句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. Martian','42 Galaxy Way','New York','NY','11213','USA');# 方法2: 只要每条INSERT语句中的列名(和次序)相同,可以如下组合各语句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. Martian','42 Galaxy Way','New York','NY','11213','USA');
19.4 插入检索出来的数据
#注意select前面没有句号也没有valuesinsert into customers (cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)select cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from custnew;
第20章 更新和删除数据:update, delete
1、update
语句 : 删除或更新指定列
2、delete
语句:删除整行而不是某列
3、truncate table语句:如果想从表中删除 所有行,不要使用DELETE,可使用TRUNCATE TABLE语句,TRUNCATE实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据。
# 更新: 客户10005现在有了电子邮件地址UPDATE customers SET cust_email = 'elmer@' WHERE cust_id = 10005;# 更新: 多个列 UPDATE customers SET cust_name = 'The Fudds',cust_email = 'elmer@'WHERE cust_id = 10005;# 删除:指定为NULLupdate customers set cust_email = null where cust_id = 10005;# 从customers表中删除一行delete from customers where cust_id = 10006;
第21章 创建和操纵表
链接:《Mysql之创建和操纵表》1、建表:CREATE TABLE
如果省略ENGINE=语句,则使用默认引擎(很可能是MyISAM),多数SQL语句都会默认使用它。InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索;MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
CREATE TABLE students(stu_id INT NOT NULL AUTO_INCREMENT,stu_name CHAR(50) NOT NULL,stu_address CHAR(50) NULL,PRIMARY KEY(stu_id))ENGINE=INNODB
2、更新表:alter table
添加一列、删除一列、定义外键
ALTER TABLE orderitemsssADD vend_phone CHAR(20);DROP COLUMN vend_phone;#定义外键ADD CONSTRAINT fk_orderitemsss_ordersFOREIGN KEY(order_num) REFERENCES orders (order_num);
3、删除表:drop table
4、重命名表:rename table... to ...
第22章 使用视图
链接:《SQL必知必会——使用视图》1、视图为虚拟的表,包含的不是数据二十根据需要检索数据的查询。相当于编程语言中的函数调用。
2、视图的规则和限制
3、作用:
(1)利用视图简化复杂的联结
(2)用视图重新格式化检索出的数据
(3)用视图过滤不想要的数据
(4)使用视图的计算字段
CREATE VIEW ProductCustomers ASSELECT cust_name,cust_contact,prod_idFROM customers,orders,orderItemsWHERE customers.cust_id=orders.cust_idAND orderitems.order_num=orders.order_num;
第23章 使用存储过程
链接:《MySQL》必知必会存储过程,类比一个脚本,存储起来,每次直接调用,优点在于:简单、安全、高性能。
-- 执行存储过程CALL productpricing(@pricelow,@pricehigh,@priceaverage);-- 创建存储过程CREATE PROCEDURE productpricing()BEGINSELECT Avg(prod_price) AS priceaverageFROM products;END;-- 调用存储过程CALL productpricing();-- 删除存储过程DROP PROCEDURE productpricing;
第24章 使用游标
游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据做出浏览和更改。
链接:《MySQL》必知必会
CREATE PROCEDURE processorders()BEGINDECLARE done BOOLEAN DEFAULT 0;DECLARE o INT;DECLARE t DECIMAL(8, 2);DECLARE ordernumbers CURSORFORSELECT order_num FROM orders;DECLARE CONTINUE HANDLER FOR SQLTATE '02000' SET done=1;CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8, 2));OPEN ordernumbers;REPEATFETCH ordernumbers INTO o;CALL ordertotal(o, 1, t);INSERT INTO ordertotals(order_num, total)VALUES(o, t);UNTIL done END REPEAT;CLOSE ordernumbers;END;
第25章 使用触发器
触发器是根据条件自动执行语句。只支持DELETE,UPDATE,INSERT。最好在一个数据库中使用唯一的触发器名。
触发器只支持每个表的每个事件,每个表最多支持6个。
## 创建触发器CREATE TRIGGER newproduct AFTER INSERT ON productsFOR EACH ROW SELECT 'Product added';## 删除触发器DROP TRIGGER newproduct;## 使用触发器## INSERT触发器## 引用一个NEW虚拟的表,访问被插入行,类比this指针CREATE TRIGGER neworder AFTER INSERT ON ordersFOR EACH ROW SELECT NEW.order_num;## 通常将BEFORE用于数据验证和净化## DELETE 触发器## 引用一个OLD虚拟的表,访问被删除行,只读,不能更改CREATE TRIGGER deleteorder BEFORE DELETE ON ordersFOR EACH ROW BEGININSERT INTO archive_orders(order_num, order_date, cust_id)VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);END;## UPDATE触发器## BEFORE: NEW,AFTER: OLDCREATE TRIGGER updatevendor BEFORE UPDATE ON vendorsFOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);