【MySQL】mysql学习笔记03 查

一、对单表的操作

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`='凯尔希'