mysql查询语句

基本查询

去除重复记录 >SELECT DISTINCT vend_id FROM products;

分页 >SELECT * FROM products LIMIT 5;

>SELECT * FROM products LIMIT 0,5;

>SELECT * FROM products LIMIT 5,5;

排序(降序) >SELECT * FROM products ORDER BY prod_price DESC;

排序(升序) >SELECT * FROM products ORDER BY prod_price [ASC];

多列排序 >SELECT * FROM products ORDER BY prod_price ASC,prod_name ASC;

过滤查询 查询产品价格在2到10之间的产品

>SELECT * FROM products WHERE prod_price >= 2 AND prod_price <= 10;

>SELECT * FROM products WHERE prod_price BETWEEN 2 AND 10;

查询产品价格不等于2.5的所有产品

>SELECT * FROM products WHERE prod_price <> 2.5; >SELECT * FROM products WHERE prod_price != 2.5;

查询没有电子邮件信息的客户

>SELECT * FROM customers WHERE cust_email IS NULL;

查询有电子邮件信息的客户

>SELECT * FROM customers WHERE cust_email IS NOT NULL;

过滤查询

查询由供应商1001和1003制造并且价格在10元以上的产品

>SELECT * FROM products WHERE vend_id = ‘1001‘ OR vend_id = ‘1003‘ AND prod_price > 10;

>SELECT * FROM products WHERE (vend_id = ‘1001‘ OR vend_id = ‘1003‘) AND prod_price > 10;

>SELECT * FROM products WHERE vend_id IN(‘1001‘,‘1003‘) AND prod_price > 10;

查询不是由供应商1001和1003制造的产品

> SELECT * FROM products WHERE vend_id NOT IN(‘1001’,‘1003’) ;

模糊查询

“_”通配符代表一个字符 “%”通配符代表0个或一个或任意多个字符 查询产品名称中以jet开头的产品

> SELECT * FROM products WHERE prod_name LIKE ‘jet%‘;

查询_ ton anvil产品

> SELECT * FROM products WHERE prod_name LIKE ‘_ ton anvil‘

? 不要过度使用LIKE通配符，如果其他操作符可以完成就使用其他操作符

? 通配符搜索使用的时间比其他搜索的时间长

? 如果确实需要使用通配符，除非绝对有必要，否则不要把通配符放到WHERE子句的开始处，把通配 符放到搜索模式的开始处，搜索起来是最慢的 

更多基本查询

列的别名

> SELECT vend_id AS ‘供应商编号‘ FROM products;

算数运算

>SELECT quantity,item_price,quantity * item_price AS ‘总价‘ FROM orderitems;

文本处理函数

left()返回左边指定长度的字符

> SELECT prod_name,LEFT(prod_name,2) FROM products;

right()返回右边指定长度的字符

>SELECT prod_name,RIGHT(prod_name,5) FROM products;

length()返回字符串的长度

> SELECT prod_name,LENGTH(prod_name) FROM products;

lower()将字符串转换为小写

> SELECT prod_name,LOWER(prod_name) FROM products;

upper()将字符串转换为大写

> SELECT prod_name,UPPER(prod_name) FROM products;

文本处理函数

ltrim()去掉字符串左边的空格

> SELECT prod_name,LTRIM(prod_name) FROM products;

rtrim()去掉串右边的空格

>SELECT prod_name,RTRIM(prod_name) FROM products;

trim()去掉左右两边的空格

>SELECT prod_name,TRIM(prod_name) FROM products;

字符串连接

>SELECT CONCAT(‘I love ‘,cust_name) AS ‘Message‘ FROM customers;

日期时间函数

日期和时间函数

获取2005-9-1日的订单

>SELECT * FROM orders WHERE order_date = ‘2005-09-01‘;

>SELECT * FROM orders WHERE DATE(order_date) = ‘2005-09-01‘;

获取2005年9月的订单

>SELECT * FROM orders WHERE order_date >= ‘2005-09-01‘ AND order_date <= ‘2005-09-30‘;

>SELECT * FROM orders WHERE YEAR(order_date) = ‘2005‘ AND MONTH(order_date) = ‘9‘;

聚合函数 ? min() ? max() ? count() ? sum() ? avg() 聚合函数常用于统计数据使用 聚合函数统计时忽略值为NULL的记录

聚合函数

查询商品价格最高的产品

> SELECT MAX(prod_price) FROM products;

查询商品价格最低的产品

> SELECT MIN(prod_price) FROM products;

查询商品价格总和

> SELECT SUM(prod_price) FROM products;

查询商品平均价格

> SELECT AVG(prod_price) FROM products;

查询客户数量

>SELECT COUNT(*) FROM customers;

>SELECT COUNT(cust_email) FROM customers;

分组统计

获取每个供应商提供的产品数量

> SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id;

获取提供产品数量大于2的供应商

> SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id HAVING COUNT(*) > 2;

HAVING语句用于GROUP BY的过滤 WHERE用于分组前过滤

获取产品提供产品数量大于等于2并产品价格大于10的供应商

> SELECT vend_id,COUNT(*) FROM products WHERE prod_price > 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

查询语句顺序 1. SELECT 2. FROM 3. WHERE 4. GROUP BY 5. HAVING 6. ORDER BY 7. LIMIT

子查询

子查询指的是嵌套在查询中的查询

获取订购商品编号为TNT2的客户名

1.从订单详情表中获取订单编号：

> SELECT order_num FROM orderitems WHERE prod_id = "TNT2";

2.根据订单编号获取下订单的客户ID：

> SELECT cust_id FROM orders WHERE order_num IN (‘20005‘,‘20007‘);

3.根据客户ID获取客户的姓名：

> SELECT cust_name FROM customers WHERE cust_id IN (‘10001‘,‘10004‘);

子查询

SELECT cust_name 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_id,cust_name, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) FROM customers；

等值查询 > SELECT ts.id AS ‘stuid‘,stu_name,tc.id AS ‘class_id‘,class_name FROM t_student AS ts,t_class AS tc WHERE ts.class_id = tc.id 内联接查询 > SELECT ts.id AS ‘stuid‘,stu_name,tc.id AS ‘class_id‘,class_name FROM t_student AS ts INNER JOIN t_class AS tc ON ts.class_id = tc.id

左(外)联接查询

> SELECT ts.id AS ‘stuid‘,stu_name,tc.id AS ‘class_id‘,class_name FROM t_student AS ts LEFT JOIN t_class AS tc ON ts.class_id = tc.id

右(外)联接查询

>SELECT ts.id AS ‘stuid‘,stu_name,tc.id AS ‘class_id‘,class_name FROM t_student AS ts RIGHT JOIN t_class AS tc ON ts.class_id = tc.id

组合查询

查询所有的用户和公司，并在一个结果集中显示

> SELECT id,name,createtime FROM t_user UNION SELECT id,name,createtime FROM t_company;

组合查询

查询所有的用户和公司，并在一个结果集中按照创建时间(createtime)降序显示

> select id,name,createtime from t_user union select id,name,createtime from t_company order by createtime desc;

组合查询

? union必须由两条或两条以上的select语句组成，语句之间使用union分割

? union的每个查询必须包含相同的列，表达式或聚合函数

? 列的数据类型必须兼容：类型不必完全相同，但是必须是相互可以转换的

? union查询会自动去除重复的行，如果不需要此特性，可以使用union all

? 对union结果进行排序，order by语句必须在最后一条select语句之后

>SELECT vend_id FROM vendors UNION ALL SELECT vend_id FROM products;

数据库引擎： ? InnoDB：可靠的事务处理引擎，不支持全文搜索 ? MyISAM：是一个性能极高的引擎，支持全文搜索，但不支持事务处理 ? MEMORY：功能等同于MyISAM引擎，但由于数据存储在内存中，所以速度快 > create table xxx ( … )engine=innodb;

根据查询记录添加到表： >insert into t_tableb(val) select val from t_tablea;