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;

DQL(Date Query Language)数据库查询语句

去重 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 对比

DQL(Date Query Language)数据库查询语句

-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
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 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!
未填写
文章
247
粉丝
17
喜欢
213
收藏
58
排名:732
访问:9674
私信
所有博文
社区赞助商