mysql扩展

作者: 憨猜猜 | 来源:发表于2019-02-26 23:12 被阅读0次

CREATE TABLE IF NOT EXISTS tb_student
(
stuid int not NULL auto_increment,
stuname VARCHAR(20) not NULL,
tel CHAR(11) not NULL,
birth date DEFAULT '2019-10-21',
addr VARCHAR(100),
gender bit DEFAULT 1,
PRIMARY key(stuid)
);

drop table if exists tb_student;

-- ===============2.课程表================
CREATE TABLE IF NOT EXISTS tb_course
(
couid int NOT NULL auto_increment,
couname VARCHAR(20) NOT NULL,
startdate date NOT NULL DEFAULT '2019-10-21',
intro VARCHAR(200),
credit int NOT NULL,
PRIMARY key(couid)
);  

-- 学生和课程是多对多的
CREATE table if not exists tb_score
(
scoreid int not null auto_increment,
mark FLOAT(4,1) comment '分数',
stuid int comment '学生外键',
couid int comment '课程外键',
PRIMARY KEY (scoreid)
);

alter table tb_score add CONSTRAINT fk_stuid_score FOREIGN key (stuid) REFERENCES tb_student (stuid);

alter table tb_score add CONSTRAINT fk_couid_score FOREIGN key (couid) REFERENCES tb_course (couid);

-- 添加成绩
INSERT INTO tb_score (mark) VALUES (89),(45.5),(100),(95),(60),(77),(90),
(97),(49),(66),(56),(71),(80.5),(33),(87),(56),(74),(68),(80),(92),(34),(75);


INSERT into tb_course VALUES(10,'语文','2014-4-6','博大精深','2');

INSERT into tb_course(couname,startdate,intro,credit) VALUES
('数学','2018-4-9','计算地球的面积','5'),
('ps基础','2008-6-8','美颜相机','6'),
('网页基础','2012-3-5','web前端','4'),
('视频剪辑','2013-4-6','特效镜头','8'),
('体育场馆智能','2017-7-8','体育场地管理','2'),
('局域网','2017-2-7','装网线','4');

INSERT into tb_student VALUES(100,'杨紫',12345678901,'1991-12-14','江西',0);

INSERT into tb_student(stuname,tel,birth,addr,gender) VALUES
('杨过二',14785236907,'1988-4-5','山东',1),
('何志成',15288446770,'1998-7-13','云南',1),
('剩菜',14789652300,'1998-7-11','云南',0),
('憨猜猜',12398745687,'1986-7-4','北京',0),
('杨小奶',14785203695,'1997-6-6','四川',0);

INSERT into tb_student(stuname,tel,birth,gender) VALUES
('黄蓉',12456398712,'1997-5-8',0),
('郭靖',18974562302,'1980-7-9',1);


-- 查询所有学生信息 
select * from tb_student;

-- 查询所有课程名及学分
select couname,credit from tb_course;
-- 查询所有女学生的姓名  
select stuname,gender from tb_student where gender=0;
-- 查询所有80后学生的姓名、性别和出生日期 
select stuname,gender,birth from tb_student where birth BETWEEN'1980-1-1' and '1989-12-31';
-- 查询姓 ’杨‘的学生姓名和性别(模糊)
SELECT stuname,gender from tb_student where stuname like '杨%';
-- 6.查询姓’杨‘名字两个字的学生姓名和性别（模糊）
SELECT stuname,gender from tb_student where stuname like '杨_';
-- 查询名字中有'不'或'嫣'字的学生的姓名  
SELECT stuname  from tb_student where stuname like '%不%' or '%嫣%';
-- 查询录入了家庭住址的学生姓名(空值)
select stuname from tb_student where addr is not null;
-- 查询没有录入家庭地址的学生姓名(空值)
select stuname from tb_student where addr is null;
-- 查询学生选课的所有日期(去重)
SELECT DISTINCT startdate from tb_course;
-- 查询学生的家庭住址(去重)
SELECT DISTINCT addr from tb_student;
-- 查询男学生的姓名和生日按年龄从大到小排序(排序)
select stuname,birth from tb_student where gender=1 ORDER BY birth asc;
-- 查询年龄最大的学到的出生日期(聚合函数)
SELECT MIN(birth) from tb_student;
-- 查询年龄最小的学生的出生日期（聚合函数）
SELECT max(birth) from tb_student;
-- 查询课程编号为11的课程的平均成绩(筛选和聚合函数)
SELECT avg(mark) from tb_score where couid=11;
-- 插叙学号为101的学生所有课程的总成绩(筛选和聚合函数)
SELECT SUM(mark) from tb_score where stuid=101;
-- 查询男女学生的人数(分组和聚合函数)
select gender,count(stuname) from tb_student group by gender;
-- 查询每个学生的学号和平均值(分组和聚合函数) 
SELECT stuid,AVG(mark) from tb_score GROUP BY  stuid;
-- 查询平均成绩大于等于80分的学生的学号和平均成绩  
SELECT stuid,AVG(mark) from tb_score GROUP BY stuid HAVING AVG(mark)>=80;
-- 查询年龄最大的学生的姓名(子查询)  
SELECT stuname,birth from tb_student WHERE birth in 
(SELECT MIN(birth) from tb_student);
-- 查询选了两门以上课程的学生姓名(子查询/组合条件/集合运算) 
SELECT stuname from tb_student WHERE stuid in
(SELECT stuid from tb_score GROUP BY stuid HAVING count(couid)>2);
-- 查询所有选过课的学生的姓名
SELECT stuname from tb_student where stuid in
(select stuid from tb_score GROUP BY stuid HAVING count(couid)>1);