MySQL语句--查询语句大全(有示例)_mysqlsql查询语句大全及实例

操作

命令

示例/说明

查询所有列

SELECT * FROM 表名

SELECT * FROM table_name;

查询指定列

SELECT 列1，列2，...FROM 表名；

SELECT id,name FROM table_name;

使用 AS  给字段起别名

SELECT id AS  序号, name AS  名字, gender AS  性别 FROM students;

AS 不写也可以，跟写AS 一样效果。

通过 AS  给表起别名

SELECT col1,col2 FROM table_name AS  xxx

SELECT s.id,s.name,t.gender FROM students AS  s, teachers AS  t;

运算符

详情

示例

比较

等于: =

大于: >

大于等于: >=

小于: <

小于等于: <=

不等于: != 或 <>

例：查询编号大于3的学生：          SELECT * FROM students WHERE id > 3;

例：查询编号不大于4的学生：       SELECT * FROM students WHERE id <= 4;

例：查询姓名不是“黄蓉”的学生：   SELECT * FROM students WHERE name != '黄蓉';

例：查询没被删除的学生：            SELECT * FROM students WHERE is_delete=0;

逻辑

AND，OR，NOT

//sql 会首先执行 AND 条件，再执行 OR 语句。除非加括号

例：查询编号大于3的女同学：               SELECT * FROM students WHERE id > 3 AND gender=0;

例：查询编号小于4或没被删除的学生： SELECT * FROM students WHERE id < 4 OR is_delete=0;

例：查询编号小于4，或大于10而且没被删除的学生： SELECT * FROM students WHERE id < 4 or id > 10 AND is_delete=0;

例：查询编号小于4或大于10，而且没被删除的学生： SELECT * FROM students WHERE (id < 4 or id > 10) AND is_delete=0;

模糊查询

LIKE

% 任意多个任意字符

_ 一个任意字符

例：查询姓黄的学生：                           SELECT * FROM students WHERE name LIKE '黄%';

例：查询姓黄并且“名”是一个字的学生：SELECT * FROM students WHERE name LIKE '黄_';

例：查询姓黄或叫靖的学生：SELECT * FROM students WHERE name LIKE '黄%' OR name LIKE '%靖';

范围查询

分为 连续/非连续 范围查询

IN：非连续

BETWEEN … AND …：连续

    （包含端点）

例：查询编号是1或3或8的学生： SELECT * FROM students WHERE id in(1,3,8);

例：查询编号为3至8的学生：       SELECT * FROM students WHERE id BETWEEN 3 AND 8;

例：查询编号是3至8的男生：    SELECT * FROM students WHERE (id BETWEEN 3 AND 8) AND gender=1;

空判断

判断为空/非空

注意： null与’’是不同的

例：查询没有填写身高的学生：SELECT * FROM students WHERE height IS NULL;

例：查询填写了身高的学生：   SELECT * FROM students WHERE height IS NOT NULL;

例：查询填写了身高的男生：   SELECT * FROM students WHERE height IS NOT NULL AND gender=1;