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條件子句

作用：檢索資料中符合條件的資料
搜尋語句由一個或多個表示式組成，結果為布林值

邏輯

儘量使用英文字母

例子

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;

模糊查詢：比較運算子

-- 查詢姓劉的同學 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

• 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(指定列)，會忽略所有的 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 協議》，轉載必須註明作者和本文連結