DQL学习-数据库语言的重点

DQL 数据库查询语言-select和concat

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL AUTO_INCREMENT 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) DEFAULT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) DEFAULT 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;

-- 创建科目表`subject`
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;


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','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');`category``grade``result``student``subject`

DROP PROCEDURE IF EXISTS loop_create_table;
DELIMITER $
CREATE PROCEDURE loop_create_table()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=1000 DO
INSERT INTO student(`StudentName`,`Sex`) VALUES('张三','2');
SET i = i+1;
END WHILE;
END $
CALL loop_create_table();

TRUNCATE TABLE	`student`;


SELECT * FROM student;

SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
SELECT CONCAT('NO.', studentno) AS 新学号,studentname AS 姓名 FROM student AS s;

语法

--选择所有字段
SELECT * FROM 表名;

-- 选择字段并创建别名
SELECT 字段名1 AS 别名1[,字段名2 AS 别名2,...] FROM student AS 表别名;

-- 函数concat的用法
SELECT CONCAT('NO.', studentno) AS 新学号,studentname AS 姓名 FROM student AS s;

distinct去重

TRUNCATE TABLE	`result`;
INSERT INTO result(`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES (1,0,SYSDATE(), 20),(1,0,SYSDATE(), 20),(1,0,SYSDATE(), 20),(1,0,SYSDATE(), 20),(1,0,SYSDATE(), 20),(1,0,SYSDATE(), 20);
INSERT INTO result(`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES (2,0,SYSDATE(), 30),(2,0,SYSDATE(), 20),(2,0,SYSDATE(), 20),(2,0,SYSDATE(), 30),(2,0,SYSDATE(), 20),(2,0,SYSDATE(), 20);

SELECT DISTINCT `studentno` AS 学号, `studentresult` AS 成绩 FROM result;

语法

-- 跟在select后面
SELECT DISTINCT 字段名1 AS 别名1[,字段名2 AS 别名2,...] FROM student AS 表别名;

数据库的列（表达式）

-- 获取数据库版本 函数
SELECT VERSION();

-- 计算 表达式
SELECT 100*3-1 AS 结果;

-- 计算 变量
SELECT @@auto_increment_increment;

-- 表的成绩+1查看
SELECT `studentno` studentresult+1 AS 成绩 FROM result;

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量

select 表达式 from 表

where条件子句

逻辑运算符

运算符	语法	意义
and &&	a and b a &&b	a 和b都满足为真
or ||	a or b a||b	a或b有一个满足为真
not !	not a !a	与a 的值相反

练习

-- ====================where 子句练习================================
INSERT INTO result(`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES (1,0,SYSDATE(), 20),(1,0,SYSDATE(), 21),(1,0,SYSDATE(), 22),(1,0,SYSDATE(), 23),(1,0,SYSDATE(), 24),(1,0,SYSDATE(), 25);
INSERT INTO result(`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES (2,0,SYSDATE(), 26),(2,0,SYSDATE(), 27),(2,0,SYSDATE(), 28),(2,0,SYSDATE(), 29),(2,0,SYSDATE(), 30),(2,0,SYSDATE(), 31);

-- 验证大小写不敏感 及where 子句条件
SELECT `studentNo`,`studentresult` AS 成绩 FROM result WHERE studentresult BETWEEN 20 AND 25;
SELECT `studentNo`,`studentresult` AS 成绩 FROM result WHERE studentresult >=20 AND studentresult <=25;

-- 验证大小写不敏感 及多条 where 子句条件
SELECT `studentNo`,`studentresult` AS 成绩 FROM result WHERE studentresult >=20 AND studentresult <=25 AND `studentno` != 1;
SELECT `studentNo`,`studentresult` AS 成绩 FROM result WHERE studentresult >=20 AND studentresult <=25 AND NOT `studentno` = 1;

模糊查询

为什么 is null 和is not null是模糊查询 参考 https://zhuanlan.zhihu.com/p/82434024 里面相亲的例子讲的很好

运算符	语法	意义
is null	a is null	a 为空，则结果为真
is not null	a is not null	a 不为空，则结果为真
between and	a between 10 and 20	a 在10到20之间为空
like	a like b	如果a匹配b, 则结果为真
in	a in (a1,a2,a3...)	精确匹配，如果a=a1 或者a=a2 或者 a=a3... 则结果为真

练习

-- ====================模糊查询练习================================
INSERT INTO student (`studentname`,`address`,`borndate`) VALUES ('李四','西安', CURRENT_DATE);
INSERT INTO student (`studentname`,`address`,`borndate`) VALUES ('王五','河南郑州', CURRENT_DATE);

SELECT `studentno`,`studentname` AS 姓名,`address` FROM student WHERE address IN ("西安",'河南郑州');
SELECT `studentno`,`studentname` AS 姓名,`address`,`borndate` FROM student WHERE borndate IS NOT NULL;
SELECT `studentno`,`studentname` AS 姓名,`address`,`borndate` FROM student WHERE borndate IS NULL;

SELECT `studentno`,`studentname` AS 姓名 FROM student WHERE `studentname` LIKE '王%';

联表查询

一定先在这里面看详细的讲解 sql之left join、right join、inner join的区别

操作	描述
inner join	以两个表的交集结果为准
left join	以左表的结果为基准，如果右表中没有对应结果以null 补齐
right join	以右表的结果为基准，如果左表中没有对应结果以null 补齐

-- ====================联表查询练习（重点）================================
-- 给subject表添加数据
INSERT INTO `subject` (`subjectname`,`classhour`,`gradeid`) VALUES (1,'高等数学','64','00001'),(2,'英语','32','00002'),(3,'物理化学','24','00003'),(4,'计算机基础','32','00004'),(5,'操作系统原理','24','00005');
INSERT INTO `result` (`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES (1,1,SYSDATE(),90),(1,2,SYSDATE(),70),(1,3,SYSDATE(),80),(1,4,SYSDATE(),60),(2,1,SYSDATE(),70),(2,2,SYSDATE(),69),(2,3,SYSDATE(),88);

-- 联表查询进阶 查询所有考生的姓名，学号，科目，成绩
/* 思路
1.一个表一个表的连接查询 
2. 涉及到的表 student,result,subject
3. 涉及到的关联 student 和result right，result 和subject inner join
*/
SELECT `studentname`,s.studentno,`subjectName`,`studentresult` FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON sub.subjectno = r.subjectno;

自联接

-- 查询所有考生的姓名，学号，科目，成绩
/* 思路
1.一个表一个表的连接查询 
2. 涉及到的表 student,result,subject
3. 涉及到的关联 student 和result right，result 和subject inner join
*/
SELECT `studentname`,s.studentno,`subjectName`,`studentresult` FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON sub.subjectno = r.subjectno;

-- ====================自联接练习（重点）================================
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','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');`category``grade``result``student``subject`

-- 获取父类对应子类的表
SELECT a.`categoryname` AS '父类别',b.`categoryname` AS '子类别' 
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.pid;