3.DQL資料查詢語言（內連線，外連線，自連線）

DQL語言

查詢用到的資料表

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;

-- 插入學生資料 其餘自行新增 這裡只新增了5行
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'),
(1003,'123456','張三',1,3,'13800002555','陝西西安','1988-1-1','text111@qq.com','123456199001011235'),
(1004,'123456','李四',1,3,'138000022666','江西贛州','1000-1-1','text111@qq.com','123456199001011222'),
(1005,'123456','王五',1,3,'13800002299','四川成都','1999-1-1','text111@qq.com','123456199001011266');


-- 插入成績資料  這裡僅插入了一組，其餘自行新增
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);

DQL(資料查詢語言）

簡單查詢

-- 查詢所有學生資訊
select * from student;
-- 查詢指定列
select studentno,studentname from student;
-- 使用as 給列和表起別名，（as 可以省略）
select studentno as 學號,studentname as 姓名 from student as s;
-- 使用as ，為查詢結果取一個新名字
-- concat（）函式拼接字串
select concat('姓名：',studentname) As 新名字 from student;
-- distinct 關鍵字的使用
-- 查詢那些同學參見了考試，去去重複項
select * from result; -- 檢視考試成績
select  studentno from result -- 檢視那些同學參加了考試
select distinct studentno from result;
-- 瞭解：distinct去重複項，預設為全部查詢結果;

使用表示式的列

資料庫的表示式：一般有文字值。列值。null，函式和操作符等組成

-- select 查詢中可以使用表示式
select @@auto_increment_increment; -- 查詢自增步長
select version(); -- 查詢版本號
select 500*2-1 as 計算結果; -- 表示式
-- 學生考試成績集體加一分查詢
SELECT studentno,studentResult+1 AS '提分後' FROM result;

where 條件語句

作用：用於檢索資料表中，符合條件的記錄

select Studentno,StudentResult form result;
-- 查詢考試成績在95-100之間的,and 可以寫成&&
select Studentno,StudentResult from result
where StudentResult>=95 and StudentResult<=100;
-- 模糊查詢
select Studentno,StudentResult from result
where StudentResult between 95 and 100;
-- 查詢除了學號為1000以外的學生成績
select studentno,studentSult from result
where studentno!=1000;
-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

模糊查詢：比較操作符

• 數值資料型別的記錄之間才可以進行算術運算
• 相同資料型別的資料之間才能進行比較

-- LIKE
-- 查詢姓劉的同學的學號及姓名
-- 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
-- 查詢學號為1000,1001,1002的學生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查詢地址在北京,南京,河南洛陽的學生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛陽');

-- NULL 空
-- 查詢出生日期沒有填寫的同學
-- 不能直接寫=NULL , 這是代表錯誤的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查詢出生日期填寫的同學
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查詢沒有寫家庭住址的同學(空字串不等於null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

連線查詢

JOIN 對比

七種Join：

/*
連線查詢
   如需要多張資料表的資料進行查詢,則可通過連線運算子實現多個查詢
內連線 inner join
   查詢兩個表中的結果集中的交集，表中至少有一個匹配，就返回行
外連線 outer join
   左外連線 left join
       (以左表作為基準,右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
   右外連線 right join
       (以右表作為基準,左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充)
       
等值連線和非等值連線

-- jojn （連結的表） on （判斷的條件） 連線查詢
-- where 等值查詢

自連線
*/

-- 查詢參加了考試的同學資訊(學號,學生姓名,科目編號,分數)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,確定查詢的列來源於兩個類,student result,連線查詢
(2):確定使用哪種連線查詢?(內連線)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右連線(也可實現)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 等值連線
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

-- 左連線 (查詢了所有同學,不考試的也會查出來)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 查一下缺考的同學(左連線應用場景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

-- 思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
/*
	思路：
	1.分析需求，分析查詢的欄位來自哪些表：學號，學生姓名，科目名，分數）
	2.確定使用哪種連線查詢 7種
	確定交叉點 兩個表種那個資料相同
	判斷條件：學生表中 studentNO = 成績表 studentNO
*/
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 要查詢的哪些資料 select ....
-- 從那幾個表中查 from 表 xxx join 連線的表 on 交叉條件
-- 假如：存在多張表查詢，先查詢兩張表，然後在慢慢增加

-- from a left join b
-- from a right join b

自連線（瞭解）

自己的表和自己的表連線，核心：一張表拆為兩張一樣的表即可

/*
自連線
   資料表與自身進行連線

需求:從一個包含欄目ID , 欄目名稱和父欄目ID的表中
    查詢父欄目名稱和其他子欄目名稱
*/

-- 建立一個表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入資料
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','資訊科技'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公資訊');

-- 編寫SQL語句,將欄目的父子關係呈現出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然後將這兩張表連線查詢(自連線)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

-- 思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查詢學員及所屬的年級(學號,學生姓名,年級名)
SELECT studentno AS 學號,studentname AS 學生姓名,gradename AS 年級名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查詢科目及所屬的年級(科目名稱,年級名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'