DQL语言（一）
DQL即Data Query Language 数据查询语言
所有的查询操作都用它 select
简单的查询，复杂的查询它都能做
数据库最核心的语言，最重要的语句
使用频率最高的语句
测试数据：
CREATE DATABASE `school`; USE `school`; -- 创建年级表 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; -- 插入数据 INSERT INTO `grade`(`gradeid`,`gradename`) VALUES (1,"大一"),(2,"大二"),(3,"大三"),(4,"大四"),(5,"大五"); -- 创建成绩表 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 `result` (`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES (1000,1,"2012-03-10 16:00:00","98"), (1000,2,"2012-03-11 16:00:00","80"), (1000,3,"2012-03-12 16:00:00","88"), (1000,4,"2012-03-13 16:00:00","88"), (1000,5,"2012-03-14 16:00:00","99"), (1010,1,"2012-03-10 16:00:00","60"), (1010,2,"2012-03-11 16:00:00","69"), (1010,3,"2012-03-12 16:00:00","78"), (1010,4,"2012-03-13 16:00:00","77"), (1010,5,"2012-03-14 16:00:00","80"), (1020,1,"2012-03-10 16:00:00","85"), (1020,2,"2012-03-11 16:00:00","83"), (1020,3,"2012-03-12 16:00:00","82"), (1020,4,"2012-03-13 16:00:00","81"), (1020,5,"2012-03-14 16:00:00","90"), (1030,1,"2012-03-10 16:00:00","69"), (1030,1,"2012-03-11 16:00:00","62"), (1030,1,"2012-03-12 16:00:00","78"), (1030,1,"2012-03-13 16:00:00","73"), (1030,1,"2012-03-14 16:00:00","89"), (1040,1,"2012-03-10 16:00:00","100"), (1040,2,"2012-03-11 16:00:00","100"), (1040,3,"2012-03-12 16:00:00","100"), (1040,4,"2012-03-13 16:00:00","100"), (1040,5,"2012-03-14 16:00:00","100"); 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; INSERT INTO `student` VALUES("1000","1242","小红",0,3,"2117264217","北京市朝阳区","2010-10-10 00:00:00","test@qq.com","424182948147124712"), ("1010","1902","小同",0,3,"7293741822","上海市浦东区","2012-04-12 00:00:00","text@qq.com","424182941232312712"), ("1020","9878","小明",1,3,"8797892642","广州市天河区","2002-02-24 00:00:00","miss@qq.com","641274818147124712"), ("1030","4252","小刚",1,3,"6776867222","长沙市岳麓区","1998-09-10 00:00:00","kiss@qq.com","212412512515215512"), ("1040","3451","小芳",0,3,"7人8327334","武汉市洪山区","2013-01-09 00:00:00","pear@qq.com","129388498428491842"); `student` -- 创建科目表 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; -- 插入数据 INSERT INTO `subject` VALUES(1,"高等数学",110,1), (2,"线性代数",101,2), (3,"流体力学",100,3), (4,"网络安全",111,4), (5,"数据结构",112,1);
查询SELECT
-- 查询全部学生 SELECT * FROM student -- 查询全部成绩 SELECT * FROM result -- 查询指定字段 SELECT studentno,studentname FROM student -- 给结果列起别名 SELECT studentno AS 学号,studentname AS 姓名 FROM student -- 给表取别名 SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s -- 函数Concat（a，b） SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student -- 去重复 SELECT DISTINCT studentno FROM result -- 给学生成绩+1分 SELECT studentno,studentresult+1 AS 提升后 FROM result -- 查系统版本 SELECT VERSION() -- 计算 SELECT 100*3-5 AS 计算结果 -- 查询自增步长 SELECT @@auto_increment_increment
-- 查询全部学生
SELECT * FROM student
-- 查询全部成绩
SELECT * FROM result
-- 查询指定字段
SELECT studentno,studentname FROM student
-- 给结果列起别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student
-- 函数Concat（a，b）
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student
-- 去重复
SELECT DISTINCT studentno FROM result
条件WHERE
作用：检索数据库中符合条件的值
逻辑运算符
| 运算符 | 语法 | 描述 |
| and && | a and b a&&b | 逻辑与，两个都为真结果才为真 |
| or || | a or b a||b | 逻辑或，一个为真结果就为真 |
| not ! | not a !a | 逻辑非，真为假，假为真 |
-- 查询成绩在95~100的学生学号和具体分数 SELECT studentno,studentresult FROM result WHERE studentresult BETWEEN 95 AND 100 SELECT studentno,studentresult FROM result WHERE studentresult >= 95 AND studentresult <= 100 SELECT studentno,studentresult FROM result WHERE studentresult >= 95 && studentresult <= 100 -- 查询除了1000学生之外的学生成绩 SELECT studentno,studentresult FROM result WHERE studentno != 1000 SELECT studentno,studentresult FROM result WHERE NOT studentno = 1000
模糊查询
| 运算符 | 表达式 | 描述 |
| IS NULL | a is null | 如果操作符为NULL，结果为真 |
| IS NOT NULL | a is not null | 如果操作符不为NULL，结果为真 |
| BETWEEN | a between b and c | 若a在b和c之间，结果为真 |
| like | a like b | SQL匹配，如果a匹配b，则结果为真 |
| in |
a in （a1,a2,a3....） |
如果a在a1或者a2或者....其中某一个中，结果为真 |
-- 查询名字带红的同学 SELECT * FROM student WHERE studentname LIKE '%红%' -- 查询姓小后面只有一个字的 SELECT * FROM studet WHERE studentname LIKE '小_'-- 同理两个字就两个_ -- 查询1010，1020，1030学员信息 SELECT * FROM student WHERE studentno IN (1010,1020,1030) -- 查询在北京的学生 in是具体的值 SELECT * FROM student WHERE address IN ('北京') -- 查询地址为空的学生 SELECT * FROM student WHERE address IS NULL OR address=''
