目录
select简单查询
建一个数据库和表
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1002,'123456','李白',2,4,'13800002552','广东广州','1991-1-1','text158@qq.com','123456199001011288'),
(1003,'123456','王源',3,5,'13800562222','四川成都','1992-1-1','text458@qq.com','123456188001011233'),
(1004,'123456','易烊千玺',4,6,'13812342222','湖南长沙','1993-1-1','text456@qq.com','123456199001011238'),
(1005,'123456','刘昊然',5,7,'13898752222','河南平顶山','1994-1-1','text185@qq.com','123456199771011233'),
(1006,'123456','王俊凯',6,8,'13855552222','浙江杭州','1995-1-1','text11178@qq.com','123456199262611233'),
(1007,'123456','杨紫',7,9,'13800006666','福建厦门','1996-1-1','text333@qq.com','123456148561011233');
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
INSERT INTO `grade` (`gradeid`,`gradename`)
VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
`classhour` INT(4) DEFAULT NULL COMMENT '学时',
`gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`subjectno` INT(4) NOT NULL COMMENT '课程编号',
`examdate` DATETIME NOT NULL COMMENT '考试日期',
`studentresult` INT (4) NOT NULL COMMENT '考试成绩',
KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- 查询全部的内容 SELECT 字段 FROM 表
SELECT * FROM studentSELECT * FROM result
-- 查询指定字段
SELECT `studentno`,`studentname` FROM student
-- 给查出的结果一个指定名字
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student有时候,列名字不是那么见名知意,我们可以给字段名起一个别名显示出来
-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',studentname)AS 新名字 FROM student
SELECT `studentno` FROM `result` -- 查询一下哪些同学参加了考试
-- 发现重复数据 去重 distinct关键字
作用:去除查询出来的数据中重复的数据,只显示一条
SELECT DISTINCT `studentno` FROM `result`
SELECT VERSION() -- 查询系统版本
SELECT 100*3-5 AS 计算结果 -- 用来计算数值
SELECT @@auto_increment_increment -- 查询自增的步长(变量)
-- 学生考试成绩
SELECT `studentno`,`studentresult` FROM `result`
-- 学生考试成绩全部+1
SELECT `studentno`,`studentresult`+1 AS 全部增加一分 FROM `result`
where条件子句
-- 查询考试成绩在95到100之间的
-- 可以使用and 、&& 、 或者 between and
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=80 AND `studentresult`<=100
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`BETWEEN 82 AND 100
-- 除了1000号学生的成绩
-- 使用!=或者not
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000
SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno` =1000
模糊查询like
like结合 %(代表0到任意个字符)或者_(代表一个字符)
-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符)或者_(代表一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '王%'
-- 模糊查询
-- like结合 %(代表0到任意个字符)或者_(代表一个字符)
-- 查询姓王的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '王%'
-- 查询姓王的同学,后面还有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '王_'
-- 查询姓王的同学,后面还有两个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '王__'
-- 查询名字中间有昊字的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%昊%'
-- 查询1000,1001号的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(1000,1001)
-- 查询在四川成都的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('四川成都')
-- 查询地址为空的学生 null或者''
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` ='' OR `address` IS NULL
-- 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL
-- 查询没有出生日期的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL
联表查询
-- 查询参加了考试的同学的学号、姓名、科目编号和分数
/*思路
1、分析需求,分析查询的字段来自哪些表(连接查询)
2、确定使用哪种连接查询
确定交叉点(这两个表中哪些数据是相同的)
判断的条件:学生表中的studentno=成绩表中的studentno
*/
-- Inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.`studentno`=r.`studentno`
-- right join 查询出所有的右表的相关内容,即使左表没有匹配的内容
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
-- left join 查询出所有的左表的相关内容,即使右表没有匹配的内容
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
-- 查询缺考的同学
-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE `studentresult` IS NULL
-- 查询参加了考试的同学的学号、姓名、科目名和分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
-- 要查询的数据 select...
-- 从哪几个表中查询 from 表... join 表... on 交叉的条件(相同的属性)
-- 假设存在一种多表查询,先查询两张表,再慢慢增加
-- 查询学生所属的年级(学号,学生的姓名,年级名称)
SELECT `studentno`,`studentname`,`gradename`
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.`gradeid`=g.`gradeid`
-- 查询科目所属的年级
SELECT `subjectname`,`gradename`
FROM `subject` AS sub
INNER JOIN `grade` AS g
ON sub.`gradeid`=g.`gradeid`
-- 查询参加高等数学-1考试的同学的学号,学生姓名,科目名,分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
拆成两张表
categoryid | categoryName |
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
pid | categoryid | categoryName |
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 查询父子信息,把一张表看成两个一模一样的表
SELECT a.`categoryname` AS '父类',b.`categoryname` AS '子类'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
分页limit和排序order by
排序
-- 按成绩升序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` ASC
-- 按学号降序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentno` DESC
分页 limit 起始的数据 一页显示的数据量
limit 5,6 当前页从第5条数据开始,显示6条
当数据量很大的时候,可以缓解数据库压力,给人的体验更好
第一页 limit 0,5
第二页 limit 5,5
第三页 limit 10,5
第n页 limit (n-1)*5,5 (n-1)*pagesize,pagesize
-- 查询Java第一学年 课程成绩排名前10的学生,并且分数要大于80的学生信息(学号,姓名,课程名称,分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `subjectname`='ava第一学年' AND `studentresult`>=80
ORDER BY `studentresult` DESC
LIMIT 0,10
子查询
在where语句中嵌套一个子查询语句
where(select * from table)
-- 查询 高等数学-1 的所有考试结果(学号,科目编号,成绩),降序排列
-- 方式1 使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` DESC
-- 方法2 使用子查询
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-1')
ORDER BY `studentresult` DESC
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80
-- 高等数学-1分数不小于80分的学生的学号和姓名
-- 子查询方式
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80 AND `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-1'
)
-- 高等数学-1分数不小于80分的学生的学号和姓名
-- Inner join
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1' AND `studentresult`>=80
-- 高等数学-1分数不小于80分的学生的学号和姓名
-- 嵌套两次的子查询
SELECT `studentno`,`studentname`
FROM `student` WHERE `studentno` IN(
SELECT `studentno` FROM `result`
WHERE `studentresult`>=80 AND `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-1'
)
)
分组
-- 查询不同课程的平均分,最高分和最低分,并且平均分大于80
-- 根据不同的课程进行分组
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`)AS 最大值,MIN(`studentresult`) AS最小值
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分>=80 -- 分组筛选的条件使用having而不是where