本文内容:
注意事项
书写习惯:SQ语句虽然不区分大小写,但是一般书写时,所有SQL语句的关键字大写,而所有列和表名小写,这样使代码更易于阅读和调试。
空格:在处理SQL语句时,所有空格都被忽略。
分号:必须加分好来结束一个sql语句
命名规范:数据库、表、字段等所有名称的可用字符范围为:A-Z,a-z, 0-9 和_下划线
行标:表中的第1个数据段是行0,LIMIT关键字中尤其注意
关于通配符:谨慎使用,因为有效率上的问题,如果能用其他操作符替代就应该换成别的。
SQL语句的顺序:学到后面会发现,各种SQL语句的关键词的出现是有先后顺序的,需要理解其执行(产生效果)的时间,是对应他的出现顺序。
查询语句
检索单个列:
SELECT 列名 FROM 表名;
检索多个列:
SELECT 列名,列名,列名 FROM 表名;
检索整张表:
SELECT * FROM 表名;
检索不同的行(检索出该列中唯一的行,去重效果):
SELECT DISTINCT 列名 FROM 表名;
注意:DISTINCT关键字应用于所有列,如果后面跟了两个列,则会把两列的字段放在一起判断。
我要消除name字段值重复的记录,同时又要得到id字段的值,其中id是自增字段:
SELECT id,name FROM t1 WHERE id IN(SELECT MAX(id) FROM t1 GROUP BY name) order by id desc;
限制结果(LIMIT关键字):
返回5行符合要求的数据:
SELECT 列名 FROM 表名 LIMIT 5;
返回从行1开始的5行:
SELECT 列名 FROM 表名 LIMIT 1,5;
查询之排序
ORDER BY (后面可以跟任意个列)
SELECT 列名 FROM 表名 ORDER BY 列名;
指定排序方向:
默认是字母(或数字)升序排序,DESC关键字可以改为降序
DESC只应用在排在其前面的列,若想对多列进行降序排序,则须使用多个DESC
SELECT 列名 FROM 表名 ORDER BY 列名 DESC, 列名;
查询之过滤
WHERE 关键字
范围值检查
WHERE 列名 BETWEEN a AND b;
空值检查
WHERE 列名 IS NULL
组合WHERE语句
1、主要有AND 和 OR,这里会涉及到操作顺序的问题,如下语句
SELECT * FROM product WHERE id = 3 OR id = 4 AND price < 10;
SQL优先处理AND字符串,所以返回的是id=4且价格小于10的商品,或者id=3的商品。
使用圆括号可以改变语义
SELECT * FROM product WHERE (id = 3 OR id = 4) AND price < 10;
2、IN操作符
SELECT * FROM actor WHERE actor_id IN (20,30);
IN可以代替OR的功能,而且一般来说比OR的执行速度要快。
注意上述语句返回的是满足20和30的两个数据,不是一个区间的概念。
3、NOT操作符
在IN前面加上NOT,表示范围之外的所有。
通配符
%(表示任何字符出现任意次数,0次也可以)(但不会匹配NULL):
匹配所有F开头的数据
SELECT * FROM actor WHERE first_name LIKE 'F%';
_(匹配单个字符):
mysql> SELECT * FROM actor WHERE first_name LIKE '_R%';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 16 | FRED | COSTNER | 2006-02-15 04:34:33 |
| 48 | FRANCES | DAY-LEWIS | 2006-02-15 04:34:33 |
| 78 | GROUCHO | SINATRA | 2006-02-15 04:34:33 |
| 86 | GREG | CHAPLIN | 2006-02-15 04:34:33 |
| 106 | GROUCHO | DUNST | 2006-02-15 04:34:33 |
| 126 | FRANCES | TOMEI | 2006-02-15 04:34:33 |
| 130 | GRETA | KEITEL | 2006-02-15 04:34:33 |
| 157 | GRETA | MALDEN | 2006-02-15 04:34:33 |
| 172 | GROUCHO | WILLIAMS | 2006-02-15 04:34:33 |
| 191 | GREGORY | GOODING | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
11 rows in set (0.00 sec)
正则表达式
REGEXP关键字
mysql> SELECT * FROM actor WHERE first_name REGEXP 'RED';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 16 | FRED | COSTNER | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
REGEXP在列值内进行匹配,因此当部分匹配时,即匹配成功并返回。上述语句若换成LIKE关键字,则无返回值!因为LIKE匹配的是整个列
2、进行OR匹配
SELECT * FROM actor WHERE first_name REGEXP 'RED|RY';
3、匹配几个字符之一
mysql> SELECT * FROM actor WHERE first_name REGEXP '[AE]RY';
+----------+------------+-------------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-------------+---------------------+
| 51 | GARY | PHOENIX | 2006-02-15 04:34:33 |
| 66 | MARY | TANDY | 2006-02-15 04:34:33 |
| 73 | GARY | PENN | 2006-02-15 04:34:33 |
| 77 | CARY | MCCONAUGHEY | 2006-02-15 04:34:33 |
| 95 | DARYL | WAHLBERG | 2006-02-15 04:34:33 |
| 129 | DARYL | CRAWFORD | 2006-02-15 04:34:33 |
| 154 | MERYL | GIBSON | 2006-02-15 04:34:33 |
| 194 | MERYL | ALLEN | 2006-02-15 04:34:33 |
| 198 | MARY | KEITEL | 2006-02-15 04:34:33 |
+----------+------------+-------------+---------------------+
3、匹配范围
(注意返回值按照主键排序了,应该是和搜索顺有关)
(不同的范围书写:[0-9A-Z])
mysql> SELECT * FROM actor WHERE first_name REGEXP '[E-Z]RY';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 60 | HENRY | BERRY | 2006-02-15 04:34:33 |
| 154 | MERYL | GIBSON | 2006-02-15 04:34:33 |
| 191 | GREGORY | GOODING | 2006-02-15 04:34:33 |
| 194 | MERYL | ALLEN | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
4、特殊字符
\. (转义,表示匹配点 )
多数正则表达式使用单斜杠来转义字符,但在MySQL中需要双斜杠,因为MySQL自己也要解析一次
5、匹配多个实例
常用重复限定符(对其前面的字符生效)
代码 说明
* 重复零次或更多次
+ 重复一次或更多次
? 重复零次或一次
{n} 重复n次
{n,} 重复n次或更多次
{n,m} 重复n到m次
6、定位符
^ 匹配行的开始
$ 匹配行的结束
创建计算字段
目的:对字段进行处理后再返回
1、拼接字符串(Concat关键字,拼接的元素之间用逗号相隔开)
mysql> SELECT Concat(first_name,' ',last_name) FROM actor WHERE first_name LIKE 'F%';
+----------------------------------+
| Concat(first_name,' ',last_name) |
+----------------------------------+
| FRED COSTNER |
| FRANCES DAY-LEWIS |
| FAY KILMER |
| FRANCES TOMEI |
| FAY WINSLET |
| FAY WOOD |
+----------------------------------+
去掉空格
Trim(列名):去掉两边的空格
RTrim(列名):去掉右边空格
mysql> SELECT Concat(Trim(first_name)) FROM actor WHERE first_name LIKE 'F%';
为查询结果命名
注意AS的位置
mysql> SELECT Concat(first_name,last_name) AS name FROM actor WHERE first_name LIKE 'F%';
+------------------+
| name |
+------------------+
| FREDCOSTNER |
| FRANCESDAY-LEWIS |
| FAYKILMER |
| FRANCESTOMEI |
| FAYWINSLET |
| FAYWOOD |
+------------------+
2、执行算术计算
使用数据处理函数
用法同样是将列表值作为参数传入数据处理函数,然后得到一个返回结果
统计
AVG函数
mysql> SELECT AVG(length) FROM film;
+-------------+
| AVG(length) |
+-------------+
| 115.2720 |
+-------------+
COUNT函数(即使是NULL值也会被返回)
mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
类似的还有MAX,MIN,SUM
加上DISTINCT关键字,先去重再聚集
mysql> SELECT AVG(DISTINCT length) FROM film;
+----------------------+
| AVG(DISTINCT length) |
+----------------------+
| 115.5000 |
+----------------------+
分组数据
GROUP BY:
mysql> SELECT length,COUNT(*) FROM film GROUP BY length;
+--------+----------+
| length | COUNT(*) |
+--------+----------+
| 46 | 5 |
| 47 | 7 |
| 48 | 11 |
| 49 | 5 |
| 50 | 9 |
| 51 | 7 |
| 52 | 7 |
| 53 | 9 |
| 54 | 6 |
先根据length分组,再进行次数统计。
GROUP BY 语句必须出现在WHERE子句之后,ORDER BY 子句之前。
过滤分组:
HAVING 语句
mysql> SELECT length,COUNT(*) FROM film GROUP BY length HAVING COUNT(*)>10;
+--------+----------+
| length | COUNT(*) |
+--------+----------+
| 48 | 11 |
| 73 | 12 |
| 74 | 12 |
| 84 | 13 |
| 85 | 17 |
| 92 | 11 |
| 100 | 12 |
| 102 | 11 |
| 112 | 13 |
| 122 | 11 |
| 139 | 11 |
| 179 | 13 |
+--------+----------+
HAVING语句和WHERE语句的区别:
HAVING语句的过滤是基于分组聚集值的,HAVING在数据分组后进行过滤,而WHERE在分组前就进行过滤。
使用子查询
1、where型子查询
SELECT goods_id,goods_name FROM goods WHERE goods_id = (SELECT MAX(goods_id) FROM goods);
2、from型子查询
子查询会生成一个临时表,由于 FROM 子句中的每个表必须有一个名称,因此 AS name 是必须的。FROM 子查询也称为衍生数据表子查询
SELECT ... FROM (subquery) AS name ...
SELECT s1,s2 FROM (SELECT s1, s2*2 AS s2 FROM table1) AS temp WHERE s1 > 1
联结表
是SQL最强大的功能之一。
外键(foreign key):外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
mysql> SELECT Concat(first_name,' ',last_name),film_id FROM actor,film_actor WHERE actor.actor_id = film_actor.actor_id AND actor.actor_id = 1;
+----------------------------------+---------+
| Concat(first_name,' ',last_name) | film_id |
+----------------------------------+---------+
| PENELOPE GUINESS | 1 |
| PENELOPE GUINESS | 23 |
| PENELOPE GUINESS | 25 |
| PENELOPE GUINESS | 106 |
| PENELOPE GUINESS | 140 |
| PENELOPE GUINESS | 166 |
| PENELOPE GUINESS | 277 |
| PENELOPE GUINESS | 361 |
| PENELOPE GUINESS | 438 |
| PENELOPE GUINESS | 499 |
| PENELOPE GUINESS | 506 |
| PENELOPE GUINESS | 509 |
| PENELOPE GUINESS | 605 |
| PENELOPE GUINESS | 635 |
| PENELOPE GUINESS | 749 |
| PENELOPE GUINESS | 832 |
| PENELOPE GUINESS | 939 |
| PENELOPE GUINESS | 970 |
| PENELOPE GUINESS | 980 |
+----------------------------------+---------+
改成INNER JOIN(效果同上,据说性能有差别):
mysql> SELECT Concat(first_name,' ',last_name),film_id FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id AND actor.actor_id = 1;
高级联结
使用表别名(书写更方便)(虽然别名的命名是在FROM之后,但在这之前就可以使用别名):
mysql> SELECT Concat(a.first_name,' ',a.last_name),film_id FROM actor AS a INNER JOIN film_actor AS b ON a.actor_id = b.actor_id AND a.actor_id = 1;
把子查询改成自联结(据说性能会更好?)
mysql> SELECT * FROM actor AS a INNER JOIN actor AS b ON a.first_name = b.last_name;
外部联接:
内部联接消除了与另一个表中的行不匹配的行。而外部联接会返回 FROM 子句中提到的至少一个表或视图中的所有行。
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
不管是否与 ProductReview 表的 ProductID 列相匹配,LEFT OUTER JOIN 都会在结果中包括 Product 表的所有行。请注意,对于结果中没有匹配的产品审核 ID 的产品,行的 ProductReviewID 列中则包含一个空值。
组合查询
执行多个查询语句,并将结果作为单个查询结果返回。
UNION关键字:将多条SELECT语句得到的结果合并,重复的结果只会出现一次
UNION ALL : 包含重复的行
全文本搜索
InnoDB不支持全文搜索,MySIAM支持。
这些列中有一个名为 note_text 的列,为了进行全文本搜索,MySQL根据子句 FULLTEXT(note_text) 的指示对它进行索引。这里的FULLTEXT 索引单个列,如果需要也可以指定多个列。
create table productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL,
primary key(note_id),
FULLTEXT(note_text)
)ENGINE = MyISAM;
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('heavy -rope*' in BOOLEAN MODE);
分析:这次只返回一行。这一次仍然匹配词 heavy ,但 -rope* 明确地指示MySQL排除包含 rope* (任何以 rope 开始的词,包括ropes )的行