创建一个score表
create table scores(id int primary key auto_increment,stuid int,
subid int,score decimal(5,2));
添加外键约束
alter table scores add constraint stu_sco foreign key(stuid) references T_students(id);
范围查询between and
select * from V_book where id between 1001 and 1003;
非连续查询 in
select * from V_book where id in(1001,1002,1003);
模糊查询like
select * from V_book where name like '%学%' or name like '%怖%';
% 代表任意多个字符,_ 代表一个字符
not
SELECT * FROM products WHERE products.vend_id NOT IN (1002,1003);
空判断
- 注意:null与‘ ’是不同的
- 判断is null
- 查询没有填写地址的学生
select * from students where hometown is null;
判断非空is not null
查询填写了地址的学生
select * from students where hometown is no null;
查询填写了地址的女生
select * from students where hometown is not null and gender=0;
优先级
- 小括号、not、比较运算符、逻辑运算符
- and比or先运算、如果同时出现并希望先算or,需要结合()使用