1 Star 0 Fork 0

chuan / SQLcode

Create your Gitee Account
Explore and code with more than 6 million developers,Free private repositories !:)
Sign up
This repository doesn't specify license. Without author's permission, this code is only for learning and cannot be used for other purposes.
Clone or download
mariadb_study2.sql 10.31 KB
Copy Edit Web IDE Raw Blame History
chuan authored 2020-10-03 14:56 . Initial commit
-- 注意事项
-- 1给数据库和表命名时尽量使用小写
-- 2作为筛选条件的字符串是否区分大小写看校队规则 collate ..(utf8_bin_ci)
-- 查看数据库所有内容
show tables;
-- 创建学院表
create table tb_collage
(
collid int not null auto_increment comment '编号,非空及自增约束',
collname varchar(50) not null comment '名称',
collmaster varchar(20) not null comment '院长',
coolwed varchar(1023) default 'null' comment '网址',
primary key (collid)
);
-- 创建学生表, 内依赖学院表
create table tb_student
(
stuid int not null comment '学号,四位起',
stunmae varchar(20) not null comment '姓名',
stusex bit default 1 comment '性别,默认唯一男',
stubirth date not null comment '出生日期',
stuadder varchar(255) default 'Null' comment '籍贯',
collid int not null comment '所属学院',
primary key (stuid),
-- 约束sollid参数为 外键参数
foreign key (collid) references tb_collage (collid)
);
-- 创建教师表 依赖学院表
create table tb_teacher
(
teaid int not null comment '工号',
teaname varchar(20) not null comment '姓名',
teatitle varchar(10) default '助教' comment '职称',
collid int not null comment '所属学院',
primary key (teaid),
foreign key (collid) references tb_collage (collid)
);
-- 创建课程表,依赖教师表
create table tb_course
(
couid int not null comment '编号',
couname varchar(50) not null comment '名称',
coucredit int not null comment '学分',
teaid int not null comment '授课老师',
primary key (couid),
-- 可以建立多对多(多个老师教一门), 加中间表
foreign key (teaid) references tb_teacher (teaid)
);
-- 创建选课记录表
-- 学生和课程的多对多关系,中间表(多个多对一建立了多对多关系)
create table tb_score
(
scid int auto_increment comment '选课纪录编号.自增',
stuid int not null comment '选课学生.外键',
couid int not null comment '所选课程.外键',
scdate datetime comment '选课的时间日期',
scmark decimal(4,1) comment '考试成绩',
-- 最大接受浮点数4位有效长度,1位小数也在4位里面
primary key (scid),
foreign key (stuid) references tb_student (stuid),
foreign key (couid) references tb_course (couid)
);
-- 给数据添加唯一性约束(这组数据匹配项只能在表里出现一次)规则
alter table tb_score add constraint one_stuid_one_couid
unique (stuid, couid);
-- 插入学院数据(为了易读此处删去了多条语句)
insert into tb_collage
(collname, collmaster, coolwed) values
('计算机学院', '左冷禅', 'http://www.abc.com')...
-- 插入学生数据
insert into tb_student
(stuid, stunmae, stusex, stubirth, stuadder, collid) values
(5804, '何京墨', 0, '1990-5-24', '新疆维吾尔自治区和田地区', 2)...
-- 插入教师数据
insert into tb_teacher
(teaid, teaname, teatitle, collid) values
(1100, '张三丰', '教授', 1)...
-- 插入课程数据
insert into tb_course
(couid, couname, coucredit, teaid) values
(9999, '离散数学', 4, 3300)..
-- 插入选课记录
insert into tb_score
(stuid, couid, scdate, scmark) values
(6797, 1111, now(), 100)...
-- 聚合函数(通用): max, min,count计数, sum求和, avg求平均
-- 函数: 非通用,MySQL特有
select version(); -- 获取版本号
select now(); -- 获取现在时间
ifnull(s,b) -- s值为空,返回b.否则s
-- having 和where差不多,只是这个用于句子后,用于接收前面处理过的数据?
-- GRUD操作 (增删改查)
-- 查询:
-- 查询所有学生信息
select * from tb_student;
-- 查所有老师的信息
select * from tb_teacher;
-- 查询所有课程名称及学分(投影和别名)
select couname, coucredit from tb_course;
-- 别名
select couname as 课程名称, coucredit as 学分 from tb_course;
-- 查看学生名,和性别(布尔值换成男女:
-- case 值 when 常量 then 真 else 假 end 值和常量相同为真
select stunmae as 姓名, case stusex when 1 then '男' else '女' end as 性别 from tb_student;
-- 非通用写法,仅mysql支持 :stusex值为0则匹配第二个参数
select stunmae as 姓名, if(stusex, '男', '女') as 性别 from tb_student;
-- 查询所有女学生的姓名和出生日期(筛选)
select stunmae, stubirth from tb_student where stusex=0;
-- 查询1993-1997出生学生的名,性,和出生日期
select stunmae, case stusex when 1 then '男' else '女' end as 性别, stubirth from tb_student
where stubirth>='1993-1-1' and stubirth <= '1997-12-31';
-- 另一种写法
select stunmae, case stusex when 1 then '男' else '女' end as 性别, stubirth from tb_student
where stubirth between '1993-1-1' and '1997-12-31';
-- 查询姓 刘 的学生姓名和性别(模糊)
select stunmae, stusex from tb_student where stunmae like '刘%';
-- 查询姓 何 的学生并名为两个字的姓名和性别(模糊)
select stunmae, stusex from tb_student where stunmae like '刘__';
-- 查询名字中有 令 和 狐 两个字的学生的姓名(模糊)
select stunmae, stusex from tb_student where stunmae like '%刘%' or stunmae
like '%何%';
-- 查询没有录入家庭住址的学生姓名(空值)\
select stunmae from tb_student where stuadder is null;
-- 查询录入了家庭住址的学生姓名(空值)
select stunmae from tb_student where stuadder is not null;
-- 比价空值用 is null, is not null
-- 查询学生的选课日期(去重)
select distinct scdate from tb_score;
-- 查询学生的加家庭住址(去重)
select distinct stuadder from tb_student where stuadder is not null;
-- distinct 去重
-- 查询男学生的生日,按升序排序
select stunmae, stubirth from tb_student where stusex=1 order by stubirth
asc;
-- order by [参数1,2,.] asc 按给定参数值排序,多个参数值(1相等,则2..)
-- asc 升序 desc 降序
-- 按年龄从大到小排序
select stunmae, year(now()) -year(stubirth) as 年龄 from tb_student order by 年龄 desc;
-- year() 特有,获取时间对象的年份
-- 查询年龄最大的学生的的出生日期(聚合函数)
select stunmae, max(stubirth) from tb_student;
-- max(),min() 最大值,最小值
-- 查询男女学生的人数(分组)
select stusex, count(stusex) from tb_student group by stusex;
-- count() 计数 group by 按照给定值分组
-- 查询男女生年龄最大的(分组)
select stunmae, stusex, max(stubirth) from tb_student group by stusex;
-- 查询课程编号未1111的课程平均成绩(筛选和聚合)
select avg(scmark) from tb_score where couid=1111;
-- 注意这些函数都会略过空值(null)
-- 查询每个学生的学号和所有课程的平均成绩(分组和聚合函数)
select stuid as 学号, avg(scmark) as 平均分 from tb_score group by 学号;
-- 查询平均成绩大于九十分的学生平均分和学号(分组后的筛选)
select stuid as 学号, avg(scmark) as 平均分 from tb_score group by stuid
having 平均分>=90;
-- where 处理分组前的筛选, having 处理分组后的筛选
-- 查询年龄最大的学生的姓名(子查询
select stunmae, stubirth from tb_student where stubirth=(
select min(stubirth) from tb_student);
-- 括号里的查询结果作为主语句的条件
-- 查询年龄最大的学生和年龄(子查询+运算)
select stunmae as 姓名, year(now())-year(stubirth) as 年龄 from tb_student where stubirth=(
select min(stubirth) from tb_student);
-- 查询选了两门课程以上的学生姓名(子查询,分组条件,集合运算)
select stunmae from tb_student where stuid in (
select stuid from tb_score group by stuid having count(stuid)>2);
-- 子语句如果返回多个值,就不能简单的用 =(比较), in 在集合中为真
-- 查询学生姓名,课程名称以及成绩(连接查询)
-- 笛卡尔集
select stunmae, couname, scmark from tb_student, tb_course, tb_score;
-- 结果是错误的, 每个数据间不匹配
select stunmae, couname, scmark from tb_student t1, tb_course t2, tb_score t3 where t1.stuid=t3.stuid and t2.couid=t3.couid;
-- 因为有外键关联可以自然连接,注意 表起别名(可以用as)最好不用as,因为不通用
-- 可以不用起别名,还有没有外键关联也是可以的这样
-- 另一种写法:
select stunmae, couname, scmark from tb_student t1 inner join tb_score t3
on t1.stuid=t3.stuid inner join tb_course t2 on t2.couid=t3.couid where scmark is not null;
-- 查询选课学生的姓名和平均成绩(子查询和连接查询)
select stunmae, avgmark from tb_student t1,
(select stuid, avg(scmark) as avgmark from tb_score GROUP BY stuid) t2
where t1.stuid=t2.stuid;
-- 先查询学号和平均成绩(t2),然后筛选条件(where),最后执行语句一输出
-- 另一种写法内连接
select stunmae, avgmark from tb_student t1 inner join
(select stuid, avg(scmark) as avgmark from tb_score GROUP BY stuid) t2
on t1.stuid=t2.stuid;
-- inner join 临时表 on 条件(连接双表) (建立软连接)
-- 外连接(outer join):左外连接,右外连接, 全外连接(mysql不支持)
-- left outer join , right..., full...(outer 可以省略)
-- 查询每个学生的姓名和选课数量(左外连接和子查询)
select stunmae, ifnull(stuid_c,0) from tb_student t1 left outer join
(select stuid, count(stuid) as stuid_c from tb_score group by stuid) t2
on t1.stuid=t2.stuid;
-- 左外连接, 把左边的表显示完整 ifnull(s,b) s为空返回b
-- 扩展
-- 等于符号
-- 查询学生姓名,课程名称以及成绩(连接查询)并排序
select stunmae, couname, scmark from tb_student t1 inner join tb_score t3
on t1.stuid=t3.stuid inner join tb_course t2 on t2.couid=t3.couid where scmark is not null order by scmark desc;
-- mysql: 单表支持65535TB数据(mysql5.5)
-- 单例: 最大支持4G的类型 ~LONGBLOB(二进制大对象) ,~LONGTEXT
-- 显示指定行数据 (分页查询)
select stunmae, couname, scmark from tb_student t1 inner join tb_score t3
on t1.stuid=t3.stuid inner join tb_course t2 on t2.couid=t3.couid where scmark is not null order by scmark desc limit 3;
-- 显示3行 limit 3
-- 跳过(偏移)指定n行,显示指定行
select stunmae, couname, scmark from tb_student t1 inner join tb_score t3
on t1.stuid=t3.stuid inner join tb_course t2 on t2.couid=t3.couid where scmark is not null order by scmark desc limit 3 offset 3;
-- 偏移n offset (注意offset在后)
select stunmae, couname, scmark from tb_student t1 inner join tb_score t3
on t1.stuid=t3.stuid inner join tb_course t2 on t2.couid=t3.couid where scmark is not null order by scmark desc limit 6,5;
-- limit 6,5 偏移6条查看5条.

Comment ( 0 )

Sign in for post a comment

1
https://gitee.com/chuancode/SQLcode.git
git@gitee.com:chuancode/SQLcode.git
chuancode
SQLcode
SQLcode
master

Search