一、单表查询
1、查询所有字段
SELECT * FROM table_name;
2、查询指定字段
语法:
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
实例:
SELECT `name`, `pwd` FROM `student`
3、查询指定记录
语法:
WHERE 条件表达式;
实例:
SELECT * FROM employee WHERE id=101;
4、查看某一数据库中某个表的所有字段名和字段类型
Select COLUMN_NAME 列名, DATA_TYPE 字段类型
from INFORMATION_SCHEMA.COLUMNS
Where table_name = 'table_name' ##表名
AND table_schema = 'database_name'##数据库名
5、带关键字IN的查询
#在table_name表中查询field为value1和value2的所有数据
SELECT * FROOM table_name WHERE field= (value1,value2);
6、带BETWEEN AND的范围查询
#在table_name表中查询field为value1~value2之间的所有记录
SELECT * FROM table_name WHERE fieldBETWEEN value1 AND value2;
7、带LIKE的字符串匹配查询
语法:
[NOT] LIKE '字符串'
-
OT:可选。加上NOT表示与指定的字符串不匹配时满足条件。
-
字符串:指定用于匹配的字符串,该字符串必须加单引号或者双引号。
-
“字符串”参数的值可以是一个完整的字符串,也可以是包含百分号(%)或者下划线(_)的通配符。
-
注意%和_的区别:
-
“%”表示任意长度的字符串,长度可以为0。例如,g%gle表示以字母g开头,以字母gle结尾的任意长度的字符串。g%gle可以表示ggle、gogle、gfgle、google、gusgle、gooooogle等。
-
“_”只能表示单个字符。例如,g_gle表示以字母g开头,以字母gle结尾的5个字符。中间的“_”可以表示任意一个字符。g_gle可以表示gagle、gbgle、gcgle、gdgle或gogle等。
8、查询空值
语法:
SELECT * | 字段名1,字段名2,……
FROM 表名
WHERE 字段名 IS [NOT] NULL
-
NOT,可选参数,用于判断字段不是空值
9、带关键字AND的多条件查询
SELECT * FROM table_name WHERE field1=value1 AND field2=value2;
10、带关键字OR的多条件查询
SELECT * FROM table_name WHERE field1=value1 OR field2=value2;
11、使用LIMIT限制查询结果的数量
语法:
LIMIT m;
-
m:表示查询多少条记录。
实例:
查询用户信息表(tb_user),按照 user_id 编号进行升序排列,显示前5条记录。
SELECT * FROM tb_user
ORDER BY user_id
LIMIT 5
12、使用EXISTS的查询
SELECT * FROM table_name WHERE EXISTS(SELECT * FROM table_name WHERE field = value);
13、用关键字DISTINCT去除结果中的重复行
SELECT DISTINCT field FROM table_name;
14、用关键字ORDER BY对查询结果进行排序
(1)、降序
SELECT * FROMtable_name ORDER BY field DESC;
(2)、升序
SELECT * FROM table_name ORDER BY field ASC;
(3)、多个字段排序
select * from table_name order by field1 DESC, field2 ASC;
(4)、分页查询
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
-
第一个参数指定第一个返回记录行的偏移量
-
第二个参数指定返回记录行的最大数目
(5)、子查询
定位偏移位置的 id,然后再查询数据,但只适用于id递增的情况
-
SELECT * FROM `user_operation_log` LIMIT 1000000, 10
-
SELECT id FROM `user_operation_log` LIMIT 1000000, 1
-
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10
(6)、采用 id 限定方式
要求id必须是连续递增,而且还得计算id的范围,然后使用 between
-
SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100
-
SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100
(7)、用关键字LIMIT限制查询结果的数量
SELECT * FROM student LIMIT 3;
15、用关键字IS NULL查询
#查询table_name表内column列中 field 字段是 NULL 的记录。
SELECT column,field FROM table_name WHERE field IS NULL;
##查询table_name表内column列中 field 字段非 NULL 的记录。
SELECT column,field FROM table_name WHERE field IS NOT NULL;
16、匹配字符
(1)、匹配字符中的任意一个
SELECT * FROM table_name WHERE field REGEXP '[value1,value2]';;
(2)、使用“*”和“+”来匹配多个字符
SELECT * FROM table_name WHERE field REGEXP 'value1*value2';
SELECT * FROM table_name WHERE field REGEXP 'value1+value2';
(3)、匹配以指定字符开头和结束的记录,以A开头,以B结尾,中间包含三个字符的记录,
SELECT * FROM table_name WHERE field REGEXP '^A...B$';
二、聚合函数查询
1、COUNT()
# COUNT()函数,表示求指定列的总行数
SELECT COUNT(*) FROM table_name;
2、SUM()函数
# SUN()函数,表示求指定列指定的和
SELECT SUM(column) FROM table_name;
3、AVG()函数
# AVG()函数,表示求指定列的平均值
SELECT AVG(column) FROM table_name;
4、MIN()函数
# MIN()函数,表示求指定列的最小值
SELECT MIN(column) FROM table_name;
5、MAX()函数
# MAX()函数,表示求指定列的最大值
SELECT MAX(column) FROM table_name;
三、连接查询
将多张表(可以大于2张表)进行记录的连接(按照某个指定的条件进行数据拼接):最终的结果是:字段数一定会增加,记录数有可能变化。连接查询的意义:在用户查看数据的时候,需要显示数据来自多张表。连接分类:SQL中将连接查询分成四类:内连接,外连接,自然连接和交叉连接。
1、内连接查询
内连接:[inner] join,从左表中取出每一条记录,去右表中与所有的记录进行匹配,匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留。
语法:
左表[inner] join 右表 on 左表.字段= 右表.字段;
-
注意:内连接可以使用where代替on关键字
2、外连接查询
外连接(outer join),是以某张表为主,取出里面的所有记录,然后每条与另外一张表进行连接。不管能不能匹配上条件,最终都会保留,也就是说,能匹配,正确保留;不能匹配,其他表的字段都置空(NULL)。
语法:
左表 left/right join 右表on 左表.字段=右表.字段;
3、复合条件连接查询
就是在连接查询的过程中,通过添加过滤条件来限制查询结果,使查询结果更加精确
四、子查询
1、带ANY,SOME关键字的子查询
ANY 和 SOME 是任意一个,是存在性。或者数学里的能成立 。也就是>ANY 大于最小的即可,<ANY 小于最大的即可。
SOME 的别名是 ANY。
2、带ALL关键字的子查询
检索成绩最高的学生
SELECT name , score FROM student
WHERE score >=ALL(SELECT score FROM student);
3、带IN关键字的子查询
IN 是“=ANY”的别名
IN和= ANY表达式列表一起使用时不是同义词。 IN可以接受表达式列表,但“= ANY不能。
NOT IN 的别名不是“<> ANY”,而是“<> ALL”。
SELECT * FROM table_name1 WHERE field IN(SELECT field table_name2);
4、带比较运算符的子查询
运算符=,<,>,<>,>=,<=来对值之间的关系进行比较。
查询大于所有人平均年龄的员工名与年龄
select name,age from employee where age >(select avg(age) from employee) ;
5、合并查询结果
(1)、UNION
SELECT field FROM table_name UNION SELECT field FROM table_name;
(2)、UNION ALL
SELECT field FROM table_name UNION ALL SELECT field FROM table_name;
7、为表和字段取别名
(1)、为表取别名
SELECT * FROM table_name 别名 WHERE 别名.field = value;
(2)、为字段取别名
SELECT field1 AS 别名1,field2 as 别名2 FROM table_name;
8、使用正则表达式查询
(1)、查询以特定字符串或字符串开头的记录(^)
查询以“b”开头的记录。
select * from 表名 where name REGEXP '^b';
(2)、查询以特定字符或字符串结尾的记录($)
查询以“be”开头的记录。
select * from 表名 where name REGEXP 'be$';
(3)、用符号“.”来替代字符串中的任意一个字符
查询a与g之间有一个字符的记录。
select * from 表名 where name REGEXP 'a.g';
(4)、使用“*”和“+”来匹配多个字符
*:匹配零个或多个它前面的字符
+:匹配至少一次前面的字符
查询以b开头,后面有至少一个a字母的记录。
select * from 表名 where name REGEXP '^ba+';
(5)、匹配指定字符串
查询包含 on 的记录
select * from 表名 where name REGEXP 'on';
(6)、匹配指定字符中的任意一个
表中字段值包含字母o或t的记录
select * from 表名 where name REGEXP '[ot]';
(7)、匹配指定字符以外的字符([^字符])
[^字符集合]:表示匹配不在集合中的任意字符。
查询字段中包含a~e和1~6以外的字符记录。
select * from 表名 where name REGEXP '[^a-e1-6]';
(8)、使用{n,}或者{n,m}来指定字符串连续出现的次数
查询name字段中至少出现两次“x”的记录。
select * from 表名 where name REGEXP 'x{2,}';
-
{n,}:表示匹配至少n次前面的字符。
-
{n,m}:表示匹配前面的字符串不少于n次,不多于m次。