SQL最常用基础语句
查询当天的所有与记录select * from sys_student_record where date(check_ins) = curdate();
select r.stu_no,i.name, i.classname,activation_type,check_ins,count( * ) as acount from sys_student_record r,sys_student_info i where activation_type=3 and r.teacher_no=031234510 and r.stu_no=i.stu_no anddate(check_ins) = curdate() group by r.stu_no order by acount desc ;
查询一天：
select * from table where to_days(column_time) = to_days(now());
select * from table where date(column_time) = curdate();
查询一周：
select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) 80 and english90;
统计总分大于250的人数有多少？
select count(*) from student where (math+english+chinese)>250;
统计一个班级数学总成绩？
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(chinese),sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(chinese+math+english) from student;
统计一个班级语文成绩平均分
select sum(chinese)/count(chinese) from student;
求一个班级数学平均分？
select avg(math) from student;
求一个班级总分平均分
select avg(chinese+english+math) from student;
求班级最高分和最低分
select max(chinese+english+math),min(chinese+english+math) from student;
对订单表中商品归类后，显示每一类商品的总价
select product from orders group by product;
select product,sum(price) from orders group by product;
查询购买了几类商品，并且每类总价大于100的商品
select product from ordersgroup by product having sum(price)>100;
定义带有主键约束的表
create table test1
(
id int primary key,
name varchar(20),
password varchar(20)
);
定义一个主键 自动增长的表
create table test2
(
id int primary key auto_increment,
name varchar(20),
password varchar(20)
);
create table test3
(
id int primary key auto_increment,
name varchar(20) unique
);
create table test4
(
id int primary key auto_increment,
name varchar(20) unique not null
);
//什么是外键约束
create table husband
(
id int primary key,
name varchar(20)
);
create table wife
(
id int primary key,
name varchar(20),
husband_id int,
constraint husband_id_FK foreign key(husband_id) references husband(id)
);
//一对多或多对一
create table department
(
)
create table employee
(
)
//多对多
create table teacher
(
id int primary key,
name varchar(20),
salary double
);
create table student
(
id int primary key,
name varchar(20)
);
create table teacher_student
(
teacher_id int,
student_id int,
primary key(teacher_id,student_id),
constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
constraint student_id_FK foreign key(student_id) references student(id)
);
//一对一
create table person
(
id int primary key,
name varchar(20)
);
create table> (
id int primary key,
address varchar(40),
constraint> );
页:
[1]