8.mysql 查询
创建流程回顾
create database test charset = utf8 //创建数据库
use test // 使用数据库
select database(); //展示当前使用数据库
create table class(id int auto_increament,key,name varchar(30),num int); //创建一个class 表
show create table class //展示数据表是怎么创建的
insert into class value(,,,,,,,) //全部插入 按顺序插入值
insert into class (id ,name ) values (),() //插入部分值
update class set name=简单说就是对 where id = 1 更新ID为3的全部修改
select * from classes
select gender ,name from classes
select gender as 性别, name as 名字 from classes
select c.name, c.age from classes as c //classes 重命名为 c 然后查询 name,age
select distinct gender from classes ; // distinct去重
1.条件查询
1.1 比较运算符
< > = >= <= !=
select * from students where age>18
select id, name, gender from students where age=18
1.2 逻辑运算符
and or not
select * form students where age > 18 and age < 28; // 年纪大于 18 小于28的
select * form students where age > 18 or gender ="女";
select * form students where not (age > 18 or gender ="女");
1.3模糊查询 like
查询姓名中以小开始的名字
select name from students where name like "小%"
查询姓名中有 小 的名字
select name from students where name like "%小%"
查询姓名中2个字的名字
select name from students where name like "__"
查询姓名中3个字的名字
select name from students where name like "___"
查询至少有2个字的名字
select name from students where name like "__%"
1.4正则查询 rlike
查询以 周开始的名字
select name from students where name rlike "^周.*"
查询以 周开始伦结尾的名字
select name from students where name rlike "^周.*伦$"
1.4范围查询
查询年龄为18、34之间的信息
select name age from students where age=18 or age = 34 // 方式一
select name age from students where age in (18,34)
查询年龄不为18、34之间的信息
select name age from students where age != 18 or age != 34 // 方式一
select name age from students where age not in (18,34)
查询在18到34连续范围内的
select name ,age from students where age between 18 and 34
查询不在18到34连续范围内的
select name ,age from students where age not between 18 and 34
select name ,age from students where not age between 18 and 34
提示 select name ,age from students where age not( between 18 and 34) 错误的。因为 not between and 是一个语法 。。。not 不是取反的意思
空判断
判断 is null
查询身高为空的信息
select * from students where height is null
判断 is not null
查询身高不为空的信息
select * from students where height is not null