MySQL学习-2

DQL查询数据库（最重点）

DQL（Data Query Language：数据库查询语言）

• 所有的查询操作都用它：select
• 简单的查询，复杂的查询它都能做~
• 数据库中最核心的语言！！！
• 使用频率最高的语句

SELECT 语法：

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
	[left | right | inner join table_name2] -- 联合查询
	[WHERE ...] -- 指定结果需要满足的条件
	[GROUP BY ...] -- 指定结果按照哪几个字段来分组
	[HAVING] -- 过滤分组记录必须满足的次要条件
	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}];
	-- 指定查询的记录从哪条至哪条

插入初始表：

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;

-- 创建年级表
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;

-- 创建科目表
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;

-- 创建成绩表
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;

-- 插入学生数据 其余自行添加 这里只添加了2行
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',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1003,'123456','赵强3',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233'),
(1004,'123456','赵强4',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
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);

-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
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);

指定查询字段

查询：

-- 查询全部的学生： select 字段 from 表
SELECT * FROM `student`;

-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`;

-- 别名，给结果（字段 或是 表）起一个名字 AS
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student` AS s;

-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',`studentname`) AS 新名字 FROM `student`;

去重 distinct：

（去除SELECT查询出来的结果中重复的数据，重复的数据值显示一条）

-- 查询一下有哪些同学参加了考试
SELECT * FROM `result`; -- 查询全部的考试成绩
SELECT `studentno` FROM `result`;	-- 查询有哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result`; -- 发现重复数据，去重

数据库的列（表达式）:

例如，文本值、列、NULL、函数、计算表达式、系统变量…

select 表达式 from 表;

SELECT VERSION(); -- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果; -- 用来计算（表达式）
SELECT @@auto_increment_increment; -- 查询自增的步长（变量）

-- 学员考试成绩 +1 分查看
SELECT `studentno`,`studentresult`+1 AS `提分后` FROM `result`;

where条件句子

• 作用：检索数据中符合条件的值

• 逻辑运算符：（搜索条件由一个或多个表达式组成！结果 布尔值）

运算符	语法	描述
and &&	a and b , a&&b	逻辑与
or ||	a or b , a||b	逻辑或
not !	not a , !a	逻辑非

SELECT `studentno`,`studentresult` FROM `result`;

-- 查询考试成绩在 95~100 分之间
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=95 AND `studentresult`<=100;

-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 80 AND 100;

-- 除了1000号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno`=1000;

• 模糊查询：（比较运算符）

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为NULL，结果为真
BETWEEN	a between b and c	若a在b和c之间，结果为真
LIKE	a like b	SQL匹配，如果a匹配b，则结果为真
IN	a in(a1,a2,a3…)	假设a在a1，或者a2…其中的某一个值，结果为真

-- ========== 模糊查询 ==========
-- 查询姓张的同学
-- 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 '%伟%';


-- ========== in（具体的一个或者多个值） ==========
-- 查询 1000,1001,1003号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno in (1000,1001,1002);

-- 查询在北京朝阳的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE address in ('北京朝阳');

-- ========== null,not null ==========
-- 查询地址为空的学生 null,''
SELECT `studentno`,`studentname` FROM `student`
WHERE address = '' OR address IS NULL;

联表查询

操作	描述
INNER JOIN	如果表中至少有一个匹配，就返回行
LEFT JOIN	会从左表中返回所有的值，即使右表中没有匹配
RIGHT JOIN	会从右表中返回所有的值，即使左表中没有匹配

-- ========== 联表查询 join ==========

/* 思路
1.分析需求，分析查询的字段来自哪些表（连接查询）
2.确定使用哪种连接查询？ 7种
3.确定交叉点（这两个表中哪个数据时相同的）
4.判断条件：学生表.studentno = 成绩表.studentno
*/

-- 连接查询：join [连接的表] on [判断的条件]
-- 等值查询：where

-- 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;


-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult IS NULL;


-- 思考题（查询参加了考试的同学信息：学号,姓名,科目名,分数）
-- 以 result 表为基准
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 表 XXX JOIN 连接的表 ON 交叉条件
-- 假设存在一种多张表查询，先查询两张表然后再慢慢增加

自连接（了解）

• 自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

-- 自连接数据库

CREATE TABLE `school`.`category`(
	`categoryid` INT(3) NOT NULL COMMENT 'id',
	`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
	`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
	PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES (2, 1, '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values (3, 1, '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values (5, 1, '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES (4, 3, '数据库'); 
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values (8, 2, '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values (6, 3, 'web开发'); 
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS (7, 5, 'ps技术');

-- 查询父子信息

SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS `子栏目`
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`;

-- ......

分页和排序

排序：

-- 排序：升序 ASC，降序 DESC
-- ORDER BY 通过哪个字段排序，怎么样

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult ASC;

分页：

-- 为什么分页？
-- 缓解数据库压力，给人的体验更好（另一种是“瀑布流”）
-- 语法：limit 起始值,页面大小
-- LIMIT 0,5	1~5
-- LIMIT 1,5	2~6
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
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 = '数据库结构-1'
ORDER BY studentresult ASC
LIMIT 0,5;

-- 第一页：limit 0,5
-- 第二页：limit 5,5
-- 第三页：limit 10,5
-- ...
-- 第N页：limit (n-1)*pageSize,pageSize
-- 【pageSize：页面大小】
-- 【(n-1)*pageSize：起始值】
-- 【n：当前页】
-- 【数据总页数/页面大小 = 总页数】

子查询

-- 查询 “数据库结构-1” 的所有考试结果（学号，科目编号，成绩）
-- 方式一：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方式二：使用子查询（由里及外）
-- 查询所有 “数据库结构-1” 的学生学号
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE subjectno = (
	SELECT subjectno FROM `subject`
	WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

-- 查询课程为 “高等数学-2” 且分数不小于80的同学的学号和姓名
SELECT s.studentno,studentname
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` = '高等数学-2' AND studentresult>=80

-- 分数不小于80的学生的学号和姓名
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE `studentresult`>=80

-- 在以上基础上增加一个科目，“高等数学-2”
-- 查询 “高等数学-2” 的编号
SELECT s.studentno,studentname
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno
WHERE studentresult>=80 AND subjectno = (
	SELECT subjectno FROM  `subject`
	WHERE `subjectname` = '高等数学-2'
)

-- 再改造（由里即外）
SELECT studentno,studentname
FROM student
WHERE studentno IN (
	SELECT studentno 
	FROM result
	WHERE studentresult>80 AND subjectno = (
		SELECT subjectno
		FROM `subject`
		WHERE `subjectname` = ''
	)
)

MySQL函数

常用函数

-- 数学运算
SELECT ABS(-8);	-- 绝对值
SELECT CEILING(9.4);	-- 向上取整
SELECT FLOOR(9.4);	-- 向下取整
SELECT RAND();	-- 返回一个0~1之间的随机数
SELECT SIGN(10);	-- 判断一个数的符号 0返回0；	负数返回-1；正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航');	-- 字符串长度
-- ......

-- 时间和日期函数（重点）
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE();	-- 获取当前日期
SELECT NOW(); -- 获取当前时间
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE(); -- 系统时间

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());


-- 系统
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

聚合函数（常用）

函数名称	描述
COUNT()	计数
SUN()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

-- ========== 聚合函数 ==========
-- 都能够统计表中的数据
SELECT COUNT(`borndate`) FROM `student`; -- count(字段),会忽略所有的null值
SELECT COUNT(*) FROM `student`;	-- count(*)，不会忽略null值，本质：计算行数
SELECT COUNT(1) FROM `student`; -- count(1)，不会忽略所有的null值，本质：计算行数

SELECT SUM(`studentresult`) AS '总和' FROM `result`;
SELECT AVG(`studentresult`) AS '平均分' FROM `result`;
SELECT MAX(`studentresult`) AS '最高分' FROM `result`;
SELECT MIN(`studentresult`) AS '最低分' FROM `result`;

数据库级别的MD5加密（拓展）

• 增强算法的复杂度和不可逆性

-- ========== 测试MD5加密 ==========
CREATE TABLE `testmd5` (
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 明文密码
INSERT INTO `testmd5`
VALUES(1,'zhangsan','123456'),
	(2,'lisi','123456'),
	(3,'wangwu','123456');
	
-- 加密
UPDATE testmd5 set pwd=MD5(pwd) WHERE id=1;

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'xiaoming',MD5('123456'));

-- 如何校验：将用户传递过来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` = 'xiaoming' AND pwd = MD5('123456');