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;