4.DQL查询数据（最重点）、

4.1 DQL

(Data Query LANGUAGE:数据查询语言)

所有的查询操作都用它 Select

简单的查询，复杂的查询它都能做

数据库中最核心的语言，最重要的语句

使用频率最高的语句

Select完整的语法：

 SELECT
     [ALL | DISTINCT | DISTINCTROW ]
     [HIGH_PRIORITY]
     [STRAIGHT_JOIN]
     [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
     [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
     select_expr[, select_expr...]
     [FROM table_references
     [PARTITION partition_list]
      [WHERE where_condition]
      [GROUP BY {col_name| expr| position}
       [ASC | DESC],...[WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name|expr | position}
        [ASC| DESC ]  ,...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name'
           [CHARACTER SET charset_name]
           export_options
          | INTO DUMPFILE 'file_name'
          | INTO var_name[,var_name]]
      [FOR UPDATE | LOCK IN SHARE MODE]]

 

表里面的数据

 CREATE DATABASE IF NOT EXISTS `school`;
 -- 创建一个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,'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);
 -- 插入学生数据 其余自行添加 这里只添加了2行
 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` (`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');
 ​
 ​
 ​
 -- 创建科目表
 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`(`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);
 ​

 

4.2 指定查询字段

 -- 查询全部的学生   语法 SELECT * FROM 表
 SELECT * FROM student
 ​
 SELECT * FROM result
 ​
 -- 查询指定字段
 SELECT `studentno`,`studentname` FROM student
 ​
 --  别名，给结果起一个名字  AS 可以给字段起别名，也可以给表起别名
 SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS s
 ​
 -- 函数 CONCAT(a,b) 拼接字符串
 SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student
 ​
 ​
 ​
 ​
 ​
 ​

语法 SELECT字段... FROM 表

有的时候，列名字不是那么见名知意，我们起别名 AS 语法： 字段名 as 别名 表名 as 别名

起别名的结果图：

拼接字符串的结果：

 

去重 distinct

作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条

 -- 查询以下有哪些同学参加了考试，成绩
 SELECT * FROM result  -- 查询全部的考试成绩
 ​
 -- 查询有哪些同学参加了考试
 SELECT `studentno` FROM result
 ​
 -- 发现重复数据，去重
 SELECT DISTINCT `studentno` FROM result

数据库的列（表达式）

 SELECT VERSION()-- 查询系统版本（函数）
 ​
 SELECT 100 * 3 -1 AS 计算结果 -- 用来计算（表达式）
 ​
 SELECT @@auto_increment_increment -- 查询自增的步长（变量）
 ​
 -- 学员考试成绩 + 1分查看
 SELECT `studentno`,`studentresult` +1 AS ''FROM result
 ​
 ​

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

select 表达式from 表

4.3 where 条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成！结果为布尔值

逻辑运算符

运算符	语法	结果描述
and &&	a and b a&&b	逻辑与 两个都为真，结果为真
or ||	a or b a||b	逻辑或 其中一个为真，则结果为真
Not !	not a ! a	逻辑非 取反，真为假，假为真！

尽量使用英文字母

 -- ======================================  where =====================
 --  列不区分大小写
 SELECT studentno,`studentresult` FROM result
 WHERE studentresult>=95 AND studentresult<=100
 ​
 -- and &&
 SELECT studentno,`studentresult` FROM result
 WHERE studentresult>=95 && studentresult<=100
 ​
 -- 模糊查询（区间）
 SELECT studentno,`studentresult` FROM result
 WHERE studentresult BETWEEN 95 AND 100
 ​
 --  除了1000号学生之外的同学的成绩
 SELECT studentno,`studentresult` FROM result
 WHERE studentno != 1000;
  -- != not
  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,a4...)	假设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 '张__'
  -- 查询名字中间有嘉字的同学  %嘉%
  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`FROM `student`
  WHERE `address`IN ('北京朝阳','广东深圳');
  
  --  ====  null     not null ====
  -- 查询地址为空的学生  Null  ''
   SELECT `studentno`,`studentname`FROM `student`
   WHERE address='' OR address IS NULL
   
  -- 查询有出生日期的同学   即表示不为空
  SELECT `studentno`,`studentname` FROM `student`
  WHERE `borndate` IS NOT NULL
  
   -- 查询没有出生日期的同学   即表示为空
  SELECT `studentno`,`studentname` FROM `student`
  WHERE `borndate` IS  NULL

4.4 联表查询

JOIN对比

 

 --  查询参加了考试的同学（学号，姓名，科目编号，分数）
  SELECT * FROM student
  SELECT * FROM result
  
  /*
  
  思路
  1.分析需求，分析查询的字段来自哪些表，（连接查询）
  2.确定使用哪种连接查询  7种
  确定交叉点（这两个表中哪个数据是相同的）
  判断的条件：学生表中的 studentno = 成绩表 studentno
  */
  
  SELECT s.studentno,studentname,subjectno,studentresult
  FROM student AS s
  INNER JOIN result AS r
  WHERE s.studentno = r.studentno
  
  --  on 是先筛选后关联，where是先关联后筛选
  -- on 用于批量，where用于单个
  
  --  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
 

left join左连接：展示左表所有数据，右表符合on条件的数据，右表不符合则为空

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配