DQL查询数据(重点)
DQL
(Date Query Language：数据库查询语言)
- 所有的查询操作都用它 select
- 简单的查询 复杂的查询它都能做
- ==数据库中最核心的语言，最重要的语句
- 使用频率最高的语句
--数据库表1
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;
--数据库表2
-- 创建年级表
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;
-- 插入科目数据
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);
-- 插入学生数据 其余自行添加 这里只添加了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');
-- 插入成绩数据 这里仅插入了一组，其余自行添加
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,'预科班');
指定查询字段
当列名字不能够直接说明查询的事务的时候 可以给字段取别名
-- 查询表
-- 查询全部的学生 SELECT 字段 FROM 表
select * from student
-- 查询指定字段
select `studentno`,`studentname` from student
-- 别名 给结果取一个名字 AS
-- 字段可以取别名 表也可以 同样使用as关键字
select `studentno` as 学号,`studentname` as 学生姓名 from student
-- 函数 concat(a,b) 拼接字符串 同时重命名字段
select concat('姓名： ',studentname)AS 新名字 from student
语法: SELECT 字段 ..... FROM 表
去重 distinct
作用：去除select查询出来的结果中重复的数据 只显示一条
-- 查询一下有那些同学参加了考试 成绩
-- 查询全部的考试成绩
select * from result
-- 查询有那些同学参加了考试
select `studentno` from result
-- 发现重复数据 去重
select distinct `studentno` from result
数据库的列(表达式)
-- 查询系统版本 (函数)
SELECT VERSION()
-- 用来计算 (表达式)
SELECT 100*100-1 AS 计算结果
-- 查询自增的步长 （变量）
SELECT @@auto_increment_increment
-- 学员考试成绩 +1 查看
SELECT `studentno`,`studentresult`+1 AS 加分后 FROM result
总结
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量......
select 表达式 from 表
where条件子句
作用：检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成 结果为 bool值
mysql官网最全的运算符与函数
链接：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与 |
| or || | a or b a||b | 逻辑或 |
| not ! | not a !a | 逻辑非 |
-- where子语句
SELECT `studentno`,`studentresult` FROM result
-- 查询考试成绩在80到100之间
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult >=80 AND studentresult<=100
-- 将and 改为 &&
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult >=60 && studentresult<=100
-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 70 AND 100
-- ！逻辑 除了1000号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE studentno !=1000
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno` = 1000
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为null，结果为真 |
| is not null | a is not null | 如果操作符为not 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 '张__'
-- 查询名字中有 伟 字的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%伟%'
-- 结合in 进行模糊查询
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno`IN (1000,1001,1002)
-- 查询在某个地方的学生
SELECT `studentno`,`studentname`,`address`FROM `student`
WHERE `address` IN ('北京朝阳')
-- null not 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
联表查询
join 对比 七种方法
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配值 返回行 |
| left join | 会从左表中返回所有的值 即使右表中没有匹配 |
| right join | 会从右表中返回所有的值 即使左表中没有匹 |
-- 联表查询
-- 查询了参见考试的同学的（学号 姓名 科目编号 分数）
SELECT * FROM student
SELECT * FROM result
/*思路
1. 分析需求 分析查询的字段来自那些表 （连接查询）
2.确定使用那种连接查询 7种
确定交叉点（两个表种的那种数据是相同的）
判断的条件：学生表种的 studentno = 成绩表种的 studentno
*/
-- join on (判断的条件) 连接查询
-- where 等值查询
-- inner join 交集
SELECT student.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student
INNER JOIN result
WHERE student.studentno = result.studentno
-- right join
SELECT student.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student
RIGHT JOIN result
ON student.studentno = result.studentno
-- left join
SELECT student.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student
LEFT JOIN result
ON student.studentno = result.studentno
-- 查询缺考的学生
SELECT student.`studentno` AS 学号 ,`studentname` AS 学生姓名,
`subjectno` AS 考试科目,`studentresult` AS 考试成绩
FROM student
LEFT JOIN result
ON studentresult IS NULL
-- 查询参见了考试的同学的详细信息
SELECT student.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student
RIGHT JOIN result
ON result.studentno = student.studentno
INNER JOIN `subject`
ON result.subjectno = `subject`.subjectno
自连接
自己的表与自己的表连接，核心：一张表拆为两张一样的表即可
自连接数据库：
-- 自连接
CREATE TABLE `school3`.`category`(
`categoryid` INT(3) NOT NULL COMMENT '主题id',
`pid` INT(3) NOT NULL COMMENT '父id',
`categoryname` VARCHAR(10) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `category`(`categoryid`,`pid`,`categoryname`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','2','办公信息'),
('8','5','ps技术');
父类
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 7 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 8 | ps技术 |
操作：查询父类对应的子类的关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 将一张表进行拆分 父类id与子类id相同的情况下 进行对比
-- 查询父子关系
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 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'