DQL(Date Query Language)数据库查询语句
DQL
- 所有的查询操作都用它 select
- 简单的查询和复杂的查询都能做
- 数据库中最核心的语言
- 使用频率最高的语句
数据库准备
create database if not exists `school2`;
-- 创建一个school数据库
use `school2`;-- 创建学生表
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;
-- 插入科目数据
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 student;
-- 查询指定字段
select `studentno`,`studentname` from student;
-- 别名,给结果起一个名字,也可以给表起别名 as 可以省略
select `studentno` as 学号,`studentname` 姓名 from student as s;
-- 函数 concat(a,b)
select concat('姓名:',studentname) as 新名字 from student;
去重 distinct
-- 查询哪些同学参加了考试,成绩
select * from result;
-- 查询了哪些同学参加了考试
select studentno from result
-- 去除select查询出来的结果中重复的数据,重复的数据只显示一条
select distinct studentno from result;
数据库的列(表达式)
select version() -- 查询系统版本号(函数)
select 100*3-1 as 计算结果 -- 用来计算(表达式)
select @@auto_increment_increment -- 查询自增的步长(变量)
-- 学员考试成绩加一分
select studentno,studentresult+1 as '提分后' from result;
数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量。。。
select 表达式 from 表名
where条件子句
作用:检索数据中符合条件的数据
搜索语句由一个或多个表达式组成,结果为布尔值
逻辑
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b | 逻辑与,两个都为真,结果为真 |
or || | a or b | 逻辑或,一个为真,结果为真 |
not ! | not a | 逻辑非 ,真为假,假为真 |
尽量使用英文字母
例子
select studentno,studentresult from result where studentresult >= 95 and studentresult <= 100;
-- 模糊查询(区间)
select studentno,studetnresult from result where studentresult between 95 and 100;
-- != 和 not
select studentno,studentresult from result wehre sutdentno != 1000;
select studentno,studentresult from result where not student = 1000;
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,结果为真 |
is not null | a is not null | 如果操作符不为null,结果为真 |
between | a berween a 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 '%刘%'
-- 查询1001,1002,1003学号的学员,in里面是具体的一个或多个值
select studentno,studentname from student where studentno in (1001,1002,1003);
-- 查询在北京的学生
select studentno,studentname from student where address in ('北京');
-- 查询地址为空的学生 null ''
select studentno,studentname from student where address = '' or address is null;
联表查询
join 对比
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
select * from sutdent
select * from result
/*
1. 分析需求,分析查询的字段来自哪些表
2.确定使用那种连接查询
确定交叉点(这两个表哪些数据是相同的)
判断条件:学生表中的studentno = 成绩表 studentno
*/
select s.studentno,studentname,subjectno,studentresult
from student s
inner join result r
where s.sutdentno = r.studentno
-- Right Join
select s.studentno,studentname,subjectno,studentresult
from student s
right join result r
on s.studentno = r.studentno
-- Left Join
select s.studentno,studentname,subjectno,studentresult
from student s
Left join result r
on s.studentno = r.studentno
操作 | 描述 |
---|---|
inner join | 如果表中至少一个匹配,就返回行 |
left join | 即使右表中没有匹配,也会从左表中返回所有的值 |
right join | 即使左表中没有匹配,也会从右表中返回所有的值 |
- join(连接的表) on(判断的条件) 连接查询
- where 等值查询
-- 查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student s
Left join result r
on s.studentno = r.studentno
where studentresult=null
- 要求:查询了参加考试的同学信息:学号,学生姓名,科目名,分数
select s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on r.studentno = s.studentno
inner join subject sub
on r.subjectno = sub.subjectno
自连接
先创建一个表
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.catrgoryid = b.pid;
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}]; -- 指定查询的记录从哪条至哪条
分页和排序
limit和order by
- 排序:升序 ASC,降序DESC
select s.studentno,studentname,subjectname,subjectresult
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 subjectresult asc
- 分页作用
缓解数据库压力,给人更好的体验,瀑布流
每页只显示五条数据
语法:limit 起始值,页面的大小
网页应用:当前,总页数,页面的大小
select s.studentno,studentname,subjectname,subjectresult
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 subjectresult asc
limit 0,5;
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第n页 limit 5n-5,5
-- 数据总数/页面大小+1 = 总页数
子查询和嵌套查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
查询数据库结构-1所有的考试结果(学号,科目名字,成绩)
-- 方式一:使用连接查询
select sutdentno,r.subjectno,studentresult
from result r
inner join subject sub
on r.subjectno = sub.subjectno
where subjectname = '数据库结构-1'
order by studentno desc
-- 方式二:使用自查询(由里及外)
select studentno,subjectno,studentresult
from result
where subjectno = (
select subjectno from subject where subjectname = '数据库结构-1'
)
-- 查询所有数据库结构-1的学生的编号
select subjectno from subject where subjectname = '数据库结构-1'
-- 查询课程为高等数学-2 并且分数不小于80 的同学的学号和姓名
select s.studentno,studentname
from student s
inner join result r
on s.studentno = r.studentno
inner join subject sub
on sub.subjectno = r.subjectno
where subjectname = '高等数学-2' and studentresult>=80;
-- 子查询
select studentno,studentname
from student s
inner join result r
on r.studentno = s.studentno
where studentresult >= 80 and studentno = (
select subjectno from subject where subjectname='高等数学-2'
)
-- 再改造
select studentno,studentname from student where studentno in(
select student from result where studentresult>80 and subjectno = (
select subjectno from subject where subjectname = ' 高等数学-2'
)
)
MySQL 函数
- 常用函数
-- 数学运算符
-- 绝对值
select ABS(-8)
-- 向上取整,返回10
SELECT CEILING(9.4)
-- 向下取整,返回9
SELECT FLOOR(9.4)
-- 返回0~1之间的随机数
SELECT RAND()
-- 判断一个数的符号:0-0,负数- -1,正数 1
SELECT SIGN(-10)
-- 字符串函数
SELECT CHAR_LENGTH('测试')
-- 合并字符串
SELECT CONCAT('I ','am ','chinese')
-- 替换插入字符串,第一个从1开始
SELECT INSERT('hello world!',7,11,'haha')
-- 小写字母
SELECT LOWER('HaHa')
-- 大些字母
SELECT UPPER('aaa')
-- 返回字母第一次出现的索引,不区分大小写
SELECT INSTR('HuDu','d')
-- 替换出现的指定的字符串
SELECT REPLACE('hello','e','a')
-- 返回指定的字符串,截取开始位置,截取几个
SELECT SUBSTR('hello world',7,5)
-- 反转字符串
SELECT REVERSE('hello')
例子
-- 查询姓周的同学,姓改为 邹
select replace(studentname,'周','邹') from student
where studentname like '周'
-- 时间和日期函数
-- 当前日期
SELECT CURRENT_DATE()
-- 获取当前的日期
SELECT CURDATE()
-- 获取当前的时间
SELECT NOW()
-- 本地时间
SELECT LOCALTIME()
-- 系统时间
SELECT SYSDATE()
SELECT YEAR(NOW())
SELECT MONTH(NOW())...
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
- 聚合函数(常用)
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
… | … |
-- count(指定列),会忽略所有的 null 值
SELECT COUNT(studentname) FROM student;
-- count(*),不会忽略 null 值,本质计算行数
SELECT COUNT(*) FROM student;
-- count(1),不会忽略 null 值,本质计算行数
SELECT COUNT(1) FROM student;
SELECT SUM(studentresult) as '总和' FROM result;
SELECT AVG(studentresult) as '总和' FROM result;
SELECT MAX(studentresult) as '总和' FROM result;
SELECT MIN(studentresult) as '总和' FROM result;
-- 查询不同课程的平均值,最高分,最低分,平均分大于80
select subjectname,avg(studentresult) 平均分,max(studentresult),min(studentresult)
from result r
inner join subject sub
on r.subjectno = sub.subjecton
group by r.subjectNo -- 通过什么字段来分组
having 平均分>80
本作品采用《CC 协议》,转载必须注明作者和本文链接