MySQL必知必会学习笔记
第3章 使用MySQL
-
mysql -u ben -p -h mysql -P 9999
登录MySQL -
show databases;
查看所有数据库 -
use book;
进入book 数据库 -
show tables;
查看所有数据表 -
show columns from customers;
desc customers;
两种查看表结构的方法 -
show create table customers;
查看表的创建语句
第4章 检索数据
-
select prod_name from products;
检索单个列 -
select prod_id, prod_name, prod_price from products;
检索多个列 -
select * from products;
检索所有列 -
select distinct vend_id from products;
检索不同的行 -
select prod_name from products limit 5;
限制结果，limit 5 表示返回不多于5行 -
select prod_name from products limit 5, 5;
限制结果，limit 5,5 表示返回从行5开始的5行
注意，第一行为行0 -
select products.prod_name from products;
select products.prod_name from crashcourse.products;
使用完全限定的表名
第5章 排序检索数据
-
select prod_name from products order by prod_name;
对prod_name列以字母排序 -
select prod_id, prod_price, prod_name from products order by prod_price, prod_name;
按多个列排序，仅在多个行具有相同的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章 过滤数据
-
select prod_name, prod_price from products where prod_price=2.50;
where子句在表名之后给出
在同时使用order by和where子句时，应将order by位于where之后 -
select prod_name, prod_price from products where prod_name='fuses';
MySQL在执行匹配时默认不区分大小写，所以fuses与Fuses匹配
单引号用来限定字符串 -
select prod_name, prod_price from products where prod_price<10;
where子句"<"小于操作符 -
select vend_id, prod_name from products where vend_id <> 1003;
select vend_id, prod_name from products where vend_id != 1003;
不匹配检查
数值列不用引号 -
select prod_name, prod_price from products where prod_price between 5 and 10;
范围值检查 -
select prod_name from products where prod_price is null;
空值检查
第7章 数据过滤
-
select prod_id, prod_price, prod_name from products where vend_id=1003 and prod_price<=10;
and操作符 -
select prod_name, prod_price from products where vend_id=1002 or vend_id=1003;
or操作符 -
select prod_name, prod_price, vend_id from products where vend_id=1002 or vend_id=1003 and prod_price>=10;
select prod_name, prod_price, vend_id from products where (vend_id=1002 or vend_id=1003) and prod_price>=10;
优先处理and操作符 -
select prod_name, prod_price from products where vend_id in (1002, 1003) order by prod_name;
in操作符 -
select prod_name, prod_price from products where vend_id=1002 or vend_id=1003 order by prod_name;
与上面的SQL等效
in操作符一般比or操作符执行更快 -
select prod_name, prod_price from products where vend_id not in (1002, 1003) order by prod_name;
not操作符
第8章 用通配符进行过滤
-
select prod_id, prod_name from products where prod_name like 'jet%';
%通配符，表示任何字符出现任意次数 -
select prod_id, prod_name from products where prod_name like '%anvi%';
select prod_id, prod_name from products where prod_name like 's%e';
通配符可在搜索模式中任意位置使用，并且可以使用多个通配符
注意，除了一个或多个字符外，%还能匹配0个字符
where prod_name like '%' 不能匹配用值NULL作为产品名的行 -
select prod_id, prod_name from products where prod_name like '_ ton anvil';
_下划线只匹配单个字符而不是多个字符 -
注意，不要过度使用通配符，通配符搜索花费时间长。
在确实需要使用通配符时，除非绝对必要，否则不要把它们用在搜索模式的开始处
第9章 用正则表达式进行搜索
-
select prod_name from products where prod_name regexp '.000' order by prod_name;
.表示匹配任意一个字符 -
注意区分：
select prod_name from products where prod_name like '1000' order by prod_name;
select prod_name from products where prod_name regexp '1000' order by prod_name;
LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不会被返回
REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将不会找到它，相应的行会被返回 -
MySQL中的正则表达式匹配不区分大小写
若要区分大小写，则要用BINARY关键字，如 WHERE prod_name REGEXP BINARY 'JetPack .000'; -
select prod_name from products where prod_name regexp '1000|2000' order by prod_name;
or匹配 -
select prod_name from products where prod_name regexp '[123] Ton' order by prod_name;
匹配几个字符之一，[]是另一种形式的OR语句，'[123] Ton' 是'[1|2|3] Ton' 的缩写，跟'1|2|3 Ton' 不是等效的
[123]，表示否定，将匹配除指定字符外的任何东西 -
select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;
匹配1到5 -
select vend_name from vendors where vend_name regexp '\\.' order by vend_name;
匹配特殊字符串，两个反斜杠，MySQL自己解释一个，正则表达式库再解释另一个 -
select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)' order by prod_name;
?表示0个或1个匹配，等于{0,1}，s后面的?使s可选 -
select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name;
[:digit:]匹配任意数字
等效于：
select prod_name from products where prod_name regexp '[0-9][0-9][0-9][0-9]' order by prod_name;
select prod_name from products where prod_name regexp '[0-9]{4}' order by prod_name; -
select prod_name from products where prod_name regexp '^[0-9\\.]' order by prod_name;
^匹配文本的开始，$匹配文本的结尾 -
注意：LIKE匹配整个串，REGEXP匹配子串
第10章 创建计算字段
-
select Concat(vend_name, ' (', vend_country, ')') from vendors order by vend_name;
Concat()拼接字段，name(country) -
select Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') from vendors order by vend_name;
RTrim()去掉值右边所有空格，LTrim()去掉值左边所有空格，Trim()去掉值左右两边所有空格 -
select Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') as vend_title from vendors order by vend_name;
使用别名 -
select prod_id, quantity, item_price,
quantity*item_price as expanded_price
from orderitems where order_num=20005;
执行算术计算
第11章 数据处理函数
-
select vend_name, Upper(vend_name) as vend_name_upcase from vendors order by vend_name;
Upper() 将文本转换为大写 -
select cust_name, cust_contact from customers where Soundex(cust_contact) = Soundex('Y Lie');
Soundex() 匹配发音类似 -
select cust_id, order_num from orders where order_date = '2005-09-01';
日期的格式必须为yyyy-mm-dd，如2005年9月1日，给出为2005-09-01 -
select cust_id, order_num from orders where Date(order_date) = '2005-09-01';
更靠谱的方法是用Date()，提取出列的日期部分，Time()，则对应时间 -
select cust_id, order_num from orders where Date(order_date) between '2005-09-01' and '2005-09-30';
select cust_id, order_num from orders where Year(order_date) = 2005 and Month(order_date)=9;
两种方法来检索2005年9月的所有订单
第12章 汇总数据
-
select avg(prod_price) as avg_price from products;
select avg(prod_price) as avg_price from products where vend_id=1003;
avg(列)，求此列平均值，avg()会忽略值为NULL的行 -
select count(*) as num_cust from customers;
select count(cust_email) as num_cust from customers;
count(*)对表中行的数目进行计数，不管列表中是否包含空值(NULL)
count(column)对特定列中具有值的行进行计数，忽略NULL值 -
select max(prod_price) as max_price from products;
max(column)返回指定列中的最大值，要求指定列名
max()忽略列值为NULL的行
在用于文本数据时，如果数据按相应的列排序，则max()返回最后一行 -
select min(prod_price) as min_price from products;
min(列)返回最小值，忽略列值为NULL的行 -
select sum(quantity) as items_ordered from orderitems where order_num = 20005;
sum(列)求和，忽略列值为NULL的行 -
select avg(distinct prod_price) as avg_price from products where vend_id = 1003;
distinct，只考虑不相同的值 -
select count(*) as num_items,
min(prod_price) as price_min,
max(prod_price) as price_max,
avg(prod_price) as price_avg
from products;
组合聚合函数
第13章 分组数据
-
select vend_id, count(*) as num_prods from products group by vend_id;
GROUP BY分组，GROUP BY必须出现在WHERE子句之后，ORDER BY子句之前，
如果分组列中具有NULL值，则NULL将作为一个分组返回
GROUP BY仅在按组计算聚集时使用 -
select vend_id, count(*) as num_prods from products group by vend_id with rollup;
WITH ROLLUP关键字，在分组的统计数据的基础上再进行相同的统计（SUM,AVG,COUNT…） -
select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;
找出至少有两个订单的所有顾客 -
WHERE过滤行，HAVING过滤分组
HAVING支持所有WHERE操作符
WHERE在数据分组前进行过滤
HAVING在数据分组后进行过滤
WHERE排除的行不包括在分组中 -
select vend_id, count() as num_prods from products where prod_price >= 10 group by vend_id having count() >= 2;
找出具有至少2个、价格为10（含）以上的产品的供应商 -
select order_num, sum(quantityitem_price) as ordertotal from orderitems group by order_num having sum(quantityitem_price) >= 50
order by ordertotal;
一般在使用GROUP BY子句时，应该也给出ORDER BY子句，这是保证数据正确排序的唯一方法
第14章 使用子查询
-
select cust_id from orders where order_num in (
select order_num from orderitems where prod_id='TNT2');
在SELECT语句中，子查询总是从内向外处理
在实际使用时由于性能的限制，不能嵌套太多的子查询
在WHERE子句中使用子查询，应保证SELECT语句具有与WHERE子句中相同数目的列 -
select cust_name, cust_state,
(select count(*) from orders where orders.cust_id=customers.cust_id) as orders
from customers order by cust_name;
显示customers表中每个客户的订单总数
第15章 联结表
-
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 inner join products
on vendors.vend_id = products.vend_id;
内部联结，联结条件用ON子句 -
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;
多表联结，显示编号为20005的订单中的物品相关信息
多表联结非常耗费资源，不要联结不必要的表
此例也可以用子查询实现
第16章 创建高级联结
-
select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') as vend_title from vendors order by vend_name;
使用列别名 -
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 = 'YNY2';
使用表别名
表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机 -
select prod_id, prod_name
from products where vend_id = (
select vend_id from products where prod_id='DTNTR');
假如发现某物品（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';
自然联结，此例中通配符只对第一个表使用，所有其他列明确列出，所以没有重复的列被检索出来 -
select customers.cust_id, orders.order_num
from customers left outer join orders
on customers.cust_id = orders.cust_id;
左外部联结，从左边的表中选择所有行
检索所有用户，包括没有订单的客户 -
select customers.cust_id, orders.order_num
from customers right outer join orders
on customers.cust_id = orders.cust_id;
右外部联结，从右边的表中选择所有行 -
select customers.cust_name,
customers.cust_id,
count(orders.order_num) as num_order
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_order
from customers left outer join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id;
检索所有客户，包括没下过单的
第17章 组合查询
-
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指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集 -
select vend_id, prod_id, prod_price from products where prod_price <= 5 or vend_id in (1001, 1002);
与上一例等效 -
UNION必须由两条或两条以上的SELECT语句组成
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型，如不同的数值类型或不同的日期类型 -
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会从查询结果集中自动去除重复的行
UNION ALL返回所有匹配的行 -
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;
在使用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后
第18章 全文本搜索
-
引擎MyISAM支持全文本搜索，而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;
启用全文本搜索支持，指定FULLTEXT
不要在导入数据时使用FULLTEXT，应该首先导入所有数据，然后再修改表，定义FULLTEXT，这样有助于更快地导入数据 -
select note_text from productnotes
where Match(note_text) Against('rabbit');
Match()指定被搜索的列，Against()指定要使用的搜索表达式
传给Match()的值必须与FULLTEXT()定义中的相同，如果指定多个列，则必须列出它们，且次序正确
除非使用BINARY方式，否则全文本搜索不区分大小写 -
select note_text from productnotes where note_text like '%rabbit%';
LIKE以不特别有用的顺序返回数据
全文搜索会对结果排序，较高等级的优先返回 -
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);
布尔文本搜索，即使没有FULLTEXT索引也可以使用 -
select note_text from productnotes
where Match(note_text) Against('heavy -rope*' in boolean mode);
匹配heavy，但排除包含rope的行 -
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中的至少一个词的行 -
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);
匹配rabbit和bait，增加前者的等级，降低后者的等级 -
select note_text from productnotes
where Match(note_text) Against('+safe +(<combination)' in boolean mode);
搜索匹配词safe和combination，降低后者的等级
第19章 插入数据
-
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);
VALUES必须以其指定的次序匹配指定的列名
一般不要使用没有明确给出列的列表的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');
插入多个行
MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快 -
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;
插入检索出的数据
使用INSERT SELECT从custnew中将所有数据导入到customers
第20章 更新和删除数据
-
update customers set cust_email = 'elmer@fudd.com'
where cust_id = 10005;
SET命令用来将新值赋给被更新的列
UPDATE语句以WHERE子句结束，告诉MySQL更新哪一行 -
update customers
set cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
where cust_id = 10005;
更新多个列 -
update customers set cust_email =null
where cust_id = 10005;
为了删除某个列的值，可设置它为NULL（前提表定义允许NULL值） -
delete from customers where cust_id = 10006;
删除表中的某一行
DELETE不需要列名和通配符
DELETE删除整行而不是删除列
为了删除指定的列，请使用UPDATE语句
DELETE不删除表本身
如果省略了WHERE子句，UPDATE和DELETE将被应用到表中所有行
如果想从表中删除所有行，可使用TRUNCATE TABLE语句，速度更快
第21章 创建和操作表
-
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(50) null,
cust_zip char(50) null,
cust_country char(50) null,
cust_contact char(50) null,
cust_email char(50) null,
primary key (cust_id)
)engine = InnoDB;
创建表
在创建新表时，指定的表名必须不存在，否则将出错
如果仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS
NULL值是没有值，不是空串
每个表值允许一个AUTO_INCREMENT列，而且它必须被索引 -
create table orderitems
(
order_num int not null,
order_item int not null,
prod_id char(10) not null,
quantity int not default 1,
item_price decimal(8,2) not null,
primary key (order_num, order_item)
)engine=InnoDB;
指定默认值
MySQL不允许使用函数作为默认值，只支持常量 -
InnoDB是可靠的事务处理引擎，但不支持全文本搜索
MEMORY功能等同于MyISAM，数据存储在内存中，速度快，特别适合于临时表
MyISAM性能极高，支持全文本搜索，但不支持事务处理
外键不能跨引擎 -
alter table vendors add vend_phone char(20);
更新表，增加列 -
alter table vendors drop column vend_phone;
更新表，删除列 -
alter table orderitems
add constraint fk_orderitems_orders
foreign key (order_num) references orders (orde_num);
定义外键
语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名) -
drop table customers2;
删除表 -
rename table customers2 to customers;
重命名表
第22章 使用视图
-
create view productcustomers as
select cust_name, cust_contact, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;
创建视图，联结3个表
视图是虚拟的表，视图本质就是对查询的一个封装 -
select cust_name, cust_contact
from productcustomers where prod_id = 'TNT2';
从视图中检索特定数据
