mysql数据库增删改查
数据库
customers表
orderitems表
orders表
productnotes表
products表
vendors表
查找
查找单个列
select prod_name from products;
查找多个列
select prod_id, prod_name, prod_price from products;
查找所有列
select * from products;
查找不同的行 distinct
去掉值相同的行
select distinct vend_id from products;
限制行数 limit
带一个值的LIMIT总是从第一行开始,给出的数为返回的行数。 带两个值的LIMIT可以指定从行号为第一个值的位置开始。
select prod_name from products limit 5;
select prod_name from products limit 5, 5;
排序查找 order by
select vend_id from products order by vend_id;
按多个列进行排序
为了按多个列排序,只要指定列名,列名之间用逗号分开即可
select prod_id, prod_name, prod_price from products order by prod_price, prod_name;
指定排序方向 desc
select vend_id from products order by vend_id desc;
下面的例子以降序排序产品 (最贵的在最前面),然后再对产品名排序。DESC关键字只应用到直接位于其前面的列名。
select prod_id, prod_name, prod_price from products order by prod_price desc, prod_name;
过滤数据 where
注意:。SQL在处理OR操作符前,优先处理AND操 作符。
select prod_name, prod_price from products where prod_price = 2.5;
select prod_name, prod_price from products where prod_price between 5 and 10;
操作符
空值检查
select prod_name from products where prod_name is not null;
IN操作符
select prod_name, prod_price from products where vend_id in (1002, 1003) order by prod_price;
NOT操作符
select prod_name, prod_price from products where vend_id not in (1002, 1003) order by prod_price;
LIKE 操作符
百分号(%)通配符
%表示任何字符出现 任意次数
select prod_id, prod_name from products where prod_name like 'Jet%';
区分大小写 根据MySQL的配置方式,搜索可以是区分大小 写的。如果区分大小写,'jet%'与JetPack 1000将不匹配。
下划线(_)通配符
下划线的用途与%一样,但下划 线只匹配单个字符而不是多个字符。
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;
进行OR匹配(二者取其一)
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;
[ ^123 ]匹配除123这些字符外的任何东西
匹配范围
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 prod_name from products where prod_name regexp '^[0-9\\.]' order by prod_name;
计算字段
拼接字段 concat
select concat(vend_name, ' (', vend_country, ')') from vendors order by vend_name;
使用别名 as
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) as vend_name_upcase from vendors order by vend_name;
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换为小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX值 |
SubString() | 返回子串的字符 |
Upper() | 将串转换为大写 |
日期和时间处理函数
select cust_id, order_num from orders where Date(order_date) between '2005-9-1' and '2005-9-30';
数值处理函数
汇总数据
聚集函数
平均值 AVG
select avg(prod_price) as avg_price from products;
计数 COUNT
COUNT()函数有两种使用方式。
- 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空 值(NULL)还是非空值。
- 使用COUNT(column)对特定列中具有值的行进行计数,忽略 NULL值。
select count(*) as num_cust from customers;
select count(cust_email) as num_cust from customers;
最大值 MAX
select max(prod_price) from products as max_price;
最小值 MIN
select min(prod_price) from products as max_price;
求和 SUM
select sum(quantity) as items_ordered from orderitems where order_num = 20005;
聚集不同值
以上5个聚集函数都可以如下使用:
- 对所有的行执行计算,指定ALL参数或不给参数(因为ALL是默认 行为)
- 只包含不同的值,指定DISTINCT参数。
select avg(distinct prod_price) as avg_price from products where vend_id=1003;
组合聚集函数
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;
分组数据 group by
select vend_id, count(*) as num_prods from products group by vend_id;
过滤分组
select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;
分组和排序
select order_num, sum(quantity*item_price) as order_total from orderitems group by order_num having sum(quantity*item_price) >= 50 order by order_total;
select子句顺序
select -> from -> where -> group by -> having -> order by -> limit
子查询
本节所有语句
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;
select prod_name from products limit 5, 5;
select vend_id from products order by vend_id;
select prod_id, prod_name, prod_price from products order by prod_price, prod_name;
select vend_id from products order by vend_id desc;
select prod_id, prod_name, prod_price from products order by prod_price desc, prod_name;
select prod_name, prod_price from products where prod_price = 2.5;
select prod_name, prod_price from products where prod_price between 5 and 10;
select prod_name from products where prod_name is not null;
select prod_name, prod_price from products where vend_id in (1002, 1003) order by prod_price;
select prod_name, prod_price from products where vend_id not in (1002, 1003) order by prod_price;
select prod_id, prod_name from products where prod_name like 'Jet%';
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 prod_name from products where prod_name regexp '^[0-9\\.]' 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) as vend_name_upcase from vendors order by vend_name;
select cust_id, order_num from orders where Date(order_date) between '2005-9-1' and '2005-9-30';
select avg(prod_price) as avg_price from products;
select count(*) as num_cust from customers;
select count(cust_email) as num_cust from customers;
select max(prod_price) from products as max_price;
select min(prod_price) from products as max_price;
select sum(quantity) as items_ordered from orderitems where order_num = 20005;
select avg(distinct prod_price) as avg_price from products where vend_id=1003;
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;
select vend_id, count(*) as num_prods from products group by vend_id;
select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;
select order_num, sum(quantity*item_price) as order_total from orderitems group by order_num having sum(quantity*item_price) >= 50 order by order_total;
-- 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
-- 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_state,
-- ( SELECT count( * ) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders
-- FROM
-- customers
-- ORDER BY
-- cust_name;
-- SELECT
-- cust_name,
-- cust_state,
-- ( SELECT count( * ) FROM orders WHERE cust_id = 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;
-- SELECT
-- vend_name,
-- prod_name,
-- prod_price
-- FROM
-- vendors
-- INNER JOIN products ON vendors.vend_id = products.vend_id;
-- SELECT
-- vend_name,
-- prod_name,
-- prod_price,
-- quantity
-- FROM
-- vendors,
-- products,
-- orderitems
-- WHERE
-- vendors.vend_id = products.vend_id;
-- AND orderitems.prod_id = products.prod_id
-- AND order_num = 20005;
-- SELECT
-- cust_name,
-- cust_contact
-- FROM
-- customers,
-- orders,
-- orderitems
-- WHERE
-- customers.cust_id = orders.cust_id
-- AND orders.order_num = orderitems.order_num
-- AND prod_id = 'TNT2';
-- SELECT
-- cust_name,
-- cust_contact
-- FROM
-- customers AS c,
-- orders AS o,
-- orderitems AS oi
-- WHERE
-- c.cust_id = o.cust_id
-- AND o.order_num = oi.order_num
-- AND prod_id ='TNT2';
-- 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 o.order_num = oi.order_num
-- AND prod_id ='FB';
-- 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 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_id,
-- Customers.cust_name,
-- 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_id,
-- Customers.cust_name,
-- 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;
-- 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
-- OR 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
-- 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
-- FROM
-- productnotes
-- WHERE
-- note_text LIKE '%rabbit%';
-- SELECT
-- note_text,
-- MATCH ( note_text ) against ( 'rabbit' ) AS rank
-- FROM
-- productnotes;
-- SELECT
-- note_text
-- FROM
-- productnotes
-- WHERE
-- MATCH ( note_text ) against ( 'anvils' );