mysql数据库查询 倒数5行平均数_mysql查询语句-like查询-限制行数-求平均分-分组统计-最高成绩-连接查询...

SELECT * FROM teacher; SELECT tid,tname FROM teacher; -- 指定个别列 SELECT tname FROM teacher; -- 跨数据库查询user表中的用户名，密码 ，主机 SELECT USER,PASSWORD,HOST FROM mysql.user; SELECT * FROM db.teacher; DROP TABLE st; -- 建立一个学生信息表 CREATE TABLE st (     sno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,     sname VARCHAR(10) NOT NULL,     sgender ENUM('男','女') DEFAULT '男',     sbirthday DATETIME,     saddress VARCHAR(30) ,     sscore TINYINT UNSIGNED,     sdept VARCHAR(10) NOT NULL )ENGINE=MYISAM AUTO_INCREMENT=201400 DEFAULT CHARSET=utf8; INSERT st VALUES(NULL,'李四','男','1980-1-1','河南郑州市',80,'计算机科学'); INSERT st VALUES(NULL,'张三','男','1985-1-1','河南郑州市',85,'会计'); INSERT st VALUES(NULL,'张三丰','男','1983-6-5','河南郑州市',60,'会计'); INSERT st VALUES(NULL,'李丽','女','1970-3-1','河南郑州市',75,'计算机科学'); INSERT st VALUES(NULL,'李勇',NULL,'1995-1-3','河南郑州市',80,'计算机科学'); INSERT st VALUES(NULL,'赵六','男','2005-2-1','河南郑州市',95,'英语'); INSERT st VALUES(NULL,'李七','女','2000-3-1','河南郑州市',30,'计算机科学'); INSERT st VALUES(NULL,'王五','男','1983-1-1','河南郑州市',20,'英语'); INSERT st VALUES(NULL,'王六','男','1984-1-1','河南郑州市',0,'数学'); INSERT st VALUES(NULL,'赵四','男','1985-1-1','天津',NULL,'计算机科学'); INSERT st VALUES(NULL,'李四四','女','1986-10-10','北京',NULL,'数学'); SELECT * FROM st; -- 空条件 is null   is not null  SELECT * FROM st WHERE sscore IS NULL; SELECT sno 学号,sname 姓名,sscore 成绩 FROM st WHERE sscore IS NOT NULL; -- in ()   not in() SELECT * FROM st WHERE sdept IN ('计算机科学','会计'); SELECT * FROM st WHERE sdept NOT IN ('计算机科学','会计'); --  between and SELECT * FROM st WHERE sscore NOT BETWEEN 70 AND 80; SELECT * FROM st WHERE sscore>=70 AND sscore<=80; SELECT * FROM st WHERE sbirthday BETWEEN '1985-1-1' AND '2000-12-31'; SELECT * FROM st WHERE sbirthday > '1985-12-31'; -- like % 代表0或多个任意符号 SELECT * FROM st WHERE sname LIKE '李%'; SELECT * FROM st WHERE sname LIKE '李_'; SELECT * FROM st WHERE sname LIKE '___'; SELECT * FROM st WHERE sname LIKE '%三%'; SELECT * FROM st WHERE sname = '李%'; SELECT * FROM st WHERE sgender='男' AND sdept='计算机科学'; SELECT * FROM st WHERE sgender='男' OR sdept='计算机科学'; -- 查询语句排序 asc 可以不写代表升序  desc 代表降序 SELECT * FROM st ORDER BY sscore DESC; -- 限制结果行数 SELECT * FROM st LIMIT 0,2; -- 前两行 SELECT * FROM st LIMIT 2,5; -- 显示第三行开始到第七行 -- 求平均分 SELECT AVG(sscore) FROM st; SELECT MAX(sscore) FROM st; SELECT MIN(sscore) FROM st; SELECT COUNT(*),COUNT(sscore) FROM st; -- 11 总人数   9参加考试的人数 SELECT COUNT(sscore) FROM st; -- 有多少个专业 SELECT COUNT(DISTINCT sdept) 专业个数 FROM st; SELECT SUM(sscore) FROM st; -- 求和 -- 分组统计 SELECT sdept,AVG(sscore) 平均分,COUNT(*) 人数 FROM st GROUP BY sdept; SELECT * FROM st WHERE sscore = MAX(sscore); 集合函数  聚集函数 -- 条件中的那个叫子查询 -- 显示出成绩最高分的学生信息 SELECT * FROM st WHERE sscore = (SELECT MAX(sscore) FROM st); SELECT * FROM st WHERE sscore IN (SELECT MAX(sscore) FROM st); SELECT IF(sgender IS NULL,'保密',sgender) 性别,COUNT(*) 人数 FROM st GROUP BY sgender; SELECT IFNULL SELECT sname 姓名,IF(sgender IS NULL,'保密',sgender) 性别,IF(sscore IS NULL,'旷考',sscore) 成绩 FROM st; SELECT sdept,AVG(sscore) FROM st WHERE 1=1 GROUP BY sdept HAVING AVG(sscore)>=60 ORDER BY AVG(sscore) ASC LIMIT 3; -- 连接查询  多表查询 查询的结果是多个表中的信息 SELECT * FROM teacher; SELECT * FROM student; -- 查询学生的学号，姓名，及代课老师的姓名 SELECT sid,sname,tname FROM teacher,student WHERE tid=stid; SELECT sid 学号,s.sname 姓名,IF(tname IS NULL,'无',tname) 代课老师 FROM student s LEFT JOIN teacher ON tid=stid ORDER BY sid ASC; -- 建立job表 CREATE TABLE job (     id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,     NAME VARCHAR(5) NOT NULL,     jid INT UNSIGNED )ENGINE=MYISAM DEFAULT CHARSET=utf8; DROP TABLE job; INSERT INTO job VALUES(NULL,'李四',NULL); INSERT INTO job VALUES(NULL,'张三',1); INSERT INTO job VALUES(NULL,'王五',1); INSERT INTO job VALUES(NULL,'赵六',2); INSERT INTO job VALUES(NULL,'李勇',3); SELECT * FROM job;