mysql 数据库之查询总结

查询基础总结

on / where / having
on  不管条件是否为真都会返回数据  两个表联接时才用on
WHERE 条件不为真的都不会被返回 关键字无法与聚合函数一起使用
HAVING 子句可以让我们筛选分组后的各组数据。

in / exists

in 操作符允许在 where 子句中规定多个值,查询相当于多个 or 条件的叠加 子条件返回结果必须只有一个字段。
exists 对外表用 loop 逐条查询,每次查询都会查看 exists 的条件语句。
left join / right join / inner join
INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录
LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录
RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录

本人mysql工具 HeidiSQL 50道训练题,详情请点链接直接建数据库 脚本导入 可以使用下面我的 亲测可用

创建学生表

create table Student(
SId INT UNSIGNED AUTO_INCREMENT,
Sname varchar(50) NOT NULL,
Sage datetime default NULL,
Ssex varchar(10) default NULL,
PRIMARY KEY ( `SId` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into Student values('1', '赵雷', '1990-01-01', '男');
insert into Student values('2', '钱电', '1990-12-21', '男');
insert into Student values('3', '孙风', '1990-05-20', '男');
insert into Student values('4', '李云', '1990-08-06', '男');
insert into Student values('5', '周梅', '1991-12-01', '女');
insert into Student values('6', '吴兰', '1992-03-01', '女');
insert into Student values('7', '郑竹', '1989-07-01', '女');
insert into Student values('9', '张三', '2017-12-20', '女');
insert into Student values('10', '李四', '2017-12-25', '女');
insert into Student values('11', '李四', '2017-12-30', '女');
insert into Student values('12', '赵六', '2017-01-01', '女');
insert into Student values('13', '孙七', '2018-01-01', '女');

创建课程表

create table Course(
CId INT UNSIGNED AUTO_INCREMENT,
Cname nvarchar(10) NOT NULL,
TId varchar(10)NOT NULL,
PRIMARY KEY ( `CId` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into Course values('1' , '语文' , '2');
insert into Course values('2' , '数学' , '1');
insert into Course values('3' , '英语' , '3');

创建教师表

create table Teacher(
TId INT UNSIGNED AUTO_INCREMENT,
Tname varchar(10)NOT NULL,
PRIMARY KEY ( `TId` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into Teacher values('1' , '张三');
insert into Teacher values('2' , '李四');
insert into Teacher values('3' , '王五');

创建成绩表

create table SC(
SId int(10) NOT NULL,
CId int(10) NOT NULL,
score decimal(18,1) NOT NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into SC values('1' , '1' , 80);
insert into SC values('1' , '2' , 90);
insert into SC values('1' , '3' , 99);
insert into SC values('2' , '1' , 70);
insert into SC values('2' , '2' , 60);
insert into SC values('2' , '3' , 80);
insert into SC values('3' , '1' , 80);
insert into SC values('3' , '2' , 80);
insert into SC values('3' , '3' , 80);
insert into SC values('4' , '1' , 50);
insert into SC values('4' , '2' , 30);
insert into SC values('4' , '3' , 20);
insert into SC values('5' , '1' , 76);
insert into SC values('5' , '2' , 87);
insert into SC values('6' , '1' , 31);
insert into SC values('6' , '3' , 34);
insert into SC values('7' , '2' , 89);
insert into SC values('7' , '3' , 98);

彼特城博客
请先登录后发表评论
  • latest comments
  • 总共0条评论