MySQL数据库CRUD语句快速入门(三)
的同学。
select * from student where math>80 and chinese>80;
查询英语>80或者总分>200的同学
select *,chinese+math+english from student where english>80 or chinese+english+math>200;
order by 子句
对数学成绩排序后输出。
select * from student order by math;
对总分排序后输出，然后再按从高到低的顺序输出
select *,chinese+math+english from student order by chinese+math+english desc;
对姓李的学生成绩排序输出 order从句是需要放在where从句的后面
select *,chinese+math+english from student where name like '李%' order by chinese+math+english;
合计函数
count
统计一个班级共有多少学生？
select count(*) from student;
统计数学成绩大于90的学生有多少个？
select count(*) from student where math>90;
统计总分大于230的人数有多少？
select count(*) from student where chinese+math+english>230;
sum
统计一个班级数学总成绩？
select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(chinese),sum(english) from student;
统计一个班级语文、英语、数学的成绩总和
select sum(math+chinese+english) from student;
统计一个班级语文成绩平均分
select sum(chinese)/count(*) from student;
缺考的不参与计算
select sum(chinese)/count(chinese) from student;
avg
语文平均分
select avg(chinese) from student;
max/min
语文最 高分
select max(chinese) from student;
select语句（6）
group by
订单表
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
将商品归类
select * from orders group by product;
显示单类商品总结
select *,sum(price) from orders group by product;
商品分类 显示单类商品总价大于100的
select *,sum(price) from orders group by product having sum(price)>100;
// 将单价大于20 的商品进行归类显示 按照价格排序
select * from orders where price>20 group by product order by price;
5、表的约束
表的约束就是在定义表时，我们可以在创建表的同时为字段增加约束，对将来插入的数据做一些限定
www.2cto.com
一、表的约束
表的约束就是在定义表时，为表中的字段加一些约束条件，对将来插入的数据做一些限定
1. 唯一约束 unique
create table a
(
id int,
username varchar(20) unique,
password varchar(20)
);
insert into a (id,username,password) values(1,'zhangsan','1111');
insert into a (id,username,password) values(2,'wangwu','1111');
2. 非空约束 not null
create table b
(
id int not null,
name varchar(20)
);
insert into b (id,name) values (1,'aaaa');
insert into b (id,name) values (1,'bbbb');
3. 主键约束 相当于 唯一约束+非空约束
数据库中的每张表都应该至少有一个主键，通常是id
create table c
(
id int primary key,
name varchar(20)
);
insert into c(id,name) values (1,'aaaa');
insert into c(id,name) values (2,'bbbb');
create table d
(
firstname varchar(20),
lastname varchar(20),
primary key(firstname, lastname)
);
insert into d (firstname, lastname) values ('tom', 'cat');
insert into d (firstname, lastname) values ('tom', 'hks');
create table e