MySQL 其他基础知识

-- 查询存储引擎
show engines;
-- 显示可用存储引擎
show variables like 'have%';
-- concat多个字段联合
select tname ,cname ,concat(tname,':11',cname ) from  (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where tsex='男';
-- 查出字段间的卡迪尔积
select* from  student ,score
-- std方差
select sno,cno,degree,std(degree)from score where degree in (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
-- group_concat(sname) 查询分组中各项
select class,AVG(degree),group_concat(sname)from student join score on student.sno=score.sno group by class ;
-- 多个分组查询 先按class分组 再sname相同的再分组
select class,AVG(degree),group_concat(sname)from student join score on student.sno=score.sno group by class,sname;
-- exists用法
select *from score where  exists (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
select sno,cno,degree,std(degree)from score where degree  in (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
select *from score where  not exists (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
-- 获取字符集排列方式
select collation(sname)from student;
-- 获取字符集
select charset(sname)from student;
-- 获取数据库版本号
select version();
-- 获取服务器连接数
select connection_id();
-- 获取数据库的名称
select database();
select schema();
-- 获取用户名
select user();
select system_user();
select session_user();
select current_user;
select current_user();
-- 获取最近生成auto_inscrenment的值
select last_insert_id();
-- 对字符串加密
select password('mrsoft');
-- 普通加密
select md5('mrsoft');
-- 加密
select encode('mrsoft','mr');
-- 解码
select decode('mrsoft','mr');
-- view
create view ccc as select class,AVG(degree),group_concat(sname)from student join score on student.sno=score.sno group by class,sname;
show create view ccc;
desc ccc;
show table status like 'ccc';

-- 创建数据库mydb3
create DATABASE mydb3 character set utf8;
use  mydb3;
-- 创建数据表Student
create table Student(
		Sno varchar(20) primary key,
		Sname varchar(20) NOT NULL,
		Ssex VARCHAR(20) NOT NULL,
		Sbirthday Datetime,
		Class varchar(20)
);
-- 创建数据表Course
CREATE TABLE Course(
		Cno varchar(20)primary key,
		Cname	varchar(20)NOT NULL,
		Tno varchar(20),
		constraint fk_course_teacher foreign key (tno)references teacher (tno)
);
desc Course;
-- 创建数据表Score
CREATE TABLE Score(
		Sno varchar(20),
		Cno varchar(20),
		Degree Decimal(4,1),
		constraint fk_score_student foreign key (sno)references student (sno),
		constraint fk_score_course foreign key (cno)references course (cno)
);
-- CREATE TABLE Score LIKE Course;
-- 改数据表Score字段
-- ALTER TABLE Score change Tno Degree Decimal(4,1);
desc Score;
-- 创建数据表Teacher
create table Teacher(
		Tno varchar(20)primary key,
		Tname varchar(20)NOT NULL,
		Tsex VARCHAR(20)NOT NULL,
		Tbirthday Datetime,
		Prof varchar(20),
		Depart varchar(20)NOT NULL
);
-- 给数据表Student 添加数据
INSERT INTO Student VALUES(108,'曾华','男','1977-09-01','95033');
INSERT INTO Student VALUES(105,'匡明','男','1975-10-02','95031');
INSERT INTO Student VALUES(107,'王丽','女','1976-01-23','95033');
INSERT INTO Student VALUES(101,'李军','男','1976-02-20','95033');
INSERT INTO Student VALUES(109,'王芳','女','1975-02-10','95031');
INSERT INTO Student VALUES(103,'陆君','男','1974-06-03','95031');
-- 给数据表Course 添加数据
INSERT INTO Course VALUES('3-105','计算机导论','825');
INSERT INTO Course VALUES('3-245','操作系统','804');
INSERT INTO Course VALUES('6-166','数字电路','856');
INSERT INTO Course VALUES('9-888','高等数学','831');
-- 给数据表Score 添加数据
INSERT INTO Score VALUES('103','3-245','86');
INSERT INTO Score VALUES('105','3-245','75');
INSERT INTO Score VALUES('109','3-245','68');
INSERT INTO Score VALUES('103','3-105','92');
INSERT INTO Score VALUES('105','3-105','88');
INSERT INTO Score VALUES('109','3-105','76');
INSERT INTO Score VALUES('101','3-105','64');
INSERT INTO Score VALUES('107','3-105','91');
INSERT INTO Score VALUES('108','3-105','78');
INSERT INTO Score VALUES('101','6-166','85');
INSERT INTO Score VALUES('107','6-166','79');
INSERT INTO Score VALUES('108','6-166','81');
-- 给数据表Teacher 添加数据
INSERT INTO Teacher VALUES(804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO Teacher VALUES(856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO Teacher VALUES(825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO Teacher VALUES(831,'刘冰','女','1977-08-14','助教','电子工程系');
-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列
SELECT Sname,Ssex,Class FROM Student;
-- 2、 查询教师所有的单位即不重复的Depart列
SELECT distinct Depart FROM Teacher;
-- 3、查询Student表的所有记录
select *from Student;
-- 4、 查询Score表中成绩在60到80之间的所有记录。
SELECT *FROM Score WHERE Degree BETWEEN 60 and 80;
-- 5、 查询Score表中成绩为85，86或88的记录。
SELECT * FROM Score WHERE Degree=85 or Degree=86 or Degree=88;
-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT *FROM Student WHERE Class=95031 or Ssex='女';
-- 7、以Class降序查询Student表的所有记录。
SELECT * FROM Student ORDER BY Class desc;
-- 8、以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM Score ORDER BY Cno asc,Degree desc;
-- 9、查询“95031”班的学生人数。
SELECT COUNT(*) FROM Student WHERE Class=95031;
-- 10、查询每门课的平均成绩。
SELECT  Cname ,AVG(Degree) FROM Score GROUP BY Cname;
-- 11、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(Degree) from Score where Cno like '3%'group by cno HAVING COunt(*)>=5;
select avg(Degree) from Score group by cno HAVING COunt(*)>=5 and  Cno like '3%';
select avg(Degree) from Score where Cno in (select Cno from Score group by Cno having count(*)>5)and Cno like '3%' group by Cno;
select avg(Degree) from Score where Cno in (select Cno from Score where Cno like '3%' ) group by Cno having count(*)>5;
-- 12、查询分数大于70，小于90的Sno列
select sno from score where degree>70 or degree<90;
-- 13查询所有学生的Sname、Cno和Degree列。
select sname,cno,degree from student join score on student.sno=score.sno;
-- 13、查询所有学生的Sno、Cname和Degree列
select sno,cname,degree from course join score on score.cno= course.cno;
-- 15查询所有学生的Sname、Cname和Degree列。
select sname,cname,degree from student join score on student.sno=score.sno join course on score.cno= course.cno ;
-- 16、查询“95033”班学生的平均分。
select avg(degree) from student join score on student.sno=score.sno WHERE class=95033;
select avg(degree) from student join score on student.sno=score.sno group by class having  class=95033;
-- 17、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno,sname,sbirthday from student where sno <>108 and YEAR(sbirthday)  in (select YEAR(sbirthday) from student where sno=108);
select Sno,Sname,Sbirthday from Student where YEAR(Sbirthday) = (select YEAR(Sbirthday) from Student where Sno = '108')
-- 18查询“张旭“教师任课的学生成绩（姓名）。
select sname,degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where tname='张旭';
-- 19、查询考计算机导论的学生成绩
select sname,degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where cname='计算机导论';
-- 20、查询李诚老师教的课程名称
select distinct cname from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where tname='李诚';
-- 21、教高等数学的老师是哪个系的
select depart from teacher join course on teacher.tno= course.tno where cname='高等数学';
-- 22、查询选修某课程的同学人数多于5人的教师姓名。
select tname from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) group by cname having count(*)>5;
-- 23、查询95033班和95031班全体学生的记录
select * from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where class=95033 or class=95031;
-- 24查询存在有85分以上成绩的课程Cno.
select distinct cno  from score where degree>85;
-- 25、查询出“计算机系“教师所教课程的成绩表
select sno,cno,degree from score where degree in (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
-- 26、 查询所有教师和同学的name、sex和birthday.
select tname as name, tsex as sex,tbirthday as birthday from teacher
union  select sname,ssex, sbirthday from student;
-- 27、查询所有“女”教师和“女”同学的name、sex和birthday.
select tname as name, tsex as sex,tbirthday as birthday from teacher where tsex='女'
union  select sname,ssex, sbirthday from student where ssex='女';
-- 28查询所有任课教师的Tname和Depart.
select tname,depart from teacher where tno in (select tno from course);
-- 29、查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher left join(student join score on student.sno=score.sno join course on score.cno= course.cno ) on teacher.tno= course.tno where degree is null;
select tname,depart from teacher where tno not in (select tno from course where cno in (select cno from score))
-- 30、查询至少有2名男生的班号。
select class from student where ssex='男' GROUP BY class having count(*)>=2;
-- 31、查询Student表中不姓“王”的同学记录。
select * from student where sname not in (select sname from student where sname like '王%');
-- 32、查询Student表中每个学生的姓名和年龄
select sname,(year(now())-year(Sbirthday))as 年龄 from student;
-- 33、查询Student表中最大和最小的Sbirthday日期值
select max(Sbirthday),min(Sbirthday) from student;
-- 34、以班号和年龄从大到小的顺序查询Student表中的全部记录
select *from student order by class desc,Sbirthday asc;
-- 35、查询“男”教师及其所上的课程。
select DISTINCT tname ,cname from  (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where tsex='男';
-- 36、查询最高分同学的Sno、Cno和Degree列。
select sno,cno,max(degree) from score;
-- 37、查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex=(select ssex from student where sname='李军');
-- 38、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex=(select ssex from student where sname='李军')
and class=(select class from student where sname='李军');
-- 39、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select sno,cno,degree from score where degree in (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where cname='计算机导论'and ssex='男'); 
select   year(CURRENT_DATE () )
select getdate() 
-- 查询存储引擎
show engines;
-- 显示可用存储引擎
show variables like 'have%'; 
-- concat多个字段联合
select tname ,cname ,concat(tname,':11',cname ) from  (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where tsex='男';
-- 查出字段间的卡迪尔积
select* from  student ,score
-- std方差
select sno,cno,degree,std(degree)from score where degree in (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
-- group_concat(sname) 查询分组中各项
select class,AVG(degree),group_concat(sname)from student join score on student.sno=score.sno group by class ;
-- 多个分组查询 先按class分组 再sname相同的再分组
select class,AVG(degree),group_concat(sname)from student join score on student.sno=score.sno group by class,sname;
-- exists用法
select *from score where  exists (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
select sno,cno,degree,std(degree)from score where degree  in (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
select *from score where  not exists (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');
-- 获取字符集排列方式
select collation(sname)from student;
-- 获取字符集
select charset(sname)from student;
-- 获取数据库版本号
select version();
-- 获取服务器连接数
select connection_id();
-- 获取数据库的名称
select database();
select schema();
-- 获取用户名
select user();
select system_user();
select session_user();
select current_user;
select current_user();
-- 获取最近生成auto_inscrenment的值
select last_insert_id();
-- 对字符串加密
select password('mrsoft');
-- 普通加密
select md5('mrsoft');
-- 加密
select encode('mrsoft','mr');
-- 解码
select decode('mrsoft','mr');
-- view
create view ccc as select class,AVG(degree),group_concat(sname)from student join score on student.sno=score.sno group by class,sname;
show create view ccc;
desc ccc;
show table status like 'ccc';
explain select sno,cno,degree,std(degree)from score where degree  in (select degree from (student join score on student.sno=score.sno join course on score.cno= course.cno join teacher on teacher.tno= course.tno) where depart='计算机系');