Mysql必知必会中的SQL示例

SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses'
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;
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;
SELECT cust_id FROM customers WHERE cust_email IS NULL;
SELECT * FROM customers WHERE cust_email <> "";
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id = 1003 AND prod_price <= 10;
SELECT prod_name, prod_price FROM products WHERE vend_id = 1003 OR vend_id = 1002;
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1003 OR vend_id = 1002) AND prod_price >= 10;
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 NOT IN (1002, 1003)
ORDER BY prod_name;
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%';
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] ton'
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] ton'
ORDER BY prod_name;
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\.'
ORDER BY vend_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\([0-9] sticks?\)'
ORDER BY prod_name;
SELECT CONCAT(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name;
SELECT CONCAT(vend_name, ' (', 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 ;
select vend_name, upper(vend_name) vend_name_upcase
from vendors
order by vend_name;
select cust_name, cust_contact
from customers
where soundex(cust_contact) = soundex('Y Lie');
select cust_id, order_num, order_date
from orders
where date(order_date) = '2005-09-01';
select cust_id, order_num, order_date
from orders
where order_date between '2005-09-01' and '2005-09-30';
SELECT cust_id, order_num, order_date
FROM orders
WHERE year(order_date) = 2005 and month(order_date) = 9;
select avg(prod_price) as avg_price
from products;
SELECT AVG(prod_price) AS avg_price
FROM products
where vend_id = 1003;
select count() num_cust
from customers;
SELECT COUNT(cust_email) num_cust
FROM customers;
select max(prod_price) max_price
from products;
SELECT min(prod_price) min_price
FROM products;
select sum(quantity) items_ordered
from orderitems
where order_num = 20005;
select sum(item_price * quantity) as total_price
from orderitems
where order_num = 20005;
select avg(distinct prod_price) avg_price
from products
where vend_id = 1003;
select count() num_items,
avg(prod_price) avg_price,
max(prod_price) max_price,
min(prod_price) min_price
from products;

select vend_id, count(*) as num_prods
from products
group by vend_id with rollup;

至少有两个订单的所有顾客

select cust_id, count()
from orders
group by cust_id
having count() >= 2;

具有2个（含）以上、价格为10（含）以上的产品的供应商：

select vend_id, count()
from products
where prod_price >=10
group by vend_id
having count() >= 2;

select order_num, sum(quantityitem_price) as order_total
from orderitems
group by order_num
having sum(quantityitem_price) >=50
order by order_total;

select order_num
from orderitems
where prod_id = 'TNT2';

select cust_id
from orders
where order_num in (SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2');

订购物品TNT2的所有客户

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表中每个客户的订单总数。

对客户10001的订单进行计数：

select count(*) as orders
from orders
where cust_id = 10001;

对每一个客户的订单进行计数(相关子查询)

select cust_name, cust_state,
(select count(*)
from orders
where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;

联结

select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

笛卡儿积

SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;

订购物品TNT2的所有客户

SELECT cust_name, cust_contact
FROM customers
INNER JOIN orders
ON customers.cust_id = orders.cust_id
INNER JOIN orderitems
ON orderitems.order_num = orders.order_num
WHERE prod_id = 'TNT2';

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到

生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

1子查询

select prod_name
from products
where vend_id = (SELECT vend_id
FROM products
WHERE prod_id = 'DTNTR');

2自联结

select p1.prod_name,p1.vend_id
from products p1
join products p2
on p1.vend_id = p2.vend_id
where p2.prod_id = 'DTNTR';

检索所有客户及其订单：

select customers.*, orders.order_num
from customers
left join orders
on customers.cust_id = orders.cust_id;

检索所有客户及每个客户所下的订单数

select c.cust_name, count(o.order_num) as num_ord
from customers as c
left join orders as o
on c.cust_id = o.cust_id
group by c.cust_id

联合查询

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);

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);

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)
order by vend_id, prod_price;

全文本搜素

select note_text
from productnotes
where match(note_text) against('rabbit')

为演示排序如何工作，请看以下例子：

select note_text,
match(note_text) against('rabbit') as rank
from productnotes;

查询扩展

首先进行一个简单的全文本搜索，没有查询扩展

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);

了匹配包含heavy但不包含任意以rope开始的词的行，可使用以下查询：

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);

插入数据

插入完整的行

insert into
customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country)
values('Pep E. LaPew',
null,
null,
'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. Martian',
'42 Galaxy Way',
'New York',
'NY',
'11213',
'USA');

插入检索出的数据

insert 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;

更新数据

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;

删除某个列的值

update customers
set cust_email = null
where cust_id = 10005;

删除数据

delete from
customers
where cust_id = 10006;

创建表

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 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;

更新表

添加列

alter table vendors
add vend_phone char(20);

删除刚刚添加的列

alter table vendors
drop column vend_phone;

ALTER TABLE的一种常见用途是定义外键

alter table orderitems
add constraint fk_orderitems_orders
foreign key (order_num) references orders (order_num);

alter table products
add constraint fk_products_vendors
foreign key (vend_id) references vendors (vend_id);

alter table orders
add constraint fk_orders_customers
foreign key (cust_id) references customers (cust_id);

删除表

drop table customers2;

重命名表

rename table customers2 to customers;

视图

创建视图（虚拟的表）

create view productcustomers as
select cust_name, cust_contact, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orders.order_num = orderitems.order_num;

使用视图 极大地简化了复杂SQL语句的使用。

select cust_name, cust_contact
from productcustomers
where prod_id = 'TNT2';

用视图重新格式化检索出的数据

create view vendorlocations as
select concat(Rtrim(vend_name), ' (', ltrim(vend_country), ')')
as vend_title
from vendors
order by vend_name;

select *
from vendorlocations;