mysql语句整理

查询
1.查询所有字段
select * from 表名;
例：select * from students;

2.查询指定字段
 select 列1,列2,... from 表名;
例：select name,age from students;

3. 使用 as 给字段起别名
 select 字段 as 名字.... from 表名;
例：select name as '姓名',age from students;

 

4.select 表名.字段 .... from 表名;
例：select students.name from students;

5.可以通过 as 给表起别名
select 别名.字段 .... from 表名 as 别名;
例：select * from students as s;

      select s.name from students as s

> < >= <= != <>
6.条件查询
select .... from 表名 where .....

查询年纪大于18岁的信息
select * from students where age > 18;

查询年纪小于18岁的信息
select * from students where age < 18;

查询小于等于18岁的信息
select * from students where age <= 18;

查询年龄为18岁的所有学生的名字
select * from students where age = 18;

查询年龄不为18岁的所有学生的名字

select * from students where age != 18;
select * from students where age <> 18;

7.逻辑运算符

18和28之间的所以学生信息
select * from students where age > 18 and age < 28;

18岁以上的女性
select * from students where age > 18 and gender = '女';

18以上或者身高高过180(包含)以上
select * from students where age > 18 or height >= 180;

不在 18岁以上的女性 这个范围内的信息
select * from students where not (age>18 and gender=2);

select * from students where not age > 18 and gender= "女";

select * from students where not (age > 18 and gender = "女");

8.模糊查询(where name like 要查询的数据)

select * from students where name like '三%';

查询姓名中 有 "三" 所有的名字
select * from students where name like '%三%';

查询有2个字的名字

select * from students where name like '__';

查询有3个字的名字
select * from students where name like '___';

 

查询至少有2个字的名字
select * from students where name like '__%';

select * from students where name not like "__";

范围查询

查询 年龄为18或34的姓名
select * from students where age = 18 or age = 34 ;
select * from students where age in (18,34);

年龄不是 18或34岁的信息
select * from students where age not in(18,34);

查询 年龄在18到34之间的的信息
select * from students where age > 18 and age < 34;
select * from students where age between 18 and 34; 

查询 年龄不在18到34之间的的信息

select * from students where age not between 18 and 34;

 

9.空判断

select * from students where height is null;