自动增量式mysql可以自动为每个行为分配一个可用编号,不用再添加一行时手动分配唯一值。
show databases; 显示数据库
use testdb(database);切换数据库
show tables;返回当前选择的数据库内可用表的列表
show columns from defend_log; 显示表的列,也可以用DESCRIBE defend_log,即是前面的快捷方式。
show status;用于显示广泛的服务器状态信息
show create database testdb; 显示数据库testdb的创建语句
show create table defend_log; 显示数据表defend_log的创建语句
show grants; 显示授予用户的安全权限
show errors; show warnings; 显示服务器错误或警告消息
help show; 显示允许的show语句
MySQL 5支持一个新的INFORMATION_SCHEMA命令,可用来获得和过滤模式信息
DISTINCT:显示不同的值,去除相同的值
SELECT prod_name FORM products LIMIT 5; 显示前五行,带一个值得是行数,从第一行开始
SELECT prod_name FROM products LIMIT 5, 5; 后两个值指定要检索的开始行和行数,即从第5行开始的5行。第一个数为开始位置,第二个数为要检索的行数。
DESC:降序,只应用到直接位于其前面的列名。如果想在多个列上进行降序排序,必须对每个列制定DESC关键字。
SELECT proid_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
先以prod_price降序排列,然后再以prod_name升序排序
使用ORDER BY和LIMIT组合,可以找出一个列中最高或最低的值。
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
//选出价格最高的一行
在给出ORDER BY子句时,应保证它位于FROM子句之后,且是最后一个子句。如果使用LIMIT,则LIMIT必须位于ORDER BY之后。
同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误。
空值检查:可以检查一个列是否包含空值,空值表示为NULL,不包含0,空字符串,或仅仅包含空格
SELECT prod_name FROM products WHERE prod_price IS NULL; 返回价格为空的产品
AND 优先级高于OR
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
返回id为1002 或1003中价格大于10的产品名称,价格
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;
用or也可以实现
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;
IN优点:
比OR执行速度快,且可以包含其他SELECT语句
使用通配符和正则表达式进行匹配时,需要用''单引号将其表达式包围起来。
使用通配符的时候要使用LIKE操作符,表示使用通配符匹配而不是相等。
通配符%匹配任何字符出现任何次数 ‘jet%’匹配以jet开头出现的所有字符串
_:匹配一个字符
通配符搜索一般要比前面的其他搜索所花时间更长
正则表达式匹配,将LIKE代替为REGEXP,表示REGEXP后面使用的是正则表达式
LIKE与REGEXP的一个重要区别是:LIKE匹配整个列,如果被匹配的文本在列中出现,LIKE不会找到他,不会返回该行。
REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现,该行会被返回。要使REGEXP像LIKE一样匹配整个行,需要使用^(开始)与$(结束)定位符。
如下:
mysql> select prod_name from products where prod_name LIKE '1000';
Empty set (0.00 sec)
mysql> select prod_name from products where prod_name REGEXP '1000';
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)
|:匹配两个串之一,相当于or,如'1000|2000|3000'
[123]匹配1 或2或3
[^123]除1 2 3这几个字符之外的其他任何东西
匹配范围[1-9] [a-z]匹配任意字符
特殊字符用\转义
\\-表示查找- \\.表示查找.
*:0或多个匹配 +:1或多个匹配相当于{1,} ?:0个或1个匹配相当于{0, 1}
{n}:指定书目的匹配 {n, }:不少于指定数目的匹配 {n, m}:匹配数目的范围
^: 文本的开始 $:文本的结束 [[:<:>:]]词的结尾
如果找一个以一个数开始(包括小数点开始的数),[0-9\\.]或[[:digit:]\\.]
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;
RTrim():去掉右边的所有空格;LTrim():去掉左边的所有空格;Trim():去掉左右两边的空格
AS可以表示别名,如果一列太长,可以使用别名(或者也可以省略)。
select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors order by vend_name;
NOW():返回当前日期和时间,select Now();
数据处理函数:
SubString():找出子串的字符, Upper():串转换为大写, Lower():小写
Locate():找出串的一个子串 Soundex:返回串的SOUNDEX,即对串的发音进行比较而不是字母
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y.Lie')
找到cust_contact的发音与Y.Lie发音相似的行
Exp():返回一个数的指数 Rand():返回一个随机数
COUNT():
使用COUNT(*)对表中行的数目进行计数,不管表中包含的是空值还是非空值
使用COUNT(column)对特定列中具有值得行进行技术,忽略NULL值
sum():返回某列的和
AVG(),COUNT(), MAX(), MIN(), SUM():都可指定ALL和DISTINCT值,ALL表示所有行执行计算,是默认的,DISTINCT表示只对不同的行进行计算,如
select avg(distinct prod_price) as avg_price from products where vend_id = 1003;
DISTINCT不能用于COUNT(*),DISTINCT必须使用列名,不能用于计算或表达式,DISTINCT用于MIN()与MAX()并没有实际意义。
使用这些聚集函数一般比在应用程序中计算要快的多。
Group by对数据进行分组,之后队每个组而不是整个结果集进行聚集,group by经常和聚集函数一起使用,如:
select vend_id, count(*) as num_prods from products group by vend_id; //按vend_id进行分组,且分别对每个分组进行行数统计,不包括NULL
可以使用WITH ROLLUP关键字,队每个分组以及每个分组汇总级别的值,结果包括了NULL,
使用Group by的一些规定:
1 Group by子句可以包含任意数目的列,使得能对分组进行嵌套,为数据分组提供更细致的控制
2 如果Group by子句中嵌套了分组,数据将在最后规定的分组上进行汇总,
3 Group by子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在select中使用表达式,则必须在group by子句中指定相同的表达式,不能使用别名
4 除聚集语句外(select中可以有聚集函数,聚集函数的列不一定要出现在group by子句中),select语句中的每个列都必须在group by子句中给出(select语句中没有出现在聚集函数中的列必须在group by子句中出现),如
select vend_id, min(prod_price) as min_prods from products group by vend_id WITH ROLLUP;
5 如果分组列中有NULL值,则NULL将作为一个分组返回。如果有多行NULL值,将它们分成一组
6 Group by子句必须出现在Where子句之后,order by子句之前。
group by与order by区别:
order by
order by
group by
排序产生的输出
分组行,但输出可能不是分组的排序
任意列都可以使用(即使非选择的列也可以使用)
只可能使用选择列或表达式列,而且必须使用每个选择列表达式,子句中不能使用聚集函数,select可以出现聚集函数
经常发现group by分组的输出是按照分组顺序输出的,但是并不总是这样,不是SQL规范,如果要保证数据正确分组,则应该和order by子句一起使用,例如检索总计订单价格大于50的订单的订单号和总计订单价格:
select order_num, SUM(quantity*item_price) AS ordertotal from orderitems group by order_num HAVING SUM(ordertotal) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
| 20009 | 38.47 |
+-----------+------------+
5 rows in set (0.00 sec)
如果要按照ordertotal进行排序,则需要order by:
select order_num, SUM(quantity*item_price) AS ordertotal from orderitems group by order_num HAVING SUM(ordertotal) >= 50 order by ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20009 | 38.47 |
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+------------+
5 rows in set (0.00 sec)
HAVING:过滤分组,HAVING支持所有WHERE操作,区别是HAVING过滤分组,WHERE过滤行。还有一个区别是WHERE在分组前进行过滤,HAVING在分组后进行过滤,WHERE排除的行不包括在分组中。where子句中不能使用聚集函数。WHERE和HAVING可以一起使用,例如列出具有2个以上,价格为10以上的产品的供应商:
SELECT vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1003 | 4 |
| 1005 | 2 |
+---------+-----------+
2 rows in set (0.00 sec)
select子句的顺序:select, from, where, group by, having, order by, limit;
当同时含有where子句、group by 子句 、having子句及聚集函数时,执行顺序如下: --执行where子句查找符合条件的数据; --使用group by 子句对数据进行分组;对group by 子句形成的组运行聚集函数计算每一组的值;最后用having 子句去掉不符合条件的组。 --having 子句中的每一个元素也必须出现在select列表中。有些数据库例外,如oracle. --having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。 --having子句限制的是组,而不是行。where子句中不能使用聚集函数,而having子句中可以。