mysql 查询 约束语句练习

查询语句练习

创建数据库 和 表结构

create database student;
create table studentinfo (id int ,name varchar(10),mscore int,cscore int,class int);

插入十一条数据

insert into studentinfo values(1,’James’,100,100,’1班’),
(2,’Oliver’,51,57,’1班’),
(3,’Harry’,12,78,’2班’),
(4,’Kate’,45,88,’2班’),
(5,’Charlotter’,78,54,’3班’),
(6,’Catherine’,51,42,’3班’),
(7,’George’,79,98,’4班’),
(8,’Alice’,24,86,’4班’),
(9,’Gracie’,12,56,’5班’),
(10,’River’,27,98,’5班’),
(11,’mike’,84,null,’5班’);

按照数学成绩降序排序输出信息(mscore)

select * from studentinfo order by mscore desc;
使用 order by 默认是asc 升序 降序是desc

按照数学成绩降序排序输出信息 如果数学成绩一样 按照语文成绩降序排序

select * from studentinfo order by mscore desc,cscore desc;

查询表中的记录

select count(*) from studentinfo;
使用count() 函数

查询数学成绩大于60分的人数

select count(*) from studentinfo where mscore >60;

查询数学 语文成绩之和大于120的人数

select count(*) from studentinfo where mscore+ ifnull(cscore,0)>120;
ifnull(cscore,0) 防止表中有人成绩为Null 导致整个成绩为null 从而出现的偏差

查询全部的语文 数学成绩之和

select sum(mscore),sum(cscore) from studentinfo;
使用sum()函数

查询数学的平均和

select avg(mscore) from studentinfo;
平均数使用 avg() 函数

查询数学的最高分和最低分

select max(mscore),min(mscore) from studentinfo;

查询每个班的数学最高分和每个班的编号

select class,max(mscore) from studentinfo group by class;
使用group by 来进行分组

查询每个班数学成绩大于60分的人数和班级编号

select class ,count(*) from studentinfo where mscore > 60 group by class;

查询班级总成绩大于240的人数和班级编号

select class,count(*) from studentinfo group by class having sum(mscore+ifnull(cscore,0))>240;
使用having 来查询分组后的条件
having 是分组之后使用的筛选条件 可以使用函数
where 是分组之前使用的 不能使用函数要使用字段

查询5行数据

select * from studentinfo limit 0,5;
limit 参数0代表起始页 参数5 显示几条数据

设置 当前页数为 和 页面内容数量

int current = 1;
int page = 3;
select * from studentinfo limit (current - 1) * page, page;

模糊查询
%代表N个字符
_代表一个字符
select * from studentinfo where ename like’w%’;
select * from studentinfo where ename like’%w%’;
select * from studentinfo where ename like’w_’;
select * from studentinfo where ename like’w___’;

主外键 约束练习

为什么要创建主键?

假如上述表中 id 字段 我错误的存入两个为id = 1的人 另一个人使用该表通过查询 id = 1 会出现两个人 这样就无法判断 id=1的人到底是谁 添加主键就可以避免这种问题的出现

创建主键
1. 第一种创建方法

create table students(id int primary key,name varchar(10));
插入数据时 主键的值不能为空

1. 
   
2. 第二种创建方法
   

create table students(id int ,name varchar(10),primary key(id));
这种方法可以添加联合主键
什么是联合主键?
这里可以看做 一个学校有很多班级 班级中有很多学生 这班级和学生两个关键字可以共同作为主键使用
例如:
create table school(class int, stuid int,primary key(class, stuid));

3.第三种创建方法

create table students(classid int,name varchar(10));
alter table students3 add constraint primary key (classid);

创建唯一约束

唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的。可以有空值 unique 关键字
create table students (id int primary key,name varchar(10) unique);

自动增长列

create table students(id int primary key auto_increment,name varchar(10));
添加数据时可以跳过添加id 的值 关键字 auto_increment

域完整性 限制单元格的数据内容
数据类型 非空约束(not null) 默认值约束(default)

reate table stu1 (id int primary key ,name varchar(10) not null ,sex varchar(10) default ‘男’);

引用完整性(外键约束 把表与表之间通过字段建立联系)
为什么要创建外键?

假如你要统计数据 学生表中有10名学生 但是成绩表中有11名学生 学生id 到了 11 这样会出现统计偏差 使用外键约束可以避免

第一种创建方法

create table stu1(sid int primary key,name varchar(10));
create table score(scores int,sid int,constraint fk_scores_sid foreign key(sid) references stu2(sid));

第二种创建方式

create table stu1(sid int primary key,name varchar(10));
create table score(scores int,sid int);
alter table score add constraint fk_scores_sid foreign key(sid) references stu2(sid);