mysql练习（重）

重要语句

CREATE TABLE student2
(
	s_no int(6) not NULL PRIMARY KEY auto_increment,
	s_name VARCHAR(12),
	s_sex VARCHAR(4),
	s_score DOUBLE(6,1)
);
#外部添加外键的方法  CONSTRAINT取外键名
ALTER TABLE student2 ADD CONSTRAINT fk_tids FOREIGN KEY (t_id) REFERENCES teacher(t_no);
#撤销 FOREIGN KEY 约束,请使用下面的 SQL：
ALTER TABLE student2 DROP FOREIGN KEY fk_tids
#创建唯一索引 如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：
CREATE UNIQUE INDEX index_s_name ON student2 (s_name DESC)
#删除唯一索引
DROP INDEX index_s_name ON student2
select * from student2
#创建表
CREATE table teacher(
t_no int(4) not null PRIMARY KEY auto_increment,
t_name VARCHAR(12),
t_sex VARCHAR(4),
t_age VARCHAR(4),
t_tel VARCHAR(11)
);

#笛卡尔积  第一个表的全部语句从第二个表的第一条开始乘
select s.s_name,t.t_name from student2 s,teacher t;  
#返回两张表符合条件的记录
select s.s_name,t.t_name from student2 s,teacher t WHERE s.t_id=t.t_no;  
# 左连接(根据学生查老师)
select * from student2 s left JOIN teacher t on s.t_id =t.t_no;
#右连接(根据老师查学生)
select * from student2 s RIGHT JOIN teacher t on s.t_id =t.t_no;
#内连接
select * from student2 s INNER JOIN teacher t on s.t_id =t.t_no;
#全连接 UNION:去重  UNION all:不去重
select * from student2 s left JOIN teacher t on s.t_id =t.t_no
UNION all
select * from student2 s RIGHT JOIN teacher t on s.t_id =t.t_no;
#SQL 在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束：
CREATE TABLE Persons
(
Id_P int NOT NULL ,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
#在表内创建外键的方法:FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
);
#性别分组
select * from student2 GROUP BY s_sex;
#平均分 最高分 最低分 分组
SELECT avg(s_score) as '平均分',max(s_score) as '最高分',MIN(s_score) as '最低分',s_sex as '性别' from student2 GROUP BY s_sex;

select ABS(-123) from dual;
SELECT DATE("2021-9-7") FROM dual;
SELECT NOW() from dual;
SELECT ADDDATE("2021-9-7",3);
SELECT CONCAT("hello","world");
SELECT DATE(now()) from dual;
SELECT DATE_ADD('2018-05-12',INTERVAL 3 MINUTE);
SELECT DATE_SUB('2018-05-12',INTERVAL 2 DAY);
SELECT DATEDIFF('2018-09-07','2018-09-05');
alter table student2 add column birthday date;

#查询和当前事件相差30天的日期
SELECT * FROM student2 where birthday > DATE_SUB(now(),INTERVAL 30 DAY);
SELECT * from student2 WHERE DATEDIFF(birthday,now()) < 30;

#HAVING和 GROUP BY不能连用是版本冲突
SELECT * from student2 where s_score > 80 GROUP BY s_sex HAVING avg(s_score) >=80;

#创建视图

#查询视图
select * from student2_view;

一题

1、用sql建表student：
学号 姓名 性别 生日 专业
1 张三 男 1980-12-03 1
2 王武 女 1980-09-22 3
3 李四 女 1981-03-04 2
4 赵六 女 1981-05-24 1
5 张建国 男 1980-06-02 4
6 赵娟 女 1980-08-30 2

2、用sql建表course：
学号 学期 课程编号 分数
1 1 2 92.0
1 2 2 76.0
2 1 3 60.0
2 2 3 90.0
3 4 1 66.0
3 4 2 NULL
3 4 4 81.0
3 4 6 95.0
5 1 2 67.0
6 1 2 50.0
6 2 2 87.0
6 2 3 86.0

要求：不考虑考试科目和学期，列出不同学生所有考试成绩中，成绩为优的分数的累加值，和成绩为良的分数的平均值。优和良的界线是90分和80分。

CREATE TABLE student3 (
id  int not NULL PRIMARY KEY auto_increment,
s_name VARCHAR(12),
s_sex VARCHAR(8),
s_birthday date ,
s_major int
);
insert into student3(s_name,s_sex,s_birthday,s_major) 
VALUES
('张三','男','1980-12-03',1),
('王武','女','1980-09-22',3),
('李四','女','1981-03-04',2),
('赵六','女','1981-05-24',1),
('张建国','男','1980-06-02',4),
('赵娟','女','1980-08-30',2);


CREATE TABLE course(
sid  int not NULL,
term int,
cid int,
score DOUBLE(6,2)
);
INSERT INTO course(sid,term,cid,score) VALUES
(1,1,2,92.0),
(1,2,2,76.0),
(2,1,3,60.0),
(2,2,3,90.0),
(3,4,1,66.0),
(3,4,2,NULL),
(3,4,4,81.0),
(3,4,6,95.0),
(5,1,2,67.0),
(6,1,2,50.0),
(6,2,2,87.0),
(6,2,3,86.0);
ALTER TABLE course ADD CONSTRAINT fk_sid FOREIGN KEY (sid) REFERENCES student3(id);
SELECT * from course;
#要求不考虑考试科目和学期，列出不同学生所有考试成绩中，成绩为优的分数的累加值，和成绩为良的分数的平均值。优和良的界线是90分和80分
select sum(score) as '累加值' from course c,student3 s WHERE c.sid = s.id and score>=90;
select avg(score) as '平均值' from course c,student3 s WHERE c.sid = s.id and 80<score<90;

---

二题
1．建表:
Student（
学号：s_id 整型 主键 ，
姓名：name 字符型 ,
电话：Tel 字符型,
学历：Content 字符型,
毕业日期：Date 日期型，
教师编号：teacher_id 整型，外键）

2.插入记录：

s_id Name Tel Content Date teacher_id

1 张三 13333663366 大专毕业 2006-10-11 1
2 张三 13612312331 本科毕业 2006-10-15 2
3 张四 021-55665566 中专毕业 2006-10-16 3
4 张五 021-55665566 大专毕业 2006-10-16 2
5 张六 021-55665566 大专毕业 2006-10-18 3
6 张七 021-55665566 中专毕业 2006-10-12 1

3.建表:
Teacher(
教师编号：t_id 整型 主键 ，
姓名：name 字符型 ，
年龄：age 整型，
地址：address 字符型)

2.插入记录：
t_id name age address

1 张老师 38 成都
2 李老师 42 北京
3 王老师 35 上海

要求：

(a) 有一新记录(小王 13254748547 高中毕业 2007-05-06 1)请用SQL语句新增至表student中；

(b）请统计不同教师所教授学生的总数以及学历为大专以上的学生总数，并按照教师姓名进行分组显示；

create table student4(
	s_id int not null primary key auto_increment,
	s_name varchar(5),
	s_tel varchar(11),
	s_content varchar(20),
	s_date date,
	t_id int
);
ALTER table student4 ADD CONSTRAINT fk_tid FOREIGN key(t_id) REFERENCES teacher2(tid);

insert into student4(s_name,s_tel,s_content,s_date,t_id) values
('张三',13333663366,'大专毕业','2006-10-11',1),
('张三',13612312331,'本科毕业','2006-10-15',2),
('张四',021-55665566,'中专毕业','2006-10-16',3),
('张五',021-55665566,'大专毕业','2006-10-16',2),
('张六',021-55665566,'大专毕业','2006-10-18',3),
('张七',021-55665566,'中专毕业','2006-10-12',1);

CREATE table teacher2 (
tid int PRIMARY KEY auto_increment,
t_name VARCHAR(20),
t_age int,
t_address VARCHAR(20)
)
INSERT into teacher2(t_name,t_age,t_address) VALUES
('张老师',38,'成都'),
('李老师',42,'北京'),
('王老师',35,'上海');

#有一新记录(小王 13254748547 高中毕业 2007-05-06  1)请用SQL语句新增至表student中；
insert into student4(s_name,s_tel,s_content,s_date,t_id) 
values
('小王','13254748547','高中毕业','2007-05-06',1);

#请统计不同教师所教授学生的总数以及学历为大专以上的学生总数，并按照教师姓名进行分组显示；
select count(*) as '学生总数',t.t_name from student4 s left join teacher2 t on s.t_id = t.tid where s.s_content like '本科%'  group by t.t_name;

select t.t_name as '教师姓名',COUNT(s.t_id) as '大专以上人数' from student4 s,teacher2 t where s.t_id = t.tid and (s.s_content <> '大专毕业' and s.s_content <> '高中毕业' and s.s_content <> '中专毕业')   GROUP BY t.t_name HAVING COUNT(s.s_name);