查询空和不为空
select * from cms_user where card is not null
select * from cms_user where card is null
设置查询范围 >=2 <=4
select * from cms_user where id between 2 and 4
//指定集合 在这个里面就查到了 忽略大小写
select * from cms_user where id in(1,2,3,4,1000,55)
select * from cms_user where username in('张三','章子怡',21)
模糊查询
%代表 0~n个字符
_代表 1 个字符
select * from cms_user where username like '%in%'
select * from cms_user where username like '%张_'
select * from cms_user where username like '____'//四个下划线代表四个字符
;逻辑运算符号 or 和 and
select * from cms_user where username='lily' or username='rose';
select * from cms_user where username='lily' and id='2;
//分组查询
select any_value(id),group_concat(username),group_concat(password)from cms_user group by proid;
select any_value(id),group_concat(username),group_concat(password)from cms_user group by proid with rollup; //在最后一行进行统计
//这些函数类似字段
select any_value(id),group_concat(username),group_concat(password),count(*) from cms_user group by proid
//函数一般针对分组
//只会显示第一条 然后总数 1 12
select any_value(id),count(*) from cms_user;
//having 只是配合 group by 才有意义
select any_value(id),group_concat(username),group_concat(password),count(*)
from cms_user
group by proid
having count(*)>1
//升序和降序 order by 是放在查询语句的最后的
select * from cms_user order by id asc
select * from cms_user order by id desc
select * from cms_user order by id asc,username desc
Rand() 排序随机
select * from cms_user order by rand()
//从第0条开始,显示俩条
select * from cms_user limit 0,2;
//多表连接查询
#cms_cate cms_news cms_admin
select n.id,n.title,c.cateName,a.username from
cms_news as n
join cms_cate as c on n.cid=c.id
join cms_admin as a on n.aid=a.id;
外键约束 意思就是
比如有一个部门表 和一个员工表 他们有关联
如果把部门表删除一个部门 员工表上还有显示已经删除的部门
加一个约束就可以 删除部门必须先删除部门下的员工
//会去掉重复的结果 如果 cms_user和employee 都有一个zyc的话
只是显示一个
select username from employee union select username from cms_user;
//这样会都显示
select username from employee union all select username from cms_user;
create table if not exists department(
id tinyint unsigned auto_increment key,
depName varchar(20) not null unique
)engine=innodb;
//外键的方式 这是字表 上面是附表
//constraint 是起一个名字
create table if not exists employee(
id smallint unsigned auto_increment key,
username varchar(20) not null unique,
depId tinyint unsigned,
constraint emp_fk_dep foreign key(depId) references department(id)
);
//删除外键
alter table employee drop foreign key emp_fk_dep
//添加外键
alter table employee add foreign key(depId) references department(id)
//子查询 > >= any 代表取子查询结果最小的
Any some all
> >= 最小值 最小值 最大值
< <= 最大值 最大值 最小值
= 任意值 任意值
<> != 任意值
select id,username from student where score>=any(select level from scholarship)
使用正则表达式
select * from employee where username regexp '[^1to]'