mySQL语言总结--1

最新推荐文章于 2024-08-28 14:13:28 发布

Sr's man

最新推荐文章于 2024-08-28 14:13:28 发布

阅读量117

点赞数

分类专栏： mySQL 文章标签： mysql

本文链接：https://blog.csdn.net/qq_20966795/article/details/89390247

版权

2 篇文章 0 订阅

订阅专栏

检索单个列，SELECT prod_num FROM products;
检索多个列，SELECT prod_id, prod_name, prod_price FROM products;
检索所有列，SELECT * FROM products;
检索不同的行，即所有行的值要求不一样 SELECT DINSTINCT vend_id FROM products;
限制结果
- 返回不多于几行，SELECT prod_name FROM products LIMIT 5;
- 可以指定开始行和结束行，SELECT prod_name FROM products LIMIT 5,5;
  - 同义SELECT prod_name FROM products LIMIT 4 OFFSET 3, 意思是从第三行开始选4行。
使用完全限定的表名， SELECT products.prod_name FROM products;

排序数据，SELECT prod_name FROM products ORDER BY prod_name;
按照多个列排序，SELECT prod_id, prod_price,prod_name FROM products ORDRE BY prod_price, prod_name;
指定排序方向，用DESC进行降序排序，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;
ASC 可以指定升序排序。
结合DESC,LIMIT找到最大值或者最小值，SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

% 通配符表示任意字符串出线任意次数.
- ‘jet%’表示匹配以jet开头的字符串, SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘jet%’;
- '%anvil%'包含anvi的字符，SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘%anvil%’;
- ‘s%e’ 表示以s开头，e结尾，ELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘s%e’;
‘-’ 匹配任意单个字符.
- 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;
进行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;
范围匹配，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 Concat(vend_name, ’ (’, vend_country, ‘)’) FROM vendors ORDER BY vend_name;
- RTrim（）去右边括号；LTrim() 去左边括号， Trim() 去两边括号。: SELECT Concat(RTrim(vend_name), ’ (’ , RTrim(vend_country) ,’)’) FROM vendors ORDER BY vend_name;
- 使用别名，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 WHERE order_num = 20005;

文本处理函数，Upper() 大写函数: SELECT vend_name , Upper(vend_name) AS cend_name_upcase FROM vendors ORDER BY vend_name;
- Left() 返回串左边的字符
- Length() 字符串长度
- Locate() 找到一个串的子串
- Lower() 返回小写字符串
- LTrim() 去掉串左边的空格
- RTrim() 去掉串右边的空格
- Soundex() 返回串的SOUNDEX的值 // 字符串的发音
- SubString() 返回子串的字符
日期和时间处理函数。

SELECT cust_id, order_num FROM orders WHERE Date(order_data) = ‘2005-09-01’;

SELECT cust_id, order_num FRM orders WHERE (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;

更多时间处理函数参考<MySql必知必会p72>
数值处理函数。 Abs() | Cos() | Exp() | Mod() | Pi() | Rand() | Sin() | Sqrt() | Tan() …

关注

专栏目录