一、对单表的操作
1、*为通配符,意为查询所有信息 从student表中
SELECT *FROM student
2、查询特定列
SELECT `studentno` ,`studentname` FROM student
3、AS 意为重命名使查出的汇总表列名改为更容易理解的,并不会改变数据库中的名字,大概类似取绰号。
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student
4、CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',`studentname`)AS 新名字 FROM student
5、DISTINCT 关键字
作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条
SELECT DISTINCT `studentno` FROM result
6、查询时可加入计算
SELECT `studentno`,`studentresult`+1 AS `提分后` FROM result
7.查询时可加入条件
- %:代表0~任意个字符
- _:代表1个字符
二、联表查询
//查询马什么同学
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student
WHERE `studentname` LIKE '马%'
//查询什么梅同学
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student
WHERE `studentname` LIKE '%梅'
//查询名叫啥伟的同学
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student
WHERE `studentname` LIKE '_伟'
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student
WHERE `gradeid` IS NULL
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student
WHERE `gradeid` IS NOT NULL
//查询学号为1001,1003,1007的人
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student
WHERE `studentno` IN(1001,1003,1007)
//查询1001到1006的同学
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student
WHERE `studentno` BETWEEN 1001 AND 1006
– INNER JOJN
SELECT s.`studentno` AS 学号,`studentname` AS 姓名 ,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN `result` AS r
WHERE s.studentno=r.studentno
– LEFT JOJN 左表为主
SELECT s.`studentno` AS 学号,`studentname` AS 姓名 ,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN `result` AS r
ON s.studentno=r.studentno
– RIGHT JOJN 右表为主
SELECT s.`studentno` AS 学号,`studentname` AS 姓名 ,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN `result` AS r
ON s.studentno=r.studentno
– 三表查询
SELECT s.`studentno` AS 学号,`studentname` AS 姓名 ,`subjectname`AS 科目,`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
– 自连接
1.先建一个表
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
2.插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryname`)
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');
3、自己的表和自己连接,即一张表拆为两张表
SELECT a.`categoryname` AS 父栏目 ,b.`categoryname`AS 子栏目
FROM `category`AS a,`category`AS b
WHERE a.`categoryid`=b.`pid`
– 查询学生所属的年级(学号,姓名,年级)
SELECT `studentno`,`studentname`,`gradename`
FROM `student`AS s
INNER JOIN `grade` AS b
ON s.`gradeid`=b.`gradeid`
– 查询科目所属的年级(科目名称,年级)
SELECT `subjectname`,`gradename`
FROM `grade`AS g
INNER JOIN `subject` AS s
ON g.`gradeid`=s.`gradeid`
– 查询参加了高等数学-4的学生信息 (学号,姓名,科目,成绩)
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`='高等数学-4'
– 排序 升序ASC 降序DESC
– 分页 limit 初始值 页面大小
– 第一页 0,4
– 第二页 4,4
– 第三页 8,4
– 第四页 12,4result
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`
ORDER BY `studentresult` DESC
LIMIT 12,4
– 查询C语言-1课程成绩排名前五的学生信息并且分数大于80(学号,姓名,课程名称,成绩)
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`='C语言-1' AND `studentresult`>=80
ORDER BY `studentresult` DESC
LIMIT 0,5
– 查询C语言-1课程的所有考试结果(学号,课程编号,成绩)
– 方式一:使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='C语言-1'
– 方式二:子查询(由里及外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
SELECT `subjectno`
FROM `subject`
WHERE `subjectname`='C语言-1'
)
– 查询’C语言-1’分数不小于80分的学生信息(学号,姓名)
SELECT s.`studentno`,`studentname`
FROM `student` AS s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='C语言-1'AND `studentresult`>=80
– 子查询嵌套
SELECT `studentno`,`studentname`FROM `student`WHERE `studentno`IN(
SELECT `studentno`FROM `result` WHERE `studentresult`>80 AND `subjectno`=(
SELECT `subjectno`FROM `subject`WHERE `subjectname`= 'C语言-1'
)
)
– ================= 常用函数 ================
SELECT ABS(-5) -- 绝对值
SELECT CEILING(5.4) -- 向上取整
SELECT SYSDATE() -- 时间
– ================= 聚合函数=================
SELECT COUNT(studentname) FROM student
– 计数 count(字段)会忽略null
SELECT COUNT(*) FROM `student`-- 不会忽略null
SELECT COUNT(1) FROM `student`-- 不会忽略null
SELECT SUM(`studentresult`) FROM `result` -- 总和
SELECT AVG(`studentresult`) FROM `result` -- 平均
SELECT MAX(`studentresult`) FROM `result` -- 最大值
SELECT MIN(`studentresult`) FROM `result` -- 最大值
– 分组GROUP BY
– 查询 不同课程平均分,最高分,最低分
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `subject` sub
INNER JOIN `result` r
ON sub.`subjectno`=r.`subjectno`
GROUP BY `subjectname`
– =MD5加密============
UPDATE `student0` SET pwd=MD5(pwd) WHERE `name`='凯尔希'